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

1、.Oracle 數(shù)據(jù)庫(kù)安裝與運(yùn)維工作指南金蝶軟件（中國(guó)）XX K3 CLOUD 基礎(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)，盡可能避免已知的問(wèn)題影響生產(chǎn)庫(kù)的運(yùn)營(yíng)。文章不涉與到軟件的安裝方法步驟。1 服務(wù)器系統(tǒng)軟件，數(shù)據(jù)庫(kù)版本安裝推薦：ORACLE 。11204RAC 。2 數(shù)據(jù)庫(kù)安裝創(chuàng)建完畢后，需要進(jìn)行下列的完善工作， ，截止至 2017 4 , 當(dāng)20161018 ,p24436338_112040_Linux-x86-64_GI_20161018.zip -rac 版

2、本 , p24006111_112040_Linux-x86-64_DB_20161018.zip -純數(shù)據(jù)庫(kù)版。請(qǐng)按照補(bǔ)丁集上的 readme.html，把補(bǔ)丁集打到 GI, 數(shù)據(jù)庫(kù)上。初始化參數(shù)，下面的參數(shù)值，只是通常情況下的設(shè)置（修改配置，要想系統(tǒng)效率最優(yōu)，得依據(jù)數(shù)據(jù)庫(kù)實(shí)際的運(yùn)行情況逐步作微調(diào)（注意，下面的RAC 和單節(jié)點(diǎn)環(huán)境下，稍有不同。alter profile default LIMIT PASSWORD_LIFE_TIME UNLIMITED; alter system set processes=500 scope=spfilesid=*;alter system set co

3、ntrol_file_record_keep_time=21 scope=spfilesid=*; alter system set open_cursors=300scope=spfilesid=*;alter systemset session_cached_cursors=300alter system set audit_trail=NONEscope=spfilesid=*;alter system set recyclebin=offscope=spfilesid=*;altersystemsetlog_archive_dest_1=location=use_db_recovery

4、_file_destsid=*;alter system set fast_start_mttr_target = 30 scope=both sid=*; alter system set streams_pool_size=100m scope=spfilesid=*; alter system set archive_lag_target = 1200;alter system set _optimizer_use_feedback= false scope=spfilesid=*; alter system set _optimizer_invalidation_period=60 s

5、cope=both sid=*;修改結(jié)束后，需重啟數(shù)據(jù)庫(kù)，才能確保上述修改生效。.db_reocvery_file_dest 參數(shù)，設(shè)置好歸檔日志存放路徑， SYSk3db1show parameter db_recoveryNAMETYPEVALUEdb_recovery_file_deststring db_recovery_file_dest_sizebig integer SYSk3db1alter system set db_recovery_file_dest_size=50g;- 此參數(shù)值按實(shí)際情況設(shè)置。System altered.Elapsed: 00:00:00.04SYS

6、k3db1alter system set db_recovery_file_dest=+FLASH;- 此參數(shù)值按實(shí)際情況設(shè)置。System altered.Elapsed: 00:00:00.03altersystemsetlog_archive_dest_1=location=use_db_recovery_file_destsid=*;同時(shí)在兩節(jié)點(diǎn)上執(zhí)行：關(guān)閉數(shù)據(jù)庫(kù)，設(shè)置歸檔模式，都設(shè)置了歸檔模式后，再打開(kāi)數(shù)據(jù)庫(kù)。12 上。Shutdown immediate;12 上。Startup mount;12 上。Alter database archivelog;Alter databas

7、e add supplemental log data;- 開(kāi)啟最小附加日志alter database add supplemental log data;12 上。Alter database open;制定作業(yè)，定時(shí)采集系統(tǒng)表，業(yè)務(wù)表的統(tǒng)計(jì)信息，以 sys sqlplus 工具，登錄生產(chǎn)庫(kù)，創(chuàng)建定時(shí)作業(yè)，采集系統(tǒng)表，每周7 點(diǎn)開(kāi)始運(yùn)行。create procedure pro_analyze_dict as begin dbms_stats.gather_dictionary_stats(); dbms_stats.gather_fixed_objects_stats(); end pr

8、o_analyze_dict;/begin dbms_scheduler.create_schedule(schedule_name= SCHEDULE_SUNDAY_AT_0700,start_date= 00:00:00,YYYY-MM-DD HH24: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_D

9、ICT); begindbms_scheduler.create_program(program_name= PROC_ANALYZE_DICT, program_type= PLSQL_BLOCK, program_action= BEGIN pro_analyze_dict; END;, number_of_arguments= 0,enabled= TRUE,comments= 2016-08-07 07:00:00);end;/begin dbms_scheduler.create_job(job_name= JOB_ANALYZE_DICT, program_name= PROC_A

10、NALYZE_DICT, schedule_name= SCHEDULE_SUNDAY_AT_0700,enabled= TRUE,comments= zhang.huafu, analyze sys tables stats and fixed objects stats);end;/關(guān)閉系統(tǒng)自帶的統(tǒng)計(jì)信息等功能。exec dbms_auto_task_admin.disable(client_name = auto space advisor, operation = NULL, window_name = null);exec dbms_auto_task_admin.disable(c

11、lient_name = sql tuning advisor,operation = null, window_name = null);exec dbms_auto_task_admin.disable(client_name = auto optimizer stats collection, operation= NULL, window_name = NULL);以 sys sqlplus 工具，登錄生產(chǎn)庫(kù)，創(chuàng)建一個(gè)常用的管理賬戶，并授予合適的權(quán)限：create user manager identified by 20160803; grant dba to manager;gra

12、nt drop any table to manager;grant delete any table to manager; grant select any table to manager; grant select on dba_tables to manager;grant select on dba_objects to manager; grant select on dba_recyclebin to manager; grant select on dba_mview_logs to manager; grant analyze any to manager;grant up

13、date(failure_count) on sys.scheduler$_job to manager;-3.3.1 以 manager 身份登錄，創(chuàng)建采集業(yè)務(wù)表的定時(shí)作業(yè)，每天早上5 點(diǎn)運(yùn)行。create or replace procedure _analyze_stats as beginfori in (select owner, table_name from dba_tables a where owner in ( LNZHONGYI,ZHONGYICS) and temporary=N and table_name not like TMP%andnot

14、exists(select1fromdba_mview_logswherelog_owner=a.ownerlog_table=a.table_name) order by owner, last_analyzed nulls first) loop dbms_stats.gather_table_stats(i.owner,i.table_name);end loop;fori in (select owner, table_name from dba_tables where owner in (and temporary=Y and last_analyzed is not nullor

15、der by owner, table_name) loop dbms_stats.delete_table_stats(i.owner,i.table_name); end loop;endpro_analyze_stats;/說(shuō)明：紅色部分，是業(yè)務(wù)賬戶名，請(qǐng)根據(jù)實(shí)際情況更改。（ORACLE 定義的臨時(shí)表11G 版本是可行的， 12C，ORACLE 對(duì)臨時(shí)表的統(tǒng)計(jì)信息作了進(jìn)一步的改進(jìn)，需要分情況處理了。begin dbms_scheduler.create_schedule(schedule_name= SCHEDULE_DAILY_AT_0510,start_date= 00:00:00,

16、YYYY-MM-DD HH24:MI:SS),end_date= NULL,repeat_interval= FREQ=DAILY;BYHOUR=05; BYMINUTE=10; comments= end;/說(shuō)明：紅色部分說(shuō)明的是作業(yè)的運(yùn)行時(shí)間點(diǎn)，可以更改。begin dbms_scheduler.create_program(program_name= PROC_ANALYZE_STATS, program_type= PLSQL_BLOCK, program_action= BEGIN pro_analyze_stats; END;, number_of_arguments= 0,ena

17、bled= TRUE,comments= 2017-03-13 13:56);end;/begin dbms_scheduler.create_job(job_name= JOB_ANALYZE_STATS, program_name= PROC_ANALYZE_STATS, schedule_name= SCHEDULE_DAILY_AT_0510, enabled= TRUE,comments= zhang.huafu, analyze tables stats); end;/-3.3.2 創(chuàng)建一作業(yè)，刪除過(guò)期的臨時(shí)表- 創(chuàng)建刪除臨時(shí)表的過(guò)程。create or replace proce

18、dure _purge_tmptable as beginfori in (select table_name, last_analyzed from dba_tables awhere owner=LNZHONGYI and table_name like TMP% and length(table_name) = 30 and exists (select 1 from dba_objects where owner=LNZHONGYIandobject_type=TABLE and object_name like TMP% and object_name=a.ta

19、ble_name and created desclnzhongyi.t_bas_temporarytablenameNameNull?TypeFTABLENAMENOT NULL VARCHAR2(30) FCREATEDATEDATEFUSERTOKENVARCHAR2(36)FPROCESSTYPENUMBER(10)FUSERTRANSACTIONIDVARCHAR2(200)- 創(chuàng)建作業(yè)運(yùn)行用的時(shí)間表，每小時(shí)運(yùn)行一次。begin dbms_scheduler.create_schedule(schedule_name= SCHEDULE_HOURLY_INTERVAL_1,start

20、_date= 15:00:00,YYYY-MM-DD HH24:MI:SS),end_date= NULL,repeat_interval= FREQ=HOURLY; INTERVAL=1; BYMINUTE=17; BYSECOND=30,comments= end;/- 創(chuàng)建作業(yè)運(yùn)行的 PROGRAM。begin dbms_scheduler.create_program(program_name= PROC_PURGE_TMPTABLE, program_type= PLSQL_BLOCK,program_action= BEGIN pro_purge_tmptable; END;, number_of_arguments= 0,enabled= TRUE,comments= 2016-08-04 06:30:00);end;/-創(chuàng)建定期作業(yè)， begin (job_name= JOB_PURGE_TMPTABLE, program_name= PROC_PURGE_TMPT