DG Broker switchover的过程_ora-16778

作者：凡人多烦事01 | 2024-04-13 09:55:06

踩

ora-16778

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)

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/凡人多烦事01/article/detail/415743?site