![Cover image for A warning about DG Broker "PREPARE DATABASE FOR DATA GUARD" command on 21c [21.3 version]](https://media2.dev.to/dynamic/image/width=1000,height=420,fit=cover,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Farticles%2Fuwqlukrqeovwgs9sedyn.png)
One of the new features of 21c Data Guard Broker is the ability to prepare a Database for Dataguard Configuration running a simple command.
It is very handy feature since DG broker will enable Archive Mode, restart the database, create the Standby Redo logs, etc..
Before you use it though, please be aware of some shortcoming you may still have on 21.3 version.
Let's enable DG Broker first:
SQL> show parameter brok
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),
((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1 string /u01/app/oracle/homes/OraDB21H
ome1/dbs/dr1cdb21.dat
dg_broker_config_file2 string /u01/app/oracle/homes/OraDB21H
ome1/dbs/dr2cdb21.dat
dg_broker_start boolean FALSE
use_dedicated_broker boolean FALSE
SQL> alter system set dg_broker_start=true;
System altered.
SQL> show parameter brok
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),
((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1 string /u01/app/oracle/homes/OraDB21H
ome1/dbs/dr1cdb21.dat
dg_broker_config_file2 string /u01/app/oracle/homes/OraDB21H
ome1/dbs/dr2cdb21.dat
dg_broker_start boolean TRUE
use_dedicated_broker boolean FALSE
SQL>
Now that we have DG broker enable, we just need to execute the command to prepare the system.
No need to create an initial configuration or enable anything else.
The complete command syntax options can be found here:
[oracle@rac1-node1 ~]$ dgmgrl
DGMGRL for Linux: Release 21.0.0.0.0 - Production on Sun Sep 5 09:26:53 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> PREPARE DATABASE FOR DATA GUARD WITH DB_UNIQUE_NAME IS cdb21 DB_RECOVERY_FILE_DEST IS "/u01/oradata/CDB21/FRA/" DB_RECOVERY_FILE_DEST_SIZE is "10G";
Preparing database "cdb21" for Data Guard.
Initialization parameter DB_FILES set to 1024.
Initialization parameter LOG_BUFFER set to 268435456.
Primary database must be restarted after setting static initialization parameters.
Primary database must be restarted to enable archivelog mode.
Shutting down database "cdb21".
Database closed.
Database dismounted.
ORACLE instance shut down.
Starting database "cdb21" to mounted mode.
ORACLE instance started.
Database mounted.
Initialization parameter DB_FLASHBACK_RETENTION_TARGET set to 120.
Initialization parameter DB_LOST_WRITE_PROTECT set to 'TYPICAL'.
RMAN configuration archivelog deletion policy set to SHIPPED TO ALL STANDBY.
Initialization parameter DB_RECOVERY_FILE_DEST_SIZE set to '10G'.
Initialization parameter DB_RECOVERY_FILE_DEST set to '/u01/oradata/CDB21/FRA/'.
LOG_ARCHIVE_DEST_n initialization parameter already set for local archival.
Initialization parameter LOG_ARCHIVE_DEST_2 set to 'location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles)'.
Initialization parameter LOG_ARCHIVE_DEST_STATE_2 set to 'Enable'.
Adding standby log group size 209715200 and assigning it to thread 1.
Adding standby log group size 209715200 and assigning it to thread 1.
Adding standby log group size 209715200 and assigning it to thread 1.
Initialization parameter STANDBY_FILE_MANAGEMENT set to 'AUTO'.
Database set to FORCE LOGGING.
Database set to ARCHIVELOG.
Database set to FLASHBACK ON.
Database opened.
DGMGRL>
This is great, as you can see for the command output, the system was converted into "Archive mode", restarted, the Standby redo was create.. etc..
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL>
DEST_ID DEST_NAME DESTINATION TARGET STATUS ERROR
---------- -------------------- ----------------------------------- --------------- ---------- ---------------
2 LOG_ARCHIVE_DEST_2 USE_DB_RECOVERY_FILE_DEST PRIMARY VALID
NAME Size GB Used GB Used Percent
---------------------------------------- ------------ ------------ ------------
/u01/oradata/CDB21/FRA/ 10 1 10
FILE_TYPE % used % reclaimable NUMBER_OF_FILES
----------------------- ---------- ------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 5.86 0 3
ARCHIVED LOG 0 0 0
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 3.91 0 2
FOREIGN ARCHIVED LOG 0 0 0
AUXILIARY DATAFILE COPY 0 0 0
8 rows selected.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 10G
However, there is something that still not perfect, or at least is not the recommended configuration for Standby Redo logs (at least for older versions, can't think of a reason why 21c would be different)
For some reason, for the 21.3 version (I have the feeling this will change) the standby redo logs groups created are the same than the REDO log Groups, something that is not part of the Best Practices for Standby configuration:
Extract from Document Best Practices for Synchronous Redo Transport - Data Guard and Active Data Guard
http://www.oracle.com/technetwork/database/availability/async-2587521.pdf
"Once the online redo logs have been appropriately sized you should create standby redo logs of the same size. It is
critical for performance that standby redo log groups only contain a single member. In addition, for each redo
log thread (a thread is associated with an Oracle RAC database instance), the number of Standby Redo Logs = number of Redo Log Groups + 1"
As we can see, we have 3 Redo Log Groups and 3 Standby Redo Logs:
-- Redo Log Groups
GROUP# THREAD# MEMBER ARC STATUS FSIZE
---------- ---------- ----------------------------------------------------------------- --- ---------- ----------
1 1 /u01/oradata/CDB21/redo01.log NO CURRENT 200
2 1 /u01/oradata/CDB21/redo02.log YES INACTIVE 200
3 1 /u01/oradata/CDB21/redo03.log YES INACTIVE 200
-- Standby Redo Logs
GROUP# THREAD# MEMBER ARC STATUS FSIZE
---------- ---------- ----------------------------------------------------------------- --- ---------- ----------
4 1 /u01/oradata/CDB21/FRA/CDB21/onlinelog/o1_mf_4_jmcnty9f_.log YES UNASSIGNED 200
5 1 /u01/oradata/CDB21/FRA/CDB21/onlinelog/o1_mf_5_jmcntzxm_.log YES UNASSIGNED 200
6 1 /u01/oradata/CDB21/FRA/CDB21/onlinelog/o1_mf_6_jmcnv1b4_.log YES UNASSIGNED 200
I'm not 100% sure the reason for this, maybe just an oversight or a future change of the Best Practices Documentation.. let's see.
In any case, if you were planning to use this command for easier deploy of your databases (this could be really useful for faster systems deployments) make sure you are at least aware of what is
actually created and setup first :)
EDIT: As Ludovico mentioned in the comments, I was missleaded to an older Documentation version :)
Top comments (2)
Hi Victor :-)
the paper you link is quite old (2015). The new recommendation is SRL groups >= ORL groups.
docs.oracle.com/en/database/oracle...
(same in 19c)
The HAOVW book is the new place to-go to find the latest HA best practices.
Are you calling me old??!! :P
Thanks for the clarification Ludovico!!