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.
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]
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.
– 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
– 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