Um datafile foi apagado do banco de de dados, e justo quando o DBA Sênior está de férias? Você está desesperado?
Seus problemas acabaram ! Se você estiver usando o Oracle Database 11g…
Eu não gosto muito de Advisors / Wizards, prefiro fazer o trabalho eu mesmo. Mas vi esta New Feature na documentação, e fui testar. Funciona mesmo, e sem precisar do EM.
Removi um datafile, perguntei ao RMAN qual era o problema (LIST FAILURE), perguntei qual era a solução (ADVISE FAILURE) e pedi para executar a correção (REPAIR FAILURE).
É gerado até um script com o que foi executado, e armazenado para uma futura consulta. Só faltou ele dizer em voz metálica: “Olá, Sr. DBA, apagaram um arquivo meu, e eu gostaria de fazer um restore. O Sr. me permite?”
Depois vou testar esta funcionalidade em algum cenário mais difícil para ver como este Advisor se sai… Tomara que ele se dê mal. 😉
[oracle@labdb ~]$ rman CATALOG=RMAN_USER/RMAN_USER@ORCL TARGET / Recovery Manager: Release 11.1.0.7.0 - Production on Tue Jul 28 10:35:17 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: ORCL (DBID=1217465568) connected to recovery catalog database RMAN> SHUTDOWN IMMEDIATE; database closed database dismounted Oracle instance shut down RMAN> EXIT; Recovery Manager complete. [oracle@labdb ~]$ mv /u01/app/oradata/ORCL/example01.dbf /u01/app/oradata/ORCL/example01.moved [oracle@labdb ~]$ rman CATALOG=RMAN_USER/RMAN_USER@ORCL TARGET / Recovery Manager: Release 11.1.0.7.0 - Production on Tue Jul 28 10:36:39 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database (not started) RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-04004: error from recovery catalog database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor [oracle@labdb ~]$ rman TARGET / Recovery Manager: Release 11.1.0.7.0 - Production on Tue Jul 28 10:36:45 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database (not started) RMAN> STARTUP Oracle instance started database mounted RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 07/28/2009 10:37:00 ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/u01/app/oradata/ORCL/example01.dbf' RMAN> LIST FAILURE; using target database control file instead of recovery catalog List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 202 HIGH OPEN 28-JUL-09 One or more non-system datafiles are missing RMAN> ADVISE FAILURE; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 202 HIGH OPEN 28-JUL-09 One or more non-system datafiles are missing analyzing automatic repair options; this may take some time allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=208 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=206 device type=DISK analyzing automatic repair options complete Mandatory Manual Actions ======================== no manual actions available Optional Manual Actions ======================= 1. If file /u01/app/oradata/ORCL/example01.dbf was unintentionally renamed or moved, restore it 2. If a standby database is available, then consider a Data Guard switchover or failover Automated Repair Options ======================== Option Repair Description ------ ------------------ 1 Restore and recover datafile 5 Strategy: The repair includes complete media recovery with no data loss Repair script: /u01/app/diag/rdbms/orcl/ORCL/hm/reco_4260100358.hm RMAN> REPAIR FAILURE PREVIEW; Strategy: The repair includes complete media recovery with no data loss Repair script: /u01/app/diag/rdbms/orcl/ORCL/hm/reco_4260100358.hm contents of repair script: # restore and recover datafile restore datafile 5; recover datafile 5; RMAN> REPAIR FAILURE; Strategy: The repair includes complete media recovery with no data loss Repair script: /u01/app/diag/rdbms/orcl/ORCL/hm/reco_4260100358.hm contents of repair script: # restore and recover datafile restore datafile 5; recover datafile 5; Do you really want to execute the above repair (enter YES or NO)? YES executing repair script Starting restore at 28-JUL-09 using channel ORA_DISK_1 using channel ORA_DISK_2 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 00005 to /u01/app/oradata/ORCL/example01.dbf channel ORA_DISK_1: reading from backup piece /u02/backup/ORCL_20090728_87_1 channel ORA_DISK_1: piece handle=/u02/backup/ORCL_20090728_87_1 tag=TAG20090728T102901 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:35 Finished restore at 28-JUL-09 Starting recover at 28-JUL-09 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 28-JUL-09 repair failure complete Do you want to open the database (enter YES or NO)? YES database opened RMAN> EXIT; Recovery Manager complete. [oracle@labdb ~]$
RSRSRSRS Como estou começando no mundo do oracle, vi esta opção com o advise, mas achei que já havia na versões anteriores; Conversando com um colega que é DBA desde o 8i parece que é uma tedência A ORACLE automatizar estas tarefas 🙂 que antes parecia de outro mundo rsrsrsrss