Oracle real time query on standby database or Active data guard

Enable Real time query:

On Primary:

========

SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME              OPEN_MODE        DATABASE_ROLE

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

orcl                  READ WRITE        PRIMARY

Add the standby logfiles:

SQL> select member,type from v$logfile;

MEMBER                    TYPE

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

+DATA2/orcl/redo03.log            ONLINE

+DATA2/orcl/redo02.log            ONLINE

+DATA2/orcl/redo01.log            ONLINE

SQL> alter database add standby logfile '+DATA2';

Database altered.

SQL> alter database add standby logfile '+DATA2';

Database altered.

SQL> alter database add standby logfile '+DATA2';

Database altered.

SQL> select member,type from v$logfile;

MEMBER                           TYPE

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

+DATA2/orcl/redo03.log                   ONLINE

+DATA2/orcl/redo02.log                   ONLINE

+DATA2/orcl/redo01.log                   ONLINE

+DATA2/orcl/onlinelog/group_4.314.888428335       STANDBY

+DATA2/orcl/onlinelog/group_5.334.888428449       STANDBY

+DATA2/orcl/onlinelog/group_6.347.888428451       STANDBY

On Standby:

=========

SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME              OPEN_MODE        DATABASE_ROLE

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

msft                  MOUNTED  PHYSICAL STANDBY

SQL> recover managed standby database cancel;

Media recovery complete.

Add the standby logfiles:

SQL> alter database add standby logfile '+DATA2';

Database altered.

SQL> alter database add standby logfile '+DATA2';

Database altered.

SQL> alter database add standby logfile '+DATA2';

Database altered.

SQL> select sequence#,status from v$managed_standby;

SEQUENCE# STATUS

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

    0 CONNECTED

    0 CONNECTED

    0 CONNECTED

    0 CONNECTED

    0 IDLE

   13 IDLE

    0 IDLE

7 rows selected.

SQL> select member,type from v$logfile;

MEMBER                           TYPE

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

+DATA2/msft/redo03.log                   ONLINE

+DATA2/msft/redo02.log                   ONLINE

+DATA2/msft/redo01.log                   ONLINE

+DATA2/msft/onlinelog/group_4.380.888428573       STANDBY

+DATA2/msft/onlinelog/group_5.379.888428577       STANDBY

+DATA2/msft/onlinelog/group_6.378.888428577       STANDBY

6 rows selected.

Start the recovery using the current logfile:

SQL> alter database open;

Database altered.

SQL> recover managed standby database using current logfile disconnect;

Media recovery complete.

SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME              OPEN_MODE        DATABASE_ROLE

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

msft                  READ ONLY WITH APPLY PHYSICAL STANDBY

Test the real time query:

SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME              OPEN_MODE        DATABASE_ROLE

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

orcl                  READ WRITE        PRIMARY

SQL> select * from hr.regions;

REGION_ID REGION_NAME

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

    1 Europe

    2 Americas

    3 Asia

    4 Middle East and Africa

Add a new region to the regions table:

On Primary:

=========

SQL> select * from hr.regions;

REGION_ID REGION_NAME

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

    1 Europe

    2 Americas

    3 Asia

    4 Middle East and Africa

SQL> insert into hr.regions values (5,'Central America');

1 row created.

SQL> commit;

Commit complete.

Verify the data @ standby:

SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME              OPEN_MODE        DATABASE_ROLE

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

msft                  READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>  select * from hr.regions;

REGION_ID REGION_NAME

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

    1 Europe

    2 Americas

    3 Asia

    4 Middle East and Africa

    5 Central America

Newly added row (region_id- 5) is instantly accessible from the standby.