Skip navigation.

DBA Blogs

The Hitchhiker’s Guide to the EXPLAIN PLAN (Act II)

Iggy Fernandez - Mon, 2014-06-16 13:04
Over at ToadWorld: Part 5: SQL Sucks! Part 6: Trees Rule Part 7: Don’t pre-order your EXPLAIN PLAN Part 8: Tree Menagerie Bonus article: Equivalence of Relational Algebra and Relational Calculus The story so far: A relational database is “a database in which: the data is perceived by the user as tables (and nothing but tables)  and […]
Categories: DBA Blogs

Instant REST API For Any Database

Pythian Group - Mon, 2014-06-16 07:48

Not so long ago, I was playing with ElasticSearch, which has the interesting characteristic of having a REST API as its primary interface. Sure, it’s a little more stilted and awkward than any native interface but, on the other hand, it’s a nice universal type of API. Any language that can make a http request can talk to it and, hey, bad comes to worse, even ‘curl’ will do. It would be kinda cool if other databases had such a web service.

And then I began to think…

Don’t we have DBIx::Class::Schema::Loader, which can connect to a database and auto-generate its DBIx::Class schema?

package MyDB;

use parent 'DBIx::Class::Schema::Loader'; 


# later on

my $schema = MyDB->connect( 'dbi:SQLite:foo.db' ); # boom, we have our schema

And once we have a DBIx::Class representation of a schema, can’t we introspect it and pretty much get everything there is to know about it?

use Data::Printer;

# get all the table names
my @tables = $schema->sources;

# and all the columns of all the tables
for my $table ( $schema->sources ) {
    say "Table $table";
    p $schema->source($table)->columns_info;

That is, that’s if we want to do it manually, considering that there’s already SQL::Translator that can do most of the job for us.

use SQL::Translator;

print SQL::Translator->new (
    parser      => 'SQL::Translator::Parser::DBIx::Class',
    parser_args => {
        dbic_schema => $schema,
    producer    => 'JSON',

Of course, since we are talking web service, we will want to pass everything back and forth using JSON, including database entries. Well, that’s hardly a problem if we use DBIx::Class::Helper::Row::ToJSON.

So it seems we have the database side covered. For the web framework? You’ll probably not be surprised to see me go with Dancer. Not only can we leverage the serializers and plugins like Dancer::Plugin::DBIC, but setting routes are ridiculously easy.

get '/_tables' => sub {
    return [ schema->sources ];

Even niftier: remember that Dancer routes are defined at runtime, so we can introspect that schema as much as we want and come up with any route we can dream of.

my @primary_key = schema->source($table)->primary_columns;
my $row_url = join '/', undef, $table, ( '*' ) x @primary_key;
 # GET ///
get $row_url => sub {
    my @ids = splat;
    return $schema->resultset($table)->find({
        zip @primary_key, @ids
 # GET /
get "/$table" => sub {
    my @things = $schema->resultset($table)->search({ params() })->all;
    return \@things;
 # create new entry
post "/$table" => sub {
    $schema->resultset($table)->create({ params() });

Added bonus: the way Dancer’s params() conglomerate parameters defined in the query string and in the serialized body of the request plays in our favor: simple queries can be passed directly via the url, and more complicated ones can be defined as JSON structures.

So, you put all of this together, and you obtain waack. All it needs is a dsn pointing to the right database (and credentials, if needed). To illustrate, let’s try with my Digikam SQLite database.

$ waack dbi:SQLite:digikam4.db
>> Dancer 1.3124 server 28914 listening on
>> Dancer::Plugin::DBIC (0.2100)
== Entering the development dance floor ...

And now, let’s fire up App::Presto as our REST client.

$ presto http://enkidu:3000

http://enkidu:3000> type application/json

First, we can retrieve all the table names.

http://enkidu:3000> GET /_tables

We can also get the whole schema.

http://enkidu:3000> GET /_schema
   "translator" : {
      "producer_args" : {},
      "show_warnings" : 0,
      "add_drop_table" : 0,
      "parser_args" : {
         "dbic_schema" : null
      "filename" : null,
      "no_comments" : 0,
      "version" : "0.11018",
      "parser_type" : "SQL::Translator::Parser::DBIx::Class",
      "trace" : 0,
      "producer_type" : "SQL::Translator::Producer::JSON"
   "schema" : {
      "tables" : {
         "ImageRelations" : {
            "options" : [],
            "indices" : [],
            "order" : "12",
            "name" : "ImageRelations",
            "constraints" : [
                  "type" : "UNIQUE",
                  "deferrable" : 1,
                  "name" : "subject_object_type_unique",
                  "on_delete" : "",
                  "reference_fields" : [],
                  "fields" : [
                  "match_type" : "",
                  "reference_table" : "",
                  "options" : [],
                  "expression" : "",
                  "on_update" : ""

Too much? We can get the columns of a single table.

http://enkidu:3000> GET /Tag/_schema
   "iconkde" : {
      "is_nullable" : 1,
      "data_type" : "text",
      "is_serializable" : 1
   "name" : {
      "is_serializable" : 1,
      "data_type" : "text",
      "is_nullable" : 0
   "id" : {
      "is_nullable" : 0,
      "data_type" : "integer",
      "is_auto_increment" : 1,
      "is_serializable" : 1
   "icon" : {
      "is_nullable" : 1,
      "data_type" : "integer",
      "is_serializable" : 1
   "pid" : {
      "is_serializable" : 1,
      "is_nullable" : 1,
      "data_type" : "integer"

Query that table, with a simple condition…

http://enkidu:3000> GET /Tag id=1
      "name" : "orchid",
      "icon" : null,
      "id" : 1,
      "pid" : 0,
      "iconkde" : null

… or with something a little more oomphie.

$ curl -XGET -H Content-Type:application/json --data '{"name":{"LIKE":"%bulbo%"}}' http://enkidu:3000/Tag
      "pid" : 1,
      "name" : "Bulbophyllum 'Melting Point'",
      "icon" : null,
      "id" : 32,
      "iconkde" : "/home/yanick/Pictures/My Plants/IMG_0461.JPG"
      "id" : 56,
      "iconkde" : "tag",
      "icon" : null,
      "pid" : 39,
      "name" : "Bulbophyllum ebergardetii"
      "name" : "bulbophyllum",
      "pid" : 564,
      "iconkde" : null,
      "id" : 565,
      "icon" : 0

Btw: I cheated for that last one. Presto doesn’t send body with GET requests. And Dancer doesn’t deserialize GET bodies either. Patches will be written tonight.

Anyway, back with the show. We can also select specific rows by primary keys.

http://enkidu:3000> GET /Tag/1
   "id" : 1,
   "iconkde" : null,
   "pid" : 0,
   "icon" : null,
   "name" : "orchid"

Create new rows.

http://enkidu:3000> POST /Tag '{"name":"nepenthes","pid":0}'
   "pid" : 0,
   "name" : "nepenthes",
   "iconkde" : null,
   "icon" : null,
   "id" : 569

And do updates.

http://enkidu:3000> PUT /Tag/569 '{"icon":"img.png"}'
   "icon" : "img.png",
   "iconkde" : null,
   "pid" : 0,
   "name" : "nepenthes",
   "id" : 569

Not too shabby, isn’t? Mostly considering that, if you look at the source of waack, you’ll see that it barely clock over 100 lines of code. Take a minute and let this sink in.

One hundred lines of code. For a universal database REST web service.

If that’s not standing on the shoulders of giants, then I don’t know what is.

Categories: DBA Blogs

Internet Scale Design: Part Two

Pythian Group - Fri, 2014-06-13 08:01

In my previous blog post, I emphasized that internet scale design can be implemented for any type of company. Whether it’s a small, bootstrapped startup or a rapidly growing, well-funded tier 2. But if it’s suitable for that many companies, why isn’t everyone moving into the cloud? In my opinion, there are two reasons.

First, the model of utility computing doesn’t work for all business models. It is most effective in models where demand changes, where there are peaks and valleys for larger scale systems. It also works well as a way to get your startup or project off the ground with little-to-no capital investment. In the story I began in my previous blog post, the seasonality of their environment made them a perfect candidate.

The second is more of a people problem. In many companies,  IT leadership, SysAdmins, Developers, DBAs, and everyone else involved in service management, have been working with whatever technology stack that company has been using for years. It’s important to remember that most SysAdmins see their primary job as keeping things up and running, so we typically prefer working with things we know vs things we don’t.

If a C-level executive or VP returns from a conference about cloud, and issues a mandate that they need to “move everything to the cloud!” to remain “competitive” the SysAdmins will likely fail. Why? Not because they’re not smart enough, but because they simply don’t know enough about it.

While it would be ideal for the COO to say, “I want us to look into moving our platform into AWS, so I’m going to send you to get Amazon certified,” it rarely happens. Usually it sounds more like, “You’re smart, you’ll figure it out. Oh sure, you already have a full-time job keeping the lights on, but just squeeze it in when you can. We’ll need to see a POC by the end of the quarter.”

I don’t need to tell you how this ends ? it’s obvious. It will fail almost every time.

One of the amazing benefits to the Pythian model is that our teams are exposed to a wide variety of systems. We have built auto-scaling systems in AWS, OpenStack systems, VMWare systems, as well as legacy physical systems we support. Our teams are not relegated to whichever tech stack they happened to be stuck with for the last five years.

The bottom line here is that it doesn’t matter what kind of company you’re at – Whether it’s a small retailer, midsize tier 3, or larger tier 2, if you’re willing to sign on for the concept of site reliability engineering and commit to it, together we can accomplish some amazing things, all for a price you can afford.

Categories: DBA Blogs

Log Buffer #375, A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2014-06-13 07:57

What’s better; Watching Football World Cup or Reading Log Buffer? Yes, right Log Buffer, but please also take out sometime to watch the matches, as this tournament comes only once in 4 years. No? Ok, as you say. So read along then.


Alan Hargreaves reasons as Why you should Patch NTP.

This post examines the results of a recent database protection survey conducted by Database Trends and Applications (DBTA) Magazine.

Keep jobs active with screen command.

ORE Getting Connected: ore.connect and other commands.

End-to-End ODI12c ETL on Oracle Big Data Appliance Pt.4 : Transforming Data using Python & Hive Streaming

SQL Server:

Enabling and Configuring Reporting and Logging for Maintenance Plans in SQL Server 2012

The articles collected here will help you understand the theories and methodologies behind every stage of the database delivery pipeline, starting when database changes are checked in, and ending when they’re deployed to production.

Stairway to Database Source Control Level 2: Getting a Database into Source Control .

What does it take to become a database administrator, or what kinds of traits should I be looking for when I am hiring a DBA. Those traits can be summarized it two categories: Technical and Personal.

Display all errors thrown within a catch block by a restore statement in a stored procedure using extended events.


RHEL7 & the transition from MySQL to MariaDB: A first look.

FairWarning Privacy Monitoring Solutions Rely on MySQL to Secure Patient Data

MariaDB 5.5.38 Overview and Highlights

Recruiters Looking for MySQL DBAs and MySQL Developers

MariaDB Galera Cluster 10.0.11 now available

On-disk/block-level encryption for MariaDB

Categories: DBA Blogs

Gather Statistics Enhancements in 12c

Hemant K Chitale - Fri, 2014-06-13 01:32
Here are 5 posts that I did on Gather Statistics Enhancements in 12c :

1.  During a CTAS

2.  In a Direct Path INSERT

3.   Reports on Statistics

4.  Does not COMMIT a GTT

5.  Report on COL_USAGE

Categories: DBA Blogs

12c RAC: ORA-15477: cannot communicate with the volume driver

Oracle in Action - Fri, 2014-06-13 01:02

RSS content

I received ORA-15477 when I was trying to set attribute  compatible.advm  for DATA diskgroup to 12.1

– Using SQL –

SQL> alter diskgroup DATA set attribute 'compatible.advm'='12.1';

alter diskgroup DATA set attribute 'compatible.advm'='12.1'
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute compatible.advm
ORA-15238: 12.1 is not a valid value for attribute compatible.advm
ORA-15477: cannot communicate with the volume driver

– using ASMCMD

ASMCMD> setattr -G DATA compatible.advm 12.1

ORA-15032: not all alterations performed
ORA-15242: could not set attribute compatible.advm
ORA-15238: 12.1 is not a valid value for attribute compatible.advm
ORA-15477: cannot communicate with the volume driver (DBD ERROR: OCIStmtExecute)

– I tried to load advm drivers : again got error as version oracleasm rpm’s installed
did not match that of the OS

[root@host01 bin]# /u01/app/12.1.0/grid/bin/acfsload start

ACFS-9459: ADVM/ACFS is not supported on this OS version: '2.6.32-100.26.2.el5'

– checked version of oracleasm rpm’s installed –

[grid@host01 bin]$ rpm -qa |grep oracleasm


– Checked kernel version : Kernel version = 2.6.32-100.26.2.el5 : different from oracleasm rpms (2.6.18-238.el5-2.0.5-1.el5)

[grid@host01 bin]$ uname -a
Linux 2.6.32-100.26.2.el5 #1 SMP Tue Jan 18 20:11:49 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

– checked /etc/grub.conf : Two kernels were there and by default 2.6.32-100.26.2.el5 was getting loaded

[root@host01 bin]# cat /etc/grub.conf

# grub.conf generated by anaconda
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/sda2
#          initrd /initrd-version.img
title Oracle Linux Server (2.6.32-100.26.2.el5uek)
root (hd0,0)
kernel /vmlinuz-2.6.32-100.26.2.el5 ro root=/dev/sda2 rhgb quiet
initrd /initrd-2.6.32-100.26.2.el5.img
title Oracle Linux Server-base (2.6.18-238.el5)
root (hd0,0)
kernel /vmlinuz-2.6.18-238.el5 ro root=LABEL=/ rhgb quiet
initrd /initrd-2.6.18-238.el5.img

– Edited /etc/grub.conf and modified default=1 so that kernel 2.6.18-238.el5 was loaded at boot time

# grub.conf generated by anaconda
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/sda2
#          initrd /initrd-version.img


title Oracle Linux Server (2.6.32-100.26.2.el5uek)
root (hd0,0)
kernel /vmlinuz-2.6.32-100.26.2.el5 ro root=/dev/sda2 rhgb quiet
initrd /initrd-2.6.32-100.26.2.el5.img
title Oracle Linux Server-base (2.6.18-238.el5)
root (hd0,0)
kernel /vmlinuz-2.6.18-238.el5 ro root=LABEL=/ rhgb quiet
initrd /initrd-2.6.18-238.el5.img

– Rebooted and verified that loaded  kernel is 2.6.18-238.el5

[root@host01 ~]# init 6

uname -a
Linux 2.6.18-238.el5 #1 SMP Tue Jan 415:41:11 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

- Checked that oracleacfs and oracleadvm modules were not loaded

[root@host01 ~]# lsmod |grep oracle
oracleasm              84136  1

– Tried to load ADVM modules – got error as ADVM/ACFS was not installed

[root@host01 ~]# $ORACLE_HOME/bin/acfsload start
ACFS-9391: Checking for existing ADVM/ACFS installation.
ACFS-9129: ADVM/ACFS not installed

– Used oerr to diagnose ACFS-9129

[root@host01 ~]# oerr ACFS 9129

09129, 0, "ADVM/ACFS not installed"
// *Cause:  No ADVM/ACFS drivers or commands have been found on the system.
//          All ADVM and ACFS actions will be disabled.
// *Action: Install components using 'acfsroot install <options>'.  No ACFS
//          file systems or ADVM volume devices will be available until
//          ADVM/ACFS has been installed.

– Installed ADVM/ACFS as advised by oerr

[root@host01 ~]# acfsroot install -h

ACFS-9161:  acfsroot install: Install ADVM/ACFS components.
ACFS-9185:  Usage: acfsroot install [-h] [-s | -v | -t <0,1,2>] [-l <directory>]
ACFS-9132:         [-h]             - print help/usage information
ACFS-9131:         [-s]             - silent mode (error messages only)
ACFS-9159:         [-v]             - verbose mode
ACFS-9332:         [-l <directory>] - location of the installation directory
ACFS-9189:         [-t <0,1,2> ]    - trace level
[root@host01 ~]# acfsroot install -v

ACFS-9500: Location of Oracle Home is '/u01/app/12.1.0/grid' as determined from the internal configuration data
ACFS-9505: Using acfsutil executable from location: '/u01/app/12.1.0/grid/usm/install/cmds/bin/acfsutil'
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9155: Checking for existing 'oracleoks.ko' driver installation.
ACFS-9155: Checking for existing 'oracleadvm.ko' driver installation.
ACFS-9155: Checking for existing 'oracleacfs.ko' driver installation.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9503: ADVM and ACFS driver media location is '/u01/app/12.1.0/grid/usm/install/Oracle/EL5/x86_64/2.6.18-8/2.6.18-8.el5-x86_64/bin'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/Oracle/EL5/x86_64/2.6.18-8/2.6.18-8.el5-x86_64/bin/oracleadvm.ko' to the path '/lib/modules/2.6.18-8.el5/extra/usm/oracleadvm.ko'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/Oracle/EL5/x86_64/2.6.18-8/2.6.18-8.el5-x86_64/bin/oracleoks.ko' to the path '/lib/modules/2.6.18-8.el5/extra/usm/oracleoks.ko'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/Oracle/EL5/x86_64/2.6.18-8/2.6.18-8.el5-x86_64/bin/oracleacfs.ko' to the path '/lib/modules/2.6.18-8.el5/extra/usm/oracleacfs.ko'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/advmutil' to the path '/sbin/advmutil'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/advmutil.bin' to the path '/sbin/advmutil.bin'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/fsck.acfs' to the path '/sbin/fsck.acfs'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/fsck.acfs.bin' to the path '/sbin/fsck.acfs.bin'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/mkfs.acfs' to the path '/sbin/mkfs.acfs'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/mkfs.acfs.bin' to the path '/sbin/mkfs.acfs.bin'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/mount.acfs' to the path '/sbin/mount.acfs'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/mount.acfs.bin' to the path '/sbin/mount.acfs.bin'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/acfsdbg' to the path '/sbin/acfsdbg'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/acfsdbg.bin' to the path '/sbin/acfsdbg.bin'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/acfsutil' to the path '/sbin/acfsutil'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/acfsutil.bin' to the path '/sbin/acfsutil.bin'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/umount.acfs' to the path '/sbin/umount.acfs'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/umount.acfs.bin' to the path '/sbin/umount.acfs.bin'
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9321: Creating udev for ADVM/ACFS.
ACFS-9323: Creating module dependencies - this may take some time.

- Checked that  ACFS/ADVM modules have been loaded

[root@host01 ~]# lsmod |grep oracle

 oracleacfs           2837904  0
oracleadvm            342512  2
oracleoks             409560  2 oracleacfs,oracleadvm
oracleasm              84136  1

– Now I could change the attribute compaible.advm for diskgroup DATA to 12.1 successfully

SQL> alter diskgroup DATA set attribute 'compatible.advm'='12.1';

Diskgroup altered.

I hope this post was useful.

Your comments and suggestions are always welcome.


Related Links:


12c RAC Index




Comments:  0 (Zero), Be the first to leave a reply!You might be interested in this:  
Copyright © ORACLE IN ACTION [12c RAC: ORA-15477: cannot communicate with the volume driver], All Right Reserved. 2014.

The post 12c RAC: ORA-15477: cannot communicate with the volume driver appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Getting your Transaction ID

Hemant K Chitale - Fri, 2014-06-13 00:42
You can get the Transaction ID for a session by joining V$SESSION.TADDR to V$TRANSACTION.ADDR.

A Transaction ID consists of the Undo Segment #, the Slot # and the SEQ #.

For example :
SQL> select dbms_transaction.local_transaction_id from dual;



Thus, my current transaction is in Undo Segment 6, Slot 3, Sequence 9463.
SQL>    select count(*) from v$transaction;


SQL> col username format a12
SQL> l
1 select s.username, s.sid, s.serial#,
2 t.xidusn, t.xidslot, t.xidsqn
3 from v$session s, v$transaction t
4* where s.taddr=t.addr
SQL> /

------------ ---------- ---------- ---------- ---------- ----------
HEMANT 38 23 6 3 9463


As soon as I commit or rollback or issue a DDL or a CONNECT, the transaction ends.
SQL> rollback;

Rollback complete.

SQL> select count(*) from v$transaction;



Remember that an Undo segment can hold multiple transactions. That is why the Undo Segment Header has multiple "slots", one for each active transaction.  Once a transaction completes (and likely after the undo_retention period), a slot can be reused with an incremented seq#.
Categories: DBA Blogs

Counting the many rows of Oracle GoldenGate

DBASolved - Thu, 2014-06-12 22:29

!The code contained in this post is meant to be  used at your own risk!

With any Oracle GoldenGate replication configuration, it is always good to monitor what is going on.  One aspect that many people want to know is how can I validate that every record is being transferred to the target system.  Once such way is to use Oracle Veridata; however, some times there is not enough time to setup and run Oracle Veridata.  Also in smaller shops, Oracle Veridata may be a bit costly to initially start with. How can someone get the “warm-and-fuzzy” feeling when replicating data with Oracle GoldenGate?

Oracle GoldenGate has a lot of good command that can be used from the GGSCI prompt to check and see what type of transactions have been processed and the totals of those transactions.  What I was recently tasked with was a way to quickly do a validation of the rows between source and target within a Oracle GoldenGate configuration.  One way to do this is to quickly get a count of the rows per table between the source and target systems.  In discussions with a co-worker, it came out that they had a small utility, primarily scripts, that could do the counts between source and target systems.  In reviewing these scripts I saw where I could possibly improve on the process and make it a bit more streamlined and integrated with the target database.

In streamlining the process I decided to take the main portions of the scripts and rewrite it into a stored procedure that could be use from the Oracle GoldenGate user inside the target database of the replication environment.  The initial stored procedure I came up with can be seen in Code 1 below.

Code 1: Stored procedure for counts

create or replace procedure rowcounts(v_tgtschema in varchar2, v_srcschema in varchar2, v_dblink in varchar2)
—Author: Bobby Curtis, Oracle ACE
—Copyright: 20014
—Company: Accenture Enkitec Group
v_tgtcount number(16) := 0;
v_srccount number(16) := 0;
v_sqlstmt0 varchar2(1000);
v_sqlstmt1 varchar2(1000);
v_sqlstmt2 varchar2(1000);
 for vtable
 in (select table_name
     from all_tables
     where owner = v_tgtschema
     order by 1)


v_sqlstmt0 := 'select count(*) from '||v_tgtschema||'.'||vtable.table_name;
 execute immediate v_sqlstmt0 into v_tgtcount;

 v_sqlstmt1 := 'select count(*) from '||v_srcschema||'.'||vtable.table_name||'@'||v_dblink;
 execute immediate v_sqlstmt1 into v_srccount;

v_sqlstmt2 := 'update onetstats set row_cnt_source='|| v_srccount ||', row_cnt_target=' || v_tgtcount || ', end_time=sysdate where schemaname='''||v_tgtschema||''' and tablename='''||vtable.table_name||''' and dataset=null';
 execute immediate v_sqlstmt2;

 if (sql%notfound)
     v_sqlstmt2 := 'insert into onetstats (schemaname,tablename,start_time,end_time,row_cnt_source,row_cnt_target,dataset) values ('''||v_tgtschema||''','''||vtable.table_name||''',sysdate,sysdate,' || v_srccount || ',' || v_tgtcount || ', null)';
    --dbms_output.put_line (v_sqlstmt2);
     execute immediate v_sqlstmt2;
 end if;

 end loop;
       when others

As you can tell from looking at the stored procedure it uses a table to store the counts for each table in the schema being replicated.  Also notice that a database link is used to access the source server.  The table that stores the count information is just a really simple table with columns that maps to the update/insert statement in the stored procedure.  The database link needs to be configured in the local TNSNames.ora on the target server.  Code 2 and code 3 show an example of these objects.

Code 2: Table for counts

create table &ggate_user..onetstats (
 schemaname varchar2(30),
 tablename varchar2(30),
 start_time date,
 end_time date,
 row_cnt_source number,
 row_cnt_target number,
 dataset number

Code 3: Database Link to source

create database link ggcounts connect to &&ggate_user identified by &ggate_user_pwd using 'ggcounts';

The last thing that needed to be done is granting SELECT ON <TABLE> to the Oracle GoldenGate user on the source and target systems.  Once this is done, the stored procedure can be ran from SQL*Plus or SQL Developer at anytime on the target system to get a rough estimate count of the rows between the source and target databases.


twitter: @dbasolved


!The code contained in this post is meant to be  used at your own risk!

Filed under: Golden Gate, Replication
Categories: DBA Blogs

Well dang sessions got rejected ( yes again ) at Open World 2014 ... makes me grumpy

Grumpy old DBA - Thu, 2014-06-12 17:27
Two volleyed in for Open World and two shot down in flames.

Not that I had high expectations after so many tries.  I did get one accepted "once" ... ha ha!
Categories: DBA Blogs

Limits of SQL Optimization Toastmasters Talk

Bobby Durrett's DBA Blog - Thu, 2014-06-12 16:48

I think I’m done with the script for my Toastmaster’s talk: pdf

It’s Toastmaster’s talk 3 “Get to the Point” so I’m trying to do a 5-7 minute talk that focuses on how Oracle’s SQL optimizer sometimes chooses a slow plan.  It’s tough to say anything meaningful in 7 minutes and I’m not using any kind of slides so I have to describe everything verbally.

But, it’s a topic I’m passionate about so I will enjoy giving it.  I kind of got bogged down thinking about ways to relate this information to my non-technical audience’s life and I decided to give up on that.  I would probably need another 7 minutes or more to explain why they should care that there are limits to SQL optimization so I decided to focus on convincing them that the limits exist.

- Bobby

Categories: DBA Blogs

Partner Webcast – Platform as a Service with Oracle WebLogic and OpenStack

Platform as a service is defined as Platform that facilitates the deployment of applications without the complexity of buying and managing the underlying hardware and software...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Keep jobs active with screen command

DBA Scripts and Articles - Thu, 2014-06-12 12:00

What is the screen command ? When you have long running jobs, you need to keep them active when you disconnect from server. The screen command allows you to detach your terminal from you session. By doing this there is no link between your session and your terminal and you can disconnect from server while [...]

The post Keep jobs active with screen command appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Internet Scale Design: Part One

Pythian Group - Thu, 2014-06-12 07:52

Whether your company is a small, bootstrapped startup or a rapidly growing, well-funded tier 2 or 3 business, everyone dreams of having ‘Internet scale’ systems which are highly automated, anti-fragile, self-healing, and inexpensive to maintain.

The problem, however, is that those types of complex systems are only within the reach of well-funded companies who can afford to hire elite technical resources and expensive, leading edge technology systems, right?


Recently, I was working with one of my smaller retail clients. Their business is highly seasonal, doing the vast majority of their business during two separate two-month periods each year. During these periods, the demand on their system is extremely high, and downtime would be catastrophic because of the amount of their annual revenue generated during those times.

They run on a LAMP stack (Linux Apache MySQL PHP) and they’re hosted on a mix of shared VMs, and colocation equipment.  Their hosting costs are fixed year round, even though most of the year they don’t need two thirds of it.

They were beginning their annual budget review when we brought up what the next steps were for them. About a year ago, I began introducing the idea of Amazon Web Services (AWS) cloud, taking more of a site reliability engineering (SRE) approach to their business, so we scheduled a conference call with their Chief Financial Officer, a few VPs, and one of my SREs.

“We would like to move you 100% into the Amazon Cloud,” I said. “In doing so, we will automate all of your systems using image and configuration management, and set up detailed monitoring, graphics, and trending systems. We will work with your developers to redesign your applications to take advantage of the platform and its flexibility. The end result will be a system that is considerably more anti-fragile, runs in multiple regions, offers significantly faster recovery in the event of major failures, and can scale down to very little and up to larger scale in a moment’s notice.” We assured them that the systems could be self-healing and would require very little management.

The Sr. VP of Product Management’s response surprised me.

“Bill, this sounds awesome. But we’re a pretty small company, and we couldn’t possibly afford that sort of system.” I laughed a bit and responded, “Actually, not only can you afford it, but it will save you tens of thousands of dollars.”

I explained the benefits of auto-scaling and the cost savings it would bring to their business model. We discussed how those cost savings would fund the expenses of having Pythian build and manage the new service.

His response? “This is amazing – why isn’t everyone doing this?”

The answer is twofold. I’ll be sharing my insights in part two, so stay tuned…

Categories: DBA Blogs

Adding an outline hint to a PS/Query

Bobby Durrett's DBA Blog - Wed, 2014-06-11 17:52

I just finished working with a coworker on speeding up a PS/Query.  It was running well in a development environment but not in production.  I tried to find a simple hint or two to make production run like development but didn’t have any luck.  Then I remembered that my script to get a query’s plan prints out a full set of outline hints like this:

      USE_NL(@"SEL$E903463E" "CLS"@"SEL$4")
      USE_NL(@"SEL$E903463E" "SEC"@"SEL$3")
      USE_NL(@"SEL$E903463E" "SOC"@"SEL$4")

... lines removed to make this post shorter ...

      PUSH_PRED(@"SEL$F5BB74E1" "A1"@"SEL$1" 1)
      OPT_PARAM('_unnest_subquery' 'false')

A coworker edited the PS/Query and tried to add this hint but got errors because of the quotation marks.  So, I simplified the hint by removing all the double quotes and taking out the lines with single quotes because I knew they weren’t needed.  They only related to parameters that I knew were already set in production.

Here is the new quote-less hint:

      USE_NL(@SEL$E903463E CLS@SEL$4)
      USE_NL(@SEL$E903463E SEC@SEL$3)
      USE_NL(@SEL$E903463E SOC@SEL$4)

... lines removed to make this post shorter ...

      PUSH_PRED(@SEL$F5BB74E1 A1@SEL$1 1)

We ran the modified PS/Query in production and I verified that the query was running the correct plan.  The only weird thing was that because the query has a group by PS/Query stuck the hint in the group by clause and in the select clause.


... middle of select statement ...


... end of select statement ...

At first this put us off, but really the hint in the group by clause is just a comment and syntactically is insignificant even though it is ugly.

By the way, here are the options I used to output the outline hint:

select * from 

I get all my plans this way now.  It generates a lot of output that I don’t usually use.  But, seeing it reminds you that it is there if you need it.

- Bobby

Categories: DBA Blogs

so who is using oracle result caching? ( makes me grumpy )

Grumpy old DBA - Wed, 2014-06-11 17:05
Not giving out too many details ... but some people trying to use this feature ( cough cough ) have had issues.

Getting an oracle 600 and having to restart a database instance is not necessarily good right?

Some things sound so good "in theory" and maybe in some distant release the ( oracle code issues ) will be all worked out.

Now staying away from using it ... ( cough cough ) ...
Categories: DBA Blogs

Data Guard 12c New Features: Far Sync & Real-Time Cascade

The Oracle Instructor - Wed, 2014-06-11 08:10

UKOUG Oracle Scene has published my article about two exciting Data Guard 12c New Features:

Far Sync Instance enables Zero-Data-Loss across large distance

Hope you find it useful :-)

Tagged: 12c New Features, Data Guard
Categories: DBA Blogs

Availability Group – Delay with Listener Connectivity After Failover

Pythian Group - Wed, 2014-06-11 07:45

The beauty of working for multiple clients from different industries, is that you get exposed to a myriad of environment setup and configuration. Every company has its own standards for Network and Server configuration, as well different hardware vendors.  This introduces their own kinks and excitement to your everyday work – half of which you’ll likely not encounter if you are working in-house and using the same hardware.

The past week we encountered a rare and interesting issue with High Availability Group. The issue was two-fold, first it was initially not failing over automatically one one node though that was not as exciting as the second part – when it was able to failover correctly, our client was experiencing delays with the availability of the Listener Name outside its own sub-net  after failover, automatic or otherwise.  It is reachable within its own subnet but takes more than thirty minutes to be reachable outside of it even though the Failover happened smoothly and without error.

The first part was fairly straightforward. Checking on the cluster logs and event logs the automatic failover was throwing the error below when trying to failover on one of the nodes.

Cluster network name resource 'Listener_DNS_NAME' failed registration of one or more associated DNS name(s) for the following reason:
DNS operation refused.

Ensure that the network adapters associated with dependent IP address resources are configured with at least one accessible DNS server.

The error is as it says, the Computer object does not have the appropriate permissions on the Domain to register the DNS Name Resource for the Listener.  For the cluster  to perform this operation smoothly “Authenticated Users” should have read/write all permissions on the Computer Object for the cluster, its nodes and the Listener DNS Name. To do this, Log in to the Active Directory Server

  1. Open Active Directory Users and Computers.
  2. On the View menu, select Advanced Features.
  3. Right-click the object  and then click Properties.
  4. On the Security tab, click Advanced to view all of the permission entries that exist for the object.
  5. Verify that the Authenticated Users is in the list and has the permission to Read and Write All. Add the required permissions then Save the changes.

Now after doing that and testing the fail over, it is now encountering a different error, Kerberos-related one showed below.

The Kerberos client received a KRB_AP_ERR_MODIFIED error from the server ComputerName$. The target name used was HTTP/ This indicates that the target server failed to decrypt the ticket provided by the client. This can occur when the target server principal name (SPN) is registered on an account other than the account the target service is using. Ensure that the target SPN is only registered on the account used by the server. This error can also happen if the target service account password is different than what is configured on the Kerberos Key Distribution Center for that target service. Ensure that the service on the server and the KDC are both configured to use the same password. If the server name is not fully qualified, and the target domain (Domain.COM) is different from the client domain (Doamin.COM), check if there are identically named server accounts in these two domains, or use the fully-qualified name to identify the server.

Ah, the often over-looked SPN. This should be part of your installation process – setting the SPN. To keep the story short and so we can get to the exciting part, you can refer here for the detailed instructions on how to configure the SPN for SQL Server.  Aside from registering the SPN for each of the Nodes as specified on the MSDN Link, You’ll also need to register the SPN for the Listener, as always 1433 is the port being used by your SQL Server:

setspn -A MSSQLSvc/ DOMAIN/SQLServiceAccount

This will enable Kerberos for the client connection to the Availability Group Listener and address the errors we received above. After configuring the SPN for the servers Automatic Fail over is now running smoothly, or so we thought.

The client came back to us that it was taking some time for the application to connect to the Listener Name. Checking on the cluster logs and SQL Server’s end, everything appears to be in order. No errors being thrown and Server is accessible. Now it get’s interesting. Ping test within the Database sub net is successful but ping test outside of it was timing out. It was able to connect though after a substantial amount of time.  After a few more test and checking the behavior is the same on both nodes. It takes more than thirty minutes for the Name to be reachable outside of the database sub net.  After involving the Network Admin we found out that  a MAC Address conflict is happening.  That’s our “Aha!” moment.  Windows 2003 servers and later issues a Gratuitous ARP (GARP) requests during failover. There are some switches/devices that does not forward Gratuitous ARP by default.   This causes the devices on the other end of the switch to not have the correct MAC address associated to the Name.  This causes the failure. It often corrects itself when the router detects the failures and do a broadcast and gets the correct value. That’s why it becomes accessible after some time. This KB details the issue. To address this, changes must be done on the configuration of the switches, you’ll need to check with your hard ware vendor for this.

Now, that could be the end of our problems, but after enabling the switch to forward GARP, we found out that the server itself is not sending a GARP request.  This is a server configuration issue and requires some Registry changes. Open the Registry for the server and locate the key below:


From there check if there is a key for ArpRetryCount, if there is make sure that the Value is not set to 0. The value could be between 0-3. After changing this and restarting the servers Everything works perfectly.

Last two issues are a bit rare, and something I wouldn’t have experienced if the client wasn’t using that particular hard ware and that particular standard configuration.

Categories: DBA Blogs

SCN to Timestamp and back

DBASolved - Tue, 2014-06-10 09:37

When working with Oracle GoldenGate, understanding the System Change Number (SCN) is important.  The SCN is an internal number maintained by the database that keeps track of the changes made to the database for recovery purposes.  The SCN is also important when working with Oracle GoldenGate.  In many environments, instantiation of Oracle GoldenGate environments require knowing where to start the replicat from.

To find the current SCN for the database, Oracle has made this pretty easy.  There is a column in the V$DATABASE view called CURRENT_SCN.

select current_scn from v$database;

Now that the current SCN has been found, it can be used in an data pump export parameter file using FLASHBACK_SCN to ensure a consistent copy of the database at that point-in-time.  Once an import is completed on the target side of GoldenGate the replicat can be started using the SCN with the ATCSN or AFTERCSN option.

What does the SCN really mean to an Oracle GoldenGate Admin though?  As outlined above it really is just a point-in-time place holder to key admins in on a place to start the replicat.  At times there maybe need to start the replicat from an adjusted point-in-time.  In order to do this, it is handy to know how to convert the SCN to a Timestamp and back to SCN.  In order to do this, Oracle has provided two packages called SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN.

Using these packages is pretty simple.  The following code blocks demonstrates how to convert from SCN to Timestamp and back.

Convert SCN to Timestamp:

select scn_to_timestamp(8697520) from dual;

Convert Timestamp to SCN:

select timestamp_to_scn('10-JUN-14 AM') from dual;

Knowing how to convert the SCN to Timestamp and back to SCN can be very handy in many different situations and useful when working with Oracle GoldenGate.


twitter: @dbasolved


Filed under: General, Golden Gate, Replication
Categories: DBA Blogs

EMEA OTN Virtual Technology Summit - Hands-On Learning

The Oracle Technology Network (OTN) is excited to invite you to our first Virtual Technology Summit. EMEA – Thursday July 10th / 9am to 1pm BST / 10am – 2pm CET / 12pm to 4pm MSK / GST - Register...

We share our skills to maximize your revenue!
Categories: DBA Blogs

2014 Hadoop Summit Summary

Pythian Group - Mon, 2014-06-09 07:41


Last week I was at the 2014 Hadoop Summit in San Jose, trying to keep abreast of the ever-changing Apache landscape: what projects are up-and-coming, what projects are ready for production, and most importantly which projects can solve problems for our clients. It was also a great chance to hear about real, production deployments – both in the halls and in some of the presentations. And Doug Cutting and Arun Murthy had an excellent panel about the future of the platform, both from a business and technology perspective.


Hadoop Security was an incredibly popular topic this year, reflecting the fact that Hadoop deployments are growing up and fulfilling their stated purpose: to consolidate organizations’ data and make it visible to everyone. “Visible to everyone” is a noble goal, but in reality PII and other sensitive information needs to be guarded, and access needs to be audited and limited. Apache Knox makes it possible to audit all user interactions with the cluster, tying user access to an existing identity management system. Cloudera Sentry provides fine-grained user permissions for Hive, HBase and Search, similar to existing RDBMSes. During the conference Cloudera also announced their acquisition of Gazzang, who make  a platform for key management and data encryption at rest in Hadoop (similar to Intel’s Project Rhino).

Booz Allen Hamilton also put on an excellent presentation about a real client system storing sensitive data on EMR using ephemeral storage – I strongly recommend looking at this as an example of what’s possible now, and also how difficult it is to implement cell or row-level security policies in Hadoop.


YARN is the new “data operating system” responsible for all computations running on your clutster. It handles container placement and resource allocation to allow multiple frameworks like MapReduce, Tez and Spark to co0exist on the same nodes without competing for resources. Applications can also be written to run directly on YARN, opening up the Hadoop cluster to support more general purpose tasks (Yahoo is apparently encoding video on YARN with low latency, although details were scarce), and making it easier for developers to provide distributed, fault-tolerant applications. Early adopters have been using YARN in production for a while, but now every major vendor is including it in their distribution, and features like the High-Availability ResourceManager (or “HARMful YARN”) are available.

Many talks from different sources (Twitter, Yahoo, HortonWorks) focused on different aspects of YARN: new features, production deployment hints, and the general architecture.


I thought with Spark becoming so popular and widely supported – in every major distribution – Spark Streaming would supplant Storm as the leading complex event processing engine. Visiting Hadoop Summit, however, it seems like Storm has plenty of momentum. It’s been ported to YARN to work seamlessly within your cluster, and multiple presentations demonstrated real-world systems running on Storm right now, as well as integrations with other technologies like R and Pig. Spark overall had nearly as many presentations, but these were more technical and theoretical: it might be another year before we see many presentations about Spark and Spark Streaming applications being deployed at scale.


Apache Falcon had two talks this summit, and it’s been incubating since last year. It caught my attention as an open-source project which is aiming to supplant existing proprietary tools. Falcon allows you to declaratively define ETL flows in terms of sources, sinks and transformations, and schedule them on a regular basis. Flows are monitored and idempotent, and late data can be handled according to user-defined rules. Right now the emphasis is on power: an XML config coordinates Hive, Pig, Oozie and distcp, but more user-friendly features like libraries of transformations and a web UI for visualizing flows will bring Falcon closer to the feature set of commerical ETL tools.

SQL on Hadoop

This space has settled down a lot since last year, when Stinger and Impala seemed to invade every track and time slot. Yahoo still put on a number of Hive-on-Tez architecture and performance reviews, and less established projects like Apache Tajo (incubating), BlinkDB, Actian’s Vortex and Facebook’s Presto made appearances. Even though performance has been increasing year over year, SQL-on-Hadoop engines are still wildly variable in their features and performance, and there aren’t any clear winners right now – new entrants still have a chance to make their mark. If you’re curious about choosing a SQL-on-Hadoop engine, check out my presentation this year surveying the landscape.

More to Watch

There were so many great presentations, it was hard to choose for every time slot. Once the videos are released I also recommend watching:

  • Ted Dunning’s stunningly simple anomaly detection
  • Jagane Sundar (of WanDisco) explaining Paxos
  • Koji Noguchi (of Yahoo) with tips about stabilising your Hadoop clusters

Were you at Hadoop Summit? What were your favourite presentations and what trends did you notice?

Categories: DBA Blogs