One of the most valuable sources of information about what the Oracle database has done and is currently doing is the alert log. It's something that every Oracle Database professional should be familiar with. So what can you do to improve you chances of not missing important pieces of info? The obvious answer is that you should use a tool like Enterprise Manager. This is particularly true if you are looking after hundreds of databases.

But what if you are only looking after one or two or just testing something out? Well the most common solution is to simply tail the alert log file.

The only issue is that it's not the most exciting thing to view, this of course could be said for any terminal based text file. But there are things you can do to make it easier to parse visually and improve your chances of catching an unexpected issue.

The approach I take is to push the alert log file through python and use the various libraries to brighten it up. It's very easy to go from this (tail -f)

To this

The reason this works is that python provides a rich set of libraries which can add a little bit of colour and formatting to the alert file.

You can find the code to achieve this in the gist below

Just a quick note on installing this. You'll need either python 2.7 or 3 available on your server.

I'd also recommend installing pip and then the following libraries

pip install humanize psutil colorama python-dateutil


After you've done that it's just a case of running the script. If you have $ORACLE_BASE and$ORACLE_SID set the library will try and make a guess at the location of the alert file. i.e

python alertlogparser.py


But if that doesn't work or you get an error you can also explicitly specify the location of the alert log with something like

python alertlogparser.py -a $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/alert_orcl.log  This script isn't supposed to be an end product just a simple example of what can be achieved to make things a little easier. And whilst I print information like CPU load and Memory there's nothing to stop you from modifying the script to display the number of warnings or errors found in the alert log and update it things change. Or if you really want to go wild implement something similar but a lot more sophisticated using python and curses The age of "Terminal" is far from over…. Comments # Installing Python 2.7 in local directory on Oracle Bare Metal Cloud I know there will be Linux and Python specialist spitting feathers about this approach. But if you're in need of an up to date python environment then the following approach might be of help.It's worth nothing that this technique will probably work on most Oracle Enterprise Linux or Red Hat Platform releases. #make localdirectory to install python i.e. mkdir python #make sure latest libs needs for python are installed sudo yum install openssl openssl-devel sudo yum install zlib-devel #Download latest source i.e. Python-2.7.13.tgz and uncompress tar xvfz Python-2.7.13.tgz cd Python-2.7.13 #configure python for local install config --enable-shared --prefix=/home/opc/python --with-libs=/usr/local/lib make; make install #python 2.7.13 is now installed but isn't currently being used (2.6 is still the default) #get pip curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py #install pip (this will still be installed with 2.6) sudo python get-pip.py #install virtualenv sudo pip install virtualenv #create a virtualenv using the newly installed python virtualenv -p /home/opc/python/bin/python myvirtualenv #activate it source myvirtualenv/bin/activate #install packages… pip install cx_Oracle  Comments # Changing the size of redo logs in python I create a lot of small databases to do testing on. The trouble is that I often need to change the size of redo log files when I'm testing large transaction workloads or loading a lot of data. Now there are lots of better ways to do whats shown in the code below but this approach gave me the chance to keep brushing up my python skills and use the might cx_oracle driver. The following should never be considered anything but a nasty hack but it does save me a little bit of time i.e. don't use this on anything but a test database… Clearly the sensible way to do this is to write my own scripts to build databases. The following code works it's way through the redo log files drops one thats inactive and then simply recreates it. It finished when it's set all of the redo to the right size. Running the script is simply a case of running it with the parameters shown below python ChangeRedoSize -u sys -p welcome1 -cs myserver/orclcdb --size 300  Note : the user is the sysdba of the container database if you are using the multitenant arhcitecture and the size is in Mega Bytes. You should then see something similar to the following  Current Redo Log configuration +-----------+------------+--------------+-----------+---------------+----------+ | Group No. | Thread No. | Sequence No. | Size (MB) | No of Members | Status | +-----------+------------+--------------+-----------+---------------+----------+ | 1 | 1 | 446 | 524288000 | 1 | INACTIVE | | 2 | 1 | 448 | 524288000 | 1 | CURRENT | | 3 | 1 | 447 | 524288000 | 1 | ACTIVE | +-----------+------------+--------------+-----------+---------------+----------+ alter system switch logfile alter system switch logfile alter database drop logfile group 2 alter database add logfile group 2 size 314572800 alter system switch logfile alter database drop logfile group 1 alter database add logfile group 1 size 314572800 alter system switch logfile alter system switch logfile alter system switch logfile alter system switch logfile alter database drop logfile group 3 alter database add logfile group 3 size 314572800 alter system switch logfile All logs correctly sized. Finishing... New Redo Log configuration +-----------+------------+--------------+-----------+---------------+----------+ | Group No. | Thread No. | Sequence No. | Size (MB) | No of Members | Status | +-----------+------------+--------------+-----------+---------------+----------+ | 1 | 1 | 455 | 314572800 | 1 | ACTIVE | | 2 | 1 | 454 | 314572800 | 1 | INACTIVE | | 3 | 1 | 456 | 314572800 | 1 | CURRENT | +-----------+------------+--------------+-----------+---------------+----------+  Comments # Interpolating data with Python SensorData So as usual for this time of year I find myself on vacation with very little to do. So I try and find personal projects that interest me. This is usually a mixture of electronics and mucking around with software in a way that I don't usally find the time for normally. One of projects is my sensor network. I have a number of Raspberry Pi's around my house and garden that take measurements of temperature, humidity, pressure and light. They hold the data locally and then periodically upload them to a central server (another Raspberry Pi) where they are aggregated. However for any number of reasons (usally a power failure) the raspberrypi's occasionally restart and are unable to join the network. This means that some of their data is lost. I've improved their resiliance to failure and so it's a less common occurance but it's still possible for it to happen. When this means I'm left with some ugly gaps in an otherwise perfect data set. It's not a big deal but it's pretty easy to fix. Before I begin, I acknolwedge that I'm effectively "making up" data to make graphs "prettier". In the following code notebook I'll be using Python and Pandas to tidy up the gaps. To start with I need to load the libraries to process the data. The important ones are included at the start of the imports. The rest from "SensorDatabaseUtilities" aren't really relevant since they are just helper classes to get data from my repository In [75]: import matplotlib.pyplot as plt from matplotlib import style import pandas as pd import matplotlib import json from pandas.io.json import json_normalize # The following imports are from my own Sensor Library modules and aren't really relevant from SensorDatabaseUtilities import AggregateItem from SensorDatabaseUtilities import SensorDatabaseUtilities # Make sure the charts appear in this notebook and are readable %matplotlib inline matplotlib.rcParams['figure.figsize'] = (20.0, 10.0)  The following function is used to convert a list of JSON documents (sensor readings) into a Pandas DataFrame. It then finds the minimum and maximum dates and creates a range for that period. It uses this period to find any missing dates. The heavy lifting of the function uses the reindex() function to insert new entries whilst at the same time interpolating any missing values in the dataframe. It then returns just the newly generated rows In [76]: def fillin_missing_data(sensor_name, results_list, algorithm='linear', order=2): # Turn list of json documents into a json document results = {"results": results_list} # Convert JSON into Panda Dataframe/Table df = json_normalize(results['results']) # Convert Date String to actual DateTime object df['Date'] = pd.to_datetime(df['Date']) # Find the max and min of the Data Range and generate a complete range of Dates full_range = pd.date_range(df['Date'].min(), df['Date'].max()) # Find the dates that aren't in the complete range missing_dates = full_range[~full_range.isin(df['Date'])] # Set the Date to be the index df.set_index(['Date'], inplace=True) # Reindex the data filling in the missing date and interpolating missing values if algorithm in ['spline', 'polynomial'] : df = df.sort_index().reindex(full_range).interpolate(method=algorithm, order=order) elif algorithm in ['ffill', 'bfill']: df = df.sort_index().reindex(full_range, method=algorithm) else: df = df.sort_index().reindex(full_range).interpolate(method=algorithm) # Find the dates in original data set that have been added new_dates = df[df.index.isin(missing_dates)] # Create new aggregate records and insert them into the database # new_dates.apply(gen_json,axis=1, args=[sensor_name]) return new_dates  This function simply takes an array of JSON documents and converts them into a DataFrame using the Pandas json_normalize function. It provides us with the dataset that contains missing data i.e. an incomplete data set. In [77]: def json_to_dataframe(results_list): # Turn list of json documents into a json dodument results = {"results": results_list} # Convert JSON into Panda Dataframe/Table df = json_normalize(results['results']) return df  The first step is to pull the data from the database. I'm using some helper functions to do this for me. I've also selected a date range where I know I have a problem. In [92]: utils = SensorDatabaseUtilities('raspberrypi', 'localhost') data = utils.getRangeData('20-jan-2015', '10-feb-2015') # The following isn't need in the code but is included just to show the structure of the JSON Record json.dumps(data[0])  Out[92]: '{"Date": "2015-01-20 00:00:00", "AverageHumidity": 35.6, "AverageTemperature": 18.96, "AveragePressure": 99838.78, "AverageLight": 119.38}' Next simply convert the list of JSON records into a Pandas DataFrame and set it's index to the "Date" Column. NOTE : Only the first 5 records are shown In [93]: incomplete_data = json_to_dataframe(data) # Find the range of the data and build a series with all dates for that range full_range = pd.date_range(incomplete_data['Date'].min(), incomplete_data['Date'].max()) incomplete_data['Date'] = pd.to_datetime(incomplete_data['Date']) incomplete_data.set_index(['Date'], inplace=True) # Show the structure of the data set when converted into a DataFrame incomplete_data.head()  Out[93]: AverageHumidity AverageLight AveragePressure AverageTemperature Date 2015-01-20 35.60 119.38 99838.78 18.96 2015-01-21 38.77 63.65 99617.15 19.48 2015-01-22 37.45 143.00 100909.08 20.08 2015-01-23 35.52 119.87 101306.30 20.12 2015-01-24 39.72 92.43 101528.54 19.90 The following step isn't needed but simply shows the problem we have. In this instance we are missing the days for Janurary 26th 2015 to Janurary 30th 2015 In [94]: #incomplete_data.set_index(['Date'], inplace=True) problem_data = incomplete_data.sort_index().reindex(full_range) axis = problem_data['AverageTemperature'].plot(kind='bar') axis.set_ylim(18,22) plt.show()  Pandas offers you a number of approaches for interpolating the missing data in a series. They range from the simple method of backfilling or forward filling values to the more powerful approaches of methods such as "linear", "quadratic" and "cubic" all the way through to the more sophisticated approaches of "pchip", "spline" and "polynomial". Each approach has its benefits and disadvantages. Rather than talk through each it's much simpler to show you the effect of each interpolation on the data. I've used a line graph rather than a bar graph to allow me to show all of the approaches on a single graph. In [95]: interpolation_algorithms = ['linear', 'quadratic', 'cubic', 'spline', 'polynomial', 'pchip', 'ffill', 'bfill'] fig, ax = plt.subplots() for ia in interpolation_algorithms: new_df = pd.concat([incomplete_data, fillin_missing_data('raspberrypi', data, ia)]) ax = new_df['AverageTemperature'].plot() handles, not_needed = ax.get_legend_handles_labels() ax.legend(handles, interpolation_algorithms, loc='best') plt.show()  Looking at the graph it appears that either pchip (Piecewise Cubic Hermite Interpolating Polynomial) or Cubic interpolation is going to provide the best approximation for the missing values in my data set. This is largely subjective because these are "made up values" but I believe either of these approaches provide values that are closest to what the data could have been. The next step is to apply one to the incomplete data set and store it back in the database In [96]: complete_data = pd.concat([incomplete_data, fillin_missing_data('raspberrypi', data, 'pchip')]) axis = complete_data.sort_index()['AverageTemperature'].plot(kind='bar') axis.set_ylim(18,22) plt.show()  And thats it. I've made the code much more verbose that it needed to be purely to demonstrate the point. Pandas makes it very simple to patch up a data set. Comments # Java Version Performance Sometimes it’s easy to loose track of the various version numbers for software as they continue their march ever onwards. However as I continue my plans to migrate onto Java8 and all of the coding goodness that lies within I thought it was a sensible to check what difference it would make to swingbench in terms of performance. Now before we go any further it’s worth pointing out this was a trivial test and my results might not be representative of what you or anyone else might find. My environment was iMac (Retina 5K, 27-inch, Late 2014), 4 GHz Intel Core i7, 32 GB 1600 MHz DDR3 with a 500GB SSD Oracle Database 12c (12.1.0.2) with the January Patch Bundle running in a VM with 8GB of memory. The test is pretty simple but might have an impact on your choice of JVM when generating a lot of data (TB+) with swingbench. I simply created a 1GB SOE with the oewizard. This is a pretty CPU intensive operation for the entire stack : swingbench, the jdbc drivers and the database. The part of the operation that should be most effected by the performance of the JVM is the “Data Generation Step”. So enough talk what impact did the JVM version have? Now the numbers might not be earth shattering but it’s nice to know a simple upgrade of the JVM can result in nearly a 25% improvement in performance of a CPU/database intensive batch job. I expect these numbers to go up as I optimise some of the logic to take advantage of Java8 specific functionality. Comments # PDF Generation of report files I finally got round to adding some code that creates pdf files such that you can convert the “XML” result files into something more readable. However this new functionality requires a Java8 VM to work. You can download the latest build here. All you need to do is to run swingbench and from the menu save the summary results. Minibench and charbench will automatically create a results config file in the local directory after a benchmark run. The file that’s created will typically start with “result” and it should look something like this. [bin]$ ls ccwizard.xml coordinator oewizard shwizard.xmlbmcompare charbench data oewizard.xml swingbenchccconfig.xml clusteroverview debug.log results.xml swingconfig.xmlccwizard clusteroverview.xml minibench shwizardAll you need to do after this is to run the “results2pdf command

[bin]$./results2pdf -c results2pdf There’s really only 2 command line options [bin]$ ./results2pdf -husage: parameters: -c the config file to convert from xml to pdf -debug send debug information to stdout -h,--help print this message -o output filenameThey are for the input file (-c) and the output file (-o).

The resultant file will contain tables and graphs. The type of data will depend heavily on the type of stats collected in the benchmark. For the richest collection you should enable

• Full stats collection
• Database statistics collection
• CPU collection

An example of the output can be found here.

I plan to try and have the resultant pdf generated and displayed at the end of every bench mark. I’ll include this functionality in a future build.