Skip to Main Content

Lost Archive Logs and PITR to a point in the lost archive logs

user13040671Mar 11 2014 — edited Mar 18 2014

Hi,

Looks impossible, but thought I'll throw it out here anyway to get expert opinions.

Context:

======

Primary DB (4 node RAC in Exadata)

Standby DB (4 node RAC in Exadata)

Archivelog deletion policy (in primary):

===========================

Primary DB: CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;

Problem:

=======

Normally about 70 logs are generated per day in primary. Last Sunday, 4000 logs (50x normal) !

So, the Oracle database automatically deleted the logs in the FRA and we lost about 100 logs generated for a 10hour period (generated from 2AM to 3PM this past Sunday).

Of course, we immediately backed up full LV0 as soon as we came to know of the massive load and loss of archive logs and that ensured that all was well with production.

Btw, the 100 logs were also gone in the standby due to a cleanup job.

Now comes in a sweet little ticket to our DBA queue to duplicate the production data onto a test server as of 6AM on Sunday.

This data is apparently needed for the government compliance, toast !

Our approach:

==========

I gave the incremental-scn-backup-for-standby-catchup-method a try.

(1) Restored the prod onto to test TILL the missing archive log

(2) Then did a (in primary): BACKUP INCREMENTAL FROM SCN 234230561198 DATABASE FORMAT '/log/jxa/_%U' tag 'test';

(3) In Test, CATALOG START WITH '/log/jxa/_%U'

(4) In Test, tried:

run

{

allocate  channel ch1 type disk;

set until time "to_date('09-03-2014 06:00:00','dd-mm-yyyy hh24:mi:ss')";

RECOVER DATABASE NOREDO;

}

NO LUCK, obviously.

Question:

=======

It seems you are TOAST if your archive logs are missing and you want to do an PITR on a test server to a point in the lost archive logs.

It would have helped if there was an option (my enhancement wish list) to do an incremental scn based backup from scn1 TO scn2.


Please advise:
(1) (I know this maybe hopeless) if an incremental backup can be taken between 2 SCNs (starting SCN is first_change# of the 1st archived log and ending SCN is the time i want to do a PITR)
(2) if an incremental backup is being restored in test server, can the restore be done to a point in time (present in that incremental).


Thanks


Comments
Post Details
Added on Mar 11 2014
10 comments
238 views