4 Replies Latest reply: Feb 23, 2011 8:30 AM by 466593 RSS

    Data Guard Configuration Issue / ORA-16047

    Centinul
      So last night I decided to setup a test Physical Standby database. I had everything working correctly and when I started playing around with the Data Guard Broker I started having some problems. Now I can't get the logs to ship from the primary to the standby.

      Version: Primary and Standby
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE    11.2.0.1.0    Production
      TNS for Linux: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      OS: Primary and Standby
      [oracle@dgdb0 trace]$ uname -a
      Linux dgdb0.localdomain 2.6.32-100.28.5.el6.x86_64 #1 SMP Wed Feb 2 18:40:23 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
      I first noticed a problem with a large gap in sequence numbers.

      Standby
      SQL> SELECT sequence#, applied from v$archived_log order by sequence#;
      
       SEQUENCE# APPLIED
      ---------- ---------
           8 YES
           9 YES
          10 YES
          11 YES
          12 YES
          13 YES
          14 YES
      
      7 rows selected.
      Primary
      SQL> archive log list;
      Database log mode           Archive Mode
      Automatic archival           Enabled
      Archive destination           USE_DB_RECOVERY_FILE_DEST
      Oldest online log sequence     37
      Next log sequence to archive   39
      Current log sequence           39
      Here is some of the configuration information on the primary:
      SQL> show parameter db_name
      
      NAME                     TYPE     VALUE
      ------------------------------------ ----------- ------------------------------
      db_name                  string     dgdb0
      SQL> show parameter db_unique_name
      
      NAME                     TYPE     VALUE
      ------------------------------------ ----------- ------------------------------
      db_unique_name                 string     dgdb0
      SQL> show parameter log_archive_config
      
      NAME                     TYPE     VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_config             string     dg_config=(dgdb0,dgdb1)
      SQL> show parameter log_archive_dest_2
      
      NAME                     TYPE     VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_dest_2             string     service=dgdb1 async valid_for=
                               (online_logfile,primary_role)
                               db_unique_name=dgdb1
      Standby parameters
      SQL> show parameter db_name
      
      NAME                     TYPE     VALUE
      ------------------------------------ ----------- ------------------------------
      db_name                  string     dgdb0
      SQL> show parameter db_unique_name
      
      NAME                     TYPE     VALUE
      ------------------------------------ ----------- ------------------------------
      db_unique_name                 string     dgdb1
      So I proceeded to run this query:
      SQL> SELECT error from v$archive_dest WHERE dest_name='LOG_ARCHIVE_DEST_2';
      
      ERROR
      -----------------------------------------------------------------
      ORA-16047: DGID mismatch between destination setting and target
      database
      The error description is:
      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.
      As you can see from above the DB_UNIQUE_NAME in the LOG_ARCHIVE_DEST_2 parameter matches that of the standby database.

      Also DG_BROKER_START is set to false on both the primary and standby databases.

      Finally, I've removed all the drc* files from the $ORACLE_HOME/dbs directories on both the primary and standby servers to ensure the broker is not configured.

      Where did I go wrong? How can I get the standby caught up and working correctly again?

      I apologized if I missed anything. I'm relatively new to standby databases.
        • 1. Re: Data Guard Configuration Issue / ORA-16047
          mseberg
          Centinul;

          I have noticed a couple things

          1. If you are running the query below from the standby you will probably always get the results you posted

          SELECT sequence#, applied from v$archived_log order by sequence#;


          What I do if run this from the primary and I add the "DEST_ID" column to the query.

          2. You might have better luck finding GAPS using these queries:


          select max(sequence#) from v$archived_log where applied='YES';

          select process,status from v$managed_standby;

          SELECT * FROM V$ARCHIVE_GAP;


          3. You are mixing SQL results with Data Broker, that can bite you. Not sure where you went wrong but I would create PFILE versions at both ends before trying to Data Broker. The you can review each setting and avoid issues before adding Data Broker. Data Broker will take control and you may even find it adds entries to your parameter file.

          The ORA-16047 is probably database parameter related and this should at least help answer the question. For example you might be missing log_archive_config on the Standby or soething. Comparing the two PFILE's should narrow this down


          I checked my Data Broker notes but did not find an ORA-16047, I managed ORA-01031, ORA-16675, ORA-12514, and ORA-16608.


          For me I decided it was a good idea to run Data Guard without Data Broker at first until I got the feel of it using SQL.


          Last of all if you have not already consider buying Larry Carpenter's "Oracle Data Guard 11g Handbook" In my humble opinion its worth every penny and more.


          Best Regards

          mseberg
          • 2. Re: Data Guard Configuration Issue / ORA-16047
            Centinul
            mseberg wrote:
            1. If you are running the query below from the standby you will probably always get the results you posted

            SELECT sequence#, applied from v$archived_log order by sequence#;
            Why? Can you please explain?
            3. You are mixing SQL results with Data Broker, that can bite you. Not sure where you went wrong but I would create PFILE versions at both ends before trying to Data Broker. The you can review each setting and avoid issues before adding Data Broker. Data Broker will take control and you may even find it adds entries to your parameter file.

            The ORA-16047 is probably database parameter related and this should at least help answer the question. For example you might be missing log_archive_config on the Standby or soething. Comparing the two PFILE's should narrow this down
            Turns out that is exactly what it was. The LOG_ARCHIVE_CONFIG parameter on the standby was incorrect, probably due to me removin the broker configuration.
            For me I decided it was a good idea to run Data Guard without Data Broker at first until I got the feel of it using SQL.
            In retrospect I wish I had done this.
            Last of all if you have not already consider buying Larry Carpenter's "Oracle Data Guard 11g Handbook" In my humble opinion its worth every penny and more.
            I'll look into this.

            Thanks!
            • 3. Re: Data Guard Configuration Issue / ORA-16047
              mseberg
              What I mean by "Why? Can you please explain?" (referring mixing SQL results with Data Broker) is that I noticed Data Broker was adding setting to my parameter file when I first added it (Data Broker). I kept trying to "Fix" my my parameter file only to have Data Broker add the setting back in. I think it was log_archive_dest_n.

              Data Broker takes control of the entire Data Guard configuration, if you try to make changes using SQL Data Broker will try to fix it and this can lead to inconsistencies between the Data Broker configuration and the database parameters.

              Thank You for the kind words.

              Glad I could help.

              mseberg

              It occurs to you might mean "SELECT sequence#, applied from v$archived_log order by sequence#;"

              I use these on the Primary to check things. The DEST_ID allows a quick look at both systems/

              SELECT name as STANDBY, SEQUENCE#, applied, completion_time , DEST_ID from v$archived_log ;

              or

              SELECT name as STANDBY, SEQUENCE#, applied, completion_time from v$archived_log WHERE DEST_ID = 2 AND NEXT_TIME > SYSDATE -1;

              Edited by: mseberg on Feb 21, 2011 8:35 PM
              • 4. Re: Data Guard Configuration Issue / ORA-16047
                466593
                make sure that log_archive_config is set correctly on standby as well.