Oracle control file recovery

If the Oracle control file is recovered from the backup, the following describes how to restore the Oracle control file for your reference.

What will happen to the database during the open process?

First, back up the Oracle control file, open the database, and enhance the checkpoint:

 
 

  
  
[oracle@jumper eygle]$ cp control01.ctl control01.ctl.bak  
  
  
[oracle@jumper eygle]$ sqlplus "/ as sysdba"  
  
  
 
  
  
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Aug 11 10:46:05 2006  
  
  
 
  
  
Copyright (c) 1982, 2002, Oracle Corporation.   All rights reserved.  
  
  
 
  
  
Connected to an idle instance.  
  
  
 
  
  
SQL> startup  
  
  
ORACLE instance started.  
  
  
 
  
  
Total System Global Area   139531744 bytes  
  
  
Fixed Size                    452064 bytes  
  
  
Variable Size              121634816 bytes  
  
  
Database Buffers            16777216 bytes  
  
  
Redo Buffers                  667648 bytes  
  
  
Database mounted.  
  
  
Database opened.  
  
  
SQL> alter system checkpoint;  
  
  
 
  
  
System altered.  
  
  
 
  
  
SQL> shutdown immediate;  
  
  
Database closed.  
  
  
Database dismounted.  
  
  
ORACLE instance shut down.  
  
  
SQL> exit  
  
  
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production  
  
  
With the Partitioning option  
  
  
JServer Release 9.2.0.4.0 - Production  
  
  
 
 
 

Then restore the old control file, mount the database, and dump the data file header:

 
 

  
  
[oracle@jumper eygle]$ mv control01.ctl control01.ctl.n  
  
  
[oracle@jumper eygle]$ mv control01.ctl.bak control01.ctl   
  
  
[oracle@jumper eygle]$ sqlplus "/ as sysdba"  
  
  
 
  
  
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Aug 11 10:46:50 2006  
  
  
 
  
  
Copyright (c) 1982, 2002, Oracle Corporation.   All rights reserved.  
  
  
 
  
  
Connected to an idle instance.  
  
  
 
  
  
SQL> startup mount;  
  
  
ORACLE instance started.  
  
  
 
  
  
Total System Global Area   139531744 bytes  
  
  
Fixed Size                    452064 bytes  
  
  
Variable Size              121634816 bytes  
  
  
Database Buffers            16777216 bytes  
  
  
Redo Buffers                  667648 bytes  
  
  
Database mounted.  
  
  
SQL> alter session set events 'immediate trace name file_hdrs level 10';  
  
  
 
  
  
Session altered.  
  
  
 
  
  
SQL> !  
  
  
 
 
 

Let's take a look at the information of the Oracle control file and select a file ):

 
 

  
  
DATA FILE #4:   
  
  
   (name #4) /opt/oracle/oradata/eygle/eygle01.dbf  
  
  
creation size=0 block size=8192 status=0xe head=4 tail=4 dup=1 
  
  
tablespace 4, index=4 krfil=4 prev_file=0 
  
  
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00  
  
  
Checkpoint cnt:61 scn: 0x0000.002acb1e 08/11/2006 10:44:38  
  
  
Stop scn: 0x0000.002acb1e 08/11/2006 10:44:38  
  
  
Creation Checkpointed at scn:   0x0000.0015078d 06/06/2006 09:41:54  
  
  
 
 
 

Check the data file header information again:

 
 

  
  
FILE HEADER:  
  
  
         Software vsn=153092096=0x9200000, Compatibility Vsn=134217728=0x8000000  
  
  
         Db ID=1407686520=0x53e79778, Db Name='EYGLE' 
  
  
         Activation ID=0=0x0  
  
  
         Control Seq=989=0x3dd, File size=1280=0x500  
  
  
         File Number=4, Blksiz=8192, File Type=3 DATA  
  
  
Tablespace #4 - EYGLE   rel_fn:4   
  
  
Creation    at    scn: 0x0000.0015078d 06/06/2006 09:41:54  
  
  
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0  
  
  
reset logs count:0x232bee1f scn: 0x0000.0007c781 recovered at 08/11/2006 10:11:26  
  
  
status:0x0 root dba:0x00000000 chkpt cnt: 64 ctl cnt:63  
  
  
begin-hot-backup file size: 0  
  
  
Checkpointed at scn:   0x0000.002acb98 08/11/2006 10:46:24  
  
  
 
 
 

We noticed that the chkpt cnt: 64 of the data file requires the Checkpoint cnt: 61 of the control file, that is, the control file is old.

The following error occurs when you try to open the database:

 
 

  
  
[oracle@jumper udump]$ sqlplus "/ as sysdba"  
  
  
 
  
  
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Aug 11 10:51:20 2006  
  
  
 
  
  
Copyright (c) 1982, 2002, Oracle Corporation.   All rights reserved.  
  
  
 
  
  
Connected to:  
  
  
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production  
  
  
With the Partitioning option  
  
  
JServer Release 9.2.0.4.0 - Production  
  
  
 
  
  
SQL> alter database open;  
  
  
alter database open  
  
  
*  
  
  
ERROR at line 1:  
  
  
ORA-01122: database file 1 failed verification check  
  
  
ORA-01110: data file 1: '/opt/oracle/oradata/eygle/system01.dbf'  
  
  
ORA-01207: file is more recent than controlfile - old controlfile  
  
  
 
 
 

Oracle tells us that the control file is old. In this case, we can restore the data by recreating the control file or starting from the backup of the old data.
 

Oracle single row date functions

Introduction to Oracle bitand () Functions

Use of oracle Aggregate functions

Oracle Pipeline Function usage

Use of oracle custom functions