Re: DBPITR and ORA-16005 Error: database requires recovery.

From: <gbahut_at_ravenpack.com>
Date: Tue, 6 May 2008 05:07:48 -0700 (PDT)
Message-ID: <42579c64-ff81-40cb-95d3-bed1c3875d19@26g2000hsk.googlegroups.com>

This time I got it.

I will write down how I did:

1. Backup the database including the control file.
2. Get the CURRENT_SCN from V$Database; [scn1]
3. Drop table T1 and added some rows to table T2.
4. Perform DBPITR up to SCN1:
   >RMAN / target
   RMAN> shutdown immediate; RMAN> startup nomount; RMAN> SET DBID [My database Identifier]; RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP; RMAN> ALTER DATABASE MOUNT; RMAN> RUN { SET UNTIL SCN [scn1]; RESTORE DATABASE; RECOVER DATABASE; } RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';

The database successfully opens in read only mode and I verify that the operations after SCN1 are reversed, these are, T1 dropped table and the inserts on T2.

I can copy T1 to another DB using a DB link and perform complete recovery of the database:

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> RECOVER DATABASE;
RMAN> SQL 'ALTER DATABASE OPEN RESETLOGS';

With the database open I can copy T1 backup back to my target database not losing the INSERTS I made on T2.

So, It looks like the control file must be restored in the first place, as many of you suggested, so that RMAN can perform a consistent incomplete recovery and let the DB be opened in READ ONLY mode.

Thanks again for your comments.

Gonzalo. Received on Tue May 06 2008 - 07:07:48 CDT