Solved

AIX - oracle halt

Posted on 2008-06-23
15
1,554 Views
Last Modified: 2013-12-18
Hi gurus,

 I have a problem with oracle database 10g running on AIX 5.3.
the Oracle halted and i can not log on to shut it down and restart through sqlplus.
 So i have to restart the AIX system to bring the oracle up.
 For window server i dont' have to restart the server but restart the Oracle service only

 Could you guys can help me manage this problem on AIX.

Thanks
 
 
0
Comment
Question by:thangoftin2
15 Comments
 
LVL 14

Expert Comment

by:mikelfritz
ID: 21852595
Log on as root to restart it.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21852724
what happens when you log in to sqlplus and try to log in?

If the oracle processes are running, you should be able to kill them.  I don't recommend killing them, but if you're going to reboot the system then you'll be doing it anyway.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21852847
You can not logon to Oracle because:
1. Shutting down Oracle via automated scripts allmost in every case
quits the listener.
2. Even by running listener your instance is not registered.

To fix:

1. Investigate the Listener:

% lsnrctl status
% lsnrctl services

2. If you do not see your service register it static

Backup LISTENER.ORA file!!!!!!!

In ...\network\admin open listener.ora

see

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Ora10g)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
  )

Add the entry
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
with regard to the particular Oracle Home and SID Name.

Restart the listener:
c:>lsnrctl stop
c:>lsnctl start


try to learn Oracle basics.
The fact that for such issue you sacrify 125 points displays
that you underestimate Oracle  ... and us!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:thangoftin2
ID: 21852911
@schwertner: i run Oracle on Unix system not windows, the database halted after long time running stable .
I tried to restart listener but it not work
@sdstuber: i used sqlplus command to login but can not connected to the instance.
                  of cource i can kill oracle process but how can i start it up after i killed them.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21853264
There are many causes for an instance to halt.

1. Full logs (listener.log, alert.log)
2. Too much sessions (most of them nonactive and even dead)
3. Bugs
4. Investigate the Network card (often it gets broken), see for changes of the
IP and domain name, speak with the Network admin - did he changed
the DNS address and the Gateway recently.

You have to sort out the reason.
Investigate every log file.
Post here every error message you get.
0
 

Author Comment

by:thangoftin2
ID: 21853726
1. Full logs ( if the log files is full , i could not start up oracle after reboot system. isn't it )
2. Too much sessions ( it's halted at mid nights when just a few sessions)
3. What kind of bugs u recommend ?
4. I checked the network and it worked well.

Other wise , what i concern is how to restart oracle in aix eventhough u can not log on to oracle by sqlplus ?
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21854643
1. Yes, but mainly on 32 bits architecture, 2 GB limit
3. Have you access to Metalink? It is very professional resource.
4. How many network cards you have on the box? Are they OK?
Have you static IP?

Something has to be written in the logs - Oracle, Oracle network, AIX system logs.
Please investigate all available logs.

Have you checked Listener.ora file as I suggested on 06.24.2008 at 09:12AM EEST
0
 

Author Comment

by:thangoftin2
ID: 21862379
Thanks bro,

I have check the lsnrctl services :
Service "MyService" has 1 instance(s)
and
Service "PLSExtProc" has 1 instance(s)


and i check the listener.ora so on:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.1.0)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = Server01)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )


As i described above, when oracle halted i can not log on to the oracle console by sqlplus, the screen was like that:

sqlplus username/password

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Jun 25 09:57:45 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

I also checked the log file : udump, cdump and listener.log but i could not investigate any things


0
 
LVL 47

Expert Comment

by:schwertner
ID: 21863133
O...OOOOOOOOO!
Finally!

Do you see the entry
(SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )

in the posting 06.24.2008 at 09:12AM EESTYour Comment

You have no registered instance by the listener.
Possibly it stops because there is nothing to do.

You have to add your entry like

   (SID_DESC =
      (SID_NAME = your_SID_here)
      (ORACLE_HOME = /u01/app/oracle/product/10.1.0)
    )

and restart the listener.

check the result

% lsnrctl services

Do you see the service?
0
 

Author Comment

by:thangoftin2
ID: 21863458
Thanks for your help, but there is no changes in the content of listener.ora file at all.
As my understanding when we create database on the server at the oracle installing phase, we key in the SID as "MyService" and even thought we do not have the SID_NAME in listener.ora file, but the SID is seemed as the default SID, so it's always available in the result of lsnrctl services such as

lsnrctl services :
Service "MyService" has 1 instance(s)
and
Service "PLSExtProc" has 1 instance(s)


0
 
LVL 47

Expert Comment

by:schwertner
ID: 21863577
if you think so ....
0
 

Author Comment

by:thangoftin2
ID: 21863612
:) dont be angry, i will print current listener.ora and result of lsnrctl services:

lsnrctl services

LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.5.0 - Production on 25-JUN-2008 15:30:58

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521))
Services Summary...
Service "ATMTEST" has 1 instance(s).
  Instance "ATMTEST", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:149 refused:0 state:ready
         LOCAL SERVER
Service "TESTXDB" has 1 instance(s).
  Instance "TEST", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: AIX01, pid: 409786>
         (ADDRESS=(PROTOCOL=tcp)(HOST=AIX01)(PORT=32781))
The command completed successfully


listener.ora

SID_LIST_TEST =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.1.0)
      (PROGRAM = extproc)
    )
  )

TEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = AIX01)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

0
 
LVL 47

Expert Comment

by:schwertner
ID: 21863718
This is very bad topic.
Oracle servers uses dynamic registration of the services.
But it seems ... sometimes they are able to unregister automatically the service.
I know the parameters in parameter file that support dynamic registration,
but this happens not immediate. Listener needs some time to do this and
I have no experience how to command this.

The rule is: No 10g and 11g installation is complete before
you statically register the service.
0
 

Author Comment

by:thangoftin2
ID: 21863959
"But it seems ... sometimes they are able to unregister automatically the service."

So what you mean is for the above reason i should edit the listener.ora file and restart listener?
0
 
LVL 47

Accepted Solution

by:
schwertner earned 125 total points
ID: 21864071
Hundereds of Askers on Experts Exchange have done this
and there were no problems.
Backup LISTENER.ORA    before this in order to recreate if you do mistake.
The syntax of these files is very sensitive to lexical errors.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Why Shell Scripting? Shell scripting is a powerful method of accessing UNIX systems and it is very flexible. Shell scripts are required when we want to execute a sequence of commands in Unix flavored operating systems. “Shell” is the command line i…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question