Synopsis. Oracle Database 11g offers several enhancements to the Oracle Data Guard feature set that helps an Oracle DBA to manage a complex, multi-database disaster recovery environment. This article – the second in an ongoing series – explores how to set up Data Guard Broker for simpler configuration, monitoring and maintenance of Oracle 11g primary and standby databases.
The prior article in this series demonstrated how to use Oracle 11g’s new Recovery Manager (RMAN) features to construct a standby database environment on a different host from an active primary database. Now that I’ve successfully cloned a copy of the primary database to the standby server, it’s the perfect time to address how to configure, monitor, and maintain relationships between the two database environments, and Oracle 11g’s Data Guard Broker (DGB) utilities offer the simplest method to handle these “command and control” tasks.
Best of all, Data Guard Broker eliminates the need for a complex checklist filled with tediously obscure commands that are demanded during role transitions – say, for instance, a switchover between a primary and a standby database. I built several of these checklists when switchover/switchback became available formally in Oracle 9i, so I know how manually intensive this process can be … and since there’s an excellent chance that I’ll have to perform a switchover and/or switchback under pressure during disaster recovery, I’m certainly in favor of turning over that job to Data Guard Broker in Oracle 11g.
Enabling Data Guard Real-Time Apply
During my cloning operation, I noticed that standby redo logs hadn’t been created properly on the standby database server. I’ll need to correct this before attempting to establish a viable standby environment. While this is not an uncommon issue, it’s also quite simple to repair.
Since the standby database is already in MOUNT state, I’ll just issue the commands in Listing 2.1 from a SQL*Plus session to create the standby redo logs. Since Oracle recommends creating one additional standby redo log group than the total number of online redo log groups for any database that’s going to participate in a Data Guard configuration, note that I’ve created four standby redo log groups for both the primary and the standby databases.
Once the standby redo logs are in place, I can test the standby environment with one simple command from a SQL*Plus session on the standby database. This command tells the standby database that it’s ready to begin receiving and applying archived redo logs from the primary database, and that it will use its standby redo logs to implement the Real-Time Apply feature:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE SQL> USING CURRENT LOGFILE DISCONNECT;
The advantage of Real-Time Apply is that the log apply services on the standby database will use the standby redo logs to apply redo data in real time. In normal redo application, the log apply services on the standby use the archived redo logs themselves to apply the redo to the database’s datafiles. Since I’m using a physical standby database, Oracle 11g uses one or more managed recovery process (MRPn) to apply the redo from the standby redo logs just after the remote file server (RFS) background process has completed writing the redo log to the standby database’s host. This is especially helpful when a really huge archived redo log file has just been received at the standby site; moreover, whenever a switchover or switchback operation is imminent, the log apply services can speed up the application of redo log data, and that in turn speeds up role switching operations.
Prerequisites to Data Guard Broker Configuration
To use Data Guard Broker effectively, I’ll need to adjust my database’s initialization parameters slightly. Some of the adjustments are mandatory, and others I’ve made so that it’ll be easier to track which database and server is participating in which Data Guard operation. I’ve captured these commands and modifications in Listing 2.2:
- To make it easier to track which database is doing what, I decided to change the unique names for my two databases. To accomplish this, I changed DB_UNIQUE_NAME from ORCL to PRIMARY_DB for the original primary site, and I then changed DB_UNIQUE_NAME from STDBY to STDBY_DB for the original standby site. And that’s where things got really interesting! I was also forced to change all settings for these original names for several database initialization parameters, including all of the ones I’d already set up for the archived redo log destinations.
- I also set initialization parameter DG_BROKER_START to a value of TRUE on both databases so that Data Guard Broker background process (DMON) would start automatically whenever a Data Guard configuration was successfully implemented.
- I adjusted the network configuration files on both the primary and standby hosts to incorporate the modifications to DB_UNIQUE_NAME.
- Lastly, I added a static database registration entry on each database server’s TNSNAMES.ORA configuration file so that each database could communicate with the Data Guard Broker Management Utility (DGMGRL). DGMGRL requires an entry named <database_unique_name>_DGMGRL for this to work.
Password File Travails. I also learned a crucial lesson about how to recreate the password file in an Oracle 11g Data Guard environment. While verifying the connection between the renamed primary and standby databases, I began encountering errors when I attempted to start up the Real-Time Apply feature:
. . .
Media Recovery Waiting for thread 1 sequence 112
Completed: alter database recover managed standby database disconnect
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
FAL[client, MRP0]: Error 16191 connecting to orcl for fetching gap sequence
Errors in file /u01/app/oracle/diag/rdbms/stdby_db/orcl/trace/orcl_mrp0_5719.trc:
ORA-16191: Primary log shipping client not logged on standby
. . .
Since the errors appeared to be related to the password file on the standby server, I (mistakenly) recreated it there using the orapwd utility. Unfortunately, that’s an egregious error in the Oracle 11g environment because the password file needs to be copied directly from the primary server to any standby server(s). Moreover, Oracle recommends deactivating case sensitivity in the password file flag and also setting the SEC_CASE_SENSITIVE_LOGON parameter to FALSE to eliminate case sensitivity issues in the password file.
To solve this, I recreated the password file on the primary database’s host using orapwd:
$> orapwd file=$ORACLE_HOME/dbs/orapworcl entries=5 ignorecase=n force=y
I then copied the file over to the same directory on the standby database’s host and restarted the Real Time Apply feature on that server. Success at last!
DGMGRL: Data Guard Command Line Interface
Now that I’ve finished my housekeeping tasks, I’m ready to set up and enable a Data Guard Broker configuration for my primary and standby databases. I’ll use the Data Guard Broker Command Line Interface (DGMGRL) utility to accomplish this. I’ll log into the primary database and connect to DGMGRL as the SYS user:
[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 <database name> 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 <target standby database>:
[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 <target standby database> 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"
I’ve showed the results of this switchback operation from the perspective of the primary and standby databases’ alert logs in Listing 2.5.
Next Steps
In the next article in this series, I’ll explore how to:
- Configure data protection modes between the primary and standby databases
- Set up Oracle Enterprise Manager Grid Control for management of a Data Guard environment
- Perform role transitions using Oracle Enterprise Manager Grid Control
References and Additional Reading
While I’m hopeful that I’ve given you a thorough grounding in the technical aspects of the features I’ve discussed in this article, I’m also sure that there may be better documentation available since it’s been published. I therefore strongly suggest that you take a close look at the corresponding Oracle documentation on these features to obtain crystal-clear understanding before attempting to implement them in a production environment. Please note that I’ve drawn upon the following Oracle Database 11g documentation for the deeper technical details of this article:
B28279-02 Oracle Database 11g New Features Guide
B28294-03 Oracle Database 11g Data Guard Concepts and Administration
B28295-03 Oracle Database 11g Data Guard Broker
B28320-01 Oracle Database 11g Reference Guide
B28419-02 Oracle Database 11g PL/SQL Packages and Types Reference
Also, the following MetaLink documentation helps clarify the topics I’ve discussed in this article:
806703.1 Changing SYS Password Of PRIMARY Database When STANDBY In Place To Avoid ORA-16191
