Suppose you have duplicated a 19c physical standby already, it's time to build the data guard relationship between the primary and standby databases. In this post, we directly enable data guard broker to implement 19c data guard environment.
Standby Redo Logs
We have to add standby redo logs on both primary and standby database for real-time apply before enabling data guard broker.
Primary Database
[oracle@primary-19c ~]$ sqlplus / as sysdba
...
SQL> alter database add standby logfile '/u01/app/oracle/oradata/COMPDB/redo04.log' size 200m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/COMPDB/redo05.log' size 200m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/COMPDB/redo06.log' size 200m;
Database altered.
Check locations of all redo logs.
SQL> column group# format 9;
SQL> column type format a10;
SQL> column member format a50;
SQL> select group#, type, member from v$logfile order by 1;
GROUP# TYPE MEMBER
------ ---------- --------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/COMPDB/redo01.log
2 ONLINE /u01/app/oracle/oradata/COMPDB/redo02.log
3 ONLINE /u01/app/oracle/oradata/COMPDB/redo03.log
4 STANDBY /u01/app/oracle/oradata/COMPDB/redo04.log
5 STANDBY /u01/app/oracle/oradata/COMPDB/redo05.log
6 STANDBY /u01/app/oracle/oradata/COMPDB/redo06.log
6 rows selected.
Standby Database
[oracle@standby-19c ~]$ sqlplus / as sysdba
...
SQL> alter database add standby logfile '/u01/app/oracle/oradata/COMPDB/redo04.log' size 200m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/COMPDB/redo05.log' size 200m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/COMPDB/redo06.log' size 200m;
Database altered.
Configure Static Listener
The broker requires a special static service used for connecting remote idle databases in order to switch over the primary role back and forth more smoothly. That is, you have to add it to listener.ora and restart listener on both sides.
Modify Primary Listener.ora
[oracle@primary-19c ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=PRIMDB)
(GLOBAL_DBNAME=PRIMDB_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
)
)
Then restart it.
[oracle@primary-19c ~]$ lsnrctl stop; lsnrctl start
Modify Standby Listener.ora
[oracle@standby-19c ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=STANDB)
(GLOBAL_DBNAME=STANDB_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
)
)
Then restart it.
[oracle@standby-19c ~]$ lsnrctl stop; lsnrctl start
For RAC environment, you should add those static services to the listeners at grid-level.
Enable Data Guard Broker
We have to enable data guard brokers on both database. First of all, check the configuration of data guard broker. Take the primary database for an instance.
SQL> show parameter broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),
((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1 string /u01/app/oracle/product/19.0.0
/dbhome_1/dbs/dr1PRIMDB.dat
dg_broker_config_file2 string /u01/app/oracle/product/19.0.0
/dbhome_1/dbs/dr2PRIMDB.dat
dg_broker_start boolean FALSE
use_dedicated_broker boolean FALSE
Please make sure the locations of broker configuration files are existing and allowable to be written, otherwise, you will get ORA-16571 when creating a broker configuration. Next, set DG_BROKER_START to TRUE on both sides so as to enable the broker to work for data guard.
SQL> alter system set dg_broker_start=TRUE scope=both;
System altered.
Check the broker parameters again.
SQL> show parameter broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),
((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1 string /u01/app/oracle/product/19.0.0
/dbhome_1/dbs/dr1PRIMDB.dat
dg_broker_config_file2 string /u01/app/oracle/product/19.0.0
/dbhome_1/dbs/dr2PRIMDB.dat
dg_broker_start boolean TRUE
use_dedicated_broker boolean FALSE
Configure Data Guard Broker
Create a Broker Configuration
There're scenarios using the DGMGRL command-line interface including creating a broker configuration.
[oracle@primary-19c ~]$ dgmgrl /
...
Connected to "PRIMDB"
Connected as SYSDG.
DGMGRL> create configuration drconf as primary database is PRIMDB connect identifier is PRIMDB;
Configuration "drconf" created with primary database "primdb"
DGMGRL> add database STANDB as connect identifier is STANDB maintained as physical;
Database "standb" added
DGMGRL> enable configuration;
Enabled.
We may have to wait for a few minutes or so to let them fill the gap.
Configuration Status
DGMGRL> show configuration verbose;
Configuration - drconf
Protection Mode: MaxPerformance
Members:
primdb - Primary database
standb - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'COMPDB_CFG'
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS
Primary Database Status
DGMGRL> show database primdb;
Database - primdb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PRIMDB
Database Status:
SUCCESS
Standby Database Status
DGMGRL> show database standb;
Database - standb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 652.00 KByte/s
Real Time Query: OFF
Instance(s):
STANDB
Database Status:
SUCCESS
By configuring data guard broker, a 19c data guard environment has been built as we saw.
Listener Status
The best thing is that data guard broker will automatically publish a configuration-wide service named <DB_NAME>_CFG to listeners at all parties, which does not affected by role transition.
For Oracle database developers or administrators, it's pretty helpful to design connection descriptors (strings) for scenarios in failover, switchover or real-time query.
By the way, the new feature for data guard broker begins from 12.2.
Primary Listener
[oracle@primary-19c ~]$ lsnrctl status
...
Services Summary...
Service "COMPDB_CFG" has 1 instance(s).
Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDB" has 1 instance(s).
Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDBXDB" has 1 instance(s).
Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDB_DGMGRL" has 1 instance(s).
Instance "PRIMDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Standby Listener
[oracle@standby-19c ~]$ lsnrctl status
...
Services Summary...
Service "COMPDB_CFG" has 1 instance(s).
Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "STANDB" has 1 instance(s).
Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "STANDBXDB" has 1 instance(s).
Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "STANDB_DGMGRL" has 1 instance(s).
Instance "STANDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
As you can see, PRIMDB_DGMGRL and STANDB_DGMGRL are static services used by the broker.
Although configuring broker can save most of our jobs on data guard configuration, at least two things that you have to take care of them by yourself.
- System-Level
- Database-Level
Setting STANDBY_FILE_MANAGEMENT to AUTO in parameter file before duplication, this can replay new data files creation on the standby database.
SQL> alter system set standby_file_management=auto;
Enabling force logging in control file before duplication, this can propagate all block changes to the standby database without missing anything.
SQL> alter database force logging;
Next, maybe we should take a look at how we switchover to the standby database in 19c by data guard broker.