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

By haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

One thought on “[TIP] 금융권 대용량 Oracle DB 의 init.ora 관리”

답글 남기기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다