Next: Oracle Distribution Files
Up: Files and Componants
Previous: Initialization Parameters
Contents
The best way to understand how all the diffrent files work together
is to examine the Oracle startup proccess. Even though it might seem
like a strange place in this book to cover the topic, it makes the
most sense from a sysadmin point of view, so lets dive in.
When Oracle starts an instance it reads the spfile or pfile to determine
the initialization paramters. It uses these paramters to allocate the SGA
and create background proccesses. All this is done without associating a database
to the instance! At this point the instance is started but not mounted, or as
some say "Started in no mount mode". They say that because you can reach this
state by using the SQL*Plus command "startup nomount".
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1300928 bytes
Variable Size 157820480 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
SQL> quit
# ps -ef | grep -i ora_
oracle 720 1 0 14:14:20 ? 0:00 ora_reco_test
oracle 710 1 0 14:14:19 ? 0:00 ora_mman_test
oracle 708 1 0 14:14:19 ? 0:00 ora_pmon_test
oracle 712 1 0 14:14:19 ? 0:00 ora_dbw0_test
oracle 718 1 0 14:14:19 ? 0:00 ora_smon_test
oracle 714 1 0 14:14:19 ? 0:00 ora_lgwr_test
oracle 716 1 0 14:14:19 ? 0:00 ora_ckpt_test
oracle 726 1 0 14:14:20 ? 0:00 ora_s000_test
oracle 724 1 0 14:14:20 ? 0:00 ora_d000_test
oracle 722 1 0 14:14:20 ? 0:00 ora_cjq0_test
#
When a database is mounted, the datafiles are actually associated
is the instance. It's somewhat akin to loading the bullets into a
gun; the gun is mearly a vehicle to utilize bullets and without them
it's utterly useless. As a fun test, you can rename the datafile
directory (where the control and datafiles are) and startup the
instance without mounting. You won't get an error, you won't
get a complaint... because Oracle isn't interested in anything
but the parameter files at this point. Even though the pfile
specifies the location of the control file, it hasn't tried to
open the control files yet so it won't complain!
This last revolation is extremely important. Why? You'll
notice that alot of the documenation, particularly reguarding
recovery will tell you to connect to an instance even though it's
in need of major recovery. At first glance, in some cases, you'll
scratch your head trying to figure out why they expect you to
start an instance of a database thats been destroyed. Well, now
you know.
Moving along to the second phase of database startup, the database
is mounted. In this step we associate the control, data, redo, and
other database related files to the running instance. If you
are missing files this is where you'll get your error. If you started
your instance using nomount you can't use the startup command again, but
you can use alter database statements to change the state of your
instance.
Lets quickly look at what happens when you mount your database
with the instance already running but with the all the data and control
files missing (renamed data directory):
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying controlfile, check alert log for more info
SQL> quit
# tail /u01/app/oracle/admin/test/bdump/alert_test.log
alter database mount
Wed Oct 13 14:28:12 2004
ORA-00202: controlfile: '/u02/oradata/test/control01.ctl'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
Wed Oct 13 14:28:12 2004
Controlfile identified with block size 0
Wed Oct 13 14:28:12 2004
ORA-205 signalled during: alter database mount...
#
Notice that it complains about the first controlfile and not
the datafiles. Thats because the parameter file has a listing of
all the controlfiles and the controlfile is responsable for storing
information about all the other datafiles and resources used by
the database. If the controlfile can't be read the database doesn't
know what else exists! This is why you should be careful to keep good
backups of your controlfiles using plain ol' system backups. This
is also why Oracle maintains multiple copies (typically 3) of the control
file for safety.
Lets put all the datafiles back and try mounting the database again.
# mv test.HOLD/ test
# sqlplus sys/passwd as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Oct 13 14:36:09 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database mount;
Database altered.
Looking at the proccesses on the system, you'll notice that after mounting the
database no change has occured to the proccesses.
Once an instance is started using a pfile and the mount proccess has used
the controlfile(s) to associate the datafiles with the instance we need to
open the database. Untill a database is opened it is not accessable.
It's equivilent to starting a system in single-user mode. Some ammount
of interaction with the databse is avalible at this stage but it's limited
to fixed tables and views. The fixed tables and views are
those in the data dictionary (Oracle's internal configuration tables).
But here's the confusing part, the normal data dictionary tables (ALL_USERS, for
example) will give you an error, but most of the V$ tables, which are supposed
to be the dynamic tables, work fine! What exactly "fixed" is supposed to mean
in the case I dunno.
SQL> select * from all_users;
select * from all_users
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed
tables/views only
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL>
To open the database for normal access, we can alter the database again.
SQL> alter database open;
Database altered.
The shutdown proccess is the simply opposite of the startup.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Next: Oracle Distribution Files
Up: Files and Componants
Previous: Initialization Parameters
Contents
2005-02-10