SQL Tuning Advisor against sql_id's in AWR

We were in a situation very recently to run SQL Tuning Advisor against a bunch of SQL statements that appeared in the AWR's ADDM recommendations report. The initial effort to launch SQL Tuning Advisor against the SQL_ID couldn't go through as the SQL didn't exist in the shared pool.

Since the sql_id was present in the AWR report, thought of running the advisory against the AWR data, and found a very nice and precisely explained at the following blog:


---- Example how to run SQL Tuning advisor against sql_id in AWR

variable stmt_task VARCHAR2(64);
SQL> exec :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK (begin_snap => 4118, end_snap => 4119, sql_id => 'caxcavmq6zkv9' , scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'sql_tuning_task01' );

SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'sql_tuning_task01');

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'sql_tuning_task01';

set long 50000
set longchunksize 500000
Set pagesize 5000


SQL> exec DBMS_SQLTUNE.drop_tuning_task(task_name =>'sql_tuning_task01');


Happy reading/learning.


Migrating data from on-primeses to cloud

No doubt everyone talks about cloud technologies and certainly could holds the future for various reasons. Oracle doesn't want to left behind in the competition and put the top gear towards cloud offerings. 

This blog explore various Oracle options to migrate on-premises data to cloud. Typically, when a database is created on cloud, the next challenging factor is loading the data to cloud. The good thing about data migration is that the methods and procedures remain the same as you were doing earlier. All data migration constraints still applied, like the following:
  • OS versions of on-premises and cloud machine
  • DB versions
  • Character set
  • DB Size
  • data types
  • Network bandwidth
 The very known and DBA friendly popular Oracle methods are still valid for cloud data migration too :
  • Logical method (conventional data pumps)
  • TTS
  • Cross platform TTS
  • Unplugging/Plugging/Cloning/Remote Cloning of PDBs
  • SQL Developer and SQL Loader
  • Golden Gate
Usually, you take the data backup, choosing the method which suits your requirements,  and upload the backup files to the cloud machine where the database is hosted. Please consider good network and internet speed to expedite the data migration process.

In the example below, data pump (dumpfile) is copied from the on-premises machine to the cloud host machine:

Once the backup files are transferred to the cloud host, you use the typically method to do the data restore.

For more options, read the URL below:


For example using SQL Developer and SQL Loading, read the URLs below:



Golden Gate


Transforming a heap table to a partitioned table - how and whats new in 12c R1 & R2

As part of the daily operational job, one of the typical requests we DBAs get is to convert a regular (heap) table into a partitioned table. This can be achieved either offline or online. This blog will demonstrate some of the pre-12c methods and enhancements in Oracle 12c R1 and R2.

I remembered when I  had such requests in the past, I used the following offline/online methods to achieve the goals, whatever best fit my application needs.

The offline method involves the following action sequence:
  1. Create empty interim partitioned table, indexes and etc
  2. Stop the application services if the non-partitioned table involved in any operations
  3. Migrate the data from the non-partitioned table to partitioned table
  4. Swap the table names
  5. Drop the non-partitioned table
  6. Compile any invalid package/procedure/functions/triggers
  7. Gather table stats
Note: If any integrity references, dependencies exists, the above procedure slightly defers with a couple of additional actions. The downside of this workaround is the service interruption during the course of transformation.

To avoid any service interruption, Oracle provides redefinition feature to perform the action online, without actually impacting the going DML operations on the table. The redefinition option involves the following action sequence:
  1.  Validate if the table can use redefinition feature or not (DBMS_REDEFINITION.CAN_REDEF_TABLE procedure)
  2.  Create interim partition table and all indexes
  3. Start the online redefinition process (DBMS_REDEFINITION.START_REDEF_TABLE procedure)
  4. Copy dependent objects (DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS procedure)
  5. Perform data synchronization (DBMS_REDEFINITION.SYNC_INTERIM_TABLE procedure)
  6. Stop the online redefinition process (DBMS_REDEFINITIONS.FINISH_REDEF_TABLE procedure)
  7. Swap the table names
  8. Compile any invalid package/procedure/functions/triggers
  9. Gather table stats
 However, such sort of action is simplified in Oracle 12c R1 and made easier in R2. The following demonstraes12c R1 and R2 methods.

With EXCHANGE PARTITION feature, the data can be quickly loaded from a non-partitioned table to a partitioned table:

Once you have the partitioned table, use the following example to exchange the data of heap table to partitioned table. In this example, the existing data will be copied to a single partition in the partitioned table.


With 12cR2 ALTER TABLE MODIFY option, a non-partitioned table can be easily transformed into a partitioned table, either offline or online. The example below demonstrate creating daily interval partition:

offline procedure:
(partition p1 values less than (100),
partitionp2 values less than (1000)) ;

Online procedure:
(partition p1 values less than (100),
partitionp2 values less than (1000))ONLINE UPDATE INDEXES (index1, index2 LOCAL) ;



Oracle Private Cloud Appliance (PCA) - when and why?

What has become so critical in today's competitive business is the ability to fulfill the sudden and unpredictable demands that arises. It requires data centers agility, rapid deployments and cloud ready solutions. To succeed in today's modern business, companies must be ready to deploy innovative applications and quickly adopt the changes in the market.

Oracle Private Cloud Appliance (PCA) is an integrated, 'wire once' converged system designed for fast cloud and rapid application deployments at the data centers. PCA is a one stop system for all your applications, where mixed operating systems (Linux, Solaris, RHEL and Windows) workloads can be consolidated into a single machine.

Its has been observed off-late here in GCC specially, more and more organization are moving towards the PCA adoption. Hence, I thought of just writing a blog explaining the prime features and functionalities of PCA.  Once I get some hands-on (which is in the very near future), I would love to write some advance concepts about of PCA and how really organization benefited with PCA.

Here are the key features of PCA:
  • Engineered system comes with fully prebuilt and preconfigured setup
  • Cost effective solution for most of the Oracle and non-Oracle workloads
  • Automated installation and configuration software controller
  • Prebuilt OVM to speed-up the Oracle deployments
  • Single-button DR solutions through OEM
  • Pay for only you use policy
  • Flexibility to Oracle storage or any pre-existing storage
  • PCA certifies all Oracle software that is certified to run on OVM  
  • Deployment of PCA at the data center is very straightforward and simple. The system will be ready within minutes/
  • You can add virtual machines (OVM) either with some basic configuration or use the standard OVM templates
  • No additional software licenses are required on PCA
  • Greatly reduces the time required for deployments. A new deployment can be achieved in hours rather than days in contrast to the traditional infrastructure
  • Easy integration into to existing data center models
  • OVM included with no additional cost

Below picture depicts the typical architecture, what PCA comprises of and supports:

A pair of management servers are installed in a active/standard  for HA. The master management node runs the full set of services, whereas the standby node runs only a subset of services.

The compute nodes (Oracle Servers X series) constitutes the virtual platforms and provides the processing power and memory capacity for the servers they hosted. The entire functionality is orchestrated by the management node (master). 



Stay tuned for more updates on this.