Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

there is NO APPLIED archivelog after dataguard switchover successful

2624243Oct 10 2014

I have encountered a strange problem after I swicthover primary and standby database(11.2.0.1). First of all, the database (former standby db) works fine, and the new standby db (former primary db) receive archivelog from new primary db well. They two have almost the same sequence number. But when I execute the following SQL to check whether the new primary db transfer archivelog or not, I found lots of history archivelog not transfered:

SELECT local.name, local.FIRST_TIME, local.thread#, local.sequence#
  FROM (SELECT NAME, FIRST_TIME, thread#, sequence#
          FROM v$archived_log
         WHERE dest_id = 1) LOCAL
 WHERE local.sequence# NOT IN
       (SELECT sequence#
          FROM v$archived_log
         WHERE dest_id = 2
           AND thread# = local.thread#)

NAME FIRST_TIM THREAD# SEQUENCE#

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

/db/oracle/archive/1_14175_813434841.dbf 07-OCT-14 1 14175

/db/oracle/archive/1_14176_813434841.dbf 07-OCT-14 1 14176

... ...

/db/oracle/archive/1_14311_813434841.dbf 09-OCT-14 1 14311

Sequence 14312 is the exact point I swithover. According to my confirmation, archivelog 14312,14313,14314... are transfered and applied in my new standby db normally with no exception. I just wonder why the archivelogs before switching show in my query above. As I know, those archivelogs were transfered from former primary db.

So I go to the new standby db side, and execute following SQL to check NO-APPLIED archivelog:

SELECT NAME,DEST_ID,SEQUENCE#,FIRST_TIME,APPLIED FROM v$archived_log WHERE sequence# LIKE '1431%'

NAME DEST_ID SEQUENCE# FIRST_TIME APPLIED

/db/oracle/archive/1_14311_813434841.dbf 1 14311 2014/10/9 19:08:04 YES

/db/oracle/archive/1_14312_813434841.dbf 1 14312 2014/10/9 19:08:45 YES

CRMPROD_ST 2 14312 2014/10/9 19:08:45 NO

/db/oracle/archive/1_14313_813434841.dbf 1 14313 2014/10/9 19:09:06 YES

CRMPROD_ST 2 14313 2014/10/9 19:09:06 NO

/db/oracle/archive/1_14314_813434841.dbf 1 14314 2014/10/9 19:11:22 YES

/db/oracle/archive/1_14315_813434841.dbf 1 14315 2014/10/9 19:11:51 YES

There shows that archivelog 14312,14313 need to be applied manually in new standby db, but you konw 14314,14315... shipped and applied correctly .That's unbelivable!

How to solve the NO APPLIED archivelog problem ?

additional info

standby db:

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

PROCESS STATUS

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

ARCH CLOSING

ARCH CONNECTED

ARCH CLOSING

MRP0 APPLYING_LOG

RFS IDLE

primary db:

SQL> select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name,gap_status,recovery_mode,error from v$archive_dest_status WHERE status='VALID';

DEST_NAME ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# DB_UNIQUE_NAME GAP_STATUS RECOVERY_MODE ERROR

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

LOG_ARCHIVE_DEST_1 1 14359 0 0 CRMPROD_ST IDLE

LOG_ARCHIVE_DEST_2 1 14359 1 14358 CRMPROD NO GAP MANAGED REAL TIME APPLY

both new primary db and standby db:

SQL> SELECT * FROM v$archive_gap;

no rows selected

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 7 2014

Added on Oct 10 2014

#data-guard, #dataguard, #performance-availability

0 comments

1,025 views