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.