Skip navigation.


Syndicate content DBASolved
Helping Oracle DBAs solve problems
Updated: 14 hours 57 min ago

Setup Streams Performance Advisor (UTL_SPADV) for #GoldenGate

Mon, 2015-01-12 14:47

With Oracle “merging” Oracle GoldenGate into Oracle Streams (or vise-versa), capturing statitics on the intergrated extract (capture) or integrated replicat (happy) will be needed.  In order to do this, the Streams Performance Advisor (UTL_SPADV) can be used.  Before using the Stream Performance Advisor, it needs to be configured under the Streams Administrator, i.e. Oracle GoldenGate user.  In my test lab, I use a user named GGATE for all my Oracle GoldenGate work.

Configure user for UTL_SPADV:

The Oracle user (GGATE) needs to be granted priviliges to run the performance advisor.  This is done by granting permissions through DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE.

SQL> conn ggate/test123
SQL> exec dbms_streams_auth.grant_admin_privilege(‘GGATE’);

Install performance advisor:

After granting the requried permissions to the Oracle user, then the UTL_SPADV package can be installed.

SQL> conn ggate/test123
SQL> @?/rdbms/admin/utlspadv.sql

Gather statistics:

Now that the UTL_SPADV package has been installed, the package can be used from sql*plus to gather statistics on the integrated extract/replicat.

SQL> conn ggate/test123
SQL> exec utl_spadv.collect_stats;

Note: This will take some time to run.  From my tests, it appears to complete as my test sessions disconnect.  

Display statistics:

Once the statistics have been gathered, they can be displayed using the SHOW_STATS option.

SQL> conn ggate/test123
SQL> set serveroutput size 50000
SQL> exec utl_spadv.show_stats;

Statistics Output:

The output will be displayed through sql*plus and will be displayed in intervals of one minute.  Before the display of the statistics start it the advisor provides a legend at the top to help dechiper the output.


<statistics>= <capture> [ <queue> <psender> <preceiver> <queue> ] <apply>

<capture>   = ‘|<C>’ <name> <msgs captured/sec> <msgs enqueued/sec> <latency>

   ‘LMR’ <idl%> <flwctrl%> <topevt%> <topevt>

   ‘LMP’ (<parallelism>) <idl%> <flwctrl%> <topevt%> <topevt>

   ‘LMB’ <idl%> <flwctrl%> <topevt%> <topevt>

   ‘CAP’ <idl%> <flwctrl%> <topevt%> <topevt>

   ‘CAP+PS’ <msgs sent/sec> <bytes sent/sec> <latency> <idl%>

<flwctrl%> <topevt%> <topevt>

<apply>     = ‘|<A>’ <name> <msgs applied/sec> <txns applied/sec> <latency>

   ‘PS+PR’ <idl%> <flwctrl%> <topevt%> <topevt>

   ‘APR’ <idl%> <flwctrl%> <topevt%> <topevt>

   ‘APC’ <idl%> <flwctrl%> <topevt%> <topevt>

   ‘APS’ (<parallelism>) <idl%> <flwctrl%> <topevt%> <topevt>

<queue>     = ‘|<Q>’ <name> <msgs enqueued/sec> <msgs spilled/sec> <msgs in


<psender>   = ‘|<PS>’ <name> <msgs sent/sec> <bytes sent/sec> <latency> <idl%>

<flwctrl%> <topevt%> <topevt>

<preceiver> = ‘|<PR>’ <name> <idl%> <flwctrl%> <topevt%> <topevt>

<bottleneck>= ‘|<B>’ <name> <sub_name> <sessionid> <serial#> <topevt%> <topevt>

<msgs in
PATH 1 RUN_ID 1 RUN_TIME 2015-JAN-12 15:17:31 CCA Y
| OGG$CAP_EXTI 31 31 0 LMR 99.7% 0% 0.3% “” LMP (2) 199.7% 0% 0.3% “” LMB
99.3% 0% 0.3% “”  CAP 99.7% 0% 0.3% “” | “GGATE”.”OGG$Q_EXTI” 0.01 0.01 0

PATH 1 RUN_ID 2 RUN_TIME 2015-JAN-12 15:18:32 CCA Y
| OGG$CAP_EXTI 37 33 1 LMR 98.4% 0% 1.6% “” LMP (2) 198.4% 0% 1.6% “” LMB
98.4% 0% 1.6% “” CAP 100% 0% 0% “” | “GGATE”.”OGG$Q_EXTI” 0.01 0.01 0 |

If you want to find out more on how to decipher these statistics, the legend is located


Filed under: Golden Gate, Performance
Categories: DBA Blogs

Oracle GoldenGate Processes – Part 4 – Replicat

Thu, 2015-01-08 15:00

The replicat process is the apply process within the Oracle GoldenGate environment.  The replicat is responsible for reading the remote trail files and applying the data found in cronilogical order.  This ensures that the data is applied in the same order it was captured.  

Until recently there was only one version of a replicat, that version was the classic version.  As of, there are now three distinct versions of a replicat.  These replicat types are:

  • Classic Mode
  • Coordinated Mode
  • Integrated Mode

Each of on these modes provide some sort of benefit depending on the database being applied to.  Oracle is pushing everyone to a more integrated approach; however, you have to be on database version at a minimum.  

To configure the replicat process is similar to the extract and data pump processes.

Adding a Replicat:

From GGSCI (classic):

$ cd $OGG_HOME
$ ./ggsci
GGSCI> add replicat REP, exttrail ./dirdat/rt

Note:  The add command is assuming that you already have a checkpoint table configured in the target environment.

Edit Replicat parameter file:


$ cd $OGG_HOME
$ ./ggsci
GGSCI> edit params REP

From Command Line:

$ cd $OGG_HOME
$ cd ./dirprm
$ vi REP.prm

Example of Replicat Parameter file:

SETENV (ORACLE_HOME=”/u01/app/oracle/product/11.2.0/db_3″)
USERID ggate, PASSWORD ggate
DISCARDFILE ./dirrpt/REP.dsc, append, megabytes 500

Start the Replicat process:

Depending on if you are starting the replicat for the first time or not; how you start is going to be similar yet different.

To star the Replicat after an inital load:

$ cd $OGG_HOME
$ ./ggsci
GGSCI> start replicat REP, atcsn [ current_scn ]

Note: The current_scn needs be obtained from the source database prior to doing the inital load of data to the target.  This ensure the consistancy of the data and provides a starting point for the replicat to start applying data from.

To start Replicat normally:

$ cd $OGG_HOME
$ ./ggsci
GGSCI> start replicat REP

Stop the Replicat process:

Stop replicat normally:

$ cd $OGG_HOME
$ ./ggsci
GGSCI> stop replicat REP


Filed under: Golden Gate
Categories: DBA Blogs

Oracle GoldenGate Processes – Part 3 – Data Pump

Thu, 2015-01-08 14:00

The Data Pump group is an secondary extract group that is used to help send data over a network.  Although a data pump is another extract group, don’t confuse it with the primary extrat group. The main purpose of the data pump extract is to write the captured data over the network to the remote trail files on the target system.  

Note: if the data pump is not confgured then the primary extract group will write directly to the remote trail file.

Why would you want to use a data pump process?  Two advantage of using a data pump are:

  • Protects against network failures
  • Helping to consolidate data, from multiple sources, into a single remote trail file

The data pump process, just as the extract process, uses a parameter file to run the process.  The parameter file can be edited either before or after adding the process to the Oracle GoldenGate environment.

Adding a Data Pump:


$ cd $OGG_HOME
$ ./ggsci
GGSCI> add extract PMP, exttrailsource ./dirdat/lt
GGSCI> add rmttrail ./dirdat/rt, extract PMP, megabytes 200

Note: In the example above notice that the data pump is reading from ./dirdat/lt and then writing to ./dirdat/rt on the remote server.

Edit Data Pump parameter file:


$ cd $OGG_HOME
$ ./ggsci
GGSCI> edit params PMP

From Command Line:

$ cd $OGG_HOME
$ cd ./dirprm
$ vi ./PMP.prm

Example of Data Pump parameter file:




RMTTRAIL ./dirdat/rt


Start/Stop the Data Pump:

Start the Data Pump:

$ cd $OGG_HOME
$ ./ggsci
GGSCI> start extract PMP

Stop the Data Pump:

$ cd $OGG_HOME
$ ./ggsci
GGSCI> stop extract PMP


Filed under: Golden Gate
Categories: DBA Blogs

Oracle GoldenGate Processes – Part 2 – Extract

Thu, 2015-01-08 13:00

The extract process of Oracle GoldenGate is used to perform change data capture from the source database.  The extract can be used to read the online transaction log (in Oracle the online redo logs) or the associated archive logs.  The data that is extracted from the source database is then placed into an trail file (another topic for a post) for shipping to the apply sided. 

To configure an extract process there needs to be a parameter file associated with it.  The parameter file can be edited after adding the extract to the Oracle GoldenGate environment if needed.  In order to configure an extract it needs to be added to the environment and assigned a local trail file location.

Adding an Extract and Local Trail File:

Using GGSCI:

$ cd $OGG_HOME
$ ./ggsci
GGSCI> add extract EXT, tranlog, begin now
GGSCI> add exttrail ./dirdat/lt, extract EXT, megabytes 200

Note: The trail file is required to be prefixed with a two letter prefix.  

Edit Extract Parameter File:


$ cd $OGG_HOME
$ ./ggsci
GGSCI> edit params EXT

Edit Extract Parameter File from Command Line:

$ cd $OGG_HOME
$ cd ./dirprm
$ vi ./ext.prm

Example of Extract Parameter File:

USERID ggate, PASSWORD ggate 
SETENV (ORACLE_HOME=”/u01/app/oracle/product/11.2.0/db_3″)
EXTTRAIL ./dirdat/lt

Start/Stop the Extract:

Start Extract:

$ cd $OGG_HOME
$ ./ggsci
GGSCI> start extract EXT

Stop Extract:

$ cd $OGG_HOME
$ ./ggsci
GGSCI> stop extract EXT


Filed under: Golden Gate
Categories: DBA Blogs

Oracle GoldenGate Processes – Part 1 – Manager

Thu, 2015-01-08 12:00

Oracle GoldenGate is made up of processes that are used to ensure replication.  These processes include a manager process, an extract and a replicat process. The primise of this post is to focus on the manager process.

In order to configure and run an Oracle GoldenGate enviornment, a manager process must be running on all the source, target, and intermediary servers in the configuration.  This is due to the manager process being the controller process for the Oracle GoldenGate processes, allocates ports and performs file maintenance.  The manager process peforms the following functions within the Oracle GoldenGate isntance:

  • Starts Processes
  • Starts Dynamic Processes
  • Start the Controller process
  • Manage the port numbers for the processes
  • Trail File Management
  • Create reports for events, error and threshold

Note: There is only one manager processes per Oracle GoldenGate instance.

Configure Manager Process
Before a manager process can be started it needs to be configured.  There are many different parameters than can be used in the manager parameter file, but the only required one is PORT parameter.  The default port for a manager is 7809.  In order to edit the manager parameter file, it can be done either from the command line or from within the GGSCI utility.

Edit via command line:

$ cd $OGG_HOME/dirprm
$ vi mgr.prm

Edit via GGSCI:

GGSCI> edit params mgr

Example of a Manager parameter file:

PORT 15000

Start the Manager Process

Starting the manager process is pretty simple.  The process can be started either from the command line or from the GGSCI utility.

Start from command line:

$ cd $OGG_HOME
$ mgr paramfile ./dirprm/mgr.prm reportfile ./dirrpt/mgr.rpt

Start from GGSCI:

$ cd $OGG_HOME
$ ./ggsci
GGSCI> start manager  
GGSCI> start mgr

Stop the Manager Process

Stopping the manager process is pretty simple as well (from the GGSCI).

Stop from GGSCI:

$ cd $OGG_HOME
$ ./ggsci
GGSCI> stop manager [ ! ]


Filed under: Golden Gate
Categories: DBA Blogs

Access Oracle GoldenGate JAgent XML from browser

Tue, 2015-01-06 09:26

There are many different ways of monitoirng Oracle GoldenGate; I have posted about many of these in earlier blog posts.  Additionally, I have talked about the different ways of monitoring Oracle GoldenGate at a few conferences as well.  (The slides can be found on my slideshare site if wanted).  In both my blog and presentations I highlight many different approaches; yet I forgot one that I think is really cool!  This one was shown to me by an Oracle Product Manager before Oracle Open World 2014 back in October (yes, I’m just now getting around to writing about it).  

This approach is using the Oracle GoldenGate Manager (port) to view a user friendly version of the XML that is passed by the Oracle Monitor Agent (JAgent) to monitoring tools like Oracle Enterprise Manager or Oracle GoldenGate Director.  This approach will not work with older versions of the JAgent.

Note: The Oracle Monitor Agent (JAgent) used in this approach is version  It can be found here.  

Note: There is a license requirement to use this approach since this is part of the Management Pack for Oracle GoldenGate.  Contact you local sales rep for more info.

After the Oracle Monitor Agent (JAgent) is configured for your environment, the XML can be accessed via any web browser.  Within my test enviornment, I have servers named OEL and FRED.  The URLs needed to to view this cool feature are:



As you can see, by using the port number (15000) of the Manager process, I can directly tap into the information being feed to the management tools for monitoring.  The “groups” directory places you at the top level of the monitoring stack.  By clicking on a process groups, this will take you down into the process group and show additional items being monitored by the JAgent.

In this example, you are looking at the next level down for the process EXT on OEL.  At this point, you can see what is available: monitoring points, messages, status changes and associated files for the extract process.


Digging further into the stack, you can see what files are associated with the process.  (This is an easy way to identify parameter files without having to go directly to the command line).



As you can see, the new Oracle Monitor Agent (JAgent) provides you another way of viewing your Oracle GoldenGate environment without needing direct access to the server.  Although this is a cool way of looking at a Oracle GoldenGate environment, it does not replace traditionall monitoring approaches.  

Cool Tip: The OS tool “curl” can be used to dump similar XML output to a file (showed to me by the product team).

$ curl --silent | xmllint --format -

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="/style/registry.xsl"?>
<registry xmlns:xsi="" xsi:noNamespaceSchemaLocation="">
<process name="PMP" type="4" status="3"/>
<process name="EXT" type="2" mode="1" status="3"/>
<process name="MGR" type="1" status="3"/>

In my opinion, many of the complants about the original version of the JAgent have been addressed with the latest release of the Oracle Monitor Agent (JAgent).  Give it a try!

Filed under: Golden Gate
Categories: DBA Blogs

Upgrade Oracle GoldenGate to

Fri, 2014-11-28 22:33

Recently, I’ve been engaged in a conversation on the OTN community pages about upgrading Oracle GoldenGate 12c from to the  During the discussion I mentioned that you can upgrade using the Oracle Universal Installer (OUI) that is now available with Oracle GoldenGate 12c.  The upgrade process I’m going to show you here is an in-place upgrade of Oracle GoldenGate 12c for Oracle Database 12c.

Note: Before doing any upgrades of Oracle GoldenGate, make sure to stop all processes and backup your existing binaries and associated files needed for your environment.

The first thing that needs to be done is to download the binaries from either or My Oracle Support (Image 1).

Image 1:
















After downloading the new binaries to a location where they can be extracted; they need to be unzipped.

 $ unzip ./ -d ./ggate12121 

Once the binaries are unzipped, lets go into the ./ggate12121 directory to find the runInstaller.  On my system the runInstaller is found at this location.


Before running the runInstaller, I need to make sure that all my Oracle GoldenGate processes are down.  Since this is on my target (test) system, that means the manager, all replicats and collector processes should be stopped.  A simple “ps -ef” command can help identify what is running.

 $ ps -ef | grep dirpm oracle 2401 1 0 22:47 ? 00:00:00 ./mgr PARAMFILE /opt/app/oracle/product/12.1.2/oggcore_1/dirprm/MGR.prm REPORTFILE /opt/app/oracle/product/12.1.2/oggcore_1/dirrpt/MGR.rpt PROCESSID MGR USESUBDIRS oracle 2407 2401 1 22:47 ? 00:00:02 /opt/app/oracle/product/12.1.2/oggcore_1/replicat PARAMFILE /opt/app/oracle/product/12.1.2/oggcore_1/dirprm/REP.prm REPORTFILE /opt/app/oracle/product/12.1.2/oggcore_1/dirrpt/REP.rpt PROCESSID REP USESUBDIRS $ ps -ef | grep server oracle    2486  1848  0 22:51 pts/0    00:00:00 grep server 

After identifying the processes, they need to be stopped from within GGSCI.

 Oracle GoldenGate Command Interpreter for Oracle Version 17185003 OGGCORE_12. Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54 Operating system character set identified as UTF-8. Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. GGSCI ( 1&gt; info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING &lt;br /&gt;REPLICAT RUNNING REP 45:06:32 00:04:35 GGSCI ( 2&gt; stop er * Sending STOP request to REPLICAT REP ... Request processed. GGSCI ( 3&gt; stop mgr ! Sending STOP request to MANAGER ... Request processed.&lt;br /&gt;Manager stopped. GGSCI ( 9&gt; info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED &lt;br /&gt;REPLICAT STOPPED REP 45:14:08 00:00:05 

To verify that everything has been stopped, the “ps -ef” command can be ran to verify. After verifying that everything is stopped, the runInstaller can be used to start the OUI for the upgrade (Image 2).

Image 2:









Notice that there are 5 steps to the OUI.  Also notice that there is not an “upgrade” option.  Not to worry, we can still perform the upgrade.  Since this is an upgrade of Oracle GoldenGate 12c for Oracle Database 12c, make sure to select the option for Oracle Database 12c (default), then click the next button.

On the Installation screen, the location of the existing binaries need to be selected from the drop down box for Software Location.  In the example, the location is /opt/app/oracle/product/12.1.2/oggcore_1.  Also notice in image 3, that I do not want the manager process to start.  After making sure everything is correct and as expected, click Next.

Note: The information on this screen is read from the oraInventory files.  Make sure you know where the oraInventory located and set as needed.

Image 3:












The wizard now moves to the Summary screen (Image 4).  On this screen, the typical information is seen. Click Install when ready.

Image 4:










Image 5 shows the progress of the install/upgrade.

Image 5:













After the install/upgrade is done (Image 6), we get a nice message saying that it was successful.

Image 6:









Once the upgrade is complete, then the Oracle GoldenGate processes (manger, replicats) can be restarted.  Notice that the version of Oracle GoldenGate 12c that is running now is

 [oracle@ggtest2 Disk1]$ cd /opt/app/oracle/product/12.1.2/oggcore_1 [oracle@ggtest2 oggcore_1]$ pwd /opt/app/oracle/product/12.1.2/oggcore_1 [oracle@ggtest2 oggcore_1]$ . oraenv ORACLE_SID = [oracle] ? remote12c The Oracle base has been set to /opt/app/oracle [oracle@ggtest2 oggcore_1]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version OGGCORE_12. Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34 Operating system character set identified as UTF-8. Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.</p>
<p>GGSCI ( 1&gt; info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED &lt;br /&gt;REPLICAT STOPPED REP 45:14:08 00:24:08 GGSCI ( 2&gt; start mgr Manager started. GGSCI ( 3&gt; start replicat rep Sending START request to MANAGER ... REPLICAT REP starting GGSCI ( 4&gt; info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP 45:39:08 00:00:01 

As you can tell the upgrade from Oracle GoldenGate 12c ( to Oracle GoldenGate 12c ( can be completed using the Oracle Universal Installer that now comes with Oracle GoldenGate 12c.  I wish Oracle would give an option for an upgrade in the OUI, it would cut down on some confusion when it comes to upgrades with Oracle GoldenGate using the OUI.


Filed under: Golden Gate
Categories: DBA Blogs

Repair Replicat after mount point name change

Sat, 2014-11-15 11:06

Working on Oracle GoldenGate can be an interesting adventure. In such a case, I have been doing some migration work for a client. Half way though the migration, the target system ran out of resources need to create the tablespaces and store files export and trail files (i.e. disk space and a story for another time). The impact to the migration was that everything had to stop until resources were allocated.

Part of the allocation of resources was to change the mount point name. If you know anything about Oracle GoldenGate Replicats, using a static mount point is not the best approach (slipped my mind at the time); however, I made this mistake. When the mount point name changed, all the replicats broke because they couldn’t locate the trail files where specified.


When I initially setup the replicat I used a static mount point. Let’s take a look at the create replicat statement I used initially:

--Add Replicat Process
ADD REPLICAT REPM01, EXTTRAIL /orabackup/ggate/trail/ars/ra, DESC "Replicat process for a schema”

As you can see the replicat is looking for the “ra” trail files on the “/orabackup” mount point.


During the allocation of space the mount point “/orabackup” was changed to “/orabkup”. How does this affect the replicat? Simple, the replicat will through an OGG-01091 error stating that it coudn’t find the trail file.

ERROR OGG-01091 Unable to open file “/orabackup/ggate/trail/ars/ra000000″ (error 2, No such file or directory).


The solution to fixing this problem is to capture the last CSN number from the Checkpoint table.

SQL> select group_name, rba, seqno, log_cmplt_csn from checkpoint where group_name = 'REPM01';

--------          ---------- ---------- -----------------------------------
REPM01       544013          1       11108080706671

Once the last completed CSN has been identified, then the replicat can be dropped, recreated with the new path to the trail file.

GGSCI> dblogin userid ggate password
GGSCI> delete replicat REPM01
GGSCI> add replicat REPM01, EXTTRAIL /orabkup/ggate/trail/ars/ra, DESC "Replicat process for a schema”
GGSCI> start replicat REPM01, atcsn 11108080706671
GGSCI> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

REPLICAT    RUNNING     REPM01      00:00:00      00:00:06

Lesson Learned:

When setting up locations for your trail files make sure they are not static locations. Realitve locations should be used. In most Oracle GoldenGate architectures the “dirdat” directory under $OGG_HOME is used for trails files; however, if you need more space for trail files the “dirdat” directory can be linked to a directory on a larger mount point. This will keep the replicat consistant for trail file purposes and make it easier to manage the names of the mount point if the static name changes.


Filed under: Golden Gate
Categories: DBA Blogs

ECO 2014 and Slides

Mon, 2014-11-10 07:30

Last week I attended the East Coast Oracle User Group conference, also known as ECO, in Raleigh, NC.  This being my first time at ECO, it was a good event for being a two day conference.  The low-key environment provided a nice, comfortable environment for interaction between the speakers and those in attendance.  If you ever have the chance to catch this conference, it would be a good one to attend.

What you can expect from ECO, is to see great speakers, both local to Raleigh and from around the country. There seems to be opportunities to see also see speakers that we all hear about and would like to see at some point.  As one of the speakers at this year’s conference, I have to say it was nice to have great attendance for my session on Oracle GoldenGate 12c Conflict Detection and Resolution.  My session was scheduled for 45 minutes; due to discussions throughout the session it lasted about 65 minutes.  Although the session ran over, it was exciting to see so many people wantiong to know more about Oracle GoldenGate and what benefits it provides to an organization.

If you would like to see the slides from my ECO session, they can be found here.

Lastly, I would like to say that ECO is one of the smaller user group conferences which seem to draw some great speakers.  Check it out next year!


Filed under: General
Categories: DBA Blogs

SQL Developer and Big Data Appliance (sort of)

Sun, 2014-11-09 20:55

Recently, Enkitec received an Oracle Big Data Appliance (BDA) for our server farm in Dallas (Thanks Accenture!).  With this new addition to the server farm, I’m excited to see what the BDA can do and how to use it.  Since I use Oracle SQL Developer for a lot of things, I figure I better see if I can connect to it…. wait I don’t have access yet, darn!  Simple solution, I’ll just use the Oracle Virtual Box VM (Big Data Lite) to make sure my that my SQL Developer can connect when I eventually get access.

The first thing I needed is download the Big Data Lite VM.  It can be downloaded from the Oracle Technology Network (here). The second thing I needed to do was download the connectors for HIVE from Cloudera, use the version for the platform you need (here).

After downloading the Cloudera connectors for HIVE, these needed to be unzipped in a location that can be accessed by SQL Developer. Since I’m on a MacBook Pro, I unzipped them in this location:

$ cd ~/Downloads
$ unzip ./ -d /Users/Bobby/Oracle/connectors
$ cd /Users/Bobby/Oracle/connectors
$ ls -ltr
total 21176
-rw-r--r--@ 1 Bobby  staff  5521341 Sep 10 15:16
-rw-r--r--@ 1 Bobby  staff  5317239 Sep 10 15:16
$ unzip ./ -d ./Hive
$ cd ./Hive
$ ls -ltr
-r--r--r--@ 1 Bobby  staff  1083758 Sep  8 17:28 Cloudera - Simba JDBC Driver for Hive Install Guide.pdf
-rw-r--r--@ 1 Bobby  staff     9679 Sep  8 23:28 slf4j-log4j12-1.5.8.jar
-rw-r--r--@ 1 Bobby  staff    23445 Sep  8 23:28 slf4j-api-1.5.8.jar
-rw-r--r--@ 1 Bobby  staff   367444 Sep  8 23:28 log4j-1.2.14.jar
-rw-r--r--@ 1 Bobby  staff   347531 Sep  8 23:28 libthrift-0.9.0.jar
-rw-r--r--@ 1 Bobby  staff   275186 Sep  8 23:28 libfb303-0.9.0.jar
-rw-r--r--@ 1 Bobby  staff   294796 Sep  8 23:28 ql.jar
-rw-r--r--@ 1 Bobby  staff   596600 Sep  8 23:28 hive_service.jar
-rw-r--r--@ 1 Bobby  staff  7670596 Sep  8 23:28 hive_metastore.jar
-rw-r--r--@ 1 Bobby  staff  2972229 Sep  8 23:28 TCLIServiceClient.jar
-rw-r--r--@ 1 Bobby  staff  1656683 Sep  8 23:29 HiveJDBC4.jar

Once the connectors are extracted, SQL Developer needs to know which HIVE connector to use.  In this case the JDBC4 connector is required.  Unzipped the JDBC4 set of files into a directory, in my case I’m using a directory called Hive.

In order to tell SQL Developer which connector to use, it needs to be specified in the interface by doing the following:

  1. Start SQL Developer
  2. Oracle SQL Developer -> Preferences
  3. Database -> Third Party JDBC -> Add Entry
  4. Restart SQL Developer

After restarting SQL Developer, we now see an option on the connection screen for Hive.





Now SQL Developer is ready to connect to a Big Data Appliance, oh I mean to my VM for Big Data Lite :), lets setup a connection and see if we can connect.  Since I’m connecting to a Virtual Box VM, I need to setup some ports to be used between my MacBook and the VM.  In this case, I have setup a SQL port on 15211 which maps to the standard database port of 1521.  For the Hive connection I’ve setup 10001 which maps to port 10000.



With the ports put in place, now I can setup SQL Developer to connect to the Hive on the Big Data Lite VM.  You will notice that on the username, password, server name and port is needed.  The database parameter is optional when connecting to a Bid Data Hive.





Once the connection is configured, I can login to the Hive and review what tables are listed in the Big Data Lite VM.









The end result is that now I can visualize the data that is in a Big Data Appliance/Lite VM and begin to interact with objects defined within.


Filed under: BigData
Categories: DBA Blogs

Securing Oracle GoldenGate Database Login in a Credential Store

Fri, 2014-11-07 17:44

Security is always a big deal.  In setting up Oracle GoldenGate the capture (extract) and apply (replicat) parameter files need to be configured to log in to the database which they will perform operations.  In order to do this the Oracle GoldenGate User name and password need to be provided in the parameter files.  Example 1 shows how the database login is traditionally done in a extract or replicat parameter file.

Example 1:

--Oracle Login
USERID ggate, PASSWORD ggate

To make this process login information more secure, we can create a userid alias that the extract or replicat process can use to log into the database.  In order to create a login alias, a credential store needs to be create.  Below are the steps to create the credential store and associated aliases.

After logging into the GoldenGate Service Command Interface (GGSCI), a credential store needs to be created.  By default the credential store will be kept in the “dircrd” directory undert the $OGG_HOME.

Create the credential store:

GGSCI ( 1> add credentialstore

Credential store created in ./dircrd/.

With the credential store created, now an alias can be created for the gguser.

GGSCI ( 2> alter credentialstore add user ggate, password ggate alias aggate

Credential store in ./dircrd/ altered.

The extract or replicat parameter files need to be updated to use the new alias.  Once the update is done the associated process needs to be restarted.

--Oracle Login


After restarting the process, the Oracle GoldenGate login is secure.

Note: If the password for the Oracle GoldenGate User changes, the alias in the credential store will need to be updated.


Filed under: Golden Gate
Categories: DBA Blogs

OGG-01742 when trying to start extract process

Tue, 2014-10-28 13:57

Every once in awhile I do something in my test environments to test something else, then I go back to test core functions of the product; in this case I was testing a feature of Oracle GoldenGate 12c.  Earlier in the day, I had set the $ORACLE_HOME enviornment variable to reference the Oracle GoldenGate home.  Instead of closing my session and restarting, I just started GGSCI and the associated manager.  To my surprise, the extracts that I had configured wouldn’t start.  GGSCI was issuing an OGG-01742 error about the “child process is no longer alive” (Image 1).

Image 1:

As I was trying to figure out what was going on, I checked the ususal files (ggserr.log and report files) for any associated errors.  I didn’t find anything that was out of place or lead me to believe there was a  problem with Oracle GoldenGate.  The next thing I needed to identify was if the environment was configured correctly.  In doing this, I first checked the session environment variables (Image 2) related to the Oracle database.

Image 2:

As you can see, I had set the ORACLE_HOME equal to my OGG_HOME.  After seeing this, I remembered that I had set it this way for a specific test.  Since the ORACLE_HOME was set to OGG_HOME, the Oracle GoldenGate Extract (capture) process didn’t know where to get the needed libraries for the database.  

The Fix:

To fix this issue, I just needed to reset my environment to reference the ORACLE_HOME and ORACLE_SID correctly.  In Linux enviornment, I like to use “. oraenv” to do this (Image 3).

Image 3:

Now that I have reset my Oracle environment to point to the database, I should be able to start my extracts and not get any error messages related to OGG-01742 (Image 4).

Note: It appears that if the manager process has AUTOSTART and/or AUTORESTART set, the manager process needs to be restarted before the OGG-01742 message will go away.

Image 4:

With my extracts started now, I’ve got all my Oracle GoldenGate processes back up and running (Image 5).

Image 5:

In a nutshell, if you get OGG-01742 error while trying to start or restart an extract (capture) process; just reset your Oracle environment parameters!


Filed under: Golden Gate
Categories: DBA Blogs

Use OPatch to check Oracle GoldenGate version

Tue, 2014-10-28 11:08

Recently on I was strolling the OTN message boards and came across a question about identifying the version of Oracle GoldenGate using OPatch.  This was the second time I came across this question; with that I decided to take a look and see if Oracle GoldenGate information could be retrieved using opatch.

Initially I thought that identifing the Oracle GoldenGate version could only be done by logging into GGSCI and reviewing the header information.  To do this, just setup the Oracle environment using “. oraenv”.

Note: “. oraenv” will use the /etc/oratab file to set the ORACLE_HOME and ORACLE_SID parameters and ensure that Oracle GoldenGate has access to the library files needed.

Once the enviornment is set, the GGSCI can be used to start the interface.

[oracle@db12cgg ogg]$ . oraenv
ORACLE_SID = [oragg] ?
The Oracle base has been set to /u01/app/oracle
[oracle@db12cgg ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version OGGCORE_12.
Linux, x64, 64bit (optimized), Oracle 12c on Aug  7 2014 10:21:34
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI ( 1>

Notice in the code above, that the version of Oracle GoldenGate being ran is for Linux x64.

How can this be done through OPatch?  The same information can be gathered using the opatch utility.  Ideally, you will want to use opatch from the $GG_HOME/OPatch directory.

Note: $ORACLE_HOME needs to be set to $OGG_HOME before correct opatch inventory will be listed.  If $ORACLE_HOME is set for the database, the opatch will return information the database not Oracle GoldenGate.

After making sure that the $ORACLE_HOME directory is pointed to the correct $GG_HOME,  the inventory for Oracle GoldenGate can be retrieved using “./opatch lsinventory”.

[oracle@db12cgg ogg]$ pwd
[oracle@db12cgg ogg]$cd OPatch
[oracle@db12cgg OPatch]$./opatch lsinventory
Invoking OPatch

Oracle Interim Patch Installer version
Copyright (c) 2011, Oracle Corporation.  All rights reserved.
Oracle Home      : /u01/app/oracle/product/12.1.2/ogg
Central Inventory : /u01/app/oraInventory
  from          : /etc/oraInst.loc
OPatch version    :
OUI version      :
Log file location : /u01/app/oracle/product/12.1.2/ogg/cfgtoollogs/opatch/opatch2014-10-28_11-18-49AM.log
Lsinventory Output file location : /u01/app/oracle/product/12.1.2/ogg/cfgtoollogs/opatch/lsinv/lsinventory2014-10-28_11-18-49AM.txt

Installed Top-level Products (1):

Oracle GoldenGate Core                                    

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.


OPatch succeeded.

As you can tell, I was able to find the same information using OPatch without having to go to the GGSCI utility.

Note: I have not had a chance to check this against Oracle GoldenGate 11g and earlier. This may be something specific to Oracle GoldenGate 12c.  Will verify at a later time.



Filed under: Golden Gate
Categories: DBA Blogs