4 Replies Latest reply: Dec 30, 2013 1:38 AM by CKPT RSS

Max SCN Number in redolog file

user9085814 Newbie
Currently Being Moderated

Hi ,

 

I have configured a data guard environment using below configuration

 

STANDBY TYPE : - PHYSICAL STANDBY

LOG TRANSPORT SERVICE : - ARCH [ ARCHIVE PROCESS ]

STANDBY LOG :- NO STANDBY LOG IN PRIMARY AND STANDBY

SYNC STATUS OF PRIMARY AND STANDBY : - FULLY SYNC

OPERATION  : - FAIL OVER USING 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;'

               ACTIVATING THE STANDBY USING ' ALTER DATABASE ACTIVATE STANDBY DATABASE;'

 

PRIMARY AND STANDBY ARE IN FULLY SYNC

 

ON PRIMARY

LAST ARCHIVED SEQUENCE NUMBER IS 12 AND FIRST AND LAST SCN ASSOCIATED WITH SEQUENCE 12 IS AS BELOW

SELECT SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG WHERE SEQUENCE#=12;

SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#

        12          669447                 670246

 

ON STANDBY

THE ARCHIVE LOG WITH SEQUENCE NUMBER 12 HAS ARCHIVED AND APPLIED ON STANDBY DATABASE SUCCESSFULLY.

 

NOW I AM DOING A FAIL OVER BY USING THE BELOW COMMANDS

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE ACTIVATE STANDBY DATABASE;

 

ALERT  LOG ON STANDBY DATABASE

Media Recovery Log /data/PRD_DR/arch/arch_1_11_834360625.arch

Media Recovery Log /data/PRD_DR/arch/arch_1_12_834360625.arch

Media Recovery Waiting for thread 1 sequence 13

Error 12154 received logging on to the standby

FAL[client, MRP0]: Error 12154 connecting to PRD for fetching gap sequence

Errors in file /apps/oracle/diag/rdbms/stand/PRD/trace/PRD_mrp0_7865.trc:

ORA-12154: TNS:could not resolve the connect identifier specified

Thu Dec 26 18:00:36 2013

alter database recover managed standby database cancel

Thu Dec 26 18:00:36 2013

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /apps/oracle/diag/rdbms/stand/PRD/trace/PRD_mrp0_7865.trc:

ORA-16037: user requested cancel of managed recovery operation

Shutting down recovery slaves due to error 16037

Recovery interrupted!

Errors in file /apps/oracle/diag/rdbms/stand/PRD/trace/PRD_mrp0_7865.trc:

ORA-16037: user requested cancel of managed recovery operation

MRP0: Background Media Recovery process shutdown (PRD)

Waiting for MRP0 pid 7865 to terminate

Managed Standby Recovery Canceled (PRD)

Completed: alter database recover managed standby database cancel

Thu Dec 26 18:00:59 2013

alter database activate standby database

ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (PRD)

tkcrrxms: Killing 2 processes (all RFS)

RESETLOGS after incomplete recovery UNTIL CHANGE 670246

Resetting resetlogs activation ID 1898010833 (0x712158d1)

Online log /data/PRD_DR/REDOLOG11.LOG: Thread 1 Group 1 was previously cleared

Online log /data/PRD_DR/REDOLOG21.LOG: Thread 1 Group 2 was previously cleared

Online log /data/PRD_DR/REDOLOG33.LOG: Thread 1 Group 3 was previously cleared

Standby became primary SCN: 670244

Thu Dec 26 18:01:01 2013

Setting recovery target incarnation to 3

Converting standby mount to primary mount.

ACTIVATE STANDBY: Complete - Database mounted as primary (PRD)

Completed: alter database activate standby database

 

 

IN STANDBY ALERT LOG I CAN SEE BELOW THINGS

RESETLOGS after incomplete recovery UNTIL CHANGE 670246

Standby became primary SCN: 670244

 

 

MY QUESTION IS ON 'SCN NUMBER OF 'Standby became primary SCN: 670244'.

I HAVE CHECKED THE SCN NUMBERS OF THE ARCHIVE LOG OF SEQUENCE 12 [ USING LOGMINER ] THE MAX SCN ASSOCIATED WITH THE ARCHIVE LOG IS 670242

 

SELECT MAX(SCN) FROM V$LOGMNR_CONTENTS; [ FOR LOGMINER I HAVE USED '

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING + DBMS_LOGMNR.DICT_FROM_REDO_LOGS); ]

 

  MAX(SCN)

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

  670242

 

- WHY IN LOGMINER MAX(SCN) IS NOT SHOWING AS 670246 ?

- HOW I CAN SEE THE SCN 670244 FOR ARCHIVE LOG FOR SEQUENCE NUMBER 12 ?

 

Thanks,

  • 1. Re: Max SCN Number in redolog file
    mseberg Guru
    Currently Being Moderated

    Hello;

     

    You say:

     

    "PRIMARY AND STANDBY ARE IN FULLY SYNC"

     

    And you show the Primary sequence of 12 and Media Recovery Waiting for thread 1 sequence 13.

     

    But you also show on the standby:

     

    "FAL[client, MRP0]: Error 12154 connecting to PRD for fetching gap sequence"

     

     

    It would appear you have not accounted for the differance between what has Transported, and what has applied to the Standby.

     

    You need to resolve a gap I believe.

     

     

    Best Regards

     

    mseberg

  • 2. Re: Max SCN Number in redolog file
    user9085814 Newbie
    Currently Being Moderated

    Hi ,

     

    Thanks for the reply ....

     

    I TOLD FULLY SYNC BECAUSE THE EXCEPT THE CURRENT ONLINE REDO LOG [ SEQUENCE 13 ]OF THE PRIMARY ALL THE PREVIOUS ARCHIVE LOG ARE REGISTERED  AND APPLIED IN THE STANDBY SIDE .

     

    THOUGH IN  THE ABOVE DATA GARD CONFIGURATION THE LOG TRANSPORT SERVICE IS ARCH , SO UNLESS AND UNTIL THE CURRENT REDO LOG WILL BE ARCHIVED ON THE PRIMARY SIDE ,NO LOG FROM THE CURRENT REDO LOG OF PRIMARY WILL COME TO STANDBY SIDE .

     

    SO THIS SCENARIO IF I AM FAILING OVER USING 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL' ON STAND BY SIDE THEN MRP PROCESS WILL BE TERMINATED IMMEDIATELY .

     

    PLEASE CORRECT ME IF I AM WRONG .....

     

    Thanks,

  • 3. Re: Max SCN Number in redolog file
    mseberg Guru
    Currently Being Moderated

    Can you run this query on the Primary side and post the 1 line result?

     

    http://www.visi.com/~mseberg/data_guard/monitor_data_guard_transport.html

     

    Best Regards

     

    mseberg

  • 4. Re: Max SCN Number in redolog file
    CKPT Guru
    Currently Being Moderated

    IN STANDBY ALERT LOG I CAN SEE BELOW THINGS

    RESETLOGS after incomplete recovery UNTIL CHANGE 670246

    Standby became primary SCN: 670244

     

     

    MY QUESTION IS ON 'SCN NUMBER OF 'Standby became primary SCN: 670244'.

    I HAVE CHECKED THE SCN NUMBERS OF THE ARCHIVE LOG OF SEQUENCE 12 [ USING LOGMINER ] THE MAX SCN ASSOCIATED WITH THE ARCHIVE LOG IS 670242

     

    in fact, it is really intelligent question.

    First you have to know the sequence 12, next_change# is not belongs to 12 but it belongs to the 13th sequence first_change...

    So in the real, the seqeunce 12 change number is only up to 670245 and the change 670246 is the starting change of sequence numebr 13.

     

    It is not using any real time apply, Now as per the my above conclusion the sequence number last change is only 670245 , As per the recovery concepts.. If you want to perform recovery change up to 100, you need to mention as "until 100 + 1", i.e. 101.. So if you mention 101 then it performs recovery until 100.

     

    1) the 12 sequence max change is 670245

    2) when it performs recovery until that sequence, then then usually it performs recovery until 6740244 as per the recovery rules.

     

    From http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12033.htm

    UNTIL CHANGE integer

    Processes managed recovery up to but not including the specified system change number (SCN).

     

    Still at this point am not giving conclusion 100%, am testing same as you using log miner and will let you know sure..

     

    - WHY IN LOGMINER MAX(SCN) IS NOT SHOWING AS 670246 ?

    When you analyze archive redo log file, Have you used starttime and end time? Note that if you give end time bit less then there is chance to truncate to gather information for log miner and important thing is Oracle writes checksum information and change information in terms of metadata into headers.Also note that oracle uses some of records for SYSTEM CHANGE, so some of them may not visible.

     

    HTH.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points