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:

ORACLE_BASE=/mnt/app/oracle
Database flash recovery area is /flash_recovery

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 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.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_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
`*.INSTANCE_NAME=SALES_CHICAGO`	`*INSTANCE_NAME=SALES_BOSTON`	`INSTANCE_NAME=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`	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 sync=noparallel affirm valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SALES_BOSTON'`	`*.LOG_ARCHIVE_DEST_2='service=SALES_BOSTON reopen=15 max_failure=10 lgwr sync=noparallel 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 sync=noparallel 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 sync=noparallel 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/archivelo`g
`*.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 sync=noparallel 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 sync=noparallel 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 sync=noparallel 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 sync=noparallel 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 sync=noparallel 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'`

Oracle Net Configuration Files

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

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

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

B Database SPFILE and Oracle Net Configuration File Samples