Solved

AIX - oracle halt

Posted on 2008-06-23
15
1,562 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 14

Expert Comment

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

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 48

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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

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 48

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 48

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 48

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 48

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 48

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 48

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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

691 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