Pythian Group

Subscribe to Pythian Group feed
Updated: 2 hours 30 min ago

Datascape Podcast: Episode 16 – Recapping Oracle OpenWorld 2017

Thu, 2017-10-19 08:26

Today we’re going to recap some of the most interesting concepts from the recently completed Oracle OpenWorld 2017 and to help us navigate this I have invited Simon Pane back to the show. Simon is an accomplished Principal Consultant, who has developed a multitude of complex solutions for Pythian clients. He leverages his understanding of the industry and technologies such as Oracle, SQL Server, Linux, Oracle Cloud, AWS and more, to propose timely solutions that best suit the needs of clients. Also joining us for the first time is Ivica Arsov. Ivica is an Oracle Certified Master 12c and 11g, and a recognized member of the Oracle ACE Program as an Oracle ACE Associate. He is a blogger and active contributor to the Oracle community and presents at many technology conferences. Known for his deep Oracle expertise and ability to troubleshoot quickly and efficiently, Ivica has the skills to solve problems quickly regardless of size and complexity. Keep listening to hear more!


Key points from this episode:

  • Simon tells us more about his career at Pythian and the work he is doing with Oracle.
  • Ivica shares with us on the work he is doing with Oracle while working at Pythian.
  • The atmosphere at OOW this year.
  • The common theme of automation and security.
  • Competitive threats versus security threats.
  • The new version of the Oracle database, 18c.
  • Improvements in reduction of danger surrounding patching by balancing risk.
  • The new autonomous feature and the NRX guarantee.
  • Elasticity and automatic scaling.
  • DBA’s focusing on tasks specific to the business in the world of automation.
  • The trade-off of simplicity versus index.
  • Machine learning being introduced with cyber security.
  • New features coming out with 18c and 19c releases and architecture versions.
  • Understanding how customers are at the mercy of vendors.
  • Certifications bundled to a number of versions.
  • Industry problems around tools and security assessment.
  • Hear more about Ivica and some of his favorites in the lightning round.
  • Simon tells us more about the UK Oracle User Group Conference taking place.
  • And much more!

Links Mentioned in Today’s Episode:

Oracle OpenWorld 2017
Simon Pane
Ivica Arsov
Pythian
Amazon 
Microsoft
Oracle
Microsoft Azure
Dataguard
Rac
The book, Transaction Processing: Concepts and Techniques
UK Oracle User Group

Categories: DBA Blogs

Log Buffer #522: A Carnival of the Vanities for DBAs

Mon, 2017-09-18 10:22

This edition of Log Buffer covers Cloud, Oracle, SQL Server and MySQL and much more.

Cloud:

Introducing managed SSL for Google App Engine

Using Cloud Foundry CUPS to inject Spring Security credentials into a Spring Boot Application

ClusterControl in the Cloud – All Our Resources

Monitoring Amazon Aurora Audit Events with Amazon CloudWatch

Integrating Teradata with Amazon Redshift Using the AWS Schema Conversion Tool

Oracle:

Demo App for REST enabled SQL

The Insurance of Oracle APEX – Can Your Low Code Framework Do This?

Oracle GoldenGate is now avaliable on Docker! All the details you need to get up and running on an image is included in Oracle’s public github.

Assign Pseudo Random Password for Oracle DB Users

Simple regexp to check that string contains word1 and word2 and doesn’t contain word3

SQL Server:

Automate MSBI deployments Using PowerShell

NEW – Machine Learning with SQL Server 2017 and R Tutorial

Optional Parameters and Missing Index Requests

Moving Dynamic Data Masked Data to New Locations

Indexing for Windowing Functions: WHERE vs. OVER

MySQL:

Lock, Stock and MySQL Backups: Data Guaranteed Webinar Follow Up Questions

gh-ost 1.0.42 released: JSON support, optimizations

Massive Parallel Log Processing with ClickHouse

cscope: Searching Code Efficiently

Updating InnoDB Table Statistics Manually

Categories: DBA Blogs

Log Buffer #521: A Carnival of the Vanities for DBAs

Tue, 2017-09-12 11:36

While cloud technologies are roaring ahead in full force; the traditional RDBMS like Oracle, Microsoft SQL Server and MySQL are adapting pretty fast. This Log Buffer Edition covers blogs related to that and more.

Oracle:

Oracle JET Simple Table with Fake JSON Server

Every time any page is requested from your website by a human or another program or an automated bot, the event is tracked in a log file that is stored on the web server.

Gitora 2 enabled developers to manage database objects that support the CREATE OR REPLACE command, namely PACKAGES, PROCEDURES, FUNCTIONS, TRIGGERS, VIEWS, SYNONYMS and TYPES in a Git repository.

Oracle just released its first REST JDBC driver on OTN, in conjunction with the 17.3.0 Oracle REST Data Services Beta release.

When you are restrained in the JRE that you can use with SQLcl, you can embed your own in the sqlcl directory tree.

SQL Server:

Understanding Azure Data Factory – A Cloud Based Integration Service

The Shortcomings of Predictive Accuracy

Setting Variables in Calling T-SQL Code While Using sp_executesql

Azure DWH part 15: PolyBase and Azure Data Lake

Creating Basic Indexes on the Stack Overflow Public Database

MySQL:

Heads Up: The List of Replication Defaults That Have Changed in 8.0.2

IO-bound table scan performance for MyRocks, InnoDB and TokuDB

MyRocks Experimental Now Available with Percona Server for MySQL 5.7.19-17

Timing load & index for sysbench tables

Automatic Partition Maintenance in MariaDB

Categories: DBA Blogs

Oracle OpenWorld 2017 Bloggers Meetup

Tue, 2017-09-05 18:41

DON’T PANIC. Yes, we are doing the OOW Bloggers Meetup this year. Yes, it’s the same time. Yes, it’s the same location. Yes, it’s more fun every year.

What: Oracle Bloggers Meetup 2017.

When: Wed, 04-Oct-2017, 5:30pm.

Where: Main Dining Room, Jillian’s Billiards @ Metreon, 175 Fourth Street, San Francisco, CA 94103 (street view). Yes, it’s a good time to try taking our blog down with DoS attack of “COUNT ME IN” comments below – if you’re coming, comment away!

As usual Pythian sponsors the venue, drinks and cool fun social stuff. The competition is on — you know what that means (new unique swag this year) and if not, come and learn. All blogger community participants are invited (self qualifying).

As usual, vintage t-shirts, ties, or bandanas from previous meetups will make you look extremely cool — feel free to wear them. The new wearable will be a nice addition to your collection!

For those of you who don’t know the history: The Bloggers Meetup during Oracle OpenWorld was started by Mark Rittman and continued by Eddie Awad. Alex Gorbachev picked up the flag in 2009 and delegated to me in 2017.

The meetups have been a great success for making new friends and catching up with the old, so let’s keep them this way! To give you an idea, here are some photos and a video of past events.

All Oracle technologies — Oracle Database, MySQL, Apps, Sun technologies, Java and ALL THE CLOUD PEEPS — come and meet like-minded “techies”. All bloggers are welcome. We’ve reached 150 attendees in the last few years — maybe another record breaking gathering this year.

If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us ensure we have the attendance numbers right. Please provide your blog URL with your comment — it’s a Bloggers Meetup after all! Please do make sure you comment here if you are attending so that we have enough room, food, and (most important) drinks.

And even if you choose to not attend the meetup, check out all Oracle OpenWorld sessions with Pythian speakers and book your seat in these sessions today.

And you can now fill your twitter feeds (linkedin, G+, instagram, mailing lists) by talking about how excited you are to attend, and talk about this year’s meetup with fellow bloggers you’ll meet at the show. See you all there — it will be fun!

Categories: DBA Blogs

Chart Your Course With sar

Tue, 2017-08-15 12:02

The sar (system activity reporter) is a Linux utility that actually started life long ago in System V Unix.

In spite of its ancient origins, sar is still quite useful, and has even been enhanced in recent versions of Linux.

The use of sar is not limited to root – any user can run sar and its utilities.  Most Linux systems have sar installed and enabled by default to collect sar data at 10 minute intervals and retain that data for 10 days.

The reports output by sar will look familiar:

 

jkstill@poirot ~/tmp $ sar -d | head -20
Linux 4.4.0-53-generic (poirot.jks.com)         08/07/17        _x86_64_        (1 CPU)

00:00:01          DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
00:02:01       dev7-0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:02:01       dev7-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:02:01       dev7-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:02:01       dev7-3      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:02:01       dev7-4      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:02:01       dev7-5      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:02:01       dev7-6      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:02:01       dev8-0      0.40      0.00      4.97     12.43      0.00      0.26      0.26      0.01
00:02:01     dev252-0      0.63      0.00      4.90      7.78      0.00      0.16      0.16      0.01
00:02:01     dev252-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:04:01       dev7-0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:04:01       dev7-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:04:01       dev7-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:04:01       dev7-3      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:04:01       dev7-4      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:04:01       dev7-5      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:04:01       dev7-6      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

This report format is good for a quick look at different metrics to get an overall idea of system perform, disk metrics in the previously shown example.

However what if you would like to collate this data into something more readily consumed for charting an analysis, such as a CSV file?  The standard output of sar does not readily lend itself to such use without quite a bit of work.

The sar utility is part of the Linux  sysstat package and contains not only sar, but sadf.  If sar is available then so is sadf. What is sadf you say?  sadf is a the sar data formatting utility, and can output data in CSV, XML and other formats.

Using sadf a bash script can be created that will extract sar data for many Linux subsystems and write that data in the format of your choice, CSV in this case.

The asp.sh script does just that, creating CSV files that can then be used for charting and analysis.

 

#!/bin/bash

# Jared Still - Pythian
# still@pythian.com jkstill@gmail.com
# 2017-07-20

# asp - another sar processor

# tested on Red Hat Enterprise Linux Server release 6.6 (Santiago)
# also tested on Linux Mint

help() {
	echo
	echo $0 -s source-dir -d dest-dir
	echo
}



# variables can be set to identify multiple sets of copied sar files
sarSrcDir='/var/log/sa' # RedHat, CentOS ...
#sarSrcDir='/var/log/sysstat' # Debian, Ubuntu ...

sarDstDir="sar-csv"

csvConvertCmd=" sed -e 's/;/,/g' "


while getopts s:d:h arg
do
	case $arg in
		d) sarDstDir=$OPTARG;;
		s) sarSrcDir=$OPTARG;;
		h) help; exit 0;;
		*) help; exit 1;;
	esac
done


cat << EOF

Source: $sarSrcDir
  Dest: $sarDstDir

EOF

#exit


mkdir -p $sarDstDir || {

	echo 
	echo Failed to create $sarDstDir
	echo
	exit 1

}

echo "lastSaDirEl: $lastSaDirEl"


# sar options
# -d activity per block device
  # -j LABEL: use label for device if possible. eg. sentryoraredo01 rather than /dev/dm-3
# -b IO and transfer rates
# -q load
# -u cpu
# -r memory utilization
# -R memory
# -B paging
# -S swap space utilization
# -W swap stats
# -n network
# -v kernel filesystem stats
# -w  context switches and task creation
#sarDestOptions=( '-d -j LABEL -p' '-b' '-q' '-u ALL' '-r' '-R' '-B' '-S' '-W' '-n DEV,EDEV,NFS,NFSD,SOCK,IP,EIP,ICMP,EICMP,TCP,ETCP,UDP' '-v' '-w')
# break up network into a separate file for each option
# not all options available depending on sar version
# for disk "-d" you may want one of ID, LABEL, PATH or UUID - check the output and the sar docs
sarDestOptions=( '-d -j ID -p' '-b' '-q' '-u ALL' '-r' '-R' '-B' '-S' '-W' '-n DEV' '-n EDEV' '-n NFS' '-n NFSD' '-n SOCK' '-n IP' '-n EIP' '-n ICMP' '-n EICMP' '-n TCP' '-n ETCP' '-n UDP' '-v' '-w')

#sarDestFiles=( sar-disk.csv sar-io.csv sar-load.csv sar-cpu.csv sar-mem-utilization.csv sar-mem.csv sar-paging.csv sar-swap-utilization.csv sar-swap-stats.csv sar-network.csv sar-kernel-fs.csv sar-context.csv)
sarDestFiles=( sar-disk.csv sar-io.csv sar-load.csv sar-cpu.csv sar-mem-utilization.csv sar-mem.csv sar-paging.csv sar-swap-utilization.csv sar-swap-stats.csv sar-net-dev.csv sar-net-ede.csv sar-net-nfs.csv sar-net-nfsd.csv sar-net-sock.csv sar-net-ip.csv sar-net-eip.csv sar-net-icmp.csv sar-net-eicmp.csv sar-net-tcp.csv sar-net-etcp.csv sar-net-udp.csv sar-kernel-fs.csv sar-context.csv)

lastSarOptEl=${#sarDestOptions[@]}
echo "lastSarOptEl: $lastSarOptEl"

#while [[ $i -lt ${#x[@]} ]]; do echo ${x[$i]}; (( i++ )); done;

# initialize files with header row
i=0
while [[ $i -lt $lastSarOptEl ]]
do
	CMD="sadf -d -- ${sarDestOptions[$i]}  | head -1 | $csvConvertCmd > ${sarDstDir}/${sarDestFiles[$i]} "
	echo CMD: $CMD
	eval $CMD
	#sadf -d -- ${sarDestOptions[$i]}  | head -1 | $csvConvertCmd > ${sarDstDir}/${sarDestFiles[$i]}
	echo "################"
	(( i++ ))
done

#exit

#for sarFiles in ${sarSrcDirs[$currentEl]}/sa??
for sarFiles in $(ls -1dtar ${sarSrcDir}/sa??)
do
	for sadfFile in $sarFiles
	do

		#echo CurrentEl: $currentEl
		# sadf options
		# -t is for local timestamp
		# -d : database semi-colon delimited output

		echo Processing File: $sadfFile

		i=0
		while [[ $i -lt $lastSarOptEl ]]
		do
			CMD="sadf -d -- ${sarDestOptions[$i]} $sadfFile | tail -n +2 | $csvConvertCmd  >> ${sarDstDir}/${sarDestFiles[$i]}"
			echo CMD: $CMD
			eval $CMD
			if [[ $? -ne 0 ]]; then
				echo "#############################################
				echo "## CMD Failed"
				echo "## $CMD"
				echo "#############################################

			fi
			(( i++ ))
		done

	done
done


echo
echo Processing complete 
echo 
echo files located in $sarDstDir
echo 


# show the files created
i=0
while [[ $i -lt $lastSarOptEl ]]
do
	ls -ld ${sarDstDir}/${sarDestFiles[$i]} 
	(( i++ ))
done

 

Following is an example execution of the script:

 

jkstill@poirot ~/pythian/blog/sar-tools-blog $ ./asp.sh -s /var/log/sysstat

Source: /var/log/sysstat
  Dest: sar-csv

lastSaDirEl:
lastSarOptEl: 23
CMD: sadf -d -- -d -j ID -p | head -1 | sed -e 's/;/,/g' > sar-csv/sar-disk.csv
################
CMD: sadf -d -- -b | head -1 | sed -e 's/;/,/g' > sar-csv/sar-io.csv
################
CMD: sadf -d -- -q | head -1 | sed -e 's/;/,/g' > sar-csv/sar-load.csv
################
CMD: sadf -d -- -u ALL | head -1 | sed -e 's/;/,/g' > sar-csv/sar-cpu.csv
################
CMD: sadf -d -- -r | head -1 | sed -e 's/;/,/g' > sar-csv/sar-mem-utilization.csv
################
CMD: sadf -d -- -R | head -1 | sed -e 's/;/,/g' > sar-csv/sar-mem.csv
################
CMD: sadf -d -- -B | head -1 | sed -e 's/;/,/g' > sar-csv/sar-paging.csv
################
CMD: sadf -d -- -S | head -1 | sed -e 's/;/,/g' > sar-csv/sar-swap-utilization.csv
################
CMD: sadf -d -- -W | head -1 | sed -e 's/;/,/g' > sar-csv/sar-swap-stats.csv
################
CMD: sadf -d -- -n DEV | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-dev.csv
################
CMD: sadf -d -- -n EDEV | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-ede.csv
################
CMD: sadf -d -- -n NFS | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-nfs.csv
################
CMD: sadf -d -- -n NFSD | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-nfsd.csv
################
CMD: sadf -d -- -n SOCK | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-sock.csv
################
CMD: sadf -d -- -n IP | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-ip.csv
Requested activities not available in file /var/log/sysstat/sa07
################
CMD: sadf -d -- -n EIP | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-eip.csv
Requested activities not available in file /var/log/sysstat/sa07
################
CMD: sadf -d -- -n ICMP | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-icmp.csv
Requested activities not available in file /var/log/sysstat/sa07
################
CMD: sadf -d -- -n EICMP | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-eicmp.csv
Requested activities not available in file /var/log/sysstat/sa07
################
CMD: sadf -d -- -n TCP | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-tcp.csv
Requested activities not available in file /var/log/sysstat/sa07
################
CMD: sadf -d -- -n ETCP | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-etcp.csv
Requested activities not available in file /var/log/sysstat/sa07
################
CMD: sadf -d -- -n UDP | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-udp.csv
Requested activities not available in file /var/log/sysstat/sa07
################
CMD: sadf -d -- -v | head -1 | sed -e 's/;/,/g' > sar-csv/sar-kernel-fs.csv
################
CMD: sadf -d -- -w | head -1 | sed -e 's/;/,/g' > sar-csv/sar-context.csv
################
Processing File: /var/log/sysstat/sa30
CMD: sadf -d -- -d -j ID -p /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-disk.csv
CMD: sadf -d -- -b /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-io.csv
CMD: sadf -d -- -q /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-load.csv
CMD: sadf -d -- -u ALL /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-cpu.csv
CMD: sadf -d -- -r /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-mem-utilization.csv
CMD: sadf -d -- -R /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-mem.csv
CMD: sadf -d -- -B /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-paging.csv
CMD: sadf -d -- -S /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-swap-utilization.csv
CMD: sadf -d -- -W /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-swap-stats.csv
CMD: sadf -d -- -n DEV /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-dev.csv
CMD: sadf -d -- -n EDEV /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-ede.csv
CMD: sadf -d -- -n NFS /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-nfs.csv
CMD: sadf -d -- -n NFSD /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-nfsd.csv
CMD: sadf -d -- -n SOCK /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-sock.csv
CMD: sadf -d -- -n IP /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-ip.csv
Requested activities not available in file /var/log/sysstat/sa30
CMD: sadf -d -- -n EIP /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-eip.csv
Requested activities not available in file /var/log/sysstat/sa30
CMD: sadf -d -- -n ICMP /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-icmp.csv
Requested activities not available in file /var/log/sysstat/sa30
...

CMD: sadf -d -- -v /var/log/sysstat/sa07 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-kernel-fs.csv
CMD: sadf -d -- -w /var/log/sysstat/sa07 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-context.csv

Processing complete

files located in sar-csv

Note that for some commands the result is Requested activities not available.  What this means is that we have asked for something sar may be capable of collecting, but in this case there is no data. It may be that sar has not been configured to collect that particular metric. These messages can be ignored unless, of course, it is for a metric you would like to see. (sar configuration is outside the scope of this article)

Let’s break down the following sadf command line

sadf -d -- -d -j ID -p /var/log/sysstat/sa05 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-disk.csv
The sadf ‘-d’ option

This option tells sadf to output data in  CSV format.  As CSV is an acronym for Comma-Separated-Values, the use of a semi-colon as a delimiter may at first seem a little puzzling.  However it makes sense to use something other than a comma, as it may be that a comma could appear in the output.  If that were the case sar would need to then surround the data with quotes.  While many programs can deal with data that may or may not be quoted, it is just simpler to use a semi-colon, which is unlikely to appear in data seen in sar.

The ‘–‘ option

This option doesn’t appear to be too well known. When used on the shell command line, — signifies the end of arguments for the program. A typical use might be if you were using grep to search for ‘-j’ in some text files.  This following command fails due to grep interpreting the text we want to search as an argument. Since there is no ‘-j’ argument for grep, the command fails.

 

jkstill@poirot ~/pythian/blog/sar-tools-blog $ grep '-j' *.sh
grep: invalid option -- 'j'
Usage: grep [OPTION]... PATTERN [FILE]...
Try 'grep --help' for more information.

 

Using ‘–‘ tells the shell to stop processing arguments for the current command, which is grep in this case, and so grep searches for ‘-j’ as intended.

jkstill@poirot ~/pythian/blog/sar-tools-blog $ grep -- '-j' *.sh
  # -j LABEL: use label for device if possible. eg. sentryoraredo01 rather than /dev/dm-3
#sarDestOptions=( '-d -j LABEL -p' '-b' '-q' '-u ALL' '-r' '-R' '-B' '-S' '-W' '-n DEV,EDEV,NFS,NFSD,SOCK,IP,EIP,ICMP,EICMP,TCP,ETCP,UDP' '-v' '-w')
sarDestOptions=( '-d -j ID -p' '-b' '-q' '-u ALL' '-r' '-R' '-B' '-S' '-W' '-n DEV' '-n EDEV' '-n NFS' '-n NFSD' '-n SOCK' '-n IP' '-n EIP' '-n ICMP' '-n EICMP' '-n TCP' '-n ETCP' '-n UDP' '-v' '-w')

Why in this case are we using ‘–‘?  It is because the other options following -d are not for sadf, but are sar options that sadf will use when calling sar.

The ‘-d’, ‘-j’ and ‘-p’ options

Be reading the sar man page you can learn what each of these are:

  • -d block device activity
  • -j display persistent device names – options are ID, LABEL, PATH and UUID
  • -p pretty print device names
tail -n +2

Just get the rows after the header row.  asp.sh calls sadf for each day’s sar data.  The first step of creating the header has already been done.

 

sed

sed is being used to change semi-colons to commas.  So far I have not seen any commas in sar data, and using a comma delimiter is just more convenient.

 

Charting in Excel with Perl

 

Having the sar data in the CSV format is convenient, as it will load directly into Excel or Google Sheets. What isn’t convenient is all the work required to load the data, format it, and then create multiple charts from the data.  There could be a number of CSV files this needs to be done with.

Perl to the rescue!  The Excel::Writer::XLSX Perl module can directly create formatted Excel files, complete with charts. I am going to make some basic use of this quite capable module via the dynachart.pl Perl script. This script reads CSV data and creates Excel files, optionally with line charts of selected columns.

If you would like to see some simple examples of using Perl to create Excel files, the documentation for the Perl Module Writer::Excel::XLSX contains several good examples, such as this one:  Example-1

There are several others to be found in the documentation for  Excel::Writer::XLSX

Following is the entire script.  This script and others can be found at the csv-tools github repo.

#!/usr/bin/env perl

# dynachart.pl
# Jared Still 2017-07-23
# still@pythian.com jkstill@gmail.com

# data is from STDIN

use warnings;
use strict;
use Data::Dumper;
use Pod::Usage;
use Getopt::Long;
use Excel::Writer::XLSX;

my $debug = 0;
my $combinedChart = 0;
my %optctl = ();
my ($help,$man);
my @chartCols;
my $categoryColName='';
my $categoryColNum=0;

Getopt::Long::GetOptions(
	\%optctl, 
	'spreadsheet-file=s',
	'debug!' => \$debug,
	'chart-cols=s{1,10}' => \@chartCols,
	'combined-chart!' => \$combinedChart,
	'worksheet-col=s',  # creates a separate worksheet per value of this column
	'category-col=s' => \$categoryColName,
	'h|help|?' => \$help, man => \$man
) or pod2usage(2) ;

pod2usage(1) if $help;
pod2usage(-verbose => 2) if $man;

my $xlFile = defined($optctl{'spreadsheet-file'}) ? $optctl{'spreadsheet-file'} : 'asm-metrics.xlsx';
my $workSheetCol = defined($optctl{'worksheet-col'}) ? $optctl{'worksheet-col'} : 0;

my %fonts = (
	fixed			=> 'Courier New',
	fixed_bold	=> 'Courier New',
	text			=> 'Arial',
	text_bold	=> 'Arial',
);

my %fontSizes = (
	fixed			=> 10,
	fixed_bold	=> 10,
	text			=> 10,
	text_bold	=> 10,
);

my $maxColWidth = 50;
my $counter = 0;
my $interval = 100;

# create workbook
my $workBook = Excel::Writer::XLSX->new($xlFile);
die "Problems creating new Excel file $xlFile: $!\n" unless defined $workBook;

# create formats
my $stdFormat = $workBook->add_format(bold => 0,font => $fonts{fixed}, size => $fontSizes{fixed}, color => 'black');
my $boldFormat = $workBook->add_format(bold => 1,font => $fonts{fixed_bold}, size => $fontSizes{fixed_bold}, color => 'black');
my $wrapFormat = $workBook->add_format(bold => 0,font => $fonts{text}, size => $fontSizes{text}, color => 'black');
$wrapFormat->set_align('vjustify');


my $labels=<>;
chomp $labels;
# sadf starts header lines with '# ' - remove that
$labels =~ s/^#\s+//;
my @labels = split(/,\s*/,$labels);

if ($debug) {

print qq{LABELS:\n};

print join("\n",@labels);

print "\n";

}

# get the X series category
if ( $categoryColName ) {

	my $want = $categoryColName;
	my $index = 0;
	++$index until ($labels[$index] eq $want) or ($index > $#labels);
	$categoryColNum = $index;	

}

#print join("\n",@labels);

# get the element number of the column used to segregate into worksheets
my $workSheetColPos;
if ($workSheetCol) {
	my $i=0;
	foreach my $label ( @labels)  {
		if ($label eq $workSheetCol) { 
			$workSheetColPos = $i;
			last;
		}
		$i++;
	}
}

print "\nworkSheetColPos: $workSheetColPos\n" if $debug;

# validate the columns to be charted
# use as an index into the labels
my @chartColPos=();
{
	my $i=0;
	foreach my $label ( @labels)  {
		foreach my $chartCol ( @chartCols ) {
			if ($label eq $chartCol) { 
				push @chartColPos, $i;
				last;
			}
		}
		$i++;
	}
}

if ($debug) {
	print "\nChartCols:\n", Dumper(\@chartCols);
	print "\nChartColPos:\n", Dumper(\@chartColPos);
	print "\nLabels:\n", Dumper(\@labels);
}

my %lineCount=();
my %workSheets=();


# the first worksheet is a directory
my $directoryName='Directory';
my $directory;
my $noDirectory=0;
my $directoryLineCount=0;

unless ($noDirectory) {
	$directory = $workBook->add_worksheet($directoryName)	;
	$directory->set_column(0,0,30);
	$directory->write_row($directoryLineCount++,0,['Directory'],$boldFormat);
}

while (<>) {

	chomp;
	my @data=split(/,/);

	my $currWorkSheetName;
	if ($workSheetCol) {
		$currWorkSheetName=$data[$workSheetColPos];
		if (length($currWorkSheetName) > 31 ) {
			# cut some out of the middle of the name as the Excel worksheet name has max size of 31
			$currWorkSheetName = substr($currWorkSheetName,0,14) . '...' . substr($currWorkSheetName,-14);
		}
	} else {
		$currWorkSheetName='DynaChart';
	}

	print "Worksheet Name: $currWorkSheetName\n" if $debug;

	unless (defined $workSheets{$currWorkSheetName}) {
		$workSheets{$currWorkSheetName} = $workBook->add_worksheet($currWorkSheetName);
		$workSheets{$currWorkSheetName}->write_row($lineCount{$currWorkSheetName}++,0,\@labels,$boldFormat);
		# freeze pane at header
		$workSheets{$currWorkSheetName}->freeze_panes($lineCount{$currWorkSheetName},0);
	}

	# setup column widths
	#$workSheet->set_column($el,$el,$colWidth);
	$workSheets{$currWorkSheetName}->write_row($lineCount{$currWorkSheetName}++,0,\@data, $stdFormat);

}

if ($debug) {
	print "Worksheets:\n";
	print "$_\n" foreach keys %workSheets;
	print Dumper(\%lineCount);
}

# each row consumes about 18 pixels
my $rowHeight=18; # pixels
my $chartHeight=23; # rows

# triple from default width of 480
my $chartWidth = 480 * 3;

=head1 Write the Charts

 The default mode is to create a separate chart for each metric
 By specifying the command line option --combined-chart the values will be charted in a single chart
 Doing so is probably useful only for a limited number of sets of values

 Some may question the apparent duplication of code in the sections to combine or not combine the charts
 Doing so would be a bit convoluted - this is easier to read and modify

=cut

foreach my $workSheet ( keys %workSheets ) {
	print "Charting worksheet: $workSheet\n" if $debug;

	my $chartNum = 0;

	if ($combinedChart) {
		my $chart = $workBook->add_chart( type => 'line', name => "Combined" . '-' . $workSheets{$workSheet}->get_name(), embedded => 1 );
		$chart->set_size( width => $chartWidth, height => $chartHeight * $rowHeight);
		
		# each chart consumes about 16 rows
		$workSheets{$workSheet}->insert_chart((($chartNum * $chartHeight) + 2),3, $chart);

		foreach my $colPos ( @chartColPos ) {
			my $col2Chart=$labels[$colPos];
			# [ sheet, row_start, row_end, col_start, col_end]
			$chart->add_series(
				name => $col2Chart,
				#categories => [$workSheet, 1,$lineCount{$workSheet},2,2],
				categories => [$workSheet, 1,$lineCount{$workSheet},$categoryColNum,$categoryColNum],
				values => [$workSheet, 1,$lineCount{$workSheet},$colPos,$colPos]
			);
		}
		
	} else {
		foreach my $colPos ( @chartColPos ) {
			my $col2Chart=$labels[$colPos];
			print "\tCharting column: $col2Chart\n" if $debug;
			my $chart = $workBook->add_chart( type => 'line', name => "$col2Chart" . '-' . $workSheets{$workSheet}->get_name(), embedded => 1 );
			$chart->set_size( width => $chartWidth, height => $chartHeight * $rowHeight);

			# each chart consumes about 16 rows
			$workSheets{$workSheet}->insert_chart((($chartNum * $chartHeight) + 2),3, $chart);
		

			# [ sheet, row_start, row_end, col_start, col_end]
			$chart->add_series(
				name => $col2Chart,
				#categories => [$workSheet, 1,$lineCount{$workSheet},2,2],
				categories => [$workSheet, 1,$lineCount{$workSheet},$categoryColNum,$categoryColNum],
				values => [$workSheet, 1,$lineCount{$workSheet},$colPos,$colPos]
			);

			$chartNum++;
		}
	}
}


# write the directory page
my $urlFormat = $workBook->add_format( color => 'blue', underline => 1 );
my %sheetNames=();

foreach my $worksheet ( $workBook->sheets() ) {
	my $sheetName = $worksheet->get_name();
	next if $sheetName eq $directoryName;
	$sheetNames{$sheetName} = $worksheet;
}

foreach my $sheetName ( sort keys %sheetNames ) {
	$directory->write_url($directoryLineCount++,0, qq{internal:'$sheetName'!A1} ,$urlFormat, $sheetName);
}

__END__

=head1 NAME

dynachart.pl

  --help brief help message
  --man  full documentation
  --spreadsheet-file output file name - defaults to asm-metrics.xlsx
  --worksheet-col name of column used to segragate data into worksheets 
    defaults to a single worksheet if not supplied
  --chart-cols list of columns to chart

 dynachart.pl accepts input from STDIN

 This script will read CSV data created by asm-metrics-collector.pl or asm-metrics-aggregator.pl


=head1 SYNOPSIS

dynachart.pl [options] [file ...]

 Options:
   --help brief help message
   --man  full documentation
   --spreadsheet-file output file name - defaults to asm-metrics.xlsx
   --worksheet-col name of column used to segragate data into worksheets 
     defaults to a single worksheet if not supplied
  --chart-cols list of columns to chart
  --category-col specify the column for the X vector - a timestamp is typically used 
    the name must exactly match that in the header
  --combined-chart create a single chart rather than a chart for each value specified in --chart-cols

 dynachart.pl accepts input from STDIN

 dynachart.pl --worksheet-col DISKGROUP_NAME < my_input_file.csv


 dynachart.pl --spreadsheet-file sar-disk-test.xlsx --combined-chart --worksheet-col DEV --category-col 'timestamp' --chart-cols 'rd_sec/s' --chart-cols 'wr_sec/s' < sar-disk-test.csv


=head1 OPTIONS

=over 8

=item B<-help>

Print a brief help message and exits.

=item B<-man>

Prints the manual page and exits.

=item B<--spreadsheet-file>

 The name of the Excel file to create.
 The default name is asm-metrics.xlsx

=item B<--worksheet-col>

 By default a single worksheet is created.
 When this option is used the column supplied as an argument will be used to segragate data into separate worksheets.

=item B<--chart-cols>

 List of columns to chart
 This should be the last option on the command line if used.

 It may be necessary to tell Getopt to stop processing arguments with '--' in some cases.

 eg.

 dynachart.pl dynachart.pl --worksheet-col DISKGROUP_NAME --chart-cols READS WRITES -- logs/asm-oravm-20150512_01-agg-dg.csv

=item B<--category-col>

 Column to use as the category for the X line in the chart - default to the first column
 The name must exactly match a column from the CSV file
 Typically this line is a timestamp

=back

=head1 DESCRIPTION

B<dynachart.pl> creates an excel file with charts for selected columns>

=head1 EXAMPLES

 dynachart.pl accepts data from STDIN
 
 dynachart.pl --worksheet-col DISKGROUP_NAME --spreadsheet-file mywork.xlsx

=cut


 

Creating Multiple Charts with Bash

 

A number of invocations of dynachart.pl can be found in sar-chart.sh as seen in the following example. This allows creating a number of Excel files from sar data for later analysis, all with one command.

 

 

#!/bin/bash


usage() {

	cat <<-EOF

	usage: $0 destination-directory

	example script that charts CSV data that has been generated from sar
	Excel XLXS files with charts are produced

	applicable to any CSV data

	
	example: sar-chart.sh data-dir

	This script is using data generated by asp.sh


	EOF

}


while getopts h arg
do
	case $arg in
		h) usage;exit 0;;
		*) usage;exit 1;;
	esac
done

destDir=$1

[ -d "$destDir" -a -w "$destDir" ] || {
	echo
	echo cannot read and/or write directory destDir: $destDir
	echo
	usage
	echo
	exit 1
}

### Template
#echo working on sar-
#dynachart.pl --spreadsheet-file ${destDir}/ --worksheet-col hostname --category-col 'timestamp' 
################


# if dynachart.pl is in the path then use it.
# if not then check for local directory copy or link
# otherwise error exit

dynaChart=$(which dynachart.pl)

if [[ -z $dynaChart ]]; then
	if [[ -f ./dynachart.pl ]]; then
		dynaChart='./dynachart.pl'
		[ -x "$dynaChart" ] || {
			echo
			echo $dynaChart is not executable	
			echo
			exit 2
		}
	else
		echo
		echo "dynachart.pl not found"
		echo
		exit 1
	fi
fi


# default of 1 chart per metric
echo working on sar-disk-default.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-disk-default.xlsx --worksheet-col DEV --category-col 'timestamp' --chart-cols 'rd_sec/s' --chart-cols 'wr_sec/s' < sar-disk.csv

# combine metrics into one chart
echo working on sar-disk-combined.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-disk-combined.xlsx --combined-chart --worksheet-col DEV --category-col 'timestamp' --chart-cols 'rd_sec/s' --chart-cols 'wr_sec/s' < sar-disk.csv


echo working on sar-network-device.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-network-device.xlsx --combined-chart --worksheet-col IFACE --category-col 'timestamp' --chart-cols 'rxkB/s' --chart-cols 'txkB/s' < sar-net-dev.csv

echo working on sar-network-error-device.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-network-error-device.xlsx --combined-chart --worksheet-col IFACE --category-col 'timestamp' --chart-cols 'rxerr/s' --chart-cols 'txerr/s' < sar-net-ede.csv

echo working on sar-network-nfs.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-network-nfs.xlsx --worksheet-col hostname --category-col 'timestamp' --chart-cols 'call/s' --chart-cols 'retrans/' --chart-cols 'read/s' --chart-cols 'write/s' --chart-cols 'access/s' --chart-cols 'getatt/s'  < sar-net-nfs.csv

echo working on sar-network-nfsd.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-network-nfsd.xlsx --worksheet-col hostname --category-col 'timestamp' --chart-cols 'scall/s' --chart-cols 'badcall/s' --chart-cols 'packet/s' --chart-cols 'udp/s' --chart-cols 'tcp/s' --chart-cols 'hit/s' --chart-cols 'miss/s' --chart-cols 'sread/s' --chart-cols 'swrite/s' --chart-cols 'saccess/s' --chart-cols 'sgetatt/s' < sar-net-nfsd.csv

echo working on sar-network-socket.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-network-socket.xlsx --worksheet-col hostname --category-col 'timestamp' --chart-cols 'totsck' --chart-cols 'tcpsck' --chart-cols 'udpsck' --chart-cols 'rawsck' --chart-cols 'ip-frag' --chart-cols 'tcp-tw' < sar-net-sock.csv

echo working on sar-context.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-context.xlsx --worksheet-col hostname --category-col 'timestamp' --chart-cols 'proc/s' --chart-cols 'cswch/s' < sar-context.csv

echo working on sar-cpu.xlsx
# extracted with -u ALL, so all CPU on one line
dynachart.pl --spreadsheet-file ${destDir}/sar-cpu.xlsx --worksheet-col hostname --category-col 'timestamp' --chart-cols '%usr' --chart-cols '%nice' --chart-cols '%sys' --chart-cols '%iowait' --chart-cols '%steal' --chart-cols '%irq' --chart-cols '%soft' --chart-cols '%guest' --chart-cols '%idle' < sar-cpu.csv


echo working on sar-io-default.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-io-default.xlsx --worksheet-col hostname --category-col 'timestamp' --chart-cols 'tps' --chart-cols 'rtps' --chart-cols 'wtps' --chart-cols 'bread/s' --chart-cols 'bwrtn/s' < sar-io.csv

echo working on sar-io-tps-combined.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-io-tps-combined.xlsx --combined-chart --worksheet-col hostname --category-col 'timestamp' --chart-cols 'tps' --chart-cols 'rtps' --chart-cols 'wtps' < sar-io.csv

echo working on sar-io-blks-per-second-combined.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-io-blks-per-second-combined.xlsx --combined-chart --worksheet-col hostname --category-col 'timestamp' --chart-cols 'bread/s' --chart-cols 'bwrtn/s' < sar-io.csv


echo working on sar-load-runq-threads.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-load-runq-threads.xlsx --combined-chart --worksheet-col hostname --category-col 'timestamp' --chart-cols 'runq-sz' --chart-cols 'plist-sz' --chart-cols 'ldavg-1' --chart-cols 'ldavg-5' --chart-cols 'ldavg-15' < sar-load.csv

echo working on sar-load-runq.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-load-runq.xlsx --combined-chart --worksheet-col hostname --category-col 'timestamp' --chart-cols 'runq-sz' --chart-cols 'ldavg-1' --chart-cols 'ldavg-5' --chart-cols 'ldavg-15' < sar-load.csv

echo working on sar-memory.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-memory.xlsx --combined-chart --worksheet-col hostname --category-col 'timestamp' --chart-cols 'frmpg/s' --chart-cols  'bufpg/s' < sar-mem.csv


echo working on sar-paging-rate.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-paging-rate.xlsx --combined-chart --worksheet-col hostname --category-col 'timestamp'  --chart-cols 'pgpgin/s' --chart-cols  'pgpgout/s' < sar-paging.csv

echo working on sar-swap-rate.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-swap-rate.xlsx --combined-chart --worksheet-col hostname --category-col 'timestamp'  --chart-cols 'pswpin/s' --chart-cols 'pswpout/s' < sar-swap-stats.csv

Filter, rename and aggregate the data

Perhaps you want to see a subset of some data. Let’s say you wish too see the data for only a select group of devices; those making up the DATA diskgroup in an Oracle database.  In addition you would like the sar report to use names that match those seen by ASM. Rather than /dev/mapper/mp1, mp2,…, you would like to see DATA00, DATA01,…

In addition you would like to see the IOPS, read rate and write rate data for a set of disks aggregated per timestamp.

We can do that. The script remap.sh has an example (real life BTW) of how to map the ASM disk names to the linux device names, and create a new CSV file.

This query was used to map device names to disk names:

 

select
	'dm-name-' || substr(d.path,instr(d.path,'/',-1)+1,length(d.path) - instr(d.path,'/',-1)) path
	, g.name || lpad(d.disk_number,2,'0') name
from v$asm_disk d
join v$asm_diskgroup g on g.group_number = d.group_number
order by 2

15:10:17 SYSDBA> /

PATH			                   NAME
------------------------------ --------------------
dm-name-mpathmp1	             DATA00
dm-name-mpathnp1	             DATA01
dm-name-mpathop1	             DATA02
dm-name-mpathqp1	             DATA03
dm-name-mpathrp1	             DATA04
dm-name-mpathsp1	             DATA05
dm-name-mpathtp1	             DATA06
dm-name-mpathvp1	             DATA07
dm-name-mpathwp1	             DATA08
dm-name-mpathcp1	             FRA00

10 rows selected.

 

This information was used to then create a sed command to rewrite the CSV data as needed.

 


originalFile=sar-disk-original.csv
newFile=sar-disk.csv

sed \
	-e 's/dm-name-mpathmp1/DATA00/g' \
	-e 's/dm-name-mpathnp1/DATA01/g' \
	-e 's/dm-name-mpathop1/DATA02/g' \
	-e 's/dm-name-mpathqp1/DATA03/g' \
	-e 's/dm-name-mpathrp1/DATA04/g' \
	-e 's/dm-name-mpathsp1/DATA05/g' \
	-e 's/dm-name-mpathtp1/DATA06/g' \
	-e 's/dm-name-mpathvp1/DATA07/g' \
	-e 's/dm-name-mpathwp1/DATA08/g' \
	-e 's/dm-name-mpathcp1/FRA00/g' \
< $originalFile > $newFile

Following that csv-aggregator.sh and csv-aggregator.pl were used to filter and aggregate the data.  The result is a much smaller CSV file with data only for the DATA diskgroup, with the disk data rolled up to the disk group level.

Rather than post several hundred more lines of Perl here, I will recommend you to follow the links if you want to see how this aggregation and filtering is done. The command line though is shown here:

 


csvFile=sar-csv/sar-disk-test.csv

./csv-aggregator.pl --filter-cols DEV  --filter-vals 'DATA..'  --key-cols hostname --key-cols timestamp  --grouping-cols DEV  --agg-cols tps --agg-cols 'rd_sec/s' --agg-cols 'wr_sec/s'  --agg-cols 'avgrq-sz' --agg-cols 'avgqu-sz' < $csvFile

 

This is a chart taken directly from the Excel file created from this CSV data via dynachart.pl.

 

image

 

 

 

Installing Excel::Writer::XLSX

So that the dynachart.pl script will work, the Excel::Writer::XLSX Perl module must be installed.

There are several possible methods by which this may be done, but if you are not already fairly familiar with the Perl environment some of the methods are a little daunting.  As Oracle by default always installs Perl in ORACLE_HOME, you can make use of that existing installation.  These instructions will install the Excel::Writer::XLSX module and it’s dependencies directly into the Oracle Perl installation. Because of that, you would want to do this in a test environment, such as Virtual Machine running on your laptop.  It isn’t too likely that this would break something in the Oracle home, but then again, why take the chance?

An even better option would be to install your own Perl, and not worry about affecting any other software. This is not too difficult to do via Perlbrew. Check out https://perlbrew.pl/ for the simple instructions.

The following example installs Excel::Writer::XLSX in Oracle’s copy of Perl on a test server.  For my own use, I do use Perlbrew, but am just including the following as an example

 

[oracle@oravm01 ~]$ $ORACLE_HOME/perl/bin/perl -MCPAN -e shell

...

Would you like me to configure as much as possible automatically? [yes] yes

...

These next commands tell CPAN to automatically answer appropriately rather than prompt you to continually type ‘yes’.
This lasts the duration of this CPAN session.  Following that is the command to install Excel::Writer::XLSX.

 

cpan> o conf prerequisites_policy 'follow'
cpan> o conf build_requires_install_policy yes


cpan> install Excel::Writer::XLSX

...


cpan> exit
Terminal does not support GetHistory.
Lockfile removed.

 

Now test the installation.  This command simply tells Perl the we require version 99 of the module. The failure is normal, as only version 0.95 is installed.  A different message would appear if no version of the module was installed.

[oracle@oravm01 ~]$ $ORACLE_HOME/perl/bin/perl -MExcel::Writer::XLSX=99
Excel::Writer::XLSX version 99 required--this is only version 0.95 at /u01/app/oracle/product/11.2.0/db_1/perl/lib/5.10.0/Exporter/Heavy.pm line 122.
BEGIN failed--compilation aborted.
Categories: DBA Blogs

Chart Your Course With sar

Tue, 2017-08-15 12:02

The sar (system activity reporter) is a Linux utility that actually started life long ago in System V Unix.

In spite of its ancient origins, sar is still quite useful, and has even been enhanced in recent versions of Linux.

The use of sar is not limited to root – any user can run sar and its utilities.  Most Linux systems have sar installed and enabled by default to collect sar data at 10 minute intervals and retain that data for 10 days.

The reports output by sar will look familiar:

 

jkstill@poirot ~/tmp $ sar -d | head -20
Linux 4.4.0-53-generic (poirot.jks.com)         08/07/17        _x86_64_        (1 CPU)

00:00:01          DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
00:02:01       dev7-0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:02:01       dev7-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:02:01       dev7-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:02:01       dev7-3      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:02:01       dev7-4      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:02:01       dev7-5      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:02:01       dev7-6      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:02:01       dev8-0      0.40      0.00      4.97     12.43      0.00      0.26      0.26      0.01
00:02:01     dev252-0      0.63      0.00      4.90      7.78      0.00      0.16      0.16      0.01
00:02:01     dev252-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:04:01       dev7-0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:04:01       dev7-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:04:01       dev7-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:04:01       dev7-3      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:04:01       dev7-4      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:04:01       dev7-5      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
00:04:01       dev7-6      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

This report format is good for a quick look at different metrics to get an overall idea of system perform, disk metrics in the previously shown example.

However what if you would like to collate this data into something more readily consumed for charting an analysis, such as a CSV file?  The standard output of sar does not readily lend itself to such use without quite a bit of work.

The sar utility is part of the Linux  sysstat package and contains not only sar, but sadf.  If sar is available then so is sadf. What is sadf you say?  sadf is a the sar data formatting utility, and can output data in CSV, XML and other formats.

Using sadf a bash script can be created that will extract sar data for many Linux subsystems and write that data in the format of your choice, CSV in this case.

The asp.sh script does just that, creating CSV files that can then be used for charting and analysis.

 

#!/bin/bash

# Jared Still - Pythian
# still@pythian.com jkstill@gmail.com
# 2017-07-20

# asp - another sar processor

# tested on Red Hat Enterprise Linux Server release 6.6 (Santiago)
# also tested on Linux Mint

help() {
	echo
	echo $0 -s source-dir -d dest-dir
	echo
}



# variables can be set to identify multiple sets of copied sar files
sarSrcDir='/var/log/sa' # RedHat, CentOS ...
#sarSrcDir='/var/log/sysstat' # Debian, Ubuntu ...

sarDstDir="sar-csv"

csvConvertCmd=" sed -e 's/;/,/g' "


while getopts s:d:h arg
do
	case $arg in
		d) sarDstDir=$OPTARG;;
		s) sarSrcDir=$OPTARG;;
		h) help; exit 0;;
		*) help; exit 1;;
	esac
done


cat << EOF

Source: $sarSrcDir
  Dest: $sarDstDir

EOF

#exit


mkdir -p $sarDstDir || {

	echo 
	echo Failed to create $sarDstDir
	echo
	exit 1

}

echo "lastSaDirEl: $lastSaDirEl"


# sar options
# -d activity per block device
  # -j LABEL: use label for device if possible. eg. sentryoraredo01 rather than /dev/dm-3
# -b IO and transfer rates
# -q load
# -u cpu
# -r memory utilization
# -R memory
# -B paging
# -S swap space utilization
# -W swap stats
# -n network
# -v kernel filesystem stats
# -w  context switches and task creation
#sarDestOptions=( '-d -j LABEL -p' '-b' '-q' '-u ALL' '-r' '-R' '-B' '-S' '-W' '-n DEV,EDEV,NFS,NFSD,SOCK,IP,EIP,ICMP,EICMP,TCP,ETCP,UDP' '-v' '-w')
# break up network into a separate file for each option
# not all options available depending on sar version
# for disk "-d" you may want one of ID, LABEL, PATH or UUID - check the output and the sar docs
sarDestOptions=( '-d -j ID -p' '-b' '-q' '-u ALL' '-r' '-R' '-B' '-S' '-W' '-n DEV' '-n EDEV' '-n NFS' '-n NFSD' '-n SOCK' '-n IP' '-n EIP' '-n ICMP' '-n EICMP' '-n TCP' '-n ETCP' '-n UDP' '-v' '-w')

#sarDestFiles=( sar-disk.csv sar-io.csv sar-load.csv sar-cpu.csv sar-mem-utilization.csv sar-mem.csv sar-paging.csv sar-swap-utilization.csv sar-swap-stats.csv sar-network.csv sar-kernel-fs.csv sar-context.csv)
sarDestFiles=( sar-disk.csv sar-io.csv sar-load.csv sar-cpu.csv sar-mem-utilization.csv sar-mem.csv sar-paging.csv sar-swap-utilization.csv sar-swap-stats.csv sar-net-dev.csv sar-net-ede.csv sar-net-nfs.csv sar-net-nfsd.csv sar-net-sock.csv sar-net-ip.csv sar-net-eip.csv sar-net-icmp.csv sar-net-eicmp.csv sar-net-tcp.csv sar-net-etcp.csv sar-net-udp.csv sar-kernel-fs.csv sar-context.csv)

lastSarOptEl=${#sarDestOptions[@]}
echo "lastSarOptEl: $lastSarOptEl"

#while [[ $i -lt ${#x[@]} ]]; do echo ${x[$i]}; (( i++ )); done;

# initialize files with header row
i=0
while [[ $i -lt $lastSarOptEl ]]
do
	CMD="sadf -d -- ${sarDestOptions[$i]}  | head -1 | $csvConvertCmd > ${sarDstDir}/${sarDestFiles[$i]} "
	echo CMD: $CMD
	eval $CMD
	#sadf -d -- ${sarDestOptions[$i]}  | head -1 | $csvConvertCmd > ${sarDstDir}/${sarDestFiles[$i]}
	echo "################"
	(( i++ ))
done

#exit

#for sarFiles in ${sarSrcDirs[$currentEl]}/sa??
for sarFiles in $(ls -1dtar ${sarSrcDir}/sa??)
do
	for sadfFile in $sarFiles
	do

		#echo CurrentEl: $currentEl
		# sadf options
		# -t is for local timestamp
		# -d : database semi-colon delimited output

		echo Processing File: $sadfFile

		i=0
		while [[ $i -lt $lastSarOptEl ]]
		do
			CMD="sadf -d -- ${sarDestOptions[$i]} $sadfFile | tail -n +2 | $csvConvertCmd  >> ${sarDstDir}/${sarDestFiles[$i]}"
			echo CMD: $CMD
			eval $CMD
			if [[ $? -ne 0 ]]; then
				echo "#############################################
				echo "## CMD Failed"
				echo "## $CMD"
				echo "#############################################

			fi
			(( i++ ))
		done

	done
done


echo
echo Processing complete 
echo 
echo files located in $sarDstDir
echo 


# show the files created
i=0
while [[ $i -lt $lastSarOptEl ]]
do
	ls -ld ${sarDstDir}/${sarDestFiles[$i]} 
	(( i++ ))
done

 

Following is an example execution of the script:

 

jkstill@poirot ~/pythian/blog/sar-tools-blog $ ./asp.sh -s /var/log/sysstat

Source: /var/log/sysstat
  Dest: sar-csv

lastSaDirEl:
lastSarOptEl: 23
CMD: sadf -d -- -d -j ID -p | head -1 | sed -e 's/;/,/g' > sar-csv/sar-disk.csv
################
CMD: sadf -d -- -b | head -1 | sed -e 's/;/,/g' > sar-csv/sar-io.csv
################
CMD: sadf -d -- -q | head -1 | sed -e 's/;/,/g' > sar-csv/sar-load.csv
################
CMD: sadf -d -- -u ALL | head -1 | sed -e 's/;/,/g' > sar-csv/sar-cpu.csv
################
CMD: sadf -d -- -r | head -1 | sed -e 's/;/,/g' > sar-csv/sar-mem-utilization.csv
################
CMD: sadf -d -- -R | head -1 | sed -e 's/;/,/g' > sar-csv/sar-mem.csv
################
CMD: sadf -d -- -B | head -1 | sed -e 's/;/,/g' > sar-csv/sar-paging.csv
################
CMD: sadf -d -- -S | head -1 | sed -e 's/;/,/g' > sar-csv/sar-swap-utilization.csv
################
CMD: sadf -d -- -W | head -1 | sed -e 's/;/,/g' > sar-csv/sar-swap-stats.csv
################
CMD: sadf -d -- -n DEV | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-dev.csv
################
CMD: sadf -d -- -n EDEV | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-ede.csv
################
CMD: sadf -d -- -n NFS | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-nfs.csv
################
CMD: sadf -d -- -n NFSD | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-nfsd.csv
################
CMD: sadf -d -- -n SOCK | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-sock.csv
################
CMD: sadf -d -- -n IP | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-ip.csv
Requested activities not available in file /var/log/sysstat/sa07
################
CMD: sadf -d -- -n EIP | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-eip.csv
Requested activities not available in file /var/log/sysstat/sa07
################
CMD: sadf -d -- -n ICMP | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-icmp.csv
Requested activities not available in file /var/log/sysstat/sa07
################
CMD: sadf -d -- -n EICMP | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-eicmp.csv
Requested activities not available in file /var/log/sysstat/sa07
################
CMD: sadf -d -- -n TCP | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-tcp.csv
Requested activities not available in file /var/log/sysstat/sa07
################
CMD: sadf -d -- -n ETCP | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-etcp.csv
Requested activities not available in file /var/log/sysstat/sa07
################
CMD: sadf -d -- -n UDP | head -1 | sed -e 's/;/,/g' > sar-csv/sar-net-udp.csv
Requested activities not available in file /var/log/sysstat/sa07
################
CMD: sadf -d -- -v | head -1 | sed -e 's/;/,/g' > sar-csv/sar-kernel-fs.csv
################
CMD: sadf -d -- -w | head -1 | sed -e 's/;/,/g' > sar-csv/sar-context.csv
################
Processing File: /var/log/sysstat/sa30
CMD: sadf -d -- -d -j ID -p /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-disk.csv
CMD: sadf -d -- -b /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-io.csv
CMD: sadf -d -- -q /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-load.csv
CMD: sadf -d -- -u ALL /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-cpu.csv
CMD: sadf -d -- -r /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-mem-utilization.csv
CMD: sadf -d -- -R /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-mem.csv
CMD: sadf -d -- -B /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-paging.csv
CMD: sadf -d -- -S /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-swap-utilization.csv
CMD: sadf -d -- -W /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-swap-stats.csv
CMD: sadf -d -- -n DEV /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-dev.csv
CMD: sadf -d -- -n EDEV /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-ede.csv
CMD: sadf -d -- -n NFS /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-nfs.csv
CMD: sadf -d -- -n NFSD /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-nfsd.csv
CMD: sadf -d -- -n SOCK /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-sock.csv
CMD: sadf -d -- -n IP /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-ip.csv
Requested activities not available in file /var/log/sysstat/sa30
CMD: sadf -d -- -n EIP /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-eip.csv
Requested activities not available in file /var/log/sysstat/sa30
CMD: sadf -d -- -n ICMP /var/log/sysstat/sa30 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-net-icmp.csv
Requested activities not available in file /var/log/sysstat/sa30
...

CMD: sadf -d -- -v /var/log/sysstat/sa07 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-kernel-fs.csv
CMD: sadf -d -- -w /var/log/sysstat/sa07 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-context.csv

Processing complete

files located in sar-csv

Note that for some commands the result is Requested activities not available.  What this means is that we have asked for something sar may be capable of collecting, but in this case there is no data. It may be that sar has not been configured to collect that particular metric. These messages can be ignored unless, of course, it is for a metric you would like to see. (sar configuration is outside the scope of this article)

Let’s break down the following sadf command line

sadf -d -- -d -j ID -p /var/log/sysstat/sa05 | tail -n +2 | sed -e 's/;/,/g' >> sar-csv/sar-disk.csv
The sadf ‘-d’ option

This option tells sadf to output data in  CSV format.  As CSV is an acronym for Comma-Separated-Values, the use of a semi-colon as a delimiter may at first seem a little puzzling.  However it makes sense to use something other than a comma, as it may be that a comma could appear in the output.  If that were the case sar would need to then surround the data with quotes.  While many programs can deal with data that may or may not be quoted, it is just simpler to use a semi-colon, which is unlikely to appear in data seen in sar.

The ‘–‘ option

This option doesn’t appear to be too well known. When used on the shell command line, — signifies the end of arguments for the program. A typical use might be if you were using grep to search for ‘-j’ in some text files.  This following command fails due to grep interpreting the text we want to search as an argument. Since there is no ‘-j’ argument for grep, the command fails.

 

jkstill@poirot ~/pythian/blog/sar-tools-blog $ grep '-j' *.sh
grep: invalid option -- 'j'
Usage: grep [OPTION]... PATTERN [FILE]...
Try 'grep --help' for more information.

 

Using ‘–‘ tells the shell to stop processing arguments for the current command, which is grep in this case, and so grep searches for ‘-j’ as intended.

jkstill@poirot ~/pythian/blog/sar-tools-blog $ grep -- '-j' *.sh
  # -j LABEL: use label for device if possible. eg. sentryoraredo01 rather than /dev/dm-3
#sarDestOptions=( '-d -j LABEL -p' '-b' '-q' '-u ALL' '-r' '-R' '-B' '-S' '-W' '-n DEV,EDEV,NFS,NFSD,SOCK,IP,EIP,ICMP,EICMP,TCP,ETCP,UDP' '-v' '-w')
sarDestOptions=( '-d -j ID -p' '-b' '-q' '-u ALL' '-r' '-R' '-B' '-S' '-W' '-n DEV' '-n EDEV' '-n NFS' '-n NFSD' '-n SOCK' '-n IP' '-n EIP' '-n ICMP' '-n EICMP' '-n TCP' '-n ETCP' '-n UDP' '-v' '-w')

Why in this case are we using ‘–‘?  It is because the other options following -d are not for sadf, but are sar options that sadf will use when calling sar.

The ‘-d’, ‘-j’ and ‘-p’ options

Be reading the sar man page you can learn what each of these are:

  • -d block device activity
  • -j display persistent device names – options are ID, LABEL, PATH and UUID
  • -p pretty print device names
tail -n +2

Just get the rows after the header row.  asp.sh calls sadf for each day’s sar data.  The first step of creating the header has already been done.

 

sed

sed is being used to change semi-colons to commas.  So far I have not seen any commas in sar data, and using a comma delimiter is just more convenient.

 

Charting in Excel with Perl

 

Having the sar data in the CSV format is convenient, as it will load directly into Excel or Google Sheets. What isn’t convenient is all the work required to load the data, format it, and then create multiple charts from the data.  There could be a number of CSV files this needs to be done with.

Perl to the rescue!  The Excel::Writer::XLSX Perl module can directly create formatted Excel files, complete with charts. I am going to make some basic use of this quite capable module via the dynachart.pl Perl script. This script reads CSV data and creates Excel files, optionally with line charts of selected columns.

If you would like to see some simple examples of using Perl to create Excel files, the documentation for the Perl Module Writer::Excel::XLSX contains several good examples, such as this one:  Example-1

There are several others to be found in the documentation for  Excel::Writer::XLSX

Following is the entire script.  This script and others can be found at the csv-tools github repo.

#!/usr/bin/env perl

# dynachart.pl
# Jared Still 2017-07-23
# still@pythian.com jkstill@gmail.com

# data is from STDIN

use warnings;
use strict;
use Data::Dumper;
use Pod::Usage;
use Getopt::Long;
use Excel::Writer::XLSX;

my $debug = 0;
my $combinedChart = 0;
my %optctl = ();
my ($help,$man);
my @chartCols;
my $categoryColName='';
my $categoryColNum=0;

Getopt::Long::GetOptions(
	\%optctl, 
	'spreadsheet-file=s',
	'debug!' => \$debug,
	'chart-cols=s{1,10}' => \@chartCols,
	'combined-chart!' => \$combinedChart,
	'worksheet-col=s',  # creates a separate worksheet per value of this column
	'category-col=s' => \$categoryColName,
	'h|help|?' => \$help, man => \$man
) or pod2usage(2) ;

pod2usage(1) if $help;
pod2usage(-verbose => 2) if $man;

my $xlFile = defined($optctl{'spreadsheet-file'}) ? $optctl{'spreadsheet-file'} : 'asm-metrics.xlsx';
my $workSheetCol = defined($optctl{'worksheet-col'}) ? $optctl{'worksheet-col'} : 0;

my %fonts = (
	fixed			=> 'Courier New',
	fixed_bold	=> 'Courier New',
	text			=> 'Arial',
	text_bold	=> 'Arial',
);

my %fontSizes = (
	fixed			=> 10,
	fixed_bold	=> 10,
	text			=> 10,
	text_bold	=> 10,
);

my $maxColWidth = 50;
my $counter = 0;
my $interval = 100;

# create workbook
my $workBook = Excel::Writer::XLSX->new($xlFile);
die "Problems creating new Excel file $xlFile: $!\n" unless defined $workBook;

# create formats
my $stdFormat = $workBook->add_format(bold => 0,font => $fonts{fixed}, size => $fontSizes{fixed}, color => 'black');
my $boldFormat = $workBook->add_format(bold => 1,font => $fonts{fixed_bold}, size => $fontSizes{fixed_bold}, color => 'black');
my $wrapFormat = $workBook->add_format(bold => 0,font => $fonts{text}, size => $fontSizes{text}, color => 'black');
$wrapFormat->set_align('vjustify');


my $labels=<>;
chomp $labels;
# sadf starts header lines with '# ' - remove that
$labels =~ s/^#\s+//;
my @labels = split(/,\s*/,$labels);

if ($debug) {

print qq{LABELS:\n};

print join("\n",@labels);

print "\n";

}

# get the X series category
if ( $categoryColName ) {

	my $want = $categoryColName;
	my $index = 0;
	++$index until ($labels[$index] eq $want) or ($index > $#labels);
	$categoryColNum = $index;	

}

#print join("\n",@labels);

# get the element number of the column used to segregate into worksheets
my $workSheetColPos;
if ($workSheetCol) {
	my $i=0;
	foreach my $label ( @labels)  {
		if ($label eq $workSheetCol) { 
			$workSheetColPos = $i;
			last;
		}
		$i++;
	}
}

print "\nworkSheetColPos: $workSheetColPos\n" if $debug;

# validate the columns to be charted
# use as an index into the labels
my @chartColPos=();
{
	my $i=0;
	foreach my $label ( @labels)  {
		foreach my $chartCol ( @chartCols ) {
			if ($label eq $chartCol) { 
				push @chartColPos, $i;
				last;
			}
		}
		$i++;
	}
}

if ($debug) {
	print "\nChartCols:\n", Dumper(\@chartCols);
	print "\nChartColPos:\n", Dumper(\@chartColPos);
	print "\nLabels:\n", Dumper(\@labels);
}

my %lineCount=();
my %workSheets=();


# the first worksheet is a directory
my $directoryName='Directory';
my $directory;
my $noDirectory=0;
my $directoryLineCount=0;

unless ($noDirectory) {
	$directory = $workBook->add_worksheet($directoryName)	;
	$directory->set_column(0,0,30);
	$directory->write_row($directoryLineCount++,0,['Directory'],$boldFormat);
}

while (<>) {

	chomp;
	my @data=split(/,/);

	my $currWorkSheetName;
	if ($workSheetCol) {
		$currWorkSheetName=$data[$workSheetColPos];
		if (length($currWorkSheetName) > 31 ) {
			# cut some out of the middle of the name as the Excel worksheet name has max size of 31
			$currWorkSheetName = substr($currWorkSheetName,0,14) . '...' . substr($currWorkSheetName,-14);
		}
	} else {
		$currWorkSheetName='DynaChart';
	}

	print "Worksheet Name: $currWorkSheetName\n" if $debug;

	unless (defined $workSheets{$currWorkSheetName}) {
		$workSheets{$currWorkSheetName} = $workBook->add_worksheet($currWorkSheetName);
		$workSheets{$currWorkSheetName}->write_row($lineCount{$currWorkSheetName}++,0,\@labels,$boldFormat);
		# freeze pane at header
		$workSheets{$currWorkSheetName}->freeze_panes($lineCount{$currWorkSheetName},0);
	}

	# setup column widths
	#$workSheet->set_column($el,$el,$colWidth);
	$workSheets{$currWorkSheetName}->write_row($lineCount{$currWorkSheetName}++,0,\@data, $stdFormat);

}

if ($debug) {
	print "Worksheets:\n";
	print "$_\n" foreach keys %workSheets;
	print Dumper(\%lineCount);
}

# each row consumes about 18 pixels
my $rowHeight=18; # pixels
my $chartHeight=23; # rows

# triple from default width of 480
my $chartWidth = 480 * 3;

=head1 Write the Charts

 The default mode is to create a separate chart for each metric
 By specifying the command line option --combined-chart the values will be charted in a single chart
 Doing so is probably useful only for a limited number of sets of values

 Some may question the apparent duplication of code in the sections to combine or not combine the charts
 Doing so would be a bit convoluted - this is easier to read and modify

=cut

foreach my $workSheet ( keys %workSheets ) {
	print "Charting worksheet: $workSheet\n" if $debug;

	my $chartNum = 0;

	if ($combinedChart) {
		my $chart = $workBook->add_chart( type => 'line', name => "Combined" . '-' . $workSheets{$workSheet}->get_name(), embedded => 1 );
		$chart->set_size( width => $chartWidth, height => $chartHeight * $rowHeight);
		
		# each chart consumes about 16 rows
		$workSheets{$workSheet}->insert_chart((($chartNum * $chartHeight) + 2),3, $chart);

		foreach my $colPos ( @chartColPos ) {
			my $col2Chart=$labels[$colPos];
			# [ sheet, row_start, row_end, col_start, col_end]
			$chart->add_series(
				name => $col2Chart,
				#categories => [$workSheet, 1,$lineCount{$workSheet},2,2],
				categories => [$workSheet, 1,$lineCount{$workSheet},$categoryColNum,$categoryColNum],
				values => [$workSheet, 1,$lineCount{$workSheet},$colPos,$colPos]
			);
		}
		
	} else {
		foreach my $colPos ( @chartColPos ) {
			my $col2Chart=$labels[$colPos];
			print "\tCharting column: $col2Chart\n" if $debug;
			my $chart = $workBook->add_chart( type => 'line', name => "$col2Chart" . '-' . $workSheets{$workSheet}->get_name(), embedded => 1 );
			$chart->set_size( width => $chartWidth, height => $chartHeight * $rowHeight);

			# each chart consumes about 16 rows
			$workSheets{$workSheet}->insert_chart((($chartNum * $chartHeight) + 2),3, $chart);
		

			# [ sheet, row_start, row_end, col_start, col_end]
			$chart->add_series(
				name => $col2Chart,
				#categories => [$workSheet, 1,$lineCount{$workSheet},2,2],
				categories => [$workSheet, 1,$lineCount{$workSheet},$categoryColNum,$categoryColNum],
				values => [$workSheet, 1,$lineCount{$workSheet},$colPos,$colPos]
			);

			$chartNum++;
		}
	}
}


# write the directory page
my $urlFormat = $workBook->add_format( color => 'blue', underline => 1 );
my %sheetNames=();

foreach my $worksheet ( $workBook->sheets() ) {
	my $sheetName = $worksheet->get_name();
	next if $sheetName eq $directoryName;
	$sheetNames{$sheetName} = $worksheet;
}

foreach my $sheetName ( sort keys %sheetNames ) {
	$directory->write_url($directoryLineCount++,0, qq{internal:'$sheetName'!A1} ,$urlFormat, $sheetName);
}

__END__

=head1 NAME

dynachart.pl

  --help brief help message
  --man  full documentation
  --spreadsheet-file output file name - defaults to asm-metrics.xlsx
  --worksheet-col name of column used to segragate data into worksheets 
    defaults to a single worksheet if not supplied
  --chart-cols list of columns to chart

 dynachart.pl accepts input from STDIN

 This script will read CSV data created by asm-metrics-collector.pl or asm-metrics-aggregator.pl


=head1 SYNOPSIS

dynachart.pl [options] [file ...]

 Options:
   --help brief help message
   --man  full documentation
   --spreadsheet-file output file name - defaults to asm-metrics.xlsx
   --worksheet-col name of column used to segragate data into worksheets 
     defaults to a single worksheet if not supplied
  --chart-cols list of columns to chart
  --category-col specify the column for the X vector - a timestamp is typically used 
    the name must exactly match that in the header
  --combined-chart create a single chart rather than a chart for each value specified in --chart-cols

 dynachart.pl accepts input from STDIN

 dynachart.pl --worksheet-col DISKGROUP_NAME < my_input_file.csv


 dynachart.pl --spreadsheet-file sar-disk-test.xlsx --combined-chart --worksheet-col DEV --category-col 'timestamp' --chart-cols 'rd_sec/s' --chart-cols 'wr_sec/s' < sar-disk-test.csv


=head1 OPTIONS

=over 8

=item B<-help>

Print a brief help message and exits.

=item B<-man>

Prints the manual page and exits.

=item B<--spreadsheet-file>

 The name of the Excel file to create.
 The default name is asm-metrics.xlsx

=item B<--worksheet-col>

 By default a single worksheet is created.
 When this option is used the column supplied as an argument will be used to segragate data into separate worksheets.

=item B<--chart-cols>

 List of columns to chart
 This should be the last option on the command line if used.

 It may be necessary to tell Getopt to stop processing arguments with '--' in some cases.

 eg.

 dynachart.pl dynachart.pl --worksheet-col DISKGROUP_NAME --chart-cols READS WRITES -- logs/asm-oravm-20150512_01-agg-dg.csv

=item B<--category-col>

 Column to use as the category for the X line in the chart - default to the first column
 The name must exactly match a column from the CSV file
 Typically this line is a timestamp

=back

=head1 DESCRIPTION

B<dynachart.pl> creates an excel file with charts for selected columns>

=head1 EXAMPLES

 dynachart.pl accepts data from STDIN
 
 dynachart.pl --worksheet-col DISKGROUP_NAME --spreadsheet-file mywork.xlsx

=cut


 

Creating Multiple Charts with Bash

 

A number of invocations of dynachart.pl can be found in sar-chart.sh as seen in the following example. This allows creating a number of Excel files from sar data for later analysis, all with one command.

 

 

#!/bin/bash


usage() {

	cat <<-EOF

	usage: $0 destination-directory

	example script that charts CSV data that has been generated from sar
	Excel XLXS files with charts are produced

	applicable to any CSV data

	
	example: sar-chart.sh data-dir

	This script is using data generated by asp.sh


	EOF

}


while getopts h arg
do
	case $arg in
		h) usage;exit 0;;
		*) usage;exit 1;;
	esac
done

destDir=$1

[ -d "$destDir" -a -w "$destDir" ] || {
	echo
	echo cannot read and/or write directory destDir: $destDir
	echo
	usage
	echo
	exit 1
}

### Template
#echo working on sar-
#dynachart.pl --spreadsheet-file ${destDir}/ --worksheet-col hostname --category-col 'timestamp' 
################


# if dynachart.pl is in the path then use it.
# if not then check for local directory copy or link
# otherwise error exit

dynaChart=$(which dynachart.pl)

if [[ -z $dynaChart ]]; then
	if [[ -f ./dynachart.pl ]]; then
		dynaChart='./dynachart.pl'
		[ -x "$dynaChart" ] || {
			echo
			echo $dynaChart is not executable	
			echo
			exit 2
		}
	else
		echo
		echo "dynachart.pl not found"
		echo
		exit 1
	fi
fi


# default of 1 chart per metric
echo working on sar-disk-default.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-disk-default.xlsx --worksheet-col DEV --category-col 'timestamp' --chart-cols 'rd_sec/s' --chart-cols 'wr_sec/s' < sar-disk.csv

# combine metrics into one chart
echo working on sar-disk-combined.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-disk-combined.xlsx --combined-chart --worksheet-col DEV --category-col 'timestamp' --chart-cols 'rd_sec/s' --chart-cols 'wr_sec/s' < sar-disk.csv


echo working on sar-network-device.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-network-device.xlsx --combined-chart --worksheet-col IFACE --category-col 'timestamp' --chart-cols 'rxkB/s' --chart-cols 'txkB/s' < sar-net-dev.csv

echo working on sar-network-error-device.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-network-error-device.xlsx --combined-chart --worksheet-col IFACE --category-col 'timestamp' --chart-cols 'rxerr/s' --chart-cols 'txerr/s' < sar-net-ede.csv

echo working on sar-network-nfs.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-network-nfs.xlsx --worksheet-col hostname --category-col 'timestamp' --chart-cols 'call/s' --chart-cols 'retrans/' --chart-cols 'read/s' --chart-cols 'write/s' --chart-cols 'access/s' --chart-cols 'getatt/s'  < sar-net-nfs.csv

echo working on sar-network-nfsd.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-network-nfsd.xlsx --worksheet-col hostname --category-col 'timestamp' --chart-cols 'scall/s' --chart-cols 'badcall/s' --chart-cols 'packet/s' --chart-cols 'udp/s' --chart-cols 'tcp/s' --chart-cols 'hit/s' --chart-cols 'miss/s' --chart-cols 'sread/s' --chart-cols 'swrite/s' --chart-cols 'saccess/s' --chart-cols 'sgetatt/s' < sar-net-nfsd.csv

echo working on sar-network-socket.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-network-socket.xlsx --worksheet-col hostname --category-col 'timestamp' --chart-cols 'totsck' --chart-cols 'tcpsck' --chart-cols 'udpsck' --chart-cols 'rawsck' --chart-cols 'ip-frag' --chart-cols 'tcp-tw' < sar-net-sock.csv

echo working on sar-context.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-context.xlsx --worksheet-col hostname --category-col 'timestamp' --chart-cols 'proc/s' --chart-cols 'cswch/s' < sar-context.csv

echo working on sar-cpu.xlsx
# extracted with -u ALL, so all CPU on one line
dynachart.pl --spreadsheet-file ${destDir}/sar-cpu.xlsx --worksheet-col hostname --category-col 'timestamp' --chart-cols '%usr' --chart-cols '%nice' --chart-cols '%sys' --chart-cols '%iowait' --chart-cols '%steal' --chart-cols '%irq' --chart-cols '%soft' --chart-cols '%guest' --chart-cols '%idle' < sar-cpu.csv


echo working on sar-io-default.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-io-default.xlsx --worksheet-col hostname --category-col 'timestamp' --chart-cols 'tps' --chart-cols 'rtps' --chart-cols 'wtps' --chart-cols 'bread/s' --chart-cols 'bwrtn/s' < sar-io.csv

echo working on sar-io-tps-combined.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-io-tps-combined.xlsx --combined-chart --worksheet-col hostname --category-col 'timestamp' --chart-cols 'tps' --chart-cols 'rtps' --chart-cols 'wtps' < sar-io.csv

echo working on sar-io-blks-per-second-combined.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-io-blks-per-second-combined.xlsx --combined-chart --worksheet-col hostname --category-col 'timestamp' --chart-cols 'bread/s' --chart-cols 'bwrtn/s' < sar-io.csv


echo working on sar-load-runq-threads.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-load-runq-threads.xlsx --combined-chart --worksheet-col hostname --category-col 'timestamp' --chart-cols 'runq-sz' --chart-cols 'plist-sz' --chart-cols 'ldavg-1' --chart-cols 'ldavg-5' --chart-cols 'ldavg-15' < sar-load.csv

echo working on sar-load-runq.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-load-runq.xlsx --combined-chart --worksheet-col hostname --category-col 'timestamp' --chart-cols 'runq-sz' --chart-cols 'ldavg-1' --chart-cols 'ldavg-5' --chart-cols 'ldavg-15' < sar-load.csv

echo working on sar-memory.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-memory.xlsx --combined-chart --worksheet-col hostname --category-col 'timestamp' --chart-cols 'frmpg/s' --chart-cols  'bufpg/s' < sar-mem.csv


echo working on sar-paging-rate.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-paging-rate.xlsx --combined-chart --worksheet-col hostname --category-col 'timestamp'  --chart-cols 'pgpgin/s' --chart-cols  'pgpgout/s' < sar-paging.csv

echo working on sar-swap-rate.xlsx
dynachart.pl --spreadsheet-file ${destDir}/sar-swap-rate.xlsx --combined-chart --worksheet-col hostname --category-col 'timestamp'  --chart-cols 'pswpin/s' --chart-cols 'pswpout/s' < sar-swap-stats.csv

Filter, rename and aggregate the data

Perhaps you want to see a subset of some data. Let’s say you wish too see the data for only a select group of devices; those making up the DATA diskgroup in an Oracle database.  In addition you would like the sar report to use names that match those seen by ASM. Rather than /dev/mapper/mp1, mp2,…, you would like to see DATA00, DATA01,…

In addition you would like to see the IOPS, read rate and write rate data for a set of disks aggregated per timestamp.

We can do that. The script remap.sh has an example (real life BTW) of how to map the ASM disk names to the linux device names, and create a new CSV file.

This query was used to map device names to disk names:

 

select
	'dm-name-' || substr(d.path,instr(d.path,'/',-1)+1,length(d.path) - instr(d.path,'/',-1)) path
	, g.name || lpad(d.disk_number,2,'0') name
from v$asm_disk d
join v$asm_diskgroup g on g.group_number = d.group_number
order by 2

15:10:17 SYSDBA> /

PATH			                   NAME
------------------------------ --------------------
dm-name-mpathmp1	             DATA00
dm-name-mpathnp1	             DATA01
dm-name-mpathop1	             DATA02
dm-name-mpathqp1	             DATA03
dm-name-mpathrp1	             DATA04
dm-name-mpathsp1	             DATA05
dm-name-mpathtp1	             DATA06
dm-name-mpathvp1	             DATA07
dm-name-mpathwp1	             DATA08
dm-name-mpathcp1	             FRA00

10 rows selected.

 

This information was used to then create a sed command to rewrite the CSV data as needed.

 


originalFile=sar-disk-original.csv
newFile=sar-disk.csv

sed \
	-e 's/dm-name-mpathmp1/DATA00/g' \
	-e 's/dm-name-mpathnp1/DATA01/g' \
	-e 's/dm-name-mpathop1/DATA02/g' \
	-e 's/dm-name-mpathqp1/DATA03/g' \
	-e 's/dm-name-mpathrp1/DATA04/g' \
	-e 's/dm-name-mpathsp1/DATA05/g' \
	-e 's/dm-name-mpathtp1/DATA06/g' \
	-e 's/dm-name-mpathvp1/DATA07/g' \
	-e 's/dm-name-mpathwp1/DATA08/g' \
	-e 's/dm-name-mpathcp1/FRA00/g' \
< $originalFile > $newFile

Following that csv-aggregator.sh and csv-aggregator.pl were used to filter and aggregate the data.  The result is a much smaller CSV file with data only for the DATA diskgroup, with the disk data rolled up to the disk group level.

Rather than post several hundred more lines of Perl here, I will recommend you to follow the links if you want to see how this aggregation and filtering is done. The command line though is shown here:

 


csvFile=sar-csv/sar-disk-test.csv

./csv-aggregator.pl --filter-cols DEV  --filter-vals 'DATA..'  --key-cols hostname --key-cols timestamp  --grouping-cols DEV  --agg-cols tps --agg-cols 'rd_sec/s' --agg-cols 'wr_sec/s'  --agg-cols 'avgrq-sz' --agg-cols 'avgqu-sz' < $csvFile

 

This is a chart taken directly from the Excel file created from this CSV data via dynachart.pl.

 

image

 

 

 

Installing Excel::Writer::XLSX

So that the dynachart.pl script will work, the Excel::Writer::XLSX Perl module must be installed.

There are several possible methods by which this may be done, but if you are not already fairly familiar with the Perl environment some of the methods are a little daunting.  As Oracle by default always installs Perl in ORACLE_HOME, you can make use of that existing installation.  These instructions will install the Excel::Writer::XLSX module and it’s dependencies directly into the Oracle Perl installation. Because of that, you would want to do this in a test environment, such as Virtual Machine running on your laptop.  It isn’t too likely that this would break something in the Oracle home, but then again, why take the chance?

An even better option would be to install your own Perl, and not worry about affecting any other software. This is not too difficult to do via Perlbrew. Check out https://perlbrew.pl/ for the simple instructions.

The following example installs Excel::Writer::XLSX in Oracle’s copy of Perl on a test server.  For my own use, I do use Perlbrew, but am just including the following as an example

 

[oracle@oravm01 ~]$ $ORACLE_HOME/perl/bin/perl -MCPAN -e shell

...

Would you like me to configure as much as possible automatically? [yes] yes

...

These next commands tell CPAN to automatically answer appropriately rather than prompt you to continually type ‘yes’.
This lasts the duration of this CPAN session.  Following that is the command to install Excel::Writer::XLSX.

 

cpan> o conf prerequisites_policy 'follow'
cpan> o conf build_requires_install_policy yes


cpan> install Excel::Writer::XLSX

...


cpan> exit
Terminal does not support GetHistory.
Lockfile removed.

 

Now test the installation.  This command simply tells Perl the we require version 99 of the module. The failure is normal, as only version 0.95 is installed.  A different message would appear if no version of the module was installed.

[oracle@oravm01 ~]$ $ORACLE_HOME/perl/bin/perl -MExcel::Writer::XLSX=99
Excel::Writer::XLSX version 99 required--this is only version 0.95 at /u01/app/oracle/product/11.2.0/db_1/perl/lib/5.10.0/Exporter/Heavy.pm line 122.
BEGIN failed--compilation aborted.
Categories: DBA Blogs

How Pythian Helped Nav Canada Migrate Their Oracle Database to a Microsoft Azure SQL Database

Wed, 2017-08-09 15:20

A common concern for businesses is optimizing database speed and performance, without raising costs.

Nav Canada supports flight planning with aviation weather, aeronautical information, and online flight planning. To address a need for a more cost-efficient environment for their critical flight planning data, while optimizing performance they decided to migrate from an on-premises Oracle system to Microsoft Azure SQL Database. They turned to the database and cloud experts at Pythian to assist them.

“Pythian’s breadth of expertise spans both traditional on-premises and cloud environments, making Pythian the perfect partner as we migrated critical workloads to the cloud.”

Pascal Chamma, Manager, Nav Canada Database Technology Services.

Pythian was tasked with creating a Proof of Concept (PoC) on an Azure database, while simulating the central SQL database. The project team demonstrated that the same level of performance could be achieved within a more cost-effective cloud environment. The PoC proved that the new system could run a process (3000 lines of PL/SQL code) every 20 seconds, performing calculations on up to 3 minutes of collected data.

Nav Canada is currently implementing the system across a production environment, with Pythian advising them as needed.

Read the full case study to learn how Nav Canada was able to migrate to the cloud to reduce costs, without sacrificing performance.

Categories: DBA Blogs

How Pythian Helped Nav Canada Migrate Their Oracle Database to a Microsoft Azure SQL Database

Wed, 2017-08-09 15:20

A common concern for businesses is optimizing database speed and performance, without raising costs.

Nav Canada supports flight planning with aviation weather, aeronautical information, and online flight planning. To address a need for a more cost-efficient environment for their critical flight planning data, while optimizing performance they decided to migrate from an on-premises Oracle system to Microsoft Azure SQL Database. They turned to the database and cloud experts at Pythian to assist them.

“Pythian’s breadth of expertise spans both traditional on-premises and cloud environments, making Pythian the perfect partner as we migrated critical workloads to the cloud.”

Pascal Chamma, Manager, Nav Canada Database Technology Services.

Pythian was tasked with creating a Proof of Concept (PoC) on an Azure database, while simulating the central SQL database. The project team demonstrated that the same level of performance could be achieved within a more cost-effective cloud environment. The PoC proved that the new system could run a process (3000 lines of PL/SQL code) every 20 seconds, performing calculations on up to 3 minutes of collected data.

Nav Canada is currently implementing the system across a production environment, with Pythian advising them as needed.

Read the full case study to learn how Nav Canada was able to migrate to the cloud to reduce costs, without sacrificing performance.

Categories: DBA Blogs

What You Should Know About Docker Containers for Oracle Data Integrator

Tue, 2017-08-01 14:12

Not long ago, Oracle adopted the Docker engine as one of the accepted platforms for its products and published a set of scripts and examples in the Oracle GitHub repository. This includes sets for rapid deployment of Oracle databases, Weblogic and for a number of other products. I tried some of the published docker implementations including Oracle database using my sandbox and it worked pretty well for me. Among the published containers, I didn’t find an example for Oracle Data Integrator (ODI) and decided to make one just for myself. I created it and found it to be useful when you need just to test one or another ODI scenario and wipe everything out after the tests.

I’ve decided to share my scripts with you and describe how it had been made. Just before publishing the post, I checked the Oracle git repository for docker and found a new “OracleDataIntegrator” template there. So, now we have the proper version from Oracle but even having the “official” deployment, you may find my version useful and I hope it may help you to understand the Docker engine.

To build my version, I used the Oracle developed images for Oracle Linux, Oracle Java, and Oracle Database as a basis for ODI making only minor modifications to have the final product better suited to my needs. You may find that other solutions are better and perhaps more adapted for your needs, but please keep in mind that my example is purely educational and not designed for any production use.

First we need the Docker engine on your machine. Depending on your platform, you need to download an appropriate engine from the Docker site and install it. The installation is pretty straightforward and easy. If you are on Linux you may need to add your user to the “docker” group. For example, I am adding user oracle to the group to be able to run docker containers and have access to the docker registry from the Oracle account:

[root@vm129-132 ~]# gpasswd -a oracle docker
Adding user oracle to group docker
[root@vm129-132 ~]# 

And make sure the docker service is up and running:

[root@vm129-132 ~]# systemctl status  docker.service
? docker.service - Docker Application Container Engine
   Loaded: loaded (/usr/lib/systemd/system/docker.service; disabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/docker.service.d
           ??docker-sysconfig.conf
   Active: active (running) since Tue 2017-06-06 12:29:35 EDT; 1 months 24 days ago

Then you need to clone or download the Oracle git repository for docker containers to be able to build necessary base images before starting with the ODI. On your system with docker, you either run “git clone https://github.com/oracle/docker-images.git” or go to the https://github.com/oracle/docker-images in your browser, download and unzip the full archive. There you will be able to find scripts to build docker images with all the different sets of software. Here is my listing after cloning it from the Oracle git repository:

[oracle@vm129-132 docker-images]$ ls -l
total 68
drwxr-xr-x. 4 oracle oinstall  4096 May 11 16:42 ContainerCloud
drwxr-xr-x. 6 oracle oinstall  4096 May 11 16:42 GlassFish
drwx------. 2 root   root     16384 May 11 16:54 lost+found
drwxr-xr-x. 2 oracle oinstall  4096 May 11 16:42 MySQL
drwxr-xr-x. 7 oracle oinstall  4096 May 11 16:42 NoSQL
drwxr-xr-x. 5 oracle oinstall  4096 May 11 16:42 OpenJDK
drwxr-xr-x. 4 oracle oinstall  4096 May 11 16:42 OracleCoherence
drwxr-xr-x. 5 oracle oinstall  4096 Jul 18 14:40 OracleDatabase
drwxr-xr-x. 4 oracle oinstall  4096 May 11 16:42 OracleHTTPServer
drwxr-xr-x. 6 oracle oinstall  4096 May 11 16:42 OracleJava
drwxr-xr-x. 4 oracle oinstall  4096 May 11 16:42 OracleTSAM
drwxr-xr-x. 4 oracle oinstall  4096 May 11 16:42 OracleTuxedo
drwxr-xr-x. 5 oracle oinstall  4096 May 11 16:42 OracleWebLogic
-rw-r--r--. 1 oracle oinstall  1588 Jul 17 09:10 README.md

(The listing represents the state as of May 2017 and may look different now)

Let’s continue and go step by step preparing our images. To understand the process, let me recall what we need for ODI in standalone mode. We need a Linux box with installed JDK or JRE and Oracle database as a repository. Also, if we plan to use the ODI Studio, it makes sense to have either X window or a VNC server installed on the box. We start with building a Linux image with Java JRE or JDK for our ODI. Oracle provides “OracleJava” docker configuration where we can build an image with Java 7 or 8:

[oracle@vm129-132 docker-images]$ ll OracleJava
total 20
drwxr-xr-x. 2 oracle oinstall 4096 May 11 16:42 java-7
drwxr-xr-x. 2 oracle oinstall 4096 May 12 10:56 java-8
-rw-r--r--. 1 oracle oinstall 1886 May 11 16:42 README.md
drwxr-xr-x. 4 oracle oinstall 4096 May 11 16:42 windows-java-7
drwxr-xr-x. 4 oracle oinstall 4096 May 11 16:42 windows-java-8

I used Oracle JDK 8 instead of a server JRE distribution. To make that happen, I’ve slightly modified the Dockerfile in the “OracleJava/java-8” directory replacing the server JRE distribution by JDK. It is optional and you may choose to keep JRE instead. In my case, the original string in the file was replaced from:

ENV JAVA_PKG=server-jre-8u*-linux-x64.tar.gz

to:

ENV JAVA_PKG=jdk-8u*-linux-x64.tar.gz

After that, I downloaded the JDK from the Oracle OTN site, put it in the folder and ran the build.sh script. The script prepares an image with Oracle Linux 7 in minimal configuration with the Oracle JDK 8 installed.

[oracle@vm129-132 java-8]$ ll
total 181204
-rwxr-xr-x. 1 oracle oinstall        47 May 11 16:42 build.sh
-rw-r--r--. 1 oracle oinstall       644 May 11 16:42 Dockerfile
-rw-r--r--. 1 oracle oinstall 185540433 May 12 10:43 jdk-8u131-linux-x64.tar.gz
-rw-r--r--. 1 oracle oinstall       263 May 11 16:42 server-jre-8u101-linux-x64.tar.gz.download
[oracle@vm129-132 java-8]$ cp Dockerfile Dockerfile.orig
[oracle@vm129-132 java-8]$ vi Dockerfile
[oracle@vm129-132 java-8]$ ./build.sh 
Sending build context to Docker daemon 185.5 MB
Step 1 : FROM oraclelinux:7-slim
7-slim: Pulling from library/oraclelinux
...............
Successfully built 381e0684cea2
[oracle@vm129-132 java-8]$ 
[oracle@vm129-132 java-8]$ docker images
REPOSITORY            TAG                 IMAGE ID            CREATED             SIZE
oracle/serverjre      8                   381e0684cea2        10 weeks ago        490.6 MB
oraclelinux           7-slim              442ebf722584        3 months ago        114.4 MB

The next step is optional, but it makes life a bit easier. We need a number of tools to be installed on top of our minimal installation. In Docker, you can create a container, modify it and save as another image using command “commit”. The beauty of this that you are not really doubling your space consumption by those incremental images. Docker will just add those extra changes you’ve made as a separate volume behind the scenes and will use original image plus your changes for the newly created image. You may think about it as a set of “snapshots”. So, I created a container with name “orajrevnc” from the “oracle/serverjre:8” image, installed additional packages including some diagnostic, configuration and other useful packages like a VNC server, vi editor, and others.

[oracle@vm129-132 java-8]$ docker run --name serverjrevnc -p 5901:5901 -ti oracle/serverjre:8
bash-4.2# 
bash-4.2# yum -y install vim
bash-4.2# yum -y install net-tools
bash-4.2# yum -y install telnet
bash-4.2# yum -y install strace
bash-4.2# yum -y install gcc
bash-4.2# yum -y install xterm
.....

After that, I used the container to commit it as a new image and saved it as “oracle/serverjrevnc:8”.

[oracle@vm129-132 java-8]$ docker commit serverjrevnc oracle/serverjrevnc:8
sha256:ac5b4d85fccc5427c92e65a6c3b1c06e3e8d04ffbe7725bcca1759a2165353d7
[oracle@vm129-132 java-8]$ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
oracle/serverjrevnc 8 ac5b4d85fccc 3 minutes ago 1.661 GB
oracle/serverjre 8 381e0684cea2 34 minutes ago 490.6 MB
oraclelinux 7-slim 442ebf722584 3 weeks ago 114.4 MB
[oracle@vm129-132 java-8]$

The container “orajrevnc” can be deleted now :

[oracle@vm129-132 java-8]$ docker stop orajrevnc
[oracle@vm129-132 java-8]$ docker rm orajrevnc

Now we have a basic Oracle Linux image with the java installed and all necessary tools and utilities we need. The image can be used for the next step as a basis for our Oracle Database image. The database will serve as a repository for our ODI. We go to the folder “docker-images/OracleDatabase/dockerfiles/ ” where we replace a line “FROM oraclelinux:7-slim” by “FROM oracle/serverjrevnc:8”, download the 12.2.0.1 EE database software from “Oracle site” and build the image for Oracle Database 12.2.0.1.

[oracle@vm129-132 java-8]$ cd ../../OracleDatabase/dockerfiles/
[oracle@vm129-132 dockerfiles]$ ll
total 16
drwxr-xr-x. 2 oracle oinstall 4096 May 11 16:42 11.2.0.2
drwxr-xr-x. 2 oracle oinstall 4096 Jul 18 14:18 12.1.0.2
drwxr-xr-x. 2 oracle oinstall 4096 Jul 25 14:37 12.2.0.1
-rwxr-xr-x. 1 oracle oinstall 3975 May 11 16:42 buildDockerImage.sh
[oracle@vm129-132 dockerfiles]$ vi 12.2.0.1/Dockerfile.ee
[oracle@vm129-132 dockerfiles]$ ll 12.2.0.1/*.zip
total 3372836
-rw-r--r--. 1 oracle oinstall 3453696911 May 12 09:26 linuxx64_12201_database.zip

[oracle@vm129-132 dockerfiles]$ ./buildDockerImage.sh -v 12.2.0.1 -e 
Checking if required packages are present and valid...
......

  Build completed in 906 seconds.
  
[oracle@vm129-132 dockerfiles]$

Here is the new list of images we have after building the Oracle Database image.

[oracle@vm129-132 dockerfiles]$ docker images
REPOSITORY            TAG                 IMAGE ID            CREATED             SIZE
oracle/database       12.2.0.1-ee         91aaea30a651        28 minutes ago      16.18 GB
oracle/serverjrevnc   8                   2d7be7d163dc        50 minutes ago      1.543 GB
oracle/serverjre      8                   c2c247029798        About an hour ago   490.5 MB
oraclelinux           7-slim              08a01cc7be97        5 weeks ago         114.4 MB

Having all necessary images prepared we can start building our ODI. I have prepared the scripts to build the Docker image and published them on “https://github.com/gotochkin/docker-images”. You can either download or clone them using the git software. Let’s have a look at the scripts and discuss what they do:

[oracle@vm129-132 ~]$ ls -l docker-images/ODI/dockerfiles/
total 8
drwxr-xr-x. 2 oracle oinstall 4096 Jul 27 14:18 12.2.1.2.6
-rwxr-xr-x. 1 oracle oinstall 3195 Jul 27 11:54 buildDockerImage.sh
[oracle@vm129-132 ~]$ ls -l docker-images/ODI/dockerfiles/12.2.1.2.6/
total 2390372
-rw-r--r--. 1 oracle oinstall        277 May  9 14:42 Checksum.standalone
-rw-r--r--. 1 oracle oinstall       8572 Jul 17 12:43 createOdiDomainForStandaloneAgent.py
-rw-r--r--. 1 oracle oinstall       4270 Jul 26 10:41 Dockerfile.standalone
-rw-r--r--. 1 oracle oinstall       1289 May  9 14:49 install.file
-rw-r--r--. 1 oracle oinstall       6477 Jul 14 13:03 oracledi.gz
-rw-r--r--. 1 oracle oinstall         55 May  8 13:57 oraInst.loc
-rw-rw-r--. 1 oracle oinstall       2695 May 15 15:08 rcuResponseFile.properties
-rw-r--r--. 1 oracle oinstall       7920 Jul 27 14:18 README.md
-rwxr-xr-x. 1 oracle oinstall       5332 Jul 14 15:51 runOracle.sh
-rwxr-xr-x. 1 oracle oinstall       4406 Jul 27 11:08 startAgent.sh
[oracle@vm129-132 ~]$ 

— buildDockerImage.sh – A script to build the ODI image. It takes parameter -v as the version for the ODI (so far only 12.2.1.2.6) and -t to tell that we are going to configure ODI Agent in standalone mode.
— Checksum.standalone – to verify checksum for the downloaded installation files which you will need to put into the docker-images/ODI/dockerfiles/12.2.1.2.6 directory
— createOdiDomainForStandaloneAgent.py – A python script to create a domain for an ODI standalone agent. The original script had been taken from the “Oracle tutorial” and slightly modified for our needs.
— Dockerfile.standalone – The instructions/script for Docker how to build the image for the ODI standalone agent.
— install.file – A response file for ODI silent installation.
— oracledi.gz – Gzipped files with ODI connection properties, it will be uncompressed into the $HOME/.odi directory.
— oraInst.loc – The configuration for Oracle Inventory.
— rcuResponseFile.properties – A parameter file for Repository Creation Utility.
— README.md – An instruction how to build an image.
— runOracle.sh – A startup script for the Oracle database which is going to be used as a repository.
— startAgent.sh – A script to configure and start the ODI agent.

We need to download ODI installation files fmw_12.2.1.2.6_odi_Disk1_1of2.zip and fmw_12.2.1.2.6_odi_Disk1_2of2.zip files from “Oracle OTN site” and put them to the docker-images/ODI/dockerfiles/12.2.1.2.6 folder.

At last, everything is ready and we can build the image using the buildDockerImage.sh script. Of course, you can build it without the script since it is only a wrapper for “docker build” command. The script just makes it bit easier.

[oracle@vm129-132 dockerfiles]$ ./buildDockerImage.sh -v 12.2.1.2.6 -t

..............

Successfully built 239bdf178fbe

  ODI Docker Image for 'standalone' version 12.2.1.2.6 is ready to be extended: 
    
    --> oracle/odi:12.2.1.2.6-standalone

  Build completed in 1032 seconds.

We can see the built image on the list:

[oracle@vm129-132 dockerfiles]$ docker images
REPOSITORY            TAG                     IMAGE ID            CREATED             SIZE
oracle/odi            12.2.1.2.6-standalone   239bdf178fbe        24 seconds ago      23.73 GB
oracle/database       12.2.0.1-ee             91aaea30a651        About an hour ago   16.18 GB
oracle/serverjrevnc   8                       2d7be7d163dc        About an hour ago   1.543 GB
oracle/serverjre      8                       c2c247029798        About an hour ago   490.5 MB
oraclelinux           7-slim                  08a01cc7be97        5 weeks ago         114.4 MB

We are ready to create our container with ODI. When we create the container it will do several steps which can be in general listed as:
— Create a container with Oracle Linux 7 with JDK and other supplemental packages.
— Create or start an Oracle database.
— Create an ODI repository, if it is not created already.
— Configure an ODI agent in the repository or adjust hostname for the agent if it has been already configured in the repository.
— Create a Weblogic domain for standalone ODI agent.
— Start the agent.

We have an option to create a fresh repository database every time when we deploy a new container using the command:

[oracle@vm129-132 dockerfiles]$ docker run --name oditest -p 1521:1521 -p 5500:5500 -p 5901:5901 -p 5902:5902 --env ORACLE_BASE=/opt/oracle --env ORACLE_HOME=/opt/oracle/product/12.2.0.1/dbhome_1 oracle/odi:12.2.1.2.6-standalone

And the database will be created inside docker file system (docker FS). It is convenient when you want to have a fresh repository every time but it takes a time to create a new database and, as result, your deployment will be delayed.

Or we can define a volume for database files out of the docker file system in which case you may reuse database for your containers again and again which can save some time during deployment.

[oracle@vm129-132 dockerfiles]$ docker run --name oditest -p 1521:1521 -p 5500:5500 -p 5901:5901 -p 5902:5902 -v /home/oracle/docker-images/OracleDatabase/oradata:/opt/oracle/oradata --env ORACLE_BASE=/opt/oracle --env ORACLE_HOME=/opt/oracle/product/12.2.0.1/dbhome_1 oracle/odi:12.2.1.2.6-standalone

Just be aware that if you want to use more than one container with the same Oracle database the scripts should be adapted. In the current implementation a new deployment will try to use the same repository.
After executing the command you will see the log of creation and in the end, you get the container with a running standalone ODI agent.

[oracle@vm129-132 ~]$ docker ps
CONTAINER ID        IMAGE                              COMMAND                  CREATED             STATUS              PORTS                                                                                        NAMES
71969452f5b3        oracle/odi:12.2.1.2.6-standalone   "/u01/app/oracle/star"   4 days ago          Up 2 days           0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp, 0.0.0.0:5901-5902->5901-5902/tcp, 5903/tcp   oditest
[oracle@vm129-132 ~]$ 

Inside the container you can start a vnc server and run an ODI studio.

[oracle@vm129-132 ~]$ docker exec -ti oditest bash
[oracle@71969452f5b3 Middleware]$ vncserver

You will require a password to access your desktops.

Password:

After starting the ODI studio you get the usual questions like whether you want to import any settings or to allow send data to Oracle about ODI studio usage. You don’t have anything to import from the previous installation since this is the first one, so, you can ignore and say “no”. The studio is eventually up and you need to connect to the repository. When you push the button to connect to the repository, you will be asked if you want to store the credentials in a wallet.

You have an option to refuse and use the pre-created connection and saved credentials.

Ideally this will make the usage bit easier and convenient. Of course, for any kind of production development it is strongly recommended to use a wallet and a proper password.
If you’re someone making your first steps in Docker, I hope this article has been helpful. In my opinion, Docker can be extremely useful for test deployments. The persistent database files makes the deployment easy and quick. I have some reservations about using Docker for any production deployments of Oracle databases, but that discussion deserves a dedicated post. Stay tuned.

Categories: DBA Blogs

What You Should Know About Docker Containers for Oracle Data Integrator

Tue, 2017-08-01 14:12

Not long ago, Oracle adopted the Docker engine as one of the accepted platforms for its products and published a set of scripts and examples in the Oracle GitHub repository. This includes sets for rapid deployment of Oracle databases, Weblogic and for a number of other products. I tried some of the published docker implementations including Oracle database using my sandbox and it worked pretty well for me. Among the published containers, I didn’t find an example for Oracle Data Integrator (ODI) and decided to make one just for myself. I created it and found it to be useful when you need just to test one or another ODI scenario and wipe everything out after the tests.

I’ve decided to share my scripts with you and describe how it had been made. Just before publishing the post, I checked the Oracle git repository for docker and found a new “OracleDataIntegrator” template there. So, now we have the proper version from Oracle but even having the “official” deployment, you may find my version useful and I hope it may help you to understand the Docker engine.

To build my version, I used the Oracle developed images for Oracle Linux, Oracle Java, and Oracle Database as a basis for ODI making only minor modifications to have the final product better suited to my needs. You may find that other solutions are better and perhaps more adapted for your needs, but please keep in mind that my example is purely educational and not designed for any production use.

First we need the Docker engine on your machine. Depending on your platform, you need to download an appropriate engine from the Docker site and install it. The installation is pretty straightforward and easy. If you are on Linux you may need to add your user to the “docker” group. For example, I am adding user oracle to the group to be able to run docker containers and have access to the docker registry from the Oracle account:

[root@vm129-132 ~]# gpasswd -a oracle docker
Adding user oracle to group docker
[root@vm129-132 ~]# 

And make sure the docker service is up and running:

[root@vm129-132 ~]# systemctl status  docker.service
? docker.service - Docker Application Container Engine
   Loaded: loaded (/usr/lib/systemd/system/docker.service; disabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/docker.service.d
           ??docker-sysconfig.conf
   Active: active (running) since Tue 2017-06-06 12:29:35 EDT; 1 months 24 days ago

Then you need to clone or download the Oracle git repository for docker containers to be able to build necessary base images before starting with the ODI. On your system with docker, you either run “git clone https://github.com/oracle/docker-images.git” or go to the https://github.com/oracle/docker-images in your browser, download and unzip the full archive. There you will be able to find scripts to build docker images with all the different sets of software. Here is my listing after cloning it from the Oracle git repository:

[oracle@vm129-132 docker-images]$ ls -l
total 68
drwxr-xr-x. 4 oracle oinstall  4096 May 11 16:42 ContainerCloud
drwxr-xr-x. 6 oracle oinstall  4096 May 11 16:42 GlassFish
drwx------. 2 root   root     16384 May 11 16:54 lost+found
drwxr-xr-x. 2 oracle oinstall  4096 May 11 16:42 MySQL
drwxr-xr-x. 7 oracle oinstall  4096 May 11 16:42 NoSQL
drwxr-xr-x. 5 oracle oinstall  4096 May 11 16:42 OpenJDK
drwxr-xr-x. 4 oracle oinstall  4096 May 11 16:42 OracleCoherence
drwxr-xr-x. 5 oracle oinstall  4096 Jul 18 14:40 OracleDatabase
drwxr-xr-x. 4 oracle oinstall  4096 May 11 16:42 OracleHTTPServer
drwxr-xr-x. 6 oracle oinstall  4096 May 11 16:42 OracleJava
drwxr-xr-x. 4 oracle oinstall  4096 May 11 16:42 OracleTSAM
drwxr-xr-x. 4 oracle oinstall  4096 May 11 16:42 OracleTuxedo
drwxr-xr-x. 5 oracle oinstall  4096 May 11 16:42 OracleWebLogic
-rw-r--r--. 1 oracle oinstall  1588 Jul 17 09:10 README.md

(The listing represents the state as of May 2017 and may look different now)

Let’s continue and go step by step preparing our images. To understand the process, let me recall what we need for ODI in standalone mode. We need a Linux box with installed JDK or JRE and Oracle database as a repository. Also, if we plan to use the ODI Studio, it makes sense to have either X window or a VNC server installed on the box. We start with building a Linux image with Java JRE or JDK for our ODI. Oracle provides “OracleJava” docker configuration where we can build an image with Java 7 or 8:

[oracle@vm129-132 docker-images]$ ll OracleJava
total 20
drwxr-xr-x. 2 oracle oinstall 4096 May 11 16:42 java-7
drwxr-xr-x. 2 oracle oinstall 4096 May 12 10:56 java-8
-rw-r--r--. 1 oracle oinstall 1886 May 11 16:42 README.md
drwxr-xr-x. 4 oracle oinstall 4096 May 11 16:42 windows-java-7
drwxr-xr-x. 4 oracle oinstall 4096 May 11 16:42 windows-java-8

I used Oracle JDK 8 instead of a server JRE distribution. To make that happen, I’ve slightly modified the Dockerfile in the “OracleJava/java-8” directory replacing the server JRE distribution by JDK. It is optional and you may choose to keep JRE instead. In my case, the original string in the file was replaced from:

ENV JAVA_PKG=server-jre-8u*-linux-x64.tar.gz

to:

ENV JAVA_PKG=jdk-8u*-linux-x64.tar.gz

After that, I downloaded the JDK from the Oracle OTN site, put it in the folder and ran the build.sh script. The script prepares an image with Oracle Linux 7 in minimal configuration with the Oracle JDK 8 installed.

[oracle@vm129-132 java-8]$ ll
total 181204
-rwxr-xr-x. 1 oracle oinstall        47 May 11 16:42 build.sh
-rw-r--r--. 1 oracle oinstall       644 May 11 16:42 Dockerfile
-rw-r--r--. 1 oracle oinstall 185540433 May 12 10:43 jdk-8u131-linux-x64.tar.gz
-rw-r--r--. 1 oracle oinstall       263 May 11 16:42 server-jre-8u101-linux-x64.tar.gz.download
[oracle@vm129-132 java-8]$ cp Dockerfile Dockerfile.orig
[oracle@vm129-132 java-8]$ vi Dockerfile
[oracle@vm129-132 java-8]$ ./build.sh 
Sending build context to Docker daemon 185.5 MB
Step 1 : FROM oraclelinux:7-slim
7-slim: Pulling from library/oraclelinux
...............
Successfully built 381e0684cea2
[oracle@vm129-132 java-8]$ 
[oracle@vm129-132 java-8]$ docker images
REPOSITORY            TAG                 IMAGE ID            CREATED             SIZE
oracle/serverjre      8                   381e0684cea2        10 weeks ago        490.6 MB
oraclelinux           7-slim              442ebf722584        3 months ago        114.4 MB

The next step is optional, but it makes life a bit easier. We need a number of tools to be installed on top of our minimal installation. In Docker, you can create a container, modify it and save as another image using command “commit”. The beauty of this that you are not really doubling your space consumption by those incremental images. Docker will just add those extra changes you’ve made as a separate volume behind the scenes and will use original image plus your changes for the newly created image. You may think about it as a set of “snapshots”. So, I created a container with name “orajrevnc” from the “oracle/serverjre:8” image, installed additional packages including some diagnostic, configuration and other useful packages like a VNC server, vi editor, and others.

[oracle@vm129-132 java-8]$ docker run --name serverjrevnc -p 5901:5901 -ti oracle/serverjre:8
bash-4.2# 
bash-4.2# yum -y install vim
bash-4.2# yum -y install net-tools
bash-4.2# yum -y install telnet
bash-4.2# yum -y install strace
bash-4.2# yum -y install gcc
bash-4.2# yum -y install xterm
.....

After that, I used the container to commit it as a new image and saved it as “oracle/serverjrevnc:8”.

[oracle@vm129-132 java-8]$ docker commit serverjrevnc oracle/serverjrevnc:8
sha256:ac5b4d85fccc5427c92e65a6c3b1c06e3e8d04ffbe7725bcca1759a2165353d7
[oracle@vm129-132 java-8]$ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
oracle/serverjrevnc 8 ac5b4d85fccc 3 minutes ago 1.661 GB
oracle/serverjre 8 381e0684cea2 34 minutes ago 490.6 MB
oraclelinux 7-slim 442ebf722584 3 weeks ago 114.4 MB
[oracle@vm129-132 java-8]$

The container “orajrevnc” can be deleted now :

[oracle@vm129-132 java-8]$ docker stop orajrevnc
[oracle@vm129-132 java-8]$ docker rm orajrevnc

Now we have a basic Oracle Linux image with the java installed and all necessary tools and utilities we need. The image can be used for the next step as a basis for our Oracle Database image. The database will serve as a repository for our ODI. We go to the folder “docker-images/OracleDatabase/dockerfiles/ ” where we replace a line “FROM oraclelinux:7-slim” by “FROM oracle/serverjrevnc:8”, download the 12.2.0.1 EE database software from “Oracle site” and build the image for Oracle Database 12.2.0.1.

[oracle@vm129-132 java-8]$ cd ../../OracleDatabase/dockerfiles/
[oracle@vm129-132 dockerfiles]$ ll
total 16
drwxr-xr-x. 2 oracle oinstall 4096 May 11 16:42 11.2.0.2
drwxr-xr-x. 2 oracle oinstall 4096 Jul 18 14:18 12.1.0.2
drwxr-xr-x. 2 oracle oinstall 4096 Jul 25 14:37 12.2.0.1
-rwxr-xr-x. 1 oracle oinstall 3975 May 11 16:42 buildDockerImage.sh
[oracle@vm129-132 dockerfiles]$ vi 12.2.0.1/Dockerfile.ee
[oracle@vm129-132 dockerfiles]$ ll 12.2.0.1/*.zip
total 3372836
-rw-r--r--. 1 oracle oinstall 3453696911 May 12 09:26 linuxx64_12201_database.zip

[oracle@vm129-132 dockerfiles]$ ./buildDockerImage.sh -v 12.2.0.1 -e 
Checking if required packages are present and valid...
......

  Build completed in 906 seconds.
  
[oracle@vm129-132 dockerfiles]$

Here is the new list of images we have after building the Oracle Database image.

[oracle@vm129-132 dockerfiles]$ docker images
REPOSITORY            TAG                 IMAGE ID            CREATED             SIZE
oracle/database       12.2.0.1-ee         91aaea30a651        28 minutes ago      16.18 GB
oracle/serverjrevnc   8                   2d7be7d163dc        50 minutes ago      1.543 GB
oracle/serverjre      8                   c2c247029798        About an hour ago   490.5 MB
oraclelinux           7-slim              08a01cc7be97        5 weeks ago         114.4 MB

Having all necessary images prepared we can start building our ODI. I have prepared the scripts to build the Docker image and published them on “https://github.com/gotochkin/docker-images”. You can either download or clone them using the git software. Let’s have a look at the scripts and discuss what they do:

[oracle@vm129-132 ~]$ ls -l docker-images/ODI/dockerfiles/
total 8
drwxr-xr-x. 2 oracle oinstall 4096 Jul 27 14:18 12.2.1.2.6
-rwxr-xr-x. 1 oracle oinstall 3195 Jul 27 11:54 buildDockerImage.sh
[oracle@vm129-132 ~]$ ls -l docker-images/ODI/dockerfiles/12.2.1.2.6/
total 2390372
-rw-r--r--. 1 oracle oinstall        277 May  9 14:42 Checksum.standalone
-rw-r--r--. 1 oracle oinstall       8572 Jul 17 12:43 createOdiDomainForStandaloneAgent.py
-rw-r--r--. 1 oracle oinstall       4270 Jul 26 10:41 Dockerfile.standalone
-rw-r--r--. 1 oracle oinstall       1289 May  9 14:49 install.file
-rw-r--r--. 1 oracle oinstall       6477 Jul 14 13:03 oracledi.gz
-rw-r--r--. 1 oracle oinstall         55 May  8 13:57 oraInst.loc
-rw-rw-r--. 1 oracle oinstall       2695 May 15 15:08 rcuResponseFile.properties
-rw-r--r--. 1 oracle oinstall       7920 Jul 27 14:18 README.md
-rwxr-xr-x. 1 oracle oinstall       5332 Jul 14 15:51 runOracle.sh
-rwxr-xr-x. 1 oracle oinstall       4406 Jul 27 11:08 startAgent.sh
[oracle@vm129-132 ~]$ 

— buildDockerImage.sh – A script to build the ODI image. It takes parameter -v as the version for the ODI (so far only 12.2.1.2.6) and -t to tell that we are going to configure ODI Agent in standalone mode.
— Checksum.standalone – to verify checksum for the downloaded installation files which you will need to put into the docker-images/ODI/dockerfiles/12.2.1.2.6 directory
— createOdiDomainForStandaloneAgent.py – A python script to create a domain for an ODI standalone agent. The original script had been taken from the “Oracle tutorial” and slightly modified for our needs.
— Dockerfile.standalone – The instructions/script for Docker how to build the image for the ODI standalone agent.
— install.file – A response file for ODI silent installation.
— oracledi.gz – Gzipped files with ODI connection properties, it will be uncompressed into the $HOME/.odi directory.
— oraInst.loc – The configuration for Oracle Inventory.
— rcuResponseFile.properties – A parameter file for Repository Creation Utility.
— README.md – An instruction how to build an image.
— runOracle.sh – A startup script for the Oracle database which is going to be used as a repository.
— startAgent.sh – A script to configure and start the ODI agent.

We need to download ODI installation files fmw_12.2.1.2.6_odi_Disk1_1of2.zip and fmw_12.2.1.2.6_odi_Disk1_2of2.zip files from “Oracle OTN site” and put them to the docker-images/ODI/dockerfiles/12.2.1.2.6 folder.

At last, everything is ready and we can build the image using the buildDockerImage.sh script. Of course, you can build it without the script since it is only a wrapper for “docker build” command. The script just makes it bit easier.

[oracle@vm129-132 dockerfiles]$ ./buildDockerImage.sh -v 12.2.1.2.6 -t

..............

Successfully built 239bdf178fbe

  ODI Docker Image for 'standalone' version 12.2.1.2.6 is ready to be extended: 
    
    --> oracle/odi:12.2.1.2.6-standalone

  Build completed in 1032 seconds.

We can see the built image on the list:

[oracle@vm129-132 dockerfiles]$ docker images
REPOSITORY            TAG                     IMAGE ID            CREATED             SIZE
oracle/odi            12.2.1.2.6-standalone   239bdf178fbe        24 seconds ago      23.73 GB
oracle/database       12.2.0.1-ee             91aaea30a651        About an hour ago   16.18 GB
oracle/serverjrevnc   8                       2d7be7d163dc        About an hour ago   1.543 GB
oracle/serverjre      8                       c2c247029798        About an hour ago   490.5 MB
oraclelinux           7-slim                  08a01cc7be97        5 weeks ago         114.4 MB

We are ready to create our container with ODI. When we create the container it will do several steps which can be in general listed as:
— Create a container with Oracle Linux 7 with JDK and other supplemental packages.
— Create or start an Oracle database.
— Create an ODI repository, if it is not created already.
— Configure an ODI agent in the repository or adjust hostname for the agent if it has been already configured in the repository.
— Create a Weblogic domain for standalone ODI agent.
— Start the agent.

We have an option to create a fresh repository database every time when we deploy a new container using the command:

[oracle@vm129-132 dockerfiles]$ docker run --name oditest -p 1521:1521 -p 5500:5500 -p 5901:5901 -p 5902:5902 --env ORACLE_BASE=/opt/oracle --env ORACLE_HOME=/opt/oracle/product/12.2.0.1/dbhome_1 oracle/odi:12.2.1.2.6-standalone

And the database will be created inside docker file system (docker FS). It is convenient when you want to have a fresh repository every time but it takes a time to create a new database and, as result, your deployment will be delayed.

Or we can define a volume for database files out of the docker file system in which case you may reuse database for your containers again and again which can save some time during deployment.

[oracle@vm129-132 dockerfiles]$ docker run --name oditest -p 1521:1521 -p 5500:5500 -p 5901:5901 -p 5902:5902 -v /home/oracle/docker-images/OracleDatabase/oradata:/opt/oracle/oradata --env ORACLE_BASE=/opt/oracle --env ORACLE_HOME=/opt/oracle/product/12.2.0.1/dbhome_1 oracle/odi:12.2.1.2.6-standalone

Just be aware that if you want to use more than one container with the same Oracle database the scripts should be adapted. In the current implementation a new deployment will try to use the same repository.
After executing the command you will see the log of creation and in the end, you get the container with a running standalone ODI agent.

[oracle@vm129-132 ~]$ docker ps
CONTAINER ID        IMAGE                              COMMAND                  CREATED             STATUS              PORTS                                                                                        NAMES
71969452f5b3        oracle/odi:12.2.1.2.6-standalone   "/u01/app/oracle/star"   4 days ago          Up 2 days           0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp, 0.0.0.0:5901-5902->5901-5902/tcp, 5903/tcp   oditest
[oracle@vm129-132 ~]$ 

Inside the container you can start a vnc server and run an ODI studio.

[oracle@vm129-132 ~]$ docker exec -ti oditest bash
[oracle@71969452f5b3 Middleware]$ vncserver

You will require a password to access your desktops.

Password:

After starting the ODI studio you get the usual questions like whether you want to import any settings or to allow send data to Oracle about ODI studio usage. You don’t have anything to import from the previous installation since this is the first one, so, you can ignore and say “no”. The studio is eventually up and you need to connect to the repository. When you push the button to connect to the repository, you will be asked if you want to store the credentials in a wallet.

You have an option to refuse and use the pre-created connection and saved credentials.

Ideally this will make the usage bit easier and convenient. Of course, for any kind of production development it is strongly recommended to use a wallet and a proper password.
If you’re someone making your first steps in Docker, I hope this article has been helpful. In my opinion, Docker can be extremely useful for test deployments. The persistent database files makes the deployment easy and quick. I have some reservations about using Docker for any production deployments of Oracle databases, but that discussion deserves a dedicated post. Stay tuned.

Categories: DBA Blogs

Oracle Scheduler Fail To Send Email Notifications

Fri, 2017-07-21 13:32

In this blog post I would like to share an interesting issue we encountered couple of months ago related to scheduler job email notifications. As some of you may know, starting with Oracle 11.2 you can subscribe to receive notification emails from a scheduler job. You can define an email to be sent on various job events (job_started, job_completed, job_failed etc.). The job email notification is defined with DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION procedure.

I am assuming you already have a configured and working SMTP server. If not, that can be done with DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE (attributes: email_server and email_sender).

The issue we encountered was on database version 12.1. After configuring the scheduler jobs and email notification lists, emails were not sent out.

This blog post should give you some guidance on how you can troubleshoot and properly define email job notifications.

The problem:

In our case, we used one “system” account to manage the job email notification subscription. Some of you might use the same approach, having a single and separate account used to manage job email notifications for all other accounts.

Let’s assume we have a job called JOB1 defined in schema IARSOV.

exec dbms_scheduler.create_job(job_name => 'JOB1', job_type => 'PLSQL_BLOCK', job_action => 'begin null; end;');

PL/SQL procedure successfully completed.

If we now try to add the a job notification email for IARSOV.JOB1 with the SYSTEM user we should receive an error: “ORA-24093: AQ agent SCHED$_AGT2$_xx not granted privileges of database user”.

exec dbms_scheduler.add_job_email_notification('IARSOV.JOB1','arsov@pythian.com');

BEGIN dbms_scheduler.add_job_email_notification('IARSOV.JOB1','arsov@pythian.com'); END;

*
ERROR at line 1:
ORA-24093: AQ agent SCHED$_AGT2$_101 not granted privileges of database user
SYSTEM
ORA-06512: at "SYS.DBMS_ISCHED", line 7847
ORA-06512: at "SYS.DBMS_SCHEDULER", line 4063
ORA-06512: at line 1

As a workaround we can grant the necessary privileges with the DBMS_AQADM.ENABLE_DB_ACCESS package used for managing Oracle Database Advanced Queuing (AQ).

exec DBMS_AQADM.ENABLE_DB_ACCESS(agent_name  => 'SCHED$_AGT2$_101', db_username => 'SYSTEM');

PL/SQL procedure successfully completed.

We can confirm the granted privileges via the DBA_AQ_AGENT_PRIVS dictionary view (Line 11):

set lines 120
col agent_name for a40
col db_username for a40

select * from dba_aq_agent_privs;

AGENT_NAME                     DB_USERNAME                    HTTP SMTP
------------------------------ ------------------------------ ---- ----
DB12C_3938_ORCL11G             DBSNMP                         NO   NO
SCHED$_AGT2$_101               IARSOV                         NO   NO
SCHED$_AGT2$_101               SYSTEM                         NO   NO
SCHEDULER$_EVENT_AGENT         SYS                            NO   NO
SCHEDULER$_REMDB_AGENT         SYS                            NO   NO
SERVER_ALERT                   SYS                            NO   NO
HAE_SUB                                                       NO   NO

7 rows selected.

Let’s now try to define job email notification for IARSOV.JOB1:

exec dbms_scheduler.add_job_email_notification('IARSOV.JOB1','arsov@pythian.com');

PL/SQL procedure successfully completed.

set pages 200
set lines 200
col owner for a40
col job_name for a40
col recipient for a20
select owner, job_name, recipient, event from dba_scheduler_notifications where job_name = 'JOB1';

OWNER                          JOB_NAME                       RECIPIENT            EVENT
------------------------------ ------------------------------ -------------------- -------------------
IARSOV                         JOB1                           arsov@pythian.com    JOB_FAILED
IARSOV                         JOB1                           arsov@pythian.com    JOB_BROKEN
IARSOV                         JOB1                           arsov@pythian.com    JOB_SCH_LIM_REACHED
IARSOV                         JOB1                           arsov@pythian.com    JOB_CHAIN_STALLED
IARSOV                         JOB1                           arsov@pythian.com    JOB_OVER_MAX_DUR

The notification has been successfully defined, however, upon testing the events email was not sent. In our case, the events were ending up in the exception queue AQ$_SCHEDULER$_EVENT_QTAB_E and there was not much information we could derive from the AQ$ scheduler related tables.

Troubleshooting:

The DBA_SUBSCR_REGISTRATIONS view contains mapping definitions for each schema showing which event_queue:consumer_group it is subscribed to. If we check the subscription definition for IARSOV user we can see the event_queue:consumer_group is linked to USER# 5 which is the SYSTEM user. In this case IARSOV’s AQ agent SCHED$_AGT2$_101 is linked to wrong user.

What we’re interested in is the association between SUBSCRIPTION_NAME and USER# columns.

SQL> select subscription_name, user#, status from dba_subscr_registrations;

SUBSCRIPTION_NAME                                                                     USER# STATUS
-------------------------------------------------------------------------------- ---------- --------
"SYS"."SCHEDULER$_EVENT_QUEUE":"SCHED$_AGT2$_101"                                         5 DB REG


SQL> select username, user_id from dba_users where user_id = 5;

USERNAME                          USER_ID
------------------------------ ----------
SYSTEM                                  5

In this case the emails won’t be sent out because the subscription registration is not properly initialized (linked) with the correct user (schema). In order for the notifications to work we need the proper link between the agent and the agent’s owner. In this case “SYS”.”SCHEDULER$_EVENT_QUEUE”:”SCHED$_AGT2$_101″ and the IARSOV schema should be properly linked – notice that the user’s ID is also part of the agent name.

What we now need to do is to drop all job email notifications (in this case only one) for IARSOV jobs. When dropping the last job email notification the subscription registration will be removed from DBA_SUBSCR_REGISTRATIONS.
However, note that you have to drop the job email notifications as the user to which the subscription registration is defined, in this case the SYSTEM user.

Hint: If you don’t know the password for the schema you need to connect to, you can can use the Proxy Authenticated Connection feature as documented in the blog article The power of the Oracle Database “proxy authenticated” connections.

SQL> show user;

USER is "SYSTEM"

SQL>
SQL> exec dbms_scheduler.remove_job_email_notification('IARSOV.JOB1','arsov@pythian.com');

PL/SQL procedure successfully completed.

SQL> select subscription_name, user#, status from dba_subscr_registrations;

no rows selected

Once we clear the subscription, we can properly initialize the link by adding the first job notification with the job schema’s owner. This will properly initialize the event_queue:consumer_group with the correct user. After that we can add multiple job notifications from other users as long as we have appropriate privileges granted.

--as SYSTEM user.

SQL> show user;
USER is "SYSTEM"
SQL>
SQL> select subscription_name, user#, status from dba_subscr_registrations;

SUBSCRIPTION_NAME                                       USER# STATUS
-------------------------------------------------- ---------- --------
"SYS"."SCHEDULER$_EVENT_QUEUE":"ILM_AGENT"                  0 DB REG

SQL>

--as IARSOV user.

SQL> show user;
USER is "IARSOV"
SQL>
SQL> exec DBMS_SCHEDULER.add_job_email_notification (job_name => 'IARSOV.JOB1', recipients => 'arsov@pyhian.com');

PL/SQL procedure successfully completed.

SQL>
SQL>

--as SYSTEM user.

SQL> show user;
USER is "SYSTEM"
SQL>
SQL> select subscription_name, user#, status from dba_subscr_registrations;

SUBSCRIPTION_NAME                                       USER# STATUS
-------------------------------------------------- ---------- --------
"SYS"."SCHEDULER$_EVENT_QUEUE":"ILM_AGENT"                  0 DB REG
"SYS"."SCHEDULER$_EVENT_QUEUE":"SCHED$_AGT2$_101"         101 DB REG

SQL>
SQL>
SQL> select username from dba_users where user_id = 101;

USERNAME
----------------
IARSOV

SQL>

Conclusion:

If you decide to use scheduler job email notifications, and also prefer that the notifications management is done by a single user (such as “system”) I would advise you to create a “dummy” job notification (with the job owner’s schema) as soon as you create the first scheduler job. This will link the event_queue:consumer_group to the proper user. Afterwards, once you define the rest of the scheduler job notifications (under the common “system” user), you can clean-up that initial “dummy” job notification.

This behavior (bug) is fixed in 12.2 so that the notifications always go through the user’s AQ agent which defines the notifications.

Categories: DBA Blogs

Oracle Scheduler Fail To Send Email Notifications

Fri, 2017-07-21 13:32

In this blog post I would like to share an interesting issue we encountered couple of months ago related to scheduler job email notifications. As some of you may know, starting with Oracle 11.2 you can subscribe to receive notification emails from a scheduler job. You can define an email to be sent on various job events (job_started, job_completed, job_failed etc.). The job email notification is defined with DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION procedure.

I am assuming you already have a configured and working SMTP server. If not, that can be done with DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE (attributes: email_server and email_sender).

The issue we encountered was on database version 12.1. After configuring the scheduler jobs and email notification lists, emails were not sent out.

This blog post should give you some guidance on how you can troubleshoot and properly define email job notifications.

The problem:

In our case, we used one “system” account to manage the job email notification subscription. Some of you might use the same approach, having a single and separate account used to manage job email notifications for all other accounts.

Let’s assume we have a job called JOB1 defined in schema IARSOV.

exec dbms_scheduler.create_job(job_name => 'JOB1', job_type => 'PLSQL_BLOCK', job_action => 'begin null; end;');

PL/SQL procedure successfully completed.

If we now try to add the a job notification email for IARSOV.JOB1 with the SYSTEM user we should receive an error: “ORA-24093: AQ agent SCHED$_AGT2$_xx not granted privileges of database user”.

exec dbms_scheduler.add_job_email_notification('IARSOV.JOB1','arsov@pythian.com');

BEGIN dbms_scheduler.add_job_email_notification('IARSOV.JOB1','arsov@pythian.com'); END;

*
ERROR at line 1:
ORA-24093: AQ agent SCHED$_AGT2$_101 not granted privileges of database user
SYSTEM
ORA-06512: at "SYS.DBMS_ISCHED", line 7847
ORA-06512: at "SYS.DBMS_SCHEDULER", line 4063
ORA-06512: at line 1

As a workaround we can grant the necessary privileges with the DBMS_AQADM.ENABLE_DB_ACCESS package used for managing Oracle Database Advanced Queuing (AQ).

exec DBMS_AQADM.ENABLE_DB_ACCESS(agent_name  => 'SCHED$_AGT2$_101', db_username => 'SYSTEM');

PL/SQL procedure successfully completed.

We can confirm the granted privileges via the DBA_AQ_AGENT_PRIVS dictionary view (Line 11):

set lines 120
col agent_name for a40
col db_username for a40

select * from dba_aq_agent_privs;

AGENT_NAME                     DB_USERNAME                    HTTP SMTP
------------------------------ ------------------------------ ---- ----
DB12C_3938_ORCL11G             DBSNMP                         NO   NO
SCHED$_AGT2$_101               IARSOV                         NO   NO
SCHED$_AGT2$_101               SYSTEM                         NO   NO
SCHEDULER$_EVENT_AGENT         SYS                            NO   NO
SCHEDULER$_REMDB_AGENT         SYS                            NO   NO
SERVER_ALERT                   SYS                            NO   NO
HAE_SUB                                                       NO   NO

7 rows selected.

Let’s now try to define job email notification for IARSOV.JOB1:

exec dbms_scheduler.add_job_email_notification('IARSOV.JOB1','arsov@pythian.com');

PL/SQL procedure successfully completed.

set pages 200
set lines 200
col owner for a40
col job_name for a40
col recipient for a20
select owner, job_name, recipient, event from dba_scheduler_notifications where job_name = 'JOB1';

OWNER                          JOB_NAME                       RECIPIENT            EVENT
------------------------------ ------------------------------ -------------------- -------------------
IARSOV                         JOB1                           arsov@pythian.com    JOB_FAILED
IARSOV                         JOB1                           arsov@pythian.com    JOB_BROKEN
IARSOV                         JOB1                           arsov@pythian.com    JOB_SCH_LIM_REACHED
IARSOV                         JOB1                           arsov@pythian.com    JOB_CHAIN_STALLED
IARSOV                         JOB1                           arsov@pythian.com    JOB_OVER_MAX_DUR

The notification has been successfully defined, however, upon testing the events email was not sent. In our case, the events were ending up in the exception queue AQ$_SCHEDULER$_EVENT_QTAB_E and there was not much information we could derive from the AQ$ scheduler related tables.

Troubleshooting:

The DBA_SUBSCR_REGISTRATIONS view contains mapping definitions for each schema showing which event_queue:consumer_group it is subscribed to. If we check the subscription definition for IARSOV user we can see the event_queue:consumer_group is linked to USER# 5 which is the SYSTEM user. In this case IARSOV’s AQ agent SCHED$_AGT2$_101 is linked to wrong user.

What we’re interested in is the association between SUBSCRIPTION_NAME and USER# columns.

SQL> select subscription_name, user#, status from dba_subscr_registrations;

SUBSCRIPTION_NAME                                                                     USER# STATUS
-------------------------------------------------------------------------------- ---------- --------
"SYS"."SCHEDULER$_EVENT_QUEUE":"SCHED$_AGT2$_101"                                         5 DB REG


SQL> select username, user_id from dba_users where user_id = 5;

USERNAME                          USER_ID
------------------------------ ----------
SYSTEM                                  5

In this case the emails won’t be sent out because the subscription registration is not properly initialized (linked) with the correct user (schema). In order for the notifications to work we need the proper link between the agent and the agent’s owner. In this case “SYS”.”SCHEDULER$_EVENT_QUEUE”:”SCHED$_AGT2$_101″ and the IARSOV schema should be properly linked – notice that the user’s ID is also part of the agent name.

What we now need to do is to drop all job email notifications (in this case only one) for IARSOV jobs. When dropping the last job email notification the subscription registration will be removed from DBA_SUBSCR_REGISTRATIONS.
However, note that you have to drop the job email notifications as the user to which the subscription registration is defined, in this case the SYSTEM user.

Hint: If you don’t know the password for the schema you need to connect to, you can can use the Proxy Authenticated Connection feature as documented in the blog article The power of the Oracle Database “proxy authenticated” connections.

SQL> show user;

USER is "SYSTEM"

SQL>
SQL> exec dbms_scheduler.remove_job_email_notification('IARSOV.JOB1','arsov@pythian.com');

PL/SQL procedure successfully completed.

SQL> select subscription_name, user#, status from dba_subscr_registrations;

no rows selected

Once we clear the subscription, we can properly initialize the link by adding the first job notification with the job schema’s owner. This will properly initialize the event_queue:consumer_group with the correct user. After that we can add multiple job notifications from other users as long as we have appropriate privileges granted.

--as SYSTEM user.

SQL> show user;
USER is "SYSTEM"
SQL>
SQL> select subscription_name, user#, status from dba_subscr_registrations;

SUBSCRIPTION_NAME                                       USER# STATUS
-------------------------------------------------- ---------- --------
"SYS"."SCHEDULER$_EVENT_QUEUE":"ILM_AGENT"                  0 DB REG

SQL>

--as IARSOV user.

SQL> show user;
USER is "IARSOV"
SQL>
SQL> exec DBMS_SCHEDULER.add_job_email_notification (job_name => 'IARSOV.JOB1', recipients => 'arsov@pyhian.com');

PL/SQL procedure successfully completed.

SQL>
SQL>

--as SYSTEM user.

SQL> show user;
USER is "SYSTEM"
SQL>
SQL> select subscription_name, user#, status from dba_subscr_registrations;

SUBSCRIPTION_NAME                                       USER# STATUS
-------------------------------------------------- ---------- --------
"SYS"."SCHEDULER$_EVENT_QUEUE":"ILM_AGENT"                  0 DB REG
"SYS"."SCHEDULER$_EVENT_QUEUE":"SCHED$_AGT2$_101"         101 DB REG

SQL>
SQL>
SQL> select username from dba_users where user_id = 101;

USERNAME
----------------
IARSOV

SQL>

Conclusion:

If you decide to use scheduler job email notifications, and also prefer that the notifications management is done by a single user (such as “system”) I would advise you to create a “dummy” job notification (with the job owner’s schema) as soon as you create the first scheduler job. This will link the event_queue:consumer_group to the proper user. Afterwards, once you define the rest of the scheduler job notifications (under the common “system” user), you can clean-up that initial “dummy” job notification.

This behavior (bug) is fixed in 12.2 so that the notifications always go through the user’s AQ agent which defines the notifications.

Categories: DBA Blogs

Log Buffer #517: A Carnival of the Vanities for DBAs

Fri, 2017-07-21 11:47

This Log Buffer Edition covers Oracle, SQL Server and MySQL.

Oracle:

Protecting Financial Data with Oracle WebCenter and Adobe LiveCycle

Oracle Forms 12c oracle.security.jps.JpsException Error after Database change

The Future of Content Management: Oracle Content & Experience Cloud

Today Oracle released a very large „monster“ Upgrade. This July 2017 Update includes the first time the new RU „Release Upgrade“ and RUR „Release Update Revision“ Patches.

Cloud Ward: Who Will Win the Battle for IT’s Future?

SQL Server:

SQL Server Management Studio add-ins

Resolve Network Binding Order Warning in failover cluster

Queries to inventory your SQL Server Agent Jobs

SQL Server 2016 ColumnStore Index String Predicate Pushdown

The Fast Route from Raw Data to Analysis and Display

MySQL:

Group-Replication, sweet & sour

You QA Most of Your System — What About Your Database?

Multi-Threaded Slave Statistics

Protecting your data! Fail-safe enhancements to Group Replication.

InnoDB Basics – Compaction: when and when not

Categories: DBA Blogs

Log Buffer #517: A Carnival of the Vanities for DBAs

Fri, 2017-07-21 11:47

This Log Buffer Edition covers Oracle, SQL Server and MySQL.

Oracle:

Protecting Financial Data with Oracle WebCenter and Adobe LiveCycle

Oracle Forms 12c oracle.security.jps.JpsException Error after Database change

The Future of Content Management: Oracle Content & Experience Cloud

Today Oracle released a very large „monster“ Upgrade. This July 2017 Update includes the first time the new RU „Release Upgrade“ and RUR „Release Update Revision“ Patches.

Cloud Ward: Who Will Win the Battle for IT’s Future?

SQL Server:

SQL Server Management Studio add-ins

Resolve Network Binding Order Warning in failover cluster

Queries to inventory your SQL Server Agent Jobs

SQL Server 2016 ColumnStore Index String Predicate Pushdown

The Fast Route from Raw Data to Analysis and Display

MySQL:

Group-Replication, sweet & sour

You QA Most of Your System — What About Your Database?

Multi-Threaded Slave Statistics

Protecting your data! Fail-safe enhancements to Group Replication.

InnoDB Basics – Compaction: when and when not

Categories: DBA Blogs

Log Buffer #516: A Carnival of the Vanities for DBAs

Wed, 2017-07-12 11:02

This Log Buffer Edition covers Oracle, SQL Server and MySQL.

Oracle:

12.2 New Feature: the FLEX ASM disk group part 2

Oracle ASM in Azure corruption – follow up

Set-based processing

ADF 12c BC Proxy User DB Connection and Save Point Error

Enabling A Modern Analytics Platform

SQL Server:

Batch SSIS pkg execution from Business Intelligence Development Studio

Find Database Connection Leaks in Your Application

Troubleshooting CPU Performance on VMware

SQLskills Wait Types Library now shows SentryOne data

PowerShell Tool Time: The Tool Framework

MySQL:

Installing Zabbix into Azure using a MySQL PaaS

Streaming Global Cyber Attack Analytics with Tableau and Python

Thread Statistics and High Memory Usage

On slave_parallel_workers and the logical clock

RDS / Aurora OS monitoring with Monyog v8.1.0

Categories: DBA Blogs

Log Buffer #516: A Carnival of the Vanities for DBAs

Wed, 2017-07-12 11:02

This Log Buffer Edition covers Oracle, SQL Server and MySQL.

Oracle:

12.2 New Feature: the FLEX ASM disk group part 2

Oracle ASM in Azure corruption – follow up

Set-based processing

ADF 12c BC Proxy User DB Connection and Save Point Error

Enabling A Modern Analytics Platform

SQL Server:

Batch SSIS pkg execution from Business Intelligence Development Studio

Find Database Connection Leaks in Your Application

Troubleshooting CPU Performance on VMware

SQLskills Wait Types Library now shows SentryOne data

PowerShell Tool Time: The Tool Framework

MySQL:

Installing Zabbix into Azure using a MySQL PaaS

Streaming Global Cyber Attack Analytics with Tableau and Python

Thread Statistics and High Memory Usage

On slave_parallel_workers and the logical clock

RDS / Aurora OS monitoring with Monyog v8.1.0

Categories: DBA Blogs

Log Buffer #515: A Carnival of the Vanities for DBAs

Mon, 2017-06-26 09:15

This Log Buffer Edition covers Oracle, SQL Server and MySQL.

Oracle:

You may wish to clone an Oracle Home (for example you have all your databases on a single Oracle Home, but you want to separate Development from Test

Removing Outliers using stddev()

Installing Scala and Apache Spark on a Mac

Introduction to Oracle Big Data Cloud Service – Compute Edition (Part V) – Pig

More on Optimistic Locking with ORA_ROWSCN

SQL Server:

How to Decipher sysschedules

SQL Azure Reporting Automation

Database Code Analysis

Visual Studio 2017 and Swagger: Building and Documenting Web APIs

Techniques to Cleanse Bad Data in SQL Server

MySQL:

ClickHouse in a General Analytical Workload (Based on a Star Schema Benchmark)

Debian 9 and MySQL: Watch Out!

How to Install LAMP on Ubuntu 16.04

Linux perf and the CPU regression in MySQL 5.7

Secure Binlog Server: Encrypted binary Logs and SSL Communication

Categories: DBA Blogs

Log Buffer #515: A Carnival of the Vanities for DBAs

Mon, 2017-06-26 09:15

This Log Buffer Edition covers Oracle, SQL Server and MySQL.

Oracle:

You may wish to clone an Oracle Home (for example you have all your databases on a single Oracle Home, but you want to separate Development from Test

Removing Outliers using stddev()

Installing Scala and Apache Spark on a Mac

Introduction to Oracle Big Data Cloud Service – Compute Edition (Part V) – Pig

More on Optimistic Locking with ORA_ROWSCN

SQL Server:

How to Decipher sysschedules

SQL Azure Reporting Automation

Database Code Analysis

Visual Studio 2017 and Swagger: Building and Documenting Web APIs

Techniques to Cleanse Bad Data in SQL Server

MySQL:

ClickHouse in a General Analytical Workload (Based on a Star Schema Benchmark)

Debian 9 and MySQL: Watch Out!

How to Install LAMP on Ubuntu 16.04

Linux perf and the CPU regression in MySQL 5.7

Secure Binlog Server: Encrypted binary Logs and SSL Communication

Categories: DBA Blogs

EM13cR2 Installation Failing on BI Publisher Configuration

Wed, 2017-06-21 16:07

This is going to be a short post as there wasn’t much information on this type of failure when searching for it. Also, the IP’s are changed due to security reasons

I was working on an Oracle EM13cR2 installation and when the installation reached 78%, it failed on the BI Publisher configuration. First, I looked at the CfmLogger log.

INFO: oracle.sysman.top.oms:WLST offline debugging is in the file: /u01/app/oracle/middleware/cfgtoollogs/bip/bipca_20170613152059.log.wlst_20170613152122.trc
INFO: oracle.sysman.top.oms:Securing BI Publisher to work with Enterprise Manager
INFO: oracle.sysman.top.oms:Locking Enterprise Manager ...
INFO: oracle.sysman.top.oms:OMS Console is locked. Access the console over HTTPS ports.
INFO: oracle.sysman.top.oms:BI Publisher is locked. Access BI Publisher over HTTPS ports.
INFO: oracle.sysman.top.oms:Restart OMS.
INFO: oracle.sysman.top.oms:Restarting Enterprise Manager
INFO: oracle.sysman.top.oms:Stopping Enterprise Manager, this can take some time ...
INFO: oracle.sysman.top.oms:Starting BI Publisher ...
INFO: oracle.sysman.top.oms:Failed to start Enterprise Manager. Diagnostic code 1.
INFO: oracle.sysman.top.oms:See log at /u01/app/oracle/middleware/cfgtoollogs/bip/bipca_20170613152059.log for details.
INFO: oracle.sysman.top.oms:Error extending domain
INFO: oracle.sysman.top.oms:See log at /u01/app/oracle/middleware/cfgtoollogs/bip/bipca_20170613152059.log for details.
INFO: oracle.sysman.top.oms:java.lang.Exception: See log at /u01/app/oracle/middleware/cfgtoollogs/bip/bipca_20170613152059.log for details.
INFO: oracle.sysman.top.oms: at oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA.extendDomainWithBIP(BIPCA.java:3401)
INFO: oracle.sysman.top.oms: at oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA.main(BIPCA.java:1973)

When this led me to the bipca.log , it referenced the details below (which really didn’t tell me much):

[2017-05-09T21:55:17.585-07:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: OracleEntMgr] [nwaddr: 192.168.51.10] [tid: 1] [userId: oracle] [ecid: a4412d2a-def5-49d8-ac27-1046c7243de9-00000001,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: handleFatalError] Error extending domain
[2017-05-09T21:55:17.586-07:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: OracleEntMgr] [nwaddr: 192.168.51.10] [tid: 1] [userId: oracle] [ecid: a4412d2a-def5-49d8-ac27-1046c7243de9-00000001,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: handleFatalError] Fatal error:[[
java.lang.Exception: See log at /u01/app/oracle/middleware/cfgtoollogs/bip/bipca_20170509214828.log for details.
at oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA.extendDomainWithBIP(BIPCA.java:3401)
at oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA.main(BIPCA.java:1973)

A review of the bipca trace file didn’t tell me much about why the configuration was failing:

2017-05-24 09:26:51,250 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - Selected DB vendor: Oracle
2017-05-24 09:26:51,250 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - adding normal datasource: emgc-mgmt_view-pool
2017-05-24 09:26:51,250 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - datasource: emgc-mgmt_view-pool component name: null
2017-05-24 09:26:51,251 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - Found normal datasource: emgc-sysman-pool
2017-05-24 09:26:51,253 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - Decomposing url: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=1521)))(CONNECT_DATA=(SID=emrep))) for driver: oracle.jdbc.OracleDriver
2017-05-24 09:26:51,253 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - There is an unsupported jdbc URL or JDBC driver is detected. See Stack trace:
java.lang.NullPointerException
at com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper.decomposeURL(DatasourceXBeanAspectHelper.java:316)
at java.lang.reflect.Method.invoke(Method.java:606)
at weblogic.WLST.main(WLST.java:29)
2017-05-24 09:26:51,253 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - Found normal datasource: jvmd-ds
2017-05-24 09:26:51,255 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - Decomposing url: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=1521)))(CONNECT_DATA=(SID=emrep))) for driver: oracle.jdbc.OracleDriver
2017-05-24 09:26:51,255 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - There is an unsupported jdbc URL or JDBC driver is detected. See Stack trace:
java.lang.NullPointerException
at com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper.decomposeURL(DatasourceXBeanAspectHelper.java:316)
at org.python.pycode._pyx95.updateDomain$21(/tmp/WLSTOfflineIni822569357732826272.py:103)
at org.python.pycode._pyx95.call_function(/tmp/WLSTOfflineIni822569357732826272.py)
at org.python.core.PyTableCode.call(Unknown Source)
at org.python.core.PyTableCode.call(Unknown Source)
at org.python.core.PyFunction.__call__(Unknown Source)

After reading for a while and working back and forth with Oracle, we identified the problem. The host wasn’t resolving via nslookup. Though we were able to ping it, what nslookup does is query a DNS domain nameserver to lookup and find IP address and it , so what we had to do was fix this :

[oracle@dbhost ~]# nslookup dbhost
Server: 128.101.1.256
Address: 128.101.1.256#53

Name: dbhost
Address: 128.101.1.1

Once we fixed this, we were able to install EM13cR2 successfully.

Note:This was originally published on rene-ace.com

Categories: DBA Blogs

Log Buffer #514: A Carnival of the Vanities for DBAs

Fri, 2017-06-16 13:19

This Log Buffer Edition encompasses few of the nifty blog posts from Oracle, SQL Server and MySQL.

Oracle:

Introduction to Oracle Big Data Cloud Service

Interpreting wpad.dat using jrunscript from JDK 8 to work out the right proxy.

Nice Trick to Get ADF LOV Description Text

Presentation: Dynamic Actions, Javascript, & CSS for APEX Beginners

E4 Session Developing Agnostic Data Services

SQL Server:

Setting up R in SQL 2016 – Avoiding Pitfalls

Setting Variables in Calling T-SQL Code While Using sp_executesql

To Fly, To Serve, To Fry Your Servers

Working with Windows Containers and Docker: Into your Stride

What is normal? Finding outliers with R

Two Approaches to Addressing Circular References in SSDT

MySQL:

Howto make MySQL point-in-time recovery faster ?

Three Methods of Installing Percona Monitoring and Management

Watch the tutorial: backup best practices for MySQL, MariaDB and Galera Cluster

MySQL 8.0: Kana-sensitive collation for Japanese

MySQL Document Store: Getting Started

Categories: DBA Blogs

Pages