Techblogs

Metric collection for Oracle databases

Terug naar overzicht

17 maart 2023

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