RMAN Data Recovery Advisor in Oracle

Hi,

I will explain RMAN Data Recovery Advisor feature in Oracle in this article.

 

2-dra

 

 

There are many advisors who direct DBAs and facilitate their work In Oracle Database like following. Using these advisors, you can perform some tasks much more easily.

  • Data Recovery Advisor (DRA)
  • SQL Tuning Advisor
  • SQL Access Advisor
  • Memory Advisor
  • Undo Advisor

 

 

Data Recovery Advisor is a advisor that analyzes the issues related to the corruptions, Datafile, Controlfile losses, RMAN and reports problems and repair them if you want. Data recovery advisor doesn’t support RAC (Real Application Cluster) database. When you run this advisor in the RAC environment, you encounter the following error.

 

RMAN> LIST FAILURE;
using target database control file instead of recovery catalog
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of list command at 09/21/2011 13:43:53
 RMAN-05533: LIST FAILURE is not supported on RAC database
RMAN>

 

let’s go to make an example to understand Data recovery advisor easily.

 

 

 

When i startup Oracle database, i got following errors.

 

[oracle@database ~]$ . profile
 [oracle@database ~]$ sqlplus / as sysdba

Connected to an idle instance.

SQL> select instance_name from v$instance;

INSTANCE_NAME
 ----------------
 POCDB

SQL> startup
 ORACLE instance started.

Total System Global Area 538640384 bytes
 Fixed Size 2230112 bytes
 Variable Size 339740832 bytes
 Database Buffers 192937984 bytes
 Redo Buffers 3731456 bytes
 Database mounted.
 ORA-00338: log 2 of thread 1 is more recent than control file
 ORA-00312: online log 2 thread 1:
 '/u01/app/oracle/oradata/POCDB/POCDB/onlinelog/o1_mf_2_9pdlq8p8_.log'


 

What should we do now ? Do not be afraid, let’s run the Data recovery advisor.

 

Step 1: Connect to RMAN and execute list failure command.

 

[oracle@database ~]$ rman target /

connected to target database: POCDB (DBID=2629568568, not open)

RMAN> list failure;

using target database control file instead of recovery catalog
 List of Database Failures
 =========================

Failure ID Priority Status Time Detected Summary
 ---------- -------- --------- ------------- -------
 462 CRITICAL OPEN 29-MAY-14 Control file is older than datafiles and/or log files

 

 

Step 2: Let’s ask failure tool to advise you failures.

 RMAN> advise failure;

List of Database Failures
 =========================

Failure ID Priority Status Time Detected Summary
 ---------- -------- --------- ------------- -------
 462 CRITICAL OPEN 29-MAY-14 Control file is older than datafiles and/or log files

analyzing automatic repair options; this may take some time
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=19 device type=DISK
 analyzing automatic repair options complete

Mandatory Manual Actions
 ========================
 no manual actions available

Optional Manual Actions
 =======================
 1. If you have the correct version of the control file, then shutdown the database and replace the old control file

Automated Repair Options
 ========================
 Option Repair Description
 ------ ------------------
 1 Restore a backup control file
 Strategy: The repair includes complete media recovery with no data loss
 Repair script: /u01/app/oracle/diag/rdbms/pocdb/POCDB/hm/reco_855908832.hm





Step 3: RMAN advised us some solutions, now we will say to our Advisor that please repair failures what you find.

 

 RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
 Repair script: /u01/app/oracle/diag/rdbms/pocdb/POCDB/hm/reco_855908832.hm

contents of repair script:
 # restore control file
 shutdown;
 startup nomount;
 set dbid 2629568568;
 restore controlfile from autobackup;
 sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)? yes
 executing repair script

database dismounted
 Oracle instance shut down

connected to target database (not started)
 Oracle instance started

Total System Global Area 538640384 bytes

Fixed Size 2230112 bytes
 Variable Size 339740832 bytes
 Database Buffers 192937984 bytes
 Redo Buffers 3731456 bytes

executing command: SET DBID

Starting restore at 29-MAY-14
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140529
 channel ORA_DISK_1: AUTOBACKUP found: c-2629568568-20140529-06
 channel ORA_DISK_1: restoring control file from AUTOBACKUP c-2629568568-20140529-06
 channel ORA_DISK_1: control file restore from AUTOBACKUP complete
 output file name=/u01/app/oracle/oradata/POCDB/controlfile/control.ctl
 Finished restore at 29-MAY-14

sql statement: alter database mount
 released channel: ORA_DISK_1
 repair failure complete

RMAN>


Step 4: RMAN Data recovery advisor solve the problem and open database in mount mode. Now let’s go to open database read write mode.

 SQL> alter database open resetlogs;
 alter database open resetlogs
 *
 ERROR at line 1:
 ORA-01194: file 1 needs more recovery to be consistent
 ORA-01110: data file 1: '/u01/app/oracle/oradata/POCDBAUX/system01.dbf'
 SQL>

 

 

Database could not be opened again with different errors. Now i will apply Data recovery advisor again 😉

Step 5: List failures.

 

 [oracle@database ~]$ rman target /

 connected to target database: POCDB (DBID=2629568568, not open)

RMAN> list failure;

using target database control file instead of recovery catalog
 List of Database Failures
 =========================

Failure ID Priority Status Time Detected Summary
 ---------- -------- --------- ------------- -------
 510 CRITICAL OPEN 29-MAY-14 System datafile 1: '/u01/app/oracle/oradata/POCDBAUX/system01.dbf' needs media recovery
 507 CRITICAL OPEN 29-MAY-14 Control file needs media recovery
 513 HIGH OPEN 29-MAY-14 One or more non-system datafiles need media recovery






Step 6: Advisor found some errors, execute advise failures.

 

RMAN> advise failure;

List of Database Failures
 =========================

Failure ID Priority Status Time Detected Summary
 ---------- -------- --------- ------------- -------
 510 CRITICAL OPEN 29-MAY-14 System datafile 1: '/u01/app/oracle/oradata/POCDBAUX/system01.dbf' needs media recovery
 507 CRITICAL OPEN 29-MAY-14 Control file needs media recovery
 513 HIGH OPEN 29-MAY-14 One or more non-system datafiles need media recovery
 analyzing automatic repair options; this may take some time
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=20 device type=DISK
 analyzing automatic repair options complete

Mandatory Manual Actions
 ========================
 no manual actions available

Optional Manual Actions
 =======================
 1. If you have the correct version of the control file, then shutdown the database and replace the old control file
 2. If you restored the wrong version of data file /u01/app/oracle/oradata/POCDBAUX/system01.dbf, then replace it with the correct one
 3. If you restored the wrong version of data file /u01/app/oracle/oradata/POCDBAUX/sysaux01.dbf, then replace it with the correct one
 4. If you restored the wrong version of data file /u01/app/oracle/oradata/POCDBAUX/undotbs01.dbf, then replace it with the correct one
 5. If you restored the wrong version of data file /u01/app/oracle/oradata/POCDBAUX/users01.dbf, then replace it with the correct one
 6. If you restored the wrong version of data file /u01/app/oracle/oradata/POCDBAUX/rmancat.dbf, then replace it with the correct one

Automated Repair Options
 ========================
 Option Repair Description
 ------ ------------------
 1 Recover database
 Strategy: The repair includes complete media recovery with no data loss
 Repair script: /u01/app/oracle/diag/rdbms/pocdb/POCDB/hm/reco_1698080840.hm


 

 

 

Step 7: RMAN advised us again some solutions, now we will say to our Advisor that please repair failures what you find.

 

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
 Repair script: /u01/app/oracle/diag/rdbms/pocdb/POCDB/hm/reco_1698080840.hm

contents of repair script:
 # recover database
 recover database;
 alter database open resetlogs;

Do you really want to execute the above repair (enter YES or NO)? yes
 executing repair script

Starting recover at 29-MAY-14
 using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/POCDB/POCDB/onlinelog/o1_mf_1_9pdlq852_.log
 archived log file name=/u01/app/oracle/oradata/POCDB/POCDB/onlinelog/o1_mf_1_9pdlq852_.log thread=1 sequence=1
 media recovery complete, elapsed time: 00:00:00
 Finished recover at 29-MAY-14

database opened
 repair failure complete

RMAN>

 

 

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

One comment

  1. I came here for “RMAN-05533: LIST FAILURE is not supported on RAC database”
    Sure it doesn’t work. So how do you solve it? I do not understand. Because all you did is to connect to your CDB and it worked… How?! This is really confusing explanation.
    Of course I can just manually restore corrupted files. But advised doesn’t work at all in RAC

Leave a Reply

Your email address will not be published. Required fields are marked *