1 2 3 Previous Next 39 Replies Latest reply: Nov 25, 2013 4:02 AM by Anar Godjaev Go to original post RSS
      • 15. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
        Anar Godjaev

        Hi,

         

        Please check In Secondary location:

         

        select PROCESS, STATUS from v$managed_standby where PROCESS ='MRP0';

         

        if is it status APPLYING_LOG then run query in Secondary location:

         

        select name, value from v$dataguard_Stats;

        • 16. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
          MHAIDAR

          In Seconday Location

          SQL> select PROCESS, STATUS from v$managed_standby where PROCESS ='MRP0';

          PROCESS   STATUS
          --------- ------------
          MRP0      APPLYING_LOG

          SQL>

          SQL> select name, value from v$dataguard_Stats;

          NAME                             VALUE
          -------------------------------- -------------------
          transport lag
          apply lag                        +00 00:00:00
          apply finish time
          estimated startup time           22

           

           

          I can't understand the second query , please explain.

           

          Thanks in advance

          • 17. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
            Anar Godjaev

            In primary location:

            Alter system set LOG_ARCHIVE_DEST_STATE_2=defer;

            Alter system set LOG_ARCHIVE_DEST_STATE_2=enable;

            alter system switch logfile;

             

            And the run query in Seconray location:

             

            select name, value from v$dataguard_Stats;    (Paste result)

             

             

            V$DATAGUARD_STATS displays how much redo data generated by the primary database is not yet available on the standby database, showing how much redo data could be lost if the primary database were to crash at the time you queried this view. You can query this view on any instance of a standby database in a Data Guard configuration. If you query this view on a primary database, then the column values are cleared.

             

             

            V$DATAGUARD_STATS

            • 18. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
              Mahir M. Quluzade

              MHAIDAR wrote:

               

              In the primary

               

              SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

              RECOVERY_MODE
              -----------------------
              MANAGED REAL TIME APPLY

               

              It means your Apply Service is started on your STANDBY DATABASE.

              You are using DEST_2 for archive redo logs to STANDBY DATABASE.

               

               

              SQL> show parameter LOG_ARCHIVE_MAX_PROCESSES ;

              NAME                                 TYPE        VALUE
              ------------------------------------ ----------- -----------------------------
              log_archive_max_processes            integer     8

               

               

              In the Secondary

              SQL> select PROCESS, STATUS from v$managed_standby where PROCESS ='MRP0';

              PROCESS   STATUS
              --------- ------------
              MRP0      WAIT_FOR_LOG

               

              It means MRP process waiting logs for resolve GAP.

               

              SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

              RECOVERY_MODE
              -----------------------
              IDLE


              SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

              Database altered.

              SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

              Database altered.

              SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

              RECOVERY_MODE
              -----------------------
              IDLE

               

               

              RECOVERY_MODE getting information from standby side. And your query running on standby  database.

              IDLE because  DEST_2 database is not standby  database

              You can check : dest_2 = SERVICE= <priamry> on standby database in your case or not nothing


              what is is MRP0 ?


              Apply service using MRP - background process.

              MRP - Media Recover process applying received redo changes from primary.

               

              my setup is ok ?

              How can I make sure the 2 databases is  sychronized?

               

              You  must check transport service, apply service is running, standby can receive redo changes from  primary.

              If you  are using Real time apply , we can say your database is sychronized.

               

              But if you want full sychronized standby database, then you must use MAX PROTECTION mode for zero loss data.

               

              Regards

              Mahir M. Quluzade

              • 19. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
                MHAIDAR

                Dear Mahir and AnarGodjeav

                 

                Thanks for your help, and explination.

                • 20. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
                  Anar Godjaev

                  You are welcome.....

                   

                  just look at the automatic sh....... (in secondary location)

                   

                  bash-3.2$ cat ./check_standby.sh
                  #!/bin/sh
                  ORACLE_HOME="/u01/app/oracle/product/11.2.0/db_1"
                  export ORACLE_HOME

                  LD_LIBRARY_PATH="$ORACLE_HOME/lib:/usr/dt/lib:/usr/openwin/lib"
                  PATH="$ORACLE_HOME/bin:/usr/ccs/bin:/usr/sbin:$PATH"

                  export PATH LD_LIBRARY_PATH

                  ORACLE_SID=AZDB
                  export ORACLE_SID
                  sqlplus -s <<SQLEND
                  connect /as sysdba
                  set pagesize 1000
                  set linesize 120
                  column message format A99
                  column name format A25
                  column value format a50
                  select timestamp, message from v\$dataguard_Status;
                  select name, value from v\$dataguard_Stats;
                  SQLEND

                  bash-3.2$

                   

                  For example In my system:

                   

                  bash-3.2$ ./check_standby.sh


                  TIMESTAMP          MESSAGE
                  ------------------ ---------------------------------------------------------------------------------------------------
                  21-NOV-13          ARC3: Beginning to archive thread 2 sequence 46330 (768523508221-768534808619)
                  21-NOV-13          ARC3: Completed archiving thread 2 sequence 46330 (0-0)
                  21-NOV-13          Media Recovery Waiting for thread 2 sequence 46331 (in transit)
                  21-NOV-13          ARC1: Beginning to archive thread 1 sequence 49564 (768523571693-768534864343)
                  21-NOV-13          ARC1: Completed archiving thread 1 sequence 49564 (0-0)
                  21-NOV-13          ARC3: Beginning to archive thread 2 sequence 46331 (768534808619-768534864347)
                  21-NOV-13          ARC3: Completed archiving thread 2 sequence 46331 (0-0)
                  21-NOV-13          Media Recovery Waiting for thread 1 sequence 49565 (in transit)
                  21-NOV-13          Media Recovery Waiting for thread 2 sequence 46332 (in transit)
                  21-NOV-13          ARC3: Beginning to archive thread 1 sequence 49565 (768534864343-768543205568)
                  21-NOV-13          ARC3: Completed archiving thread 1 sequence 49565 (0-0)
                  21-NOV-13          Media Recovery Waiting for thread 1 sequence 49566 (in transit)
                  21-NOV-13          ARC1: Beginning to archive thread 2 sequence 46332 (768534864347-768543211830)
                  21-NOV-13          ARC1: Completed archiving thread 2 sequence 46332 (0-0)
                  21-NOV-13          Media Recovery Waiting for thread 2 sequence 46333 (in transit)
                  21-NOV-13          ARC0: Beginning to archive thread 1 sequence 49566 (768543205568-768552988820)
                  21-NOV-13          ARC0: Completed archiving thread 1 sequence 49566 (0-0)
                  21-NOV-13          Media Recovery Waiting for thread 1 sequence 49567 (in transit)
                  21-NOV-13          ARC3: Beginning to archive thread 2 sequence 46333 (768543211830-768552994497)
                  21-NOV-13          ARC3: Completed archiving thread 2 sequence 46333 (0-0)
                  21-NOV-13          Media Recovery Waiting for thread 2 sequence 46334 (in transit)

                  256 rows selected.


                  NAME                      VALUE
                  ------------------------- --------------------------------------------------
                  transport lag             +00 00:00:00
                  apply lag                 +00 00:00:00
                  apply finish time         +00 00:00:00.000
                  estimated startup time    29

                  • 21. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
                    MHAIDAR

                    Hi AnarGodjeav

                     

                    I am doing test for switch over

                    I did the following:

                    SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

                    SWITCHOVER_STATUS
                    --------------------
                    TO STANDBY

                    Then I did the following:

                    SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

                    Database altered.

                     

                    On Standby side


                    SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

                    SWITCHOVER_STATUS
                    --------------------
                    TO PRIMARY

                    SQL>
                    SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
                    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
                    *
                    ERROR at line 1:
                    ORA-01275: Operation ADD LOGFILE is not allowed if standby file management is automatic.

                     

                    Do I have to cancel the session and what is the statment the following is correct,

                     

                    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;>

                    ALTER DATABASE OPEN;

                     

                    Please advice

                    • 22. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
                      Anar Godjaev

                      HI ,

                      This Switchover process is a very important.  Please see following link:  SWITCHOVER PROCESS |

                      Oracle database know-how and tips: Dataguard : Switch over step

                       

                      OERR: ORA-1275 Operation %s is not allowed if standby file management is autom (Doc ID 172490.1)

                       

                       

                      please send result in Secondary location:  (All archived logs is applied or not?)

                       

                      select name, value from v$dataguard_Stats;

                       

                       

                      Thank you

                      • 23. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
                        Mahir M. Quluzade

                        All archived logs must be applied, check this. 

                         

                        What about log_file_name_convert parameter on standby database?

                        If you are didn't set this parameter, please set and start database mount mode.

                         

                        1. alter system set log_file_name_convert ='primary','standby' scope=spfile;

                        2. shut immediate;

                        3. startup mount;

                        4. ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

                         

                        Regards

                        Mahir M. Quluzade

                        • 24. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
                          MHAIDAR

                          Hi Thanks for your reply

                           

                          I tried the second link procedure , I think still not completed see last lines in the alert log file

                          Sun Nov 24 07:34:13 2013

                          Errors in file D:\APP\ADMIN\diag\rdbms\epprod2\epprod2\trace\epprod2_m000_1872.trc:

                          ORA-19624: operation failed, retry possible

                          ORA-19504: failed to create file "E:\RBACKUP\CFC-2128199230-20131124-00"

                          ORA-27040: file create error, unable to create file

                          OSD-04002: unable to open file

                          O/S-Error: (OS 3) The system cannot find the path specified.

                          Sun Nov 24 07:40:40 2013

                          ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;

                           

                          I did ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH; to return it back it should be AUTO, but i change it to manual to

                          Becuase it give me an error.

                          ===

                          SQL> select name, value from v$dataguard_Stats;

                          no rows selected

                          ===

                           

                          I am getting the error again

                           

                          PING[ARC2]: Heartbeat failed to connect to standby 'epprod2'. Error is 16047.

                           


                          Any help please?

                           

                          Thanks in advance

                          • 25. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
                            Anar Godjaev

                            PING[ARC2]: Heartbeat failed to connect to standby 'epprod2'. Error is 16047.

                             

                            % oerr ora 16047

                            16047, 00000, "DGID mismatch between destination setting and standby"

                            // *Cause: The DB_UNIQUE_NAME specified for the destination does not match

                            // the DB_UNIQUE_NAME at the destination.

                            // *Action: Make sure the DB_UNIQUE_NAME specified in the LOG_ARCHIVE_DEST_n

                            // parameter defined for the destination matches the DB_UNIQUE_NAME

                            // parameter defined at the destination.

                            Thank you

                            • 26. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
                              MHAIDAR

                              Dear

                               

                               


                              alter system set dg_broker_start=true scope=both;
                              alter system set dg_broker_config_file1='D:\app\admin\product\11.2.0\dbhome_1\database\primary_dg1.conf' scope=spfile;
                              alter system set dg_broker_config_file1='D:\app\admin\product\11.2.0\dbhome_1\database\primary_dg2.conf' scope=spfile;


                              Then connect to standby:
                              SQL> alter system set dg_broker_start=true scope=both;
                              SQL> alter system set dg_broker_config_file1='D:\app\admin\product\11.2.0\dbhome_1\database\stdydb_dg1.conf' scope=spfile;
                              SQL> alter system set dg_broker_config_file2='D:\app\admin\product\11.2.0\dbhome_1\database\stdydb_dg2.conf' scope=spfile;

                              Add database to Data Guard.

                              $ dgmgrl
                              DGMGRL> connect sys/password@epprod1
                              Connected.
                              DGMGRL> CREATE CONFIGURATION 'epprod1' AS PRIMARY DATABASE IS 'epprod1' CONNECT IDENTIFIER IS 'epprod1';
                              DGMGRL> ADD DATABASE 'epprod2 AS CONNECT IDENTIFIER IS ‘epprod2’;
                              DGMGRL> enable configuration;

                              When I show the configuration I am getting

                              Database Status:

                              WARNING

                               

                              I am really confused... I don't know what shall I do

                              • 27. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
                                Mahir M. Quluzade

                                Hi,

                                 

                                Then paste here:

                                 

                                DGMGRL> show configuration verbose

                                 

                                DGMGRL> show database verbose <standby> ;

                                DGMGRL> show database verbose <primary> ;

                                 

                                Regards

                                Mahir M. Quluzade

                                • 28. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
                                  MHAIDAR

                                  Hi Please find the result

                                   

                                  DGMGRL> show configuration verbose

                                  Configuration - DREPPROD

                                    Protection Mode: MaxAvailability
                                    Databases:
                                      epprod1 - Primary database
                                        Warning: ORA-16629: database reports a different protection level from the protection mode

                                      epprod2 - Physical standby database
                                        Error: ORA-16664: unable to receive the result from a database

                                    Properties:
                                      FastStartFailoverThreshold      = '30'
                                      OperationTimeout                = '30'
                                      FastStartFailoverLagLimit       = '30'
                                      CommunicationTimeout            = '180'
                                      FastStartFailoverAutoReinstate  = 'TRUE'
                                      FastStartFailoverPmyShutdown    = 'TRUE'
                                      BystandersFollowRoleChange      = 'ALL'

                                  Fast-Start Failover: DISABLED

                                  Configuration Status:
                                  ERROR

                                  ===

                                  DGMGRL> show database verbose epprod1

                                  Database - epprod1

                                    Role:            PRIMARY
                                    Intended State:  TRANSPORT-ON
                                    Instance(s):
                                      epprod1

                                    Database Warning(s):
                                      ORA-16629: database reports a different protection level from the protection mode

                                    Properties:
                                      DGConnectIdentifier             = 'epprod'
                                      ObserverConnectIdentifier       = ''
                                      LogXptMode                      = 'SYNC'
                                      DelayMins                       = '0'
                                      Binding                         = 'optional'
                                      MaxFailure                      = '0'
                                      MaxConnections                  = '1'
                                      ReopenSecs                      = '300'
                                      NetTimeout                      = '30'
                                      RedoCompression                 = 'DISABLE'
                                      LogShipping                     = 'ON'
                                      PreferredApplyInstance          = ''
                                      ApplyInstanceTimeout            = '0'
                                      ApplyParallel                   = 'AUTO'
                                      StandbyFileManagement           = 'AUTO'
                                      ArchiveLagTarget                = '0'
                                      LogArchiveMaxProcesses          = '8'
                                      LogArchiveMinSucceedDest        = '2'
                                      DbFileNameConvert               = 'D:\DATABASES\EPPROD\, D:\DATABASES\EPPROD\, E:\DATABASES\EPPROD\, E:\DATABASES\EPPROD\'
                                      LogFileNameConvert              = 'D:\Databases\epprod\PrimaryRedolog\, D:\Databases\epprod\PrimaryRedolog\, D:\Databases\epprod\StandbyRedoLog\, D:\Databases\epprod\StandbyRedoLog\'
                                      FastStartFailoverTarget         = ''
                                      InconsistentProperties          = '(monitor)'
                                      InconsistentLogXptProps         = '(monitor)'
                                      SendQEntries                    = '(monitor)'
                                      LogXptStatus                    = '(monitor)'
                                      RecvQEntries                    = '(monitor)'
                                      SidName                         = 'epprod1'
                                      StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CORSKMBBOR01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=epprod1_DGMGRL)(INSTANCE_NAME=epprod1)(SERVER=DEDICATED))
                                      StandbyArchiveLocation          = 'E:\Fast_Recovery_Area\Archive\Epprod\Dest1\'
                                      AlternateLocation               = ''
                                      LogArchiveTrace                 = '127'
                                      LogArchiveFormat                = 'epprod_t%t_s%S_r%r.arc'
                                      TopWaitEvents                   = '(monitor)'

                                  Database Status:
                                  WARNING

                                  DGMGRL> show database verbose epprod2

                                  Database - epprod2

                                    Role:            PHYSICAL STANDBY
                                    Intended State:  APPLY-ON
                                    Transport Lag:   (unknown)
                                    Apply Lag:       (unknown)
                                    Real Time Query: OFF
                                    Instance(s):
                                      epprod2

                                    Properties:
                                      DGConnectIdentifier             = 'epprod2'
                                      ObserverConnectIdentifier       = ''
                                      LogXptMode                      = 'ASYNC'
                                      DelayMins                       = '0'
                                      Binding                         = 'OPTIONAL'
                                      MaxFailure                      = '0'
                                      MaxConnections                  = '1'
                                      ReopenSecs                      = '300'
                                      NetTimeout                      = '30'
                                      RedoCompression                 = 'DISABLE'
                                      LogShipping                     = 'ON'
                                      PreferredApplyInstance          = ''
                                      ApplyInstanceTimeout            = '0'
                                      ApplyParallel                   = 'AUTO'
                                      StandbyFileManagement           = 'AUTO'
                                      ArchiveLagTarget                = '0'
                                      LogArchiveMaxProcesses          = '8'
                                      LogArchiveMinSucceedDest        = '2'
                                      DbFileNameConvert               = 'D:\DATABASES\EPPROD\, D:\DATABASES\EPPROD\, E:\DATABASES\EPPROD\, E:\DATABASES\EPPROD\'
                                      LogFileNameConvert              = 'D:\Databases\epprod\PrimaryRedolog\, D:\Databases\epprod\PrimaryRedolog\, D:\Databases\epprod\StandbyRedoLog\, D:\Databases\epprod\StandbyRedoLog\'
                                      FastStartFailoverTarget         = ''
                                      InconsistentProperties          = '(monitor)'
                                      InconsistentLogXptProps         = '(monitor)'
                                      SendQEntries                    = '(monitor)'
                                      LogXptStatus                    = '(monitor)'
                                      RecvQEntries                    = '(monitor)'
                                      SidName                         = 'epprod2'
                                      StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.5.51)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=EPPROD2_DGMGRL)(INSTANCE_NAME=epprod2)(SERVER=DEDICATED)))'
                                      StandbyArchiveLocation          = 'E:\Fast_Recovery_Area\Archive\Epprod\Dest1\'
                                      AlternateLocation               = ''
                                      LogArchiveTrace                 = '127'
                                      LogArchiveFormat                = 'epprod_t%t_s%S_r%r.arc'
                                      TopWaitEvents                   = '(monitor)'

                                  Database Status:
                                  DGM-17016: failed to retrieve status for database "epprod2"
                                  ORA-16664: unable to receive the result from a database

                                  DGMGRL>

                                  • 29. Re: I am not getting archive files in standby Error "Heartbeat failed to connect to standby ' database'. Error is 12514.
                                    Mahir M. Quluzade

                                    Try this:

                                     

                                    DGMGRL> edit configuration set protection mode as MAXPERFORMACE;

                                    DGMGRL> edit database epprod1 set property LogxptMOde='ASYNC';

                                     

                                    DGMGRL> show configuration;

                                     

                                    If  Status Success, then try  following  steps:

                                     

                                    DGMGRL> edit database epprod1 set property LogxptMOde='SYNC';

                                    DGMGRL> edit database epprod2 set property LogxptMOde='SYNC';

                                     

                                    DGMGRL> edit configuration set protection mode as MAXAVAILABILITY;

                                     

                                    If Status Warning or Error, please check connectivity between primary and  standby database:

                                     

                                    tnsping and connection with sqlplus

                                     

                                     

                                     

                                    Regards

                                    Mahir M. Quluzade