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: 5 hours 11 min ago

Switchover and Switchback simplified in Oracle 12c

Fri, 2017-10-13 07:51

Business continuity (Disaster Recovery) has become a very critical factor for every business, especially in the financial sectors. Most of the banks are tending to have their regular DR test to meet the central bank regulation on DR testing capabilities.

Very recently, there was a request from one of the clients to perform a reverse replication and rollback (i.,e switchover & switchback) between the HO and DR for one of the business critical databases. Similar activities performed with easy on pre 12c databases. However, this was my first experience with Oracle 12c. After spending a bit of time to explore whats new in 12c Switchover, it was amazing to learn how 12c simplified the procedure. So, I decided to write a post on my experience.

This post demonstrates how Switchover and Switchback procedure is simplified in Oracle 12c.

The following is used in the scenario:

·        2 instances Oracle 12c RAC primary database (IMMPRD)
·        Single instance Oracle 12c RAC Standby database (IMMSDB)

Look at the current status of the both databases:
-- Primary
IMMPRD> select status,instance_name,database_role from v$database,v$instance;

------------ ---------------- ----------------
OPEN         IMMPRD1           PRIMARY

-- Standby
IMMSDB> select status,instance_name,database_role from v$database,v$instance;

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

Before getting into the real action, validate the following to avoid any failures during the course of role transition:

·        Ensure log_archive_dest_2 is configured on PRIMARY and STANDBY databases
·        Media Recovery Process (MRP) is active on STANDBY and in sync with PRIMARY database
·        Create STANDBY REDO logs on PRIMARY, if not exists
·        FAL_CLIENT & FAL_SERVER parameters set on both databases
·        Verify TEMP tablespaces on STANDBY, add them if required, as TEMPFFILES created after STANDBY creation won’t be propagated to STANDBY site.

Pre-Switchover in 12c

For a smooth role transition, it is important to have everything in-place and in sync. Pre-Oracle 12c, a set of commands used on PRIMARY and STANDBY to validate the readiness of the systems. However, with Oracle 12c, this is simplified with the ALTER DATABASE SWITCHOVER VERIFY command. The command performs the following set of actions:

·        Verifies minimum Oracle version, i.e, Oracle 12.1
·        Verify MRP status on Standby database

Let’s run the command on the primary database to validate if the environments are ready for the role transition.

IMMPRD>  alter database switchover to IMMSDB verify;
 alter database switchover to IMSDB verify
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

When the command is executed, an ORA-16475 error was encountered. For more details, lets walk through the PRIMARY and STANDBY databases alert.log file, and pay attention to the SWITCHOVER VERIFY WARNING.

--primary database alert.log

Fri Oct 13 11:16:00 2017
SWITCHOVER VERIFY: Send VERIFY request to switchover target IMSDB
SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to a primary database, the new primary database will not be protected.

ORA-16475 signalled during:  alter database switchover to IMSDB verify...

The LOG_ARCHIVE_DEST_2 parameter was not set on the STANDBY database and the VERIFY command produced the warning. After setting the parameter on the STANDBY, the verify command was re-ran, and it went well this time.

IMMPRD> alter database switchover to IMMSDB verify;

Database altered.

PRIMARY database alert.log confirms no WARINGS

alter database switchover to IMMSDB verify
Fri Oct 13 08:49:20 2017
SWITCHOVER VERIFY: Send VERIFY request to switchover target IMMSDB
Completed: alter database switchover to IMMSDB verify

Switchover in 12c 

After successful validation and confirmation about the DBs readiness for the role transition, execute the actual switchover command on the primary database. (advised to view the alert.log files of PRIMARY and STANDBY instances).

IMMPRD> alter database switchover to IMMSDB;

Database altered.

Let’s walk through the PRIMARY and STANDBY database alert.log files to review what Oracle has internally done.

--primary database alert.log

alter database switchover to IMMSDB
Fri Oct 13 08:50:21 2017
Starting switchover [Process ID: 302592]
Fri Oct 13 08:50:21 2017
Waiting for target standby to receive all redo
Fri Oct 13 08:50:21 2017
Waiting for all non-current ORLs to be archived...
Fri Oct 13 08:50:21 2017
All non-current ORLs have been archived.
Fri Oct 13 08:50:21 2017
Waiting for all FAL entries to be archived...
Fri Oct 13 08:50:21 2017
All FAL entries have been archived.
Fri Oct 13 08:50:21 2017
Waiting for dest_id 2 to become synchronized...
Fri Oct 13 08:50:22 2017
Active, synchronized Physical Standby switchover target has been identified
Preventing updates and queries at the Primary
Generating and shipping final logs to target standby
Switchover End-Of-Redo Log thread 1 sequence 24469 has been fixed
Switchover End-Of-Redo Log thread 2 sequence 23801 has been fixed
Switchover: Primary highest seen SCN set to 0x960.0x8bcd0f48
ARCH: Noswitch archival of thread 2, sequence 23801
ARCH: End-Of-Redo Branch archival of thread 2 sequence 23801
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
ARCH: Standby redo logfile selected for thread 2 sequence 23801 for destination LOG_ARCHIVE_DEST_2
ARCH: Noswitch archival of thread 1, sequence 24469
ARCH: End-Of-Redo Branch archival of thread 1 sequence 24469
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
ARCH: Standby redo logfile selected for thread 1 sequence 24469 for destination LOG_ARCHIVE_DEST_2
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
Waiting for target standby to apply all redo
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/imprd/IMPRD1/trace/IMPRD1_ora_302592.trc
Converting the primary database to a new standby database
Clearing standby activation ID 627850507 (0x256c3d0b)
The primary database controlfile was created using the
'MAXLOGFILES 192' clause.
There is space for up to 186 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
Archivelog for thread 1 sequence 24469 required for standby recovery
Archivelog for thread 2 sequence 23801 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover complete. Database shutdown required
USER (ospid: 302592): terminating the instance
Fri Oct 13 08:50:44 2017
Instance terminated by USER, pid = 302592
Completed: alter database switchover to IMMSDB
Shutting down instance (abort)

--standby database alert.log

SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER  TO PRIMARY' from primary database.
Fri Oct 13 08:50:32 2017
Maximum wait for role transition is 15 minutes.
Switchover: Media recovery is still active
Role Change: Canceling MRP - no more redo to apply

SMON: disabling cache recovery
Fri Oct 13 08:50:41 2017
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/imsdb/IMMSDB1/trace/IMMSDB1_rmi_120912.trc
SwitchOver after complete recovery through change 10310266982216
Online logfile pre-clearing operation disabled by switchover
Online log +DATAC1/IMMSDB/ONLINELOG/group_1.3018.922980623: Thread 1 Group 1 was previously cleared
Standby became primary SCN: 10310266982214
Switchover: Complete - Database mounted as primary
SWITCHOVER: completed request from primary database.
Fri Oct 13 08:51:11 2017

At this point-in-time, the new PRIMARY database is in MOUNT state, so you need to OPEN the database.

IMMSDB> alter database open

And startup the STANDBY database and enable MRP: (below is the active standby database command)

IMMPRD> startup
IMMPRD> recover managed standby database using current logfile disconnect from session;

Post Switchover, run through the following:

IMMSDB> alter system switch logfile;

IMMSDB> select dest_id,error,status from v$archive_dest where dest_id=2;

IMMSDB> select max(sequence#),thread# from v$log_history group by thread#;
IMMSDB> select max(sequence#)  from v$archived_log where applied='YES' and

On Standby database

IMMPRD> select thread#,sequence#,process,status from gv$managed_standby;
-- in 12.2, use gv$dataguard_status instead of gv$managed_standby view

IMMPRD> select max(sequence#),thread# from v$archived_log group by thread#;

You can also enable the trace on primary and standby before performing the role transition to analyze any failures during the procedure. Use the below procedure on the PRIMARY database to enable the tracing:

SQL> alter system set log_archive_trace=8191;  -- enabling trace

SQL> alter system set log_archive_trace=0;      -- disabling trace


To revert (switch back) to the previous situation, perform the same action. Remember, now, your primary is your previous STANDBY and standby is previous PRIMARY.


12c Data guard Switchover Best Practices using SQLPLUS (Doc ID 1578787.1)

A few useful Oracle 12cR2 MOS Docs

Thu, 2017-07-06 07:33
A few useful MOS Docs are listed below , in case if 12cR2 upgrade around the corner.

  • How to Upgrade to/Downgrade from Grid Infrastructure 12.2 and Known Issues (Doc ID 2240959.1)
  • Complete Checklist for Upgrading to Oracle Database 12c Release 2 (12.2) using DBUA (Doc ID 2189854.1)
  • 12.2 Grid Infrastructure Installation: What's New (Doc ID 2024946.1)
  • Patches to apply before upgrading Oracle GI and DB to (Doc ID 2180188.1)
  • Differences Between Enterprise, Standard Edition 2 on Oracle 12.2 (Doc ID 2243031.1)
  • 12.2 Does Not List Disks Unless the Discovery String is Provided (Doc ID 2244960.1)

Oracle Clusterware 12cR2 - deprecated and desupported features

Thu, 2017-07-06 04:27

Having clear understanding of deprecated and desupported features in a new release is equally important as knowing the new features of the release. In this short blog post, I would like to highlight the following features that are either deprecated or desupported in 12cR2.

·        config.shwill no longer be used for Grid configuration wizard, instead, the is used in 12cR2;
·        Placement of OCR and Voting files directly on a shared filesystem is not deprecated;
·        The utility is deprecated in favor of Oracle Trace File Analyzer;

·        You are no longer able to use Oracle Clusterware commands that are prefixed with crs_.

In my next blog post, will go over some of the important features Oracle Clusterware in 12cR2. Stay tuned.


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: