[Oracle DB] Vấn đề về lưu trữ: Thủ tục tự động thêm datafile mới khi datafile cũ sắp đầy
Hôm nay chúng ta sẽ thử làm một vài điều thú vị.
I. Bài toán
Như các bạn đã biết, các datafile của Oracle DB đều có giới hạn. Đối với các hệ thống bình thường (Qui mô dữ liệu chưa đến mức hàng trăm TB) thì với cấu hình blocksize 8KB => datafile sẽ có dung lượng tối đa là 32GB.
Vậy điều gì xảy ra khi datafile đạt tới hạn: Oracle DB sẽ không thể ghi dữ liệu vào datafile nữa, treo nghiệp vụ (app) => Trên thực tế, chúng ta sẽ cần làm các việc sau:
- Tạo job cảnh báo khi datafile chuẩn bị tới hạn.
- Tạo thủ tục tự động bổ sung datafile mới.
II. Thực hiện
--Tạo tablespace EMPLOYEE và bảng EMPLOYEES với các datafile giới hạn ở mức tối đa 100MB để test tình huống cần bổ sung datafile mới khi datafile cũ gần đầy:
CREATE TABLESPACE EMPLOYEE DATAFILE '/u01/app/oracle/oradata/CDB1/pdb1/EMPLOYEE.DBF' SIZE 250M AUTOEXTEND ON NEXT 250M MAXSIZE 1000M; CREATE TABLE OAZ21.EMPLOYEES ( ID NUMBER, NAME VARCHAR2(255 BYTE), DATETIME TIMESTAMP(6), SALARY NUMBER(10,2), CREDITCARD CHAR(12 BYTE) ) TABLESPACE EMPLOYEE NOLOGGING NOCOMPRESS NOCACHE ;-- Grant các quyền cho user nếu cần
GRANT SELECT ANY DICTIONARY TO OAZ21; GRANT SELECT_CATALOG_ROLE TO OAZ21; GRANT ALTER TABLESPACE TO OAZ21; GRANT EXECUTE ON DBMS_LOCK TO OAZ21;
-- Thủ tục tự động thêm datafile mới khi dung lượng datafile cũ gần đầy:
-- Th? t?c thêm datafile n?u dung lu?ng tr?ng nh? hon 15%
CREATE OR REPLACE PROCEDURE AddDatafileIfFull(p_tablespace_name IN VARCHAR2 := 'EMPLOYEE') IS
v_free_space NUMBER;
v_datafile_path VARCHAR2(255) := '/u01/app/oracle/oradata/CDB1/pdb1/'; -- The path where your datafiles are stored.
v_threshold NUMBER := 15; -- The free space threshold in percent below which to add a new datafile.
BEGIN
-- Check the free space percentage in the specified tablespace.
SELECT 100 - ROUND((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) INTO v_free_space
FROM dba_free_space fs
JOIN (SELECT tablespace_name, SUM(bytes) AS bytes
FROM dba_data_files
GROUP BY tablespace_name) df ON fs.tablespace_name = df.tablespace_name
WHERE df.tablespace_name = p_tablespace_name
GROUP BY df.tablespace_name, df.bytes;
-- If the free space is below the threshold, add a new datafile.
IF v_free_space < v_threshold THEN
EXECUTE IMMEDIATE 'ALTER TABLESPACE ' || p_tablespace_name || ' ADD DATAFILE '''
|| v_datafile_path || p_tablespace_name || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') || '.DBF'' SIZE 250M AUTOEXTEND ON NEXT 250M MAXSIZE 1000M';
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/-- Tạo job gọi thủ tục trên chạy kiểm tra dung lượng datafile hiện hành, với lab thì tôi để tần suất 10 giây / lần:
-- Ð?t job ch?y t? d?ng ki?m tra và thêm datafile
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'add_datafile_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN AddDatafileIfFull(''EMPLOYEE''); END;',
start_date => SYSTIMESTAMP,
-- repeat_interval => 'FREQ=HOURLY; BYMINUTE=0', -- Runs at the top of every hour
-- repeat_interval => 'FREQ=MINUTELY', -- Runs every minute
repeat_interval => 'FREQ=SECONDLY; INTERVAL=15', -- Runs every 15 seconds
end_date => NULL,
enabled => TRUE,
comments => 'Job to add datafile if EMPLOYEE tablespace is 85% full'
);
END;
/-- Để kiểm nghiệm, tôi sẽ viết thủ tục giả lập và insert 10 triệu bản ghi vào bảng EMPLOYEES, tổng dung lượng bảng sau khi insert khoảng 700 MB:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INSERT01',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_start_date TIMESTAMP := TO_TIMESTAMP('2024-01-01 00:00:01', 'YYYY-MM-DD HH24:MI:SS');
v_end_date TIMESTAMP := TO_TIMESTAMP('2024-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
v_diff_seconds NUMBER;
v_name VARCHAR2(255);
v_creditcard CHAR(12);
v_datetime TIMESTAMP;
BEGIN
-- Calculate the difference in seconds between the start and end dates
SELECT (EXTRACT(DAY FROM (v_end_date - v_start_date)) * 86400 +
EXTRACT(HOUR FROM (v_end_date - v_start_date)) * 3600 +
EXTRACT(MINUTE FROM (v_end_date - v_start_date)) * 60 +
EXTRACT(SECOND FROM (v_end_date - v_start_date)))
INTO v_diff_seconds
FROM dual;
FOR i IN 1..10000000 LOOP
v_name := 'Employee ' || TO_CHAR(i);
v_creditcard := LPAD(TRUNC(DBMS_RANDOM.VALUE(100000000000, 999999999999)), 12, '0');
-- Add a random interval, up to the difference calculated, to the start date
v_datetime := v_start_date + NUMTODSINTERVAL(TRUNC(DBMS_RANDOM.VALUE(0, v_diff_seconds)), 'SECOND');
INSERT INTO EMPLOYEES (ID, Name, Datetime, Salary, CreditCard)
VALUES (i, v_name, v_datetime, DBMS_RANDOM.VALUE(10000, 20000), v_creditcard);
IF MOD(i, 500000) = 0 THEN
COMMIT;
DBMS_LOCK.SLEEP(5); -- Pause for 5 seconds after each commit to reduce load
END IF;
END LOOP;
COMMIT; -- Ensure the final commit
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => ''
);
END;
/-- Và bắt đầu chạy thủ tục insert, khi này ta có thể dùng công cụ Toad để xem các datafile mà thủ tục tự động tạo thêm để ghi được dữ liệu:
SELECT FILE_NAME,FILE_ID,TABLESPACE_NAME,round(BYTES /1024 /1024,2) BYTES_MB FROM dba_data_files where TABLESPACE_NAME = 'EMPLOYEE';
--GRANT EXECUTE ON DBMS_LOCK TO OAZ21;
BEGIN
-- Stop the job first
DBMS_SCHEDULER.STOP_JOB(
job_name => 'OAZ21.INSERT_EMPLOYEE_S01',
force => TRUE -- Use force to immediately stop the job
);
-- Removed the sleep delay
-- Now drop the job
DBMS_SCHEDULER.DROP_JOB(
job_name => 'OAZ21.INSERT_EMPLOYEE_S01'
);
END;
/
select * from ALL_SCHEDULER_JOB_RUN_DETAILS
--where OWNER = 'TUANTN'
order by LOG_ID desc;
--watch -n 5 'echo "SELECT JOB_NAME,SLAVE_OS_PROCESS_ID,ELAPSED_TIME,CPU_USED FROM USER_SCHEDULER_RUNNING_JOBS;" | sqlplus tuantn/1985Mobi@cdb1'
SELECT *--JOB_NAME,SLAVE_OS_PROCESS_ID,ELAPSED_TIME,CPU_USED
FROM USER_SCHEDULER_RUNNING_JOBS;
Nhận xét
Đăng nhận xét