POiSON WORLD

Oracle Database and Instance

The Database is a set of physical operating system files. These files actually holds the user data and the metadata (or the data dictionary). Every running Oracle database is associated with (atleast) an Oracle instance. The Instance refers to the set of Oracle background processes or threads and a shared memory area (SGA). An instance can mount and open at most one database in its life. A database may be mounted and opened by one or more instances (using RAC) and the number of instances mounting a single database can fluctuate over time.

Database statup

For any normal user to access the database, the instance has to be started up and it should mount and open the corresponding database. We term the entire steps as database startup. In short, database startup includes the following steps:

1. Start an instance.
2. Mount the database.
3. Open the database.

Database startup requires SYSDBA privilege. To start a database :

$ export ORACLE_SID=[SID of the instance]
$ export ORACLE_HOME= [location of ORACLE_HOME]
$ sqlplus / as sysdba
SQL> startup

The above syntax assumes you have a pfile or spfile in the default location ($ORACLE_HOME/dbs). If you are using a non default parameter file, the startup command is:

$ sqlplus / as sysdba
SQL> startup pfile=[file name and location]

Nomount stage

When you issue a ‘Startup’ command, this is the first stage. ie, starting up the instance. Here,
– Oracle will read the parameter file (spfile or pfile) in the default location or in the location specified in the startup command.
– It will then allocate the memory area (SGA) and starts the background processes. Together we call it as the instance.
– Please note that no database is associated with the instance at this point. We can start the instance alone using the command:

$ sqlplus / as sysdba
SQL> startup nomount
ORACLE instance started.

Total System Global Area 304807936 bytes
Fixed Size 2221000 bytes
Variable Size 113249336 bytes
Database Buffers 184549376 bytes
Redo Buffers 4788224 bytes

The instance is now started and the database is in nomount stage. Starting the instance in nomount stage is usually required for database creation or for creating or recovering the controlfiles.

Mount stage

– In this phase the instance will mount the database.
– Mounting the instance means associating the started instance with a specified database.
– For this, the instance checks the controlfiles specified under CONTROL_FILES parameter and opens it.
– It then reads the control files to find the names of the data files and the online redo log files that it will attempt to access when opening the database.
– The database is still closed and only the DBA can access it.
– This stage is normally used for maintenance operations like renaming datafiles, enabling and disabling archiving options.
– Adding, dropping, or renaming redo log files is also done in mount stage.
– For performing full database recovery database is opened in mount stage.

To mount a database:

$ sqlplus / as sysdba
SQL> startup mount

To mount a database from nomount stage:

$ sqlplus / as sysdba
SQL> alter database mount

Open stage

– This is the final stage and here Oracle opens the online datafiles and redolog files.
– If any of the required files are not present, media recovery is required .
– It also ensures the datafiles are consistent. Incase of a normal shutdown, the in-memory changes will be written to disk as part of the shutdown checkpoint.
– But if the instance crashed (or shutdown abort), then Oracle Database performs the instance recovery in the next startup.

To open a database :

$ sqlplus / as sysdba
SQL> startup

To open a database from mount stage:

$ sqlplus / as sysdba
SQL> alter database open

To verify :

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE