Tuesday, September 10, 2013

RMAN-08120 on Primary with Active Standby and GoldenGate

I came across this a few months ago at a client site. It was peculiar problem whose fix wasn’t quite straightforward (well, not initially anyway).
 
Ever seen this?
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
This one I’ve got to work twice now so, it probably works for others. Once when I encountered it with Data Guard and GoldenGate (Integrated Capture Enabled), and second when I suggested it as a solution for a Data Guard with Snap Clones.
RMAN> BACKUP ARCHIVELOG UNTIL TIME '' NOT BACKED UP 1 TIMES TO DEVICE TYPE SBT;
RMAN> DELETE ARCHIVELOG SEQUENCE 12206 BACKED UP 1 TIMES TO DEVICE TYPE DISK;
Once upon a time, there was a Primary Database with a Standby Database and GoldenGate Target Database configured where, all logs/trail files were successfully applied without a lag. To resolve an unrelated problem I’d been futzing around with GoldenGate, had enabled integrated capture, resolved my original issue and forgotten about enabling it! A few weeks later, my colleague mentioned that when deleting archived logs (post backup) he received the above RMAN error message. What was stranger still is, that the issue was identified only after a certain someone had enabled Integrated Capture.
 
Tube light went on, umm wait, I might have done thing to do cause it! 
 
For reference, the piece in the backup script that failed is below:
SQL> select capture_name, queue_owner, capture_user, start_scn, status from dba_capture;

CAPTURE_NAME     QUEUE_OWNER      CAPTURE_USER     START_SCN STATUS
------------     ------------- ----------       --------- ------
OGG$CAP_E_PRD    GGATE         GGATE            525864662 ENABLED
Version information
 
Database: 11.2.0.3 64bit
Data Guard: Active Data Guard
GoldenGate: 11.2.1.0.7 64bit
 
So, after struggling with MOS unsuccessfully for a day, I gave up and came back the next day to surprisingly find a good lead.
 
Why is RMAN Not Purging Archive Logs After Backup Since OGG was Configured?, RMAN-08137 (Doc ID 1351352.1)
 
With that information in hand, I went through the following steps:
 
Let’s find out what is registered in the Source Database.
 
According to the MOS Note, we can simply drop it.
SQL> exec DBMS_CAPTURE_ADM.DROP_CAPTURE ('OGG$CAP_E_PRD');
BEGIN DBMS_CAPTURE_ADM.DROP_CAPTURE ('OGG$CAP_E_PRD'); END;

*
ERROR at line 1:
ORA-01338: Other process is attached to LogMiner session
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 726
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 697
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 566
ORA-06512: at line 1
Or, maybe not. Let’s stop it first.
SQL> exec dbms_capture_adm.STOP_capture(CAPTURE_NAME=>'OGG$CAP_E_PRD')

PL/SQL procedure successfully completed.
Check the status again.
SQL> select capture_name, queue_owner, capture_user, start_scn, status from dba_capture;

CAPTURE_NAME     QUEUE_OWNER      CAPTURE_USER     START_SCN STATUS
------------     ------------- ----------       --------- ------
OGG$CAP_E_PRD    GGATE         GGATE            525864662 DISABLED
Attempt another drop.
SQL> exec DBMS_CAPTURE_ADM.DROP_CAPTURE ('OGG$CAP_E_PRD');

PL/SQL procedure successfully completed.
Check status again.
SQL> select capture_name, queue_owner, capture_user, start_scn, status from dba_capture;

no rows selected
Perfect!! After this, I was successfully able to issue the Archived Log backup with delete without the error message.
 
In the second case, the result of the query was: two disabled capture objects. When dropped, the backup script (with delete) worked as intended.
 
Cheers!

at
Labels: , , , , , ,

2 comments :

  1. AnonymousSeptember 19, 2013 at 11:38 AM

    very useful, thank you very much for detailed explaination

    ReplyDelete
    Replies
    1. UnknownSeptember 27, 2013 at 7:45 AM

      Dear Anonymous,

      Thanks for the feedback!

      Cheers!

      Delete

Subscribe to: Post Comments ( Atom )