Re: RMAN Recovery - I'm missing a step or something
- From: Guenadi Jilevski <gjilevski@xxxxxxxxx>
- To: ChrisDavid.Taylor@xxxxxxxxxxxxxxx
- Date: Tue, 8 Nov 2011 17:00:42 +0200
Hi,
I think it should be:
RUN
{
SET UNTIL SEQUENCE 120;
RESTORE DATABASE;
RECOVER DATABASE; # recovers through log 120
}
Best Regards,
Guenadi jilevski
On Tue, Nov 8, 2011 at 4:41 PM, Taylor, Chris David <
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 bytes
> Fixed Size 2071056 bytes
> Variable Size 2080376304 bytes
> Database Buffers 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 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
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
