Oracle Database 11g: Data Recovery Advisor

Oracle Database 11g: Data Recovery Advisor

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 ~]$