IzzySoft

• Person
• Services
• References
• Software
• Oracle
• Imprint

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: 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.

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.

Contents

• Preconditions
• Preparations
• Creating the DataGuard configuration
  • Create Configuration
  • Adding the standby database
  • Verifying the configuration
  • Enabling the configuration
  • Fast-Start Failover
• Troubleshooting
  • ORA-01017 on ADD DATABASE or SHOW CONFIGURATION
  • ORA-16607 on SHOW CONFIGURATION
  • ORA-16664 at SHOW CONFIGURATION
  • ORA-16608 at SWITCHOVER
  • ORA-01031 during switchover
  • ORA-16675 during SWITCHOVER
  • ORA-12514 during SWITCHOVER
• Sources

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 a 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!

Fast-Start Failover

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 dgmgrl:

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 FastStartFailOverAutoReinstate to 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 article.

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-01017 on ADD DATABASE or SHOW CONFIGURATION

Problem: When creating your configuration, on adding the standby database (or a subsequent show configuration) you see an Error: ORA-01017: invalid username/password; logon denied for the physical standby database. Trying to find the cause, all direct logon attempts succeed, passwords match on both ends.

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 ($ORACLE_HOME/dbs/orapw$ORACLE_SID) to the standby DB. Then startup the database(s) again, and retry. The error should be gone.

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-16664 at SHOW CONFIGURATION

I experienced this error (ORA-16664: unable to receive the result from a database) once. 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.

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 Dokument 260112.1
• MetaLink Dokument 308943.1