DG broker swithover报 :ORA-12514

郑全

在使用dgmgrl进行主备切换时,老是报 ora-12514错误:

DGMGRL>  SWITCHOVER TO sbdb1;
Performing switchover NOW, please wait...
New primary database "sbdb1" is opening...
Operation requires shutdown of instance "prod1" on database "prod1"
Shutting down instance "prod1"...
ORACLE instance shut down.
Operation requires startup of instance "prod1" on database "prod1"
Starting instance "prod1"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
        start up instance "prod1" of database "prod1"

切换回来,不会报错:

DGMGRL> SWITCHOVER TO prod1;
Performing switchover NOW, please wait...
New primary database "prod1" is opening...
Operation requires shutdown of instance "sbdb1" on database "sbdb1"
Shutting down instance "sbdb1"...
ORACLE instance shut down.
Operation requires startup of instance "sbdb1" on database "sbdb1"
Starting instance "sbdb1"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "prod1"

查看 prod1的配置:

DGMGRL> show database verbose prod1
Database - prod1
  Enterprise Manager Name: prod1.us.oracle.com
  Role:                    PHYSICAL STANDBY
  Intended State:          APPLY-ON
  Transport Lag:           0 seconds
  Apply Lag:               0 seconds
  Real Time Query:         ON
  Instance(s):
    prod1
  Properties:
    DGConnectIdentifier             = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=edbj2p1.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod1.us.oracle.com)(SERVER=DEDICATED)))'
    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           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'prod1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=edbj2p1.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod1_DGMGRL.us.oracle.com)(INSTANCE_NAME=prod1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'
Database Status:
SUCCESS

检查切换后的监听,没有这个服务的监听 :

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=edbj2p1.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                17-NOV-2016 14:44:06
Uptime                    0 days 5 hr. 39 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/edbj2p1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=edbj2p1.example.com)(PORT=1521)))
Services Summary...
Service "prod1.us.oracle.com" has 2 instance(s).
  Instance "prod1", status UNKNOWN, has 1 handler(s) for this service...
  Instance "prod1", status READY, has 1 handler(s) for this service...
Service "prod1XDB.us.oracle.com" has 1 instance(s).
  Instance "prod1", status READY, has 1 handler(s) for this service...
Service "prod1_DGB.us.oracle.com" has 1 instance(s).
  Instance "prod1", status READY, has 1 handler(s) for this service...
Service "prod2" has 1 instance(s).
  Instance "prod2", status READY, has 1 handler(s) for this service...
Service "prod2XDB" has 1 instance(s).
  Instance "prod2", status READY, has 1 handler(s) for this service...
Service "prod2_DGB" has 1 instance(s).
  Instance "prod2", status READY, has 1 handler(s) for this service...
The command completed successfully

修改监听配置文件,增加对dgmgrl服务的静态监听:
[oracle@edbj2p1 admin]$ vi listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = prod1_DGMGRL.us.oracle.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = prod1)
    )
  )

LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=edbj2p1.example.com)(PORT=1521)))
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=edbj2p1.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                17-NOV-2016 14:44:06
Uptime                    0 days 5 hr. 39 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/edbj2p1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=edbj2p1.example.com)(PORT=1521)))
Services Summary...
Service "prod1.us.oracle.com" has 1 instance(s).
  Instance "prod1", status READY, has 1 handler(s) for this service...
Service "prod1XDB.us.oracle.com" has 1 instance(s).
  Instance "prod1", status READY, has 1 handler(s) for this service...
Service "prod1_DGB.us.oracle.com" has 1 instance(s).
  Instance "prod1", status READY, has 1 handler(s) for this service...
Service "prod1_DGMGRL.us.oracle.com" has 1 instance(s).
  Instance "prod1", status UNKNOWN, has 1 handler(s) for this service...
Service "prod2" has 1 instance(s).
  Instance "prod2", status READY, has 1 handler(s) for this service...
Service "prod2XDB" has 1 instance(s).
  Instance "prod2", status READY, has 1 handler(s) for this service...
Service "prod2_DGB" has 1 instance(s).
  Instance "prod2", status READY, has 1 handler(s) for this service...
The command completed successfully

再去切换测试:

DGMGRL>  SWITCHOVER TO sbdb1;
Performing switchover NOW, please wait...
New primary database "sbdb1" is opening...
Operation requires shutdown of instance "prod1" on database "prod1"
Shutting down instance "prod1"...
ORACLE instance shut down.
Operation requires startup of instance "prod1" on database "prod1"
Starting instance "prod1"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "sbdb1"
DGMGRL>

这次成功了.