Oracle DBA Lab TH [EP.1]

- ติดตั้งระบบฐานข้อมูล Oracle 11g บน Windows XP

- ทดสอบและแก้ไข LISTENER

- ตรวจสอบการใช้งานฐานข้อมูลที่ติดตั้งสำเร็จแล้ว

Cacti + ORACLE ตอนที่ 4: Multiple output และ Data input field in graph title

ความเดิมตอนที่แล้วเราสามารถสร้างกราฟได้แล้ว ในบทนี้จะเพิ่มเิติมถึงการกำหนด title ของกราฟให้อ่านง่ายและสามารถรู้ว่ากราฟนี้เป็นหัวข้อใด มาจาก server ใหนและ Instance ชื่ออะไร รวมถึงตัวอย่างการรับค่าจาก Data Input Method แบบหลาย output ในครั้งเดียว และนำมาสร้างกราฟที่แสดงข้อมูลหลายชนิดพร้อมๆักัน

Data input field in graph title
เริ่มจาก download และติดตั้งตามตัวอย่างจาก Link นี้
http://forums.cacti.net/viewtopic.php?f=5&t=42802

1. Place 'addon_data_input_field.php' at 'cacti/lib/' directory
2. Modify get_graph_title() in 'cacti/lib/functions.php' as follows:

function get_graph_title($local_graph_id) {
        global $config;
        include_once($config["library_path"] . "/addon_data_input_field.php");

        $graph = db_fetch_row("select
                graph_local.host_id,
                graph_local.snmp_query_id,
                graph_local.snmp_index,
                graph_templates_graph.title
                from (graph_templates_graph,graph_local)
                where graph_templates_graph.local_graph_id=graph_local.id
                and graph_local.id=$local_graph_id");

        if ((strstr($graph["title"], "|")) && (!empty($graph["host_id"]))) {
                $graph["title"] = substitute_data_input_field($graph["title"], $graph["host_id"], $local_graph_id);
                return expand_title($graph["host_id"], $graph["snmp_query_id"], $graph["snmp_index"], $graph["title"]);
        }else{
                return $graph["title"];
        }
}

Multiple Output
ตัวอย่างนี้จะเป็นการสร้างกราฟ Tablespace usage monitor

1.ให้สร้าง Data Input Method ใหม่ชื่อว่า Oracle Tablespaces Monitor ดังรูป

2.สร้างสคริปท์ ss_oracle_tbls.php ที่ path <path_cacti>/scripts

<?
/* do NOT run this script through a web browser */
if (!isset($_SERVER["argv"][0]) || isset($_SERVER['REQUEST_METHOD'])  || isset($_SERVER['REMOTE_ADDR'])) {
   die("<br><strong>This script is only meant to run at the command line.</strong>");
}

/* display No errors */
error_reporting(0);

if (!isset($called_by_script_server)) {
        array_shift($_SERVER["argv"]);

        print call_user_func_array("ss_oracle_tbls", $_SERVER["argv"])."\n";
}

function ss_oracle_tbls ($tnsnames, $sql_path, $username, $password,$tablespacename) {

        putenv("ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1");
        putenv("TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin");

        $ret = '';

        $gdb_conn = ocilogon($username, $password,$tnsnames);

        $gdb_statement = OCIParse ($gdb_conn, "alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS' ");
        OCIExecute ($gdb_statement);

        if (! OCIExecute($gdb_statement)){
                        return;
        }

        include $sql_path;

        $gdb_statement = OCIParse ($gdb_conn, $query);
        OCIExecute ($gdb_statement);

        while (OCIFetchInto ($gdb_statement, $row, OCI_RETURN_NULLS + OCI_ASSOC)) {
                reset($row);

                $tbls_current=$row['CURR'];
                                $tbls_free=$row['FREE'];
                                $tbls_total=$row['TOTAL'];
                                $tbls_curr_percent=$row['CURRENT_PERCENT'];

        }

        OCIFreeStatement($gdb_statement);

        $ret .= 'current:'.$tbls_current.' current_percent:'.$tbls_curr_percent.' free:'.$tbls_free.' total:'.$tbls_total.' ';

        return trim($ret);
}
?>

3.สร้างสคริปท์ tablespace_usage.php ที่ path <path_cacti>/sql

<?

$query = " SELECT DISTINCT
         a.tablespace_name,
         (SUM (a.bytes) / 1024 / 1024 - ROUND (c.free / 1024 / 1024)) curr,
         (  SUM (DECODE (b.maxextend,
                         NULL, a.bytes / 1024 / 1024,
                           b.maxextend
                         * (SELECT VALUE
                              FROM v\$parameter
                             WHERE name = 'db_block_size')
                         / 1024
                         / 1024))
          - (SUM (a.bytes) / 1024 / 1024 - ROUND (c.Free / 1024 / 1024)))
            free,
         SUM (DECODE (b.maxextend,
                      NULL, a.bytes / 1024 / 1024,
                        b.maxextend
                      * (SELECT VALUE
                           FROM v\$parameter
                          WHERE name = 'db_block_size')
                      / 1024
                      / 1024))
            total,
         ROUND (  100
                * (SUM (a.bytes) / 1024 / 1024 - ROUND (c.free / 1024 / 1024))
                / (SUM (DECODE (b.maxextend,
                                NULL, a.bytes / 1024 / 1024,
                                  b.maxextend
                                * (SELECT VALUE
                                     FROM v\$parameter
                                    WHERE name = 'db_block_size')
                                / 1024
                                / 1024))))
            current_percent
    FROM dba_data_files a,
         sys.filext$ b,
         (  SELECT d.tablespace_name, SUM (NVL (c.bytes, 0)) free
              FROM dba_tablespaces d, dba_free_space c
             WHERE d.tablespace_name = c.tablespace_name(+)
          GROUP BY d.tablespace_name) c
   WHERE     a.file_id = b.file#(+)
         AND a.tablespace_name = c.tablespace_name
         AND a.tablespace_name = UPPER ('$tablespacename')
GROUP BY a.tablespace_name, c.free / 1024 ";

?>

4.สร้าง Data templates และ Graph templates ดังนี้
กดไปตาม Link จนถึงหน้า download จากนั้นนำไป import ยังระบบ Cacti ของท่าน
tablespace_data_template.xml
tablespace_graph_template.xml

เมื่อเปิดดู Data Templates จะเห็นว่ามีการสร้าง Internal Data Source Name มาเท่ากับ output จากสคริปท์ PHP

5. เปิดดุ Graph Templates จะเห็นว่าที่ช่อง title จะมีการใส่ parameter เข้าไปเพื่อให้แสดงว่ากราฟนี้มีข้อมูลมาจาก input อย่างไรบ้าง

|host_description|/|input_tnsnames| - Tablespace - |input_tablespace_name|

6. เปิดเมนู Management -> Data Sources แล้ว Click ที่ Add

ใส่ข้อมูล Data Source Fields -> Name ดังนี้

|host_description| - Tablespace - |input_tnsnames| : |input_tablespace_name|

7.กำหนด SQL script path ให้ตรงกับไฟล์ Tablespace_usage.php

8.เปิดเมนุ Management -> Graph Management แล้ว Click ที่ Add

เมื่อกำหนดค่าตามที่ต้องการแล้วให้กดที่ Save

หลักจากนั้นเมื่อเปิดดูรูปจะสังเกตูเห็นว่ามีข้อมูลแสดงที่ Title ออกมาอย่างครบถ้วนและข้อมูลของกราฟสามารถแสดงรายละเีอียดจากหลาย output ได้แล้ว


หมายเหตุ
จากตัวอย่างจะเป็นการ Monitor Tablespace usage ที่ไม่ได้ใช้ระบบ Auto-Extend เราจึงสังเกตุแค่ค่า Max หรือ Total space ว่ามีค่า used และ free เท่าใหร่ โดยไม่ได้สนใจว่าพร้อมสำหรับขยายอย่างไร การทำแบบนี้เป็นที่นิยมในระบบส่วนใหญ่เพราะจะได้ไม่ต้องกังวลกับ OS Disk หรือ Storage จะเต็มโดยที่เราไม่รู้ตัวหรือเปล่าเพราะกำหนดตายตัวไปแล้วว่าให้ Database ใช้ไปเท่าใหร่ แล้วค่อยมาดูว่าแต่ะละ Tablespace จะเต็ํมหรือยังจะได้ทำการ Add new datafiles

Cacti + ORACLE ตอนที่ 3: สร้าง Data Source และ Graph

ความเดิมตอนที่แล้วเราได้สร้าง Templates เอาไว้เรียบร้อย ขั้นต่อไปจะเริ่มสร้าง Data Source สำหรับเก็บข้อมูลที่ query มาจาก ORACLE และทำการสร้าง Graph ออกมา

สร้าง Data Source

ให้ Click ที่เมนู Management -> Data Sources จากนั้น click ที่ Add ด้านขวาบน

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

สร้าง Graph

ให้ Click ที่เมนู Management ->Graph Management จากนั้น Click ที่ Add ด้านขวาบน

เมื่อท่านทำการ Save อาจจะไม่แสดงรูปขึ้นมาเป็นเพราะ poller ยังไม่ได้รันเพื่อทำการเก็บข้อมูลเพื่อสร้าง Graph ให้รอประมาณ 5 นาที หากยังไม่มีรูปให้ Click ที่ Turn On Graph Debug Node เพื่อตรวจสอบดูว่ามี Error แบบใด

จัดแสดง Graph

ให้ Click ที่เมนู Management ->Graph Management จากนั้นให้ทำตามรูปตัวอย่าง

เสร็จแล้วกดดูกราฟ

Cacti + ORACLE ตอนที่ 2: สร้าง Graph Templates และ Data Templates

ความเดิมตอนที่แล้วเราได้ทำการสร้าง Data Input Method เพื่อ query ข้อมูลจาก ORACLE ในลำดับต่อไปจะเป็นการสร้าง Template ที่จะเป็นคล้ายๆกับ Library ไว้ให้ Cacti รู้ได้ว่าเราจะเก็บข้อมูลและสร้างกราฟอย่างไร ผมได้ทำตัวอย่างมาแล้วดังนี้

กดไปตาม Link จนถึงหน้า download จากนั้นนำไป import ยังระบบ Cacti ของท่าน
shared_pool_free_data_template.xml
shared_pool_free_graph_template.xml

หรือท่านสามารถทำด้วยตัวเองดังนี้
Data Template

Graph Template

Cacti + ORACLE ตอนที่ 1: สร้างสคริปท์ PHP เพื่ออ่านค่าจากฐานข้อมูล

ในกาีรทำงานของ Cacti ที่จะสร้าง poller ไปทำการ query ข้อมูลจากระบบที่ต้องการนั้นจะใช้การทำงานจาก SNMP โปรโตคอลเป็นหลัก แต่ในบางกรณีที่เป็นการ query ข้อมูลที่มีลักษณะพิเศษหรือเป็นการทำงานเฉพาะกิจบางอย่างนั้น จะไม่ได้มี SNMP มาให้ เราจึงต้องใช้วิธีการอื่นเพื่อใช้ query ข้อมูลออกมา

สำหรับ ORACLE Database ที่เราจะทำกันในบทนี้เราจะใช้วิธีที่เรียกว่า Data Input Methods ที่มีให้เลือกใช้ในเมนูของ Cacti ดังรูป

ในตัวอย่างนี้ผมจะสร้าง Data Input Methods ใหม่ขึ้นมาตั้งชื่อว่า Oracle Monitor Query เป็นฟังชั่นที่เราจะนำมาใช้สำหรับการ connect ไปยังระบบฐานข้อมูล ORACLE โดยฟังชั่นที่เราสร้างขึ้นมานี้ จะสามารถรับ input เพื่อนำไปประมวลผลค่าออกมาได้ ดังนั้นเราจะสร้างแค่ครั้งเีดียวแล้วจะประยุกต์ใช้ในการทำงานกับแต่ะละ SQL ที่เราจะนำไป query ได้จากหลายๆระบบ

Input Type กำหนดเป็น Script - Script Server (PHP) เพราะเราจะใช้การเรียก php สคริปท์จาก command line เพื่อติดต่อไปยังฐานข้อมูล ORACLE โดยใน php.ini เราจะต้องทำการติดตั้ง module เพื่อการใช้งาน จากตัวอย่างสคริปท์ของผมใช้ oci8 จากการติดตั้ง ORACLE client

Input String
path_cacti  path ที่อยู่ของ cacti (ของผมคือ /usr/share/cacti มาจากการติดตั้งด้วย YUM หากท่านใช้วิธีอื่นจะไม่เหมือนกัน)

/scripts/ss_oracle_query.php ss_oracle_query คือ sub folder ใน cacti ที่ระบุถึง php สคริปท์ที่เราต้องการให้รันเพื่อ query ค่าจาก ORACLE (ss_oracle_query ตัวที่สองใช้เพื่อระบุ function ในสคริปท์ที่เราต้องการ call)

tnsnamesค่า tnsnames หรือชื่อที่เรากำหนดไว้ในไฟล์ tnsnames.ora ที่เราได้ทำการกำหนด connection description เอาไว้

path_oracle_sql เพื่อที่เราจะสามารถกำหนด SQL Statement ได้โดยอิสระสำหรับแต่ละ query ที่เราจะใช้งาน ผมจึงกำหนดให้เป็น path ที่ระบุไปยัง php ไฟล์ที่เราจะนำมา include ไปในสคริปท์ ss_oracle_query.php ที่เป็นตัวรันอีกครั้งหนึ่ง สาเหตุที่ไม่ได้ทำเป็นไฟล์ .sql หรือรับค่าเป็น statement ไปเลยก็เพราะว่าการรันใน php จะต้องทำการแก้ escape character บางอย่างเสียก่อน ไม่สามารถรันได้ทันที (สคริปท์ SQL ที่ต้องทำเป็น PHP นั้นผมจะสอนวิธีการทำ และจะนำมาแจกในบทความต่อๆไปครับ)

user username สำหรับ login เพื่อเข้าไป query ยัง Database ที่เราต้องการ

password password สำหรับ login เพื่อเข้าไป query ยัง Database ที่เราต้องการ


ตัวอย่าง php สคริปท์ที่ใช้ใน parameter path_oracle_sql
/usr/share/cacti/sql/shared_pool_free.php

<?

$query = "      select
                        round(
                                (sum(decode(name,'free memory',bytes))/sum(bytes))*100
                        ,2) as free
                from v\$sgastat
                where pool = 'shared pool'  ";

?>

Source code ของสคริปท์หลักที่ใช้เพื่อ query ข้อมูล
/usr/share/cacti/scripts/ss_oracle_query.php

<?
/* do NOT run this script through a web browser */
if (!isset($_SERVER["argv"][0]) || isset($_SERVER['REQUEST_METHOD'])  || isset($_SERVER['REMOTE_ADDR'])) {
   die("<br><strong>This script is only meant to run at the command line.</strong>");
}

/* display No errors */
error_reporting(0);

if (!isset($called_by_script_server)) {
        array_shift($_SERVER["argv"]);

        print call_user_func_array("ss_oracle_query", $_SERVER["argv"])."\n";
}

function ss_oracle_query ($tnsnames, $sql_path, $username, $password) {

        putenv("ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1");
        putenv("TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin");

        $ret = '';

        $gdb_conn = ocilogon($username, $password,$tnsnames);

        $gdb_statement = OCIParse ($gdb_conn, "alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS' ");
        OCIExecute ($gdb_statement);

        if (! OCIExecute($gdb_statement)){
                        return;
        }

        include $sql_path;

        $gdb_statement = OCIParse ($gdb_conn, $query);
        OCIExecute ($gdb_statement);

        while (OCIFetchInto ($gdb_statement, $row, OCI_RETURN_NULLS + OCI_ASSOC)) {
                reset($row);

                $shared_pool_free=$row['FREE'];
        }

        OCIFreeStatement($gdb_statement);

        $ret .= 'value:'.$shared_pool_free.' ';

        return trim($ret);
}
?>

ข้อควรระวัง

putenv("ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1");
putenv("TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin");

2 บรรทัดนี้ในไฟล์ ss_oracle_query.php ท่านจะต้องแก้ให้ตรงกับระบบที่ท่านใช้งานในการกำหนด Environment เพื่อให้ PHP OCI8 ใช้งานในการเชื่อมต่อไปยังฐานข้อมูล ORACLE

Monitor ระบบ ORACLE DATABASE อย่างไร

เรื่องมีอยู่ว่าลูกศิษย์ของผมซึ่งทำงานเป็น Outsource DBA ประจำอยู่กับลูกค้าแห่งหนึ่งได้รับมอบหมายงานให้นำ Cacti มาใช้งานสำหรับ Monitor ORACLE ที่มีอยู่หลายระบบ (เค้าเล่าว่าเป็นพัน instance) ก็เลยคิดว่าแทนที่จะสอนลูกศิษย์ตัวเองอย่างเดียวก็มาเขียนให้คนอื่นได้อ่านด้วยน่าจะดีกว่า โดยจากประสบการณ์เดิมของผมที่ได้เคยทดสอบใช้เครื่องมือต่างๆมาหลายชนิด มักจะมีบางหัวข้อที่ไม่สามารถหาเครื่องมือตัวใหนมาช่วยจัดการให้ได้ จนในที่สุดก็ต้องเขียนขึ้นมาเองหรือใช้โปรแกรมที่เป็น Opensource และทำสคริปท์ขึ้นมาใช้ส่วนตัวสำหรับงานของตัวเอง

หลักการทั่วไปของการ monitoring ORACLE
ความยากก็คือเครื่องมือที่มีขายในตลาดสำหรับระบบ Enterprise ที่ใช้สำหรับจัดการกับ Server ที่มีจำนวนมาก โดยส่วนใหญ่ จะเป็นการใช้ระบบ SNMP Agent เพื่อเป็นส่วนใช้สำหรับส่งและรับค่าระหว่าง Database และ Monitoring tools ก็จะมีปัญหาเกิดขึ้นมาเพราะ DBA ส่วนใหญ่แม้กระทั่งตัวผมเองไม่มีความรู้ SNMP ทำให้การติดตั้ง SNMP Agent เป็นไปได้อย่างยากลำบาก โดยเฉพาะสำหรับการติดตั้งบนทุก Server ที่เราต้องการ monitor ตัวอย่างปัญหาที่จะเกิดขึ้นก็ได้แก่ restart server แต่ลืม restart agent เป็นต้น หรือในบางกรณีก็จะมีปัญหากับเรื่อง security ที่เราต้องสร้าง user dbsnmp บนทุกๆ database มันจะมีปัญหาว่าถูกบังคับให้เปลี่ยน password ตามช่วงเวลา จะลำบากมากกับการมาคอยเปลี่ยน แต่ว่าที่เล่ามาทั้งหมดได้รับการทำให้สะดวกขึ้นใน Oracle Enterprise Manager 12c ได้ทำให้การใช้ SNMP Agent สะดวกกว่าเดิม แต่ว่า EM 12c มีราคาที่สูงบางองค์กรก็สู้ไม่ใหวเหมือนกัน

ทางเลือกที่เราจะไม่ใช้ SNMP ก็จะต้องมาทำสคริปท์เพื่อ query ค่าออกมาแล้วส่งต่อให้กับ monitoring tools ก็คือสร้าง background service หรือโปรแกรมขึ้นมารันเพื่อ query ทุกๆ 5 นาที หรือตามเวลาที่ได้ตั้งเอาไว้ แบบนี้ก็เรียกว่า Agent แต่ก็เหมือนเดิมคือถ้าลืม start Agent หรือเกิดอาการ hang/non-response ก็หยุดส่งค่าดื้อๆเอาเหมือนกัน ดังนั้นก็จะมีวิธีอีกแบบก็คือไปสร้างสคริปท์หรือ background service ที่ monitoring tools อย่างเดียวแล้วใช้การ remote query ข้อมูลกลับมาที่ปลายทาง วิธีนี้เรียกว่า Agentless


แล้่วผมจะช่วยลูกศิษย์ใช้ Cacti เพื่อ monitoring ระบบ ORACLE Database อย่างไร
จากโจทย์ที่ได้รับมาก็คือนำมาใช้เพื่อเก็บข้อมูลต่างๆเพื่อการตัดสินใจ โดยสิ่งที่จะนำมาเก็บก็คือ Hit ratio ต่างๆและข้อมูลที่เป็นการเข้าไป query จาก database ก็เลยคิดว่าจะใช้ Agentless โดยใช้ Cacti เป็นระบบ monitoring ที่จะรับค่าที่เรา query ออกมาและบันทึกเอาไว้เพื่อใช้สร้างกราฟเอาไว้ทำ report ได้ ซึ่งบอกได้ว่าเหมือนกับเอา Cacti แค่มาเป็นตัวสร้างกราฟเท่านั้นส่วนการ query ออกมาเราจะต้องทำเองทั้งหมด อ่านมาถึงตรงนี้คนที่เคยคิดจะใช้ Cacti มา monitoring ORACLE คงจะทราบดีว่าหากท่าน search google และในเว็บไซต์ของ Cacti เอง จะพบ template สำหรับใช้งาน ORACLE ในระบบ SNMP และแทบไม่ได้บอกวิธีอะไรมากมาย และไม่สอนให้ query ข้อมูลอื่นๆที่ไม่มีใน template ตัวผมเองเป็น DBA มาหลายปีอยากได้เครื่องมือที่สามารถใช้งานก็จนปัญญาเพราะหาวิธีไม่เจอและเครื่องมือที่ดังๆอย่าง Cacti ก็ไม่มีคนทำสคริปท์สำหรับ ORACLE จนในที่สุดผมก็ทำเองได้สำเร็จครับ ซึ่งผมก็จะค่อยเขียนวิธีทำและวิธีใช้งานในลำดับถัดไป