IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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/
http://xn--52-6kcaysovb0bn1b.xn--p1ai – сантехника52.рф –
http://xn--52-6kcaysovb0bn1b.xn--p1ai – вызов сантехника на дом круглосуточно
express cash advance