[Data Guard] Creating a "real-time apply" physical standby databse

 

In the last [Data Guard] article, I used the following command to start the redo apply, which is not "real-time" apply...

SQL> alter database recover managed standby database disconnect from session;

 

Oracle also supports "real-time" apply (and "real-time query" in 11g -- active dg) so that the standby database can reflect the changes made in primary database immediately, instead of waiting until log file switch (log archive) happens. This feature is very absorbing as we can use the standby database for query/reporting which can access the latest data in the primary database. The only change we need to take to make the redo-apply in real time is to add the clause "using current logfile" in the "alter database recover managed standby database command", like...

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

The following screenshot (excerpted from Oracle document) illustrates the architecture of the "real-time" apply...

 

 

Let's see the real-time apply in action.

-- In standby database
-- First stop redo apply
SQL> alter database recover managed standby database cancel;

Database altered.

-- open db in read only mode
-- Note though don't specify "read only" option explicitly, the standby is opened in read-only
SQL> alter database open;

Database altered.


-- Start real-time apply
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL>

-- Verify the db open mode -- read only
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

 

 The alert file writed down all the action happened... 

 

 

Wed May 09 09:35:37 2012
alter database recover managed standby database cancel
Wed May 09 09:35:37 2012
MRP0: Background Media Recovery cancelled with status 16037
Errors in file D:\ORACLE\diag\rdbms\standby\standby\trace\standby_pr00_7988.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 4114980
Completed: alter database recover managed standby database cancel

alter database recover managed standby database using current logfile disconnect from session
Wed May 09 09:27:47 2012
MRP0 started with pid=82, OS id=8756
started logmerger process
Wed May 09 09:27:52 2012
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 24 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Completed: alter database recover managed standby database using current logfile disconnect from session

 

 

Now let's see what would happen if we make changes in primary database...

-- Primary 

C:\Documents and Settings\yufa>sqlplus sys/a@primary as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 8 21:02:34 2012

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


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

SQL> select * from test;

no rows selected

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test values(2);

1 row created.

SQL> commit;

Commit complete.

SQL>



-- standby
SQL> select * from test;

no rows selected

SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)

SQL> select * from test;

no rows selected

SQL> select * from test;

        ID
----------
         1

SQL> select * from test;

        ID
----------
         1
         2

SQL>

 

As soon as there is one record commited in the primary database, the standby database can see that change. Amazing, right?