Hi,
I will explain RMAN Data Recovery Advisor feature in Oracle in this article.
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/