1 2 Previous Next 22 Replies Latest reply: Jan 26, 2013 4:47 PM by Pinela RSS

    help with log_archive_dest_3

    977635
      Hello friends.

      I recently inherited an Oracle 11.2 database (Enterprise Edition) running on Solaris 10 running Physical DG with a standby database on another server.
      No problems there, everything is in sync.

      My review of the database found that the log_archive_dest_3 was set to a service for a database that did not exist (on another server).
      Apparently it had existed at one time in an attempt to set up a reporting database, but had since been shutdown and removed.
      But, the log_archive_dest_3 still pointed to it, and the log_archive_dest_state_3 was still enabled.

      The first thing I did was set my log_archive_dest_state_3 to DEFER.

      Next, I reset the log_archive_dest_3 using command:
      SQL> ALTER SYSTEM RESET log_archive_dest_3 SCOPE=SPFILE SID='*';
      Since the above command is the proper command for unsetting any parameter, after I rebooted the database, I expected to find the value for log_archive_dest_3 empty. But it was not. Instead, I found:
      location="/opt/oracle/orabackup/REMEDY/archivelogs",  valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)
      So, why and how did my dest_3 get changed to the above, instead of being empty.
      (yes, I restarted with spfile)

      Also, I found my log_archive_dest_state_3 set to ENABLE.
      I did not set this, nor did I find any instances of it being changed or set in the alert log.

      What does the above mean?
      And I wonder if I should leave it as it is set, including the log_archive_dest_state_3 to ENABLE.

      Looking back at the alert log for when I rebooted last night, I found the commands in the alert log that was apparently automatically generated at the time of startup.
      Sun Jan 20 23:53:14 2013
      NSA2 started with pid=34, OS id=24199
      ARC3: Archival started
      ARC0: STARTING ARCH PROCESSES COMPLETE
      ALTER SYSTEM SET log_archive_dest_3='location="/opt/oracle/orabackup/REMEDY/archivelogs"',' valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)' SCOPE=BOTH SID
      ='REMEDY';
      Using STANDBY_ARCHIVE_DEST parameter default value as /opt/oracle/orabackup/arch/REMEDY
      ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH SID='REMEDY';
      ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='REMEDY';
      ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='REMEDY';
        • 1. Re: help with log_archive_dest_3
          CKPT
          Hello,

          Is your Primary database is cluster database? See below test in case of single instance where it is worked as expected.
          SQL> show parameter dest_3
          
          NAME                                 TYPE        VALUE
          ------------------------------------ ----------- ------------------------------
          db_create_online_log_dest_3          string
          log_archive_dest_3                   string
          log_archive_dest_30                  string
          log_archive_dest_31                  string
          SQL> show parameter spfile
          
          NAME                                 TYPE        VALUE
          ------------------------------------ ----------- ------------------------------
          spfile                               string      C:\ORACLE\APP\PRODUCT\11.2.0\D
                                                           B_1\DATABASE\SPFILECKPT.ORA
          
          SQL> alter system set log_archive_dest_3='location=c:\oracle\arch_3 valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)' scope=both;
          
          System altered.
          
          SQL> show parameter log_archive_dest_3;
          
          NAME                                 TYPE        VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_dest_3                   string      location=c:\oracle\arch_3 vali
                                                           d_for=(STANDBY_LOGFILE,STANDBY
                                                           _ROLE)
          log_archive_dest_30                  string
          log_archive_dest_31                  string
          "SQL> ALTER SYSTEM RESET log_archive_dest_3 SCOPE=SPFILE SID='*';"
          
          System altered.
          
          SQL> show parameter log_archive_dest_3;
          
          NAME                                 TYPE        VALUE
          ------------------------------------ ----------- ------------------------------
          "log_archive_dest_3                   string      location=c:\oracle\arch_3 valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)"
          log_archive_dest_30                  string
          log_archive_dest_31                  string
          SQL> shut immediate
          Database closed.
          Database dismounted.
          ORACLE instance shut down.
          SQL> startup
          ORACLE instance started.
          
          Total System Global Area  778387456 bytes
          Fixed Size                  1374808 bytes
          Variable Size             343934376 bytes
          Database Buffers          427819008 bytes
          Redo Buffers                5259264 bytes
          Database mounted.
          Database opened.
          SQL> show parameter log_archive_dest_3;
          
          NAME                                 TYPE        VALUE
          ------------------------------------ ----------- ------------------------------
          "log_archive_dest_3                   string"
          log_archive_dest_30                  string
          log_archive_dest_31                  string
          SQL>
          Also read the below content for the clause you are using for RAC and non-RAC.

          >
          alter_system_reset_clause
          The alter_system_reset_clause is for use in a Real Application Clusters environment. It gives you separate control for an individual instance over parameters that may have been set for all instances in a server parameter file. The SCOPE clause has the same behavior as described for the alter_system_set_clause.

          SID
          Specify the SID clause to remove a previously specified setting of this parameter for your instance—that is, a previous ALTER SYSTEM SET ... SID = 'sid' statement. (In a non-RAC environment, you can specify SID = '*' for this clause.) Your instance will assume the value of the parameter as specified in a previous or subsequent ALTER SYSTEM SET ... SID = '*' statement.
          >

          Source: http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_2013.htm#i2146449
          • 2. Re: help with log_archive_dest_3
            977635
            Well, I'm not sure how to answer the question regarding if the database is clustered or not.
            No, I am not using RAC if that is what you are asking.

            I believe I used the 'reset' command properly, (the same as you did with"ALTER SYSTEM RESET log_archive_dest_3 SCOPE=SPFILE SID='*';")

            So, how (and why) did Oracle automatically reset my dest_3 and enable it?

            Note that i just found my log_archive_config set to include the original dest_3 service, so I changed it to only include the two main services for our primary and standby databases. But I don't think this has anything to do with how or why Oracle reset my dest_3 and enabled it.
            • 3. Re: help with log_archive_dest_3
              CKPT
              974632 wrote:
              Well, I'm not sure how to answer the question regarding if the database is clustered or not.
              No, I am not using RAC if that is what you are asking.
              Yes, my question was either it is RAC database or not. Thanks.
              I believe I used the 'reset' command properly, (the same as you did with"ALTER SYSTEM RESET log_archive_dest_3 SCOPE=SPFILE SID='*';")
              Even i used the same command what you have shared here. :-)
              So, how (and why) did Oracle automatically reset my dest_3 and enable it?
              Note that i just found my log_archive_config set to include the original dest_3 service, so I changed it to only include the two main services for our primary and standby databases. But I don't think this has anything to do with how or why Oracle reset my dest_3 and enabled it.
              Can you perform once again the same test now? Do you think there are any changes after you reset the destination?
              BTW, You can alternate as below
              SQL> alter system set log_archive_dest_3='' scope=both sid='*';
              System altered.
              SQL> show parameter log_archive_dest_3
              
              NAME                                 TYPE        VALUE
              ------------------------------------ ----------- ------------------------------
              log_archive_dest_3                   string
              log_archive_dest_30                  string
              log_archive_dest_31                  string
              SQL>
              Or you can defer the destination as below.
              SQL> alter system set log_archive_dest_state_3='defer' scope=both sid='*';
              • 4. Re: help with log_archive_dest_3
                977635
                Unfortunately, I can not bounce the database since it is production.
                I have read that one should never reset a parameter by setting it to an empty value manually as in your example.
                alter system set log_archive_dest_3='' scope=both sid='*';
                There are a couple of sources on the internet that vehemently state not to do it as shown above.
                I just follow what they said and used the 'reset' command instead.
                • 5. Re: help with log_archive_dest_3
                  977635
                  Should I repost this thread? It seems our dialog got off the main subject and confused the issue in case anyone else has some inputs.
                  • 6. Re: help with log_archive_dest_3
                    CKPT
                    974632 wrote:
                    Should I repost this thread? It seems our dialog got off the main subject and confused the issue in case anyone else has some inputs.
                    Now i think you cannot bounce it your production to reset the DEST_3. You can see clearly how it was worked without any issues.
                    i dont think there will be such bugs, because this is relationship with parameter and they can modified on fly too. Let's have a trial once again whenever it is allowed and then post the complete log what you did then we can see where is going wrong.
                    • 7. Re: help with log_archive_dest_3
                      Pinela
                      Hi,

                      If you are using the Broker, it can set parameters based on its stored configurations about the environment.

                      br,
                      jpinela.
                      • 8. Re: help with log_archive_dest_3
                        977635
                        Okay, thanks CKPT. I also opened an SR with Oracle to get their feedback.

                        Funny thing is that the dest_3 is not getting archivelogs put in the folder.

                        And if we look at what it set the dest_3 as, it shows:
                        location="/opt/oracle/orabackup/REMEDY/archivelogs",  valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)
                        This seems to apply it only writes to this location when (this) primary database is switched over to standby_role.
                        • 9. Re: help with log_archive_dest_3
                          977635
                          That is what I think may be happening.
                          Exactly.

                          So, I think we are using the broker.
                          How do I confirm and make the changes accordingly.

                          When i look at the configuration in dgmgr, it shows:
                          GMGRL for Solaris: Version 11.2.0.2.0 - 64bit Production
                          
                          Copyright (c) 2000, 2009, Oracle. All rights reserved.
                          
                          Welcome to DGMGRL, type "help" for information.
                          DGMGRL> connect /
                          Connected.
                          DGMGRL> show configuration
                          
                          Configuration - REMEDY_DG
                          
                            Protection Mode: MaxPerformance
                            Databases:
                              REMEDY_DG1 - Primary database
                                Warning: ORA-16792: configurable property value is inconsistent with database setting
                          
                              REMEDY_DG2 - Physical standby database
                          
                          Fast-Start Failover: DISABLED
                          
                          
                          Configuration Status:
                          WARNING
                          oerr ora 16792 shows:

                          16792, 0000, "configurable property value is inconsistent with database setting"
                          // *Cause:  The values of one or more configurable properties were
                          // inconsistent with database in-memory settings or server parameter
                          // file settings. This may happen by directly altering initialization
                          // parameters instead of editing configurable property values using
                          // Data Guard broker.
                          // *Action: Query the InconsistentProperties property on the database or check
                          // the Data Guard broker log to find which properties are set
                          // inconsistently. Reset these properties to make them consistent
                          // with the database settings. Alternatively, enable the database
                          // or the entire configuration to allow the configurable property
                          // settings to be propagated to the initialization parameters.

                          Edited by: 974632 on Jan 21, 2013 8:51 AM

                          Edited by: 974632 on Jan 21, 2013 8:52 AM


                          I guess I'm going to need a quick summary of how to configure the database parameters using DG Broker.
                          • 10. Re: help with log_archive_dest_3
                            Pinela
                            hi,

                            like you show us, you are using the broker.
                            another way would be using
                            SQL> show parameter dg_broker_start
                            to check the value of the propertys issue
                            DGMGRL> SHOW DATABASE VERBOSE 'REMEDY_DG1';
                            br,
                            jpinela
                            • 11. Re: help with log_archive_dest_3
                              977635
                              Here are the results which confirm we are using DG Broker.
                              SQL> sho parameter dg_broker_start
                              
                              NAME                                 TYPE                             VALUE
                              ------------------------------------ -------------------------------- ------------
                              dg_broker_start                      boolean                          TRUE
                              
                              
                              DGMGRL> show database verbose 'REMEDY_DG1';
                              
                              Database - REMEDY_DG1
                              
                                Role:            PRIMARY
                                Intended State:  TRANSPORT-ON
                                Instance(s):
                                  REMEDY
                                    Warning: ORA-16714: the value of property StandbyFileManagement is inconsistent with the database setting
                              
                                Properties:
                                  DGConnectIdentifier             = 'REMEDY_DG1'
                                  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           = 'MANUAL'
                                  ArchiveLagTarget                = '0'
                                  LogArchiveMaxProcesses          = '4'
                                  LogArchiveMinSucceedDest        = '1'
                                  DbFileNameConvert               = ''
                                  LogFileNameConvert              = ''
                                  FastStartFailoverTarget         = ''
                                  InconsistentProperties          = '(monitor)'
                                  InconsistentLogXptProps         = '(monitor)'
                                  SendQEntries                    = '(monitor)'
                                  LogXptStatus                    = '(monitor)'
                                  RecvQEntries                    = '(monitor)'
                                  SidName                         = 'REMEDY'
                                  StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cassini)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=REMEDY_DG1)(INSTANCE_NAME=REMEDY)(SERVER=DEDICATED)))'
                                  StandbyArchiveLocation          = '/opt/oracle/orabackup/REMEDY/archivelogs'
                                  AlternateLocation               = ''
                                  LogArchiveTrace                 = '0'
                                  LogArchiveFormat                = '%t_%s_%r.dbf'
                                  TopWaitEvents                   = '(monitor)'
                              
                              Database Status:
                              WARNING
                              • 12. Re: help with log_archive_dest_3
                                Pinela
                                right!

                                and there you have the inconsistency.
                                the parameter standby file management.

                                you probably have that on AUTO in the DB.

                                BR,
                                Jpinela.
                                • 13. Re: help with log_archive_dest_3
                                  977635
                                  Yes, that is correct. I just set it to AUTO a couple of days ago (before the reboot).
                                  So now, how do I change it in the DG Manager?

                                  And also, how do I change my StandbyArchiveLocation in DG Manager?

                                  I assume I can just update my log_archive_dest_3 in the database.
                                  • 14. Re: help with log_archive_dest_3
                                    Pinela
                                    >
                                    Yes, that is correct. I just set it to AUTO a couple of days ago (before the reboot).

                                    So now, how do I change it in the DG Manager?
                                    >
                                    like this:
                                    DGMGRL> EDIT DATABASE 'REMEDY_DG1' SET PROPERTY 'StandyFileManagementt'='AUTO';
                                    And also, how do I change my StandbyArchiveLocation in DG Manager?
                                    >
                                    DG broker.. same command as before. check here the reference.

                                    http://docs.oracle.com/cd/B28359_01/server.111/b28295/cli.htm#i1005804
                                    >
                                    I assume I can just update my log_archive_dest_3 in the database.
                                    >
                                    yes sure, but using the broker, you should maintain with the broker CLI.
                                    SQL> alter system set log_archive_dest_3='<what you want>' scope=both;
                                    br,
                                    jpinela.

                                    Edited by: Pinela on Jan 21, 2013 6:58 PM
                                    1 2 Previous Next