Skip Headers

Oracle® Database High Availability Architecture and Best Practices
10g Release 1 (10.1)

Part Number B10726-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
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

B 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 HA architectures. These samples also clarify how the database server parameter file (SPFILE) relates to the Oracle Net configuration for dynamic service registration.

The following tables and sample files are included in this appendix:

The tables and files are shown for the following configuration:

B.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.


Note:

Oracle recommends that you never use the LGWR SYNC=NOPARALLEL option for the LOG_ARCHIVE_DEST_n initialization parameter for the maximum availability or maximum protection modes of Oracle Data Guard. Always use the SYNC=PARALLEL default. Fault detection after a standby instance fails occurs within the time specified by the NET_TIMEOUT option of the LOG_ARCHIVE_DEST_n initialization parameter. Further, Oracle recommends that NET_ TIMEOUT be set to 30 seconds for most configurations.

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 B-1 Generic SPFILE Parameters for Primary, Physical Standby, and Logical Standby Databases

Chicago (Primary Database) Boston (Physical Standby Database) Boston (Logical Standby Database)
*.COMPATIBLE='10.1.0' 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
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE Same as Chicago Same as Chicago
*.LOG_CHECKPOINT_INTERVAL=0 Same as Chicago Same as Chicago
*.LOG_CHECKPOINT_TIMEOUT=0 Same as Chicago Same as Chicago
*.LOG_CHECKPOINTS_TO_ALERT=TRUE Same as Chicago Same as Chicago
*.DB_BLOCK_CHECKING=TRUE Same as Chicago Same as Chicago
*.DB_BLOCK_CHECKSUM=TRUE Same as Chicago Same as Chicago
*.TIMED_STATISTICS=TRUE 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'
*.DB_RECOVERY_FILE_DEST=/flash_recovery 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
*.UNDO_MANAGEMENT=AUTO Same as Chicago Same as Chicago
*.UNDO_RETENTION=900 Same as Chicago Same as Chicago
*.UNDO_TABLESPACE='rbs01' Same as Chicago Same as Chicago
*.DB_NAME='SALES' Same as Chicago *.DB_NAME='SALES_LOG'
*.SERVICE_NAME='SALES_CHICAGO' *.SERVICE_NAME='SALES_BOSTON' *.SERVICE_NAME='SALES_BOSTON'
*.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'
*.CLUSTER_DATABASE=FALSE Same as Chicago Same as Chicago
*.CONTROL_FILES='/oradata/SALES/SALES_cntr01','/oradata/SALES/SALES_cntr02' *.CONTROL_FILES='/oradata/SALES/SALES_cntr01','/oradata/SALES/SALES_cntr02' *.CONTROL_FILES='/oradata/SALES_LOG/SALES_cntr01','/oradata/SALES_LOG/SALES_cntr02'
*.DB_FILE_NAME_CONVERT='/SALES_LOG/','/SALES/' Same as Chicago *.DB_FILE_NAME_CONVERT='/SALES/','/SALES_LOG/'
*.LOG_FILE_NAME_CONVERT='/SALES_LOG/','/SALES/' Same as Chicago *.LOG_FILE_NAME_CONVERT='/SALES/','/SALES_LOG/'
*.STANDBY_FILE_MANAGEMENT=AUTO Same as Chicago Same as Chicago
*.CONTROL_FILE_RECORD_KEEP_TIME=30 Same as Chicago Same as Chicago
*.RESUMABLE_TIMEOUT=900 Same as Chicago Same as Chicago
SALES_CHICAGO SALES_BOSTON SALES_BOSTON_LOG

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

Chicago (Primary Database) Boston (Physical Standby Database) Boston (Logical Standby Database)
*.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_BOSTON1
SALES2.INSTANCE_NAME=SALES_CHICAGO2 SALES_PHYS2.INSTANCE_NAME=SALES_BOSTON2 SALES_LOG2.INSTANCE_NAME=SALES_BOSTON2
SALES1.UNDO_TABLESPACE='rbs01' SALES_PHYS1.UNDO_TABLESPACE='rbs01' SALES_LOG1.UNDO_TABLESPACE='rbs01'
SALES2.UNDO_TABLESPACE='rbs02' SALES_PHYS2.UNDO_TABLESPACE='rbs02' SALES_LOG2.UNDO_TABLESPACE='rbs02'
*.STANDBY_FILE_MANAGEMENT=MANUAL Same as Chicago Same as Chicago

Table B-3 Data Guard SPFILE Parameters for Primary Database 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
*STANDBY_ARCHIVE_DEST=USE_DB_RECOVERY_FILE_DEST_SIZE Same as Chicago
*.LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST arch noreopen max_failure=0 mandatory valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=SALES_CHICAGO' *.LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST arch noreopen max_failure=0 mandatory valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=SALES_BOSTON'
*.LOG_ARCHIVE_DEST_2='service=SALES_BOSTON reopen=15 max_failure=10 lgwr affirm valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON' *.LOG_ARCHIVE_DEST_2='service=SALES_CHICAGO reopen=15 max_failure=10 lgwr affirm valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_CHICAGO'

Table B-4 Data Guard SPFILE Parameters for Primary Database 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'
*.DB_UNIQUE_NAME=SALES_CHICAGO' *.DB_UNIQUE_NAME='SALES_BOSTON_LOG'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(SALES_CHICAGO,SALES_BOSTON_LOG)' Same as Chicago
*.STANDBY_ARCHIVE_DEST='/arch/SALES/archivelog' *.STANDBY_ARCHIVE_DEST='/arch/SALES_LOG/archivelog'
*.LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST arch noreopen 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 noreopen max_failure=0 mandatory valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=SALES_BOSTON_LOG'
*.LOG_ARCHIVE_DEST_3='service=SALES_BOSTON_LOG reopen=15 max_failure=10 lgwr affirm valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON_LOG' *.LOG_ARCHIVE_DEST_3='service=SALES_CHICAGO reopen=15 max_failure=10 lgwr affirm valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_CHICAGO'
*.LOG_ARCHIVE_DEST_4='location=/arch/SALES/archivelog arch noreopen max_failure=0 mandatory valid_for=(STANDBY_LOGFILES,ALL_ROLES) db_unique_name=SALES_CHICAGO' *.LOG_ARCHIVE_DEST_4='location=/arch/SALES_LOG/archivelog arch noreopen max_failure=0 mandatory valid_for=(STANDBY_LOGFILES,ALL_ROLES) db_unique_name=SALES_BOSTON_LOG'
*.PARALLEL_MAX_SERVERS=9 Same as Chicago

Table B-5 applies to a Data Guard environment running in either maximum availability mode or maximum protection mode.

Table B-5 Data Guard SPFILE Parameters for Primary Database, Physical Standby Database, and Logical Standby Database

Chicago (Primary Database) Boston (Physical Standby Database) Boston (Logical Standby Database)
*.FAL_CLIENT='SALES_CHICAGO' *.FAL_CLIENT='SALES_BOSTON' *.FAL_CLIENT='SALES_BOSTON_LOG'
*.FAL_SERVER='SALES_BOSTON','SALES_BOSTON_LOG' *.FAL_SERVER='SALES_BOSTON','SALES_BOSTON_LOG' *.FAL_SERVER='SALES_BOSTON','SALES_BOSTON'
*.DB_UNIQUE_NAME='SALES_CHICAGO' *.DB_UNIQUE_NAME='SALES_BOSTON' *.DB_UNIQUE_NAME='SALES_BOSTON_LOG'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(SALES_CHICAGO,SALES_BOSTON,SALES_BOSTON_LOG)' Same as Chicago Same as Chicago
*.STANDBY_ARCHIVE_DEST='/arch/SALES/archivelog' Same as Chicago *.STANDBY_ARCHIVE_DEST='/arch/SALES_LOG/archivelog
*.LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST arch noreopen 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 noreopen max_failure=0 mandatory valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=SALES_BOSTON' *.LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST arch noreopen max_failure=0 mandatory valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=SALES_BOSTON_LOG'
*.LOG_ARCHIVE_DEST_2='service=SALES_BOSTON reopen=15 max_failure=10 lgwr affirm valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=SALES_BOSTON' *.LOG_ARCHIVE_DEST_2='service=SALES_CHICAGO reopen=15 max_failure=10 lgwr affirm valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=SALES_CHICAGO' N/A
*.LOG_ARCHIVE_DEST_3='service=SALES_BOSTON_LOG reopen=15 max_failure=10 lgwr affirm valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON_LOG' *.LOG_ARCHIVE_DEST_3='service=SALES_BOSTON_LOG reopen=15 max_failure=10 lgwr affirm valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON_LOG' *.LOG_ARCHIVE_DEST_3='service=SALES_CHICAGO reopen=15 max_failure=10 lgwr affirm valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_CHICAGO'
*.LOG_ARCHIVE_DEST_4='location=/arch/SALES/archivelog arch noreopen max_failure=0 mandatory valid_for=(STANDBY_LOGFILES,ALL_ROLES) db_unique_name=SALES_CHICAGO' *.LOG_ARCHIVE_DEST_4='location=/arch/SALES/archivelog arch noreopen max_failure=0 mandatory valid_for=(STANDBY_LOGFILES,ALL_ROLES) db_unique_name=SALES_BOSTON' *.LOG_ARCHIVE_DEST_4='location=/arch/SALES_LOG/archivelog arch noreopen max_failure=0 mandatory valid_for=(STANDBY_LOGFILES,ALL_ROLES) db_unique_name=SALES_BOSTON_LOG'
*.PARALLEL_MAX_SERVERS=9 Same as Chicago Same as Chicago

Table B-6 shows how to change the parameters for a Data Guard environment that is running in maximum performance mode.

Table B-6 Data Guard SPFILE Parameters for Maximum Performance Mode

Chicago (Primary Database) Boston (Physical Standby Database) Boston (Logical Standby Database)
*.LOG_ARCHIVE_DEST_2='service=SALES_BOSTON reopen=15 max_failure=10 lgwr async=102400 net_timeout=30 valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=SALES_BOSTON' *.LOG_ARCHIVE_DEST_2='service=SALES_CHICAGO reopen=15 max_failure=10 lgwr async=102400 net_timeout=30 valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=SALES_CHICAGO' N/A
*.LOG_ARCHIVE_DEST_3='service=SALES_BOSTON_LOG reopen=15 max_failure=10 lgwr async=102400 net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON_LOG' *.LOG_ARCHIVE_DEST_3='service=SALES_BOSTON_LOG reopen=15 max_failure=10 lgwr async=102400 net_timeout=30 valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=SALES_BOSTON_LOG' *.LOG_ARCHIVE_DEST_3='service=SALES_CHICAGO reopen=15 max_failure=10 lgwr async=102400 net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_CHICAGO'

B.2 Oracle Net Configuration Files

B.2.1 SQLNET.ORA File Example for All Hosts Using Dynamic Instance Registration

# Set dead connection time
SQLNET.EXPIRE_TIME = 1
# Set default SDU for all connections
DEFAULT_SDU_SIZE=32767

B.2.2 LISTENER.ORA File 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)))))
# Password Protect listener; See "Oracle Net Services Administration Guide"
PASSWORDS_lsnr_SALES = 876EAE4513718ED9 
# Prevent listener administration 
ADMIN_RESTRICTIONS_lsnr_SALES=ON

B.2.3 TNSNAMES.ORA File 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=
      (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=
      (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=
      (ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=<boston_host1>))
      (ADDRESS=(PROTOCOL=tcp)(PORT=1513)(HOST=<boston_host2>)))
    (CONNECT_DATA=(SERVICE_NAME=SALES_BOSTON_LOG)))