The normal database shuts down hang problem

tonight the office building outage maintenance, need to shut down the server early, in order to prevent abnormal shutdown caused by various problems, There is a test library, using shutdown normal to stop the library, the result is very common hang.

Sequence of operations:

? 1. Shutdown normal, then closes the current Sqlplus window.
? From the alert log:
?Mon June 16:50:22
shutting down instance (normal)
stopping background process SMCO
shutting down Instance:further logons disabled
Here comes the principle of shutdown normal, which is quoted later.
?

? 2. Log in again at this time, Sqlplus/as sysdba ?, the Execute startup or shutdown immediate command fails to prompt,

Sql*plus:release 11.2.0.1.0 Production on Mon June 17:03:06 2015Copyright (c) 1982, for 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 was not permittedmon June 22 16:50:24 20 15Stopping background Process cjq0stopping Background process qmncstopping background process mmnlstopping Background Pro Cess mmonlicense High Water mark = 125

ORA-01090 indicates that a close operation is being performed and that no other connected operation is allowed.

3. In fact, this involves the principle of normal shutdown, he needs to wait for all connected users to disconnect, in other words, if there are still users connected to the library, shutdown operation has been waiting. This is the most complete way to shut down, but at the same time is the most variable, because you may not know when other users are interrupted.

First try to find out all connected users, using kill-9 to kill the process directly.

You can use PS-EF to find all processes (Local=no), 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 $} ', then kill-9 process number or ps-ef|grep ora|grep-v grep|grep -V ora_|grep Local=no|awk ' {print $} ' |xargs Kill

Look at the alert log:

Mon June 22 16:55:26 2015
Active process 27446 user ' oracle11g ' program ' [email protected] '
Active process 27402 user ' oracle11g ' program ' [email protected] '
Active process 27555 user ' oracle11g ' program ' [email protected] '
Active process 11697 user ' oracle11g ' program ' [email protected] '
Active process 14942 user ' oracle11g ' program ' [email protected] '
Active process 27559 user ' oracle11g ' program ' [email protected] '
Active process 27513 user ' oracle11g ' program ' [email protected] '
Active process 26911 user ' oracle11g ' program ' [email protected] '
Active process 31993 user ' oracle11g ' program ' [email protected] '
Active process 30810 user ' oracle11g ' program ' [email protected] '
Active process 27557 user ' oracle11g ' program ' [email protected] '
Active process 11684 user ' oracle11g ' program ' [email protected] '
Active process 11666 user ' oracle11g ' program ' [email protected] '
Active process 27510 user ' oracle11g ' program ' [email protected] '
Active process 11688 user ' oracle11g ' program ' [email protected] '
Shutdown:waiting for logins to complete.
Mon June 22 17:01:29 2015
All dispatchers and shared servers shutdown

Is prompted for all dispatcher and shared services to shut down, but Sqlplus is still the top hint after logging in.

4. Try shutting down the listening service, Lsnrctl stop.

The problem remains.

5. Re-login to execute shutdown abort, Force shutdown.

Look at the alert log:

USER (ospid:28558): terminating the instance

Instance terminated by USER, PID = 28558

It seems that the instance was closed.

Re-execute Sqlplus/as sysdba

Sql*plus:release 11.2.0.1.0 Production on Mon June 17:43:25 2015Copyright (c) 1982, for 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< c2/>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, normal startup, normal shutdown.

From the alert log,

Mon June 17:46:01 2015Starting ORACLE instance (normal) license_max_session = 0license_sessions_warning = 0Picked latch-f REE 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 = Memory_target = 3152M control_files = "/oracle/ora11gr2/oradata/dcsopen/control01.ctl" control_files = "/oracle/ora11gr2/oradata/dcsopen/con Trol02.ctl "Db_block_size = 8192 compatible =" 11.2.0.0.0 "undo_tablespace =" Undotbs 1 "Remote_Login_passwordfile= "EXCLUSIVE" Db_domain = "dispatchers =" (protocol=tcp) (Service=dcsope                  NXDB) "audit_file_dest ="/oracle/ora11gr2/admin/dcsopen/adump "Audit_trail =" DB "db_name = "Dcsopen" open_cursors = Diagnostic_dest = "/ORACLE/ORA11GR2" Mon June 22 17:46:03 2 015PMON started with pid=2, OS id=30699 Mon June, 17:46:03 2015VKTM started with pid=3, OS id=30701 at elevated priorityv KTM running at (ten) Millisec precision with DBRM quantum (+) Msmon June, 17:46:03 2015gen0 started with pid=4, OS id=3070 5 Mon June 17:46:03 2015DIAG started with pid=5, OS id=30707 Mon June 17:46:03 2015DBRM started with pid=6, OS id=3070 9 Mon June 17:46:03 2015psp0 started with pid=7, OS id=30711 Mon June-17:46:03 2015dia0 started with pid=8, OS id=3071 3 Mon June 17:46:03 2015MMAN started with pid=9, OS id=30715 Mon June, 17:46:03 2015dbw0 started with pid=10, OS id=307 Mon June 22 17:46:03 2015LGWR started with pid=11, OS id=30721 mon June 17:46:03 2015CKPT started with pid=12, OS id=30723 mon June 22 17:46:03 2015SMON started with pid=13, OS id=30725 mon June, 17:46:03 2015RECO started with pid=14, OS id=30727 mon June 22 17:46: 2015MMON started with pid=15, OS id=30729 starting up 1 dispatcher (s) for network address ' (Address= (partial=yes) (PROTO col=tcp)) ' ... Mon June 17:46:03 2015MMNL started with pid=16, OS id=30731 starting up 1 shared server (s) ... Oracle_base from environment =/oracle/ora11gr2mon June 22 17:46:04 2015ALTER DATABASE MOUNTSuccessful mount of Redo thread 1, with Mount ID 2809595100Database mounted in Exclusive modelost write protection disable Dcompleted:alter DATABASE Mountmon June 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.LOGSUCCESSF UL open of Redo Thread 1MTTR advisory is disabled because fast_start_mttr_target are not setsmon:enabling cache Recoverysu ccessfully 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 fo und) starting background process Qmncmon June 17:46:09 2015QMNC started with pid=20, OS id=30789 completed:alter databas E openstarting Background Process Cjq0mon June 17:46:11 2015cjq0 started with pid=22, OS id=30806 Mon June 22 17:46:18 20 15shutting down instance (normal)Shutting down Instance:further logons disabledstopping background process qmncstopping background process cjq0stopping BA Ckground process mmnlstopping Background process mmonlicense High water mark = 5All Dispatchers and shared servers Shutdow NALTER DATABASE CLOSE NORMALMon June 17:46:22 2015smon:disabling TX recoverysmon:disabling Cache Recoverymon June 17:46:22 2015Shutting down arc Hive processesarchiving is disabledarchive process shutdown avoided:0 activethread 1 closed at log sequence 1279Successfu L close of Redo thread 1completed:alter DATABASE close NORMALALTER DATABASE DismountCompleted:alter DATABASE dismountarch:archival disabled due to shutdown:1090shutting down archive processesarchiving are Disabledarchive process shutdown avoided:0 Activemon June 17:46:23 2015Stopping Background process VKTM:ARCH:Archiva L disabled due to shutdown:1090shutting-archive processesarchiving is disabledarchive process shutdown avoided:0 AC Tivemon June 22 17:46:25 2015Instance shutdown Complete

Draw on "Concept", some knowledge points:

1. If a user tries to access a database that is shutting down, they get an error message: Ora-01090:shutdown in progress-connection are not permitted.

2. To close the database, you must use the Sysoper or SYSDBA role.

3. Close the database, there is a time-out, if the user has not interrupted the connection, or the transaction is not completed, more than an hour, then the shutdown command will be canceled, prompting the error: Ora-01013:user requested cancel of the current operation.

4. Several parameters for closing the library,

Shutdown Normal:

The default shutdown parameter requires two conditions:

(1) The new connection is not allowed after the statement is executed.

(2) The database waits for all connected users to disconnect before the database is shut down.

No instance recovery is required for the next boot.

Shutdown Immediate:

Usage scenarios:

(1) Initialize an automatic, unattended backup.

(2) The power will 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.

Conditions:

(1) New connections are not allowed and new transactions are not allowed.

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

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

No instance recovery is required for the next boot.

Shutdown Transactional:

A scenario that applies to scheduled outages, allowing the active transaction to be processed before stopping the instance.

Conditions:

(1) New connections are not allowed and new transactions are not allowed.

(2) When all transactions are completed, all connections to the library will be interrupted.

(3) At this point in time, closing the instance is like performing a shutdown immediate.

No instance recovery is required for the next boot.

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

Shutdown Abort:

Applicable scenarios:

The database or app does not work correctly and no other type of shutdown is in progress.

(1) You need to close the database immediately (for example, the power will be turned off after one minute).

(2) A problem was encountered while launching the instance.

Conditions:

(1) New connections are not allowed and new transactions are not allowed.

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

(3) Uncommitted transactions are not rolled back.

(4) Oracle does not wait for clients that are staying connected to log out. The database implicitly interrupts all connections.

An instance recovery is required for the next boot.

Summarize:

The above four parameters will be suitable for different scenarios, simply speaking,shutdown Normal is the default mode of closure, the most complete closure method, the disadvantage is to passively wait for all transactions to complete, all users logged out. shutdown immediate The shutdown time is faster as long as there are no longer transactions that need to be rolled back. shutdown transactional will ensure that the transaction is completed as fully as possible. No instance recovery is required for the first three types. shutdown Abort is the most violent shutdown, with the fastest shutdown time, but at the cost of initiating an instance recovery because there is no rollback of uncommitted transactions at shutdown.

The normal database shuts down hang problem