DGMGRL

[oracle@11gPrimary dbs]$ dgmgrl

DGMGRL for Linux: Version 11.1.0.6.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
1.) Creating Data Guard Broker configuration for the primary database. Next, I’ll use the CREATE CONFIGURATION command to initialize a Data Guard Broker configuration named MAA_orcl, and confirm its creation with the SHOW CONFIGURATION command:

DGMGRL> CREATE CONFIGURATION 'MAA_orcl' AS
> PRIMARY DATABASE IS primary_db
> CONNECT IDENTIFIER IS orcl;

Configuration "MAA_orcl" created with primary database "primary_db"

DGMGRL> show configuration

Configuration
Name: MAA_orcl
Enabled: NO
Protection Mode: MaxPerformance
Databases:
primary_db - Primary database

Fast-Start Failover: DISABLED

Current status for "MAA_orcl":
DISABLED
2.) Adding a standby database to the Data Guard Broker configuration. To add a standby database to the current configuration, I’ll use the ADD DATABASE command. Note that I could add up to eight additional standby databases to the configuration using DGMGRL:

DGMGRL> ADD DATABASE 'stdby_db' AS
> CONNECT IDENTIFIER IS stdby;

Database "stdby_db" added
I’ll then use DGMGRL’s SHOW CONFIGURATION and SHOW DATABASE commands to confirm the configuration before I enable it:

DGMGRL> show configuration verbose;

Configuration
Name: MAA_orcl
Enabled: NO
Protection Mode: MaxPerformance
Databases:
primary_db - Primary database
stdby_db - Physical standby database

Fast-Start Failover: DISABLED

Current status for "MAA_orcl":
DISABLED

DGMGRL> show database stdby_db

Database
Name: stdby_db
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: OFFLINE
Instance(s):
orcl

Current status for "stdby_db":
DISABLED
4.) Enabling the Data Guard Broker configuration. Lastly, I’ll issue the ENABLE CONFIGURATION command to activate the configuration and then confirm the successful activation of the primary and standby databases via the SHOW DATABASE command:

DGMGRL> enable configuration;

Enabled.

DGMGRL> show database primary_db;

Database
Name: primary_db
Role: PRIMARY
Enabled: YES
Intended State: TRANSPORT-ON
Instance(s):
orcl

Current status for "primary_db":
SUCCESS

DGMGRL> show database stdby_db;

Database
Name: stdby_db
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: APPLY-ON
Instance(s):
orcl

Current status for "stdby_db":
SUCCESS
One of the beautiful things about Data Guard Broker is that it automatically creates and issues all necessary ALTER SYSTEM commands to enable the required Data Guard configurations on each database in the configuration. I’ve showed the results of enabling the Data Guard Broker configuration from the primary and standby databases’ alert logs in Listing 2.3.

Performing a Switchover between Primary and Standby Databases
If you’ve ever performed a switchover operation between a primary and a standby database without Data Guard Broker, I’m sure you’ll agree that it’s a tedious, manually-intensive process that involves several distinct steps which must be performed carefully and in precise order to insure a successful role transition. Here’s a brief summary of the steps required:

1.) Check the status of the primary database. Column V$DATABASE.SWITCHOVER_STATUS on the primary database must reflect the proper state of the primary database. Either a value of TO_STANDBY or SESSIONS_ACTIVE indicates that a switchover is possible; otherwise, redo transport services haven’t been configured properly.

2.) Start the switchover process on the primary database. Next, the standby database has to be prepared to handle switchover by issuing the appropriate switchover command from SQL*Plus. During this process, the primary database is prepared to assume the standby role, and its control file is backed up to make sure it could be reconstructed in case a failure occurs.

-- If no sessions are active ...
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
-- ... but if sessions are active:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
3.) Shut down, then mount, the original primary database. Once the role transition is complete on the original primary (and soon to become the standby) database, it’s time to shut it down and mount it.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
4.) Check that the target database’s status is ready for switchover. The target standby database must be ready to accept its new primary role. Column V$DATABASE.SWITCHOVER_STATUS on this database must contain a value of either TO­_PRIMARY or SESSIONS_ACTIVE to indicate that a switchover is possible; otherwise, redo transport services haven’t been configured properly.

5.) Switch the target database to its new primary role. Depending on the result from the previous step, the appropriate command needs to be issued on the (soon to be) primary database from SQL*Plus:

-- If no sessions are active ...
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
-- ... but if sessions are active:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
6.) Open the new primary database. It’s time to activate the new primary database from SQL*Plus:

SQL> ALTER DATABASE OPEN;
7.) Make the original primary database into the standby database. To complete the role transition, the original primary database must be enabled to accept its new standby role.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE
DISCONNECT FROM SESSION;
In this relatively simple example of a role transition, notice that I haven’t discussed what to do if there’s a failure at any point during this process. Frankly, I’d much rather have a lot more insurance should the process fail at any point … and that’s where Data Guard Broker comes in.

Example: Using Data Guard Broker to Perform a Switchover
Data Guard Broker makes extremely short work of role transitions like a switchover. Once I’ve connected to DGMGRL as the SYS user on the primary database server, all I need to do is issue one simple command, SWITCHOVER TO :

[oracle@11gPrimary dbs]$ dgmgrl
DGMGRL for Linux: Version 11.1.0.6.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.

DGMGRL> SWITCHOVER TO stdby_db;

Performing switchover NOW, please wait...
New primary database "stdby_db" is opening...
Operation requires shutdown of instance "orcl" on database "primary_db"
Shutting down instance "orcl"...
ORA-1109: database not open
Database dismounted.
ORACLE instance shut down.

Operation requires startup of instance "orcl" on database "stdby_db"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "stdby_db"
I’ve showed the results of this switchover operation from the perspective of the primary and standby databases’ alert logs in Listing 2.4.

Example: Using Data Guard Broker to Perform a Switchback
Data Guard Broker also makes it simple to revert the original primary and standby database back to their original roles. Once again, I’ll connect to DGMGRL as the SYS user on the original primary database server and just issue the SWITCHOVER TO command to revert to the original Data Guard configuration:

[oracle@11gPrimary dbs]$ dgmgrl
DGMGRL for Linux: Version 11.1.0.6.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.

DGMGRL> SWITCHOVER TO primary_db;

Performing switchover NOW, please wait...
New primary database "stdby_db" is opening...
Operation requires shutdown of instance "orcl" on database "stdby_db"
Shutting down instance "orcl"...
ORA-1109: database not open
Database dismounted.
ORACLE instance shut down.

Operation requires startup of instance "orcl" on database "primary_db"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "primary_db"