CONVERT PHYSICAL STANDBY DATABASE TO ACTIVE DATAGUARD USING MANUAL METHOD
Active dataguard is a licensed extension feature to physical standby database which operate in ‘open read only with log apply mode’
Advantages:
- Analytical queries can be offloaded for reports
- Backup,export import jobs or other jobs can be offloaded to reduce overhead to primary system
Steps to launch active dataguard:
Cancel MRP in standby database and open the database.
kish@STDBY>alter database recover managed standby database cancel;
Database altered.
kish@STDBY>alter database flashback on;
Database altered.
kish@STDBY>alter database open;
Database altered.
kish@STDBY>select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DBX00 READ ONLY PHYSICAL STANDBY
After open of database, enable MRP with the below syntax and check the status. If you see ‘read only with apply’ status ,then active dataguard is at your service!
kish@STDBY>alter database recover managed standby database using current logfile disconnect from session;
Database altered.
kish@STDBY>select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DBX00 READ ONLY WITH APPLY PHYSICAL STANDBY
Check the status of MRP
kish@STDBY>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 0
RFS IDLE 80
MRP0 WAIT_FOR_LOG 80
7 rows selected.
I test the effectiveness of active dataguard by using sample table and switch the logfile in primary
kish@PRIM>create table t2 as select * from t;
Table created.
kish@PRIM>insert into t2 values('activedg_work_or_not?',2);
1 row created.
kish@PRIM>commit;
Commit complete.
kish@PRIM>alter system switch logfile;
System altered.
Check if you can insert data in standby database. As the standby database is in open read only mode, you cannot perform any DML transactions on standby. But we can read the table data!
kish@STDBY>insert into t values('activedg_work_or_not?',2);
insert into t values('activedg_work_or_not?',2)
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access
kish@STDBY>select * from t2;
select * from t2
*
ERROR at line 1:
ORA-00942: table or view does not exist
kish@STDBY>select * from t2;
NAME ID
------------------------------ ----------
abc 1
abc 1
snapshot_work_or_not? 2
rechechk_work_or_not? 3
rechechk_again 4
activedg_work_or_not? 2
6 rows selected.
kish@STDBY>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 0
RFS IDLE 81
MRP0 WAIT_FOR_LOG 81
7 rows selected.
Log file details after converting to active dataguard. As you can observe that the database is operating on read only mode with logs applied parallely
alter database open
Sat Oct 02 19:55:55 2021
Ping without log force is disabled
.
Sat Oct 02 19:55:57 2021
SMON: enabling cache recovery
Sat Oct 02 19:55:57 2021
Dictionary check beginning
Dictionary check complete
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Pluggable database PDB$SEED dictionary check beginning
Sat Oct 02 19:55:58 2021
===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
Sat Oct 02 19:55:58 2021
Pluggable Database PDB$SEED Dictionary check complete
Database Characterset for PDB$SEED is AL32UTF8
Opening pdb PDB$SEED (2) with no Resource Manager plan active
Physical standby database opened for read only access.
Completed: alter database open
Sat Oct 02 19:56:33 2021
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
alter database recover managed standby database using current logfile disconnect from session
Standby redo logs should be configured for real time apply. Real time apply will be ignored.
Sat Oct 02 19:56:33 2021
Attempt to start background Managed Standby Recovery process (dbx01)
Starting background process MRP0
Sat Oct 02 19:56:33 2021
MRP0 started with pid=28, OS id=13057
Sat Oct 02 19:56:33 2021
MRP0: Background Managed Standby Recovery process started (dbx01)
Sat Oct 02 19:56:39 2021
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Sat Oct 02 19:56:39 2021
Waiting for all non-current ORLs to be archived...
Sat Oct 02 19:56:39 2021
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 80 (in transit)
Completed: alter database recover managed standby database using current logfile disconnect from session
Sat Oct 02 19:59:03 2021
Archived Log entry 64 added for thread 1 sequence 80 rlc 1083714910 ID 0x82d9615a dest 2:
Sat Oct 02 19:59:04 2021
Media Recovery Log /apps01/FRA/DBX01/archivelog/2021_10_02/o1_mf_1_80_jojpxy2r_.arc
Sat Oct 02 19:59:05 2021
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Assigned to RFS process (PID:13137)
RFS[3]: No standby redo logfiles created
RFS[3]: Opened log for thread 1 sequence 81 dbid 2195240283 branch 1083714910
Sat Oct 02 19:59:10 2021
Media Recovery Waiting for thread 1 sequence 81 (in transit)
To get back to physical standby from active dataguard, follow the below steps
kish@STDBY>alter database recover managed standby database cancel;
Database altered.
kish@STDBY>shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
kish@STDBY>startup mount;
ORACLE instance started.
Total System Global Area 1509949440 bytes
Fixed Size 2924640 bytes
Variable Size 989859744 bytes
Database Buffers 503316480 bytes
Redo Buffers 13848576 bytes
Database mounted.
kish@STDBY>select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DBX00 MOUNTED PHYSICAL STANDBY
kish@STDBY>alter database recover managed standby database using current logfile disconnect from session;
Database altered.
kish@STDBY>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 0
RFS IDLE 81
MRP0 WAIT_FOR_LOG 81
7 rows selected.