Syed Jaffar

Subscribe to Syed Jaffar feed
Whatever topic has been discussed on this blog is my own finding and views, not necessary match with others. I strongly recommend you to do a test before you implement the piece of advice given at my blog.The Human Fly
Updated: 13 hours 39 min ago

SQL Tuning Advisor against sql_id's in AWR

Tue, 2017-05-23 04:23
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

Mon, 2017-05-15 14:37
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

Sun, 2017-05-14 04:13
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?

Sun, 2017-04-30 13:44

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.


How to stabilize, improve and tune Oracle EBS Payroll and Retropay process

Sat, 2017-04-15 05:45
Visited few customers off late to review the performance issues pertaining to their Oracle EBS Payroll and Retro-pay processes. Not sure if many are aware of the tools Oracle has to analyze and improve any Oracle EBS modules, including Payroll and Retro-pay. To get proactive with Oracle EBS, refer the following note:

  • Get Proactive with Oracle E-Business Suite - Product Support Analyzer Index (Doc ID 1545562.1)

I must say, after running through the analyzers (Retro and Payroll), and implementing the suggestions, significant performance is achieved without making any change to the queries. I would strongly recommend to run the analyzers on different modules on Oracle EBS to get proactive and achieve performance improvements and stability. Below is the Payroll analyzer report screen shot, explains the findings and recommendations:


Few good MOS notes to stabilize, improve and tune the Retro-pay and Payroll processes on Oracle EBS environment:

  • EBS Payroll RetroPay Analyzer (Doc ID 1512437.1)
  • EBS Database Parameter Settings Analyzer (Doc ID 1953468.1)
  • EBS Payroll Analyzer (Doc ID 1631780.1)
  • EBS HRMS Payroll - RetroPay Advisor (Doc ID 1482827.1)
  • RetroPay Analyzer Tool FAQ (Doc ID 1568129.1)

12cR1 RMAN Restore/Duplicate from ASM to Non ASM takes a longer time waiting for the ASMB process

Tue, 2017-03-14 08:51
Yet another exciting journey with Oracle bugs and challenges. Here is the story for you.

One of our recent successful migrations was a single instance Oracle EBS 12cR1 database to Oracle Super Cluster M7 as a RAC database with 2 instances on the same DB version ( Subsequently, the customer wants to run through EBS cloning and set up an Oracle active data guard configuration.

The target systems are not Super Cluster. The requirement to clone and set up an Oracle data guard is to configure as a single-instance database onto a filesystem (non-ASM). After initiating the cloning procedure using the DUPLICATE TARGET DATABASE method trough RMAN, we noticed that RMAN is taking significant time to restore(ship) the data files to the remote server. Also, the following warning messages were appeared in the alert.log:

ASMB started with pid=63, OS id=18085
WARNING: failed to start ASMB (connection failed) state=0x1 sid=''
WARNING: ASMB exiting with error
Starting background process ASMB
Sat Mar 11 13:53:24 2017
ASMB started with pid=63, OS id=18087
WARNING: failed to start ASMB (connection failed) state=0x1 sid=''
WARNING: ASMB exiting with error
Starting background process ASMB
Sat Mar 11 13:53:27 2017
ASMB started with pid=63, OS id=18089
WARNING: failed to start ASMB (connection failed) state=0x1 sid=''
WARNING: ASMB exiting with error
Starting background process ASMB

The situation raised couple of concerns in our minds:
  1. Why is the restore is too slow from RMAN? (while there is no Network latency and DB files are not so big sized)
  2. Why Oracle is looking for an ASM instance on a non-Cluster home? (not even a standard Grid home)
After some initial investigation, we come across following MOS Docs:
  • '12c RMAN Operations from ASM To Non-ASM Slow (Doc ID 2081537.1)'. 
  • WARNING: failed to start ASMB after RAC Database on ASM converted to Single Instance Non-ASM Database (Doc ID 2138520.1)
According to the above MOS Docs, this is an expected behavior  due to an  Unpublished BUG 19503821:  RMAN CATALOG EXTREMELY SLOW WHEN MIGRATING DATABASE FROM ASM TO FILE SYSTEM

You need to apply a patch 19503821 to overcome from the bug.

If you similar demand, make sure you apply the patch in your environmet before you proceed with the restore/duplicate procedure.

-- Excerpt from the above notes:

Oracle Database - Enterprise Edition - Version to [Release 12.1]
 Information in this document applies to any platform.

1*. RAC Database with ASM has been converted or restored to Standalone Single Instance Non-ASM Database.
2*. From the RDBMS alert.log, it is showing continuous following messages.

3*.RMAN Restore/Duplicate from ASM to Non ASM in 12.1 take a longer time waiting for the ASMB process.
4*.Any RMAN command at the mount state which involves Non ASM location can take more time.


Apply the patch 19503821, if not available for your version/OS then please log a SR with the support to get the patch for your version.

12cR2 new features for Developers and DBAs - Here is my pick (Part 2)

Sat, 2017-03-04 10:29
In Part 1, I have outlined a few (my pick) 12cR2 new features useful for Developers and DBAs. In the part 2, I am going to discuss a few more new features.
Read/Write and Read-Only Instances
Read-write and read-only database instances of the same primary database can coexist in an Oracle Flex Cluster.
Advanced Index Compression
Prior to this release, the only form of advanced index compression was low compression. Now you can also specify high compression. High compression provides even more space savings than low compression.
PDBs Enhancements
  • I/O Rate Limits for PDBs
  • Different character sets of PDBs in a CDB
  • PDB refresh to periodically propagate changes from a source PDB to its cloned copy
  • CONTAINERS hint : When a CONTAINERS ()query is submitted, recursive SQL statements are generated and executed in each PDB. Hints can be passed to these recursive SQL statements by using the CONTAINERS statement-level hint. 
  • Cloning PDB no longer to be in R/W mode : Cloning of a pluggable database (PDB) resolves the issue of setting the source system to read-only mode before creating a full or snapshot clone of a PDB.
  • Near Zero Downtime PDB Relocation:This new feature significantly reduces downtime by leveraging the clone functionality to relocate a pluggable database (PDB) from one multitenant container database (CDB) to another CDB. The source PDB is still open and fully functional while the actual cloning operation is taking place.
  • Proxy PDB: A proxy pluggable database (PDB) provides fully functional access to another PDB in a remote multitenant container database (CDB). This feature enables you to build location-transparent applications that can aggregate data from multiple sources that are in the same data center or distributed across data centers.
Oracle Data Pump Parallel Export of Metadata: The PARALLEL parameter for Oracle Data Pump, which previously applied only to data, is extended to include metadata export operations. The performance of Oracle Data Pump export jobs is improved by enabling the use of multiple processes working in parallel to export metadata.
Renaming Data Files During Import
Oracle RAC :
  • Server Weight-Based Node Eviction :Server weight-based node eviction acts as a tie-breaker mechanism in situations where Oracle Clusterware needs to evict a particular node or a group of nodes from a cluster, in which all nodes represent an equal choice for eviction. In such cases, the server weight-based node eviction mechanism helps to identify the node or the group of nodes to be evicted based on additional information about the load on those servers. Two principle mechanisms, a system inherent automatic mechanism and a user input-based mechanism exist to provide respective guidance.
  • Load-Aware Resource Placement : Load-aware resource placement prevents overloading a server with more applications than the server is capable of running. The metrics used to determine whether an application can be started on a given server, either as part of the startup or as a result of a failover, are based on the anticipated resource consumption of the application as well as the capacity of the server in terms of CPU and memory.
Enhanced Rapid Home Provisioning and Patch Management
TDE Tablespace Live Conversion: You can now encrypt, decrypt, and rekey existing tablespaces with Transparent Data Encryption (TDE) tablespace live conversion. A TDE tablespace can be easily deployed, performing the initial encryption that migrates to an encrypted tablespace with zero downtime. This feature also enables automated deep rotation of data encryption keys used by TDE tablespace encryption in the background with zero downtime.
Fully Encrypted Database: Transparent Data Encryption (TDE) tablespace encryption is applied to database internals including SYSTEM, SYSAUX, and UNDO.
TDE Tablespace Offline Conversion: This release introduces new SQL commands to encrypt tablespace files in place with no storage overhead. You can do this on multiple instances across multiple cores. Using this feature requires downtime, because you must take the tablespace temporarily offline. With Data Guard configurations, you can either encrypt the physical standby first and switchover, or encrypt the primary database, one tablespace at a time.

12cR2 new features for Developers and DBAs - Here is my pick (Part 1)

Thu, 2017-03-02 14:07
Since the announcement of 12cR2 on-premises availability, the Oracle community become energetic and busy tweeting/blogging the new features, demonstrating installation & upgrades. Hence, I have decided to pick my favorite list of 12cR2 new features for Developers and & DBAs. Here is the high-level summary, until I write a detailed post for each feature (excerpt from Oracle 12cR2 new features document).
Command history for SQL * Plus: Pre 12cR2, this could be achieved through a workaround, now, the history command would do the magic for you.
Materialized Views: Real-Time Materialized Views: Materialized views can be used for query rewrite even if they are not fully synchronized with the base tables and are considered stale. Using materialized view logs for delta computation together with the stale materialized view, the database can compute the query and return correct results in real time.
For materialized views that can be used for query rewrite all of the time, with the accurate result being computed in real time, the result is optimized and fast query processing for best performance. This alleviates the stringent requirement of always having to have fresh materialized views for the best performance.
Materialized Views: Statement-Level Refresh: In addition to ON COMMIT and ON DEMAND refresh, the materialized join views can be refreshed when a DML operation takes place, without the need to commit such a transaction. This is predominantly relevant for star schema deployments.
The new ON STATEMENT refresh capability provides more flexibility to the application developers to take advantage of the materialized view rewrite, especially for complex transactions involving multiple DML statements. It offers built-in refresh capabilities that can replace customer-written trigger-based solutions, simplifying an application while offering higher performance.
Oracle Data Guard Database Compare: This new tool compares data blocks stored in an Oracle Data Guard primary database and its physical standby databases. Use this tool to find disk errors (such as lost write) that cannot be detected by other tools like the DBVERIFY utility.
Subset Standby: A subset standby enables users of Oracle Multitenant to designate a subset of the pluggable databases (PDBs) in a multitenant container database (CDB) for replication to a standby database. 
Automatically Synchronize Password Files in Oracle Data Guard Configurations: This feature automatically synchronizes password files across Oracle Data Guard configurations. When the passwords of SYS, SYSDG, and so on, are changed, the password file at the primary database is updated and then the changes are propagated to all standby databases in the configuration.
Preserving Application Connections to An Active Data Guard Standby During Role Changes: Currently, when a role change occurs and an Active Data Guard standby becomes the primary, all read-only user connections are disconnected and must reconnect, losing their state information. This feature enables a role change to occur without disconnecting the read-only user connections. Instead, the read-only user connections experience a pause while the state of the standby database is changed to primary. Read-only user connections that use a service designed to run in both the primary and physical standby roles are maintained. Users connected through a physical standby only role continue to be disconnected.
Oracle Data Guard for Data Warehouses: The use of NOLOGGING for direct loads on a primary database has always been difficult to correct on an associated standby database. On a physical standby database the data blocks were marked unrecoverable and any SQL operation that tried to read them would return an error. Or, for a logical standby database, SQL apply would stop upon encountering the invalidation redo.
Rolling Back Redefinition: There is a new ROLLBACK parameter for the FINISH_REDEF_TABLE procedure that tracks DML on a newly redefined table so that changes can be easily synchronized with the original table using the SYNC_INTERIM_TABLE procedure.
The new V$ONLINE_REDEF view displays runtime information related to the current redefinition procedure being executed based on a redefinition session identifier.
Online Conversion of a Nonpartitioned Table to a Partitioned Table: Nonpartitioned tables can be converted to partitioned tables online. Indexes are maintained as part of this operation and can be partitioned as well. The conversion has no impact on the ongoing DML operations.
Online SPLIT Partition and Subpartition: The partition maintenance operations SPLIT PARTITION and SPLIT SUBPARTITION can now be executed as online operations for heap organized tables, allowing the concurrent DML operations with the ongoing partition maintenance operation.
Online Table Move: Nonpartitioned tables can be moved as an online operation without blocking any concurrent DML operations. A table move operation now also supports automatic index maintenance as part of the move.
Oracle Database Sharding: Sharding with Oracle Database 12c Release 2 (12.2) is an architecture for suitable online transaction processing (OLTP) applications where data is horizontally partitioned across multiple discrete Oracle databases, called shards, which share no hardware or software. The collection of shards is presented to an application as a single logical Oracle database.
Stay tuned for Part 2..

Oracle Enterprise Manager 13c R2 configuration on Win 2008 R2 server stops at 78%

Sun, 2017-01-08 05:13
Oracle Enterprise Manager (OEM) 13cR2 configuration on a Win 2008 R2 was stopping at 78% completion  while performing the BI Publisher Configuration. Apparently, the problem exists pre 13cR2 as well.

All OMS components like (WebTier, Oracle Management Server, JVMD Engine) will stop/start during the BI Publisher configuration operations. Unfortunately, the windows service for OMS was taking too long to complete the start operation of Webtier (HTTP) and installation at 78% stopped (didn't move forward). Initially, I have started looking at WebTier startup issues, in the process, tried to disable firewall, also excluded the installation directory from the anti virus on the Window server, and the result remains the same.

Cleaned-up the previous installation and start-over the OEM 13cR2 installation on the server, but this time I didn't check the BI Publisher configuration option as I wanted to exclude the BI Publisher configuration to ensure I complete the OEM installation without any issues. Despite the fact I didn't check the option, OEM started configuring BI and stopped exactly at 78%, the issue remains.

The error messages in the sysman and other OMS logs didn't provide any useful hints, in fact, it was misleading and took me in the wrong direction.

Came across of a MOS ID( 1943820.1), and after applying the solution, OEM configuration completed successfully.

Here is the excerpt from the MOS ID :

On some occasions, httpd.exe will fail to start, If you are missing or have a damaged Microsoft Visual C++ Redistributable 64-bit package.
It may report the error above, or give <SEVERE> <OHS-0> <Failed to start the server ohs_1>, with 0 bytes of details in the OHS log.
Install the Microsoft Visual C++ Redistributable Package (x64) anyhow.

1. You can obtain this file at:

2. Download the Microsoft Visual C++ Redistributable Package (x64)

3. Should have a file called vcredist_x64.exe. Run installation.

4. Try starting OHS again.

I understood why Oracle still does the BI Publisher configuration despite I didn't select the option. When you don't select the option, BI Publisher is confgured, but, will be disabled, so that in the future you can easily enable this option.


OHS 12c Fails To Start On Windows Server 2008 X64, with no detailed errors. (Doc ID 1943820.1)

Exadata migration

Sun, 2016-11-27 12:21
Had a wonderful Sangam16 conference in India, and received much applaud for the two presentations delivered,  Oracle 12c multitenancy and Exadata migration best practices.

After a very short trip to India, life started to be business as usual again, and become busy. Was fully occupied with multiple assignments: Oracle EBS database health check assessment at a client for 2 days, GI/RDBMS/PSU deployments on Oracle Sun Super Cluster M7, Exadata configuration preparation and 9 databases migration to Exadata during the week-end.

Over the last week-end, we (me and my colleague) were pretty busy with 9 databases migration to Exadata. There were a few challenges , and learned a few new things too. I would like to discuss couple of scenarios that were interesting:

One of the databases had corrupted blocks, and the expdp was keep failing with ORA-01555: snapshot too old: rollback segment number  with name "" too small. Our initial thoughts were tuning undo_retention, increasing the undo tablespace, setting an event, etc. Unfortunately, none of the workarounds helped in the situation. We then cameacross a MOS note which explains that an ORA-01555 with "", no rollback segment name is probably due to corrupted blocks. After applying the solution explained in the note, we managed to export/import the database successfully. My colleague has blogged about the scenario at his blog:

Another database is running on Windows x86 64-bit, and its full of LOBs, hence, the datapumps (expdp) took significant time, as NFS filesystem used to store the  dump file. We then thought of doing direct RMAN restore from source to target, as the database on Windows x86 64bit and Linux x86 64-bit are the same (Litten) Endian formats. As per one of the MOS notes, we can also do the Data Guard setup, and do RMAN restore. However, RMAN recovery would fail with ORA-600, as cross platform redo conversion won't be possible. We are now thinking of taking a cold backup (consistent) and do a complete restore with reset logs option.

Stayed tuned for more updates on this.

Wanna become an OCM? Go on read out Kamran's new OCM practical guide - MY TAKE

Tue, 2016-10-25 10:31
When I first heard about Kamran's new book, OCM practical guide, I said wow, because I was thinking on the same line a few years back, but, dropped the idea due to several factors, including the fact that the time and efforts required on this book.When he approached me to be one of the technical reviewers, I have accepted the deal straightaway without having a second thought. I am really honored to be part of this wonderful book which unfold the knowledge and help OCM aspirants to get what they dream in their lives.

I remember the debates at various places and at several Oracle forums where people discussed about the necessity and advantages of being Oracle certified professional. There were so many discussions and debates whether being certified professional will really add any value to one's career. Anyways, this is not the platform to discuss such things , however, in my own perspective, with real experience and having certification surely boosts the career and gives more chances to advance in the career.

First thing first, Kamran really put his heart out to come-up with such an extraordinary book in the form of practical guide. I thoroughly enjoyed reviewing every bits and bytes of the book, and the amount of practical examples demonstrate in this book is just prodigious. Only one with the real world and tremendous experience in the technology could do that. Take a bow my friend.

Each and every chapter got some great contents, and neatly explained with about 200+ practical examples. Let me walk through the chapters and give you my inputs:

Server Configuration provides a detailed step-by-step guide on Oracle 11g Database Software setup and new database creation through GUI and silent mode likewise. Also, outlines the the procedure to configure network settings, listener, tns names and etc.

Enterprise Manager Grid Control explains step-by-step procedure to install and configure OEM, and how to schedule and manage stuff.  

Managing Database Availability one of the important chapters, not only for OCM preparation perspective, to manage our production databases, deploying optimal backup and recovery strategies to secure the databases.

Data Management chapter provides detailed information about types of materialized view and materialized view log and how Oracle uses precomputed materialized view instead of querying a table with different aggregate functions and provides a quick result.

Data Warehouse Management chapter provides information about main data warehouse topics such as partitioning and managing large objects. Next, we talk about large objects and show how to use various SecureFile LOB features such as compression, deduplication, encryption, caching and logging.

Performance Tuning I particularly enjoyed this reading and reviewing this chapter, Its the heart of the book with so much explanation and practical examples. This one shouldn't be missed out. 

Grid Infrastructure and ASM contains all you wanted to know about GRID infrastructure and ASM technologies. How to install GRID and create disk groups.

Real Application Clusters explores steps to successfully create a RAC database on two nodes. With only few additional steps, you will successfully create a RAC database. Then you see the way how to create and configure ASM with command line interface. Once ASM is configured, silent RAC database creation steps are provided.

Data Guard chapter starts by creating a data guard using command line interface, OEM and data guard broker. It also provides steps on performing switchover and failover using all mentioned tools.

In nutshell, its a practice guide with a perfect recipe and ingredients to become an OCM, which evenly blended with many useful examples and extraordinary explanation. The wait is over, this is the book we all been looking for a long time. Go and place your order and get certified, become an OCM.

You can place the order through Amazon, use the below URL:

Is Fixed Objects Stats needed in PDB?

Sat, 2016-10-22 04:53
Doesn't matter if we are working on a new technology or something we are very family with. When we encounter tricky situations, sometimes neither we could find things in the manuals nor could find any useful references.

All what you need is an action by yourself and then become a reference to others .

Mike Dietrich had received an interesting question about whether Gather Fixed Objects required for an indiviudal PDBs or just required on the ROOT Container. He did a small test and explained the answer at oracle.blogs.

Oracle Critical Patch Update for October 2016

Wed, 2016-10-19 05:08
The Critical Patch Update for October 2016 was released on October 18th, 2016. Oracle strongly recommends applying the patches as soon as possible. 

Visit the URL below for more updates

reports (cvucheckreport_*.xml) from ora.cvu resource causing excessive disk space

Sun, 2016-10-09 06:34
On Exadata dbnodes, we noticed that Clusterware ora.cvu resource generating XML reports every 6 hours and occupied large amounts of space on /u01 filesystem. Perhaps this is the same on non-Exadata systems too, if you are running Clusterware.

Upon looking into MOS ID (reports from ora.cvu resource using excessive disk space (Doc ID 1964482.1) it was learned that this is an expected behavior with where ora.cvu invokes Clusterware Health check (CHC) every 6 hrs and stores the XML reports under $GI_HOME/cvu/baseline or $ORACLE_BASE/crsdata/@global/cvu/baseline' location. However, the CVU didn't clean-up the reports periodically which ultimately causes  disk full.

As per the note, the bug is fixed as follows:

Bug 18143707 is fixed in
Bug 19703199 is fixed in PSU
Bug 20177779 is fixed in PSU

Quick workaround to this problem is to delete the files (xml) manually in order to release the space.


reports from ora.cvu resource using excessive disk space (Doc ID 1964482.1)
ora.cvu Resource Reports not Purged (Doc ID 2054765.1) Grid Infrastructure Patch Set Updates - List of Fixes in each GI PSU (Doc ID 1928853.1)

Learn and Master the concepts of Oracle 12c RAC and EM 13c from Oracle Experts

Sat, 2016-10-08 05:30
Learn and master the concepts of Oracle RAC 12c and EM 13c from Experts, online Instructor lead course.

Enroll now and avail 20% discount upfront, exclusively for my network.

Coupon, exclusively for my network : SJRAC20

At the end of the course and last 2 days, I will be sharing the following:
Day 1 : RAC best practices in real-world scenarios:
- Installation, Network, Storage, Application Design, Backup & Recovery
RAC internals

Day 2 : Taking all your questions. Exclusively taking your questions and answering them.

Guys, do forward this to your networking and help your friends/dears to master Oracle RAC and EM 13c concepts.

Course Objective:

To master Oracle Real Application Clusters 12c. Course is for Oracle DBAs or Apps DBAs looking to upgrade their skill.

Expectations and Goals
  • Install, Configure &amp; Administer 12c RAC
  • Install &amp; Configure 13c Cloud Control
  • Upgrade Database/RAC from 11g to 12c
  • Install, Configure &amp; Administer 12c Grid Infrastructure
Course Agenda:

  • RAC Introduction
  • RAC Architecture
  • Install and configure 12c RAC
  • Administer GI & RAC
  • Monitoring and Troubleshooting RAC
  • Upgrade RAC and Database
  • Migrate DB to ASM and RAC
  • Install, Configure EM 13c
  • Backup, Recovery & Cloning
  • Performance Tuning

The New Batch is going to start from Friday, 14th October 2016 from 07:00 PM IST

Explore more about 12c Multitenant with useful MOS Docs

Tue, 2016-10-04 12:03
As part of an ongoing Exadata migration assignment, we have been exploring Oracle 12c multitenant capabilities a lot. I would like to share here some of selected useful MOS Doc about 12c multitenant concepts:

Below docs explains most of the 12c multitenant concepts:

  • Initialization parameters in a Multitenant database - Facts and additional information (Doc ID 2101596.1)
  • Initialization parameters in a Multitenant database - FAQ and Examples (Doc ID 2101638.1)
  • Oracle Multitenant Option - 12c : Frequently Asked Questions (Doc ID 1511619.1)
  • 12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set ( Character set mismatch: PDB character set CDB character set ) (Doc ID 1968706.1)
  • How to Restore - Dropped Pluggable database (PDB) in Multitenant (Doc ID 2034953.1)
  • Script For Getting Complete Basic Information about configured CDB and PDB in Oracle Database Multitenant 12c (Doc ID 2012221.1)
  • Difference Between Major Components of Traditional Databases and Multitenant Databases CDB/PDB Introduced in Version 12c (Doc ID 2013529.1)
  • How to set a Pluggable Database to have a Different Time Zone to its own CDB (Doc ID 2127835.1)
  • Where Manageability Data is Stored in 12c Multi-tenant (CDB) database (Doc ID 1586256.1)
  • Master Note for the Oracle Multitenant Option (Doc ID 1519699.1)

Below notes outlines guideline to manage 12c Multitenant Container Database while Oracle Data Guard configuring is in-place:

  • Step by Step Examples of Migrating non-CDBs and PDBs Using ASM for File Storage (Doc ID 1576755.1)
  • Making Use Deferred PDB Recovery and the STANDBYS=NONE Feature with Oracle Multitenant (Doc ID 1916648.1)
  • Data Guard Impact on Oracle Multitenant Environments (Doc ID 2049127.1)
  • PDB Failover in a Data Guard environment: Unplugging a Single Failed PDB from a Standby Database and Plugging into a New Container (Doc ID 2088201.1)

New ORAchk & EXAchk Versions Released

Mon, 2016-09-19 06:29

New ORAchk & EXAchk v12. release is out now with so many new features. Its easy to use and upgrade.

Below is the blog entry about the new release and features by RAC Assurance Development team at Oracle:

Upgrade yourself from DBA to DMA

Thu, 2016-09-15 04:09
Be it any profession, learning never ends. From my very own personal perspective and exprience, I strongly beleinv in one who upgrade his skills always stays a step ahead from the rest. To stay competitive, it is necessary to upgrade our skills according to the market demand.

For DBA folks who aim and dream to learn and upgrade their knwoledge with Oracle Exadata technologies, I wrote a 5 series article to Toad, below are the links to the articles:

Below are few more Exadata related stuff.

Managing & Troubleshooting Exadata - Upgrading & Patching ExadataShrink/Grow Exadata diskgroups

Pass on the knolwedge to the community.

Happy reading.

E-book: Discover the breakthrough innovations in Oracle Database 12c

Wed, 2016-09-14 11:23
Download the eBook and read to learn about 12c (the next generation most popular database) :

  • Simplify database consolidation and manage them as cloud services
  • Accelerate analytical performance whilst achieving new levels of efficiency, security and availability
  • Lower cost and mitigate risk

AIOUG Sangam'16 - India's largest Annual Oracle Confernece - DON'T MISS IT

Wed, 2016-09-07 07:53
AIOUG Sangam'16, India's largest annual Oracle conference, to be held in Bangalore this year during 11th and 12th November.

I am glad and super excited to present and attend the conference. I am going to present the following two sessions:

  • Exadata Migration Recipes - Secret Sauce for a Tasty Migration
The purpose of this presentation is to provide real-world augmented database migration procedures that allowed us to successfully achieve the migrations of 54 business critical databases to Exadata x5-2 of different sizes and Operating Systems. The presentation will layout the different possible migration technologies that meets the business demands such as Data Pumps, RMAN, XTTS with Incremental Backups.  The presentation will also highlight and focus on the challenges that were encountered and their solutions. A very specific scenario will also be presented: Cross platform Oracle EBS migration of 30TB size.
  • Cost Optimization and Better Database Management with Oracle 12c Multitenant A 360 Degree Overview
Organizations who deploy and manage several numbers of standalone databases in their complex environments typically face the uphill task of better (ease) database management and also Cost optimization. Oracle 12c Multitenant got the ability to consolidate databases, which addresses the major concerns of those Organizations and provide the easier Database management to the DBAs. The presentation will focuses on the key advantages and benefits of 12c Multitenant features, and how one can reduce the IT cost as well as manage their databases efficiently. Outlines the architecture, deployment scenarios, easy management of Multitenant features.

Why should you attend
If you are around in India during those dates, I strongly recommend you to avail the opportunity to meet and learn from many international stars. AIOUG really did a tremeounds job to bring famous Oracle personalities together on board for this year's event.I am really honored and indeed my previlege to present and listen to other fellow speakers.

Visit the website to for more details: enrollment, view list of topics and spearks etc

Look forward to seeng you at the event!