dataguard broker主从切换

转载

mb5fe94c9f04536 2021-08-20 15:13:00

文章标签 oracle hive ide sql 配置文件 文章分类 MySQL 数据库

环境:

OS:Centos 7

DB:11.2.0.4

1.搭建dataguard环境

步骤省略

2.配置相应参数

主库

alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0.4/db_1/dbs/dr1slnngk.dat';

alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0.4/db_1/dbs/dr2slnngk.dat';

备库

alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0.4/db_1/dbs/dr1slavea.dat';

alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0.4/db_1/dbs/dr2slavea.dat';

文件名称无关紧要,可以随便取,一般放dbs目录下,若是asm的需要放共享磁盘上.

启用BROKER(主备库均执行)

alter system set dg_broker_start=true scope=both;

3.主从看需要配置好tns

[oracle@dbslave01 admin]$ more tnsnames.ora
tnsslnngk =
    (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.180)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = slnngk)
    )
  )

tnsslavea =
    (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.181)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = slavea)
    )
  )

4.创建并启用配置文件(主库上执行)

主库上执行即可

[oracle@dg admin]$ dgmgrl sys/oracle@tnsslnngk; ##这里的tnsslnngk是连接主库的tns

添加主库

DGMGRL> create configuration slnngktest as primary database is 'slnngk' connect identifier is 'tnsslnngk';

Configuration "slnngktest" created with primary database "slnngk"

slnngktest:配置别名,可以随意取

slnngk:主库的db_unique_name

tnsslnngk:连接主库的tns

#启用配置文件

DGMGRL> enable configuration;

Enabled.

这个时候可以看到主库的日志输出:

RSM0 started with pid=32, OS id=4157 
ALTER SYSTEM SET log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='slnngk';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='slnngk';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_file_name_convert='slavea','slnngk' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='slavea','slnngk' SCOPE=SPFILE;

#添加备库(也是在主库上执行)

DGMGRL> add database 'slavea' as connect identifier is 'tnsslavea';

DGMGRL> enable database 'slavea';

slavea:备库的db_unique_name

tnsslaveb:连接备库的tns

这个时候可以看到备库的输出日志:

ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='slavea';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='slavea';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_file_name_convert='slnngk','slavea' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='slnngk','slavea' SCOPE=SPFILE;
ALTER SYSTEM SET fal_server='tnsslnngk' SCOPE=BOTH;

5.查看配置信息

DGMGRL> show configuration

Configuration - slnngktest

Protection Mode: MaxPerformance

Databases:

slnngk - Primary database

slavea - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

6.查看主备库配置详细信息

主库

DGMGRL> show database verbose slnngk;

Database - slnngk

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

  Properties:
    DGConnectIdentifier             = 'tnsslnngk'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    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               = 'slavea, slnngk'
    LogFileNameConvert              = 'slavea, slnngk'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'slnngk'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbmaster)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slnngk_DGMGRL)(INSTANCE_NAME=slnngk)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

备库

DGMGRL> show database verbose slavea


Database - slavea

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    slavea

  Properties:
    DGConnectIdentifier             = 'tnsslavea'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    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               = 'slnngk, slavea'
    LogFileNameConvert              = 'slnngk, slavea'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'slavea'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbslave01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slavea_DGMGRL)(INSTANCE_NAME=slavea)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/archive_log/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

--这里需要注意的一点是:

SERVICE_NAME=slnngk_DGMGRL 这个service_name参数，默认格式为db_unique_name_DGMGRL,如果这里采用默认格式，那么主备库的监听，就要配置静态监听.

下面进行添加主备静态监听,同时需要在两台机器上配置hosts文件,因为主备的配置HOST=dbmaster,HOST=dbslave01 需要进行解析

将如下两项加入到/etc/hosts文件中

192.168.56.180 dbmaster

192.168.56.181 dbslave01

若是不想修改hosts文件的话,可以修改配置,写死ip,如下

DGMGRL> edit database slnngk set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.180)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slnngk_DGMGRL)(INSTANCE_NAME=slnngk)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
DGMGRL> edit database slavea set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.181)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slavea_DGMGRL)(INSTANCE_NAME=slavea)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated

7.主库添加静态监听

vi /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = slnngk_DGMGRL)
      (ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/db_1)
      (SID_NAME =slnngk)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = slnngk)
      (ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/db_1)
      (SID_NAME =slnngk)
    )
  )

然后重启动监听器

[oracle@dbmaster admin]$ lsnrctl stop

[oracle@dbmaster admin]$ lsnrctl start

[oracle@dbmaster admin]$ lsnrctl status

8.备库添加静态监听器

vi /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = slavea)
      (ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/db_1)
      (SID_NAME =slavea)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = slavea_DGMGRL)
      (ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/db_1)
      (SID_NAME =slavea)
    )
  )

[oracle@dbslave01 admin]$ lsnrctl stop

[oracle@dbslave01 admin]$ lsnrctl start

[oracle@dbslave01 admin]$ lsnrctl status

如不想配置静态监听的话,可以手工修改配置

DGMGRL> edit database slnngk set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.180)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slnngk)(INSTANCE_NAME=slnngk)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
DGMGRL> edit database slavea set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.181)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slavea)(INSTANCE_NAME=slavea)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated

7.switchover切换测试(切换操作在主从可以上都可以的)

[oracle@dbmaster ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle

Connected.

在当前的主库上执行,将主库角色切换成从库,to后面是新主库

DGMGRL> switchover to slavea

Performing switchover NOW, please wait...

Operation requires a connection to instance "slavea" on database "slavea"

Connecting to instance "slavea"...

Connected.

New primary database "slavea" is opening...

Operation requires startup of instance "slnngk" on database "slnngk"

Starting instance "slnngk"...

ORACLE instance started.

Database mounted.

Database opened.

Switchover succeeded, new primary is "slavea"

可以再次切换回来

DGMGRL> switchover to slnngk

Performing switchover NOW, please wait...

Operation requires a connection to instance "slnngk" on database "slnngk"

Connecting to instance "slnngk"...

Connected.

New primary database "slnngk" is opening...

Operation requires startup of instance "slavea" on database "slavea"

Starting instance "slavea"...

Unable to connect to database

ORA-12545: Connect failed because target host or object does not exist

Failed.

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:

start up instance "slavea" of database "slavea"

这里需要手工启动备库

SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 3140026368 bytes

Fixed Size 2257352 bytes

Variable Size 704646712 bytes

Database Buffers 2415919104 bytes

Redo Buffers 17203200 bytes

Database mounted.

Database opened.

SQL> select process,status from v$managed_standby;

PROCESS STATUS

--------- ------------

ARCH CONNECTED

ARCH CLOSING

RFS IDLE

MRP0 APPLYING_LOG

9 rows selected.

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：RESOLVABLE GAP

下一篇：现有dgbroker管理的dg下添加一台从库且互相切换

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

dataguard broker主从切换

dataguard broker主从切换

51CTO博客