Skip navigation.


Syndicate content DBASolved
Helping Oracle DBAs solve problems
Updated: 23 hours 48 min ago

Recover from ORA-01172 & ORA-01151

Tue, 2016-01-19 07:48

This morning I was working on an Oracle Management Repository (OMR) for a test Enterprise Manager that is used by a few consultants I work with. When I logged into the box, I found that the OMR was down. When I went to start the database, I was greeted with ORA-01172 and ORA-01151.

These errors basically say:

ORA-01172 – recovery of thread % stuck at block % of file %
ORA-01151 – use media recovery to recover block, restore backup if needed

So how do I recover from this. The solution is simple, I just needed to perform the following steps:

1. Shutdown the database

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

2. Mount the database

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.0033E+10 bytes
Fixed Size 2934696 bytes
Variable Size 1677723736 bytes
Database Buffers 8321499136 bytes
Redo Buffers 30617600 bytes
Database mounted.

3. Recover the database

SQL> recover database;
Media recovery complete.

4. Open the database with “alter database”

SQL> alter database open;
Database altered.

At this point, you should be able to access the database (OMR) and then have the EM environment up and running.


Filed under: Database
Categories: DBA Blogs

Defining Resources in #GoldenGate Studio 12c

Fri, 2016-01-15 16:12

As I’ve been working with the beta of GoldenGate Studio 12c, I have tried to do simple things to see what will break and what is needed to make the process work. One of the things that I lilke about the studio is that prior to creating any solutions, mappings or projects, you can define what databases and GoldenGate instances will be used during the design process. What I want to show you in this blog post is how to create the database resource and the GoldenGate instance resource.

Creating a Resource:

To create a database resource, after opening GoldenGate Studio, go to the Resource tab. On this tab, you will see that is it empty. This is because no resources have been created yet.

In the left hand corner of the Resources tab, you should see a folder with a small arrow next to it. When you click on the arrow, you are provided with a context menu that provides you with three options for resources (Databases, Global Mappings, and GoldenGate Instances).

Database Resources:

Now that you know how to select what resrouce you want to create, lets create a database resource. To do this, select the database resource from the context menu. This will open up a one page wizard/dialog for you to fill out the connection information for the database you want to use as a resource.

You will notice there are a few fields that need to be populated. Provide the relative information you need to connect to the database. Once you all the information has been provided, you can test the connection to validate that it works before clicking ok.

Once you click ok, the database resource will be added to the resrouce tab under database header.

Notice that the database is automatically connected to once it is created. This allows you to immediately start using the resource for mappings and global mappings.

GoldenGate Instance Resources:

The GoldenGate Instance resources are a little more complex to configure. This is due to the requirement that the GoldenGate environment has to have the GoldenGate Monitoring Agent (aka. JAgent ( running. This is the same JAgent that is used with the OEM plug-in. If you need more information on how to install and configure the JAgent, you can find it at this here.

Now, to create a new GoldenGate Instance resource, you follow the same approach as you would to create a database resource; instead of selecting database; select GoldenGate Instance. This will open up the GoldenGate Instance wizard/dialog for you to fill out. Provide all the information requested.

In setting up the GoldenGate Instance, there are a few things that you need to provide. In my opinion, the names of the items requested in the GoldenGate Information section are misleading. To make this a bit easier, I’m providing an explanation of what each field means.

GoldenGate Version: This is the version of GoldenGate running with the JAgent
GoldenGate Database Type: Database which GoldenGate is running against. There are multiple opptions here
GoldenGate Port: This is the port number of the manager process
Agent Username: This is the username that is defined in $GGAGENT_HOME/cfg/
Agent Password: This is the password that is created and stored in the datastore for the JAgent
Agent Port: This is the JMX port number that is defined in $GGAGENT_HOME/cfg/

After providing all the required information, you can then perform a test connection. If the connection is successful, then you can click “ok” to create the GoldenGate Instance resource. If the connection fails, then you need to confirm all your settings.

Once all the resources you need for designing your GoldenGate architecture is done, you will see all the rsources under the Resource tab.

Now that you know how to create resources in GoldenGate Studio, it will help you in designing your replication flows.


Filed under: Golden Gate
Categories: DBA Blogs

Error when starting #GoldenGate Extract against MS SQL Server

Wed, 2016-01-06 16:10

If you work with Oracle GoldenGate long enough, you will eventually have to setup against a Microsoft SQL Server. Being that GoldenGate is a heterogeneous application, this isn’t a problem; however there are small differences. One such difference is how the exact/replicat will connect to the MS SQL Database.

In an Oracle-to-Oracle configuration, you would just use a command line the following from the command line:

GGSCI> dblogin useridalias [ alias name]
GGSCI> dblogin userid [ user name ] password [ password ]

In a MS SQL Server environment, you can still login at the GGSCI command prompt with the following:

GGSCI> dblgoin sourcedb [ dns ]

You will notice the difference, which is the use of an ODBC DNS entry. Although setting up the ODBC DNS entry is not the point of this post, just keep it in mind that is is required when connecting to MS SQL Server with Oracle GoldenGate.

After setting up the ODBC DNS, you will need to add the following to the extract/replicat parameter file to enable the process to connect to the database.

sourcedb [ dns ]

Note: I normally put my connection information in a macro to modularize my parameter files. Please it makes it easier if it needs to change.

MACRO #logon_settings
sourcedb [ dns ]

Now, when you go to start the extract/replicat, you may get the following error:

ERROR OGG-00551 Database operation failed: Couldn’t connect to [ dns ]. ODBC error: SQLSTATE 37000 native database error 4060. [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database “db_name” requested by the login. The login failed.

The error message is a little bit misleading. It tells you that the process cannot connect to the database which you were able to connect to from the GGSCI command prompt with no issue. Why is this? The issue lies in the fact that the manager (MGR) process is running as a service and does not have the correct permissions to access the database from the service.

In searching MOS for this error, I was found Note ID: 1633138.1. In this note, notice that this issue is known as of Oracle GoldenGate version 12.1.2.x.x. The note also provides you a fix to this issue. In simple terms, since the manager process is running as a service; additional permissions have to be granted to manger.

To grant the SYSADMIN privilege for the manager process follow the below sequence of steps (on windows after all):

1. Manager is installed as service:
Open SQL Server Management studio -> Security ->login>select NT AUTHORITY\SYSTEM ->Right Click -> Properties–>Server Role –>Enable sysadmin role

2. ggsci>stop mgr

3. ggsci>start mgr

4. ggsci>start extract <extract-name>

After granting the sysadmin role, the extract will start.


Filed under: Golden Gate
Categories: DBA Blogs

Exporting solutions from #GoldenGate Studio

Tue, 2016-01-05 23:21

In doing some testing with Oracle GoldenGate Studio, I decided to create a test solution that can be moved from studio-to-studio. In order to move the test solution from studio-to-studio, it has to be exported first. This post will be about how to export a solution so it can be archived or shipped to a co-worker.

To export a solution, you will start in the Projects window. After opening the project, you will see a little red puzzle piece under the “Solutions”.

By right-clicking on the solution name, you are presented with a context menu that provides a few options for dealing with solutions within Oracle GoldenGate Studio. The option you are interested in, is at the very bottom of the context menu. This is the export option.

After selecting the “export” option, studio will open a small wizard that allows you to provide information and options for the solution that is to be exported. Everything on the export screen can be edited; however, the only thing that should not be changed is the “Advanced Options”. Provide a directory where the export should reside and provide an encryption key (optional).

When everything is filled out as you want, click “ok” and the export will be done. At the end of the export, should be pretty quick, you will receive a message saying that the export completed.

Once the export is completed, you will find the XML file in the directory you specified in the export wizard. This XML file can be opened up with any text editor and reviewed. A sample of the XML content is provided below.

The beauty in this XML file is that everything created in studio is contained within it. This makes it every simple and easy to email to co-workers or others if they want to see the architecture being worked on. Making collaboration on GoldenGate architectures easier.


Filed under: Golden Gate
Categories: DBA Blogs

Table Definitions in Oracle #GoldenGate #12c Trail Files

Sat, 2015-11-28 09:07

Oracle GoldenGate 12c ( has changed the information that is stored in the trail files. All the standard information is still there. What Oracle changed has to do with the meta data that is used to define a table.

Note: If you want a understand how to use log dump and general trail information, look here.

Prior to release of Oracle GoldenGate, if the column order of tables between source and target were different, you needed to generate a “definition” file using the “defgen” utility located in $OGG_HOME. This file allowed you to specify either a source or target definitions file which could be used to map the order of columns correctly. This was a nice tool when needed.

In, Oracle took this concept a little bit further. Instead of using a definitions file to do the mapping between source and target tables; Oracle has started to provide this information in the trail files. Review the image below, and you will see the table definition for SOE.ORDERS, which I run in my test environment.

Notice at the top, the general header information is still available for view. Directly under that, you will see a line that has the word “metadata” in it. This is the start of the “metadata” section. Below this is the name of the table and a series of number categories (keep this in mind). Then below this, is the definition of the table with columns and the length of the record.

A second ago, I mentioned the “numbered categories”. The categories correspond to the information defined to the right of the columns defined for the table. When comparing the table/columns between the database and trail file, as few things stand out.

In column 2 (Data Types), the following numbers correspond to this information:

134 = NUMBER

In column 3 (External Length), is the size of the data type:

13 = NUMBER(12,0) + 1
8 = VARCHAR2 length of 8
15 = VARCHAR2 length of 15
30 = VARCHAR2 length of 30

There is more information that stands out, but I’ll leave a little bit for you to decode. Below is the table structure that is currently mapped to the example given so far.

Now, you may be wondering, how do you get this information to come up in the logdump interface? Oracle has provided a logdump command that is used to display/find metadata information. This command is:


There are a few options that can be passed to this command to gather specific information. These options are:


If you issue:


You will get information related to Data Definition Records (DDR) of the table. Information this provides includes the following output:

If you issue:


You will get information related to Table Definition Record (TDR) on the table. Information provide includes the output already discussed earlier.

As you can tell, Oracle has provided a lot of information that is traditionally in the definitions files for mapping tables directly into the trail files. This will make mapping data between systems a bit easier and less complicated architectures.


Filed under: Golden Gate
Categories: DBA Blogs

The Integrated Heartbeat of Oracle #GoldenGate #12c (12.2)

Thu, 2015-11-26 21:10

With the announcement of Oracle GoldenGate 12c ( at Oracle Open World this year, I was interested in hearing that Oracle has finally integrated the heartbeat process into the core functionality of GoldenGate. Setting up the heartbeat is always a fun thing to do with GoldenGate and I had hoped that this move to integrated it would bring benefits with it. To my dismay, I’m a little underwhelmed with the heartbeat that is integrated with GoldenGate 12c ( now.

Note: If you would like to setup the older version of heartbeat, use My Oracle Support (MOS Note: 1299679.1) to reference the items needed.

Although, I’m not 100% sold on the new integrated heartbeat, it is a bit easier to setup than the process identified in the MOS note 1299679.1. And a little less confusing.

First thing that Oracle has done is add a few new GoldenGate parameters that are used to define heartbeat related items. I’ve used these parameters in the GLOBALS file, that way the changes effect the whole environment.

  • HEARTBEATTABLE <table_name> = this parameter allows you to define the heartbeat table you want to use; default name will be GG_HEARTBEAT
  • ENABLE_HEARTBEAT_TABLE | DISABLE_HEARTBEAT_TABLE = These parameters are used to either start or stop the heartbeat related items in the GoldenGate environment. (the parameters can be used in either GLOBALS, Extract or Replicat to enable/disable the heartbeat)

After making the changes to the GLOBALS file, then additional changes need to be made from GGSCI. If you were to issue a “help” command from the GGSCI prompt, you will notice there is now options to add a heartbeat table (similar to setting up the checkpoint table). The image below shows the commands that are now available in GGSCI.

To create the heartbeat table, you just need to simply run ADD HEARTBEATTABLE from GGSCI after logging into the database as the GoldenGate user.

After logging into the database and issuing the ADD HEARTBEATTABLE command, GoldenGate will create all the items needed for the heartbeat process to work. The below image shows all the objects that GoldenGate will create for heartbeat.

You will notice that the ADD HEARTBEAT command creates the following database objects:

<heartbeat_table>_SEED (default GG_HEARTBEAT_SEED)
<heartbeat_table> (default GG_HEARTBEAT)
<heartbeat_table>_HISTORY (default GG_HEARTBEAT_HISTORY)


Stored Procedures:

Scheduler Jobs:

Unlike the old way of creating a heartbeat, where GoldenGate used triggers; it is now using stored procedures to populated the tables. Then just like the old way, a scheduler job is used to execute the stored procedures to update or purge the tables. The main difference compared to the old heartbeat way is that there are views used to calculate the lag between processes.

Closer Look at Heartbeat Table:

Taking a closer look at the heartbeat table, Oracle is using a lot of timestamps. This is similar to what is traditionally done in the older heartbeat setup. The one thing that should be noticed, is that you cannot customize the table to include DML and/or DDL statistics.

Note: I have not tried to customize the table, will test this a bit later to see if can add the items I would like to add, such as DML/DDL statistics.

—Heartbeat Table Structure—


—End Heartbeat Table Structure–

The end result here is that you now have a table that is a “canned” table for strictly reporting lag within the GoldenGate environment. This is great for identifying network bandwidth issues; however, any other metrics you would like to track would need to be gathered by other means.

Closer Look at the GG_LAG View:

From looking at the views that are created with running ADD HEARTBEATTABLE from GGSCI, it is clear that Oracle took the logic that was in the old triggers and moved it into the views. The GG_LAG view also has a few additional information about path structure as well.

—Heartbeat View – GG_LAG —
SELECT local_database, current_local_ts, remote_database,
(extract(day from in_hb_age) * 86400 + extract (hour from in_hb_age) * 3600 + extract(minute from in_hb_age) * 60 + extract (second from in_hb_age)) incoming_heartbeat_age,
(extract(day from in_lag) * 86400 + extract (hour from in_lag) * 3600 + extract(minute from in_lag) * 60 + extract (second from in_lag)) incoming_lag,
(extract(day from out_hb_age) * 86400 + extract (hour from out_hb_age) * 3600 + extract(minute from out_hb_age) * 60 + extract (second from out_hb_age)) outgoing_heartbeat_age,
outgoing_path, (extract(day from out_lag) * 86400 + extract (hour from out_lag) * 3600 + extract(minute from out_lag) * 60 + extract (second from out_lag)) outgoing_lag
(SELECT local_database, sys_extract_utc(systimestamp) current_local_ts, remote_database, (sys_extract_utc(systimestamp) – incoming_heartbeat_ts) in_hb_age,
(case when
incoming_routing_path is null
then (‘ ‘ || trim(incoming_extract) || ‘ ==> ‘ || trim(incoming_replicat) || ‘ ‘)
else (‘ ‘ || trim(incoming_extract) || ‘ ==> ‘ || substr(trim(incoming_routing_path),1,3734) || ‘ ==> ‘ || trim(incoming_replicat) || ‘ ‘) end) incoming_path,
(incoming_replicat_ts – incoming_heartbeat_ts) in_lag, (sys_extract_utc(systimestamp) – outgoing_heartbeat_ts) out_hb_age,
(case when outgoing_extract is null then null else (case when outgoing_routing_path is null
then (‘ ‘ || trim(outgoing_extract) || ‘ ==> ‘ || trim(outgoing_replicat) || ‘ ‘)
else (‘ ‘ || trim(outgoing_extract) || ‘ ==> ‘ || substr(trim(outgoing_routing_path),1,3734) || ‘ ==> ‘ || trim(outgoing_replicat) || ‘ ‘) end) end) outgoing_path,
(outgoing_replicat_ts – outgoing_heartbeat_ts) out_lag
FROM ggate.gg_heartbeat) ;

—End Heartbeat View—

I like the view that are pre-packaged with the heartbeat setup; however, I think there is still additional information that is not gathered based on personal preferences. All the other items that are created with the ADD HEARTBEATTABLE are pretty standard database items.

Overall, the new heartbeat functionality is decent, but leaving me a bit under-whelmed with functionality that I would like to see as part of the heartbeat process. Hopefully, Oracle in the near future will flesh out more options for the heartbeat table to make it more statistical gathering tool within the environment (i.e. DML/DDL deltas).


Filed under: Golden Gate
Categories: DBA Blogs

Building Oracle #GoldenGate Studio Repository…. a walk through

Wed, 2015-11-04 10:49

With the announcement of Oracle GoldenGate Studio at OOW this year, there has been a good bit of interest in what is can do for any Oracle GoldenGate environment. The basics of this new design tool is that it will allow the end user to quickly build out GoldenGate architectures and mappings; however, before you can build the architectures and mappings there needs to be a repository to store this information.

The Oracle GoldenGate Studio is built on the same framework that the Oracle Data Integrator is built on. With this framework, a repository database has to be created to retain all of the architectures and mappings. To do this, you use the Repository Creation Utility (RCU). Unlike ODI, the GoldenGate Studio repository can only be created in Oracle database. The RCU can be used to create the repository in any version of the Oracle Database (EE, SE, or XE).

After identifying or installing a new Oracle database for the repository; the RCU will need to be ran. The steps below will guide you through the creation of the repository needed for Oracle GoldenGate Studio.

Note: The RCU will be ran out of the Oracle GoldenGate Studio home directory.

To run the RCU, you will need to be in the Oracle GoldenGate Studio home directory. In this directory, you will need to go to oracle_common/bin as indicated below. Then execute the RCU from there.

$ cd $GGSTUDIO_HOME/oracle_common/bin
$ ./rcu &

Executing the “rcu” command, will start the RCU wizard to build the repository. The first screen of the RCU will be the welcome screen. Click Next.

Being that this is a new repository, you will want to select “Create Repository” and “System Load and Product Load” options. Click Next.

The next screen of the RCU will ask you for connection information related to the database where the repository will be built. Provide the information and click Next.

While the RCU is attempting to connect to the database, it will run a few checks to verify that the database is supported and can be used for the repository. If you get a warning, this is normal and can be ignored. Once the warning has been ignored, the prerequisites will complete. Click Ok then Next.

The next step of the RCU will allow you to select the components needed for the repository. There are only two main components needed for the repository. “Common Infrastructure Services” (selected by default) and “Oracle GoldenGate -> Repository” (selected by default). Both of these selection will have a prefix of “DEV” by default. This is something that can be changed in the “Create new prefix” box.

Note: I like changing it to GGR (GoldenGateRepository), this way I can keep different schemas in the same repository database.

Just like the database connection prerequisites, the RCU will check for all the items needed. Click OK.

The next screen will ask you for passwords that will be used with the schemas in the repository. You have the option of using a single password for all schemas or specify different passwords. Since this is mostly for testing, a single password works for my setup. Click Next.

The custom variables step will require you to create a password for the Supervisor user. Remember the Supervisior user is a layover from the ODI framework. Provide a password that you would like to use. Also notice that the “Encryption Algorithm” variable is empty. This is meant to be empty, do not place anything here. Then click Next.

Now the wizard will prompt you about information needed to create default and temp tablespaces for the schemas setup earlier in the wizard. Taking all the defaults unless there is something specific you would like to change. Click Next.

The summary page will provide you with the information on items that will be created with the RCU. Click Create and wait for the repository to be created.

Once the “create” button has been pushed, the RCU will begin building the repository.

Upon completion of the repository, the RCU will provide a Completion Summary screen with all the details of the repository build. At this point, you can close out of the RCU by clicking “close”

If you are familiar with any of the Oracle Data Integration tools, this repository wizard is very similar to other products that use a repository (example: Oracle Data Integrator). The repository is a very nice and useful with Oracle GoldenGate Studio because it will be used to keep track of projects, solutions and mapping that you will be working on.


Filed under: Golden Gate
Categories: DBA Blogs

Installing Oracle #GoldenGate Studio 12.2.1

Fri, 2015-10-30 01:03

At Oracle Open World this year, Oracle announced the release of Oracle GoldenGate Studio 12.2.1. This is a graphical design tool for Oracle GoldenGate. It will allow you to design your replication flows, map tables, and generate your parameter files based on these items.

To install Oracle GoldenGate Studio, you will need access to Java 8 (1.8) JDK. Java should be mapped to the PATH as well for easier access when installing. Additionally, you need to download the Oracle GoldenGate Studio jar files from OTN or eDelivery once it is generally available.

Note: This installation steps outlined here, were done using the latest release I had access to (beta). Some things may or may not change with the general availability release.

To begin the installation of Oracle GoldenGate Studio, you need to navigate to the directory where the jar files for installation are located and execute the java installation.

cd /media/sf_Oracle/GGATE/ggstudio/OGGS\ Latest
$ java -jar ./fmw_12.

When it starts, you will be met with the Oracle Universal Installer (OUI) splash screen, which will eventually open the OUI.

After the OUI opens up, you will be on the welcome screen. On this screen, just push next to progress through the OUI.

Just like any other OUI process, you have the option to check for updates. Being that this is a new product, there are not any updates for it yet. Select the “Skip Auto Updates” and click next.

The next screen is the installation location. Provide where you would like to install Oracle GoldenGate Studio. The drop down menu on this screen, reads the OraInventory file and highlights any middle tier home that is on the machine. You cannot (and should not) install on top of an existing middleware home.

The only option for the installation is a Complete Install, which is listed on the Installation Type screen. Just click next here.

Next, the OUI will check to verify that all the prerequisites are met. If anything is not met, you will need to correct the problem. Click next.

After the verification of prerequisites have been met, the next screen that is shown is the Installation Summary screen. This screen shows you key information for where Oracle GoldenGate Studio is going to be installed and the resources that are going to be used. Click install to begin the installation.

Once the installation starts, just monitor the process until it completes. Click next or finish when it completed. If clicking finish, this will close the OUI.

With the installation complete, the final screen will provide details of the installation. Click finish when done reviewing.

Now, you can start Oracle GoldenGate Studio. In order to do this, you have to go to the $GGSTUDIO_HOME/bin/oggstudio.

Hope this gives you a sense of how easy it is to install Oracle GoldenGate Studio.


Filed under: Golden Gate
Categories: DBA Blogs

Oracle GoldenGate Studio, It is here and so are my thoughts

Wed, 2015-10-28 18:44

Ever since Oracle purchased GoldenGate, the biggest complaint about Oracle GoldenGate (OGG) has been around the fact that it is a command line tool. I like the command line interface of OGG, the simplicity of it and what can be done by just editing the parameter files from the command line. However, for those of you that require a graphical interface, Oracle has listened and provided a new tool. This tool is Oracle GoldenGate Studio!

This year at Oracle Open World 2015, Oracle announced Oracle GoldenGate Studio Release 1 (12.2.1). This is Oracle’s first step into bringing Oracle GoldenGate into the GUI environment. Studio is a design tool that complements the monitoring tools that Oracle provides for GoldenGate.

As a design tool, you can do a range of things now. A few of the thing are as follows:

  • Build out a wide range of Oracle GoldenGate architectures
  • Synced parameter files to systems where Oracle GoldenGate is running or dump the files to a directory for later installation
  • Export/Import GoldenGate Solutions
  • Define mappings in either local or global settings

When you first start Oracle GoldenGate Studio, anyone who has worked with Oracle Data Integrator will notice some similarities. This is because the initial framework for Oracle GoldenGate Studio is built around the ODI framework. This is good because if provides a familiar looking interface for the end user.

Just like ODI, Oracle GoldenGate Studio uses a repository. This repository can be the same repository as many other Oracle products. In my environment, I’ve got Oracle GoldenGate Studio running against a database. The repository is used to keep track of all the mappings needed to keep track of the architectures that is designed within the studio.

Overall, my initial thoughts on this product are:

  • For a first release, it is good but has some limited functionality
  • I like the interface and it helps to make quick work of designing a flow; however, initially there will be a learning curve for most GoldenGate Admins (no more Visio diagrams need to track)
  • Not thrilled with having another repository to keep up with; nice that is can be combined into other repositories if needed, just need to make sure schema names do not overwrite


With this tool being announced at Oracle Open World, expect some great blogs about this tool being road tested as well. In the mean time, once it becomes available (after OOW15) give it a try. It will make your life a bit easier.


Filed under: Golden Gate
Categories: DBA Blogs