Re: DataGuard with Physical & Snapshot Standby not deleting archivelogs

From: Maaz Anjum <maazanjum_at_gmail.com>
Date: Tue, 10 Sep 2013 15:34:10 -0400
Message-ID: <CA+zcOQ8OHi8GoiUP3mnAP=ioVwFmexLABUPRNcwg56Vs-zKzbQ_at_mail.gmail.com>

Don,
I've seen this issue before when I configured Data Guard along with GoldenGate, the latter was purposefully registered to use LogMiner within the primary database. We had the exact same error message during "backup and deleting" arcihived logs. The only fix, before the actual fix, was to delete them with the "force" option.

Do you recall making any changes with registering a streams or GoldenGate for integrated capture against the primary database?

In any case, I hope this is helpful to point you in the right direction. Below are the checks I ran to find out and fix the problematic issue.

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

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

SQL> exec dbms_capture_adm.STOP_capture(CAPTURE_NAME=>'OGG$CAP_E_PRD')

PL/SQL procedure successfully completed.

SQL> ;
  1* select capture_name, queue_owner, capture_user, start_scn, status from dba_capture
SQL> /

CAPTURE_NAME                   QUEUE_OWNER
------------------------------ ------------------------------
CAPTURE_USER                    START_SCN STATUS
------------------------------ ---------- --------
OGG$CAP_E_PRD                  GGATE
GGATE                           525864662 DISABLED

SQL> exec DBMS_CAPTURE_ADM.DROP_CAPTURE ('OGG$CAP_E_PRD');

PL/SQL procedure successfully completed.

SQL> select capture_name, queue_owner, capture_user, start_scn, status from dba_capture;

no rows selected

Afterwards, I was able to delete the archived logs without the "force" option.

Cheers,
Maaz

On Tue, Sep 10, 2013 at 2:23 PM, Don Seiler <don_at_seiler.us> wrote:

> As a quick follow-up, v$archived_log on the primary does indeed say all
> those logs have APPLIED='NO'. The full list actually goes back to Feb 9.
> Still need to know why! The dest_id for all of the logs where applied='NO'
> that are older than a day is always 1 (FRA).
>
> On Tue, Sep 10, 2013 at 1:09 PM, Don Seiler <don_at_seiler.us> wrote:
>
> > Oracle 11.2.0.3 on Linux (RH6).
> >
> > I have a Dataguard config with one primary, one physical standby and five
> > snapshot standbys. The physical standby has 0 lag, and all of the five
> > snapshot standbys are converted back to physical and caught up every
> > morning and then back to snapshot.
> >
> > I've confirmed that all 5 snapshot standbys were successfully refreshed
> > and dataguard reports apply lag of no more than 9 hours for any of them.
> >
> > However, when we try to delete archivelogs via RMAN, either through
> > "backup database plus archivelog delete input" or just "delete
> archivelog",
> > we get:
> >
> > "RMAN-08120: WARNING: archived log not deleted, not yet applied by
> standby"
> >
> > I'm talking about archivelogs going back to April and May.
> >
> > The deletion policy in RMAN is:
> >
> > CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
> >
> > and the physical standby is configured as a mandatory archive destination
> > (otherwise we'd get "invalid archivelog deletion policy" warnings as
> well).
> >
> > Has this something to do with the snapshot standbys not being in physical
> > standby mode at the time of the delete attempt? I don't otherwise see
> what
> > the problem could be, certainly not why RMAN thinks it needs to hang onto
> > logs from over 4 months ago when it shouldn't need any logs older than 2
> > days.
> >
> > I'm sure I could use "force" to delete them but I'd really like to know
> > why RMAN is telling me what it is.
> >
> > --
> > Don Seiler
> > http://www.seiler.us
> >
>
>
>
> --
> Don Seiler
> http://www.seiler.us
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
A life yet to be lived...


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 10 2013 - 21:34:10 CEST