Managing Oracle Instance

When we talk about the Oracle instance, we mean the memory and background processes that enable the user interact with the database. If you haven’t read about the instance, take a look at it here. You see, when Oracle is started, the instance goes through some steps to start up before the database is available to all users. Don’t worry, they are not so many steps. They are only three steps i.e. NOMOUNT, MOUNT, and OPEN 😲 . Let us look at each of these stages and see what happens at every stage.

Instance Startup Process

NOMOUNT

At this stage, the initialisation parameter file i.e. SP or P file is read. This file is charged with providing the instance with all database settings. When this is successful (reading of the parameter file), memory and background processes are started. At this stage, the database is not yet open and not associated with instance. We can create databases and control file at this stage.

MOUNT

When the instance has been opened, the control file is read at this stage. The control file contains all important information about the database for example, database location, names and locations of important database files, etc. When this file is read, the instance is associated with the database but not yet available to all users. Some of the administrative tasks that can be done at this stage include renaming database files, enabling and disabling archiving, etc.

OPEN

This is the final stage of instance start up process. At this stage, all database files are located and loaded. The database is open and available to all users. Users can perform all activities with the database like retrieving data, updating data, deleting, etc.

Instance Startup Modes

With the startup modes, we look at the different ways we can open the instance. Just think of it like the different Windows shutdown modes i.e. shutdown, restart and sleep. Each mode has a different behaviour. After a database shutdown, the next time you connect to Oracle database, a “connected to an idle instance” message will be displayed. To solve this, you need to connect to the database as a DBA, and start the instance. To start the instance, we use the keyword STARTUP Below are the instance startup modes we have.

STARTUP OPEN

This is the default startup mode. If we don’t specify the startup mode we want, this is the default mode Oracle uses. With this mode, the instance goes through a normal 3 step process (nomount, mount and open). The database is open and availed to all users. To use this, we use the STARTUP or STARTUP OPEN command after connecting as a DBA.

STARTUP

STARTUP NOMOUNT

Sometimes we don’t want to go through a normal process for some reasons for example, we may be in need of doing some maintenance work on the database and we don’t want anyone to access it. We can use other modes and one of them is this one. With this mode, the instance is started and only the memory and background processes are started. Only activities performed under the nomount stage can be done with this kind of startup mode.

STARTUP NOMOUNT

STARTUP MOUNT

With the startup mount mode, the database is associated with the instance i.e. the startup process stops at the mount stage. All activities done under the mount stage can be done with this kind of mode. The database is not open and not available to users.

STARTUP OPEN READ ONLY

With this kind of startup mode, the database is open and available for all users. However, users cannot do anything to the data in the database other than just retrieving and reading data. Activities like updating, inserting, deleting are all not possible with this mode. Just like the name suggests, the database is opened in read-only mode.

STARTUP FORCE

Sometimes you may find that you have difficulties with starting up the database using the normal startup modes. When it happens, we can just force the database to startup using this mode. When this mode is being used, the database is restarted after a shutdown abort (to be looked at very soon). It shouldn’t be used regularly.

STARTUP RESTRICT

Sometimes we need to make the database available to only a few users. When this is the case, we use a STARTUP RESTRICT mode. With this mode, the database is open but available to only users with the RESTRICTED SESSION privilege. We can use this mode when we are performing some database maintenance work and we don’t want users to connect to the database.

STARTUP OPEN RECOVER

This mode opens the database and performs database recovery.

Instance Shutdown Process

Just like the startup process, we also have the instance’s shutdown process. Sometimes you’ll need to shutdown the database for some reasons like performing backup, doing database upgrades, etc. To shutdown, we issue a SHUTDOWN command to shut it down. The instance goes through a process until it is shutdown. Below are the steps:

CLOSE

At this stage, the database is shutdown and not available to all users. All database files are no longer accessible to the users.

UNMOUNT

The database is disassociated with the instance. The database is stored on disk.

SHUTDOWN

The background processes are stopped and memory is de-allocated. The instance is shutdown.

Instance Shutdown Modes

SHUTDOWN NORMAL

This is the default shutdown mode in Oracle. It goes through a normal shutdown process of the instance. When issued, no more new connections to the database are allowed. It waits for all connected users to disconnect from the database before it shuts down the database. It is the safest mode of all modes though it is the slowest since it waits for all users to disconnect from the database.

SHUTDOWN TRANSACTIONAL

With this shutdown mode, no new connections are allowed to the database just like all other modes. Users are left to finish the work (transactions) they are doing but no new transaction is allowed to be started by the user. All idle connections are terminated. Once all active transactions are done, the shutdown goes into effect. It is also a clean mode and doesn’t require any recovery on the next startup.

SHUTDOWN IMMEDIATE

With this shutdown mode, the database is shutdown immediately. All active uncommitted transactions are terminated and rolled back and thus losing of the uncommitted transactions. There is no waiting for users to disconnect from the database and no new connections to the database are allowed. Even though it is an aggressive mode, it is still a clean mode and no recovery is required on the next startup.

SHUTDOWN ABORT

This is the most aggressive shutdown mode. All connections are terminated and no new connections are allowed to the database. Any ongoing SQL statement is terminated and uncommitted work is not rolled back. It is considered an unclean mode and requires recovery on the next startup. Oracle performs this recovery automatically to rollback the uncommitted transactions and committed changes are written to the data files. It is not advisable to use this mode regularly.

Managing Oracle’s Metadata

In the previous discussion, we’ve been getting data from tables that start with v$ and dba and maybe you haven’t been paying much attention to that but now I got you 😜 . These are examples of something we call metadata.

Metadata is data about the data. Apart from user tables, Oracle has system tables that store data about the database for example names of the all tables in the database, number of rows the tables contain, data types of those columns, among others. Oracle uses two metadata views i.e. Data Dictionary Views and Dynamic Performance Views. Both these views are owned by SYS and stored in the SYSTEM tablespace.

Data Dictionary Views

These are views (virtue tables) that provide information about the database and its objects for example tables, users, etc. Data dictionary views have names that start with DBA_, ALL_ and USER_ and are generally in plural form. Whenever you see a SQL statement querying from a view (virtue table) starting with DBA or ALL or USER, just know you’re working with a data dictionary view for example dba_users, dba_data_files, dba_tables, dba_views, etc.

The difference between DBA_, ALL_, and USER_ can be got using tables as a database object. Lets use DBA_TABLES, ALL_TABLES and USER_TABLES. DBA_TABLES returns all tables in the database, ALL_TABLES returns tables belonging to a particular user and those to which the user has been granted access to and USER_TABLES return only tables belonging to a particular user. Oracle database can have 1600 data dictionary views.

Dynamic Performance Views

The dynamic performance views provide information about the database status and performance. They are dynamic in nature because of the ever changing performance of the database. Dynamic Performance Views start with v$ and are generally in singular form for example, v$process, v$sgainfo, v$datafile, v$pgastat, etc. Oracle database can have 480 dynamic performance views. The data in these views is dynamic and lost when the database is shutdown.

Summary

1. Instance startup process include nomount, mount and open.

2. Instance startup modes include startup nomount, startup open, startup restrict, etc.

3. Instance shutdown process include close, unmount and shutdown.

4. Instance shutdown modes include shutdown normal, abort, transactional and immediate.

5. Data dictionary views provide information about the database and its objects.

6. Dynamic performance views provide information about database performance.