解决DGMGRL Unable to connect to database ORA-12541: TNS:no listener问题

晚上配了一套11.2.0.2 的DataGuard物理备库,准备用DGMGRL做切换测试,结果发现在切换过程中会遇到”Unable to connect to database”的问题,具体日志如下:

 

DGMGRL> switchover to dgogg
Performing switchover NOW, please wait...
New primary database "dgogg" is opening...
Operation requires shutdown of instance "SBDB" on database "sbdb"
Shutting down instance "SBDB"...
ORACLE instance shut down.
Operation requires startup of instance "SBDB" on database "sbdb"
Starting instance "SBDB"...
Unable to connect to database
ORA-12541: TNS:no listener

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
        start up instance "SBDB" of database "sbdb"

 

ORA-12541: TNS:no listener显然是因为DGMGRL使用的连接串ConnectIdentifier存在问题,但是在创建DGMGRL的Configuration之前我已经将SERIVCENAME_DGMGLR形式的静态信息添加到listener.ora文件中了,如:

 

DGLSN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vrh3.oracle.com)(PORT = 1588))
  )

SID_LIST_DGLSN =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DGOGG)
      (ORACLE_HOME = /s01/orabase/product/11.2.0/dbhome_1)
      (SID_NAME = DGOGG)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = DGOGG_DGB)
      (ORACLE_HOME = /s01/orabase/product/11.2.0/dbhome_1)
      (SID_NAME = DGOGG)
    )

    (SID_DESC =
      (GLOBAL_DBNAME = DGOGG_DGMGRL)
      (ORACLE_HOME = /s01/orabase/product/11.2.0/dbhome_1)
      (SID_NAME = DGOGG)
    )
  )

 

查了半天最后终于发现时因为监听端口的问题,我创建的LISTENER DGLSN使用1588端口,而dgmgrl中的静态连接串默认使用1521端口,如:

 

DGMGRL> show database dgogg

Database - dgogg

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    DGOGG

Database Status:
SUCCESS

DGMGRL> show database verbose dgogg

Database - dgogg

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    DGOGG

  Properties:
    DGConnectIdentifier             = 'dgogg_dgb'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'sync'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'DGOGG'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vrh3.oracle.com)
    (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DGOGG_DGMGRL)(INSTANCE_NAME=DGOGG)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/s01/orabase/arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

 

了解到问题所在后就容易解决了,只需要修改Broker中的StaticConnectIdentifier就可以了:

 

edit database dgogg set property StaticConnectIdentifier='(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vrh3.oracle.com)(PORT = 1588)) (CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = DGOGG_DGMGRL)))';

edit database sbdb set property StaticConnectIdentifier='(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = vrh4.oracle.com)(PORT = 1588))) (CONNECT_DATA =
(SERVICE_NAME = SBDB_DGMGRL)))';

 

再次尝试切换Switchover DataGuard,不再需要手动启动standby 实例了:

 

DGMGRL> switchover to sbdb;

Performing switchover NOW, please wait...
New primary database "sbdb" is opening...
Operation requires shutdown of instance "DGOGG" on database "dgogg"
Shutting down instance "DGOGG"...
ORACLE instance shut down.
Operation requires startup of instance "DGOGG" on database "dgogg"
Starting instance "DGOGG"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "sbdb"

DGMGRL> show configuration

Configuration - dgogg

  Protection Mode: MaxAvailability
  Databases:
    sbdb  - Primary database
    dgogg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Posted

in

by

Tags:

Comments

One response to “解决DGMGRL Unable to connect to database ORA-12541: TNS:no listener问题”

  1. maclean Avatar

    Bug 6630566 – DG broker reports ORA-12541 during switchover after changing local_listener [ID 6630566.8]

    Affects:

    Product (Component) Oracle Server (Rdbms)
    Range of versions believed to be affected Versions switchover to latest_p
    Performing switchover NOW, please wait…
    New primary database “latest_p” is opening…
    Operation requires shutdown of instance “latests” on database “latest_s”
    Shutting down instance “latests”…
    ORA-1109: database not open

    Database dismounted.
    ORACLE instance shut down.
    Operation requires startup of instance “latests” on database “latest_s”
    Starting instance “latests”…
    Unable to connect to database
    ORA-12541: TNS:no listener

    Failed.
    You are no longer connected to ORACLE
    Please connect again.
    Unable to start instance “latests”
    You must start instance “latests” manually
    Switchover succeeded, new primary is “latest_p”
    DGMGRL>

    WORKAROUND:
    ———–
    Remove and re-add the database to broker configuration whose LOCAL_LISTENER
    parameter value is altered at database level.

    RELATED BUGS:
    ————-

    REPRODUCIBILITY:
    —————-
    11.1.0.6

    TEST CASE:
    ———-
    Steps to reproduce this issue:

    1) Configure listener.ora and tnsnames.ora with some port# say 1592
    2) In primary, set LOCAL_LISTENER value correctly to that of listener port#
    say 1592
    3) In standby, set LOCAL_LISTENER value to some incorrect port# say 1590
    4) Now perform manual switchovers between primary and standby, we see that
    switchover works only in one direction – primary to standby works fine and
    standby to primary resulted in ORA-12541 (TNS:no listener), which is expected
    since standby wasn’t registering with the listener.
    5) Now correct the wrong port# (1590) specified in the standby LOCAL_LISTENER
    value using ALTER SYSTEM statement to the correct port# (1592).
    6) Once again perform manual switchovers and still we see ORA-12541 reported
    during switchover from standby to primary.

    RELEASE NOTES:
    ]]DGMGRL driven switchover does not restart the database if LOCAL_LISTENER is c
    ]]hanged after creating the configuration.
    REDISCOVERY INFORMATION:
    If restart fails after a broker role change.
    WORKAROUND:
    Set the hidden property StaticConnectIdentifier

Leave a Reply

Your email address will not be published. Required fields are marked *