A warning about DG Broker "PREPARE DATABASE FOR DATA GUARD" command on 21c [21.3 version]
Tags: tutorial, database, oracle, 21c
Published: 2021-09-06 09:16:43

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:
```bash
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:

https://docs.oracle.com/en/database/oracle/oracle-database/21/dgbkr/oracle-data-guard-broker-commands.html#GUID-46F6267D-E3CF-4544-AC47-A22D9704BAF2
```bash
[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..
```bash
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:

```bash
-- 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 :)