control file management

Using control file : oracle bring database to the mount stage 

what is the information stored in control file # ?

Metadata of the database if the stores in the control file #

Metadata # structure of the database, : physical location of the database  . db_name, db_unique_name, database scn_number, backup information etc. 

If the control file is corrupted, we cannot start the database, database cannot be mounted 

#### 

oracle recommends us to have 3 control files / in three different locations / 

All the control files have same data ## 

if we mutliplexing control file , if one control file is controlfile is corrupted, we can restore using another control file : 

we store control files using control_files parameter  ### 

SQL> show parameter control_files

NAME     TYPE VALUE

------------------------------------ ----------- ------------------------------

control_files     string /u01/app/oracle/oradata/mig23p/control01.ctl,/u01/app/oracle/mig23p/control02.ctl

Procedure to multiplex control files: 

SQL> select name,value,issys_modifiable from v$parameter where name='control_files';

NAME

--------------------------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

ISSYS_MOD

---------

control_files

/u01/app/oracle/oradata/mig23p/control01.ctl, 

/u01/app/oracle/mig23p/control02.ctl

FALSE

Nature of parameter is static ## In order to make it permanent, I need to bounce the database ### Here I want to Multiplex the 3rd control file in the same location # 

SQL> alter system set control_files='/u01/app/oracle/oradata/mig23p/control01.ctl','/u01/app/oracle/mig23p/control02.ctl','/u01/app/oracle/mig23p/control03.ctl' scope=spfile;

System altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

And also 3rd control file doesn't exist physically.
Go to the controlfile location and create new controlfile using existing control file 
[oracle@atl mig23p]$ cd /u01/app/oracle/oradata/mig23p/
[oracle@atl mig23p]$ ls -ltr control02.ctl

-rw-r----- 1 oracle oinstall 9748480 May  2 20:05 control02.ctl

[oracle@atl mig23p]$ 

[oracle@atl mig23p]$ 

[oracle@atl mig23p]$ cp control02.ctl control03.ctl

[oracle@atl mig23p]$ 

[oracle@atl mig23p]$ 

[oracle@atl mig23p]$ ls -ltr

total 19072

-rw-r----- 1 oracle oinstall 9748480 May  2 20:05 control02.ctl

-rw-r----- 1 oracle oinstall 9748480 May  2 20:08 control03.ctl

SQL> alter database mount;

Database altered.

SQL> select name,OPEN_MODE from v$database;

NAME   OPEN_MODE

--------- --------------------

OVAL009P  MOUNTED

SQL> alter database open;

Database altered.

SQL> show parameter control_files

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_files   string /u01/app/oracle/oradata/mig23p/control01.ctl, /u01/app/oracle/mig23p/control02.ctl, /u01/app/oracle/mig23p/control03.ctl

What are the operations performed in mount stage #?

we can open database 

we can convert database from noarchivelog mode to archivelog mode

we can enable supplemental logging 

we can enable flashback for database 

we can restore / recover the database 

### Important — >   CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused. This parameter is dynamic. we can change this parameter when database is up and running 

SQL> show parameter control_file

NAME         TYPE             VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time     integer 7

--Nikhil Tatineni--

--Oracle Database -