| Oracle® Database High Availability Architecture and Best Practices 10g Release 1 (10.1) Part Number B10726-02 |
|
|
View PDF |
| Oracle® Database High Availability Architecture and Best Practices 10g Release 1 (10.1) Part Number B10726-02 |
|
|
View PDF |
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:
Table B-1, "Generic SPFILE Parameters for Primary, Physical Standby, and Logical Standby Databases"
Table B-2, "RAC SPFILE Parameters for Primary, Physical Standby, and Logical Standby Databases"
Table B-3, "Data Guard SPFILE Parameters for Primary Database and Physical Standby Database Only"
Table B-4, "Data Guard SPFILE Parameters for Primary Database and Logical Standby Database Only"
Table B-6, "Data Guard SPFILE Parameters for Maximum Performance Mode"
The tables and files are shown for the following configuration:
ORACLE_BASE=/mnt/app/oracle
Database flash recovery area is /flash_recovery
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 theLGWR 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' |
# Set dead connection time SQLNET.EXPIRE_TIME = 1 # Set default SDU for all connections DEFAULT_SDU_SIZE=32767
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
# 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)))
