Thursday 5 November 2009

Restore and Recover Controlfile


Restore and Recover Controlfile ( Recovery Without Catalog database )

Last Controlfile Backup
------- ---- -- ---------- ----------- ------------ ---------------
44      Full    7.02M      DISK        00:00:01     22-FEB-12
        BP Key: 44   Status: AVAILABLE  Compressed: NO  Tag: TAG20120222T160900
        Piece Name: /u01/app/oracle/flash_recovery_area/FWLIVE/autobackup/2012_02_22/o1_mf_s_775930140_7nb4rx18_.bkp
  Control File Included: Ckp SCN: 10880516     Ckp time: 22-FEB-12

Simulate Loss of Controlfile

 [oracle@testbox:fwlive]$ pwd
/u01/app/oracle/oradata/fwlive
[oracle@testbox:fwlive]$ ls
control01.ctl  control02.ctl  control03.ctl  indx.dbf  paf_data.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@testbox:fwlive]$ mkdir test
[oracle@testbox:fwlive]$ mv *.ctl test/
[oracle@testbox:fwlive]$ ls -lrt test/
total 21396
-rw-r----- 1 oracle oinstall 7290880 Feb 22 16:12 control03.ctl
-rw-r----- 1 oracle oinstall 7290880 Feb 22 16:12 control02.ctl
-rw-r----- 1 oracle oinstall 7290880 Feb 22 16:12 control01.ctl
[oracle@testbox:fwlive]$ s

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 22 16:12:51 2012
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@fwlive >> select * from v$Database;
select * from v$Database
              *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/fwlive/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


SYS@fwlive >> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@testbox:fwlive]$
[oracle@testbox:fwlive]$
[oracle@testbox:fwlive]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Feb 22 16:13:10 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/fwlive/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

[oracle@testbox:fwlive]$ s
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 22 16:13:38 2012
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@fwlive >> shutdown abort;
ORACLE instance shut down.
SYS@fwlive >> exit

SYS@fwlive >> startup
ORACLE instance started.
Total System Global Area  851443712 bytes
Fixed Size      1270316 bytes
Variable Size    432016852 bytes
Database Buffers   415236096 bytes
Redo Buffers      2920448 bytes
ORA-00205: error in identifying control file, check alert log for more info


SYS@fwlive >> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


 [oracle@testbox:fwlive]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Feb 22 16:16:24 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: fwlive (not mounted)

RMAN> set DBID 2568429818;
executing command: SET DBID

RMAN>  restore controlfile from autobackup;   -- only works if your controlfile autobackup  in flash recovery area

Otherwise

RMAN>  restore controlfile from '/u01/app/oracle/flash_recovery_area/FWLIVE/autobackup/2012_02_22/o1_mf_s_775930140_7nb4rx18_.bkp'; )


Starting restore at 22-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: FWLIVE
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/FWLIVE/autobackup/2012_02_22/o1_mf_s_775930140_7nb4rx18_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/app/oracle/oradata/fwlive/control01.ctl
output filename=/u01/app/oracle/oradata/fwlive/control02.ctl
output filename=/u01/app/oracle/oradata/fwlive/control03.ctl
Finished restore at 22-FEB-12

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/22/2012 16:17:54
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/22/2012 16:18:00
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/fwlive/system01.dbf'

RMAN> recover database;

Starting recover at 22-FEB-12
Starting implicit crosscheck backup at 22-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 11 objects
Finished implicit crosscheck backup at 22-FEB-12

Starting implicit crosscheck copy at 22-FEB-12
using channel ORA_DISK_1
Crosschecked 14 objects
Finished implicit crosscheck copy at 22-FEB-12

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/FWLIVE/archivelog/2012_01_31/o1_mf_1_7_7lj7nyfj_.arc
File Name: /u01/app/oracle/flash_recovery_area/FWLIVE/archivelog/2012_01_31/o1_mf_1_8_7lj7nznk_.arc
File Name: /u01/app/oracle/flash_recovery_area/FWLIVE/archivelog/2012_01_31/o1_mf_1_6_7lj7nzwb_.arc
File Name: /u01/app/oracle/flash_recovery_area/FWLIVE/autobackup/2012_02_22/o1_mf_s_775930140_7nb4rx18_.bkp

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 43 is already on disk as file /u01/app/oracle/oradata/fwlive/redo01.log
archive log filename=/u01/app/oracle/oradata/fwlive/redo01.log thread=1 sequence=43
media recovery complete, elapsed time: 00:00:01
Finished recover at 22-FEB-12

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/22/2012 16:18:26
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

database opened

RMAN>

No comments: