The Geek Diary

Oracle DGMGRL (Data Guard Manager) Command Line Reference (Cheat Sheet)

by

Using DGMGRL, you can change property values directly by using the command-line utility. It includes commands to create an observer process that monitors the whole configuration, including the primary and standby, to evaluate if a failover is necessary, and to initiate FSFO. It’s also possible to add new standby databases to the configuration. Instead of managing primary and standby databases with various SQL*Plus statements, the broker provides a single, unified interface.

From the command utility DGMGRL, you can obtain a list of all of the commands supported with the help command as follows:

DGMGRL> help

To access DGMGRL, type dgmgrl at the command prompt and the CLI will start up and return a DGMGRL> prompt:

$ dgmgrl
DGMGRL>

You can use below options are well to connect to DGMGRL:

$ dgmgrl [-silent | -echo] [username/password[@connect_identifier] [dgmgrl_command]]
$ dgmgrl  /
$ dgmgrl sys/pwd
$ dgmgrl sys/pwd@oltp
$ dgmgrl sys/test@dgprimary "show database 'prod'"
$ dgmgrl -logfile observer.log / "stop observer"
$ dgmgrl -silent sys/test@dgprimary "show configuration verbose"
$ dgmgrl  / "show configuration verbose"

ADD

Adds a standby database to the broker configuration:

DGMGRL> ADD DATABASE db_name [AS CONNECT IDENTIFIER IS conn_identifier] [MAINTAINED AS {PHYSICAL|LOGICAL}];
DGMGRL> ADD DATABASE 'testdb' AS CONNECT IDENTIFIER IS testdb MAINTAINED AS PHYSICAL;
DGMGRL> ADD DATABASE 'logdb' AS CONNECT IDENTIFIER IS logdb MAINTAINED AS LOGICAL;
DGMGRL> ADD DATABASE 'devdb' AS CONNECT IDENTIFIER IS devdb.foo.com;

CONNECT

Connects to an Oracle database instance:

DGMGRL> CONNECT username/password[@connect_identifier]
DGMGRL> CONNECT /
DGMGRL> CONNECT sys;
DGMGRL> CONNECT sys@test;
DGMGRL> CONNECT sys/pwd;
DGMGRL> CONNECT sys/pwd@dwh;
DGMGRL> CONNECT /@dwh;
$ dgmgrl connect sys

CONVERT

Converts a database from one type to another (from Oracle 11g):

DGMGRL> CONVERT DATABASE database_name TO {SNAPSHOT STANDBY|PHYSICAL STANDBY};
DGMGRL> CONVERT DATABASE 'devdb' to SNAPSHOT STANDBY;
DGMGRL> CONVERT DATABASE 'devdb' to PHYSICAL STANDBY;

CREATE

Creates a broker configuration:

DGMGRL> CREATE CONFIGURATION config_name AS PRIMARY DATABASE IS db_name CONNECT IDENTIFIER IS conn_ident;
DGMGRL> CREATE CONFIGURATION 'dg' AS PRIMARY DATABASE IS 'prod' CONNECT IDENTIFIER IS prod.foo.com;
DGMGRL> CREATE CONFIGURATION 'dg_test' AS PRIMARY DATABASE IS 'test' CONNECT IDENTIFIER IS test;

DISABLE

Disables a configuration, a database, or fast-start failover (FSFO):

DGMGRL> DISABLE CONFIGURATION;
DGMGRL> DISABLE CONFIGURATION;
DGMGRL> DISABLE DATABASE database_name;
DGMGRL> DISABLE DATABASE 'devdb';
DGMGRL> DISABLE FAST_START FAILOVER [FORCE | CONDITION condition];
DGMGRL> DISABLE FAST_START FAILOVER;
DGMGRL> DISABLE FAST_START FAILOVER FORCE;
DGMGRL> DISABLE FAST_START FAILOVER CONDITION '1578';

EDIT

Edits a configuration, database, or instance:

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS {MaxProtection|MaxAvailability|MaxPerformance};
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;
DGMGRL> EDIT CONFIGURATION SET PROPERTY property_name = value;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 45;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverAutoReinstate = FALSE;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverAutoReinstate = TRUE;
DGMGRL> EDIT CONFIGURATION SET PROPERTY BYSTANDERSFOLLOWROLECHANGE= 'NONE';
DGMGRL> EDIT DATABASE database_name SET PROPERTY property_name = value;
DGMGRL> EDIT DATABASE devdb SET PROPERTY 'LogArchiveFormat'='log_%t_%s_%r_%d.arc';
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=SYNC;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=ASYNC;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=ARCH;
DGMGRL> EDIT DATABASE devdb SET PROPERTY LogShipping=OFF;
DGMGRL> EDIT DATABASE devdb SET PROPERTY LogShipping=ON;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogArchiveTrace=8;
DGMGRL> EDIT DATABASE prodb SET PROPERTY NetTimeout=60;
DGMGRL> EDIT DATABASE devdb SET PROPERTY 'ReopenSecs'=300;
DGMGRL> EDIT DATABASE prodb SET PROPERTY ArchiveLagTarget=1200;
DGMGRL> EDIT DATABASE prodb SET PROPERTY FastStartFailoverTarget='standby_name';
DGMGRL> EDIT DATABASE devdb SET PROPERTY 'StandbyArchiveLocation'='/oradata/archive/';
DGMGRL> EDIT DATABASE devdb SET PROPERTY 'DbFileNameConvert' = '/u01/od01/datafile/, /oradisk/od01/datafile/';
DGMGRL> EDIT DATABASE testdb SET PROPERTY DelayMins='720';
DGMGRL> EDIT DATABASE prodb SET PROPERTY RedoCompression ='ENABLE'
DGMGRL> EDIT DATABASE prodb SET PROPERTY RedoCompression ='DISABLE'
DGMGRL> EDIT DATABASE testdb SET PROPERTY LogArchiveMinSucceedDest =1
DGMGRL> EDIT DATABASE database_name RENAME TO new database_name;
DGMGRL> EDIT DATABASE 'devdbb' RENAME TO 'devdb';
DGMGRL> EDIT DATABASE database_name SET STATE = state [WITH APPLY INSTANCE = instance_name];
DGMGRL> EDIT DATABASE devdb SET STATE='READ-ONLY';
DGMGRL> EDIT DATABASE devdb SET STATE='OFFLINE';
DGMGRL> EDIT DATABASE devdb SET STATE='APPLY-OFF';
DGMGRL> EDIT DATABASE devdb SET STATE='APPLY-ON';
DGMGRL> EDIT DATABASE devdb SET STATE='TRANSPORT-OFF';
DGMGRL> EDIT DATABASE devdb SET STATE='TRANSPORT-ON';
DGMGRL> EDIT DATABASE prodb SET STATE='LOG-TRANSPORT-OFF';
DGMGRL> EDIT DATABASE devdb SET STATE='ONLINE' WITH APPLY INSTANCE=devdb2;
DGMGRL> EDIT INSTANCE instance_name [ON DATABASE database_name] SET AUTO PFILE [={init_file_path|OFF}];
DGMGRL> EDIT INSTANCE 'devdb1' ON DATABASE 'devdb' SET AUTO PFILE='initdevdb1.ora';
DGMGRL> EDIT INSTANCE instance_name [ON DATABASE database_name] SET PROPERTY property_name = value;
DGMGRL> EDIT INSTANCE * ON DATABASE database_name SET PROPERTY property_name = value;
DGMGRL> EDIT INSTANCE 'proddb' ON DATABASE 'proddb' SET PROPERTY 'StandbyArchiveLocation'='/oradata/arch/';
ENABLE - Enables a configuration, a database, or fast-start failover (FSFO).
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> ENABLE DATABASE database_name;
DGMGRL> ENABLE DATABASE 'devdb';
DGMGRL> ENABLE FAST_START FAILOVER [CONDITION condition];
DGMGRL> ENABLE FAST_START FAILOVER;
DGMGRL> ENABLE FAST_START FAILOVER CONDITION '1578';
DGMGRL> ENABLE FAST_START FAILOVER CONDITION "Stuck Archiver";
DGMGRL> ENABLE FAST_START FAILOVER CONDITION 'Corrupted Controlfile';
DGMGRL> ENABLE FAST_START FAILOVER CONDITION 'Corrupted Dictionary';
DGMGRL> ENABLE FAST_START FAILOVER CONDITION 'Inaccessible Logfile';

EXIT

Exits the program.

DGMGRL> EXIT;

FAILOVER

Changes a standby database to be the primary database.

DGMGRL> FAILOVER TO standby_database_name [IMMEDIATE]
DGMGRL> FAILOVER TO "testdb";
DGMGRL> FAILOVER TO "snapdb" IMMEDIATE;

HELP

Displays description and syntax for a command.

DGMGRL> HELP [command];
DGMGRL> HELP REINSTATE
DGMGRL> HELP EDIT

QUIT

Exits the program.

DGMGRL> QUIT;

REINSTATE

Changes a database marked for reinstatement into a viable standby.

DGMGRL> REINSTATE DATABASE database_name;
DGMGRL> REINSTATE DATABASE prim1;

REM

Comment to be ignored by DGMGRL.

DGMGRL> REM [comment];

REMOVE

Removes a configuration, Oracle database, or instance.

DGMGRL> REMOVE CONFIGURATION [PRESERVE DESTINATIONS];
DGMGRL> REMOVE CONFIGURATION;
DGMGRL> REMOVE CONFIGURATION PRESERVE DESTINATIONS;
DGMGRL> REMOVE DATABASE database_name [PRESERVE DESTINATIONS];
DGMGRL> REMOVE DATABASE devdb;
DGMGRL> REMOVE DATABASE standby PRESERVE DESTINATIONS;
DGMGRL> REMOVE INSTANCE instance_name [ON DATABASE database_name];
DGMGRL> REMOVE INSTANCE inst1 ON DATABASE racdb;

SHOW

Displays information about a configuration, database, instance or FSFO.

DGMGRL> SHOW CONFIGURATION [VERBOSE];
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW CONFIGURATION VERBOSE;
DGMGRL> SHOW DATABASE [VERBOSE] db_name [property_name];
DGMGRL> SHOW DATABASE 'devdb';
DGMGRL> SHOW DATABASE VERBOSE 'test';
DGMGRL> SHOW DATABASE 'dwhdb' 'StatusReport';
DGMGRL> SHOW DATABASE 'proddb' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'proddb' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'proddb' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'testdb' 'ArchiveLagTarget';
DGMGRL> SHOW DATABASE 'testdb' 'LogShipping';
DGMGRL> SHOW DATABASE 'testdb' 'PreferredApplyInstance';
DGMGRL> SHOW DATABASE 'proddb' 'StatusReport';
DGMGRL> SHOW DATABASE 'testdb' 'RecvQEntries';
DGMGRL> SHOW DATABASE 'proddb' 'SendQEntries';
DGMGRL> SHOW INSTANCE [VERBOSE] instance_name [property_name] [ON DATABASE db_name];
DGMGRL> SHOW INSTANCE inst1;
DGMGRL> SHOW INSTANCE VERBOSE inst3;
DGMGRL> SHOW INSTANCE testdb 'TopWaitEvents';
DGMGRL> SHOW FAST_START FAILOVER;
DGMGRL> SHOW FAST_START FAILOVER;

Note: From 18c Oracle Database, SHOW ALL command shows the values of DGMGRL command line utility properties.

DGMGRL> SHOW ALL;
   debug ON
   echo OFF
   time OFF
   observerconfigfile = observer.ora

SHUTDOWN

Shuts down a currently running Oracle instance.

DGMGRL> SHUTDOWN [NORMAL | IMMEDIATE | ABORT];
DGMGRL> SHUTDOWN;
DGMGRL> SHUTDOWN NORMAL;
DGMGRL> SHUT IMMEDIATE;
DGMGRL> SHUT ABORT;

SQL

Executes a SQL statement:

DGMGRL> SQL "sql_statement";

START

Starts the fast-start failover(FSFO) observer.

DGMGRL> START OBSERVER [FILE=observer_configuration_file];
DGMGRL> START OBSERVER;

STARTUP

Starts an Oracle database instance.

DGMGRL> STARTUP [RESTRICT] [FORCE] [PFILE=filespec]
[NOMOUNT  |  MOUNT | OPEN [READ ONLY|READ WRITE]];
DGMGRL> STARTUP;
DGMGRL> STARTUP NOMOUNT;
DGMGRL> STARTUP MOUNT;
DGMGRL> STARTUP OPEN;
DGMGRL> STARTUP FORCE;
DGMGRL> STARTUP FORCE RESTRICT NOMOUNT;
DGMGRL> STARTUP PFILE=initdwh.ora NOMOUNT;

STOP

Stops the fast-start failover(FSFO) observer.

DGMGRL> STOP OBSERVER;

SWITCHOVER

Switches roles between a primary and standby database.

DGMGRL> SWITCHOVER TO standby_database_name;
DGMGRL> SWITCHOVER TO "standby";

VALIDATE

command to checks whether the database is ready for a role transition or not.

DGMGRL> VALIDATE DATABASE ...;    -- From Oracle Database 12c
DGMGRL> VALIDATE DATABASE standby-database-name SPFILE;   -- From Oracle Database 18c
DGMGRL> VALIDATE NETWORK CONFIGURATION FOR { ALL | member name };
DGMGRL> VALIDATE NETWORK CONFIGURATION FOR stdby;
DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR { ALL | database name };
DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR stdby;

Log Files

$BDUMP/drc*.log
$ORACLE_HOME/rdbms/log/drc*.log

Other Commands

alter system set dg_broker_start=false;
alter system set dg_broker_start=false sid='*';
alter system set dg_broker_start=FALSE SCOPE=spfile SID='*';
alter system set dg_broker_start=true;
alter system set dg_broker_start=true sid='*';
alter system set dg_broker_start=TRUE SCOPE=spfile SID='*';
alter system set dg_broker_config_file1='/u01/dg_broker_config_files/dr1TESTP.dat' sid='*';
alter system set dg_broker_config_file2='/u01/dg_broker_config_files/dr2TESTP.dat' sid='*';

You May Also Like