This discussion is archived
12 Replies Latest reply: Nov 15, 2012 11:26 PM by Shivananda Rao RSS

Out of sync  Oracle DB on AIX machine

Meenakshy singh Newbie
Currently Being Moderated
Hi Masters,

Again hit with the log out of sync error on primary and secondary but this time teh DG_BROKER_START=TRUE .
My machine in AIX.

Below are some details and query that i fired on DR database. I have tried the command " ALTER DATABASE REGISTER LOGFILE 'LOGFILE NAME' but it says log file already registered.
Please let me know what i need to do to fix this issue.

SQL> *show parameter DG_BROKER_START*

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE


SQL> *select switchover_status from v$database;*

SWITCHOVER_STATUS
------------------
SESSIONS ACTIVE

SQL> *select process,status,sequence# from v$managed_standby;*

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS RECEIVING 585297
RFS RECEIVING 585296

SQL> *select sequence#,applied,first_time,next_time from v$archived_log order by sequence#;*

585258 YES 15-NOV-12 15-NOV-12
585259 YES 15-NOV-12 15-NOV-12
585260 YES 15-NOV-12 15-NOV-12
585261 NO 15-NOV-12 15-NOV-12
585262 NO 15-NOV-12 15-NOV-12
585263 NO 15-NOV-12 15-NOV-12
585264 NO 15-NOV-12 15-NOV-12
|
|
585295 NO 16-NOV-12 16-NOV-12
585296 NO 16-NOV-12 16-NOV-12
585297 NO 16-NOV-12 16-NOV-12

SQL> *archive log list;*
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u03/oradata/ccnbdr/archive
Oldest online log sequence 585294
Next log sequence to archive 0
Current log sequence 585298

SQL> *SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"*
*2 FROM*
*3 (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,*
*4 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL*
*5 WHERE*
*6 ARCH.THREAD# = APPL.THREAD#*
*7 ORDER BY 1;*

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 585297 585260 37
  • 1. Re: Out of sync  Oracle DB on AIX machine
    LaserSoft Journeyer
    Currently Being Moderated
    Hi

    First we need to get the sequence# from v$archived_log of the log which is not applied.

    SQL>select sequence#,archived,applied,completion_time from v$archived_log where applied='NO';

    Then the below query should be fired.,

    SQL>select sequence#,archived,applied,completion_time from v$archived_log where sequence#='sequence_number';

    We will get two rows one with 'NO' and the other with 'YES'. if it is with 'YES' it is applying to standby database.

    Just check this query and post the results

    Thanks
    LaserSoft
  • 2. Re: Out of sync  Oracle DB on AIX machine
    mseberg Guru
    Currently Being Moderated
    Hello;

    Run this query and post results :

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

    Also please look at you last question where I posted more details on this.

    h3. Later

    Based on your post in the "Answered" question I believe you have an issue.

    Can you check both the Primary and Standby for Errors and post those too?


    Best Regards

    mseberg

    Edited by: mseberg on Nov 15, 2012 11:34 AM
  • 3. Re: Out of sync  Oracle DB on AIX machine
    Meenakshy singh Newbie
    Currently Being Moderated
    Hi Mseberg,

    As per your last update i have already registered the log and it says log already registered.
    Below is the output of the script provided by you.Need your help to fix this

    DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
    ---------- -------------- ------------ ----------- -------------- -------
    CCNBPRD HFCDXDBPRD 585306 585260 15-NOV/08:50 46
  • 4. Re: Out of sync  Oracle DB on AIX machine
    Shivananda Rao Guru
    Currently Being Moderated
    Hello,

    From your primary database, run:
    sql>select severity,error_code,message,timestamp from v$dataguard_status where dest_id=2;
    Also, please post the error message that is seen in the alert log files.
  • 5. Re: Out of sync  Oracle DB on AIX machine
    mseberg Guru
    Currently Being Moderated
    OK;

    Can you post the errors from both alert logs?

    Last 100 lines or so? The Primary probably has an error, but check the Standby too

    this is a large gap 585306 585260

    h2. Gap of 46

    mseberg
  • 6. Re: Out of sync  Oracle DB on AIX machine
    Meenakshy singh Newbie
    Currently Being Moderated
    It shows *"No rows selected"*
  • 7. Re: Out of sync  Oracle DB on AIX machine
    mseberg Guru
    Currently Being Moderated
    Hello;

    Let focus on the issue.

    1. Is the Standby receiving redo ?


    SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST;
    2. Is the listener working and up?
    SQL> connect sys/<password>@standbytnsname as sysdba;
    3. Is the FRA on the Standby full?

    Run on Standby. Look for ORA-19815 in standby alert log
    select * from v$flash_recovery_area_usage;
    4. Is MRP up on Standby?
    select process,status from v$managed_standby;
    Finally please check both Primary and Standby alert logs and post all these results.


    Best Regards

    mseberg
  • 8. Re: Out of sync  Oracle DB on AIX machine
    Meenakshy singh Newbie
    Currently Being Moderated
    Hi Mseberg,

    Below are the output. Also the DB_BROKER is true .Also want to know if DG_BROKER is managing the Primary and Standby then is it necesaary to have MRP0 process runnign on Standby.

    SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST;

    DEST_ID STATUS ERROR
    ---------- --------- ----------
    1 VALID
    2 VALID
    3 INACTIVE
    4 INACTIVE
    5 INACTIVE
    6 INACTIVE
    7 INACTIVE
    8 INACTIVE
    9 INACTIVE
    10 INACTIVE

    2)Listener is working and up
    3)select process,status from v$managed_standby;

    PROCESS STATUS SEQUENCE#
    --------- ------------ ----------
    ARCH CONNECTED 0
    ARCH CONNECTED 0
    RFS RECEIVING 585341
    RFS ATTACHED 585342

    Also sending u the alert log in shortwhile..

    Thanks
    Meena
  • 9. Re: Out of sync  Oracle DB on AIX machine
    mseberg Guru
    Currently Being Moderated
    Hello;

    Here's mine : ( run on standby )

    Notice the MRP0      WAIT_FOR_LOG
    SQL> select process,status from v$managed_standby;
    
    PROCESS   STATUS
    --------- ------------
    ARCH      CLOSING
    ARCH      CONNECTED
    ARCH      CLOSING
    ARCH      CLOSING
    ARCH      CLOSING
    ARCH      CLOSING
    ARCH      CLOSING
    ARCH      CLOSING
    MRP0      WAIT_FOR_LOG
    RFS       IDLE
    RFS       IDLE
    
    PROCESS   STATUS
    --------- ------------
    RFS       IDLE
    RFS       IDLE
    RFS       IDLE
    RFS       IDLE
    RFS       IDLE
    RFS       IDLE
    
    17 rows selected.
    I don't see yours this is key. Please confirm.

    Best Regards

    mseberg
  • 10. Re: Out of sync  Oracle DB on AIX machine
    Meenakshy singh Newbie
    Currently Being Moderated
    Hi ,

    This are the DR DB logs ....seems like MRP process got shutdown.

    Thu Nov 15 06:51:26 2012
    Media Recovery Waiting for thread 1 seq# 585259
    Media Recovery Log /u03/oradata/ccnbdr/archive/standbyccnbdr_1_585259.arc
    Thu Nov 15 06:54:41 2012
    Media Recovery Waiting for thread 1 seq# 585260
    Media Recovery Log /u03/oradata/ccnbdr/archive/standbyccnbdr_1_585260.arc
    Thu Nov 15 08:41:43 2012
    Media Recovery Waiting for thread 1 seq# 585261
    Thu Nov 15 08:59:40 2012
    alter database recover managed standby database cancel
    Thu Nov 15 08:59:43 2012
    MRP0: Background Media Recovery user canceled with status 16037
    Recovery interrupted.
    MRP0: Background Media Recovery process shutdown
    Thu Nov 15 08:59:45 2012
    Managed Standby Recovery Cancelled
    Completed: alter database recover managed standby database ca
    Thu Nov 15 08:59:46 2012
    alter database open read only
    Thu Nov 15 08:59:47 2012
    SMON: enabling cache recovery
    Thu Nov 15 08:59:54 2012
    Database Characterset is UTF8
    replication_dependency_tracking turned off (no async multimaster replication found)
    Completed: alter database open read only
    Fri Nov 16 00:28:02 2012
    ALTER DATABASE REGISTER LOGFILE '/u03/oradata/ccnbdr/archive/standbyccnbdr_1_585261.arc'
    Fri Nov 16 00:28:02 2012
    There are 1 logfiles specified.
    ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
    Register archivelog /u03/oradata/ccnbdr/archive/standbyccnbdr_1_585261.arc already exists
    ORA-16089 signalled during: ALTER DATABASE REGISTER LOGFILE '/u03/oradata/ccnb...
    Fri Nov 16 00:28:32 2012
    ALTER DATABASE REGISTER LOGFILE '/u03/oradata/ccnbdr/archive/standbyccnbdr_1_585262.arc'
    Fri Nov 16 00:28:32 2012
    There are 1 logfiles specified.
    ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
    Register archivelog /u03/oradata/ccnbdr/archive/standbyccnbdr_1_585262.arc already exists
    ORA-16089 signalled during: ALTER DATABASE REGISTER LOGFILE '/u03/oradata/ccnb...
    $
  • 11. Re: Out of sync  Oracle DB on AIX machine
    mseberg Guru
    Currently Being Moderated
    Agreed.

    What happens when you start MRP?

    For this ORA-16089: archive log has already been registered

    You can try :

    ALTER DATABASE REGISTER OR REPLACE PHYSICAL LOGFILE 'full_path_log_onStandby';

    Example from mine
    ALTER DATABASE REGISTER OR REPLACE LOGFILE '/u01/app/oracle/oradata/STANDBY/archive/PRIMARY_1_21_716110538.arc';
    Not worth a look
    OERR: ORA-16089 archive log has already been registered [ID 172821.1]


    Best Regards

    mseberg

    Edited by: mseberg on Nov 15, 2012 7:04 PM
  • 12. Re: Out of sync  Oracle DB on AIX machine
    Shivananda Rao Guru
    Currently Being Moderated
    Hello,
    Media Recovery Log /u03/oradata/ccnbdr/archive/standbyccnbdr_1_585260.arc
    Thu Nov 15 08:41:43 2012
    Media Recovery Waiting for thread 1 seq# 585261
    Thu Nov 15 08:59:40 2012
    alter database recover managed standby database cancel
    Thu Nov 15 08:59:43 2012
    MRP0: Background Media Recovery user canceled with status 16037
    Recovery interrupted.
    MRP0: Background Media Recovery process shutdown
    Thu Nov 15 08:59:45 2012
    Managed Standby Recovery Cancelled
    Completed: alter database recover managed standby database ca
    Thu Nov 15 08:59:46 2012
    alter database open read only
    It looks like you have cancelled the MRP on the standby database, opened the standby database in read only mode. I am completely unaware of the database version you are using as you have not mentioned it.

    If you are on 10g, then you cannot start the MRP when your standby database is opened read only mode. Logs would not be applied if you have your standby database opened in read only mode. To start MRP, you need to have your standby database is mount stage.

    If you are on 11g, then you can start MRP even when your standby database is opened in read only mode. Start the MRP on the standby database and post the outcome of the below query on the standby database:
    select process,status,sequence# from v$managed_standby;

Legend

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