Labels

Tuesday, December 7, 2010

Recover controlfile using RMAN with autobackup option

Errors:-1
SQL> shu immediate
ORA-00227: corrupt block detected in controlfile: (block 1, # blocks 1)
ORA-00202: controlfile: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\CONTROL01.CTL'

Errors:-2
SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

Recovery the controfile from auto backup using RMAN.Here database is in nomount stage
Oracle 10.1.0.2.0
Windows XP
1)Connect to target as sys

D:\Documents and Settings\Administrator>rman target=sys

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

target database Password:
connected to target database: rajastan (not mounted)

RMAN>

2)restore the controlfile from auto backup location.

RMAN> restore controlfile from autobackup;

Starting restore at 07-DEC-10
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK

recovery area destination: C:\oracle\product\10.1.0\flash_recovery_area
database name (or lock name space) used for search: RAJASTAN
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found:

C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\RAJASTAN\AUTOBACKUP\2010_12_07\O1_MF_S_737136120_6HW391PG_.BKP
channel ORA_DISK_1: controlfile restore from autobackup complete
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\CONTROL03.CTL
Finished restore at 07-DEC-10
3)Mount the database as db is in nomount stage.
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

4)Recover the controlfile from auto backup location.

RMAN> restore controlfile from autobackup;

Starting restore at 07-DEC-10
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK

recovery area destination: C:\oracle\product\10.1.0\flash_recovery_area
database name (or lock name space) used for search: RAJASTAN
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\R
channel ORA_DISK_1: controlfile restore from autobackup complete
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\CONTROL03.CTL
Finished restore at 07-DEC-10


5)restore the database
rman>restore database;
RMAN> restore database;

Starting restore at 07-DEC-10
Starting implicit crosscheck backup at 07-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 07-DEC-10

Starting implicit crosscheck copy at 07-DEC-10
using channel ORA_DISK_1
Finished implicit crosscheck copy at 07-DEC-10

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

List of Cataloged Files
=======================
File Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\RAJASTAN\AUTOBACKUP\2010_12_07\O1_MF_S_7

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\USERS01.DBF
restoring datafile 00006 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\RAJ02.DBF
restoring datafile 00007 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\RMAN01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\RAJASTAN\BACKUPSET\2010_12_07\O1_MF_NN
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\RAJ01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\RAJASTAN\BACKUPSET\2010_12_07\O1_MF_NN
channel ORA_DISK_1: restore complete
Finished restore at 07-DEC-10
6)recover the database

RMAN> recover database;

Starting recover at 07-DEC-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 9 is already on disk as file C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\REDO02.LOG
archive log filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\RAJASTAN\REDO02.LOG thread=1 sequence=9
media recovery complete
Finished recover at 07-DEC-10


7)restart the database
SQL> shu immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


8)open the database in reset log mode

SQL> alter database open resetlogs;

Database altered.

No comments:

Post a Comment