Enabling Active Data Guard in Physical Standby Database

brunors

Active Data Guard is the option when the standby database is configured to be in its status open with read-only mode. As explained below in the white paper “Oracle (Active) Data Guard 19c Real-Time Data Protection and Availability,” there are some benefits, dated March 2019:

dg-adg-technical-overview-wp-53475483Download

With this in mind, the current post is about enabling your Physical Standby database as an Active Data Guard. Taking as a primary step that you already have a configured Physical Standby database, this post follows the procedures below:

  • CANCEL the current managed recovery in the Physical Standby database;
  • STOP the Physical Standby database (if Oracle RAC, shutdown in all nodes);
  • START the Physical Standby database in MOUNTED state (if Oracle RAC, start in all nodes);
  • OPEN the Physical Standby database (if Oracle RAC, shutdown in all nodes);
  • ENABLE managed recovery in the Physical Standby database;

All these steps mentioned above, except the shutdown part, it is shown in the example below:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
-- STARTING FROM step "START the Physical Standby database in MOUNTED
 state (if Oracle RAC, start in all nodes);"
 
SQL> conn /as sysdba
Connected.
SQL>  alter database recover managed standby database cancel;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> alter database recover managed standby database using current logfile disconnect from session;
 
Database altered.
 
SQL> select name,open_mode from v$database;
 
NAME      OPEN_MODE
--------- --------------------
TECHDB1       READ ONLY WITH APPLY
 
SQL> select process,status,sequence# from v$managed_standby;
 
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
DGRD      ALLOCATED             0
ARCH      CLOSING          292859
DGRD      ALLOCATED             0
ARCH      CLOSING          311792
ARCH      CLOSING          292860
ARCH      CLOSING          311791
RFS       IDLE             292861
MRP0      APPLYING_LOG     292861
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE             311793
 
11 rows selected.
 
 
SQL> conn /as sysdba
Connected.
SQL> select name,open_mode from v$database;
 
NAME      OPEN_MODE
--------- --------------------
TECHDB1       MOUNTED
 
-- Here it is crucial to verify that all STANDBY database instances are open.
 Otherwise, only one instance will be behaving as an ACTIVE Data Guard, as in the example below:
 
SQL> select name,open_mode from gv$database;
 
NAME      OPEN_MODE
--------- --------------------
TECHDB1       MOUNTED
TECHDB1       READ ONLY WITH APPLY
 
SQL> alter database recover managed standby database cancel;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> alter database recover managed standby database using current logfile disconnect from session;
 
Database altered.
 
-- All instances of the Oracle RAC Data Guard database are running as an ACTIVE Data Guard:
 
SQL> select name,open_mode from gv$database;
 
NAME      OPEN_MODE
--------- --------------------
TECHDB1       READ ONLY WITH APPLY
TECHDB1       READ ONLY WITH APPLY
 
SQL>  select process,status,sequence# from v$managed_standby;
 
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
DGRD      ALLOCATED             0
DGRD      ALLOCATED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
MRP0      APPLYING_LOG     292862
 
7 rows selected.

Related posts

Leave a Comment