RE: RMAN Recovery - I'm missing a step or something
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: 'Howard Latham' <howard.latham@xxxxxxxxx>
- Date: Tue, 8 Nov 2011 08:54:46 -0600
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@xxxxxxxxxxxxx'
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@xxxxxxxxx]
Sent: Tuesday, November 08, 2011 8:49 AM
To: Taylor, Chris David
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxx<mailto:ChrisDavid.Taylor@xxxxxxxxxxxxxxx>>
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.
--
//www.freelists.org/webpage/oracle-l
--
Howard A. Latham
Sent from my Nokia N97
--
//www.freelists.org/webpage/oracle-l
Other related posts:
