Oracle數(shù)據(jù)庫(kù)安裝及運(yùn)維工作指南

./Oracle數(shù)據(jù)庫(kù)安裝及運(yùn)維工作指南金蝶軟件〔中國(guó)有限公司K3CLOUD基礎(chǔ)系統(tǒng)部2017-04-10張華福本文主要強(qiáng)調(diào)ORACLE數(shù)據(jù)庫(kù)服務(wù)器環(huán)境的安裝軟件及版本,以及安裝完成后的后續(xù)完善工作。以確保數(shù)據(jù)庫(kù)以比較完善的狀態(tài)投入生產(chǎn),盡可能避免已知的問題影響生產(chǎn)庫(kù)的運(yùn)營(yíng)。文章不涉及到軟件的安裝方法步驟。1服務(wù)器系統(tǒng)軟件,數(shù)據(jù)庫(kù)版本安裝推薦：1數(shù)據(jù)庫(kù)服務(wù)器的操作系統(tǒng),推薦使用ORACLELINUX6.8。2數(shù)據(jù)庫(kù)軟件版本,要求安裝11204,單節(jié)點(diǎn)或RAC環(huán)境均可,推薦使用RAC。2數(shù)據(jù)庫(kù)安裝創(chuàng)建完畢后,需要進(jìn)行下列的完善工作,1打上最新的補(bǔ)丁集,如：ORACLE11204,截止至2017年4月,ORACLE11204數(shù)據(jù)庫(kù),當(dāng)前最新版本的補(bǔ)丁集為20161018日發(fā)布的,p24436338_112040_Linux-x86-64_GI_20161018.zip--rac版本,p24006111_112040_Linux-x86-64_DB_20161018.zip--純數(shù)據(jù)庫(kù)版。請(qǐng)按照補(bǔ)丁集上的readme.html,把補(bǔ)丁集打到GI,數(shù)據(jù)庫(kù)上。2初始化參數(shù),下面的參數(shù)值,只是通常情況下的設(shè)置〔或可根據(jù)服務(wù)器的硬件條件自行修改配置,要想系統(tǒng)效率最優(yōu),得依據(jù)數(shù)據(jù)庫(kù)實(shí)際的運(yùn)行情況逐步作微調(diào)〔注意,下面的命令,在RAC和單節(jié)點(diǎn)環(huán)境下,稍有不同。alterprofiledefaultLIMITPASSWORD_LIFE_TIMEUNLIMITED;altersystemsetprocesses=500scope=spfilesid='*';altersystemsetcontrol_file_record_keep_time=21scope=spfilesid='*';altersystemsetopen_cursors=300scope=spfilesid='*';altersystemsetsession_cached_cursors=300scope=spfilesid='*';altersystemsetaudit_trail=NONEscope=spfilesid='*';altersystemsetrecyclebin=offscope=spfilesid='*';altersystemsetlog_archive_dest_1='location=use_db_recovery_file_dest'scope=bothsid='*';altersystemsetfast_start_mttr_target=30scope=bothsid='*';altersystemsetstreams_pool_size=100mscope=spfilesid='*';altersystemsetarchive_lag_target=1200;altersystemset"_optimizer_use_feedback"=falsescope=spfilesid='*';altersystemset"_optimizer_invalidation_period"=60scope=bothsid='*';修改結(jié)束后,需重啟數(shù)據(jù)庫(kù),才能確保上述修改生效。3將數(shù)據(jù)庫(kù)設(shè)置成歸檔模式,并開啟最小附加日志.3.1啟用db_reocvery_file_dest參數(shù),設(shè)置好歸檔日志存放路徑,SYS@k3db1>showparameterdb_recoveryNAME TYPE VALUEdb_recovery_file_dest stringdb_recovery_file_dest_size biginteger0SYS@k3db1>altersystemsetdb_recovery_file_dest_size=50g;--此參數(shù)值按實(shí)際情況設(shè)置。Systemaltered.Elapsed:00:00:00.04SYS@k3db1>altersystemsetdb_recovery_file_dest='+FLASH';--此參數(shù)值按實(shí)際情況設(shè)置。Systemaltered.Elapsed:00:00:00.03altersystemsetlog_archive_dest_1='location=use_db_recovery_file_dest'scope=bothsid='*';同時(shí)在兩節(jié)點(diǎn)上執(zhí)行：關(guān)閉數(shù)據(jù)庫(kù),設(shè)置歸檔模式,都設(shè)置了歸檔模式后,再打開數(shù)據(jù)庫(kù)。2 節(jié)點(diǎn)1,節(jié)點(diǎn)2上。Shutdownimmediate;3節(jié)點(diǎn)1,節(jié)點(diǎn)2上。Startupmount;4節(jié)點(diǎn)1,節(jié)點(diǎn)2上。Alterdatabasearchivelog;Alterdatabaseaddsupplementallogdata;--開啟最小附加日志alterdatabaseaddsupplementallogdata;5節(jié)點(diǎn)1,節(jié)點(diǎn)2上。Alterdatabaseopen;3制定作業(yè),定時(shí)采集系統(tǒng)表,業(yè)務(wù)表的統(tǒng)計(jì)信息,3.1以sys身份,使用sqlplus工具,登錄生產(chǎn)庫(kù),創(chuàng)建定時(shí)作業(yè),采集系統(tǒng)表,每周日早上7點(diǎn)開始運(yùn)行。createprocedurepro_analyze_dictasbegindbms_stats.gather_dictionary_stats<>;dbms_stats.gather_fixed_objects_stats<>;endpro_analyze_dict;/begindbms_scheduler.create_schedule<schedule_name=>'SCHEDULE_SUNDAY_AT_0700',start_date=>TO_DATE<'2016-08-0400:00:00','YYYY-MM-DDHH24:MI:SS'>,end_date=>NULL,repeat_interval=>'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=07;BYMINUTE=00;BYSECOND=0',comments=>''>;end;/execdbms_scheduler.drop_program<program_name=>'PROC_ANALYZE_DICT'>;begindbms_scheduler.create_program<program_name=>'PROC_ANALYZE_DICT',program_type=>'PLSQL_BLOCK',program_action=>'BEGINpro_analyze_dict;END;',number_of_arguments=>0,enabled=>TRUE,comments=>'2016-08-0707:00:00'>;end;/begindbms_scheduler.create_job<job_name=>'JOB_ANALYZE_DICT',program_name=>'PROC_ANALYZE_DICT',schedule_name=>'SCHEDULE_SUNDAY_AT_0700',enabled=>TRUE,comments=>'zhang.huafu,analyzesystables''statsandfixedobjects''stats'>;end;/3.2關(guān)閉系統(tǒng)自帶的統(tǒng)計(jì)信息等功能。execdbms_auto_task_admin.disable<client_name=>'autospaceadvisor',operation=>NULL,window_name=>null>;execdbms_auto_task_admin.disable<client_name=>'sqltuningadvisor',operation=>null,window_name=>null>;execdbms_auto_task_admin.disable<client_name=>'autooptimizerstatscollection',operation=>NULL,window_name=>NULL>;3.3以sys身份,用sqlplus工具,登錄生產(chǎn)庫(kù),創(chuàng)建一個(gè)常用的管理賬戶,并授予合適的權(quán)限：createusermanageridentifiedby"20160803";grantdbatomanager;grantdropanytabletomanager;grantdeleteanytabletomanager;grantselectanytabletomanager;grantselectondba_tablestomanager;grantselectondba_objectstomanager;grantselectondba_recyclebintomanager;grantselectondba_mview_logstomanager;grantanalyzeanytomanager;grantupdate<failure_count>onsys.scheduler$_jobtomanager;--3.3.1以manager身份登錄,創(chuàng)建采集業(yè)務(wù)表的定時(shí)作業(yè),每天早上5點(diǎn)運(yùn)行。createorreplaceprocedure_analyze_statsasbeginforiin<selectowner,table_namefromdba_tablesawhereownerin<'LNZHONGYI','ZHONGYICS'>andtemporary='N'andtable_namenotlike'TMP%'andnotexists<select1fromdba_mview_logswherelog_owner=a.ownerandlog_table=a.table_name>orderbyowner,last_analyzednullsfirst>loopdbms_stats.gather_table_stats<i.owner,i.table_name>;endloop;foriin<selectowner,table_namefromdba_tableswhereownerin<'LNZHONGYI','ZHONGYICS'>andtemporary='Y'andlast_analyzedisnotnullorderbyowner,table_name>loopdbms_stats.delete_table_stats<i.owner,i.table_name>;endloop;endpro_analyze_stats;/說(shuō)明：紅色部分,是業(yè)務(wù)賬戶名,請(qǐng)根據(jù)實(shí)際情況更改。藍(lán)色部分,用于清空臨時(shí)表〔ORACLE定義的臨時(shí)表的統(tǒng)計(jì)信息,這在11G版本是可行的,到了12C,ORACLE對(duì)臨時(shí)表的統(tǒng)計(jì)信息作了進(jìn)一步的改進(jìn),需要分情況處理了。begindbms_scheduler.create_schedule<schedule_name=>'SCHEDULE_DAILY_AT_0510',start_date=>TO_DATE<'2016-08-0400:00:00','YYYY-MM-DDHH24:MI:SS'>,end_date=>NULL,repeat_interval=>'FREQ=DAILY;BYHOUR=05;BYMINUTE=10;BYSECOND=0',comments=>''>;end;/說(shuō)明：紅色部分說(shuō)明的是作業(yè)的運(yùn)行時(shí)間點(diǎn),可以更改。begindbms_scheduler.create_program<program_name=>'PROC_ANALYZE_STATS',program_type=>'PLSQL_BLOCK',program_action=>'BEGINpro_analyze_stats;END;',number_of_arguments=>0,enabled=>TRUE,comments=>'2017-03-1313:56'>;end;/begindbms_scheduler.create_job<job_name=>'JOB_ANALYZE_STATS',program_name=>'PROC_ANALYZE_STATS',schedule_name=>'SCHEDULE_DAILY_AT_0510',enabled=>TRUE,comments=>'zhang.huafu,analyzetables''stats'>;end;/--3.3.2創(chuàng)建一作業(yè),刪除過期的臨時(shí)表--創(chuàng)建刪除臨時(shí)表的過程。createorreplaceprocedure_purge_tmptableasbeginforiin<selecttable_name,last_analyzedfromdba_tablesawhereowner='LNZHONGYI'andtable_namelike'TMP%'andlength<table_name>>=30andexists<select1fromdba_objectswhereowner='LNZHONGYI'andobject_type='TABLE'andobject_namelike'TMP%'andobject_name=a.table_nameandcreated<sysdate-1>>loopbeginexecuteimmediate'droptableLNZHONGYI.'||i.table_name||'purge';end;endloop;deletefromlnzhongyi.t_bas_temporarytablenameawherenotexists<select1fromdba_tableswhereowner='LNZHONGYI'andtable_name=a.ftablename>;commit;endpro_purge_tmptable;/說(shuō)明：1腳本中,紅色部分,是用于業(yè)務(wù)運(yùn)營(yíng)的數(shù)據(jù)庫(kù)賬戶。2據(jù)我了解,這些臨時(shí)表都應(yīng)該記錄在該賬戶下的：T_BAS_TEMPORARYTABLENAME此表中。但由于該表中對(duì)臨時(shí)表的創(chuàng)建時(shí)間,FCREATEDATE字段,沒有強(qiáng)制NOTNULL,我擔(dān)心某些記錄沒時(shí)間,這樣,若讀取該表的數(shù)據(jù)來(lái)做刪除操作,這些沒時(shí)間的記錄,將被漏掉。SYS@orcl>desclnzhongyi.t_bas_temporarytablenameNameNull?TypeFTABLENAMENOTNULLVARCHAR2<30>FCREATEDATEDATEFUSERTOKENVARCHAR2<36>FPROCESSTYPENUMBER<10>FUSERTRANSACTIONIDVARCHAR2<200>--創(chuàng)建作業(yè)運(yùn)行用的時(shí)間表,每小時(shí)運(yùn)行一次。begindbms_scheduler.create_schedule<schedule_name=>'SCHEDULE_HOURLY_INTERVAL_1',start_date=>TO_DATE<'2017-04-1815:00:00','YYYY-MM-DDHH24:MI:SS'>,end_date=>NULL,repeat_interval=>'FREQ=HOURLY;INTERVAL=1;BYMINUTE=17;BYSECOND=30',comments=>''>;end;/--創(chuàng)建作業(yè)運(yùn)行的PROGRAM。begindbms_scheduler.create_program<program_name=>'PROC_PURGE_TMPTABLE',program_type=>'PLSQL_BLOCK',program_action=>'BEGINpro_purge_tmptable;END;',number_of_arguments=>0,enabled=>TRUE,comments=>'2016-08-0406:30:00'>;end;/--創(chuàng)建定期作業(yè),begindbms_scheduler.create_job<job_name=>'JOB_PURGE_TMPTABLE',program_name=>'PROC_PURGE_TMPTABLE',schedule_name=>'SCHEDULE_HOURLY_INTERVAL_1',enabled=>TRUE,comments=>''>;end;/4制定合適的備份策略,4.1通常情況下,我們都是以物理備份為主,邏輯備份為輔,也就是,主要使用rman備份,同時(shí)輔以expdp導(dǎo)出備份。4.2對(duì)于rman的備份策略,基本有兩者4.2.1保留幾份全庫(kù)備份〔包括全庫(kù)備份以后的所有日志,以保障可用該備份恢復(fù)自該備份以來(lái)的任意時(shí)間點(diǎn),但此策略只要求有備份,不保障能往回追溯