-

How to shutdown a Single Instance Database in Oracle 19c

Overview

Shutdown is an operation performed when a command is issued by the Administrator to bring down the Database or when it receives a signal to bring down the Database incase of a failure/malfunction or due to SHUTDOWN ABORT command execution. The options used by the Administrator to bring down the Database is called as Shutdown Modes and the Database Server follows a sequence to shutdown a Database . Let's check how does that happen.

Concepts

The following are the shutdown Modes.

Shutdown Modes

A database administrator with SYSDBA or SYSOPER privileges can shut down the database using the SQL*Plus SHUTDOWN command or Enterprise Manager. The SHUTDOWN command has the following options that determine the shutdown behavior.



Now let's look into each SHUTDOWN option in detail.


SHUTDOWN ABORT 

This mode is used for emergency situations, such as when shutdown with "IMMEDIATE" option doesn't work. This mode of shutdown is the fastest. However, a subsequent open of this database may take substantially longer because instance recovery must be performed to make the data files consistent. SHUTDOWN ABORT does not checkpoint the open data files, instance recovery is necessary before the database can reopen. The other shutdown modes do not require instance recovery before the database can reopen.

SHUTDOWN IMMEDIATE 

This mode is typically the fastest next to SHUTDOWN ABORT. Oracle Database terminates any executing SQL statements and disconnects users. Active transactions are terminated and uncommitted changes are rolled back.

SHUTDOWN TRANSACTIONAL 

This mode prevents users from starting new transactions, but waits for all current transactions to complete before shutting down. This mode can take a significant amount of time depending on the nature of the current transactions. 

SHUTDOWN NORMAL 

This is the default mode of shutdown. The database waits for all connected users to disconnect before shutting down.
The Database shutdown happens in a Normal manner or in an Abnormal Manner.

The following figure shows the sequence of a consistent Shutdown.



Normal Database Shutdown  

When a database is closed as part of a SHUTDOWN with any option other than ABORT, Oracle Database writes the dirty buffer(modified data) in the SGA to the data files and Redo entries in the log buffer to the online redo log files. After this operation, the database closes online data files and online redo log files. Any offline data files of offline tablespaces have been closed already. When the database reopens, any tablespace that was offline remains offline. At this stage, the database is closed and inaccessible for normal operations. The control files remain open after a database is closed.

After the database is closed, Oracle Database unmounts the database to disassociate it from the instance. After a database is unmounted, Oracle Database closes the control files of the database. At this point, the database instance remains in memory. 

The last step involved in a database shutdown operation is to shutdown the instance. When the database instance shuts down, the SGA is released and the background processes are terminated.

Abnormal Database Shutdown 

If a SHUTDOWN ABORT or abnormal termination occurs, then the instance of an open database closes and shuts down the database immediately. In an abnormal shutdown, Oracle Database does not write dirty buffers and redo entries to the datafiles and online redo log files. The database instance  requires an instance recovery when the database is opened very next time, which will be performed by the Oracle Database automatically. SMON background process is responsible for this operation.

After the database is closed, Oracle Database unmounts the database to disassociate it from the instance. After a database is unmounted, Oracle Database closes the control files of the database. At this point, the database instance remains in memory. 

The last step involved in a database shutdown operation is to shutdown the instance. When the database instance shuts down, the SGA is released and the background processes are terminated.


Practical Demonstration

1)  SHUTDOWN NORMAL

Let's try to shutdown the database using SHUTDOWN NORMAL command.

To shutdown a database, set the environment variables, login to SQL*Plus with SYSDBA or SYSOPER privilege.

export ORACLE_HOME=/u01/app/oracle/product/19c/db

export ORACLE_SID=ORADEV

export PATH=$ORACLE_HOME/bin:$PATH

Tip#1 :- It is always a best practice to view the alert log of instance in another session, to understand what is happening in the background, as well as errors and warnings that helps in troubleshooting a problem.

Verify  the name of the Database which needs to be brought down.

SQL> show parameter db_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------------

db_name                              string      oradev

I have a user connected to the Database.

SQL> conn oradevuser/oradevuser

Connected.

SQL> show user

USER is "ORADEVUSER"

Let's ISSUE the command SHUTDOWN NORMAL

SQL> shutdown normal;

From the Alert Log we can see that the option used to shutdown the Database is "Normal". Hence it is waiting for the user to disconnect their session.

Shutting down ORACLE instance (normal) (OS id: 17575)

2023-10-12T14:45:33.890232+00:00

Shutdown is initiated by sqlplus@oradevserver (TNS V1-V.

2023-10-12T14:45:36.639475+00:00

Stopping background process SMCO

Shutting down instance: further logons disabled

2023-10-12T14:45:36.975817+00:00

Stopping background process CJQ0

Stopping background process MMNL

2023-10-12T14:45:37.016289+00:00

Process termination requested for pid 15883 [source = rdbms], [info = 2] [request issued by pid: 15877, uid: 502]

Active process 16600 user 'oracle' program 'oracle@oradevserver (TNS V1-V)', waiting for 'SQL*Net message from client'

SHUTDOWN: waiting for logins to complete.

I am disconnecting the user session to check if the Database shuts down. I disconnected the user's session, and the shutdown operation is successful.

ALTER DATABASE CLOSE NORMAL

2023-10-12T14:51:23.709564+00:00

Stopping Emon pool

Stopping background process AQPC

2023-10-12T14:51:24.765515+00:00

IM on ADG: Start of Empty Journal 

IM on ADG: End of Empty Journal 

Stopping Emon pool

Closing sequence subsystem (2197549537874).

Stopping change tracking

2023-10-12T14:51:25.182951+00:00

Shutting down archive processes

2023-10-12T14:51:25.184724+00:00

TT00 (PID:15310): Gap Manager exiting

Archiving is disabled

2023-10-12T14:51:25.291783+00:00

Thread 1 closed at log sequence 2

Successful close of redo thread 1

Completed: ALTER DATABASE CLOSE NORMAL

ALTER DATABASE DISMOUNT

Shutting down archive processes

Archiving is disabled

Completed: ALTER DATABASE DISMOUNT

2023-10-12T14:51:26.506849+00:00

.... (PID:17575): Archival disabled due to shutdown: 1090

Shutting down archive processes

Archiving is disabled

2023-10-12T14:51:27.512214+00:00

JIT: pid 17575 requesting stop

.... (PID:17575): Archival disabled due to shutdown: 1090

Shutting down archive processes

Archiving is disabled

JIT: pid 17575 requesting stop

2023-10-12T14:51:27.520671+00:00

Stopping background process VKTM

2023-10-12T14:51:32.898974+00:00

freeing rdom 0

freeing the fusion rht of pdb 0

2023-10-12T14:51:50.492443+00:00

Instance shutdown complete (OS id: 17575)

2)  SHUTDOWN IMMEDIATE

I have a user connected to the database.

SQL> conn oradevuser/oradevuser

Connected.

SQL> show user

USER is "ORADEVUSER"

Let's issue a shutdown command with the option "IMMEDIATE" and see what happens.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

As expected the SHUTDOWN operation did not wait for the user to disconnect, and the shutdown was successful as we can see in the below Alert Log section of this operation

Shutting down ORACLE instance (immediate) (OS id: 26628)

2023-10-12T15:02:55.382473+00:00

Shutdown is initiated by sqlplus@oradevserver (TNS V1-V.

Stopping background process SMCO

Shutting down instance: further logons disabled

2023-10-12T15:02:55.745228+00:00

Stopping background process CJQ0

Stopping background process MMNL

2023-10-12T15:02:55.787095+00:00

Process termination requested for pid 27284 [source = rdbms], [info = 2] [request issued by pid: 27278, uid: 502]

Stopping background process MMON

2023-10-12T15:02:57.893472+00:00

License high water mark = 5

2023-10-12T15:02:57.968163+00:00

Dispatchers and shared servers shutdown

Data Pump shutdown on PDB: 0 in progress

2023-10-12T15:02:57.974480+00:00

Process termination requested for pid 29001 [source = rdbms], [info = 2] [request issued by pid: 26628, uid: 502]

2023-10-12T15:02:59.988045+00:00

ALTER DATABASE CLOSE NORMAL

2023-10-12T15:03:00.029012+00:00

Stopping Emon pool

2023-10-12T15:03:01.032269+00:00

IM on ADG: Start of Empty Journal 

IM on ADG: End of Empty Journal 

Stopping Emon pool

Closing sequence subsystem (2198245798838).

Stopping change tracking

2023-10-12T15:03:01.506252+00:00

Shutting down archive processes

2023-10-12T15:03:01.508299+00:00

TT00 (PID:26641): Gap Manager exiting

Archiving is disabled

2023-10-12T15:03:01.612067+00:00

Thread 1 closed at log sequence 2

Successful close of redo thread 1

Completed: ALTER DATABASE CLOSE NORMAL

ALTER DATABASE DISMOUNT

Shutting down archive processes

Archiving is disabled

Completed: ALTER DATABASE DISMOUNT

2023-10-12T15:03:02.883102+00:00

.... (PID:26628): Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

2023-10-12T15:03:03.888795+00:00

JIT: pid 26628 requesting stop

.... (PID:26628): Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

JIT: pid 26628 requesting stop

2023-10-12T15:03:03.942920+00:00

Stopping background process VKTM

2023-10-12T15:03:09.036053+00:00

freeing rdom 0

freeing the fusion rht of pdb 0

2023-10-12T15:03:28.196073+00:00

Instance shutdown complete (OS id: 26628)

Note:- This is the most preferred way to shutdown a Database.

3) SHUTDOWN TRANSACTIONAL 

To test this scenario, I have a user connected to the Database. 

SQL> conn oradevuser/oradevuser

Connected.

Let's try to create a table and insert some rows.

SQL> create table SalesAdmin(Name Varchar(20),Age Number(2));

Table created.

SQL> insert into SalesAdmin values('James',25);

1 row created.

Let's issue SHUTDOWN TRANSACTIONAL now.

SQL> shutdown transactional;

The command is waiting for the transaction to complete. As I have not issued a "COMMIT", the transaction is still incomplete.

Shutting down ORACLE instance (transactional) (OS id: 4834)
2023-10-12T15:19:59.409837+00:00
Shutdown is initiated by sqlplus@oradevserver (TNS V1-V.
2023-10-12T15:20:00.543126+00:00
Stopping background process SMCO
Shutting down instance: further logons disabled
2023-10-12T15:20:02.482444+00:00
Stopping background process CJQ0
2023-10-12T15:20:02.524566+00:00
Stopping background process MMNL
Stopping background process MMON

Now let me commit the transaction from the user's session and see how Oracle Database responds to this.

SQL> commit;
Commit complete.

The shutdown is now complete.

SQL> shutdown transactional;
Database closed.
Database dismounted.
ORACLE instance shut down.

From the Alert Log section of this operation we can clearly see that the shutdown operation is now complete upon the completion of the transaction.

2023-10-12T15:23:27.714838+00:00

All transactions complete. Performing immediate shutdown

License high water mark = 7

2023-10-12T15:23:28.788893+00:00

Dispatchers and shared servers shutdown

Data Pump shutdown on PDB: 0 in progress

2023-10-12T15:23:28.793882+00:00

Process termination requested for pid 10316 [source = rdbms], [info = 2] [request issued by pid: 4834, uid: 502]

2023-10-12T15:23:30.798805+00:00

ALTER DATABASE CLOSE NORMAL

2023-10-12T15:23:30.868313+00:00

Stopping Emon pool

2023-10-12T15:23:31.872050+00:00

IM on ADG: Start of Empty Journal 

IM on ADG: End of Empty Journal 

Stopping Emon pool

Closing sequence subsystem (2199476639132).

Stopping change tracking

2023-10-12T15:23:32.273237+00:00

Shutting down archive processes

2023-10-12T15:23:32.274880+00:00

TT00 (PID:4847): Gap Manager exiting

Archiving is disabled

2023-10-12T15:23:32.378864+00:00

Thread 1 closed at log sequence 2

Successful close of redo thread 1

Completed: ALTER DATABASE CLOSE NORMAL

ALTER DATABASE DISMOUNT

Shutting down archive processes

Archiving is disabled

Completed: ALTER DATABASE DISMOUNT

2023-10-12T15:23:33.658119+00:00

.... (PID:4834): Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

2023-10-12T15:23:34.664160+00:00

JIT: pid 4834 requesting stop

.... (PID:4834): Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

JIT: pid 4834 requesting stop

2023-10-12T15:23:34.756986+00:00

Stopping background process VKTM

2023-10-12T15:23:40.014552+00:00

freeing rdom 0

freeing the fusion rht of pdb 0

2023-10-12T15:23:58.677330+00:00

Instance shutdown complete (OS id: 4834)

4) SHUTDOWN ABORT 

In unusual circumstances, shutdown of a database instance may not occur cleanly. Memory structures may not be removed from memory or one of the background processes may not be terminated. When remnants of a previous instance exist, a subsequent instance startup may fail. In such situations, you can force the new instance to start by removing the remnants of the previous instance and then starting a new instance, or by issuing a SHUTDOWN ABORT statement. In some cases, process cleanup itself can encounter errors, which can result in the termination of process monitor (PMON) or the instance. The dynamic initialization parameter INSTANCE_ABORT_DELAY_TIME specifies how many seconds to delay an internally generated instance failure. This delay gives you a chance to respond. The database writes a message to the alert log when the delayed termination is initiated. In some circumstances, by allowing certain database resources to be quarantined, the instance can avoid termination.

Note:-  Use the option "ABORT" with care. As it recommended to use it only when the shutdown immediate doesn't serve the purpose.

To Test the scenario, I have an ACTIVE user connection in one of the putty sessions.

SQL> conn oradevuser/oradevuser
Connected.
SQL> show user;
USER is "ORADEVUSER"

I have inserted a row into a table and did not commit the transaction yet.

SQL> insert into SalesAdmin values('Peter',29);

1 row created.

Now let's issue the SHUTDOWN ABORT command.

SQL> shutdown abort;           
ORACLE instance shut down.

The shutdown was completed.

Shutting down ORACLE instance (abort) (OS id: 26095)
2023-10-12T15:39:05.344368+00:00
Shutdown is initiated by sqlplus@oradevserver (TNS V1-V.
License high water mark = 6
USER (ospid: 26095): terminating the instance
2023-10-12T15:39:05.695637+00:00
ORA-1092 : opitsk aborting process
2023-10-12T15:39:06.366095+00:00
Instance terminated by USER, pid = 26095
2023-10-12T15:39:17.119346+00:00
Instance shutdown complete (OS id: 26095)

Now let's try to start the Database again and see if the Instance is recovered as we had an incomplete transaction before shutdown.

Beginning crash recovery of 1 threads
 parallel recovery started with 21 processes
 Thread 1: Recovery starting at checkpoint rba (logseq 2 block 145332), scn 0
2023-10-12T15:42:32.201410+00:00
Started redo scan
2023-10-12T15:42:32.216752+00:00
Completed redo scan
 read 331 KB redo, 75 data blocks need recovery
2023-10-12T15:42:32.221863+00:00
Started redo application at
 Thread 1: logseq 2, block 145332, offset 0
2023-10-12T15:42:32.225361+00:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/ORADEV/redo02.log
2023-10-12T15:42:32.229587+00:00
Completed redo application of 0.27MB
2023-10-12T15:42:32.241529+00:00
Completed crash recovery at
 Thread 1: RBA 2.145995.16, nab 145995, scn 0x00000000001df16e
 75 data blocks read, 75 data blocks written, 331 redo k-bytes read
2023-10-12T15:42:32.593357+00:00

As you can see in the above section of the Alert Log, the instance was recovered during the very next  startup.

Popular posts from this blog

-
How to startup a Single Instance Database in Oracle 19c Overview An Oracle Database goes through various stages until it is made available to the users. They are categorized into the below stages. Concepts The following are the stages of the startup sequence. Nomount   The instance is started, but is not yet associated with a database. The stages are as follows:  1. Searches for a server parameter file in a platform-specific default location and, if not found, for a text initialization parameter file (specifying STARTUP with the SPFILE or PFILE parameters overrides the default behavior)  2. Reads the parameter file to determine the values of initialization parameters  3. Allocates the SGA based on the initialization parameter settings  4. Starts the Oracle background processes  5. Opens the alert log and trace files and writes all explicit parameter settings to the alert log in valid parameter syntax At this stage, no database is associated with the instance. Scenarios that require a N
Read more