Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Saturday, August 22, 2015

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.

No comments: