本帖最后由 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_startaltersystem 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配置完成.
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 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认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2022-5-30 17:35:04
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对