12 Replies Latest reply: Nov 16, 2012 1:26 AM by Shivananda Rao RSS

    Out of sync  Oracle DB on AIX machine

    Meenakshy singh
      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
          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
            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
              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
                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
                  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
                    It shows *"No rows selected"*
                    • 7. Re: Out of sync  Oracle DB on AIX machine
                      mseberg
                      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
                        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
                          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
                            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
                              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
                                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;