Data Recovery Advisor in Oracle Database 19c

Data Recovery Advisor in Oracle Database 19c

Alireza Kamrani
08/March/2025

The Oracle advised recovery(AR) feature uses Data Recovery Advisor, which is an Oracle Database feature that automatically diagnoses data failures, determines and presents appropriate repair options, and performs repairs if requested by the user.

Overview of Data Recovery Advisor

The Data Recovery Advisor is a tool that helps reduce database recovery time by determining the best automated repair option for database failures.

Starting in Oracle Database 19c, the Data Recovery Advisor (DRA) feature is deprecated.

The deprecation of DRA includes deprecation of the following Oracle Recovery Manager (RMAN) commands: LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE, and CHANGE FAILURE. Database administrators will no longer have access to these commands. There is no replacement feature for DRA.

Purpose of Data Recovery Advisor

Data Recovery Advisor is an Oracle Database tool that automatically diagnoses data failures, determines and presents appropriate repair options, and executes repairs at the user's request.

In this context, a data failure is a corruption or loss of persistent data on disk. By providing a centralized tool for automated data repair, Data Recovery Advisor improves the manageability and reliability of an Oracle database and thus helps reduce the MTTR.

Diagnosing a data failure and devising an optimal strategy for repair requires a high degree of training and experience. Data Recovery Advisor provides the following advantages over traditional repair techniques:

• Data Recovery Advisor can potentially detect, analyze, and repair data failures before a database process discovers the corruption and signals an error. Early warnings help limit damage caused by corruption.

• Manually assessing symptoms of data failures and correlating them into a problem statement can be complex, error-prone, and time-consuming. Data Recovery Advisor automatically diagnoses failures, assesses their impact, and reports these findings to the user.

• Traditionally, users must manually determine repair options along with the repair impact. If multiple failures are present, then users must determine the right sequence of repair execution and try to consolidate repairs. In contrast, Data Recovery Advisor automatically determines the best repair options and runs checks to ensure that these options are feasible in your environment.

• Execution of a data repair can be complex and error-prone. If you choose an automated repair option, then Data Recovery Advisor executes the repair and verifies its success.

Perform Oracle 19c Advised Recovery(AR) in Multitenant environment

In the first step run:





RMAN> validate database;
And check alert log to identify error about corruptions.

Identify Corrupt Blocks

You can use RMAN to check for corrupt blocks:

RMAN> BACKUP VALIDATE DATABASE;

Or for a specific table or tablespace:

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;

To identify corrupt blocks in a specific datafile:

RMAN> VALIDATE DATAFILE 5;

To check database views for corrupted blocks:

SELECT * FROM V$DATABASE_BLOCK_CORRUPTION


Query the V$DATAFILE view to determine the file name of the file that belongs to the APPTS tablespace.

SQL> select name from v$datafile;
NAME --------------------------------------------------------------------------------
/scratch/u01/app/oracle/oradata/ORCL/system01.dbf
/scratch/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/scratch/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/scratch/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
/scratch/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
/scratch/u01/app/oracle/oradata/ORCL/users01.dbf
/scratch/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
/scratch/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
/scratch/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
/scratch/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
/scratch/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf 
/scratch/u01/app/oracle/product/19.0.0/dbhome_1/dbs/appts.dbf
12 rows selected.

Enter the SQL*Plus host command to obtain an operating system prompt.

SQL> host

Use the Linux mv command to move the datafile belonging to the APPTS tablespace to $HOME/appts.bkup.

$ mv /scratch/u01/app/oracle/product/19.0.0/dbhome_1/dbs/appts.dbf  $HOME/appts.bkup

Return to SQL*Plus by entering the exit command.
$ exit

As the APPUSER resides in the pluggable database, switch session to the pluggable database container and query the APPUSER.PURCHASE_ORDERS table by executing the following command.

SQL> connect / as sysdba
Connected.

SQL> alter session set container=orclpdb;
Session altered.

SQL> select * from appuser.purchase_orders;

select * from appuser.purchase_orders
*
ERROR at line 1: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

Open the pluggable database.

Note that you get an error when you try to open the pluggable database.

SQL> alter database open;

alter database open
*
ERROR at line 1: ORA-01157: cannot identify/lock data file 13 - see DBWR trace file ORA-01110: data file 13: '/scratch/u01/app/oracle/product/19.0.0/dbhome_1/dbs/appts.dbf'

Exit from SQL*Plus.

Invoke RMAN and connect as the SYSBACKUP user.

$ rman target sysbackup

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Mar 03 12:35:43 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. target database Password: <enter password>
connected to target database: ORCL (DBID=1530541453)

List all the failures known to the Data Recovery Advisor by executing the LIST FAILURE command.

RMAN> list failure;

using target database control file instead of recovery catalog Database Role: PRIMARY
List of Database Failures =========================
Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- -------
22722 HIGH OPEN 3-MAR-25 One or more non-system datafiles are missing

Determine repair options, both automatic and manual, by executing the ADVISE FAILURE command.

RMAN> REPAIR FAILURE PREVIEW;

RMAN> advise failure;

output.txt :

Database Role: PRIMARY List of Database Failures =========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
22722 HIGH OPEN 3-MAR-25 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=22
device type=DISK analyzing automatic repair options complete Mandatory Manual Actions
========================
no manual actions available Optional Manual Actions
=======================
1. If file /scratch/u01/app/oracle/product/19.0.0/dbhome_1/dbs/appts.dbf was unintentionally renamed or moved, restore it Automated Repair Options
========================
Option Repair Description
------ ------------------

1 Restore and recover datafile 13 Strategy: The repair includes complete media recovery with no data loss Repair script: /scratch/u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_3633660278.hm


Correct the problem by executing the REPAIR FAILURE command.

RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss Repair script: /scratch/u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_3633660278.hm

contents of repair script:
# restore and recover datafile
sql  'ORCLPDB'  'alter database datafile 13 offline';
restore ( datafile 13 );
recover datafile 13;
sql  'ORCLPDB'  'alter database datafile 13 online';

Respond with "yes" to execute the repair script.
Do you really want to execute the above repair (enter YES or NO)? yes

output.txt :

sql statement:
alter database datafile 13 offline
Starting restore at 3-MAR-25 using channel ORA_DISK_1 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 00013 to /scratch/u01/app/oracle/product/19.0.0/dbhome_1/dbs/appts.dbf channel ORA_DISK_1: reading from backup piece /scratch/u01/app/oracle/recovery_area/ORCL/83F608325477C5A9E053193EC40A7267/backupset/25_03_29/o1_mf_nnndf_TAG20190329T005838_g9vn2kq5_.bkp channel ORA_DISK_1: piece handle=/scratch/u01/app/oracle/recovery_area/ORCL/83F608325477C5A9E053193EC40A7267/backupset/25_03_29/o1_mf_nnndf_TAG20190329T005838_g9vn2kq5_.bkp tag=TAG20190329T005838 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 31-MAR-25 Starting recover at 31-MAR-25 using channel ORA_DISK_1
starting media recovery media recovery complete, elapsed time: 00:00:00
Finished recover at 31-MAR-25
sql statement: alter database datafile 13 online repair failure complete

Exit from RMAN.

RMAN> exit
Recovery Manager complete.

Log in to SQL*Plus as sysdba.

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 03 12:41:25 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL>

Open the pluggable database. In this example, the pluggable database is orclpdb.

SQL> alter pluggable database orclpdb open; Pluggable database altered.

Once again, switch session to the pluggable database container and query the APPUSER.PURCHASE_ORDERS table by executing the following command.

SQL> alter session set container=orclpdb;
Session altered.


SQL> select count(*) from appuser.purchase_orders;
Count(*)
---‐----------
       3

Alireza Kamrani