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 10GHowever, 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 200I’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 commented in my "parallel" blog in DEV.to, I was referring to older documentation, so that is all clear now:
Comments
Btw, you can skip the step of manual enabling the DG Broker. According to RTFM and therein the “Usage Notes”, if DG broker is not enabled, the command is taking care about setting DG_BROKER_START=TRUE. This is really true … 🙂 There will be a message “Initialization parameter DG_BROKER_START set to TRUE” when running the “PREPARE DATABASE” command. After the command is finished, DG_BROKER_START is set to TRUE. Happy DG-ing ! Cheers, Silvio
Yes, I realized that later. Also, you can set the configuration files location during the prepare command.. really cool feature, I think it will make things easier for terraform/ansible deployments
The command looks promising, but in the current state is not really a help, to be honest. As you already mentioned, the “redolog+1” standby log group is missing. Next, the documentation is stating “… If the logs exist and are misconfigured, they are deleted and recreated …” . It created completely misconfigured standby log groups, each with different sizes and all of them smaller in size as the smallest online redolog group. These existing standby logfiles were not deleted – the command just added additional standby log files with the size of the biggest online redolog group. So, in the current state it would be better to implement all these checks in Ansible yourselfves – you are required to re-check and correct the outcome of command anyway … 🙂
What is interesting, is that Oracle themselves have some documentation showing that there is not need for the extra Redo as @CrossMyP4th pointed out on twitter: https://twitter.com/CrossMyP4th/status/1434856670224658437?s=20 https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/oracle-data-guard-redo-transport-services.html#GUID-E6EC6104-3C38-482D-B807-A0E84ECFB937 ‘The standby redo log must have at least one more redo log group than the redo log at the redo source database vs Doc ID 2283978.1 ‘Oracle recommends having the same number of standby redo log groups as there are online redo log groups for each thread’