Skip Headers
Oracle® Database High Availability Best Practices
10g Release 2 (10.2)
Part Number B25159-01
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, RAC, and Data Guard parameter file values. Some parameters appear in both the generic database parameter table and the RAC parameter table. If RAC is being used, then the value in the RAC parameter table should be used instead of the value in the generic database parameter table.

The parameters show the configuration for a database in Chicago 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 a 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 SPFILE Parameters for Primary, Physical Standby, and Logical Standby Databases

Chicago (Primary Database) Boston (Physical Standby) Boston (Logical Standby) 
*.COMPATIBLE='10.2.0'

Same as Chicago

Same as Chicago

 
*.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=30

Same as Chicago

Same as Chicago

 
*.DB_NAME='SALES'

Same as Chicago

 
*.DB_NAME='SALES_LOG'

*.DB_CREATE_FILE_DEST=+DATA

Same as Chicago

Same as Chicago

 
*.DB_RECOVERY_FILE_DEST=+RECO

Same as Chicago

Same as Chicago

 
*.DB_RECOVERY_FILE_DEST_SIZE=100G

Same as Chicago

Same as Chicago

 
*.DB_FLASHBACK_RETENTION_TARGET=240

Same as Chicago

Same as Chicago

 
*.BACKGROUND_CORE_DUMP=FULL

Same as Chicago

Same as Chicago

 
*.BACKGROUND_DUMP_DEST=
'mnt/app/oracle/admin/SALES/bdump'

*.BACKGROUND_DUMP_DEST=
'mnt/app/oracle/admin/
SALES/bdump'

*.BACKGROUND_DUMP_DEST=
'mnt/app/oracle/admin/
SALES_LOG/bdump'

*.CORE_DUMP_DEST=
'/mnt/app/oracle/admin/SALES/cdump'

*.CORE_DUMP_DEST=
'/mnt/app/oracle/admin/
SALES/cdump'

*.CORE_DUMP_DEST=
'/mnt/app/oracle/admin/
SALES_LOG/cdump'

*.USER_DUMP_DEST=
'/mnt/app/oracle/admin/SALES/udump'

*.USER_DUMP_DEST=
'/mnt/app/oracle/admin/
SALES/udump'

*.USER_DUMP_DEST=
'/mnt/app/oracle/admin/
SALES_LOG/udump'

*.DB_BLOCK_CHECKING=MEDIUM

Same as ChicagoFoot 1 

Same as Chicago

 
*.DB_BLOCK_CHECKSUM=FULL

Same as Chicago

Same as Chicago

 
*.LOG_ARCHIVE_FORMAT=
'arch_%t_%S_%r.log'

Same as Chicago

Same as Chicago

 
*.LOG_ARCHIVE_TRACE=0

Same as Chicago

Same as Chicago

 
*.FAST_START_MTTR_TARGET=300

Same as Chicago

Same as Chicago

 
*.STATISTICS_LEVEL=TYPICAL

Same as Chicago

Same as Chicago

 
*.LOCAL_LISTENER='SALES_lsnr'

Same as Chicago

Same as Chicago

 
*.REMOTE_LISTENER=
'SALES_remotelsnr_CHICAGO'

*.REMOTE_LISTENER=
'SALES_remotelsnr_BOSTON'

*.REMOTE_LISTENER=
'SALES_remotelsnr_BOSTON'

*.UNDO_MANAGEMENT=AUTO

Same as Chicago

Same as Chicago

 
*.UNDO_RETENTION=900

Same as Chicago

Same as Chicago

 
*.UNDO_TABLESPACE='UNDOTBS'

Same as Chicago

Same as Chicago

 
*.RESUMABLE_TIMEOUT=900

Same as Chicago

Same as Chicago

Footnote 1 This can be turned off by setting DB_BLOCK_CHECKING=FALSE if recovery performance is adversely affected.

Table A-2 shows RAC best practice SPFILE parameters for primary, physical standby, and logical standby databases.

Table A-2 RAC SPFILE Parameters for Primary, Physical Standby, and Logical Standby Databases

Chicago (Primary Database) Boston (Physical Standby) Boston (Logical Standby) 
*.CLUSTER_DATABASE=TRUE

Same as Chicago

Same as Chicago

 
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_CHICAGO1

SALES_PHYS1.INSTANCE_NAME=
SALES_BOSTON1

SALES_LOG1.INSTANCE_NAME=
SALES_BOSTON_LOG1

SALES2.INSTANCE_NAME=
SALES_CHICAGO2

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. These parameters must be set whether or not you use the Data Guard broker.

Table A-3 Data Guard SPFILE Parameters for Primary, Physical Standby, and Logical Standby Databases

Chicago (Primary Database) Boston (Physical Standby) Boston (Logical Standby) 
*.DB_UNIQUE_NAME='SALES_CHICAGO'

*.DB_UNIQUE_NAME='SALES_BOSTON'

*.DB_UNIQUE_NAME='SALES_BOSTON_LOG'

Table A-4 shows Data Guard best practice SPFILE parameters for primary database and physical standby database only. If you are using Data Guard 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 SPFILE Parameters for Primary, Physical Standby, and Logical Standby Databases

Chicago (Primary Database) Boston (Physical Standby) Boston (Logical Standby) 
*.DB_BROKER_CONFIG_FILE_1=
'+DATA/SALES_CHICAGO/dr1SALES_CHICAGO.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_CHICAGO/dr2SALES_CHICAGO.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 Chicago

Same as Chicago

Table A-5 shows Data Guard best practice SPFILE parameters for primary, physical standby, and logical standby databases if you are not using Data Guard Broker to manage your database environment. If you are not using Data Guard Broker, you must also set the parameters in Table A-6 through Table A-9.

Table A-5 Data Guard (No Broker) SPFILE Parameters for Primary, Physical Standby, and Logical Standby Databases

Chicago (Primary Database) Boston (Physical Standby) Boston (Logical Standby) 
*.LOG_FILE_NAME_CONVERT=' ',' '

Same as Chicago

Same as Chicago

 
*.STANDBY_FILE_MANAGEMENT=AUTO

Same as Chicago

Same as Chicago

 
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

Same as Chicago

Same as Chicago

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 Data Guard Broker to manage your database environment.

Table A-6 Data Guard SPFILE Parameters for Primary and Physical Standby Database Only

Chicago (Primary Database) Boston (Physical Standby Database) 
*.FAL_CLIENT='SALES_CHICAGO'

*.FAL_CLIENT='SALES_BOSTON'

*.FAL_SERVER='SALES_BOSTON'

*.FAL_SERVER='SALES_CHICAGO'

*.DB_UNIQUE_NAME='SALES_CHICAGO'

*.DB_UNIQUE_NAME='SALES_BOSTON'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(SALES_CHICAGO,SALES_BOSTON)'

Same as Chicago

*.LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST arch mandatory valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=SALES_CHICAGO'

*.LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST arch mandatory valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=SALES_BOSTON'

*.LOG_ARCHIVE_DEST_2='service=SALES_BOSTON lgwr sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON'

*.LOG_ARCHIVE_DEST_2='service=SALES_CHICAGO lgwr sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_CHICAGO'

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 Data Guard Broker to manage your database environment.

Table A-7 Data Guard SPFILE Parameters for Primary and Logical Standby Database Only

Chicago (Primary Database) Boston (Logical Standby Database)

*.FAL_CLIENT='SALES_CHICAGO'

 
*.FAL_CLIENT='SALES_BOSTON_LOG'

*.FAL_SERVER='SALES_BOSTON_LOG'

*.FAL_SERVER='SALES_CHICAGO'

*.LOG_ARCHIVE_CONFIG=
'DG_CONFIG=(SALES_CHICAGO,SALES_BOSTON_LOG)'

Same as Chicago

 
*.STANDBY_ARCHIVE_DEST=+RECO/SALES_CHICAGO/archivelog/FAL/

*.STANDBY_ARCHIVE_DEST=+RECO/SALES_BOSTON/archivelog/FAL/

 
*.LOG_ARCHIVE_DEST_1=
'location=USE_DB_RECOVERY_FILE_DEST arch 
max_failure=0 mandatory 
valid_for=(ONLINE_LOGFILES,ALL_ROLES) 
db_unique_name=SALES_CHICAGO'

*.LOG_ARCHIVE_DEST_1=
'location=USE_DB_RECOVERY_FILE_DEST arch 
max_failure=0 mandatory 
valid_for=(ONLINE_LOGFILES,ALL_ROLES) 
db_unique_name=SALES_BOSTON_LOG'

*.LOG_ARCHIVE_DEST_2=
'service=SALES_BOSTON_LOG reopen=15 max_failure=10 lgwr sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON_LOG'

*.LOG_ARCHIVE_DEST_2=
'service=SALES_CHICAGO 
lgwr sync affirm net_timeout=30 
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) 
db_unique_name=SALES_CHICAGO'

*.LOG_ARCHIVE_DEST_3 = 'location=+RECO/SALES_CHICAGO/archivelog/SRL/ arch max_failure=0 mandatory valid_for=(STANDBY_LOGFILES,STANDBY_ROLE) db_unique_name=SALES_CHICAGO'

*.LOG_ARCHIVE_DEST_3= 'location=+RECO/SALES_BOSTON/archivelog/SRL/ arch 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 SPFILE Parameters for Primary Database, Physical Standby Database, and Logical Standby Database: Maximum Availability or Maximum Protection Modes

Chicago (Primary Database) Boston (Physical Standby) Boston (Logical Standby) 
*.FAL_CLIENT='SALES_CHICAGO'

*.FAL_CLIENT='SALES_BOSTON'

*.FAL_CLIENT='SALES_BOSTON_LOG'

*.FAL_SERVER=
'SALES_BOSTON','SALES_BOSTON_LOG'

*.FAL_SERVER=
'SALES_CHICAGO','SALES_BOSTON_LOG'

*.FAL_SERVER=
'SALES_CHICAGO','SALES_BOSTON'

*.LOG_ARCHIVE_CONFIG=
'DG_CONFIG=(SALES_CHICAGO,
SALES_BOSTON,SALES_BOSTON_LOG)'

Same as Chicago

Same as Chicago

 
*.STANDBY_ARCHIVE_DEST=
'+RECO/SALES_CHICAGO/archivelog/FAL/'

*.STANDBY_ARCHIVE_DEST=
'+RECO/SALES_BOSTON/archivelog/FAL/'

*.STANDBY_ARCHIVE_DEST=
'+RECO/SALES_BOSTON_LOG/archivelog/FAL/

*.LOG_ARCHIVE_DEST_1=
'location=USE_DB_RECOVERY_FILE_
DEST arch mandatory 
valid_for=(ONLINE_
LOGFILES,ALL_ROLES) db_unique_
name=SALES_CHICAGO'

*.LOG_ARCHIVE_DEST_1=
'location=USE_DB_RECOVERY_FILE_
DEST arch mandatory 
valid_for=(ONLINE_
LOGFILES,ALL_ROLES) db_unique_
name=SALES_BOSTON'

*.LOG_ARCHIVE_DEST_1=
'location=USE_DB_RECOVERY_FILE_
DEST arch max_failure=0 mandatory 
valid_for=(ONLINE_
LOGFILES,ALL_ROLES) db_unique_
name=SALES_BOSTON_LOG'

*.LOG_ARCHIVE_DEST_2=
'service=SALES_BOSTON lgwr sync
affirm net_timeout=30 valid_
for=(ONLINE_LOGFILES,ALL_ROLES) 
db_unique_name=SALES_BOSTON'

*.LOG_ARCHIVE_DEST_2=
'service=SALES_CHICAGO lgwr sync 
affirm net_timeout=30 valid_
for=(ONLINE_LOGFILES,ALL_ROLES) 
db_unique_name=SALES_CHICAGO'

Not applicable

 
*.LOG_ARCHIVE_DEST_3=
'service=SALES_BOSTON_LOG lgwr 
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 
lgwr sync affirm net_
timeout=30 valid_for=(ONLINE_
LOGFILES,PRIMARY_ROLE) 
db_unique_name=SALES_BOSTON_LOG'

*.LOG_ARCHIVE_DEST_3 ='service=SALES_CHICAGO lgwr sync affirm net_timeout=30 
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) 
db_unique_name=SALES_CHICAGO'

*.LOG_ARCHIVE_DEST_4=
'location=+RECO/SALES_CHICAGO/archivelog/
SRL/ arch mandatory 
valid_for=(STANDBY_
LOGFILES,STAMDBY_ROLE) 
db_unique_name=SALES_CHICAGO'

*.LOG_ARCHIVE_DEST_4=
'location=+RECO/SALES_BOSTON/archivelog/
SRL/ arch mandatory 
valid_for=(STANDBY_
LOGFILES,STANDBY_ROLE) 
db_unique_name=SALES_BOSTON'

*.LOG_ARCHIVE_DEST_4=
'location=+RECO/SALES_BOSTON_LOG/
archivelog/SRL/ arch mandatory valid_
for=(STANDBY_LOGFILES,STANDBY_ROLES) db_unique_name=SALES_BOSTON_LOG'

*.PARALLEL_MAX_SERVERS=9

Same as Chicago

Same as Chicago

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 SPFILE Parameters for Primary Database, Physical Standby Database, and Logical Standby Database: Maximum Performance Mode

Chicago (Primary Database) Boston (Physical Standby) Boston (Logical Standby) 
*.LOG_ARCHIVE_DEST_2=
'service=SALES_BOSTON lgwr async net_timeout=30 
valid_for=(ONLINE_
LOGFILES,ALL_ROLES) 
db_unique_name=SALES_BOSTON'

*.LOG_ARCHIVE_DEST_2=
'service=SALES_CHICAGO lgwr 
async net_timeout=30 valid_
for=(ONLINE_LOGFILES,ALL_ROLES) 
db_unique_name=SALES_CHICAGO'

Not applicable

 
*.LOG_ARCHIVE_DEST_3=
'service=SALES_BOSTON_LOG lgwr 
async net_timeout=30 valid_for=
(ONLINE_LOGFILES,PRIMARY_ROLE) 
db_unique_name=SALES_BOSTON_LOG'

*.LOG_ARCHIVE_DEST_3=
'service=SALES_BOSTON_LOG lgwr 
async net_timeout=30 valid_for=
(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON_LOG'

*.LOG_ARCHIVE_DEST_3=
'service=SALES_CHICAGO lgwr 
async net_timeout=30 valid_for=
(ONLINE_LOGFILES,PRIMARY_ROLE) 
db_unique_name=SALES_CHICAGO'

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:

Oracle Database 10g Release 2 Best Practices: Data Guard Redo Apply and Media Recovery located on the OTN web site at http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm

This white paper contains instructions for calculating your bandwidth delay product.

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

For a 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

See Also:

Oracle Database Net Services Administrator's Guide for more information on listener password protection

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

# Used for database parameter local_listener 
SALES_lsnr =
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1513)))  
SALES_remotelsnr_CHICAGO =
   (DESCRIPTION=
     (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=<chicago_host1>))
       (ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=<chicago_host2>)))  
SALES_remotelsnr_BOSTON =
   (DESCRIPTION=
     (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=<boston_host1>))
       (ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=<boston_host2>)))   
# Net service used for communication with SALES database in Chicago 
SALES_CHICAGO =
   (DESCRIPTION=
     (ADDRESS_LIST=
       (SEND_BUF_SIZE=4665000)(RECV_BUF_SIZE=4665000)
       (ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=<chicago_host1>))
       (ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=<chicago_host2>)))
     (CONNECT_DATA=(SERVICE_NAME=SALES_CHICAGO)))   
# 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>))
       (ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=<boston_host2>)))
     (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>))
       (ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=<boston_host2>)))
     (CONNECT_DATA=(SERVICE_NAME=SALES_BOSTON_LOG)))
Go to previous page
Previous		 Go to next page
Next
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