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

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

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

环境介绍：

数据库：11.2.0.4

系统：CentOS7.9

主库IP：192.168.133.150

备库IP：192.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

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配置完成.

2、switchover切换

检查主备库状态

主库

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

3、failover切换

模拟主库宕机

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 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/)