pat98's always & forever

11gR2 RAC 권장 파라미터 정리

############# For Single ##############

alter system set Audit_trail=NONE scope=spfile;
alter system set db_files=1000 scope=spfile;
alter system set db_cache_advice=ON scope=spfile;
alter system set deferred_segment_creation=false scope=spfile;
alter system set fast_start_mttr_target=240 scope=spfile;
alter system set sec_case_sensitive_logon=false scope=spfile;
alter system set open_links_per_instance=40 scope=spfile;
alter system set open_links=40 scope=spfile;
alter system set optimizer_connect_by_cost_based=false scope=spfile;
#alter system set optimizer_dynamic_sampling=0 scope=spfile;   #OLTP 0 DW 2
alter system set optimizer_secure_view_merging=false scope=spfile;
alter system set parallel_adaptive_multi_user=false scope=spfile;
alter system set resource_manager_plan='FORCE:' scope=spfile;
 
alter system set "_add_col_optim_enabled"=false scope=spfile;
alter system set "_datafile_write_errors_crash_instance"=false scope=spfile;
alter system set "_b_tree_bitmap_plans"=false scope=spfile;
alter system set "_bloom_filter_enabled=false scope=spfile;
alter system set "_cleanup_rollback_entries"=500 scope=spfile;
alter system set "_db_mttr_advice"=OFF scope=spfile;
#alter system set  _enqueue_hash_chain_latches= scope=spfile;  #CPU core*3
alter system set "_index_partition_large_extents"=false scope=spfile;  
alter system set "_in_memory_undo"=false scope=spfile;
alter system set "_library_cache_advice"=false scope=spfile;
alter system set "_nlj_batching_enabled"=0 scope=spfile;
alter system set "_nlj_batching_misses_enabled"=0 scope=spfile;
alter system set "_optim_peek_user_binds"=false scope=spfile;
alter system set "_optimizer_adaptive_cursor_sharing"=false scope=spfile;
alter system set "_optimizer_connect_by_cost_based"=false scope=spfile;
alter system set "_optimizer_extended_cursor_sharing"=NONE scope=spfile;
alter system set "_optimizer_extended_cursor_sharing_rel"=NONE scope=spfile;
alter system set "_optimizer_use_feedback"=false scope=spfile;
alter system set "_optimizer_null_aware_antijoin"=false scope=spfile;
alter system set "_partition_large_extents"=false scope=spfile; 
alter system set "_PX_use_large_pool"=true scope=spfile;
alter system set "_rollback_segment_count=1000" scope=spfile;
alter system set "_row_cr"=false scope=spfile;
alter system set "_undo_autotune"=false scope=spfile;
alter system set "_use_adaptive_log_file_sync"=false scope=spfile;
alter system set "_memory_imm_mode_without_autosga"=false scope=spfile;

============  For RAC ####################

alter system set Audit_trail=NONE scope=spfile;
alter system set db_files=1000 scope=spfile;
alter system set db_cache_advice=ON scope=spfile;
alter system set deferred_segment_creation=false scope=spfile;
alter system set fast_start_mttr_target=240 scope=spfile;
alter system set sec_case_sensitive_logon=false scope=spfile;
alter system set open_links_per_instance=40 scope=spfile;
alter system set open_links=40 scope=spfile;
alter system set optimizer_connect_by_cost_based=false scope=spfile;
#alter system set optimizer_dynamic_sampling=0 scope=spfile;   #OLTP 0 DW 2
alter system set optimizer_secure_view_merging=false scope=spfile;
alter system set parallel_adaptive_multi_user=false scope=spfile;
alter system set parallel_force_local=true scope=spfile;
alter system set resource_manager_plan='FORCE:' scope=spfile; 
 
alter system set "_add_col_optim_enabled"=false scope=spfile;
alter system set "_datafile_write_errors_crash_instance"=false scope=spfile;
alter system set "_b_tree_bitmap_plans"=false scope=spfile;
alter system set "_bloom_filter_enabled=false scope=spfile;
alter system set "_cleanup_rollback_entries"=500 scope=spfile;
alter system set "_clusterwide_global_transactions"=false scope=spfile;
alter system set "_db_mttr_advice"=OFF scope=spfile;
#alter system set  _enqueue_hash_chain_latches= scope=spfile;  #CPU core*3
alter system set "_index_partition_large_extents"=false scope=spfile;  
alter system set "_in_memory_undo"=false scope=spfile;
alter system set "_gc_bypass_readers"=FALSE scope=spfile;
alter system set "_gc_integrity_checks"=0 scope=spfile;
alter system set "_gc_maximum_bids"=8 scope=spfile;
alter system set "_gc_policy_time"=0 scope=spfile;
alter system set "_gc_read_mostly_locking"=FALSE scope=spfile; 
alter system set "_gc_undo_affinity"=false scope=spfile;
alter system set "_library_cache_advice"=false scope=spfile;
alter system set "_nlj_batching_enabled"=0 scope=spfile;
alter system set "_nlj_batching_misses_enabled"=0 scope=spfile;
alter system set "_optim_peek_user_binds"=false scope=spfile;
alter system set "_optimizer_adaptive_cursor_sharing"=false scope=spfile;
alter system set "_optimizer_connect_by_cost_based"=false scope=spfile;
alter system set "_optimizer_extended_cursor_sharing"=NONE scope=spfile;
alter system set "_optimizer_extended_cursor_sharing_rel"=NONE scope=spfile;
alter system set "_optimizer_use_feedback"=false scope=spfile;
alter system set "_optimizer_null_aware_antijoin"=false scope=spfile;
alter system set "_partition_large_extents"=false scope=spfile; 
alter system set "_px_execution_services_enabled"=false scope=spfile;
alter system set "_PX_use_large_pool"=true scope=spfile;
alter system set "_rollback_segment_count=1000" scope=spfile;
alter system set "_row_cr"=false scope=spfile;
alter system set "_undo_autotune"=false scope=spfile;
alter system set "_use_adaptive_log_file_sync"=false scope=spfile;
alter system set "_memory_imm_mode_without_autosga"=false scope=spfile;

자세한 설명은 생략한다.

Exadata  FlashCache 모드를 write-through 에서 write-back 모드로 바꾸어 보자.

- 온라인중 플래쉬캐쉬 writeback 모드로 변경하기 (DB & ASM Up 상태)

1. 플래쉬캐쉬 drop

# cellcli -e drop flashcache

2. offlne된 디스크가 있는지 확인 

# cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome

3. 그리드디스크 비활성화

# cellcli ?e alter griddisk all inactive

4. 셀서비스 내리기

# cellcli -e alter cell shutdown services cellsrv

5. writeback 모드로 변경

# cellcli -e "alter cell flashCacheMode=writeback"

6. 셀서비스 재시작

# cellcli -e alter cell startup services cellsrv

7. 그리드디스크 재활성화

# cellcli ?e alter griddisk all active

8. 그리드디스크가 성공적으로 온라인 되었는지 확인

# cellcli -e list griddisk attributes name, asmmodestatus

9. 플래쉬캐쉬 재성성

# cellcli -e create flashcache all

10. WriteBack 모드인지 확인

# cellcli -e list cell detail | grep flashCacheMode

11. 다음 셀에 동일한 작업 반복 (작업전 'asmdeactivationoutcome' 이 YES 인지 반드시 확인)

# cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome 

- 오프라인 플래쉬캐쉬 writeback 모드로 변경하기 (DB & ASM Down 상태)

1. 플래쉬캐쉬 drop

# cellcli -e drop flashcache

2. 셀서비스 내리기

# cellcli -e alter cell shutdown services cellsrv

3. writeback 모드로 변경

# cellcli -e "alter cell flashCacheMode=writeback"

4. 셀서비스 재시작

# cellcli -e alter cell startup services cellsrv

5. 플래쉬캐쉬 재성성

# cellcli -e create flashcache all