Cenário: Desastre e Recovery, COM BACKUP, SEM CATALOGO, INSTANCE
SHUTDOWN.Enterprise Edition 12.1.0.2 Linux 64
SQL> archive log listDatabase log mode Archive ModeAutomatic archival
EnabledArchive destination /u01/app/archivelog2Oldest online log
sequence 67Next log sequence to archive 70Current log sequence 70
a) PASSO 1 - BACKUPEAR TODOS OS ARQUIVOS DO DATABASE
run{alter system archive log current; backup database plus archivelog delete
input;}
Starting backup at 15-MAR-17using channel ORA_DISK_1channel ORA_DISK_1:
starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in
backup setinput datafile file number=00001
name=/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_system_dbz6nx14_.dbfinput
datafile file number=00003
name=/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_sysaux_dbz6m573_.dbfinput
datafile file number=00005
name=/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_new_user_dcchkvk9_.dbfinput
datafile file number=00004
name=/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_undotbs1_dcczlbll_.dbfinput
datafile file number=00006
name=/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_tbs_user_dccnlhxy_.dbfinput
datafile file number=00007
name=/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_new_user_dcchkvnx_.dbfchannel
ORA_DISK_1: starting piece 1 at 15-MAR-17channel ORA_DISK_1: finished piece 1
at 15-MAR-17piece
handle=/u01/app/oracle/fast_recovery_area/TERRA1/backupset/2017_03_15/o1_mf_nnndf_TAG20170315T021022_ddkm6036_.bkp
tag=TAG20170315T021022 comment=NONEchannel ORA_DISK_1: backup set complete,
elapsed time: 00:01:45Finished backup at 15-MAR-17
Starting backup at 15-MAR-17current log archivedusing channel ORA_DISK_1channel
ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying
archived log(s) in backup setinput archived log thread=1 sequence=72 RECID=133
STAMP=938657529channel ORA_DISK_1: starting piece 1 at 15-MAR-17channel
ORA_DISK_1: finished piece 1 at 15-MAR-17piece
handle=/u01/app/oracle/fast_recovery_area/TERRA1/backupset/2017_03_15/o1_mf_annnn_TAG20170315T021210_ddkm9bgq_.bkp
tag=TAG20170315T021210 comment=NONEchannel ORA_DISK_1: backup set complete,
elapsed time: 00:00:01channel ORA_DISK_1: deleting archived log(s)archived log
file name=/u01/app/archivelog/arc_1_72_936747113.arc RECID=133
STAMP=938657529Finished backup at 15-MAR-17
Starting Control File and SPFILE Autobackup at 15-MAR-17piece
handle=/u01/backup/controlfile/control_file_c-2182710439-20170315-00.ctl
comment=NONEFinished Control File and SPFILE Autobackup at 15-MAR-17
2) rm -rf em todos os datafiles, controlfiles, redo logs, spfile e init.
3) Restaurando o spfile
RMAN> set dbid=2182710439
executing command: SET DBID
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parametersLRM-00109:
could not open parameter file
'/u01/app/oracle/product/12.1.0.2/db_1/dbs/initTERRA1.ora'
starting Oracle instance without parameter file for retrieval of spfileOracle
instance started
Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytesVariable Size
281018472 bytesDatabase Buffers 784334848 bytesRedo Buffers
5455872 bytes
RMAN> set dbid=2182710439
executing command: SET DBID
RMAN> restore spfile from
'/u01/backup/controlfile/control_file_c-2182710439-20170315-00.ctl';
Starting restore at 15-MAR-17using target database control file instead of
recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=12 device
type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP
/u01/backup/controlfile/control_file_c-2182710439-20170315-00.ctlchannel
ORA_DISK_1: SPFILE restore from AUTOBACKUP completeFinished restore at 15-MAR-17
RMAN> EXIT
4) Restaurando o controlfile
SQL> shutdown abort;
RMAN> startup nomount;
Oracle instance started
Total System Global Area 838860800 bytes
Fixed Size 2929936 bytesVariable Size
570428144 bytesDatabase Buffers 260046848 bytesRedo Buffers
5455872 bytes
RMAN> set dbid=2182710439
executing command: SET DBID
RMAN> restore controlfile from
'/u01/backup/controlfile/control_file_c-2182710439-20170315-00.ctl';
Starting restore at 15-MAR-17using target database control file instead of
recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=12 device
type=DISK
channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete,
elapsed time: 00:00:01output file
name=/u01/app/oracle/oradata/TERRA1/controlfile/o1_mf_dbz6roh5_.ctloutput file
name=/u01/app/oracle/fast_recovery_area/TERRA1/controlfile/o1_mf_dbz6roox_.ctlFinished
restore at 15-MAR-17
RMAN>
RMAN> alter database mount;
Statement processedreleased channel: ORA_DISK_1
5) Restaurando os datafiles
RMAN> list backup of database;
BS Key Type LV Size Device Type Elapsed Time Completion Time------- ----
-- ---------- ----------- ------------ ---------------19 Full 1.14G
DISK 00:01:03 28-FEB-17 BP Key: 19 Status: AVAILABLE
Compressed: NO Tag: TAG20170228T152646 Piece Name:
/u01/app/oracle/fast_recovery_area/TERRA1/backupset/2017_02_28/o1_mf_nnndf_TAG20170228T152646_dccj773m_.bkp
List of Datafiles in backup set 19 File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ---- 1 Full 2479294 28-FEB-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_system_ddko3n6m_.dbf 3
Full 2479294 28-FEB-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_sysaux_ddko3n6t_.dbf 4
Full 2479294 28-FEB-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_undotbs1_ddko3nb2_.dbf 5
Full 2479294 28-FEB-17 7 Full 2479294 28-FEB-17
BS Key Type LV Size Device Type Elapsed Time Completion Time-------
---- -- ---------- ----------- ------------ ---------------21 Full
1.13G DISK 00:01:00 28-FEB-17 BP Key: 21 Status:
AVAILABLE Compressed: NO Tag: TAG20170228T165349 Piece Name:
/u01/app/oracle/fast_recovery_area/TERRA1/backupset/2017_02_28/o1_mf_nnndf_TAG20170228T165349_dccobh6h_.bkp
List of Datafiles in backup set 21 File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ---- 1 Full 2486852 28-FEB-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_system_ddko3n6m_.dbf 3
Full 2486852 28-FEB-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_sysaux_ddko3n6t_.dbf 4
Full 2486852 28-FEB-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_undotbs1_ddko3nb2_.dbf 5
Full 2486852 28-FEB-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_new_user_ddko3n73_.dbf 6
Full 2486852 28-FEB-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_tbs_user_ddko3nbd_.dbf 7
Full 2486852 28-FEB-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_new_user_ddko3nc2_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time-------
---- -- ---------- ----------- ------------ ---------------24 Full
1.14G DISK 00:01:34 15-MAR-17 BP Key: 24 Status:
AVAILABLE Compressed: NO Tag: TAG20170315T021022 Piece Name:
/u01/app/oracle/fast_recovery_area/TERRA1/backupset/2017_03_15/o1_mf_nnndf_TAG20170315T021022_ddkm6036_.bkp
List of Datafiles in backup set 24 File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ---- 1 Full 2797971 15-MAR-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_system_dbz6nx14_.dbf 3
Full 2797971 15-MAR-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_sysaux_dbz6m573_.dbf 4
Full 2797971 15-MAR-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_undotbs1_dcczlbll_.dbf 5
Full 2797971 15-MAR-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_new_user_dcchkvk9_.dbf 6
Full 2797971 15-MAR-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_tbs_user_dccnlhxy_.dbf 7
Full 2797971 15-MAR-17
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_new_user_dcchkvnx_.dbf
RMAN> restore database;
channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1:
specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring
datafile 00001 to
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_system_dbz6nx14_.dbfchannel
ORA_DISK_1: restoring datafile 00003 to
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_sysaux_dbz6m573_.dbfchannel
ORA_DISK_1: restoring datafile 00004 to
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_undotbs1_dcczlbll_.dbfchannel
ORA_DISK_1: restoring datafile 00005 to
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_new_user_dcchkvk9_.dbfchannel
ORA_DISK_1: restoring datafile 00006 to
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_tbs_user_dccnlhxy_.dbfchannel
ORA_DISK_1: restoring datafile 00007 to
/u01/app/oracle/oradata/TERRA1/datafile/o1_mf_new_user_dcchkvnx_.dbfchannel
ORA_DISK_1: reading from backup piece
/u01/app/oracle/fast_recovery_area/TERRA1/backupset/2017_03_15/o1_mf_nnndf_TAG20170315T021022_ddkm6036_.bkpchannel
ORA_DISK_1: piece
handle=/u01/app/oracle/fast_recovery_area/TERRA1/backupset/2017_03_15/o1_mf_nnndf_TAG20170315T021022_ddkm6036_.bkp
tag=TAG20170315T021022channel ORA_DISK_1: restored backup piece 1channel
ORA_DISK_1: restore complete, elapsed time: 00:01:15Finished restore at
15-MAR-17
RMAN> recover database;
Starting recover at 15-MAR-17using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 72 is already on disk as file
/u01/app/archivelog2/arc_1_72_936747113.arcarchived log file
name=/u01/app/archivelog2/arc_1_72_936747113.arc thread=1 sequence=72unable to
find archived logarchived log thread=1 sequence=73RMAN-00571:
===========================================================RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571:
===========================================================RMAN-03002: failure
of recover command at 03/15/2017 02:34:28RMAN-06054: media recovery requesting
unknown archived log for thread 1 with sequence 73 and starting SCN of 2798016
List of Archived Logs in backup set 23 Thrd Seq Low SCN Low Time
Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1
26 2466076 28-FEB-17 2479421 28-FEB-17 1 27 2479421
28-FEB-17 2479425 28-FEB-17 1 28 2479425 28-FEB-17 2479428
28-FEB-17 1 29 2479428 28-FEB-17 2479431 28-FEB-17 1 30
2479431 28-FEB-17 2479434 28-FEB-17 1 31 2479434 28-FEB-17
2479437 28-FEB-17 1 32 2479437 28-FEB-17 2479440 28-FEB-17 1
33 2479440 28-FEB-17 2479443 28-FEB-17 1 34 2479443
28-FEB-17 2479446 28-FEB-17 1 35 2479446 28-FEB-17 2479449
28-FEB-17 1 36 2479449 28-FEB-17 2479452 28-FEB-17 1 37
2479452 28-FEB-17 2479455 28-FEB-17 1 38 2479455 28-FEB-17
2479458 28-FEB-17 1 39 2479458 28-FEB-17 2479461 28-FEB-17 1
40 2479461 28-FEB-17 2479464 28-FEB-17 1 41 2479464
28-FEB-17 2479472 28-FEB-17 1 42 2479472 28-FEB-17 2481272
28-FEB-17 1 43 2481272 28-FEB-17 2482580 28-FEB-17 1 44
2482580 28-FEB-17 2482924 28-FEB-17 1 45 2482924 28-FEB-17
2486752 28-FEB-17 1 46 2486752 28-FEB-17 2486755 28-FEB-17 1
47 2486755 28-FEB-17 2486759 28-FEB-17 1 48 2486759
28-FEB-17 2486762 28-FEB-17 1 49 2486762 28-FEB-17 2486765
28-FEB-17 1 50 2486765 28-FEB-17 2487185 28-FEB-17 1 51
2487185 28-FEB-17 2494079 28-FEB-17 1 52 2494079 28-FEB-17
2694082 28-FEB-17 1 53 2694082 28-FEB-17 2695889 28-FEB-17 1
54 2695889 28-FEB-17 2695896 28-FEB-17 1 55 2695896
28-FEB-17 2695901 28-FEB-17 1 56 2695901 28-FEB-17 2695904
28-FEB-17 1 57 2695904 28-FEB-17 2696428 28-FEB-17 1 58
2696428 28-FEB-17 2696431 28-FEB-17 1 59 2696431 28-FEB-17
2696434 28-FEB-17 1 60 2696434 28-FEB-17 2696437 28-FEB-17 1
61 2696437 28-FEB-17 2696440 28-FEB-17 1 62 2696440
28-FEB-17 2696447 28-FEB-17 1 63 2696447 28-FEB-17 2696471
28-FEB-17 1 64 2696471 28-FEB-17 2696489 28-FEB-17 1 65
2696489 28-FEB-17 2696492 28-FEB-17 1 66 2696492 28-FEB-17
2696495 28-FEB-17 1 67 2696495 28-FEB-17 2696498 28-FEB-17 1
68 2696498 28-FEB-17 2696501 28-FEB-17 1 69 2696501
28-FEB-17 2796888 15-MAR-17 1 70 2796888 15-MAR-17 2797938
15-MAR-17 1 71 2797938 15-MAR-17 2797947 15-MAR-17
BS Key Size Device Type Elapsed Time Completion Time------- ----------
----------- ------------ ---------------25 5.00K DISK 00:00:00
15-MAR-17 BP Key: 25 Status: AVAILABLE Compressed: NO Tag:
TAG20170315T021210 Piece Name:
/u01/app/oracle/fast_recovery_area/TERRA1/backupset/2017_03_15/o1_mf_annnn_TAG20170315T021210_ddkm9bgq_.bkp
List of Archived Logs in backup set 25 Thrd Seq Low SCN Low Time
Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1
72 2797947 15-MAR-17 2798016 15-MAR-17
Após o recover sem sucesso. Deletei todos os datafiles novamente e refiz o
restore, realizei o recover novamente, desta vez, com o seguinte comando:
RMAN> RECOVER DATABASE UNTIL SEQUENCE 71;
Starting recover at 15-MAR-17using channel ORA_DISK_1RMAN-00571:
===========================================================RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571:
===========================================================RMAN-03002: failure
of recover command at 03/15/2017 02:44:33RMAN-06556: datafile 1 must be
restored from backup older than SCN 2797938
ALGUEM PODE AJUDAR PQ NAO CONSEGUINDO RECUPERAR O DATABASE?
a) Se eu mando o "RECOVER DATABASE" o RDBMS nao deveria recuperar até onde
seria possível?b) Mesmo informando o SCN que o controlfile me diz que eu
possuo, o database também nao recupera, por qual motivo isso está
acontecendo?c) Estou fazendo algum procedimento errado? Alguém poderia ajudar?
