Testing Real-time Query

In my last post I documented the steps to create an 11g Physical Standby Database configuration using RMAN and the Data Guard Broker. Now, I want to test the 'Real-time Query' functionality, introduced with 11g.

With the primary already started, I mount the standby and put it into managed recovery until it is caught up with the primary; after which, I cancel the recovery.

$ export ORACLE_SID=stdby
$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 22 07:22:34 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  196681728 bytes
Fixed Size                  1298836 bytes
Variable Size             104861292 bytes
Database Buffers           83886080 bytes
Redo Buffers                6635520 bytes
Database mounted.

SQL> alter database recover managed standby database disconnect;
alter database recover managed standby database disconnect
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> alter database recover managed standby database cancel
  2  ;

Database altered.

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
  2  FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1         59          1        246
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
MRP0      WAIT_FOR_LOG          1         60          0          0
RFS       IDLE                  1         60        827          6
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0

9 rows selected.

SQL> alter database recover managed standby database cancel;

Database altered.

After that I open the standby and once again place it into managed recovery.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

I test by creating a table at the primary and then querying the standby to see whether the change is propagated.

$ export ORACLE_SID=nf
$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle
$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 22 07:38:02 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table scott.real_time_query_test (rtqt_date date) tablespace users;

Table created.

SQL> insert into scott.real_time_query_test values (sysdate);

1 row created.

SQL> commit
  2  ;

Commit complete.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ export ORACLE_SID=stdby
$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle
$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 22 07:39:52 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from scott.real_time_query_test;

RTQT_DATE
---------
22-OCT-07

I see the table and row that I inserted at the primary on the standby so the test was successful!

Labels: 11g, Data Guard