Oracle 11g Active Data Guard搭建、管理(5)

五、修改dataguard的数据保护模式为最高可用性模式

根据Oracle文档的解释，最高可用性数据保护模式需要先满足以下几个条件

Maximum Availability

 

Maximum Performance

 

Maximum Protection

 

AFFIRM or NOAFFIRM

 

NOAFFIRM

 

AFFIRM

 

SYNC

 

ASYNC

 

SYNC

 

DB_UNIQUE_NAME

 

DB_UNIQUE_NAME

 

DB_UNIQUE_NAME

 

Minimum Requirements for Maximum Protection Mode

Redo Archival Process

 

LGWR

 

Network Transmission Mode

 

SYNC

 

Disk Write Option

 

AFFIRM

 

Standby Redo Logs?

 

Yes

 

Standby Database Type

 

Physical Only

 

For example:

log_archive_dest_2='service=testdb_standby LGWR SYNC AFFIRM'

 

主库上查看DG状态

SQL> select db_unique_name,protection_mode,protection_level from v$database;

DB_UNIQUE_NAME PROTECTION_MODEPROTECTION_LEVEL

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

ORCL_PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

备库上查看DG状态

SQL> select db_unique_name,protection_mode,protection_level from v$database;

DB_UNIQUE_NAME PROTECTION_MODEPROTECTION_LEVEL

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

orcl_standby MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

在主库上设置DG保护模式为最高可用性模式

SQL> alter database set standby database to maximize availability;

再次查看主库、备库，发现DG保护模式都已经变了。

主库

SQL> select db_unique_name,protection_mode,protection_level from v$database;

DB_UNIQUE_NAME PROTECTION_MODEPROTECTION_LEVEL

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

ORCL_PRIMARY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

备库

SQL> select db_unique_name,protection_mode,protection_level from v$database;

DB_UNIQUE_NAME PROTECTION_MODEPROTECTION_LEVEL

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

orcl_standby MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

将备库shutdown后，主库和备库的PROTECTION_LEVEL将变为RESYNCHRONIZATION

SQL> select db_unique_name,protection_mode,protection_level from v$database;

DB_UNIQUE_NAME PROTECTION_MODEPROTECTION_LEVEL

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

orcl_standby MAXIMUM AVAILABILITYRESYNCHRONIZATION

备库执行

取消Redo Apply

SQL> alter database recover managedstandby database cancel;

SQL> shutdown immediate

SQL> selectdb_unique_name,protection_mode,protection_level from v$database;

六、体验实时查询（Real-timequery）特性 (可选)

1）主库上创建表空间、用户以及表并初始化数据

（1）创建表空间并查看表空间创建结果和状态

SQL> create tablespace test_tbs datafile '/data/oracle/app/oracle/oradata/orcl/test_tbs01.dbf' size 10m;

SQL> select * from v$tablespace where name = 'TEST_TBS';

SQL> select ts#,status,bytes,name fromv$datafile where ts# = 8;

（2）创建用户并授权

SQL> create user islandstar identifiedby 123456 default tablespace test_tbs;

SQL> grant dba to islandstar;

（3）创建表并初始化数据

$ sqlplus /nolog

SQL> conn islandstar/123456

SQL> create table t (x varchar2(8));

SQL> insert into t values ('islandstar');

SQL> commit;

SQL> select * from t;

X

--------

Secooler

4）验证主库所创建表空间、用户以及表并初始化数据是否在备库应用成功

（1）查看备库表空间

SQL> select * from v$tablespace where name = 'TEST_TBS';

TS# NAME INCBIG FLA ENC

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

8SECOOLER_TBS YES NO YES

SQL> select ts#,status,bytes,name from v$datafile where ts# = 8;

TS# STATUS BYTES

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

NAME

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

8 RECOVER 10485760

/data/oracle/app/oracle/oradata/orcl/test_tbs01.dbf

创建成功，但是此时备库数据文件的状态是“RECOVER”。

（2）查看备库用户及数据是否存在

方法同主库

主库上的变化已经即使的在备库上得到应用。

这便是Oracle 11g物理Active Data Guard实时查询（Real-time query）特性的体现。