Skip navigation.

DBA Blogs

Building Simple Java EE REST Service Using Oracle JDeveloper 12c

v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} REST (Representational State Transfer) – an...

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

Empty Leaf Blocks After Rollback Part II (Editions of You)

Richard Foote - Wed, 2015-06-24 01:35
In my last post, I discussed how both 1/2 empty and totally empty leaf blocks can be generated by rolling back a bulk update operation. An important point I made within the comments of the previous post is that almost the exact scenario would have taken place had the transaction committed rather than rolled back. A […]
Categories: DBA Blogs

RMAN - 3 : The DB_UNIQUE_NAME in Backups to the FRA

Hemant K Chitale - Tue, 2015-06-23 03:14
When you run RMAN Backups to the FRA without using the FORMAT clause, Oracle automatically generates filenames for the BackupPieces.  The folder name is derived from the system date.  But what is the parent folder for backups ?  Is it simply the DB_RECOVERY_FILE_DEST ?  Actuallly, the DB_UNIQUE_NAME comes into play as well.

For example :

[oracle@localhost ~]$ rman target /

Recovery Manager: Release - Production on Tue Jun 23 16:57:19 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> list backup of datafile 1;

using target database control file instead of recovery catalog

List of Backup Sets

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 831.23M DISK 00:03:32 07-JUN-15
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20150607T165914
Piece Name: /NEW_FS/oracle/FRA/ORCL/backupset/2015_06_07/o1_mf_nnndf_TAG20150607T165914_bq81z2y6_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14068320 07-JUN-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 366.89M DISK 00:01:56 07-JUN-15
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20150607T170754
Piece Name: /NEW_FS/oracle/FRA/ORCL/backupset/2015_06_07/o1_mf_nnndf_TAG20150607T170754_bq82hc5f_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14068721 07-JUN-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf


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

SQL*Plus: Release Production on Tue Jun 23 16:58:34 2015

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

Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_recovery_file_dest

------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /NEW_FS/oracle/FRA
db_recovery_file_dest_size big integer 8G

We can see that the DB_RECOVERY_FILE_DEST is defined as "/NEW_FS/oracle/FRA". However, the backups go into a "backupset" folder under "/NEW_FS/oracle/FRA/ORCL/". The "ORCL" is part of the path to the folder holding the backups. How is this "ORCL" derived ?

SQL> show parameter db_name

------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> show parameter db_unique_name

------------------------------------ ----------- ------------------------------
db_unique_name string orcl

By default, the DB_UNIQUE_NAME is the same as DB_NAME. Let's see what happens after I change the DB_UNIQUE_NAME.

SQL> !ls -l /NEW_FS/oracle/FRA/
total 4
drwxrwx--- 5 oracle oracle 4096 Jun 7 17:10 ORCL

SQL> alter system set db_unique_name='HEMANTDB' scope=SPFILE;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 385878712 bytes
Database Buffers 62914560 bytes
Redo Buffers 6008832 bytes
Database mounted.
Database opened.
SQL> !ls -l /NEW_FS/oracle/FRA/
total 4
drwxrwx--- 5 oracle oracle 4096 Jun 7 17:10 ORCL


After resetting the DB_UNIQUE_NAME, Oracle doesn't immediately create the folder for the new DB_UNIQUE_NAME until and unless I run an RMAN Backup.

RMAN> exit

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-03135: connection lost contact
Process ID: 3344
Session ID: 67 Serial number: 13

Recovery Manager complete.
[oracle@localhost ~]$ rman target /

Recovery Manager: Release - Production on Tue Jun 23 17:07:14 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> backup datafile 1;

Starting backup at 23-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=38 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 23-JUN-15
channel ORA_DISK_1: finished piece 1 at 23-JUN-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_06_23/o1_mf_nnndf_TAG20150623T170721_brl8g9od_.bkp tag=TAG20150623T170721 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 23-JUN-15

Starting Control File and SPFILE Autobackup at 23-JUN-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_06_23/o1_mf_s_883156126_brl8k0w4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 23-JUN-15


SQL> !ls -l /NEW_FS/oracle/FRA/
total 8
drwxrwx--- 3 oracle oracle 4096 Jun 23 17:07 HEMANTDB
drwxrwx--- 5 oracle oracle 4096 Jun 7 17:10 ORCL

SQL> show parameter db_name

------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> show parameter db_unique_name

------------------------------------ ----------- ------------------------------
db_unique_name string HEMANTDB

Notice how Oracle created the "HEMANTDB" folder under the designated DB_RECOVERY_FILE_DEST. It then created the "backupset" and "autobackup" folders also as subfolders under this.  BackupSet BackupPieces and Controlfile Autobackups are now going to the new path.  The backups are go to folders under {DB_RECOVERY_FILE_DEST}/{DB_UNIQUE_NAME}

Categories: DBA Blogs

Empty Leaf Blocks After Rollback Part I (Empty Spaces)

Richard Foote - Tue, 2015-06-23 00:09
There’s been an interesting recent discussion on the OTN Database forum regarding “Index blank blocks after large update that was rolled back“. Setting aside the odd scenario of updating a column that previously had 20 million distinct values to the same value on a 2 billion row table, the key questions raised are why the blank index leaf blocks […]
Categories: DBA Blogs

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

Pythian Group - Mon, 2015-06-22 11:45

The Log Buffer Edition once again is sparkling with some gems, hand-picked from Oracle, SQL Server and MySQL.


  • Oracle GoldenGate  is now certified with Unity 14.10.  With this certification, customers can use Oracle GoldenGate to deliver data to Teradata Unity which can then automate the distribution of data to multiple Teradata databases.
  • How do I change DNS servers on Exadata storage servers.
  • Flushing Shared Pool Does Not Slow Its Growth.
  • Code completion is the key feature you need when adding support for your own JavaScript framework to NetBeans IDE.
  • Replicating Hive Data Into Oracle BI Cloud Service for Visual Analyzer using BICS Data Sync.

SQL Server:

  • Trigger an Email of an SSRS Report from an SSIS Package.
  • Script All Server Level Objects to Recreate SQL Server.
  • A Syntax Mystery in a Previously Working Procedure.
  • Using R to Explore Data by Analysis – for SQL Professionals.
  • Converting Rows to Columns (PIVOT) and Columns to Rows (UNPIVOT) in SQL Server.


  • Some applications, particularly those written with a single-node database server in mind, attempt to immediately read a value they have just inserted into the database, without making those operations part of a single transaction. A read/write splitting proxy or a connection pool combined with a load-balancer can direct each operation to a different database node.
  • Q&A: High availability when using MySQL in the cloud.
  • MariaDB 10.0.20 now available.
  • Removal and Deprecation in MySQL 5.7.
  • Getting EXPLAIN information from already running queries in MySQL 5.7.

Learn more about Pythian’s expertise in Oracle , SQL ServerMySQL, as well as the author Fahd Mirza.

Categories: DBA Blogs

Index Tree Dumps in Oracle 12c Database (New Age)

Richard Foote - Sun, 2015-06-21 23:56
I’ve previously discussed Index Tree Dumps but I’ve recently found a nice little improvement that’s been introduced in Oracle Database 12c. Let’s begin by creating a little table and index: To generate an Index Tree Dump, we first need the OBJECT_ID of the index: And then use it to generate the Index Tree Dump: Previously, an […]
Categories: DBA Blogs

Creating Oracle Service Bus 12c Proxy Service to Decouple JCA Database Adapter Business Services

In an earlier blog I showed how to service enable a database on OSB 12c . The business service created as we saw is strongly coupled to the database. A change on the database (table name, data type...

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

Flushing Shared Pool Does Not Slow Its Growth

Bobby Durrett's DBA Blog - Thu, 2015-06-18 17:14

I’m still working on resolving the issues caused by bug 13914613.

Oracle support recommended that we apply a parameter change to resolve the issue but that change requires us to bounce the database  and I was looking for a resolution that does not need a bounce.  The bug caused very bad shared pool latch waits when the automatic memory management feature of our database expanded the shared pool.  Oracle support recommending setting _enable_shared_pool_durations=false and I verified that changing this parameter requires a bounce.  It is a big hassle to bounce this database because of the application so I thought that I might try flushing the shared pool on a regular basis so the automatic memory management would not need to keep increasing the size of the shared pool.  The shared pool was growing in size because we have a lot of SQL statements without bind variables.  So, I did a test and in my test flushing the shared pool did not slow the growth of the shared pool.

Here is a zip of the scripts I used for this test and their outputs: zip

I set the shared pool to a small value so it was more likely to grow and I created a script to run many different sql statements that don’t use bind variables:

spool runselects.sql

select 'select * from dual where dummy=''s'
from dba_objects;

spool off


So, the queries looked like this:

select * from dual where dummy='s0818111';
select * from dual where dummy='s0818112';
select * from dual where dummy='s0818113';
select * from dual where dummy='s0818114';
select * from dual where dummy='s0818115';
select * from dual where dummy='s0818116';
select * from dual where dummy='s0818117';

I ran these for an hour and tested three different configurations.  The first two did not use the _enable_shared_pool_durations=false setting and the last did.  The first test was a baseline that showed the growth of the shared pool without flushing the shared pool.  The second test including a flush of the shared pool every minute.  The last run included the parameter change and no flush of the shared pool.  I queried V$SGA_RESIZE_OPS after each test to see how many times the shared pool grew.  Here is the query:

to_char(start_time,'dd-mon hh24:mi:ss') Started, 
to_char(end_time,'dd-mon hh24:mi:ss') Ended 
where component='shared pool'
order by start_time,end_time;

Here are the results.

Baseline – no flush, no parameter change:

--------- ----------- --------------- ---------------
GROW      150,994,944 18-jun 05:03:54 18-jun 05:03:54
GROW      134,217,728 18-jun 05:03:54 18-jun 05:03:54
STATIC    117,440,512 18-jun 05:03:54 18-jun 05:03:54
GROW      167,772,160 18-jun 05:04:36 18-jun 05:04:36
GROW      184,549,376 18-jun 05:47:38 18-jun 05:47:38

Flush every minute, no parameter change:

--------- ----------- --------------- ---------------
GROW      134,217,728 18-jun 06:09:15 18-jun 06:09:15
GROW      150,994,944 18-jun 06:09:15 18-jun 06:09:15
STATIC    117,440,512 18-jun 06:09:15 18-jun 06:09:15
GROW      167,772,160 18-jun 06:09:59 18-jun 06:09:59
GROW      184,549,376 18-jun 06:22:26 18-jun 06:22:26
GROW      201,326,592 18-jun 06:42:29 18-jun 06:42:29
GROW      218,103,808 18-jun 06:47:29 18-jun 06:47:29

Parameter change, no flush:

--------- ------------ --------------- ---------------
STATIC     117,440,512 18-jun 07:16:09 18-jun 07:16:09
GROW       134,217,728 18-jun 07:16:18 18-jun 07:16:18

So, at least in this test – which I have run only twice – flushing the shared pool if anything makes the growth of the shared pool worse.  But, changing the parameter seems to lock it in.

– Bobby

Categories: DBA Blogs

Partner Webcast – Next Generation IoT real time applications with Oracle Stream Explorer

The Internet of Things (IoT)- or rather „The Internet of Everything“ is one of the key drivers transforming the world from analogue to digital. Cheap sensors, cheap bandwidth, cheap processing,...

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

Create #em12c users fast and easy!

DBASolved - Thu, 2015-06-18 11:57

Over the last few months, I’ve been working a project where I’ve started to dive into EM CLI and the value that EM CLI brings to cutting down on doing things like creating Enterprise Manager users. Hence the reason for this post.

Note: If you haven’t looked into EM CLI yet, I encourage you to do so. A good starting point is here. Plus there is a whole book written on the topic by some friends and guru’s of mine, here.

Creating users in Enterprise Manager 12c is pretty simple as it is. Simply go to Setup -> Security -> Administrators. When you get this screen, then click on either the Create or Create Like buttons.

After clicking Create or Create Like, Enterprise Manger takes you to a five (5) step wizard for creating a user. This wizard allows you to provide details about the user, assign roles, assign target privileges, assign resource privileges and then review what you have done.

Depending on how many users you have to create, this wizard is either an great way of creating user or a slow way for creating users. Using EM CLI, users can be created from the command line very quickly and easily and no need to use the GUI wizard either.. :)

The syntax to create a user from the command line is as follows:

emcli create_user

The beautiful part of EM CLI is that is can be used with any scripting language. Since I like to use PERL, I decided to write a simple script that can be used to create a user from the command line using EM CLI.

#!/usr/bin/perl -w
use strict;
use warnings;

my $oem_home_bin = “$OMS_HOME/bin";
my ($username, $passwd, $email) = @ARGV;
my $pwdchange = ‘false';

if (not defined $username or not defined $passwd or not defined $email)
    print "\nUsage: perl ./ username password email_address\n\n";    

system($oem_home_bin.'/emcli login -username=sysman);
system($oem_home_bin.'/emcli sync');
my $cmd = 'emcli create_user -name='.$username.' -password='.$passwd.' -email='.$email.' -prevent_change_password='.$pwdchange;
#print $cmd."\n";
system($oem_home_bin.'/emcli logout');

Now using this bit of code, I’m able to create users very rapidly using EM CLI with a command like this:

perl ./ <username> <password for user> <email address>

Well, I hope this helps other look at and start using EM CLI when managing their EM environments.


Filed under: EMCLI, OEM
Categories: DBA Blogs

Oracle Cloud Platform Launch Event on Monday June 22 - Integrate, Accelerate and Lead with the Oracle Cloud Platform

Watch live as Larry Ellison, Executive Chairman of the Board and Chief Technology Officer, Oracle, unveils new Oracle Cloud Platform services. Discover how you can drive innovation with the leading...

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

Indexing and Transparent Data Encryption Part III (You Can’t Do That)

Richard Foote - Tue, 2015-06-16 00:28
In Part II of this series, we looked at how we can create a B-Tree index on a encrypted column, providing we do not apply salt during encryption. However, this is not the only restriction with regard to indexing an encrypted column using column-based encryption. If we attempt to create an index that is not a […]
Categories: DBA Blogs

Hot off the press : Latest Release of Oracle Enterprise Manager 12c (R4)

Pankaj Chandiramani - Tue, 2014-06-03 06:53

Read more here about the PRESS RELEASE:  Oracle Delivers Latest Release of Oracle Enterprise Manager 12c

Service Catalog for Database and Middleware as a Service; Enhanced
Database and Middleware Management Help Drive Enterprise-Scale Private
Cloud Adoption

In coming weeks  , i will be covering latest topics like :

  1. DbaaS Service Catalog incorporating High Availability and Disaster Recovery

  2. New Rapid Start kit

  3. Other new Features 

Stay Tuned !

Categories: DBA Blogs

Interesting info-graphics on Data-center / DB-Manageability

Pankaj Chandiramani - Mon, 2014-05-19 04:21

 Interesting info-graphics on Data-center / DB-Manageability

Categories: DBA Blogs

Tackling the challange of Provisoning Databases in an agile datacenter

Pankaj Chandiramani - Wed, 2014-05-14 01:03

One of the key task that a DBA performs repeatedly is Provisioning of Databases which also happens to one of the top 10 Database Challenges as per IOUG Survey .

Most of the challenge comes in form of either Lack of Standardization or it being a Long and Error Prone Process . This is where Enterprise Manager 12c can help by making this a standardized process using profiles and lock-downs ; plus have a role and access separation where lead dba can lock certain properties of database (like character-set or Oracle Home location  or SGA etc) and junior DBA's can't change those during provisioning .Below image describes the solution :

In Short :

  • Its Fast

  • Its Easy 

  • And you have complete control over the lifecycle of your dev and production resources.

I actually wanted to show step by step details on how to provision a 11204 RAC using Provisioning feature of DBLM  , but today i saw a great post by MaaZ Anjum that does the same , so i am going to refer you to his blog here :

Patch and Provision in EM12c: #5 Provision a Real Application Cluster Database

Other Resources : 

Official Doc :

Screen Watch :

Others :

Categories: DBA Blogs

Nationwide Deploys Database Applications 600% Faster

Pankaj Chandiramani - Mon, 2014-04-28 03:37

Nationwide Deploys Database Applications 600% Faster

Heath Carfrey of Nationwide, a leading global insurance and
financial services organization, discusses how Nationwide saves time and
effort in database provisioning with Oracle Enterprise Manager

Key-points :

  1. Provisioning Databases using Profiles  (aka Gold Images)

  2. Automated Patching

  3.  Config/Compliance tracking

Categories: DBA Blogs

EMCLI setup

Pankaj Chandiramani - Mon, 2014-04-28 02:15

A quick note on how to install EMCLI which is used for various CLI operations from EM . I was looking to test some Database provisioning automation via EMCLI and thus was looking to setup the same . 

To set up EMCLI on the host, follow these steps:
1.    Download the emcliadvancedkit.jar from the OMS using URL https://<omshost>:<omsport>/em/public_lib_download/emcli/kit/emcliadvancedkit.jar
2.    Set your JAVA_HOME environment variable and ensure that it is part of your PATH. You must be running Java 1.6.0_43 or greater. For example:
o    setenv JAVA_HOME /usr/local/packages/j2sdk
o    setenv PATH $JAVA_HOME/bin:$PATH
3.    You can install the EMCLI with scripting option in any directory either on the same machine on which the OMS is running or on any machine on your network (download the emcliadvancedkit.jar to that machine)
java -jar emcliadvancedkit.jar client -install_dir=<emcli client dir>
4.    Run emcli help sync from the EMCLI Home (the directory where you have installed emcli) for instructions on how to use the "sync" verb to configure the client for a particular OMS.
5.    Navigate to the Setup menu then the Command Line Interface. See the Enterprise Manager Command Line Tools Download page for details on setting EMCLI.

Categories: DBA Blogs

Webcast: Database Cloning in Minutes using Oracle Enterprise Manager 12c Database as a Service Snap Clone

Pankaj Chandiramani - Thu, 2014-04-17 04:02

Since the demands
from the business for IT services is non-stop, creating copies of production
databases in order to develop, test and deploy new applications can be
labor intensive and time consuming. Users may also need to preserve private
copies of the database, so that they can go back to a point prior to when
a change was made in order to diagnose potential issues. Using Snap Clone,
users can create multiple snapshots of the database and “time
” across these snapshots to access data from any point
in time.

Join us for an in-depth
technical webcast and learn how Oracle Cloud Management Pack for Oracle
Database's capability called Snap Clone, can fundamentally improve the
efficiency and agility of administrators and QA Engineers while saving
CAPEX on storage. Benefits include:

  • Agile provisioning
    (~ 2 minutes to provision a 1 TB database)

  • Over 90% storage

  • Reduced administrative
    overhead from integrated lifecycle management


April 24 — 10:00 a.m. PT | 1:00 p.m. ET

May 8 — 7:00 a.m. PT | 10:00 a.m. ET | 4:00 p.m. CET

May 22 — 10:00 a.m. PT | 1:00 p.m. ET

Categories: DBA Blogs

Wed, 1969-12-31 18:00