Managing Control Files
Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. The control file includes:
· The database name
· Names and locations of associated datafiles and redo log files
· The timestamp of the database creation
· The current log sequence number
· Checkpoint information
It is strongly recommended that you multiplex control files i.e. Have at least two control files one in one hard disk and another one located in another disk, in a database. In this way if control file becomes corrupt in one disk the another copy will be available and you don’t have to do recovery of control file.
You can multiplex control
file at the time of creating a database and later on also. If you have
not multiplexed control file at the time of creating a database you can
do it now by following given procedure.
Multiplexing Control File
Steps:
1. Shutdown the Database.
SQL>SHUTDOWN IMMEDIATE;
2. Copy the control file from old location to new location using operating system command. For example.
$cp /u01/oracle/ica/control.ora / u02/oracle/ica/control.ora
3. Now open the parameter file and specify the new location like this
CONTROL_FILES=/u01/oracle/ica/ control.ora
Change it to
CONTROL_FILES=/u01/oracle/ica/ control.ora,/u02/oracle/ica/ control.ora
4. Start the Database
Now Oracle will start updating both the control files and, if one control file is lost you can copy it from another location.
Changing the Name of a Database
If you ever want to change the name of database or want to change the setting of MAXDATAFILES, MAXLOGFILES,M AXLOGMEMBERS then you have to create a new control file.
Creating A New Control File
Follow the given steps to create a new controlfile
Steps
1. First generate the create controlfile statement
SQL>alter database backup controlfile to trace;
After giving this statement oracle will write the CREATE CONTROLFILE statement in a trace file. The trace file will be randomly named something like ORA23212.TRC and it is created in USER_DUMP_DEST directory.
2. Go to the USER_DUMP_DEST directory and open the latest trace file in text editor. This file will contain the CREATE CONTROLFILEstatemen t. It will have two sets of statement one with RESETLOGS and another without RESETLOGS. Since we are changing the name of the Database we have to use RESETLOGS option of CREATE CONTROLFILE statement. Now copy and paste the statement in a file. Let it be c.sql
3. Now open the c.sql file in text editor and set the database name from ica to prod shown in an example below
CREATE CONTROLFILE
SET DATABASE prod
LOGFILE GROUP 1 ('/u01/oracle/ica/redo01_01. log',
'/u01/ oracle/ica/redo01_02.log'),
GROUP 2 ('/u01/oracle/ica/redo02_01. log',
'/u01/oracle/ica/redo02_ 02.log'),
GROUP 3 ('/u01/oracle/ica/redo03_01. log',
'/u01/ oracle/ica/redo03_02.log')
RESETLOGS
DATAFILE '/u01/oracle/ica/system01.dbf' SIZE 3M,
'/u01/oracle/ica/ rbs01.dbs' SIZE 5M,
'/u01/oracle/ica/ users01.dbs' SIZE 5M,
'/u01/oracle/ica/ temp01.dbs' SIZE 5M
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;
4. Start and do not mount the database.
SQL>STARTUP NOMOUNT;
5. Now execute c.sql script
SQL> @/u01/oracle/c.sql
6. Now open the database with RESETLOGS
SQL>ALTER DATABASE OPEN RESETLOGS;
Introduction:
Control files are created by Oracle.
The path of a control file is specified in INIT.ORA. Every Oracle
database should have at least two control files (recommended); each
stored on different disks. If a control file is damaged due to disk
failure the assocaited instance must be shutdown. Once the disk drive is
reapired, the damaged control file can be restored using an intact copy
of the control file and the instance can be restarted. Assume that one
control file is located in the path /disk1/oradata/DEMO/control1. ctl, and the other in /disk2/oradata/DEMO/control2. ctl. If one control file 'control1.ctl' is in an unrecoverable state, then issue the following commands
$cp /disk1/oradata/DEMO/control1. ctl \ /disk2/oradata/DEMO/control2. ctl
SQL>startup
Now media recovery is required. By using mirrored control
files you avoid unnecessary problems if a disk failure occurs on the
database servers.
Managing the size of the control file:
Typical
control files are small. The major portion of a control file size
depends on the values set for the parameters: MAXDATAFILES, MAXLOGFILES,
MAXLOGMEMBERS, MAXLOGHISTORY, MAXINSTANCES of the CREATE DATABASE
statement that created the associated database. The maximum control file
size is operating system specific:
To check the number of files specified in control files:
SQL>alter database backup controlfile to trace;
$cd /disk1/oradata/DEMO/udump
$cp ora_2065.trc bkup.sql
$cat bkup.sql
If the MAXDATAFILES parameter is set to 5 and if you try to add sixth datafile issuing the command:
SQL>alter tablespace user_demo add datafile '/disk1/oradata/DEMO/user03. dbf' size 10m;
ORA-1503 create control file failed
ORA-1166 file number 3 larger than MAXDATAFILES (5)
To increase the number of maximum data files supported by your DB edit your trace file.
$vi bkup.sql
MAX_DATA_FILES 10
:wq
SQL>conn / as sysdba
SQL>@bkup.sql
SQL>alter tablespace user_demo add datafile '/disk1/oradata/DEMO/user03. dbf' size 10m;
Follow the same steps to increase the parameter values for LOGFILES and LOGMEMBERS.
To create an additional copy of controlfile, issue the
following statements. Include the complete path of the new file in
'control_files' parameter in INIT.ORA.
$cd /disk2/oradata/DEMO
$cp control1.ctl control2.ctl
SQL>startup
To drop excessive control files:
-Shutdown the database
-Edit
the parameter control_files in INIT.ORA and remove one of the control
file entries, leaving atleast one control file to start the database
-Restart the database
-The above steps do not delete the file physically from the disk.
SQL>SHUTDOWN IMMEDIATE
$cat initDEMO.ora # Here we are only observing 1 line which reads controlfiles
CONTROL_FILES=(/disk2/oradata/ DEMO/control2.ctl)
SQL>STARTUP
To Trace the control file to udump destination and generate the create controlfile syntax.
SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
$cd /disk2/oradata/DEMO/udump
$vi ora_2065.trc
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DEMO" RESETLOGS NOARCHIVELOG
LOGFILE GROUP 1 ('/disk1/oradata/DEMO/ redolog1.log',
'/disk2/oradata/DEMO/ redolog2.log') SIZE 4M
GROUP 2 ('/disk1/oradata/DEMO/ redolog1.log',
'/disk2/oradata/DEMO/ redolog2.log') SIZE 4M
DATAFILE '/disk1/oradata/DEMO/system01. dbf'
:wq!
SQL>ALTER DATABASE OPEN;
$cp ora_2065.trc orabkup.sql
SQL>SHUTDOWN ABORT
SQL>@orabkup.sql
To rename (change the name of) a database:
-To rename a database follow the following steps:
-Trace the controlfile.
SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
-Edit the trace file as follows, replacing the keyword "REUSE" to "SET"
SQL> CREATE CONTROLFILE SET DATABASE "ACCT" RESETLOGS NOARCHIVELOG
LOGFILE GROUP 1 ('/disk1/oradata/DEMO/ redolog1.log',
'/disk2/oradata/DEMO/ redolog2.log') SIZE 4M
GROUP 2 ('/disk1/oradata/DEMO/ redolog1.log',
'/disk2/oradata/DEMO/ redolog2.log') SIZE 4M
DATAFILE '/disk1/oradata/DEMO/system01. dbf'
SQL>ALTER DATABASE OPEN RESETLOGS;
-Set the parameter db_name to the new name in the init.ora file.
-Remove the existing control files from their destination.
-Finally execute the traced controlfile to create new controlfiles.
Data Dictionary Views you can Query:
-V$CONTROLFILE
-V$CONTROLFILE_RECORD_SECTION
Control File
Control File
Smallest file in database.
Crucial information about the database:
Database Name
Creation time of Database
Location of Redo & DBA files
SCN No(System Chane Number)(Logical time stamp for transaction)
Log Sequence Number
To create a database there should be one minimum control file.
Min: 1 Control File
Max: 8 Control Files
Multiplex of Control Files-Availability
Oracle recommends 3 control files.
Demo on control file & redo log file
SQL>startup
SQL>sho parameter control_
SQL>desc v$controlfile
SQL>select * from v$controlfile;
SQL>shutdown immediate
SQL>exit
$cd ORACLE_HOME/dbs
$vi init$ORACLE_SID.ora
control_files=_____,/disk2/ oradata/xyz/control2.ctl
$cp control.ctl /disk2/oradata/xyz/control2. ctl
SQL>startup
SQL>sho parameter control_
SQL>sho parameter spfile
SQL>shutdown immediate
SQL>alter system set control_files='/disk3/oradata/ earnest3/control.ctl','/disk2/ oradata/xyz/control2.ctl' scope=spfile;
SQL>startup force
SQL>sho parameter control_
SQL>select * from v$controlfile;
SQL>shutdown immediate
SQL>exit
$cd $ORACLE_HOME/dbs
$vi init$ORACLE_SID.ora
SQL>startup
SQL>sho parameter spfile
SQL>sho parameter control_
SQL>shutdown immediate
SQL>startup mount
SQL>alter database backup controlfile to trace;
SQL>exit
$cd udump
$cp ___.trc ___/control.trc
$vi control.trc
SQL>@control.trc
SQL>select status from v$instance;
SQL>alter database open;
SQL>sho parameter control_
How to rename the database:
SQL>startup mount
SQL>alter database backup controlfile to trace;
Edit the trace file and select the second set2, remove {reuse} & place set & change the database name.
$vi control.trc
SQL>@control.trc
SQL>select name from v$database;
SQL>alter database open;
SQL>alter database open resetlogs;
SQL>archive log list
How to multiple redo log:
SQL>select * from v$log;
SQL>desc v$log
SQL>desc v$logfile
SQL>select * from v$log;
How to add a group:
SQL>alter database add logfile group 3 '/disk3/oradata/earnest3/ redo3a.log' size 5m;
SQL>select * from v$log;
SQL>alter database add logfile member '/disk2/oradata/xyz/redo3b. log' to group 3;
************************************************************
No comments:
Post a Comment