Oracle Database - Startup & Shutdown Options

For a DBA, starting up of oracle database is a routine and basic operation. Sometimes Linux administrator or programmer may end-up doing some basic DBA operations on development database. So, it is important for non-DBAs to understand some basic database administration activities.

How to start Oracle Database
  1. Startup procedures
  2. Start Oracle Database
Startup & shutdown options : When we start up or shutdown the database, we can see internal processes and stages goes for each action. When we startup the database, it goes for 3 stages,

Startup: When we start a database, we can see it cross different stages.
  • startup nomount
  • startup mount
  • startup
  • startup force
  • startup upgrade
  • startup migrate
  • startup restrict
1. Startup Procedures
We can startup Oracle Database in 3 states/modes

Startup nomount :
In this state the control file, online redo log files, and the database files are closed and are not accessible. The Oracle instance is available. We startup the database in this state to perform operations like, creating a database, recreating controlfile etc.
	SQL> startup nomount
	ORACLE instance started.
	Total System Global Area 1157627168 bytes
	Fixed Size		    8895776 bytes
	Variable Size		  301989888 bytes
	Database Buffers	  838860800 bytes
	Redo Buffers		    7880704 bytes

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

	SQL>
	In this stage we check spfile or pfile
	SQL> show parameter spfile


		NAME			TYPE					VALUE
	----------------	  ----------------   --------------------------------------------------------
	  spfile			string	    /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfiletesdb.ora
To Check Control file path:
	SQL> show parameter control

	NAME				TYPE		VALUE
	----------------		-------		----------------
	control_file_record_keep_time	integer		7

	control_files			string	/u01/app/oracle/oradata
						/TESDB/control01.ctl,
						/u01/app/oracle/fast_recovery_area
						/TESDB/control02.ctl

	control_management_pack_access	string	DIAGNOSTIC+TUNING

	SQL>
	In no mount some of the parameters only work like controlfile
Startup mount
  • This is the next phase through which the database passes
  • In this stage, the control file is opened and the existence of all the database files and online redo log files is verified
  • We startup the database in this state to perform operations like, Backup, Recovery of the system or undo datafile, Change the database to archive log mode etc
  • it check where the scn number of datafile header, ctrl file and lsn of logfile header are same
  • Database will only open when all the above numbers are of same, else will throw out error as database doesnt open
When a database is mounted, it
	SQL> startup mount

	ORACLE instance started.
	Total System Global Area 1157627168 bytes
	Fixed Size		    8895776 bytes
	Variable Size		  301989888 bytes
	Database Buffers	  838860800 bytes
	Redo Buffers		    7880704 bytes
	Database mounted.

	SQL>
	(Or)
	Incase of it has nomount stage . we just modify alter using mount stage.
	SQL> alter database mount;
	Database altered.

	SQL> select open_mode,name from v$database;
	OPEN_MODE	     NAME
	------------      ---------
	MOUNTED 	     TESDB
STARTUP OPEN STAGE
	startup = normal startup, where it most defaulty uses spfile or pfile to open database.
	(crosses all the 3 stages & open database).

	open mode : During open mode, the datafiles and the online redo log files are opened
	and are ready to use.

	Oracle doesn?t allow you to open the database if any of the datafile or online redo
	log file is missing or is corrupted.

	Any valid user can connect to the database.

	Opening the database includes opening datafiles and redo log files.

	If any of these files are missing, Oracle will return an error.

	If errors occurred during the previous database shutdown, the SMON background process
	will initiate instance recovery usage: startup

	Oracle will use this SPFILE during startup, if you don?t specify PFILE. Default
	Location of the pfile and spfile are $ORACLE_HOME/dbs.

	SQL> startup
	ORACLE instance started.

	Total System Global Area 1157627168 bytes
	Fixed Size		    8895776 bytes
	Variable Size		  301989888 bytes
	Database Buffers	  838860800 bytes
	Redo Buffers		    7880704 bytes
	Database mounted.
	Database opened.

	SQL>

	When you need to startup Oracle Database with PFILE, then pass it as a parameter as shown below :
	SQL> startup pfile='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/inittes.ora'
	ORACLE instance started.

	Total System Global Area 1157627168 bytes
	Fixed Size		    8895776 bytes
	Variable Size		  301989888 bytes
	Database Buffers	  838860800 bytes
	Redo Buffers		    7880704 bytes
	Database mounted.
	Database opened.

	SQL> show parameter pfile

		  NAME		TYPEVALUE
		------------- ---------------
		spfile		  string
	
	SQL> show parameter spfile
	
		NAME		TYPEVALUE
		------------- ---------------
		spfile		  string
	
	SQL>
	(Or )
	Previous it has mount stage. We alter open .

	SQL> alter database open;

	Database altered.

	SQL> select open_mode,name from v$database;

	OPEN_MODE		    NAME
	-------------------- 	  ---------
	READ WRITE		    TESDB

	When database start scn number started.
	SQL> select current_scn from v$database;

	CURRENT_SCN
	-----------
	2253894

	SQL>
Startup force = shut abort + startup (normal).
Startup force "kills" the instance and restarts it and since the instance wasn't shutdown gracefully, it would be requiring an instance recovery in the next startup.

Now, depending on how much work you were doing, it could take from few minutes to probably much longer to get it done completely. So unless you don't have some really hard reason to do so, it would be better if you would shutdown properly and then do a database startup again.

startup restrict= maintainence work of db like, full backup processing,(avoiding transaction) statement to enable startup restrict is.,

Restrict Mode in which Oracle database allow making connection with special rights such as DBA, SYSDBA to perform the maintenance activity like rebuilding index, remove fragmentation etc.

It is very use full for DBA for start database in restricted mode for planned maintenance activity. So, No other user such as application users are able to connect with database until they have special rights.

startup restrict command : will open database in restricted mode
sql> alter system enable restricted session can be view in v$instance view.


(Oracle Database - Redo Logfiles)