This discussion is archived
1 2 3 Previous Next 39 Replies Latest reply: Nov 25, 2013 2: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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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.
    MahirM.Quluzade Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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.
    MahirM.Quluzade Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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.
    MahirM.Quluzade Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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.
    MahirM.Quluzade Guru
    Currently Being Moderated

    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