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

This Log Buffer Edition covers some blog posts of Oracle, SQL Server and MySQL from this past week.


  • Oracle Utilities Application Framework V4. includes a new help engine and changes to the organization of help.
  • Very simple oracle package for HTTPS and HTTP.
  • Oracle ZFS Storage Appliance surpasses $1B in Revenue (Oct 6th).
  • Tim spotted a problem with the PDB Logging Clause.
  • How to Pass Arguments to OS Shell Script (more...)

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

This Log Buffer Edition finds and publishes blog posts from Oracle, SQL Server and MySQL.


  • SCRIPT execution errors when creating a DBaaS instance with local and cloud backups.
  • Designing Surrogate vs Natural Keys with Oracle SQL Developer.
  • EBS General Ledger – Accounting Hub Reporting Cloud Service.
  • Oracle Database Standard Edition 2 is available.
  • Disable “Build After Save at JDeveloper” 12c.

SQL Server:

  • Learn where to get the latest installation and learning resources for the (more...)

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

This Log Buffer Edition collects and then showers around some of the information-rich blog posts from Oracle, SQL Server and MySQL.


  • Generic Java Server for Static content and Directory Listing using API mode or command line mode.
  • OEM agents on each host upload data to your management servers every few minutes.
  • exitcommit … or your career down the drain.
  • Encryption is the Easy Part; Managing those Keys is Difficult.
  • Managing the OBIEE BI Server (more...)

ORA-01917: user or role ‘PDB_DBA’ does not exist

I manually created a container database (CDB) in my Linux 6.4 based virutal machine. After creating it, I tried to create a pluggable database but got following error:

SQL> select instance_name,status from v$instance;

---------------- ------------
cdbtest          OPEN

1 row selected.

SQL> show con_name


SQL> select * from cdb_pdbs;

      DBID    CON_UID GUID                             STATUS    CREATION_SCN
---------- ---------- -------------------------------- --------- ------------

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

This Log Buffer Edition dives deep into the ocean of blogosphere and surfaces with some cool blog posts from Oracle, SQL Server and MySQL.


  • Lets Talk DB Perth
  • The Fundamental Challenge of Computer System Performance
  • Index Advanced Compression: Multi-Column Index
  • Middleware Diagnostics Advisor (MDA) Setup
  • Most people are now aware that in 12c, a VARCHAR2 has been extended from a maximum size of 4000 to 32767 bytes.

SQL Server:

Amazon S3 to Glacier – Cloud ILM

Falling in love with Kate Upton is easy but more easier is to be swept off your feet by information lifecycle management (ILM) in the Amazon Web Services (AWS). Simple, easily-configurable, fast, reliable, cost effective and proven are the words which describe it.

Pythian has been involved with ILM for a long time. With various flavors of databases and systems, Pythian has been overseeing creation, alteration, and flow of data for a long time until (more...)

Favorite Way: Migrating to Exadata

There are lots of considerations to be taken into account when migrating databases to the Exadata. It’s like any other migration. DBAs and other stakeholders of the system have to evaluate what to migrate and what not to, physical and logical settings, versions and many other things.

I’m not delving into migration preparation or strategies and methods here, instead I want to mention which method I like the best to migrate databases to the Exadata. By (more...)

Last Successful login time in 12c

One cool small yet valuable feature in Oracle 12c is the display of 'Last Successful login time'. If authentication is from the OS level, then it isn't shown. A small demo is as follows:

[oracle@targettest ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release Production on Sun Sep 6 18:22:00 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release (more...)

d in Vertica

A quick neat way to list down important and oft-needed information like names of databases, schemas, users, tables, projections etc. We can also use patterns with the 'd' to narrow down the results. Let's see it in action:

Connect with Vertica vsql:

vsql  -U dbadmin -w vtest -h -p 5433 -d vtest

 Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type:  h or ? for help with vsql commands
g (more...)

Creating User Schema Table and Projections in Vertica

Vertica is a an exciting database with some real nifty features. Projections is a ground breaking unique feature of Vertica which dramatically increases performance benefits in terms of querying and space benefits in terms of compression.

Following test commands are impromptu sesssion in which a user is being created, then a schema is created, and that user is authorized on that schema. Then a table is created with a default superprojection and then a projection (more...)

Mongostat ; A Nifty Tool for Mongo DBA

One of the main Mongodb DBA's task is to monitor the usage of Mongodb system and it's load distribution. This could be needed for proactive monitoring, troubleshooting during performance degradation, root cause analysis, or capacity planning.

Mongostat is a nifty tool which comes out of the box with Mongodb which provides wealth of information in a nicely and familiar formatted way. If you have used vmstat, iostat etc on Linux; Mongostat should seem very familiar.


Shift Command in Shell Script in AIX and Linux

Shell in Unix never ceases to surprise. Stumbled upon 'shift 2' command in AIX few hours ago and it's very useful.

'Shift n' command shifts the parameters passed to a shell script by 'n' numbers to the left.

For example:

if you have a shell script which takes 3 parameters like:

./mytest.sh arg1 arg2 arg3

and you use shift 2 in your shell script, then the values of arg1 and arg2 will be lost (more...)

Recover Oracle Undo Tablespace without Backup

Woke up with an issue regarding a Oracle 10.2.0 database on Linux complaining about an Undo file on startup.

sqlplus '/ as sysdba'

SQL*Plus: Release - Production on Fri May 22 20:11:07 2015

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup pfile='init.ora'
ORACLE instance started.

Total System Global Area 2801795072 bytes
Fixed Size                  2075504 bytes
Variable Size            1275069584 bytes

LPAR and Oracle Database

What is LPAR?

LPAR stands for Logical Partitioning and it's a feature of IBM's operating system AIX (Also available in Linux). By abstracting all the physical devices in a system, LPAR creates a virtualized computing environment.

In a server; the processor, memory, and storage are divided into multiple sets. Each set in a server consist of resources like processor, memory and storage. Each set is called as LPAR.

One server can have many LPARs operating (more...)

Showing Interval Partitons Code in DBMS_METADATA.GET_DDL

-- If you want to display the system generated partitions as part of the CREATE TABLE DDL, then set the EXPORT parameter of the dbms_metadata to true.

-- The default behavior of "DBMS_METADATA.GET_DDL" is that it does not show Interval Partitions created by the system for interval partitioned tables and indexes.

-- In the case of Interval Partitioning, New Partitions are created automatically when corresponding row is inserted.  This newly created partition information (more...)

Oracle Querayable Patch Interface

Starting from Oracle 12c, from within the SQL patching information can be obtained. A new package DBMS_QOPATCH offers some really nifty procedures to get the patch information. Some of that information is shared below:

To get patch information from the inventory:

SQL> select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) from dual;

Oracle Home      : /u01/app/oracle/product/12.1.0/db_1
Inventory      : /u01/app/oraInventory

The following is an equivalent of opatch lsinventory command at the OS level:

SQL> select (more...)

Steps to Blackout Agent of Cloud Control 12c

1) Set the environment to the cloud control agent. You can agent name from /etc/oratab file.

myserver: $ . oraenv
ORACLE_SID = [ORCL] ? agent12c

2) Check which targets are being monitored by the cloud control agent on this server:

myserver: $ emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 12c Release 4 
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
[MYSERVER, host]
[MYSERVER:3872, oracle_emd]
[ORCL, oracle_database]

3) Check if there is any existing (more...)

Exadata Vulnerability

This Exadata vulnerability is related to glibc vulnerability. A heap-based buffer overflow was found in glibc's __nss_hostname_digits_dots() function, which is used by the gethostbyname() and gethostbyname2() glibc function calls.

A remote attacker able to make an application call either of these functions could use this flaw to execute arbitrary code with the permissions of the user running the application.

In order to check if your Exadata system suffers from this vulnerability, use:

[root@server ~]# ./ghostest-rhn-cf. (more...)

Indexing Points to Remember

Indexing depends upon the queries in the application.

There is no one-size-fits-all break-even point for indexed versus table scan access. If only a few rows are being accessed, the index will be preferred.

If almost all the rows are being accessed, the full table scan will be preferred. In between these two extremes, your “mileage” will vary.

A concatenated index is more useful if it also supports queries where not all columns are specified. For (more...)

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

This Log Buffer Edition covers blog posts from various bloggers of Oracle, SQL Server and MySQL.


Sync tables: generate MERGE using Unique constraint.
What Hardware and Software Do YOU Want Oracle to Build?
There were a number of new features introduced in Ops Center 12.2.2. One of the shiny ones is an expansion of the backup and recovery capabilities to include Proxy Controllers.
Want to Be a Better Leader? Answer One Question.