10 Replies Latest reply: Dec 18, 2012 11:42 PM by 974842 RSS

    how to stop dg log transfers

    977635
      Hello. I'm on Solaris 10, running Oracle EE 11.2.0.2.

      I'm fairly new to this job site and I've found they have a database that was once set up with DG physical standby (no FSFO).
      Apparently it has not been updating their standby database for over a year.
      At this point, they said they can live without the standby (at least for now).

      First problem I see is that in the alert log, there are millions of errors trying to connect to the old standby database.
      I have tried a couple times to disable (defer) the dest_2, but I'm still getting the errors in the alert log even though dest_2 is deferred.
      See below:
      ***********************************************************************
      
      Fatal NI connect error 12541, connecting to:
       (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=taurus)(PORT=1522))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ASSIA_DG2_DGB)(CID=(PROGRAM=oracle)(HOST=copernicus)(USER=ora
      cle))))
      
        VERSION INFORMATION:
              TNS for Solaris: Version 11.2.0.2.0 - Production
              TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.2.0 - Production
        Time: 18-DEC-2012 09:11:08
        Tracing not turned on.
        Tns error struct:
          ns main err code: 12541
      
      TNS-12541: TNS:no listener
          ns secondary err code: 12560
          nt main err code: 511
      
      TNS-00511: No listener
          nt secondary err code: 146
          nt OS err code: 0
      
      
      ***********************************************************************
      When I first checked the alert log, it was over 3Gb, which makes it pretty difficult to use.
      Anyway, now that I have a new fresh alert log, I see the errors keep being generated.
      See init params below.
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_dest                     string
      log_archive_dest_1                   string      LOCATION=/opt/oracle/orabackup/ASSIA/archivelogs/
      log_archive_dest_10                  string
      log_archive_dest_11                  string
      log_archive_dest_12                  string
      log_archive_dest_13                  string
      log_archive_dest_14                  string
      log_archive_dest_15                  string
      log_archive_dest_16                  string
      log_archive_dest_17                  string
      log_archive_dest_18                  string
      log_archive_dest_19                  string
      log_archive_dest_2                   string      service="ASSIA_DG2", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable 
                                                                              max_failure=0 max_connections=1 reopen=300 db_unique_name="ASSIA_DG2" 
                                                                              net_timeout=30, valid_for=(all_logfiles,primary_role)
      log_archive_dest_20                  string
      log_archive_dest_21                  string
      log_archive_dest_22                  string
      log_archive_dest_23                  string
      log_archive_dest_24                  string
      log_archive_dest_25                  string
      log_archive_dest_26                  string
      log_archive_dest_27                  string
      log_archive_dest_28                  string
      log_archive_dest_29                  string
      log_archive_dest_3                   string
      log_archive_dest_30                  string
      log_archive_dest_31                  string
      log_archive_dest_4                   string
      log_archive_dest_5                   string
      log_archive_dest_6                   string
      log_archive_dest_7                   string
      log_archive_dest_8                   string
      log_archive_dest_9                   string
      log_archive_dest_state_1             string      ENABLE
      log_archive_dest_state_10            string      enable
      log_archive_dest_state_11            string      enable
      log_archive_dest_state_12            string      enable
      log_archive_dest_state_13            string      enable
      log_archive_dest_state_14            string      enable
      log_archive_dest_state_15            string      enable
      log_archive_dest_state_16            string      enable
      log_archive_dest_state_17            string      enable
      log_archive_dest_state_18            string      enable
      log_archive_dest_state_19            string      enable
      log_archive_dest_state_2             string      DEFER   <=======
      log_archive_dest_state_20            string      enable
      log_archive_dest_state_21            string      enable
      log_archive_dest_state_22            string      enable
      log_archive_dest_state_23            string      enable
      log_archive_dest_state_24            string      enable
      log_archive_dest_state_25            string      enable
      log_archive_dest_state_26            string      enable
      log_archive_dest_state_27            string      enable
      log_archive_dest_state_28            string      enable
      log_archive_dest_state_29            string      enable
      log_archive_dest_state_3             string      enable
      log_archive_dest_state_30            string      enable
      log_archive_dest_state_31            string      enable
      log_archive_dest_state_4             string      enable
      log_archive_dest_state_5             string      enable
      log_archive_dest_state_6             string      enable
      log_archive_dest_state_7             string      enable
      log_archive_dest_state_8             string      enable
      log_archive_dest_state_9             string      enable
      I'm thinking I could just clear out the log_archive_dest_2 parameter entirely since they don't need it for now, but I'd also like to understand why it is still attemting to connect to the standby server even though dest_2 is set to DEFER.
        • 1. Re: how to stop dg log transfers
          mseberg
          Hello;

          Any chance you are using SQL to DEFER and the database is setup with Data Guard Broker?

          Test

          dgmgrl /

          DGMGRL> show configuration;

          If yes use

          DGMGRL> EDIT DATABASE '<your_db_name>' SET PROPERTY LogShipping=OFF;



          Best Regards

          mseberg

          Edited by: mseberg on Dec 18, 2012 8:29 AM
          • 2. Re: how to stop dg log transfers
            977635
            I don't think so, but here is what I see (on the primary database side).
            DGMGRL 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 sys@ASSIA
            Password:
            Connected.
            DGMGRL> show configuration
            
            Configuration - ASSIA_DG
            
              Protection Mode: MaxPerformance
              Databases:
                ASSIA_DG1 - Primary database
                  Error: ORA-16778: redo transport error for one or more databases
            
                ASSIA_DG2 - Physical standby database
                  Error: ORA-12541: TNS:no listener
            
            Fast-Start Failover: DISABLED
            
            Configuration Status:
            ERROR
            Also, I tried the command you gave anyway, but it does not find the database.
            DGMGRL> edit database ASSIA set PROPERTY LogShipping=OFF;
            Object "assia" was not found
            DGMGRL> edit database ASSIA_DG set PROPERTY LogShipping=OFF;
            Object "assia_dg" was not found
            DGMGRL> edit database ASSIA_DG1 set PROPERTY LogShipping=OFF;
            Object "assia_dg1" was not found
            Edited by: 974632 on Dec 18, 2012 6:34 AM
            • 3. Re: how to stop dg log transfers
              mseberg
              If, If you are using Broker the command would be :
              edit database 'ASSIA' set PROPERTY LogShipping=OFF;
              Without the quotes you will get the error you show.

              ORA-12541 - is one of the listeners down?

              For ORA-16778 - I would check the NetTimeout setting.

              OR

              OERR: ORA-16778 { Message text depends on version } [ID 173073.1]

              Best Regards

              mseberg

              Edited by: mseberg on Dec 18, 2012 9:52 AM
              • 4. Re: how to stop dg log transfers
                977635
                Hello mseberg.

                See below when I run the command as you provided:
                DGMGRL> connect sys@ASSIA
                Password:
                Connected.
                DGMGRL> edit database 'ASSIA' set PROPERTY LogShipping=OFF;
                Object "ASSIA" was not found
                Also, just to mention, I noticed in the database that dg_broker_start=TRUE.
                Not sure why they have this set to true if this is not intended to be used as a fast-start failover database.
                Anyway, the ASSIA_DG2 database no longer exist and has been completely removed from the standby server.

                As for the listeners, all listeners are running normally as far as i can tell.
                We have two listeners:
                 /opt/oracle/product/11.2.0.2/db_1/bin/tnslsnr ASSIA -inherit
                 /opt/oracle/product/11.2.0.2/db_1/bin/tnslsnr REMEDY -inherit
                If I look at the services for our ASSIA listener, this is what it shows...
                $ <ASSIA> /opt/oracle/product/11.2.0.2/db_1/network/admin>lsnrctl services ASSIA
                
                LSNRCTL for Solaris: Version 11.2.0.2.0 - Production on 18-DEC-2012 11:20:32
                
                Copyright (c) 1991, 2010, Oracle.  All rights reserved.
                
                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCassia)))
                Services Summary...
                Service "ASSIA" has 1 instance(s).
                  Instance "ASSIA", status UNKNOWN, has 1 handler(s) for this service...
                    Handler(s):
                      "DEDICATED" established:2855 refused:1
                         LOCAL SERVER
                Service "ASSIA_DG1" has 1 instance(s).
                  Instance "ASSIA", status UNKNOWN, has 1 handler(s) for this service...
                    Handler(s):
                      "DEDICATED" established:0 refused:0
                         LOCAL SERVER
                Service "ASSIA_DG1_DGB" has 1 instance(s).
                  Instance "ASSIA", status UNKNOWN, has 1 handler(s) for this service...
                    Handler(s):
                      "DEDICATED" established:0 refused:0
                         LOCAL SERVER
                Service "PLSExtProc" has 1 instance(s).
                  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                    Handler(s):
                      "DEDICATED" established:0 refused:0
                         LOCAL SERVER
                The command completed successfully
                • 5. Re: how to stop dg log transfers
                  mseberg
                  OK

                  So you are setup with Data Guard Broker.

                  You know your primary database is ASSIA_DG1.

                  You know your standby database is ASSIA_DG2.

                  So the answer to the first question appears to be :
                  edit database 'ASSIA_DG1' set PROPERTY LogShipping=OFF;
                  ASSIA appears to be an alias in the listener.


                  Why two two listeners? What does this give you?

                  Best Regards

                  mseberg
                  • 6. Re: how to stop dg log transfers
                    977635
                    Hi mesberg,

                    I could not agree with you more. Why two listeners? Why do people feel compelled to create multiple listeners, and then, even set them to use different ports.
                    I have no idea. I just got here. I only use one listerner, even on a multi-version platform where I have multiple versions of Oracle.
                    And, I have never found a good reason to use any other port than 1521 (unless we have extra hardware and a dedicated NIC).
                    Other than that, or for security isolation purposes, which is really over the top as far as i'm concerned, I've never understood why sites have multiple listeners and ports.
                    Oh well... don't get me started on that one... :-)

                    Now, for log shipping, I believe i showed you earlier that i did that command for ASSIA_DG1.
                    But, I did it again now, but this time, I connected to SYS@ASSIA_DG1 and perhaps this made a difference (instead of connecting as sys@ASSIA).
                    Either that, or this time, using your syntax, the ASSIA_DG1 is within single quotes.
                    Either way, thank you for your help to resolve this.

                    DGMGRL> edit database 'ASSIA_DG1' set PROPERTY LogShipping=OFF;
                    Property "logshipping" updated
                    • 7. Re: how to stop dg log transfers
                      mseberg
                      Hello again;

                      The single quotes in Data Guard Broker don't seem to work the same way from command to command.

                      You are correct - You did say you just got there - So forget my listener question.

                      Thanks for the follow up.

                      mseberg
                      • 8. Re: how to stop dg log transfers
                        977635
                        Well crud... After all that, it is still getting similar errors in the alert log.

                        As you can see below, this is still going on, several times per second.
                        ***********************************************************************
                        
                        Fatal NI connect error 12541, connecting to:
                         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=taurus)(PORT=1522))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ASSIA_DG2_DGB)(CID=(PROGRAM=oracle)(HOST=copernicu
                        s)(USER=oracle))))
                        
                          VERSION INFORMATION:
                                TNS for Solaris: Version 11.2.0.2.0 - Production
                                TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.2.0 - Production
                          Time: 18-DEC-2012 15:03:39
                          Tracing not turned on.
                          Tns error struct:
                            ns main err code: 12541
                        
                        TNS-12541: TNS:no listener
                            ns secondary err code: 12560
                            nt main err code: 511
                        
                        TNS-00511: No listener
                            nt secondary err code: 146
                            nt OS err code: 0
                        
                        
                        ***********************************************************************
                        • 9. Re: how to stop dg log transfers
                          mseberg
                          I would probably remove Data Broker using this note :

                          How to Safely Remove a Data Guard Broker Configuration [ID 261336.1]

                          That way we can narrow down any additional issues.

                          Did not check the note number for awhile. If you have problems finding let me know.

                          Does this make sense?

                          Best Regards

                          Broker will add parameters on its own for sure. The other option is chase settings in broker.

                          DGMGRL> SHOW DATABASE VERBOSE 'ASSIA_DG1';

                          mseberg

                          Edited by: mseberg on Dec 18, 2012 2:47 PM
                          • 10. Re: how to stop dg log transfers
                            974842
                            There is no need to remove ur log_dest_2 and as u have set up with defer optin still the standby will get connected.
                            Defer is mainly used in a situation to get resolution from ur n/w failure.

                            First properly check at ur standy whether Lsnrctl is configured and running or not.
                            second check the set up again whether it is configured properly or not.

                            If not share the alert log details of standby database and check whether heart ping failed error u will get