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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s