1、如何开启归档
alter database mount;

alter database add archivelog ‘dest=\dm8\dmbak ,TYPE=local,FILE_SIZE=1024,SPACE_LIMIT=102400’;

alter database archivelog;

alter database open;

2、定时备份任务开启
2.1 全量备份定时任务
–全量备份（每周六 23 点全备）：call SP_CREATE_JOB(‘bakfull’,1,0,‘’,0,0,‘’,0,‘’);

call SP_JOB_CONFIG_START(‘bakfull’);

call SP_ADD_JOB_STEP(‘bakfull’, ‘bak1’, 6, ‘01020000/dmdata/dmbak’, 0, 0, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE(‘bakfull’, ‘std1’, 1, 2, 1, 64, 0, ‘23:00:00’, NULL, ‘2021-12-01 15:52:22’, NULL, ‘’);

call SP_JOB_CONFIG_COMMIT(‘bakfull’);

2.2 增量备份定时任务
–增量备份（每周除周六外每天 23 点增量备份）：

call SP_CREATE_JOB(‘bakincr’,1,0,‘’,0,0,‘’,0,‘’);

call SP_JOB_CONFIG_START(‘bakincr’);

call SP_ADD_JOB_STEP(‘bakincr’, ‘bak2’, 6, ‘41010000/dmdata/dmbak’, 0, 0, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE(‘bakincr’, ‘std2’, 1, 2, 1, 63, 0, ‘23:00:00’, NULL, ‘2021-12-01 15:56:30’, NULL, ‘’);

call SP_JOB_CONFIG_COMMIT(‘bakincr’);

2.3 备份定期删除
–备份定期删除（每天 23：30 删除 14 天前备份）：

call SP_CREATE_JOB(‘delbak’,1,0,‘’,0,0,‘’,0,‘’);

call SP_JOB_CONFIG_START(‘delbak’);

call SP_ADD_JOB_STEP(‘delbak’,‘bak1’,0, ‘SF_BAKSET_BACKUP_DIR_ADD(’‘DISK’‘,’‘/dmdata/dmbak’‘);call sp_db_bakset_remove_batch(’‘DISK’‘,now()-14);’, 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE(‘delbak’, ‘del01’, 1, 1, 1, 0, 0, ‘23:30:00’, NULL, ‘2020-12-01 15:57:41’, NULL, ‘’);

call SP_JOB_CONFIG_COMMIT(‘delbak’);

2.4 自动收集统计信息的任务
–另外添加自动收集统计信息的任务（每天1点收集全库统计信息）

call SP_CREATE_JOB(‘statistics’,1,0,‘’,0,0,‘’,0,‘’);

call SP_JOB_CONFIG_START(‘statistics’);

call SP_ADD_JOB_STEP(‘statistics’, ‘statistics1’, 0, 'begin

for rs in (select ‘‘sf_set_SESSION_para_value(’’‘‘HAGR_HASH_SIZE’’‘’,(select cast(

case when max(table_rowcount(owner,table_name))<=(select max_value from v$dm_ini

where para_Name=‘’‘‘HAGR_HASH_SIZE’’‘’) and max(table_rowcount(owner,table_name))>=(

select min_value from v$dm_ini where para_Name=‘’‘‘HAGR_HASH_SIZE’’‘’) then

max(table_rowcount(owner,table_name)) when max(table_rowcount(owner,table_name))<(

select min_value from v$dm_ini where para_Name=‘’‘‘HAGR_HASH_SIZE’’‘’) then

(select min_value from v$dm_ini where para_Name=‘’‘‘HAGR_HASH_SIZE’’‘’) else

(select max_value from v$dm_ini where para_Name=‘’‘‘HAGR_HASH_SIZE’’‘’) end as bigint)

from dba_tables where owner=‘’‘’‘’||NAME||‘’‘’‘’));‘’

sql1,‘‘DBMS_STATS.GATHER_SCHEMA_STATS(’’‘’‘’||NAME||‘’‘’‘’,100,TRUE,‘’‘‘FOR ALL COLUMNS SIZE AUTO’’‘’);‘’

sql2

from SYS.SYSOBJECTS where TYPE$=‘‘SCH’’ ) loop

execute immediate rs.sql1;

execute immediate rs.sql2;

end loop;

end;', 0, 0, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE(‘statistics’, ‘statistics1’, 1, 2, 1, 64, 0, ‘01:00:00’, NULL, ‘2021-12-23 22:54:37’, NULL, ‘’);

call SP_JOB_CONFIG_COMMIT(‘statistics’);

3. 查看定时任务日志
   select * from sysjob.sysjobhistories2;

4、 查看统计更新情况
select

    sch.name 模式名         ,

    obj.name 对象名字        ,

    st.t_flag 属性类型T表C列I索引,

    st.last_gathered 收集时间,

    case when (st.t_total = 0 or t_flag='T'

                    ) then 100 else trunc(st.n_smaple*100/st.t_total) end 采用百分比

from

    (

            SELECT ID, T_FLAG, last_gathered, t_total, n_smaple FROM sysstats

            

            UNION ALL

            

            SELECT ID, T_FLAG, last_gathered, n_total, n_smaple FROM SYSMSTATS

    )

    ST            ,

    sysobjects obj,

    sysobjects sch

where

    sch.id =obj.schid

and st.id  =obj.id;