RMAN – Recuperando um Banco de Dados Inteiro

RMAN – Recuperando um Banco de Dados Inteiro

Olá pessoal, hoje estarei postando um pouco sobre uma ferramenta que utilizo muito e que estou me especializando. Essa ferramenta é o RMAN, ferramenta de Backup recomendada pela Oracle. Este artigo vai em homenagem a um grande amigo Rodrigo Almeida, na minha opinião o Mestre dos Magos em RMAN, pois ele tem me ajudado muito com meus estudos e apredizados com RMAN. RMAN vale um destaque especial no meu blog, pois quando realmente precisei ele funcionou. Irei contar uma história…

Um mês atrás eu estava de férias e o DBA junior da empresa ficou como responsável durante minha ausência, com isso todas as atividades relacionadas a Banco de Dados ficou para sua responsabilidade. Na empresa em que trabalhamos possuímos 2 tipos de backup, 1 backup Lógico(Export),1 backup com RMAN FULL e um Standby Manual. Um certo dia um usuario rodou um Update sem a clausula Where. Pronto ai você já viu o estrago. Com isso o Usuário aciona o DBA Junior. O DBA Junior sem muita experiência se desespera e ja pensa em voltar o backup com Export, sendo que um FlashBack Table nesta situação resolveria em minutos. Com isso o DBA Junior limpa todo o Usuario(schema) no qual estava o problema, mas esqueceu de verificar o backup Export se tinha realmente executado com sucesso. Como vocês já devem estar imaginando o backup Export não rodou por falta de espaço no Servidor e pra ajudar piorou a situação. Um certo dia nas minhas férias recebo uma ligação do DBA Junior desesperado porque tinha removido o Usuario(Schema) e não tinha backup. Bom, resumindo minha estratégia para este problema foi:

• Voltar o Backup RMAN em outra maquina e exportar apenas o schema dropado e importar para o Banco de Dados Produção. Tive que agir com esta estratégia porque o Usuario(schema) dropado estava na mesma Tablespace que varios outros Usuario de bases de clientes diferentes.

Devido esta situação neste post estarei demonstrando um EXEMPLO de como voltar de forma ágil e fácil um backup RMAN FULL considerando que ja temos o backup em mãos:

CONSIDERANDO O SEGUINTE AMBIENTE:

• Banco de Dados: Oracle 10g 10.2.0.1 – Enterprise Edition
• Sistema Operacional: Red Hat Advanced Server 4 Update 4
• Modo de arquivamento: Archivelog Ativo.
• Backup: Backup RMAN FULL (Online, Inconsistente, Hot).
• IMPORTANTE: Ter em mãos o DBID=1207426900
• Objetivo: voltar o Backup até o ultimo ponto de recuperação possível.

1- PREPARAR UM NOVO SERVIDOR PARA RESTAURAR O BACKUP

• Prepara um novo Servidor com Oracle instalado para restauração do Backup RMAN
• Instalar apenas o Software Oracle

2- RESTAURAR O SPFILE

RMAN TARGET /
RMAN>SET DBID=1207426900
RMAN>startup nomount
RMAN>restore spfile from '/oracle10g/backupRMAN/backup_FULL_RMAN_220609/controlfile.ctlc-1207426900-20090622-00';
Starting restore at 22-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /oracle10g/backupRMAN/backup_FULL_RMAN_220609/controlfile.ctlc-1207426900-20090622-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 22-JUN-09
RMAN> shutdown immediate
Oracle instance shut down

3- RESTAURAR O CONTROLFILE

RMAN>SET DBID=1207426900

RMAN> startup nomount

database is already started

RMAN> restore controlfile from '/oracle10g/backupRMAN/backup_FULL_RMAN_220609/controlfile.ctlc-1207426900-20090622-00';

Starting restore at 22-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1091 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/oracle10g/oradata/BSM/control01.ctl
output filename=/oracle10g/oradata/BSM/control02.ctl
output filename=/oracle10g/oradata/BSM/control03.ctl
Finished restore at 22-JUN-09

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

4- SINCRONIZAR O BACKUP

RMAN> CROSSCHECK BACKUP;
RMAN> CROSSCHECK COPY;
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;
RMAN> DELETE EXPIRED BACKUP;
RMAN> delete obsolete device type disk;
RMAN> list backup;

5- RESTAURAR O BANCO DE DADOS ORACLE

RMAN> restore database;

Starting restore at 22-JUN-09
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /oracle10g/oradata/BSM/undotbs01.dbf
restoring datafile 00005 to /oracle10g/BSM01.dbf
restoring datafile 00006 to /oracle10g/BSM02.dbf
restoring datafile 00007 to /oracle10g/BSM03.dbf
restoring datafile 00008 to /oracle10g/BSM04.dbf
restoring datafile 00009 to /oracle10g/BSM05.dbf
restoring datafile 00020 to /oracle10g/OBM01.dbf
restoring datafile 00021 to /oracle10g/OBM02.dbf
restoring datafile 00024 to /oracle10g/HINT01.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_                                                                                                          RMAN_220609/backupfullBSM_20090622_qpki6ahi_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qpki6ahi_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:17:35
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /oracle10g/oradata/BSM/sysaux01.dbf
restoring datafile 00013 to /oracle10g/oradata/BSM/RMAN01.dbf
restoring datafile 00015 to /oracle10g/BSMLOG02.dbf
restoring datafile 00016 to /oracle10g/oradata/MGA01.dbf
restoring datafile 00017 to /oracle10g/oradata/MGA02.dbf
restoring datafile 00018 to /oracle10g/oradata/ELOP01.dbf
restoring datafile 00019 to /oracle10g/oradata/ELOP02.dbf
restoring datafile 00022 to /oracle10g/oradata/_NOVA01.dbf
restoring datafile 00023 to /oracle10g/oradata/_NOVA02..dbf
restoring datafile 00025 to /oracle10g/BSMLOB01.dbf
restoring datafile 00031 to /oracle10g/oradata/BSM301.dbf
restoring datafile 00038 to /oracle10g/oradata/BSM/GERAL01.dbf
restoring datafile 00054 to /oracle10g/oradata/UNTI.dbf
restoring datafile 00060 to /oracle10g/oradata/BSM/BSMREPLIC.dbf
restoring datafile 00061 to /oracle10g/oradata/_CNAB.dbf
restoring datafile 00068 to /oracle10g/IDX.dbf
restoring datafile 00071 to /oracle10g/oradata/FAT01.dbf
restoring datafile 00085 to /oracle10g/oradata/BSM/USR01.dbf
restoring datafile 00086 to /oracle10g/oradata/BSM/USR02.dbf
restoring datafile 00091 to /oracle10g/BSM19.dbf
restoring datafile 00093 to /oracle10g/oradata/BSM/BSMFINANC.dbf
restoring datafile 00095 to /oracle10g/oradata/BSM/INSTERT.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qqki6c7g_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qqki6c7g_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:09:46
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /oracle10g/oradata/OWERSDF01.dbf
restoring datafile 00026 to /oracle10g/BSM06.dbf
restoring datafile 00027 to /oracle10g/BSM07.dbf
restoring datafile 00028 to /oracle10g/BSM08.dbf
restoring datafile 00029 to /oracle10g/BSM09.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qrki6d2i_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qrki6d2i_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:12:26
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00030 to /oracle10g/BSM10.dbf
restoring datafile 00032 to /oracle10g/HIPTRE02.dbf
restoring datafile 00033 to /oracle10g/ACERT01.dbf
restoring datafile 00062 to /oracle10g/oradata/BSM/WERWER01.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qski6e8h_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qski6e8h_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:04:35
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle10g/oradata/BSM/system01.dbf
restoring datafile 00014 to /oracle10g/BSMLOG01.dbf
restoring datafile 00034 to /oracle10g/ACERT02.dbf
restoring datafile 00035 to /oracle10g/PERT01.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qtki6ek9_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qtki6ek9_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:02:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /oracle10g/oradata/BSM/users01.dbf
restoring datafile 00036 to /oracle10g/PERT02.dbf
restoring datafile 00037 to /oracle10g/BSM_DAD01.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_quki6eqc_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_quki6eqc_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00010 to /oracle10g/oradata/KBSM01.dbf
restoring datafile 00039 to /oracle10g/MWER01.dbf
restoring datafile 00040 to /oracle10g/MWER02.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qvki6etv_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qvki6etv_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00012 to /oracle10g/oradata/OWER02.dbf
restoring datafile 00075 to /oracle10g/BSM11.dbf
restoring datafile 00076 to /oracle10g/BSM12.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r0ki6f18_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r0ki6f18_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:06:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00052 to /oracle10g/oradata/GWER01.dbf
restoring datafile 00081 to /oracle10g/BSM13.dbf
restoring datafile 00082 to /oracle10g/BSM14.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r1ki6fle_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r1ki6fle_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:05:06
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00053 to /oracle10g/oradata/GWER02.dbf
restoring datafile 00087 to /oracle10g/BSM15.dbf
restoring datafile 00088 to /oracle10g/BSM16.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r2ki6g57_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r2ki6g57_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:06:06
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00074 to /oracle10g/oradata/GWER03.dbf
restoring datafile 00089 to /oracle10g/BSM17.dbf
restoring datafile 00090 to /oracle10g/BSM18.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r3ki6go4_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r3ki6go4_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:06:06
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00092 to /oracle10g/BSM20.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r4ki6hb2_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r4ki6hb2_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:02:15
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00107 to /oracle10g/BSM21.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r5ki6hi3_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r5ki6hi3_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00108 to /oracle10g/BSM22.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r6ki6hjg_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r6ki6hjg_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 22-JUN-09

6- RECUPERAR O BANCO DE DADOS

RMAN> recover database;

Starting recover at 22-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1088 devtype=DISK
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=17486
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/Archivelog_BSM_r8ki6hl4.arc
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/Archivelog_BSM_r8ki6hl4.arc tag=TAG20090622T040139
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
archive log filename=/oracle9/1_17486_613563284.arc thread=1 sequence=17486
unable to find archive log
archive log thread=1 sequence=17487
RMAN-00571: =======================================
RMAN-00569: ======= ERROR MESSAGE STACK FOLLOWS ==========
RMAN-00571: =======================================
RMAN-03002: failure of recover command at 06/22/2009 14:07:33
RMAN-06054: media recovery requesting unknown log: thread 1 seq 17487 lowscn 6611212706

7- ABRIR O BANCO DE DADOS COM RESETLOGS

RMAN> alter database open resetlogs;

database opened

RMAN>

PRONTO!! BANCO DE DADOS RECUPERADO COM SUCESSO!!

Abraço