creating a pluggable database from an existing non-CDB Using the DBMS_PDB package

There are three ways of creating a pluggable database from an existing non-CDB:
• Using the DBMS_PDB package to generate metadata and then creating the pluggable database
• Data Pump (using the transportable tablespace feature)
• GoldenGate replication

Note :

When using the DBMS_PDB package to convert a non-CDB to a pluggable database, the non-CDB must be Oracle12c or higher.

Take care of your Non CDB and CDB chraceter set (more...)

Online Partition Move fails with ORA-00932 –

Oracle 12c introduced feature to move table partitions and sub-partitions as online operations.So, this blog is related to this feature and the issue I faced.

The database was upgraded from to and this issue is seen in upgraded databases only.

Lets start!!!

SQL> create table sales_part
    (product char(25),channel_id number,cust_id number,
    amount_sold number, time_id date)
    partition by range (time_id)
    partition sale_jan values less than (to_date('01-02-2015','dd-mm-yyyy')),
    partition  (more...)

SQL Profile and Plan Baseline Notes

Optimizer with Oracle Database 12c
Oracle White Paper – June 2013

What is the difference between SQL Profiles and SQL Plan Baselines?
By Maria Colgan-Oracle on May 08, 2012

Plan Stability using Sql Profiles and SQL Plan Management
Amit Bansal / 20 December, 2011

3 OCT/10

Comments from Kerry Osborne September 9th, 2011 – 18:31
You didn’t mention the two biggest differences (in (more...)

PDB Saved state –

Prior to version, whenever container database was restarted,the pluggable databases within the container database remained in MOUNT state. Startup trigger were written to open the database in READ-WRITE/READ-ONLY mode.Starting from, this can be done with PDB save state feature

SQL> show pdbs

---------- ----------------- ---------- ----------

Lets create a new PDB


Result Cache 2

Following on from my earlier posting of problems with temporary table and the PL/SQL result cache (a combination which the manuals warn you against) here’s another problem – again, to a large degree, self-inflicted.

Imagine you have a complex report involving a large number of financial transactions with a need to include calculations about current exchange rates. Unfortunately the rules about calculating the appropriate exchange rate for any transaction are complex and you find you (more...)

flashback database point in time recovery where is my relocated datafile available in 12c

Its new location

Documentaion says

“A flashback operation does not relocate a moved data file to its previous location. If you move a data file online from one location to another and later flash back the database to a point in time before the move, then the data file remains in the new location, but the contents of the data file are changed to the contents at the time specified in the flashback.”

Let (more...)

Result Cache

Yesterday I thought I’d spend half an hour before breakfast creating a little demonstration of a feature; some time about midnight I felt it was time to stop because I’d found enough bugs to drive me up the wall. Today’s short post is just little warning: be VERY careful what you do with the PL/SQL result cache – if you use the results of database queries in the cache you may end up with inconsistent (more...)

Index Advanced Compression: Multi-Column Index Part I (There There)

I’ve discussed Index Advanced Compression here a number of times previously. It’s the really cool additional capability introduced to the Advanced Compression Option with, that not only makes compressing indexes a much easier exercise but also enables indexes to be compressed more effectively than previously possible. Thought I might look at a multi-column index to highlight just […]

ORDIM – Oracle Multimedia – Invalid after upgrade

ORDIM – The Oracle Multimedia component might be in INVALID state after the database upgrade to 12c. While I was testing the upgrade on test server, received below errors after the completion.

Serial   Phase #:69 Files: 1 Use of uninitialized value $gsRTInclusion in concatenation (.) or string at line 1500.
    Time: 49s

Phases [0-73]         End Time:[2015_08_08 08:06:45]

Grand Total Time: 4848s



Discovery and Monitor Oracle Database Appliance (#ODA) using #EM12C

A few months ago, I heard that Oracle was releasing a plug-in for the Oracle Database Appliance (ODA (Oh Dah). At first I couldn’t find anything on this plug-in, then I was able to find it in the Self-Update for Plug-ins (Extensibility -> Self Update -> Plug-ins).

After finding the plug-in, it needed to be deployed to the Oracle Management Server (OMS). Once deployed, it can be used to monitor the ODA; however, this plug-in (more...)

ZFS Storage monitoring with #EM12C

How do you monitor an ZFS Storage Appliance with Oracle Enterprise Manager 12c? This is a question that has been asked a few time and I needed to solve this issue. You hear a lot about the em agents and that they need to be used to monitor many different targets. Let’s just say, that is not the case when wanting to monitor a ZFS Storage Appliance.

In order to monitor a ZFS Storage Appliance, (more...)

Patching Fun: GI and Database Patches

So, it has been some time since I have patched a database. Patching seemed necessary in a 12c database used for testing, as it was having some crashing issues. As the database and GI home were both un-patched, it seemed worthwhile to just apply the latest set of patches rather than spend (more) time troubleshooting the issue. This is a CDB database on Oracle Linux 6.5, 64 bit. This is (more...)

Switch the Audit Level to Development mode

With JDeveloper 12c it is now possible to run your SOA Suite against the Integrated WLS. However, by default the Audit Level of this server is Production mode which means you do not get to see details when looking at the flow trace of your service.
If you do not change your running mode, the flow trace window will look like this:

We will switch the Audit Level of your local SOA domain to Development (more...)

PRCA-1057 : Failed to retrieve the password file location used by ASM

After the Oracle Restart (Grid Infrastructure) upgrade from 11.2. to , you might encounter the below lines in the ASM alert log file

Tue Jun 23 01:16:01 2015
WARNING: unknown state for ASM password file location resource, Return Value: 3
WARNING: unknown state for ASM password file location resource, Return Value: 3

After the upgrade, below command can get the error

[grid@oracle1 ~]$ srvctl config asm
ASM home: <crs home=''>

Run ADF & SOA on the IntegratedWLS in separated domain

In JDeveloper 12c, it is possible to run SOA Suite on the InternalWLS, which is a great feature. However, once you have done this, your domain will be filled with SOA extensions. Meaning the server start up time will increase drastically.
When you do both ADF & SOA development, this can be annoying when you just want to run your ADF application and the IntegratedWLS is busy starting up the SOA domain. One way to (more...)

Oracle releases new Standard Edition 2 for

Customers have been waiting for a Standard Edition release for version Then, in July, a support engineer leaked that there will be a whole new type of “Edition”, called “Standard Edition 2″ to replace both “Standard Edition” and “Standard Edition One”. The respective MOS Note disappeared shortly after the accidental publication. Now today the release finally became official and can be downloaded via OTN. The “SE2″ is priced exactly as the (more...)


Here’s a simple data set – I’m only interested in three of the columns in the work that follows, but it’s a data set that I use for a number of different models:

execute dbms_random.seed(0)

create table t1
with generator as (
	select	--+ materialize
		rownum id 
	from dual 
	connect by 
		level <= 1e4
	trunc(dbms_random.value(0,1000))	n_1000,
	trunc(dbms_random.value(0,750))		n_750,
	trunc(dbms_random.value(0,600))		n_600,
	trunc(dbms_random.value(0,400))		n_400,
	trunc(dbms_random.value(0,90))		n_90,
	trunc(dbms_random. (more...)

Moving Datafiles Online in Oracle 12c

I wrote about this feature in Hebrew quite some time ago but in the last week I was asked by couple of my customers about the correct way to move datafiles with minimal downtime so I thought it would be nice to show the Oracle 12c solution before talking about the solution I gave them.

For years, moving datafiles was a pain in the DBA behinds. It required some database action, then some OS actions (more...)

Issue with Perl in $ORACLE_HOME during installs

I’ve been doing some Enterprise Manager installs a bit more lately. At the same time, I’ve been working on Data Integration items such as GoldenGate and ODI.  What these products have in common are that they require an Oracle Database for a repository.  Needless to say I’ve been installing a lot of databases in test and production environments.  The one thing that has been consistent is the issue I keep seeing (more...)

12c Upgrade – It can be slow!!!!

This post is for some of you who are planning to upgrade to 12c.I worked on upgrading single instance database from to, so thought to share my experience.

From 12c catupgrade.sql has been deprecated and Oracle has introduced script for upgrade. With script Oracle tries to perform as much work as possible in parallel, therefore minimizing downtime for upgrade.