Oracle 3-node RAC 에서 Oracle 10g R2 버전 DB의 파라미터 init.ora 관리 예시 ….
금융권 이나 트래픽이 굉장히 많은 테라급 사이트는 PFILE 형태 로 init.ora 파일을 타이트하게 직접 DBA 가 관리 합니다.
##############################################################################
# haisins oracle 10g Release 2 RAC 3 node
##############################################################################
###########################################
# Archive
###########################################
*.log_archive_dest_1='LOCATION=/ora_arch/ORADB MANDATORY REOPEN=5' #haisins
*.log_archive_format=arch_ORADB_%r_%t_%s.dbf
*.log_checkpoints_to_alert=TRUE #haisins
*.fast_start_mttr_target=300 ## 5 min #haisins
### *.fast_start_mttr_target=1800 ### for MIG
*.recovery_parallelism=32 ### DISK
*.fast_start_parallel_rollback=false ### Note 464246.1
###########################################
# Cache and I/O
###########################################
*.db_block_size=8192
*.db_cache_size=30G #haisins Total RealMemory 224G (before_db:30640M)
###*.db_cache_size=3G #haisins Total RealMemory 224G (before_db:30640M)
###*.db_keep_cache_size=2G #haisins (before_db:2G)
*.db_keep_cache_size=40G #haisins (before_db:2G)
*.db_file_multiblock_read_count=16
###*.db_file_multiblock_read_count=128
*.db_files=8000 #haisins
# *._smm_auto_max_io_size=1008 ### default 248 for MIG only
# *._smm_auto_min_io_size=1008 ### default 56 for MIG only
###########################################
# Cluster Database
###########################################
*.cluster_database_instances=3
*.cluster_database=true
### *.cluster_database=false
#remote_listener=LISTENERS_ORADB #haisins
ORADB1.instance_groups='ORADB1'
ORADB2.instance_groups='ORADB2'
ORADB3.instance_groups='ORADB3'
ORADB1.parallel_instance_group='ORADB1'
ORADB2.parallel_instance_group='ORADB2'
ORADB3.parallel_instance_group='ORADB3'
ORADB1.instance_number=1
ORADB2.instance_number=2
ORADB3.instance_number=3
ORADB1.thread=1
ORADB2.thread=2
ORADB3.thread=3
###########################################
# Cursors and Library Cache
###########################################
*.open_cursors=1500 #haisins 300 -> 1500 (before_db:1500)
*.open_links=16 #haisins 4 -> 16 (before_db:16)
*.session_cached_cursors=200 #haisins 20 -> 200 (before_db:100)
###########################################
# Database Identification
###########################################
*.db_domain=""
*.db_name=ORADB
*.service_names=ORADB #haisins
###########################################
# Diagnostics and Statistics
###########################################
*.background_dump_dest=/ora_trc/admin/ORADB/bdump #haisins
*.core_dump_dest=/ora_trc/admin/ORADB/cdump #haisins
*.user_dump_dest=/ora_trc/admin/ORADB/udump #haisins
*.max_dump_file_size=409600 #haisins
###########################################
# File Configuration
###########################################
*.control_files=("/dev/vx/rdsk/MDB09/ctrl01", "/dev/vx/rdsk/MDB09/ctrl02", "/dev/vx/rdsk/MDB10/ctrl03")
###########################################
# Job Queues
###########################################
*.job_queue_processes=10
###########################################
# Listener
###########################################
ORADB1.local_listener=ORADB1_LL
ORADB2.local_listener=ORADB2_LL
ORADB3.local_listener=ORADB3_LL
### ORADB1.remote_listener=ORADB1_RL
### ORADB2.remote_listener=ORADB2_RL
### ORADB3.remote_listener=ORADB3_RL
###########################################
# Miscellaneous
###########################################
*.compatible=10.2.0.4.0
###########################################
# Parallel Executions
###########################################
### *.parallel_max_servers=300 ## CPU * 4 1800MHz*36 #haisins (before_db:300)
*.parallel_max_servers=720 ## CPU * 4 1800MHz*36 #haisins (before_db:300) ######## Mig only
*.parallel_execution_message_size=8192 ## tuning #haisins (default:2152, before_db:8192)
*._PX_use_large_pool=TRUE ## tuning #haisins (before_db:TRUE)
*.parallel_adaptive_multi_user=FALSE ## manual #haisins (Default:TRUE, before_db:FALSE)
###########################################
# Optimizer
###########################################
# *.optimizer_mode=all_rows ### MIG only
*.optimizer_mode=first_rows_1 ## all_rows/first_rows_1/10/100/1000 #haisins (Default,before_db:first_row_1)
# *.optimizer_index_caching =80 #haisins (Default,before_db:0)
# *.optimizer_index_cost_adj=30 #haisins (Default,before_db:100)
*.optimizer_dynamic_sampling=0 ## disable dynamic sampling #haisins (Default,before_db:0)
*._optim_peek_user_binds=FALSE ## Disable Bind Peeking #haisins (Default,before_db:FALSE)
###########################################
# Pools
###########################################
#*.java_pool_size=250M #haisins Only PatchSetApply
*.large_pool_size=1G #haisins (before_db:304M) 1G -> 500M
*.shared_pool_size=8G #haisins (before_db:3200M) 5G -> 8G
*.shared_pool_reserved_size=800M #haisins (before_db:300M)
*.log_buffer=30486528 #haisins (before_db:30486528)
###########################################
# Processes and Sessions
###########################################
*.processes=5000
#*.sessions=5505
*.aq_tm_processes=1 #haisins 0 -> 1 (before_db:1)
###########################################
# Security and Auditing
###########################################
# audit_file_dest=/oraom/admin/ORADB/adump #haisins
*.remote_login_passwordfile=exclusive
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
*.sort_area_size=1048576 #haisins 1M(Default 1M)
*.workarea_size_policy=AUTO #haisins
*.pga_aggregate_target=50G #haisins 20G -> 40G (before_db:40G)
###########################################
# System Managed Undo and Rollback Segments
###########################################
*.undo_management=AUTO
*.undo_retention=21600 # 1800(0.5H) 7200(2H) 21600(6H) #haisins
### *._undo_autotune=FALSE ## use undo_retention (disable autotune) #haisins
### *.undo_retention=1800 ### MIG only
ORADB1.undo_tablespace=UNDOTBS1
ORADB2.undo_tablespace=UNDOTBS2
ORADB3.undo_tablespace=UNDOTBS3
###########################################
# Miscellaneous
###########################################
*._trace_files_public=true
# *._transaction_auditing=FALSE ### MIG only
### *._gby_hash_aggregation_enabled=FALSE
*._enable_reliable_latch_waits=FALSE
*._enable_NUMA_optimization=FALSE ### bug 7171446
*._db_block_numa = 1 ### bug 7171446
### *._optimizer_autostats_job=false ### Turn off automatic statistics.
### *.lock_sga = true
*.distributed_lock_timeout = 100 ### Txtime < SesTm < DistLockTimeout for XA
###########################################
# Bug
###########################################
*._gc_affinity_time=0 ### bug4940890 diable DRM (RAC) Bug 6018125 Instance crash Fixed 10.2.0.4
*._gc_undo_affinity=FALSE ### diable DRM (RAC)
*._gc_maximum_bids=8 ### (RAC)
*._rowsource_execution_statistics=TRUE ### sql info (cr=59 r=52 w=0 time=111599 us)
*.star_transformation_enabled=FALSE ### Bug 6318487 fixed 10.2.0.5
*._object_statistics=FALSE ### Bug more than 16 CPU
*._optimizer_push_pred_cost_based=FALSE ### Bug
*._optimizer_connect_by_cost_based=FALSE ### Bug after 10.2.0.2
*._optimizer_cost_based_transformation=OFF ### Bug 6815733 fixed 10.2.0.5, Bug 6795926 Bug 6310653 Bug 6183577 Bug 6061623
*._complex_view_merging=FALSE ### Bug 6634836 Bug 6316585 Bug 6276590 fixed 10.2.0.5, Bug 7189447
*._subquery_pruning_enabled=FALSE ### Bug 6714608 fixed 10.2.0.5
*._bloom_filter_enabled=FALSE ### Bug 5766718 Bug 6070877 fixed 10.2.0.5
*._b_tree_bitmap_plans=FALSE ### Bug 6122097 fixed 10.2.0.5, Bug 6653704 Bug 6525496
### *._optimizer_skip_scan_enabled=FALSE ### Bug 6070954 Fixed 10.2.0.4
### *._db_fast_obj_truncate = FALSE ### Bug 5575748 enq: RO - fast object reuse
### *.db_cache_advice=READY ## Bug 5918642 Fixed 10.2.0.4
### *._library_cache_advice=FALSE ## Bug 4697252 Fixed 10.2.0.4
### *._ash_enable=false
###########################################
# Diagnostics and Events
###########################################
*.event="10246 trace name context forever, level 1"
*.event="10511 trace name context forever, level 1" ## Disable undo offline
*.event="10422 trace name context forever, level 1" ## control enqueue while startup
*.event="240 debug" ## control enqueue while startup
*.event = "4031 trace name ERRORSTACK level 3" ## 4031 issue
*.event = "4031 trace name LIBRARY_CACHE level 10" ## 4031 issue
*.event = "4031 trace name heapdump level 2" ## 4031 issue
max_dump_file_size = unlimited ## 4031 issue
_oradbg_pathname="/oraom/users/kkm/oradbg" ## control enqueue while startup
# *.event="29740 trace name errorstack level 3" ### for evict (RAC)
# *.event="481 trace name errorstack level 3" ### for LMON (RAC)
# *.event="484 trace name errorstack level 3" ### for LMS (RAC)
# *.event="470 trace name errorstack level 3" ### for LGWR
It’s really a nice and useful piece of info. I am glad that you shared this useful information with us. Please keep us up to date like this. Thank you for sharing.