Back Index
Creating A DataGuard Broker Configuration Using DGMGRL
 
Contents
Intro
Though Grid Control offers a graphical interface for much more than just configuring a high availability database, sometimes its Installation maybe considered a huge overhead. Or you have other reasons to favor the command line. So this documentation will help you to create a DataGuard Configuration including a primary and a standby database.
Now we also need to connect to the primary database: CONNECT sys/syspassword. Though DGMGRL will not throw any error when using CONNECT /, this would probably cause some ORA-01031 later since it cannot handle the OS-authentication correctly. So better explicitly specify an account with SYSDBA privileges here. Forget about the AS SYSDBA suffix: This will simply throw an error. Specifying an account with SYSDBA privileges will make DGMGRL connect AS SYSDBA automatically.
Preconditions
In order to successfully create the configuration, both your primary and standby database must be up and running using SPFILE. If you did not yet create the SPFILE, you must do so first:
CREATE SPFILE FROM PFILE; SHUTDOWN IMMEDIATE STARTUP [MOUNT]
As indicated, you have to restart the database after creating the SPFILE, since the database can only use it if it is started with it. The keyword "MOUNT" is put in square brackets, since it applies only to the standby database.
Preparations
To make sure we have a clean start, we have to remove all "evidence" left over from possible failed attempts. This is done as follows:
  1. Make sure the listener is configured with a special entry for DGMGRL (see below)
  2. CleanUp archive log destination on the STANDBY database:
    ALTER SYSTEM SET log_archive_dest_1='';
  3. Stop DG broker process (DMON) on both, primary and standby database:
    ALTER SYSTEM SET dg_broker_start=FALSE SCOPE=spfile SID='*';
  4. (Re)Move any existing DG broker configuration files from the following locations: $ORACLE_BASE/admin/<db_unique_name> or $ORACLE_HOME/dbs, namely dr1<db_unique_name>.dat and dr2<db_unique_name>.dat
  5. Start the DG broker process (DMON) again on both, the primary and standby databases:
    ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=spfile SID='*';
    Make sure it is running - check output of SHOW PARAMETER DG
Creating the DataGuard configuration
Now it's time to startup the DataGuard manager CLI interface. On the primary database machine (make sure the $ORACLE_SID is pointing to your primary instance), simply issue the command dgmgrl. This will bring you to the CLI interface command prompt, indicated by DGMGRL> replacing the OS prompt.
Create Configuration
To create an initial configuration, we use the CREATE CONFIGURATION command. This will look like following:
CREATE CONFIGURATION 'sample' AS PRIMARY DATABASE IS 'primary_db' CONNECT IDENTIFIER IS primary_db.world;
As this is missing in most documentations, I want to explicitly state what the parameters must be:
  • CREATE CONFIGURATION 'sample': 'sample' can be anything you want - it is simply the name of the configuration you may want to remember. So you can chose anything suitable here.
  • PRIMARY DATABASE IS 'primary_db': 'primary_db' must match your primary databases db_unique_name. You can issue the command SHOW PARAMETER db_unique_name in SQL*Plus to verify.
  • CONNECT IDENTIFIER IS primary_db.world: This specifies how the database is resolved, and should reflect the database alias as defined in your tnsnames.ora (or whatever name service you are using)
Once created, you can display the details issuing the SHOW CONFIGURATION command. You may wonder about additional settings being displayed - well, since we did not define more specials, we implicitely adviced the broker to use its defaults for these. You may note that the current status will display "DISABLED": That's okay, we are not yet done :)
Adding the standby database
When creating the configuration, we only specified the primary database - so now it's time to add our standby database(s). The command to use for this step is ADD DATABASE, and to explain it, we assume the following example:
ADD DATABASE 'standby_db' AS CONNECT IDENTIFIER IS standby_db.world MAINTAINED AS PHYSICAL;
Not that much to explain here, if you've been reading the details on the CREATE CONFIGURATION command above: standby_db again has to reflect the db_unique_name, this time of your standby database. The CONNECT IDENTIFIER again is how the database is resolved. Last but not least, MAINTAINED AS PHYSICAL is quite self-explaining: We just added a physical standby database.
Verifying the configuration
Now you may want to check what you configured. As already mentioned before, you may issue the command SHOW CONFIGURATION - this time there will be some more details, since one more database has been added. Similarly, you can display details on the database(s) configured issuing the SHOW DATABASE [VERBOSE] <db_unique_name>; command. You may already have guessed it: the "VERBOSE" keyword will cause a much more verbose output ;)
Enabling the configuration
Even after adding our standby database(s), a SHOW CONFIGURATION always told us it is not yet enabled. That's still right - we have to do so explicitly. And now is the time for it, so we issue a ENABLE CONFIGURATION at the DGMGRL> prompt. If we configured everything correctly, this step will take some time. After the prompt returned, you may issue the SHOW CONFIGURATION command again. Don't worry if you now get some "...in progress" warnings - in fact, this is a good sign: If it's in progress, the configuration must be available.
But after a couple of minutes, SHOW CONFIGURATION should end with the word "SUCCESS" - then you are done, congratulations!
Troubleshooting
When it comes to errors, the returned errors maybe not that helpful. Sometimes all they say is simply "well, there was some error - go, check and fix that". To give you an example: Warning: ORA-16607: one or more databases have failed Now go and check!
ORA-16607 on SHOW CONFIGURATION
Problem: After creating your configuration and adding the standby database, you issued a SHOW CONFIGURATION as suggested. Instead of the expected SUCCESS, the report ends up with
Warning: ORA-16607: one or more databases have failed
. Checking with oerr ora 16607 was not very helpful (see above), and you neither can find anything in your alert.log nor any trace files.
Cause: Probably at least one of your databases is not using an SPFILE.
Solution: Check whether your databases have an SPFILE associated. It is usually located in $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora. If it does not exist, create it: Login to your database as SYSDBA, and issue the command CREATE SPFILE FROM PFILE;. Even if it exists, to make the database using it you need to restart the instance - it must be used already at startup.
ORA-16608 at SWITCHOVER
Problem: You issued a SWITCHOVER TO <standby_db> at the DGMGRL prompt. But instead of initiating the switchover, DGMGRL throws the error
Error: ORA-16608: one or more databases have warnings
Cause: This is similar to the error above - just a different error since you issued a different command.
Solution: See above.
ORA-01031 during switchover
Problem: You started a switchover, which is performed successfully by DGMGRL. However, DGMGRL does not startup the databases again but issues an ORA-01031: insufficient privileges.
Cause: You lack some privileges (obviously). Probably you wonder why, since you may have connected with the command CONNECT /. NOTE: DGMGRL does not support OS authentication!
Solution: You need to explicitly connect as a user with the SYSDBA privileges, e.g. CONNECT sys/password.
ORA-16675 during SWITCHOVER
Problem: You issued a SWITCHOVER TO <standby> at the DGMGRL prompt. But instead of the switchover to be performed, you get an
Error: ORA-16775: target standby database in broker operation has potential data loss
Cause: Recovery has not caught up (yet).
Solution: Make sure your standby database is mounted and recovery is running. If not, issue a STARTUP MOUNT to mount the standby database, and start managed recovery with ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;. Wait a few minutes, and try again.
ORA-12514 during SWITCHOVER
Problem: You perform a switchover using DGMGRL. Though the switchover itself completes successful, databases don't get started up again. Instead, DGMGRL output holds an
ORA-12514: TNS:listener does not currently know of service requested in connect
Cause: This is a configuration issue: DGMGRL needs a special entry in the listener.ora.
Solution: Details on this can be found in the Metalink note 308943.1. Make sure both, your primary and standby database have an explicit entry in the listener.ora like this:
SID_LIST_LISTENER = ( SID_LIST = ( SID_DESC = ( GLOBAL_DBNAME = <db_unique_name>_DGMGRL.<db_domain> ) ( SERVICE_NAME = <db_unique_name>.<db_domain> ) ( SID_NAME = <ORACLE_SID> ) ( ORACLE_HOME = <ORACLE_HOME> ) ) )
Take special care, that:
  • <db_unique_name> reflects the databases unique name (SHOW PARAMETER db_unique_name)
  • <db_domain> reflects the databases domain (SHOW PARAMETER db_domain)
  • <ORACLE_SID> and <ORACLE_HOME> reflect the corresponding settings of the database
Having adjusted your listener.ora files, restart the listener (lsnrctl stop && lsnrctl start). Give your databases a few minutes to register with the listener again, and then - just to be on the safe side - let the listener tell you its configuration: lsnrctl status && lsnrctl services. Check the output if everything matches.
Sources
Next to my own experiences, this article is based on:
  • two MetaLink service requests (private)
  • MetaLink document 260112.1 ("10g DGMGRL CLI Configuration")
  • MetaLink document 308943.1 ("Automatic Restart of Databases during Switchover fail with ORA-12514 in DGMGRL")
w3cCreated by iFAQMaker v0.1.4 © 2004-2008 by Itzchak Rehberg & IzzySoftIzzySoft