Subscribe

While writing the previous two posts about GTTs and Smart Scan, I stumbled across an interesting observation. When I started putting my thoughts to (virtual) paper, I thought it was going to be a short post. Well, it wasn’t to be, again. Anyway, you might find this interesting.

Initial Situation

If you read the previous posts this code example I used to populate the GTT might look familiar:

In situations like this where I’m moving data I developed a habit of using the append hint. I guess I’ve been working on HCC tables a bit too much and therefore didn’t even make a conscious decision to use that hint. It was the right thing to do, as you will see next.

The full test harness is shown here again for reference:

SQL> !cat test.sql
set lines 120 tab off trimspool on verify off timing on
-- this statement is using the /*+ append */ hint
insert /*+ append using_append */ into gtt select * from t4 where rownum < 400000;
commit;
-- not testing if I can get Smart Scans, I know there are :)
alter session set "_serial_direct_read" = always;
-- make sure the query can be ever so slightly more complex and always return rows
col min_id new_v min_id
col max_id new_v max_id
select min(id) min_id from gtt;
select max(id) max_id from gtt;
-- this is the actual test
@mystats start s=s
set echo on
select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id;
set echo off
@mystats stop t=1
SQL>

When executing the script and looking at the output I didn’t see anything out of the ordinary. When checking for the “depth” of the Smart Scan, I regularly consult the cell blocks processed by % layer statistics to see if I lose blocks between code layers. It wouldn’t appear so:

66,667 blocks in each code layer. This looks ok to me: the number of all physical reads recorded for this statement is 66,669. I certainly don’t want to endorse tuning by ratio, but in this case it sort-of makes sense: the offload efficiency pretty high in my case:

Now imagine my surprise when I didn’t execute a script but rather typed the insert command in an interactive session. In the next example, created to reproduce the observation, I “forgot” to specify the append hint:

SQL> !cat test2.sql
-- not using the append hint
insert /* not_using_append */ into gtt select * from t4 where rownum < 400000;
commit;
-- not testing if I can get Smart Scans, I know there are :)
alter session set "_serial_direct_read" = always;
-- make sure the query can be ever so slightly more complex and always return rows
col min_id new_v min_id
col max_id new_v max_id
select min(id) min_id from gtt;
select max(id) max_id from gtt;
-- this is the actual test
@mystats start s=s
set echo on
select /* not_using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id;
set echo off
@mystats stop t=1

The difference between test.sql and test2.sql looks harmless enough – just a hint, or lack thereof. However the result is interesting. Most executions of the above script resulted in Oracle reporting the following statistics:

Huh? I was clearly surprised to see so many rows not making it to the higher layers (txn and data) after having been read by the cache layer. Further executions of the select statement within the same session do not necessarily improve this significantly, but I noticed savings by storage index to come into play.

Speaking more scientifically, the listing immediately above shows that out of 67,026 blocks that were read by the cache layer only 1 (!) was processed by txn and data layers. As a direct result Smart Scan does not offer any real benefit: cell physical IO interconnect bytes returned by smart scan is actually ≥ cell physical IO bytes eligible for predicate offload.

Why? What?

There were a few red herrings in the statistic counters I saw:

Cleanout statistics have been reported by mystats

commit txn count during cleanout

cleanouts only – consistent read gets

immediate (CR) block cleanout applications

etc.

There are consistent gets examination that might be peeks at undo information

Lots of queries to the commit cache (cell commit cache queries) – but for most of my tests I didn’t see replies. Only 1 out of maybe 20 tests produced some cell transactions found in commit cache, but even then their number was not significant. I will provide an example towards the end of this post.

Further investigation

So this leads me to suspect that the blocks that were created with the append hint are “clean”, e.g. suitable for a consistent read on the cells without further need for processing. It would appear so, judging by this block dump (dump the block from the tempfile, not datafile!):

This is in contrast to the blocks I dumped that have been created without the append hint. To make sure I’m comparing apples with apples these blocks have been created in a different session after having disconnected from the previous one:

This supports the theory that the block I created without the append hint features some information that prevents it from being passed from cache to txn layer. I didn’t see active txn count during cleanout though, but commit txn count during cleanout and immediate (CR) block cleanout applications instead. So cleanout is definitely needed and performed.

I stated in the second bullet point that commit cache queries were not producing cache-hits most of the time. I managed to get an execution of the select statement against the blocks created without append hint, where at least some commit cache queries produced hits:

I couldn’t reproduce this at will though, and it wasn’t for lack of trying.

Summary

I am not entirely sure what causes the observed behaviour, especially with regards to the commit cache and/or minscn optimisation but it evidently seems to be better to populate a GTT with the append hint if you want to make best use of Smart Scans against such segments. A very quick and dirty test against a heap table suggests that this is true for this segment types as well.

Continuing the example of the previous blog post (is it possible to Smart Scan GTTs?), I am investigating if Smart Scans against GTTs can benefit from Flash Cache, or “optimised reads”. The easiest way to see this in your session is to calculate the change in session counters based on v$mystat or v$sesstat. Other tools might also provide information about the amount of data read from Flash Cache, but there are quite often extra licenses required …

Calculating the delta in session counters can be done in many ways, for example using Tanel’s session snapper. For clearly defined test cases where I can control beginning and end of the execution I prefer to use another great script for the purpose. My thanks go to Adrian Billington for providing the Oracle community with the most useful “mystats” package which I’m using here. You can – and should – get it from oracle-developer.net.

Let’s check I can get flash cache read hits on GTT scans – which would help performance of repeated queries against the segment. And since GTTs reside on TEMP, I had a few doubts whether flash cache read hits were possible.

set lines 120 tab off trimspool on verify off timing on
-- it will be crucially important to use the /*+ append */ hint
-- as you will see in the next post about GTTs on Exadata
insert /*+ append */ into gtt select * from t4 where rownum < 400000;
commit;
-- not testing if I can get Smart Scans, I know there are :)
alter session set "_serial_direct_read" = always;
-- make sure the query can be ever so slightly more complex and always return rows
col min_id new_v min_id
col max_id new_v max_id
select min(id) min_id from gtt;
select max(id) max_id from gtt;
-- hoping to trigger flash cache population here
select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id;
select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id;
select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id;
select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id;
select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id;
select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id;
-- and measuring effect, if any
@mystats start s=s
set echo on
select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id;
set echo off
@mystats stop t=1

Have a look at the code in mystats.sql to see what the options mean. Essentially I’m asking it to record session statistics only (start s=s) and after the execution list only those counters that have changed (t=1 where t is for threshold). I hacked the script a little to order by statistic name rather than the default.

The observation

It appears I can get Flash Cache hits. The relevant statistic name is “cell flash cache read hits”, and the fact that physical IO was optimised can also be seen in “physical read requests optimized” and “physical read total bytes optimized” amongst others. Here is the relevant output from mystats:

I usually check the cell blocks processed by% layer first to get an idea about the “depth” of the Smart Scan-losing blocks in transaction and data layers wouldn’t be great. There’s been 1 Smart Scan against my segment (the GTT isn’t partitioned) recorded in “cell scans”, so that covers that aspect as well.

Out of the 523 “physical read IO requests” 315 were optimised so that’s a pretty good ratio of approximately 60% of the IO requests coming from Flash Cache. The other statistics represent the same ratio in bytes rather than I/O requests.

A question that has come up while preparing to deliver another Exadata Optimisation Class is whether you can offload queries against Global Temporary Tables (GTT) or not. There are subtle differences between heap tables and GTTs such as how statistics are handled and where they are stored that merit a little investigation.

I have used the lab environment on the X3-2 (Exadata 12.1.2.3.0, 12.1.0.2.160419 RDBMS) to test if I can Smart Scan GTTs. There are some enhancements in 12c regarding GTTs that are mentioned on the Optimiser blog here:

I have used a brand new, 12c non-CDB for these tests. The SGA_TARGET is deliberately set to something really small so as to provoke Smart Scans without having to resort to setting _serial_direct_read to “always” in my session.

First of all, I needed a GTT for this purpose. I have a few scripts that create large tables for testing, and T4 will serve as my basis. Here is my setup:

This might be the time to point out that the GTT has two types of statistics in 12c – shared and session. Again, please refer to the Optimiser blog post for more information about private session statistics. I first looked at dba_tables for num_rows etc but found the column empty. Using a query I found on oracle-base.com in Tim’s “Session-Private Statistics for Global Temporary Tables in Oracle Database 12c Release 1” article I detected some statistics in the end:

There is a simple conclusion: yes, there is a Smart Scan. I can’t see a massive difference between the NSMTIO trace for a GTT or a heap table. Since direct path read decisions are statistics driven (check parameter _direct_read_decision_statistics_driven) and there weren’t shared statistics on GTT I assume the single block reads are related to reading object statistics. OBJ# 163899 is the object_id of the GTT.

What is interesting is the use of “enq: RO – fast object reuse” prior to the start of the Smart Scan. This surprised me a little and I wasn’t expecting it. Here’s the same trace combination for a heap table for comparison:

This is the more familiar picture: reliable message, enq: KO – fast object checkpoint followed by cell smart table scan.

The Verdict

Quite simple this time: yes, you can use Smart Scans on GTT. Additionally there are a few caveats around the use of GTTs when you are migrating your database to 12.1 but you can read about them in the link to the Optimiser blog provided.

Thinking about automation a lot, especially in the context of cloud computing, I have decided to create a small series of related posts that hopefully help someone deploying environments in a fully automated way. As my colleague @fritshoogland has said many times: the only way to deploy database servers (or any other server for that matter) in a consistent way, is to do it via software. No matter how hard you try to set up 10 identical systems manually, there will be some, maybe ever so subtle, differences between them. And with the cloud you probably have 42 systems or more to deal with! In this context, my first post could be a building block: the provisioning of the Oracle RDBMS the form of an RPM.

The idea

In a nutshell, I would like to

Create a “golden image” of my database installation

Make this available for deployment via NFS

Create an RPM that allows me to deploy the “golden image” on the server I want to install and call clone.pl to complete the process

Execute the root scripts (orainstRoot.sh, root.sh)

This is actually quite easy to do! The oracle-validated RPM, and nowadays oracle-rdbms-server-release-preinstall.x86_64 packages simplify the task greatly as they can pull in dependent packages, set sensible default parameters and generally perform a lot of the tedious setup work for you. All I have to do in my RPM is to enforce the version of the system is Oracle Linux 7 and pull the preinstall RPM in if not yet installed. Since there is no more 12c RDBMS server for i686 this entire post implicitly is about Linux x86-64.

The Implementation

I am using a development system (linuxdev in this example) to build the RPM. It’s an Oracle Linux 7.2 system with all upgrades up to 24/10/2016. The first thing to do is to install the RPM dev tools and their dependencies. As always, you use yum to do so.

The development tools contain a lot of useful scripts, one of them I consider essential: rpmdev-setuptree. It sets up the relevant directory structure. Granted, the rpmdev tools are geared towards compiling software from their source, optionally apply patches and then create SRPMs and RPMs. RPM wasn’t designed for the purpose of packaging an Oracle database, but it can still be made to work. You can work around the size restriction using the %post hook in the SPEC file, as you will see later in this post. But I’m getting ahead of myself.

Completing the SPEC file is easy if you know how to :) To save you the pain I put a stub together that is far from perfect, but should give you an idea. This is just a demo, the code especially lacks all (error, space, etc) checking which really should be in.

Again, at the risk of repeating myself, this is just a stub and needs to be extended to include error handling and other things that we consider good coding practice. You also should check if there is sufficient space in the $ORACLE_HOME directory etc… you get the idea. Also, if you create another SPEC file for a 12c installation you currently can’t install a new Oracle Home as the path is hard coded to dbhome_1.

The script – which is the part in the %post section – is so simple it should be self-explanatory. It will be passed to /bin/sh and executed as part of the installation process.

With the SPEC file in place you can try and build the RPM. Funny enough, the rpmbuild tool is used for that:

And that’s it! One of the slightly more complex things was to ensure that this can be installed on Oracle Linux 7 only. One way to do this is to add the oraclelinux-release package as a dependency. I had to fiddle around with it a bit as it uses the epoch field (which seems rare), and not just the version. After I worked it out I managed to enforce the release. On my Oracle Linux 6 system the RPM could not be installed:

If you are now thinking of using the epoch:version to check for the oraclelinux-release on Oracle Linux 6, don’t. This is the current oraclelinux-release RPM taken from the “latest” channel in public-yum:

So instead of “Requires: oraclelinux-release >= 7:7.0”, … you probably need to replace that with “Requires: oraclelinux-release >= 6:6Server-2.0” for 12c (Oracle Linux 6 update 2 or newer, according to the Linux installation guide).

In the previous blog post I used libvirt and KVM in my lab environment to simulate a transient disk failure and how to recover from it. This post takes this example a step further: I am simulating another disk failure, but this time won’t pretend I can fix the issue and put it back. In other words, I simulate the effect of the disk_repair_time hitting zero.

The idea is to show you the output of the ASM alert.log and result of the lost disk in the V$-views.

As with the previous post, the code examples in this one are for demonstration purposes only!

The setup in a nutshell

I am using Oracle Linux 7.2 with UEK4 as the host system; KVM and libvirt are responsible for the guests (virtual machines). The guest VM used for this example is named asmtest, and uses Oracle Linux 7.2 as well. There are 9 ASM disks – 8 for +DATA featuring normal redundancy in 2 failure groups. I added +RECO for the sake of completeness with external redundancy. This post is about +DATA. To keep it simple I used an Oracle Restart configuration patched to the July 2016 PSU.

Removing the disk

As with the previous example I am using libvirt to remove a “LUN” temporarily from the guest. And sure enough, the VM picks this up. This is the relevant output obtained via journalctl -f

In the previous post I keep referring to I copied the part of the ASM instance’s alert.log that showed how the disk repair timer was ticking down. This time I am simulating the case where – for whatever reason – the transient failure could not be fixed. In that case, this is what you would see in the alert.log:

The last line in the above output is definitely NOT what you want to see. If at all possible, you should fix the problem causing DATA_0001 in this example from being dropped. If you see the message about the disk being dropped you are facing the inevitable rebalance operation. Here is the continued output from the ASM alert.log:

My system is fully operational, and the rebalance did not run into any space problems. Space problems are the last thing you want to have when rebalancing. I did some research about this subject earlier and documented it in a blog post.

In a future blog post I am going to investigate what happens when a partner disk of DATA_0001 fails while the repair timer is ticking down.

For quite some time we have been treated nicely by ASM when it comes to transient disk failures. Since 11.1 (if memory serves me right), transient failures won’t cause an ASM disk to be dropped immediately. This is good, because it can potentially save a lot of time! When a disk is dropped from an ASM disk, a rebalance operation is unavoidable. And there is more than a chance of another one following it, because ultimately you want to add the disk back to the disk group. Well, to be fair, this is only applicable for normal or high redundancy disk groups.

The feature I just described very briefly is referred to as ASM Fast Mirror Resync, and documented in the ASM Administrator’s Guide in section Administering Oracle ASM Disk Groups.

By the way, there is another new feature you might want to look at, called FAILGROUP_REPAIR_TIME, but I’ll keep that for another post.

In this post I’d like to demonstrate how easy it is to simulate a transient failure of an ASM disk using libvirt and KVM on a host running Oracle Linux 7.2. I also wanted to have the output from the O/S and ASM alert.log as personal reference.

As usual, this procedure is strictly for YOUR HOME LAB USE ONLY and for educational purposes.

The setup

When creating the VM – named “asmtest” – I created a boot volume, and a 50 GB volume for the Oracle binaries. To keep it manageable I went for an Oracle 12.1 Restart configuration, patched to the July PSU. There are 9 ASM disks, presented as SCSI devices (not virtio), I’ll explain why in a bit. The guest O/S is Oracle Linux 7.2, as is my lab server. All virtual disks on the VM are partitioned although that is not necessary (strictly speaking).

Using the lsscsi tool (part of the sg3_utils), I created udev rules following the excellent description I found on oracle-base.com.

This is slightly more complicated than my usual setup, but was needed for device name persistence. Unfortunately libvirt does not report the same block device names as my guest VM. Whilst the host “sees” sd[h-v] my guest reported these devices as sd[a-i]. Persistent device names to the rescue! The only issue I had initially was that disks presented to the VM via virtio don’t report any SCSI IDs I need for my udev rules, or in other words, lsscsi returned no data. Presenting the block devices via the SCSI interface did the trick.

The hypothesis

This snippet from the documentation (referenced above) is exactly what I wanted to test:

After you repair the disk, run the SQL statement ALTER DISKGROUP ONLINE DISK. This statement brings a repaired disk group back online to enable writes so that no new writes are missed. This statement also starts a procedure to copy of all of the extents that are marked as stale on their redundant copies.

Here is the current configuration I am using for my DATA disk group, as seen by ASM:

I mentioned previously that the information provided by the host does not necessarily match that of the guest. Since I have persistent device names though in my VM there shouldn’t be an issue. Let’s take a disk away, but only temporarily! Since my O/S and Oracle binaries reside on disks presented to the guest as virtio devices, I know they are named vd[a-z] and better left alone. That leaves sdh as the first disk to remove.

In yet another session I can see that the disk is about to go, the repair timer has started ticking down, and is decremented as time passes. This did not happen straight away, there was a little delay of maybe a couple of minutes after the disk was removed.

Does this question sound familiar? “We are running the exact same workload in UAT as we do in production, but it behaves completely different. Can you help?”

If it does, then you might find the following post useful, provided you are appropriately licensed to use this feature anyway. I have been working on AWR data for a long time, and one of the best ways to do so according to David Kurtz and others, is to do with the AWR data directly. I have seen a number of presentations on how to consolidate AWR data from multiple sources in a single repository, and this is just my write up of this same procedure for my purpose. I don’t even want to pretend it’s elegant, but it works for me-it might work for you too but I can’t guarantee that :)

Setup

To demonstrate the concept I once again use Dominic Giles’s most excellent Swingbench. I am simulating two environments, “development” and “production”. Development is hosted on a virtual machine named server3, with 2 virtual CPUs and 8 GB DRAM. The production environment is my lab server, with 2s12c24t and 64 GB DRAM. To demonstrate a case of “same workload-vastly different performance” I created the swingbench order entry schema (SOE) on development without any indexes. All indexes are present in production.

If you create the schema on the command line like I just did, make sure to grant execute on DBMS_LOCK to SOE as sys once the procedure is complete.

The command to create the swingbench schema in “production” included all the indexes, for this reason alone there should be a lot more throughput seen in “production” compared to “development”.

The workload has been generated using charbench, and I specified that AWR snapshots should be created at the beginning and end of the test. Each test had a run-time of 10 minutes. To make sure I am using the same setup I used the same swingconfig.xml (with the exception of the connection string of course).

Working with raw AWR data and how to move it between systems is documented in the Database Performance Guide: chapters 6 covers transporting AWR data and chapter 8 covers the aspect of comparison. On a high level, the procedure in this blog involves only very few steps:

Extract AWR data from each database, production and development

Transfer the resulting AWR data to the AWR repository database. In my case this is a third database that will contain the AWR information about itself, and the AWR extracts to be loaded

Import the data

AWR data export

Here is an example of the procedure that I completed on production to export the AWR data for my swingbench run. CAREFUL: the dump can be very large! Ensure you have the necessary space in your destination directory.

As a result of that operation you’ll get 2 files: a log file and a dump file. As you can imagine there is some data pump activity involved. Transfer the dump file to your AWR repository database. In a fit of curiosity I named that database AWRDWH.

Repeat this task with the other database, in my case it’s NCDB on server3, selecting the releavant AWR snapshots and transfer the dump file to the AWRDWH database’s DATA_PUMP_DIR.

Loading the AWR extracts

Once the data is available for load, switch environment to the AWR repository database and load it. In this example I’m loading the extract from server3.

One of the treats of the difference report is that it shows you the difference in environment. It is immediately visible that the hosts are quite different, and as a result, so is the throughput. This is not much of a revelation because I knew from the beginning that my results were going to be different, but if you are seeing the systems for the first time this might be useful information.

Using sections further down in the report you should be able to compare execution statistics for the various SQL IDs (they are identical because of my identical workloads), and other pieces of information that give you a better understanding of what is going on.

RDA, or the Remote Diagnostics Agent, has been around for a little while. Over the time, and with the growing number of Oracle’s acquisitions it has become, shall we say, a little more difficult to handle. It appears to me as if every one of them will have its diagnostics handled by RDA making it hard to focus on something specific, like for example the database.

I won’t go into very detail of the Remote Diagnostic Agent in this post; please make sure you read and understand the documentation on MOS and the README* files that come with it before using it.

I based this blog post on Oracle Linux 7.2 and RDA 8.12.16.6.14 in case you find this via a search engine.

Basic Usage

In the first step you need to download RDA from My Oracle Support. I used DOC ID 314422.1 to get it for Linux x86-64. Installation was simple, I just unzipped the zipfile as always into the RDBMS owner’s home which has plenty of free space for data collection in my lab systems. You might actually already have a copy of RDA somewhere that shipped with your Oracle product as it comes bundled with a lot of them. I am focusing on the Oracle database and infrastructure and as far as I know, have to download RDA in this case.

If you follow the documentation, you verify the installation and begin with the setup. And here is where I am starting to struggle with the tool’s concept, but let me explain why:

And this takes you on a long journey where you are asked to collect diagnostic information about pretty much every Oracle product or at least so it seems. I was actually only interested in my local Oracle database installation, but nevertheless I was prompted for Oracle VM for example:

A useful question for some, but not for me at this point. And so on; the list gets longer it seems with every release. And I am not willing to answer 42,000 questions every time I deploy RDA! That left me with 2 options:

Patiently go through the list and dutifully answer every question. If you inadvertently hit the return key one time too often-which happens quite easily-you can correct the problem later by editing the configuration file.

Consider using profiles

Option 1 might be viable because it’s a one-off process but I personally don’t find it very practical for various reasons. And yes, it seems possible to let RDA “guess” your environment but that didn’t work as I expected.

Profiles

Profiles on the other hand are easy to use! You can view them online on MOS 391983.1 or alternatively as part of RDA’s built-in man-page by executing ./rda.sh -L and searching for “Available profiles:”. If you haven’t set the PAGER variable you might end up with more, but I think less is more :)

These profiles are a logical grouping of various tests provided by the RDA framework. From a remote support point of view they are fantastic and leave little room for user error! Always get the information you need, not a report that only has half of the required information for troubleshooting. If you like to see more detail you can combine the -M (for the manual) and -p (for profile) options as in this example:

Some time ago I wrote a post about using OSWatcher for system analysis. Neil Chandler (@ChandlerDBA) rightfully pointed out that although OSWatcher was cool, TFA was the way to go. TFA can include OSWatcher, but more importantly it adds a lot of value over and above what OSWatcher does.

I guess it depends on what you want to do-I still think that OSWatcher is a good starting point and enough for most problems on single instance systems. When it comes to clustered environments, TFA looks a lot more appealing though.

In this article I am taking a closer look at using TFA – which is part of the Oracle 11.2.0.4 and 12.1.0.2. TFA is automatically updated as part of the quarterly patches, which is nice because the default/base release does not seem to be working properly. Thankfully TFA can be patched outside the regular patch cycle.

What is TFA?

TFA is a tool which – amongst other things – helps you gather information about incidents across your cluster. If you ever worked on Exadata half-racks or other clusters with more than 4 nodes you will quickly start to appreciate having to use one tool for this task. The TFA output is suitable for attaching to a Service Request which should, at least in theory, help speed up the problem resolution.

It is also an excellent parsing tool and has excellent reporting capabilities thanks to its “analyze” command.

As an added benefit you get a lot of tools that were previously known as “RAC and DB Support Tools Bundle”. This includes OSWatcher as well, the reason for this post.

Plus you don’t have to worry about starting OSWatcher when booting: TFA is started via a systemd unit file in Oracle Linux 7, and I found it started as a service in Oracle Linux 6. On OL7.x you can check its status using the standard systemd commands suite, as shown here:

It is quite likely that your version of TFA is older than the one available from MOS ID 1513912.2, which appears to be its main landing page. I applied the proactive bundle patch for July 2016 to my 2 node RAC cluster and found the TFA version to be 12.1.2.7.0. At the time of writing Oracle has released TFA 12.1.2.8.0.

The update is quite simple, but needs to be performed as root. To be sure I’m not doing something I shouldn’t be doing I checked the current version:

If you read MOS 1513912.2 carefully, you undoubtedly spotted that beginning with TFA 12.1.2.3.0 the RAC and DB Support Tools Bundle is included with TFA, alongside some other very useful utilities. But you only get them after deploying TFA from MOS. Here is the list as shown post-patch:

The stock-version, although it gets patched with the proactive bundle patch, does not include them. I ran this command before applying the TFA patch, but after having applied the proactive bundle patch to my cluster:

This is actually a feature, not a bug, as documented in MOS 2054786.1. The note states quite clearly that the RAC and DB Support Tools bundle is only installed if you deploy the MOS version. I just did that; I am good.

TFA Tools

I really love the idea of having these tools availble. The TFA user guide, also available from MOS 1513912.2 (tab “Users Guide”) explains from page 39 onwards how to use them.

The analyze command can do a lot more, make sure to have a look at the documentation to find out more.

But you can run OSWatcher directly as well:

[oracle@rac12sbnode1 ~]$ tfactl oswbb -h
Usage : /u01/app/12.1.0.2/grid/tfa/rac12sbnode1/tfa_home/bin/tfactl.pl oswbb \
[<OSWatcher Analyzer Options> | -since n[mhd] ]
Options:
-since n[mhd] Run OSWatcher analyzer for last n [m]inutes or [h]ours or [d]ays.
<OSWatcher Analyzer Options>: -P <name> -L <name> -6 -7 -8 -B <time> -E <time> -A
-P <profile name> User specified name of the html profile generated
by oswbba. This overrides the oswbba automatic naming
convention for html profiles. All profiles
whether user specified named or auto generated
named will be located in the /profile directory.
-A <analysis name> Same as option A from the menu. Will generate
an analysis report in the /analysis directory or
user can also specify the name of the analysis file
by specifying full qualified path name of file.
The "A" option can not be used together with the
"S" option.
-S <> Will generate an analysis of a subset of the data
in the archive directory. This option must be used
together with the -b and -e options below. See the
section "Specifying the begin/end time of the analysis"
above. The "S" option can not be used together with
the "A" option.
-START <filename> Used with the analysis option to specify the first
file located in the oswvmstat directory to analyze.
-STOP <filename> Used with the analysis option to specify the last
file located in the oswvmstat directory to analyze.
-b <begin time> Used with the -S option to specify the begin time
of the analysis period. Example format:
-b Jan 09 13:00:00 2013
-e <end time> Used with the -S option to specify the end time
of the analysis period. Example format:
-e Jan 09 13:15:00 2013
-L <location name> User specified location of an existing directory
to place any gif files generated
by oswbba. This overrides the oswbba automatic
convention for placing all gif files in the
/gif directory. This directory must pre-exist!
-6 Same as option 6 from the menu. Will generate
all cpu gif files.
-7 Same as option 7 from the menu. Will generate
all memory gif files.
-8 Same as option 8 from the menu. Will generate
all disk gif files.
-NO_IOSTAT Ignores files in the oswiostat directory from
analysis
-NO_TOP Ignores files in the oswtop directory from
analysis
-NO_NETSTAT Ignores files in the oswnetstat directory from
analysis
-NO_PS Ignores files in the oswps directory from
analysis
-MEM_ALL Analyzes virtual and resident memory allocations
for all processes. This is very resource intensive.
-NO_Linux Ignores files in the oswmeminfo directory from
analysis
e.g:
/u01/app/12.1.0.2/grid/tfa/rac12sbnode1/tfa_home/bin/tfactl.pl oswbb
/u01/app/12.1.0.2/grid/tfa/rac12sbnode1/tfa_home/bin/tfactl.pl oswbb -since 2h
[oracle@rac12sbnode1 ~]$

Those look quite similar to the ones I have shown you in my previous post about OSWatcher, so I won’t go into detail. Here is an example, note how I can specify the last 10 minutes:

[oracle@rac12sbnode1 ~]$ tfactl oswbb -since 10m
Validating times in the archive...
Warning. The end date you entered is not contained in the archive directory
The end date you entered is: Tue Aug 02 15:39:06 BST 2016
The last date in the archive is: Tue Aug 02 15:38:55 BST 2016
Defaulting to using the last date in the archive
Scanning file headers for version and platform info...
Parsing file rac12sbnode1_iostat_16.08.02.1500.dat ...
Parsing file rac12sbnode1_vmstat_16.08.02.1500.dat ...
Parsing file rac12sbnode1_top_16.08.02.1500.dat ...
Parsing file rac12sbnode1_ps_16.08.02.1500.dat ...
...

After the analysis has completed, the report is opened in a pager and shown.

TFA really is a very useful tool, and this is not only due to the integration of OSWatcher. A lot of useful information that is beyond the scope of this article is available, and the search function is quite invaluable when trying to hunt down problems in your cluster. Maybe I’ll dedicate another post to that at some later time …

I have already written about the use of Connection Manager 11.2 to govern access to a database. While researching this piece I have updated the original post so that it’s relevant for 12c as well.

Although the idea of using Connection Manager is tempting, the obvious first question is about high availability of the process, you don’t want it to become a Single Point of Failure (SPOF). After all, if the gatekeeper to your database environment fails, you are effectively closed for business. One option would be to add another CMAN process that could be used.

Lab Setup

I have created a few VMs to simulate the scenario. I installed CMAN in 2 VMs running Oracle Linux 7 and CMAN 12c. The client homes have been patched up to the April PSU because that’s considered good practice (although I couldn’t find a reference to CMAN in the OPatch logs). The hosts are named oracledev and server4.

I have a 4 node RAC 12c database named NCDB to which the CMAN rules provide access. The SCAN is named rac12pri-scan.

And finally I have an Oracle 12c installation on server3 which will act as the client.

CMAN configuration

The CMAN configuration is quite straight forward. Following my earlier post’s instructions I created the following $ORACLE_HOME/network/cman.ora on oracledev:

Please note that the configuration file has been kept at its bare minimum to keep the discussion simple. You should review the net services reference guide for a list of available parameters and how to secure the system.

The configuration on host server4 is identical except that the CMAN host has changed. Using that information I can start the CMAN process:

So this is all fair and good, but how can I configure the client to connect to the database? I would like two things to happen:

Connect time load balancing. In other words, don’t hammer just one of the two CMAN processes with incoming connection requests while the second one is idling around

Ensure that the alternative CMAN is tried in case one of the two systems dies

Things seemed a bit more complicated because I have to use SOURCE_ROUTE to indicate to the client that a connection manager process is involved. For instance, if I have a single CMAN process, I would use the following entry in tnsnames.ora:

After a bit of digging around on My Oracle Support I gave up and used my search engine skills, and that proved more effective. The Oracle Database Net Services References has the correct example in chapter 6, which looks as follows after I applied it to my environment:

Using an approach like this allows me to connect to the database even if one of the two CMAN systems are down. But what about load balancing? This appears to work as well, tailing the cman1.log file I can see that out of 10 sessions I requested in a bash for-loop they have been evenly spread across both CMAN hosts: