Description:-
The read-only physical standby database can be used to offload query from the primary database. Users can use select statements and complex queries against this this database and thereby decreasing the load on the primary database. While the standby is open read-only, the following operations are disallowed
- Any Data Manipulation Language (DML) except for select statements
- Any Data Definition Language (DDL)
- Access of local sequences
- DMLs on local temporary tables
Steps on how to setup the active dataguard:
Once you setup the physical standby database as described in 19c physical standby database configuration
follow the below steps to setup the active dataguard.
Step 1: Check the status of the Primary database and the latest sequence generated in the primary database. SQL> select status,instance_name,database_role from v$instance,v$database; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ------------- ---------------- OPEN prod PRIMARY SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 40 Step 2: Check the status of the physical standby database and the latest sequence applied on the physical standby database. SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------- ------------- --------------------- MOUNTED stnd PHYSICAL STANDBY SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 40
Step 3:
Check if the Managed Recovery Process (MRP) is active on the physical standby database.
SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 RFS IDLE 141 RFS IDLE 0 RFS IDLE 0 RFS IDLE 0 MRP0 WAIT_FOR_LOG 141 9 rows selected.
Here, MRP is active. The PROCESS Column above shows that MRP is active and is waiting for the log sequence 141.
Step 4:
Cancel the MRP on the physical standby database and open the standby database. The standby database would be opened in the READ ONLY Mode.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select status,instance_name,database_role,open_mode from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------ -------------- ---------------- --------------- OPEN stnd PHYSICAL STANDBY READ ONLY
Step 5:
Now start the MRP on the physical standby database.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ --------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 RFS IDLE 141 RFS IDLE 0 RFS IDLE 0 RFS IDLE 0 MRP0 WAIT_FOR_LOG 141 9 rows selected.
The MRP is active and is waiting for the log sequence 141 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.
Catch Me On:- Hariprasath Rajaram
Telegram App: https://t.me/joinchat/I_f4DkeGfZsVSRHiM8pfyg LinkedIn:https://www.linkedin.com/in/hari-prasath-a65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba
