restore the control file from different machine

This note is maily tell you how to restore the controlfile from different machine..


//construct a failure case of losing controlfile

[oracle@hundsun trade]$ ls -lrt
total 3118452
-rw-r–r– 1 oracle dba        930 Apr 13 06:46 init_trade.ora
-rw-r—– 1 oracle dba  524296192 Apr 16 22:00 temp01.dbf
-rw-r—– 1 oracle dba    5251072 May  7 21:07 users01.dbf
-rw-r—– 1 oracle dba 2147491840 May  7 21:07 TRADE.dbf
-rw-r—– 1 oracle dba   52429312 May  7 21:07 redo03.log
-rw-r—– 1 oracle dba   52429312 May  7 21:07 redo02.log
-rw-r—– 1 oracle dba  335552512 May  7 21:18 system01.dbf
-rw-r—– 1 oracle dba  241180672 May  7 21:20 sysaux01.dbf
-rw-r—– 1 oracle dba  277880832 May  7 21:20 undotbs01.dbf
-rw-r—– 1 oracle dba   52429312 May  7 21:20 redo01.log
-rw-r—– 1 oracle dba    7061504 May  7 21:20 control03.ctl
-rw-r—– 1 oracle dba    7061504 May  7 21:20 control02.ctl
-rw-r—– 1 oracle dba    7061504 May  7 21:20 control01.ctl
[oracle@hundsun trade]$ rm -rf control01.ctl
[oracle@hundsun trade]$ rm -rf control02.ctl
[oracle@hundsun trade]$ rm -rf control03.ctl


[oracle@hundsun trade]$ ls -lrt
total 3097728
-rw-r–r– 1 oracle dba        930 Apr 13 06:46 init_trade.ora
-rw-r—– 1 oracle dba  524296192 Apr 16 22:00 temp01.dbf
-rw-r—– 1 oracle dba    5251072 May  7 21:07 users01.dbf
-rw-r—– 1 oracle dba 2147491840 May  7 21:07 TRADE.dbf
-rw-r—– 1 oracle dba   52429312 May  7 21:07 redo03.log
-rw-r—– 1 oracle dba   52429312 May  7 21:07 redo02.log
-rw-r—– 1 oracle dba  335552512 May  7 21:18 system01.dbf
-rw-r—– 1 oracle dba  241180672 May  7 21:20 sysaux01.dbf
-rw-r—– 1 oracle dba  277880832 May  7 21:20 undotbs01.dbf
-rw-r—– 1 oracle dba   52429312 May  7 21:20 redo01.log
 

 

//when oracle try to startup, it meets the error message of losing the controlfiles

SQL> startup
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  2095736 bytes
Variable Size             239076744 bytes
Database Buffers           41943040 bytes
Redo Buffers                6291456 bytes
ORA-00205: error in identifying control file, check alert log for more info

//check out the alert log for more informations

here are the details in alert log

***********************************************************
PMON started with pid=2, OS id=3211
PSP0 started with pid=3, OS id=3213
MMAN started with pid=4, OS id=3215
LGWR started with pid=6, OS id=3219
CKPT started with pid=7, OS id=3221
SMON started with pid=8, OS id=3223
DBW0 started with pid=5, OS id=3217
RECO started with pid=9, OS id=3225
CJQ0 started with pid=10, OS id=3227
MMON started with pid=11, OS id=3229
MMNL started with pid=12, OS id=3231
Tue May 07 18:19:17 CST 2013
ALTER DATABASE   MOUNT
Tue May 07 18:19:17 CST 2013
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oradata/trade/control01.ctl'
ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue May 07 18:19:17 CST 2013
ORA-205 signalled during: ALTER DATABASE   MOUNT…

*************************************************************

–how to solve this problem?—–

// the basic idea is to construct the creation script of control file and recreate the control file to startup the oracle instance

1.shutdown the instance

SQL> select status from v$instance;

STATUS
————————
STARTED

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> quit


2.generate the trace file to recreate the controlfile

SQL> alter database backup controlfile to trace as 'c:control.ctl';

check out the details of trace file c:control.ctl and use the "RESETLOGS" section


*************************************************************************
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CESHI" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 1168
LOGFILE
  GROUP 1 'C:ORACLEPRODUCT10.2.0ORADATACESHIREDO01.LOG'  SIZE 50M,
  GROUP 2 'C:ORACLEPRODUCT10.2.0ORADATACESHIREDO02.LOG'  SIZE 50M,
  GROUP 3 'C:ORACLEPRODUCT10.2.0ORADATACESHIREDO03.LOG'  SIZE 50M
— STANDBY LOGFILE

DATAFILE
  'C:ORACLEPRODUCT10.2.0ORADATACESHISYSTEM01.DBF',
  'C:ORACLEPRODUCT10.2.0ORADATACESHIUNDOTBS01.DBF',
  'C:ORACLEPRODUCT10.2.0ORADATACESHISYSAUX01.DBF',
  'C:ORACLEPRODUCT10.2.0ORADATACESHIUSERS01.DBF',
  'C:ORACLEPRODUCT10.2.0ORADATACESHIUSERS02.DBF',
  'D:USERS03.DBF',
  'D:USERS04.DBF',
  'D:USERS05.DBF'
CHARACTER SET ZHS16GBK
;
**************************************************************************

3.change the path and filename to meet the environments

*************************************************************

CREATE CONTROLFILE REUSE DATABASE "trade" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 1168
LOGFILE
  GROUP 1 '/oradata/trade/redo01.log'  SIZE 50M,
  GROUP 2 '/oradata/trade/redo02.log'  SIZE 50M,
  GROUP 3 '/oradata/trade/redo03.log'  SIZE 50M
DATAFILE
  '/oradata/trade/system01.dbf',
  '/oradata/trade/undotbs01.dbf',
  '/oradata/trade/sysaux01.dbf',
  '/oradata/trade/users01.dbf',
  '/oradata/trade/TRADE.dbf'
CHARACTER SET ZHS16GBK;

**************************************************************

In this script, we had the redo,system,sysaux,users,trade but don't have the section do with tempfiles

I'll handle the temp tablespace later when the instance was open

4.start the instance to nomount status

[oracle@hundsun trade]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 – Production on Tue May 7 22:01:07 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  2095736 bytes
Variable Size             239076744 bytes
Database Buffers           41943040 bytes
Redo Buffers                6291456 bytes
SQL> select status from v$instance;

STATUS
————————
STARTED

//create the controlfile using the constructed scripts

SQL> CREATE CONTROLFILE REUSE DATABASE "trade" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
  2    3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
    MAXLOGHISTORY 1168
LOGFILE
  GROUP 1 '/oradata/trade/redo01.log'  SIZE 50M,
  GROUP 2 '/oradata/trade/redo02.log'  SIZE 50M,
  GROUP 3 '/oradata/trade/redo03.log'  SIZE 50M
DATAFILE
  '/oradata/trade/system01.dbf',
  '/oradata/trade/undotbs01.dbf',
  '/oradata/trade/sysaux01.dbf',
  6    7    8    9   10   11   12   13   14   15    '/oradata/trade/users01.dbf',
  '/oradata/trade/TRADE.dbf'
 16   17  CHARACTER SET ZHS16GBK;

Control file created.

//check out the controlfiles just created

[oracle@hundsun trade]$ ls -lrth control*
-rw-r—– 1 oracle dba 8.2M May  7 22:05 control03.ctl
-rw-r—– 1 oracle dba 8.2M May  7 22:05 control02.ctl
-rw-r—– 1 oracle dba 8.2M May  7 22:05 control01.ctl


//recover database using backup controlfile until cancel


SQL> set linesize 160
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2957843 generated at 05/07/2013 21:07:07 needed for thread 1
ORA-00289: suggestion : /home/oracle/flash_recovery_area/TRADE/archivelog/2013_05_07/o1_mf_1_136_%u_.arc
ORA-00280: change 2957843 for thread 1 is in sequence #136

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/trade/redo01.log
Log applied.
Media recovery complete.

–try to open the instance with resetlogs

SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS
————
OPEN

5.handle the tempfiles

//reuse the tempfile

SQL> alter tablespace temp add tempfile '/oradata/trade/temp01.dbf' reuse;

Tablespace altered.


SQL> l
  1* select file_name,tablespace_name,status from dba_temp_files
SQL> /

FILE_NAME                                TABLESPACE_NAME                STATUS
—————————————- —————————— ———
/oradata/trade/temp01.dbf                TEMP                           AVAILABLE

–EOF–