Google+ Followers


Thursday, July 14, 2011

Oracle Startup Behavior

The Oracle startup process consist out of 3 steps:
nomount phase: during this phase, the initialization file is read
mount phase: during this phase, the controlfile file is read
open phase: during this phase, the datafiles and redolog files are checked for consistency and opened
During the nomount phase, the Oracle initialization file is read. If it is found, the memory structures are created and the Oracle processes are started.

The memory area and startup of the processes are done using the ‘startup nomount’ command.
SQL> conn / as sysdba
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                  2082496 bytes
Variable Size             113248576 bytes
Database Buffers           50331648 bytes
Redo Buffers                6303744 bytes
We can check the v$instance view to check if the instance was started successfully and check the status of the instance
SQL> select instance_name, status from v$instance;

------------- ----------------------
Because we only have read the initialization file, we cannot check the status of the database.

To be able to check the database, we need to mount the database.
SQL> select * from v$database;
select * from v$database
ERROR at line 1:
ORA-01507: database not mounted
During the mount phase, the controlfile(s) are checked for availability and read.

The controlfile contain information about the database, such as the database name and the number and location of the datafiles.

The database is mounted using the ‘alter database mount’ command.
SQL> alter database mount;

Database altered.
Should there be a problem with (one of the) controlfile(s), you will get an error.

In order to mount a database, all controlfiles should be available and accessible.
SQL> alter database mount;
alter database mount
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
You need to check the alert.log file in order to rectify the problem.

You can use following query to check what controlfiles are needed to mount the database.
SQL> select value from v$parameter where name='control_files';

Now you can check the status of the database using the v$database view:
SQL> select name, open_mode from v$database;

--------- ----------
In order to use the database, we need to open the database.
During the open phase, the datafiles and redolog files are check for consistency and opened.

When the database is open, you can also access the data dictionary views. Because the data dictionary views are stored in datafiles, you can only query the views when the database is open.
SQL> select count(*) from dba_users;
select count(*) from dba_users
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
In order to open the database, we issue the ‘alter database open’ command.
SQL> alter database open;

Database altered.

No comments:

Post a Comment