Normal database disabling hang

Normal database disabling hang

To avoid problems caused by abnormal shutdown, we have a test database that uses shutdown normal to stop the database. The result is that the hang is very common.

 

Operation Sequence:

? 1. shutdown normal, and then close the current sqlplus window.
? From the alert Log:
? Mon Jun 22 16:50:22 2015
Shutting down instance (normal)
Stopping background process SMCO
Shutting down instance: further logons disabled
The shutdown normal principle is involved here and will be quoted later.
?

? 2. Log On again, sqlplus/as sysdba ?, An error occurred while executing the startup or shutdown immediate command,

 

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 22 17:03:06 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected.SQL> startupORA-01012: not logged onSQL> shutdown immediateORA-24324: service handle not initializedORA-24323: value not allowedORA-01090: shutdown in progress - connection is not permittedMon Jun 22 16:50:24 2015Stopping background process CJQ0Stopping background process QMNCStopping background process MMNLStopping background process MMONLicense high water mark = 125

 

 

The ORA-01090 prompts that the close operation is being performed and other connection operations are not allowed.

 

 

3. In fact, this involves the principle of normal shutdown. He needs to wait for all connected users to interrupt the connection. In other words, if there are still users connected to the database, the shutdown operation will always wait. This is the most completely closed method, but it is also the most variable, because you may not know when other users will interrupt.

First, try to find all connected users and kill the process with kill-9.

You can use ps-ef to find all (LOCAL = NO) processes. LOCAL = NO indicates that the connection is not LOCAL, but remote.

 

Ps-ef | grep ora | grep-v grep | grep-v ora _ | grep LOCAL = NO | awk '{print $2 }', then kill-9 process number or ps-ef | grep ora | grep-v grep | grep-v ora _ | grep LOCAL = NO | awk '{print $2}' | xargs kill

From the alert Log:

 

Mon Jun 22 16:55:26 2015
Active process 27446 user 'oracle11g' program 'oracle @ dcsopennode1'
Active process 27402 user 'oracle11g' program 'oracle @ dcsopennode1'
Active process 27555 user 'oracle11g' program 'oracle @ dcsopennode1'
Active process 11697 user 'oracle11g' program 'oracle @ dcsopennode1'
Active process 14942 user 'oracle11g' program 'oracle @ dcsopennode1'
Active process 27559 user 'oracle11g' program 'oracle @ dcsopennode1'
Active process 27513 user 'oracle11g' program 'oracle @ dcsopennode1'
Active process 26911 user 'oracle11g' program 'oracle @ dcsopennode1'
Active process 31993 user 'oracle11g' program 'oracle @ dcsopennode1'
Active process 30810 user 'oracle11g' program 'oracle @ dcsopennode1'
Active process 27557 user 'oracle11g' program 'oracle @ dcsopennode1'
Active process 11684 user 'oracle11g' program 'oracle @ dcsopennode1'
Active process 11666 user 'oracle11g' program 'oracle @ dcsopennode1'
Active process 27510 user 'oracle11g' program 'oracle @ dcsopennode1'
Active process 11688 user 'oracle11g' program 'oracle @ dcsopennode1'
SHUTDOWN: waiting for logins to complete.
Mon Jun 22 17:01:29 2015
All dispatchers and shared servers shutdown

It prompts that all dispatcher and sharing services are disabled, but the above prompt is still displayed after sqlplus logon.

 

4. Try to disable the listening service and lsnrctl stop.

The problem persists.

 

5. Log On again and execute shutdown abort to force shutdown.

From the alert Log:

USER (ospid: 28558): terminating the instance

Instance terminated by USER, pid = 28558

It seems that the instance is disabled.

Execute sqlplus/as sysdba again

 

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 22 17:43:25 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to an idle instance.

 

Execute startup-shutdown normal again,

 

SQL> startupORACLE instance started.Total System Global Area 3290345472 bytesFixed Size                  2217832 bytesVariable Size            2499807384 bytesDatabase Buffers          771751936 bytesRedo Buffers               16568320 bytesDatabase mounted.Database opened.SQL> shutdown normalDatabase closed.Database dismounted.ORACLE instance shut down.

Because there are no connected users now, they can be started normally and disabled normally.

 

From the alert Log,

 

Mon Jun 22 17:46:01 2015Starting ORACLE instance (normal)LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0Picked latch-free SCN scheme 3Using LOG_ARCHIVE_DEST_1 parameter default value as /oracle/ora11gR2/product/11.2.0/dbhome_1/dbs/archAutotune of undo retention is turned on. IMODE=BRILAT =27LICENSE_MAX_USERS = 0SYS auditing is disabledStarting up:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options.Using parameter settings in server-side spfile /oracle/ora11gR2/product/11.2.0/dbhome_1/dbs/spfiledcsopen.oraSystem parameters with non-default values:  processes                = 150  memory_target            = 3152M  control_files            = "/oracle/ora11gR2/oradata/dcsopen/control01.ctl"  control_files            = "/oracle/ora11gR2/oradata/dcsopen/control02.ctl"  db_block_size            = 8192  compatible               = "11.2.0.0.0"  undo_tablespace          = "UNDOTBS1"  remote_login_passwordfile= "EXCLUSIVE"  db_domain                = ""  dispatchers              = "(PROTOCOL=TCP) (SERVICE=dcsopenXDB)"  audit_file_dest          = "/oracle/ora11gR2/admin/dcsopen/adump"  audit_trail              = "DB"  db_name                  = "dcsopen"  open_cursors             = 300  diagnostic_dest          = "/oracle/ora11gR2"Mon Jun 22 17:46:03 2015PMON started with pid=2, OS id=30699 Mon Jun 22 17:46:03 2015VKTM started with pid=3, OS id=30701 at elevated priorityVKTM running at (10)millisec precision with DBRM quantum (100)msMon Jun 22 17:46:03 2015GEN0 started with pid=4, OS id=30705 Mon Jun 22 17:46:03 2015DIAG started with pid=5, OS id=30707 Mon Jun 22 17:46:03 2015DBRM started with pid=6, OS id=30709 Mon Jun 22 17:46:03 2015PSP0 started with pid=7, OS id=30711 Mon Jun 22 17:46:03 2015DIA0 started with pid=8, OS id=30713 Mon Jun 22 17:46:03 2015MMAN started with pid=9, OS id=30715 Mon Jun 22 17:46:03 2015DBW0 started with pid=10, OS id=30717 Mon Jun 22 17:46:03 2015LGWR started with pid=11, OS id=30721 Mon Jun 22 17:46:03 2015CKPT started with pid=12, OS id=30723 Mon Jun 22 17:46:03 2015SMON started with pid=13, OS id=30725 Mon Jun 22 17:46:03 2015RECO started with pid=14, OS id=30727 Mon Jun 22 17:46:03 2015MMON started with pid=15, OS id=30729 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...Mon Jun 22 17:46:03 2015MMNL started with pid=16, OS id=30731 starting up 1 shared server(s) ...ORACLE_BASE from environment = /oracle/ora11gR2Mon Jun 22 17:46:04 2015ALTER DATABASE   MOUNTSuccessful mount of redo thread 1, with mount id 2809595100Database mounted in Exclusive ModeLost write protection disabledCompleted: ALTER DATABASE   MOUNTMon Jun 22 17:46:08 2015ALTER DATABASE OPENThread 1 opened at log sequence 1279  Current log# 1 seq# 1279 mem# 0: /oracle/ora11gR2/oradata/dcsopen/redo01.logSuccessful open of redo thread 1MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setSMON: enabling cache recoverySuccessfully onlined Undo Tablespace 2.Verifying file header compatibility for 11g tablespace encryption..Verifying 11g file header compatibility for tablespace encryption completedSMON: enabling tx recoveryDatabase Characterset is ZHS16GBKNo Resource Manager plan activereplication_dependency_tracking turned off (no async multimaster replication found)Starting background process QMNCMon Jun 22 17:46:09 2015QMNC started with pid=20, OS id=30789 Completed: ALTER DATABASE OPENStarting background process CJQ0Mon Jun 22 17:46:11 2015CJQ0 started with pid=22, OS id=30806 Mon Jun 22 17:46:18 2015Shutting down instance (normal)Shutting down instance: further logons disabledStopping background process QMNCStopping background process CJQ0Stopping background process MMNLStopping background process MMONLicense high water mark = 5All dispatchers and shared servers shutdownALTER DATABASE CLOSE NORMALMon Jun 22 17:46:22 2015SMON: disabling tx recoverySMON: disabling cache recoveryMon Jun 22 17:46:22 2015Shutting down archive processesArchiving is disabledArchive process shutdown avoided: 0 activeThread 1 closed at log sequence 1279Successful close of redo thread 1Completed: ALTER DATABASE CLOSE NORMALALTER DATABASE DISMOUNTCompleted: ALTER DATABASE DISMOUNTARCH: Archival disabled due to shutdown: 1090Shutting down archive processesArchiving is disabledArchive process shutdown avoided: 0 activeMon Jun 22 17:46:23 2015Stopping background process VKTM: ARCH: Archival disabled due to shutdown: 1090Shutting down archive processesArchiving is disabledArchive process shutdown avoided: 0 activeMon Jun 22 17:46:25 2015Instance shutdown complete

 

 

Some knowledge points from Concept:

1. If the user tries to access a database that is being closed, the error message is: ORA-01090: shutdown in progress-connection is not permitted.

2. Shut down the database. You must use the role of SYSOPER or SYSDBA.

3. shut down the database, there is a timeout, if the user has not interrupted the connection, or the transaction is not completed, more than one hour, the shutdown command will be canceled, the error message: ORA-01013: user requested cancel of current operation.

4. Several Parameters for shutting down the database,

Shutdown normal:

The default disable parameter requires two conditions:

(1) After the statement is executed, new connections are not allowed.

(2) Before the database is closed, the database waits for all connected users to interrupt the connection.

The instance does not need to be restored at the next startup.

Shutdown immediate:

Use Cases:

(1) Initialize an automatic, unattended backup.

(2) power is about to be cut off immediately.

(3) The database or application is not working properly. You cannot immediately contact the user to log out or they cannot log out.

Condition:

(1) New connections and transactions are not allowed.

(2) Any uncommitted transactions will be rolled back (if a long transaction is not committed at this time, it will not be quickly closed like the closed name immediate ).

(3) do not wait for the connected user to log out. The database implicitly rolls back the active transaction and interrupts the connection to the user.

The instance does not need to be restored at the next startup.

Shutdown transactional:

It is applicable to scenarios where an instance is scheduled to be stopped and the instance is stopped after the active transaction processing is completed.

Condition:

(1) New connections and transactions are not allowed.

(2) After all transactions are completed, all connections to the database will be interrupted.

(3) at this point in time, shutting down an instance is like executing shutdown immediate.

The instance does not need to be restored at the next startup.

The transactional parameter mainly prevents users from losing transactions and does not require all users to log out.

Shutdown abort:

Applicable scenarios:

The database or application cannot work normally and no other types of close operations are in progress.

(1) The database needs to be shut down immediately (for example, the power will be shut down one minute later ).

(2) An error occurred while starting the instance.

Condition:

(1) New connections and transactions are not allowed.

(2) Client SQL statements being processed by Oracle will be immediately interrupted.

(3) uncommitted transactions will not be rolled back.

(4) Oracle will not wait for the client that is maintaining the connection to log out. The database implicitly interrupts all connections.

The instance must be restored at the next startup.

Summary:

The above four parameters are suitable for different scenarios. Simply put, shutdown normal is the default shutdown method and the most complete shutdown method. The disadvantage is that you need to wait passively for all transactions to complete, all users log out. Shutdown immediate as long as there are no long transactions that need to be rolled back, the shutdown time will be faster. Shutdown transactional guarantees the transaction fulfillment to the maximum extent. No instance recovery is required for the first three methods. Shutdown abort is the most violent shutdown, with the fastest shutdown time, but the cost is that the instance needs to be restored when it is started, because uncommitted transactions are not rolled back when it is closed.