1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Can't connect to physical standby db as sysdba using sqlplus

Discussion in 'General' started by 13478, Dec 12, 2018.

  1. 13478

    13478 Active Member

    Messages:
    43
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello,

    I just created a physical standby database, it can sync with primary db automatically without problem(I tested many times). so it is good,

    While, the problem I have is:

    from primary server, I can't connect to physical standby db as sysdba , for example

    --in primary server

    >sqlplus sys/xxx@cat_stby as sysdba

    SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 12 15:18:19 2018

    Copyright (c) 1982, 2016, Oracle. All rights reserved.


    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

    sqlplus> select db_unique_name, database_role from v$database;

    DB_UNIQUE_NAME DATABASE_ROLE
    ------------------------------ ----------------
    cat PRIMARY ===========================>not "cat_stby"

    but, tnsping works, and it points to standby db without problem


    >tnsping cat_stby

    TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 12-DEC-2018 15:30:43

    Copyright (c) 1997, 2016, Oracle. All rights reserved.

    Used parameter files:
    /opt/oracle/product/12.2.0.1/network/admin/sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standbyservername.domainnamexxx)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)

    (SERVICE_NAME = cat_stby.domainnamexxx)))
    OK (10 msec)

    Anyway, the sync is good, this is wired, any thought?

    (for standby server, I can't connect to primary db as sysdba either, same problem)

    it is 12.2 version in x86_64 linux

    thank you
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,682
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Check the listener.ora on the standby and verify that a static registration exists for the standby database. Since the standby is only mounted it can't auto-register with the listener and sqlplus connections will fail.

    This should be one of the steps in setting up a standby -- it's documented in many places on the internet.
     
  3. 13478

    13478 Active Member

    Messages:
    43
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    thanks, actually we already did
    and in fact, the dataguard broker has been added, it runs good.

    DGMGRL> show configuration

    Configuration - CAT_DR

    Protection Mode: MaxPerformance
    Members:
    cat - Primary database
    cat_stby - Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    SUCCESS (status updated 24 seconds ago)

    DGMGRL> exit

    --primary listener.ora

    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (GLOBAL_DBNAME=cat_DGMGRL.ufo.com)
    (SID_NAME=cat)
    (ORACLE_HOME=/opt/oracle/product/12.2.0.1)
    )
    (SID_DESC=
    (GLOBAL_DBNAME=cat_stby_DGMGRL.ufo.com)
    (SID_NAME=cat)
    (ORACLE_HOME=/opt/oracle/product/12.2.0.1)
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hh-ii-db4.ufo.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    )



    --standby listener.ora


    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (GLOBAL_DBNAME=cat_DGMGRL.ufo.com)
    (SID_NAME=cat)
    (ORACLE_HOME=/opt/oracle/product/12.2.0.1)
    )
    (SID_DESC=
    (GLOBAL_DBNAME=cat_stby_DGMGRL.ufo.com)
    (SID_NAME=cat)
    (ORACLE_HOME=/opt/oracle/product/12.2.0.1)
    )
    )

    LISTENER =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hh-ii-db5.ufo.com)(PORT = 1521))
    )


    while, I still can't login to standby db from primary server as sysdba
    same as standby server, I can't login to primary db from standby server as sysdba.

    anything I am missing
    ....???

    thank you
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,682
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are still missing a static entries for the primary and for the standby. These are in ADDITION to the entries for data guard. You also need these:

    (SID_DESC=
    (GLOBAL_DBNAME=cat.ufo.com)
    (SID_NAME=cat)
    (ORACLE_HOME=/opt/oracle/product/12.2.0.1)
    )
    (SID_DESC=
    (GLOBAL_DBNAME=cat_stby.ufo.com)
    (SID_NAME=cat)
    (ORACLE_HOME=/opt/oracle/product/12.2.0.1)
    )