一、什么是adg
adg 是11g的一个新特性 就是active dataguard ,在Oracle 11g之前,物理备库(physical Standby)在应用redo的时候,是不可以打开的,只可以mount。从11g开始,在应用redo的时候,物理备库可以处于read-only模式,这就称为Active Data Guard 。通过Active Data Guard,可以在物理备库进行查询或者导出数据,
从而减少对主库的访问和压力。
Active Data Guard适用于一些只读性的应用,比如,有的应用程序只是查询数据,进行一些报表业务,不会产生redo数据,
这些应用可以转移到备库上,避免对主库资源的争用。
如需启用Active Data Guard, 只需要将备库以 read-only 模式打开,而且执行 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE语句就可以。
需要注意的是:主库和备库的COMPATIBLE 参数至少要设置为11.0.0。
二、配置成adg需要准备
主库:
SQL> select status,instance_name,database_role from v$instance,v$database;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN rac PRIMARY
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
396
备库:
SQL> select status,instance_name,database_role from v$instance,v$database;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
MOUNTED orcl PHYSICAL STANDBY
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
396
主库和备库的最大sequence#要一致
备库:
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 41
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 41
mrp的状态是wait 状态
三、添加standby redo log
alter database add standby logfile '/u01/app/oracle/oradata/standredo01.log' size 50M;
alter database add standby logfile '/u01/app/oracle/oradata/standredo02.log' size 50M;
alter database add standby logfile '/u01/app/oracle/oradata/standredo03.log' size 50M;
alter database add standby logfile '/u01/app/oracle/oradata/standredo04.log' size 50M;
四、备库执行如下命令
如下:在备库执行:
SQL>startup mount;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL>alter database recover managed standby database cancel;
SQL> alter database open;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL>alter database recover managed standby database using current logfile disconnect;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
如果已经启用了Active Data Guard,备库的V$DATABASE会显示为"READ ONLY WITH APPLY':
SQL> SELECT open_mode FROM V$DATABASE;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
select DB_UNIQUE_NAME,PROTECTION_MODE,database_role,open_mode from v$database
DB_UNIQUE_NAME PROTECTION_MODE DATABASE_ROLE OPEN_MODE
------------------------------ -------------------- ---------------- --------------------
orcl MAXIMUM PERFORMANCE PHYSICAL STANDBY READ ONLY WITH APPLY
注意:使用Active
Data Guard要求主库和备库的COMPATIBLE 参数至少设置为11.0.0。
ADG实际上就是Physical Standby的Real Time Apply方式应用的日志,在我们配置了standby redo log后,会直接通过standby redo log做recover,
你提到了读一致性的问题,毕竟undo datafile也会被恢复过来,所以未commit的事务,还是会读取undo segment的,这点应该不会改变
查看standby redo log
主库查看:
SQL> select * from v$standby_log;
GROUP# DBID THREAD#SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
7 UNASSIGNED 1 0 52428800 512 512 YES UNASSIGNED 0 0 0
8 UNASSIGNED 1 0 52428800 512 512 YES UNASSIGNED 0 0 0
9 UNASSIGNED 1 0 52428800 512 512 YES UNASSIGNED 0 0 0
10 UNASSIGNED 2 0 52428800 512 512 YES UNASSIGNED 0 0 0
11 UNASSIGNED 2 0 52428800 512 512 YES UNASSIGNED 0 0 0
12 UNASSIGNED 2 0 52428800 512 512 YES UNASSIGNED 0 0 0
6 rows selected.
备库查看:
SQL> select * from v$standby_log;
GROUP# DBID THREAD#SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
7 2407816564 1 157 52428800 512 11044352 YES ACTIVE 12370225 02-AUG-13 12394343 02-AUG-13 12394343 02-AUG-13
8 UNASSIGNED 1 0 52428800 512 512 NO UNASSIGNED 0 0 0
9 UNASSIGNED 1 0 52428800 512 512 NO UNASSIGNED 0 0 0
10 2407816564 2 194 52428800 512 9279488 YES ACTIVE 12370222 02-AUG-13 12394344 02-AUG-13 12394344 02-AUG-13
11 UNASSIGNED 2 0 52428800 512 512 NO UNASSIGNED 0 0 0
12 UNASSIGNED 2 0 52428800 512 512 NO UNASSIGNED 0 0 0
6 rows selected.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CLOSING 221
ARCH CLOSING 222
ARCH CLOSING 229
ARCH CLOSING 228
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 223
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 230
RFS IDLE 0
RFS IDLE 0
MRP0 APPLYING_LOG 223
39 rows selected.
下面的功能是允许在read-only的数据库上执行的:
• Issue SELECT statements, including queries that require multiple sorts that leverage TEMP
segments
• Use ALTER SESSION and ALTER SYSTEM statements
• Use SET ROLE
• Call stored procedures
• Use database links (dblinks) to write to remote databases
• Use stored procedures to call remote procedures via dblinks
• Use SET TRANSACTION READ ONLY for transaction level read consistency
• Issue complex queries (such as grouping SET queries and WITH CLAUSE queries)
下面的功能是不允许在read-only的数据库上执行的:
• Any DMLs (excluding simple SELECT statements) or DDLs
• Query accessing local sequences
• DMLs to local temporary tables
比较典型的Active Data Guard 分为:
• 单实例的物理主库和单实例的物理备库
• 主库为Oracle Real Application Clusters (Oracle RAC) ,备库为单实例
• RAC主库和RAC备库
Oracle Data Guard 的配置方法,,请参考下面的文档:
* 单实例的物理主库和单实例的物理备库:
http://docs.oracle.com/cd/B28359_01/server.111/b28294/create_ps.htm
* 主库为Oracle Real Application Clusters (Oracle RAC) ,备库为单实例:
http://www.oracle.com/technetwork/database/features/availability/maa-wp-10g-racprimarysingleinstance-131970.pdf
* RAC 主库和RAC 备库:
10g: http://www.oracle.com/technetwork/database/features/availability/maa-wp-10g-racprimaryracphysicalsta-131940.pdf
11g: http://www.oracle.com/technetwork/database/features/availability/dataguard11g-rac-maa-1-134639.pdf
* 关于Active Data Guard的最佳实践经验,请参考文档:
http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr1-activedataguard-1-128199.pdf
* 关于Oracle Maximum Availability Architecture Best Practices的更多文档,请参考:
http://www.oracle.com/goto/maa