2019-05-28

Recovering a Database to a Previous Incarnation

Environment

Oracle 12.1, RMAN

Problem

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

Solution

Find a previous incarnation that you need.
Option 1: connect to a target database and catalog, run the following command and choose "Inc Key"
RMAN> LIST INCARNATION OF DATABASE DBPRD1;

Option 2: Connect to the recovery catalog, run the following command and choose DBINC_KEYB. Option 2 is more convenient as it provides more information, e.g. PARENT_DBINC_KEY.
select * from RC_DATABASE_INCARNATION where name='DBPRD1' order by DBINC_KEY;

Then use the incarnation key in RESET DATABASE, exempli gratia:
reset database to incarnation 3587196;
run {
set until time = "to_date('2019-05-27 10:00:00','YYYY-MM-DD HH24:MI:SS')";
allocate channel ch01 type 'SBT_TAPE' PARMS 'some parameters';
restore database;
recover database;
alter database open resetlogs;
}




2019-02-14

ORACLE data block corrupted after restoring database (2)

A test database was restored from production backup. Some object have NOLOGGING, so recovery for such object will fail to roll changes forward.
When such object is used, the following errors happen

ORA-01578: ORACLE data block corrupted (file # 42, block # 1029253)
ORA-01110: data file 42: '/data001/oradata/CDRSIT/cdrsit_STG_DATA_02.dbf'

Find the affected segment:

select segment_type, owner, segment_name, 
case segment_type WHEN 'INDEX' THEN 'ALTER INDEX '||owner||'.'||segment_name||' UNUSABLE; ALTER INDEX '||owner||'.'||segment_name||' REBUILD;' 
                  WHEN 'TABLE' THEN 'TRUNCATE TABLE '||owner||'.'||segment_name||';'
end as SQLstatement
from dba_extents
where file_id = 42 -- change
and 1029253 -- change
between block_id and (block_id + blocks -1);

If it is an index, we can (make unusable and rebuild) or (drop and recreate).