3 Familiar Control file Commands for DBAs

Control file in Oracle is a binary file, which keeps record of physical database structure. Control file has information related to Database datafile, redo log files, checkpoint information and current log sequence number. This file is very frequently accessed by database for different -2 reasons like accessing path of data file, updating redo log sequence number etc.

Control files are created at time of database creation, If control file of a database is not accessible database will stop working immediately. By seeing critical need of control file, I will suggest my readers to have at lest two copies of database control file and make sure these copies are on different physical storage devices.

So that, in case if one physical storage device is not accessible, we can still get control file from other Physical device. When a database instance start it start from pfile or spfile, next is database control file, which has all database related information as discussed above. If control file of database is not accessible, DBA can't start the database. Here are.


3 useful Control file commands for DBAs


1. Control file Location: DBA has to copy control files from one location to another location. In that case, to find control file path you can either look into pfile or v$controlfile view.

If database is not up and running DBA can field control file path from database pfile.

*.compatible='11.2.0.3.0'
*.control_files='+DATA_DMORL/dbm4/controlfile/current.256.809881659','+DATA_DMORL/dbm4/controlfile/current.257.809881659'
*.db_block_checking='false'
*.db_block_checksum='typical'


If database is up and running use v$controlfile view to find control file location.

SQL> select * from v$controlfile;

STATUS  NAME                                               IS_ BLOCK_SIZE FILE_SIZE_BLKS
------ -------------------------------------------------- --- ---------- --------------
        +DATA_DMORL/dbm4/controlfile/current.256.809881659 NO       16384          10184
        +DATA_DMORL/dbm4/controlfile/current.257.809881659 NO       16384          10184


In above outputs, I am using ASM instance for database, DBA can use same way for both ASM or Non ASM instances.


2. Control file Backup: Since, Database functionality entirely depends on control fils, so DBA has to take control file backup at regular interval and every time after any one of these following operations.

    A. Adding, dropping, or renaming datafiles
    B. Adding or dropping a tablespace, or altering the read/write state of the tablespace
    C. Adding or dropping redo log files or groups

DBA can make duplication control file using control file backups, Below are steps to take backup of control file.
SQL> alter database backup controlfile to '/export/home/oracle/control.bkp';
Database altered.

SQL> !

bash-4.1$ cd /export/home/oracle
bash-4.1$ ls *.bkp
control.bkp

DBA can also take current control file backup using RMAN
RMAN> backup as copy current controlfile format  '/home/oracle/control.bkp';

Starting backup at 09-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 instance=odarm2 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/home/oracle/control.bkp tag=TAG20130709T155845 RECID=1 STAMP=820339126
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 09-JUL-13

RMAN> exit

[oracle@odarm2 ~]$ pwd
/home/oracle

[oracle@odarm2 ~]$ ls *.bkp
control.bkp


3. Recovering a Control File Using a Current Copy: Suddenly, mount point having control files is not accessible due to some hardware failure. In that case your database will not work. To resolve this quickly. DBA can copy existing current control file to new location and update control_file parameter accordingly. Here, I am providing a case to change control file location.

Suppose we have two control file like below and Database loose '/etc/oracle/oradat/orcl/control01.ctl'. DBA has to shutdown the database and copy existing control file '/etc/oracle/fast_recovery_area/orcl/control02.ctl' to an new location.

Steps to follow:

1. Shutdown database.
2. Copy existing or backup control file to new location using OS command.
3. Change path in Pfile.
4. Start database with Pfile.
5. Create spfile from pfile.
6. shutdown and start database to use spfile.

SQL> select * from v$controlfile;

STATUS  NAME                                               IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------------------------------------- --- ---------- --------------
        /etc/oracle/oradata/orcl/control01.ctl             NO       16384            594
        /etc/oracle/fast_recovery_area/orcl/control02.ctl  NO       16384            594


SQL> shutdown immediate
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/etc/oracle/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

[oracle@database ~]$ ps -ef | grep smon

oracle    3112     1  0 21:20 ?        00:00:00 asm_smon_+ASM
oracle    3491     1  0 21:25 ?        00:00:00 ora_smon_orcl
oracle    4061  3233  0 22:10 pts/1    00:00:00 grep smon

[oracle@database ~]$ kill -9 3491

[oracle@database ~]$ cp /etc/oracle/fast_recovery_area/orcl/control02.ctl /etc/oracle/oradata/control01.ctl

[oracle@database ~]$ ls /etc/oracle/oradata/control01.ctl
/etc/oracle/oradata/control01.ctl

[oracle@database ~]$ vi /etc/oracle/oracle/dbs/initorcl.ora

SQL> startup pfile ='$ORACLE_HOME/dbs/initorcl.ora'

ORACLE instance started.
Total System Global Area  610992128 bytes
Fixed Size                  1345880 bytes
Variable Size             406849192 bytes
Database Buffers          197132288 bytes
Redo Buffers                5664768 bytes
Database mounted.
Database opened.

SQL> create spfile from pfile;
File created.

SQL> shutdown immediate

SQL> startup 

SQL> select * from v$controlfile;
STATUS  NAME                                     IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ---------------------------------------- --- ---------- --------------
        /etc/oracle/oradata/control01.ctl        NO       16384            594
        /etc/oracle/fast_recovery_area/orcl/cont NO       16384            594
        rol02.ctl

From above output, DBA can see control file is restored at "/etc/oracle/oradata/control01.ctl" new location. There could by n number of scenarios for loose of control files.  Same way you can also file Spfile Commands and tablespace operations.

Please share few more commands if you know !!

No comments:

Post a Comment