Use
Data Recovery Advisor to Repair Failures
Before You Begin
This 15-minute tutorial shows you how to use Data Recovery Advisor to automatically list and repair failures.
Select the Oracle Database release:
Background
The Oracle advised recovery 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.
What Do You Need?
- Oracle Database 18c19c
Perform
Oracle Advised Recovery
- 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 NAME -------------------------------------------------------------------------------- /scratch/u01/app/oracle/product/18.0.0/dbhome_1/dbs/appts.dbf 12 rows selected.
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 NAME -------------------------------------------------------------------------------- /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/18.0.0/dbhome_1/dbs/appts.dbf $HOME/appts.bkupmv /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_ORDERStable 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/18.0.0/dbhome_1/dbs/appts.dbf'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 18.0.0.0.0 - Production on Sun Mar 31 23:35:43 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. target database Password: <enter password> connected to target database: ORCL (DBID=1530541453)Recovery Manager: Release 19.0.0.0.0 - Production on Sun Mar 31 23: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 FAILUREcommand.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 31-MAR-19 One or more non-system datafiles are missing - Determine repair options, both automatic and manual, by
executing the
ADVISE FAILUREcommand.RMAN> advise failure;Click the output.txt to see the output.
- Correct the problem by executing the
REPAIR FAILUREcommand.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)? yesClick the output.txt to see the output.
- Exit from RMAN.
RMAN> exit Recovery Manager complete. - Log in to SQL*Plus as sysdba.
$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 31 23:41:25 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 31 23:41:25 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 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_ORDERStable by executing the following command.SQL> alter session set container=orclpdb; Session altered.SQL> select * from appuser.purchase_orders;PO_NUMBER ---------- PO_DESCRIPTION -------------------------------------------------------------------------------- PO_DATE PO_VENDOR PO_DATE_R PO_REQUESTOR_NAME --------- ---------- --------- ---------------------------------------- 1 Office Equipment 25-MAY-12 1201 13-JUN-12 2 Computer System 18-JUN-12 1201 27-JUN-12 PO_NUMBER ---------- PO_DESCRIPTION -------------------------------------------------------------------------------- PO_DATE PO_VENDOR PO_DATE_R PO_REQUESTOR_NAME --------- ---------- --------- ---------------------------------------- 3 Travel Expense 26-JUN-12 1340 11-JUL-12 - Exit from SQL*Plus.