OGG-01224 TCP/IP error 111 (Connection refused), endpoint: :Port.

Well, this article is related to the previous one - click here where pump extract at source was getting ABEND and it is again a confusing error in Oracle GoldenGate 12c Release 2. In this case, for this reported error, Oracle Support Note(Doc ID 2139900.1) explains that the pump extract was getting abend due to " No ports available for the target manager to start a dynamic server collector", this MOS note applies to this kind of error but in my case it was fine since Manager process was already able to start collector processes dynamically on dynamic ports as you would notice in the log below.

2017-10-30 21:06:11  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #4).
2017-10-30 21:06:11  INFO    OGG-01677  Oracle GoldenGate Collector for Oracle:  Waiting for connection (started dynamically).
2017-10-30 21:06:11  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from SERVER on host [127.0.0.1]:60108 (REPORT 4202 7819).
2017-10-30 21:06:11  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #1).
2017-10-30 21:06:11  INFO    OGG-00974  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started collector process (Port 7819).

After further evaluation of OGG error log at target server, I could see the following ERROR (error: 2, No such file or directory)

2017-10-30 21:08:45  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #4).
2017-10-30 21:08:46  ERROR   OGG-01878  Oracle GoldenGate Collector for Oracle:  Failed resolving ALLOWOUTPUTDIR /u01/ogg_12c/rmttrail/em (error: 2, No such file or directory).
2017-10-30 21:08:46  ERROR   OGG-01668  Oracle GoldenGate Collector for Oracle:  PROCESS ABENDING.




Yes, directory /u01/ogg_12c/rmttrail/em was not existing on the target server so I corrected rmttrail parameter value in pump extract at source.

GGSCI (prodsrv) 15> view param pemp

EXTRACT PEMP
RMTHOST drsrv, mgrport 7809, timeout 30
RMTTRAIL /u01/ogg_12c/rmttrail/em      ----- Wrong entry here
PASSTHRU
TABLE scott.emp;

GGSCI (prodsrv) 15> view param pemp

EXTRACT PEMP
RMTHOST drsrv, mgrport 7809, timeout 30
RMTTRAIL /u01/ogg_12c/rmttrail   ---- Corrected the entry here
PASSTHRU
TABLE scott.emp;

After correcting the value for remote trail location, pump started up normally.

GGSCI (prodsrv) 18> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXEMP       00:00:00      00:00:00    
EXTRACT     RUNNING     PEMP        00:00:00      00:00:06    







OGG-01031 There is a problem in network communication

During starting up a new Pump Extract in Oracle GoldenGate 12c Release 2, I came through the following error that ABEND the pump extract at source database server.


ERROR   OGG-01031  There is a problem in network communication, a remote file problem, encryption keys f
or target and source do not match (if using ENCRYPT) or an unknown error. (Reply received is Output file /u01/ogg_12c/rmttrai
l/em000000 is not in any allowed output directories.).

GGSCI (prodsrv) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXEMP       00:00:00      00:00:01    
EXTRACT     ABENDED     PEMP        00:00:00      00:24:10    










Cause : In Oracle GoldenGate 12c Release 2, by default, Pump extract try to push the EXTRACT TRAIL logs from source Database server to target database server to the default directory(i.e. ./dirdat) but in our case we are using the custom directory(/u01/ogg_12c/rmttrail) to store the remote trail files on the target server.


From OGG Error log at target server.

2017-10-30 21:06:16  INFO    OGG-01226  Oracle GoldenGate Collector for Oracle:  Socket buffer size set to 187200 (flush size 27985).
2017-10-30 21:06:16  WARNING OGG-01223  Oracle GoldenGate Collector for Oracle:  Output file /u01/ogg_12c/rmttrail/em000000 is not in any allowed output directories.
2017-10-30 21:06:16  INFO    OGG-01971  Oracle GoldenGate Collector for Oracle:  The previous message, 'WARNING OGG-01223', repeated 1 times.
2017-10-30 21:06:16  INFO    OGG-01676  Oracle GoldenGate Collector for Oracle:  Terminating after client disconnect.

Pump Extract Parameter file at source:

GGSCI (prodsrv) 15> view param pemp

EXTRACT PEMP
RMTHOST drsrv, mgrport 7809, timeout 30
RMTTRAIL /u01/ogg_12c/rmttrail/em
PASSTHRU
TABLE scott.emp;







Solutions: Go to the target Oracle GoldenGate server and add the following parameter(ALLOWOUTPUTDIR )in the ./GLOBALS parameter file to solve this problem.

GGSCI (DRsrv) 8> view param ./GLOBALS

GGSCHEMA ogg
ALLOWOUTPUTDIR /u01/ogg_12c/rmttrail


GGSCI (DRsrv) 9> 




Subsequently start the pump extract at source and it would move forward.

GGSCI (prodsrv) 16> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXEMP       00:00:00      00:00:04    
EXTRACT     RUNNING     PEMP        00:00:00      00:00:03    







Happy Learning....!!

ORION - ORacle I/O Numbers - I/O Bench-marking Tool in Oracle

ORION - ORacle I/O Numbers :  An I/O Bench-marking Tool in Oracle


Generally, you do the workload testing of your production database system or the new hardware arrived in your data center when your Oracle RDBMS server already running with actual application data but you can simulate the similar I/O testing with Oracle provided Tool called "Orion", an I/O bench-marking Tool. It helps you predict the performance of I/O Bench-marking for new hardware system for Oracle database.

The ORION tool has been available for some time now and honestly I was not aware of it so far. While exploring the advanced performance tuning of Oracle RDBMS, I came to know about this nice pre-bundled script to evaluate the I/O performance of your Oracle database on new hardware where you are going to deploy or migrate your database.

Initially this tool was available to download from Oracle OTN but since Oracle 11.2 onward, it is bundled inside Orale RDBMS database software package itself. So you can invoke from $ORACLE_HOME/bin directory to work with it.

ORION works best during the evaluation phase of new platform without application-data on your logical unit numbers(LUNs). It is specifically designed to simulate the I/O workload on your new hardware system for Oracle database. This package uses asynchronous I/O and large pages for read and writes to concurrently submit I/O requests to operating system. Asynchronous I/O uses "libaio" on Linux platform. You can quite easily see that the ORION slave processes use "io_submit" and "io_getevents" by tracing strace on this.


Here, let's go ahead and fake some LUNs to perform evaluation of I/O on it.

-bash-4.1$ dd if=/dev/zero of=lun01.file bs=1G count=1
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 24.0865 s, 44.6 MB/s


-bash-4.1$ dd if=/dev/zero of=lun02.file bs=1G count=1
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 12.9167 s, 83.1 MB/s


-bash-4.1$ dd if=/dev/zero of=lun03.file bs=1G count=1
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 1.00802 s, 1.1 GB/s


-bash-4.1$ dd if=/dev/zero of=lun04.file bs=1G count=1
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 1.01502 s, 1.1 GB/s

You can see LUNs are faked inside /u01 mount by listing the directory.

bash-4.1$ pwd
/u01
-bash-4.1$ ls -ltr *.file
-rw-r--r--. 1 oracle oinstall 1073741824 Oct 17 06:53 lun01.file
-rw-r--r--. 1 oracle oinstall 1073741824 Oct 17 06:53 lun02.file
-rw-r--r--. 1 oracle oinstall 1073741824 Oct 17 06:54 lun03.file
-rw-r--r--. 1 oracle oinstall 1073741824 Oct 17 06:54 lun04.file




Once the LUNs are created, make a group file with list of LUNs we created earlier with its obsolute file path. I created a group file /u01/orion.lun as below.

-bash-4.1$ cat /u01/orion.lun 
/u01/lun01.file
/u01/lun02.file
/u01/lun03.file
/u01/lun04.file

Finally, invoke orion executable from $ORACLE_HOME/bin location as below. As soon as you start the command to evaluate the I/O testing, this tool will tell you the approximate time it will take to complete.

-bash-4.1$ $ORACLE_HOME/bin/orion -run oltp -testname /u01/orion -hugenotneeded

Output should be like below.

Below I have generated a graph based on above out to relate Latency and and corresponding # No. of I/Os(Read) per second. You can realise that lower the latency the higher #No. of I/O(Reads)

As part of the I/O performance evaluation, orion tool creates multiple CSV files, text files with detailed metrics you should look at for evaluating IOPS, MBPS operations.

-rw-r--r--. 1 oracle oinstall   355 Oct 17 07:00 orion_20171017_0700_summary.txt
-rw-r--r--. 1 oracle oinstall     0 Oct 17 07:00 orion_20171017_0700_mbps.csv
-rw-r--r--. 1 oracle oinstall     0 Oct 17 07:00 orion_20171017_0700_lat.csv
-rw-r--r--. 1 oracle oinstall     0 Oct 17 07:00 orion_20171017_0700_iops.csv
-rw-r--r--. 1 oracle oinstall     0 Oct 17 07:00 orion_20171017_0700_hist.txt
-rw-r--r--. 1 oracle oinstall   515 Oct 17 07:00 orion_20171017_0700_trace.txt
-rw-r--r--. 1 oracle oinstall 56161 Oct 17 07:22 orion_20171017_0702_trace.txt
-rw-r--r--. 1 oracle oinstall   570 Oct 17 07:22 orion_20171017_0702_mbps.csv
-rw-r--r--. 1 oracle oinstall   811 Oct 17 07:22 orion_20171017_0702_lat.csv
-rw-r--r--. 1 oracle oinstall   742 Oct 17 07:22 orion_20171017_0702_iops.csv
-rw-r--r--. 1 oracle oinstall 19494 Oct 17 07:22 orion_20171017_0702_hist.txt
-rw-r--r--. 1 oracle oinstall  2008 Oct 17 07:22 orion_20171017_0702_summary.txt










Below is the contents I extracted from orion_20171017_0700_iops.csv

file and it has more details for Small I/O and Large I/O statistics in there.

 

This comma-separated-value file contains the rates sustained by small I/Os in IOPS.

Each value corresponds to a data point test that used a fixed number of outstanding small and large I/Os.

The number of outstanding small I/Os for a value is specified by its column header in the first row.

The number of outstanding large I/Os for a value is specified by its row header in the first column.




Small I/O 4 8 12 16 20 24 28 32 36 40 44 48 52 56 60 64 68 72 76 80 Large I/O 587 667 595 428 361 338 340 340 336 341 342 351 339 335 346 350 351 353 352 351





Similarly, you can review all required .csv and other files to study the evaluation in details to get an idea how your oracle database will perform on new hardware.

Oracle Database Editions - Types

This article describes the types of Oracle Database Editions: 

Oracle Database is available in five editions, each suitable for different development and deployment scenarios. Oracle also offers several database options, packs, and other products that enhance the capabilities of Oracle Database for specific purposes. This section describes the Oracle Database editions.

Below are the brief details about different Oracle 12c Software Editions available:

Oracle Database Standard Edition One:

Oracle Database Standard Edition One delivers unprecedented ease of use, power, and performance for workgroup, department-level, and Web applications. From single-server environments for small business to highly distributed branch environments, Oracle Database Standard Edition One includes all the facilities necessary to build business-critical applications.

Oracle Database Standard Edition:

Oracle Database Standard Edition delivers the unprecedented ease of use, power, and performance of Standard Edition One, with support for larger machines and clustering of services with Oracle Real Application Clusters (Oracle RAC). Oracle RAC is not included in the Standard Edition of releases prior to Oracle Database 10g, nor is it an available option with those earlier releases.

Oracle Database Enterprise Edition:

Oracle Database Enterprise Edition provides the performance, availability, scalability, and security required for mission-critical applications such as high-volume online transaction processing (OLTP) applications, query-intensive data warehouses, and demanding Internet applications. Oracle Database Enterprise Edition contains all of the components of Oracle Database, and can be further enhanced with the purchase of the options and packs described in Chapter 2, "Options and Packs".

Oracle Database Express Edition:

Oracle Database Express Edition (Oracle Database XE) is an entry-level edition of Oracle Database that is quick to download, simple to install and manage, and is free to develop, deploy, and distribute. Oracle Database XE makes it easy to upgrade to the other editions of Oracle without costly and complex migrations. Oracle Database XE can be installed on any size machine with any number of CPUs, stores up to 11 GB of user data, using up to 1 GB of memory, and using only one CPU on the host machine. Support is provided by an online forum.

Oracle Database Personal Edition:

Oracle Database Personal Edition supports single-user development and deployment environments that require full compatibility with Oracle Database Standard Edition One, Oracle Database Standard Edition, and Oracle Database Enterprise Edition. Personal Edition includes all of the components that are included with Enterprise Edition, as well as all of the options that are available with Enterprise Edition, with the exception of the Oracle Real Application Clusters option, which cannot be used with Personal Edition. Personal Edition is available on Windows and Linux platforms only. The Management Packs are not included in Personal Edition.

Reference: https://docs.oracle.com/cd/E11882_01/license.112/e47877/editions.htm#DBLIC109

Overview - Oracle GoldenGate 12c

This article describes the capabilities of Oracle GoldenGate replication technology. Oracle GoldenGate enables the exchange of changed data between heterogeneous databases and provides the capability to migrate databases to another platform or database with zero or minimal downtime. It is very light weight replication software as compared to other replication technologies available in the market, it captures the transaction level committed data from source database and move, apply that committed transactions over to the target database server near real time.

It is widely used replication for Oracle Snapshot Standby databases for MIS reporting purpose, specially in banking domains, financial sectors. I have worked for India's leading banking customer where we use Oracle GoldenGate to replicate crucial business reporting tables from production database servers to MIS (Snapshot Standby) reporting databases for real-time reporting for the management and Branch users to offload the workload from Core-Banking production server to the Snapshot-Standby databases.

Below is the diagram which depicts the type of replication possible with Oracle GoldenGate.

With the flexibility, and the filtering, transformation, and custom processing features of Oracle GoldenGate, you can support numerous business requirements:

¦ Business continuance and high availability.
¦ Initial load and database migration.
¦ Data integration.
¦ Decision support and data warehousing.



Oracle GoldenGate can be configured for the following purposes:

- A static extraction of data records from one database and the loading of those records to another            database.

- Continuous extraction and replication of transnational DML operations and DDL changes (for supported databases) to keep source and target data consistent.

- Extraction from a database and replication to a file outside the database.




Oracle GoldenGate is composed of the following components:

 - Manager
 - Extract
 - Data pump
 - Replicat
 - Trails or extract files
 - Checkpoints
 - Collector

Overview of Manager

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

Manager is the control process of Oracle GoldenGate. Manager must be running on each system in the Oracle GoldenGate configuration before Extract or Replicat can be started, and Manager must remain running while those processes are running so that resource management functions are performed. 

Manager performs the following functions:

■ Start Oracle GoldenGate processes

■ Start dynamic processes

■ Maintain port numbers for processes

■ Perform trail management

■ Create event, error, and threshold reports

One Manager process can control many Extract or Replicat processes. On Windows systems, Manager can run as a service.

Overview of Extract Process:

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

The Extract process is the extraction (capture) mechanism of Oracle GoldenGate. Extract runs on a source database system or on a downstream database, or both, depending on the database and the implementation requirements.

You can configure Extract in one of the following ways:

■ Initial loads: For initial data loads, Extract process extracts (captures) a current, static set of data directly from their source objects.

■ Change synchronisation: To keep source data synchronised with another set of data, Extract captures DML and DDL operations after the initial synchronisation has taken place.

Multiple Extract processes can operate on different objects at the same time. For example, two Extract processes can extract and transmit in parallel to two Replicat processes (with two persistence trails) to minimise target latency when the databases are large. To differentiate among different Extract processes, you assign each one a group name.

Overview of Data Pumps

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

A data pump is a secondary Extract group within the source Oracle GoldenGate configuration. If a data pump is not used, Extract must send the captured data operations to a remote trail on the target. In a typical configuration with a data pump, however, the primary Extract group writes to a trail on the source system. The data pump reads this trail and sends the data operations over the network to a remote trail

Note: Extract ignores operations on objects that are not in the Extract configuration, even though the same transaction may also include operations on objects that are in the Extract configuration. on the target. The data pump adds storage flexibility and also serves to isolate the primary Extract process from TCP/IP activity.

In general, a data pump can perform data filtering, mapping, and conversion, or it can

be configured in pass-through mode, where data is passively transferred as-is, without

manipulation. Pass-through mode increases the throughput of the data pump, because

all of the functionality that looks up object definitions is bypassed.

Overview of Replicat

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

The Replicat process runs on the target system, reads the trail on that system, and then reconstructs the DML or DDL operations and applies them to the target database. Replicat uses dynamic SQL to compile a SQL statement once, and then execute it many times with different bind variables.

You can configure Replicat in one of the following ways:

■ Initial loads: For initial data loads, Replicat can apply a static data copy to target

objects or route it to a high-speed bulk-load utility.

■ Change synchronization: When configured for change synchronization, Replicat

applies the replicated source operations to the target objects using a native

database interface or ODBC, depending on the database type.

You can use multiple Replicat processes with one or more Extract processes and data

pumps in parallel to increase throughput. To preserve data integrity, each set of

processes handles a different set of objects. To differentiate among Replicat processes,

you assign each one a group name

Overview of Trails

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

To support the continuous extraction and replication of database changes, Oracle GoldenGate stores records of the captured changes temporarily on disk in a series of files called a trail. A trail can exist on the source system, an intermediary system, the target system, or any combination of those systems, depending on how you configure Oracle GoldenGate. On the local system it is known as an extract trail (or local trail). On a remote system it is known as a remote trail.

Overview of Extract Files

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

In some configurations, Oracle GoldenGate stores extracted data in an extract file instead of a trail. The extract file can be a single file, or it can be configured to roll over into multiple files in anticipation of limitations on file size that are imposed by the operating system. In this sense, it is similar to a trail, except that checkpoints are not recorded. The file or files are created automatically during the run. The same versioning features that apply to trails also apply to extract files.

Overview of Checkpoints

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

Checkpoints store the current read and write positions of a process to disk for recovery purposes. Checkpoints ensure that data changes that are marked for synchronization actually are captured by Extract and applied to the target by Replicat, and they prevent redundant processing. They provide fault tolerance by preventing the loss of data should the system, the network, or an Oracle GoldenGate process need to be restarted. For complex synchronization configurations, checkpoints enable multiple

Extract or Replicat processes to read from the same set of trails. Checkpoints work with inter-process acknowledgments to prevent messages from being lost in the network. Oracle GoldenGate has a proprietary guaranteed-message delivery technology.

Overview of Collector

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

Collector is a process that runs in the background on the target system when continuous, online change synchronization is active.

Collector does the following:

■ Upon a connection request from a remote Extract to Manger, scan and bind to an available port and then send the port number to Manager for assignment to the requesting Extract process.

■ Receive extracted database changes that are sent by Extract and write them to a trail file. Manager starts Collector automatically when a network connection is required, so Oracle GoldenGate users do not interact with it. Collector can receive information from only one Extract process, so there is one Collector for each Extract that you use. Collector terminates when the associated Extract process

terminates.

Reference : http://docs.oracle.com/goldengate/1212/gg-winux/GWUAD/toc.htm

How to know if your Database is running on Exadata Machine?

Today one of my friend asked me how can we identify if our Oracle database we are connected is running on Oracle Exadata Database Machine or not? It was pretty good question, right?

Well, suppose, you had been working with non-Exadata machines and you joined a new company and there are various Database systems in your IT Infrastructure, some of the databases are hosted on Exadata Database Machine and some of them are running on non-Exadata Servers.

Mostly, you won't have the access to Cell Nodes of Exadata Storage server so you may find it difficult to identify the answer of your questions.

Now, knowing that your team supports Exadata, you would now be too excited to know about it, right?

Here you go :-)

Below is the simple SQL query which would tell if your Oracle database running on Exadata Machine or not.

SQL> select * from (select count( distinct cell_name) from v$cell_state);

COUNT(CELL_NAME)
----------------
           0



OR

 select case when count(cell_name) > 0 then 'EXADATA'
 else 'NOT EXADATA' END "IsExadata"
 from v$cell_state; 




If above query returns zero(0) rows then your Oracle database you are connected is not running on Exadata Database Machine and if it returns values (minimum would be 3 rows as Exadata Database Machine has minimum 3 storage cell nodes) then your database you are connected is running on Exadata Database machine.

Oracle GoldenGate 12c Silent Installation

In my last article, I demonstrated the steps to install Oracle GoldenGate 12c software in Graphical Mode, thanks to my friend "Juan Andres Mercado" who reminded me for Oracle GoldenGate silent installation because it is possible you may not find graphical access to some critical Data Center environments to install in Graphical Mode so the only option to install Oracle products would be in silent mode.

Oracle GoldenGate silent installation is similar to other oracle product's silent or Text based installation like we do for Oracle Grid Infrastructure and Oracle RDBMS installations using a response file. All we need to do is to create a response file to be used for silent installation. Oracle already provides a sample response file (Disk1/response/oggcore.rsp) with Oracle GoldenGate software package which can be used to create a new response file or we can modify the existing sample response file to be used for our Oracle GoldenGate silent installation.


I used the existing sample response file for this demo by adjusting the required parameters to be used as per its need and requirements.

Below is the sample response file I have used and have highlighted the parameters which are required and needed to modify for the silent installation.



Sample Response File:
==================

 bash-4.1$ cat /u01/oggcore.rsp   
 ####################################################################  
 ## Copyright(c) Oracle Corporation 2014. All rights reserved.   ##  
 ##                                ##  
 ## Specify values for the variables listed below to customize   ##  
 ## your installation.                       ##  
 ##                                ##  
 ## Each variable is associated with a comment. The comment    ##  
 ## can help to populate the variables with the appropriate    ##  
 ## values.                            ##  
 ##                                ##  
 ## IMPORTANT NOTE: This file should be secured to have read    ##  
 ## permission only by the oracle user or an administrator who   ##  
 ## own this installation to protect any sensitive input values.  ##  
 ##                                ##  
 ####################################################################  
 #-------------------------------------------------------------------------------  
 # Do not change the following system generated value.   
 #-------------------------------------------------------------------------------  
 oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2  
 ################################################################################  
 ##                                      ##  
 ## Oracle GoldenGate installation option and details             ##  
 ##                                      ##  
 ################################################################################  
 #-------------------------------------------------------------------------------  
 # Specify the installation option.  
 # Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and  
 #     ORA11g for installing Oracle GoldenGate for Oracle Database 11g   
 #-------------------------------------------------------------------------------  
 INSTALL_OPTION=ORA11g  
 #-------------------------------------------------------------------------------  
 # Specify a location to install Oracle GoldenGate  
 #-------------------------------------------------------------------------------  
 SOFTWARE_LOCATION=/u01/app/oracle/product/11.2.0/ogg_home  
 #-------------------------------------------------------------------------------  
 # Specify true to start the manager after installation.   
 #-------------------------------------------------------------------------------  
 START_MANAGER=true  
 #-------------------------------------------------------------------------------  
 # Specify a free port within the valid range for the manager process.  
 # Required only if START_MANAGER is true.  
 #-------------------------------------------------------------------------------  
 MANAGER_PORT=7800  
 #-------------------------------------------------------------------------------  
 # Specify the location of the Oracle Database.  
 # Required only if START_MANAGER is true.  
 #-------------------------------------------------------------------------------  
 DATABASE_LOCATION=/u01/app1/oracle/product/11.2.0/dbhome_1  
 ################################################################################  
 ##                                      ##  
 ## Specify details to Create inventory for Oracle installs          ##  
 ## Required only for the first Oracle product install on a system.      ##  
 ##                                      ##  
 ################################################################################  
 #-------------------------------------------------------------------------------  
 # Specify the location which holds the install inventory files.  
 # This is an optional parameter if installing on  
 # Windows based Operating System.  
 #-------------------------------------------------------------------------------  
 INVENTORY_LOCATION=/u01/app1/oraInventory  
 #-------------------------------------------------------------------------------  
 # Unix group to be set for the inventory directory.   
 # This parameter is not applicable if installing on  
 # Windows based Operating System.  
 #-------------------------------------------------------------------------------  
 UNIX_GROUP_NAME=oinstall  

In above sample response file, following parameters are used for silent installation.

INSTALL_OPTION=This parameter is used to choose the option whether you want to install Oracle GoldenGate for Oracle Database 11g or Oracle Database 12c. To install Oracle GoldenGate for Oracle Database 11g, you would need to specify its value as ORA11G or ORA12C to install Oracle GoldenGate for Oracle Database 12c. I have used the value ORA11G as I am installing Oracle Goldenate for Oracle Database 11g.

SOFTWARE_LOCATION: Specify the Oracle GoldenGate Home location for this parameter.

START_MANAGER: Specify true if you want Oracle GoldenGate Manager process to start upon completion of Oracle GoldenGate or false if you don't want the Manager process to start automatically after Oracle GoldenGate installation.

MANAGER_PORT: Specify Manager port number on which your Oracle GoldenGate Manager process will run and listen remote GoldenGate connections.

DATABASE_LOCATION: Specify Oracle Database Home location for which you are installation Oracle GoldenGate software to capture changed data from or want to replicate the changes to.

INVENTORY_LOCATION: Specify Oracle Inventory location here.

UNIX_GROUP_NAME: Specify Oracle OS user group.



Once you are done with the preparation of response file, in my case, I have placed my response file at /u01/oggcore.rsp location. Go to the Oracle GoldenGate extracted software location and run the /runInstaller in silent mode to install Oracle GoldenGate software as below.

[oracle@DRsrv Disk1]$ ./runInstaller -silent -responseFile /u01/oggcore.rsp -showProgress
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 2520 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4095 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-10-16_01-52-22PM. Please wait ...[oracle@DRsrv Disk1]$ No protocol specified
You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2017-10-16_01-52-22PM.log

Prepare in progress.
..................................................   9% Done.

Prepare successful.

Copy files in progress.
..................................................   55% Done.
..................................................   62% Done.
..................................................   69% Done.
..................................................   74% Done.
..................................................   79% Done.
..................................................   85% Done.
..........
Copy files successful.

.

.

.

Link binaries in progress.
..........
Link binaries successful.

Setup files in progress.
..................................................   90% Done.

Setup files successful.
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2017-10-16_01-52-22PM.log' for more details.

Configuring and starting manager... in progress.
..................................................   100% Done.

Configuring and starting manager... successful.
Successfully Setup Software.




The installation of Oracle GoldenGate Core was successful.

Please check '/u01/app1/oraInventory/logs/silentInstall2017-07-30_09-26-10PM.log' for more details.

Successfully Setup Software.

Once the Oracle GoldenGate Installation completes successfully, run the orainstroot.sh script located in oraInventory location.

[root@prodsrv app]# /u01/app1/oraInventory/orainstRoot.sh 
Changing permissions of /u01/app1/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app1/oraInventory to oinstall.
The execution of the script is complete.

Validating Oracle GoldenGate Installation:
----------------------------------------------------

You can verify what all Oracle products are installed on your server just by looking at your Oracle inventory file.


First identify your Oracle inventory location as below.

[root@prodsrv ~]# cat /etc/oraInst.loc
inventory_loc=/u01/app1/oraInventory
inst_group=oinstall


Once you identified Oracle Inventory location then go to your Oracle Inventory location and get the contents of inventory.xml file to see the installed oracle products on your server.

[root@prodsrv ContentsXML]# pwd
/u01/app1/oraInventory/ContentsXML


[root@prodsrv ContentsXML]# cat inventory.xml
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2011, Oracle. All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>11.2.0.3.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraDb11g_home1" LOC="/u01/app1/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="1"/>
<HOME NAME="OraHome1" LOC="/u01/app/oracle/product/11.2.0/ogg_home" TYPE="O" IDX="2"/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>
[root@prodsrv ContentsXML]#

In above contents from inventory.xml file, you can see an Oracle Home added for our Oracle GoldenGate product as highlighted.


Another simplest way to validate your Oracle GoldenGate software installation quickly is to go to Oracle GoldenGate Home and invoke Oracle GoldenGate command line Interface to check the status of Manager process.

 $cd $OGG_HOME  
 -bash-4.1$ pwd  
 /u01/app/oracle/product/11.2.0/ogg_home  

 -bash-4.1$ ./ggsci  
 Oracle GoldenGate Command Interpreter for Oracle  
 Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO  
 Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38  
 Operating system character set identified as UTF-8.  
 Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.  
 GGSCI (prodsrv) 1>  

 GGSCI (prodsrv) 1> info all  
 Program   Status   Group    Lag at Chkpt Time Since Chkpt  
 MANAGER   RUNNING                        
 GGSCI (prodsrv) 2>   

 GGSCI (prodsrv) 2> view param mgr  
 PORT 7800  
 GGSCI (prodsrv) 3>   

As you can see above that Oracle GoldenGate Installation has been performed successfully in silent mode and working fine.

Oracle GoldenGate 12c Installation

..............................I wanted to write few sample papers for my friends who just wonder to get started with Oracle Golden Gate so I thought to start writing it now for those who are looking for it.

Here I would explain the steps to install Oracle Golden Gate software for Oracle Database 11g which would be applicable to both source and target replication servers.


Golden Gate:
Oracle GoldenGate is a comprehensive software package for real-time data integration and replication in heterogeneous IT environments. The product set enables high availability solutions, real-time data integration, transactional change data capture, data replication, transformations, and verification between operational and analytical enterprise systems.

In addition to the Oracle GoldenGate core platform for real-time data movement, Oracle provides the Management Pack for Oracle GoldenGate—a visual management and monitoring solution for Oracle GoldenGate deployments—as well as Oracle GoldenGate Veridata, which allows high-speed, high-volume comparison between two in-use databases.


 - Instructions for installing Oracle Golden Gate, click here

 - Download relevant version of Oracle Golden Gate from OTN Click Here or edelivery

 - I downloaded Oracle Golden Gate 12c software and staged inside /u01 mount point on source database server as below.

 cd /u01  
 -bash-4.1$ ls -ltr  
 -rwxrwxr-x. 1 oracle oinstall 475611228 Jun 3 2016 fbo_ggs_Linux_x64_shiphome.zip 

Next step is to unzip the downloaded Oracle Golden Gate software and navigate to the Oracle Golden Gate extracted folder as below.

cd /u01/fbo_ggs_Linux_x64_shiphome/Disk1

-bash-4.1$ ls -ltr
total 16
drwxr-xr-x.  4 oracle oinstall 4096 Dec 12  2015 install
drwxr-xr-x. 11 oracle oinstall 4096 Dec 12  2015 stage
-rwxr-xr-x.  1 oracle oinstall  918 Dec 12  2015 runInstaller
drwxrwxr-x.  2 oracle oinstall 4096 Dec 12  2015 response
-bash-4.1$ 

Now, here actual installation of Oracle Golden Gate will begin as follows. Run ./runInstaller to start with Oracle Golden Gate Installation.

-bash-4.1$ ./runInstaller 
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 3221 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4095 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-07-29_04-11-58PM. Please wait ...-bash-4.1$ 

Once you start the runInstaller to install Golden Gate, following Oracle Golden Gate installation wizard will appear. Choose the appropriate Golden Gate option for database version you want to install. Here in this demo, I'm installing Oracle Golden Gate for Oracle Database 11g. Click Next.

At below screen of Golden Gate Installation wizard, choose the right Software Location which would be your Oracle Golden Gate Home, by default, Start Manager check box would be selected that means Golden Gate Manager will be started on completion of Oracle Golden Gate software installation.

In Database Location box, provide your source/target Oracle Database Home location that you want to capture or replicate to. For Manager Port option, you can choose appropriate port for your Golden Gate manager, 7809 is the default port for Oracle Golden Manager and I used that one for this demo. Click Next.

Below is the screen of Oracle Golden Gate Install wizard shows all details you provided above for it's installation. Click on Install.

Installation progresses as below.

As below screen depicts, Oracle Golden Gate Installation completed successfully.

As of now, we have installed Oracle Golden Gate software for Oracle Database 11g, now we would validate the installation of Oracle Golden Gate software as follows.

Go to Oracle Golden Gate Home and run ./ggsci to start Golden Gate Command Line Interface.

 -bash-4.1$ pwd  
 /u01/app/oracle/product/11.2.0/dbhome_1 

 
 -bash-4.1$ ./ggsci  
 Oracle GoldenGate Command Interpreter for Oracle  
 Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO  
 Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38  
 Operating system character set identified as UTF-8.  
 Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.  
 GGSCI (prodsrv) 1>   

Run info all Golden Gate command to check the status of Golden Gate Manager and extract processes.

 GGSCI (prodsrv) 1> info all  
 Program   Status   Group    Lag at Chkpt Time Since Chkpt  
 MANAGER   RUNNING                        
 GGSCI (prodsrv) 2>  

As we can see above that our Oracle Golden Gate software installed and Manager process is running so in order to configure it to get ready for database change capture, follow the below steps to create all Golden Gate sub-directories/files which are needed for it to work ahead.



Installing the Oracle GoldenGate files

1. Run the command shell/terminal.
2. Change directories to the Oracle Golden Gate Home directory.
3. From the Oracle GoldenGate directory, run the GGSCI program.
./ggsci

4. In GGSCI prompt, issue the following command to create the Oracle Golden Gate working directories.

GGSCI (prodsrv) 2> CREATE SUBDIRS  
 Creating subdirectories under current directory /u01/app/oracle/product/11.2.0/dbhome_1  
 Parameter files        /u01/app/oracle/product/11.2.0/dbhome_1/dirprm: already exists  
 Report files          /u01/app/oracle/product/11.2.0/dbhome_1/dirrpt: already exists  
 Checkpoint files        /u01/app/oracle/product/11.2.0/dbhome_1/dirchk: already exists  
 Process status files      /u01/app/oracle/product/11.2.0/dbhome_1/dirpcs: already exists  
 SQL script files        /u01/app/oracle/product/11.2.0/dbhome_1/dirsql: already exists  
 Database definitions files   /u01/app/oracle/product/11.2.0/dbhome_1/dirdef: already exists  
 Extract data files       /u01/app/oracle/product/11.2.0/dbhome_1/dirdat: already exists  
 Temporary files        /u01/app/oracle/product/11.2.0/dbhome_1/dirtmp: already exists  
 Credential store files     /u01/app/oracle/product/11.2.0/dbhome_1/dircrd: already exists  
 Masterkey wallet files     /u01/app/oracle/product/11.2.0/dbhome_1/dirwlt: already exists  
 Dump files           /u01/app/oracle/product/11.2.0/dbhome_1/dirdmp: already exists  
 GGSCI (prodsrv) 3>   

Note : I had already one setup of Oracle Golden Gate running on my system previously hence it displays that 

all directories are already exist so just ignore it here for now.

So far, we have installed Oracle Golden Gate software for Oracle Database 11g and created all required SUBDIRS for further configuration.

Note : Follow the same process to install Oracle Golden Gate software on target database server when you need to replicate change captured from source database.

In next article, I will be publishing how to configure Oracle Golden Gate for Uni-directional replication between two Oracle databases.

My Interview @Bank of America

Well, here I wanted to explain about my experience of interview process held at Bank of America. I wanted to share this experience so that other upcoming interviewees can have an idea before appearing for an interview there.

It was quite exciting recruiting process from starting to end. I received a call from recruiter, Nilima Pange, regarding the position available for the post of Sr. Analyst and agreed to schedule an interview call for a telephonic discussion after knowing the requirement they have. At scheduled time, I received interview call and it started with a warm introduction.

Excellent technical discussion started from areas of

•        General database discussions – majorly database concepts, Architecture
•         Oracle Real Application Cluster 11g (RAC)
•         Data Guard – Physical standby database, Snapshot standby and Cascade Standby database.
•         Recover Manager (RMAN) – Backup & Recovery
•         Data Pump – Tradition export/import Vs Data Pump
•         Performance Tuning and Troubleshooting
•         Automatic Storage Management (ASM) and its benefits

This interview discussion went up to 1 hour.

It was very interesting technical discussion and it ended with a little fun and good luck by the interviewer.

A day after first technical round, I received a follow up call by recruiter that I was gone through the first technical round of interview and next technical discussion would be the face to face at their Office premise, Gurgaon location.

Well, I would like to say that two things always excite me much in my life always, the first is sitting in an exam and the 2^nd one is to be interviewed, no matter if I miss the answer of some questions but it leads to a new level of learning. It actually reveals about me that where I stand once I get its result, and it works as a mentor where do I need to improve myself if I missed something out there.

I reached at the “Bank of America” venue at Gurgaon facility for the face to face technical discussion. I waited (well, your patience is your power) for my turn to get interviewed as multiple interview schedules were there and interview rooms were occupied already. It was a video conference from Hyderabad and it again started with a warm introduction and welcoming me there for the technical discussion, two interviewers were there to interview in this 2^nd round of technical discussion, and it was the final one.

And again technical discussion started with areas of followings:

•             Data Guard – Troubleshooting scenarios (Only real time problem solving approach)
•         Oracle Real Application cluster 11g Release 2(various scenarios from this area)
•         Automatic Storage Management (ASM)
•         Backup & Recovery scenarios – RMAN and Data Pump
•         Database Performance tuning scenarios
•         Database Migration Methodologies – RMAN & Data Pump and across RDBMS version
•         Oracle database Upgrade Methods - 9i>10g>11g>12c
•         Oracle Database Patching – RAC and non-RAC databases
•         Database cross-platform migration
•         Data Pump new features and performance enhancement from earlier versions.
•         Oracle Enterprise Manager 12c Cloud Control – Consolidated monitoring and its benefits.
•         Oracle Cloud – Cloud services and its merits
•         Oracle Exadata Database Machine – its unique features.
•         Linux Shell scripting,
•         Oracle RAC troubleshooting on windows platforms – ASM and RDBMS.
•         Oracle Golden Gate replication
•         Bundle of general database discussions J

This second round of interview held for around 1 hour and 28 minutes, and the most unique thing was that we (the interviewers & me) were having smile on our faces during whole interview process – it was great thing J

Finally, there was a great positive feedback from interviewer in the end of the technical discussion and they wished me all the best for further processing J

And two days after second round of technical discussion, I received call from recruiter and had discussion about HR stuffs and was invited for the offer and I was requested to submit my all required documents for further offer processing and verification and finally received my offer letter in next few days.

I thanks to the recruiter, Nilima Pange, who has been so smooth in whole recruitment process and it is amazing the way she interacts step by step….Excellent J

I will be joining Bank of America on 21 July 2017 J

Thanks,

-Raj Kumar Kushwaha

Download : Oracle Database 12c Release 2

Finally Oracle Database 12c Release 2 is available to download on-premise now.


Click Here :: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_XX"

EXPDP in Oracle 12c (12.1.0.2) fails with below error.

During performing Data Pump Export backup in a 2 node Oracle 12c RAC database, Data Pump job terminates with below error.

=============================================================

Export: Release 12.1.0.2.0 - Production on Mon Jan 23 10:16:15 2017  
 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.  
 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production  
 With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,  
 OLAP, Advanced Analytics and Real Application Testing options  
 ORA-31626: job does not exist  
 ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_05"  
 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95  
 ORA-06512: at "SYS.KUPV$FT", line 1048  
 ORA-06502: PL/SQL: numeric or value error: character string buffer too small

=============================================================

Note: - Master table for Data Pump job was not being created hence it was terminating the Data pump job at its initial startup. In above error we can see ORA-06502 that comes most of the time if we have not set streams_pool_size parameter value to enough one but in my case it was sized enough set as below to perform Data Pump Operations.

NAME                 TYPE               VALUE  
 ------------------------------------ -------------------------------- ------------------------------  
 streams_pool_size          big integer           128M  

Further we decided to clear any data pump orphaned job left in the database from earlier executions.

-- locate Data Pump master tables:  
 SQL> SELECT o.status, o.object_id, o.object_type,  
     o.owner||'.'||object_name "OWNER.OBJECT"   
  FROM dba_objects o, dba_datapump_jobs j   
  WHERE o.owner=j.owner_name AND o.object_name=j.job_name   
   AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;   
 STATUS  OBJECT_ID OBJECT_TYPE       OWNER.OBJECT  
 ------- ---------- ----------------------- ----------------------------------------  
 VALID   1434366 TABLE          SYS.SYS_EXPORT_FULL_01  
 VALID   1434139 TABLE          SYS.SYS_EXPORT_SCHEMA_01  
 VALID   1434144 TABLE          SYS.SYS_EXPORT_SCHEMA_02  
 VALID   1434149 TABLE          SYS.SYS_EXPORT_SCHEMA_03  
 VALID   1434160 TABLE          SYS.SYS_EXPORT_SCHEMA_04  
 VALID   1434377 TABLE          SYS.SYS_EXPORT_SCHEMA_05  
 VALID   1432335 TABLE          SYSTEM.SYS_EXPORT_FULL_01  
 VALID   1434155 TABLE          SYSTEM.SYS_EXPORT_FULL_02  
 VALID   1434339 TABLE          SYSTEM.SYS_EXPORT_FULL_03  
 VALID   1434344 TABLE          SYSTEM.SYS_EXPORT_FULL_04  
 VALID   1434349 TABLE          SYSTEM.SYS_EXPORT_FULL_05  
 VALID   1434354 TABLE          SYSTEM.SYS_EXPORT_FULL_06  
 VALID   1434360 TABLE          SYSTEM.SYS_EXPORT_FULL_07  
 VALID   1434372 TABLE          SYSTEM.SYS_EXPORT_FULL_08  
 VALID   1434392 TABLE          SYSTEM.SYS_EXPORT_FULL_09  
 VALID   1434408 TABLE          SYSTEM.SYS_EXPORT_FULL_10  
 VALID   1434983 TABLE          SYSTEM.SYS_EXPORT_FULL_11  
 -- Below we cleared all Orphaned Data Pump Jobs.  
 SQL> drop table SYS.SYS_EXPORT_FULL_01;  
 Table dropped.  
 SQL> drop table SYS.SYS_EXPORT_SCHEMA_01;  
 Table dropped.  
 SQL> drop table SYS.SYS_EXPORT_SCHEMA_02;  
 Table dropped.  
 SQL> drop table SYS.SYS_EXPORT_SCHEMA_03;  
 Table dropped.  
 SQL> drop table SYS.SYS_EXPORT_SCHEMA_04;  
 Table dropped.  
 SQL> drop table SYS.SYS_EXPORT_SCHEMA_05;  
 Table dropped.  
 SQL> drop table SYSTEM.SYS_EXPORT_FULL_01;  
 Table dropped.  
 SQL> drop table SYSTEM.SYS_EXPORT_FULL_02;  
 Table dropped.  
 SQL> drop table SYSTEM.SYS_EXPORT_FULL_03;  
 Table dropped.  
 SQL> drop table SYSTEM.SYS_EXPORT_FULL_04;  
 Table dropped.  
 SQL> drop table SYSTEM.SYS_EXPORT_FULL_05;  
 Table dropped.  
 SQL> drop table SYSTEM.SYS_EXPORT_FULL_06;  
 Table dropped.  
 SQL> drop table SYSTEM.SYS_EXPORT_FULL_07;  
 Table dropped.  
 SQL> drop table SYSTEM.SYS_EXPORT_FULL_08;  
 Table dropped.  
 SQL> drop table SYSTEM.SYS_EXPORT_FULL_09;  
 Table dropped.  
 SQL> drop table SYSTEM.SYS_EXPORT_FULL_10;  
 Table dropped.  
 SQL> drop table SYSTEM.SYS_EXPORT_FULL_11;  
 Table dropped.  

-- Now no orphaned data pump job left in the system.

 SQL> SELECT * FROM user_datapump_jobs;  
 no rows selected  

 SQL> SELECT owner_name, job_name, rtrim(operation) "OPERATION",  
     rtrim(job_mode) "JOB_MODE", state, attached_sessions  
  FROM dba_datapump_jobs  
  WHERE job_name NOT LIKE 'BIN$%'  
  ORDER BY 1,2;  
  2  3  4  5  
 no rows selected  

-- As, we are clean at this step so tried to run expdp job again.

 Export: Release 12.1.0.2.0 - Production on Wed Jan 18 19:33:40 2017   
 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.   
 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production   
 With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,   
 OLAP, Advanced Analytics and Real Application Testing options   
 ORA-31626: job does not exist   
 ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_05"   
 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95   
 ORA-06512: at "SYS.KUPV$FT", line 1048   
 ORA-06502: PL/SQL: numeric or value error: character string buffer too small  

Terrible, it failed again with same error.

We then decided to re-load Data Pump packages even our Catalog status was in VALID state in database registry.

 -- Decided to re-load data pump packages  
 1.Catproc.sql  

 SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql  

 2.To recompile invalid objects, if any  
 SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql  

Note :- you need to follow proper steps to run catproc.sql script in Oracle RAC database.

=============================================================

Tried to fire Data Pump Export job again but failed with same error…… L

è Tried to trace the data pump session to diagnose it in depth. You should try this step earlier than reloading data pump packages. We set below event tracing and fired Data Pump job again and it generated trace file for that point in time. Make sure you turn off the event after you are done with tracing.

Set event 6502 to trap ORA-6502 and dump a stack trace

SQL> alter system set events '6502 trace name errorstack level 3';   
 SQL> alter system set events 'sql_trace {process : pname = dw , pname =   
 dm} level=12';  

After reviewing the trace file generated, we could see, there was a TRIGGER(MONITORING_DDL) which was  causing our export job to fail at every attempt.

 SQL> select owner, object_name, object_type, status from dba_objects where lower(object_name) like '%monitoring_ddl';  
 OWNER      OBJECT_NAME          OBJECT_TYPE       STATUS  
 --------------- ------------------------------ ----------------------- -------  
 SYSTEM     MONITORING_DDL       TRIGGER         VALID  
 SYSTEM     MONITORING_DDL       TABLE          VALID  
 3 rows selected.  

-- We Disabled the Trigger..............

We decided to disable to trigger as it was preventing DDLs operations to be performed other than SYS and SYSTEM users.

SQL> alter trigger system.MONITORING_DDL disable;  
 Trigger altered.  

Finally, tried to run Data Pump Export Job again and it went fine.

Export: Release 12.1.0.2.0 - Production on Mon Jan 23 12:37:53 2017  
 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.  
 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production  
 With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,  
 OLAP, Advanced Analytics and Real Application Testing options  
 FLASHBACK automatically enabled to preserve database integrity.  
 Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** directory=EXPORT_DIR dumpfile=expdp_rac1_2017-01-23.dmp logfile=expdp_rac1_2017-01-23.log full=y metrics=y  
 Startup took 8 seconds  
 Estimate in progress using BLOCKS method...  
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA  
    Estimated 18894 TABLE_DATA objects in 357 seconds  
 Total estimation using BLOCKS method: 120.9 GB  
 Processing object type DATABASE_EXPORT/TABLESPACE  
    Completed 175 TABLESPACE objects in 18 seconds  

Hope it would help someone if gets in same trouble...!!