Thinking Out Loud

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>
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: