Oracle数据库安装和运维工作指引

1、范文范例指导参考Oracle数据库安装及运维工作指南金蝶软件中国K3 CLOUD根底系统部2021-04-10张华福本文主要强调ORACLE数据库效劳器环境的安装软件及版本,以及安装完成后的后续 完善工作.以保证数据库以比拟完善的状态投入生产,尽可能防止的问题影响生产库 的运营.文章不涉及到软件的安装方法步骤.1效劳器系统软件,数据库版本安装推荐：1数据库效劳器的操作系统,推荐使用ORACLE LINUX6.8.2数据库软件版本,要求安装11204,单节点或RAC环境均可,推荐使用RAC2数据库安装创立完毕后,需要进行以下的完善工作,1 打上最新的补丁集,如：ORACLE1204,截止至202

2、1年4月,ORACLE11204数据库,当前最新版本的补丁集为20211018日发布的,p24436338_112040_Linux-x86-64_GI_20211018.zip -rac版本,p24006111_112040_L in ux-x86-64_DB_20211018.zip -纯数据库版.请根据补丁集上的readme.html ,把补丁集打到GI,数据库上.2初始化参数,下面的参数值,只是通常情况下的设置或可根据效劳器的硬件条件自行 修改配置,要想系统效率最优,得依据数据库实际的运行情况逐步作微调注意,下面的 命令,在RAC和单节点环境下,稍有不同.alter profile d

3、efault LIMIT PASSWORD_LIFE_TIME UNLIMITED;alter system set processes=500 scope=spfile sid='*'alter system set con trol_file_record_keep_time=21 scope=spfile sid='*'alter system set ope n_cursors=300 scope=spfile sid='*'alter system set sessi on _cached_cursors=300 scope=spfil

4、e sid='*'alter system set audit_trail=NONE scope=spfile sid='*'alter system set recycleb in=off scope=spfile sid='*'alter system set log archive dest 1='location=use db recovery file dest' scope=both sid='*'alter system set fast_start_mttr_target = 30 scope=bo

5、th sid='*'alter system set streams_pool_size=100m scope=spfile sid='*'alter system set archive_lag_target = 1200;alter system set "_optimizer_use_feedback"= false scope=spfile sid='*'alter system set "_optimizer_i nvalidati on _period"=60 scope=both sid=&#

6、39;*'修改结束后,需重启数据库,才能保证上述修改生效.3将数据库设置成归档模式,并开启最小附加日志3.1 启用db_reocvery_file_dest 参数,设置好归档日志存放路径,SYSk3db1>show parameter db_recoveryNAMETYPE VALUEdb_recovery_file_deststri ngdb_recovery_file_dest_sizebig in teger 0SYSk3db1>alter system set db_recovery_file_dest_size=50g;-此参数值按实际情况设置.System al

7、tered.Elap sed: 00:00:00.04SYSk3db1>alter system set db_recovery_file_dest='+FLASH：-此参数值按实际情况设置.System altered.Elap sed: 00:00:00.03alter system set log archive dest 1='location=use db recovery file dest' scope=both sid='*'同时在两节点上执行：关闭数据库,设置归档模式,都设置了归档模式后,再翻开数据库.2 节点1,节点2上.Shu

8、tdow n immediate;3 节点1,节点2上.Startup mount;4 节点1,节点2上.Alter database archivelog;Alter database add suppleme ntal log data;-开启最小附加日志alter database add suppleme ntal log data;5 节点1,节点2上.Alter database ope n;3制定作业,定时采集系统表,业务表的统计信息,3.1 以sys身份,使用sqlplus工具,登录生产库,创立定时作业,采集系统表,每周日早上7点开始运行.create procedure pr

9、o_a nalyze_dict asbegindbms_stats.gather_dicti on ary_stats(); dbms_stats.gather_fixed_objects_stats();end pro_a nalyze_dict;/begindbms_scheduler.create_schedule(schedule_ name=> 'SCHEDULE_SUNDAY_AT_0700',start_date => TO_DATE('2021-08-04 00:00:00','YYYY-MM-DD HH24:MI:SS

10、9;), en d_date=> NULL,repeat_ in terval BYSECOND=0',=> 'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=07BYMINUTE=00;comme nts=> '');en d;/exec dbms_scheduler.drop_program(program_name=> 'PROC_ANALYZE_DICT'); begindbms_scheduler.create_program(program_ name program_type program_act

11、i on=> 'PROC_ANALYZE_DICT',=> 'PLSQL_BLOCK',=> 'BEGIN pro_a nalyze_dict; END;',nu mber_of_argume nts => 0,en abled comme nts=> TRUE,=> '2021-08-07 07:00:00');en d;/begindbms_scheduler.create_job(job_ name program_ name schedule_ name en abled comme n

12、ts=> 'JOB_ANALYZE_DICT',=> 'PROC_ANALYZE_DICT',=> 'SCHEDULE_SUNDAY_AT_0700',=> TRUE,=> 'zha ng.huafu, an alyze sys tables'' stats and fixedobjects'' stats');en d;/3.2 关闭系统自带的统计信息等功能.exec dbms_auto_task_adm in. disable(clie nt_n ame =>

13、 'auto space advisor', operati on => NULL, win dow_ name => n ull);exec dbms_auto_task_admin.disable(client_name=> 'sql tuning advisor',operationnu II, win dow_ name => n ull);exec dbms_auto_task_admin.disable(client_name=> 'auto optimizer stats collection',ope

14、rati on => NULL, win dow_ name => NULL);3.3 以sys身份,用sqlplus工具,登录生产库,创立一个常用的治理账户,并授予合适的权限：create user manager identified by "20210803"grant dba to man ager;grant drop any table to man ager;grant delete any table to man ager;grant select any table to man ager;grant select on dba_tables

15、 to man ager;grant select on dba_objects to man ager;grant select on dba_recycleb in to man ager;gra nt select on dba_mview_logs to man ager;grant an alyze any to man ager;grant update(failure_co unt) on sys.scheduler$_job to man ager;-3.3.1 以manager身份登录,创立采集业务表的定时作业,每天早上5点运行.create or replace proce

16、dure man _a nalyze_stats asbeginfor i in (select owner,table_ namefrom dba_tables a where owner('LNZHONGYI','ZHONGYICS') and temporary='N' and table_ name not like 'TMP%'and not exists (select 1 from dba_mview_logs where log_ow ner=a.ow nerand log_table=a.tabl

17、e_ name) order by own er, last_a nalyzed n ulls first) loop dbms_stats.gather_table_stats(i.ow ner,i.table_ name);end loop;for i in (select owner,table_ namefrom dba_tables where owner('LNZHONGYI','ZHONGYICS') and temporary='Y' and last_a nalyzed is not null order by owner, t

18、able_ name) loopdbms_stats.delete_table_stats(i.ow ner,i.table_ name);end loop;end pro_a nalyze_stats;/说明：红色局部,是业务账户名,请根据实际情况更改.蓝色局部,用于清空临时表(ORACL定义的临时表)的统计信息,这在11G版本是可行的, 到了 12C,ORACLE寸临时表的统计信息作了进一步的改良,需要分情况处理了.begindbms_scheduler.create_schedule=>inin(schedule_name => 'SCHEDULE_DAILY_AT_

19、0510',start_date=> TO_DATE('2021-08-04 OO:OO:OO','YYYY-MM-DD HH24:MI:SS'),en d_date=> NULL,repeat_i nterval => 'FREQ=DAILY;BYHOUR=05; BYMINUTE=10; BYSECOND=0comme nts=> '');en d;/说明：红色局部说明的是作业的运行时间点,可以更改.begindbms_scheduler.create_program(program, name =&g

20、t; 'PROC_ANALYZE_STATS',program_type=> 'PLSQL_BLOCK',program_acti on=> 'BEGIN pro_a nalyze_stats; END;',nu mber_of_argume nts => 0,en abled=> TRUE,comme nts=> '2021-03-13 13:56');en d;/begindbms_scheduler.create_job (job_ nameprogram_ name schedule_ nam

21、e en abledcomme nts=> 'JOB_ANALYZE_STATS',=> 'PROC_ANALYZE_STATS',=> 'SCHEDULE_DAILY_AT_0510',=> TRUE,=> 'zha ng.huafu, an alyze tables'' stats');word版整理andfromend;/-3.3.2创立一作业,删除过期的临时表-创立删除临时表的过程.create or replace procedure man _purge_t

22、mptable asbeginfor i in (select table_ name, last_a nalyzed from dba_tables awhereowner=' LNZHONGYI andtable_namelike 'TMP%'len gth(table_ name) >= 30and exists (select 1 from dba_objects where own er='LNZHONGYIand object_type='TABLE' and object_ name like 'TMP%' a

23、ndobject_ name=a.table_ name and created < sysdate - 1) ) loopbeginexecute immediate 'drop tableLNZHONG Y【|i.table_ name purge'en d;end loop;delete from Inzhongyi .t_bas_temporarytablenamea where not exists (select 1dba_tables where own er=' LNZHONGYa nd table_ name=a.ftable name);com

24、mit;end pro_purge_tmptable;/说明：1脚本中,红色局部,是用于业务运营的数据库账户.2据我了解,这些临时表都应该记录在该账户下的：T_BAS_TEMPORARYTABLENAM表中.但由于该表中对临时表的创立时间,FCREATEDAT字段,没有强制 NOT NULL,我担忧某些记录没时间,这样,假设读取该表的数据来做删除操作,这些没时间的记录,将被漏掉.SYSorcl>desc In zho ngyi.t_bas_temporarytable nameNameNull? TypeFTABLENAMEFCREATEDATEFUSERTOKENFPROCESSTYP

25、EFUSERTRANSACTIONIDNOT NULL VARCHAR2(30)DATEVARCHAR2(36)NUMBER(10)VARCHAR2(200)-创立作业运行用的时间表,每小时运行一次.begindbms_scheduler.create_schedule(schedule_name => 'SCHEDULE_HOURLY_INTERVAL_1',start_date=> TO_DATE('2021-04-18 15:00:00','YYYY-MM-DD HH24:MI:SS'),en d_date=> NULL,

26、repeat_i nterval => 'FREQ=HOURLY; INTERVALS; BYMINUTE=17; BYSECOND=3O',comme nts => '');en d;/ -创立作业运行的 PROGRAM begindbms_scheduler.create_program(program_ name program_type program_acti on=> 'PROC_PURGE_TMPTABLE',=> 'PLSQL_BLOCK',=> 'BEGIN pro_purg

27、e_tmptable; END;',nu mber_of_argume nts => 0,en abled comme nts=> TRUE,=> '2021-08-04 06:30:00');end;/-创立定期作业,begindbms_scheduler.create_job(job_ name=> 'JOB_PURGE_TMPTABLE',program, name schedule_ name=> 'PROC_PURGE_TMPTABLE',=> 'SCHEDULE_HOURLY_INTERVAL_1',en abled=> TRUE,comme nts=> )；end;/4制定适宜的备份策略,4.1 通常情况下,我们都是以物理备份为主,逻辑备份为辅,也就是,主要使用rman备份,同时辅以expdp导出备份.4.2 对于rman的备份策略,根本有两者4.2.1 保存几份全库备份包括全库