Creating a DataGuard Broker configuration using DGMGRL
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:
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.
Of course you can establish a simply, password-less login using Oracle Wallet – see Managing the Secure External Password Store for Password Credentials in the Oracle documentation.
- Creating the DataGuard configuration
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.
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:
- Make sure the listener is configured with a special entry for DGMGRL (see a below)
- CleanUp archive log destination on the STANDBY database:
ALTER SYSTEM SET log_archive_dest_1='';
- Stop DG broker process (DMON) on both, primary and standby database:
ALTER SYSTEM SET dg_broker_start=FALSE SCOPE=spfile SID='*';
- (Re)Move any existing DG broker configuration files from the following
- Start the DG broker process (DMON) again on both, the primary and standby
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.
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_namein 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
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
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
again is how the database is resolved. Last but not least,
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
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!
If you need High Availability, it may be recommended to enable FSFO in addition to above, simple configuration. This has a few more requirements, though, e.g.
- Flashback needs to be enabled on the primary as well as on the standby database. You can verify this via
SELECT flashback_on FROM v$database;
Is it not enabled:
ALTER DATABASE FLASHBACK ON;
On the standby database you will need to disable recovery before executing that statement (and re-enable it afterwards):
ALTER DATABASE RECOVER MANAGED DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED DATABASE DISCONNECT;
- Both databases need a password file (in either EXCLUSIVE or SHARED mode). These password files must be binary-identical (it doesn't suffice if the contained passwords match). To ensure that, you'll need to stop the standby, copy over the password file from the primary, and restart the standby. Also seee ORA-1017 below.
Now you should be able to activate FSFO via
edit database primary_db set property 'LogXptMode'='SYNC'; edit database standby set property 'LogXptMode'='SYNC'; edit database primary_db set property FastStartFailOverTarget=standby; edit configuration set protection mode as maxavailability; edit configuration set property FastStartFailOverAutoReinstate=FALSE; enable fast_start failover;
Pointer: I've set
FALSE in above
example to avoid a potential SwitchOver being triggered by the observer (e.g.
if the failover was caused by a network outage and the original primary
returned within minutes) – as that would automatically activate ADG and thus
imply a license violation should you not have licensed
it. For a manual switchover avoiding this situation as well, please see this
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!
ADD DATABASE or
Problem: When creating your configuration, on adding the standby
database (or a subsequent
show configuration) you see an
invalid username/password; logon denied for the physical standby database.
Trying to find the cause, all direct logon attempts succeed, passwords match on
Cause: While passwords must be identical on primary and standby, that alone
is not sufficient. It seems the password file used must be binary-identical on
both sides. To confirm this is the culprit, use
diff to compare the two files:
diff <pwd_file_primary> <pwd_file_standby> will tell you whether the two are
identical or not (though if not, it won't tell you what the difference is – but
that doesn't matter here anyway).
Solution: Shutdown the databases (at least the standby must be shut down),
and copy the password file from the primary database (
to the standby DB. Then startup the database(s) again, and retry. The error
should be gone.
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.
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.
I experienced this error (
ORA-16664: unable to receive the result from a database)
oerr ora 16664 gives some pointers on where to look (DG Broker logs,
network configuration), but findings were not really conclusive. In my case the
culprit turned out to be the very same as with the ORA-01017 above:
someone seems to have changed the
SYS password on the primary (and then back to
the original password), thus tainting the password file – which resulted in the
two files (primary, standby) no longer being „binary identical“. So please see
there for details.
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
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.
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
Cause: This is a configuration issue: DGMGRL needs a special entry in the
Solution: Details on this can be found in the Metalink note
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_HOME>reflect the corresponding settings of the database
Having adjusted your
listener.ora files, restart the listener (
&& 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.
Next to my own experiences, this article is based on: