Startup and shutdown sequence of oracle database - A) Shutdown  (or Shutdown normal); - Blocks All connections immediately; - Wait for all connections to logout gracefully - Once all the connections are disconnected, database is marked as “shutdown completed”. - B) Shutdown transactional; - Blocks All connections immediately - Kills all connections that are “Select” only. - Wait for all DML (Delete, Update, Insert) transactions to end. Meaning? User has to either commit or rollback before the transaction is marked as completed. - Once all the DML transactions ends, database is marked as “shutdown completed”. - C) Shutdown immediate; - Blocks All connections immediately - Kills all connections. Select are killed immediately; Insert, Update and Deletes are rolled back to previous state. - Select are killed immediately; - Insert, Update and Deletes are rolled back to previous state. - D) Shutdown abort; - Kills the PMON process, hence database is not functional. - All connected “select only” queries are aborted and will be cleaned up by PMON process. - All DML transactions (transactions which were doing insert, update or delete) will be consider as “In-Doubt Transactions” during the next database started. At this point, SMON process will perform rollback or rollforward to mark the transaction complete. 0 Closed 1 nomount 2 mount 3 open nomount :- - Oracle assigns a small memory to the named instance. - Oracle Verifies initialization parameter file is available in $ORACLE_HOME/dbs location. Default initialization parameter file in 12c and 19c is spfile If spfile is not found, init$ORACLE.SID.ora will be referred to start the database. - Default initialization parameter file in 12c and 19c is spfile - If spfile is not found, init$ORACLE.SID.ora will be referred to start the database. - Once the initialization parameter is finalized (spfile or pfile), Oracle will check the accuracy of each parameter inside the initialization parameter file. Verification process looks for the following: All locations which are given to parameter are available on OS level. If the location is not valid, an error is trigged and recorded in alert log. All numeric values should be valid. If they are not valid numers, an error is trigged and recorded in alert log. Errors are also raised when typos are found. These errors are again recorded in alert log. - All locations which are given to parameter are available on OS level. If the location is not valid, an error is trigged and recorded in alert log. - All numeric values should be valid. If they are not valid numers, an error is trigged and recorded in alert log. - Errors are also raised when typos are found. These errors are again recorded in alert log. - Listener is notified that the instance. However, the service is marked as “Blocked” Once all of the above passes, instance status is moved to next step which is “mount” stage mount :- - Oracle reads the initialization parameter file for the location of controlfile. - Controlfile keeps record of the following: - The database name - Tablespace Names - Names and locations of associated datafiles - Names and locations of redo log files - The timestamp of the database creation - The current log sequence number → we will cover this backup and recovery - Checkpoint information → we will cover this later. - Backup and Recovery Information. → we will cover this backup and recovery - Archivelog Information. → we will cover this backup and recovery - Verifies all multiplexed controlfils are same in size. (checksum). If not, error is raised and recorded in alert log. - Listener Service status is changed to “Ready”, however the dispatcher is still not allocated to the listener. Once all of the above check passes, instance is moved to next stage of opening the database open :- - Listener requests are given a dispatcher to allow the traffic to connect to the database for I/O. - Database status is finally changed to open Shutdown  = shutdown normal shutdown immediate; shutdown transactional; shutdown abort; startup nomount;  ==> requires spfile or pfile alter database mount; ==> requires controlfiles alter database open; ==> requires datafiles and redologs startup mount; alter database open; PFile SPFile - Editable (using vi) - Change needed to be made manually through vi - Not editable (Binary) - Changes can be made with “Alther System” command show parameter spfile If you see the path of spfile, database is started with spfile. If you see the path is null (emtpy), database is started with pfile [init<DBName>.ora] Example: create pfile=/data/app/oracle/product/19C/dbhome_3/dbs/spfileTESTDB.ora Example: If the instance is already started with spfile, then you will get an error like below: 14. To start the database from pfile instead of spfile [Assuming the DB is down and pfile is already created.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

ps -ef |grep pmon

cat /etc/oratab

sqlplus / as sysdba
select status from v$instance;

export ORACLE_SID=DatabaseName
(or)
. oraenv

sqlplus / as sysdba
startup

create pfile='/data/app/oracle/product/19C/dbhome_3/dbs/spfileTESTDB.ora' from spfile;

create spfile='/data/app/oracle/product/19C/dbhome_3/dbs/spfileTESTDB.ora' from pfile;

startup pfile='/data/app/oracle/product/19C/d bhome_3/dbs/spfileTESTDB.ora'