| Oracle Data Guard Concepts and Administration Release 2 (9.2) Part Number A96653-02 |
|
|
View PDF |
| Oracle Data Guard Concepts and Administration Release 2 (9.2) Part Number A96653-02 |
|
|
View PDF |
This chapter describes how to view the current database initialization parameters, how to modify a server parameter file (SPFILE), and provides reference information for the initialization parameters that affect instances in a Data Guard configuration.
All database initialization parameters are contained in either an initialization parameter file (PFILE) or a server parameter file (SPFILE). As an alternative to specifying parameters in an initialization parameter file or server parameter file, you can modify dynamic parameters at runtime using the ALTER SYSTEM SET or ALTER SESSION SET statements.
The following table describes the methods you can use to view the current initialization parameter settings:
The following example queries the V$PARAMETER view for the CONTROL_FILES parameter setting:
SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'CONTROL_FILES';
The server parameter file is a binary file and therefore cannot be edited manually. To change values in the server parameter file, you must export it to an editable format, make changes, and then import it back into a server parameter file, or use the ALTER SYSTEM SET statement to change the server parameter values. These methods are described in the following sections.
To modify a server parameter file do the following:
CREATE PFILE statement to export the server parameter file to a text initialization parameter file, as shown in Example 1 and Example 2 (that follow this list).
An initialization parameter file is a text file and can therefore be edited manually. You must have the SYSDBA or the SYSOPER system privilege to execute this statement. The exported file is created on the database server system. It contains any comments associated with the parameter in the same line as the parameter setting.
CREATE SPFILE statement to create a new server parameter file from the edited initialization parameter file, as shown in Example 3 and Example 4 (that follow this list).
You must have the SYSDBA or the SYSOPER system privilege to execute this statement.
This example creates a text initialization parameter file from the server parameter file without specifying filenames:
CREATE PFILE FROM SPFILE;
Because no names are specified for the files, an operating system-specific name is used for the initialization parameter file, and it is created from the operating system-specific default server parameter file.
This example creates a text initialization parameter file from a server parameter file where the names of the files are specified:
SQL> CREATE PFILE='/u01/oracle/dbs/test_init.ora' 2> FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';
This example creates a server parameter file from the initialization parameter file /u01/oracle/dbs/test_init.ora. An SPFILE name is not specified, so the file is created using an operating system-specific default server parameter filename and location:
SQL> CREATE SPFILE FROM PFILE='/u01/oracle/dbs/test_init.ora';
This example creates a server parameter file and supplies a name for both the server parameter file and the initialization parameter file:
SQL> CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora' 2> FROM PFILE='/u01/oracle/dbs/test_init.ora';
As an alternative to exporting, editing, and importing the server parameter file, as described in the previous section, you can use the SQL ALTER SYSTEM SET statement to change initialization parameter values. Make sure that you use the SCOPE clause to apply the change in the server parameter file.
By default, the scope is set to BOTH if a server parameter file was used to start up the instance, and the scope is set to MEMORY if an initialization parameter file was used to start up the instance. The following example adds a new local archive log destination to the server parameter file:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_4= 2> 'LOCATION=/disk1/oracle/oradata/payroll/', 3> 'MANDATORY', 'REOPEN=2' SCOPE=SPFILE;
The following list shows the initialization parameters that affect instances in a Data Guard environment:
The following sections provide a description for each parameter that indicates if the parameter applies to the primary database role, the standby database role, or both. For parameters that apply to the standby database role, most of the parameters pertain to both physical and logical standby databases. Any differences are noted.
| See Also:
Oracle9i Database Reference for information about these parameters that is not specific to Data Guard and for the type, default values, and syntax for these initialization parameters. Also refer to your Oracle operating system-specific documentation for more information about setting initialization parameters. |
Limits the amount of data that can be lost and effectively increases the availability of the standby database by forcing a log switch after the amount of time you specify (in seconds) elapses. The standby database will not miss redo logs generated from a time range longer than a value of the ARCHIVE_LAG_TARGET parameter.
Applies to the primary database role
The following example sets the log switch interval to 30 minutes (a typical value):
ARCHIVE_LAG_TARGET = 1800
Controls the compatibility of your database. Set to 9.0.0.0.0 or higher to use the Data Guard broker, logical standby databases, and the enhanced features of physical standby databases. Always set this parameter to the same value on the primary database and standby databases. If the values differ, you might not be able to archive the redo logs from the primary database to the standby database.
Applies to the primary and standby database roles
The following example sets the database compatible level to '9.2.0.0.0':
COMPATIBLE = '9.2.0.0.0'
Specifies the minimum number of days before a reusable record in the control file can be reused. Use this parameter to avoid overwriting a reusable record in the control file (that contains needed information such as an archive log) for a specified period of time. The range of values for this parameter is 0 to 356 days. If this parameter is set to 0, then the reusable records are reused as needed.
Applies to the primary and standby database roles
The following example sets the minimum number of days before a reusable record in the control file can be reused to 20 days:
CONTROL_FILE_RECORD_KEEP_TIME = 20
Specifies the names of one or more control files, separated by commas. Always set this parameter on the standby database to a different value than the CONTROL_FILES parameter for the primary database, if these databases are on the same system. The filenames you specify with the CONTROL_FILES parameter for the standby database must exist at the standby location.
Applies to the primary and standby database roles
The following example specifies two control files for the database instance:
CONTROL_FILE = ("/disk1/oracle/oradata/payroll/control01.ctl", "/disk1/oracle/oradata/payroll/control02.ctl")
Converts the filename of a datafile on the primary database to a filename on the standby database. Because the standby database control file is a copy of the primary database control file, you must use this parameter to convert the standby database filenames when they are different from the primary database filenames. If the standby database is on the same system as the primary database, you must use different path names.
Applies to the physical standby database role
The following example shows the conversion of paths from /dbs/t1/ (primary database) to /dbs/t1/stdby (standby database) and dbs/t2/ (primary database) to dbs/t2/stdby (standby database):
DB_FILE_NAME_CONVERT = ('/dbs/t1/','/dbs/t1/stdby','dbs/t2/ ','dbs/t2/stdby')
Specifies the maximum number of database files that can be open for this database. The primary and standby databases should have the same value for this parameter.
Applies to the primary and standby database roles
The following example specifies that a maximum of 300 database files can be open for this database instance:
DB_FILES = 300
Specifies a database identifier of up to eight characters. For a physical standby database, set the DB_NAME parameter to the same value as it is set in the primary database initialization file. For a logical standby database, set the DB_NAME parameter to a different value from that in the primary database initialization files. Use the DBNEWID (nid) utility to set the database name for a logical standby database, as described in Section 4.2.14.
Applies to the primary and standby database roles
The following example shows that the database name is Sales:
DB_NAME = Sales
The following example shows how to use the DBNEWID utility to set a logical standby database name. You must mount the database before issuing this command.
nid TARGET=SYS/CHANGE_ON_INSTALL@LogicalSDB DBNAME=SalesLSDB SETNAME=YES
Assigns the fetch archive log (FAL) client name used by the FAL server to refer to the FAL client. This is the Oracle Net service name that the FAL server should use to refer to the standby database. This Oracle Net service name must be configured properly on the FAL server (primary database) to point to the FAL client. Given the dependency of the FAL_CLIENT parameter on the FAL_SERVER parameter, the two parameters should be configured or changed at the same time. This parameter is set on the standby site.
Applies to the physical standby database role in managed recovery mode
The following example assigns the FAL client to the Oracle Net service name StandbyDB:
FAL_CLIENT = StandbyDB
Assigns the Oracle Net service name that the standby database should use to connect to the fetch archive log (FAL) server. This parameter is set on the standby system.
String
None
Applies to the physical standby database role in managed recovery mode
The following example shows that the FAL server is assigned to the Oracle Net service name PrimaryDB:
FAL_SERVER = PrimaryDB
Specifies the name space that the distributed lock manager (DLM) uses to generate lock names. Set this parameter to a unique value in each initialization parameter file if the standby database has the same name as the primary database and is on the same system or cluster.
|
Note: If you do not set the |
Applies to the primary and standby database roles
The following example shows that the LOCK_NAME_SPACE is set to payroll2 in the standby initialization parameter file:
LOCK_NAME_SPACE = payroll2
Defines an archive log destination and attributes for log transport services. This parameter is discussed in Chapter 5 and in Chapter 12.
Applies to the primary and standby database roles
The following example shows a remote archive log destination to a standby database:
LOG_ARCHIVE_DEST_2 = 'SERVICE=payroll2 OPTIONAL REOPEN=180'
Specifies the state of the destination specified by the LOG_ARCHIVE_DEST_n parameter. The possible values are as follows:
ENABLE specifies that a valid log archive destination can be used for a subsequent archiving operation (automatic or manual). This is the default.DEFER specifies that valid destination information and attributes are preserved, but the destination is excluded from archiving operations until you reenable archiving with the ENABLE option.ALTERNATE specifies that the destination is not enabled, but will become enabled if communication to another destination fails.Applies to the primary and standby database roles
The following example shows the LOG_ARCHIVE_DEST_STATE_2 state is set to ENABLE:
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
Specifies the format for archived redo log filenames. STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT initialization parameters are concatenated to generate fully-qualified standby database archived redo log filenames.
Applies to the primary and standby database roles
The following example specifies the format for the archive redo log filename using a database ID (%d), thread (%t), and sequence number (%s):
LOG_ARCHIVE_FORMAT = 'log%d_%t_%s.arc'
Specifies the number of archiver background processes to be invoked by the database server. This value is evaluated at instance startup if the LOG_ARCHIVE_START parameter has the value TRUE; otherwise, this parameter is evaluated when the archiver process is invoked.
Applies to the primary and standby database roles
The following example sets the number of log archiver processes to 2:
LOG_ARCHIVE_MAX_PROCESSES = 2
Defines the minimum number of destinations that must receive redo logs successfully before the log writer process on the primary database can reuse the online redo logs.
Applies to the primary and standby database roles
The following example sets the minimum number of destinations that must succeed to 2:
LOG_ARCHIVE_MIN_SUCCEED_DEST = 2
Indicates if archiving should be automatic or manual when the instance starts up. To enable automatic archiving of filled log groups, set LOG_ARCHIVE_START in the initialization parameter file to TRUE. To disable the automatic archiving of filled online redo log groups, set LOG_ARCHIVE_START to FALSE. You cannot specify this parameter in a server parameter file.
Applies to the primary and standby database roles
The following example sets LOG_ARCHIVE_START to TRUE:
LOG_ARCHIVE_START = TRUE
Controls trace output generated by the ARCn and LGWR processes and foreground processes on the primary database, and the RFS and FAL server processes on the standby database. It allows you to see the progression of the archivedd redo logs to the standby site. The Oracle database server writes an audit trail of the redo logs received from the primary database into a trace file. You specify the location of the trace file using the USER_DUMP_DEST parameter. Possible values include:
Applies to the primary and standby database roles
The following example sets the LOG_ARCHIVE_TRACE to 1:
LOG_ARCHIVE_TRACE = 1
Converts the filename of a log on the primary database to the filename of a log on the standby database. Adding a log to the primary database necessitates adding a corresponding log to the standby database. When the standby database is updated, this parameter is used to convert the log filename from the primary database to the log filename on the standby database. This parameter is necessary when the standby database uses different path names from the primary database. If the standby database is on the same system as the primary database, you must use different path names.
String
None
Applies to the physical standby database roles
The following example shows the conversion of two paths. It converts /dbs/t1/ (primary database) to /dbs/t1/stdby (standby database) and dbs/t2/ (primary database) to dbs/t2/stdby (standby database):
LOG_FILE_NAME_CONVERT = ('/dbs/t1/','/dbs/t1/stdby','dbs/t2/ ','dbs/t2/stdby')
Specifies the level of concurrency for redo data allocation to allow parallel generation of redo data. Set this value to 1 for the primary database and for all logical standby databases, The default value is 1.
Integer
The default value for this parameter is 1.
Applies to the logical standby database role only
The following example sets the LOG_PARALLELISM parameter to 1:
LOG_PARALLELISM = 1
This parameter specifies the maximum number of parallel servers that can work on log apply services on the logical standby database. This parameter is not used with physical standby databases.
Log apply services use parallel query processes to perform processing, and use parallel apply algorithms to maintain a high level of database apply performance. A minimum of 5 parallel query processes is required for a logical standby database. Thus, the value of the PARALLEL_MAX_SERVERS parameter must be set to a value of 5 or greater.
Applies to the primary and logical standby database roles
The following example sets the PARALLEL_MAX_SERVERS initialization parameter to 10:
PARALLEL_MAX_SERVERS = 10
Enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs.
Possible values are:
TRUE
Enables the sending of redo logs to a remote destination or receipt of remote redo logs. Set this parameter to TRUE on the primary database and standby databases in the Data Guard environment to allow the primary database to send redo logs to the standby database and to allow the standby database to receive redo logs for archiving from the primary database.
FALSE
Disables both the sending and receiving of redo logs.
SEND
Enables the primary database to send redo logs to the standby database.
RECEIVE
Enables the standby database to receive redo logs from the primary database.
To independently enable and disable the sending and receiving of remote redo logs, use the send and receive values. The SEND and RECEIVE values together are the same as specifying true. Every instance of an Oracle Real Application Clusters database must contain the same REMOTE_ARCHIVE_ENABLE value.
Applies to the primary and standby database roles
The following example enables the remote log sending or receiving:
REMOTE_ARCHIVE_ENABLE = true
Specifies (in bytes) the size of the shared pool. Log apply services of logical standby databases use a shared pool system global area (SGA) to stage the information read from the redo logs. The more SGA that is available, the more information that can be staged. By default, one quarter of the value set for the SHARED_POOL_SIZE parameter will be used by log apply services. You can change this default using the DBMS_LOGSTDBY.APPLY_SET PL/SQL procedure.
Applies to the primary and standby database roles
The following example sets the shared pool size to 33 MB:
SHARED_POOL_SIZE = 33554432
Specifies in bytes the maximum amount of memory the Oracle database server will use for a sort operation. Set this parameter to a value that allows you to execute the SELECT * FROM V$PARAMETER statement when the database is not open. This prevents errors if you attempt to sort without temporary tablespaces when the database is not open.
Applies to the primary and standby database roles
The following example sets the sort area size to 65536 bytes:
SORT_AREA_SIZE = 65536
Used by a standby database to determine the archive location of online redo logs received from the primary database. The RFS process uses this value in conjunction with the LOG_ARCHIVE_FORMAT value to generate the fully-qualified standby database redo log filenames. Note that the generated filename is overridden by the TEMPLATE attribute of the LOG_ARCHIVE_DEST_n parameter.
You can see the value of this parameter by querying the V$ARCHIVE_DEST data dictionary view.
Applies to the standby database role
The following example specifies that '/u01/oracle/oradata/archive' is the redo log file path on the standby database:
STANDBY_ARCHIVE_DEST = '/u01/oracle/oradata/archive'
Enables or disables automatic standby file management.
The possible values for this parameter are:
When set to AUTO, this parameter automates the creation and deletion of datafile filenames on the standby site using the same filenames as the primary site. When set to MANUAL, datafile creation and deletion are not automated and might cause managed recovery operations to terminate.
Use this parameter with the DB_FILE_NAME_CONVERT initialization parameter to ensure that the correct files are created on the standby site when the standby database has a different file path from the primary database. Note that this parameter does not support datafile filenames on RAW devices.
Applies to the primary and standby database roles
The following example enables automatic standby file management:
STANDBY_FILE_MANAGEMENT = TRUE
Specifies the directory path name where the database server will write debugging trace files on behalf of a user process. Use the LOG_ARCHIVE_TRACE parameter to control the trace information.
Applies to the primary and standby database roles
The following example specifies the location for the database trace files to be '/u01/oracle/oradata/utrc':
USER_DUMP_DEST = '/u01/oracle/oradata/utrc'
|
 Copyright © 1998, 2002 Oracle Corporation. All Rights Reserved. |
|