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で使用されている接続列ConnectIdentifiierに問題があるためですが、DGMGRLのコンフィギュレーションを作成する前に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)
    )
  )

半日調べた結果、やっと発見されたのはリスニングポートの問題で、私が作成したLISTENR DGLSNは1588ポートを使用し、dgmgllの静的接続列は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 Data Guardを再度切り替えてみます.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