Skip Headers
Oracle® Database High Availability Best Practices
11g Release 1 (11.1)

B28282-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

A Database SPFILE and Oracle Net Configuration File Samples

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:

The tables and files are shown for the following configuration:

A.1 SPFILE Samples

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

*.FAST_START_PARALLEL_ROLLBACK=HIGH

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'

*.LOG_ARCHIVE_MAX_PROCESSES=3Foot 1 

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'

*.FAL_CLIENT='SALES_BOSTON'

*.FAL_SERVER='SALES_BOSTON'

*.FAL_SERVER='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_NEWYORK'

*.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'

A.2 Oracle Net Configuration Files

This section contains examples of the following Oracle Net configuration file settings:

A.2.1 SQLNET.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 at http://www.otn.oracle.com/goto/maa

This white paper contains instructions for calculating the bandwidth delay

A.2.2 LISTENER.ORA Example for All Hosts Using Dynamic Instance Registration

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:

A.2.3 TNSNAMES.ORA Example for All Hosts Using Dynamic Instance Registration

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)))