Solved

AIX - oracle halt

Posted on 2008-06-23
15
1,547 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to recover a database from a user managed backup
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now