Sunday, May 31, 2009

การใช้ Constraints เพื่อเพิ่มประสิทธิภาพในการคิวรี (ตอนที่ 1)

(แปลจาก “On Constraints, Metadata, and Truth” โดย Tom Kyte, Oracle Magazine V XXIII, Issue3)
เป็นเรื่องน่าสนใจว่า Constraints สำคัญต่อความเร็วในการคิวรีเพียงใด คนส่วนใหญ่จะคิดว่า Constraints เป็นเรื่องที่เกี่ยวกับ Data Integrity ซึ่งก็ถูก แต่ Constraints ก็มีผลต่อ Optimizer ในการคิด Execution Plan เช่นกัน ซึ่งองค์ประกอบของตัวแปรที่ Optimizer ใช้ในการคำนวณได้แก่
• คำสั่ง SQL
• ค่าสถิติของตารางหรือ Database Objects อื่นๆ ที่มี ทั้งหมดที่เกี่ยวข้อง
• ข้อมูลทางสถิติของระบบ เช่น ความเร็ว CPU, ความเร็วในการอ่าน 1 Data Block เป็นต้น
• ตัวแปรตั้งต้นของฐานข้อมูล (Initialization Parameters)
• และ Constaints!
Optimizer จะใช้ข้อมูลเหล่านี้ทั้งหมดในการคำนวณ Execution Plan ที่ดีที่สุด ผมพบว่าบางครั้ง เรามีแนวโน้มที่จะไม่ใช้ Constraints ใน Data Warehouse หรือในระบบที่ใช้ในการออก Report โดยอ้างว่า “ข้อมูลดีอยู่แล้ว เราได้ทำการ Cleansing ข้อมูลแล้ว เราไม่ต้องใช้ Constraints เพื่อควบคุมความสอดคล้องกันของข้อมูล (Data Integrity) แล้ว”
ความจริงก็คือเขาอาจจะไม่ต้องการ Constraints ในการควบคุมความสอดคล้องกันของข้อมูล (แต่เขาอาจจะแปลกใจถ้าลองได้ลองเปิด (Enable) Constraints ขึ้นมาจริง ๆ) อย่างไรก็ตามเขายังคงต้องการ Constraints เพื่อที่จะได้ Execution Plan ที่ดีที่สุด ในระบบขนาดใหญ่อย่าง Data Warehouse ถ้าคิวรีมี Execution Plan ที่ไม่ดีอาจจะต้องใช้เวลาเป็นชั่วโมงหรือเป็นวัน แทนที่จะเป็นวินาทีหรือเป็นนาที ดังนั้น Data Warehouse จึงจำเป็นจะต้องมี Constraints เพื่อประโยชน์ในแง่ความเร็วในการคิวรี

ลองดูตัวอย่าง (ทดลองกับ Oracle 11g Release 11.1.0.6) ตัวอย่างแรกเป็นคุณสมบัติของ Optimizer ที่เรียกว่า Partition View Elimination ซึ่งมีมาตั้งแต่เวอร์ชั่น 7.3 กล่าวคือถ้าเราให้ข้อมูลกับ Optimizer มากพอ (อาจจะโดยการกำหนด Constraints) มันก็จะสามารถตัดเอาบางตารางที่ไม่จำเป็นต้องใช้ในการคิวรีได้

ใน Listing1 เราสร้างตารางขึ้นมาสองตาราง ซึ่งเก็บข้อมูลที่เป็นคนละชุดกัน ไม่มีข้อมูลใดที่อยู่ในทั้งสองตาราง (Mutually Exclusive) โดยใช้คอลัมน์ OBJECT_TYPE และเราก็ได้กำหนด View ซึ่ง UNION ALL ตารางทั้งสองเข้าด้วยกัน
==========================================================
Listing1: สร้างตารางสองตารางที่มีข้อมูลไม่ซ้ำกัน และสร้างวิว
SQL> create table t1
2 as
3 select * from all_objects
4 where object_type in ('TABLE','VIEW');

Table created.

SQL> alter table t1 modify object_type not null;

Table altered.

SQL> alter table t1 add constraint t1_check_otype
2 check (object_type in ('TABLE','VIEW'));

Table altered.

SQL> create table t2
2 as
3 select * from all_objects
4 where object_type in ('SYNONYM','PROCEDURE');

Table created.

SQL> alter table t2 modify object_type not null;

Table altered.

SQL> alter table t2 add constraint t2_check_otype
2 check (object_type in ('SYNONYM','PROCEDURE'));

Table altered.

SQL> create or replace view v
2 as
3 select * from t1
4 union all
5 select * from t2;

View created.

==========================================================

คราวนี้ใน Listing2 เราคิวรีวิวตัวที่เราสร้างนี้ (V) และใช้ OBJECT_TYPE ในส่วน Where ของคำสั่ง และดูว่า Optimizer ตอบสนองยังไง

==========================================================
Listing2: การตอบสนองของ Optimizer

SQL> set autotrace traceonly explain
SQL> select * from v where object_type='TABLE';

Execution Plan
----------------------------------------------------------
Plan hash value: 3982894595

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 5372 | 128 (1)| 00:00:02 |
| 1 | VIEW | V | 34 | 5372 | 128 (1)| 00:00:02 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL | T1 | 136 | 21488 | 10 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| T2 | 5 | 790 | 119 (0)| 00:00:02 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("OBJECT_TYPE"='TABLE')
4 - filter(NULL IS NOT NULL)
5 - filter("OBJECT_TYPE"='TABLE')
==========================================================
ครั้งแรกดูเหมือนว่าใน Execution Plan ไม่ได้เอาตาราง T2 ออก (เพราะใน T2 ไม่มี OBJECT_TYPE เป็น ‘TABLE’) แต่เมื่อดูลึก ๆ จะพบว่ามีการ Filter ที่ข้อ 4 ด้วยดังนี้
“NULL IS NOT NULL”
ซึ่งน่าสนใจตรงที่ว่า เราไม่ได้เป็นคนกำหนด Filter ตัวนี้ และเนื่องจาก NULL IS NOT NULL เป็นเท็จเสมอ Execution Plan ตัวนี้จึงไม่เคยถูกใช้เลย (ถ้าเราใช้ TKPROF เราจะพบว่าไม่มี I/O เกิดขึ้นบนตาราง T2 เลย

คราวนี้มาดูตัวอย่างที่สอง เราจะมาดูที่ NOT NULL Constraint และดูว่าทำไมมันจึงสำคัญเอามาก ๆ เราจะสร้างตาราง และอินเด็กซ์คอลัมน์ที่สามารถเป็น NULL ได้

SQL> create table t
2 as
3 select * from all_objects;

Table created.

SQL> create index t_idx on t(object_type);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,’T’);

PL/SQL procedure successfully completed.

คราวนี้เราจะนับจำนวนแถวในตารางนี้ ซึ่งโดยปกติ Optimizer จะมีวิธีการเดียวในการแสดงผลดังใน Listing3

==========================================================
Listing3: วิธีการเดียวที่ใช้ในการนับจำนวนแถว

SQL> set autotrace traceonly explain
SQL> select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 228 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 53798 | 228 (1)| 00:00:03 |
-------------------------------------------------------------------
==========================================================

เนื่องจากว่า OBJECT_TYPE สามารถเป็น NULLได้ และอินเด็กซ์จะไม่เก็บข้อมูลใด ๆ ถ้าทุก ๆ คอลัมน์ที่ประกอบเป็นอินเด็กซ์ล้วนเป็น NULL ดังนั้นเรา (Optimizer) จึงไม่สามารถนับจำนวนแถวในตารางโดยใช้อินเด็กซ์ได้ – เราจึงต้องใช้วิธีการ Full Table Scan ดังนั้นถ้าเราบอก Optimizer ว่า OBJECT_TYPE IS NOT NULL เราจะเห็นว่า Execution Plan เปลี่ยนไป (ในทางที่ดีกว่า) ดังแสดงใน Listing4
==========================================================
Listing4: วิธีที่ดีกว่าที่จะนับจำนวนแถว

SQL> alter table t modify object_type NOT NULL;

Table altered.

SQL> set autotrace traceonly explain;
SQL> select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1058879072

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| T_IDX | 53798 | 43 (0)| 00:00:01 |
==========================================================

แต่ว่าถ้ายังไง ๆ เราก็ต้องให้ OBJECT_TYPE เป็น NULL ล่ะ มีวิธีการใดพอจะช่วยได้ไหม? -- สิ่งที่เราสามารถทำได้ก็คือเพิ่มคอลัมน์ใด ๆ ที่เป็น NOT NULL เข้าไปในอินเด็กซ์ ซึ่งจะทำให้อินเด็กซ์สามารถถูกใช้ได้ตอนทำ Full Table Scan ได้ แม้ว่าคิวรีจะมี WHERE Clause เป็น OBJECT_TYPE IS NULL ซึ่งอาจจะทำให้หลาย ๆ คนแปลกใจด้วยความเชื่อที่ว่า “IS NULL” ไม่สามารถใช้อินเด็กซ์ ซึ่งผมเชื่อว่าเนื่องมาจากคนส่วนใหญ่คิดว่าค่าที่เป็น Null จะไม่ถูกเก็บในอินเด็กซ์

ความจริงก็คือค่า Null อาจจะถูกอินเด็กซ์ได้ เพียงแต่ถ้าทุก ๆ คอลัมน์ที่ประกอบกันเป็นอินเด็กซ์มีค่าเป็น Null จึงจะไม่ถูกเก็บในอินเด็กซ์ ดังนั้นถ้ามีอย่างน้อยหนึ่งคอลัมน์ในอินเด็กซ์ที่ NOT NULL ทุกๆ แถวในตารางจะถูกเก็บในอินเด็กซ์ ลองดู Listing5

==========================================================
Listing5: Execution Plan ที่ใช้ Full Table Scan เมื่อ OBJECT_TYPE สามารถเป็น NULL

SQL> alter table t modify object_type NULL;

Table altered.

SQL> set autotrace traceonly explain
SQL> select * from t where object_type is null;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 102 | 228 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 1 | 102 | 228 (1)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE" IS NULL)

==========================================================

แสดงให้เห็นว่าถ้า OBJECT_TYPE สามารถเป็น NULL ได้ Optimizer จะไม่สามารถใช้อินเด็กซ์ในการหา OBJECT_TYPE IS NULL ได้ ถ้าเราเพิ่มคอลัมน์ที่เป็น NOT NULL เข้าไปในอินเด็กซ์ Execution Plan จะเปลี่ยนไป ซึ่งในที่นี่ผมเพิ่มค่า 0 เข้าไปในอินเด็กซ์ (จริง ๆ แล้วค่าใด ๆ ก็ได้ ขอแค่ NOT NULL ก็พอ ซึ่ง 0 เป็นค่าที่เล็กมาก ผมจึงนำมาใช้)

SQL> drop index t_idx;
Index dropped.

SQL> create index t_idx
2 on t (object_type,0);
Index created.

ใน Listing6 แสดง Execution Plan ที่สามารถใช้อินเด็กซ์

==========================================================
Listing6: Execution Plan ที่ใช้อินเด็กซ์เมื่ออินเด็กซ์มีคอลัมน์ที่เป็น NOT NULL

SQL> select * from t where object_type is null;

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 102 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 102 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE" IS NULL) ==========================================================
(ยังไม่จบนะครับ ยังมีตอนต่อไป...ขอขอบคุณที่ให้ความสนใจและโปรดติดตามตอนต่อไปนะครับ)

อ่านเพิ่มเติม:
การใช้ Constraints เพื่อเพิ่มประสิทธิภาพในการคิวรี (ตอนที่ 2)
การใช้ Constraints เพื่อเพิ่มประสิทธิภาพในการคิวรี (ตอนที่ 3-ตอนจบ)

Sunday, May 24, 2009

การกู้ข้อมูลกรณี Control File เสีย

Updated: 5/4/2009

การกู้ข้อมูลกรณีที่เกิดเหตุต่อไปนี้:
ระบบฐานข้อมูลเป็นแบบ Noarchivelog, Control files เสียหายทั้งหมด ไม่ว่าจะมีการแก้ไขโครงสร้างทางกายภาพของระบบฐานข้อมูล (เช่นเพิ่มสมาชิกของ redo log)หลังจากการแบ็คอัพครั้งสุดท้าย (ไม่ว่าจะเป็นแบบ Whole หรือ Incremental)หรือไม่ คุณจะต้องมีแบ็คอัพเก็บไว้ โดยจะต้องมีการแบ็คอัพ Control file ไว้ด้วยทุกครั้ง

วิธีการแก้ไขโดยสังเขป
1) Open database in nomount mode เพื่อให้ Database ปล่อย Control File เราจึงจะกู้ Control ที่แบ็คอัพไว้มาทับได้
2) Recover control file คือการกู้ Control File ที่ได้แบ็คอัพไว้ล่าสุด
3) Mount the database เมื่อกู้ Control File ได้แล้วเราจะให้ Database Mount กับ Control File อีกครั้ง
4) Recover the database คือขั้นตอนการกู้คืนฐานข้อมูลกลับมาตามโครงสร้างของฐานข้อมูลที่ปรากฎใน Control File ที่กู้มา
5) Open the database with resetlogs เราต้อง Open Database โดยให้ Reset Log เนื่องจากเราได้ Recover Database (โดยใช้ Redo Log) แล้ว เราไม่มีความจำเป็นจะต้องใช้ Redo Log อีกต่อไป (และไม่ควรจะใช้ด้วย) เราจึง Reset Log ทั้งหมด

ถ้าคุณมีการเปลี่ยนแปลงโครงสร้างทางกายภาพของระบบฐานข้อมูลหลังจากการ Backup ครั้งล่าสุด คุณอาจจะสูญเสียการเปลี่ยนแปลงเหล่านั้น

1) Open database in nomount mode
C:\Documents and Settings\FB00012Chalermpon>rman
Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 23 15:51:41 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target
connected to target database: orcl2 (not mounted)

2) Recover control file
RMAN> restore controlfile from autobackup;
Starting restore at 23-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
recovery area destination: D:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: ORCL2
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AR
EA\ORCL2\AUTOBACKUP\2008_05_23\O1_MF_S_655486779_43F0S6O1_.BKP
channel ORA_DISK_1: control file restore from autobackup complete
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\CONTROL02.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\CONTROL03.CTL
Finished restore at 23-MAY-08

3) Mount the database
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

4) Recover the database
RMAN> recover database;
Starting recover at 23-MAY-08
Starting implicit crosscheck backup at 23-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Finished implicit crosscheck backup at 23-MAY-08
Starting implicit crosscheck copy at 23-MAY-08
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 23-MAY-08
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files

=======================
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL2\AUTOBACKUP\2008_05
_23\O1_MF_S_655486779_43F0S6O1_.BKP

using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 7 is already on disk as file D:\ORACLE\PRODUCT\10.
2.0\ORADATA\ORCL2\REDO02.LOG
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\REDO02.LOG thread=1
sequence=7
media recovery complete, elapsed time: 00:00:03

Finished recover at 23-MAY-08

5) Open the database with resetlogs
RMAN> alter database open resetlogs;
database opened

เมื่อ Open database ได้แล้ว shutdown database แบบ immediate
แล้ว Open database อีกครั้งด้วยโหมด Mountและให้ทำการ Full Backup ทันที

บทความที่เกี่ยวเนื่องกัน
1. การกู้ข้อมูลกรณี Redo Log File ที่ไม่ได้ Active อยู่เสีย

การ Select ข้อมูลจากเท็กซ์ไฟล์

Date: 29/03/2009

เราสามารถ Select ข้อมูลจากเท็กซ์ไฟล์ข้างนอกระบบฐานข้อมูลได้

1. สมมติเรามีเท็กซ์ไฟล์ที่มีข้อมูลดังนี้
7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20

2. การ Select จากไฟล์ จะต้องเก็บไฟล์ไว้ในไดเรคทอรีที่ Oracle รู้จัก โดยในตัวอย่างนี้เราจะเก็บไฟล์ไว้ในไดเรคทอรีบนดาต้าเบสที่ชื่อว่า 'DATA_PUMP_DIR' ซึ่งมีมากับดาต้าเบสโดยดีฟอลต์ การจะเช็คดูว่ามี directory แล้วหรือยังและไดเรคทอรีดังกล่าวแม็ปกับไดเรคทอรีบน OS ใด เราสามารถใช้คำสั่ง (จะต้อง Login user ที่มีสิทธิ์ DBA)
SQL> select * from dba_directories where directory_name = 'DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------ --------------------- ----------------------------------
SYS DATA_PUMP_DIR C:\oraclexe\app\oracle\admin\XE\dpdump\
เราอาจจะสร้าง directory ใหม่เพื่อให้แม็ปกับ directory ที่เราต้องการก็ได้โดยการสร้างจะต้องมีสิทธิ์ที่เป็น DBA เช่นกัน

3. Save ไฟล์ในข้อ 1 ด้วยชื่อ "emp.dat" ไว้ในไดเรคทอรีที่แม็ปไว้กับไดเรคทอรี DATA_PUMP_DIR ใน Oracle
4. ผู้จะสร้าง External Table จะต้องมีสิทธิ์ Read,write ในไดเรคทอรีที่เก็บไฟล์ (จะต้องให้ user ที่มีสิทธิ์ DBA grant ให้)
SQL> grant read,write on directory data_pump_dir to scott;
Grant succeeded

5. จากนั้นล็อกอินด้วย user ทีต้องการสร้างตารางแล้วสร้างตารางดังข้างล่าง
SQL> create table emp_ext
(empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2)
)
organization external
(type oracle_loader
default directory data_pump_dir
access parameters
(
records delimited by newline
fields terminated by ','
)
location ('emp.dat')
)
reject limit 1000;
โดยคอลัมน์ที่กำหนดจะต้องสอดคล้องกับข้อมูลในไฟล์
- ตรง 'location' เป็นตัวกำหนดว่าชื่อไฟล์ที่จะให้ไปอ่านชื่ออะไร
- โดย Oracle จะไปอ่านไฟล์ดังกล่าวตามที่กำหนดไว้ใน default directory
- 'fields terminated by' เป็นตัวกำหนดว่าข้อมูลแต่ละคอลัมน์ถูกแยกจากกันด้วยสัญญลักษณ์อะไร
- 'reject limit' เป็นตัวกำหนดว่าถ้ามีข้อผิดพลาดในการอ่านไม่เกินจำนวนเท่าไรจึงจะแสดง error และไม่ต้องอ่านต่อ

6. หลังจากสร้างตารางเสร็จแล้ว เราสามารถ select ข้อมูลได้เหมือนตารางปกติ
SQL> select * from emp_ext;

Saturday, May 9, 2009

การแสดงผลแบบ Transpose ข้อมูลจาก Row มาเป็น Column

Update: 8/03/2009

ในบางครั้งเราอาจจะต้องการแสดงผลข้อมูลที่อยู่ในรูปแถวแนวตั้ง ให้มาแสดงผลในรูปแนวนอนแทน ดังตัวอย่างต่อไปนี้

SQL> select deptno, ename from emp order by deptno;

DEPTNO ENAME
10 CLARK
10 KING
10 MILLER
20 JONES
20 FORD
20 ADAMS
20 SCOTT
30 ALLEN
30 JAMES
30 TURNER
30 MARTIN
30 BLAKE
30 WARD

ถ้าหากคุณต้องการแสดงผลแบบให้ ENAME ที่อยู่ใน DEPTNO เดียวกันขึ้นมาอยู่บน row เดียวกัน ดังนี้
DEPTNO ENAME
10 ,CLARK,KING,MILLER
20 ,ADAMS,FORD,JONES,SCOTT
30 ,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

โดยใช้คำสั่งดังต่อไปนี้
SQL> SELECT DEPTNO, MAX(SYS_CONNECT_BY_PATH(ENAME, ',')) ENAME
FROM (SELECT DEPTNO,
ENAME,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RN
FROM EMP
GROUP BY DEPTNO, ENAME)
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1
AND PRIOR DEPTNO = DEPTNO
GROUP BY DEPTNO

โดย ROW_NUMBER() เป็น function ที่จะสร้าง pseudo column เป็นค่าอันดับคล้าย ๆ ROWNUM แต่ ROW_NUMBER จะเริ่มนับ 1 ใหม่ทุกครั้งที่ค่าของคอลัมน์ที่ต้องการ partition เปลี่ยนไป เช่นถ้าคิวรี
SQL> (SELECT DEPTNO,
ENAME,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RN
FROM EMP
GROUP BY DEPTNO, ENAME)
จะได้ว่า
DEPTNO ENAME RN
10 CLARK 1
10 KING 2
10 MILLER 3
20 ADAMS 1
20 FORD 2
20 JONES 3
20 SCOTT 4
30 ALLEN 1
30 BLAKE 2
30 JAMES 3
30 MARTIN 4
30 TURNER 5
30 WARD 6

ฟังก์ชั่น SYS_CONNECT_BY_PATH , START WITH และ CONNECT BY เป็นการเอาข้อมูลจากในฟิลด์ ENAME มาต่อกันโดยใช้คอมมา (‘,’) เป็นตัวคั่น (SYS_CONNECT_BY_PATH(COLNAME,’,’) โดยเอาค่าจากคอลัมน์นี้ในแถวปัจจุบันมาเชื่อมกับแถวก่อนหน้า (CONNECT BY PRIOR RN = RN-1 AND PRIOR DEPTNO = DEPTNO) โดยเริ่มที่ row แรก (START WITH RN = 1)
จะได้คำสั่งเป็น
SQL> SELECT DEPTNO, SYS_CONNECT_BY_PATH(ENAME, ',') ENAME
FROM (SELECT DEPTNO,
ENAME,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RN
FROM EMP
GROUP BY DEPTNO, ENAME)
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1
AND PRIOR DEPTNO = DEPTNO

DEPTNO ENAME
10 ,CLARK
10 ,CLARK,KING
10 ,CLARK,KING,MILLER
20 ,ADAMS
20 ,ADAMS,FORD
20 ,ADAMS,FORD,JONES
20 ,ADAMS,FORD,JONES,SCOTT
30 ,ALLEN
30 ,ALLEN,BLAKE
30 ,ALLEN,BLAKE,JAMES
30 ,ALLEN,BLAKE,JAMES,MARTIN
30 ,ALLEN,BLAKE,JAMES,MARTIN,TURNER
30 ,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

จะเห็นได้ว่า ENAME จะมาเชื่อมต่อ (connect) กับ row ก่อนหน้าไปเรื่อย ๆ จนหมดทุก row ในแต่ละ DEPTNO สังเกตว่า row สุดท้ายของแต่ละ DEPTNO จะมีข้อมูลครบถ้วนเนื่องจากเอาข้อมูลมารวมกันทั้งหมดตั้งแต่ row แรกถึง row สุดท้าย เราจึงต้องการเฉพาะ row สุดท้าย row เดียว เราจึงจะใช้ function MAX มาตัดเอา ENAME ที่ไม่ต้องการออกไป
SQL> SELECT DEPTNO, MAX(SYS_CONNECT_BY_PATH(ENAME, ',')) ENAME
FROM (SELECT DEPTNO,
ENAME,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RN
FROM EMP
GROUP BY DEPTNO, ENAME)
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1
AND PRIOR DEPTNO = DEPTNO
GROUP BY DEPTNO
จะได้ข้อมูลสุดท้ายดังนี้
DEPTNO ENAME
10 ,CLARK,KING,MILLER
20 ,ADAMS,FORD,JONES,SCOTT
30 ,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

การโหลดข้อมูลออกเป็น Text File

Updated: 8/03/2009

เปิด Text Editor แล้วคีย์อินคำสั่งข้างล่าง
==============================
set heading off
set linesize 1000
set pagesize 0
set echo off

spool d:\table1.dat

select
'COL1|COL2|COL3|COL4|' from dual
union all
select
trim(col1) ||'|'||
trim(col2) ||'|'||
trim(col3) ||'|'||
trim(col4) ||'|'
from table1;

spool off
==============================

คำสั่ง Spool เป็นการเขียน Output ที่ได้ไปไว้ในไฟล์ที่กำหนด ในตัวอย่างนี้เรารันคำสั่ง Select ให้แสดงข้อมูลในคอลัมน์ที่ต้องการคั่นด้วยไปป์ (|) ส่วนแรกของคำสั่งเป็นการแสดงชื่อคอลัมน์เพื่อให้ชื่อคอลัมน์ปรากฎในไฟล์ด้วย เราอาจจะรันสคริปต์ข้างบน โดยคีย์สคริปต์เข้าใน SQL*Plus หรือ Save เป็นไฟล์แล้วรันสคริปต์ก็ได้ดังต่อไปนี้

ตอนรันจะ connect เข้า SQL*Plus
C:\> SQLPLUS scott
SQL> @ชื่อไฟล์ (อาจจะต้องใส่พาธด้วย)

หรือสามารถรันบน DOS prompt โดย
C:\> echo @ชื่อไฟล์ (อาจจะต้องใส่พาธด้วย) | sqlplus scott/tiger@orcl

การตั้ง Oracle Default Home

Updated: 9/5/2007

Oracle Home เป็นที่ ๆ Oracle ใช้เก็บไฟล์ระบบต่าง ๆ ที่ระบบฐานข้อมูลต้องใช้ในการทำงาน (เช่น .EXE หรือไฟล์ที่เกี่ยวกับระบบการเชื่อมต่อเช่น TNSNAMES.ORA)บนเครื่อง ๆ หนึ่ง อาจจะลง Oracle ไว้มากกว่า 1 Home ก็ได้ (การลง Oracle Home จะต้องใช้ Oracle Installer ในการลง) ตัวที่ Install ครั้งหลังสุดจะเป็น Default Home โดยปกติ ถ้าเราต้องการให้ Oracle Home เป็นตัวอื่น เราสามารถจะเซ็ทได้ดังนี้

1. ใช้ Universal Installer (Start menu => Oracle - OraDb10g_home1 => Oracle Installation Products => Universal Installer)
2. คลิ๊ก Installed Products คลิ๊ก Environment tab แล้วคลิ๊กเลือก Home Name ที่ต้องการให้เป็น default แล้วคลิ๊ก Apply แล้วคลิ๊ก Close เพื่อกลับไปที่หน้า Welcome
3. ตรวจสอบ Oracle default home โดยลองเปิด command prompt พิมพ์ "LSNRCTL STATUS" ดูที่ Listener Parameter File ว่าอยู่ใน Oracle default home directory ที่ต้องการ

การแบ็คอัพแบบ Copy Incremental Level 0, 1 (No Archivelog Mode)

Updated: 5/4/2009

ผลที่ได้จากกระบวนการแบ็คอัพนี้ จะคล้าย ๆ กับ การทำ Daily Backup ใน Enterprise Manager (EM) โดยเป็นการแบ็คอัพเมื่อดาต้าเบสรันเป็นแบบ No Archivelog ซึ่งในวันแรกจะทำการ Copy Datafile ทั้งหมดก่อน (Level 0) พอวันที่สองจะทำการแบ็คอัพเพิ่มเฉพาะข้อมูลส่วนต่าง (Level 1) พอวันที่สามข้อมูลที่เพิ่มของวันที่สองจะถูกเอารวมเข้ากับ Datafile ในวันที่ 1 รวมทั้งจะทำการแบ็คอัพเพิ่มเฉพาะข้อมูลส่วนต่างของวันที่สาม พอวันต่อ ๆ มาก็จะทำเหมือนกับวันที่สาม

ด้วยหลักการข้างบนสามารถใช้คำสั่งในกรอบข้างล่างซึ่งจะแบ็คออัพลงบนดิสก์เท่านั้น การก๊อปปี้ขึ้น Tape อาจจะใช้ Third Party software หรืออื่น ๆ
****************************************************************************************************************
ตัวอย่างของการรัน RMAN ที่จะทำการแบ็คอัพข้อมูลที่สามารถกู้ข้อมูลกลับมาได้ ณ ช่วงเวลาต่าง ๆ ที่อยู่ในช่วง 7 วันก่อนที่จะกู้ข้อมูล

RUN {
RECOVER COPY OF DATABASE WITH TAG 'incr_update'
UNTIL TIME 'SYSDATE - 7';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update'
DATABASE;
}

คำสั่งข้างต้นจะมีผลดังนี้:
คืนแรกคำสั่ง RECOVER COPY... UNTIL TIME จะไม่มีผลอะไร และคำสั่ง BACKUP INCREMENTAL... FOR RECOVER OF COPY จะสร้างไฟล์แบ็คอัพ Level 0

พอคืนที่สองถึงคืนที่เจ็ด คำสั่ง RECOVER COPY... UNTIL TIME จะยังคงไม่มีผลเนื่องจาก SYSDATE-7 ยังไม่มีข้อมูลที่จะ Recover (เพื่ออัพเดท Datafile Copy)คำสั่ง BACKUP INCREMENTAL... FOR RECOVER OF COPY จะสร้างไฟล์แบ็คอัพ Differential Level1 ซึ่งจะแบ็คอัพเฉพาะข้อมูลส่วนต่างของแต่ละวันไว้

พอวันที่แปดเป็นต้นไป คำสั่ง RECOVER COPY... UNTIL TIME จะทำการอัพเดทไฟล์แบ็คอัพ โดยเอาไฟล์แบ็คอัพแบบ Incremental Level1 ที่ได้ทำไว้ทุกวัน (เมื่อเจ็ดวันก่อน)และเช่นเดียวกับวันก่อนหน้า คำสั่ง BACKUP INCREMENTAL... FOR RECOVER OF COPY จะสร้างไฟล์แบ็คอัพ Differential Level1 ซึ่งจะแบ็คอัพเฉพาะข้อมูลส่วนต่างของแต่ละวันไว้

ดังตัวอย่าง คุณสามารถทำการกู้ข้อมูลไป ณ จุด SCN ใด ๆ ระหว่างจุดที่ได้ทำการแบ็คอัพไว้ ร่วมกับ Redo Logs ซึ่งเนื่องจากเราทำการอัพเดทไฟล์แบ็คอัพทุกวัน เราจึงจะใช้ข้อมูลใน Redo Logs ย้อนหลังไม่เกิน 1 วัน

จากการทดสอบ
สมมติว่าเราตั้งตารางการแบ็คอัพดังนี้
อาทิตย์ backup incremental level 0 ลงดิสก์, ก๊อปปี้ขึ้น TapeWeek1
วันจันทร์ backup incremental level1 ลงดิสก์, ก๊อปปี้ขึ้น TapeDay1
วันอังคาร backup incremental level 1 ลงดิสก์, ก๊อปปี้ขึ้น TapeDay2
วันพุธ backup incremental level 1 ลงดิสก์, ก๊อปปี้ขึ้น TapeDay3
วันพฤหัส backup incremental level 1 ลงดิสก์, ก๊อปปี้ขึ้น TapeDay4
วันศุกร์ backup incremental level 1 ลงดิสก์, ก๊อปปี้ขึ้น TapeDay5
วันเสาร์ backup incremental level1 ลงดิสก์, ก๊อปปี้ขึ้น TapeDay6

-- เมื่อ backup level 0 จะได้ไฟล์ copy จาก database file จำนวน 4 ไฟล์ และ backupset ของ controlfile อีกหนึ่ง
-- เมื่อ backup level 1 cumulative จะได้ไฟล์ backupset (rman ไม่ support incremental level อื่น นอกจาก 0 กรณีเป็น backup copy ก็เลยมีแต่ backup set เท่านั้น) ของการเปลี่ยนแปลงรวมทั้ง 4 ไฟล์เป็นไฟล์เดียว และ backupset ของ controlfile อีกหนึ่ง
-- ไฟล์สุดท้ายที่เป็น incremental backup จะหายไม่ได้ เราไม่สามารถจะ recover จากไฟล์ incremental ก่อนหน้าได้ กรณีหายจริงๆ จะต้องใช้ recover database until cancel แทน recover database noredo แทน ซึ่งจะได้ข้อมูลคืนเท่ากับ level 0 (whole backup) เท่านั้น

Thursday, May 7, 2009

การแบ็คอัพแบบ Copy Incremental Level 0, 1 (Archivelog Mode)

Updated: 5/4/2009

การแบ็คอัพบนเครื่องที่ใช้งานจริง (Production Server)
1. ดาต้าเบสใช้ archivelog mode
การ ดูว่าดาต้าเบสอยู่ใน mode archivelog หรือเปล่า
ล็อคอินเข้า SQL*Plus ด้วย sys as sysdba

--***************************************************************
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Next log sequence to archive 18
Current log sequence 18
SQL>
--***************************************************************

ตรงบรรทัด Database log mode จะต้องเป็น Archive Mode
และ Automatic archival ต้องเป็น Enabled
ถ้าหากว่า Database log mode เป็น No Archive Mode ให้ทำดังนี้

--***************************************************************
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 83887484 bytes
Database Buffers 75497472 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Next log sequence to archive 18
Current log sequence 18
SQL>
--***************************************************************

2. ข้อมูลของการแบ็คอัพ รวมทั้ง archived logs จะถูกเก็บใน Flash Recovery Area
เมื่อ archive log list จะมี Archive destination เป็น USE_DB_RECOVERY_FILE_DEST

3. คอนฟิค RMAN ดังนี้

C:\> RMAN

RMAN> CONNECT TARGET SYS;

-->> กำหนดว่าจะให้คงไฟล์แบ็คอัพไว้ได้นานแค่ไหน เช่น RETENTION POLICY=1 หมายความว่าจะเก็บไฟล์แบคอัพไว้เพียงชุดเดียว ถ้ามีไฟล์แบ็คอัพใหม่ออกมาตัวเก่าก็จะถูกกำหนดให้ obsolete รอเวลาการลบออกเป็นต้น
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
-->> ถ้าใน Flash Recovery Area มีไฟล์แบ็คอัพที่มีข้อมูลแบ็คอัพชุดเดียวกัน (เช่นมี SCN ชุดเดียวกัน) อยู่แล้ว ก็จะไม่แบ็คอัพซ้ำ
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
-->> กำหนดให้แบ็คอัพ controlfile และ spfile ทุกครั้งที่แบ็คอัพ กรณีไม่ได้ใช้ catalog ควรใช้ฟีเจอร์นี้
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

--***************************************************************************
-->> อันนี้ ถ้ามีเทปก็คอนฟิค
CONFIGURE DEVICE TYPE sbt PARMS='...' PARALLELISM 1; # PARMS are vendor-specific
--***************************************************************************
-->> ตรวจดูคอนฟิคอีกครั้งโดยใช้คำสั่ง
RMAN> SHOW ALL;
RMAN> EXIT;

4. กำหนดขนาดของ Flash Recover Area (FRA) โดยให้มีขนาดอย่างน้อยที่สุดเท่ากับ
ขนาดของ datafile (.DBF) ทั้งหมดรวมกัน ยกเว้น datafile ที่เป็นของ temporary tablespace
+ ขนาดของไฟล์ incremental backup จำนวน 1 ไฟล์
+ ขนาดของไฟล์ archived logs (ถ้าเราเก็บ archived logs ใน FRA) ตามจำนวนวันที่ต้องการให้ย้อนหลังไปได้ หรือจำนวนของวันที่ห่างกันของการแบ็คอัพแต่ละครั้ง บวกด้วย 1
เช่นถ้าแบ็คอัพทุก ๆ 3 วัน ก็ต้องเตรียมพื้นที่ให้พอเก็บ archivelog เป็นจำนวน 4 วัน

การกำหนดขนาดของ flash recovery area ทำได้ดังนี้
สมมติว่าเราต้องการกำหนดขนาดเป็น 10G
ล็อคอินเข้า SQL*PLus ด้วย sys as sysdba

SQL> select name,space_limit,space_used from v$recovery_file_dest;

NAME SPACE_LIMIT SPACE_USED
-------------------------------------------------- ----------- ----------
D:\oracle\product\10.2.0/flash_recovery_area 1073741824 1017326080

SQL> alter system set db_recovery_file_dest_size = 10G;

System altered.

ขนาดที่กำหนดขึ้นนี้เป็นการบอก Oracle ว่าขนาดของ FRA มีได้สูงสุด 10G ถ้าข้อมูลทั้งหมดใน FRA เกินขนาดที่ระบุนี้ระบบจะ Hang

5. เขียนสคริปต์ที่จะใช้รันทุกวัน
RMAN> RECOVER COPY OF DATABASE WITH TAG "whole_db_copy";

RMAN> BACKUP INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG "whole_db_copy" DATABASE;

--***************************************************************************
-->> กรณีมีเทปจะแบ็คอัพเอา flash recovery area ขึ้นเทป
RMAN> BACKUP RECOVERY AREA;
-->> ลบไฟล์ที่ obsolete ออกจากเทป
RMAN> DELETE OBSOLETE DEVICE TYPE sbt;
--***************************************************************************
สมมติว่าไม่มีไฟล์แบ็คอัพชื่อ "whole_db_copy" อยู่ก่อนหน้านี้ ผลของการรันสคริปต์ข้างบนทุกวันจะเป็นดังนี้
> ในวันแรก, คำสั่ง RECOVER จะไม่มีผลอะไร, ส่วนคำสั่ง BACKUP... FOR RECOVER OF COPY... จะสร้างไฟล์แบ็คอัพ level 0 (คล้ายการ copy ไฟล์จาก original datafiles)
> วันที่สอง, คำสั่ง RECOVER ยังคงไม่ทำอะไร เพราะว่ายังไม่มีไฟล์แบ็คอัพ incremental level1 มาให้อัพเดท, ส่วนคำสั่ง BACKUP... จะสร้างไฟล์แบ็คอัพที่เป็น incremental level 1
> วันต่อมา, คำสั่ง RECOVER จะเอาไฟล์ incremental level 1 ที่ได้จากวันก่อนหน้ามาอัพเดทไฟล์แบ็คอัพ level 0 ที่ได้จากวันแรก ทำให้ไฟล์แบ็คอัพ level 0 นี้มีความทันสมัยขึ้นเทียบเท่ากับวันที่สอง, คำสั่ง BACKUP... จะทำการแบ็คอัพ incremental level 1 ของวันนี้

สมมติอีกว่าแผนการแบ็คอัพนี้ให้เริ่มต้นเมื่อวันที่ 1 กุมภาพันธ์ ข้อมูลข้างล่างนี้แสดงให้เห็นว่าเนื้อหาใน flash recovery area เปลี่ยนไปอย่างไรในแต่ละวันเมื่อใช้แผนการแบ็คอัพแบบนี้
วันที่ Sun Feb1
ผลของการรันสคริปต์ :
1) พยายามอัพเดทแบ็คอัพ level 0 ด้วยไฟล์แบ็คอัพ incremental แต่ไม่เป็นผล เพราะไม่มีแบ็คอัพ level 0
2) แบ็คอัพ level 0
ความเปลี่ยนแปลงของเนื้อหาของ flash recovery area เมื่อรันสคริปต์ :
1) เริ่มมีไฟล์แบ็คอัพ level 0 ที่มี SCN ตรงกับวันอาทิตย์ที่ 1
--***************************************************************************
ความเปลี่ยนแปลงของเนื้อหาในเทป (ถ้ามีการรันคำสั่งแบ็คอัพลงเทป)
1) ไฟล์แบ็คอัพ level 0
2) ไฟล์อื่นๆ ใน flash recovery area เช่น archived redo logs จะถูกก๊อปปี้ลงเทป ไฟล์เหล่านี้อาจจะถูกลบออกจาก flash recovery area (โดยอัตโนมัติ) เมื่อมีความต้องการพื้นที่
--***************************************************************************

วันที่ Mon Feb2
ผลของการรันสคริปต์ :
1) พยายามอัพเดทไฟล์ level 0 ด้วยไฟล์แบ็คอัพ incremental level 1 ซึ่งก็ยังคงไม่เป็นผล เพราะยังไม่มีไฟล์แบ็คอัพ incremental level 1
2) เริ่มแบ็คอัพ Incremental Level 1
ความเปลี่ยนแปลงของเนื้อหาของ flash recovery area เมื่อรันสคริปต์ :
1) ยังคงมีไฟล์เดิมคือ ไฟล์แบ็คอัพ level 0 ที่ยังคงมี SCN ของวันอาทิตย์ที่ 1เหมือนเก่า
2) มีไฟล์แบ็คอัพ incremental level 1 ซึ่งเก็บความเปลี่ยนแปลงเริ่มจากวันที่ 1 Feb จนถึงวันจันทร์ที่ 2 Feb
3) มีไฟล์ archive logs ซึ่งเริ่มตั้งแต่เมื่อวันที่ 1 จนถึง ณ ขณะปัจจุบัน
ความเปลี่ยนแปลงของเนื้อหาในเทป
--***************************************************************************
ความเปลี่ยนแปลงของเนื้อหาในเทป (ถ้ามีการรันคำสั่งแบ็คอัพลงเทป)
1) ไฟล์แบ็คอัพ level 0 และ ไฟล์แบ็คอัพ incremental level 1
2) ไฟล์อื่นๆ ใน flash recovery area เช่น archived redo logs จะถูกก๊อปปี้ลงเทป ไฟล์เหล่านี้อาจจะถูกลบออกจาก flash recovery area (โดยอัตโนมัติ) เมื่อมีความต้องการพื้นที่
3) ไฟล์ที่ obsolete จะถูกลบออกจากเทป
--***************************************************************************

วันที่ Mon Feb3
ผลของการรันสคริปต์ :
1) อัพเดทไฟล์ level 0 ด้วยไฟล์แบ็คอัพ incremental level 1 สำเร็จ แบ็คอัพ level 0 ขณะนี้มีความทันสมัยเทียบเท่ากับวันวาน (เนื่องจากเอา incremental แบ็คอัพของวันวานมาอัพเดทนั่นเอง)
2) เริ่มแบ็คอัพ Incremental Level 1
ความเปลี่ยนแปลงของเนื้อหาของ flash recovery area เมื่อรันสคริปต์ :
1) ยังคงมีไฟล์เดิมคือ ไฟล์แบ็คอัพ level 0 แต่ SCN (รวมทั้งข้อมูล) ได้อัพเดทไปจนถึงวันที่ SCN ของไฟล์ level 1 อันสุดท้าย (วันที่ 2 Feb)
2) มีไฟล์แบ็คอัพ incremental level 1 ซึ่งเก็บความเปลี่ยนแปลงที่เกิดขึ้นกับระบบฐานข้อมูล 24 ชั่วโมงก่อนหน้าไว้
3) มีไฟล์ archived logs ซึ่งเริ่มตั้งแต่เมื่อวันที่ 1 จนถึง ณ ขณะปัจจุบัน
--***************************************************************************
ความเปลี่ยนแปลงของเนื้อหาในเทป (ถ้ามีการรันคำสั่งแบ็คอัพลงเทป)
1) ไฟล์แบ็คอัพ level 0 และ ไฟล์แบ็คอัพ incremental level 1
2) ไฟล์อื่นๆ ใน flash recovery area เช่น archived redo logs จะถูกก๊อปปี้ลงเทป ไฟล์เหล่านี้อาจจะถูกลบออกจาก flash recovery area (โดยอัตโนมัติ) เมื่อมีความต้องการพื้นที่
3) ไฟล์ที่ obsolete จะถูกลบออกจากเทป
--***************************************************************************
หมายเหตุ
ไฟล์ incremental backup และ archived logs ที่หมดประโยชน์ คือไม่สามารถนำมาใช้ rolled-forward แบ็คอัพ level 0 จะถูกลบทิ้งโดยอัตโนมัติเมื่อระบบการแบ็คอัพต้องการใช้พื้นที่ใน flash recovery area

ถ้าลองเปลี่ยนตัวอย่างข้างบนเล็กน้อย โดยหากต้องการให้ flash recovery area เก็บข้อมูลที่เป็น archived logs และ incremental backups ไว้เป็นจำนวน n วัน เพื่อที่จะสามารถจะ recover ย้อนกลับไปในเวลาใด เวลาหนึ่งในช่วง n วันนี้ได้ จะต้องทำดังนี้คือ
กำหนดขนาดของ Flash Recover Area โดยให้มีขนาดอย่างน้อยที่สุดเท่ากับ
ขนาดของ datafile (.DBF) ทั้งหมดรวมกัน ยกเว้น datafile ที่เป็นของ temporary tablespace
+ ขนาดของไฟล์ incremental backup จำนวน n ไฟล์
+ ขนาดของไฟล์ archived logs ตามจำนวนวันที่ต้องการให้ย้อนหลังไปได้ หรือจำนวนของวันที่ห่างกันของการแบ็คอัพแต่ละครั้ง บวกด้วย 1
เช่นถ้าแบ็คอัพทุก ๆ 3 วัน ก็ต้องเตรียมพื้นที่ให้พอเก็บ archivelog เป็นจำนวน 4 วัน

การกำหนดขนาดของ flash recovery area ทำได้ดังนี้
ล็อคอินเข้า SQL*PLus ด้วย sys as sysdba

SQL> select name,space_limit,space_used from v$recovery_file_dest;

NAME SPACE_LIMIT SPACE_USED
-------------------------------------------------- ----------- ----------
D:\oracle\product\10.2.0/flash_recovery_area 1073741824 1017326080

SQL> alter system set db_recovery_file_dest_size = 4G;

System altered.

โดยใช้สคริปต์ข้างล่างนี้ในการ backup

RECOVER COPY OF DATABASE TAG "whole_db_copy" UNTIL TIME 'SYSDATE-3';
# Make an incremental backup of the database to the flash recovery area.
BACKUP INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG "whole_db_copy"
DATABASE;

ส่วนที่เพิ่มขึ้นมาคือ UNTIL TIME 'SYSDATE-3' ซึ่งเป็นตัวที่บอกว่า การ recover copy ให้ทำมาจนถึง 3 วันก่อนหน้าวันปัจจุบัน (ซึ่งเมื่อรวมกับ archived logs ของวันที่ SYSDATE-3 จนถึงวันปัจจุบัน จะสามารถ recover ไปที่จุดใด ๆในช่วง 3 วันดังกล่าวได้)

วันที่ Sun Feb1
ผลของการรันสคริปต์ :
1) พยายามอัพเดทแบ็คอัพ level 0 ด้วยไฟล์แบ็คอัพ incremental แต่ไม่เป็นผล เพราะไม่มีแบ็คอัพ level 0
2) สร้างแบ็คอัพ level 0
ความเปลี่ยนแปลงของเนื้อหาของ flash recovery area เมื่อรันสคริปต์ :
1) เริ่มมีไฟล์แบ็คอัพ level 0 ที่มี SCN ตรงกับวันอาทิตย์ที่ 1 Feb

วันที่ Mon Feb2
ผลของการรันสคริปต์ :
1) พยายามอัพเดทไฟล์ level 0 ด้วยไฟล์แบ็คอัพ incremental level 1 ซึ่งก็ยังคงไม่เป็นผล เพราะยังไม่มีไฟล์แบ็คอัพ incremental level 1
2) เริ่มแบ็คอัพ Incremental Level 1 ของการเปลี่ยนแปลงในวันที่ 2 Feb
ความเปลี่ยนแปลงของเนื้อหาของ flash recovery area เมื่อรันสคริปต์ :
1) ยังคงมีไฟล์เดิมคือ ไฟล์แบ็คอัพ level 0 ที่ยังคงมี SCN ของวันอาทิตย์ที่ 1 เหมือนเก่า
2) มีไฟล์แบ็คอัพ incremental level 1 ซึ่งเก็บความเปลี่ยนแปลงเริ่มจากวันที่ 1 Feb จนถึงวันจันทร์ที่ 2 Feb
3) มีไฟล์ archive logs ซึ่งเริ่มตั้งแต่เมื่อวันที่ 1 จนถึง ณ ขณะปัจจุบัน

วันที่ Tue Feb3
ผลของการรันสคริปต์ :
1) พยายามอัพเดทไฟล์ level 0 ด้วยไฟล์แบ็คอัพ incremental level 1 แต่ก็ยังไม่สำเร็จ เพราะว่ายังไม่มีไฟล์แบ็คอัพที่เก่าพอที่จะ roll forward ไปที่วันที่ SYSDATE-3
2) เริ่มแบ็คอัพ Incremental Level 1 ของการเปลี่ยนแปลงในวันที่ 3 Feb
ความเปลี่ยนแปลงของเนื้อหาของ flash recovery area เมื่อรันสคริปต์ :
1) ยังคงมีไฟล์เดิมคือ ไฟล์แบ็คอัพ level 0 ที่ยังคงมี SCN ของวันอาทิตย์ที่ 1 เหมือนเก่า
2) มีไฟล์แบ็คอัพ incremental level 1 ซึ่งเก็บความเปลี่ยนแปลงที่เกิดขึ้นกับระบบฐานข้อมูลในวันที่ 2 Feb (1 ไฟล์) และวันที่ 3 Feb (อีก 1 ไฟล์)
3) มีไฟล์ archived logs ซึ่งเริ่มตั้งแต่เมื่อวันที่ 1 จนถึง ณ ขณะปัจจุบัน

วันที่ Wed Feb4
ผลของการรันสคริปต์ :
1) พยายามอัพเดทไฟล์ level 0 ด้วยไฟล์แบ็คอัพ incremental level 1 แต่ก็ยังไม่สำเร็จ เพราะว่ายังไม่มีไฟล์แบ็คอัพที่เก่าพอที่จะ roll forward ไปที่วันที่ SYSDATE-3
2) เริ่มแบ็คอัพ Incremental Level 1 ของการเปลี่ยนแปลงในวันที่ 4 Feb
ความเปลี่ยนแปลงของเนื้อหาของ flash recovery area เมื่อรันสคริปต์ :
1) ยังคงมีไฟล์เดิมคือ ไฟล์แบ็คอัพ level 0 ที่ยังคงมี SCN ของวันอาทิตย์ที่ 1 เหมือนเก่า
2) มีไฟล์แบ็คอัพ incremental level 1 ซึ่งเก็บความเปลี่ยนแปลงที่เกิดขึ้นกับระบบฐานข้อมูลในวันที่ 2 Feb (1 ไฟล์), วันที่ 3 Feb (อีก 1 ไฟล์) และวันที่ 4 Feb (วันปัจจุบัน)
3) มีไฟล์ archived logs ซึ่งเริ่มตั้งแต่เมื่อวันที่ 1 จนถึง ณ ขณะปัจจุบัน


วันที่ Thu Feb5
ผลของการรันสคริปต์ :
1) พยายามอัพเดทไฟล์ level 0 ด้วยไฟล์แบ็คอัพ incremental level 1 สำเร็จ ด้วยไฟล์แบ็คอัพ incremental ไฟล์แรกคือของวันที่ 2 Feb
2) เริ่มแบ็คอัพ Incremental Level 1 ของการเปลี่ยนแปลงในวันที่ 5 Feb
ความเปลี่ยนแปลงของเนื้อหาของ flash recovery area เมื่อรันสคริปต์ :
1) ยังคงมีไฟล์เดิมคือ ไฟล์แบ็คอัพ level 0 แต่ SCN (รวมทั้งข้อมูล) ได้อัพเดทไปจนถึงวันที่ SCN ของไฟล์ level 1 ของวันที่ SYSDATE - 3 (คือวันที่ 2)
2) มีไฟล์แบ็คอัพ incremental level 1 ซึ่งเก็บความเปลี่ยนแปลงที่เกิดขึ้นกับระบบฐานข้อมูลตั้งแต่วันที่ 2 Feb จนถึงวันปัจจุบัน
3) มีไฟล์ archived logs ซึ่งเริ่มตั้งแต่เมื่อวันที่ 2 (archived logs ของวันที่ 1 obsolete ไปแล้ว เนื่องจากแบ็คอัพ level 0 ถูก update เป็นวันที่ 2 แล้ว) จนถึง ณ ขณะปัจจุบัน


หลังวันที่ 5
ผลของการรันสคริปต์ :
1) พยายามอัพเดทไฟล์ level 0 ด้วยไฟล์แบ็คอัพ incremental level 1 สำเร็จ ด้วยไฟล์แบ็คอัพ incremental ของวันที่ SYSDATE - 3
2) เริ่มแบ็คอัพ Incremental Level 1 ของการเปลี่ยนแปลงในวันนั้น ๆ
ความเปลี่ยนแปลงของเนื้อหาของ flash recovery area เมื่อรันสคริปต์ :
1) ยังคงมีไฟล์เดิมคือ ไฟล์แบ็คอัพ level 0 แต่ SCN (รวมทั้งข้อมูล) ได้อัพเดทไปจนถึงวันที่ SCN ของไฟล์ level 1 ของวันที่ SYSDATE - 3
2) มีไฟล์แบ็คอัพ incremental level 1 ซึ่งเก็บความเปลี่ยนแปลงที่เกิดขึ้นกับระบบฐานข้อมูลของสามวันย้อนหลัง
3) มีไฟล์ archived logs ซึ่งเริ่มตั้งแต่เมื่อวันที่ SYSDATE - 3 จนถึง ณ ขณะปัจจุบัน

Monday, May 4, 2009

การ Move ตารางไปไว้ในเทเบิลสเปซอื่น

Updated: 4/5/2009

เราสามารถย้ายเทเบิลสเปซของตารางได้ โดยสามารถที่จะ compress ขณะที่ย้ายตารางได้ด้วย ดังตัวอย่างข้างล่าง

เราสร้างเทเบิลสเปซ ชื่อ "uncompress1" เป็นแบบธรรมดา และ "compress1" เป็นแบบ compress

SQL> create tablespace uncompress1 datafile 'd:\uncompress1.dbf' size 5m;
Tablespace created

SQL> create tablespace compress1 datafile 'd:\compress1.dbf' size 5m default compress;
Tablespace created

และสร้างตาราง "testuncomp" ไว้ในเทเบิลสเปซ "uncompress1" และสร้างตาราง "testcomp" ไว้ในเทเบิลสเปซ "compress1"

SQL> create table testuncomp (id number) tablespace uncompress1;
Table created

SQL> create table testcomp (id number) tablespace compress1;
Table created

จะสังเกตว่า "testcomp" ซึ่งอยู่ในเทเบิลสเปซ "compress1" จะถูก compress โดยอัตโนมัติเมื่อสร้าง ในขณะที่ testuncomp จะไม่ถูก compress

SQL> select table_name,tablespace_name,compression from dba_tables where table_name in ('TESTCOMP','TESTUNCOMP');
TABLE_NAME TABLESPACE_NAME COMPRESSION
------------------------------ ------------------------------ -----------
TESTUNCOMP UNCOMPRESS1 DISABLED
TESTCOMP COMPRESS1 ENABLED

SQL> insert into testcomp values (100);
1 row inserted

SQL> commit;
Commit complete

SQL> select * from testcomp;
ID----------
100

หลังจากนั้นเราทำการ move ตาราง "testuncomp" (Uncompress) ไปยังเทเบิลสเปซ compress โดยใช้คำสั่ง alter table testuncomp move tablespace compress1 COMPRESS; หากเราไม่ใช้คำว่า COMPRESS ตารางจะไม่ compress แม้ว่าการ compress จะเป็นดีฟอลต์ในเทเบิลสเปซ compress1

SQL> alter table testuncomp move tablespace compress1 compress;
Table altered

ถ้าเรา offline เทเบิลสเปซเดิม จะยังคงคิวรีตารางได้ แสดงว่าข้อมูลไม่ได้อยู่ที่เทเบิลสเปซเดิมแล้ว

SQL> alter tablespace uncompress1 offline;
Tablespace altered

SQL> select * from testcomp;
ID----------
100

ถ้าเราจำเป็นต้องทำกับตารางจำนวนมากอาจจะสร้างสคริปต์ได้จากคำสั่ง

SQL> select 'alter table ' table_name ' move tablespace compress1 compress;' from user_tables;

Saturday, May 2, 2009

Oracle เก็บพาสเวิร์ดอย่างไร

Updated: 1/4/2007

ในการเก็บพาสเวิร์ดของ user ปกติ (ที่ไม่ใช่ user ที่ได้สิทธิ์ SYSDBA) Oracle ไม่ได้ใช้วิธีการเข้ารหัส-ถอดรหัส (Encrypted-Decrypted) ในการเก็บพาสเวิร์ดให้เป็นความลับเพราะการใช้วิธีนั้น ทำให้เกิดคำถามขึ้นมาว่า ถ้าอัลกอริทึมนั้น (อาจจะบวกกับคีย์ในการเข้ารหัส) คนที่รู้นำไปใช้ในทางที่ผิดเช่น ไปแฮ็คเอาพาสเวิร์ดของบุคคล (ที่ใช้ Oracle) ที่มีหน้าที่เกี่ยวกับการเงินในองค์กร หรือแฮ็คเอาพาสเวิร์ดของ database admin ของธนาคารเป็นต้น

จริงๆ แล้ว Oracle ไม่ได้เข้ารหัสพาสเวิร์ด แต่ใช้อัลกอริทึมที่เรียกว่า Hash แทนคือ ปกติถ้าคุณเข้ารหัส คุณจะได้ชุดของตัวอักษรที่ดูไม่รู้เรื่องออกมา พอจะใช้งานคุณก็ถอดรหัสออกดังนี้

การเข้ารหัส: 'MyPassword' ---- เข้ารหัส-----> 'AE045B73'
การถอดรหัส: 'AE045B73' -----ถอดรหัส -----> 'MyPassword'

แต่ลักษณะของ Hash อัลกอริทึมจะเป็นการเอาข้อมูลที่เป็น username และ password มาผ่าน Hash อัลกอริทึมที่จะเอาข้อมูลทั้งสองมา 'ยำ' (ขออนุญาตใช้คำนี้นะครับ) กันเพื่อแปลงให้ออกมาเป็นค่าๆ หนึ่ง (โดยปกติจะเป็นเลขฐานสิบหกจำนวนหลายๆหลักเช่น 32 หลักเป็นต้น) เวลาจะตรวจเช็คจะไม่มีการดึงข้อมูลที่เป็นค่าที่ 'ยำ' แล้วออกมา แต่จะเป็นการเอาค่าทั้งสองมา 'ยำ' กันอีกด้วยอัลกอริทึมเดิม แล้วไปเช็คกับค่าที่ยำกันไว้ก่อนหน้าว่าตรงกันหรือเปล่า

การเก็บรหัส: 'UserName' + 'Password' ---- hashed ----> 'A304Fe89604353fb'
การตรวจสอบรหัส: 'UserName' + 'Password' ---- hashed ----> 'A304Fe89604353fb'

ด้วยวิธีการนี้ค่าที่ใส่เข้าไปทั้งสองค่าจะเป็นคีย์ของมันเอง ไม่ต้องใช้คีย์อื่นใดอีก Hash อัลกอริทึมที่ใช้ก็เป็นอัลกอริทึมมาตรฐานที่ใช้กันทั่วๆไป เช่น MD5 หรือจะคิดขึ้นมาใหม่ก็ได้ ส่วนวิธีการที่ Oracle ใช้ในการเข้ารหัสพาสเวิร์ดของตัวเองเป็นดังนี้ครับ

function digest( p_username in varchar2, p_password in varchar2 )
return varchar2
is
begin
return ltrim( to_char( dbms_utility.get_hash_value( upper(p_username)||'/'||upper(p_password),1000000000, power(2,30) ),rpad( 'X',29,'X')||'X' ) );
end digest;

ฟังก์ชัน digest นี้จะนำรับเอาค่าพารามิเตอร์ username และพาสเวิร์ด เอามา Hash ให้เป็นค่าใดๆจากใน 1073741824 (หนึ่งพันเจ็ดสิบสามล้านเจ็ดแสนสี่หมื่นหนึ่งพันแปดร้อยยี่สิบสี่) ค่า บวกกับอีก 1000000000 (หนึ่งพันล้าน) เพื่อที่จะให้ค่าใหญ่ขึ้น แล้วแปลงให้เป็นเลขฐานสิบหก แล้วจึงนำไปเก็บในฟิลด์พาสเวร์ด คราวนี้ถ้า user ต้องการล็อกอินก็ใส่ username และพาสเวิร์ดเข้ามาแล้วผ่านฟังก์ชันเดียวกันนี้ ถ้าผลที่ได้ตรงกับค่าที่เก็บในฟิลด์พาสเวิร์ดก็ผ่าน ถ้าไม่ก็ไม่ผ่าน

- Hash อัลกอริทึมเป็นการแม็บกลุ่มของตัวอักษรใดๆ (String) ที่ไร้ค่าจำกัดและขอบเขตใดๆ (username+พาสเวิร์ด) กับค่าที่มีจำนวนจำกัด (เช่น 1073741824) ดังนั้นอาจจะเป็นไปได้ว่ามีค่าสองค่าใดๆซึ่งเป็น username+พาสเวิร์ด ที่หลังจากการ hash แล้วให้ค่าเดียวกัน แต่ก็มีความเป็นไปได้ยากโดยเฉพาะถ้าเรากำหนดให้ขนาดของเซ็ทของค่าที่เป็นผลของ hash มีขนาดใหญ่ขึ้น อย่างในกรณีของฟังก์ชัน Hash ข้างบนค่าที่จะเป็นไปได้มีถึง 1,073,741,824 ทีเดียว (ผมลองใช้ Hash อัลกอริทึม ที่เป็น MD5 จะได้ตัวเลขฐานสิบหกถึง 32 ตัว ซึ่งเซ็ทของค่าที่เป็นไปได้ก็คือ 16 ยกกำลัง 32 ซึ่งเป็นขนาดของเซ็ทที่มีขนาดใหญ่มาก)
- Hash ไม่มีอัลกอริทึมในการย้อนกลับ (ซึ่งก็ไม่จำเป็นที่จะต้องทำ) เหมือนกับการเข้ารหัส-ถอดรหัส สิ่งที่ต้องการคือเอาแค่ username และพาสเวิร์ดใส่เข้าไปเท่านั้น ดังนั้นจึงค่อนข้างปลอดภัย (เพราะมันไม่มีอัลกอริทึมในการย้อนกลับนั่นเอง)Hash อัลกอริทึมของ Oracle เองใน DBMS_UTILITY.GET_HASH_VALUE อาจจะมีการเปลี่ยนแปลงไปได้ในแต่ละเวอร์ชั่น ดังนั้นคุณอาจจะเลือกที่จะใช้อัลกอริทึมที่เป็นมาตรฐานอย่าง MD5 (ใน Oracle10g คือ dbms_crypto.HASH_MD5 ส่วน Oracle9i อาจจะต้องใช้ dbms_obfuscation_toolkit.MD5แทน) เป็นต้น

ORA_ROWSCN ล๊อคเรคคอร์ดของ Web Application ยังไง ไม่ให้เกิดปัญหา Concurrency

Updated: 1/4/2007

ถ้าคุณเคยเขียน Application ที่เป็น Web base แล้วเคยใช้คำสั่ง SELECT FOR UPDATE คุณอาจจะพบกับปัญหาที่ session ของ web หลุดไปหลังจากที่คุณ SELECT FOR UPDATE ซึ่งเป็นเรื่องที่เกิดขึ้นได้บ่อยๆ ใน Web เทคโนโลยี พอคุณ select ข้อมูลด้วยคำสั่งดังกล่าวเพื่อล๊อค ไม่ให้ใครเข้ามาแก้ไขข้อมูล row เดียวกับของคุณในขณะที่ user กำลังแก้ข้อมูลในหน้า Web ยังไม่ทันที่จะ Submit ก็มีเหตุบางอย่างทำให้ Web session นั้นหลุดไปผลก็คือข้อมูลไม่ได้ถูกอัพเดท แล้ว row นั้นก็จะล๊อคอยู่อย่างนั้นจนกว่า Oracle (หรือตัวคุณเอง) จะมา Kill session นั้นออกไป ซึ่งในระหว่างก่อนจะมีการ Kill session นี้ คนอื่นจะเข้ามาอัพเดท row เดียวกันนี้ไม่ได้เลย
Oracle10g ได้ให้ feature ที่เรียกว่า ORA_ROWSCN ซึ่งเป็น pseudo column ที่จะให้ค่าของ SCN หรือ System Change Number ซึ่งสรุปแบบง่าย ๆ ก็คือหมายเลขที่จะเปลี่ยนไปทุกครั้งที่มีการ commit ทรานแซคชั่น ลองดูจากตัวอย่างดีกว่าครับ

SQL> select ora_rowscn, empno, ename from emp where rownum <> update emp set ename = 'สมชาย' where empno = 7369;
1 row updated.

SQL> commit;
Commit complete.

ถ้าเรา select ข้อมูลขึ้นมาใหม่จะเห็นว่า ORA_ROWSCN เปลี่ยนไป

SQL> select ora_rowscn, empno, ename from emp;
ORA_ROWSCN EMPNO ENAME
---------- ---------- ----------
315968 7369 สมชาย
315951 7499 ALLEN
315951 7521 WARD
315951 7566 JONES

4 rows selected.

จากจุดนี้เองทำให้เราสามารถรู้ได้ว่ามีการเปลี่ยนแปลงข้อมูลไปหลังจากที่เราได้ select ข้อมูลไว้ก่อนหรือเปล่า ซึ่งทำให้เราสามารถเพิ่มเติมเงื่อนไขในการ update ข้อมูลเข้าไป เช่น
SQL> update emp set ename = 'สมโชค' where empno = 7369 and ORA_ROWSCN = 315968;
0 rows updated.

หากว่าก่อนที่เราจะรันคำสั่งข้างบน มี user คนอื่นได้อัพเดทเรคคอร์ดนี้ไปเป็นค่าอื่นแล้ว เราจะพบว่า row นั้นจะไม่ถูกอัพเดทเพราะว่าเลข ORA_ROWSCN ได้เปลี่ยนไปนั่นเอง จากนี้เราอาจจะเช็คว่าถ้าจำนวนของเรคคอร์ดที่ถูกอัพเดทมีค่าเป็น 0 ก็ให้ raise exception ขึ้นมา

SQL> select ora_rowscn, empno, ename from emp where empno = 7369;
ORA_ROWSCN EMPNO ENAME
---------- ---------- ----------
315980 7369 สมชาย

การจะใช้ feature นี้ เราจะต้องสร้างตารางใหม่ โดยเพิ่มคำว่า ROWDEPENDENCIES เข้าไป เนื่องจากโดยปกติ Oracle จะอัพเดท SCN ในระดับ Database Block ข้อมูลที่อยู่ใน Block เดียวกันจะถูกอัพเดท SCN พร้อม ๆ กันซึ่งเราจะไม่รู้ว่า เรคคอร์ดใดที่ถูกอัพเดทบ้าง เมื่อเราสร้างตารางให้เป็นแบบ ROWDEPENDENCIES เรคคอร์ดที่มีการเปลี่ยนแปลงในการ commit แต่ละครั้งจะมี ORA_ROWSCN เดียวกัน

วันที่กับ Oracle

Updated: 8/6/2008

มักจะมีคนถามบ่อย ๆ ว่า Oracle เก็บวันที่เป็นปีฝรั่งหรือปีไทย จริงๆ แล้วฟิลด์ DATE ของ Oracle จะเก็บข้อมูลวันที่เป็นตัวเลขค่าหนึ่งซึ่งแทนวันที่ในปฏิทินสากลแบบแบบของฝรั่งเขา ซึ่งเรียกว่าปฏิทินแบบ Gregorian ซึ่งเป็นปฏิทินแบบ default ของ Oracle การเก็บจึงไม่ใช่การเก็บเป็นตัวเลข 2551 หรือ 2008 ดังนั้นคำถามจึงไม่ได้อยู่ที่ว่ามันเก็บเป็นปีฝรั่งหรือปีไทย แต่น่าจะอยู่ที่ว่าคุณยินดีจะคีย์วันที่เป็นปีไทยหรือปีฝรั่งคุณก็คีย์ แต่บอก Oracle หน่อยว่าที่คุณคีย์นั้นเอาฟอร์แมตแบบไหน เป็นปีไทยหรือปีฝรั่ง การนำออกมาแสดงก็เช่นกัน ตอนคุณเอาออกมาแสดงก็เพียงแต่บอกว่าต้องการดูแบบปีไทยหรือปีฝรั่ง ในตัวอย่างผมเปลี่ยนประเภทของปฏิทินเป็นแบบปีพ.ศ. (THAI_BUDDHA) แล้ว insert ข้อมูลเข้าไปเป็นทั้งปีแบบ ค.ศ. และ พ.ศ. แล้วก็เปลี่ยนกลับเป็นแบบปี ค.ศ. (GREGORIAN) จะเห็นว่า เมื่อเรา insert ปีพ.ศ.เข้าไปในขณะที่เราตั้งปฏิทินเป็นปี พ.ศ. เมื่อ select ข้อมูลขึ้นมาก็จะได้ข้อมูลที่ถูกต้อง แต่ถ้าเรา insert ปีค.ศ.เข้าไปเราจะเห็นว่า กันจะกลายเป็นปี พ.ศ.2008 แทน เช่นเดียวกับเมื่อเราตั้งปฏิทินเป็นแบบ ค.ศ. แล้วคีย์ข้อมูลเป็น 2551 เราก็จะได้ปี ค.ศ.2551
ตอนท้ายผม select ข้อมูลที่เรา insert เข้าไปให้ดูในทั้งสองแบบปฏิทิน หวังว่าคงจะช่วยทำให้เข้าใจเรื่องปฏิทินใน Oracle ดีขึ้นมั่งนะครับ

SQL> create table mydate (date_col date);

Table created.

SQL> alter session set nls_calendar = 'THAI BUDDHA';

Session altered.

SQL> insert into mydate values (to_date ('15/12/2008','dd/mm/yyyy'));

1 row created.

SQL> insert into mydate values (to_date ('15/12/2551','dd/mm/yyyy'));

1 row created.

SQL> alter session set nls_calendar = 'GREGORIAN';

Session altered.

SQL> insert into mydate values (to_date ('15/12/2008','dd/mm/yyyy'));

1 row created.

SQL> insert into mydate values (to_date ('15/12/2551','dd/mm/yyyy'));

1 row created.

SQL> commit;

Commit complete.

SQL> conn scott/tiger
Connected.
SQL> select * from mydate;

DATE_COL
-------------
15 ธ.ค. 1465
15 ธ.ค. 2008
15 ธ.ค. 2008
15 ธ.ค. 2551

SQL> alter session set nls_calendar='THAI BUDDHA';

Session altered.

SQL> select * from mydate;

DATE_COL
--------------------------------------------------------
วันที่ 15 ธันวาคม พุทธศักราช 2008
วันที่ 15 ธันวาคม พุทธศักราช 2551
วันที่ 15 ธันวาคม พุทธศักราช 2551
วันที่ 15 ธันวาคม พุทธศักราช 3094

การใช้ NCHAR และการกำหนด Character Sets

Updated: 8/6/2008

เมื่อเรา create database ตัวใหม่ (หรือ instance ใหม่) ทุกครั้ง ในส่วนที่เกี่ยวกับภาษาเราจะต้องระบุพารามิเตอร์สองตัวคือ
1. Database Character Set ซึ่งเป็น Character Set ที่จะถูกใช้เป็น default Character Set
2. National Character Set เป็น Character Set สำหรับ NVARCHAR data type เท่านั้น ซึ่งมีสองแบบคือ AL16UTF16 กับ UTF8
สำหรับการกำหนดค่าของ National Character Set มีข้อคิดดังนี้
AL16UTF16 :
1. ถ้าข้อมูลเป็นภาษาเอเชีย จะประหยัดพื้นที่กว่า UTF8 ซึ่งจะดีในแง่ของพื้นที่ในฮาร์ดดิสก์ และ I/O แต่ถ้าข้อมูลส่วนใหญ่เป็นภาษาทางยุโรปใช้ UTF8 ดีกว่าเพราะประหยัดพื้นที่กว่า
2. Oracle มอง AL16UTF16 เป็น fixed-width characters ดังนั้นจะ process เร็วกว่า
3. ความกว้างสูงสุดของ NCHAR และ NVARCHAR คือ 1000 และ 2000 ตัวอักษร ซึ่งการันตี เพราะเป็น fixed-width
UTF8:
1. ถ้าข้อมูลเป็นภาษาทางยุโรป โดยปกติจะประหยัดพื้นที่กว่า AL16UTF16 (เนื่องจากภาษาทางยุโรปอยู่ในลำดับต้นๆของ Unicode ซึ่งสามารถแทนได้ด้วยข้อมูลเพียง Byte เดียว) และให้ประสิทธิภาพดีกว่าในการ Process
2. ความยาวสูงสุดของ NCHAR และ NVARCHAR คือ 2000 และ 4000 ซึ่งดูเหมือนจะมากกว่า AL16UTF16 แต่ไม่การันตี เนื่องจาก UTF8 เป็น variable width ถ้าเป็น Character ที่ใช้ Byte เดียวก็จะได้ถึง 4000 bytes แต่ถ้าเป็น Character ที่ใช้ 3 Bytes ก็จะมีได้สูงสุดแค่ 4000/3 ตัวอักษร

ตัวอย่างที่ 1
ฐานข้อมูลชื่อ test มี Database Character Set เป็น TH8TISASCII และมี National Character Set = UTF8
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< province_id =" 91;"
SQL> select dump(province_th) from tcr_province where province_id =91; -- สตูล
DUMP(PROVINCE_TH)
--------------------------------------------------------------------------------
Typ=1 Len=12: 224,184,170,224,184,149,224,184,185,224,184,165

คำว่าสตูลมีสี่ตัวอักษร (Character) แต่ละตัวอักษรประกอบด้วย 3 Byte

ในฟิลด์นี้เก็บชื่อจังหวัด โดยตัวที่เืลือกมาคือ 'สตูล' ซึ่งประกอบด้วยตัวอัีกษรสี่ตัว จะเห็นได้ว่ามีรหัสอยู่ = 4 x 3 = 12 ตัว

ตัวอย่างที่ 2
ฐานข้อมูลอีกอันหนึ่งที่มี Database Character Set เป็น TH8TISASCII และมี National Character Set เป็น AL16UTF16
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
SQL> select name,name2 from testn;

NAME NAME2
--------------------------
สตูล สตูล

SQL> select dump(name) as db_char_set , dump(name2) as nls_char_set from testn;

DB_CHAR_SET NLS_CHAR_SET
----------------------------------------------------------------
Typ=1 Len=4: 202,181,217,197 Typ=1 Len=8: 14,42,14,21,14,57,14,37

SQL> select column_name,data_type, data_length from user_tab_columns where table_name = 'TESTN';

COLUMN_NAME DATA_TYPE DATA_LENGTH
------------------------------------------------------
NAME VARCHAR2 300
NAME2 NVARCHAR2 600

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
จะเห็นว่ากรณีของภาษาไทยการใช้ AL16UTF16 กับ NVARCHAR คอลัมน์ จะประหยัดพื้นที่ (และมีประสิทธิภาพในการ process ดีกว่า แต่ไม่ได้แสดงให้ดูในที่นี้) กว่าการใช้ UTF8
อย่างกรณีของคำว่า 'สตูล' ซึ่งถ้าเราใช้ Character Set 'TH8TISASCII' (คอลัมน์ 'NAME' ในตาราง TESTN) จะกินพื้นที่ 4 Bytes(1 Byte ต่อ 1 ตัวอักษร) ในขณะที่ Character Set AL16UTF8 (คอลัมน์ 'NAME2' ในตาราง TESTN) จะกินพี้นที่ 8 Bytes (2 Bytes ต่อ 1 ตัวอัีกษร) และ Character Set UTF8 (คอลัมน์ province_th ในตาราง tcr_province) จะกินพื้นที่ 12 Bytes (3 Bytes ต่อ 1 ตัวอักษร)
อีกประการหนึ่งคือ เมื่อเรา Define จำนวนตอนสร้างตาราง เช่น NAME2 เป็น NVARCHAR2(300) นั้น จำนวน 300 คือจำนวนตัวอักษร ไม่ใช่จำนวน Byte เหมือนกับเมื่อเรากำหนดใน VARCHAR
ดังนั้นเมื่อเรา Query จากใน data dictionary (ตาราง user_tab_columns) เราจะพบว่าบนคอลัมน์ NVARCHAR ที่มี Character Set เป็น AL16UTF16 จะมี data_length เป็นสองเท่าของจำนวนที่ระบุตอนสร้างตาราง (AL16UTF16 ใช้ 2 Bytes แทน 1 ตัวอักษร) ในขณะที่ NVARCHAR ทีมี Character Set เป็น UTF8 จะีมี data_length เป็นสามเท่า
(UTF8 ใช้ 1-4 Bytes สำหรับตัวอักษร (และ 6 Bytes สำหรับ Supplementary Characters) ในกรณีนี้ใช้ 3 Bytes ต่อตัวอักษร)

บทความที่เกี่ยวเนื่องกัน
1. NLS_LANG คือตัวแปร Environment บนฝั่ง Client ไม่ใช่บน Database Server

spfile ต่างจาก pfile อย่างไร

Updated: 13/4/2009

บนวินโดวส์ ระลึกเสมอว่าให้ใช้ pfile จาก $ORACLE_BASE/admin เท่านั้น
และ spfile ตัวที่เป็น default จะอยู่ที่ $ORACLE_HOME/database บนวินโดวส์หรือ dbs บน unix เสมอ

ถ้า STARTUP ด้วย pfile (ที่ $ORACLE_BASE/admin) แล้ว ALTER SYSTEM แต่ต้องการให้ SCOPE ไปที่ spfileจะไม่ได้ แม้จะใช้ create spfile from pfile เพราะยังเป็นการ create จาก pfile ตัวเก่าที่ยังไม่ได้ alter

ถ้า spfile เสียเนื่องมาจากการ update parameter บางตัว หรือไป update spfile ด้วย text editor เข้า จะทำให้ startup ไม่ได้ให้ STARTUP ด้วย pfile ก่อนแล้วจึง create spfile from pfile= 'pfile ตัวที่ต้องการ' (อย่าใช้ create spfile from pfile เฉยๆ เพราะจะทำให้ oracle ไปเอาตัว pfile ที่เป็น default ซึ่งจะเป็นแค่ตัวที่ชี้ไปยัง spfile ตัวที่มีปัญหาเท่านั้น) เพื่อสร้าง spfile ตัวใหม่ (ควรจะแบ็คอัพ spfile ตัวเก่าไว้ก่อน เพื่อเอาไว้เป็น ref ในการแก้ parameter ตัวอื่น ๆ) แล้วจึง startup ใหม่ คราวนี้ระบบฯ จะใช้ spfile แล้ว ก็สามารถแก้ไขพารามิเตอร์ได้โดยการ ALTER SYSTEM SCOPE=BOTH

เราสามารถใช้ create spfile from pfile ได้แม้ database จะยังไม่ได้ open

หรือนอกเหนือจากวิธีข้างบนเราสามารถจะใช้วิธีการ restore spfile จากแบ็คอัพก็ได้ เคยใช้มาแล้ว

เปิดปิดระบบฐานข้อมุลด้วยสคริปต์

Updated: 8/03/2009

เราสามารถเปิดปิดระบบฐานข้อมูล โดยเขียนเป็นสคริปต์ได้ดังข้างล่าง
สร้างเท็กซ์ไฟล์โดยมีสคริปต์ข้างล่างนี้ แล้วบันทึกเป็นนามสกุล .BAT เพื่อใช้เปิดฐานข้อมูล
set oracle_sid=orcl
oradim -startup -sid orcl
echo startup sqlplus / as sysdba
lsnrctl start

*โดยเปลี่ยน orcl ด้วยชื่อ sid ของคุณ
* oradim เป็นการเปิด Service ของ Oracle บน Windows

ปิดฐานข้อมูล
set oracle_sid=orcl
oradim -shutdown -shuttype srvc -shutmode immediate -sid orcl
rem lsnrctl stop