10 Replies Latest reply: Apr 3, 2013 11:35 AM by mseberg RSS

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

    977635
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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