玉面飞龙的BLOG

11g Oracle Active Data Guard

September 29th, 2008 | Categories: Boring | Tags: Oracle, Scale out, Standby

11g physical standby的这个enhancement果然强大，standby数据库在READ ONLY的时候既可以被只读查询，也能够被实时的应用日志。

1)增加了投资回报率。数据库既可以作为DR，也可以同时提供复杂的报表SQL服务。
2)可以替换只读复制replication
3)Reader Farms还为数据库Scale Out提供了可选方案

9.2.1 Real-time query

A physical standby database can be open for read-only access while Redo Apply is active if a license for the Oracle Active Data Guard option has been purchased. This capability is known as real-time query.

A physical standby database instance cannot be opened if Redo Apply is active on that instance or on any other mounted instance. Use the following SQL statements to stop Redo Apply, open a standby instance read-only, and restart Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Standby数据库real time query open后,可以让普通用户登陆执行只读查询
SQL> show user USER is "XFAN"


SQL> create table t3 (id number);

create table t3 (id number)

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database open for read-only access
SQL> select count(*) from t;

  COUNT(*)

----------

     11823
SQL> select * from t where rownum<=10;

        ID

----------

         1

         2

         3

         4

         5

         6

         7

         8

         9

        10
10 rows selected.

SQL>

SQL> select count(*) from t;
  COUNT(*)

———-

     11849
SQL> select count(*) from t as of timestamp TO_TIMESTAMP(’20080928 20:00:00′,’yyyymmdd hh24:mi:ss’);

  COUNT(*)

———-

     11827