Wednesday, 7 August 2019

RMAN - Recovery of datafiles using Image copy method 12.1.0.2.0

Senario Preview :


  • Database in archivelog mode
  • No recovery catalog configured
  • One of datafile lost
Solution :
  • Earlier copy database taken at rman level
  • Validate database 
  • Restore database preview
  • offline datafile
  • restore copy
  • Switch datafile to copy
  • online datafile
  • check datafile name
[oracle@srv1 expdp]$ rman


Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 7 13:16:44 2019

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

 RMAN> connect target / sys/system123@smr1

RMAN>  BACKUP AS COPY DATABASE format '/u01/rmanbkp/copy_db_%U' TAG 'DB_COPY';

Starting backup at 07-AUG-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/smr1/idata01.dbf
output file name=/u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-IDATA_FNO-5_0nu8k2tt tag=DB_COPY RECID=2 STAMP=1015679984
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/smr1/system01.dbf
output file name=/u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-SYSTEM_FNO-1_0ou8k2vk tag=DB_COPY RECID=3 STAMP=1015680014
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/smr1/sysaux01.dbf
output file name=/u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-SYSAUX_FNO-3_0pu8k30o tag=DB_COPY RECID=4 STAMP=1015680043
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/smr1/undotbs01.dbf
output file name=/u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-UNDOTBS1_FNO-4_0qu8k31h tag=DB_COPY RECID=5 STAMP=1015680052
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/smr1/ilog01.dbf
output file name=/u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-ILOG_FNO-2_0ru8k31o tag=DB_COPY RECID=6 STAMP=1015680056
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/smr1/indx01.dbf
output file name=/u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-INDX_FNO-7_0su8k31p tag=DB_COPY RECID=7 STAMP=1015680057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/rmanbkp/copy_db_cf_D-SMR1_id-3409868754_0tu8k31r tag=DB_COPY RECID=8 STAMP=1015680059
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/smr1/users01.dbf
output file name=/u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-USERS_FNO-6_0uu8k31s tag=DB_COPY RECID=9 STAMP=1015680060
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-AUG-19
channel ORA_DISK_1: finished piece 1 at 07-AUG-19
piece handle=/u01/rmanbkp/copy_db_0vu8k31t_1_1 tag=DB_COPY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-AUG-19

RMAN>

RMAN> ALTER SYSTEM SWITCH LOGFILE;

Statement processed

RMAN> ALTER SYSTEM SWITCH LOGFILE;

Statement processed

RMAN> ALTER SYSTEM SWITCH LOGFILE;

Statement processed

RMAN> ALTER SYSTEM SWITCH LOGFILE;

Statement processed

RMAN> SELECT FILE#, NAME FROM V$DATAFILE WHERE TS# = (SELECT TS# FROM V$TABLESPACE WHERE NAME='USERS');

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         6
/u01/app/oracle/oradata/smr1/users01.dbf


RMAN>


[oracle@srv1 expdp]$ ps -ef |grep pmon
oracle    9470 15024  0 13:23 pts/1    00:00:00 grep pmon
oracle   24707     1  0 Aug06 ?        00:00:04 ora_pmon_smr1


[oracle@srv1 expdp]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 7 13:23:22 2019

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

connected to target database: SMR1 (DBID=3409868754)

RMAN> VALIDATE DATABASE;

Starting validate at 07-AUG-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
RMAN-06169: could not read file header for datafile 6 error reason 5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 08/07/2019 13:23:25
RMAN-06056: could not access datafile 6

RMAN>

RMAN> restore database preview;

Starting restore at 07-AUG-19
using channel ORA_DISK_1

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
3       1    A 07-AUG-19       2326497    07-AUG-19
        Name: /u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-SYSTEM_FNO-1_0ou8k2vk
        Tag: DB_COPY

6       2    A 07-AUG-19       2326527    07-AUG-19
        Name: /u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-ILOG_FNO-2_0ru8k31o
        Tag: DB_COPY

4       3    A 07-AUG-19       2326515    07-AUG-19
        Name: /u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-SYSAUX_FNO-3_0pu8k30o
        Tag: DB_COPY

5       4    A 07-AUG-19       2326523    07-AUG-19
        Name: /u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-UNDOTBS1_FNO-4_0qu8k31h
        Tag: DB_COPY

2       5    A 07-AUG-19       2326086    07-AUG-19
        Name: /u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-IDATA_FNO-5_0nu8k2tt
        Tag: DB_COPY

9       6    A 07-AUG-19       2326531    07-AUG-19
        Name: /u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-USERS_FNO-6_0uu8k31s
        Tag: DB_COPY

7       7    A 07-AUG-19       2326528    07-AUG-19
        Name: /u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-INDX_FNO-7_0su8k31p
        Tag: DB_COPY


List of Archived Log Copies for database with db_unique_name SMR1
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
76      1    82      A 07-AUG-19
        Name: /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_82_gno0s1t1_.arc

77      1    83      A 07-AUG-19
        Name: /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_83_gno0s2w7_.arc

78      1    84      A 07-AUG-19
        Name: /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_84_gno0s42h_.arc

79      1    85      A 07-AUG-19
        Name: /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_85_gno0s53j_.arc

RMAN-05119: recovery can not be done to a consistent state.
Media recovery start SCN is 2326086
Recovery must be done beyond SCN 2326531 to clear datafile fuzziness
Finished restore at 07-AUG-19

RMAN>


RMAN> ALTER DATABASE DATAFILE 6 offline;

Statement processed

RMAN> switch datafile 6 to copy;

datafile 6 switched to datafile copy "/u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-USERS_FNO-6_0uu8k31s"

RMAN> recover datafile 6;

Starting recover at 07-AUG-19
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 82 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_82_gno0s1t1_.arc
archived log for thread 1 with sequence 83 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_83_gno0s2w7_.arc
archived log for thread 1 with sequence 84 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_84_gno0s42h_.arc
archived log for thread 1 with sequence 85 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_85_gno0s53j_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_82_gno0s1t1_.arc thread=1 sequence=82
archived log file name=/u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_83_gno0s2w7_.arc thread=1 sequence=83
media recovery complete, elapsed time: 00:00:00
Finished recover at 07-AUG-19

RMAN> alter database datafile 6 online;

Statement processed

RMAN>

RMAN> SELECT NAME FROM V$DATAFILE WHERE TS# = (SELECT TS# FROM V$TABLESPACE WHERE NAME='USERS');



NAME
--------------------------------------------------------------------------------

/u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-USERS_FNO-6_0uu8k31s

RMAN> select name from v$datafile;



NAME
--------------------------------------------------------------------------------

/u01/app/oracle/oradata/smr1/system01.dbf


/u01/app/oracle/oradata/smr1/ilog01.dbf


/u01/app/oracle/oradata/smr1/sysaux01.dbf


/u01/app/oracle/oradata/smr1/undotbs01.dbf


/u01/app/oracle/oradata/smr1/idata01.dbf


/u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-USERS_FNO-6_0uu8k31s


/u01/app/oracle/oradata/smr1/indx01.dbf


7 rows selected

RMAN>

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)