There are a few steps you can take to identify and resolve the issue if you are having trouble shutting down an Oracle database:
1. Examine for ongoing sessions: Verify that there are no currently running sessions or transactions before shutting down the database. To find out if any sessions or transactions are currently active, you can query the v$session and v$transaction views.
2. Examine the sessions for hungs: Check for any hung sessions that may be preventing the database from shutting down if there are no active sessions or transactions. Any hung sessions can be ended with the ALTER SYSTEM KILL SESSION command.
3. Examine for pending transactions: Check for any open transactions that may be preventing the database from shutting down if there are no active or hung sessions. To locate any currently active transactions, you can make use of the SELECT * FROM V$TRANSACTION command.
4. Look over the alert log: Examine the Oracle alert log for any possible issues or error messages that are preventing the database from shutting down.
5. Instigate a shutdown: The SHUTDOWN ABORT command can be used to force the database to shut down if nothing else works. However, due to the possibility of data loss and database corruption, this should only be utilized as a last resort.
It is vital to take note of that closing down a Prophet data set requires legitimate preparation and coordination to guarantee that all exchanges are appropriately finished and information isn't lost. To ensure a successful database shutdown, it is recommended to seek assistance from an Oracle support professional or qualified database administrator.
#Important Cmd:
Shutdown Issue (Shut immediate taking time troubleshoot)
1. Before shutting down database, flush relevant information using command -
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
2. Get the information of database status before shutdown -
SELECT * FROM V$TRANSACTION;
SELECT * FROM V$FAST_START_TRANSACTIONS;
SELECT COUNT(1) FROM SMON_SCN_TIME;
SELECT SID, SERIAL#, SQL_ID, LAST_CALL_ET, USERNAME, STATUS FROM V$SESSION;
--- Then do Disable archive processes, switch log files, checkpoint
3. Enable 10046 trace for database shutdown operation.
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
--- Then shutdown the database in immediate mode.
ALTER SESSION SET EVENTS '10046 trace name context off'; --- If shutdown happens properly.
4. Because of delay, if you have to abort the database, immediately after startup, get the output of -
SELECT * FROM V$FAST_START_TRANSACTIONS;
