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=

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

*.DB_BLOCK_CHECKING=MEDIUM Same as Chicago¹ 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

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

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

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

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

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

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–1 (Cont.) Generic SPFILE Parameters for Primary, Physical Standby, and Logical Standby

Chicago (Primary Database) Boston (Physical Standby) Boston (Logical Standby)

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–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–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–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=

*.DG_BROKER_START=TRUE Same as Chicago Same as Chicago

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

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–8 applies to a Data Guard environment running in either maximum availability mode or maximum protection mode.

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'

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)

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'

Same as Chicago Same as Chicago

*.STANDBY_ARCHIVE_DEST=

DEST arch max_failure=0 mandatory valid_for=(ONLINE_

LOGFILES,ALL_ROLES) db_unique_

name=SALES_BOSTON_LOG'

Table A–9 shows how to change the parameters for a Data Guard environment that is running in maximum performance mode.