RMAN Restore or Duplicate Performs Implicit Crosschecking and Cataloging (Doc ID 1489027.1)

In this Document

Symptoms
Cause
Solution
References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.


SYMPTOMS

After restoring a controlfile, the restore database can take a long time to restore due to implicit croscchecking and cataloging.

RMAN> restore database;

RMAN-03090: Starting implicit crosscheck backup at 2012/10/05 01:56:55
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=63 device type=DISK
RMAN-06206: Crosschecked XXXX  objects                               ------> Time taken <------
RMAN-03091: Finished implicit crosscheck backup at 2012/10/05 02:57:02

RMAN-03090: Starting implicit crosscheck copy at 2012/10/05 02:57:02
RMAN-12016: using channel ORA_DISK_1                                 ------> Time taken <-------
RMAN-03091: Finished implicit crosscheck copy at 2012/10/05 03:57:02

RMAN-07501: searching for all files in the recovery area
RMAN-07507: cataloging files...
RMAN-07508: cataloging done                                          ------> Time taken <-------

 

Sometimes this may also inadvertently introduce orphaned incarnations and lead to issues such as these:

a) duplicate failed during the recovery phase:

RMAN-03002: failure of Duplicate Db command at 08/22/2014 11:43:47
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-10032: unhandled exception during execution of job step 1:
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until change 3996570506 using backup controlfile
RMAN-11001: Oracle Error:
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '<path>/<filename'
  b) Cannot restore from a known backup:
RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1     <db_name>  543219554        PARENT  1          12-MAR-2008 00:38:41
2       2     <db_name>  543219554        PARENT  602821     25-MAY-2014 01:24:52
4       4     <db_name>  543219554        ORPHAN  8618277    02-JUL-2014 15:11:35
7       7     <db_name>  543219554        ORPHAN  8999502    22-JUN-2014 17:04:48
3       3     <db_name>  543219554        ORPHAN  12377463   10-JUL-2014 02:45:40
9       9     <db_name>  543219554        ORPHAN  13414195   15-JUL-2014 15:57:28
8       8     <db_name>  543219554        ORPHAN  13906671   28-JUL-2014 11:25:03
5       5     <db_name>  543219554        ORPHAN  22434478   28-AUG-2014 19:14:15
6       6     <db_name>  543219554        CURRENT 23304847   02-SEP-2014 19:33:04


RMAN> run {
set until TIME "TO_DATE('2014-09-08:20:41:00','yyyy-dd-mm:hh24:mi:ss')";
RESTORE DATABASE;
}
2> 3> 4>
executing command: SET until clause
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 09/09/2014 00:45:39
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
  

CAUSE

The implicit crosscheck and cataloging is only seen when using the fast recovery area.

When using a restored controlfile to restore the database, we need to ensure that the controlfile metadata is still correct. So RMAN automatically crossheck all backups and catalogs the flash recovery area. This is a one time operation. So the first time a restore of any datafile is initiated, we will crosscheck and catalog the backuppieces. Subsequent restores from the same controlfile will not kick off the crosscheck and catalog operation.

It is not uncommon to have 1000's  of records stored within the controlfile that will require crosschecking.

Once the crosschecking is complete RMAN continues with cataloging the fast recovery area.

This task can potentially take hours to complete.

Further, if a new incarnation is introduced you may have issues with recovery due to the orphaned incarnation.

 

SOLUTION

Workarounds:

1) Disable the fast recovery area (FRA). 

 SQL> alter system set db_recovery_file_dest='' scope=both;

 

Remember to re-enable the FRA at a later point once the restore (or recovery) is completed.

 OR

2) Remove the old files, not needed files from the flashback recovery area.

Please note in 11gr2 you have option to specify incarnation

How to duplicate a database to a previous Incarnation Document 293717.1

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more