Release 2 (8.1.6)
Part Number A76995-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Standby Database Concepts and Administration Release 2 (8.1.6) Part Number A76995-01 |
|
This chapter describes how to set up a standby database in preparation for manual or managed recovery. It includes the following topics:
After you have decided to implement a standby database, you should address several important issues relating to the environment in which the standby database will reside. How you answer the questions depends on the purpose of the standby database: protection against data loss or corruption, or supplemental reporting.
The first question is whether the standby database will run in a managed standby environment (see Configuration Options). In this environment, the primary database automatically archives redo logs to the standby site so long as the standby instance is started.
Whether you implement a managed standby environment depends on your situation. The following table offers suggestions:
If you choose a managed standby environment, then you can run the standby database in managed recovery mode so that it applies archived redo logs automatically. Whether you plan to use the database for reporting or protection against data loss, this option requires the least user intervention. If you choose a non-managed environment, then you must transfer the logs manually and perform manual recovery of the standby database.
If you can, it is almost always better to place a standby database on a separate host, for the following reasons:
To learn about renaming primary files in the standby control file, see Renaming Primary Filenames in the Standby Control File and Manually Renaming Standby Files Not Captured by Conversion Parameters.
See Also:
Both the size of the online redo logs and the frequency with which they switch affect the generation of archived redo logs at the primary site. Table 2-1 describes some consequences of various online redo log configurations.
| If the online redo logs are | Then |
|---|---|
|
Small |
The logs switch more frequently. If you maintain a managed standby environment, frequent switches can cause:
|
|
Large |
The redo logs switch less frequently, but more transactions are stored in each file. This situation can: |
|
Numerous |
The primary database does not have to reuse redo logs as often as it does when you maintain the minimum of two. Because Oracle is not forced to archive at every log switch, a disaster can wipe out multiple logs before the primary database archives them to the standby site. |
If the primary database fails, users should be able to connect to the activated standby database seamlessly. For example, one simple solution is to configure the client tnsnames.ora files or Oracle Names server to contain an entry for the standby service name. After failover, users can connect to the database using the standby service name instead of the primary service name.
Other failover solutions include:
When deciding on a method, weigh the simplicity of the solution against the need for a seamless failover.
After you have decided to create a standby database and determined where to place it, you can begin preparing it. The procedure of standby database creation differs depending on whether you plan to set up a managed standby environment or a non-managed standby environment.
This section contains the following topics:
For a complete overview of what a standby database is and how it works, see Chapter 1. For an explanation of important terms such as managed standby environment, see Concepts and Terminology.
See Also:
Setting up a standby database for managed recovery requires you to perform a series of different tasks. After you have completed the preparation and initiated managed recovery, the standby database automatically and continuously applies redo logs as they are received from the primary database.
Table 2-2 summarizes the basic tasks for setting up a standby database and synchronizing it so it is ready to begin managed recovery.
| Task | Procedure | |
|---|---|---|
|
1 |
Either make a new backup of the primary database datafiles or access an old backup. |
|
|
2 |
Connect to the primary database and create the standby control file. |
|
|
3 |
Copy the backup datafiles and control file from the primary site to the standby site. |
|
|
4 |
Create a service name for the standby database. |
|
|
5 |
Configure the listener on the standby site so that it can receive the archived redo logs from the primary site. |
|
|
6 |
Set the initialization parameters for the primary database. |
Configuring the Primary Database Initialization Parameter File |
|
7 |
Create the standby initialization parameter file and set the initialization parameters for the standby database. Depending on your configuration, you may need to set filename conversion parameters. |
Configuring the Standby Database Initialization Parameter File |
|
8 |
Start the standby instance and mount the standby database. |
|
|
9 |
Manually change the names of the primary datafiles and redo logs in the standby control file for all files not automatically renamed using DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT in step 7. If step 7 renamed all files, skip this step. |
Manually Renaming Standby Files Not Captured by Conversion Parameters |
|
10 |
Manually enable initialization parameter changes on the primary database so that it can initiate archival to the standby site. |
Table 2-3 illustrates the basic tasks for setting up a standby database in preparation for manual recovery. This procedure assumes that you plan to connect to the standby database through Net8. If you do not wish to use Net8 to connect to the standby database, skip tasks 4 and 5.
| Task | Procedure | |
|---|---|---|
|
1 |
Either make a new backup of the primary datafiles or access an old backup. |
|
|
2 |
Connect to the primary database and create the standby control file. |
|
|
3 |
Copy the backup datafiles and standby control file from the primary site to the standby site. |
|
|
4 |
If you want to create a Net8 connection to the standby database, create a service name. |
|
|
5 |
If you want to create a Net8 connection to the standby database, configure the listener on the standby site so that it can receive requests for connections to the standby instance. |
|
|
6 |
Create the standby initialization parameter file on the standby site and set the initialization parameters for the standby database. Optionally, set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT to automatically rename primary files in the standby control file. |
Configuring the Standby Database Initialization Parameter File |
|
7 |
Start the standby instance and mount the standby database. |
|
|
8 |
While connected to the standby database, manually change the names of the primary datafiles and redo logs in the standby control file for all files not automatically renamed using DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT in step 6. If step 6 renamed all files, skip this step. |
Manually Renaming Standby Files Not Captured by Conversion Parameters |
You can create a standby database on the same host as your primary database or on a separate host. If you create your standby database on the same host, follow the creation procedure carefully when creating the standby database files so that you do not overwrite files on the primary database.
The creation of the standby database files occurs in three stages:
First, make backups of your primary database datafiles. You create the standby datafiles from these backups.
You can use any backup of the primary database so long as you have archived redo logs to completely recover the database. The backup can be old or new, consistent or inconsistent. Hot backups have the advantage of allowing you to keep the database open while performing the backup. Nevertheless, you may prefer to make a new closed, consistent backup to prevent the application of a large number of archived redo logs.
SQL> SELECT name FROM v$datafile; NAME ---------------------------------------------------------------------------- /oracle/dbs/tbs_01.f /oracle/dbs/tbs_02.f /oracle/dbs/tbs_03.f /oracle/dbs2/tbs_11.f /oracle/dbs2/tbs_12.f /oracle/dbs3/tbs_21.f /oracle/dbs3/tbs_22.f 7 rows selected.
SQL> SHUTDOWN IMMEDIATE;
/backup temporary directory, enter:
% cp /oracle/dbs/*.f /backup % cp /oracle/dbs2/*.f /backup % cp /oracle/dbs3/*.f /backup
SQL> STARTUP pfile=initPROD1.ora;
|
See Also:
To learn how to make operating system backups, see the Oracle8i Backup and Recovery Guide. |
After you have created the backups that will be used as the standby datafiles, you can create the standby database control file. The control file must have been created at a time later than the latest timestamp for the backup datafiles.
SQL> CONNECT sys/change_on_install@prod1 SQL> ARCHIVE LOG LIST Database log mode Archive Mode Automatic archival Enabled Archive destination /vobs/oracle/work/arc_dest/arc Oldest online log sequence 821 Next log sequence to archive 822 Current log sequence 822
oracle/dbs/stbycf.f on the primary site, enter the following:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oracle/dbs/stbycf.f';
Note that the filename for the created standby control file must be different from the filename of the current control file of the primary database.
After you have successfully created the standby datafiles and control file, transfer the files to the standby site using an operating system utility. For example, if the standby site and primary site are on the same host, you can use the UNIX cp command to transfer files; if they are on separate hosts, you can use ftp.
| If the standby database is on | Then you |
|---|---|
|
A separate host with the same directory structure as the primary database |
Can use the same path names for the standby files as the primary files. In this way, you do not have to rename the primary datafiles in the standby control file. |
|
The same host as the primary database, or the standby database is on a separate host with a different directory structure |
Must rename the primary datafiles in the standby control file after copying them to the standby site. You can:
|
Transfer the created control file and datafile backups to the standby site using operating system commands or utilities. Use an appropriate method for transferring binary files.
% cp /backup/db.cf /standby/oracle/dbs/db.cf
% cp /backup/*.df /standby/oracle/dbs
% cp /arc_dest/*.arc /standby/arc_dest
Network configuration varies greatly from system to system. If you do not plan to implement a managed standby environment in which the primary site archives to a standby site, you do not need Net8. You can connect to the standby database using operating system authentication. To implement a managed standby environment, however, you must create a service name for the standby instance. Consequently, you must use Net8.
In a managed standby environment, you must configure the listener.ora file for the standby site so that the standby site can receive archived logs from the primary site. If you do not use a name server, you will have to configure a tnsnames.ora file for the primary site. You may also have to configure additional network files such as SQL*Net.ora and names.ora. For an account of Net8 configuration, see the Net8 Administrator's Guide.
The information in the primary site tnsnames.ora file must correspond to the information in the standby site listener.ora file. The following table indicates which parameters must be identical and gives sample settings. Values that must be the same in both the tnsnames.ora and listener.ora files are in bold:
Following are generic procedures for modifying network files in a typical configuration. This section contains the following topics:
If you want to archive redo logs to a standby site, and you are not using an Oracle Names server, you must configure the tnsnames.ora file on the primary site. This file contains the service name of the standby instance.
tnsnames.ora file, which is typically located in $ORACLE_HOME/network/admin.
tnsnames.ora file, substituting appropriate values for standby_service_name, port_number, host_name, and standby_sid. Make sure the SID matches the SID_NAME of the listener.ora file and the PORT values are the same in the two files:
standby_service_name = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(PORT=port_number)(HOST=host_name)) (CONNECT_DATA=(SID=standby_sid)) )
For example, for standby database STANDBY1 on host REMOTE2 you can enter the following:
standby1 = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=remote2)) (CONNECT_DATA=(SID=standby1)) )
tnsnames.ora file, which is typically located in $ORACLE_HOME/network/admin.
tnsnames.ora file, substituting appropriate values for standby_service_name, key_handle, and standby_sid. Make sure the SID matches the SID_NAME of the listener.ora file and the KEY values are the same in the two files:
standby_service_name = (DESCRIPTION= (ADDRESS=(PROTOCOL=ipc)(KEY=key_handle)) (CONNECT_DATA=(SID=standby_sid)) )
For example, for standby database STANDBY1 you might enter the following:
standby1 = (DESCRIPTION= (ADDRESS=(PROTOCOL=ipc)(KEY=stby)) (CONNECT_DATA=(SID=standby1)) )
|
See Also:
For information about the |
In order to archive redo logs to a standby site, you must configure the listener.ora file on the standby site. The listener receives network connection requests from client applications and reroutes them to the standby server.
listener.ora file, which is typically located in $ORACLE_HOME/network/admin.
STANDBY_LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(PORT=port_number)(HOST=host_name)) )
For example, for a standby host called REMOTE2 you might enter:
STANDBY_LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=remote2)) )
SID_LIST_STANDBY_LISTENER = (SID_LIST= (SID_DESC=(SID_NAME=standby_sid_name)(ORACLE_HOME=/oracle_home)) )
For example, for a standby database called STANDBY1 you might enter:
SID_LIST_STANDBY_LISTENER = (SID_LIST= (SID_DESC=(SID_NAME=standby1)(ORACLE_HOME=/oracle)) )
stop and then enter start. If it is not started, use an operating system command to start it. For example, on a UNIX system enter:
% lsnrctl LSNRCTL for Solaris: Version 8.1.5.0.0 - Production on 23-MAR-99 12:04:10 (c) Copyright 1998 Oracle Corporation. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> stop
status command:
LSNRCTL> status
If the information for the standby service is configured correctly, you should see it among the list of the valid service names:
standby1 has 1 service handler(s)
listener.ora file, which is typically located in $ORACLE_HOME/network/admin.
STANDBY_LISTENER = (ADDRESS_LIST= (ADDRESS = (PROTOCOL=ipc) (KEY=key_handle)) )
For example, for a standby host called REMOTE2 you might enter:
STANDBY_LISTENER = (ADDRESS_LIST= (ADDRESS = (PROTOCOL=ipc) (KEY=stby)) )
SID_LIST_STANDBY_LISTENER = (SID_LIST= (SID_DESC = (SID_NAME=standby_sid_name)(ORACLE_HOME=oracle_home)) )
For example, for a standby database called STANDBY1 you might enter:
SID_LIST_STANDBY_LISTENER = (SID_LIST= (SID_DESC = (SID_NAME=standby1)(ORACLE_HOME=/oracle)) )
stop and then enter start. If it is not started, use an operating system command to start it. For example, on a UNIX system enter:
% lsnrctl LSNRCTL for Solaris: Version 8.1.5.0.0 - Production on 23-MAR-99 12:04:10 (c) Copyright 1998 Oracle Corporation. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> stop
status command:
LSNRCTL> status
If the information for the standby service is configured correctly, you should see it among the list of the valid service names:
standby1 has 1 service handler(s)
If you do not plan to implement a managed standby environment, you should not have to change the initialization parameter file of the primary system at all. You must transfer and apply the archived redo logs to the standby database manually, so you do not need to alter the archiving destinations.
If you do plan to implement a managed standby environment, you need to add new archiving destinations to the initialization parameter file of the primary system. This section assumes that you plan to implement a managed standby environment.
For the primary database to archive to a local or remote standby database location, the following must be true:
tnsnames.ora file on the primary site and the listener.ora file on the standby site have the correct corresponding entries.
This section contains these topics:
To learn how to manage archived redo logs, see the chapter on archived redo logs in the Oracle8i Administrator's Guide. For more information about Oracle networking options, see the Net8 Administrator's Guide. For an overview of the archiver process, see Oracle8i Concepts.
See Also:
Specify up to five destinations for your primary database archived logs by setting the LOG_ARCHIVE_DEST_n initialization parameter (where n is an integer from 1 to 5). Each numerically suffixed parameter uniquely identifies an individual destination, as shown in the following example:
# first local archiving destination LOG_ARCHIVE_DEST_1 = 'LOCATION=/oracle/arc/' # second local archiving destination LOG_ARCHIVE_DEST_2 = 'LOCATION=/oracle/arc2/' # third standby archiving destination LOG_ARCHIVE_DEST_3 = 'SERVICE=stby'
If you wish to archive redo logs to a standby database in managed recovery mode, you must use LOG_ARCHIVE_DEST_n in conjunction with the SERVICE keyword. Note that at least one archiving destination must be a local directory; that is, a non-standby site.
When using LOG_ARCHIVE_DEST_n, specify the archiving location using these keywords:
| Keyword | Indicates | Example |
|---|---|---|
|
LOCATION |
A local directory |
LOG_ARCHIVE_DEST_1= 'LOCATION=/arc_dest/' |
|
SERVICE |
Archival through a Net8 service name |
LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1' |
When using the LOCATION keyword, specify a valid path name for your operating system. When you specify SERVICE, Oracle translates the net service name through the tnsnames.ora file to a connect descriptor. The descriptor contains the information necessary for connecting to the standby database.
|
See Also:
For a detailed account of LOG_ARCHIVE_DEST_n and the archiving process, see the chapter on archived redo logs in the Oracle8i Administrator's Guide. |
Using the LOG_ARCHIVE_DEST_n parameters, you can specify whether a destination has the attributes OPTIONAL (default) or MANDATORY. For example, you can set the parameter as follows:
LOG_ARCHIVE_DEST_3 = 'SERVICE=standby1 MANDATORY'
Oracle Corporation recommends specifying the local directory destination as MANDATORY.
The LOG_ARCHIVE_MIN_SUCCEED_DEST=m parameter (where m is an integer from 1 to 5) specifies the number of destinations that must archive successfully before LGWR can overwrite the online redo logs. All MANDATORY destinations and non-standby OPTIONAL destinations contribute to satisfying the LOG_ARCHIVE_MIN_SUCCEED_DEST=m count. For example, you can set the parameter as follows:
LOG_ARCHIVE_MIN_SUCCEED_DEST = 2 # Oracle must archive to at least two # locations before overwriting the online # redo logs.
|
See Also:
For a detailed account of the OPTIONAL and MANDATORY keywords, see the chapter on archived redo logs in the Oracle8i Administrator's Guide. |
The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 5) initialization parameter specifies the state of the destination specified by its corresponding LOG_ARCHIVE_DEST_n parameter (where n is the same integer). For example, the LOG_ARCHIVE_DEST_STATE_3 parameter specifies the state of the LOG_ARCHIVE_DEST_3 archiving destination.
The archiving destination parameters can have two values: ENABLE and DEFER. ENABLE indicates that Oracle can archive to the destination, whereas DEFER indicates that it should not.
For example, you can set the parameter as follows:
LOG_ARCHIVE_DEST_STATE_2 = ENABLE LOG_ARCHIVE_DEST_2 = 'SERVICE=stby1'
|
See Also:
For a detailed account of the archive destination states, see the chapter on archived redo logs in the Oracle8i Administrator's Guide. For a description of the LOG_ARCHIVE_DEST_STATE_n parameter, see the Oracle8i Reference. |
Use the LOG_ARCHIVE_DEST_n parameters to determine whether and when the archiver process attempts to re-archive to a failed destination following an error. The REOPEN=s (where s is an integer) option specifies the minimum number of seconds before the archiver process should try to reaccess a failed destination. Note that REOPEN applies to all errors, not just OPEN errors.
If you specify REOPEN, the keyword has a default value of 300 seconds. If you do not specify REOPEN, it has the value of 0, which is the same as turning off the option. Note that if the REOPEN option is turned off, the archiver process will never attempt to reaccess a destination following an error.
You cannot use REOPEN to specify a limit on the number of attempts to reconnect and transfer archived logs. The REOPEN attempt either succeeds or fails, in which case the REOPEN information is reset. By default, the managed recovery operation waits indefinitely for a requested archived redo log; it terminates only through a CANCEL command, a shutdown, or a crash.
For example, you can set the parameter as follows to specify a reopen time of 60 seconds:
LOG_ARCHIVE_DEST_2 = 'SERVICE=standby2 OPTIONAL REOPEN=60'
|
See Also:
For a detailed account of how to use the REOPEN option, see the chapter on archived redo logs in the Oracle8i Administrator's Guide. |
Once you have configured the primary database initialization parameter file, you can duplicate the file for use by the standby database. The procedure for creating the standby initialization parameter file is as follows:
This section contains the following topics:
As a rule, most initialization parameters at the primary and standby databases should be identical, although some initialization parameters such as CONTROL_FILES and DB_FILE_NAME_CONVERT need to be changed. Differences in initialization parameters other than those described in the following table can cause performance degradation at the standby database and, in some cases, halt standby database operations. Only change parameter values when required for the functioning of the standby database or for filename conversions.
The initialization parameters in Table 2-4 play a key role in the configuration of the standby database.
| Parameter | Guideline |
|---|---|
|
COMPATIBLE |
Always set the same at the primary and standby databases. If different, you may not be able to apply the logs from your primary database to the standby database. |
|
DB_NAME |
Always set to the same value as DB_NAME value in the primary database. |
|
CONTROL_FILES |
Always set to a different value from CONTROL_FILES in primary database. The names of the control files for the standby database must exist at the standby site. |
|
DB_FILE_NAME_CONVERT |
Set to distinguish standby datafile filenames from primary datafile filenames, for example, when both databases reside on the same host, or a separate standby host uses different path names from the primary host. Because the standby control file is a copy of the primary control file, you must convert the standby filenames if the standby database is on the same host as the primary database or on a separate host with different path names. See also Renaming Primary Filenames in the Standby Control File. |
|
LOCK_NAME_SPACE |
Always set this value when the standby and primary databases share a host. Specifies the name space that the distributed lock manager uses to create lock names. |
|
LOG_ARCHIVE_DEST |
Specifies the location of the archived logs for a standby database in manual recovery mode. When performing manual recovery on the standby database, Oracle relies on either LOG_ARCHIVE_DEST or a user-defined filename to locate the logs. This parameter is also relevant for managed recovery. If a log is missing at the standby site and managed recovery halts, you must issue RECOVER STANDBY DATABASE to initiate manual recovery, which causes Oracle to look in LOG_ARCHIVE_DEST for the logs by default. Typically, you set STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST to the same value for managed recovery. |
|
LOG_ARCHIVE_TRACE |
Optionally, set this parameter to an integer value to see the progression of the archiving of redo logs to the standby site. Oracle writes an audit trail of the archived logs received from the primary database into a trace file. The parameter controls output generated by the archiving process (ARCn and foreground processes) on the primary database and the RFS process of the standby database. For a description of the possible integer values for this parameter and their meanings, see Determining Which Archived Logs Have Been Received by the Standby Site. |
|
LOG_FILE_NAME_CONVERT |
Set to make your standby redo log filenames distinguishable from primary database redo log filenames. The parameter value converts the filename of a new log file on the primary database to the filename of a log file on the standby database. Adding a log file to the primary database necessitates adding a corresponding file to the standby database. When the standby database is updated, this parameter is used to convert the log file name on the primary database to the log file name on the standby database. The file must exist and be writable on the standby database or the recovery process will halt with an error. |
|
STANDBY_ARCHIVE_DEST |
Used solely by a standby database in managed recovery mode to determine the location for the archived logs received from the primary database. Managed recovery mode uses this value along with LOG_ARCHIVE_FORMAT to generate the fully qualified standby database log filenames and stores the filenames in the standby control file. Managed recovery uses this data to drive recovery. For managed recovery, set STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST to the same value. If manual recovery is required because of a gap sequence, copy the missing log to the same location as the other logs and recover manually. You can then place the standby database back into managed recovery mode. |
If the standby site uses the same directory structure as the primary site, then you do not have to rename the primary filenames in the standby control file. If the primary and standby databases occupy the same node, however, or if the primary and standby sites use different directory structures, then you must rename the primary files in the standby control file so that the archived logs can be applied to the standby datafiles.
You can set initialization parameters so that your standby database automatically converts datafile and archived redo log filenames based on data in the standby database control file. If you cannot rename all primary filenames automatically using these parameters, then you must rename them manually (see Manually Renaming Standby Files Not Captured by Conversion Parameters).
The initialization parameters in Table 2-5 perform automatic filename conversions.
Use DB_FILE_NAME_CONVERT to convert the filename of a datafile on the primary database to a filename on the standby database; use LOG_FILE_NAME_CONVERT to convert the filename of a new redo log on the primary database to a filename on the standby database. Adding a datafile or log to the primary database necessitates adding a corresponding file to the standby database.
When the standby database is updated, DB_FILE_NAME_CONVERT is used to convert the datafile name on the primary database to a datafile name on the standby database. The file must exist and be writable on the standby database or the recovery process will halt with an error.
The DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters must have two strings. The first string is a sequence of characters to be looked for in a primary database filename. If that sequence of characters is matched, it is replaced by the second string to construct the standby database filename.
Figure 2-1 shows how the filename conversion parameters work.
If you execute the following statements, then the conversion parameters do not apply to the affected files:
To learn how to add datafiles to the standby database, see Adding Tablespaces or Datafiles to the Primary Database.
See Also:
The naming conventions for archived redo logs on the standby site depend on whether the standby database is part of a managed standby environment. This section contains the following topics:
When in a managed standby environment, the standby database uses the STANDBY_ARCHIVE_DEST parameter in the standby initialization parameter file to determine the directory in which to store the archived redo logs. Oracle uses this value in conjunction with LOG_ARCHIVE_FORMAT to generate the archived log filenames on the standby site.
Oracle stores the fully qualified filenames in the standby control file. Managed recovery uses this information to perform the recovery operation. Access this information through the V$ARCHIVED_LOG view:
SQL> SELECT name FROM v$archived_log; NAME -------------------------------------------------------------------------------- /arc_dest/log_1_771.arc /arc_dest/log_1_772.arc /arc_dest/log_1_773.arc /arc_dest/log_1_774.arc /arc_dest/log_1_775.arc
With the exception of RECOVER MANAGED STANDBY DATABASE, the RECOVER STANDBY DATABASE statements rely on one of the following to provide the location of the archived files:
Even if you run a standby database in managed recovery mode, manual recovery may sometimes be necessary; for example, when an archived redo log is absent from the standby site. This situation can occur when a network error prevents one or more archived redo logs from being transferred by the primary database.
Issuing the RECOVER STANDBY DATABASE statement manually in these circumstances requires you to use the LOG_ARCHIVE_DEST parameter to locate the necessary archived redo log. For a standby database that is normally in managed recovery mode, Oracle Corporation recommends setting STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST to the same value. In this way, a standby database can access the same set of archived redo logs whether in managed or manual recovery mode.
How you configure the initialization parameter file for the standby database depends on several interrelated factors:
Table 2-6 illustrates which parameters you should set depending on the standby database configuration.
Following are some sample initialization parameter settings for a managed standby database that resides on the same host as primary database PROD1.
COMPATIBLE = 8.1.6 CONTROL_FILES = /oracle/work/standby/dbs/cf1.f DB_FILE_NAME_CONVERT = /oracle/dbs/tbs, /oracle/work/standby/dbs/standby DB_NAME = prod1 LOCK_NAME_SPACE = stby # required for a standby on the same host as the primary LOG_ARCHIVE_DEST = /oracle/work/standby/dbs/arc/ LOG_ARCHIVE_FORMAT = arc_%t_%s.ar LOG_ARCHIVE_TRACE = 8 LOG_FILE_NAME_CONVERT = /oracle/dbs/t1, /oracle/work/standby/dbs/standby_t1 STANDBY_ARCHIVE_DEST = /oracle/work/standby/dbs/arc/
Following are some sample initialization parameter settings for a managed standby database that resides on a different host from primary database PROD1, but uses the same directory structure.
COMPATIBLE = 8.1.6 CONTROL_FILES = /oracle/work/standby/dbs/cf1.f DB_NAME = prod1 LOG_ARCHIVE_DEST = /oracle/work/standby/arc_dest/ LOG_ARCHIVE_FORMAT = arc_%t_%s.ar LOG_ARCHIVE_TRACE = 6 STANDBY_ARCHIVE_DEST = /oracle/work/standby/arc_dest/
Following are some sample initialization parameter settings for a standby database in manual recovery mode that resides on a different host from primary database PROD1, and also uses a different directory structure.
COMPATIBLE = 8.1.6 CONTROL_FILES = /oracle/work/standby/cf1.f DB_FILE_NAME_CONVERT = /oracle, /oracle/work/standby DB_NAME = prod1 LOG_ARCHIVE_DEST = /oracle/work/arc_dest/ LOG_ARCHIVE_FORMAT = arc_%t_%s.ar LOG_ARCHIVE_TRACE = 34 LOG_FILE_NAME_CONVERT = /oracle/dbs/t1, /oracle/work/standby/dbs/standby_t1
This scenario assumes the following:
Following are sample settings for LOG_ARCHIVE_DEST_1, LOG_ARCHIVE_DEST_2, LOG_ARCHIVE_DEST_3, and LOG_ARCHIVE_DEST_4 in the initialization parameter file for the primary database PROD1:
# This example specifies a local archiving destination and enables the # destination. LOG_ARCHIVE_DEST_1 = 'LOCATION=/arc_dest/' LOG_ARCHIVE_DEST_STATE_1 = ENABLE # This example specifies net service name "standby1", makes archiving mandatory, # and enables the destination. # A REOPEN value of 5 indicates that if the LOG_ARCHIVE_DEST_2 location # encounters an error during archival of a redo log file, the destination # remains inactive until the archival of a redo file is about to begin and 5 # seconds has elapsed. At that time, Oracle again attempts the archival to LOG_ # ARCHIVE_DEST_2. # If Oracle encounters an error when archiving to a mandatory destination, the # destination is inactive for the duration of the archival of the current redo # log file. The destination may be reactivated (based on the REOPEN attribute) # at the start of the archival of another redo log. LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1 MANDATORY REOPEN=5' LOG_ARCHIVE_DEST_STATE_2 = ENABLE # Specifies net service name "standby2", makes archiving optional, and makes # Oracle retry archiving after 5 seconds should an error occur. The destination # is enabled. LOG_ARCHIVE_DEST_3 = 'SERVICE=standby2 OPTIONAL REOPEN=5' LOG_ARCHIVE_DEST_STATE_3 = ENABLE # Specifies net service name "standby3", makes archiving optional, and makes # Oracle retry archiving after 60 seconds should an error occur. The # destination is deferred, however, which means that Oracle has currently # disabled archiving to this destination. LOG_ARCHIVE_DEST_4 = 'SERVICE=standby3 OPTIONAL REOPEN=60' LOG_ARCHIVE_DEST_STATE_4 = DEFER
Following are settings in the primary host tnsnames.ora file for connecting to the standby databases STANDBY1, STANDBY2, and STANDBY3 in the preceding example:
# The standby1 database is on the same node as the primary database. Connection # is made through the IPC protocol. standby1 = (DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=local_standby)) (CONNECT_DATA= (SID=stby1) (SERVER=DEDICATED))) # The standby2 database is on a different node from the primary database. # Connection is made through the TCP protocol. standby2 = (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=remote2) (PORT=1512)) (CONNECT_DATA= (SID=stby2) (GLOBAL_NAME=standby2) (SERVER=DEDICATED))) # The standby3 database is on a different node from the primary database. # Connection is made through the TCP protocol. standby3 = (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=remote3) (PORT=1523)) (CONNECT_DATA= (SID=stby3) (GLOBAL_NAME=standby3) (SERVER=DEDICATED)))
Following are the settings in the listener.ora files for the standby databases STANDBY1, STANDBY2, and STANDBY3:
# The listener settings for standby1 on host local LISTENER = (ADDRESS_LIST= (ADDRESS= (PROTOCOL=ipc) (KEY=local_standby))) SID_LIST_LISTENER = (SID_LIST= (SID_DESC=(SID_NAME=stby1)(ORACLE_HOME=/oracle)) # The listener settings for standby2 on the remote host remote2 LISTENER = (ADDRESS_LIST= (ADDRESS= (PROTOCOL=tcp) (HOST=remote2) (PORT=1512))) SID_LIST_LISTENER = (SID_LIST= (SID_DESC=(SID_NAME=stby2)(ORACLE_HOME=/oracle)) # The listener settings for standby3 on the remote host remote3 LISTENER = (ADDRESS_LIST= (ADDRESS= (PROTOCOL=tcp) (HOST=remote3) (PORT=1523))) SID_LIST_LISTENER = (SID_LIST= (SID_DESC=(SID_NAME=stby3)(ORACLE_HOME=/fs3/oracle))
Following are settings in the initialization parameter files for the standby databases STANDBY1, STANDBY2, and STANDBY3 in the preceding example. These settings determine the filenames on the standby database for the archived redo logs:
# The init.ora values for the standby1 database, which is on the same host as # the primary database STANDBY_ARCHIVE_DEST = /oracle/standby/arc/ LOG_ARCHIVE_DEST = /oracle/standby/arc/ # the location is the same as # STANDBY_ARCHIVE_DEST LOG_ARCHIVE_FORMAT = log%t_%s.arc LOG_ARCHIVE_TRACE = 16 LOCK_NAME_SPACE = foo_bar DB_FILE_NAME_CONVERT = /oracle/dbs, /oracle/standby/dbs LOG_FILE_NAME_CONVERT = /oracle/dbs, /oracle/standby/dbs # The init.ora values for the standby2 database, which is on # host remote2. Host remote2 uses the same directory structure as host local. STANDBY_ARCHIVE_DEST = /oracle/standby/arc/ LOG_ARCHIVE_DEST = /oracle/standby/arc/ # the location is the same as # STANDBY_ARCHIVE_DEST LOG_ARCHIVE_FORMAT = log%t_%s.arc LOG_ARCHIVE_TRACE = 16 # The init.ora values for the standby3 database, which is on host remote3. Host # remote3 uses a different directory structure from host local. STANDBY_ARCHIVE_DEST = /fs3/arc_dest/ LOG_ARCHIVE_DEST = /fs3/arc_dest/ # the location is the same as # STANDBY_ARCHIVE_DEST LOG_ARCHIVE_FORMAT = log%t_%s.arc DBS_FILE_NAME_CONVERT = /oracle, /fs3/oracle LOG_FILE_NAME_CONVERT = /oracle, /fs3/oracle
The final stage of standby database preparation is starting the standby database instance so you can begin manual or managed recovery. This stage involves three tasks:
After all necessary parameter and network files have been configured, you can start the standby instance. Note that if the instance is not started, the standby database cannot receive archived redo logs that are automatically transmitted to the standby site by the primary database.
SQL> CONNECT sys/change_on_install@standby1
SQL> STARTUP NOMOUNT pfile=initSTANDBY.ora;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Sometimes all of the primary datafiles and redo log files cannot be renamed in the standby control file by conversion parameters. For example, assume that your database has the following datafiles, which you want to rename as shown in the following table:
| Primary Filename | Standby Filename |
|---|---|
|
/oracle/dbs/df1.f |
/standby/df1.f |
|
/oracle/dbs/df2.f |
/standby/df2.f |
|
/data/df3.f |
/standby/df3.f |
You can set DB_FILE_NAME_CONVERT as follows to convert the filenames for the first two datafiles:
DB_FILE_NAME_CONVERT = '/oracle/dbs', '/standby'
Nevertheless, this parameter will not capture the renaming of /data/df3.f. You must rename this datafile manually in the standby database control file by issuing a SQL statement as follows:
SQL> ALTER DATABASE RENAME FILE '/data/df3.f' to '/standby/df3.f';
SQL> STARTUP NOMOUNT pfile=initSTANDBY1.ora; SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';
When you manually rename all of the datafiles that are not captured by the DB_FILE_NAME_CONVERT parameter, the standby database control file can correctly interpret the log stream during the recovery process.
If you configured the primary initialization parameter file to archive to the standby site, you should enable these new parameter settings after starting the standby instance and the listener on the standby site.
You can make changes to the primary database initialization parameter file while the database is open, but the changes only take effect when the instance is restarted. If the database is open and you want to avoid restarting it, enable the parameter changes dynamically using ALTER SYSTEM statements.
For example, assume that you made the following changes to the initialization parameter file while the database was open:
LOG_ARCHIVE_DEST_1="LOCATION=/arc_dest/ MANDATORY REOPEN=2"; LOG_ARCHIVE_DEST_2="SERVICE=stby1 MANDATORY REOPEN=2"; LOG_ARCHIVE_DEST_STATE_1=ENABLE; LOG_ARCHIVE_DEST_STATE_2=ENABLE; LOG_ARCHIVE_MIN_SUCCEED_DEST=2;
You can then connect to the primary database using SQL*Plus and issue ALTER SYSTEM statements as follows to enable these settings:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1="LOCATION=/arc_dest/ MANDATORY REOPEN=2"; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2="SERVICE=stby1 MANDATORY REOPEN=2'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; ALTER SYSTEM SET LOG_ARCHIVE_MIN_SUCCEED_DEST=2;
Once you have enabled these changes, the primary database can start archiving redo logs to a standby database.
If you encounter a problem during standby database preparation, it will probably be one of the following:
If the standby site is not receiving the logs, the first thing you should do is obtain information about the archiving status of the primary database by querying the V$ARCHIVE_DEST view. Check especially for error messages. For example, enter:
SQL> SELECT dest_id "ID", 2> status "DB_status", 3> destination "Archive_dest", 4> error "Error" 5> FROM v$archive_dest; ID DB_status Archive_dest Error -- --------- ------------------------------ -------------------------------------- 1 VALID /vobs/oracle/work/arc_dest/arc 2 ERROR standby1 ORA-16012: Archivelog standby database identifier mismatch 3 INACTIVE 4 INACTIVE 5 INACTIVE 5 rows selected.
If the output of the query does not help you, check the following list of possible issues. If any of the following conditions is not met, the primary database will fail to archive to the standby site:
tnsnames.ora file at the primary site.
listener.ora file has not been configured correctly at the standby site.
If any of the following conditions is not met, you cannot mount the standby database:
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|