Continuando o Oracle DUPLICATE database que falhou no RECOVER dos datafiles

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

    • Fábio Navarro em fevereiro 19, 2019 às 19:21
    • Responder

    Obrigado, me ajudou bastante!

Deixe um comentário

Seu e-mail não será publicado.