RE: RMAN Recovery - I'm missing a step or something
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 pmC-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 bytesSQL> 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-01Control 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_1List 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_1List 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_1Control 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_1List 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-lReceived on Tue Nov 08 2011 - 08:54:46 CST