DBA Tips Archive for Oracle
|
DBA Tips Archive for Oracle
|
|
ORA-01041 / ORA-03113 after shutdown/startup - (Oracle 9i)
by Jeff Hunter, Sr. Database Administrator
Overview
When shutting down the database using SQL*Plus, you will need to do one of the following in order to startup the database again:
- Re-connect to the database again:
SQL> connect / as sysdba- Exit from SQL*Plus and start a new SQL*Plus session
Details
There seems to be a bug in SQL*Plus that does not allow you to perform a shutdown followed by a startup in the same SQL*Plus session.If you attempt to perform a startup and shutdown in the same session, you will be confronted with an ORA-01041 and ORA-03113 error. What seems to be happening is a problem with the allocation of shared memory segments. If you mistakenly try this, you have two options to recover from it:
- Bounce the database server (init 6).
- Remove all allocated shared memory segments using the ipcrm command.
Below is an example of the problem and how to solve it by removing the shared memory segments created by the startup command after shutting down the database:
- First notice that while the database is running, Oracle has allocated the proper shared memory segments: (SHMIDs = 10485772, 10518542, 10551311, 10584080, 10616849, 10649618, 10682387, 10715156, 10747925)
OEMDB on dbautil: ipcs ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 458759 root 777 196608 2 dest 0x00000000 1212424 root 777 196608 2 dest 0x00000000 1277961 root 777 196608 2 dest 0x00000000 1409034 root 777 196608 2 dest 0x00000000 2392075 root 777 196608 2 dest 0x00000000 10485772 oracle 640 4194304 10 0x00000000 2457613 root 777 196608 2 dest 0x00000000 10518542 oracle 640 33554432 10 0x00000000 10551311 oracle 640 33554432 10 0x00000000 10584080 oracle 640 33554432 10 0x00000000 10616849 oracle 640 33554432 10 0x00000000 10649618 oracle 640 33554432 10 0x00000000 10682387 oracle 640 33554432 10 0x00000000 10715156 oracle 640 33554432 10 0xc8131978 10747925 oracle 640 4194304 40 0x00000000 8126486 root 777 196608 2 dest ------ Semaphore Arrays -------- key semid owner perms nsems status 0x0db4c224 851970 oracle 640 154 ------ Message Queues -------- key msqid owner perms used-bytes messages- Now log into SQL*Plus and shutdown the database. From within that same session, using bang and the ipcs command, look at the shared memory. Notice that all the previously allocated shared memory segments have been removed.
OEMDB on dbautil: sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 25 17:20:33 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> !ipcs ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 458759 root 777 196608 2 dest 0x00000000 1212424 root 777 196608 2 dest 0x00000000 1277961 root 777 196608 2 dest 0x00000000 1409034 root 777 196608 2 dest 0x00000000 2392075 root 777 196608 2 dest 0x00000000 2457613 root 777 196608 2 dest 0x00000000 8126486 root 777 196608 2 dest ------ Semaphore Arrays -------- key semid owner perms nsems status ------ Message Queues -------- key msqid owner perms used-bytes messages- Now from within the same session, try to startup the database:
SQL> startup open ORA-03113: end-of-file on communication channel- At this point you should exit from SQL*Plus and examine the shared memory that has allocated from the startup command.
SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production OEMDB on dbautil: ipcs ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 458759 root 777 196608 2 dest 0x00000000 1212424 root 777 196608 2 dest 0x00000000 1277961 root 777 196608 2 dest 0x00000000 1409034 root 777 196608 2 dest 0x00000000 2392075 root 777 196608 2 dest 0x00000000 11468812 oracle 640 4194304 0 0x00000000 2457613 root 777 196608 2 dest 0x00000000 11501582 oracle 640 33554432 0 0x00000000 8126486 root 777 196608 2 dest ------ Semaphore Arrays -------- key semid owner perms nsems status ------ Message Queues -------- key msqid owner perms used-bytes messages- In order to startup the database without having to reboot the database server, simply remove the newly allocated shared memory. In this case, (SHMIDs = 11468812, 11501582 )
% ipcrm shm 11468812 11501582- Now try to startup the database:
OEMDB on dbautil: sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 25 17:39:30 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup open ORACLE instance started. Total System Global Area 252776588 bytes Fixed Size 450700 bytes Variable Size 218103808 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> exit
Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.