This post is also available in:
English
Há um tempo, o problema que eu costumava enfrentar era a falha da etapa the recovery do script de duplicate por qualquer tipo de problema (como, por exemplo, esquecer se setar a db_recovery_area_dest_size grande o suficiente para segurar os archives que são trazidos) e eu acabava tendo que iniciar o comando DUPLICATE do começo. A parte de recovery só traria alguns gigas, menos que 1% do total se comparado com o tamanho do backup full da parte de restore (uns 500 GBs). Portanto, rodar tudo do começo era uma grande perda de tempo.
Para retomar o comando de DUPLICATE do ponto de falha, comecei a dissecá-lo. Aiaxo irei mostrar um exemplo de como retomar uma duplicata de backup de fita que falhou na parte de recuperação.
OBS: Antes de começar, como dito nos comentários, é importante dizer que o DUPLICATE é na maioria das vezes uma operação RESUMABLE, desde que reexecutado com A MESMA SINTAXE e com o seu BD de destino de volta em NOMOUNT. Ele vai fazer o skip, na maioria dos casos, os datafiles e archivelog já baixados anteriormente. Esse artigo é mais voltado para entende-lo internamente ou em casos muito particulares quando for necessária uma abordagem mais manual. Apenas considere esse post caso já tenha tentando rodar o mesmo comando e ele começou a baixar tudo novamente. =]
Cenário:
- BD Origem: DBPROD
- BD Destino: DBTEST
Eu criei o meu próprio pfile para o BD DBTEST, defini os parâmetros e criei os diretórios.
Depois de iniciar o banco de dados in nomount, executei o comando:
-- My DUPLICATE command
connect target mdoxdba/"xxx"@dbprod
connect auxiliary mdoxdba/"xxx"@dbtest
connect catalog rman/"xxx"@rmanbkp
RUN
{
SET UNTIL TIME "to_date('2015-01-06 00:00:00','yyyy-mm-dd hh24:mi:ss')";
ALLOCATE AUXILIARY CHANNEL T1 TYPE 'sbt_tape';
DUPLICATE TARGET DATABASE TO 'dbtest';
}
Note que o meu "until time" é 06/Jan. O meu último full foi no dia 03/Jan. Portanto, eu tenho 3 dias de archives a serem aplicados.
Então, quando o meu script iniciou a fase de recover, abortei ele (CTRL+C) para simular uma falha:
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 9 12:15:49 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN>
connected to target database: DBPROD (DBID=2452006932)
RMAN>
connected to auxiliary database: DBTEST (not mounted)
RMAN>
connected to recovery catalog database
RMAN> 2> 3> 4> 5> 6> 7>
executing command: SET until clause
allocated channel: t1
channel t1: SID=554 device type=SBT_TAPE
channel t1: Data Protector A.08.10/205
Starting Duplicate Db at 09-JAN-15
contents of Memory Script:
{
set until scn 13169104914343;
set newname for datafile 1 to
"/oravl02/oradata/dbtest/system01.dbf";
set newname for datafile 2 to
"/oravl02/oradata/dbtest/sysaux01.dbf";
set newname for datafile 3 to
"/oravl02/oradata/dbtest/undotbs01.dbf";
set newname for datafile 4 to
"/oravl02/oradata/dbtest/users01.dbf";
set newname for datafile 5 to
"/oravl02/oradata/dbtest/usr01.dbf";
set newname for datafile 6 to
"/oravl02/oradata/dbtest/usr02.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 09-JAN-15
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to /oravl02/oradata/dbtest/system01.dbf
channel t1: restoring datafile 00002 to /oravl02/oradata/dbtest/sysaux01.dbf
channel t1: restoring datafile 00003 to /oravl02/oradata/dbtest/undotbs01.dbf
channel t1: restoring datafile 00004 to /oravl02/oradata/dbtest/users01.dbf
channel t1: restoring datafile 00005 to /oravl02/oradata/dbtest/usr01.dbf
channel t1: restoring datafile 00006 to /oravl02/oradata/dbtest/usr02.dbf
channel t1: reading from backup piece 1hprsdqk_1_1
channel t1: piece handle=1hprsdqk_1_1 tag=FULL_DB_INCR_ONLINE
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:09:45
Finished restore at 09-JAN-15
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DBTEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/oravl02/oradata/dbtest/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/oravl02/oradata/dbtest/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/oravl02/oradata/dbtest/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/oravl02/oradata/dbtest/system01.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=868537545 file name=/oravl02/oradata/dbtest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=868537545 file name=/oravl02/oradata/dbtest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=868537545 file name=/oravl02/oradata/dbtest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=868537545 file name=/oravl02/oradata/dbtest/usr01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=868537545 file name=/oravl02/oradata/dbtest/usr02.dbf
contents of Memory Script:
{
set until time "to_date('2015-01-06 00:00:00','yyyy-mm-dd hh24:mi:ss')";
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 09-JAN-15
starting media recovery
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=7304
channel t1: restoring archived log
archived log thread=1 sequence=7305
channel t1: reading from backup piece 1jprsef0_1_1
user interrupt received
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oravl02/oradata/dbtest/system01.dbf'
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/09/2015 12:26:47
RMAN-03015: error occurred in stored script Memory Script
RMAN-03099: job cancelled at user request
RMAN>
Aqui podemos ver que o RESTORE completou com sucesso. Após isso, o DUPLICATE criou o controfile com apenas o datafile SYSTEM. Em seguida, ele executou um "switch datafile all" para adicionar o resto.
Quando começou a parte de RECOVER, eu o abortei.
Como podemos então continuar daqui?
Primeiramente, precisamos terminar o recovery. Abra novamente o rman conectando ao auxiliary, target e catalog servers (se presentes) e então reexecute a etapa de recovery.
Note abaixo que eu aloquei um canal auxiliar novamente e o banco DBTEST deve ficar em modo MOUNT.
RMAN> run
2> {
3> allocate auxiliary channel t1 type 'SBT_TAPE';
5> set until time "to_date('2015-01-06 00:00:00','yyyy-mm-dd hh24:mi:ss')";
6> recover
7> clone database
8> delete archivelog
9> ;
10> }
allocated channel: t1
channel t1: SID=552 device type=SBT_TAPE
channel t1: Data Protector A.08.10/205
executing command: SET until clause
Starting recover at 09-JAN-15
added tempfile /oravl04/oradata/dbprod/dbprod/temp01.dbf to tablespace TEMPORARY in control file
starting media recovery
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=7304
channel t1: restoring archived log
archived log thread=1 sequence=7305
channel t1: reading from backup piece 1jprsef0_1_1
channel t1: piece handle=1jprsef0_1_1 tag=FULL_ARCHIVELOG_ONLINE
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:03:55
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7304_bbzsz4nh_.arc thread=1 sequence=7304
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7304_bbzsz4nh_.arc RECID=2 STAMP=868538309
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7305_bbzsz4o6_.arc thread=1 sequence=7305
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7305_bbzsz4o6_.arc RECID=1 STAMP=868538308
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=7306
channel t1: restoring archived log
archived log thread=1 sequence=7307
channel t1: restoring archived log
archived log thread=1 sequence=7308
channel t1: restoring archived log
archived log thread=1 sequence=7309
channel t1: reading from backup piece 1lprto0p_1_1
channel t1: piece handle=1lprto0p_1_1 tag=ARCHIVE_ONLINE
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:55
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7306_bbzt0nlk_.arc thread=1 sequence=7306
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7306_bbzt0nlk_.arc RECID=4 STAMP=868538362
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7307_bbzt0nns_.arc thread=1 sequence=7307
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7307_bbzt0nns_.arc RECID=6 STAMP=868538362
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7308_bbzt0nn1_.arc thread=1 sequence=7308
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7308_bbzt0nn1_.arc RECID=5 STAMP=868538362
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7309_bbzt0np3_.arc thread=1 sequence=7309
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7309_bbzt0np3_.arc RECID=3 STAMP=868538362
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=7310
channel t1: restoring archived log
archived log thread=1 sequence=7311
channel t1: restoring archived log
archived log thread=1 sequence=7312
channel t1: restoring archived log
archived log thread=1 sequence=7313
channel t1: restoring archived log
archived log thread=1 sequence=7314
channel t1: restoring archived log
archived log thread=1 sequence=7315
channel t1: restoring archived log
archived log thread=1 sequence=7316
channel t1: restoring archived log
archived log thread=1 sequence=7317
channel t1: reading from backup piece 1mps0cd1_1_1
channel t1: piece handle=1mps0cd1_1_1 tag=ARCHIVE_ONLINE
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:55
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7310_bbzt2v2x_.arc thread=1 sequence=7310
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7310_bbzt2v2x_.arc RECID=9 STAMP=868538434
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7311_bbzt2v3d_.arc thread=1 sequence=7311
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7311_bbzt2v3d_.arc RECID=10 STAMP=868538434
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7312_bbzt2v22_.arc thread=1 sequence=7312
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7312_bbzt2v22_.arc RECID=8 STAMP=868538434
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7313_bbzt2v3p_.arc thread=1 sequence=7313
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7313_bbzt2v3p_.arc RECID=11 STAMP=868538434
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7314_bbzt2v42_.arc thread=1 sequence=7314
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7314_bbzt2v42_.arc RECID=12 STAMP=868538434
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7315_bbzt2v4c_.arc thread=1 sequence=7315
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7315_bbzt2v4c_.arc RECID=13 STAMP=868538434
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7316_bbzt2v4t_.arc thread=1 sequence=7316
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7316_bbzt2v4t_.arc RECID=14 STAMP=868538434
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7317_bbzt2v55_.arc thread=1 sequence=7317
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7317_bbzt2v55_.arc RECID=7 STAMP=868538429
media recovery complete, elapsed time: 00:00:21
Finished recover at 09-JAN-15
released channel: t1
RMAN>
Perfeito, a parte de recovery funcionou. A partir daqui, temos 2 caminhos para finalizar o duplicate: o caminho fácil e óbvio OU o caminho do DUPLICATE (dissecado). Mostrarei ambos:
1. Caminho fácil:
Por fim, abra o database com resetlogs:
SQL> alter database open resetlogs; Database altered. SQL>
Ok. Seu BD está pronto.
2. Caminho do DUPLICATE (dissecado):
O comando DUPLICATE faz um monte de paços extras, provavelmente por que ele pode receber uma grande quantidade de combinações de parâmetro e condições, como por exemplo "skipar" alguma tablespace. Portanto, é um modo de implementação mais genérica para lidar com um leque maior de casos.
Para simulá-lo, primeiro vamos deixar o BD em NOMOUNT novamente para recriar o controlfile (note que eu fiz isso usando ainda a sessão do RMAN):
RMAN> RUN
2> {
3> shutdown clone immediate;
4> startup clone nomount ;
5> }
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 7081771008 bytes
Fixed Size 2147080 bytes
Variable Size 3283610872 bytes
Database Buffers 3791650816 bytes
Redo Buffers 4362240 bytes
RMAN>
Em seguida, ao invés de recriar o controlfile com todos os datafiles, o oracle vai apenas cria-lo com o datafile SYSTEM. Vamos fazer isso. (É o mesmo comando que foi executado lá no começo):
SQL> CREATE CONTROLFILE REUSE SET DATABASE "DBTEST" RESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 ( '/oravl02/oradata/dbtest/redo01.log' ) SIZE 50 M REUSE, 9 GROUP 2 ( '/oravl02/oradata/dbtest/redo02.log' ) SIZE 50 M REUSE, 10 GROUP 3 ( '/oravl02/oradata/dbtest/redo03.log' ) SIZE 50 M REUSE 11 DATAFILE 12 '/oravl02/oradata/dbtest/system01.dbf' 13 CHARACTER SET AL32UTF8; Control file created. SQL>
Agora vamos adicionar ao controlfile os outros datafiles. Conecte novamente o RMAN ao auxiliary e target:
RMAN> run
2> {
3> set newname for tempfile 1 to "/oravl02/oradata/dbtest/temp01.dbf";
4> switch clone tempfile all;
5> set newname for datafile 1 to "/oravl02/oradata/dbtest/system01.dbf";
6> set newname for datafile 2 to "/oravl02/oradata/dbtest/sysaux01.dbf";
7> set newname for datafile 3 to "/oravl02/oradata/dbtest/undotbs01.dbf";
8> set newname for datafile 4 to "/oravl02/oradata/dbtest/users01.dbf";
9> set newname for datafile 5 to "/oravl02/oradata/dbtest/usr01.dbf";
10> set newname for datafile 6 to "/oravl02/oradata/dbtest/usr02.dbf";
11> catalog clone datafilecopy "/oravl02/oradata/dbtest/sysaux01.dbf";
12> catalog clone datafilecopy "/oravl02/oradata/dbtest/undotbs01.dbf";
13> catalog clone datafilecopy "/oravl02/oradata/dbtest/users01.dbf";
14> catalog clone datafilecopy "/oravl02/oradata/dbtest/usr01.dbf";
15> catalog clone datafilecopy "/oravl02/oradata/dbtest/usr02.dbf";
16> switch clone datafile all;
17> }
executing command: SET NEWNAME
renamed tempfile 1 to /oravl02/oradata/dbtest/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
cataloged datafile copy
datafile copy file name=/oravl02/oradata/dbtest/sysaux01.dbf RECID=1 STAMP=868554741
cataloged datafile copy
datafile copy file name=/oravl02/oradata/dbtest/undotbs01.dbf RECID=2 STAMP=868554741
cataloged datafile copy
datafile copy file name=/oravl02/oradata/dbtest/users01.dbf RECID=3 STAMP=868554741
cataloged datafile copy
datafile copy file name=/oravl02/oradata/dbtest/usr01.dbf RECID=4 STAMP=868554742
cataloged datafile copy
datafile copy file name=/oravl02/oradata/dbtest/usr02.dbf RECID=5 STAMP=868554742
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=868554741 file name=/oravl02/oradata/dbtest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=868554741 file name=/oravl02/oradata/dbtest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=868554741 file name=/oravl02/oradata/dbtest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=868554742 file name=/oravl02/oradata/dbtest/usr01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=868554742 file name=/oravl02/oradata/dbtest/usr02.dbf
RMAN>
OBS: Apesar de não estar presente no trace do DUPLICATE, eu adicionei aqui a cláusula "set newname" para todos os datafiles. Caso contrário, o comando falharia. Na verdade, quando é o RMAN executa, ele não necessita executar esses "sets" bloco final porque isso já foi feito no início. Como a sessão é o mesma, ele não precisa executá-los novamente.
Então, finalmente, abre o seu BD com resetlogs:
RMAN> run
2> {
3> Alter clone database open resetlogs;
4> }
database opened
RMAN>
E é isso! Por estes caminhos, não há necessidade de restaurar todos os datafiles novamente.
Alguns sites úteis que me ajudaram e onde você pode encontrar mais informações:
Gostou? Não deixe de comentar ou deixar um 👍!
1 comentário
Obrigado, me ajudou bastante!