Recover Database using Incremental Backups after ResetLogs done

[Sunny_15]

Hi,

I am using Oracle 8.1.7 and using RMAN to perform backup and recovery. I have taken a full backup with the database open. After some changes were made to the database I have taken the Increnetal backup level0 and level1 cumulative. all the backups were taken on the same day because this was a testing.

Now while recovering the database I have to open the database with RESETLOGS option, which ultimately does not allow me to apply the incremental backups. Basically the user wants to apply backups in steps and go back and check whether the data is recovered completely.

Please suggest how do I proceed.

I am attaching my backup and recovery scripts.

[Steven E. Protter]

Is this going to be a 24/7 operation?

If not, I'd change the procedure and make your you catch a nighly cold backup.

Then you can take a few hot ones while transactions are being applied to the database.

The user can run before and after reports or sql queries to verify the restore really works.

What is the sql message leading you to need to open the database in RESETLOGS?

SEP

[Sunny_15]

Steven.Thanks for your quick response.

Yes. This is going to be a 24/7 operation and the database needs to be always up.

Actually the user just wants to verify that everytime the backups are restored, the data that he had chenged is reflected or not. To have this done, I have to restore the backups in steps. But since they are hot backups I have to RESETLOGS to open the database. Is there any way that the user can verify the changes in the database without I Resetting the Logs.

I tried doing the recovery operation earlier and it gave me the error something like the SYSTEM.dbf needs recovery and that I need to pen the database using RESTLOGS.

Please suggest !!!

[Volker Borowski]

Bad news: this is not possible.
Good news: this is not needed.

The aproach seems to me related to the assumption to see diffrent deltas which might be possible when restoreing full and incremental backups to a filesystem. You might get trouble when you do not apply one of the incrementals or apply them in the wrong order (if your backup SW allows this).
As far as I know, RMAN does not allows this, so there is no need for this, because the sequence of the incrementals can not be wrongly re-aranged upon restore.

If you want to recover until 12:34, there may be several ways to get there, but none of these allows to do this i.e. with leaving out some changes from 10:30 to 10:50.

The other problem is, if you do not know WHEN and WHAT and WHO and ....
In this case you might need to consider a shadow database (which might be a good idea in any case when running 24x7), which is running a good way behind and may be opened READONLY to check, but .... once you are "behind" the point you wanted to reach, you need to start over with your restore, since Oracle is not able to rollback commited transactions during recovery.

Hope this helps
Volker

[Sunny_15]

Volker.. Thanks for your response.

Since I am taking a cumulative backup everytime which includes the current controlfile, cant I replace the controlfifle from backup and restore the database at point-in-time.

For eg.

I have a FULL_SUNDAY_BKP, MONDAY_LEVEL_0_BKP, TUESDAY_LEVEL_1_CUMULATIVE_BKP, WED_LEVEL_1_CUM_BKP.

Please note that I am backing up the control files and archive logs everytime any change is made.

So to recover till tuesday can I apply the FULL_SUNDAY_BKP using the then controlfile and then again apply the TUESDAY_LEVEL_1_CUMULATIVE_BKP using its control file. I dont know whether this is possible with RMAN.

Thanks & Regards.

[Volker Borowski]

Sunny,

as far as I know this will not work.

Once you OPEN or OPEN RESETLOGS the database, additional recovery is not possible. The first issue upon OPEN is to set Rollback segments online. This eats up a SCN, which needs to be applied to your then current controlfile, thus avoiding the SCN beloning to your further recovery to be recovered.

I am not sure, if you can open a primary DB in READ ONLY mode (Which would handle the rollback segment stuff mentioned above).
Since yet I had only to do with this on standby databases with a MANAGED RECOVERY. Checkout google on this. May be it is possible from version 9.x, I did not check myself yet.

Hope this helps
Volker

[Sunny_15]

Hi Volker

If it is possible to restore the backups without resetting the logs by bringing the database in read only mode then Can you please give me the steps to do so. I have been restoring the database in with mount option.

Thanks,

Sunny

[Stan_17 · Accepted solution]

Sunny,

Incomplete recoveries definitely require resetlogs that means your telling oracle to recover the database up to a point/scn and ignore the rest.

when you open the database with resetlogs, it resets the redolog information in the controlfile, clears the contents of online redologs if present otherwise it creates one and finally resets the log sequence number to 1. so any archivelogs/cummulative backups that were taken before resetlogs become *unusable*.

In your case, if you want to test changes in stages, then i would restore the

--base backup first
--open with resetlogs
-- test

then again restore

-- base backup
-- apply level0
-- open with resetlogs
-- test

likewise test different cummulative levels.

hth,
Stan

[Sunny_15]

Hi Everybody,

Thankyou for your replies. I was able to succefully restore the incremental backups one by one and also verify them in the database.

This is how I did....I explicitly copied the Controlfile and backed up the archive logs every time I did the incremental backup. During restore I replaced the current control file with the backup control file and did the point in time receovery using the UNTIL TIME clause. After restore I opened the database by resetting the logs and verified the backups.

Thanks,

Sunny