This discussion is archived
10 Replies Latest reply: Apr 3, 2013 9:35 AM by mseberg RSS

primary db logs going to $OH/dbs location on standby instead of dest_1

977635 Newbie
Currently Being Moderated
Running Oracle 11.2 on Linux 5.8
=====================

I've just set up a new DG Test environment and confirmed logs are being applied on my physical standby database.

Problem is that I just noticed the logs are going to my $OH/dbs directory.
When I checked V$ARCHIVE_DEST, it showed:
SQL> select dest_name,status,destination from V$ARCHIVE_DEST;

DEST_NAME                      STATUS    DESTINATION
------------------------------ --------- ------------------------------
LOG_ARCHIVE_DEST_1             VALID     /arch/DGTEST
LOG_ARCHIVE_DEST_2             DEFERRED  DGTEST_DG1
LOG_ARCHIVE_DEST_3             INACTIVE
 . . .
LOG_ARCHIVE_DEST_31            INACTIVE
STANDBY_ARCHIVE_DEST           VALID     /oracle/product/db/11.2.0.2/dbs/arch
But I do not have STANDBY_ARCHIVE_DEST set, and it is not in my init<SID>.ora file.
In fact, I did not even have an 'arch' directory located under $OH/dbs, so I created one, then did a log switch on the primary side, and sure enough, it is now putting the archive files in the $OH/dbs/arch directory.

Here are my destination locations for both primary and standby:
Primary database:
=============
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(DGTEST_DG1,DGTEST_DG2)'
*.log_archive_dest_1='LOCATION=/arch/DGTEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DGTEST_DG1'
*.LOG_ARCHIVE_DEST_2='service=DGTEST_DG2 LGWR ASYNC valid_for=(all_logfiles,primary_role) db_unique_name=DGTEST_DG2'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'


Standby database:
=============
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(DGTEST_DG1,DGTEST_DG2)'
*.log_archive_dest_1='LOCATION=/arch/DGTEST valid_for=(ONLINE_LOGFILE,ALL_ROLES) DB_UNIQUE_NAME=DGTEST_DG2'
*.LOG_ARCHIVE_DEST_2='service=DGTEST_DG1 LGWR ASYNC valid_for=(all_logfiles,primary_role) DB_UNIQUE_NAME=DGTEST_DG1'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=DEFER
*.log_archive_format='%t_%s_%r.arc'
Also, meant to include that my archive destination when I run "archive log list" shows for both databases as:
Archive destination /arch/DGTEST
Archive destination /arch/DGTEST


Edited by: 974632 on Apr 3, 2013 6:30 AM
  • 1. Re: primary db logs going to $OH/dbs location on standby instead of dest_1
    mseberg Guru
    Currently Being Moderated
    Hello;

    h3. Update

    Its STANDBY_ARCHIVE_DEST, obsolete in Oracle 11. I missed that on my first pass.

    http://docs.oracle.com/cd/E14072_01/server.112/e10820/initparams242.htm
    STANDBY_ARCHIVE_DEST           VALID     /oracle/product/db/11.2.0.2/dbs/arch
    Not really obsolete, deprecated. Deprecated means it is no need required, As said the archive log location will be the default archive destination.


    ------------------------------------------------------------------------------------------------------------------------------

    Any chance you have db_recovery_file_dest defined?

    I might do a show parameter on it to be sure.

    Remember to remove LOG_ARCHIVE_DEST if it exists.

    STANDBY_ARCHIVE_DEST has been deprecated.

    In any event Oracle default mechanism is causing this, so something is not set.

    Best Regards

    mseberg

    Edited by: mseberg on Apr 3, 2013 8:41 AM
  • 2. Re: primary db logs going to $OH/dbs location on standby instead of dest_1
    977635 Newbie
    Currently Being Moderated
    Yes, correct, I understand that the STANDBY_ARCHIVE_DEST is deprecated, but it is using it anyway, even though I do not have it set.
    My compatible parameter is set to 11.2.0.0.0, so that is not an issue.
    I also saw some other postings of other people having similar issue, but no resolutions.

    I did have my db_recovery_file_dest defined, but was not using it.
    I undefined it and restarted the database, but am still seeing same behavior.
    I confirmed permissions are open on /arch/DGTEST so that is not the problem either.
  • 3. Re: primary db logs going to $OH/dbs location on standby instead of dest_1
    mseberg Guru
    Currently Being Moderated
    I agree. You are correct.

    My check ( not set ! )
    SQL> show parameter STANDBY_ARCHIVE_DEST
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    standby_archive_dest                 string      ?/dbs/arch
    SQL> 
    Turns out there's a note on this :

    Setup Local archiving on standby to Prevent Logs sent to $ORACLE_HOME/dbs (on both GRID and SQLPLUS) [ID 1271095.1]

    Best Regards

    mseberg
  • 4. Re: primary db logs going to $OH/dbs location on standby instead of dest_1
    977635 Newbie
    Currently Being Moderated
    Thank you sir. That was helpful.

    It turns out I had my VALID_FOR parameter in my dest_1 string set incorrectly.
    The note gave me the information to set it correctly, and now it seems to be working correctly.

    The archive files are now going to the correct location on the standby server as defined in DEST_1 in the standby database.
    I still see the STANDBY_ARCHIVE_DEST defined in the V$ARCHIVE_DEST view, but now it is showing correctly, and it is working properly.
    SQL> select dest_name,status,destination from V$ARCHIVE_DEST;
    
    DEST_NAME                      STATUS    DESTINATION
    ------------------------------ --------- ------------------------------
    LOG_ARCHIVE_DEST_1             VALID     /arch/DGTEST
    LOG_ARCHIVE_DEST_2             DEFERRED  DGTEST_DG1
    LOG_ARCHIVE_DEST_3             INACTIVE
     .  .  .
    LOG_ARCHIVE_DEST_31            INACTIVE
    STANDBY_ARCHIVE_DEST           VALID     /arch/DGTEST
    But when I sho parameter dest, it still shows as: ?/dbs/arch
  • 5. Re: primary db logs going to $OH/dbs location on standby instead of dest_1
    mseberg Guru
    Currently Being Moderated
    Hello again;
    ?/dbs/arch
    I believe this is a default. Shows the same on mine too but mine works OK. Since I had it in my setup notes not to set it it almost has to be the default.

    Best Regards

    mseberg
  • 6. Re: primary db logs going to $OH/dbs location on standby instead of dest_1
    977635 Newbie
    Currently Being Moderated
    Hi mesberg,

    I mean to call something to your attention in your setup notes:

    Re. http://www.visi.com/~mseberg/data_guard_on_oracle_11_step_by_step.html

    In your pfile parameters, you have a duplicate entry for:

    *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST','mandatory'

    and then lower in the file, you have:

    *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'

    I ignored the first one and only put the second entry in my pfile.
  • 7. Re: primary db logs going to $OH/dbs location on standby instead of dest_1
    mseberg Guru
    Currently Being Moderated
    Thanks

    Will fix!

    Fixed. That's one old ugly HTML file.

    Thanks again!
  • 8. Re: primary db logs going to $OH/dbs location on standby instead of dest_1
    977635 Newbie
    Currently Being Moderated
    Well, here I am again... I've recreated the entire DGTEST environment and did a switchover (using instructions from http://www.visi.com/~mseberg/data_guard/Data_Guard_switchover.html), and my log files are not being shipped to the original primary server.

    I can't find anything wrong, and tnsping works as it should to the services as well as the database.
    from the NEW primary db:
    =================
    *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(DGTEST_DG1,DGTEST_DG2)'
    *.log_archive_dest_1='LOCATION=/arch/DGTEST valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DGTEST_DG2'
    *.LOG_ARCHIVE_DEST_2='service=DGTEST_DG1 LGWR ASYNC valid_for=(all_logfiles,primary_role) DB_UNIQUE_NAME=DGTEST_DG1'
    *.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
    *.log_archive_dest_state_2='ENABLE'
    From the new primary server:   "brahe"
    ========================
    
    $ [brahe] <DGTEST> /oracle/product/db/11.2.0.2/dbs>tnsping *DGTEST*
    
    TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 03-APR-2013 12:00:46
    
    Copyright (c) 1997, 2010, Oracle.  All rights reserved.
    
    Used parameter files:
    
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = *kepler.nnet*)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DGTEST) (UR = A)))
    OK (0 msec)
    
    
    $ [brahe] <DGTEST> /oracle/product/db/11.2.0.2/dbs>tnsping *DGTEST_DG1*
    
    TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 03-APR-2013 12:00:52
    
    Copyright (c) 1997, 2010, Oracle.  All rights reserved.
    
    Used parameter files:
    
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = *kepler.nnet*)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DGTEST) (UR = A)))
    OK (0 msec)
    
    
    $ [brahe] <DGTEST> /oracle/product/db/11.2.0.2/dbs>tnsping *DGTEST_DG2*
    
    TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 03-APR-2013 12:00:59
    
    Copyright (c) 1997, 2010, Oracle.  All rights reserved.
    
    Used parameter files:
    
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = *brahe.nnet*)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DGTEST) (UR = A)))
    OK (10 msec)
    From the NEW standby:
    ===============
    *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(DGTEST_DG1,DGTEST_DG2)'
    *.log_archive_dest_1='LOCATION=/arch/DGTEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DGTEST_DG1'
    *.LOG_ARCHIVE_DEST_2='service=DGTEST_DG2 LGWR ASYNC valid_for=(all_logfiles,primary_role) db_unique_name=DGTEST_DG2'
    *.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
    *.log_archive_dest_state_2='DEFER'
    From the NEW standby server:   "kepler"
    =========================
    $ [kepler] <DGTEST> /oracle/product/db/11.2.0.2/dbs>tnsping DGTEST
    
    TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 03-APR-2013 12:07:39
    
    Copyright (c) 1997, 2010, Oracle.  All rights reserved.
    
    Used parameter files:
    
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = kepler.nnet)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DGTEST)))
    OK (10 msec)
    
    
    $ [kepler] <DGTEST> /oracle/product/db/11.2.0.2/dbs>tnsping DGTEST_DG1
    
    TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 03-APR-2013 12:07:57
    
    Copyright (c) 1997, 2010, Oracle.  All rights reserved.
    
    Used parameter files:
    
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = kepler.nnet)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DGTEST) (UR = A)))
    OK (0 msec)
    
    
    $ [kepler] <DGTEST> /oracle/product/db/11.2.0.2/dbs>tnsping DGTEST_DG2
    
    TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 03-APR-2013 12:08:14
    
    Copyright (c) 1997, 2010, Oracle.  All rights reserved.
    
    Used parameter files:
    
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = brahe.nnet)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DGTEST) (UR = A)))
    OK (0 msec)
  • 9. Re: primary db logs going to $OH/dbs location on standby instead of dest_1
    977635 Newbie
    Currently Being Moderated
    Should I start a new thread for this?
  • 10. Re: primary db logs going to $OH/dbs location on standby instead of dest_1
    mseberg Guru
    Currently Being Moderated
    I would. Why limit your answers?

    Test I use from new Standby ( Primary shown )
    export ORACLE_SID=PRIMARY
    /home/oracle:PRIMARY >sqlplus "/ as sysdba" SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 3 11:16:44 2013 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> connect sys/<password>@standby as sysdba Connected. SQL>
    This rules out connect issues in almost all cases

    h2. ?

    Have performed a switchover on my test system and I'm waiting to compare notes

    Edited by: mseberg on Apr 3, 2013 11:17 AM

    Edited by: mseberg on Apr 3, 2013 11:34 AM

Legend

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