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

Startup procedures
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.

« Previous Next Topic » (Oracle Database - Redo Logfiles)