This discussion is archived
10 Replies Latest reply: Dec 18, 2012 11:42 PM by 974842 RSS

how to stop dg log transfers

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

Legend

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