Metric collection for Oracle databases

Metric collection for Oracle databases

Published on: Category: Oracle

How do you create XML files from an Oracle database? And how do you collect and load these files into a reporting database? The answers don’t have to be complicated. In this blog, I will show you how I developed a system that creates, collects, and loads XML files from an Oracle database to a reporting database.

This system mainly focuses on collecting data about Oracle databases, but can also be used to store data from other sources, such as operating systems. In addition, it can be used to report about trends in size and load. 

Once a day, this system can collect data about “anything” you want to report about. For example:

  • sizes of tablespaces and schemas 
  • all kinds of performance information collected from dictionary views like v$sysstat
  • run times from database jobs and backups

The information is collected once a day and stored in XML files. These files are transported to a server with our reporting database, where all the information about various objects is collected. 

But let’s look at the basics first.

Step 1: creating XML files

Creating basic XML files from an Oracle database is actually quite simple when you use the SQL/XML standard functions. To illustrate this, I will write a query to create an XML file with information about the size of the database schemas. At the end of this blog, I’ll give you a few other examples.

The XML file contains 2 parts. First, we have (header) XML elements with general information about where the file comes from, like the database name, server name and timestamp. Next, we add XML elements about the actual data (in this case, the schemas). In other words: the owner and size for each schema.

For the schema sizes, we collect the extent sizes for dabases extents: 

 SELECT e.owner      AS "OWNER"
 ,      SUM(e.bytes) AS "BYTES"
 FROM dba_extents e
 GROUP BY e.owner
 
 
OWNER                   BYTES
------------------ ----------
SYS                1692065792
SYSTEM               14376960
DBSNMP                 196608
METRIC              125042688
FLOWS_FILES           1638400
ORDS_METADATA         1048576
TSTUSR                2416640
APEX_200100         246415360
GSMADMIN_INTERNAL      950272
QMETRIC              13959168
OUTLN                  155648
XDB                 613220352
 
12 ROWS selected.

Next step: translate each row of the result into an XML element. To do this, we use the functions xmlelement and xmlforest.

SELECT xmlelement
  ( SCHEMA
  , xmlforest
    (  e.owner      AS "OWNER"
    ,  SUM(e.bytes) AS "BYTES"
    )
   ) xmldata
FROM   dba_extents e
GROUP  BY e.owner
/
 
XMLDATA
-------------------------------------------------------------------------
<SCHEMA><OWNER>SYS</OWNER><BYTES>1692065792</BYTES></SCHEMA>
<SCHEMA><OWNER>SYSTEM</OWNER><BYTES>14376960</BYTES></SCHEMA>
<SCHEMA><OWNER>DBSNMP</OWNER><BYTES>196608</BYTES></SCHEMA>
<SCHEMA><OWNER>METRIC</OWNER><BYTES>125042688</BYTES></SCHEMA>
<SCHEMA><OWNER>FLOWS_FILES</OWNER><BYTES>1638400</BYTES></SCHEMA>
<SCHEMA><OWNER>ORDS_METADATA</OWNER><BYTES>1048576</BYTES></SCHEMA>
<SCHEMA><OWNER>TSTUSR</OWNER><BYTES>2416640</BYTES></SCHEMA>
<SCHEMA><OWNER>APEX_200100</OWNER><BYTES>246415360</BYTES></SCHEMA>
<SCHEMA><OWNER>GSMADMIN_INTERNAL</OWNER><BYTES>950272</BYTES></SCHEMA>
<SCHEMA><OWNER>QMETRIC</OWNER><BYTES>13959168</BYTES></SCHEMA>
<SCHEMA><OWNER>OUTLN</OWNER><BYTES>155648</BYTES></SCHEMA>
<SCHEMA><OWNER>XDB</OWNER><BYTES>613220352</BYTES></SCHEMA>
 
12 ROWS selected.

Now, we have 12 XML elements, and each is about a schema. Each element has two properties: the owner and the number of bytes. Using XMLAGG, we join these 12 elements into one: 

SET feedback 1
col XMLDATA format a60
 
SELECT xmlagg (xmlelement
   ( SCHEMA
    , xmlforest
     ( e.owner      AS "OWNER"
     , SUM(e.bytes) AS "BYTES"
     )
    )) xmldata
FROM   dba_extents e
GROUP  BY e.owner
/
 
 
XMLDATA
------------------------------------------------------------
<SCHEMA><OWNER>SYS</OWNER><BYTES>1692098560</BYTES></SCHEMA>
<SCHEMA><OWNER>SYSTEM</OWNER><BYTES>14376960</BYTES></SCHEMA
><SCHEMA><OWNER>DBSNMP</OWNER><BYTES>196608</BYTES></SCHEMA>
<SCHEMA><OWNER>METRIC</OWNER><BYTES>125042688</BYTES></SCHEM
A><SCHEMA><OWNER>FLOWS_FILES</OWNER><BYTES>1638400</BYTES></
SCHEMA><SCHEMA><OWNER>ORDS_METADATA</OWNER><BYTES>1048576</B
YTES></SCHEMA><SCHEMA><OWNER>TSTUSR</OWNER><BYTES>2416640</B
YTES></SCHEMA><SCHEMA><OWNER>APEX_200100</OWNER><BYTES>24641
5360</BYTES></SCHEMA><SCHEMA><OWNER>GSMADMIN_INTERNAL</OWNER
><BYTES>950272</BYTES></SCHEMA><SCHEMA><OWNER>QMETRIC</OWNER
><BYTES>13959168</BYTES></SCHEMA><SCHEMA><OWNER>OUTLN</OWNER
><BYTES>155648</BYTES></SCHEMA><SCHEMA><OWNER>XDB</OWNER><BY
TES>613220352</BYTES></SCHEMA>
 
1 ROW selected.

The XML file should also contain header information about the database and instance, so let’s write a query to achieve this. 

SELECT 'SCHEMAS' docname
,      dbid
,      name
,      instance_name
,      host_name
,      db_unique_name
,      to_char(sysdate,'DD-MM-YYYY-HH24:MI:SS') DATE_TIME
FROM   v$database
,      v$instance
/

You can also add extra columns for the header information when needed. If you work with a container database for example, you could add the name of the pluggable database. Make sure you have enough information in the header to identify a unique database. Also include a document name that is used to recognize the structure of the XML when we load it into our monitoring database later.

The header information query is also rewritten in XML format and combined with the XML element SCHEMAS. It’s based on the query for the schema sizes.

SELECT xmlelement("DATABASE",
            xmlelement("DOCNAME",'SCHEMAS'),
            xmlelement("DBID",dbid),
            xmlelement("DBNAME",name),
            xmlelement("INSTANCE_NAME",instance_name),
            xmlelement("HOST_NAME",host_name),
            xmlelement("DB_UNIQUE_NAME",db_unique_name),            xmlelement("DATE_TIME",to_char(sysdate,'DD-MM-YYYY-HH24:MI:SS')),
            xmlelement("SCHEMAS",
                         ( SELECT xmlagg( xmlelement
                             ( SCHEMA
                             , xmlforest
                               ( e.owner      AS "OWNER"
                               , SUM(e.bytes) AS "BYTES"
                             )
                           ))
                           FROM dba_extents e
                           GROUP BY e.owner
                         )
                      ) AS schema
            ) AS xmldata
FROM v$database
,    v$instance
/

When executed, the query results in a single XML document.

XMLDATA
------------------------------------------------------------
<DATABASE><DOCNAME>SCHEMAS</DOCNAME><DBID>1203838370</DBID><
DBNAME>XMIXP</DBNAME><INSTANCE_NAME>XMIXP</INSTANCE_NAME><HO
ST_NAME>orclmetricdbhst</HOST_NAME><DB_UNIQUE_NAME>XMIXP</DB
_UNIQUE_NAME><DATE_TIME>14-10-2021-15:08:24</DATE_TIME><SCHE
MAS><SCHEMA><OWNER>APEX_200100</OWNER><BYTES>246415360</BYTE
S></SCHEMA><SCHEMA><OWNER>DBSNMP</OWNER><BYTES>196608</BYTES
></SCHEMA><SCHEMA><OWNER>FLOWS_FILES</OWNER><BYTES>1638400</
BYTES></SCHEMA><SCHEMA><OWNER>GSMADMIN_INTERNAL</OWNER><BYTE
S>950272</BYTES></SCHEMA><SCHEMA><OWNER>METRIC</OWNER><BYTES
>125042688</BYTES></SCHEMA><SCHEMA><OWNER>ORDS_METADATA</OWN
ER><BYTES>1048576</BYTES></SCHEMA><SCHEMA><OWNER>OUTLN</OWNE
R><BYTES>155648</BYTES></SCHEMA><SCHEMA><OWNER>QMETRIC</OWNE
R><BYTES>13959168</BYTES></SCHEMA><SCHEMA><OWNER>TSTUSR</OWN
ER><BYTES>2416640</BYTES></SCHEMA><SCHEMA><OWNER>SYS</OWNER>
<BYTES>1692098560</BYTES></SCHEMA><SCHEMA><OWNER>SYSTEM</OWN
ER><BYTES>14376960</BYTES></SCHEMA><SCHEMA><OWNER>XDB</OWNER
><BYTES>613220352</BYTES></SCHEMA></SCHEMAS></DATABASE>
 
1 ROW selected.

Schedule a daily script for the query above. If we use the sql spool command with a unique output name and the “.xml” extension, we should have an XML file with the schema sizes. Make sure that each file has a unique name. This can be done by including the timestamp, object name and document name in the file name. 

When creating the file, it is important that the complete XML is on one line. You cannot have line breaks in the file, so you must use a large line-size in SQL Plus or Linux commands like sed or xargs to remove any line breaks.

Step 2: collect the XML files

All XML files should be loaded into an Oracle database. To do this, we need to collect the XML files daily. 

Copy the XML file to the server where we collect all our information. Depending on your situation, you can use scp, email, FTP or any other protocol to collect the files. All XML files for all monitored databases can be stored in one directory.

Step 3: read XML into the database staging table

All files can be loaded into the reporting database with SQL Loader. At this point, it isn’t necessary to know anything about the actual structure of the XML files. We just need to create a staging table that can contain XML data:  

CREATE TABLE STAGE_XMLDATA
(filename varchar2(120)
, XMLDATA SYS.XMLTYPE ) 
;

The script for loading XML files has several steps. We use a bash shell script in combination with SQL Loader and SQL Plus.

The basic script:

  • makes a list of all XML files in the LOAD directory
  • creates a SQL Loader control file 
  • starts SQL Loader to load all XML files in the staging table
# ##################################
# specify directories
# ##################################
 
HOMEDIR=/home/oracle/scripts/load_data
LOAD_DIR=/u01/xmldir
INBOX=/u01/metrics/attachments
 
# ##################################
# specify file names
# ##################################
 
LST_FILE=${HOMEDIR}/tmp/load_${TIMESTAMP}.txt
CTL_FILE=${HOMEDIR}/tmp/load_${TIMESTAMP}.ctl
LOG_FILE=${HOMEDIR}/log/load_${TIMESTAMP}.log
BAD_FILE=${HOMEDIR}/log/load_${TIMESTAMP}.bad
SQL_LOG_FILE=${HOMEDIR}/log/load_${TIMESTAMP}.sql_log
 
# ##################################
# specify DATABASE CONNECT string
# ##################################
 
CONNECT_STRING="qmetric/secretpasswd@myserver:1521/METRICDB"
 
# ##################################
# FIRST we need TO collect the files INTO one directory, 
# AND CREATE a list containing ALL the file names.
# ##################################
 
find ${LOAD_DIR} -TYPE f -name ‘*.xml’  | sort  > ${LST_FILE}
 
# ##################################
# CREATE an SQL loader control file 
# ##################################
 
cat <<EOF > ${CTL_FILE}
LOAD DATA
INFILE '${LST_FILE}'
REPLACE
INTO TABLE STAGE_XMLDATA
(
 filename filler CHAR(120),
 XMLDATA  lobfile(filename) TERMINATED BY eof
)
EOF
 
# #############################
# LOAD files WITH SQL loader
# #############################
 
sqlldr userid=${CONNECT_STRING} control=${CTL_FILE}  log=${LOG_FILE} bad=${BAD_FILE}
 

Using SQL Loader, the XML files are loaded into the STAGE_XMLDATA table. Each XML file results in one row.

To see the XML again, simply query the table: 

SELECT XMLDATA FROM STAGE_XMLDATA
/
 
XMLDATA
-----------------------------------------------------------------------
<DATABASE>
  <DOCNAME>SCHEMAS</DOCNAME>
  <DBID>1203838370</DBID>
  <DBNAME>XMIXP</DBNAME>
  <INSTANCE_NAME>XMIXP</INSTANCE_NAME>
  <HOST_NAME>orclmetricdbhst</HOST_NAME>
  <DB_UNIQUE_NAME>XMIXP</DB_UNIQUE_NAME>
  <DATE_TIME>14-10-2021-10:40:00</DATE_TIME>
  <SCHEMAS>
    <SCHEMA>
      <OWNER>APEX_200100</OWNER>
      <BYTES>246415360</BYTES>
    </SCHEMA>
    <SCHEMA>
      <OWNER>DBSNMP</OWNER>
      <BYTES>196608</BYTES>
    </SCHEMA>
    <SCHEMA>
      <OWNER>FLOWS_FILES</OWNER>
      <BYTES>1638400</BYTES>
    </SCHEMA>
    <SCHEMA>
      <OWNER>GSMADMIN_INTERNAL</OWNER>
      <BYTES>950272</BYTES>
    </SCHEMA>
    <SCHEMA>
      <OWNER>METRIC</OWNER>
      <BYTES>106233856</BYTES>
    </SCHEMA>
    <SCHEMA>
      <OWNER>ORDS_METADATA</OWNER>
      <BYTES>1048576</BYTES>
    </SCHEMA>
    <SCHEMA>
      <OWNER>OUTLN</OWNER>
      <BYTES>155648</BYTES>
    </SCHEMA>
    <SCHEMA>
      <OWNER>QMETRIC</OWNER>
      <BYTES>13959168</BYTES>
    </SCHEMA>
    <SCHEMA>
      <OWNER>TSTUSR</OWNER>
      <BYTES>2416640</BYTES>
    </SCHEMA>
    <SCHEMA>
      <OWNER>SYS</OWNER>
      <BYTES>1555423232</BYTES>
    </SCHEMA>
    <SCHEMA>
      <OWNER>SYSTEM</OWNER>
      <BYTES>14376960</BYTES>
    </SCHEMA>
    <SCHEMA>
      <OWNER>XDB</OWNER>
      <BYTES>613220352</BYTES>
    </SCHEMA>
  </SCHEMAS>
</DATABASE>
 
1 ROW selected.

Step 4: convert XML back to normal data

All XML files are now loaded into the STAGE_XMLDATA table. Our next step is to convert the data into a normal format, so it can be stored into the history tables. These contain the daily size of all schemas. 

In the first step, we included a document name in each XML file. This name is used to recognize the structure for each file, as there can be multiple scripts to create XML files. For each unique document, we now create an SQL script to move data from the staging table to the history tables. In this case, we query rows with docname = 'SCHEMAS'

The actual conversion from XML to a normal table format is done using the XMLTable function. The column names here match with the XML tags used when we created the XML files. At this point, you also need to do a data type conversion for all the columns you want to store in a number or date format. For readability, I left out some of the header columns.

SELECT DBID
,      DBNAME
,      to_date(DATE_TIME,'DD-MM-YYYY-HH24:MI:SS')  DATE_TIME
,      OWNER
,      to_number(BYTES)  BYTES
FROM   stage_xmldata x,
           XMLTABLE('/DATABASE'
             PASSING x.xmldata
             COLUMNS
               DBID           VARCHAR2(20)  PATH 'DBID',
               DBNAME         VARCHAR2(20)  PATH 'DBNAME',
               DATE_TIME      VARCHAR2(20)  PATH 'DATE_TIME',
               DOCNAME        VARCHAR2(20)  PATH 'DOCNAME'
             )
,          XMLTABLE('/DATABASE/SCHEMAS/SCHEMA'
             PASSING x.xmldata
             COLUMNS
               OWNER           VARCHAR2(20)  PATH 'OWNER',
               BYTES           VARCHAR2(40)  PATH 'BYTES'
             )
WHERE docname = 'SCHEMAS'
ORDER BY DATE_TIME
/
 
DBID                 DBNAME  DATE_TIME OWNER                     BYTES
-------------------- ------- --------- -------------------- ----------
1203838370           XMIXP   14-OCT-21 APEX_200100           246415360
1203838370           XMIXP   14-OCT-21 DBSNMP                   196608
1203838370           XMIXP   14-OCT-21 FLOWS_FILES             1638400
1203838370           XMIXP   14-OCT-21 GSMADMIN_INTERNAL        950272
1203838370           XMIXP   14-OCT-21 METRIC                106233856
1203838370           XMIXP   14-OCT-21 XDB                   613220352
1203838370           XMIXP   14-OCT-21 OUTLN                    155648
1203838370           XMIXP   14-OCT-21 QMETRIC                13959168
1203838370           XMIXP   14-OCT-21 TSTUSR                  2416640
1203838370           XMIXP   14-OCT-21 SYS                  1555423232
1203838370           XMIXP   14-OCT-21 SYSTEM                 14376960
1203838370           XMIXP   14-OCT-21 ORDS_METADATA           1048576
 
12 ROWS selected.

The output from this query can be used to fill the history tables:

insert into history_schemas
select …..

Other examples

As promised, here are a few other examples of creating XML files from the database or the operating system.

How to create an XML element for all physical IO:

SELECT xmlagg (xmlelement
        (PHYSICAL_IO
        , xmlforest
          ( sn.name     AS "NAME"
          , stat.value  AS "VALUE"
          )
        )) XML
FROM  v$statname  sn
,     v$sysstat   stat
WHERE sn.name LIKE 'physical%'
AND   sn.STATISTIC# = stat.STATISTIC#
/
 
XML
----------------------------------------
<PHYSICAL_IO><NAME>physical READ total I
O requests</NAME><VALUE>34392312</VALUE>
</PHYSICAL_IO><PHYSICAL_IO><NAME>physica
l READ total multi block requests</NAME>
<VALUE>1107651</VALUE></PHYSICAL_IO><PHY
SICAL_IO><NAME>physical READ requests op
timized</NAME><VALUE>0</VALUE></PHYSICAL
_IO><PHYSICAL_IO><NAME>physical READ tot
al bytes optimized</NAME><VALUE>0</VALUE

How to create an XML element for the run times of database jobs:

SELECT  xmlagg ( xmlelement
           ( JOBRUN
           , xmlforest
            ( trunc(ACTUAL_START_DATE)  start_date
            , owner
            , job_name
            , run_duration
            )
           )) JOBRUNS
FROM   dba_scheduler_job_run_details
WHERE trunc(ACTUAL_START_DATE) = trunc(sysdate - 1)
/
 
JOBRUNS
----------------------------------------
<JOBRUN><START_DATE>2021-10-14</START_DA
TE><OWNER>ORACLE_OCM</OWNER><JOB_NAME>MG
MT_CONFIG_JOB_1</JOB_NAME><RUN_DURATION>
+000 00:00:00</RUN_DURATION></JOBRUN><JO
BRUN><START_DATE>2021-10-14</START_DATE>
<OWNER>SYS</OWNER><JOB_NAME>ORA$AT_OS_OP
T_SY_81711</JOB_NAME><RUN_DURATION>+000
00:09:10</RUN_DURATION></JOBRUN><JOBRUN>
<START_DATE>2021-10-14</START_DATE><OWNE
[....]

The following example is a script that can be used to create an XML file about server statistics. In this case, swapping information. 

SWAP_IN=`vmstat -s |grep "pages swapped in" |sed 's/pages swapped in//' |sed 's/ //g'`
SWAP_OUT=`vmstat -s|grep "pages swapped out"|sed 's/pages swapped out//'|sed 's/ //g'`
TIME=`date +%H:%M:%S`
DATE=`date +%d-%m-%Y`
 
#create XML file
 
cat <<EOF > ${XMLFILE}
<METRIC>
<DOCNAME>${DOCNAME}</DOCNAME>
<DATE>${DATE}</DATE>
<TIME>${TIME}</TIME>
<HOST_NAME>${SERVER}</HOST_NAME>
<SWAPDATA>
<SWAP_IN>${SWAP_IN}</SWAP_IN>
<SWAP_OUT>${SWAP_OUT}</SWAP_OUT>
</SWAPDATA>
</METRIC>
EOF
Bastiaan Bak
About the author Bastiaan Bak

DBA with over 15 years of experience. Experience in various branches, with several modules. Including: Oracle database, Oracle RAC, Oracle EBS and PL/SQL.

More posts by Bastiaan Bak
Comments (1)
  1. om 13:01

    Leuk om te lezen!

Reply