| Oracle® Data Guard Concepts and Administration 10g Release 1 (10.1) Part Number B10823-01 |
|
|
View PDF |
| Oracle® Data Guard Concepts and Administration 10g Release 1 (10.1) Part Number B10823-01 |
|
|
View PDF |
This chapter steps you through the process of creating a physical standby database. It includes the following main topics:
The steps described in this chapter configure the standby database for maximum performance mode, which is the default data protection mode. Chapter 5 provides information about configuring the different data protection modes. Also, the discussions in this chapter assume that you specify initialization parameters in a server parameter file (SPFILE), instead of a text initialization parameter file (PFILE).
See also:
Before you create a standby database you must first ensure the primary database is properly configured.
Table 3-1 provides a checklist of the tasks that you perform on the primary database to prepare for physical standby database creation. There is also a reference to the section that describes the task in more detail.
| Reference | Task |
|---|---|
|
Note: Perform these preparatory tasks only once. After you complete these steps, the database is prepared to serve as the primary database for one or more standby databases. |
Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:
SQL> ALTER DATABASE FORCE LOGGING;
This statement can take a considerable amount of time to complete, because it waits for all unlogged direct write I/O to finish.
Create a password file if one does not already exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed. See Oracle Database Administrator's Guide.
On the primary database, you define initialization parameters that control log transport services while the database is in the primary role. There are additional parameters you need to add that control the receipt of the redo data and log apply services when the primary database is transitioned to the standby role.
Example 3-1 shows the primary role initialization parameters that you maintain on the primary database. This example represents a Data Guard configuration with a primary database located in Chicago and one physical standby database located in Boston. The parameters shown in Example 3-1 are valid for the Chicago database when it is running in either the primary or the standby database role. The configuration examples use the names shown in the following table:
| Database | DB_UNIQUE_NAME | Oracle Net Service Name |
|---|---|---|
|
Primary |
chicago |
chicago |
|
Physical standby |
boston |
boston |
DB_NAME=chicago DB_UNIQUE_NAME=chicago SERVICE_NAMES=chicago LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)' CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl' LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/chicago/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago' LOG_ARCHIVE_DEST_2= 'SERVICE=boston VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
These parameters control how log transport services transmit redo data to the standby system and the archiving of redo data on the local file system. Note that the example assumes the use of the ARCn processes (the default) to transmit redo data. If you specify the LGWR process to transmit redo data to both the local and remote destinations, also include the NET_TIMEOUT attribute (described in Chapter 12) on the LOG_ARCHIVE_DEST_2 initialization parameter.
Example 3-2 shows the additional standby role initialization parameters on the primary database. These parameters take effect when the primary database is transitioned to the standby role.
FAL_SERVER=boston FAL_CLIENT=chicago DB_FILE_NAME_CONVERT= '/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/' LOG_FILE_NAME_CONVERT= '/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/' STANDBY_FILE_MANAGEMENT=AUTO
Specifying the initialization parameters shown in Example 3-2 sets up the primary database to resolve gaps, converts new datafile and log file path names from a new primary database, and archives the incoming redo data when this database is in the standby role. With the initialization parameters for both the primary and standby roles set as described, none of the parameters need to change after a role transition.
The following table provides a brief explanation about each parameter setting shown in Examples 3-1 and 3-2.
| Parameter | Recommended Setting |
|---|---|
|
Specify an 8-character name. Use the same name for all standby databases. | |
|
Specify a unique name for each database. This name stays with the database and does not change, even if the primary and standby databases reverse roles. | |
|
Specify a service name for this standby database that is unique from the primary database service name. If you do not explicitly specify unique service names and the primary and standby databases are located on the same system, the same default global name (consists of the database name, | |
|
Specify the | |
|
Specify the path name for the control files on the primary database. Example 3-1 shows how to do this for two control files. It is recommended that a second copy of the control file is available so an instance can be easily restarted after copying the good control file to the location of the bad control file. | |
|
Specify where the redo data is to be archived on the primary and standby systems. In Example 3-1:
Note: If a flash recovery area was configured (with the | |
|
Specify | |
|
Set the same password for | |
|
Specify the format for the archived redo log files using a thread (%t), sequence number (%s), and resetlogs ID (%r). See Section 5.7.1 for another example. | |
|
Specify the Oracle Net service name of the FAL server (typically this is the database running in the primary role). When the Chicago database is running in the standby role, it uses the Boston database as the FAL server from which to fetch (request) missing archived redo log files if Boston is unable to automatically send the missing log files. See Section 5.8. | |
|
Specify the Oracle Net service name of the Chicago database. The FAL server (Boston) copies missing archived redo log files to the Chicago standby database. See Section 5.8. | |
|
Specify the path name and filename location of the primary database datafiles followed by the standby location. This parameter converts the path names of the primary database datafiles to the standby datafile path names. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site, then this parameter is required. Note that this parameter is used only to convert path names for physical standby databases. | |
|
Specify the location of the primary database online redo log files followed by the standby location. This parameter converts the path names of the primary database log files to the path names on the standby database. If the standby database is on the same system as the primary database or if the directory structure where the log files are located on the standby system is different from the primary system, then this parameter is required. | |
|
Set to |
|
Caution: Review the initialization parameter file for additional parameters that may need to be modified. For example, you may need to modify the dump destination parameters ( |
If archiving is not enabled, issue the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN;
See Oracle Database Administrator's Guide for information about archiving.
This section describes the tasks you perform to create a physical standby database.
Table 3-2 provides a checklist of the tasks that you perform to create a physical standby database and the database or databases on which you perform each task. There is also a reference to the section that describes the task in more detail.
| Reference | Task | Database |
|---|---|---|
|
Primary | ||
|
Primary | ||
|
Prepare an Initialization Parameter File for the Standby Database |
Primary | |
|
Primary | ||
|
Standby | ||
|
Standby | ||
|
Standby |
You can use any backup copy of the primary database to create the physical standby database, as long as you have the necessary archived redo log files to completely recover the database. Oracle recommends that you use the Recovery Manager utility (RMAN).
See Oracle High Availability Architecture and Best Practices for backup recommendations and Oracle Database Backup and Recovery Advanced User's Guide to perform an RMAN backup operation.
If the backup procedure required you to shut down the primary database, issue the following SQL*Plus statement to start the primary database:
SQL> STARTUP MOUNT;
Then, create the control file for the standby database, and open the primary database to user access, as shown in the following example:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl'; SQL> ALTER DATABASE OPEN;
Perform the following steps to create a standby initialization parameter file.
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE) used by the primary database; a text initialization parameter file can be copied to the standby location and modified. For example:
SQL> CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;
Later, in Section 3.2.5, you will convert this file back to a server parameter file after it is modified to contain the parameter values appropriate for use with the physical standby database.
Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made.
Example 3-3 shows the portion of the standby initialization parameter file where values were modified for the physical standby database. Parameter values that are different from Example 3-1 and Example 3-2 are shown in bold typeface. The parameters shown in Example 3-3 are valid for the Boston database when it is running in either the primary or the standby database role.
. . . DB_NAME=chicago DB_UNIQUE_NAME=boston SERVICE_NAMES=boston LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)' CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl' DB_FILE_NAME_CONVERT= '/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/' LOG_FILE_NAME_CONVERT= '/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/' LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/boston/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston' LOG_ARCHIVE_DEST_2= 'SERVICE=chicago VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE STANDBY_FILE_MANAGEMENT=AUTO INSTANCE_NAME=boston FAL_SERVER=chicago FAL_CLIENT=boston . . .
Note that the example assumes the use of the ARCn processes (the default) to transmit redo data. If you specify the LGWR process to transmit redo data to both the local and remote destinations, also include the NET_TIMEOUT attribute (described in Chapter 12) on the LOG_ARCHIVE_DEST_2 initialization parameter.
In addition, ensure the COMPATIBLE initialization parameter is set to the same value on both the primary and standby databases. If the values differ, log transport services may be unable to transmit redo data from the primary database to the standby databases. In a Data Guard configuration, COMPATIBLE must be set to a minimum of 9.2.0.1.0. However, if you want to take advantage of new Oracle Database 10g features, set the COMPATIBLE parameter to 10.1.0.0 or higher.
It is always a good practice to use the SHOW PARAMETERS command to verify no other parameters need to be changed.
The following table provides a brief explanation about the parameter settings shown in Example 3-3 that have different settings from the primary database.
| Parameter | Recommended Setting |
|---|---|
|
Specify a unique name for this database. This name stays with the database and does not change even if the primary and standby databases reverse roles. | |
|
Specify a service name for this standby database that is unique from the primary database service name. If you do not explicitly specify unique service names and the primary and standby databases are located on the same system, the same default global name (comprised of the database name, | |
|
Specify the path name for the control files on the standby database. Example 3-3 shows how to do this for two control files. It is recommended that a second copy of the control file is available so an instance can be easily restarted after copying the good control file to the location of the bad control file. | |
|
Specify the path name and filename location of the primary database datafiles followed by the standby location. This parameter converts the path names of the primary database datafiles to the standby datafile path names. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site, then this parameter is required. | |
|
Specify the location of the primary database online redo log files followed by the standby location. This parameter converts the path names of the primary database log files to the path names on the standby database. If the standby database is on the same system as the primary database or if the directory structure where the log files are located on the standby system is different from the primary system, then this parameter is required. | |
|
Specify where the redo data is to be archived. In Example 3-3:
Note: If a flash recovery area was configured (with the | |
|
Specify a different value for the standby database than the primary database when the primary and standby databases reside on the same system. | |
|
Specify the Oracle Net service name of the FAL server (typically this is the database running in the primary role). When the Boston database is running in the standby role, it uses the Chicago database as the FAL server from which to fetch (request) missing archived redo log files if Chicago is unable to automatically send the missing log files. See Section 5.8. | |
|
Specify the Oracle Net service name of the Boston database. The FAL server (Chicago) copies missing archived redo log files to the Boston standby database. See Section 5.8. |
|
Caution: Review the initialization parameter file for additional parameters that may need to be modified. For example, you may need to modify the dump destination parameters ( |
Use an operating system copy utility to copy the following binary files from the primary system to the standby system:
Perform the following steps to create a Windows-based service, create a password file, set up the Oracle Net environment, and create a SPFILE.
If the standby system is running on a Windows-based system, use the ORADIM utility to create a Windows Service and password file. For example:
WINNT> oradim -NEW -SID boston -INTPWD password -STARTMODE manual
See Oracle Database Platform Guide for Windows for more information about using the ORADIM utility.
On platforms other than Windows, create a password file, and set the password for the SYS user to the same password used by the SYS user on the primary database. The password for the SYS user on every database in a Data Guard configuration must be identical for redo transmission to succeed. See Oracle Database Administrator's Guide.
On both the primary and standby sites, use Oracle Net Manager to configure a listener for the respective databases.
To restart the listeners (to pick up the new definitions), enter the following LSNRCTL utility commands on both the primary and standby systems:
% lsnrctl stop % lsnrctl start
See Oracle Net Services Administrator's Guide.
Enable broken connection detection by setting the SQLNET.EXPIRE_TIME parameter to 2 (minutes) in the SQLNET.ORA parameter file on the standby system. For example:
SQLNET.EXPIRE_TIME=2
See Oracle Net Services Administrator's Guide.
On both the primary and standby systems, use Oracle Net Manager to create a network service name for the primary and standby databases that will be used by log transport services.
The Oracle Net service name must resolve to a connect descriptor that uses the same protocol, host address, port, and SID that you specified when you configured the listeners for the primary and standby databases. The connect descriptor must also specify that a dedicated server be used.
See the Oracle Net Services Administrator's Guide and the Oracle Database Administrator's Guide.
If you plan to immediately transition the physical standby database to a logical standby database (as described in Chapter 4, "Creating a Logical Standby Database"), then skip this step and proceed with the instructions in Section 3.2.6.
On an idle standby database, use the SQL CREATE statement to create a server parameter file for the standby database from the text initialization parameter file that was edited in Step 2. For example:
SQL> CREATE SPFILE FROM PFILE='initboston.ora';
Perform the following steps to start the physical standby database and Redo Apply.
On the standby database, issue the following SQL statements to start and mount the database in read-only mode:
SQL> STARTUP OPEN READ ONLY;
Do not open the database; it should remain closed to user access; a physical standby database must be in the mounted state (or open in read-only mode) to receive redo data.
If you plan to immediately transition the physical standby database to a logical standby database (as described in Chapter 4, "Creating a Logical Standby Database"), then skip this step and proceed with the instructions in Step 3.
Creating a new temporary file on the physical standby database now, rather than later, is beneficial. Temporary files enable disk sorting when the database is open in read-only mode and prepare the database for future role transitions.
To add temporary files to the physical standby database, perform the following tasks:
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES 2> WHERE CONTENTS = 'TEMPORARY'; TABLESPACE_NAME -------------------------------- TEMP1 TEMP2
For each tablespace identified in the previous query, add a new temporary file to the standby database. The following example adds a new temporary file called TEMP1 with size and reuse characteristics that match the primary database temporary files:
SQL> ALTER TABLESPACE TEMP1 ADD TEMPFILE 2> '/arch1/boston/temp01.dbf' 3> SIZE 40M REUSE;
On the standby database, issue the following command to start Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
This statement automatically mounts the database. Also, the statement includes the DISCONNECT FROM SESSION option so that Redo Apply runs in a background session.
See Section 6.3, "Applying Redo Data to Physical Standby Databases" for more information.
The transmission of redo data to the remote standby location does not occur until after a log switch. A log switch occurs, by default, when an online redo log file becomes full. To force a log switch so that redo data is transmitted immediately, use the following ALTER SYSTEM statement on the primary database. For example:
SQL> ALTER SYSTEM SWITCH LOGFILE;
Once you create the physical standby database and set up log transport services, you may want to verify database modifications are being successfully transmitted from the primary database to the standby database.
To see that redo data is being received on the standby database, you should first identify the existing archived redo log files on the standby database, force a log switch and archive a few online redo log files on the primary database, and then check the standby database again. The following steps show how to perform these tasks.
On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME 2 FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME NEXT_TIME ---------- ------------------ ------------------ 8 11-JUL-02 17:50:45 11-JUL-02 17:50:53 9 11-JUL-02 17:50:53 11-JUL-02 17:50:58 10 11-JUL-02 17:50:58 11-JUL-02 17:51:03 3 rows selected.
On the primary database, issue the ALTER SYSTEM ARCHIVE LOG CURRENT statement to force a log switch and archive the current online redo log file group:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME 2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME NEXT_TIME ---------- ------------------ ------------------ 8 11-JUL-02 17:50:45 11-JUL-02 17:50:53 9 11-JUL-02 17:50:53 11-JUL-02 17:50:58 10 11-JUL-02 17:50:58 11-JUL-02 17:51:03 11 11-JUL-02 17:51:03 11-JUL-02 18:34:11 4 rows selected.
The archived redo log files are now available to be applied to the physical standby database.
On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG 2 ORDER BY SEQUENCE#; SEQUENCE# APP --------- --- 8 YES 9 YES 10 YES 11 YES
4 rows selected.
See Section 5.9.1, "Monitoring Log File Archival Information" and Section 6.3.4, "Monitoring Log Apply Services on Physical Standby Databases" to verify log transport services and log apply services are working correctly.
At this point, the physical standby database is running and can provide the maximum performance level of data protection. The following list describes additional preparations you can take on the physical standby database:
The Data Guard configuration is initially set up in the maximum performance mode (the default). See Section 5.6 for information about the data protection modes and how to upgrade or downgrade the current protection mode.
Standby redo logs are required for standby databases running in the maximum protection mode and maximum availability mode. However, configuring standby redo logs is recommended on all standby databases, because during a failover Data Guard can recover and apply more redo data from standby redo log files than from the archived redo log files alone. The standby redo logs should exist on both primary and standby databases and have the same size and names. See Section 5.6.2 for more information.
Flashback Database removes the need to re-create the primary database after a failover. Flashback Database is similar to conventional point-in-time recovery in its effects, enabling you to return a database to its state at a time in the recent past. Flashback Database is faster than point-in-time recovery, because it does not require restoring datafiles from backup or the extensive application of redo data. You can enable Flashback Database on the primary database, the standby database, or both. See Oracle Database Backup and Recovery Advanced User's Guide for more information.
|
 Copyright © 1999, 2003 Oracle Corporation All Rights Reserved. |
|
