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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


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]


My Cloud Certifications:
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.
Oracle Database Cloud Service Operations Certified Associate.

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: