Saving you 15 minutes: Analytics to the rescue (ASM to File system DB migration)

As part of a piece of work I’m doing I need to ‘quickly’ migrate a database from Linux to AIX (and upgrade it as well).  There are many ways to do this but due to the various constraints (lots of constraints) I need to first restore the database onto another Linux host.  Now this Linux host won’t have ASM running on it and for some reason I have been provisioned 6, 2 TB file systems.  So what is the easiest way to do the restore when there isn’t a large single file system?  For me, at least, it’s an excuse to use an analytic function:

The query is works on the basic premise of putting datafiles into one file system and once that file system is full moving onto the next.  This is of course for the RMAN restore script.  There are too many files to do this manually and that would also be prone to typos.

The first query gathers a running total of the sum of the datafiles size:

select file_id, 'SET NEWNAME FOR DATAFILE '||file_id NEWNAME, 
sum(bytes/1024/1024/1024) OVER (ORDER BY FILE_ID) RUNNING_TOTAL 
from  dba_data_files order by file_id

   FILE_ID NEWNAME                         RUNNING_TOTAL
---------- ---------------------------------------------
       240 SET NEWNAME FOR DATAFILE 240    5977.85672
       241 SET NEWNAME FOR DATAFILE 241    5985.85672
       242 SET NEWNAME FOR DATAFILE 242    5993.85672
       243 SET NEWNAME FOR DATAFILE 243    6001.85672
       244 SET NEWNAME FOR DATAFILE 244    6009.85672

The second query then uses a case statement to then determine the file system location based on the running total:

select newname, 
       (CASE when running_total < 2000 THEN 'to oracle/dbapb2/' 
             when running_total BETWEEN 2000 and  4000 THEN 'to /oracle/dbapb3/'            
             when running_total BETWEEN 4000 and  6000 THEN 'to /oracle/dbapb4/'             
             when running_total > 6000 THEN 'to /oracle/dbapb5/'
       END), 'FILE'||file_id||';'
      from (
select file_id, 'SET NEWNAME FOR DATAFILE '||file_id NEWNAME, 
sum(bytes/1024/1024/1024) OVER (ORDER BY FILE_ID) RUNNING_TOTAL 
from  dba_data_files order by file_id)

SET NEWNAME FOR DATAFILE 240    to /oracle/dbapb4/ FILE240;
SET NEWNAME FOR DATAFILE 241    to /oracle/dbapb4/ FILE241;
SET NEWNAME FOR DATAFILE 242    to /oracle/dbapb4/ FILE242;
SET NEWNAME FOR DATAFILE 244    to /oracle/dbapb5/ FILE244;
SET NEWNAME FOR DATAFILE 245    to /oracle/dbapb5/ FILE245;
SET NEWNAME FOR DATAFILE 246    to /oracle/dbapb5/ FILE246;

Embedded in the query is the SQL to generate the NEWNAME commands for the RMAN command.  Simples



log file sync waits and VMWare Storage IO Control

Before I get started, this blog entry isn’t a criticism of a necessary feature found in VMWare nor will I comment on the virtues of running an Oracle Production environment on VMWare.

Rather this is about highlighting something I was previously unaware of; Storage IO Control. We’ll get to that eventually, but for now let’s set the scene….

For the past month or so I’ve been trying to diagnose log file sync waits on a VMWare environment and going back and forth with sys/storage admins.

View from OEM: oem

View from AWR: lfpw

First stop was to look at the log file parallel write stat in the AWR reports – this showed a 3 ms write, perfectly good enough one might presume: lfpw

Initially they had configured 5(!) members per log group (think someone got a bit confused :) ) so my initial thoughts were to trim that down and everything will *probably* be better.

Having done that and some other tactical code changes the log file sync waits persisted.

I decided to do a bit more digging on the server and initially looked at some output from vmstat (vmstat 2 100 is your friend)

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
0  3 3108608 309228 123692 27757736    0    0     0     0 1511 2451  1  1 25 74  0
0  3 3108608 309252 123700 27757748    0    0    16    80 1751 2593  6  0 37 57  0
0  4 3108608 309624 123728 27757788    0    0    12    76 1677 2776  1  1 50 49  0
0  4 3108608 309764 123736 27757800    0    0     4     8 1538 2759  0  1 50 49  0
0  6 3108608 309640 123748 27757816    0    0    12    66 1886 3128  1  0 50 49  0
0  6 3108608 309516 123764 27757816    0    0     0    66 1630 2959  1  0 50 49  0
0  5 3108608 291040 123768 27757892    0    0    32     6 2159 3165  5  4 44 47  0
2  7 3108608 284072 123772 27757888    0    0    60   356 1733 3079  2  1 43 55  0
1  0 3108608 277524 123804 27761008    0    0  1482  5522 4043 3950 25  3 35 38  0
0  0 3108608 285676 123812 27760992    0    0    52   594 1918 2762  3  1 95  2  0
0  0 3108608 285428 123828 27761088    0    0    32   112 1535 2387  3  1 96  1  0

During the time of the log file sync I could see processes were being blocked on system resource (>0 in b column is never good, it means procs are waiting for system resource – CPU/disk).

You can also see there is a spike in IO after the processes are unblocked.

Hmm CPU looks OK maybe there is a disk issue after all.

So I then looked at iostat focusing on the redo log volume group :

iostat -x -t -d -N vg_ora_redologs 2 1000 

01/04/15 09:34:30
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
vg_ora_redologs     0.00     0.00   11.50   31.50    92.00   252.00     8.00     0.03    0.62   0.37   1.60

01/04/15 09:34:32
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
vg_ora_redologs     0.00     0.00   17.00   41.00   136.00   328.00     8.00     0.04    0.64   0.34   1.95

01/04/15 09:34:36
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
vg_ora_redologs     0.00     0.00    0.00    1.50     0.00    12.00     8.00     1.77    0.00 630.33  94.55

01/04/15 09:34:38
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
vg_ora_redologs     0.00     0.00    0.00    0.00     0.00     0.00     0.00     3.00    0.00   0.00 100.00

01/04/15 09:34:40
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
vg_ora_redologs     0.00     0.00    0.00    0.00     0.00     0.00     0.00     3.00    0.00   0.00 100.05

01/04/15 09:34:42
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
vg_ora_redologs     0.00     0.00    0.00    0.00     0.00     0.00     0.00     3.00    0.00   0.00  99.95

01/04/15 09:34:44
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
vg_ora_redologs     0.00     0.00    0.00    0.00     0.00     0.00     0.00     3.00    0.00   0.00 100.00

01/04/15 09:27:02
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
vg_data3-data3     0.00     0.00   33.50  753.50   268.00  6028.00     8.00     6.50   33.11   0.75  59.40

01/04/15 09:27:04
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
vg_data3-data3     0.00     0.00    5.50   57.00    44.00   456.00     8.00     0.05    0.75   0.37   2.30

So we can see %util reached 100% but w/s (writes per second) was 0!

On appearances the disk looks saturated and not able to keep up with the writes.

The average log file parallel write time had hidden this fact because we were only experiencing very intermittent spikes.

I asked the VM and SAN teams to see if they had any comparative stats from the VM host level and I was presented with this:


So much in line with the AWR snap interval the IO response is being averaged out over a 30 minute snapshot period so would not pick up our spikes.

It took some time to convince everyone that there was indeed a problem but in the end the iostat output was pretty conclusive.

I managed to convince the Storage team that we really did need RAID 10 for our redo logs and not RAID 5….

So the redo logs were moved to a new shiny RAID 10 area and everything was great.

Well ish…

The log file sync waits greatly reduced but we were still seeing occasional spikes.

So back to iostat and lo and behold the same symptoms.

I graphed out w/s against %util for a pictorial view of what was happening:


So what was the problem now?

Over the course of my investigation I’d read about a feature called Storage IO Control (SIOC)

What this does is very similar to the way in which Database Resource Manager works.

My (basic) understanding of what SIOC does is that it kicks in when the overall disk latency is 30ms or higher (based on a 4 second average)

It will then throttle VM IOs based on the “share” allocated for that particular VM. This is a necessary feature to stop one VM hogging all of the IOPS for a shared LUN.

After sitting with the VM Admins it became clear that SIOC was indeed configured and that our rather important VM was actually allocated the lowest share.

After reconfiguring the SIOC share the problem was fixed (but maybe moved to another less important VM :) )

So in summary…

  • Never go by averages!
  • There are a wealth of *nix performance tools available so don’t be afraid to use them
  • In VM environments where SIOC is enabled ensure that the disk shares are prioritized correctly!

Saving you 15 minutes (and a software download request from MOS): Cloning Oracle Homes with 12c OEM

I was attempting to install a specific version of Oracle the other day and due to the Oracle binaries download speed (estimated completion 2050) I thought why not clone; then I thought why not clone with and stick it in OEM.

There are a number of ways to do this and here is a quick example:

First of all I need to create an entry in the software library:

  • Enterprise > Provisioning & Patching > Software Library
  • Action > Create Component > Oracle Database Software Clone

You will notice that you can create installation media components or software clones.  The benefit of using the software clones is you can pick a home which already has all the PSUs deployed.


When you create a ‘software clone’ component you can select from the various Oracle homes; now if you have multiple Oracle homes make sure you double check which one.  Also the search tool doesn’t like wildcards it seems so you will need to know the host name.  Unfortuantely you can’t search by Oracle version which would be ideal.


Also verify the working area directory and software library upload file location where the clone will be uploaded.

  • Setup > Provisioning & Patching > Software Library

If you go to the OS for this location you won’t be able to find the clone as a clone as the OMS doesn’t store it in a ‘readable’ format.  Once you have finished the wizard a job will be executed to create the clone.

So to then provision the clone you go to:

  • Enterprise > Provisioning & Patching > Database Provisioning

Select the Provision Oracle Database > Launch

All you need to do is step through the wizard and select the various components.  Now the key screen is below; where you select the media component; here you can select either Oracle Installation Media or Software Clone.  I selected the clone.


This worked first time with the following config. Cloning Agent Target Agent (No PSUs) Oracle Home
Redhat 5 Hosts

So this is a great case for using the 12c OEM software library as errrr a software library for all those different versions of Oracle you run.  If you urgently need and no longer have that version in your estate but you diligently loaded into OEM then that could save you way more than 15 minutes instead of raising an SR with Oracle to get a media download request.


Saving you 15 minutes: UKOUG RDBMS User Group Manchester

Yesterday I attended the UK OUG up in Manchester and it was an interesting day.

@neilijdba did an interesting deconstruction of the instance startup.  Seeing the various processes being put through strace and IPCS was handy, especially seeing the various flags used and the information it would give you around child / parent processes.  Unfortunately the photos I took didn’t come out very well but seeing those commands and examples posted somewhere would be good (hint hint).

@dbasushi session was around the 12c OEM agent and I managed to have a quick chat with him beforehand and discussed various bits and bobs around OEM.

In terms of the session there were some interesting points:

It seems that for 12c OEM the agent was pretty much re-written as it can became fairly bloated and resource hungry.  The re-write was in Java which interestingly allowed the shareable platform code to be around 90% as opposed to about 60-70% in previous versions.  The real benefit in this was in creating patches which could be applied to all platforms; whereas historically some platforms had to wait a lot longer for patches to be applied.  For ages I have always been downloading patches for the agents thinking, that’s odd another generic patch, I wonder why it’s not platform specific…now I know why!!

Another interesting point was around 12c agent uploads, ‘backoff’ requests and target lifecycle statues.  It seems in large OEM estates there could be periods of time where the collective agent uploads would, for want of a better word, swamp the OMS.  However if you set the lifecycle property field in the targets proprieties it will actually prioritise those uploads for the mission critical / production environments.  This is another reason why target properties are key and not an admin overhead.

Agent security was also touched on, basically DON’T run your agent in HTTP mode.  In 12c the agent doesn’t store any credentials, it keeps them in the OMS until it requires them.  When it does they are retrieved and sent down the wire to the agent, however they are un-encrypted before they are sent, so if they go down HTTP instead of HTTPS then you’re asking for trouble, not literally of course.

There is more….did you know that at the point of agent start up the agent will determine the resources required based on the targets.  However if you add in a large number of targets onto the server you can actually get the agent to re-evaluate the resources required.  emdctl run_autotune agent

Finally he touched on the agent browser which can be enabled to assist de-bugging but also can give you a health score for the agent; you can login be extended the URL from the emctl status command:


Also a quick one from Phil Davies from Oracle Support; if you want your emails from MOS to include what was updated in the SR rather than just notifying you have an update check out update: 1543136.2

I will follow this blog up with some examples but I just needed to do a brain dump before I forget.

So you might say it was a pretty informative day!!


SSH by Proxy

System administrators and other IT professionals who need to maintain or use Unix-based computers in the course of their duties generally use the Secure Shell protocol, usually known as SSH, to connect a command line prompt from a remote server to their own computer. SSH can also be used to transfer files, and to act as a transport for other protocols such as HTTP, HTTPS, FTP and so on.

However, it’s often not possible to connect directly. Many companies use a gateway machine as an additional line of defence against would-be intruders – so that their production hosts can only be accessed via a single machine; a point of entry from the Internet which is carefully maintained and secure. To complicate matters, a gateway machine can often only be accessed via a VPN.

This certainly allows for a secure environment, but it can substantially complicate remote access. Let’s say (for example) I need to login to a server at a company with a gateway machine accessible via a site to site VPN. If I’m doing this from home, I might login first to a server at e-DBA (to gain access to the site to site VPN), then from there to the gateway machine, and from there to the server in need of my attention.

One way to take some of the pain out of this is to organise SSH tunnels which will forward SSH traffic from a port on my local machine through both of the intermediate hosts and to the destination server automatically. This is quite a common approach, and it certainly works. But there’s a better way to forward traffic through multiple hosts, that’s often overlooked – by using the SSH ProxyCommand directive in an SSH config.

Using an SSH config file is itself an underused practice, despite the typing and errors it can save, so first a quick overview of how that works.

Let’s say I want to login directly to an Internet-facing host somewhere. The host allows SSH connections not on the default port, 22, but on port 2233. The login to which I have access is ‘oracle’. Access is via a private key, db2key.rsa.

To login there, I can do:

$ ssh -p 2233 -i ~/keys/db2key.rsa

.. but I could instead have the SSH client remember the port, hostname, key and user for me by recording the details in its configuration file, ~/.ssh/config. The entry would look like this:

host db2
     user oracle port 2233
     IdentityFile ~/keys/db2key.rsa

With this in place, I can now login to the dbserver2 host like this:

$ ssh db2

Now, let’s suppose that I regularly need to login to a database server called jupiter at which is only accessible via their gateway machine, spock. Let’s further suppose that I can only connect to spock from our site to site VPN, so I need either to start the VPN, or connect first to a general purpose machine on our own company network. The SSH config entry for the e-DBA host might be:

host suzanne
     user jamesg
     port 2777
     IdentityFile ~/keys/suzanne.rsa

I can now login to suzanne just by typing

$ ssh suzanne

.. but more importantly for the purposes of this piece, I can use the above configuration in an entry for the gateway machine which will allow me to login using a single command – using the SSH ProxyCommand directive to forward SSH traffic through automatically. This would take the following form:

host spock
     user edbaid
     IdentityFile ~/keys/spockkey.rsa
     ProxyCommand ssh suzanne -W %h:%p

With the above two entries in place in ~/.ssh/config on my own computer I can type

$ ssh spock

.. and I’ll appear to login there directly, though in actual fact all SSH traffic will be forwarded via suzanne, using the details from the first entry.

Note that the name provided in the hostname directive must in this case resolve from suzanne (I could instead have used the IP address). The location of the key given in the IdentityFile directive though is still a local pathname (in effect the authentication is passed through with the SSH traffic).

The Proxycommand directive specifies the command to connect to the server, so that the stdin / stdout of the command will be used as a transport for SSH.

So with the entry for the gateway machine spock in place, an additional entry for the database server, jupiter, can be added that will make use of it – in effect chaining the three configurations together recursively. It would look like this:

host jupiter
     user root
     IdentityFile ~/keys/jupiterroot.rsa
     ProxyCommand ssh spock -W %h:%p

So this configuration tells SSH that connections to jupiter are routed through the gateway spock, which itself accepts connections through a machine on our own network, suzanne. And with the three entries in place on my local PC, even if working at home or otherwise away from the office, I can simply do:

jglaptop $ ssh jupiter
Last login: Thu Apr 9 06:11:00 2015 from polaski.somecompany,net
[root@jupiter ~]#

.. and I’m straight in, no need to start tunnels or fire up the company VPN. All communication with jupiter from this point will be directed through two intermediate hosts, but that’s handled by the SSH configuration and nicely transparent.

This technique is most valuable when used in conjunction with SSH keys for passwordless login, as illustrated in the example above. Traditional password-based authentication will still work, but you may be prompted for more than one password on the way to your destination, depending on the number of intermediate hosts set up to require passwords.