Oracle Database User-managed control file backup and recovery

InOracleDatabase,Control FileIs very important. It is used to record and maintain databases. When the database is restored, the server process and background process need to read various backup-related information from the control file. If the control file is corrupted, the backup information will be lost. Although the use of diverse control files can prevent Control File Corruption, control files should be backed up on a regular basis due to the importance of control files. When the database configuration changes, you must back up the control file.

Commands that involve database configuration changes:

 
 

  
  
alter database [add|drop] logfile  
  
  
 
  
  
alter database [add|drop] logfile member  
  
  
 
  
  
alter database [add|drop] logfile group  
  
  
 
  
  
alter database [noarchivelog|archivelog]  
  
  
 
  
  
alter database rename file  
  
  
 
  
  
create tablespace  
  
  
 
  
  
alter tablespace [add|rename] datafile  
  
  
 
  
  
alter tablespace [read write|read only]  
  
  
 
  
  
drop tablespace 
 
 

Control file backup in three ways

1) Use the OS command to copy

1) Use the alter database command to generate a control file copy in the open state.

2) In the open state, use the alter database backup controlfile to trace command to back up the control file to the trace file

Two methods are available to control file recovery.

1) Use recover database using backup controlfile in the mount status

2) in the mount status, the trace file is generated and restored.

2-2 example:

 
 

  
  
[Oracle @ localhost ~] $ Rlsqlplus/as sysdba
  
  
 
  
  
SQL * Plus: Release 10.2.0.1.0-Production on Monday August 1 21:40:03 2011
  
  
 
  
  
Copyright (c) 1982,200 5, Oracle. All rights reserved.
  
  
 
  
  
Connected to an idle instance.
  
  
 
  
  
SQL> startup
  
  
 
  
  
ORACLE instance started.
  
  
 
  
  
Total System Global Area 528482304 bytes
  
  
 
  
  
Fixed Size 1220360 bytes
  
  
 
  
  
Variable Size 176161016 bytes
  
  
 
  
  
Database Buffers 343932928 bytes
  
  
 
  
  
Redo Buffers 7168000 bytes
  
  
 
  
  
Database mounted.
  
  
 
  
  
Database opened.
 
 

-- Generate a control file copy in the open state

 
 

  
  
SQL> alter database backup controlfile
  
  
 
  
  
2'/oracle/10g/oracle/bakup/database/oralife. ctl ';
  
  
 
  
  
Alter database backup controlfile
  
  
 
  
  
*
  
  
 
  
  
ERROR at line 1:
  
  
 
  
  
ORA-01580: error creating control backup file
  
  
 
  
  
/Oracle/10g/oracle/bakup/database/oralife. ctl
  
  
 
  
  
ORA-27038: created file already exists
  
  
 
  
  
Additional information: 1
  
  
 
  
  
SQL> alter database backup controlfile
  
  
 
  
  
2 '/oracle/10g/oracle/bakup/database/oralife. ctl' reuse; -- reuse is used to overwrite the original control file copy
  
  
 
  
  
Database altered.
 
 

-- Manually delete all control file simulated file loss

 
 

  
  
SQL> ho rm /oracle/10g/oracle/product/10.2.0/oradata/oralife/*.ctl; 
 
 

-- Use evan to log on and add data

 
 

  
  
SQL> conn evan/evan  
  
  
 
  
  
Connected.  
  
  
 
  
  
SQL> select * from t_evan;  
  
  
 
  
  
TEXT  
  
  
 
  
  
--------------------------------------------------------------------------------  
  
  
 
  
  
oracle  
  
  
 
  
  
java  
  
  
 
  
  
spring  
  
  
 
  
  
hibernate  
  
  
 
  
  
hibernate  
  
  
 
  
  
SQL> insert into t_evan values('added');  
  
  
 
  
  
1 row created.  
  
  
 
  
  
SQL> commit;  
  
  
 
  
  
Commit complete.  
  
  
 
  
  
SQL> conn / as sysdba  
  
  
 
  
  
Connected.  
  
  
 
  
  
SQL> shutdown immediate  
  
  
 
  
  
ORA-00210: cannot open the specified control file  
  
  
 
  
  
ORA-00202: control file: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl'  
  
  
 
  
  
ORA-27041: unable to open file  
  
  
 
  
  
Linux Error: 2: No such file or directory  
  
  
 
  
  
Additional information: 3  
  
  
 
  
  
SQL> shutdown abort  
  
  
 
  
  
ORACLE instance shut down. 
 
 

-- Alter_oralife.log:

 
 

  
  
Mon Aug  1 23:13:51 2011  
  
  
 
  
  
ORA-00202: control file: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl'  
  
  
 
  
  
ORA-27037: unable to obtain file status  
  
  
 
  
  
Linux Error: 2: No such file or directory  
  
  
 
  
  
Additional information: 3 
 
 

-- Copy the control file to the target path

 
 

  
  
SQL> ho cp/oracle/10g/oracle/bakup/database/oralife. ctl/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl
  
  
 
  
  
SQL> alter system set control_files = '/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl' scope = spfile; -- modify the control_files parameter to specify available control files
  
  
 
  
  
System altered.
  
  
 
  
  
SQL> startup force mount
  
  
 
  
  
ORACLE instance started.
  
  
 
  
  
Total System Global Area 528482304 bytes
  
  
 
  
  
Fixed Size 1220360 bytes
  
  
 
  
  
Variable Size 138412280 bytes
  
  
 
  
  
Database Buffers 381681664 bytes
  
  
 
  
  
Redo Buffers 7168000 bytes
  
  
 
  
  
Database mounted.
 
 

-- Generate a trace file

 
 

  
  
SQL> alter database backup controlfile to trace noresetlogs;  
  
  
 
  
  
Database altered.  
  
  
 
  
  
SELECT c.VALUE || '/' || d.instance_name || '_ora_' || a.spid || '.trc' TRACE  
  
  
 
  
  
FROM v$process a, v$session b, v$parameter c, v$instance d  
  
  
 
  
  
WHERE a.addr = b.paddr  
  
  
 
  
  
AND b.audsid = USERENV ('sessionid')  
  
  
 
  
  
AND c.NAME = 'user_dump_dest';  
  
  
 
  
  
TRACE  
  
  
 
  
  
--------------------------------------------------------------------------------  
  
  
 
  
  
/oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_4558.trc  
  
  
 
  
  
SQL> shutdown immediate  
  
  
 
  
  
ORA-01109: database not open  
  
  
 
  
  
Database dismounted.  
  
  
 
  
  
ORACLE instance shut down. 
 
 

-- Open the trace file, remove comments, execute the script in the shutdown state, and create the control file.

-- Use evan to log on and verify the data

 
 

  
  
SQL> conn evan/evan  
  
  
 
  
  
Connected.  
  
  
 
  
  
SQL> select * from t_evan;  
  
  
 
  
  
TEXT  
  
  
 
  
  
--------------------------------------------------------------------------------  
  
  
 
  
  
oracle  
  
  
 
  
  
java  
  
  
 
  
  
spring  
  
  
 
  
  
hibernate  
  
  
 
  
  
hibernate  
  
  
 
  
  
added  
  
  
 
  
  
6 rows selected. 
 
 

The data is not lost.

 
 

  
  
SQL> select name from v$controlfile;  
  
  
 
  
  
NAME  
  
  
 
  
  
--------------------------------------------------------------------------------  
  
  
 
  
  
/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl 
 
 

In this case, multiple control files need to be rebuilt to ensure the security of database configuration.

Here is an introduction to the backup and recovery of the Oracle Database Control file. I hope this introduction will bring you some benefits!