Implementing Oracle 10g Data Guard for Standby/Failover Availability

This tutorial demonstrates how to setup Data Guard in the Windows version of Oracle Database 10g. I am assuming you already know how to restart database instances and listeners.

Notes: Oracle Enterprise Manager requires that the database instance it manages be accessible via a Net name. In other words, ensure that a tnsnames.ora file is setup to point to the instance, correctly. DB_UNIQUE_NAME refers to a globally unique name. SERVICE_NAMES refers to one or more service-based names which are registered with the TNS listener. If SERVICE_NAMES is empty, the instance registers with the TNS listener a service name in the following format: DB_UNIQUE_NAME.DB_DOMAIN. Dont' confuse what's in SERVICE_NAMES with a "service name". service names are, typically, Oracle Net names as defined in a tnsnames.ora file, and it's how Oracle Net applications such as SQL*Plus communicate with DB instances. A SID is equivalent to a DB name, or DB_NAME, not a service name, so don't confuse SID with service names either.

Preparing the Source/Primary Database

1. Login to Oracle Enterprise Manager (typically the shortcut named "Database Control - SID").
2. Click on Administration.
3. Click on "All Initialization Parameters".
4. Ensure that the remote_login_passwordfile parameter is set to "EXCLUSIVE" (without quotes). If it's not, create an exclusive password via the "orapwd" (without quotes) command, then set it. You can set the parameter via an SQL command:ALTER SYSTEM SET remote_login_passwordfile = 'EXCLUSIVE' SCOPE=SPFILE
5. The DB_NAME parameter must be the same for both the source and target databases. The database name is NOT case sensitive. This is just a note, not anything that needs to be done or set at this point.
6. The DB_UNIQUE_NAME parameter must be unique for both the source and target databases. If they're not, set them via the Oracle Enterprise Manager. For now, just ensure that the primary/target DB has a unique DB name. Per Oracle, the default value of DB_UNIQUE_NAME is the value of DB_NAME, and DB_UNIQUE_NAME can be up to 30 characters and is case insensitive. The following characters are valid in a database name: alphanumeric characters, underscore (_), number sign (#), and dollar sign ($).
   Alternatively, you can issue the following SQL command:alter system set db_unique_name = 'unique_name' scope=spfileNOTE: Be careful when changing this value. When the SERVICE_NAMES parameter is empty, the instance will register with the TNS listener the DB_UNIQUE_NAME and DB_DOMAN parameters to form a unique, global service name. It's automatically determined. Therefore, BEFORE changing the DB_UNIQUE_NAME, ensure you set the SERVICE_NAMES parameter to include the original service name registered with the TNS listener for this DB instance ALONG with the new DB_UNIQUE_NAME.DB_DOMAIN service name that would've changed. Otherwise, applications such as Oracle Enterprise Manager will not work because they will refer to an service name that no longer exists. So, for example, if the original service name setup for use with OEM was dgtest.dotnetfun.local, and the new DB_UNIQUE_NAME for that DB instance is dgtest_primary, the SERVICE_NAMES parameter would be set to 'dgtest.dotnetfun.local', 'dgtest_primary.dotnetfun.local'.
7. The SERVICE_NAMES parameter must be unique for both the source and target databases. If they're not, set them via the Oracle Enterprise Manager as specified above. Alternatively, you can use following SQL command:ALTER SYSTEM SET service_names = 'service_name' SCOPE=SPFILENOTE: Again, be careful not to remove the original service name setup for use with Oracle Enterprise Manager, as OEM references the instance this way, and removing it will proably render OEM useless. The default value, per Oracle, is DB_NAME.DB_DOMAIN.
8. Set the LOG_ARCHIVE_CONFIG parameter to something like this: 'DG_CONFIG=(source_db_unique_name,target_db_unique_name)' (without single quotes, where source_db_unique_name is the unique DB name of the primary DB and target_db_unique_name is the unique DB name of the standby DB. You can do this via Oracle Enterprise Manager or, alternatively, by issuing the following SQL command: ALTER SYSTEM SET log_archive_config = 'DG_CONFIG=(source_db_unique_name,target_db_unique_name)' SCOPE=SPFILE
9. Set the LOG_ARCHIVE_DEST_1 parameter to point to the source archive log destination, via an SQL command:ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=archive_log_dir VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db_unique_name' SCOPE=SPFILE
10. Set the LOG_ARCHIVE_DEST_2 parameter to point to the target archive log destination, via an SQL command:ALTER SYSTEM SET log_archive_dest_2 = 'SERVICE=target_service_name VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_unique_name' SCOPE=SPFILE Note that SERVICE here refers to a TNS service name/alias that points to the standby/target DB instance, typically, via a tnsnames.ora service/alias stored on the primary DB.
11. Set the LOG_ARCHIVE_DEST_STATE_1 and LOG_ARCHIVE_DEST_STATE_2 parameters to ENABLED either via OEM or an SQL command:ALTER SYSTEM SET log_archive_dest_state_1 = "ENABLE" SCOPE=SPFILE; ALTER SYSTEM SET log_archive_dest_state_2 = "ENABLE" SCOPE=SPFILE;
12. Set the FAL_SERVER parameter to the service name/alias of the source/primary DB, either via OEM or an SQL command:ALTER SYSTEM SET fal_server = 'source_service_name' SCOPE=SPFILE
13. Set the fal_client parameter to the service name/alias of the target/standby DB, either via OEM or an SQL command:ALTER SYSTEM SET fal_client = 'target_service_name' SCOPE=SPFILE
14. If the source and target DBs use different DB data file locations/folders, specify both the source and target locations via the db_file_name_convert parameter, either via OEM or an SQL command, starting with the source path, then the target path:ALTER SYSTEM SET db_file_name_convert = 'E:\oracle\product\10.2.0\oradata\Test1\','C:\oracle\product\10.2.0\oradata\test1\' SCOPE=SPFILE
15. If the source and target DBs use different log file locations/folders (NOT archive log files), specify both the source and target locations via the log_file_name_convert parameter, either via OEM or an SQL command, starting with the source path, then the target path:ALTER SYSTEM SET log_file_name_convert = 'E:\oracle\product\10.2.0\flash_recovery_area\TEST1\ARCHIVELOG','C:\oracle\product\10.2.0\flash_recovery_area\TEST1\ARCHIVELOG' SCOPE=SPFILE
16. Set the standby_file_management parameter to AUTO, either via OEM or an SQL command:ALTER SYSTEM SET standby_file_management = 'AUTO' SCOPE=SPFILE
17. If the source database is not already in archive log mode, perform the following SQL commands:shutdown immediate; startup mount; alter database archivelog; alter database open;To test that archiving is working, force a log switch by issuing the following SQL command:ALTER SYSTEM SWITCH LOGFILE;
18. Perform a cold backup of the source/primary DB's data files. Shutdown the database first, then copy all data files, control files, and online redo log files. After that's done, startup the DB again.
19. Create a standby control file for the target/standby DB by issuing the following SQL command:alter database create standby controlfile as 'path\test1_mtgvm1.ctl';Replace path with the directory where the control file is to be created.
20. Create and edit an initialization file for the target/standby DB by issuing the following SQL command:create pfile=path\initSID.ora' from spfile;Replace path with the directory where you want the init file created and SID with the unique DB name of the target/standby DB.
21. Edit the target/standby init file so that the following parameters apply to the target/standby DB: DB_UNIQUE_NAME, SERVICE_NAMES, CONTROL_FILES, DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT, LOG_ARCHIVE_DEST_n, INSTANCE_NAME, FAL_SERVER, and FAL_CLIENT. Switch the LOG_ARCHIVE_DEST_n values so that the source and target values are reversed, opposite in order. The same goes for the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT values--their values should be switched so that the first path is the standy's and second is the primary's. Ensure that any paths that refer to the local, standby DB are accurate. Ensure that the COMPATIBLE parameter is the same for both the primary and standby databases.

Preparing the Target/Standby Database

1. Copy the data files, control files, and initialization file to the standby/target server/location, ensuring that they are placed in the proper directories and match paths specified in the initialization file, specifically according to paths defined in the following parameters: CONTROL_FILES, DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT.