DG在最大性能模式MAXIMUM PERFORMANCE
- SQL> select protection_mode,protection_level from v$database;
- PROTECTION_MODE PROTECTION_LEVEL
- -------------------- --------------------
- MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------ -----------------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- SALGRADE TABLE
- SQL> create table emp001 as select *from emp; 创建了一张表emp001
- Table created.
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------ -----------------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- EMP001 TABLE
- SALGRADE TABLE
去备机上查看,这张表并没有出现:
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------ -----------------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- SALGRADE TABLE
- 这个时候,我需要在主库上执行一次日志切换:
- SQL> alter system switch logfile;
- System altered.
- 再备库上再次查询,表已经出来:
- SQL> /
- TNAME TABTYPE CLUSTERID
- ------------------------------ -----------------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- EMP001 TABLE ----在主库新创建的表
- SALGRADE TABLE
DG在最大可用性模式Maximum availability
Primary端操作
- 修改初始化参数
- SQL> alter system set log_archive_dest_2='SERVICE=sty OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=sty';
- System altered.
- SQL> show parameter log_archive_dest_2
- NAME TYPE VALUE
- ----------------------------------------------- ------------------------------
- log_archive_dest_2 string SERVICE=sty OPTIONAL LGWR SYNC
- AFFIRM VALID_FOR=(ONLINE_LOGF
- ILES,PRIMARY_ROLE) DB_UNIQUE_N
- AME=sty
- SQL> startup mount;
- ORACLE instance started.
- Total System Global Area 839282688 bytes
- Fixed Size 2217992 bytes
- Variable Size 549455864 bytes
- Database Buffers 285212672 bytes
- Redo Buffers 2396160 bytes
- Database mounted.
- SQL> alter database set standby databaseto maximize availability;
- Database altered.
Down掉数据库,重新启动
- SQL> alter database open;
- Database altered.
- 看一下当前的保护模式 --primary数据库操作
- SQL> select protection_mode,protection_level from v$database;
- PROTECTION_MODE PROTECTION_LEVEL
- -------------------- --------------------
- MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
- SQL>
Standby端操作
- SQL> alter system set log_archive_dest_2='SERVICE=pri OPTIONAL LGWR SYNCAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri';
- System altered.
- SQL> show parameter log_archive_dest_2
- NAME TYPE VALUE
- ----------------------------------------------- ------------------------------
- log_archive_dest_2 string SERVICE=pri OPTIONAL LGWR SYNC
- AFFIRM VALID_FOR=(ONLINE_LOGF
- ILES,PRIMARY_ROLE)DB_UNIQUE_N
- AME=pri
- SQL> selectprotection_mode,protection_level from v$database;
- PROTECTION_MODE PROTECTION_LEVEL
- -------------------- --------------------
- MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
- SQL>
这样就已经配置完成了。
我们现在停掉standby数据库,再看看primary数据库状态
- SQL> shutdown immediate
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> selectprotection_mode,protection_level,name from v$database;
- PROTECTION_MODE PROTECTION_LEVEL NAME
- -------------------- ------------------------------
- MAXIMUM AVAILABILITY RESYNCHRONIZATION WOO
启备机器后,一会儿就好了:
- SQL> startup
- ORACLE instance started.
- Total System Global Area 839282688 bytes
- Fixed Size 2217992 bytes
- Variable Size 507512824 bytes
- Database Buffers 327155712 bytes
- Redo Buffers 2396160 bytes
- Database mounted.
- Database opened.
- SQL> selectprotection_mode,protection_level,name from v$database;
- PROTECTION_MODE PROTECTION_LEVEL NAME
- -------------------- ----------------------------------------------------------------------
- MAXIMUM AVAILABILITY RESYNCHRONIZATION WOO
- SQL> selectprotection_mode,protection_level,name from v$database;
- PROTECTION_MODE PROTECTION_LEVEL NAME
- -------------------- ----------------------------------------------------------------------
- MAXIMUM AVAILABILITY MAXIMUM AVAILABILITYWOO
- SQL>
- SQL> selectprotection_mode,protection_level,name from v$database;
- PROTECTION_MODE PROTECTION_LEVEL NAME
- -------------------- ------------------------------
- MAXIMUM AVAILABILITY MAXIMUM AVAILABILITYWOO
测试:
在primary 执行创建表
查看当前表的数量
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------ -----------------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- EMP001 TABLE
- EMP003 TABLE
- SALGRADE TABLE
- 6 rows selected.
删除emp003表
- SQL> drop table emp003 purge;
- Table dropped.
创建emp004表
- SQL> create table emp004 as select *from emp001;
- Table created.
查看emp003表已经被删除,emp004表被创建成功。
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------ -----------------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- EMP001 TABLE
- EMP004 TABLE
- SALGRADE TABLE
- 6 rows selected.
查询到数据和第一次pry数据库一致:
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------ -----------------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- EMP001 TABLE
- EMP003 TABLE
- SALGRADE TABLE
- 6 rows selected.
在pri端删除emp003后,可以看到在sty端也随即删除。
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------ -----------------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- EMP001 TABLE
- SALGRADE TABLE
在pri端创建emp004后,在sty端也随即实时创建。
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------ ------- ----------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- EMP001 TABLE
- EMP004 TABLE
- SALGRADE TABLE
- 6 rows selected.
- SQL>
实验二,在maximum availability 模式下关闭standby端,在主库执行DML操作后起备库,是否会有数据丢失:
1、 关闭standby端:
- [root@sty ~]# shutdown -h now
- Broadcast message from root (pts/1) (TueMay 21 02:09:34 2013):
- The system is going down for system haltNOW!
- [root@sty ~]#
2、 查看主库这个时候的状态,已经检测到了standby失败
- SQL> selectname,open_mode,database_role,protection_mode,SWITCHOVER_STATUS From v$database;
- NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
- -------------------------------------------------- ---------------- -------------------- --------------------
- WOO READ WRITE PRIMARY MAXIMUM AVAILABILITY FAILEDDESTINATION
- SQL>
3、 这个时候我们删除emp004及创建emp005
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------ -----------------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- EMP001 TABLE
- EMP004 TABLE
- SALGRADE TABLE
- 6 rows selected.
- SQL> drop table emp004 purge;
- Table dropped.
- SQL> create table emp005 as select *from emp001;
- Table created.
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------ -----------------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- EMP001 TABLE
- EMP005 TABLE
- SALGRADE TABLE
- 6 rows selected.
4、 把sty服务器启起来,open数据库:
打开备库后,我们可以看到,standby现在正在应用日志:
- SQL> select process, status fromv$managed_standby;
- PROCESS STATUS
- --------- ------------
- ARCH CLOSING
- ARCH CLOSING
- ARCH CONNECTED
- ARCH CLOSING
- RFS IDLE
- RFS IDLE
- RFS IDLE
- MRP0 APPLYING_LOG
过一会儿,我们再查询,发现standby 端宕机过程中primary端数据库的改变也同样通过日志更新过来了。
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------ -----------------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- EMP001 TABLE
- EMP005 TABLE
- SALGRADE TABLE
- 6 rows selected.
我们通过这个实验可以看出,将DataGuard配置为Maximumavailability模式后,pri数据是实时同步到sty端。
DG最大保护模式Maximum protection
1、将主库修改为最大保护模式
- 先关闭主库
- SQL>shutdown immediate
- Databaseclosed.
- Databasedismounted.
- ORACLEinstance shut down.
- 启动到mount状态
- SQL>startup mount;
- ORACLEinstance started.
- TotalSystem Global Area 839282688 bytes
- FixedSize 2217992 bytes
- VariableSize 557844472 bytes
- DatabaseBuffers 276824064 bytes
- RedoBuffers 2396160 bytes
- Databasemounted.
- 修改为最大保护模式
- SQL>alter database set standby database to maximize protection;
- Databasealtered.
- Open数据库
- SQL>alter database open;
- Databasealtered.
Primary端查询状态:
- SQL>select name,open_mode,database_role,protection_mode,SWITCHOVER_STATUS From v$database;
- NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
- -------------------------------------------------- ---------------- -------------------- --------------------
- WOO READ WRITE PRIMARY MAXIMUM PROTECTION TO STANDBY
Standby端查询状态(切换只需要操作主库,备库不需要动即可)
- SQL>select name,open_mode,database_role,protection_mode,SWITCHOVER_STATUS From v$database;
- NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
- ----------------------------- ---------------- -------------------- --------------------
- WOO READ ONLY WITH APPLY PHYSICAL STANDBYMAXIMUM PROTECTION NOT ALLOWED
我们现在可以看到,primary和standby端现在都为Maximumprotection 最大保护模式。
实验
先查看当前的表
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------------- ----------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- EMP001 TABLE
- EMP005 TABLE
- SALGRADE TABLE
- 6 rows selected.
我们删除emp005以及创建emp006表
- SQL> drop table emp005 purge;
- Table dropped.
- SQL> create table emp006 as select* from emp001;
- Table created.
我们可以看到emp005已经被删除,并且emp006表也被创建好了。
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------------- ----------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- EMP001 TABLE
- EMP006 TABLE
- SALGRADE TABLE
- 6 rows selected.
- SQL>
两边的表是一样的
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------------- ----------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- EMP001 TABLE
- EMP005 TABLE
- SALGRADE TABLE
- 6 rows selected.
在主库端删除的emp005及新创建的emp006也实时同步过来了
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------------- ----------
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- EMP001 TABLE
- EMP006 TABLE
- SALGRADE TABLE
- 6 rows selected.
- SQL>
我现在关闭sty端服务器:
- [root@sty ~]# shutdown -h now
- Broadcast message from root (pts/1)(Tue May 21 03:14:28 2013):
- The system is going down for systemhalt NOW!
返回来看主库的状态:
日志传输以及终止了:
- Log Transport Services Error ORA-16198: LGWR received timedout error from KSR
- Log Transport Services Error Error 12543 received logging on to the standby
- Log Transport Services Error LGWR: Error 12543 attaching to RFS for reconnect
- Log Transport Services Error Error 12543 received logging on to the standby
- Log Transport Services Error LGWR: Error 12543 attaching to RFS for reconnect
一段时间之后,primary端也宕机了:
- SQL> selectname,open_mode,database_role,protection_mode,SWITCHOVER_STATUS From v$database;
- NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
- ------------------------------------------------------------ ---------------- -------------------- --------------------
- WOO READWRITE PRIMARY MAXIMUM PROTECTION TO STANDBY
- SQL> selectname,open_mode,database_role,protection_mode,SWITCHOVER_STATUS From v$database;
- selectname,open_mode,database_role,protection_mode,SWITCHOVER_STATUS From v$database
- *
- ERROR at line 1:
- ORA-03135: connection lost contact
- Process ID: 8386
- Session ID: 39 Serial number: 5
这个时候需要把standby端起来,primary端,数据库才能起来。
- SQL> startup mount;
- ORACLE instance started.
- Total System Global Area 839282688 bytes
- Fixed Size 2217992 bytes
- Variable Size 507512824 bytes
- Database Buffers 327155712 bytes
- Redo Buffers 2396160 bytes
- Database mounted.
停止redo应用
- SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASECANCEL;
- Database altered.
Open数据库
- SQL> alter database open;
- Database altered.
开启redo应用
- SQL> ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE DISCONNECT FROM SESSION;
- Database altered.
Database altered.再启主库
- SQL> startup
- ORACLE instance started.
- Total System Global Area 839282688 bytes
- Fixed Size 2217992 bytes
- Variable Size 507512824 bytes
- Database Buffers 327155712 bytes
- Redo Buffers 2396160 bytes
- Database mounted.
- Database opened.