การตรวจเช็คฐานข้อมูล Oracle (v. 1) 


        หลายครั้งที่ผู้เริ่มต้นใช้งานฐานข้อมูล Oracle และต้องรับหน้าที่เป็นผู้ดูแลฐานข้อมูล อาจตั้งคำถามให้กับตัวเองว่าอะไรบ้างที่เราต้องรู้ในการตรวจเช็ค ตรวจสอบฐานข้อมูล เราสามารถอ่าน ค้นคว้าสิ่งต่างๆ เกี่ยวกับฐานข้อมูล Oracle ได้บน http://otn.oracle.com http://tahiti.oracle.com http://forums.oracle.com http://support.oracle.com หรือหาสิ่งที่อยากรู้ ได้บน google ด้วยตัวเอง

        บทความนี้ได้ใช้ไอเดียจาก My Oracle Support พร้อมกันนี้ได้ดัดแปลง ทดสอบ เพื่อนำมาเสนอ และหวังว่าจะมีประโชยน์ต่อผู้อ่าน

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 (startup)

การเซตค่า 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


หมายเหตุ เราจะรู้ได้อย่างไรว่า Initialization parameters ตัวไหนที่เราสามารถเปลี่ยนแปลง (ALTER SYSTEM SET) และมีผลในทันที

SQL> select name, value from v$parameter where ISSYS_MODIFIABLE='IMMEDIATE';


2. Control files - Control file เก็บข้อมูล - ชื่อฐานข้อมูล, เวลาที่ฐานข้อมูลถูกสร้าง, ชื่อและที่เก็บของ datafile/redo log ไฟล์ และอื่นๆ ที่จำเป็นในฐานข้อมูล, และแนะนำควรมีอย่างน้อย 2 ไฟล์ เก็บบน physical disk ที่ต่างกัน พร้อมกับ backup

SQL> select status, name from v$controlfile;

STATUS NAME
------- -----------------------------------------------------------------
/oracle/oradata/ORCL/controlfile/o1_mf_5twzvtgn_.ctl
/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_5twzvtoh_.ctl

Initialization parameter ที่เกี่ยวข้อง
CONTROL_FILES - ชื่อของ controlfiles (ชื่อไฟล์)
CONTROL_FILE_RECORD_KEEP_TIME- จำนวนวันที่เก็บข้อมูลใน controlfile ก่อนจะถูกเขียนทับ

Control file มีการเก็บข้อมูล 2 ประเภท
- ข้อมูลที่จำเป็นสำหรับฐานข้อมูล และจะไม่ถูกเขียนทับ เช่น datafile, online redo log
- ข้อมูลเกี่ยวกับ RMAN ที่ backup และใช้ในการ recovery ซึ่งจะถูกเขียนทับได้

CONTROLFILE AUTOBACKUP

RMAN> show all;
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored


ทุกๆ ครั้งหลัง backup หรือเปลี่ยนแปลงเกี่ยวกับ Tablespace, เพิ่ม redo log - oracle จะ backup control file โดยอัตโนมัติ (
อ้าง อิง)
เช่น

RMAN> backup datafile 1;

Starting backup at 19-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=141 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/oradata/ORCL/datafile/o1_mf_system_5twzp126_.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-10

channel ORA_DISK_1: finished piece 1 at 19-APR-10
piece handle=/oracle/flash_recovery_area/ORCL/backupset/2010_04_19/o1_mf_nnndf_TAG20100419T231236_5ws07tyl_.bkp tag=TAG20100419T231236 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:38
Finished backup at 19-APR-10

Starting Control File and SPFILE Autobackup at 19-APR-10 piece handle=/oracle/flash_recovery_area/ORCL/autobackup/2010_04_19/o1_mf_s_716771721_5ws0f47f_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 19-APR-10

การ backup Control file:
- backup เป็น binary file
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';

- สร้าง SQL statement เพื่อใช้ recreate control file
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/oracle/backup/control.sql';

จะเห็นว่า Control file เป็นสิ่งที่สำคัญมาก จึงจำเป็นที่จะต้อง ตรวจสอบและ BACKUP(เสมอ)

ตัวอย่าง Multiplex controlfiles เมื่อใช้งาน ASM DiskGroup

SQL> show parameter control_files


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                        string      +DATA/orcl/controlfile/current.277.741286407

                                                 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

RMAN> startup nomount;

Oracle instance started

Total System Global Area     626327552 bytes

Fixed Size                     2229080 bytes

Variable Size                390073512 bytes

Database Buffers             226492416 bytes

Redo Buffers                   7532544 bytes

RMAN> restore controlfile to '+DATA' from '+DATA/orcl/controlfile/current.277.741286407';

Starting restore at 25-APR-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=10 instance=orcl_2 device type=DISK

channel ORA_DISK_1: copied control file copy

Finished restore at 25-APR-11

ASMCMD> ls +DATA/orcl/CONTROLFILE/

Current.277.741286407

current.285.749426511

SQL> alter system set control_files='+DATA/orcl/controlfile/current.277.741286407','+DATA/orcl/controlfile/current.285.749426511' scope=spfile;

System altered.

SQL> startup force;

ORACLE instance started.

Total System Global Area  626327552 bytes

Fixed Size                  2229080 bytes

Variable Size             390073512 bytes

Database Buffers          226492416 bytes

Redo Buffers                7532544 bytes

Database mounted.

Database opened.


SQL>
show parameter  control_files


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
control_files                        string      
+DATA/orcl/controlfile/current.277.741286407, +DATA/orcl/controlfile/current.285.749426511

                                                 

   

3. Redo log files - จะเก็บข้อมูลที่มีการเปลี่ยนแปลงในฐานข้อมูล และ Oracle ใช้ online redo log files เพื่อช่วยให้เกิดการสูญหายของข้อมูลน้อยที่สุด, redolog files จะถูกใช้ เช่นกรณี instance มีปัญหา และต้องการที่จะกู้ข้อมูลที่ commit ไปแล้ว ซึ่งยังไม่ได้ถูกเขียนลง data files - redo log ควรต้องทำ multiplex และอยู่บน physical disk ที่ต่างกัน เพื่อป้องกัน disk crash หรือ ผู้ใช้ลบ และ ต้องการ 2 redo log groups อย่างน้อย และ 3 redo log groups เมื่อใช้ ARCHIVE LOG mode

การจัดการกับ Online Redo Log Groups และ Members
 (อ่านเพิ่มเติม Oracle support: 102995.1)
ตัวอย่าง: ลบ/เพิ่ม Online Redo Log Groups


SQL> select group#, sequence#, bytes, members, status from v$log;

GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 22 52428800 2 CURRENT
2 21 52428800 2 INACTIVE
3 20 52428800 2 INACTIVE


SQL> select group#, status, type, member from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------------------
3 ONLINE /oracle/oradata/ORCL/onlinelog/o1_mf_3_5wb8p2vh_.log
3 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5wb8p33t_.log
2 ONLINE /oracle/oradata/ORCL/onlinelog/o1_mf_2_5wb6kn6p_.log
2 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_5wb6koh5_.log
1 ONLINE /oracle/oradata/ORCL/onlinelog/o1_mf_1_5wb6khy8_.log
1 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_5wb6kkxd_.log

SQL>
ALTER DATABASE DROP LOGFILE GROUP 3;

Database altered.

SQL> select group#, sequence#, bytes, members, status from v$log;

GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 22 52428800 2 CURRENT
2 21 52428800 2 INACTIVE

SQL> select group#, status, type, member from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------------------
2 ONLINE /oracle/oradata/ORCL/onlinelog/o1_mf_2_5wb6kn6p_.log
2 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_5wb6koh5_.log
1 ONLINE /oracle/oradata/ORCL/onlinelog/o1_mf_1_5wb6khy8_.log
1 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_5wb6kkxd_.log

SQL>
ALTER DATABASE ADD LOGFILE GROUP 3;

Database altered.

SQL> select group#, sequence#, bytes, members, status from v$log;

GROUP# SEQUENCE# BYTES MEMBERS STATUS

---------- ---------- ---------- ---------- ----------------
1 22 52428800 2 CURRENT
2 21 52428800 2 INACTIVE
3 0 104857600 2 UNUSED

ตัวอย่าง: ลบ/เพิ่ม Online Redo Log Members


SQL> select group#, status, type, member from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------------------
3 ONLINE /oracle/oradata/ORCL/onlinelog/o1_mf_3_5x5m8zfh_.log
3 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5x5m8zrk_.log
2 ONLINE /oracle/oradata/ORCL/onlinelog/o1_mf_2_5wb6kn6p_.log
2 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_5wb6koh5_.log
1 ONLINE /oracle/oradata/ORCL/onlinelog/o1_mf_1_5wb6khy8_.log
1 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_5wb6kkxd_.log

SQL>
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/ORCL/onlinelog/o1_mf_3_5x5m8zfh_.log';

Database altered.

SQL> select group#, status, type, member from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------------------
3 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5x5m8zrk_.log
2 ONLINE /oracle/oradata/ORCL/onlinelog/o1_mf_2_5wb6kn6p_.log
2 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_5wb6koh5_.log
1 ONLINE /oracle/oradata/ORCL/onlinelog/o1_mf_1_5wb6khy8_.log
1 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_5wb6kkxd_.log

SQL>
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/ORCL/onlinelog/redo3b.log' to group 3;

Database altered.

SQL> select group#, status, type, member from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------------------
3 INVALID ONLINE /oracle/oradata/ORCL/onlinelog/redo3b.log
3 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5x5m8zrk_.log
2 ONLINE /oracle/oradata/ORCL/onlinelog/o1_mf_2_5wb6kn6p_.log
2 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_5wb6koh5_.log
1 ONLINE /oracle/oradata/ORCL/onlinelog/o1_mf_1_5wb6khy8_.log
1 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_5wb6kkxd_.log

ตัวอย่าง ที่เห็นจะเป็นแค่การจัดการกับ redolog file และหวังว่าจะเป็นประโยชน์ ช่วยในการตรวจเช็คฐานข้อมูล - อย่างไรก็ตาม เราต้องเรียนรู้การแก้ปัญหาในกรณีที่ redolog file มีปัญหาด้วย (และตรวจเช็ค backup เสมอ)


ตัวอย่าง กรณี member ของ redo group โดยลบ

SQL> select group#, status, type, member from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------------------
3 ONLINE /oracle/oradata/ORCL/onlinelog/redo3b.log
3 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5x5m8zrk_.log
2 ONLINE /oracle/oradata/ORCL/onlinelog/o1_mf_2_5wb6kn6p_.log
2 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_5wb6koh5_.log
1 ONLINE /oracle/oradata/ORCL/onlinelog/o1_mf_1_5wb6khy8_.log
1 ONLINE /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_5wb6kkxd_.log

SQL> !rm /oracle/oradata/ORCL/onlinelog/redo3b.log /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5x5m8zrk_.log

SQL> alter system switch logfile;


บน alert log file:

Sat Apr 24 18:31:47 2010
Errors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_arc2_2497.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oracle/oradata/ORCL/onlinelog/redo3b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5x5m8zrk_.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_arc2_2497.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oracle/oradata/ORCL/onlinelog/redo3b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5x5m8zrk_.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Sat Apr 24 18:31:48 2010
Errors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_3908.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oracle/oradata/ORCL/onlinelog/redo3b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5x5m8zrk_.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ตรวจสอบ redolog group 3 และแก้ปัญหา

SQL> select group#, sequence#, bytes, members, status from v$log;

GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 28 52428800 2 CURRENT
2 27 52428800 2 INACTIVE
3 26 104857600 2
INACTIVE

SQL>
alter database clear unarchived logfile group 3;

Database altered.


บน alert log file:

Sat Apr 24 18:32:45 2010
alter database clear unarchived logfile group 3
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
BEFORE 04/24/2010 18:28:58 (CHANGE 1512349) CANNOT BE USED FOR RECOVERY.
Clearing online log 3 of thread 1 sequence number 26
Errors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3904.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oracle/oradata/ORCL/onlinelog/redo3b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5x5m8zrk_.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Deleted Oracle managed file /oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5x5m8zrk_.log
Sat Apr 24 18:32:49 2010
Archiver process freed from errors. No longer stopped
Sat Apr 24 18:32:56 2010
Completed: alter database clear unarchived logfile group 3

จะเห็นว่า logfile group 3 ใช้งานได้ปกติ - ส่วนการแก้ปัญหากับ redolog file ก็ขึ้นกับแต่ละกรณีไป

4. Archiving  - เป็นกลไกที่จำเป็นในการสำรองข้อมูลที่เปลี่ยนแปลงในฐานข้อมูล
Archive files เก็บข้อมูลที่จำเป็นในการกู้ฐานข้อมูล ดังนั้นจึงมักแนะนำให้ใช้ฐานข้อมูลที่เป็น
Archive Mode

ตัวอย่าง: เปลี่ยน No Archive Mode -> Archive Mode

SQL> archive log list;
Database log mode
No Archive Mode
Automatic archival
Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 31
Current log sequence 33

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1303132 bytes
Variable Size 536874404 bytes
Database Buffers 293601280 bytes
Redo Buffers 5197824 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 31
Next log sequence to archive 33
Current log sequence 33

SQL> select NAME, CREATED, LOG_MODE, CHECKPOINT_CHANGE#,ARCHIVE_CHANGE# from V$DATABASE;

NAME CREATED LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
--------- --------- ------------ ------------------ ---------------
ORCL 28-MAR-10
ARCHIVELOG 1663822 1619901


*** กรณี ของ RAC เราต้อง shutdown database (instance all nodes) -> startup mount (only one node) -> alter database archivelog -> alter database open -> startup instance other nodes.

ก่อน 10g หากฐานข้อมูลเป็น archive mode เราควรต้องเปิด automatic archiver process โดยการเซต log_archive_start=TRUE แต่จาก 10g "log_archive_start" ไม่จำเป็นต่อไป และอาจพบ Error เมื่อ startup ฐานข้อมูล

ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated

การพิจารณา พื้นที่ disk สำหรับ Archive Files [
ID 122555.1] - ขอเสนอ script พร้อมตัวอย่าง

column ord noprint
column date_ heading 'Date' format A15
column no heading '#Arch files' format 9999999
column no_size heading 'Size Mb' format 9999999
compute avg of no on report
compute avg of no_size on report
break on report
select MAX(first_time) ord, to_char(first_time,'DD-MON-YYYY') date_,
count(recid) no, count(recid) * <REDO_SIZE_FILE_MB> no_size
from v$log_history
group by to_char(first_time,'DD-MON-YYYY')
order by ord
/
clear breaks
clear computes
clear columns


ตัวอย่าง:

SQL> select distinct (bytes)/1024/1024 from v$log;

(BYTES)/1024/1024
-----------------
50

SQL> column ord noprint
SQL> column date_ heading 'Date' format A15
SQL> column no heading '#Arch files' format 9999999
SQL> column no_size heading 'Size Mb' format 9999999
SQL> compute avg of no on report
SQL> compute avg of no_size on report
SQL> break on report

SQL> select MAX(first_time) ord, to_char(first_time,'DD-MON-YYYY') date_,
count(recid) no, count(recid) *
50 no_size
from v$log_history
group by to_char(first_time,'DD-MON-YYYY')
order by ord
/

SQL> clear breaks
SQL> clear computes
SQL> clear columns

Date #Arch files Size Mb
--------------- ----------- --------
19-APR-2010 43 2150
20-APR-2010 96 4800
21-APR-2010 96 4800
22-APR-2010 97 4850
23-APR-2010 96 4800
24-APR-2010 96 4800
25-APR-2010 97 4850
26-APR-2010 96 4800
27-APR-2010 97 4850
28-APR-2010 96 4800
29-APR-2010 96 4800
30-APR-2010 96 4800
01-MAY-2010 66 3300
----------- --------
90 4492


จะพบว่า เฉลี่ยวันละ 4 - 5 Gb ถ้าต้องการเก็บ Archive Files 3 วัน จำเป็นต้องมีพื้นที่ ถึง 5 * 3 = 15 Gb

ตัวอย่าง อื่นๆ Archive Mode -> No Archive Mode

SQL> startup mount
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1303132 bytes
Variable Size 536874404 bytes
Database Buffers 293601280 bytes
Redo Buffers 5197824 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 31
Current log sequence 33

Archiving จึงเป็นสิ่งจำเป็นในการสำรองข้อมูล หรือแม้แต่เพื่อการแก้ปัญหา บนฐานข้อมูล เช่น ปัญหา block corruption
Oracle พารามิเตอร์ที่เกี่ยวกับ Archiving (เพิ่มเติมที่
Oracle Documents):
-
LOG_ARCHIVE_DEST_n
-
LOG_ARCHIVE_DEST_STATE_n
-
LOG_ARCHIVE_FORMAT


หมายเหตุ เมื่อเราใช้ Database Archive Mode และ Archive destination เป็นUSE_DB_RECOVERY_FILE_DESTแปลว่าเรากำลังเก็บ archive files ที่ Flashback Area (db_recovery_file_dest) ดังนั้นต้องแน่ใจว่าเรากำหนดค่าพารามิเตอร์ db_recovery_file_dest_size มากพอ และตรวจเช็ค V$FLASH_RECOVERY_AREA_USAGE เพราะถ้ามีการใช้พื้นที่มากเกินค่า db_recovery_file_dest_size อาจทำให้ฐานข้อมูลไม่สามารถเขียน archive file ได้ ส่งผลให้ฐานข้อมูลมีปัญหาได้


5. Data files - เป็น physical file บนดิสต์ ที่สร้างโดย Oracle และเก็บโครงสร้างข้อมูล อาทิเช่น tables และ indexes แต่ละ datafile สามารถอยู่บนฐานข้อมูลเดียวเท่านั้น

*** อย่างไรก็ตาม read only tablespace สามารถ แชร์ datafiles ระหว่างฐานข้อมูลได้ (reference)



5.1 Autoextend
autoextend เป็นทางเลือก ที่ช่วยเราสามารถปิดหรือเปิดการใช้งาน ในการขยาย datafile โดยอัตโนมัติ แต่ถ้าเราไม่สามารถจัดสรรพื้นที่ให้กับ datafile ได้ เรายังสามารถเพิ่ม datafile

SQL> select FILE_NAME, AUTOEXTENSIBLE, INCREMENT_BY from dba_data_files where tablespace_name='USERS';

FILE_NAME AUT INCREMENT_BY
------------------------------------------------------------ --- ------------
/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf
YES 160

SQL> alter database datafile '/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf' autoextend off;

Database altered.

SQL> select FILE_NAME, AUTOEXTENSIBLE, INCREMENT_BY from dba_data_files where tablespace_name='USERS';

FILE_NAME AUT INCREMENT_BY
------------------------------------------------------------ --- ------------
/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf
NO 0

SQL> alter database datafile '/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf' autoextend on next 10M;

Database altered.

SQL> select FILE_NAME, AUTOEXTENSIBLE, INCREMENT_BY from dba_data_files where tablespace_name='USERS';

FILE_NAME AUT INCREMENT_BY
------------------------------------------------------------ --- ------------
/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf
YES 1280

มาตรฐาน Oracle datafile สามารถมีได้มากสุด 4194303 datablocks ดังนั้น
DB_BLOCK_SIZE = 2048 จะมี datafile ขนาด 8191 M
DB_BLOCK_SIZE = 4096 จะมี datafile ขนาด 16383 M
DB_BLOCK_SIZE = 8192 จะมี datafile ขนาด 32767 M
DB_BLOCK_SIZE = 16384 จะมี datafile ขนาด 65535 M

ตั้งแต่ Oracle 10g มีฟังก์ชันการทำงานใหม่ที่เรียกว่า BIGFILE ที่ช่วยให้สามารถสร้าง datafile ที่มีขนาดใหญ่กว่าเดิม แต่ใน 1 tablespace มีได้แค่ 1 datafile เท่านั้น

SELECT TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES;

อย่างไรก็ดี ขนาดของ datafile มากสุดยังขึ้นกับ Operation System อีกด้วย
ตัวอย่าง: พิจารณา datafile ที่เปิดใช้ Autoextend

SQL> select file_id, tablespace_name, bytes, maxbytes/1024/1024 "maxbytes(M)", maxblocks, increment_by, file_name from dba_data_files where autoextensible = 'YES';

FILE_ID TABLESPACE BYTES maxbytes(M) MAXBLOCKS INCREMENT_BY FILE_NAME
---------- ---------- ---------- ----------- ---------- ------------ ------------------------------------------------------------
4 USERS 298188800 32767.9844 4194302 1280 /oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf
3 UNDOTBS1 272629760 32767.9844 4194302 640 /oracle/oradata/ORCL/datafile/o1_mf_undotbs1_5twzp19d_.dbf
2 SYSAUX 962854912 32767.9844 4194302 1280 /oracle/oradata/ORCL/datafile/o1_mf_sysaux_5twzp15k_.dbf
1 SYSTEM 849346560 32767.9844 4194302 1280 /oracle/oradata/ORCL/datafile/o1_mf_system_5twzp126_.dbf
5 TBS_DATA 136314880 32767.9844 4194302 1280 /oracle/oradata/ORCL/datafile/o1_mf_tbs_data_5v51k05o_.dbf

5.2 Location
เราควรต้องรู้ว่า ตำแหน่งของ datafile นั้นอยู่ที่ไหน เพื่อใช้ในการพิจารณา และการบำรุงรักษา

SQL> select * from v$dbfile;
FILE# NAME
---------- ------------------------------------------------------------
4 /oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf
3 /oracle/oradata/ORCL/datafile/o1_mf_undotbs1_5twzp19d_.dbf
2 /oracle/oradata/ORCL/datafile/o1_mf_sysaux_5twzp15k_.dbf
1 /oracle/oradata/ORCL/datafile/o1_mf_system_5twzp126_.dbf
5 /oracle/oradata/ORCL/datafile/o1_mf_tbs_data_5v51k05o_.dbf
6 /oracle/oradata/ORCL/datafile/o1_mf_flow_1_5w23bgcm_.dbf

ตัวอย่าง: การย้าย datafile (แบบ alter database datafile ... )


SQL> select file#,name, status from v$datafile;

FILE# NAME STATUS
---------- ------------------------------------------------------------ -------
1 /oracle/oradata/ORCL/datafile/o1_mf_system_5twzp126_.dbf SYSTEM
2 /oracle/oradata/ORCL/datafile/o1_mf_sysaux_5twzp15k_.dbf ONLINE
3 /oracle/oradata/ORCL/datafile/o1_mf_undotbs1_5twzp19d_.dbf ONLINE
4 /oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf ONLINE
5 /oracle/oradata/ORCL/datafile/o1_mf_tbs_data_5v51k05o_.dbf ONLINE
6 /oracle/oradata/ORCL/datafile/o1_mf_flow_1_5w23bgcm_.dbf ONLINE


SQL> alter database datafile
4 offline;

Database altered.

SQL> !mv /oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf /oracle/oradata/ORCL/datafile_test/o1_mf_users_5twzp1c3_new.dbf

SQL> select file#,name, status from v$datafile;

FILE# NAME STATUS
---------- ------------------------------------------------------------ -------
4 /oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf
RECOVER

SQL> alter database rename file '/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf' to '/oracle/oradata/ORCL/datafile_test/o1_mf_users_5twzp1c3_new.dbf';

Database altered.

SQL> select file#,name, status from v$datafile;
FILE# NAME STATUS
---------- ---------------------------------------------------------------- -------
4 /oracle/oradata/ORCL/datafile_test/o1_mf_users_5twzp1c3_new.dbf RECOVER

SQL> recover datafile 4;

Media recovery complete.

SQL> select file#,name, status from v$datafile;

FILE# NAME STATUS
---------- ---------------------------------------------------------------- -------
4 /oracle/oradata/ORCL/datafile_test/o1_mf_users_5twzp1c3_new.dbf OFFLINE

SQL> alter database datafile
 4 online ;

Database altered.

SQL> select file#,name, status from v$datafile;

FILE# NAME STATUS
---------- ---------------------------------------------------------------- -------
4 /oracle/oradata/ORCL/datafile_test/o1_mf_users_5twzp1c3_new.dbf ONLINE


หรือ ใช้อีกวิธี (alter tablespace ...)

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace users offline ;

Tablespace altered.

SQL> select file#,name, status from v$datafile;

FILE# NAME STATUS
---------- ---------------------------------------------------------------- -------
4 /oracle/oradata/ORCL/datafile_test/o1_mf_users_5twzp1c3_new.dbf OFFLINE

SQL> !mv /oracle/oradata/ORCL/datafile_test/o1_mf_users_5twzp1c3_new.dbf /oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf

SQL> alter database rename file '/oracle/oradata/ORCL/datafile_test/o1_mf_users_5twzp1c3_new.dbf' to '/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf';

Database altered.

SQL> select file#,name, status from v$datafile;

FILE# NAME STATUS
---------- ---------------------------------------------------------------- -------
4 /oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf OFFLINE

SQL> alter tablespace users online;

Tablespace altered.

SQL> alter tablespace users read write;

Tablespace altered.

SQL> select file#,name, status from v$datafile;

FILE# NAME STATUS
---------- ---------------------------------------------------------------- -------
4 /oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf ONLINE


ข้างต้นได้แสดงตัวอย่างการย้าย datafile อย่างไรก็ดีเราควรรู้และเข้าใจ การตรวจเช็ค และจัดการกับ datafile (
อ่าน เพิ่มเติม)


หมายเหตุ การย้าย database file จากข้างต้นเป็นการย้าย database file บน File System แต่ปัจจุบันหลายๆที่ อาจจะใช้ ASM และหากต้องการย้าย database file ใน ASM DiskGroup ควรทำอย่างไร ขอแนะนำตัวอย่างคร่าวๆ

http://surachartopun.com/2009/10/moving-datafile-system-from-one-asm.html
http://surachartopun.com/2009/10/moving-datafile-in-asm-by-asmcmdcp.html

ตัวอย่างแรก เป็นการย้าย database file ของ system tablespace ดังนั้นจึงต้อง shutdown ฐานข้อมูล แต่ถ้าต้องการย้าย database file ของ tablespace ที่ไม่ใช่ system/sysaux เราแค่เพียง offline tablespace


ตัวอย่าง ย้าย database file กรณี ASM DiskGroup

SQL>   select file_id, file_name from dba_data_files where tablespace_name='TEST_DATA';


  FILE_ID FILE_NAME

---------- --------------------------------------------------

         9 +DATA/orcl/datafile/test_data.1335.721759717

SQL> alter tablespace TEST_DATA read only;


Tablespace altered.


SQL>  
alter tablespace TEST_DATA offline;


Tablespace altered

RMAN> backup as copy datafile 9 format '+DATA2';

Starting backup at 26-APR-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=38 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00009 name=+DATA/orcl/datafile/test_data.1335.721759717

output file name=+DATA2/orcl/datafile/test_data.993.749500909 tag=TAG20110426T184147 RECID=38 STAMP=749500915

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 26-APR-11

RMAN> SWITCH DATAFILE 9 to copy;

datafile 9 switched to datafile copy "+DATA2/orcl/datafile/test_data.993.749500909"

SQL>  alter tablespace TEST_DATA online;


Tablespace altered.

SQL>  alter tablespace TEST_DATA read write;


Tablespace altered.

SQL>   select file_id, file_name from dba_data_files where tablespace_name='TEST_DATA';


  FILE_ID FILE_NAME

---------- --------------------------------------------------

         9 +DATA2/orcl/datafile/test_data.993.749500909

*** จากนั้นก็ลบ datafilecopy ด้วย rman ***

RMAN> list datafilecopy all;

RMAN> delete datafilecopy … [all];

6. Tablespaces
6.1 SYSTEM Tablespace
User objects ไม่ควรสร้างใน System tablespace เพื่อช่วยไม่ให้เกิดปัญหาเรื่อง fragmentation และป้องกันการมีขนาดใหญ่ของ system tablespace โดยที่ไม่จำเป็น

SQL> select owner, segment_name, segment_type from dba_segments where tablespace_name = 'SYSTEM' and owner not in ('SYS','SYSTEM');

ตรวจสอบ segment ที่อยู่บน system tablspace และ เจ้าของไม่ใช้ SYS หรือ SYSTEM

6.2 SYSAUX Tablespace (version >= 10g)
SYSAUX ถูกติดตั้งอัตโนมัติ เป็นเหมือน tablespace (ช่วย)ให้กับ system tablespace ถ้า SYSAUX มีปัญหาอาจทำให้ ฐานข้อมูลฟีเจอร์(เช่น AWR)ที่ใช้ SYSAUX มีปัญหา


SQL> alter system checkpoint;

System altered.

SQL> exec DBMS_WORKLOAD_REPOSITORY.create_snapshot;
BEGIN DBMS_WORKLOAD_REPOSITORY.create_snapshot; END;

*
ERROR at line 1:
ORA-13509: error encountered during updates to a AWR table
ORA-00376: file ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/oracle/oradata/ORCL/datafile/o1_mf_sysaux_5twzp15k_.dbf'
cannot be read at this time
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 14
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 37
ORA-06512: at line 1

SQL> alter tablespace sysaux online;

Tablespace altered.

SQL> exec DBMS_WORKLOAD_REPOSITORY.create_snapshot;

PL/SQL procedure successfully completed.


การตรวบสอบการใช้งานบน SYSAUX

SQL> select space_usage_kbytes, occupant_name, occupant_desc from v$sysaux_occupants order by 1 desc;


หรือ

SQL> @?/rdbms/admin/utlsyxsz.sql


utlsyxsz.sql
- Utility script สำหรับ SYSAUX Size

6.3 Locally vs Dictionary Managed Tablespaces
Dictionary Managed Tablespaces
ถูกจัดการด้วยการใช้ dictionary tables
Locally Managed Tablespaces เริ่มมีมาตั้งแต่ Oracle 8i. Tablespace
จะถูกจัดการเฉพาะส่วน ด้วยการใช้ bitmap

SQL> select tablespace_name, extent_management from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
SYSTEM LOCAL
SYSAUX LOCAL

อย่างไรก็ตาม Locally Managed Tablespaces มีข้อดีกว่า Data Dictionary managed tablespaces เช่น ไม่ต้องมีการเก็บ free space ใน data dictionary จึงช่วยลด contention ของ table นั้นๆ และยังลดการเกิด fragmentation เป็นต้น

Extent_management_clause:
[EXTENT MANAGEMENT
{DICTIONARY | LOCAL
{AUTOALLOCATE | UNIFORM [SIZE integer [K|M] }}]

AUTOALLOCATE - tablespace จะถูกจัดการขนาดของ EXTENT จากระบบ
UNIFORM - tablespace จะถูกจัดการกับขนาดของ EXTENT ให้มีขนาดที่เหมือนกัน (default = 1 megabyte)

6.4 Temporary Tablespace
จะถูกใช้เพื่อการ sort และการเก็บ global temporary tables. เราควรต้องแน่ใจว่า tablespace สำหรับ temporary segments เป็นประเภท
TEMPORARY


SQL> select tablespace_name, contents from dba_tablespaces;

TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
SYSAUX PERMANENT
TEMP
TEMPORARY
USERS PERMANENT


หรือ แน่ใจว่า USER ถูกหนดให้ใช้ temporary space เป็นประเภท TEMPORARY

SQL> select u.username, t.tablespace_name from dba_users u, dba_tablespaces t where u.temporary_tablespace = t.tablespace_name and t.contents <> 'TEMPORARY';


ตัวอย่างอื่นๆ
การเปลี่ยน temporary tablespace บน user

SQL> alter user SYSTEM temporary tablespace User;

การเปลี่ยน default temporary tablespace บนฐานข้อมูล

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;


การลดขนาด tempfile

การหาขนาดของ temporary tablespace

SQL> select tablespace_name, sum(bytes)/1024/1024 mb from dba_temp_files group by tablespace_name;

TABLESPACE_NAME MB
------------------------------ ----------
TEMP
28

การ หา "high water mark" ของ temporary tablespace (max used at one time)

SQL> select tablespace_name, sum(bytes_cached)/1024/1024 mb from v$temp_extent_pool group by tablespace_name;

TABLESPACE_NAME MB
------------------------------ ----------
TEMP
27

การ หา current usage

SQL> select ss.tablespace_name, sum((ss.used_blocks*ts.blocksize))/1024/1024 mb from gv$sort_segment ss, sys.ts$ ts where ss.tablespace_name = ts.name group by ss.tablespace_name;

TABLESPACE_NAME MB
------------------------------- ----------
TEMP
1

หรือ

SQL> SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A,(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;

TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP 28 1 27


*** การลดขนาด tempfile บนเวอร์ชั่น 11g เราสามารถ Shrink Temporary Tablespace ได้ ดังตัวอย่าง ***


SQL> SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A,(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;

TABLESPACE                            MB_TOTAL        MB_USED        MB_FREE

------------------------------- ---------- ---------- ----------

TEMP2                                      100              0            100

TEMP                                9866.99219              0 9866.99219

SQL> alter tablespace TEMP shrink space;

Tablespace altered.

SQL> alter tablespace TEMP2 shrink space;

Tablespace altered.


SQL> SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A,(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;

TABLESPACE                            MB_TOTAL        MB_USED        MB_FREE

------------------------------- ---------- ---------- ----------

TEMP2                               25.9921875              1 24.9921875

TEMP                                 1.9921875              0  1.9921875

6.5 Tablespace Fragmentation
Fragmentation คือ การที่ tablespace ถูกแบ่งออกเป็นส่วนๆ อาจจะมีพื้นที่ว่างมาก แต่เป็นชิ้นเล็ก ทำให้ไม่สามารถใช้งานมันได้
Fragmented tablespaces มีผลต่อ performance โดยเฉพาะ Full Table Scans หรือ ทำให้เกิดปัญหา out-of-space. การแก้ไขปัญหา ทำได้ด้วยการ recreate object เราสามารถใช้ 'alter table .. move' หรือ การ export/import

Script to Detect Tablespace Fragmentation [ID 1020182.6]

Script to Report Tablespace Free and Fragmentation [ID 1019709.6]

SET ECHO off

REM NAME:   TFSFSSUM.SQL

REM USAGE:"@path/tfsfssum"

REM ------------------------------------------------------------------------

REM REQUIREMENTS:

REM  SELECT ON DBA_FREE_SPACE< DBA_DATA_FILES

REM ------------------------------------------------------------------------

REM AUTHOR:

REM        Cary Millsap,  Oracle  Corporation

REM        (c)1994 Oracle Corporation

REM ------------------------------------------------------------------------

REM PURPOSE:

REM        Displays tablespace free space and fragmentation for each

REM        tablespace,  Prints the total size, the amount of space available,

REM        and a summary of freespace fragmentation in that tablespace.

REM ------------------------------------------------------------------------

REM EXAMPLE:

REM

REM            Database Freespace Summary

REM

REM                           Free         Largest           Total          Available   Pct

REM           Tablespace         Frags        Frag (KB)           (KB)             (KB)         Used

REM        ---------------- -------- ------------ ------------ ------------ ----

REM        DES2                        1           30,210        40,960           30,210         26

REM        DES2_I                      1           22,848        30,720           22,848         26

REM        RBS                        16           51,198        59,392           55,748          6

REM        SYSTEM                      3            4,896        92,160            5,930         94

REM        TEMP                        5              130           550              548          0

REM        TOOLS                      10           76,358   117,760           87,402         26

REM        USERS                       1               46         1,024               46         96

REM                         --------                  ------------ ------------

REM        sum                        37                    342,566          202,732

REM

REM ------------------------------------------------------------------------

REM DISCLAIMER:

REM        This script is provided for educational purposes only. It is NOT

REM        supported by Oracle World Wide Technical Support.

REM        The script has been tested and appears to work as intended.

REM        You should always run new scripts on a test instance initially.

REM ------------------------------------------------------------------------

REM Main text of script follows:

ttitle -

   center  'Database Freespace Summary'  skip 2

comp sum of nfrags totsiz avasiz on report

break on report

col tsname  format             a16 justify c heading 'Tablespace'

col nfrags  format         999,990 justify c heading 'Free|Frags'

col mxfrag  format 999,999,990 justify c heading 'Largest|Frag (KB)'

col totsiz  format 999,999,990 justify c heading 'Total|(KB)'

col avasiz  format 999,999,990 justify c heading 'Available|(KB)'

col pctusd  format             990 justify c heading 'Pct|Used'

select

  total.tablespace_name                           tsname,

  count(free.bytes)                               nfrags,

  nvl(max(free.bytes)/1024,0)                     mxfrag,

  total.bytes/1024                                totsiz,

  nvl(sum(free.bytes)/1024,0)                     avasiz,

  (1-nvl(sum(free.bytes),0)/total.bytes)*100  pctusd

from

  dba_data_files  total,

  dba_free_space  free

where

  total.tablespace_name = free.tablespace_name(+)

  and total.file_id=free.file_id(+)

group by

  total.tablespace_name,

  total.bytes

/

7. Objects
7.1 จำนวนของ Extents
Extent เป็นหน่วย(logical) ของการจัดสรรพื้นที่ฐานข้อมูล หากเรียงลำดับ Data Block ->  Extent -> Segment

การ ขยาย ของ object อาจจะไม่ใช่สิ่งสำคัญต่อ Performance แต่ถ้าการขยายของ object รวมๆ กัน หลายๆ object ย่อมมีผลต่อ Performance แน่นอน
เราสามารถหา object ที่มีการจัดสรร extent มากกว่าที่เรากำหนดได้:

select owner, segment_type, segment_name, tablespace_name, count(blocks), SUM(bytes/1024) "BYTES K", SUM(blocks) from dba_extents where owner NOT IN ('SYS','SYSTEM') group by owner, segment_type, segment_name, tablespace_name having count(*) > &number_extents order by segment_type, segment_name;

สิ่งนี้บอกอะไร แล้วอย่างไร?
เมื่อ เราพบ object ที่มีการจัดสรร extent มาก(เช่น จำนวน extent 100 - 200) เราสามารถสร้าง object นี้ใหม่กับ extent ที่มีขนาดใหญ่กว่า

ตัวอย่าง: การย้าย table จาก extent 1M ไป 10M, สมมุติ สร้าง tablespace extent uniform 1M(TBS_EXT_1M) และ 10M(TBS_EXT_10M)

SQL> select blocks, bytes, tablespace_name from user_extents where segment_name='TB_TEST';

BLOCKS BYTES TABLESPACE_NAME
---------- ---------- ------------------------------
128 1048576 TBS_EXT_1M
128 1048576 TBS_EXT_1M
128 1048576 TBS_EXT_1M
128 1048576 TBS_EXT_1M
128 1048576 TBS_EXT_1M
128 1048576 TBS_EXT_1M
128 1048576 TBS_EXT_1M
128 1048576 TBS_EXT_1M
128 1048576 TBS_EXT_1M

SQL> alter table TB_TEST move tablespace TBS_EXT_10M;

Table altered.

SQL> select blocks, bytes, tablespace_name from user_extents where segment_name='TB_TEST';

BLOCKS BYTES TABLESPACE_NAME
---------- ---------- ------------------------------
1280 10485760 TBS_EXT_10M


จะเห็นว่า จำนวนของ extent น้อยลง

7.2 Next extent
Segment สามารถที่จะเติบโตได้ จึงต้องมีการจัดสรร next extent เมื่อจำเป็น ถ้าพื้นที่ว่างนั้นไม่เพียงพอใน tablespace จะส่งผลให้ next extent ไม่สามารถจัดสรรพื้นที่ และ object จะไม่เติบโตต่อไป
เราสามารถเช็ค segment ที่ไม่สามารถจัดสรร next extent

select s.owner, s.segment_name, s.segment_type, s.tablespace_name, s.next_extent from dba_segments s where s.next_extent > (select MAX(f.bytes) from dba_free_space f where f.tablespace_name = s.tablespace_name);


แต่ ถ้ามี fragmentation มากๆ ใน tablespace จะส่งผลให้ผลลัพท์ที่ได้ แสดงว่า object ว่ายังสามารถเติบโตได้ ดังนั้นเราอาจจะดัดแปลงโดยการหา fragmentation ใน tablespace ไปเปรียบเทียบกับ next extent ของแต่ละ object

7.3 Indexes
การ rebuild index เป็นสิ่งที่ดี แต่เรายังสามามารถเลือกวิธีการอื่น coalescing index
การ พิจารณา b-tree index ก่อน rebuild อาจใช้หลักเกณฑ์
- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.
อ่านเพิ่มเติม
Richard Foote

ตัวอย่าง: เช็ค index_stats เมื่อมีการ update/index ข้อมูล และ rebuild index (tb_test = 50 rows)

SQL> create index ind_test on tb_test(object_id);

Index created.

SQL> analyze index IND_TEST validate structure;

Index analyzed.

SQL> select lf_rows, lf_rows_len, del_lf_rows, del_lf_rows_len, used_space FROM index_stats WHERE name = 'IND_TEST';

LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN USED_SPACE
---------- ----------- ----------- --------------- ----------
50 800 0 0 800

SQL> update tb_test set object_id=1 where rownum <= 10;

10 rows updated.

SQL> commit;

Commit complete.

SQL> analyze index IND_TEST validate structure;

Index analyzed.

SQL> select lf_rows, lf_rows_len, del_lf_rows, del_lf_rows_len, used_space FROM index_stats WHERE name = 'IND_TEST';

LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN USED_SPACE
---------- ----------- ----------- --------------- ----------
60 940 10 160 940

SQL> delete from tb_test where rownum <=10; 10 rows deleted. SQL> commit;

Commit complete.

SQL> analyze index IND_TEST validate structure;

Index analyzed.

SQL> select lf_rows, lf_rows_len, del_lf_rows, del_lf_rows_len, used_space FROM index_stats WHERE name = 'IND_TEST';

LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN USED_SPACE
---------- ----------- ----------- --------------- ----------
50 780 10 140 780

SQL> alter index ind_test rebuild;

Index altered.

SQL> analyze index IND_TEST validate structure;

Index analyzed.

SQL> select lf_rows, lf_rows_len, del_lf_rows, del_lf_rows_len, used_space FROM index_stats WHERE name = 'IND_TEST';

LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN USED_SPACE
---------- ----------- ----------- --------------- ----------
40 640 0 0 640

นั่นเป็น แค่ตัวอย่างการ analyze index คร่าวๆ
การตรวจสอบ object เราควรจะตรวจเช็คอย่างสม่ำเสมอเพื่อป้องกันปัญหาในอนาคต ซึ่งอาจส่งผลต่อ application และอื่นๆ

8. AUTO vs MANUAL undo - ใน ฐาน ข้อมูล Oracle เวอร์ชั่นก่อนๆ เราจัดการกับ RollBack Segments ด้วย manual undo. ส่วน automatic undo(เริ่มใช้ Oracle 9i) จะถูกใช้เมื่อเซต UNDO_MANAGEMENT เป็น AUTO


SQL> show parameter UNDO_MANAGEMENT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string
AUTO

แต่ ถ้าหาไม่เซตค่า UNDO_MANAGEMENT หรือเซตเป็น MANUAL จะใช้กลไก 'old' rollback segment อย่างไรก็ตาม Oracle แนะนำให้ใช้ AUTO

8.1 AUTO UNDO
การกำหนดเวลาในการเก็บ undo นั้นจะถูกควบคุมด้วย
UNDO_RETENTION (วินาที)
undo_retention เป็นพารามิเตอร์ใหม่ที่พบบน Oracle 9i และยังเป็นพารามิเตอร์ที่สนับสนุน "flashback query" ฟีเจอร์

SQL> show parameter UNDO_RETENTION;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer
900

SQL> alter system set UNDO_RETENTION=3600;

System altered.

SQL> show parameter UNDO_RETENTION;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer
3600

บน Oracle 10g คุณอาจเลือกใช้ GUARANTEE เพื่อให้แน่ใจว่า undo ข้อมูล ไม่ถูกขียนทับก่อน undo_retention ที่กำหนด

SQL> show parameter undo_tablespace;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1

SQL> alter tablespace UNDOTBS1 retention GUARANTEE;

Tablespace altered.

V$UNDOSTAT view สามารถใช้ในการตรวจสอบการใช้ประโยชน์พื้นที่ undo
คำนวณจำนวน undo block ต่อวินาที:

SQL> SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM v$undostat;

(SUM(UNDOBLKS))/SUM((END_TIME-BEGIN_TIME)*86400)
------------------------------------------------
57.8207692

หรือ คำนวณจำนวน Byte ที่ต้องการ

SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes" FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),(select block_size as DBS from dba_tablespaces where tablespace_name=(select upper(value) from v$parameter where name = 'undo_tablespace'));

Bytes
----------
751016.081

เรา ยังใช้ DBA_UNDO_EXTENTS เพื่อตรวจสอบรายละเอียด extents และพื้นที่ใช้ใน undo tablespace ปัจจุบัน

SQL> SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

STATUS SUM(BYTES) COUNT(*)
--------- ---------- ----------
ACTIVE 1048576 1
EXPIRED 172687360 250
UNEXPIRED 49938432 57


ACTIVE - Undo Extent เป็น Active, ใช้โดย transaction.
EXPIRED - Undo Extent ถูก expire (Exceeded the Undo Retention).
UNEXPIRED - Undo Extent ที่ยังขึ้นอยู่กับค่า UNDO_RETENTION.

8.2 MANUAL UNDO
- rollback มีปัญหาอาจ ส่งผลต่อการเปิดฐานข้อมูล
เช่น เซต rollback_segments เป็น RBS1 แต่ไม่พบ 'RBS1' rollback segment เมื่อเปิดฐานข้อมูล

ORA-01534: rollback segment 'RBS1' doesn't exist
Error 1534 happened during db open, shutting down database

- rollback segment ขนาดเล็ก หรือไม่เพียงพอ จะมีผลกระทบต่อการทำงานของฐานข้อมูล ดังนั้นเราต้องตรวจสอบ

SQL> create rollback segment rbs1;

Rollback segment created.

SQL> show parameter UNDO_MANAGEMENT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL

SQL> show parameter rollback_segments

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
rollback_segments string RBS1

SQL> select d.segment_name, d.tablespace_name, s.waits, s.shrinks, s.wraps, s.status from v$rollstat s, dba_rollback_segs d where s.usn = d.segment_id order by 1;

SEGMENT_NAME TABLESPACE_NAME WAITS SHRINKS WRAPS STATUS
------------------------------ ------------------------------ ---------- ---------- ---------- ---------------
RBS1 SYSTEM 0 0 10 ONLINE
SYSTEM SYSTEM 0 0 0 ONLINE

ถ้าไม่กำหนด rollback_segments จะมี "SYSTEM" rollback segment เดียว

SQL> show parameter UNDO_MANAGEMENT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL

SQL> show parameter rollback_segments

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
rollback_segments string

SQL> select d.segment_name, d.tablespace_name, s.waits, s.shrinks, s.wraps, s.status from v$rollstat s, dba_rollback_segs d where s.usn = d.segment_id order by 1;

SEGMENT_NAME TABLESPACE_NAME WAITS SHRINKS WRAPS
STATUS
------------------------------ ------------------------------ ---------- ---------- ---------- ---------------
SYSTEM SYSTEM 0 0 0 ONLINE


จากข้างต้น ขอขยายความ MANUAL UNDO เพียงคร่าวๆ (
more)
อย่างไรก็ตามขอแนะนำให้ใช้ AUTO UNDO (อย่างกรณีฟีเจอร์ Total Recall ก็ต้องการ AUTO UNDO)

9. การจัดการกับหน่วยความจำ (Memory Management)
การ จัดการกับหน่วยความจำของฐานข้อมูล Oracle จะมีความแตกต่างกันไป ซึ่งขึ้นอยู่กับ oracle เวอร์ชัน เพราะ oracle เองได้มีการเพิ่มคุณสมบัติ ในการจัดการกับหน่วยความจำกับฐานข้อมูลเวอร์ชันใหม่ๆ เพื่อให้ง่ายและมีประสิทธิภาพ ดังนั้นเรามักจะถูกแนะนำให้ใช้คุณสมบัติในการจัดการกับหน่วยความจำด้วยวิธีอัตโนมัติ

                   

9.1 ก่อน Oracle 9i
องค์ ประกอบหน่วย ความจำที่แตกต่างกัน (SGA และ PGA) ต้องการที่จะถูกกำหนดในการเริ่มต้นของฐานข้อมูล(startup) ค่าเหล่านี้เป็นค่าคงที่ ดังนั้นหากหนึ่งในองค์ประกอบของหน่วยความจำที่ต่ำเกินไป และจำเป็นต้องเปลี่ยนแปลง ฐานข้อมูลจำเป็นต้อง restart เพื่อให้ฐานข้อมูลใช้ค่าใหม่ที่ได้เปลี่ยนแปลง

ตัวอย่าง พารามิเตอร์ที่อาจใช้ผิดบ่อยๆ เช่น sort_area_size


SQL> show parameter sort_area_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 1024000

พารามิเตอร์ sort_area_size กำหนด จำนวนหน่วยความจำที่สามารถใช้ในการเรียงลำดับ ค่านี้เป็นส่วนหนึ่งของ User Global Area (UGA) ดังนั้นจะถูกจัดสรรให้แต่ละผู้ใช้
หากผู้ใช้เข้ามาใช้งานพร้อมกันเป็น จำนวนมาก ในการเรียงลำดับข้อมูลขนาดใหญ่ ระบบอาจจะใช้หน่วยความจำจำนวนมาก

เช่น sort_area_size ของ 1MB กับผู้ใช้ 200 คน เข้ามาพร้อมกันในฐานข้อมูล อย่างไรก็ตามหน่วยความจำนี้ถูกจัดสรรแบบไดนามิก มันสามารถจัดสรรหน่วยความจำถึง 200MB และอาจทำให้เกิดการใช้ SWAP บนระบบ

9.2 Oracle 9i
เริ่มต้นจาก Oracle 9i พวกเราจะพบพารามิเตอร์:
workarea_size_policy = [AUTO | MANUAL]
pga_aggregate_target = <value>

ซึ่งยอมให้เรากำหนดพื้นที่หน่วยความจำสำหรับ PGA โดยที่จะถูกใช้ร่วมกันของ sessions
ถ้าค่านี้น้อยเกินไป (pga_aggregate_target) บ่อยครั้งที่เราจะพบ ORA-4030


SQL> show parameter workarea_size_policy

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO

SQL> show parameter pga_aggregate_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 500M

ถ้า workarea_size_policy เป็น AUTO หน่วยความจำสำหรับ work area (sort area) จะถูกจัดการอัตโนมัติ จากค่า pga_aggregate_target
ตัวอย่าง work area:
- Sort-based operators (ORDER BY, GROUP BY, ROLLUP, window functions)
- Hash-join
- Bitmap merge
- Bitmap create

อันได้แก่ พารามิเตอร์
*area_size

SQL> show parameter area_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_size integer 65536

ถ้า workarea_size_policy เป็น MANUAL หน่วยความจำสำหรับ work area (sort area) จะไม่ใช้ pga_aggregate_target เราต้องมีการจัดการกับพารามิเตอร์ *area_size แต่ละตัวเอง
การตรวจสอบ PGA เช่น ข้อมูลการใช้PGA

SELECT * FROM V$PGASTAT;

หรือ คำแนะนำเพื่อการปรับค่าของ PGA

SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE;


หรือ การตรวจสอบ PGA แต่ละ session

set pages500 lines110 trims on
clear col
col name format a30
col username format a20
break on username nodup skip 1
select vses.username||':'||vsst.sid||','||vses.serial# username, vstt.name, max(vsst.value) value
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and vstt.name in
('session pga memory','session pga memory max','session uga memory','session uga memory max',
'session cursor cache count','session cursor cache hits','session stored procedure space',
'opened cursors current','opened cursors cumulative') and vses.username is not null
group by vses.username, vsst.sid, vses.serial#, vstt.name order by vses.username, vsst.sid, vses.serial#, vstt.name;


การประมาณค่า PGA_AGGREGATE_TARGET:
- สำหรับระบบ OLTP

PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 20%

- สำหรับระบบ DSS

PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 50%

9.3 Oracle 10g
Automatic Shared Memory Management (ASMM) ถูกแนะนำใน Oracle เวอร์ชัน 10g คุณลักษณะการจัดการหน่วยความจำอัตโนมัติ ทำได้โดยการตั้งค่าพารามิเตอร์
SGA_TARGET ให้มากกว่าศุนย์
คุณลักษณะ นี้มีประโยชน์ ช่วยให้เราสามารถแบ่งปันทรัพยากรหน่วยความจำในส่วนต่างๆ และการจัดสรรทรัพยากรณ์จะเป็นไปโดยอัตโนมัติ ด้วย Oracle

SGA_TARGET พารามิเตอร์มีผลต่อ องประกอบหน่วยความจำ:
- Fixed SGA และ other internal ที่ต้องการโดย Oracle Database instance
- Log buffer
- Shared pool
- Java pool
- Buffer cache
- keep และ recycle buffer caches (ถ้าระบุ)
- Nonstandard block size buffer caches (ถ้าระบุ)
- Streams pool

เมื่อ เราเซตค่าสำหรับ
SGA_TARGET, Oracle 10g จะกำหนดขนาดส่วนประกอบอันได้แก่
- Shared pool (SQL และ PL/SQL execution)
- Java pool (Java execution state)
- Large pool (large allocations เช่น RMAN backup buffers)
- Buffer cache
- Streams pool

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 512M

การตรวจสอบข้อมูลการใช้ SGA

select * from v$sgastat;
select * from v$sgainfo;


หรือ คำแนะนำเพื่อการปรับค่าของ SGA


select * from v$sga_target_advice order by sga_size;


หรือตรวจสอบการเพิ่ม ค่าหรือลดค่า ของส่วนประกอบ SGA


select * from v$sga_dynamic_components;


และ


ALTER SESSION SET nls_date_format = 'DD/MM/YYYY:HH24:MI:SS';
SET PAGESIZE 900
SET LINESIZE 255
COL COMPONENT FORMAT A25
COL INITIAL_SIZE FORMAT A10
COL TARGET_SIZE FORMAT A10
COL FINAL_SIZE FORMAT A10
COL OPER_TYPE FORMAT A10
select START_TIME, component, oper_type, oper_mode,status, initial_size/1024/1024 "INITIAL", target_size/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", END_TIME
from v$sga_resize_ops
order by start_time, component;


ส่วนการจัดการหน่วยความจำ PGA อัตโนมัติ ยังใช้งานได้ผ่าน workarea_size_policy และ pga_aggregate_target พารามิเตอร์

9.4 Oracle 11g
Automatic Memory Management (AMM) ถูกแนะนำใน Oracle เวอร์ชัน 11g ช่วยให้ปรับค่า PGA และ SGA โดยอัตโนมัติ กับค่าพารามิเตอร์
MEMORY_MAX_TARGET และ MEMORY_TARGET 
ความสัมพันธ์ของพารามิเตอร์ของหน่วยความจำ:

ถ้า MEMORY_TARGET != 0

                    - ถ้าเซตค่า SGA_TARGET และ PGA_AGGREGATE_TARGET ค่าของ SGA และ PGA จะถูกพิจารณาจากสองค่าข้างต้นตามลำดับ ดังนั้น MEMORY_TARGET >= SGA_TARGET + PGA_AGGREGATE_TARGET

                    - ถ้าเซตค่า SGA_TARGET แต่ไม่ได้เซตค่า PGA_AGGREGATE_TARGET ดังนั้น PGA_AGGREGATE_TARGET = MEMORY_TARGET - SGA_TARGET

                    - ถ้าเซตค่า PGA_AGGREGATE_TARGET แต่ไม่ได้เซตค่า SGA_TARGET ดังนั้น SGA_TARGET = min(MEMORY_TARGET - PGA_AGGREGATE_TARGET,  SGA_MAX_SIZE (ถ้าถูกเซตค่าโดยผู้ใช้))

                    - ถ้าไม่ได้เซตค่า SGA_TARGET และ PGA_AGGREGATE_TARGET ค่าของ SGA และ PGA จะถูกปรับปรุงโดยอัตโนมัติ จากค่าของ MEMORY_TARGET อย่างไรก็ตามกรณี Instance เริ่มต้นจะถูกเซต 60% สำหรับ SGA และ 40% สำหรับ PGA

SQL> show parameter MEMORY_MAX_TARGET

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 800M

SQL> show parameter MEMORY_TARGET

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 800M

การตรวจสอบองค์ประกอบของหน่วย ความจำที่ใช้ AMM

select * from V$MEMORY_DYNAMIC_COMPONENTS;

ตรวจสอบการเพิ่มและลดขององค์ ประกอบใน AMM ด้วย V$MEMORY_RESIZE_OPS
หรือ คำแนะนำเพื่อการปรับค่าสำหรับ AMM

select * from v$memory_target_advice order by memory_size;




หมายเหตุ กรณีการยกเลิกการใช้งาน AMM นั้น เราจำเป็นต้องเอาพารามิเตอร์ memory_target ออกจาก init ไฟล์ (ถ้าเพียง memory_target=0 ใน init ไฟล์)

กรณีที่ 1

SQL> !strings spfileorcl.ora | grep memory

*.memory_max_target=0

*.memory_target=0

 

SQL> startup

ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account

ORA-00849: SGA_TARGET 1073741824 cannot be set to more than MEMORY_MAX_TARGET 0.

 

กรณีที่ 2

SQL> !strings spfileorcl.ora | grep memory

*.memory_max_target=1073741824

 

SQL> startup

ORACLE instance started.




10. Logging & Tracing
10.1 Alert File
alert log ไฟล์ของฐานข้อมูล จะเก็บสิ่งที่เปลี่ยนแปลงในฐานข้อมูล อันได้แก่
- internal errors (ORA-600), block corruption errors (ORA-1578), และ deadlock errors (ORA-60)
- SQL เช่น CREATE/ALTER/DROP DATABASE/TABLESPACE และจาก Oracle Enterprise Manager หรือ SQL*Plus ที่เกี่ยวกับ STARTUP, SHUTDOWN, ARCHIVE LOG, RECOVER
- Errors เกี่ยวกับ shared server และ dispatcher processes
- Errors ขณะที่ materialized view (refresh)

การที่ เรามี alert log ไฟล์ ขนาดใหญ่ จะเป็นการใช้พื้นที่ของ Disk โดยที่ไม่จำเป็น และส่งผลต่อประสิทธิภาพในการเขียนลง Disk ของ OS

ก่อน 11g:

SQL> show parameter background_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/admin/orcl/bdump

11g ->: จะใช้ diagnostic_dest พารามิเตอร์

SQL> show parameter background_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/diag/rdbms/orcl/orcl/trace

SQL> show parameter
diagnostic_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /oracle

10.2 Max_dump_file_size
Oracle server process จะสร้าง trace ไฟล์ เพื่อเก็บความผิดพลาด เราสามารถใช้ trace ไฟล์ เพื่อวิเคราะห์ในแก้ปัญหาและปรับปรุง ส่วน
max_dump_file_size จะจำกัดขนาดของ trace ไฟล์

SQL> show parameter max_dump_file_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string unlimited

SQL> alter system set max_dump_file_size=10240;

System altered.

SQL> show parameter max_dump_file_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string 10240


user_dump_dest พารามิเตอร์ จะเป็นพื้นที่ ที่ใช้เก็บข้อมูล trace ไฟล์ ที่เกิดจาก user processes
ส่วนบน Oracle 11g พื้นที่ดังกล่าว จะถูกควบคุมโดย '
diagnostic_dest' พารามิเตอร์

*** audit ไฟล์ ***
โดย ปกติการ เชื่อมต่อฐานข้อมูลด้วย SYS หรือ SYSDBA จะมีการเก็บเข้า OS ไฟล์ โดยพื้นที่ ที่ใช้เก็บจะถูกควบคุมด้วย พารามิเตอร์ 'audit_file_dest'
ถ้า พารามิเตอร์นี้ ไม่ได้กำหนดจะเป็น $ORACLE_HOME/rdbms/audit

SQL> show parameter audit_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /oracle/admin/orcl/adump

ตัวอย่าง อื่นๆ (ตรวจสอบการ ใช้งาน sys กับการใช้ syslog)

*** บน 11g เราจำเป็นที่จะต้องเรียนรู้กับ
Automatic Diagnostic Repository (ADR) ***
ADR เป็นที่เก็บข้อมูลเพื่อใช้ในการวินิจฉัย เช่น trace files, alert log, และ Health Monitor reports. ส่วนที่ตั้งของ ADR จะถูกควบคุมด้วย '
diagnostic_dest' พารามิเตอร์

การตรวจเช็คบนฐานข้อมูล เป็นสิ่งที่จำเป็นสำหรับผู้ดูแลระบบ ผมหวังว่าบทความจะมีประโยชน์นะครับ

อ้างอิง

http://support.oracle.com
http://tahiti.oracle.com


เขียนโดย: @surachart (
http://surachartopun.com)

32