DG Broker switchover的过程

最新推荐文章于 2025-06-07 10:28:55 发布

张玉龙

最新推荐文章于 2025-06-07 10:28:55 发布

阅读量1.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle12c

本文链接：https://blog.youkuaiyun.com/u010692693/article/details/75733282

oracle12c 专栏收录该内容

31 篇文章

订阅专栏

本文介绍使用 Oracle 12c 的 DGMGRL 工具进行 Dataguard 角色切换的方法及遇到 ORA-12514 错误时的三种解决方案，包括修改连接标识符、调整 listener.ora 文件等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

12c Dataguard Switchover Best Practices using DGMGRL(Dataguard Broker Command Prompt) (文档 ID 1582837.1)
DGMGRL>switchover to <standby> Fails with ORA-12514 (文档 ID 1582927.1)

1. switchover 主—>备

使用dgmgrl进行切换时，连接dgmgrl时使用TNS的方式连接，不要使用<dgmgrl />的方式连接，因为<dgmgrl />不能连接到备库执行相应操作。

[oracle@12cr2 ~]$ dgmgrl sys/oracle@newcdb
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Mon Jul 3 20:59:39 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "newcdb"
Connected as SYSDBA.
DGMGRL> SHOW CONFIGURATION;

Configuration - dg_newcdb

  Protection Mode: MaxPerformance
  Members:
  newcdb   - Primary database
    newcdbdg - Physical standby database 
    frasync  - Far sync instance 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 56 seconds ago)

DGMGRL> switchover to newcdbdg
Performing switchover NOW, please wait...
Operation requires a connection to database "newcdbdg"
Connecting ...
Connected to "newcdbdg"
Connected as SYSDBA.
New primary database "newcdbdg" is opening...
Operation requires start up of instance "newcdb" on database "newcdb"
Starting instance "newcdb"...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.16.81)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=newcdb_DGMGRL)(INSTANCE_NAME=newcdb)(SERVER=DEDICATED)))
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 "newcdb" of database "newcdb"

切换的过程出现警告，以下是解决方法：
第一种解决方法：

手动启动192.168.16.81节点上的数据库

DGMGRL> SHOW CONFIGURATION;

Configuration - dg_newcdb

  Protection Mode: MaxPerformance
  Members:
  newcdbdg - Primary database
    Error: ORA-16778: redo transport error for one or more members

    newcdb   - Physical standby database 
      Error: ORA-1034: ORACLE not available

    frasync  - Far sync instance 

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 30 seconds ago)

[oracle@12cr2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 21:08:51 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  8798312 bytes
Variable Size             322965400 bytes
Database Buffers          503316480 bytes
Redo Buffers                3780608 bytes
Database mounted.
Database opened.

DGMGRL> SHOW CONFIGURATION;

Configuration - dg_newcdb

  Protection Mode: MaxPerformance
  Members:
  newcdbdg - Primary database
    newcdb   - Physical standby database 
    frasync  - Far sync instance 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 50 seconds ago)

DGMGRL>switchover to <standby> Fails with ORA-12514 (文档 ID 1582927.1)
SOLUTION 1 : Modify the staticconnectidentifier to available listener service
----------------

Primary db_unique_name is boston
Standby db_unique_name is chicago
Check the listener services.

On both primary and standby,
$ lsnrctl services
Now edit the staticconnectidentifier to any available services of respective database.
1. Connect to DGMGRL,

DGMGRL> edit database chicago set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=nirmala-desk1.idc.oracle.com))(CONNECT_DATA=(SERVICE_NAME=chicago)(INSTANCE_NAME=chicago)(SERVER=DEDICATED)))';
NOTE : edit boston value for any future role transition,
DGMGRL> edit database boston set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=nirmala-desk1.idc.oracle.com))(CONNECT_DATA=(SERVICE_NAME=boston)(INSTANCE_NAME=boston)(SERVER=DEDICATED)))';

2. DGMGRL>show configuration;

For switchover reference,

<<1582837.1>> - 12c Dataguard Switchover Best Practices using DGMGRL(Dataguard Broker Command Prompt)

第二种解决方法：
修改dgmgrl的staticconnectidentifier参数

DGMGRL> SHOW DATABASE VERBOSE newcdb
StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.16.81)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=newcdb_DGMGRL)(INSTANCE_NAME=newcdb)(SERVER=DEDICATED)))'

DGMGRL> edit database newcdb set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.16.81)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=newcdb)(INSTANCE_NAME=newcdb)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated

DGMGRL> edit database newcdbdg set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.16.130)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=newcdbdg)(INSTANCE_NAME=newcdb)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated

DGMGRL> switchover to newcdbdg
Performing switchover NOW, please wait...
Operation requires a connection to database "newcdbdg"
Connecting ...
Connected to "newcdbdg"
Connected as SYSDBA.
New primary database "newcdbdg" is opening...
Operation requires start up of instance "newcdb" on database "newcdb"
Starting instance "newcdb"...
ORACLE instance started.
Database mounted.
Database opened.
Connected to "newcdb"
Switchover succeeded, new primary is "newcdbdg"

SOLUTION 2 : Add static listener entry with the name mentioned in staticconnectidentifier i.e default service taken by DGMGRL which add configuration with the service_name of <SID>_DGMGRL
------------

Modify static entry on listener.ora of current Standby.

1. Standby (chicago)server,

vi /$ORACLE_HOME/network/admin/listener.ora
(SID_DESC =
(GLOBAL_DBNAME = chicago_DGMGRL) <----------------add _DGMGRL
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = chicago)
)

2. Same way change the static entry of current primary also for feature role transition.

第三种解决方法：
修改主库和备库的listener.ora文件，添加<DB_UNIQUE_NAME>_dgmgrl

修改主库的listener.ora文件，添加newcdb_dgmgrl一段，GLOBAL_DBNAME的格式是<DB_UNIQUE_NAME>_dgmgrl

[oracle@12cr2 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = newcdb)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
      (SID_NAME = newcdb)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = newcdb_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
      (SID_NAME = newcdb)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.81)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

修改备库的listener.ora文件，添加newcdbdg_dgmgrl一段, GLOBAL_DBNAME的格式是<DB_UNIQUE_NAME>_dgmgrl

[oracle@oracle admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = newcdb)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
      (SID_NAME = newcdb)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = newcdbdg_dgmgrl)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
      (SID_NAME = newcdb)
    )
   )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.130)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

主端和备端重启监听服务

[oracle@12cr2 ~]$ dgmgrl sys/oracle@newcdb
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue Jul 4 22:24:36 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "newcdb"
Connected as SYSDBA.
DGMGRL> switchover to newcdbdg
Performing switchover NOW, please wait...
Operation requires a connection to database "newcdbdg"
Connecting ...
Connected to "newcdbdg"
Connected as SYSDBA.
New primary database "newcdbdg" is opening...
Operation requires start up of instance "newcdb" on database "newcdb"
Starting instance "newcdb"...
ORACLE instance started.
Database mounted.
Database opened.
Connected to "newcdb"
Switchover succeeded, new primary is "newcdbdg"

DGMGRL> SHOW CONFIGURATION

Configuration - dg_newcdb

  Protection Mode: MaxPerformance
  Members:
  newcdbdg - Primary database
    newcdb   - Physical standby database 
    frasync  - Far sync instance 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 9 seconds ago)

2 switchover 备—>主

[oracle@12cr2 ~]$ dgmgrl sys/oracle@newcdb
DGMGRL> switchover to newcdb
Performing switchover NOW, please wait...
Operation requires a connection to database "newcdb"
Connecting ...
Connected to "newcdb"
Connected as SYSDBA.
New primary database "newcdb" is opening...
Operation requires start up of instance "newcdb" on database "newcdbdg"
Starting instance "newcdb"...
ORACLE instance started.
Database mounted.
Database opened.
Connected to "newcdbdg"
Switchover succeeded, new primary is "newcdb"

DGMGRL> SHOW CONFIGURATION

Configuration - dg_newcdb

  Protection Mode: MaxPerformance
  Members:
  newcdb   - Primary database
    newcdbdg - Physical standby database 
    frasync  - Far sync instance 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 0 seconds ago)