TRẦN VĂN BÌNH MASTER: Tự động add datafile cho Oracle Database ASM với datafile autoextend

Thứ Sáu, 25 tháng 3, 2022

Tự động add datafile cho Oracle Database ASM với datafile autoextend

Mục đích: Add thêm datafile cho tablespace khi sử dụng đến ngưỡng >=90% hoặc khi còn free < 50.000MB (gần 50GB) (code dưới tôi sử dụng < 50GB free là thêm datafile)

Hoạt động: Mỗi 30 phút kiểm tra dung lượng tablespace, nếu >= 90% (ngưỡng) hoặc khi còn free < 50.000MB (gần 50GB) sẽ tự động khai báo thêm datafile

BƯỚC 1: TẠO CÁC BẢNG LƯU LOG:

CREATE TABLE binhtv.dbamf_log_op

(id NUMBER,

msg VARCHAR2(1000 BYTE),

event_datetime TIMESTAMP (6) DEFAULT sysdate,

type VARCHAR2(1000 BYTE),

note VARCHAR2(1000 BYTE))

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

CREATE SEQUENCE BINHTV.DBAMF_LOG_OP_SEQ

START WITH 1

MAXVALUE 9999999999999999999999999999

MINVALUE 1

NOCYCLE

CACHE 20

NOORDER;

BƯỚC 2: TẠO THỦ TỤC TRÊN SYS

PROCEDURE sys.auto_extend_space

v_err varchar2(1000):='';

free_space_low_level NUMBER := 50000;

CURSOR c_free_space -- get tablespace free left 50000MB.

SELECT a.tablespace_name,100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Usage",

ROUND (a.bytes_alloc / 1024 / 1024) "Size MB",

ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Used MB",

ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Free MB",

--ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Free",

ROUND (maxbytes / 1048576) "Max MB",

round(maxbytes/1048576-(ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024)),0) "Free_MB_Max",

ROUND (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)/ ROUND (maxbytes / 1048576) * 100) "%Used of Max"

FROM (SELECT f.tablespace_name, SUM (f.bytes) bytes_alloc, SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes

FROM dba_data_files f

GROUP BY tablespace_name) a,

(SELECT f.tablespace_name, SUM (f.bytes) bytes_free FROM dba_free_space f GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name(+)

--and ( a.tablespace_name not in ('DUMP'))

-- and "Free_MB_Max" < 50000

and round(maxbytes/1048576-(ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024)),0) < free_space_low_level

order by "%Used of Max" desc;

v_sql VARCHAR2 (2000);

msg VARCHAR2 (1000);

next_datafile VARCHAR2 (1000);

BEGIN

insert into binhtv.dbamf_log_jobs (id,name,status,event_date, note)

values(binhtv.dbamf_log_jobs_seq.nextval,'Starting sys.dba_op.extend_space',1,sysdate,'binhtv.dbamf_log_jobs, binhtv.dbamf_log_op');

commit;

FOR v_free_space IN c_free_space -- tablespace < 200MB

LOOP

BEGIN

v_sql :=

'ALTER TABLESPACE '

|| v_free_space.tablespace_name

|| ' ADD DATAFILE ''+DATA'' size 1g autoextend on next 100m';

DBMS_OUTPUT.put_line (v_sql);

EXECUTE IMMEDIATE v_sql;

insert into binhtv.dbamf_log_op (msg, type, note) values (v_sql, 'df','Add a new datafile');

commit;

EXCEPTION

WHEN OTHERS

THEN

null;

END;

END LOOP;

insert into binhtv.dbamf_log_jobs (id,name,status,event_date, note)

values(binhtv.dbamf_log_jobs_seq.nextval,'Completed sys.dba_op.extend_space',1,sysdate,'binhtv.dbamf_log_jobs');

commit;

EXCEPTION

WHEN others THEN

v_err := substr(SQLERRM,1,200);

insert into binhtv.dbamf_log_jobs (id,name,status,event_date, note)

values(binhtv.dbamf_log_jobs_seq.nextval,'Error sys.dba_op.extend_space',-1,sysdate,'Error sys.dba_op.extend_spac, '||v_err);

commit;

END;

BƯỚC 3: Tạo job từ sched.Jobs: 10 phút chạy 1 lần

Vào giao diện hoặc chạy câu lệnh

BEGIN

SYS.DBMS_SCHEDULER.DROP_JOB

(job_name => 'SYS.DBAVIET_ADD_DF');

END;

BEGIN

SYS.DBMS_SCHEDULER.CREATE_JOB

(

job_name => 'SYS.DBAVIET_ADD_DF'

,start_date => TO_TIMESTAMP_TZ('2021/05/07 10:45:13.876967 Asia/Ho_Chi_Minh','yyyy/mm/dd hh24:mi:ss.ff tzr')

,repeat_interval => 'FREQ=MINUTELY;INTERVAL=10'

,end_date => NULL

,job_class => 'DEFAULT_JOB_CLASS'

,job_type => 'STORED_PROCEDURE'

,job_action => 'SYS.AUTO_EXTEND_SPACE'

,comments => NULL

);

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE