E agora o que fazer???

O banco caiu e não sobe… e agora?Olá DBA ! Tudo bem?! ELA CHEGOUUUU!!!!!! Viva!!! Até que enfim é sexta-feira! E de feriadão ainda hein??? Aproveite! Curta, brinque pule, porém, sem perder a dignidade. rsrsrs.
Eu particularmente não curto a festa do Carnaval, porém o feriado sempre é bom para dar uma quebrada na rotina. Ficar com a família, amigos, enfim cumprir com a maior missão do ser humano: Ser feliz.
Mas, como ainda é sexta-feira, dia útil e vida de DBA é isso mesmo, eu sempre falo que passamos a semana inteira com todo o ambiente funcionando redondinho, chega na sexta-feira, pronto… Começam os “pepinos”, “abacaxis”, a feira inteira inventa de acontecer nos 48 minutos do segundo tempo. Vamos lá DBA! Hoje é sexta-feira, 13:20 da tarde você na contagem regressiva e alguém grita: “CAIU O BANCO DE DADOS do sistema…
Banco caiu é complicação pra nossa vida, pode ser problema à vista… E como disse o sábio Murphy… É claro que após verificar você percebe que realmente é problema.
O que fazer? Chorar? Tentar botar  a culpa em alguém? Sair correndo? Pedir demissão?
NÃO DBA! Por mais que sinto um desejo enorme de fazer qualquer uma destas coisas, use nossa técnica. Respire fundo e aproveite para aumentar o seu portfólio de problemas resolvidos e clientes felizes, Portanto FAÇA e depois não esqueça da teoria da galinha… hehehe avise a todos que VOCÊ fez.

Segue abaixo um roteiro completo de como proceder para restaurar um banco de dados onde foi perdido tudo.
Siga ele conforme indicado, caso tenha alguma dúvida jamais exite! Poste sua dúvida! Eu não garanto saber tudo, ninguém sabe! Mas prometo responder tudo!

====================================================
– Banco de Dados: Oracle 11g 11.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 ‘/oracle11g/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: /oracle11g/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 ‘/oracle11g/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=/oracle11g/oradata/ORCL/control01.ctl
output filename=/oracle11g/oradata/ORCL/control02.ctl
output filename=/oracle11g/oradata/ORCL/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 /oracle11g/oradata/ORCL/undotbs01.dbf
restoring datafile 00005 to /oracle11g/ORCL01.dbf
restoring datafile 00006 to /oracle11g/ORCL02.dbf
restoring datafile 00007 to /oracle11g/ORCL03.dbf
restoring datafile 00008 to /oracle11g/ORCL04.dbf
restoring datafile 00009 to /oracle11g/ORCL05.dbf
restoring datafile 00020 to /oracle11g/OBM01.dbf
restoring datafile 00021 to /oracle11g/OBM02.dbf
restoring datafile 00024 to /oracle11g/HINT01.dbf
channel ORA_DISK_1: reading from backup piece /oracle11g/backupRMAN/backup_FULL_                                                                                                          RMAN_220609/backupfullORCL_20090622_qpki6ahi_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_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 /oracle11g/oradata/ORCL/sysaux01.dbf
restoring datafile 00013 to /oracle11g/oradata/ORCL/RMAN01.dbf
restoring datafile 00015 to /oracle11g/ORCLLOG02.dbf
restoring datafile 00016 to /oracle11g/oradata/MGA01.dbf
restoring datafile 00017 to /oracle11g/oradata/MGA02.dbf
restoring datafile 00018 to /oracle11g/oradata/ELOP01.dbf
restoring datafile 00019 to /oracle11g/oradata/ELOP02.dbf
restoring datafile 00022 to /oracle11g/oradata/_NOVA01.dbf
restoring datafile 00023 to /oracle11g/oradata/_NOVA02..dbf
restoring datafile 00025 to /oracle11g/ORCLLOB01.dbf
restoring datafile 00031 to /oracle11g/oradata/ORCL301.dbf
restoring datafile 00038 to /oracle11g/oradata/ORCL/GERAL01.dbf
restoring datafile 00054 to /oracle11g/oradata/UNTI.dbf
restoring datafile 00060 to /oracle11g/oradata/ORCL/ORCLREPLIC.dbf
restoring datafile 00061 to /oracle11g/oradata/_CNAB.dbf
restoring datafile 00068 to /oracle11g/IDX.dbf
restoring datafile 00071 to /oracle11g/oradata/FAT01.dbf
restoring datafile 00085 to /oracle11g/oradata/ORCL/USR01.dbf
restoring datafile 00086 to /oracle11g/oradata/ORCL/USR02.dbf
restoring datafile 00091 to /oracle11g/ORCL19.dbf
restoring datafile 00093 to /oracle11g/oradata/ORCL/ORCLFINANC.dbf
restoring datafile 00095 to /oracle11g/oradata/ORCL/INSTERT.dbf
channel ORA_DISK_1: reading from backup piece /oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_20090622_qqki6c7g_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_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 /oracle11g/oradata/OWERSDF01.dbf
restoring datafile 00026 to /oracle11g/ORCL06.dbf
restoring datafile 00027 to /oracle11g/ORCL07.dbf
restoring datafile 00028 to /oracle11g/ORCL08.dbf
restoring datafile 00029 to /oracle11g/ORCL09.dbf
channel ORA_DISK_1: reading from backup piece /oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_20090622_qrki6d2i_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_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 /oracle11g/ORCL11.dbf
restoring datafile 00032 to /oracle11g/HIPTRE02.dbf
restoring datafile 00033 to /oracle11g/ACERT01.dbf
restoring datafile 00062 to /oracle11g/oradata/ORCL/WERWER01.dbf
channel ORA_DISK_1: reading from backup piece /oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_20090622_qski6e8h_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_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 /oracle11g/oradata/ORCL/system01.dbf
restoring datafile 00014 to /oracle11g/ORCLLOG01.dbf
restoring datafile 00034 to /oracle11g/ACERT02.dbf
restoring datafile 00035 to /oracle11g/PERT01.dbf
channel ORA_DISK_1: reading from backup piece /oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_20090622_qtki6ek9_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_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 /oracle11g/oradata/ORCL/users01.dbf
restoring datafile 00036 to /oracle11g/PERT02.dbf
restoring datafile 00037 to /oracle11g/ORCL_DAD01.dbf
channel ORA_DISK_1: reading from backup piece /oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_20090622_quki6eqc_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_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 /oracle11g/oradata/KORCL01.dbf
restoring datafile 00039 to /oracle11g/MWER01.dbf
restoring datafile 00040 to /oracle11g/MWER02.dbf
channel ORA_DISK_1: reading from backup piece /oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_20090622_qvki6etv_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_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 /oracle11g/oradata/OWER02.dbf
restoring datafile 00075 to /oracle11g/ORCL11.dbf
restoring datafile 00076 to /oracle11g/ORCL12.dbf
channel ORA_DISK_1: reading from backup piece /oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_20090622_r0ki6f18_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_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 /oracle11g/oradata/GWER01.dbf
restoring datafile 00081 to /oracle11g/ORCL13.dbf
restoring datafile 00082 to /oracle11g/ORCL14.dbf
channel ORA_DISK_1: reading from backup piece /oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_20090622_r1ki6fle_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_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 /oracle11g/oradata/GWER02.dbf
restoring datafile 00087 to /oracle11g/ORCL15.dbf
restoring datafile 00088 to /oracle11g/ORCL16.dbf
channel ORA_DISK_1: reading from backup piece /oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_20090622_r2ki6g57_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_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 /oracle11g/oradata/GWER03.dbf
restoring datafile 00089 to /oracle11g/ORCL17.dbf
restoring datafile 00090 to /oracle11g/ORCL18.dbf
channel ORA_DISK_1: reading from backup piece /oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_20090622_r3ki6go4_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_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 /oracle11g/ORCL20.dbf
channel ORA_DISK_1: reading from backup piece /oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_20090622_r4ki6hb2_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_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 /oracle11g/ORCL21.dbf
channel ORA_DISK_1: reading from backup piece /oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_20090622_r5ki6hi3_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_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 /oracle11g/ORCL22.dbf
channel ORA_DISK_1: reading from backup piece /oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_20090622_r6ki6hjg_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/backupfullORCL_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 /oracle11g/backupRMAN/backup_FULL_RMAN_220609/Archivelog_ORCL_r8ki6hl4.arc
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle11g/backupRMAN/backup_FULL_RMAN_220609/Archivelog_ORCL_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!!
====================================================

Perfeito DBA Parabéns!!! Banco no ar, usuários satisfeitos! Carnaval garantido!
Feste brinque pule seja feliz, mas jamais esqueça do seu compromisso maior: FOCO NA META!!!

Sobre raul andrade

DBA e Instrutor Oracle, apaixonado pela minha família e por ensinar.
Esta entrada foi publicada em DBA Oracle e marcada com a tag , , , . Adicione o link permanente aos seus favoritos.

2 respostas para E agora o que fazer???

  1. Diego disse:

    Muito bom Raul estou gostando muito dos posts, estou sempre acompanhando. Forte Abraço

Deixe uma resposta