Infrastructure at your Service

Mouhamadou Diaw

Oracle 20c : The new PREPARE DATABASE FOR DATA GUARD

As you may know, Oracle 20c is in the cloud with new features. The one I have tested is the PREPARE DATABASE FOR DATA GUARD.
This command configures a database for use as a primary database in a Data Guard broker configuration. Database initialization parameters are set to recommended values.
Let’s see what this command will do for us
The db_unique_name of the primary database is prod20 and in the Data Guard I will build, the db_unique_name will be changed to prod20_site1.

SQL> show parameter db_unique_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 prod20
SQL> 

Now let’s connect to the broker can run the help to see the syntax

[[email protected] ~]$ dgmgrl
DGMGRL for Linux: Release 20.0.0.0.0 - Production on Tue Feb 18 21:36:39 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected to "prod20_site1"
Connected as SYSDG.
DGMGRL> 
 
DGMGRL> help prepare    

Prepare a primary database for a Data Guard environment.

Syntax:

  PREPARE DATABASE FOR DATA GUARD
    [WITH [DB_UNIQUE_NAME IS ]
          [DB_RECOVERY_FILE_DEST IS ]
          [DB_RECOVERY_FILE_DEST_SIZE IS ]
          [BROKER_CONFIG_FILE_1 IS ]
          [BROKER_CONFIG_FILE_2 IS ]];

And then run the command

DGMGRL> PREPARE DATABASE FOR DATA GUARD with DB_UNIQUE_NAME is prod20_site1;
Preparing database "prod20" for Data Guard.
Initialization parameter DB_UNIQUE_NAME set to 'prod20_site1'.
Initialization parameter DB_FILES set to 1024.
Initialization parameter LOG_BUFFER set to 268435456.
Primary database must be restarted after setting static initialization parameters.
Shutting down database "prod20_site1".
Database closed.
Database dismounted.
ORACLE instance shut down.
Starting database "prod20_site1" 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.
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 FLASHBACK ON.
Database opened.
DGMGRL> 

The output shows the changes done by the PREPARE command. We can do some checks

SQL> show parameter db_unique_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 prod20_site1
SQL> select flashback_on,force_logging from v$database;

FLASHBACK_ON	   FORCE_LOGGING
------------------ ---------------------------------------
YES		   YES

SQL> 

SQL> show parameter standby_file

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
standby_file_management 	     string	 AUTO
SQL> 

But here I can see that I only have 3 standby redo log groups instead of 4 (as I have 3 redo log groups)

SQL> select bytes,group# from v$log;

     BYTES     GROUP#
---------- ----------
 209715200	    1
 209715200	    2
 209715200	    3

SQL> 


SQL> select group#,bytes from v$standby_log;

    GROUP#	BYTES
---------- ----------
	 4  209715200
	 5  209715200
	 6  209715200

SQL> 

After building the Data Guard I did some checks (note that steps not shown here but the same that other version)
For the configuration

DGMGRL> show configuration verbose;

Configuration - prod20

  Protection Mode: MaxPerformance
  Members:
  prod20_site1 - Primary database
    prod20_site2 - 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    = 'prod20_CFG'
    ConfigurationSimpleName         = 'prod20'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

For the primary database

DGMGRL> show database verbose 'prod20_site1';

Database - prod20_site1

  Role:                PRIMARY
  Intended State:      TRANSPORT-ON
  Instance(s):
    prod20

  Properties:
    DGConnectIdentifier             = 'prod20_site1'
    ObserverConnectIdentifier       = ''
    FastStartFailoverTarget         = ''
    PreferredObserverHosts          = ''
    LogShipping                     = 'ON'
    RedoRoutes                      = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    ArchiveLocation                 = ''
    AlternateLocation               = ''
    StandbyArchiveLocation          = ''
    StandbyAlternateLocation        = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'oraadserver'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraadserver)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod20_site1_DGMGRL)(INSTANCE_NAME=prod20)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/prod20_site1/prod20/trace/alert_prod20.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/prod20_site1/prod20/trace/drcprod20.log

Database Status:
SUCCESS

DGMGRL> 

For the standby database

DGMGRL> show database verbose 'prod20_site2';

Database - prod20_site2

  Role:                PHYSICAL STANDBY
  Intended State:      APPLY-ON
  Transport Lag:       0 seconds (computed 1 second ago)
  Apply Lag:           0 seconds (computed 1 second ago)
  Average Apply Rate:  2.00 KByte/s
  Active Apply Rate:   0 Byte/s
  Maximum Apply Rate:  0 Byte/s
  Real Time Query:     OFF
  Instance(s):
    prod20

  Properties:
    DGConnectIdentifier             = 'prod20_site2'
    ObserverConnectIdentifier       = ''
    FastStartFailoverTarget         = ''
    PreferredObserverHosts          = ''
    LogShipping                     = 'ON'
    RedoRoutes                      = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    ArchiveLocation                 = ''
    AlternateLocation               = ''
    StandbyArchiveLocation          = ''
    StandbyAlternateLocation        = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'oraadserver2'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraadserver2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD20_SITE2_DGMGRL)(INSTANCE_NAME=prod20)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/prod20_site2/prod20/trace/alert_prod20.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/prod20_site2/prod20/trace/drcprod20.log

Database Status:
SUCCESS

DGMGRL> 

Conclusion

I am sure that you will adopt this nice command.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mouhamadou Diaw
Mouhamadou Diaw

Consultant