Oracle Tech Talk

All about Oracle Technologies

Saturday 9 May 2015

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

13:47 Posted by NTP , No comments
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;

Saturday 2 May 2015

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

13:30 Posted by NTP , No comments
Trong quá trình xây dựng CSDL cho 1 hệ thống ta thường hay gặp phải vấn đề về quản lý dung lượng lưu trữ khi dữ liệu hệ thống "phình" lên. Để có thể quản lý được dữ liệu này cũng như quản lý dung lượng trên thiết bị lưu trữ tốt thì khâu thiết kế lưu trữ dữ liệu ban đầu rất quan trọng.

Trước hết ta hãy khảo sát trường hợp "Dung lượng lưu trữ tăng trong khi dung lượng thiết bị lưu trữ gần tới hạn" với 2 phạm vi sau:
1. Phạm vi của yêu cầu này là không mở rộng thêm dung lượng lưu trữ (chỉ sử dụng phân vùng lưu trữ được cấp phát ban đầu):  --> Yêu cầu phải xóa bớt dữ liệu theo 1 điều kiện nào đó như từ tháng N đến tháng M và sau khi xóa PHẢI TRẢ LẠI FREE SPACE để chứa dữ liệu mới
2. Phạm vi của yêu cầu này là được mở rộng thêm dung lượng lưu trữ: --> Yêu cầu là di chuyển dữ liệu của 1 số bảng được tổ chức lưu trữ theo ngày / tháng / năm sang phân vùng mới

Với 2 trường hợp trên, ta thấy rằng để thực hiện được các yêu cầu trên thì việc thiết kế CSDL lúc ban đầu rất quan trọng nếu không khi có phát sinh những yêu cầu tương tự như trên thì việc thực hiện sẽ rất khó và chiếm nhiều thời gian của chúng ta.

Khi thiết kế CSDL, Oracle đã cho phép chúng ta thiết kế các vấn đề về lưu trữ theo các khái niệm của Oracle như: Tablespace, Datafile, Partitioned Table, Partitioned Index, ... để giúp chúng ta dễ quản lý hơn. Dưới đây là 1 vài tóm tắt ngắn gọn theo ý của tác giả bài viết cho các khái niệm trên:
1. Tablespace: tên của vùng lưu trữ các Object trong Oracle
2. Datafile: tên tập tin lưu trữ Tablespace
3. Partitioned Table: là các table lưu trữ dữ liệu được phân hoạch theo từng phân vùng (Partition) để dễ quản lý
4. Partitioned Index: là các index lưu trữ dữ liệu được phân hoạch theo từng phân vùng (Partition) để dễ quản lý

Nhìn vào hình trên ta thấy rằng kiến trúc của Oracle đã rất rõ ràng cho việc thiết kế lưu trữ và quản lý do đó khi thiết kế 1 CSDL cho ứng dụng ta cứ theo mô hình cơ bản trên thì sẽ đảm bảo được cho việc dễ dàng quản lý sau này.

Để có thể đảm bảo được việc thiết kế lúc ban đầu và phát triển giữ được mô hình thiết kế thì người phát triển phải tuân thủ một số quy định như sau:
1. Các Partitioned Table được lưu trữ vào các Tablespace dành riêng cho các Partitioned Table
2. Các Partitioned Index được lưu trữ vào các Tablespace dành riêng cho các Partitioned Index
3. Các table bình thường còn lại được lưu trữ vào Tablespace Data Dafault
4. Các index bình thường còn lại được lưu trữ vào Tablespace Index Default
Người phát triển khi phát triển cần lưu ý các vấn đề trên để khi tạo table và index cần phải xác định và chỉ rõ đối tượng đó thuộc về Tablespace nào theo cú pháp sau:

1
2
3
 
CREATE TABLE <table_name> (
<column_name>  <column_data_type>)
TABLESPACE <tablespace_name>;