Resolving Gaps in Data Guard Apply Using Incremental RMAN Backup (Using SCN#)

Recently, we had a glitch on a Data Guard (physical standby database) on infrastructure. This is not a critical database; so the monitoring was relatively lax. And that being done by an outsourcer does not help it either. In any case, the laxness resulted in a failure remaining undetected for quite some time and it was eventually discovered only when the customer complained. This standby database is usually opened for read only access from time to time. This time, however, the customer saw that the data was significantly out of sync with primary and raised a red flag. Unfortunately, at this time it had become a rather political issue.

Since the DBA in charge couldn’t resolve the problem, I was called in. In this post, I will describe the issue and how it was resolved. In summary, there are two parts of the problem:

(1) What happened

(2) How to fix it

What Happened

Let’s look at the first question – what caused the standby to lag behind. First, I looked for the current SCN numbers of the primary and standby databases. 

On the primary:

SQL> select current_scn from v$database;

 

On the standby:

SQL> select current_scn from v$database;

Clearly there is a difference. But this by itself does not indicate a problem; since the standby is expected to lag behind the primary (this is an asynchronous non-real time apply setup). The real question is how much it is lagging in the terms of wall clock. To know that I used the scn_to_timestamp function to translate the SCN to a timestamp:

select scn_to_timestamp(15785062) from dual;

 

I ran the same query to know the timestamp associated with the SCN of the standby database as well (Note: I ran it on the primary database, though; since it will fail in the standby in a mounted mode):

 

This shows that the standby is two and half days lagging! The data at this point is not just stale; it must be rotten.

The next question is why it would be lagging so far back in the past. This is a 10.2 database where FAL server should automatically resolved any gaps in archived logs. Something must have happened that caused the FAL (fetch archived log) process to fail. To get that answer, first, I checked the alert log of the standby instance.

SOLUTION:

Now that I know the cause, the focus was now on the resolution. If the archived log sequence# 700 was available on the primary, I could have easily copied it over to the standby, registered the log file and let the managed recovery process pick it up. But unfortunately, the file was gone and I couldn’t just recreate the file. Until that logfile was applied, the recovery will not move forward. So, what are my options?

One option is of course to recreate the standby - possible one but not technically feasible considering the time required. The other option is to apply the incremental backup of primary from that SCN number. That’s the key – the backup must be from a specific SCN number. I have described the process since it is not very obvious. The following shows the step by step approach for resolving this problem. I have shown where the actions must be performed – [Standby] or [Primary].

1. [Standby] Stop the managed standby apply process:

SQL> alter database recover managed standby database cancel;

Database altered.

2. [Standby] Shutdown the standby database

3. [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck:

RMAN>

run { 

allocate channel c1 type disk format 'C:\shared\%U.rmb'; 

BACKUP INCREMENTAL FROM SCN 15785062 DATABASE FORMAT 'C:\shared\ForStandby_%U';

}

 

4. [Primary] On the primary, create a new standby controlfile:

SQL> alter database create standby controlfile as 'C:\shared\controlfile.ctl';

5. [Primary] Copy these files to standby host:

C:\Users\ashwanik\Desktop\MY_SCRIPT>copy C:\shared\*.* F:\tempdata\

C:\shared\CONTROLFILE.CTL

C:\shared\FORSTANDBY_0HRD3CG1_1_1

C:\shared\FORSTANDBY_0IRD3CI2_1_1

        3 file(s) copied.

6. [Standby] Bring up the instance in nomount mode:

SQL> startup nomount

7. [Standby] Check the location of the controlfile:

SQL> show parameter control_files

NAME             TYPE                        VALUE

--------------- ---------   --------------------------------------------

control_files   string      C:\APP\ASHWANIK\ORADATA\DB11GDR\CONTROL01.CTL

8. [Standby]  Replace the controlfile with the one you just created in primary.

9. C:\Users\ashwanik>copy F:\tempdata\CONTROLFILE.CTL C:\app\ashwanik\oradata\db11gdr\control01.ctl

        1 file(s) copied.

11[Standby]  RMAN does not know about these files yet; so you must let it know – by a process called cataloging. Catalog these files:

$ rman target /

catalog start with 'F:\tempdata';

 12. Recover these files:

RMAN> recover database;

RMAN> recover database;

Starting recover at 12-AUG-16
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 960 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000960_0904492316.0001
archived log for thread 1 with sequence 961 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000961_0904492316.0001
archived log for thread 1 with sequence 962 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000962_0904492316.0001
archived log for thread 1 with sequence 963 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000963_0904492316.0001
archived log for thread 1 with sequence 964 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000964_0904492316.0001
archived log for thread 1 with sequence 965 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000965_0904492316.0001
archived log for thread 1 with sequence 966 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000966_0904492316.0001
archived log for thread 1 with sequence 967 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000967_0904492316.0001
archived log for thread 1 with sequence 968 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000968_0904492316.0001
archived log for thread 1 with sequence 969 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000969_0904492316.0001
archived log for thread 1 with sequence 970 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000970_0904492316.0001
archived log for thread 1 with sequence 971 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000971_0904492316.0001
archived log for thread 1 with sequence 972 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000972_0904492316.0001
archived log for thread 1 with sequence 973 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000973_0904492316.0001
archived log for thread 1 with sequence 974 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000974_0904492316.0001
archived log for thread 1 with sequence 975 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000975_0904492316.0001
archived log for thread 1 with sequence 976 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000976_0904492316.0001
archived log for thread 1 with sequence 977 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000977_0904492316.0001
archived log for thread 1 with sequence 978 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000978_0904492316.0001
archived log for thread 1 with sequence 979 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000979_0904492316.0001
archived log for thread 1 with sequence 980 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000980_0904492316.0001
archived log for thread 1 with sequence 981 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000981_0904492316.0001
archived log for thread 1 with sequence 982 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000982_0904492316.0001
archived log for thread 1 with sequence 983 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000983_0904492316.0001
archived log for thread 1 with sequence 984 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000984_0904492316.0001
archived log for thread 1 with sequence 985 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000985_0904492316.0001
archived log for thread 1 with sequence 986 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000986_0904492316.0001
archived log for thread 1 with sequence 987 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000987_0904492316.0001
archived log for thread 1 with sequence 988 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000988_0904492316.0001
archived log for thread 1 with sequence 989 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000989_0904492316.0001
archived log for thread 1 with sequence 990 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000990_0904492316.0001
archived log for thread 1 with sequence 991 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000991_0904492316.0001
archived log for thread 1 with sequence 992 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000992_0904492316.0001
archived log for thread 1 with sequence 993 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000993_0904492316.0001
archived log for thread 1 with sequence 994 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000994_0904492316.0001
archived log for thread 1 with sequence 995 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000995_0904492316.0001
archived log for thread 1 with sequence 996 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000996_0904492316.0001
archived log for thread 1 with sequence 997 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000997_0904492316.0001
archived log for thread 1 with sequence 998 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000998_0904492316.0001
archived log for thread 1 with sequence 999 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000999_0904492316.0001
archived log for thread 1 with sequence 1000 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001000_0904492316.0001
archived log for thread 1 with sequence 1001 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001001_0904492316.0001
archived log for thread 1 with sequence 1002 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001002_0904492316.0001
archived log for thread 1 with sequence 1003 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001003_0904492316.0001
archived log for thread 1 with sequence 1004 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001004_0904492316.0001
archived log for thread 1 with sequence 1005 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001005_0904492316.0001
archived log for thread 1 with sequence 1006 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001006_0904492316.0001
archived log for thread 1 with sequence 1007 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001007_0904492316.0001
archived log for thread 1 with sequence 1008 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001008_0904492316.0001
archived log for thread 1 with sequence 1009 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001009_0904492316.0001
archived log for thread 1 with sequence 1010 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001010_0904492316.0001
archived log for thread 1 with sequence 1011 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001011_0904492316.0001
archived log for thread 1 with sequence 1012 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001012_0904492316.0001
archived log for thread 1 with sequence 1013 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001013_0904492316.0001
archived log for thread 1 with sequence 1014 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001014_0904492316.0001
archived log for thread 1 with sequence 1015 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001015_0904492316.0001
archived log for thread 1 with sequence 1016 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001016_0904492316.0001
archived log for thread 1 with sequence 1017 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001017_0904492316.0001
archived log for thread 1 with sequence 1018 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001018_0904492316.0001
archived log for thread 1 with sequence 1019 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001019_0904492316.0001
archived log for thread 1 with sequence 1020 is already on disk as file C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001020_0904492316.0001
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000960_0904492316.0001 thread=1 sequence=960
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000961_0904492316.0001 thread=1 sequence=961
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000962_0904492316.0001 thread=1 sequence=962
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000963_0904492316.0001 thread=1 sequence=963
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000964_0904492316.0001 thread=1 sequence=964
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000965_0904492316.0001 thread=1 sequence=965
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000966_0904492316.0001 thread=1 sequence=966
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000967_0904492316.0001 thread=1 sequence=967
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000968_0904492316.0001 thread=1 sequence=968
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000969_0904492316.0001 thread=1 sequence=969
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000970_0904492316.0001 thread=1 sequence=970
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000971_0904492316.0001 thread=1 sequence=971
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000972_0904492316.0001 thread=1 sequence=972
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000973_0904492316.0001 thread=1 sequence=973
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000974_0904492316.0001 thread=1 sequence=974
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000975_0904492316.0001 thread=1 sequence=975
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000976_0904492316.0001 thread=1 sequence=976
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000977_0904492316.0001 thread=1 sequence=977
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000978_0904492316.0001 thread=1 sequence=978
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000979_0904492316.0001 thread=1 sequence=979
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000980_0904492316.0001 thread=1 sequence=980
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000981_0904492316.0001 thread=1 sequence=981
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000982_0904492316.0001 thread=1 sequence=982
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000983_0904492316.0001 thread=1 sequence=983
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000984_0904492316.0001 thread=1 sequence=984
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000985_0904492316.0001 thread=1 sequence=985
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000986_0904492316.0001 thread=1 sequence=986
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000987_0904492316.0001 thread=1 sequence=987
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000988_0904492316.0001 thread=1 sequence=988
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000989_0904492316.0001 thread=1 sequence=989
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000990_0904492316.0001 thread=1 sequence=990
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000991_0904492316.0001 thread=1 sequence=991
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000992_0904492316.0001 thread=1 sequence=992
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000993_0904492316.0001 thread=1 sequence=993
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000994_0904492316.0001 thread=1 sequence=994
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000995_0904492316.0001 thread=1 sequence=995
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000996_0904492316.0001 thread=1 sequence=996
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000997_0904492316.0001 thread=1 sequence=997
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000998_0904492316.0001 thread=1 sequence=998
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000000999_0904492316.0001 thread=1 sequence=999
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001000_0904492316.0001 thread=1 sequence=1000
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001001_0904492316.0001 thread=1 sequence=1001
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001002_0904492316.0001 thread=1 sequence=1002
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001003_0904492316.0001 thread=1 sequence=1003
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001004_0904492316.0001 thread=1 sequence=1004
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001005_0904492316.0001 thread=1 sequence=1005
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001006_0904492316.0001 thread=1 sequence=1006
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001007_0904492316.0001 thread=1 sequence=1007
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001008_0904492316.0001 thread=1 sequence=1008
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001009_0904492316.0001 thread=1 sequence=1009
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001010_0904492316.0001 thread=1 sequence=1010
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001011_0904492316.0001 thread=1 sequence=1011
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001012_0904492316.0001 thread=1 sequence=1012
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001013_0904492316.0001 thread=1 sequence=1013
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001014_0904492316.0001 thread=1 sequence=1014
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001015_0904492316.0001 thread=1 sequence=1015
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001016_0904492316.0001 thread=1 sequence=1016
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001017_0904492316.0001 thread=1 sequence=1017
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001018_0904492316.0001 thread=1 sequence=1018
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001019_0904492316.0001 thread=1 sequence=1019
archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11GDR\ARC0000001020_0904492316.0001 thread=1 sequence=1020
unable to find archived log
archived log thread=1 sequence=1021
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/12/2016 20:36:16
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1021 and starting SCN of 15791963

13. After some time, the recovery fails with the message:

archived log thread=1 sequence=1021
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/12/2016 20:36:16

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1021 and starting SCN of 15791963

This happens because we have come to the last of the archived logs. The expected archived log with sequence# 1021 has not been generated yet.

14.At this point exit RMAN and start managed recovery process:

Or

Use recover database noredo

SQL> alter database recover managed standby database disconnect from session;

Database altered.

15.Check the SCN’s in primary and standby:

[Standby] SQL> select current_scn from v$database;

CURRENT_SCN

-----------

   15791962

 

[Primary] SQL> select current_scn from v$database;

CURRENT_SCN

-----------

   15791966

 

Now they are very close to each other. The standby has now caught up.

[Standby]: Now there is no lagging. All archives are in sync

 

ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist ORA-02002: error while writing to audit trail ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist

SQL> CONN ashwani/ashwani

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-00942: table or view does not exist

ORA-02002: error while writing to audit trail

ORA-00604: error occurred at recursive SQL level 1

ORA-00942: table or view does not exist

This issue occur after final incremental recovery. After digging more i came to know there is a difference in the settings of audit trail.

At source DB:

SQL>  show parameter audit_trail

NAME                                 TYPE        VALUE

------------------------------------ ----------- ----------------

audit_trail                          string      NONE

At target DB:

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------

audit_trail                          string      DB

Now I have changed the setting same as in source database.

Logging to target database. start the database using spfile.

SQL> CREATE SPFILE FROM PFILE;

File created.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> STARTUP

ORACLE instance started.

Total System Global Area 5060792320 bytes

Fixed Size                  2289960 bytes

Variable Size            1006636760 bytes

Database Buffers         4043309056 bytes

Redo Buffers                8556544 bytes

Database mounted.

Database opened.

SQL> alter system set audit_trail='NONE' SCOPE=SPFILE;

System altered.

SQL> SHUT IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP;

ORACLE instance started.

Total System Global Area 5060792320 bytes

Fixed Size                  2289960 bytes

Variable Size            1006636760 bytes

Database Buffers         4043309056 bytes

Redo Buffers                8556544 bytes

Database mounted.

Database opened.

SQL>

SQL> CONN ashwani/ashwani

Connected.

Start up error after restoration in oracle 11.2.0.4

SQL> startup
ORACLE instance started.

Total System Global Area  914358272 bytes
Fixed Size                  2100520 bytes
Variable Size             230687448 bytes
Database Buffers          675282944 bytes
Redo Buffers                6287360 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-01092: ORACLE instance terminated. Disconnection forced

 ORA-00704: bootstrap process failure 

ORA-39700: database must be opened with UPGRADE option

 Solution:

After SQL> startup upgrade, run the catupgrd.sql(if you were upgrading the database). The file can be found under $ORACLE_HOME/rdbms/admin directory.

SQL> @?\rdbms\admin\catupgrd.sql

After the completion it's execution 

SQL>shut immediate then 

SQL>startup then run 

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid database objects.