The tables and file samples in this appendix are included to illustrate the best practices as they relate to different high availability architectures. These samples also clarify how the database server parameter file (SPFILE) relates to the Oracle Net configuration for dynamic service registration.
This appendix includes the following tables and sample files:
Table A-1, "Generic Parameters for Primary, Physical Standby, and Logical Standby Databases"
Table A-2, "Oracle RAC Parameters for Primary, Physical Standby, and Logical Standby"
Table A-3, "Data Guard Parameters for Primary, Physical Standby, and Logical Standby"
Table A-4, "Data Guard Broker Parameters for Primary, and Physical and Logical Standbys"
Table A-5, "Data Guard (No Broker) Parameters for Primary, and Physical and Logical Standby"
Table A-6, "Data Guard Parameters for Primary and Physical Standby Database Only"
Table A-7, "Data Guard Parameters for Primary and Logical Standby Database Only"
The tables and files are shown for the following configuration:
ORACLE_BASE=/mnt/app/oracle
Database flash recovery area is /flash_recovery
The tables in this section represent the database, Oracle RAC, and Data Guard parameter file values. Some parameters appear in both the generic database parameter table and the Oracle RAC parameter table. If Oracle RAC is being used, then use the value in the Oracle RAC parameter table instead of the value in the generic database parameter table.
The parameters show the configuration for a database in New York City and an option for a physical standby database and a logical standby database in Boston. The primary database is the SALES database. For a single-instance database, the ORACLE_SID parameter values are SALES, SALES_PHYS, and SALES_LOG. In an Oracle RAC configuration, the corresponding instance number is appended to each of the ORACLE_SID parameter values.
Table A-1 shows generic best practice SPFILE parameters for primary, physical standby, and logical standby databases.
Table A-1 Generic Parameters for Primary, Physical Standby, and Logical Standby Databases
| NewYork (Primary Database) | Boston (Physical Standby) | Boston (Logical Standby) |
|---|---|---|
*.COMPATIBLE='11.1.0' |
Same as NewYork |
Same as NewYork |
*.CONTROL_FILES= '_+DATA/SALES/controlfiles/ control.265.263563526', '+RECO/SALES/controlfiles/ control.276.263563526' |
*.CONTROL_FILES= '+DATA/SALES/controlfiles/ backup.474.3736463483', '+RECO/SALES/cnortolfiles/ backup.363.3736463483' |
*.CONTROL_FILES= '+DATA/SALES_LOG/controlfiles/ backup.354.25365373', '+RECO/SALES_LOG/controlfiles/ backup.352.25365373' |
*.CONTROL_FILE_RECORD_KEEP_TIME=10 |
Same as NewYork |
Same as NewYork |
*.DB_NAME='SALES' |
Same as NewYork |
*.DB_NAME='SALES_LOG' |
*.DB_CREATE_FILE_DEST=+DATA |
Same as NewYork |
Same as NewYork |
*.DB_RECOVERY_FILE_DEST=+RECO |
Same as NewYork |
Same as NewYork |
*.DB_RECOVERY_FILE_DEST_SIZE=100G |
Same as NewYork |
Same as NewYork |
*.DB_FLASHBACK_RETENTION_TARGET=240 |
Same as NewYork |
Same as NewYork |
*.BACKGROUND_CORE_DUMP=FULL |
Same as NewYork |
Same as NewYork |
*.DIAGNOSTIC_DEST= '/mnt/app/oracle' |
Same as NewYork |
Same as NewYork |
*.DB_ULTRA_SAFE=DATA_AND_INDEXFoot 1 |
Same as NewYork |
Same as NewYork |
*.LOG_ARCHIVE_FORMAT= 'arch_%t_%S_%r.log' |
Same as NewYork |
Same as NewYork |
*.LOG_ARCHIVE_TRACE=0 |
Same as NewYork |
Same as NewYork |
*.FAST_START_MTTR_TARGET=300 |
Same as NewYork |
Same as NewYork |
|
|
Same as NewYork |
Same as NewYork |
*.STATISTICS_LEVEL=TYPICAL |
Same as NewYork |
Same as NewYork |
*.LOCAL_LISTENER='SALES_lsnr' |
Same as NewYork |
Same as NewYork |
*.REMOTE_LISTENER= 'SALES_remotelsnr_NEWYORK' |
*.REMOTE_LISTENER= 'SALES_remotelsnr_BOSTON' |
*.REMOTE_LISTENER= 'SALES_remotelsnr_BOSTON' |
*.UNDO_MANAGEMENT=AUTO |
Same as NewYork |
Same as NewYork |
*.UNDO_RETENTION=900 |
Same as NewYork |
Same as NewYork |
*.UNDO_TABLESPACE='UNDOTBS' |
Same as NewYork |
Same as NewYork |
*.RESUMABLE_TIMEOUT=900 |
Same as NewYork |
Same as NewYork |
*.LOG_ARCHIVE_DEST_1= 'location=USE_DB_RECOVERY_FILE_DEST mandatory valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=SALES_NEWYORK' |
*.LOG_ARCHIVE_DEST_1= 'location=USE_DB_RECOVERY_FILE_DEST mandatory valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=SALES_BOSTON' |
*.LOG_ARCHIVE_DEST_1= 'location=USE_DB_RECOVERY_FILE_DEST max_failure=0 mandatory valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=SALES_BOSTON_LOG' |
Footnote 1 Turn off DB_ULTRA_SAFE by setting DB_BLOCK_CHECKING=FALSE if recovery performance is adversely affected. See "Use Data Guard and Configure the DB_ULTRA_SAFE Initialization Parameter".
Table A-2 shows Oracle RAC best practice SPFILE parameters for primary, physical standby, and logical standby databases.
Table A-2 Oracle RAC Parameters for Primary, Physical Standby, and Logical Standby
| NewYork (Primary Database) | Boston (Physical Standby) | Boston (Logical Standby) |
|---|---|---|
*.CLUSTER_DATABASE=TRUE |
Same as NewYork |
Same as NewYork |
SALES1.THREAD=1 |
SALES_PHYS1.THREAD=1 |
SALES_LOG1.THREAD=1 |
SALES2.THREAD=2 |
SALES_PHYS2.THREAD=2 |
SALES_LOG2.THREAD=2 |
SALES1.INSTANCE_NUMBER=1 |
SALES_PHYS1.INSTANCE_NUMBER=1 |
SALES_LOG1.INSTANCE_NUMBER=1 |
SALES2.INSTANCE_NUMBER=2 |
SALES_PHYS2.INSTANCE_NUMBER=2 |
SALES_LOG2.INSTANCE_NUMBER=2 |
SALES1.INSTANCE_NAME= SALES_NEWYORK1 |
SALES_PHYS1.INSTANCE_NAME= SALES_BOSTON1 |
SALES_LOG1.INSTANCE_NAME= SALES_BOSTON_LOG1 |
SALES2.INSTANCE_NAME= SALES_NEWYORK2 |
SALES_PHYS2.INSTANCE_NAME= SALES_BOSTON2 |
SALES_LOG2.INSTANCE_NAME= SALES_BOSTON_LOG2 |
SALES1.UNDO_TABLESPACE= 'UNDOTBS1' |
SALES_PHYS1.UNDO_TABLESPACE= 'UNDOTBS1' |
SALES_LOG1.UNDO_TABLESPACE= 'UNDOTBS1' |
SALES2.UNDO_TABLESPACE= 'UNDOTBS2' |
SALES_PHYS2.UNDO_TABLESPACE= 'UNDOTBS2' |
SALES_LOG2.UNDO_TABLESPACE= 'UNDOTBS2' |
Table A-3 shows Data Guard best practice SPFILE parameters for primary, physical standby, and logical standby databases. You must set these parameters whether or not you use the broker.
Table A-3 Data Guard Parameters for Primary, Physical Standby, and Logical Standby
| NewYork (Primary Database) | Boston (Physical Standby) | Boston (Logical Standby) |
|---|---|---|
*.FAL_CLIENT='SALES_NEWYORK' |
*.FAL_CLIENT='SALES_BOSTON' |
*.FAL_CLIENT='SALES_BOSTON_LOG' |
*.FAL_SERVER= 'SALES_BOSTON','SALES_BOSTON_LOG' |
*.FAL_SERVER= 'SALES_NEWYORK','SALES_BOSTON_LOG' |
*.FAL_SERVER= 'SALES_NEWYORK','SALES_BOSTON' |
*.DB_UNIQUE_NAME='SALES_NEWYORK' |
*.DB_UNIQUE_NAME='SALES_BOSTON' |
*.DB_UNIQUE_NAME='SALES_BOSTON_LOG' |
*.LOG_ARCHIVE_CONFIG='DG_CONFIG= (SALES_NEWYORK,SALES_BOSTON,SALES_BOSTON_LOG)' |
Same as NewYork |
Same as NewYork |
*.LOG_ARCHIVE_DEST_4= 'location=+RECO/SALES_NEWYORK/archivelog/SRL/ mandatory valid_for=(STANDBY_LOGFILES,STANDBY_ROLE) db_unique_name=SALES_NEWYORK' |
*.LOG_ARCHIVE_DEST_4= 'location=+RECO/SALES_BOSTON/archivelog/SRL/ mandatory valid_for=(STANDBY_LOGFILES,STANDBY_ROLE) db_unique_name=SALES_BOSTON' |
*.LOG_ARCHIVE_DEST_4= 'location=+RECO/SALES_BOSTON_LOG/archivelog/SRL/ mandatory valid_for=(STANDBY_LOGFILES,STANDBY_ROLES) db_unique_name=SALES_BOSTON_LOG' |
|
|
Same as NewYork |
Same as NewYork |
Footnote 1 See Section 2.6.5.2.3 for best practices about setting the LOG_ARCHIVE_MAX_PROCESSES initialization parameter.
Table A-4 shows Data Guard best practice SPFILE parameters for the primary database, and for physical and logical standby databases. If you are using the broker to manage your database environment, then you need set only the values in Table A-3 and Table A-4.
Table A-4 Data Guard Broker Parameters for Primary, and Physical and Logical Standbys
| NewYork (Primary Database) | Boston (Physical Standby) | Boston (Logical Standby) |
|---|---|---|
*.DB_BROKER_CONFIG_FILE_1= '+DATA/SALES_NEWYORK/dr1SALES_NEWYORK.dat' |
*.DB_BROKER_CONFIG_FILE_1= '+DATA/SALES_BOSTON/dr1SALES_ BOSTON.dat' |
*.DB_BROKER_CONFIG_FILE_1= '+DATA/SALES_BOSTON_LOG/dr1SALES_ BOSTON_LOG.dat' |
*.DB_BROKER_CONFIG_FILE_2= '+DATA/SALES_NEWYORK/dr2SALES_NEWYORK.dat' |
*.DB_BROKER_CONFIG_FILE_2= '+DATA/SALES_BOSTON/dr2SALES_ BOSTON.dat' |
*.DB_BROKER_CONFIG_FILE_2= '+DATA/SALES_BOSTON_LOG/dr2SALES_ BOSTON_LOG.dat' |
*.DG_BROKER_START=TRUE |
Same as NewYork |
Same as NewYork |
Table A-5 shows Data Guard best practice SPFILE parameters for primary, physical standby, and logical standby databases if you are not using the broker to manage your database environment. If you are not using the broker, you must also set the parameters in Table A-6 through Table A-9.
Table A-5 Data Guard (No Broker) Parameters for Primary, and Physical and Logical Standby
| NewYork (Primary Database) | Boston (Physical Standby) | Boston (Logical Standby) |
|---|---|---|
*.LOG_FILE_NAME_CONVERT=' ',' ' |
Same as NewYork |
Same as NewYork |
*.STANDBY_FILE_MANAGEMENT=AUTO |
Same as NewYork |
Same as NewYork |
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE |
Same as NewYork |
Same as NewYork |
Table A-6 shows Data Guard best practice SPFILE parameters for primary and physical standby databases only. You must set these parameters if you are not using the broker to manage your database environment.
Table A-6 Data Guard Parameters for Primary and Physical Standby Database Only
| NewYork (Primary Database) | Boston (Physical Standby Database) |
|---|---|
*.FAL_CLIENT='SALES_NEWYORK' |
|
|
|
|
*.LOG_ARCHIVE_DEST_2= 'service=SALES_BOSTON sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON' |
*.LOG_ARCHIVE_DEST_2= 'service=SALES_NEWYORK sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_NEWYORK' |
Table A-7 shows Data Guard best practice SPFILE parameters for primary and logical standby databases only. You must set these parameters if you are not using the broker to manage your database environment.
Table A-7 Data Guard Parameters for Primary and Logical Standby Database Only
| NewYork (Primary Database) | Boston (Logical Standby Database) |
|---|---|
|
|
*.FAL_CLIENT='SALES_BOSTON_LOG' |
*.FAL_SERVER='SALES_BOSTON_LOG' |
*.FAL_SERVER='SALES_NEWYORK' |
*.LOG_ARCHIVE_DEST_2= 'service=SALES_BOSTON_LOG reopen=15 max_failure=10 sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON_LOG' |
*.LOG_ARCHIVE_DEST_2= 'service=SALES_NEWYORK sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_NEWYORK' |
*.LOG_ARCHIVE_DEST_3= 'location=+RECO/SALES_NEWYORK/archivelog/SRL/ max_failure=0 mandatory valid_for=(STANDBY_LOGFILES,STANDBY_ROLE) db_unique_name=SALES_NEWYORK' |
*.LOG_ARCHIVE_DEST_3='location=+RECO/SALES_BOSTON/archivelog/SRL/ max_failure=0 mandatory valid_for=(STANDBY_LOGFILES,STANDBY_ROLE) db_unique_name=SALES_BOSTON_LOG' |
Table A-8 applies to a Data Guard environment running in either maximum availability mode or maximum protection mode.
Table A-8 Data Guard Parameters for Primary Database, Physical Standby Database, and Logical Standby Database: Maximum Availability or Maximum Protection Modes
| NewYork (Primary Database) | Boston (Physical Standby) | Boston (Logical Standby) |
|---|---|---|
*.LOG_ARCHIVE_DEST_2= 'service=SALES_BOSTON sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON' |
*.LOG_ARCHIVE_DEST_2= 'service=SALES_NEWYORK sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_NEWYORK' |
Not applicable |
*.LOG_ARCHIVE_DEST_3= 'service=SALES_BOSTON_LOG sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON_LOG' |
*.LOG_ARCHIVE_DEST_3= 'service=SALES_BOSTON_LOG sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON_LOG' |
*.LOG_ARCHIVE_DEST_3= 'service=SALES_NEWYORK sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_NEWYORK' |
Table A-9 shows how to change the parameters for a Data Guard environment that is running in maximum performance mode.
Table A-9 Data Guard Parameters for Primary Database, Physical Standby Database, and Logical Standby Database: Maximum Performance Mode
| NewYork (Primary Database) | Boston (Physical Standby) | Boston (Logical Standby) |
|---|---|---|
*.LOG_ARCHIVE_DEST_2= 'service=SALES_BOSTON async net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON' |
*.LOG_ARCHIVE_DEST_2= 'service=SALES_NEWYORK async net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_NEWYORK' |
Not applicable |
*.LOG_ARCHIVE_DEST_3= 'service=SALES_BOSTON_LOG async net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON_LOG' |
*.LOG_ARCHIVE_DEST_3= 'service=SALES_BOSTON_LOG async net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON_LOG' |
*.LOG_ARCHIVE_DEST_3= 'service=SALES_NEWYORK async net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_NEWYORK' |
This section contains examples of the following Oracle Net configuration file settings:
SQLNET.ORA Example for All Hosts Using Dynamic Instance Registration
LISTENER.ORA Example for All Hosts Using Dynamic Instance Registration
TNSNAMES.ORA Example for All Hosts Using Dynamic Instance Registration
# Set dead connection time SQLNET.EXPIRE_TIME = 1 # Disable Nagle's algorithmTCP.NODELAY=yes # Set default SDU for all connections DEFAULT_SDU_SIZE=32767
See Also:
The MAA white paper "Oracle Database 10g Release 2 Best Practices: Data Guard Redo Apply and Media Recovery" located on the MAA Web site athttp://www.otn.oracle.com/goto/maa
This white paper contains instructions for calculating the bandwidth delay
For an Oracle RAC environment, listeners must be listening on the virtual IP addresses (VIP), rather than the local host name.
lsnr_SALES =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=local_host_name)
(PORT=1513)
(QUEUESIZE=1024)))))
PASSWORDS_lsnr_SALES = 876EAE4513718ED9
# Prevent listener administration
ADMIN_RESTRICTIONS_lsnr_SALES=ON
Note:
If you are using the broker to manage your database environment, then you can enable DGMGRL to restart instances during broker operations. Do this by statically registering the service with a specific name with the local listener of each instance. For example:SID_LIST_LISTENER=(SID_LIST_LSNR_SALES=(SID_DESC=(SID_NAME=sidname) (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain) (ORACLE_HOME=oracle_home)))
The value for the GLOBAL_DBNAME attribute must be set to a concatenation of DB_UNIQUE_NAME and DB_DOMAIN in the following format:
db_unique_name_DGMGRL.db_domain
See Also:
Oracle Database Net Services Administrator's Guide for more information about listener password protection
Oracle Data Guard Broker for more information about setting the GLOBAL_DGNAME attribute to register with the local listener
In an Oracle RAC environment, you configure VIP addresses in the address list of the TNSNAMES.ORA file. Configure a VIP address for each database connection definition to enable connectivity to the database instance. The following example uses VIP addresses newyork_host1-vip, newyork_host2-vip, boston_host1-vip, and boston_host2-vip.
# Used for database parameter local_listener
SALES_lsnr =
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1513)))
SALES_remotelsnr_NEWYORK =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=newyork_host1-vip))
(ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=newyork_host2-vip)))
SALES_remotelsnr_BOSTON =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=boston_host1-vip))
(ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=boston_host2-vip)))
# Net service used for communication with SALES database in NewYork
SALES_NEWYORK =
(DESCRIPTION=
(ADDRESS_LIST=
(SEND_BUF_SIZE=4665000)(RECV_BUF_SIZE=4665000)
(ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=newyork_host1-vip))
(ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=newyork_host2-vip)))
(CONNECT_DATA=(SERVICE_NAME=SALES_NEWYORK)))
# Net service used for communication with SALES database in Boston
SALES_BOSTON =
(DESCRIPTION=
(ADDRESS_LIST=
(SEND_BUF_SIZE=4665000)(RECV_BUF_SIZE=4665000)
(ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=boston_host1-vip))
(ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=boston_host2-vip)))
(CONNECT_DATA=(SERVICE_NAME=SALES_BOSTON)))
# Net service used for communication with Logical Standby SALES database in Boston
SALES_BOSTON_LOG =
(DESCRIPTION=
(ADDRESS_LIST=
(SEND_BUF_SIZE=4665000)(RECV_BUF_SIZE=4665000)
(ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=boston_host1-vip))
(ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=boston_host2-vip)))
(CONNECT_DATA=(SERVICE_NAME=SALES_BOSTON_LOG)))