RE: RMAN Recovery - I'm missing a step or something

From: Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com>
Date: Tue, 8 Nov 2011 08:54:46 -0600
Message-ID: <C5533BD628A9524496D63801704AE56D6A333D5814_at_SPOBMEXC14.adprod.directory>



Well, that *is* curious....
If I leave off the seq #, the restore begins correctly.

Have I forgotten some piece of Oracle recovery basics? I thought I should be able to recover to any log sequence# in the backup using "...until sequence xxx"?

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205

"Quality is never an accident; it is always the result of intelligent effort." -- John Ruskin (English Writer 1819-1900)

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

From: Taylor, Chris David
Sent: Tuesday, November 08, 2011 8:50 AM To: 'Howard Latham'
Cc: 'oracle-l_at_freelists.org'
Subject: RE: RMAN Recovery - I'm missing a step or something

My apologies.

Oracle 10.2.0.4 Bundle 24 on Windows Server 2003 64-bit

I have not tried without sequence #...I'll try that now.

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205

"Quality is never an accident; it is always the result of intelligent effort." -- John Ruskin (English Writer 1819-1900)

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

From: Howard Latham [mailto:howard.latham_at_gmail.com] Sent: Tuesday, November 08, 2011 8:49 AM To: Taylor, Chris David
Cc: oracle-l_at_freelists.org
Subject: Re: RMAN Recovery - I'm missing a step or something

What version of DB and OS?
Have you tried not specifying a point in time /sequence number?

On 8 November 2011 14:41, Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com<mailto:ChrisDavid.Taylor_at_ingrambarge.com>> wrote: Here's the scenario:
We have a saved backup on disk of a test database we like to recover to from time to time. I have the controlfile autobackup, and the necessary backup pieces. Here's the file names on disk that I have:

24MOUU55_1_1                             10/12/2011 10:47 PM
25MOV0S5_1_1                              10/12/2011 11:31 PM
26MOV3F5_1_1                              10/12/2011 11:31 pm
27MOV3FC_1_1                              10/12/2011 11:32 pm
C-609642508-20111013-00 {controlfile backup} 10/12/2011 11:32pm

Step 1: Destroy test database info as it is no longer needed

Shutdown test database, delete controlfiles, archivelogs, redologs

Step 2: startup NOMOUNT target db

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 4194304000<tel:4194304000> bytes

Fixed Size                  2071056 bytes
Variable Size            2080376304 bytes
Database Buffers         2097152000<tel:2097152000> bytes
Redo Buffers               14704640 bytes
SQL> exit

Step 3: Connect to target db

C:\>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Nov 8 08:24:21 2011

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

connected to target database: TST3 (not mounted)

RMAN> Step 4: Restore Controlfile from backup

RMAN> restore controlfile from 'z:\backup_loc\db_name\C-609642508-20111013-00';

Starting restore at 08-NOV-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid22 devtype=DISK

channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output filename={controlfile_location #1}\CONTROL01.CTL output filename={controlfile_location #2}\CONTROL02.CTL Finished restore at 08-NOV-11
RMAN> Step 5: Mount Database

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1
RMAN> Step 6: List Backup

RMAN> list backup; (I replaced drive letters and db name)

List of Backup Sets



BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    19.36M     DISK        00:00:01     04-OCT-11
       BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20111004T130512
       Piece Name: Z:\backup_loc\db_name\C-609642508-20111004-01
 Control File Included: Ckp SCN: 14011024364 Ckp time: 04-OCT-11  SPFILE Included: Modification time: 04-OCT-11

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------

47      Full    10.28G     DISK        00:46:14     12-OCT-11
       BP Key: 47   Status: AVAILABLE  Compressed: YES  Tag: %TAG
       Piece Name: Z:\backup_loc\db_name\24MOUU55_1_1
 List of Datafiles in backup set 47
 File LV Type Ckp SCN Ckp Time Name
  • -- ---- ---------- --------- ---- 1 Full 14169947878<tel:14169947878> 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\SYSTEM01.DBF 2 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\BARGE_EX_DATA_01.ORA 4 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\USERS01.DBF 5 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\TOOLS01.DBF 6 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\INDX01.DBF 7 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\ARCHIVEDATA_01.ORA 8 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\ARCHIVEDATA_INDEX_01.ORA 10 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\BTS_INDEX_01.ORA 13 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\CDO_LARGE_DATA_01.ORA 14 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\COEDATA_01.ORA 16 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\DISDATA_01.ORA 18 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\DYNDATA_02.ORA 19 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\DYNDATA_03.ORA 21 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\ETALOG_DATA_01.ORA 22 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\ETALOG_INDEX_01.ORA 24 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\INDXDATA_01.ORA 25 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\INDXDATA2_01.ORA 28 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\PROMETHEUS_01.ORA 30 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\PROMETHEUS_03.ORA 31 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\PROM_INDEX_01.ORA 35 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\STATDATA2_01.ORA 39 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\TON_MILE_INDEX_01.ORA 40 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\USR_01.ORA 41 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\SUPPLYDATA_01.ORA 43 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\DBAMON_DATA_01.ORA 45 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\ACCRUAL_INDEX_01.ORA 47 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\INGRAM_DATA_02.ORA 51 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\ENGINEHOURS_DATA_01.ORA 53 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\BACKUP_DATA_01.ORA 58 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\JTP_DATA_01.ORA 59 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\TRANSPORT_DATA_01.ORA 60 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\JTP_INDEX_01.ORA 62 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\PSDATA_DATA_01.ORA 63 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\REORG_01.ORA 64 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\GIS_DATA_01.ORA 70 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\IMG_DATA_01.ORA 71 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\IMG_INDEX_01.ORA 72 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\IMG_HIST_DATA_01.ORA 73 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\IMG_HIST_INDEX_01.ORA 74 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\UNDOTBS_01.DBF 79 Full 14169947878 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\REPORT_INDEX_01.ORA

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------

48      Full    9.48G      DISK        00:44:11     13-OCT-11
       BP Key: 48   Status: AVAILABLE  Compressed: YES  Tag: %TAG
       Piece Name: Z:\backup_loc\db_Name\25MOV0S5_1_1
 List of Datafiles in backup set 48
 File LV Type Ckp SCN Ckp Time Name
  • -- ---- ---------- --------- ---- 3 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\SYSAUX01.DBF 9 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\BTS_DATA_01.ORA 11 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\BTS_LARGE_DATA_01.ORA 12 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\BTS_LARGE_INDEX_01.ORA 15 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\COEINDEX_01.ORA 17 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\DYNDATA_01.ORA 20 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\DYNDATA2_01.ORA 23 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\GENFK_INDX_01.ORA 26 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\INDXDATA3_01.ORA 27 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\INDXDATA3_02.ORA 29 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\PROMETHEUS_02.ORA 32 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\PROM_INDEX_02.ORA 33 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\PROM_INDEX_03.ORA 34 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\STATDATA_01.ORA 36 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\SWODATA_01.ORA 37 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\TEMPDATA_01.ORA 38 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\TON_MILE_DATA_01.ORA 42 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\SUPPLYINDEX_01.ORA 44 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\ACCRUAL_DATA_01.ORA 46 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\INGRAM_DATA_01.ORA 48 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\INGRAM_DATA_03.ORA 49 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\INGRAM_INDEX_01.ORA 50 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\INGRAM_INDEX_02.ORA 52 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\ENGINEHOURS_INDEX_01.ORA 54 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\GIS_INDEX_01.ORA 55 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\IBCO_DATA_01.ORA 56 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\IBCO_INDEX_01.ORA 57 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\TRANSPORT_INDEX_01.ORA 61 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\DEVMON_DATA_01.ORA 65 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\CHANNEL_DATA_01.ORA 66 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\IBCO_MAPS_DATA_01.ORA 67 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\CREW_DATA_01.ORA 68 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\CREW_INDEX_01.ORA 69 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\TOOLS2_01.DBF 75 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\BARGE_EX_INDEX_01.ORA 76 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\TMANDM_DATA_01.DBF 77 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\CREW_DATA_02.ORA 78 Full 14169960053 12-OCT-11 DRIVE_LETTER\ORACLE\ORADATA\DB_NAME\DATAFILE\REPORT_DATA_01.ORA

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------

49      Full    1.19M      DISK        00:00:01     13-OCT-11
       BP Key: 49   Status: AVAILABLE  Compressed: YES  Tag: %TAG
       Piece Name: Z:\backup_loc\db_name\26MOV3F5_1_1
 Control File Included: Ckp SCN: 14169962024 Ckp time: 13-OCT-11

BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ---------------

50      109.18M    DISK        00:00:27     13-OCT-11
       BP Key: 50   Status: AVAILABLE  Compressed: YES  Tag: %TAG
       Piece Name: Z:\backup_loc\db_Name\27MOV3FC_1_1

 List of Archived Logs in backup set 50
 Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
 ---- ------- ---------- --------- ---------- ---------
 1    101     14157277044 12-OCT-11 14157280857 12-OCT-11
 1    102     14157280857 12-OCT-11 14157286292 12-OCT-11
 1    103     14157286292 12-OCT-11 14157292373 12-OCT-11
 1    104     14157292373 12-OCT-11 14157298477 12-OCT-11
 1    105     14157298477 12-OCT-11 14157302940 12-OCT-11
 1    106     14157302940 12-OCT-11 14157307745 12-OCT-11
 1    107     14157307745 12-OCT-11 14169918183 12-OCT-11
 1    108     14169918183 12-OCT-11 14169922156 12-OCT-11
 1    109     14169922156 12-OCT-11 14169924664 12-OCT-11
 1    110     14169924664 12-OCT-11 14169929554 12-OCT-11
 1    111     14169929554 12-OCT-11 14169933863 12-OCT-11
 1    112     14169933863 12-OCT-11 14169934037 12-OCT-11
 1    113     14169934037 12-OCT-11 14169938293 12-OCT-11
 1    114     14169938293 12-OCT-11 14169942105 12-OCT-11
 1    115     14169942105 12-OCT-11 14169946799 12-OCT-11
 1    116     14169946799 12-OCT-11 14169951949 12-OCT-11
 1    117     14169951949 12-OCT-11 14169954350 12-OCT-11
 1    118     14169954350 12-OCT-11 14169955929 12-OCT-11
 1    119     14169955929 12-OCT-11 14169959172 12-OCT-11
 1    120     14169959172 12-OCT-11 14169962038 13-OCT-11

RMAN> Step 7: *Attempt* to Restore & Recover Database

RMAN> run

2> {
3> restore database until sequence 120;
4> recover database until sequence 120;
5> }

Step 8: Receive Errors

....
....

RMAN-06023: no backup or copy of datafile 27 found to restore
RMAN-06023: no backup or copy of datafile 26 found to restore
RMAN-06023: no backup or copy of datafile 23 found to restore
RMAN-06023: no backup or copy of datafile 20 found to restore
RMAN-06023: no backup or copy of datafile 17 found to restore
RMAN-06023: no backup or copy of datafile 15 found to restore
RMAN-06023: no backup or copy of datafile 12 found to restore
RMAN-06023: no backup or copy of datafile 11 found to restore
RMAN-06023: no backup or copy of datafile 9 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
...

Step 9: List Backup of datafile 1

RMAN> list backup of datafile 1;

List of Backup Sets



BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47      Full    10.28G     DISK        00:46:14     12-OCT-11
       BP Key: 47   Status: AVAILABLE  Compressed: YES  Tag: %TAG
       Piece Name: Z:\backup_loc\db_name\24MOUU55_1_1
 List of Datafiles in backup set 47
 File LV Type Ckp SCN Ckp Time Name
  • -- ---- ---------- --------- ----

1 Full 14169947878 12-OCT-11 DRIVE_LETTER:\ORACLE\ORADATA\DB_NAME\DATAFILE\SYSTEM01.DBF

Step 10: Double Check to verify file exists:

C:\>dir Z:\backup_loc\db_name\24MOUU55_1_1 Volume in drive Z is Z
Volume Serial Number is 0EF3-EAF8

Directory of Z:\backup_loc\db_name\

10/12/2011 10:47 PM 11,036,811,264 24MOUU55_1_1 Step 11: Scratch head in confusion....

Anyone care to enlighten me on what I've seem to have forgotten?

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205

"Quality is never an accident; it is always the result of intelligent effort." -- John Ruskin (English Writer 1819-1900)

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

--
http://www.freelists.org/webpage/oracle-l



--
Howard A. Latham

Sent from my Nokia N97

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 08 2011 - 08:54:46 CST

Original text of this message