Thinking Out Loud

March 27, 2015

Be Careful when using FRA with Streams

Filed under: 11g,oracle — mdinh @ 10:12 pm

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production

select state from gv$streams_capture;

STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------
WAITING FOR REDO: LAST SCN MINED 442455793041

select thread#, sequence#, status
from v$archived_log
where 442455793041 between first_change#
and next_change# order by 1,2;

   THREAD#  SEQUENCE# S
---------- ---------- -
	 1    1070609 D
	 1    1070609 D
	 1    1070609 D
	 1    1070610 D
	 1    1070610 D
	 2    1153149 D
	 2    1153149 D
	 2    1153149 D

8 rows selected.

Who’s deleting the archived logs? Thanks to Praveen G. who figured this out. From the alert log.

WARNING: The following archived logs needed by Streams capture process
are being deleted to free space in the flash recovery area. If you need
to process these logs again, restore them from a backup to a destination
other than the flash recovery area using the following RMAN commands:
   RUN{
      # <directory/ASM diskgroup> is a location other than the
      # flash recovery area
      SET ARCHIVELOG DESTINATION TO '<directory/ASM diskgroup>';
      RESTORE ARCHIVELOG ...;
   }
Advertisement

March 7, 2015

restore validate archivelog

Filed under: 11g,RMAN — mdinh @ 7:06 pm

A common mistake I see in backup validation is not validating archivelog or Level 1 backup.

Here I will demonstrate various methods to validate achivelog.

Validate archivelog and does not list details for archivelog backup set. Too little information?

RMAN> restore validate archivelog from time “TO_DATE(‘2015-MAR-04 22:03:32′,’YYYY-MON-DD HH24:MI:SS’)”;

Starting restore at 2015-MAR-07 10:34:02
using channel ORA_DISK_1

channel ORA_DISK_1: scanning archived log /oradata/archivelog/hawklas/hawk_ba986d3b_1_871886678_245.arc
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1 tag=TAG20150307T095717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3nq17j0b_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/3nq17j0b_1_1 tag=TAG20150307T095717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 2015-MAR-07 10:34:05

Validate archivelog and list details for backupset. Too much information?

RMAN> restore validate preview archivelog from time “TO_DATE(‘2015-MAR-04 22:03:32′,’YYYY-MON-DD HH24:MI:SS’)”;

Starting restore at 2015-MAR-07 10:34:55
using channel ORA_DISK_1


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
117     45.54M     DISK        00:00:04     2015-MAR-07 09:57:21
        BP Key: 117   Status: AVAILABLE  Compressed: NO  Tag: TAG20150307T095717
        Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1

  List of Archived Logs in backup set 117
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    149     351055     2015-MAR-04 22:03:32 371415     2015-MAR-05 14:54:39
  1    150     371415     2015-MAR-05 14:54:39 372594     2015-MAR-05 15:14:04
  1    151     372594     2015-MAR-05 15:14:04 373366     2015-MAR-05 15:34:04
  1    152     373366     2015-MAR-05 15:34:04 374012     2015-MAR-05 15:54:04
  1    153     374012     2015-MAR-05 15:54:04 374560     2015-MAR-05 16:14:05
  1    154     374560     2015-MAR-05 16:14:05 375084     2015-MAR-05 16:34:04
  1    155     375084     2015-MAR-05 16:34:04 375500     2015-MAR-05 16:54:05
  1    156     375500     2015-MAR-05 16:54:05 376122     2015-MAR-05 17:14:03
  1    157     376122     2015-MAR-05 17:14:03 376539     2015-MAR-05 17:34:05
  1    158     376539     2015-MAR-05 17:34:05 376952     2015-MAR-05 17:54:05
  1    159     376952     2015-MAR-05 17:54:05 377664     2015-MAR-05 18:14:04
  1    160     377664     2015-MAR-05 18:14:04 378236     2015-MAR-05 18:34:05
  1    161     378236     2015-MAR-05 18:34:05 378694     2015-MAR-05 18:54:05
  1    162     378694     2015-MAR-05 18:54:05 379347     2015-MAR-05 19:14:04
  1    163     379347     2015-MAR-05 19:14:04 379628     2015-MAR-06 07:16:37
  1    164     379628     2015-MAR-06 07:16:37 379909     2015-MAR-06 07:26:16
  1    165     379909     2015-MAR-06 07:26:16 380968     2015-MAR-06 07:46:16
  1    166     380968     2015-MAR-06 07:46:16 381650     2015-MAR-06 08:06:16
  1    167     381650     2015-MAR-06 08:06:16 382073     2015-MAR-06 08:26:16
  1    168     382073     2015-MAR-06 08:26:16 382491     2015-MAR-06 08:46:20
  1    169     382491     2015-MAR-06 08:46:20 382987     2015-MAR-06 09:06:22
  1    170     382987     2015-MAR-06 09:06:22 383474     2015-MAR-06 09:26:22
  1    171     383474     2015-MAR-06 09:26:22 383894     2015-MAR-06 09:46:21
  1    172     383894     2015-MAR-06 09:46:21 384457     2015-MAR-06 10:06:22
  1    173     384457     2015-MAR-06 10:06:22 384876     2015-MAR-06 10:26:22
  1    174     384876     2015-MAR-06 10:26:22 385294     2015-MAR-06 10:46:28
  1    175     385294     2015-MAR-06 10:46:28 385792     2015-MAR-06 11:06:28
  1    176     385792     2015-MAR-06 11:06:28 386280     2015-MAR-06 11:26:26
  1    177     386280     2015-MAR-06 11:26:26 386698     2015-MAR-06 11:46:27
  1    178     386698     2015-MAR-06 11:46:27 387196     2015-MAR-06 12:06:28
  1    179     387196     2015-MAR-06 12:06:28 387681     2015-MAR-06 12:26:28
  1    180     387681     2015-MAR-06 12:26:28 388098     2015-MAR-06 12:46:32
  1    181     388098     2015-MAR-06 12:46:32 388673     2015-MAR-06 13:06:33
  1    182     388673     2015-MAR-06 13:06:33 389092     2015-MAR-06 13:26:34
  1    183     389092     2015-MAR-06 13:26:34 389508     2015-MAR-06 13:46:33
  1    184     389508     2015-MAR-06 13:46:33 389985     2015-MAR-06 14:06:32
  1    185     389985     2015-MAR-06 14:06:32 390472     2015-MAR-06 14:26:33
  1    186     390472     2015-MAR-06 14:26:33 390888     2015-MAR-06 14:46:32
  1    187     390888     2015-MAR-06 14:46:32 391453     2015-MAR-06 15:06:32
  1    188     391453     2015-MAR-06 15:06:32 391878     2015-MAR-06 15:26:34
  1    189     391878     2015-MAR-06 15:26:34 392298     2015-MAR-06 15:46:40
  1    190     392298     2015-MAR-06 15:46:40 392809     2015-MAR-06 16:06:40
  1    191     392809     2015-MAR-06 16:06:40 393282     2015-MAR-06 16:26:40
  1    192     393282     2015-MAR-06 16:26:40 393699     2015-MAR-06 16:46:40
  1    193     393699     2015-MAR-06 16:46:40 394186     2015-MAR-06 17:06:41
  1    194     394186     2015-MAR-06 17:06:41 394671     2015-MAR-06 17:26:46
  1    195     394671     2015-MAR-06 17:26:46 395087     2015-MAR-06 17:46:45
  1    196     395087     2015-MAR-06 17:46:45 395649     2015-MAR-06 18:06:44
  1    197     395649     2015-MAR-06 18:06:44 396072     2015-MAR-06 18:26:44
  1    198     396072     2015-MAR-06 18:26:44 396489     2015-MAR-06 18:46:46
  1    199     396489     2015-MAR-06 18:46:46 396984     2015-MAR-06 19:06:46
  1    200     396984     2015-MAR-06 19:06:46 397481     2015-MAR-06 19:26:46
  1    201     397481     2015-MAR-06 19:26:46 397897     2015-MAR-06 19:46:45
  1    202     397897     2015-MAR-06 19:46:45 398392     2015-MAR-06 20:06:45
  1    203     398392     2015-MAR-06 20:06:45 398880     2015-MAR-06 20:26:44
  1    204     398880     2015-MAR-06 20:26:44 399299     2015-MAR-06 20:46:46
  1    205     399299     2015-MAR-06 20:46:46 399775     2015-MAR-06 21:06:46
  1    206     399775     2015-MAR-06 21:06:46 400258     2015-MAR-06 21:26:45
  1    207     400258     2015-MAR-06 21:26:45 400680     2015-MAR-06 21:46:44
  1    208     400680     2015-MAR-06 21:46:44 403781     2015-MAR-06 22:06:34

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
118     30.93M     DISK        00:00:02     2015-MAR-07 09:57:33
        BP Key: 118   Status: AVAILABLE  Compressed: NO  Tag: TAG20150307T095717
        Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3nq17j0b_1_1

  List of Archived Logs in backup set 118
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    209     403781     2015-MAR-06 22:06:34 404216     2015-MAR-06 22:26:33
  1    210     404216     2015-MAR-06 22:26:33 404648     2015-MAR-06 22:46:32
  1    211     404648     2015-MAR-06 22:46:32 405189     2015-MAR-06 23:06:32
  1    212     405189     2015-MAR-06 23:06:32 405678     2015-MAR-06 23:26:35
  1    213     405678     2015-MAR-06 23:26:35 406110     2015-MAR-06 23:46:39
  1    214     406110     2015-MAR-06 23:46:39 406628     2015-MAR-07 00:06:38
  1    215     406628     2015-MAR-07 00:06:38 407191     2015-MAR-07 00:26:40
  1    216     407191     2015-MAR-07 00:26:40 407622     2015-MAR-07 00:46:39
  1    217     407622     2015-MAR-07 00:46:39 408298     2015-MAR-07 01:06:39
  1    218     408298     2015-MAR-07 01:06:39 408734     2015-MAR-07 01:26:38
  1    219     408734     2015-MAR-07 01:26:38 409167     2015-MAR-07 01:46:40
  1    220     409167     2015-MAR-07 01:46:40 409684     2015-MAR-07 02:06:39
  1    221     409684     2015-MAR-07 02:06:39 410318     2015-MAR-07 02:26:39
  1    222     410318     2015-MAR-07 02:26:39 410780     2015-MAR-07 02:46:38
  1    223     410780     2015-MAR-07 02:46:38 411462     2015-MAR-07 03:06:38
  1    224     411462     2015-MAR-07 03:06:38 411884     2015-MAR-07 03:26:40
  1    225     411884     2015-MAR-07 03:26:40 412300     2015-MAR-07 03:46:40
  1    226     412300     2015-MAR-07 03:46:40 412794     2015-MAR-07 04:06:39
  1    227     412794     2015-MAR-07 04:06:39 413315     2015-MAR-07 04:26:38
  1    228     413315     2015-MAR-07 04:26:38 413736     2015-MAR-07 04:46:38
  1    229     413736     2015-MAR-07 04:46:38 414223     2015-MAR-07 05:06:40
  1    230     414223     2015-MAR-07 05:06:40 414710     2015-MAR-07 05:26:38
  1    231     414710     2015-MAR-07 05:26:38 415134     2015-MAR-07 05:46:38
  1    232     415134     2015-MAR-07 05:46:38 417948     2015-MAR-07 06:06:26
  1    233     417948     2015-MAR-07 06:06:26 418380     2015-MAR-07 06:26:26
  1    234     418380     2015-MAR-07 06:26:26 418813     2015-MAR-07 06:46:27
  1    235     418813     2015-MAR-07 06:46:27 419405     2015-MAR-07 07:06:26
  1    236     419405     2015-MAR-07 07:06:26 419841     2015-MAR-07 07:26:28
  1    237     419841     2015-MAR-07 07:26:28 420275     2015-MAR-07 07:46:34
  1    238     420275     2015-MAR-07 07:46:34 420777     2015-MAR-07 08:06:33
  1    239     420777     2015-MAR-07 08:06:33 421312     2015-MAR-07 08:26:32
  1    240     421312     2015-MAR-07 08:26:32 421745     2015-MAR-07 08:46:32
  1    241     421745     2015-MAR-07 08:46:32 422279     2015-MAR-07 09:06:32
  1    242     422279     2015-MAR-07 09:06:32 422793     2015-MAR-07 09:26:34
  1    243     422793     2015-MAR-07 09:26:34 423233     2015-MAR-07 09:46:40
  1    244     423233     2015-MAR-07 09:46:40 423510     2015-MAR-07 09:57:16
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
245     1    245     A 2015-MAR-07 09:57:16
        Name: /oradata/archivelog/hawklas/hawk_ba986d3b_1_871886678_245.arc


channel ORA_DISK_1: scanning archived log /oradata/archivelog/hawklas/hawk_ba986d3b_1_871886678_245.arc
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1 tag=TAG20150307T095717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3nq17j0b_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/3nq17j0b_1_1 tag=TAG20150307T095717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 2015-MAR-07 10:34:59

RMAN>

Validate archivelog and list summary for backupset. Just enough information?

RMAN> restore validate preview summary archivelog from time “TO_DATE(‘2015-MAR-04 22:03:32′,’YYYY-MON-DD HH24:MI:SS’)”;

Starting restore at 2015-MAR-07 10:36:58
using channel ORA_DISK_1


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
117     B  A  A DISK        2015-MAR-07 09:57:21 1       1       NO         TAG20150307T095717
118     B  A  A DISK        2015-MAR-07 09:57:33 1       1       NO         TAG20150307T095717
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
245     1    245     A 2015-MAR-07 09:57:16
        Name: /oradata/archivelog/hawklas/hawk_ba986d3b_1_871886678_245.arc


channel ORA_DISK_1: scanning archived log /oradata/archivelog/hawklas/hawk_ba986d3b_1_871886678_245.arc
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1 tag=TAG20150307T095717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3nq17j0b_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/3nq17j0b_1_1 tag=TAG20150307T095717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 2015-MAR-07 10:37:01

RMAN> list backup of archivelog from time “TO_DATE(‘2015-MAR-04 22:03:32′,’YYYY-MON-DD HH24:MI:SS’)” summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
115     B  A  A DISK        2015-MAR-06 07:16:40 1       1       YES        TAG20150306T071638
117     B  A  A DISK        2015-MAR-07 09:57:21 1       1       NO         TAG20150307T095717
118     B  A  A DISK        2015-MAR-07 09:57:33 1       1       NO         TAG20150307T095717

Why is backupset 115 not used in restore validate?
RMAN> list backupset 115; – contains backup for seq 149-163

List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
115     3.42M      DISK        00:00:01     2015-MAR-06 07:16:40
        BP Key: 115   Status: AVAILABLE  Compressed: YES  Tag: TAG20150306T071638
        Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3kq14l6n_1_1

  List of Archived Logs in backup set 115
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    149     351055     2015-MAR-04 22:03:32 371415     2015-MAR-05 14:54:39
  1    150     371415     2015-MAR-05 14:54:39 372594     2015-MAR-05 15:14:04
  1    151     372594     2015-MAR-05 15:14:04 373366     2015-MAR-05 15:34:04
  1    152     373366     2015-MAR-05 15:34:04 374012     2015-MAR-05 15:54:04
  1    153     374012     2015-MAR-05 15:54:04 374560     2015-MAR-05 16:14:05
  1    154     374560     2015-MAR-05 16:14:05 375084     2015-MAR-05 16:34:04
  1    155     375084     2015-MAR-05 16:34:04 375500     2015-MAR-05 16:54:05
  1    156     375500     2015-MAR-05 16:54:05 376122     2015-MAR-05 17:14:03
  1    157     376122     2015-MAR-05 17:14:03 376539     2015-MAR-05 17:34:05
  1    158     376539     2015-MAR-05 17:34:05 376952     2015-MAR-05 17:54:05
  1    159     376952     2015-MAR-05 17:54:05 377664     2015-MAR-05 18:14:04
  1    160     377664     2015-MAR-05 18:14:04 378236     2015-MAR-05 18:34:05
  1    161     378236     2015-MAR-05 18:34:05 378694     2015-MAR-05 18:54:05
  1    162     378694     2015-MAR-05 18:54:05 379347     2015-MAR-05 19:14:04
  1    163     379347     2015-MAR-05 19:14:04 379628     2015-MAR-06 07:16:37

RMAN>

Backup for seq 149-163 is in backupset 115 & 117

RMAN> list backup of archivelog from sequence 149 until sequence 163 summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
115     B  A  A DISK        2015-MAR-06 07:16:40 1       1       YES        TAG20150306T071638
117     B  A  A DISK        2015-MAR-07 09:57:21 1       1       NO         TAG20150307T095717

RMAN> list backupset 117; – verified

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
117     45.54M     DISK        00:00:04     2015-MAR-07 09:57:21
        BP Key: 117   Status: AVAILABLE  Compressed: NO  Tag: TAG20150307T095717
        Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1

  List of Archived Logs in backup set 117
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    149     351055     2015-MAR-04 22:03:32 371415     2015-MAR-05 14:54:39
  1    150     371415     2015-MAR-05 14:54:39 372594     2015-MAR-05 15:14:04
  1    151     372594     2015-MAR-05 15:14:04 373366     2015-MAR-05 15:34:04
  1    152     373366     2015-MAR-05 15:34:04 374012     2015-MAR-05 15:54:04
  1    153     374012     2015-MAR-05 15:54:04 374560     2015-MAR-05 16:14:05
  1    154     374560     2015-MAR-05 16:14:05 375084     2015-MAR-05 16:34:04
  1    155     375084     2015-MAR-05 16:34:04 375500     2015-MAR-05 16:54:05
  1    156     375500     2015-MAR-05 16:54:05 376122     2015-MAR-05 17:14:03
  1    157     376122     2015-MAR-05 17:14:03 376539     2015-MAR-05 17:34:05
  1    158     376539     2015-MAR-05 17:34:05 376952     2015-MAR-05 17:54:05
  1    159     376952     2015-MAR-05 17:54:05 377664     2015-MAR-05 18:14:04
  1    160     377664     2015-MAR-05 18:14:04 378236     2015-MAR-05 18:34:05
  1    161     378236     2015-MAR-05 18:34:05 378694     2015-MAR-05 18:54:05
  1    162     378694     2015-MAR-05 18:54:05 379347     2015-MAR-05 19:14:04
  1    163     379347     2015-MAR-05 19:14:04 379628     2015-MAR-06 07:16:37
  1    164     379628     2015-MAR-06 07:16:37 379909     2015-MAR-06 07:26:16
  1    165     379909     2015-MAR-06 07:26:16 380968     2015-MAR-06 07:46:16
  1    166     380968     2015-MAR-06 07:46:16 381650     2015-MAR-06 08:06:16
  1    167     381650     2015-MAR-06 08:06:16 382073     2015-MAR-06 08:26:16
  1    168     382073     2015-MAR-06 08:26:16 382491     2015-MAR-06 08:46:20
  1    169     382491     2015-MAR-06 08:46:20 382987     2015-MAR-06 09:06:22
  1    170     382987     2015-MAR-06 09:06:22 383474     2015-MAR-06 09:26:22
  1    171     383474     2015-MAR-06 09:26:22 383894     2015-MAR-06 09:46:21
  1    172     383894     2015-MAR-06 09:46:21 384457     2015-MAR-06 10:06:22
  1    173     384457     2015-MAR-06 10:06:22 384876     2015-MAR-06 10:26:22
  1    174     384876     2015-MAR-06 10:26:22 385294     2015-MAR-06 10:46:28
  1    175     385294     2015-MAR-06 10:46:28 385792     2015-MAR-06 11:06:28
  1    176     385792     2015-MAR-06 11:06:28 386280     2015-MAR-06 11:26:26
  1    177     386280     2015-MAR-06 11:26:26 386698     2015-MAR-06 11:46:27
  1    178     386698     2015-MAR-06 11:46:27 387196     2015-MAR-06 12:06:28
  1    179     387196     2015-MAR-06 12:06:28 387681     2015-MAR-06 12:26:28
  1    180     387681     2015-MAR-06 12:26:28 388098     2015-MAR-06 12:46:32
  1    181     388098     2015-MAR-06 12:46:32 388673     2015-MAR-06 13:06:33
  1    182     388673     2015-MAR-06 13:06:33 389092     2015-MAR-06 13:26:34
  1    183     389092     2015-MAR-06 13:26:34 389508     2015-MAR-06 13:46:33
  1    184     389508     2015-MAR-06 13:46:33 389985     2015-MAR-06 14:06:32
  1    185     389985     2015-MAR-06 14:06:32 390472     2015-MAR-06 14:26:33
  1    186     390472     2015-MAR-06 14:26:33 390888     2015-MAR-06 14:46:32
  1    187     390888     2015-MAR-06 14:46:32 391453     2015-MAR-06 15:06:32
  1    188     391453     2015-MAR-06 15:06:32 391878     2015-MAR-06 15:26:34
  1    189     391878     2015-MAR-06 15:26:34 392298     2015-MAR-06 15:46:40
  1    190     392298     2015-MAR-06 15:46:40 392809     2015-MAR-06 16:06:40
  1    191     392809     2015-MAR-06 16:06:40 393282     2015-MAR-06 16:26:40
  1    192     393282     2015-MAR-06 16:26:40 393699     2015-MAR-06 16:46:40
  1    193     393699     2015-MAR-06 16:46:40 394186     2015-MAR-06 17:06:41
  1    194     394186     2015-MAR-06 17:06:41 394671     2015-MAR-06 17:26:46
  1    195     394671     2015-MAR-06 17:26:46 395087     2015-MAR-06 17:46:45
  1    196     395087     2015-MAR-06 17:46:45 395649     2015-MAR-06 18:06:44
  1    197     395649     2015-MAR-06 18:06:44 396072     2015-MAR-06 18:26:44
  1    198     396072     2015-MAR-06 18:26:44 396489     2015-MAR-06 18:46:46
  1    199     396489     2015-MAR-06 18:46:46 396984     2015-MAR-06 19:06:46
  1    200     396984     2015-MAR-06 19:06:46 397481     2015-MAR-06 19:26:46
  1    201     397481     2015-MAR-06 19:26:46 397897     2015-MAR-06 19:46:45
  1    202     397897     2015-MAR-06 19:46:45 398392     2015-MAR-06 20:06:45
  1    203     398392     2015-MAR-06 20:06:45 398880     2015-MAR-06 20:26:44
  1    204     398880     2015-MAR-06 20:26:44 399299     2015-MAR-06 20:46:46
  1    205     399299     2015-MAR-06 20:46:46 399775     2015-MAR-06 21:06:46
  1    206     399775     2015-MAR-06 21:06:46 400258     2015-MAR-06 21:26:45
  1    207     400258     2015-MAR-06 21:26:45 400680     2015-MAR-06 21:46:44
  1    208     400680     2015-MAR-06 21:46:44 403781     2015-MAR-06 22:06:34

RMAN>

March 6, 2015

RMAN-06023: no backup or copy of datafile # found to restore

Filed under: 11g,RMAN — mdinh @ 2:55 am

There’s a great note from MOS – Checklist for an RMAN Restore (Doc ID 1554636.1) but how many of you review this before performing a restore?

If you don’t then you are as guilty as I am.

RMAN> restore database until time "TO_DATE('2015-MAR-04 19:53:54','YYYY-MON-DD HH24:MI:SS')" preview summary;

Starting restore at 2015-MAR-05 18:03:28
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK

datafile 5 will be created automatically during restore operation
datafile 6 will be created automatically during restore operation
datafile 7 will be created automatically during restore operation
datafile 8 will be created automatically during restore operation
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/05/2015 18:03:28
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

Let’s check backup for datafile 5,6,7,8 – looks good

RMAN> list backup of datafile 5,6,7,8 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
98      B  0  A DISK        2015-MAR-04 19:54:24 1       1       YES        LEVEL0
99      B  0  A DISK        2015-MAR-04 19:54:26 1       1       YES        LEVEL0
100     B  0  A DISK        2015-MAR-04 19:54:27 1       1       YES        LEVEL0
101     B  0  A DISK        2015-MAR-04 19:54:29 1       1       YES        LEVEL0
109     B  1  A DISK        2015-MAR-04 22:03:26 1       1       YES        LEVEL1
110     B  1  A DISK        2015-MAR-04 22:03:28 1       1       YES        LEVEL1
111     B  1  A DISK        2015-MAR-04 22:03:29 1       1       YES        LEVEL1
112     B  1  A DISK        2015-MAR-04 22:03:30 1       1       YES        LEVEL1

RMAN>

RMAN> list backup of datafile 1 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
94      B  0  A DISK        2015-MAR-04 19:54:13 1       1       YES        LEVEL0
105     B  1  A DISK        2015-MAR-04 22:02:57 1       1       YES        LEVEL1

RMAN>

The until time was not far enough to include backup for data 1 ..

RMAN> restore database until time "TO_DATE('2015-MAR-04 19:54:30','YYYY-MON-DD HH24:MI:SS')" preview summary;

Starting restore at 2015-MAR-05 18:35:56
using channel ORA_DISK_1


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
94      B  0  A DISK        2015-MAR-04 19:54:12 1       1       YES        LEVEL0
96      B  0  A DISK        2015-MAR-04 19:54:19 1       1       YES        LEVEL0
95      B  0  A DISK        2015-MAR-04 19:54:16 1       1       YES        LEVEL0
97      B  0  A DISK        2015-MAR-04 19:54:22 1       1       YES        LEVEL0
98      B  0  A DISK        2015-MAR-04 19:54:23 1       1       YES        LEVEL0
99      B  0  A DISK        2015-MAR-04 19:54:26 1       1       YES        LEVEL0
100     B  0  A DISK        2015-MAR-04 19:54:27 1       1       YES        LEVEL0
101     B  0  A DISK        2015-MAR-04 19:54:29 1       1       YES        LEVEL0


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
102     B  A  A DISK        2015-MAR-04 19:54:30 1       1       YES        ARCHIVELOG
Media recovery start SCN is 342495
Recovery must be done beyond SCN 342508 to clear datafile fuzziness
Finished restore at 2015-MAR-05 18:35:56

Where on earth did I get the time “2015-MAR-04 19:54:30” to begin with?

I used Low Time from ARCHIVELOG backup which was not sufficient for demonstration purpose.

RMAN> list backupset 102;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
102     3.50K      DISK        00:00:00     2015-MAR-04 19:54:30
        BP Key: 102   Status: AVAILABLE  Compressed: YES  Tag: ARCHIVELOG
        Piece Name: /oradata/backup/arc_HAWK_3130551611_20150304_37q10orm_1_1

  List of Archived Logs in backup set 102
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    140     342477     2015-MAR-04 19:53:54 342514     2015-MAR-04 19:54:30

RMAN>

Instead, use “Next Time” which is also the same as “Completion Time” from list backup summary;

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
90      B  A  A DISK        2015-MAR-04 19:53:58 1       1       YES        ARCHIVELOG
91      B  A  A DISK        2015-MAR-04 19:54:00 1       1       YES        ARCHIVELOG
92      B  A  A DISK        2015-MAR-04 19:54:02 1       1       YES        ARCHIVELOG
93      B  A  A DISK        2015-MAR-04 19:54:05 1       1       YES        ARCHIVELOG
94      B  0  A DISK        2015-MAR-04 19:54:13 1       1       YES        LEVEL0
95      B  0  A DISK        2015-MAR-04 19:54:16 1       1       YES        LEVEL0
96      B  0  A DISK        2015-MAR-04 19:54:20 1       1       YES        LEVEL0
97      B  0  A DISK        2015-MAR-04 19:54:22 1       1       YES        LEVEL0
98      B  0  A DISK        2015-MAR-04 19:54:24 1       1       YES        LEVEL0
99      B  0  A DISK        2015-MAR-04 19:54:26 1       1       YES        LEVEL0
100     B  0  A DISK        2015-MAR-04 19:54:27 1       1       YES        LEVEL0
101     B  0  A DISK        2015-MAR-04 19:54:29 1       1       YES        LEVEL0
102     B  A  A DISK        2015-MAR-04 19:54:30 1       1       YES        ARCHIVELOG
103     B  F  A DISK        2015-MAR-04 19:54:34 1       1       NO         TAG20150304T195432
104     B  A  A DISK        2015-MAR-04 22:02:49 1       1       YES        ARCHIVELOG
105     B  1  A DISK        2015-MAR-04 22:02:57 1       1       YES        LEVEL1
106     B  1  A DISK        2015-MAR-04 22:03:04 1       1       YES        LEVEL1
107     B  1  A DISK        2015-MAR-04 22:03:18 1       1       YES        LEVEL1
108     B  1  A DISK        2015-MAR-04 22:03:24 1       1       YES        LEVEL1
109     B  1  A DISK        2015-MAR-04 22:03:26 1       1       YES        LEVEL1
110     B  1  A DISK        2015-MAR-04 22:03:28 1       1       YES        LEVEL1
111     B  1  A DISK        2015-MAR-04 22:03:29 1       1       YES        LEVEL1
112     B  1  A DISK        2015-MAR-04 22:03:30 1       1       YES        LEVEL1
113     B  A  A DISK        2015-MAR-04 22:03:32 1       1       YES        ARCHIVELOG
114     B  F  A DISK        2015-MAR-04 22:03:35 1       1       NO         TAG20150304T220333

RMAN>

Blog at WordPress.com.