Oracle 3-node RAC 에서 Oracle 10g R2 버전 DB의 파라미터 init.ora 관리 예시 ….
금융권 이나 트래픽이 굉장히 많은 테라급 사이트는 PFILE 형태 로 init.ora 파일을 타이트하게 직접 DBA 가 관리 합니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 |
############################################################################## # 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.