重庆思庄Oracle、Redhat认证学习论坛

标题: dg broker配置及主备切换实验 [打印本页]

作者: denglj    时间: 2022-5-30 17:35
标题: dg broker配置及主备切换实验
本帖最后由 denglj 于 2022-5-30 17:37 编辑

环境介绍:
数据库:11.2.0.4
系统:CentOS7.9
主库IP192.168.133.150
备库IP192.168.133.151

1、配置Data GuardBroker1.1、打开主备库闪回功能
主库执行:
alterdatabase flashback on;
备库执行:
alterdatabase recover managed standby database cancel;
alterdatabase flashback on;

1.2、主备库端设置dg_broker_start
altersystem set dg_broker_start=true;

1.3、配置监听
主库:
SID_LIST_LISTENER=
   (SID_LIST =
      (SID_DESC =
         (GLOBAL_DBNAME = orcl150_DGMGRL)
         (ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
         (SID_NAME = orcl150)
      )
   )
备库:
      (SID_DESC =
         (GLOBAL_DBNAME = orcl151_DGMGRL)
         (ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
         (SID_NAME = orcl151)
      )
[oracle@oeladmin]$ lsnrctl reload
[oracle@oeldgadmin]$ lsnrctl reload

1.4、创建配置文件
使用DGMGRL命令工具创建配置文件
主库执行
[oracle@oeladmin]$ dgmgrl
DGMGRLfor Linux: Version 11.2.0.4.0 - 64bit Production
Copyright(c) 2000, 2009, Oracle. All rights reserved.
Welcometo DGMGRL, type "help" for information.
DGMGRL>connect sys/oracle_4U
Connected.
DGMGRL>create configuration 'dg_conf' as primary database is 'orcl150' connectidentifier is orcl150;
Configuration"dg_conf" created with primary database "orcl150"
查看配置信息
DGMGRL>show configuration;
Configuration- dg_conf
  Protection Mode: MaxPerformance
  Databases:
    orcl150 - Primary database
Fast-StartFailover: DISABLED
ConfigurationStatus:
DISABLED
DGMGRL>show database orcl150;
Database- orcl150
  Role:            PRIMARY
  Intended State:  OFFLINE
  Instance(s):
    orcl150
DatabaseStatus:
DISABLED
添加备库
DGMGRL>add database 'orcl151' as connect identifier is orcl151 maintained as physical;
Database"orcl151" added
DGMGRL>show configuration
Configuration- dg_conf
  Protection Mode: MaxPerformance
  Databases:
    orcl150 - Primary database
    orcl151 - Physical standby database
Fast-StartFailover: DISABLED
ConfigurationStatus:
DISABLED

1.5、激活配置文件
DGMGRL>enable configuration;
Enabled.
DGMGRL>show configuration;
Configuration- dg_conf
  Protection Mode: MaxPerformance
  Databases:
    orcl150 - Primary database
    orcl151 - Physical standby database
Fast-StartFailover: DISABLED
ConfigurationStatus:
SUCCESS
dg_broker配置完成.

2switchover切换
检查主备库状态
主库
SQL>select name,open_mode,database_role,switchover_status from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
----------------------------- ---------------- --------------------
ORCL150   READ WRITE           PRIMARY          SESSIONS ACTIVE
备库:
SQL>select name,open_mode,database_role,switchover_status from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
----------------------------- ---------------- --------------------
ORCL150   READ ONLY WITH APPLY PHYSICAL STANDBY NOTALLOWED

开始切换
主库执行:
DGMGRL>switchover to orcl151;
Performingswitchover NOW, please wait...
Operationrequires a connection to instance "orcl151" on database"orcl151"
Connectingto instance "orcl151"...
Connected.
Newprimary database "orcl151" is opening...
Operationrequires startup of instance "orcl150" on database"orcl150"
Startinginstance "orcl150"...
ORACLEinstance started.
Databasemounted.
Databaseopened.
Switchoversucceeded, new primary is "orcl151"
DGMGRL>show configuration
Configuration- dg_conf
  Protection Mode: MaxPerformance
  Databases:
    orcl151 - Primary database
    orcl150 - Physical standby database
Fast-StartFailover: DISABLED
ConfigurationStatus:
SUCCESS

查看主备库状态
oldprimary
SQL> selectname,open_mode,database_role,switchover_status from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
----------------------------- ---------------- --------------------
ORCL150   READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
new primary
SQL> selectname,open_mode,database_role,switchover_status from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
----------------------------- ---------------- --------------------
ORCL150   READ WRITE           PRIMARY         SESSIONS ACTIVE

3failover切换
模拟主库宕机
SQL>shutdown abort
ORACLEinstance shut down.
确认备库开启闪回
SQL>select * from v$archive_gap;
no rowsselected
SQL>select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
将备库切换为主库
DGMGRL>failover to orcl150;
Performingfailover NOW, please wait...
Failoversucceeded, new primary is "orcl150"
failover后,当前主库为orcl150,现在尝试恢复原来的故障主库orcl151为新备库

查看配置
DGMGRL>show configuration
Configuration- dg_conf
  Protection Mode: MaxPerformance
  Databases:
    orcl150 - Primary database
    orcl151 - Physical standby database(disabled)
      ORA-16661: the standby database needs tobe reinstated
Fast-StartFailover: DISABLED
ConfigurationStatus:
SUCCESS
启动原来的主库,也就是故障主库orcl151
SQL>startup
ORACLEinstance started.
TotalSystem Global Area 2455228416 bytes
FixedSize                  2255712 bytes
VariableSize             620758176 bytes
DatabaseBuffers         1811939328 bytes
RedoBuffers               20275200 bytes
Databasemounted.
ORA-16649:possible failover to another database prevents this database from
beingopened

查看是否开启闪回
SQL>select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

直接执行命令:(orcl150节点执行)
DGMGRL>reinstate database orcl151;
Reinstatingdatabase "orcl151", please wait...
Operationrequires shutdown of instance "orcl151" on database"orcl151"
Shuttingdown instance "orcl151"...
Unable toconnect to database
ORA-12545:Connect failed because target host or object does not exist
Failed.
Warning:You are no longer connected to ORACLE.
Pleasecomplete the following steps and reissue the REINSTATE command:
        shut down instance "orcl151"of database "orcl151"
        start up and mount instance"orcl151" of database "orcl151"

orcl151执行:
SQL>shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down.
SQL>startup mount
ORACLEinstance started.
TotalSystem Global Area 2455228416 bytes
FixedSize                  2255712 bytes
VariableSize             620758176 bytes
DatabaseBuffers         1811939328 bytes
RedoBuffers               20275200 bytes
Databasemounted.
orcl150执行
DGMGRL>reinstate database orcl151;
Reinstatingdatabase "orcl151", please wait...
Reinstatementof database "orcl151" succeeded

显示配置正常
DGMGRL>show configuration;
Configuration- dg_conf
  Protection Mode: MaxPerformance
  Databases:
    orcl150 - Primary database
    orcl151 - Physical standby database
Fast-StartFailover: DISABLED
ConfigurationStatus:
SUCCESS
查看备库(orcl151)状态,显示正常
SQL> selectname,open_mode,database_role,switchover_status from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
----------------------------- ---------------- --------------------
ORCL150   READ ONLY WITH APPLY PHYSICAL STANDBYSWITCHOVER PENDING

4、切换物理备库为快照备库
查看当前物理备库
DGMGRL>show configuration
Configuration- dg_conf
  Protection Mode: MaxPerformance
  Databases:
    orcl150 - Primary database
    orcl151 - Physical standby database
Fast-StartFailover: DISABLED
ConfigurationStatus:
SUCCESS
切换为快照备库
DGMGRL>convert database orcl151 to snapshot standby;
Convertingdatabase "orcl151" to a Snapshot Standby database, please wait...
Database"orcl151" converted successfully
DGMGRL>show configuration;
Configuration- dg_conf
  Protection Mode: MaxPerformance
  Databases:
    orcl150 - Primary database
    orcl151 - Snapshot standby database
Fast-StartFailover: DISABLED
ConfigurationStatus:
SUCCESS
切换到快照备库后,备库创建了还原点(闪回到还原点是不需要打开闪回功能的,这也是还能回到物理备库的基础)
SQL>select name,database_role from v$database;
NAME      DATABASE_ROLE
-------------------------
ORCL150   SNAPSHOT STANDBY
SQL> r
  1* select name,storage_size fromv$restore_point
NAME                                              STORAGE_SIZE
--------------------------------------------------------------
SNAPSHOT_STANDBY_REQUIRED_05/25/202214:49:51          52428800

测试
主库修改一条数据并手工切换一次日志
SQL>select * from emp;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO
-------------------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH     CLERK           7902 17-DEC-80       1000                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO
-------------------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
14 rows selected.
SQL>update emp set sal=2000 where empno=7369;
1 rowupdated.
SQL>commit;
Commitcomplete.
SQL>alter system switch logfile;
Systemaltered.
SQL> select *from scott.emp;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO
-------------------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH     CLERK           790217-DEC-80       2000                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                   10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500         0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO
-------------------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
14 rows selected.

查看备库数据
SQL> r
  1* select name,open_mode,database_role fromv$database
NAME                 OPEN_MODE            DATABASE_ROLE
---------------------------------------- ----------------
ORCL150              READ WRITE           SNAPSHOT STANDBY
SQL> r
  1* select * from scott.emp
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO
-------------------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH     CLERK           790217-DEC-80       1000                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK     MANAGER         783909-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO
-------------------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
14 rowsselected.

此处数据没同步,日志其实是继续传到备库的,只是redolog未应用.
SQL>select process,client_process,status from v$managed_standby;
PROCESS   CLIENT_P STATUS
----------------- ------------
ARCH      ARCH    CLOSING
ARCH      ARCH    CONNECTED
ARCH      ARCH    CONNECTED
ARCH      ARCH    CLOSING
RFS       ARCH    IDLE
RFS       UNKNOWN IDLE
RFS       LGWR    IDLE
7 rowsselected.

5、快照备库切换回物理备库
执行装换命令
DGMGRL>convert database orcl151 to physical standby;
Convertingdatabase "orcl151" to a Physical Standby database, please wait...
Operationrequires shutdown of instance "orcl151" on database"orcl151"
Shuttingdown instance "orcl151"...
Unable toconnect to database
ORA-12545:Connect failed because target host or object does not exist
Failed.
Warning:You are no longer connected to ORACLE.
Pleasecomplete the following steps and reissue the CONVERT command:
        shut down instance "orcl151"of database "orcl151"
        start up and mount instance"orcl151" of database "orcl151"
备库(orcl151)执行
SQL>shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down.
SQL>startup mount
ORACLEinstance started.
TotalSystem Global Area 2455228416 bytes
FixedSize                  2255712 bytes
VariableSize             620758176 bytes
DatabaseBuffers         1811939328 bytes
RedoBuffers               20275200 bytes
Databasemounted.
主库(orcl150)执行
DGMGRL>convert database orcl151 to physical standby;
Convertingdatabase "orcl151" to a Physical Standby database, please wait...
Operationrequires shutdown of instance "orcl151" on database"orcl151"
Shuttingdown instance "orcl151"...
Unable toconnect to database
ORA-12545:Connect failed because target host or object does not exist
Failed.
Warning:You are no longer connected to ORACLE.
Pleasecomplete the following steps to finish the convert command:
        shut down instance "orcl151"of database "orcl151"
        start up and mount instance"orcl151" of database "orcl151"
DGMGRL>show configuration;
Configuration- dg_conf
  Protection Mode: MaxPerformance
  Databases:
    orcl150 - Primary database
    orcl151 - Physical standby database
      Error: ORA-16766: Redo Apply is stopped
Fast-StartFailover: DISABLED
ConfigurationStatus:
ERROR
备库开始实时应用后,正常显示.
DGMGRL>show configuration;
Configuration- dg_conf
  Protection Mode: MaxPerformance
  Databases:
    orcl150 - Primary database
    orcl151 - Physical standby database
Fast-StartFailover: DISABLED
ConfigurationStatus:
SUCCESS
查看备库角色
SQL>select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
----------------------------- ----------------
ORCL150   READ ONLY WITH APPLY PHYSICAL STANDBY
查询还原点
SQL>select name,storage_size from v$restore_point;
no rowsselected
说明:当快照备库转换为物理备库后,快照还原点将丢失,且快照备库中操作的数据也不会被保存.






欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) Powered by Discuz! X3.2