TRẦN VĂN BÌNH MASTER: Thủ tục add datafile TỰ ĐỘNG trên Oracle Database ASM với datafile không autoextend

Thứ Sáu, 7 tháng 5, 2021

Thủ tục add datafile TỰ ĐỘNG trên Oracle Database ASM với datafile không autoextend

Mục đích: add datafile tự động của DB lớn cho Oracle Database với datafile không autoextend

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 := 100000;

CURSOR c_free_space -- get tablespace free left 200MB.

SELECT

a.tablespace_name,

ROUND (a.bytes_alloc / 1024 / 1024) megs_alloc,

ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) megs_free,

ROUND (maxbytes / 1048576) MAX,

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

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 (LOWER (a.tablespace_name) IN

('data','indx',

'indx' || TO_CHAR (SYSDATE, 'YYYY'),

'indx' || TO_CHAR (SYSDATE+30, 'YYYY'),

'indx' || TO_CHAR (SYSDATE, 'YYYYMM'),

'indx' || TO_CHAR (SYSDATE+30, 'YYYYMM'),

'data' || TO_CHAR (SYSDATE, 'YYYY'),

'data' || TO_CHAR (SYSDATE+30, 'YYYY'),

'data' || TO_CHAR (SYSDATE, 'YYYYMM'),

'data' || TO_CHAR (SYSDATE+30, 'YYYYMM'))) */

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

order by ROUND (maxbytes / 1048576)-ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024) 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 8g autoextend off';

--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