Thursday, January 24, 2013

Use RMAN to recover a data file

As I followed wrong command " dd if=/u01/oradata/DG/users01.dbf of=/u01/oradata/DG/users01.dbf count=1 bs=8192 skip=130 seek=129 conv=notrunc".  , mentioned in my previous post

How to corrupt a specific block


So I play with RMAN to restore and recover it .
1. The file id is 4.
SYS@DG> select file_id, file_name from dba_data_files;

   FILE_ID
----------
FILE_NAME
-------------------------------------------------------------------------------------------------------------------                      -----------------
         1
/u01/oradata/DG/system01.dbf

         2
/u01/oradata/DG/sysaux01.dbf

         3
/u01/oradata/DG/undotbs01.dbf

         4
/u01/oradata/DG/users01.dbf

         5
/u01/oradata/DG/dg1_tbs01.dbf

2. Use RMAN to restore and recover file id 4.

2.1 When DB is down, RMAN can't continue.

orarac1poc:DG:/u01/oradata/DG> rman target / catalog rman/rman123@RCAT

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jan 23 18:26:11 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)
connected to recovery catalog database

RMAN> restore datafile 4;

Starting restore at Jan 23 2013 18:26:17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/23/2013 18:26:17
RMAN-12010: automatic channel allocation initialization failed
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

RMAN> exit

2.2 Then I mount the DB.

rarac1poc:DG:/u01/oradata/DG> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 23 18:26:32 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@DG> startup mount
ORACLE instance started.

Total System Global Area  680607744 bytes
Fixed Size                  2229600 bytes
Variable Size             419433120 bytes
Database Buffers          251658240 bytes
Redo Buffers                7286784 bytes
Database mounted.
SYS@DG> exit


orarac1poc:DG:/u01/oradata/DG> rman target / catalog rman/rman123@RCAT

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jan 23 18:26:52 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DG (DBID=1716975309, not open)
connected to recovery catalog database

RMAN> restore datafile 4;

Starting restore at Jan 23 2013 18:26:56
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=140 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/DG/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oradata/DG/FRA/DG/backupset/2013_01_23/o1_mf_nnndf_TAG20130123T173124_8hzchg8t_.bkp
channel ORA_DISK_1: piece handle=/u01/oradata/DG/FRA/DG/backupset/2013_01_23/o1_mf_nnndf_TAG20130123T173124_8hzchg8t_.bkp tag=TAG20130123T173124
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at Jan 23 2013 18:27:01

RMAN> exit


Recovery Manager complete.


2.3.1 Check files require recovery.

SYS@DG> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
         4 ONLINE  ONLINE                                                                       3814884 23-JAN-13

2.3.2 Continue the recovery and open database.

orarac1poc:DG:/u01/oradata/DG> rman target / catalog rman/rman123@RCAT

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jan 23 18:28:56 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DG (DBID=1716975309, not open)
connected to recovery catalog database

RMAN> recover datafile 4;

Starting recover at Jan 23 2013 18:29:05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=146 device type=DISK

starting media recovery

archived log for thread 1 with sequence 38 is already on disk as file /u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_38_8hzckknr_.arc
archived log for thread 1 with sequence 39 is already on disk as file /u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_39_8hzfnpjq_.arc
archived log for thread 1 with sequence 40 is already on disk as file /u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_40_8hzfnsm6_.arc
archived log for thread 1 with sequence 41 is already on disk as file /u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_41_8hzggvnq_.arc
archived log for thread 1 with sequence 42 is already on disk as file /u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_42_8hzggyqt_.arc
archived log for thread 1 with sequence 43 is already on disk as file /u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_43_8hzgljgm_.arc
archived log for thread 1 with sequence 44 is already on disk as file /u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_44_8hzglmdj_.arc
archived log file name=/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_38_8hzckknr_.arc thread=1 sequence=38
archived log file name=/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_39_8hzfnpjq_.arc thread=1 sequence=39
archived log file name=/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_40_8hzfnsm6_.arc thread=1 sequence=40
archived log file name=/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_41_8hzggvnq_.arc thread=1 sequence=41
archived log file name=/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_42_8hzggyqt_.arc thread=1 sequence=42
media recovery complete, elapsed time: 00:00:00
Finished recover at Jan 23 2013 18:29:07

RMAN> sql "alter database open " ;

sql statement: alter database open

2.4 The corresonding info in alert.log

Completed: ALTER DATABASE   MOUNT
Starting Data Guard Broker (DMON)
Wed Jan 23 18:26:53 2013
INSV started with pid=29, OS id=28064
Wed Jan 23 18:26:56 2013
NSV1 started with pid=30, OS id=28072
Wed Jan 23 18:27:00 2013
Checker run found 1 new persistent data failures
Wed Jan 23 18:27:00 2013
RSM0 started with pid=35, OS id=28160
Wed Jan 23 18:27:01 2013
Full restore complete of datafile 4 /u01/oradata/DG/users01.dbf.  Elapsed time: 0:00:01
  checkpoint is 3814884
  last deallocation scn is 3
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='DG';
ALTER SYSTEM SET log_archive_format='DG_%t_%s_%r.arc' SCOPE=SPFILE SID='DG';
Wed Jan 23 18:29:07 2013
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
 datafile 4
Media Recovery Start
Serial Media Recovery started
ORA-279 signalled during: alter database recover if needed
 datafile 4
...
alter database recover logfile '/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_38_8hzckknr_.arc'
Media Recovery Log /u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_38_8hzckknr_.arc
ORA-279 signalled during: alter database recover logfile '/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_38_8hzckknr_.arc'...
alter database recover logfile '/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_39_8hzfnpjq_.arc'
Media Recovery Log /u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_39_8hzfnpjq_.arc
ORA-279 signalled during: alter database recover logfile '/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_39_8hzfnpjq_.arc'...
alter database recover logfile '/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_40_8hzfnsm6_.arc'
Media Recovery Log /u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_40_8hzfnsm6_.arc
ORA-279 signalled during: alter database recover logfile '/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_40_8hzfnsm6_.arc'...
alter database recover logfile '/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_41_8hzggvnq_.arc'
Media Recovery Log /u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_41_8hzggvnq_.arc
ORA-279 signalled during: alter database recover logfile '/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_41_8hzggvnq_.arc'...
alter database recover logfile '/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_42_8hzggyqt_.arc'
Media Recovery Log /u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_42_8hzggyqt_.arc
Recovery of Online Redo Log: Thread 1 Group 1 Seq 43 Reading mem 0
  Mem# 0: /u01/oradata/DG/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 44 Reading mem 0
  Mem# 0: /u01/oradata/DG/redo02.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 45 Reading mem 0
  Mem# 0: /u01/oradata/DG/redo03.log
Media Recovery Complete (DG)
Completed: alter database recover logfile '/u01/oradata/DG/FRA/DG/archivelog/2013_01_23/o1_mf_1_42_8hzggyqt_.arc'
Wed Jan 23 18:29:40 2013
alter database open