การตรวจเช็คบนฐานข้อมูล(oracle) ตอนที่ 1

หลังจากที่ได้อ่านบทความบน Oracle Support (ID 122669.1) เห็นว่ามีประโยชน์อย่างมาก จึงได้นำมาเสนอ

บทความนี้อธิบายถึงวิธีการและพื้นฐานที่ควรต้องรู้บนฐานข้อมูล ซึ่งจะช่วยในการตรวจสอบ และหาปัญหาที่อาจจะเกิดขึ้นในอนาคตได้

1. Parameter file - parameter ไฟล์ มี 2 แบบ คือ text ไฟล์ (pfile) และ binary ไฟล์ (spfile), pfile สามารถแก้ไขได้ด้วย Editor บน OS นั้นๆ ส่วน spfile ต้องจัดการผ่าน Instance ของมันเอง

ตั้งแต่ Oracle 8i เราใช้ text ไฟล์ที่เรียกว่า pfile (parameter file) ในการเก็บ database initialization parameters ซึ่ง pfile นี้จะถูกอ่านเมื่อ Instance เริ่มต้น(startup) การเปลี่ยนค่าอะไรก็ตามใน pfile จะมีผลก็ต่อเมื่อฐานข้อมูล มีการเริ่มต้นใหม่(restart) แต่ parameter(dynamic)ก็ยังสามารถเปลี่ยนแปลงได้ด้วย ALTER SYSTEM หรือ ALTER SESSION ซึ่งจะมีผลทันที

จากนั้น Oracle 9i มีฟีเจอร์ที่เรียกว่า spfile (server parameter file) ซึ่งเป็น binary ไฟล์ และเก็บข้อมุล เหมือนกับ pfile การเปลี่ยนแปลงใน spfile ต้องทำผ่่าน Instance โดยการใช้ ALTER SYSTEM

SPFILE อนุญาติให้มีการเปลี่ยนแปลงแบบ (dynamic) โดยไม่ต้องหยุด Instance และ spfile ยังใช้ในแชร์บน RAC, ในการเริ่มต้น Instance นั้นถ้าเราไม่ระบุ pfile - Oracle จะใช้ spfile ในการเริ่มต้น Instance

การเซตค่า Server Parameter File:
ใช้ SID เพื่อกำหนดค่าเฉพาะแต่ละ Instance (เห็นได้ชัดบน RAC), ใช้ '*' สำหรับทุกๆ Instance ในฐานข้อมูล
เช่น

db2.thread=2
db1.thread=1
*.undo_management='AUTO'
*.undo_retention=3600
db2.undo_tablespace='UNDOTBS2'
db1.undo_tablespace='UNDOTBS1'

กับ Parameter ที่เป็น dynamic เราสามารถใช้ ALTER SESSION หรือ ALTER SYSTEM เปลี่ยนแปลงขณะที่ Instance ยังทำงานอยู่

ALTER SESSION SET parameter_name = value
ALTER SYSTEM SET parameter_name = value [DEFERRED]

สำหรับ ALTER SYSTEM เราสามารถกำหนด SCOPE เพื่อระบุขอบเขตในการเปลี่ยนแปลง

SCOPE = SPFILE - เหมาะกับ parameter ที่เป็น dynamic และ static เมื่อเปลี่ยนแปลงใน spfile ไฟล์ จะมีผลหลังจากเริ่มต้นใหม่ครั้งต่อไป (restart)
SCOPE = MEMORY - เหมาะกับ parameter ที่เป็น dynamic การเปลี่ยนแปลงจะเปลี่ยนเพียงใน หน่วยความจำเท่านั้น
SCOPE = BOTH - เหมาะกับ parameter ที่เป็น dynamic จะเปลี่ยนทั้งใน spfile ไฟล์ และ หน่วยความจำ
หากไม่ระบุ SCOPE = BOTH (default)
เช่น

SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 300

SQL> alter system set open_cursors=100 scope=MEMORY;
System altered.

SQL> show parameter open_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 100

SQL> select sid, name, value from v$spparameter where name='open_cursors';
SID NAME VALUE
----- -------------------- ----------
* open_cursors 300

SQL> alter system set open_cursors=200 scope=BOTH;
System altered.

SQL> show parameter open_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 200

SQL> select sid, name, value from v$spparameter where name='open_cursors';
SID NAME VALUE
----- -------------------- ----------
* open_cursors 200

SQL> alter system set open_cursors=300 scope=SPFILE;
System altered.

SQL> show parameter open_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 200

SQL> select sid, name, value from v$spparameter where name='open_cursors';
SID NAME VALUE
----- -------------------- ----------
* open_cursors 300

SQL> alter system set open_cursors=300 sid='TEST' scope=SPFILE;
System altered.

SQL> select sid, name, value from v$spparameter where name='open_cursors';
SID NAME VALUE
----- -------------------- ----------
* open_cursors 300
TEST open_cursors 300

SQL> alter system reset open_cursors sid='TEST' scope=SPFILE;
System altered.

SQL> select sid, name, value from v$spparameter where name='open_cursors';
SID NAME VALUE
----- -------------------- ----------
* open_cursors 300

จากตัวอย่างข้างบนยังแสดงให้เห็นว่าเรายังสามารถใช้ SID='...' เป็นอะไรก็ได้ และการใช้ ALTER SYSTEM RESET

การตรวจสอบว่าใช้ spfile หรือไม่ (อ้างอิง)

SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/11gR1/dbs/spfileorcl.ora

SQL> select isspecified, count(*) from v$spparameter group by isspecified;
ISSPEC COUNT(*)
------ ----------
TRUE 23
FALSE 267

หากต้องการนำ SPFILE ออกมาเป็น text ไฟล์ - ทำได้โดย
CREATE PFILE FROM SPFILE
เช่น

SQL> create pfile='/tmp/pfile' from spfile;
File created.

ในมุมกลับกัน หากต้องการเปลี่ยนจาก pfile เป็น spfile
CREATE SPFILE FROM PFILE
เช่น

SQL> create spfile from pfile;

หรือ

SQL> create spfile from pfile='/tmp/pfile';

จากนั้น stop/start ฐานข้อมูล

SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string

SQL> create spfile from pfile;
File created.

SQL> shutdown immediate;

SQL> startup

SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/11gR1/dbs/spfileorcl.ora