Troubleshooting Database Startup/Shutdown Problems (Doc ID 851057.1)

In this Document

 Purpose Troubleshooting Steps Issues Encountered During Database Shutdown Diagnostic Information to be Collected for Troubleshooting Issues During Shutdown Issues Encountered During Database Startup Diagnostic Information to be Collected For Troubleshooting Issues During Startup References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.0.4.1 to 11.2.0.3 [Release 8.0.4 to 11.2]
Information in this document applies to any platform.

PURPOSE

This troubleshooting guide for handling startup/shutdown issues.

The main purpose is to provide the Database Administrators an understanding of the issue and the diagnostic information to be collected before raising a Service Request with Oracle Support

TROUBLESHOOTING STEPS

Issues Encountered During Database Shutdown

Most common issue observed while bring down the database is Shutdown immediate hanging (or too slow). This can be due to various reasons such as :

- processes still continue to be connected to the database and do not terminate. 
- SMON is cleaning temp segments or performing delayed block cleanouts.
- Uncommitted transactions are being rolled back.

At any situation never attempt multiple shutdown abort/startups. This is going to make the situation worse. For further details on Shutdown process you can refer Note 1505155.1 - Master Note: Overview of Database Startup and Shutdown.

If a rollback is happening, this needs to be completed before shutting down the database.  PMON clears up the process state ie, the state objects, enqueues and latches, held by the failed process. And SMON rolls back the transaction. If we attempt to do a shutdown abort and then bring up the database, the same cleanup will then be carried out in the rollforward and rollback phases, which we will see in the next section. So, in any case we have to allow the background processes to complete the cleanup.

Refer: What To Do and Not To Do When 'shutdown immediate' Hangs (Doc ID 375935.1)

You can check the following before attempting a shutdown:

This shows operations taking longer than 6 seconds.
    

select count(*) from v$session_longops where time_remaining>0;

 This shows amount of undo that will need to be applied if you shutdown now.
  

 select sum(used_ublk) * <block size of undo ts> from v$transaction;

 

Diagnostic Information to be Collected for Troubleshooting Issues During Shutdown

The different scenarios and known issues are mentioned in the following document.

Master Note for Database Startup/Shutdown (Doc ID 1270450.1)

If the issue still exists, please gather the following information and raise the Service Request with Oracle Support.

1. Plan to shutdown again and gather some information. You can abort the current instance, bring it up using startup restrict and then issue shutdown immediate. Before issueing the second shutdown immediate collect following trace files / outputs.
   1. Check for any dead transactions that smon is looking to rollback.  
      
      Start Server Manager (or SQL*Plus for 8i or higher) 
       SQL> connect / as sysdba
       SQL> select * from x$ktuxe where ktuxecfl = 'DEAD';
       
   2. Set some events as follows:
      
       
      SQL> connect / as sysdba
        SQL> alter session set events '10046 trace name context forever,level 12';
        SQL> alter session set events '10400 trace name context forever, level 1';
        SQL> shutdown immediate;
       
      
        10046 turns on extended SQL_TRACE for the shutdown process.
        10400 dumps a systemstate every 5 minutes.
      
      Let the shutdown go for around 15 minutes and then send  the traces to Oracle Support. The trace files should show where the time is spent.
2. Upload the complete alert log file. Check for any tracefiles generated during this period. If present, please upload them also

Issues Encountered During Database Startup

Database Startup involves different phases like nomount,mount and open phase. In the nomount stage the parameter file (pfile or spfile) will be accessed and the instance will be started. This instance will not be associated to any database. While moving to the mount stage, the control files will be read and and the instance will be associated with the corresponding database. While moving to the open phase, the datafiles are opened and the rollforward happens. SMON initiates the rollback and then makes the database open to the users. This transaction recovery or the rollback phase continues even after the database is open. For further details on Startup process you can refer Note 1505155.1 - Master Note: Overview of Database Startup and Shutdown.

Depending on the stage various issues can be reported while starting up the database. Any issues with the pfile or spfile, issues in accessing memory, out of memory, issues in spawning background process etc can affect the nomount phase. Any issues while accessing the control files or corrupted control files can result in error during mount phase. Accessibility of the datafiles, dictionary corruption and most important, taking time for transaction recovery can be observed during the open phase. Some times database may hang if the transaction recovery takes long time, with the following message in the alert log file:

SMON: enabling tx recovery

 Internal errors (ORA-600/ORA-7445) can also be encountered during any of the above stages.

Diagnostic Information to be Collected For Troubleshooting Issues During Startup

You can refer the following document for the known issues reported while starting up the database:

Master Note for Database Startup/Shutdown (Doc ID 1270450.1)

If the above document did not help in resolving the issue, please gather the following information and raise a Service Request with Oracle Support:

1. Complete error message reported in the console while attempting startup
2. Complete alert log file and the relevant trace files.
3. OS error log (for Unix machines) or Event viewer (saved in .txt format) in case of a windows machine
4. Details of the OS resources available and the usage, in terms of memory, swap, CPU etc. If possible try to get OS Watcher output. Refer OS Watcher Black Box User Guide (Doc ID 301137.1)
5. Run HCVE to see if OS settings are set to at least the minimum (Doc ID 250262.1)
6. Use OS trace tools to trace the failing startup process. ie, strace for Linux, truss for Sun/AIX, and tusc for HP

 

truss -eafo /tmp/start_truss.out sqlplus /nolog
SQL> conn / as sysdba
SQL> startup
-- Wait 5-10 minutes then kill process and do shutdown immediate if necessary

OR

tusc -aef -o /tmp/tusc_startup.log -T "%H:%M:%S" sqlplus /nolog
SQL> conn / as sysdba
SQL> startup
-- Wait 5-10 minutes then kill process and do shutdown immediate if necessary

 

OR

 

strace -aeftT -o /tmp/strace_startup.txt  sqlplus /nolog
SQL> conn / as sysdba
SQL> startup
-- Wait 5-10 minutes then kill process and do shutdown immediate if necessary

 

7. If Startup is slow in open phase get 10046 level 12 trace along with Hanganalyze. 

SQL> conn / as sysdba
SQL> startup mount
SQL> alter session set events '10046 trace name context forever, level 12';
SQL> alter database open;
-- Wait 5-10 minutes then kill process and shutdown immediate 

Please collect the Hanganalyse report from another session to identify the blocking chains 

SQL> sqlplus " / as sysdba" 
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug hanganalyze 3
-- wait 90 seconds
SQL> oradebug hanganalyze 3
SQL> oradebug tracefile_name
SQL> exit

8. For issues where no relevant messages are reported in the alert log file

 

SQL> conn / as sysdba
SQL> startup nomount
SQL> alter session set events '10046 trace name context forever, level 12';
SQL> alter database mount;
SQL> alter database open;
-- Wait 5-10 minutes then kill process and shutdown immediate

   

REFERENCES

NOTE:249664.1 - Pfile vs SPfile
NOTE:269174.1 - Starting up the database is very slow in windows
NOTE:61333.1 - AUTOMATIC STARTUP AND SHUTDOWN OF ORACLE DATABASE INSTANCES
NOTE:760968.1 - Database Startup, Shutdown Or New Connections Hang With Truss Showing OS Failing Semtimedop Call With Err#11 EAGAIN
NOTE:416658.1 - Shutdown Immediate Hangs / Active Processes Prevent Shutdown
NOTE:91815.1 - The Annotated dbstart Script
NOTE:138129.1 - What Happens and What to Do when the SPFILE has been Manually Modified.
NOTE:15566.1 - TECH: Unix Semaphores and Shared Memory Explained
NOTE:279243.1 - Startup of Instance fails with ORA-00824 Error
NOTE:286388.1 - How to Configure Swap Space
NOTE:1076161.6 - Shutdown Normal or Shutdown Immediate Hangs. SMON disabling TX Recovery
NOTE:146577.1 - How to change static parameters through SPFILE parameter file
NOTE:144504.1 - Unable to startup database using DBA studio
NOTE:1020192.6 - Differences Between the Startup and Shutdown Procedures
NOTE:118570.1 - Diagnosing OPS Database Hanging Issues
NOTE:137483.1 - How to Modify the Content of an SPFILE Parameter File
NOTE:115753.1 - Resolving ORA-27123 Error
NOTE:118367.1 - UNIX: ORA-1990 at Startup DB After Creating Password File with Wrong Case
NOTE:148466.1 - Monitor Oracle Resource Consumption in UNIX
NOTE:560309.1 - Database Cannot Start Due to Lack of Memory
NOTE:160178.1 - How To Set EVENTS In The SPFILE
NOTE:162491.1 - Startup of an Oracle 9i, 10g, 11g Instance using SPFile or Init.ora Parameter File
NOTE:293698.1 - Troubleshooting SPFILEs and PFILEs 
NOTE:31084.1 - Startup / Shutdown of Unix Databases via Database Manager from Windows NT
NOTE:429390.1 - Database Startup Takes Longer time After Upgrade To 10.x
NOTE:50429.1 - How to Startup/Shutdown a Remote Database Through EM
NOTE:268884.1 - Shutdown Immediate Hangs
NOTE:579365.1 - Troubleshooting ORA-27300 ORA-27301 ORA-27302 Errors
NOTE:185703.1 - How to Avoid Common Flaws and Errors Using Passwordfile
NOTE:2064864.102 - TROUBLESHOOTING TNS-12500 ON MICROSOFT WINDOWS NT
NOTE:343031.1 - How to Deal with an ORA-01033 'Oracle startup or shutdown in progress' Error
NOTE:375935.1 - What To Do and Not To Do When 'shutdown immediate' Hangs
NOTE:414242.1 - Database Hangs Because SMON Is Taking 100% CPU Doing Transaction Recovery
NOTE:166601.1 - How to Migrate from a Pfile to an Spfile
NOTE:1375405.1 - Top 5 RAC Instance Crash Issues
NOTE:244139.1 - How to Start Database And Listener at System Reboot on Unix Systems
NOTE:6795133.8 - Bug 6795133 - Startup delayed by QMNC queries
NOTE:61621.1 - WINNT/WIN2000: Recreating Oracle Services and Instances from the Command Line
NOTE:61552.1 - Troubleshooting Oracle Database Hanging Issues for versions from 7 to 9--Exhaustive.
NOTE:867492.1 - WebLogic Server Support Pattern: How To Troubleshoot Too Many Open Files Problems
BUG:6795133 - QMNC DELAYS DB OPEN DUE TO DEF$ TABLE QUERIES
NOTE:1019623.102 - BACKGROUND PROCESS FAILS TO START
NOTE:153961.1 - Semaphores and Shared Memory - An Overview
NOTE:211424.1 - How to Configure RHAS 2.1 32-bit for Very Large Memory (VLM) with shmfs and bigpages
NOTE:225349.1 - Implementing Address Windowing Extensions (AWE) or VLM on 32-bit Windows Platforms
NOTE:169706.1 - Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)