How to startup a Single Instance Database in Oracle 19c
An Oracle Database goes through various stages until it is made available to the users. They are categorized into the below stages.
Concepts
The following are the stages of the startup sequence.
Nomount
The instance is started, but is not yet associated with a database. The stages are as follows:
1. Searches for a server parameter file in a platform-specific default location and, if not found, for a text initialization parameter file (specifying STARTUP with the SPFILE or PFILE parameters overrides the default behavior)
2. Reads the parameter file to determine the values of initialization parameters
3. Allocates the SGA based on the initialization parameter settings
4. Starts the Oracle background processes
5. Opens the alert log and trace files and writes all explicit parameter settings to the alert log in valid parameter syntax At this stage, no database is associated with the instance. Scenarios that require a NOMOUNT state include database creation and certain backup and recovery operations.
Mount
The instance is started and is associated with a database by reading its control file. The database is closed to users.
Open
The instance is started and is associated with an open database. The data contained in the data files is accessible to authorized users.
Practical Demonstration
A Database can be directly started up completely using startup command or it can be started to a specific stage, and move on to the further stages based upon the requirement.
Export the environment variables of the Database and login to sqlplus and sysdba
Note :-You must be connected to a database as SYSDBA, SYSOPER, SYSBACKUP, or SYSDG.
export ORACLE_HOME=/u01/app/oracle/product/19c/db
export ORACLE_SID=ORADEV
export PATH=$ORACLE_HOME/bin:$PATH
To start the Database in nomount, issue the following command.
Tip#1 :- It is always a best practice to view the alert log of instance in another session, to understand what is happening in the background as well as errors and warnings that helps in troubleshooting a problem.
The alert log can be found inside $ORACLE_BASE/diag/<db-name>/<sid>/trace/alert_<db_name>.log
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.2187E+11 bytes
Fixed Size 32535912 bytes
Variable Size 1.3959E+10 bytes
Database Buffers 1.0764E+11 bytes
Redo Buffers 235896832 bytes
SQL>
To mount the database from no mount stage, issue the following command.
SQL> alter database mount;
Database altered.
SQL>
To open the database from mount stage, issue the following command.
SQL> alter database open;
Database altered.
SQL>
Note:- The following ways are used to mount or open a database with the execution of a single command.
To mount the database, issue the following command.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1.2187E+11 bytes
Fixed Size 32535912 bytes
Variable Size 1.3959E+10 bytes
Database Buffers 1.0764E+11 bytes
Redo Buffers 235896832 bytes
Database mounted.
SQL>
To start the database, issue the following command.
Note:- It is not possible to go back to a previous stage(from open to mount stage or from mount to nomount). Database needs to be shutdown and startup again with the respective option.
SQL> startup
ORACLE instance started.
Total System Global Area 1.2187E+11 bytes
Fixed Size 32535912 bytes
Variable Size 1.3959E+10 bytes
Database Buffers 1.0764E+11 bytes
Redo Buffers 235896832 bytes
Database mounted.
Database opened.
SQL>
Now its time to verify the current state of the Database.
SQL> set lines 300
SQL> col STARTUP_TIME for a20
SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
select name,instance_name,
open_mode,status,startup_time
from
v$database,v$instance;
Session altered.
NAME INSTANCE_NAME OPEN_MODE STATUS STARTUP_TIME
--------- ---------------- -------------------- ------------ --------------------
ORADEV ORADEV READ WRITE OPEN 12/10/2023 08:21:57
SQL>
Tip#2 :- Setting the nls_date_format parameter, as mentioned above gives exact startup time of the Database Instance.
Note:- There are few other STARTUP options such as FORCE, RESTRICT,UPGRADE,MIGRATE etc, which are used to perform various operations in the Database.
