Oracle数据库安装及运维工作指南

1、.Oracle 数据库安装与运维工作指南金蝶软件（中国）XX K3 CLOUD 基础系统部2017-04-10张华福本文主要强调 ORACLE 数据库服务器环境的安装软件与版本，以与安装完成后的后续完善工作。以确保数据库以比较完善的状态投入生产，尽可能避免已知的问题影响生产库的运营。文章不涉与到软件的安装方法步骤。1 服务器系统软件，数据库版本安装推荐：ORACLE 。11204RAC 。2 数据库安装创建完毕后，需要进行下列的完善工作， ，截止至 2017 4 , 当20161018 ,p24436338_112040_Linux-x86-64_GI_20161018.zip -rac 版

2、本 , p24006111_112040_Linux-x86-64_DB_20161018.zip -纯数据库版。请按照补丁集上的 readme.html，把补丁集打到 GI, 数据库上。初始化参数，下面的参数值，只是通常情况下的设置（修改配置，要想系统效率最优，得依据数据库实际的运行情况逐步作微调（注意，下面的RAC 和单节点环境下，稍有不同。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=*;修改结束后，需重启数据库，才能确保上述修改生效。.db_reocvery_file_dest 参数，设置好归档日志存放路径， SYSk3db1show parameter db_recoveryNAMETYPEVALUEdb_recovery_file_deststring db_recovery_file_dest_sizebig integer SYSk3db1alter system set db_recovery_file_dest_size=50g;- 此参数值按实际情况设置。System altered.Elapsed: 00:00:00.04SYS

6、k3db1alter system set db_recovery_file_dest=+FLASH;- 此参数值按实际情况设置。System altered.Elapsed: 00:00:00.03altersystemsetlog_archive_dest_1=location=use_db_recovery_file_destsid=*;同时在两节点上执行：关闭数据库，设置归档模式，都设置了归档模式后，再打开数据库。12 上。Shutdown immediate;12 上。Startup mount;12 上。Alter database archivelog;Alter databas

7、e add supplemental log data;- 开启最小附加日志alter database add supplemental log data;12 上。Alter database open;制定作业，定时采集系统表，业务表的统计信息，以 sys sqlplus 工具，登录生产库，创建定时作业，采集系统表，每周7 点开始运行。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;/关闭系统自带的统计信息等功能。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 工具，登录生产库，创建一个常用的管理账户，并授予合适的权限：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 身份登录，创建采集业务表的定时作业，每天早上5 点运行。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;/说明：红色部分，是业务账户名，请根据实际情况更改。（ORACLE 定义的临时表11G 版本是可行的， 12C，ORACLE 对临时表的统计信息作了进一步的改进，需要分情况处理了。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;/说明：红色部分说明的是作业的运行时间点，可以更改。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 创建一作业，删除过期的临时表- 创建删除临时表的过程。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)- 创建作业运行用的时间表，每小时运行一次。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;/- 创建作业运行的 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;/-创建定期作业， begin (job_name= JOB_PURGE_TMPTABLE, program_name= PROC_PURGE_TMPT