Bom dia,
As vezes pode virar uma bateção de cabeça, ainda mais se o ambiente não
estiver previamente documentado...
Principalmente com relação ao *DBID do banco. *Guarda bem esse numero,
porque vc ainda vai precisar dele um dia
Passei por uma situação tosca outro dia, em que um tablespace nao era
backupeado, na configuração do rman estava excluido de proposito.
Mas na hora do restore, em outra maquina.... não retornava porque havia
referencia a ele, na listagem dos datafiles fazia uma referencia ao que não
tinha
Era uma emergência, precisava fazer uma correção em um sistema que o
usuário tinha feito uns lançamentos errados, então era uma copia do banco
de produção.
E pra completar, as pastas onde estavam os arquivos originais não eram as
mesmas no servidor ( era um outro servidor ), ter que sair adaptando com
set newname datafile..
E na pressa, até lembrar que existia o comando "recover database skip
tablespace fulano,beltrano,... until sequence (ou scn) etc."
Pode tentar também com recover database until cancel.
Carlos, vou propor um outro desafio para treinar o restore, depois que vc
vencer este: => formata a maquina, reinstala o Oracle, zerado e tenta
voltar esse backup ai de novo..
Depois de praticar alguma vezes backup e restore, fica parecendo uma
receita de bolo, que muda pouco, dependendo da situação.
On 15 March 2017 at 03:39, Carlos Eduardo carloseduard...@yahoo.com
[oracle_br] <oracle_br@yahoogrupos.com.br> wrote:
>
>
> Cenário: Desastre e Recovery, COM BACKUP, SEM CATALOGO, INSTANCE SHUTDOWN.
> Enterprise Edition 12.1.0.2 Linux 64
>
> SQL> archive log list
> Database log mode Archive Mode
> Automatic archival Enabled
> Archive destination /u01/app/archivelog2
> Oldest online log sequence 67
> Next log sequence to archive 70
> Current 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-17
> using channel ORA_DISK_1
> channel ORA_DISK_1: starting full datafile backup set
> channel ORA_DISK_1: specifying datafile(s) in backup set
> input datafile file number=00001 name=/u01/app/oracle/oradata/
> TERRA1/datafile/o1_mf_system_dbz6nx14_.dbf
> input datafile file number=00003 name=/u01/app/oracle/oradata/
> TERRA1/datafile/o1_mf_sysaux_dbz6m573_.dbf
> input datafile file number=00005 name=/u01/app/oracle/oradata/
> TERRA1/datafile/o1_mf_new_user_dcchkvk9_.dbf
> input datafile file number=00004 name=/u01/app/oracle/oradata/
> TERRA1/datafile/o1_mf_undotbs1_dcczlbll_.dbf
> input datafile file number=00006 name=/u01/app/oracle/oradata/
> TERRA1/datafile/o1_mf_tbs_user_dccnlhxy_.dbf
> input datafile file number=00007 name=/u01/app/oracle/oradata/
> TERRA1/datafile/o1_mf_new_user_dcchkvnx_.dbf
> channel ORA_DISK_1: starting piece 1 at 15-MAR-17
> channel ORA_DISK_1: finished piece 1 at 15-MAR-17
> piece handle=/u01/app/oracle/fast_recovery_area/TERRA1/
> backupset/2017_03_15/o1_mf_nnndf_TAG20170315T021022_ddkm6036_.bkp
> tag=TAG20170315T021022 comment=NONE
> channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
> Finished backup at 15-MAR-17
>
> Starting backup at 15-MAR-17
> current log archived
> using channel ORA_DISK_1
> channel ORA_DISK_1: starting archived log backup set
> channel ORA_DISK_1: specifying archived log(s) in backup set
> input archived log thread=1 sequence=72 RECID=133 STAMP=938657529
> channel ORA_DISK_1: starting piece 1 at 15-MAR-17
> channel ORA_DISK_1: finished piece 1 at 15-MAR-17
> piece handle=/u01/app/oracle/fast_recovery_area/TERRA1/
> backupset/2017_03_15/o1_mf_annnn_TAG20170315T021210_ddkm9bgq_.bkp
> tag=TAG20170315T021210 comment=NONE
> channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
> channel ORA_DISK_1: deleting archived log(s)
> archived log file name=/u01/app/archivelog/arc_1_72_936747113.arc
> RECID=133 STAMP=938657529
> Finished backup at 15-MAR-17
>
> Starting Control File and SPFILE Autobackup at 15-MAR-17
> piece handle=/u01/backup/controlfile/control_file_c-2182710439-20170315-00.ctl
> comment=NONE
> Finished 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 <(21)%208271-0439>
>
> executing command: SET DBID
>
> RMAN> startup nomount;
>
> startup failed: ORA-01078: failure in processing system parameters
> LRM-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 spfile
> Oracle instance started
>
> Total System Global Area 1073741824 bytes
>
> Fixed Size 2932632 bytes
> Variable Size 281018472 bytes
> Database Buffers 784334848 bytes
> Redo Buffers 5455872 bytes
>
> RMAN> set dbid=2182710439 <(21)%208271-0439>
>
> executing command: SET DBID
>
> RMAN> restore spfile from '/u01/backup/controlfile/
> control_file_c-2182710439-20170315-00.ctl';
>
> Starting restore at 15-MAR-17
> using target database control file instead of recovery catalog
> allocated channel: ORA_DISK_1
> channel 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.ctl
> channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
> Finished 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 bytes
> Variable Size 570428144 bytes
> Database Buffers 260046848 bytes
> Redo Buffers 5455872 bytes
>
> RMAN> set dbid=2182710439 <(21)%208271-0439>
>
> executing command: SET DBID
>
> RMAN> restore controlfile from '/u01/backup/controlfile/
> control_file_c-2182710439-20170315-00.ctl';
>
> Starting restore at 15-MAR-17
> using target database control file instead of recovery catalog
> allocated channel: ORA_DISK_1
> channel ORA_DISK_1: SID=12 device type=DISK
>
> channel ORA_DISK_1: restoring control file
> channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
> output file name=/u01/app/oracle/oradata/TERRA1/controlfile/o1_mf_
> dbz6roh5_.ctl
> output file name=/u01/app/oracle/fast_recovery_area/TERRA1/
> controlfile/o1_mf_dbz6roox_.ctl
> Finished restore at 15-MAR-17
>
> RMAN>
>
> RMAN> alter database mount;
>
> Statement processed
> released 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 restore
> channel ORA_DISK_1: specifying datafile(s) to restore from backup set
> channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/
> TERRA1/datafile/o1_mf_system_dbz6nx14_.dbf
> channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/
> TERRA1/datafile/o1_mf_sysaux_dbz6m573_.dbf
> channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/
> TERRA1/datafile/o1_mf_undotbs1_dcczlbll_.dbf
> channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/
> TERRA1/datafile/o1_mf_new_user_dcchkvk9_.dbf
> channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/
> TERRA1/datafile/o1_mf_tbs_user_dccnlhxy_.dbf
> channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/
> TERRA1/datafile/o1_mf_new_user_dcchkvnx_.dbf
> channel ORA_DISK_1: reading from backup piece
> /u01/app/oracle/fast_recovery_area/TERRA1/backupset/2017_03_
> 15/o1_mf_nnndf_TAG20170315T021022_ddkm6036_.bkp
> channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_
> recovery_area/TERRA1/backupset/2017_03_15/o1_mf_nnndf_TAG20170315T021022_ddkm6036_.bkp
> tag=TAG20170315T021022
> channel ORA_DISK_1: restored backup piece 1
> channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
> Finished restore at 15-MAR-17
>
>
> RMAN> recover database;
>
>
> Starting recover at 15-MAR-17
> using 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.arc
> archived log file name=/u01/app/archivelog2/arc_1_72_936747113.arc
> thread=1 sequence=72
> unable to find archived log
> archived log thread=1 sequence=73
> RMAN-00571: ===========================================================
> RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
> RMAN-00571: ===========================================================
> RMAN-03002: failure of recover command at 03/15/2017 02:34:28
> RMAN-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-17
> using channel ORA_DISK_1
> RMAN-00571: ===========================================================
> RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
> RMAN-00571: ===========================================================
> RMAN-03002: failure of recover command at 03/15/2017 02:44:33
> RMAN-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?
>
>
>
>
>
>
>
>
>
