With the release of Oracle 21c is time for us to start to check the new features. Besides 21c is an innovative release, it shows what we will use in the future in daily activities. For Data Guard and Broker one new feature is the PREPARE DATABASE FOR DATA GUARD that adjusts the database and some parameters to become primary. Release notes can be checked here.

I created one simple database for this test and you can see below that not in archivelog mode, the Broker is not configured, and the flashback is off. For later comparison I create one pfile as well:

[oracle@orcl19s ~]$ export ORACLE_HOME=/u01/app/oracle/product/21.0.0.0/dbhome_1
[oracle@orcl19s ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@orcl19s ~]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
[oracle@orcl19s ~]$ export ORACLE_SID=dg21nf1
[oracle@orcl19s ~]$
[oracle@orcl19s ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Sun Aug 22 17:29:38 2021
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/homes/OraDB21Home1/dbs/arch
Oldest online log sequence     11
Current log sequence           13
SQL>
SQL> 
SQL> select FLASHBACK_ON, DATAGUARD_BROKER, DB_UNIQUE_NAME from v$database;

FLASHBACK_ON       DATAGUAR DB_UNIQUE_NAME
------------------ -------- ------------------------------
NO                 DISABLED dg21nf

SQL>
SQL>
SQL> create pfile = '/tmp/pfile-dg21nf.ora' from spfile;

File created.

SQL>

The prepare statement is to be used at the Broker directly (even if you have not configured it). The changes made by it follow the MAA best practices and you can define some parameters when you call the command.

[oracle@orcl19s ~]$ dgmgrl /
DGMGRL for Linux: Release 21.0.0.0.0 - Production on Sun Aug 22 17:37:16 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.
Connected to "dg21nf"
Connected as SYSDG.
DGMGRL> help prepare

Prepare a primary database for a Data Guard environment.

Syntax:

  PREPARE DATABASE FOR DATA GUARD
    [WITH [DB_UNIQUE_NAME IS <db-unique-name>]
          [DB_RECOVERY_FILE_DEST IS <directory-location>]
          [DB_RECOVERY_FILE_DEST_SIZE IS <size>]
          [BROKER_CONFIG_FILE_1 IS <broker-config-file-1-location>]
          [BROKER_CONFIG_FILE_2 IS <broker-config-file-2-location>]];


DGMGRL>

The command has some parameters that can be used: DB_UNIQUE_NAME, DB_RECOVERY_FILE_DEST, DB_RECOVERY_FILE_DEST_SIZE, BROKER_CONFIG-FILE1 (and 2). Below, you can check how I called it:

DGMGRL> PREPARE DATABASE FOR DATA GUARD
> WITH
>      DB_UNIQUE_NAME IS dg21nf
>      DB_RECOVERY_FILE_DEST IS '+RECO'
>      DB_RECOVERY_FILE_DEST_SIZE is 5G
>      BROKER_CONFIG_FILE_1 is '+DATA'
>      BROKER_CONFIG_FILE_2 is '+RECO'
> ;
Preparing database "dg21nf" 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 "dg21nf".
Database stopped.
Starting database "dg21nf" to mounted mode.
Database started.
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 '5g'.
Initialization parameter DB_RECOVERY_FILE_DEST set to '+RECO'.
Initialization parameter DG_BROKER_CONFIG_FILE1 set to '+DATA'.
Initialization parameter DG_BROKER_CONFIG_FILE2 set to '+RECO'.
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'.
Initialization parameter DG_BROKER_START set to TRUE.
Database set to FORCE LOGGING.
Database set to ARCHIVELOG.
Database set to FLASHBACK ON.
Shutting down database "dg21nf".
Database stopped.
Starting database "dg21nf".
Database started.
DGMGRL>

And you can see from the output above, the command restarted the database and set several parameters and database details. It put the database in archivelog mode, force logging, and flashback enabled. Some parameters for Broker and file management are set too. Another detail is that it will create the standby log groups too.

Below you can see the difference between the pfile that I created before calling the commands, and one generate later (check for “>”). Check all the changes and additional parameters set:

[oracle@orcl19s ~]$ diff /tmp/pfile-dg21nf.ora /tmp/pfile-dg21nf-after-prep.ora
2c2
< dg21nf1.__db_cache_size=1409286144
---
> dg21nf1.__db_cache_size=1258291200
10,11c10,11
< dg21nf1.__shared_io_pool_size=100663296
< dg21nf1.__shared_pool_size=603979776
---
> dg21nf1.__shared_io_pool_size=83886080
> dg21nf1.__shared_pool_size=486539264
19a20,22
> *.db_files=1024
> *.db_flashback_retention_target=120
> *.db_lost_write_protect='TYPICAL'
20a24,28
> *.db_recovery_file_dest_size=5368709120
> *.db_recovery_file_dest='+RECO'
> *.dg_broker_config_file1='+DATA'
> *.dg_broker_config_file2='+RECO'
> *.dg_broker_start=TRUE
26a35,37
> *.log_archive_dest_2='location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles)'
> *.log_archive_dest_state_2='Enable'
> *.log_buffer=268435456
33a45
> *.standby_file_management='AUTO'
[oracle@orcl19s ~]$

Not all parameters that are needed for data guard creation was set (like LOG_ARCHIVE_CONFIG), but most of them are there. Be aware that you can’t modify the values for these parameters when you call the command (like db_files parameter), you need to adjust them later. You can see more detailed info in the official doc, and the command PREPARE DATABASE FOR DATA GUARD for 12.2+ databases since the dgmgrl is for Broker and not attached to any database at all.

Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications were removed to allow reach the generic audience and to be useful for the community. Post protected by copyright.”