DGMGRL – Scenarios(Command Line) –11gR2

Prerequisites for Getting Started

1. Server parameter file should be used by databases (Primary & all standby databases in the configuration)

2. The DG_BROKER_START initialization parameter must be set to TRUE for all databases in the configuration. Setting to true will start the DMON process

SQL> alter system set DG_BROKER_START=TRUE scope=both

Note :

The scenarios here assume the following broker configuration:

• The configuration name is 'dgconfig'
• The database unique name (DB_UNIQUE_NAME) of the primary database is bharatdb.
• The database unique name (DB_UNIQUE_NAME) of the remote standby database is bharatdb_stan.
• The protection mode is maximum performance mode.
• There are standby redo log files configured for both the primary and standby database. The transport mode for both databases is ASYNC.
• The standby database is a physical standby database.

Scenario 1: Creating a Configuration

Step 1: Invoke DGMGRL.

$ dgmgrl

DGMGRL>

Step 2: Connect to the primary database.

a) Connecting to local primary database

DGMGRL> CONNECT sys;

Password: password

Connected.

b) Connecting to the Primary Database on a Remote System

DGMGRL> CONNECT sys@bharatdb

Password: password

Connected.

Step 3: Create the broker configuration.

DGMGRL> CREATE CONFIGURATION ‘dgconfig’ AS

PRIMARY DATABASE IS ‘bharatdb’

CONNECT IDENTIFIER IS bharatdb;

Configuration “dgconfig” created with primary database “bharatdb”

Step 4: Add a standby database to the configuration.

DGMGRL> ADD DATABASE ‘bharatdb_stan’ AS CONNECT IDENTIFIER IS bharatdb_stan;

Database “bharatdb_stan” added

Note:

The names for the primary and standby databases must match their database unique names.

Step 5: Show the configuration information.

DGMGRL> SHOW CONFIGURATION;

Configuration – dgconfig

Protection Mode: MaxPerformance

Databases:

bharatdb – Primary database

bharatdb_stan – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

Scenario 2: Setting Database Properties

After the creation of the configuration, database properties can be set at any time.

DGMGRL> EDIT DATABASE ‘bharatdb’ SET PROPERTY ‘LogArchiveFormat’=’bharat_%s_%t_%r.arc’;

Property “LogArchiveFormat” updated

DGMGRL> edit database ‘bharatdb_stan’ set property ‘StandbyArchiveLocation’=’/u01/ofaroot/bharatdb/archive’;

Property “StandbyArchiveLocation” updated

Note:

If broker management of the database is enabled, setting a database property value causes the underlying parameter value to be changed in the corresponding database, and the value for the changed parameter is reflected in the server parameter file. Thus, if the database is shut down and restarted outside of Oracle Enterprise Manager and DGMGRL (such as from the SQL*Plus interface), the database uses the new parameter values from the updated server parameter file when it starts.

But you should not make changes to the redo transport services initialization parameters through SQL statements. Doing so will cause an inconsistency between the database and the broker.

Scenario 3: Enabling the Configuration and Databases

You can enable:

The entire configuration, including all of its databases or a a standby database

a) Enable the entire configuration.

You can enable the entire configuration, including all of the databases, with the following command:

DGMGRL> ENABLE CONFIGURATION;
Enabled.

DGMGRL> show configuration

Configuration – dgconfig
Protection Mode: MaxPerformance
Databases:
bharatdb      – Primary database
bharatdb_stan – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:SUCCESS

b)Enable the database.

This step is unnecessary except if the standby database was previously disabled with the DISABLE DATABASE command. Normally, enabling the configuration also enables the standby database.

DGMGRL> enable database ‘bharatdb_stan’;
Enabled.

Show the database.

DGMGRL> show database bharatdb_stan

Database – bharatdb_stan
Role:            PHYSICAL STANDBY
Intended State:  APPLY-ON
Transport Lag:   0 seconds
Apply Lag:       0 seconds
Real Time Query: OFF
Instance(s): bharatdb
Database Status:SUCCESS

Scenario 4: Setting the Configuration Protection Mode

Protection mode of the configuration can be changed at any time. But it is best when there is no activity occurring in the configuration if you are moving to the maximum protection or maximum availability modes.

Note:
If you change the protection mode from maximum performance mode to maximum protection mode, the broker automatically restarts the primary database. If you wish to avoid restarting the database, first change the protection mode to maximum availability mode and then change the protection mode to maximum protection mode.

A restart of the primary database is not required when changing the protection mode from:
maximum performance to maximum availability
maximum availability to maximum protection

This scenario sets the protection mode of the configuration to the MAXAVAILABILITY mode.  This protection mode requires that there be at least one standby database configured to use standby redo log files, with its LogXptMode database property set to SYNC.

Step 1 :  Configure standby redo log files, if necessary.

Because you will be setting the protection mode to the MAXAVAILABILITY mode, it is important to ensure that sufficient standby redo log files are configured on the standby database.

Step 2 :  Set the LogXptMode database property appropriately.

If the protection mode to be set is MAXAVAILABILITY, it is required that the redo transport service of at least one standby database is set to SYNC. For example:

DGMGRL> edit database ‘bharatdb_stan’ set property ‘LogXptMode’=’SYNC’;
Property “LogXptMode” updated

Step 3 :  Change the overall protection mode for the configuration.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.

Step 4:   Verify the protection mode has changed.

DGMGRL> show configuration
Configuration – dgconfig
Protection Mode: MaxAvailability
Databases:
bharatdb      – Primary database
bharatdb_stan – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:SUCCESS

Scenario 5: Enabling Fast-Start Failover and Starting the Observer

You can enable fast-start failover from any site, including the observer site, while connected to any database in the broker configuration. Enabling fast-start failover does not trigger a failover. Instead, it allows the observer that is monitoring the configuration to initiate a fast-start failover if conditions warrant a failover.

Fast-start failover can be enabled in configurations operating in either maximum performance or maximum availability protection mode. This section describes the steps to enable fast-start failover and start the observer where the configuration protection mode is set to maximum availability mode.

Step 1:   Ensure standby redo logs are configured on the primary and target standby databases.

Standby redo logs must be configured on the primary and standby databases. You must stop log apply services prior to configuring standby redo logs.

Step 2:   Ensure the LogXptMode Property is set to SYNC.

DGMGRL> edit database ‘bharatdb_stan’ set property ‘LogXptMode’=’SYNC’;
Property “LogXptMode” updated

DGMGRL> edit database ‘bharatdb’ set property ‘LogXptMode’=’SYNC’;
Property “LogXptMode” updated

Step 3:   Set the FastStartFailoverTarget configuration property.

DGMGRL> edit database ‘bharatdb’ set PROPERTY fastStartFailoverTarget=’bharatdb_stan’;
Property “faststartfailovertarget” updated

DGMGRL> edit database ‘bharatdb_stan’ set PROPERTY FastStartFailoverTarget=’bharat’;
Property “faststartfailovertarget” updated

Step 4:   Upgrade the protection mode to MAXAVAILABILITY, if necessary.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Step 5:   Enable Flashback Database on the primary and target standby databases, if necessary.

If it is not already enabled on the primary and standby databases, enable Flashback Database by issuing the following statements on each database:

ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
ALTER SYSTEM SET UNDO_MANAGEMENT=’AUTO’ SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
SHOW PARAMETER UNDO;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH;
ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SET db_recovery_file_dest_size=<size>;
ALTER SYSTEM SET db_recovery_file_dest=<directory-specification>;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

Ensure the UNDO_RETENTION and DB_FLASHBACK_RETENTION_TARGET initialization parameters are set to sufficiently large values so that reinstatement is still possible after a prolonged outage.

Step 6:   Start the observer.

Start the observer by logging into the observer computer and running DGMGRL.

Note that the command does not return; that is you will not get the DGMGRL prompt after issuing the command.

DGMGRL> CONNECT /
Connected.
DGMGRL> START OBSERVER;
Observer started

Step 7:   Enable fast-start failover.

Can be enabled while connected to any database system in the broker configuration.

DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.

Step 8:   Verify the fast-start failover configuration.

DGMGRL> show fast_start failover
Fast-Start Failover: ENABLED

Threshold:        30 seconds
Target:           bharatdb_stan
Observer:         ny02.xerox.com
Lag Limit:        30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate:   TRUE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile          YES
Corrupted Dictionary           YES
Inaccessible Logfile            NO
Stuck Archiver                  NO
Datafile Offline               YES
Oracle Error Conditions:
(none)

DGMGRL> SHOW DATABASE ‘bharatdb’ FastStartFailoverTarget;
FastStartFailoverTarget = ‘bharatdb_stan’

DGMGRL> SHOW DATABASE ‘bharatdb_stan’ FastStartFailoverTarget;
FastStartFailoverTarget = ‘bharatdb’

Scenario 6: Performing Routine Management Tasks

Alter a Database Property

You can modify the values of database properties at any time—if the database is enabled or disabled.

DGMGRL> EDIT DATABASE ’bharatdb’ SET PROPERTY ‘LogXptMode’=ASYNC;
Property “LogXptMode” updated

If the configuration is currently disabled, the database does not use the new property value until you enable the broker configuration with the ENABLE CONFIGURATION command.

Alter the State of a Standby Database

To temporarily stop Redo Apply on a physical standby.

Altering a Standby Database State
DGMGRL> EDIT DATABASE ‘bharatdb_stan’ SET STATE=’APPLY-OFF’;
Succeeded.

Redo data is still being received when you put the physical standby database in the APPLY-OFF state.

Alter the State of a Primary Database

To stop the transmittal of redo data to the standby database.

DGMGRL> EDIT DATABASE bharatdb SET STATE=TRANSPORT-OFF;
Succeeded.

To change the state of the primary database back to TRANSPORT-ON, do the following:

DGMGRL> EDIT DATABASE bharatdb SET STATE=TRANSPORT-ON;
Succeeded.

Disabling the Configuration and Primary Database

The only way to disable broker management of the primary database is to use the DISABLE CONFIGURATION command; the DISABLE DATABASE command only disables management of a standby database.
DGMGRL> DISABLE CONFIGURATION;

Disabling the broker’s management of an object does not remove its profile from the broker configuration file.

Disable a Standby Database

You use the DISABLE DATABASE command when you temporarily do not want the broker to manage and monitor a standby database.

DGMGRL> DISABLE DATABASE ‘South_Sales’;
Disabled.
or
DGMGRL> DISABLE DATABASE;
Disabled.

Removing the Configuration or a Standby Database

REMOVE CONFIGURATION –> To delete entire configuration
REMOVE DATABASE      –> To delete standby database profile from the broker configuration file

Steps:
1. Disable fast_start failover initially
DGMGRL> disable fast_start failover
Disabled.

2. Change protection mode to MaxPerformance

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS   MAXPERFORMANCE;
Succeeded.

DGMGRL> show configuration

Configuration – dgconfig

Protection Mode: MAXPERFORMANCE
Databases:
bharatdb      – Primary database
bharatdb_stan – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
Success

3. Removing standby database from configuration

DGMGRL> remove database bharatdb_stan
Removed database “bharatdb_stan” from the configuration

DGMGRL> show configuration

Configuration – dgconfig

Protection Mode: MaxPerformance
Databases:
bharatdb – Primary database
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

4. Removing entire configuration

DGMGRL> remove configuration
Removed configuration

DGMGRL> show configuration
ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

By default, the command removes the corresponding broker settings of the LOG_ARCHIVE_DEST_n initialization parameter on the primary database and the LOG_ARCHIVE_CONFIG initialization parameters on all databases in the configuration. To preserve these settings, use the PRESERVE DESTINATIONS option.

Scenario 7: Performing a Switchover Operation

Step 1   Check the primary database.

Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the primary database.
Following properties should be checked carefully

The state of the primary and standby databases are TRANSPORT-ON and APPLY-ON, respectively.
LogXptMode                      = ‘sync’   (If configuration is either maximum availability mode or maximum protection mode)
DbFileNameConvert               = ‘dbs/bt, dbs/t’
LogFileNameConvert              = ‘dbs/bt, dbs/t’
StandbyArchiveLocation          = ‘USE_DB_RECOVERY_FILE_DEST’
LogArchiveFormat                = ‘db1r_%d_%t_%s_%R.arc’

Step2 Check standby database

Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the primary database. Every thing should be good.

Step 3   Issue the switchover command.

DGMGRL> switchover to ‘bharatdb_stan’;

Step 4  Use show configuration command to check the configuration

Scenario 8: Performing a Manual Failover Operation

Failover operation is usually invoked when the primary database cannot be accessed or is unavailable

Note:
If fast-start failover is enabled, you can perform a manual failover only to the standby database that was specified as the target of a fast-start failover and only when the observer is running and currently has connectivity with the standby database.

If you want to perform a manual failover to a standby database that is not the fast-start failover target standby database, you must first disable fast-start failover using the FORCE option on the standby database you want to fail over.

Step 1   Connect to the target standby database.

To perform the failover operation, you must connect to the standby database to which you want to fail over to as a user that has the SYSDBA privilege. For

DGMGRL> CONNECT sys@bharatdb_stan
Password: password
Connected.

Step 2   Issue the failover command.

Now you can issue the failover command to make the target standby database the new primary database for the configuration.

DGMGRL> FAILOVER TO ‘bharatdb_stan’;
Performing failover NOW, please wait…
Failover succeeded, new primary is “bharatdb_stan”

Step 3   Show the configuration.

Issue the SHOW CONFIGURATION command to verify the failover.

DGMGRL> SHOW CONFIGURATION;
Configuration – dgconfig
Protection Mode: MaxAvailability
Databases:
bharatdb_stan  – Primary database
Warning: : database reports a different protection level from the    protection mode

bharatdb – Physical standby database (disabled)
: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

Note that the configuration was operating in maximum availability mode. The protection mode was preserved after the failover. The configuration also has a warning status. The SHOW DATABASE command for the new primary shows that the warning is the result of not having an enabled physical standby database. As a result, the warning status indicates that the protection level of the configuration is not the same as the configured mode.

Step 4   Show the new primary database.

DGMGRL> SHOW DATABASE bharatdb_stan;
Database – bharatdb_stan

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

Database Warning(s):
: database reports a different protection level from the protection mode

Database Status:
WARNING

Step 5   Show the old primary database.It must be re-instated

DGMGRL> SHOW DATABASE ‘bharatdb’;
Database – bharatdb

Role:            PHYSICAL STANDBY
Intended State:  APPLY-ON
Transport Lag:   (unknown)
Apply Lag:       (unknown)
Real Time Query: OFF
Instance(s):
bharatdb

Database Status:
: the standby database needs to be reinstated

Scenario 9: Reinstating a Failed Primary Database

If your primary database had been configured with Flashback Database, you can easily reinstate the failed primary database as a standby database of the new primary database.

Step 1   Restart the Old Primary Database.

% sqlplus sys as sysdba
Enter password: password

SQL> STARTUP MOUNT;

Step 2   Reinstate the old primary database.

% dgmgrl connect sys
Password: password
Connected.
DGMGRL> REINSTATE DATABASE ‘bharatdb’;

After the primary has been reinstated, issue the SHOW CONFIGURATION and SHOW DATABASE commands to confirm that the old primary has been successfully reinstated.

Scenario 10: Converting a Physical Standby to a Snapshot Standby & vice versa

A physical standby database must be configured with a fast recovery area to convert it to a snapshot standby database. This is because a guaranteed restore point is created during the conversion process, and guaranteed restore points require a fast recovery area.

DGMGRL> convert database ‘bharatdb_stan’ to snapshot standby;
Converting database “bharatdb_stan” to a Snapshot Standby database, please wait…
Database “bharatdb_stan” converted successfully

To revert the database back to a physical standby database

DGMGRL> CONVERT DATABASE ‘bharatdb_stan’ to PHYSICAL STANDBY;

Scenario 11: Monitoring a Data Guard Configuration

Check the database status

To identify the warnings on the primary database, show its status using the SHOW DATABASE command:

DGMGRL> SHOW DATABASE ‘bharatdb’;
Database – bharatdb
Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
bharatdb
Warning: : the redo transport service for standby “bharatdb_stan” has an error
Warning: : the value of property LogArchiveTrace is inconsistent with the database setting
Warning: : redo transport-related property ReopenSecs of standby
database “bharatdb_stan” is inconsistent
Database Status:
WARNING

Check the LogXptStatus monitorable database property

The SHOW DATABASE output above shows a Warning for error.To identify the exact transport error, use the LogXptStatus monitorable database property:

DGMGRL> SHOW DATABASE ‘bharatdb’ ‘LogXptStatus’;
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME  STANDBY_DATABASE_NAME  STATUS
bharatdb                                    bharatdb_stan                             TNS:no listener

The output shows that the listener for the physical standby database is not running. To fix this error, start the listener for the physical standby database bharatdb_stan.

Check the InconsistentProperties monitorable database property.

The SHOW DATABASE output above shows a Warning for error.

DGMGRL> SHOW DATABASE ‘bharatdb’ ‘InconsistentProperties’;
INCONSISTENT PROPERTIES
INSTANCE_NAME  PROPERTY_NAME   MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
bharatdb1               LogArchiveTrace         511                                     255          255

The current database memory value (511) is different from both the server parameter file (SPFILE) value (255) and the Data Guard broker’s property value (255). If you decide the database memory value is correct, you can update the Data Guard broker’s property value using the following command:

DGMGRL> EDIT DATABASE ‘bharatdb’ SET PROPERTY ‘LogArchiveTrace’=511;
Property “LogArchiveTrace” updated

This command will result in the broker updating the SPFILE value so that the value of LogArchiveTrace is kept consistent.

Check the InconsistentLogXptProps monitorable database property

To identify the inconsistent values for the redo transport database property, ReopenSecs, you can use the InconsistentLogXptProps monitorable database property:

DGMGRL> SHOW DATABASE ‘bharatdb’ ‘InconsistentLogXptProps’;
INCONSISTENT LOG TRANSPORT PROPERTIES
INSTANCE_NAME      STANDBY_NAME      PROPERTY_NAME  MEMORY_VALUE  BROKER_VALUE
bharatdb_stan1   bharatdb_stan                    ReopenSecs       600           300

The current database memory value (600) is different from the Data Guard broker’s property value (300). If you think the broker’s property value is correct, you can fix the inconsistency by re-editing the property of the standby database with the same value, as shown in the following example:

DGMGRL> EDIT DATABASE ‘bharatdb_stan’ SET PROPERTY ‘ReopenSecs’=300;
Property “ReopenSecs” updated

You can also reenable the standby database or reset the state of the primary database to TRANSPORT-ON to fix this inconsistency.

Check the monitorable property StatusReport

When you see message ORA-16810, you can use the monitorable property StatusReport to identify each of the errors or warnings:

DGMGRL> SHOW DATABASE ‘bharatdb’ ‘StatusReport’;
STATUS REPORT
INSTANCE_NAME   SEVERITY     ERROR_TEXT
bharatdb       ERROR         ORA-16737: The log transport service for standby “bharatdb_stan” has an error.
bharatdb_stan  WARNING     ORA-16714: The value of property LogArchiveTrace is inconsistent with the database  setting
bharatdb        WARNING     ORA-16715: Log transport related property ReopenSecs of standby “DR_Sales” is inconsistent.