This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Jan 26, 2013 2:47 PM by Pinela RSS

help with log_archive_dest_3

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points