Quản lý vấn đề lưu trữ khi xây dựng 1 hệ thống (Phần 2)

Bài viết này nêu 1 Case cụ thể trong việc dựng 1 CSDL mới và quản lý dung lượng của CDSL này bằng các script chạy tự động.
Trình tự các bước thực hiện trong Case này như sau:
1. Tạo 1 schema mới
2. Tạo thêm các tablespace dùng để test: Tablespace Data dành cho các Partitioned Table, Tablespace Index dành cho các Partitioned Index
3. Tạo các script quản lý dung lượng gồm: script thêm Datafile cho các Tablespace khi dung lượng tới hạn, script thêm Tablespace cho các tháng, script thêm Partition Table theo ngày
4. Testcase cho các trường hợp
Chi tiết các bước thực hiện:

1. Tạo 1 schema mới như sau:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

CREATE TABLESPACE orcl_data DATAFILE 'C:\Oracle\oradata\orcl\orcl_owner\ORCL_DATA.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M; / CREATE TABLESPACE orcl_idx DATAFILE 'C:\Oracle\oradata\orcl\orcl_owner\ORCL_IDX.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M; / CREATE TEMPORARY TABLESPACE orcl_tmp TEMPFILE 'C:\Oracle\oradata\orcl\orcl_owner\ORCL_TMP.dbf' SIZE 200M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M; / CREATE USER orcl_owner IDENTIFIED BY orclo DEFAULT TABLESPACE orcl_data TEMPORARY TABLESPACE orcl_tmp / GRANT UNLIMITED TABLESPACE TO orcl_owner / GRANT CONNECT TO orcl_owner / GRANT DBA TO orcl_owner / GRANT RESOURCE TO orcl_owner / GRANT SELECT ANY DICTIONARY TO orcl_owner / ALTER USER orcl_owner DEFAULT ROLE ALL

 2. Tạo thêm các tablespace dùng để test: Tablespace Data dành cho các Partitioned Table, Tablespace Index dành cho các Partitioned Index

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE TABLESPACE orcl_data_201407 DATAFILE 'C:\Oracle\oradata\orcl\orcl_owner\orcl_data_201407_1.dbf' SIZE 5M AUTOEXTEND ON NEXT 10M MAXSIZE 50M; / CREATE TABLESPACE orcl_idx_201407 DATAFILE 'C:\Oracle\oradata\orcl\orcl_owner\orcl_idx_201407_2.dbf' SIZE 5M AUTOEXTEND ON NEXT 10M MAXSIZE 50M;

3. Tạo các script quản lý dung lượng gồm: script thêm Datafile cho các Tablespace khi dung lượng tới hạn, script thêm Tablespace cho các tháng, script thêm Partition Table tất cả các ngày trong tháng
3.1 Script thêm Datafile
Tham số của script:

• p_percent_free: nếu dung lượng của Tablespace dưới ngưỡng này thì thủ tục sẽ thêm 1 Datafile mới cho Tablespace
• p_oradata_home: đường dẫn chứa Datafile
• p_init_size: kích thước khởi tạo cho Datafile
• p_extend_size: kích thước mở rộng mỗi lần cho Datafile
• p_maxsize: kích thước tối đa cho Datafile

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

CREATE OR REPLACE PROCEDURE pr_add_datafile (p_percent_free IN NUMBER, p_oradata_home IN VARCHAR2, p_init_size IN VARCHAR2, p_extend_size IN VARCHAR2, p_maxsize IN VARCHAR2) IS CURSOR c_tablespace IS SELECT df.tablespace_name FROM dba_free_space fs, ( SELECT tablespace_name, SUM (bytes) bytes FROM dba_data_files WHERE tablespace_name LIKE 'ORCL%' GROUP BY tablespace_name) df WHERE fs.tablespace_name(+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytes HAVING NVL (ROUND (SUM (fs.bytes) * 100 / df.bytes), 1) < p_percent_free; v_count NUMBER; v_sql VARCHAR2 (3000); BEGIN FOR r_tablespace IN c_tablespace LOOP v_sql := 'ALTER TABLESPACE ' || r_tablespace.tablespace_name || ' ADD DATAFILE ''' || p_oradata_home || r_tablespace.tablespace_name || '_' || seq_datafile.NEXTVAL || '.DBF'' SIZE ' || p_init_size || ' REUSE AUTOEXTEND ON NEXT ' || p_extend_size || ' MAXSIZE ' || p_maxsize; DBMS_OUTPUT.put_line (v_sql); EXECUTE IMMEDIATE v_sql; END LOOP; EXCEPTION WHEN OTHERS THEN raise_application_error (-20080, 'Add Datafile: ' || SQLERRM); END;

Script trên có sử dụng sequence trong lúc tạo Datafile. Đây là đoạn script tạo sequence:

1	CREATE SEQUENCE seq_datafile START WITH 10 INCREMENT BY 1 MAXVALUE 999999999;

3.2 Script tạo Tablespace hàng tháng
Tham số của script:

• p_list_tablespace: danh sách các tablespace cần tạo
• p_oradata_home: đường dẫn chứa Datafile
• p_init_size: kích thước khởi tạo cho Datafile
• p_extend_size: kích thước mở rộng mỗi lần cho Datafile
• p_maxsize: kích thước tối đa cho Datafile

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

CREATE OR REPLACE PROCEDURE pr_create_monthly_tablespace ( p_list_tablespace IN VARCHAR2, p_oradata_home IN VARCHAR2, p_init_size IN VARCHAR2, p_extend_size IN VARCHAR2, p_maxsize IN VARCHAR2) IS v_sql VARCHAR2 (3000); v_date_pattern VARCHAR2 (7) := '_yyyymm'; CURSOR c_tablespace IS SELECT TRIM (COLUMN_VALUE) || TO_CHAR (SYSDATE, v_date_pattern) tablespace_name FROM XMLTABLE ( ('"' || REPLACE (p_list_tablespace, ',', '","') || '"')) UNION ALL SELECT TRIM (COLUMN_VALUE) || TO_CHAR (ADD_MONTHS (SYSDATE, 1), v_date_pattern) tablespace_name FROM XMLTABLE ( ('"' || REPLACE (p_list_tablespace, ',', '","') || '"')) MINUS SELECT tablespace_name FROM dba_tablespaces; BEGIN FOR r_tablespace IN c_tablespace LOOP v_sql := 'CREATE TABLESPACE ' || r_tablespace.tablespace_name || ' DATAFILE ''' || p_oradata_home || r_tablespace.tablespace_name || '_' || seq_datafile.NEXTVAL || '.DBF'' SIZE ' || p_init_size || ' REUSE AUTOEXTEND ON NEXT ' || p_extend_size || ' MAXSIZE ' || p_maxsize; DBMS_OUTPUT.put_line (v_sql); EXECUTE IMMEDIATE v_sql; END LOOP; EXCEPTION WHEN OTHERS THEN raise_application_error (-20080, 'Create Monthly Tablespace: ' || SQLERRM); END;

 3.3 Script tạo Partition tất cả các ngày trong tháng cho tất cả các Partitioned Table

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

CREATE OR REPLACE PROCEDURE pr_add_monthly_partition IS v_sql VARCHAR2 (3000); v_date_pattern VARCHAR2 (8) := 'yyyymmdd'; CURSOR c_tab_partitions IS SELECT DISTINCT table_name FROM dba_tab_partitions WHERE table_owner = 'ORCL_OWNER' AND table_name NOT LIKE 'BIN$%'; BEGIN FOR r_tab_partitions IN c_tab_partitions LOOP DBMS_OUTPUT.put_line ( 'Add partition for table: ' || r_tab_partitions.table_name); FOR r_partition IN (SELECT TO_CHAR ( TO_DATE (SUBSTR (partition_name, 6), v_date_pattern) + 1, v_date_pattern) upper_bound, a.* FROM ( SELECT 'DATA_' || TO_CHAR ( TRUNC (ADD_MONTHS (SYSDATE, 1), 'MONTH') + LEVEL - 1, v_date_pattern) partition_name, 'ORCL_DATA_' || TO_CHAR ( TRUNC (ADD_MONTHS (SYSDATE, 1), 'MONTH'), 'yyyymm') tablespace_name FROM DUAL CONNECT BY LEVEL <= (SELECT TO_NUMBER ( TO_CHAR ( LAST_DAY ( ADD_MONTHS ( SYSDATE, 1)), 'dd')) FROM DUAL) MINUS SELECT partition_name, tablespace_name FROM dba_tab_partitions WHERE table_name = r_tab_partitions.table_name) a) LOOP v_sql := 'ALTER TABLE ' || r_tab_partitions.table_name || ' ADD PARTITION ' || r_partition.partition_name || ' VALUES LESS THAN (TO_DATE(''' || r_partition.upper_bound || ''',''' || v_date_pattern || ''')) TABLESPACE orcl_data_' || TO_CHAR (ADD_MONTHS (SYSDATE, 1), 'yyyymm'); DBMS_OUTPUT.put_line (v_sql); EXECUTE IMMEDIATE v_sql; END LOOP; END LOOP; EXCEPTION WHEN OTHERS THEN raise_application_error (-20080, 'Create Monthly Partition: ' || SQLERRM); END;

4. Testcase cho các trường hợp
4.1 Tạo 1 Partitioned Table

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE big_table ( id NUMBER NOT NULL, bt_date DATE NOT NULL, bt_comments VARCHAR2 (100) ) PARTITION BY RANGE (bt_date) ( PARTITION data_20140701 VALUES LESS THAN (TO_DATE ('02/07/2014', 'DD/MM/YYYY')) TABLESPACE orcl_data_201407);

* Lưu ý: dữ liệu của bảng này ta lưu vào tablespace "orcl_data_201407" để dễ quản lý. Ta có thể tạo thêm các partition theo ngày cho đủ các partition trong tháng 7.
 4.2 Tạo Index cho bảng này

1 2 3 4

CREATE INDEX big_table_idx ON big_table (id, bt_date) LOCAL TABLESPACE orcl_idx_201407;

* Lưu ý: index của bảng này ta lưu vào tablespace "orcl_idx_201407" để dễ quản lý
Trong ví dụ này ta sử dụng Local Index

4.3 Kiểm tra dung lượng của các Tablespace bằng script sau

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM (fs.bytes) / (1024 * 1024) "Free (MB)", NVL (ROUND (SUM (fs.bytes) * 100 / df.bytes), 1) "% Free", ROUND ( (df.bytes - SUM (fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, ( SELECT tablespace_name, SUM (bytes) bytes FROM dba_data_files WHERE tablespace_name LIKE 'ORCL%' GROUP BY tablespace_name) df WHERE fs.tablespace_name(+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytes;

4.4 Insert dữ liệu vào bảng "big_table"

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

DECLARE v_create_date DATE := TO_DATE ('01/07/2014', 'dd/mm/yyyy'); v_rows NUMBER := 1000000; BEGIN FOR i IN 1 .. v_rows LOOP INSERT INTO big_table (id, bt_date, bt_comments) VALUES (i, v_create_date, 'This is some data for ' || i); END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN raise_application_error (-20080, 'Insert data: ' || SQLERRM); ROLLBACK; END;

4.5 Ta dùng script ở bước 4.3 để kiểm tra dung lượng của tablespace "orcl_data_201407" và "orcl_idx_201407" đã tới ngưỡng gần đầy chưa (ngưỡng tạo ban đầu có maxsize=50M). Nếu chưa tới ta có thể chạy lại script insert dữ liệu ở bước 4.4 cho các ngày tiếp theo và sau đó lại dùng script 4.3 để kiểm tra. Cứ lặp lại như vậy cho đến khi dung lượng tablespace chứa dữ liệu gần tới hạn.
Lúc này ta sẽ dùng script 3.1 để thêm Datafile cho các tablespace bị thiếu.
Các script 3.2 và 3.3 sẽ được dùng để tạo cho các tháng tiếp theo
Cuối cùng để cho các script 3.1, 3.2, 3.3 làm việc 1 cách tự động để giám sát và quản lý dung lượng ta có thể đặt lịch cho các script này chạy tự động hàng ngày (script 3.1) và hàng tháng (script 3.2 và 3.3) thông qua tiến trình Schedule của Oracle hoặc dùng 1 ứng dụng khác để gọi các script này 1 cách định kỳ.

Phụ lục: script mẫu gọi các thủ tục 3.1, 3.2 và 3.3

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

-- Add Datafile DECLARE free NUMBER := 30; ora_home VARCHAR2 (3000) := 'C:\ORACLE\ORADATA\ORCL\ORCL_OWNER\'; init_size VARCHAR2 (10) := '5M'; extend_size VARCHAR2 (10) := '10M'; max_size VARCHAR2 (10) := '50M'; BEGIN pr_add_datafile2 (p_percent_free => free, p_oradata_home => ora_home, p_init_size => init_size, p_extend_size => extend_size, p_maxsize => max_size); END; -- Add Tablespace DECLARE list_tablespace VARCHAR2 (100) := 'ORCL_DATA,ORCL_IDX'; ora_home VARCHAR2 (3000) := 'C:\ORACLE\ORADATA\ORCL\ORCL_OWNER\'; init_size VARCHAR2 (10) := '5M'; extend_size VARCHAR2 (10) := '10M'; max_size VARCHAR2 (10) := '50M'; BEGIN pr_create_monthly_tablespace (p_list_tablespace => list_tablespace, p_oradata_home => ora_home, p_init_size => init_size, p_extend_size => extend_size, p_maxsize => max_size); END; -- Add Partitions BEGIN pr_add_monthly_partition; END;

*** Lưu ý: trong quá trình thực thi các script có thể Oracle sẽ báo thiếu quyền, ta chạy các câu lệnh sau để thêm quyền cho user:

1 2 3 4

GRANT CREATE TABLESPACE TO orcl_owner; GRANT SELECT ON dba_tablespaces TO orcl_owner; GRANT SELECT ON dba_data_files TO orcl_owner; GRANT ALTER TABLESPACE TO orcl_owner;