Oracle® Database High Availability Best Practices 10g Release 2 (10.2) Part Number B25159-01 |
|
|
View PDF |
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:
Table A-1, "Generic SPFILE Parameters for Primary, Physical Standby, and Logical Standby Databases"
Table A-2, "RAC SPFILE Parameters for Primary, Physical Standby, and Logical Standby Databases"
Table A-6, "Data Guard SPFILE Parameters for Primary and Physical Standby Database Only"
Table A-7, "Data Guard SPFILE Parameters for Primary and Logical Standby Database Only"
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.
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' |
|
|
|
|
|
|
Same as 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_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/ |
|
*.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' |
|
|
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' |
This section contains examples of the following Oracle Net configuration file settings:
SQLNET.ORA Example for All Hosts Using Dynamic Instance Registration
LISTENER.ORA Example for All Hosts Using Dynamic Instance Registration
TNSNAMES.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 athttp://www.oracle.com/technology/deploy/availability/htdocs/maa.htm
This white paper contains instructions for calculating your bandwidth delay product.
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# 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)))