RMAN Duplicate Gotchas - Part 1: Identifying the Media

I've recently been doing quite a few RMAN Duplicate Database jobs, in order to create dev and test systems as copies of our production server.

The production database is a 2-node RAC cluster, but each of our test systems are single-instance databases. This causes one or two odd issues which aren't always obvious until you've hit them at least once.

In this post I'll discuss the first tricky issue which I came across: Identifying the Media.

At my current job (as in most IT departments), each day the most recent backup tapes are removed from the tape robot and sent off-site to a secure storage facility. The company I work for doesn't have the resource to maintain duplexed tapes (i.e. one on-site and one off-site) and, at present, for reasons too complicated to go into here, the production system doesn't have a flash recovery area, so the backups are written to tape, and tape only.

All this means that when a test database refresh is requested, it is critical to identify the set of tapes on which the required backups reside. So, once you've decided to which point of time you wish to restore, it's necessary to identify exactly which tapes you need to recall in order to load into the robot.

1. Log into RMAN (Target and Catalog) and list the backups you're interested in. E.g:

rman catalog=rman/password@catalog_db target

RMAN> list backup;

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1063429 1.87G SBT_TAPE 00:03:36 26-NOV-08
BP Key: 1063435 Status: AVAILABLE Compressed: NO Tag: TAG20081126T020905
Handle: m9k0kn62_1_1 Media:

List of Archived Logs in backup set 1063429
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 58271 36469897611 25-NOV-08 36470958889 25-NOV-08
1 58272 36470958889 25-NOV-08 36472168625 25-NOV-08
1 58273 36472168625 25-NOV-08 36472947809 25-NOV-08
1 58274 36472947809 25-NOV-08 36473847271 25-NOV-08

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1063430 1.86G SBT_TAPE 00:03:44 26-NOV-08
BP Key: 1063436 Status: AVAILABLE Compressed: NO Tag: TAG20081126T020905
Handle: mak0kn62_1_1 Media:

List of Archived Logs in backup set 1063430
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
2 61976 36471707453 25-NOV-08 36472752913 25-NOV-08
2 61977 36472752913 25-NOV-08 36473892837 25-NOV-08
2 61978 36473892837 25-NOV-08 36475824974 25-NOV-08
2 61979 36475824974 25-NOV-08 36477342218 25-NOV-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1063514 Full 19.00M SBT_TAPE 00:00:30 26-NOV-08
BP Key: 1063521 Status: AVAILABLE Compressed: NO Tag: TAG20081126T021254
Handle: c-2982994484-20081126-01 Media:
Standby Control File Included: Ckp SCN: 36476925621 Ckp time: 25-NOV-08

[It's interesting to note that LIST BACKUP command in RMAN returns a label (in bold above) indicating that a media reference should be displayed, but I have yet to see any information listed here in any recent version of Oracle (9i or 10g on Windows or Linux).]

You can use the COMPLETED AFTER and/or COMPLETED BEFORE clauses to narrow down the list of displayed backup sets. And don't forget to list the backups starting with the most recent full, or level-0, backup, including any incrementals.

Make a note of the backup TAGs that you wish to check. At this point it's probably also sensible to make a note of which SCN you want to restore to. I find it much easier to cross-reference an SCN with the backup report than an exact point-in-time.

2. Now, Log into the RMAN catalog as the RMAN user:

sqlplus rman/password@catalog_db

3. Run the following SQL command. Enter a comma-separated, single-quoted list of tags as identified in step 1 (as many as you like - I've used two here, for simplicity):

select tag, bs_key, bp_key, start_time, media
from bp
where tag in(&list_of_tags.);

Enter value for list_of_tags: 'TAG20081126T020905','TAG20081126T021254'
old 3: where tag in(&list_of_tags.)
new 3: where tag in('TAG20081126T020905','TAG20081126T021254')

TAG BS_KEY BP_KEY START_TIM MEDIA
------------------ ------- ------- --------- ----------
TAG20081126T020905 1063426 1063432 26-NOV-08 000304
TAG20081126T020905 1063427 1063433 26-NOV-08 000310
TAG20081126T020905 1063428 1063434 26-NOV-08 000304
TAG20081126T020905 1063429 1063435 26-NOV-08 000304
TAG20081126T020905 1063430 1063436 26-NOV-08 000304
TAG20081126T021254 1063514 1063521 26-NOV-08 000304

Output is as above. The media column shows the label of the tape that the backup piece is stored on.

Now you know which tapes are going to be required for your restore, you can recall them from the storage company.

Next post I will consider the next "gotcha" on my list.