Site icon IT Tutorial

Oracle Database Startup and Shutdown Steps

Hi Guys,

In this article, I will explain the startup and shutdown modes of Oracle database.

Oracle Database Startup Modes
A closed Oracle database opens in 3 stages. The best summarizing image is as follows. We will review these steps one by one.

 

Startup modes

Nomount Mode: The instance and background processes start when we open the Oracle database in Nomount mode. Oracle reads the parameter files (spfile or pfile) in this step. In this mode, the database is closed to other users. Only instance and background processes are open.

bash-4.1$ ps -ef | grep smon
oracle 20114 19577 0 14:15 pts/0 00:00:00 grep smon
bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:15:10 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 339740872 bytes
Database Buffers 184549376 bytes
Redo Buffers 7942144 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bash-4.1$ ps -ef | grep smon
oracle 20143 1 0 14:15 ? 00:00:00 ora_smon_TESTDB
oracle 20160 19577 0 14:15 pts/0 00:00:00 grep smon
bash-4.1$ ps -ef | grep ckpt
oracle 20141 1 0 14:15 ? 00:00:00 ora_ckpt_TESTDB
oracle 20162 19577 0 14:15 pts/0 00:00:00 grep ckpt
bash-4.1$ ps -ef | grep lgwr
oracle 20139 1 0 14:15 ? 00:00:00 ora_lgwr_TESTDB
oracle 20166 19577 0 14:16 pts/0 00:00:00 grep lgwr


bash-4.1$ ps -ef | grep smon
oracle 20114 19577 0 14:15 pts/0 00:00:00 grep smon
bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:15:10 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 339740872 bytes
Database Buffers 184549376 bytes
Redo Buffers 7942144 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bash-4.1$ ps -ef | grep smon
oracle 20143 1 0 14:15 ? 00:00:00 ora_smon_TESTDB
oracle 20160 19577 0 14:15 pts/0 00:00:00 grep smon
bash-4.1$ ps -ef | grep ckpt
oracle 20141 1 0 14:15 ? 00:00:00 ora_ckpt_TESTDB
oracle 20162 19577 0 14:15 pts/0 00:00:00 grep ckpt
bash-4.1$ ps -ef | grep lgwr
oracle 20139 1 0 14:15 ? 00:00:00 ora_lgwr_TESTDB
oracle 20166 19577 0 14:16 pts/0 00:00:00 grep lgwr

Mount Mode: The Control file opens in this step. When the database opens and reads control file, it will know where the datafiles and archive files are. However, in this step, users still cannot access the database. Only system views and performance views are accessible.

We can mount a database in Nomount mode as follows.

 

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:24:15 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> alter database mount;

Database altered.
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED


bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:24:15 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> alter database mount;

Database altered.
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

 

Or we can mount a closed database directly as follows.

 

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 15:06:58 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 339740872 bytes
Database Buffers 184549376 bytes
Redo Buffers 7942144 bytes
Database mounted.
SQL>

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 15:06:58 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 339740872 bytes
Database Buffers 184549376 bytes
Redo Buffers 7942144 bytes
Database mounted.
SQL>

 

Open Mode: In this mode, the database opens normally and can be accessed by other users. We can start a closed database directly in Open mode as follows.

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:29:41 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 339740872 bytes
Database Buffers 184549376 bytes
Redo Buffers 7942144 bytes
Database mounted.
Database opened.
SQL>

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:29:41 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 339740872 bytes
Database Buffers 184549376 bytes
Redo Buffers 7942144 bytes
Database mounted.
Database opened.
SQL>

Even if we open a closed database directly in open mode, the above operations take place in the background. That is, the database is first opened in nomount mode, then brought to mount mode, and then opened. This happens very quickly.

We can bring a database in Mount mode to Open mode as follows.

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:32:06 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;

Database altered.

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:32:06 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;

Database altered.

Oracle Database Shutdown Modes
Oracle offers us 5 different methods for shutting down the database. As each method has different functions, we will examine they separately below.

Shutdown immediate: This method is the most preferred method. Because the database is closed in a healthy and consistent way. When this command is executed, oracle rollbacks uncommitted transactions and executes the shutdown procedures. The use of the command is as follows.

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:50:17 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:50:17 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Shutdown Normal: This method is not preferred, but sometimes it may be needed. When we try to close the database with this method, Oracle waits all users connected to the database to finish their jobs and close their sessions. The database will not close even if a single session is open. The use of the command is as follows.

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:50:17 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:50:17 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

 

Shutdown Transactional: In this method, Oracle waits all users to commit their transactions. After the Commit process is complete, Oracle kills that session and continue to shutdown process. This method has zero data loss guarantee. The use of the command is as follows.

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:59:47 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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


bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:59:47 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

 

Shutdown abort: This method is like unplugging the database. All transactions are suddenly stopped and the database is closed inconsistently. Although this method is not preferred, it is sometimes very necessary. For example, you are doing a very critical operation and time is very important for you and you need to close or restart the database. If it will take too long to shutdown the database in a consistent manner, this method can be used. The use of the command is as follows.

bash-4.1$ ps -ef | grep smon
oracle 20376 1 0 14:31 ? 00:00:00 ora_smon_TESTDB
oracle 20486 19577 0 14:45 pts/0 00:00:00 grep smon
bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:46:02 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


bash-4.1$ ps -ef | grep smon
oracle 20490 19577 0 14:46 pts/0 00:00:00 grep smon
bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:46:23 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL>
bash-4.1$ ps -ef | grep smon
oracle 20376 1 0 14:31 ? 00:00:00 ora_smon_TESTDB
oracle 20486 19577 0 14:45 pts/0 00:00:00 grep smon
bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:46:02 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bash-4.1$ ps -ef | grep smon
oracle 20490 19577 0 14:46 pts/0 00:00:00 grep smon
bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 14:46:23 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL>

In addition, there is a force restart command. Lastly, I will explain this command.

Startup Force: This command runs the Shutdown abort + Startup command in the background. So with Shutdown abort, the database is suddenly closed, and then startup and the database are opened in a normal format. The use of the command is as follows.

bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 15:03:47 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup force
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 339740872 bytes
Database Buffers 184549376 bytes
Redo Buffers 7942144 bytes
Database mounted.
Database opened.
SQL>
bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 7 15:03:47 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup force
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 339740872 bytes
Database Buffers 184549376 bytes
Redo Buffers 7942144 bytes
Database mounted.
Database opened.
SQL>


 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/

Exit mobile version