Blog

Learnomate Technologies > Blog > Oracle DBA > Startup and shutdown sequence of oracle database

Startup and shutdown sequence of oracle database

  • November 21, 2021
  • Posted by: Ankush Thavali
  • Category: Oracle DBA
No Comments

STARTUP/SHUTDOWN SEQUENCE OF ORACLE DATABASE

1. To check the database is up or down...

ps -ef |grep pmon

2. To check how many databases are on the server, either up or down... 

cat /etc/oratab

3. To check the status of the instance...[Status can be either in NOMOUNT, MOUNT or OPEN] 

sqlplus / as sysdba
select status from v$instance;

4. Different ways you can shutdown an Oracle database...

  1. 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”.
  1. 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”.
  1. C) Shutdown immediate;
  • Blocks All connections immediately
  • Kills all connections.
    • Select are killed immediately;
    • Insert, Update and Deletes are rolled back to previous state.
  1. 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.

5. How to startup the database so that users can read and write to database? 

export ORACLE_SID=DatabaseName
(or)
. oraenv

sqlplus / as sysdba
startup

6. When “startup” is issued, what stages instance goes through before starting the database?

0

Closed

1

nomount

2

mount

3

open

Describe each stage.

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.
  • 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.
  • 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

7. Different ways of shutting down database

Shutdown  = shutdown normal

shutdown immediate;

shutdown transactional;

shutdown abort;

8. Different stages of starting up database

startup nomount;  ==> requires spfile or pfile

alter database mount; ==> requires controlfiles

alter database open; ==> requires datafiles and redologs

9. If I start the database in mount stage, how can I open the database?

startup mount;

alter database open;

10. What is the difference between spfile and init.ora file? (Also called spfile and pfile).

PFile

SPFile
  • Editable (using vi)
  • Change needed to be made manually through vi
  • Not editable (Binary)
  • Changes can be made with “Alther System” command

11. To verify if the database is started from spfile, type the following.

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:

12. To create init.ora (pfile)

create pfile=/data/app/oracle/product/19C/dbhome_3/dbs/spfileTESTDB.ora

Example: 

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

13. To create spfile.ora (spfile) 

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

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. 

startup pfile='/data/app/oracle/product/19C/d bhome_3/dbs/spfileTESTDB.ora'
Startup and shutdown sequence of oracle database