DBASolved

Subscribe to  DBASolved feed  DBASolved
Updated: 13 hours 33 min ago

Loading Tables with Oracle GoldenGate and REST APIs

Mon, 2018-05-14 12:11

With Oracle GoldenGate 12c (12.3.0.1.x), you can now quickly load empty target tables with data from your source database. You could always do this in previous releases of Oracle GoldenGate, but the process has now been simplified using REST APIs and some scripting know-how. In this post, I’m going to show you, high level, how you can use the REST APIs and a bit of scripting to do an initial load of a two table with a single command.

In previous releases of Oracle GoldenGate, a similar task could be done, but it required you to include the Oracle Database Export/Import data pumps or some other drawn out process. With this new process, you can effectively get around that and only need to use trail files to perform the initial load.

In this scenario, I have two table with a total of 14,000 records in them. This will be a small example of an initial load, but you should get the idea behind how this will work. This approach will also work for adding tables into an existing replication scheme.

The below architcture diagram illistrates how the architecture would look with an existing GoldenGate capture running and incorprating an File-Based Initial Load process to load a few tables.

Image 1:

This may look a bit confusing, but this is quite simple to understand. The red items are the GoldenGate extract, trails (local and remote), and the GoldenGate replicat. This is an existing replication stream. The GoldenGate extract is capturing from the source database, moving transactions to the local trail file (aa). Then the DistroService picks up/reads the local trail and ships the transactions across the GoldenGate Path to the ReceiverService. The Receiver Service then writes to the remote trail (ab) where the GoldenGate replicat processes the transactions into the target database. Pretty simple and this is doing a continuous replication of transactions.

Now, you want to just setup a few new tables, but do not want to take the day or two it would take to configure, export, import, apply and then catch up. Along the bottom, is the initial load path (green) using a File-Based approach to initially load tables. This process is what I’ve scripted out to using cURL and Shell scripts. Normally, you would spend time doing an export/import for the table(s) that you want to move to the target system after setting up the initial load extract.

Using Oracle GoldenGate Microservices architecture, this initial load process can be simplied and done very quickly. Below is a link to a script which I wrote to perform an File-Based Initial Load within Oracle GoldenGate Microservices.

FB_InitialLoad.sh <— Use at your own risk! This is only an example script of how this can be done.

What this script does, is creates the File-Based Initial Load process and populates the two tables I’ve identified in the target system.

As you run this script, everything I needed to build has been reduced down to functions that I can call when needed within the script. Granted this script if very simple but it orchatrates the whole initial load process for the tables I wanted. After the tables have been loaded, then they can be merged into the existing replication stream.

Enjoy!!!

Categories: DBA Blogs

Build a Integrated Replicat using JSON

Mon, 2018-05-07 13:15

In a previous post, I showed you how to build an Integrated Extract (IE) and Distribution Path by using JSON and cURL. In this post, let’s look at how you can build an Integrated Replicat (IR) in the same manner.

To build a replicat using JSON, the JSON document is made up of the following 8:

Config – Details for the associated parameter file
Source – Where the replicat should read transactions from
Credentials – What credentials in the credential stores should be used
Checkpoint – What checkpoint table is used by the replicat
Mode – What type of replicat will be built
Registration – Register the replicat with the database
Begin – At what timeframe the replicat should start
Status – If the extract should be started or not

The resulting JSON document would look like the following:

{
“config”:[
“Replicat REPTS”,
“UseridAlias TGGATE”,
“Map SOE.*, Target SOE.*;”
],
“source”:{
“name”:”bc”
},
“credentials”:{
“alias”:”TGGATE”
},
“checkpoint”:{
“table”:”ggate.checkpoints”
},
“mode”:{
“type”:”integrated”,
“parallel”: true
},
“registration”: “standard”,
“begin”:”now”,
“status”:”stopped”
}

Now that you have a valid JSON document, a cURL command for building the integrated replicat can be done as follows:

curl -X POST \
http://localhost:17001/services/v2/replicats/REPTS\
-H ‘Cache-Control: no-cache’ \
-d ‘{
“config”:[
“Replicat REPTS”,
“UseridAlias TGGATE”,
“Map SOE.*, Target SOE.*;”
],
“source”:{
“name”:”bc”
},
“credentials”:{
“alias”:”TGGATE”
},
“checkpoint”:{
“table”:”ggate.checkpoints”
},
“mode”:{
“type”:”integrated”,
“parallel”: true
},
“registration”: “standard”,
“begin”:”now”,
“status”:”stopped”
}’

Just like the Integrated Extract (IE) and Distribution Service, the Integrated Replicat (IR) is created in a stopped state. At this point, you can start the IR and validate whatchanges need to be made to ensure replication happens.

Enjoy!!!

Categories: DBA Blogs

Build a Distribution Path using JSON

Fri, 2018-05-04 13:15

In a previous post, I showed you how to build an Integrated Extract (IE) using JSON and simple cURL command. In this post, let’s take a look at how to build a Distribution Service Path.

First think to understand is that the Distribution Service is the replace for the Extract Data Pump in the traditional Oracle GoldenGate architecture. The Distribution Service does the same thing as the Extract Data Pump with the exception of transformations. If you have a need to do transformations with Oracle GoldenGate Microservices; the transformations have to be pushed to either the extract or the replicat to be done.

The purpose of the Distribution Service is to ensure that the local trail files are shipped across the network and reach the Reciever Service which will create the remote trail files.

Note: The Receiver Service, on the target side, will be started automatically when the Distribution Service connects to the port number for it.

Within the Distribution Service, you will create Distribution Paths between the source and target hosts. The way you do this with JSON quite simple. There are 4 main items the JSON should contain.

1. Name – This is what the Distribution Path will be named
2. Status – Should the Distribution Path be running or stopped
3. Source – This specifies the local trail file that should be read for transactions
4. Target – This specifies the Login and URL to write to the remote trail files.

Note: For the Target setting, there are 4 protocols that can be used:
Secure Websockets (wss) – default
Websockets (ws)
UDP-based Data Transfer Protocol (udt)
Oracle GoldenGate (ogg)

An example of a JSON document that would be used to build a Distribution Path is as follows:

{
“name”: “TSTPATH”,
“status”: “stopped”,
“source”: {
“uri”: “trail://localhost:16002/services/v2/sources?trail=bb”
},
“target”: {
“uri”: “ws://OracleGoldenGate+WSTARGET@localhost:17003/services/v2/targets?trail=bc”
}
}

To build this Distirbution Path (TSTPATH), a cURL command as such can be used to build it:

curl -X POST \
http://localhost:16002/services/v2/sources/TSTPATH \
-H ‘Cache-Control: no-cache’ \
-d ‘{
“name”: “TSTPATH”,
“status”: “stopped”,
“source”: {
“uri”: “trail://localhost:16002/services/v2/sources?trail=bb”
},
“target”: {
“uri”: “ws://OracleGoldenGate+WSTARGET@localhost:17003/services/v2/targets?trail=bc”
}
}’

Once the Distribution Path is created, you can start it. Upon starting the path, you can check the Receiver Service on the target side. It should have been started as well.

Enjoy!!!

Categories: DBA Blogs

Build an Integrated Extract using JSON

Thu, 2018-05-03 13:15

Now that Oracle GoldenGate 12.3 Microservices have been out for about 9 month; there seems to be more and more discussions around how microservices can be used. The mircoservices architecture provides a faster way for users to build extract, replicats, distribution paths and many other items by using a JSON document and simply calling a REST end-point.

In this post, I’ll show you how to build an integrated extract using JSON and REST APIs. First think you need to understand, is the steps that it takes to build an extract currently in GGSCI/AdminClient.

Note: AdminClient can be used, with debug on, to see how these commands translate back into JSON and REST calls.

To build an Integrated Extract via GGSCI/AdminClient:

1. add extract exttst, integrated, begin now
2. register extract exttst, database container pdb1
3. add exttrail aa, extract exttst, megabytes 250
4. start extract exttst

As you can tell, it takes 4 steps to add and start the extract to an Oracle GoldenGate configuration.

If your a command line geek or a developer who wants to do more with Oracle GoldenGate, the mircroservices architecture provides you a way to build an extract via JSON files. A simple JSON file for building an integrated extract looks as follows:

{
“description”:”Integrated Extract”,
“config”:[
“Extract EXTTST”,
“ExtTrail bb”,
“UseridAlias SGGATE”,
“Table SOE.*;”
],
“source”:{
“tranlogs”:”integrated”
},
“credentials”:{
“alias”:”SGGATE”
},
“registration”:{
“containers”: [ “pdb1” ],
“optimized”:false
},
“begin”:”now”,
“targets”:[
{
“name”:”bb”,
“sizeMB”:250
}
],
“status”:”stopped”
}

This JSON example, describes all the attributes needed to build an integrated extract. The main items in this JSON are:

Description – Provide a description for the parameter file
Config – Details for the associated parameter file
Source – Where the extract should read transactions from
Credentials – What credentials in the credential stores should be used
Registration – Register the extract with the database and against associated pdbs
Begin – At what timeframe the extract should start
Targets – What trail files the extract should write to
Status – If the extract should be started or not

These 8 categories cover what we traditioanlly did in the classic architecture in 3 steps. With all these items in the JSON file, you can now quickly build the extract by calling a simple curl command.

In order to build the extract, you need to know the REST API end-point that is needed. All extracts are built against the Administration Server (AdminService) within the microservices architecture. In my configuration, my AdminService is running on port 16000; so the REST API end-point would be:

{{Source_AdminServer}}/services/v2/extracts/{{extract_name}}

http://localhost:16000/services/v2/extracts/EXTTST

The REST API end-point, requires you to specify the extract name in the URL. Now with the URL and associated JSON, you can create an extract with a simple cURL command or embed the call into an application. An example of a cURL command that would be used is:

curl -X POST \
http://localhost:16001/services/v2/extracts/EXTTST\
-H ‘Cache-Control: no-cache’ \
-d ‘{
“description”:””,
“config”:[
“Extract EXTTST”,
“ExtTrail bb”,
“UseridAlias SGGATE”,
“Table SOE.*;”
],
“source”:{
“tranlogs”:”integrated”
},
“credentials”:{
“alias”:”SGGATE”
},
“registration”:{
“containers”: [ “pdb1” ],
“optimized”:false
},
“begin”:”now”,
“targets”:[
{
“name”:”bb”,
“sizeMB”:250
}
],
“status”:”stopped”
}’

Once the extract is created, you will notice that the extract is stopped. This is due to the “status” that was feed through the JSON document. You should be able to start the extract and start seeing transactions being extracted.

Enjoy!!!

Categories: DBA Blogs

How to write to SYSLOG in OGG 12.3 Classic and Microservices Architectures?

Sun, 2018-03-25 17:25

Oracle GoldenGate 12.3 Microservices introduced a lot of changes into how replication is to be managed moving forward. The Microservices Architecture (MA), provides a lot of opportunity for people to learn a lot about microservices and how they can be leveraged to to bring real-time replication to their organizations.

One feature that was used in the Classic Architecture (CA) was having the GGSERR.log file write items to the messages file at the system level. Starting in Oracle GoldenGate 12.3, both MA and CA, this can still be done; however, how it is done is different.

When you first look at the release notes for Oracle GoldenGate 12.3, you will notice that the SYSLOG parameter is listed under the section 2.3.2 Deprecated Features (here). It clearly states that the SYSLOG parameter has been removed and should not be used for the GLOBALS or the MGR.prm files. So, how do we enable writing to the messages file then?

After digging around a bit, you will notice that this is covered in the docs under 17.6 Using the Error Log in the Administration Guide (here). There is a lot of information here for how to enable the SYSLOG functionality and tells you what file to look at (ogg-ggserr.xml).

Now, that you know what file controls the SYSLOG option, where is this file located?

In CA, the file is located in a new directory called “diretc” and under another directroy called “logging”.

$OGG_HOME/diretc/loggin

In MA, the file is buried in the $OGG_HOME a bit, but easy to find.

$OGG_HOME/lib/utl/logging

Once you are in the file, you will notice that the file is configured by default to write to the GGSERR.log and rollover after every 10Mb in size. Just below that, you will notice that there are three commented out items. Each one of these control what you can do with the GGSERR.log. The first option will write to the GGSERR.log without rolling over. The second will allow you to write GGSERR.log info to the SYSLOG! The third will turn off all event logging. Since you will want to write to the SYSLOG, you will go with the second option. Uncomment that section (remove <!— && ! —> ).

This section will look like this:

!- The ggserr.Syslog adapter will write all messages to the local
!- system log.
!-
<appender name=”ggserr.Syslog” class=”SyslogAppender”>
<layout class=”PatternLayout”>
<param name=”Pattern” value=”%-19.19d{%Y-%m-%d %H:%M:%S} %m%n”/>
</layout>
</appender>

After uncommiting the section for writing to SYSLOG, you will need to restart the ServiceManager by using the Action button in the Deployment section of the ServiceManager HTML5 page. After the ServiceManager restarts, you will notice Oracle GoldenGate commands being written to the /var/lib/messages file.

Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-01892 Distribution path network is configured as
Mar 20 11:12:17 ogg123 ServiceManager: OS DEFAULT SPECIFIED ACTUAL VALUE
Mar 20 11:12:17 ogg123 ServiceManager: APP_FLUSHBYTES 27985 N/A 27985
Mar 20 11:12:17 ogg123 ServiceManager: APP_FLUSHSECS 1 N/A 1
Mar 20 11:12:17 ogg123 ServiceManager: IP_DSCP DEFAULT N/A DEFAULT
Mar 20 11:12:17 ogg123 ServiceManager: IP_TOS DEFAULT N/A DEFAULT
Mar 20 11:12:17 ogg123 ServiceManager: TCP_NODELAY 0 N/A 0
Mar 20 11:12:17 ogg123 ServiceManager: TCP_QUICKACK 1 1 1
Mar 20 11:12:17 ogg123 ServiceManager: TCP_CORK 0 N/A 0
Mar 20 11:12:17 ogg123 ServiceManager: SO_SNDBUF 16384 N/A 16384
Mar 20 11:12:17 ogg123 ServiceManager: TCP_CORK 0 N/A 0
Mar 20 11:12:17 ogg123 ServiceManager: SO_SNDBUF 16384 N/A 16384
Mar 20 11:12:17 ogg123 ServiceManager: SO_RCVBUF 87380 N/A 87380.
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-01052 No recovery is required for target file CB000000000, at RBA 0 (file not opened).
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-01669 Opening /opt/app/oracle/gg_deployments/pdb2/var/lib/data/CB000000000 (byte -1, current EOF 0).
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-02243 Opened trail file /opt/app/oracle/gg_deployments/pdb1/var/lib/data/CA000000 at 2018-03-20 11:12:17.126288.
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-01478 Output file CB is using format RELEASE 12.3.
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-02756 The definition for table PDB1.TEST_USER.LRG_TABLE is obtained from the trail file.

Enjoy!!!

Categories: DBA Blogs

Changing Security Role Password in Oracle GoldenGate 12.3 Microservices

Thu, 2018-03-08 12:02

One of the nice things about Oracle GoldenGate 12.3 Microserivces, is the flexibility we now have to interact with Oracle GoldenGate. Additionally, what comes with Oracle GoldenGate 12.3 Microservices is a new role based security frame work.

When you first setup Oracle GoldenGate 12.3 Microservices, you setup your ServiceManager and initial deployment. Upon setting this up, you will assign a new administrator to the frame work. This is a different user than the Oracle GoldenGate user within the database. This user is the user assigned to the security role (highest role). This user is the equivalent of the SYS user in the Oracle Database and has access to everything within the security framework of Oracle GoldenGate 12.3 Microservices. It should be a no-brainer to keep the password for this user secure and to as few people as possible.

Now, when you look at the user from inside of the Administration Server (AdminServer); notice there is not an update button under the Action category (Figure 1).

Figure 1:

So how do we update this password for a user in the security role, much less any other role? Remember, when I said Oracle GoldenGate 12.3 Microservices was “flexible”? We have to use the RESTful API to update the password.

The exact end-point that you have to use is: /services/v2/authorizations/{role}/{user}. More info can be found here.

The way that I update the password is by using a JSON document and a simple cURL command. Below is a simple JSON document example:

{
“credential”:”welcome1″
}

Then the cURL command I use is:

curl -u oggadmin:********* -H “Content-Type:application/json” -H “Accept:application/json” -X PATCH http://localhost:16001/services/v2/authorizations/security/oggadmin -d @update_security_password.json| python -mjson.tool

After running the cURL command, the password for the oggadmin user is updated. You should see some similiar output to this output.

{
“$schema”: “api:standardResponse”,
“links”: [
{
“href”: “http://localhost:16001/services/v2/authorizations/security/oggadmin&#8221;,
“mediaType”: “application/json”,
“rel”: “canonical”
},
{
“href”: “http://localhost:16001/services/v2/authorizations/security/oggadmin&#8221;,
“mediaType”: “application/json”,
“rel”: “self”
}
],
“messages”: [],
“response”: {
“links”: [
{
“href”: “http://localhost:16001/services/v2/authorizations/security/oggadmin&#8221;,
“mediaType”: “application/json”,
“rel”: “self”
}
]
}
}

Hope this helps you understand how to change a user password from REST within the Oracle GoldenGate 12.3 Microservices Architecture.

Enjoy!!!

Categories: DBA Blogs

Identifying Integrated Replicat transactional progress

Fri, 2018-02-23 09:50

From time-to-time, you may want to find out what transactions have been applied or still in flight while Oracle GoldenGate is running while using the Integrated Replicat (IR) products. This can be done easily by using the ALL_GG_INBOUND_PROGRESS/DBA_GG_INBOUND_PROCESS views. There are a few columns of interest in this view, they are:

  • APPLIED_LOW_POSITION
  • APPLIED_HIGH_POSITION
  • OLDEST_POSITION
  • APPLIED_LOW_SCN (not applicable for GoldenGate)

Note: For more information on this view, reference Oracle Docs – here

The APPLIED_LOW_POSITION represents the commit positions less than this SCN have been applied.
The APPLIED_HIGH_POSITION is the commit position of a transaction that has been applied.
The OLDEST_POSITION is the earliest position of transactions currently being applied.
The APPLIED_LOW_SCN is the marker that represents all SCN below or equal to this number have been successfully applied; however, this column is not applicable for GoldenGate replication since the source database may be non-Oracle in nature.

To identify these columns during replication, you can use a simple query like:

select server_name, applied_low_position, applied_high_position, oldest_position, applied_low_scn
from DBA_GG_INBOUND_PROGRESS
where server_name = ‘OGG$IREPSOE’;

In the above query, I’m looking at a specific replicat, called OGG$IREPSOE. This an indicator that I’m looking at an Integrated Replicat (IR). When I run the query, I get the following output:

SERVER_NAME APPLIED_LO APPLIED_HI OLDEST_POS APPLIED_LO
————— ———- ———- ———- ———-
OGG$IREPSOE 6232708 6232710 6232505 0

Using the definitions above, the APPLIED_LOW_SCN column can be thrown out, since we do not use it for identifying what has been applied. Taking the next three columns into account, I can see that SCN 6232505 (OLDEST_POSITION) is the last SCN applied. Any transactions with SCN lower or equal to this SCN has been applied to the database.

The next column we need to look at is the APPLIED_LOW_POSITION. This column represents transactions that have been applied to the database as well. Any SCN below or equal to this SCN has been applied. In this case the SCN is 6232708. This SCN looks really close to the OLDEST_POSITION SCN that was just discussed; within 203 value (6232708 – 6232505). This change represents just a 3 seconds in changes. You can see this by running this query:

select server_name, scn_to_timestamp(applied_low_position), scn_to_timestamp(oldest_position)
from DBA_GG_INBOUND_PROGRESS
where server_name = ‘OGG$IREPSOE’;

Which produces the following output:

SERVER_NAME SCN_TO_TIMESTAMP(APPLIED_LOW_PO SCN_TO_TIMESTAMP(OLDEST_POSITIO
————— ——————————- ——————————-
OGG$IREPSOE 23-FEB-18 09.49.45.000000000 AM 23-FEB-18 09.49.42.000000000 AM

Lastly, when looking at APPLIED_HIGH_POSITION. This column represents transactions that have been applied as well; however, any SCN/transaction that is higher than this SCN has not been applied yet.

Hopefully, this helps explain how you can identify what SCNs have been applied and help identify what to look for transactions that have not been applied.

Enjoy!!

Categories: DBA Blogs

Start/Stop Extract/Replicat with REST API/JSON

Sun, 2018-02-04 22:23

Oracle GoldenGate Microservices Architecture is designed to allow the user to have three different ways of interacting with replication from anywhere. One of these approaches is to use the RESTful APIs that come bundled with release. By using RESTful APIs, an organization can orgistrate how they want GoldenGate to work within their environment.

In this post, you will take a look at how to start a pre-existing extract/replicat by using the RESTful API end points. To find more information on the APIs that are avaliable, please refere to the Oracle docs located here.

If you have an existing extract/replicat in a down or pending status, then you can start it using a JSON file and the associated RESTful API end point.

Now the image above shows you that the extract is stopped. To start the extract using RESTful API, you will need a JSON file that contains the following:


{ 
"$schema":"ogg:command",    
"name":"start",    
"processName":"IEXTSOE",    
"processType":"extract"
}

Then from the command line, you can use cURL or some other method that accepts RESTful API calls to start the extract.

curl -u oggadmin:******** -H "Content-Type: application/json" -H "Accept: application/json" -X POST http://http://localhost:16001/services/v2/commands/execute" -d @start_extracts.json | python -mjson.tool

Upon execution of the cURL command, you receive a status response on the command line in JSON output. This response shows you that the extract is starting and started.

{
"$schema": "api:standardResponse",
"links": [
{
"href": "http://localhost:16001/services/v2/commands/execute",
"mediaType": "application/json",
"rel": "canonical"
},
{
"href": "http://localhost:16001/services/v2/commands/execute",
"mediaType": "application/json",
"rel": "self"
}
],
"messages": [
{
"$schema": "ogg:message",
"code": "OGG-00975",
"issued": "2018-02-05T03:27:17Z",
"severity": "INFO",
"title": "EXTRACT IEXTSOE starting",
"type": "http://docs.oracle.com/goldengate/c1230/gg-winux/GMESG/oggus.htm#OGG-00975"
},
{
"$schema": "ogg:message",
"code": "OGG-15426",
"issued": "2018-02-05T03:27:17Z",
"severity": "INFO",
"title": "EXTRACT IEXTSOE started",
"type": "http://docs.oracle.com/goldengate/c1230/gg-winux/GMESG/oggus.htm#OGG-15426"
}
]
}

When you go back to the web page for the Administration Service, you see that the extract has been started.

 

This same process can be used when you want to start/stop an replicat.

Enjoy!!

Categories: DBA Blogs

Linking Oracle GoldenGate Classic Architecture to Oracle GoldenGate Microservices Architecture

Fri, 2018-01-26 15:15

In my last post I covered how to connect the microservices architecture to the classic architecture (here). For this post, I want to show you how to connect the GoldenGate Classic Architecture to the new GoldenGate Microservices Architecture.

You many be asking yourself, why do I want to do this? The answer is quite simple. At the current moment and time, there is no upgrade path to move from Classic to Microservices. This is due to the changes in architecture design for remote administration. If you want to make the leap to Microservices though, you can “migrate” to the architecture using this approach. Now this is only one version of migrating to Microservices. I’m calling this one the leap frogging … basically, you can set Microservices up as a target and start replicating to it. Once done and/or nsync, make a new target on the other side using Microservices to complete the configuration.

 

Before you can do a leap frog migration, you have to get your Classic Architecture to work with Microservices Architecture. In order to do this, you have to connect the Data Pump Extract (Classic Architecture) to the Reciever Service (Microservices Architecture). To keep this post, realtive short, I’m just going to focus on what you need to configured.

Some assumptions on environments:

1. Classic Extract is up and running. Capturing data and shipping it to the local trail location ($OGG_HOME/dirdat)
2. Microservices Architecture installed with at least one deployment. Reciever Services running on a specified port (in this example: 17003)
3. Replicat configured in Microservices Architecture to read remote trail file

Setting Up the Data Pump Extract (Classic Architecture):

At this point, you may have to modify your parameter file or create a new Data Pump Extract. For example purposes, let’s create a new Data Pump Extract.

1. Start GGSCI
cd $OGG_HOME
./ggsci

2. Add a Data Pump Extract
ggsci> ADD EXTRACT PSOE2SOE, EXTTRAILSOURCE ./dirdat/ca

3. Add remote trail to Data Pump Extract
ggsci> ADD RMTTRAIL cb, EXTRACT PSOE2SOE, MEGABYTES 200

Note: At this point, notice the remote trail file is not mentioning the “dirdat” directory. This is due to Microservices using a different directory structure for trail files. The Receiver Services will place the remote trail file where it needs to be for the Microservices Architecture.

4. Edit parameter file for the Data Pump Extract
ggsci> EDIT PARAMS PSOE2SOE

The contents of the parameter file are as follows:

EXTRACT PSOE2SOE
RMTHOST <hostname/IP address>, PORT <reciever service port>
RMTTRAIL cb
PASSTHRU
TABLE PDB1.SOE.ADDRESSES;

5. Start the Data Pump Extract
ggsci> START EXTRACT PSOE2SOE

After the extract starts, you should see a new path start up in the Reciever Service of the Microservices Architecture. The below image shows you an example of what this may look like.

A couple of things to notice here. The Reciever Service will be named with a default name. And the port number (17003) will highlight what protocol is being used (ogg). This approach will help you get moving towards the Microservices Architecture.

Enjoy!!!

Categories: DBA Blogs

Linking Oracle GoldenGate Microservices Architecture to Oracle GoldenGate Classic Architecture

Mon, 2018-01-22 10:45

As of August 2017, Oracle has released two distinct architectures for Oracle GoldenGate. The newest of which is the Microservices architecture. These question has to be asked, how can both of these architectures work together? Is that even possible? The answer is yes, you can link the Microservices Architecture to the Classic Architecture and vis-versa. In the post, you’ll see how to link the two from the capturing in Microservices and applying in Classic.

Note: To focus just on the linking, I will not cover how to create an extract in Microservices and a replicat in Classic. In short, you will need to have a pre-existing extra and replicat.

1. Open ServiceManager Overview page
2. Select Distribution Service

3. Click the plus ( + ) sign to add a path

4. Provide the required information to create the path (Name, Extract, Target info (Protocol, Target Host/IP, MGR Port Number, Remote Trail))

Note: The protocol needs to be OGG for the linking to work.

5. Click Create and Run at the bottom of the screen

6. After the Distribution Path is created, you will see it listed on the page. From here you can use the Action button and look at the details of the path.

Now, at this point, your Manager (MGR) process should be running and accepting connections from the Distribution Service.

Note: The key to a successful linking is to make sure the Distrubtion Service knows where to put the remote trail files for reading by the Classic Architecture Replicat.

Categories: DBA Blogs

Upgrading GoldenGate Microservices Architecture – GUI Based

Thu, 2018-01-18 10:45

In August of 2017, Oracle released two architectures for Oracle GoldenGate. These architectures were the Classic Architecture and the Microservices Architecture. Since then there has been some discussion around upgrading Oracle GoldenGate to Microservices. Due to the change in architecture, there is no direct upgrade path from Classic Architecture to Microservices Architecture. If you want to use the new Microservices Architecture, you will have to do a fresh install and migrate to the architecture.

Let’s say that you are bold and forward thinking and have already made move to the Oracle GoldenGate Microservices Architecture upon the release of 12.3.0.1.0 in August 2017 … great and I’m happy you did! It is a really cool architecture to be on and will provide you a stepping stone into the cloud world. Now what to do when Oracle releases a new release of Microservices Architecture? … The answer is upgrade!

Now upgrading Oracle GoldenGate Microservices Architecture is not a hard as it has been in the past with Oracle GoldenGate. It has actually gotten simpler to upgrade … all you have to do now is install a new set of binaries and switch the deployment home for your deployments and ServiceManager over to it.

To perform the upgrade simply follow these steps:

1. Login and verfiy what Oracle GoldenGate Home the ServiceManager is using
a. Login to ServiceManager (http(s)://<hostname>:<port>)

b. Review the Deployment section (bottom of page)

2. Install new Oracle GoldenGate for Microservices binaries next to existing binaries. (Technically an out of place upgrade)

3. Update the ServiceManager and/or Deployments with new Oracle GoldenGate Home Information
a. Click the ServiceManager or Deployment (hyperlink)

b. Click the pencil icon. This will open the dialog box for editing

c. Update the GoldenGate Home with the new Oracle GoldenGate Home Path
d. Click Apply

f. From the Overview page, use the Action button to restart the ServiceManager out of the new Oracle GoldenGate home

After hitting “restart” from the Action button, you will lose access to ServiceManager. This is due to the old ServiceManager being shutdown and the new ServiceManager being started out of the new Oracle GoldenGate Home. Once the new ServiceManager is up and running, the same steps can be followed to move your deployment homes to the new Oracle GoldenGate Home.

Note: Ensure you stop all extract/replicat processes within the Deployment Home before moving GoldenGate Home of a deployment.

Enjoy!!!

Categories: DBA Blogs

Changing the port number on a GoldenGate Microservice using REST

Sun, 2017-11-19 12:34

The new Oracle GoldenGate Microservices architecture provides a lot of new functionality and opens up a lot of possibilities. Part of these possiblies is to ability to interact with Oracle GoldenGate from a REST API stand point. The framework provides so much flexability and functionality, it is hard to highlight everything it can do.

Since services are the new thing in Oracle GoldenGate, I figured I do something simple and still show you how easy it is to work with the framework.

Upon inital installation, you have to setup a max of five (5) ports that the microservices will use. We actually recommend putting Oracle GoldenGate Microservices behind a reverse proxy but that is another discussion. With these five ports, you may inadventantly select ports that are in use. So how do you fix this issue after installation?

Answer is quite simple, just change the port number.

In order to change the port number, you need to write a JavaScript Object Notation (JSON) file for the services you want the port number changed for. In my case, I want to change my deployment (Atlanta_1) administration services port from 16001 to 16010. Figure 1 shows you that I’m on 16001.

Figure 1:

Now I want to change the port to 16010. In order to do this I need write a JSON file. My file looks like the sample code below.

{
“config”: {
“network”: {
“serviceListeningPort”:16010
}
},
“configForce”:true,
“enabled”:true,
“status”:”restart”
}

Then I need to come up with a cURL command.

curl -u oggadmin:******* -H “Content-Type: application/json” -H “Accept: application/json” -X PATCH http://localhost:16000/services/v2/deployments/Atlanta_1/services/adminsrvr -d @change_adminsrvr_port.json | python -mjson.tool

The cURL command is going to run the JSON document against my ServiceManager for deployment Atlanta_1, then restart the Administration Service. Figures 2 and , shows you the end result and that the Administration Service is still up and running.

Figure 2:

Figure 3:

There you have it, a simple way via REST to change port numbers for Oracle GoldenGate 12c Microservices.

Enjoy!!!

Categories: DBA Blogs

FORMAT RELASE changes in #GoldenGate #12.3

Sun, 2017-11-19 09:52

This is just a quick note for those who are looking to using Oracle GoldenGate 12.3.0.1 (Classic or Microservices) and want to send trails of a lower version. This information can be found in the Release Notes for Oracle GoldenGate 12.3.0.1 (here).

The default behavior for FORMAT RELEASE settings supported with EXTTRAIL, RMTTRAIL, EXTFILE and RMTFILE has changed. This is due to changes need to support PDBs with Local Undos. The three bullet points below provide a summary of what has changed.

  • Non-CDB databases with compatibility set to 12.1, FORMAT RELEASE 12.2 or above is supported.
  • Non-CDB databases with compatibility set to 12.2, FORMAT RELEASE 12.2 or above is supported.
  • CDB/PDB databases with compatibility set to 12.2, only FORMAT RELEASE 12.3 is supported. This is due to the use of local undo for PDBs, which requires augmenting the transaction ID with the PDB number to ensure uniqueness of trx IDs.

 

Enjoy!!!

Categories: DBA Blogs

Build a Parallel Replicat from #Oracle #GoldenGate #AdminClient

Sun, 2017-11-19 08:33

One of the new features that came out with Oracle GoldenGate 12.3.0.1.0 was the introduction of the Parallel Replicat. This new replicat is designed to help users quickly load data into their envrionments by using multiple parallel mappers and threads. Figure 1 shows you what this concept is about.

Figure 1:

What you need to understand about this architecture is that we are still reading a single trail file. We are just providing a wider road with mulitple lanes for reading (mappers) and writing (appliers). In the middle of all this, the replicat is ensuring that the all transactions are ordered based on key dependancies (PK, FK and UK). This is to ensure that we have all transactions in the order that is required for each individual writer to apply independantly of each other. This is a huge difference from the integrated replicat, where the dependancy and writing was done within the database.

Additonally, the parallel replicat can be configured to run in one of two modes. The first mode is the “integrated” mode. This mode is similar to the current integrated replicat. The big difference is that the readers/writers are external of the database now, but we are still using the internals of the database to manage the processes.

The second mode of the parallel replicat is “non-integrated”. This means the replicat still runs in parallel, but now it is completly outside of the database. Take a moment and think where this could go ….

Now that, you understand the high-level view of the Parallel Replicat, lets take a look at how you can build one from the AdminClient.

The following steps are what you need to do from AdminClient to build a parallel replicat:

1. Open AdminClient

$ cd $OGG_HOME/bin
$ bin> ./adminclient

2. Login to the ServiceManager for the target you want to run against

adminclient> connect http://<host&gt;:<port> deployment <deploment> as <security user> password <password>

3. Create the Parallel Replicat

adminclient> add replicat <group name>, integrated, parallel, exttrail pr

Note: After creating the replicat, you will see a warning as such: 2017-11-19T14:04:06Z INFO OGG-12029 The file with name ‘PREP1.prm’ does not exist.

Note: After creating the replicat, it will automatically show up in the associated Administration Service.

4. Edit Parameter file

adminclient> edit params prep1

5. Start Parallel Replicat

adminclient> start replicat prep1

After the replicat starts, you should see the number of threads that you specified for readers (mappers) and writers (appliers) in the report files.

Take a look and have fun with this new feature. Looks like it is going to be a really good one.

Just for reference, here is my parameter file for parallel replicat:

replicat PREP1
useridalias TGGATE2 domain TGGATE2
INSERTUPDATES
REPERROR(1, DISCARD)
MAP_PARALLELISM 2
MIN_APPLY_PARALLELISM 2
MAX_APPLY_PARALLELISM 8
SPLIT_TRANS_RECS 4
TARGETCATALOG PDB2;
MAP PDB1.SOE.ADDRESSES, TARGET SOE1.ADDRESSES;
MAP PDB1.SOE.CUSTOMERS, TARGET SOE1.CUSTOMERS;
MAP PDB1.SOE.ORDERS, TARGET SOE1.ORDERS;
MAP PDB1.SOE.ORDER_ITEMS, TARGET SOE1.ORDER_ITEMS;
MAP PDB1.SOE.CARD_DETAILS, TARGET SOE1.CARD_DETAILS;
MAP PDB1.SOE.LOGON, TARGET SOE1.LOGON;
MAP PDB1.SOE.PRODUCT_INFORMATION, TARGET SOE1.PRODUCT_INFORMATION;
MAP PDB1.SOE.INVENTORIES, TARGET SOE1.INVENTORIES;
MAP PDB1.SOE.PRODUCT_DESCRIPTIONS, TARGET SOE1.PRODUCT_DESCRIPTIONS;
MAP PDB1.SOE.WAREHOUSES, TARGET SOE1.WAREHOUSES;

MAP PDB1.SOE.ORDERENTRY_METADATA, TARGET SOE1.ORDERENTRY_METADATA;

Enjoy!!!

Categories: DBA Blogs

#GoldenGate Procedural Replication Support

Thu, 2017-10-26 17:32

With the release of Oracle GoldenGate 12c (12.3.0.1.x), came the support for Procedural Replication. This option is an enhancement to the integrated replicat which allows you to replicat Oracle-supplied PL/SQL procedures that cannot be replicated as DML or DDL.

Note: In order to replicate these packages and procedures, you need to be on Oracle Database Release 2 (12.2) an later and using the integrated extract and replicat.

To find out what packages and procedures are support, Oracle has provided new data dictionary views that can be used to see what packages and procedures can be used with Oracle GoldenGate. These views are DBA_GG_SUPPORTED_PACKAGES and DBA_GG_SUPPORTED_PROCEDURES. These views can be found in Oracle Database Release 2 (12.2).

As of Oracle GoldenGate 12.3, there are 309 procedures and 36 packages that are support. This number will increase over time.

The packages that are currently supported are:

DBMS_AQ
DBMS_AQADM
DBMS_AQELM
DBMS_AQJMS
DBMS_DBFS_CONTENT_ADMIN
DBMS_DBFS_SFS
DBMS_DBFS_SFS_ADMIN
DBMS_DDL
DBMS_FGA
DBMS_GOLDENGATE_ADM
DBMS_GOLDENGATE_IMP
DBMS_REDACT
DBMS_REDEFINITION
DBMS_RESCONFIG
DBMS_RLS
DBMS_RULE_ADM
DBMS_SQL_TRANSLATOR
DBMS_XDB
DBMS_XDBRESOURCE
DBMS_XDBZ
DBMS_XDB_ADMIN
DBMS_XDB_CONFIG
DBMS_XDB_REPOS
DBMS_XDB_VERSION
DBMS_XMLINDEX
DBMS_XMLSCHEMA
DBMS_XMLSCHEMA_LSB
SDO_META
SDO_META_USER
XS_ACL
XS_ADMIN_UTIL
XS_DATA_SECURITY
XS_DATA_SECURITY_UTIL
XS_NAMESPACE
XS_PRINCIPAL
XS_SECURITY_CLASS

Packages with associated procedures that are currentlly supported are:

DBMS_AQ AQ$_BACKGROUND_OPER
DBMS_AQ AQ$_BACKGROUND_OPER_PAS
DBMS_AQ AQ$_DELETE_DIOT_TAB
DBMS_AQ AQ$_DELETE_HIST_TAB
DBMS_AQ AQ$_DELETE_TIOT_TAB
DBMS_AQ AQ$_DEQUEUE
DBMS_AQ AQ$_ENQUEUE
DBMS_AQ AQ$_INSERT_DIOT_TAB
DBMS_AQ AQ$_INSERT_HIST_TAB
DBMS_AQ AQ$_INSERT_TIOT_TAB
DBMS_AQ AQ$_UPDATE_HIST_TAB
DBMS_AQ AQ$_UPDATE_HIST_TAB_EX
DBMS_AQ BIND_AGENT
DBMS_AQ DEQUEUE
DBMS_AQ DEQUEUE_INTERNAL
DBMS_AQ DEQUEUE_INTERNAL_PAS
DBMS_AQ ENQUEUE
DBMS_AQ ENQUEUE_INT_SHARD
DBMS_AQ ENQUEUE_INT_SHARD_JMS
DBMS_AQ ENQUEUE_INT_UNSHARDED
DBMS_AQ ENQUEUE_INT_UNSHARDED_PAS
DBMS_AQ LISTEN
DBMS_AQ POST
DBMS_AQ REGISTER
DBMS_AQ REGISTRATION_REPLICATION
DBMS_AQ UNBIND_AGENT
DBMS_AQ UNREGISTER
DBMS_AQADM ADD_ALIAS_TO_LDAP
DBMS_AQADM ADD_CONNECTION_TO_LDAP
DBMS_AQADM ADD_SUBSCRIBER
DBMS_AQADM ALTER_AQ_AGENT
DBMS_AQADM ALTER_PROPAGATION_SCHEDULE
DBMS_AQADM ALTER_QUEUE
DBMS_AQADM ALTER_QUEUE_TABLE
DBMS_AQADM ALTER_SHARDED_QUEUE
DBMS_AQADM ALTER_SUBSCRIBER
DBMS_AQADM CREATE_AQ_AGENT
DBMS_AQADM CREATE_EXCEPTION_QUEUE
DBMS_AQADM CREATE_NP_QUEUE
DBMS_AQADM CREATE_QUEUE
DBMS_AQADM CREATE_QUEUE_TABLE
DBMS_AQADM CREATE_SHARDED_QUEUE
DBMS_AQADM DEL_ALIAS_FROM_LDAP
DBMS_AQADM DEL_CONNECTION_FROM_LDAP
DBMS_AQADM DISABLE_DB_ACCESS
DBMS_AQADM DISABLE_PROPAGATION_SCHEDULE
DBMS_AQADM DROP_AQ_AGENT
DBMS_AQADM DROP_QUEUE
DBMS_AQADM DROP_QUEUE_TABLE
DBMS_AQADM DROP_SHARDED_QUEUE
DBMS_AQADM ENABLE_DB_ACCESS
DBMS_AQADM ENABLE_JMS_TYPES
DBMS_AQADM ENABLE_PROPAGATION_SCHEDULE
DBMS_AQADM GET_PROP_SEQNO
DBMS_AQADM GET_REPLAY_INFO
DBMS_AQADM GET_TYPE_INFO
DBMS_AQADM GET_WATERMARK
DBMS_AQADM GRANT_QUEUE_PRIVILEGE
DBMS_AQADM GRANT_SYSTEM_PRIVILEGE
DBMS_AQADM GRANT_TYPE_ACCESS
DBMS_AQADM MIGRATE_QUEUE_TABLE
DBMS_AQADM NONREPUDIATE_RECEIVER
DBMS_AQADM NONREPUDIATE_SENDER
DBMS_AQADM PURGE_QUEUE_TABLE
DBMS_AQADM RECOVER_PROPAGATION
DBMS_AQADM REMOVE_SUBSCRIBER
DBMS_AQADM RESET_REPLAY_INFO
DBMS_AQADM REVOKE_QUEUE_PRIVILEGE
DBMS_AQADM REVOKE_SYSTEM_PRIVILEGE
DBMS_AQADM SCHEDULE_PROPAGATION
DBMS_AQADM SET_WATERMARK
DBMS_AQADM START_QUEUE
DBMS_AQADM START_TIME_MANAGER
DBMS_AQADM STOP_QUEUE
DBMS_AQADM STOP_TIME_MANAGER
DBMS_AQADM UNSCHEDULE_PROPAGATION
DBMS_AQADM VERIFY_QUEUE_TYPES
DBMS_AQADM VERIFY_QUEUE_TYPES_GET_NRP
DBMS_AQADM VERIFY_QUEUE_TYPES_NO_QUEUE
DBMS_AQELM GET_MAILHOST
DBMS_AQELM GET_MAILPORT
DBMS_AQELM GET_PROXY
DBMS_AQELM GET_SENDFROM
DBMS_AQELM GET_TXTIMEOUT
DBMS_AQELM HTTP_SEND
DBMS_AQELM SEND_EMAIL
DBMS_AQELM SET_MAILHOST
DBMS_AQELM SET_MAILPORT
DBMS_AQELM SET_PROXY
DBMS_AQELM SET_SENDFROM
DBMS_AQJMS AQ$_GET_PROP_STAT
DBMS_AQJMS AQ$_GET_TRANS_TYPE
DBMS_AQJMS AQ$_REGISTER
DBMS_AQJMS AQ$_UNREGISTER
DBMS_AQJMS AQ$_UPDATE_PROP_STAT_QNAME
DBMS_AQJMS CLEAR_DBSESSION_GUID
DBMS_AQJMS CLEAR_GLOBAL_AQCLNTDB_CTX_CLNT
DBMS_AQJMS CLEAR_GLOBAL_AQCLNTDB_CTX_DB
DBMS_AQJMS GET_DB_USERNAME_FOR_AGENT
DBMS_AQJMS SET_DBSESSION_GUID
DBMS_AQJMS SET_GLOBAL_AQCLNTDB_CTX
DBMS_AQJMS SUBSCRIBER_EXISTS
DBMS_DBFS_CONTENT_ADMIN EXIM_MOUNT
DBMS_DBFS_CONTENT_ADMIN EXIM_MOUNTP
DBMS_DBFS_CONTENT_ADMIN EXIM_STORE
DBMS_DBFS_CONTENT_ADMIN MOUNTSTORE_LOG
DBMS_DBFS_CONTENT_ADMIN REGISTERSTORE_LOG
DBMS_DBFS_CONTENT_ADMIN UNMOUNTSTORE_LOG
DBMS_DBFS_CONTENT_ADMIN UNREGISTERSTORE_LOG
DBMS_DBFS_SFS NORMALIZEFS
DBMS_DBFS_SFS REORGANIZEFS
DBMS_DBFS_SFS SHRINKFS
DBMS_DBFS_SFS_ADMIN CREATEFILESYSTEM_LOG
DBMS_DBFS_SFS_ADMIN DELETE_ORPHANS_LOG
DBMS_DBFS_SFS_ADMIN DROPFILESYSTEM_LOG
DBMS_DBFS_SFS_ADMIN EXIM_ATTRV
DBMS_DBFS_SFS_ADMIN EXIM_FS
DBMS_DBFS_SFS_ADMIN EXIM_GRANTS
DBMS_DBFS_SFS_ADMIN EXIM_SEQ
DBMS_DBFS_SFS_ADMIN EXIM_SNAP
DBMS_DBFS_SFS_ADMIN EXIM_TABP
DBMS_DBFS_SFS_ADMIN EXIM_TAB_LOG
DBMS_DBFS_SFS_ADMIN EXIM_VOL
DBMS_DBFS_SFS_ADMIN INITFILESYSTEM_LOG
DBMS_DBFS_SFS_ADMIN PARTITION_SEQUENCE_LOG
DBMS_DBFS_SFS_ADMIN RECACHE_SEQUENCE_LOG
DBMS_DBFS_SFS_ADMIN REGISTERFILESYSTEM_LOG
DBMS_DBFS_SFS_ADMIN SETFSPROPERTIES_LOG
DBMS_DBFS_SFS_ADMIN UNREGISTERFILESYSTEM_LOG
DBMS_DDL SET_TRIGGER_FIRING_PROPERTY
DBMS_FGA ADD_POLICY
DBMS_FGA DISABLE_POLICY
DBMS_FGA DROP_POLICY
DBMS_FGA ENABLE_POLICY
DBMS_GOLDENGATE_IMP ACDR_COLUMN
DBMS_GOLDENGATE_IMP ACDR_COLUMN_GROUP
DBMS_GOLDENGATE_IMP ACDR_END
DBMS_GOLDENGATE_IMP ACDR_START
DBMS_GOLDENGATE_IMP ACDR_TABLE
DBMS_REDACT ADD_POLICY
DBMS_REDACT ALTER_POLICY
DBMS_REDACT APPLY_POLICY_EXPR_TO_COL
DBMS_REDACT CREATE_POLICY_EXPRESSION
DBMS_REDACT DISABLE_POLICY
DBMS_REDACT DROP_POLICY
DBMS_REDACT DROP_POLICY_EXPRESSION
DBMS_REDACT ENABLE_POLICY
DBMS_REDACT FPM_MASK
DBMS_REDACT FPM_UNMASK
DBMS_REDACT UPDATE_FULL_REDACTION_VALUES
DBMS_REDACT UPDATE_POLICY_EXPRESSION
DBMS_REDEFINITION ABORT_REDEF_TABLE
DBMS_REDEFINITION ABORT_ROLLBACK
DBMS_REDEFINITION ABORT_UPDATE
DBMS_REDEFINITION CAN_REDEF_TABLE
DBMS_REDEFINITION COPY_TABLE_DEPENDENTS
DBMS_REDEFINITION EXECUTE_UPDATE
DBMS_REDEFINITION FINISH_REDEF_TABLE
DBMS_REDEFINITION REDEF_TABLE
DBMS_REDEFINITION REGISTER_DEPENDENT_OBJECT
DBMS_REDEFINITION ROLLBACK
DBMS_REDEFINITION SET_PARAM
DBMS_REDEFINITION START_REDEF_TABLE
DBMS_REDEFINITION SYNC_INTERIM_TABLE
DBMS_REDEFINITION UNREGISTER_DEPENDENT_OBJECT
DBMS_RESCONFIG ADDREPOSITORYRESCONFIG
DBMS_RESCONFIG ADDRESCONFIG
DBMS_RESCONFIG APPENDRESCONFIG
DBMS_RESCONFIG DELETEREPOSITORYRESCONFIG
DBMS_RESCONFIG DELETERESCONFIG
DBMS_RULE_ADM GRANT_OBJECT_PRIVILEGE
DBMS_RULE_ADM GRANT_SYSTEM_PRIVILEGE
DBMS_RULE_ADM REVOKE_OBJECT_PRIVILEGE
DBMS_RULE_ADM REVOKE_SYSTEM_PRIVILEGE
DBMS_SQL_TRANSLATOR CLEAR_SQL_TRANSLATION_ERROR
DBMS_SQL_TRANSLATOR CREATE_PROFILE
DBMS_SQL_TRANSLATOR DEREGISTER_ERROR_TRANSLATION
DBMS_SQL_TRANSLATOR DEREGISTER_SQL_TRANSLATION
DBMS_SQL_TRANSLATOR DROP_PROFILE
DBMS_SQL_TRANSLATOR ENABLE_ERROR_TRANSLATION
DBMS_SQL_TRANSLATOR ENABLE_SQL_TRANSLATION
DBMS_SQL_TRANSLATOR REGISTER_ERROR_TRANSLATION
DBMS_SQL_TRANSLATOR REGISTER_SQL_TRANSLATION
DBMS_SQL_TRANSLATOR SET_ATTRIBUTE
DBMS_SQL_TRANSLATOR SET_ERROR_TRANSLATION_COMMENT
DBMS_SQL_TRANSLATOR SET_SQL_TRANSLATION_COMMENT
DBMS_SQL_TRANSLATOR SET_SQL_TRANSLATION_MODULE
DBMS_XDBZ ADD_APPLICATION_PRINCIPAL
DBMS_XDBZ CHANGE_APPLICATION_MEMBERSHIP
DBMS_XDBZ DELETE_APPLICATION_PRINCIPAL
DBMS_XDBZ DISABLE_HIERARCHY
DBMS_XDBZ ENABLE_HIERARCHY
DBMS_XDBZ SET_APPLICATION_PRINCIPAL
DBMS_XDB_ADMIN CREATENONCEKEY
DBMS_XDB_ADMIN INSTALLDEFAULTWALLET
DBMS_XDB_ADMIN MOVEXDB_TABLESPACE
DBMS_XDB_ADMIN REBUILDHIERARCHICALINDEX
DBMS_XDB_CONFIG ADDAUTHENTICATIONMAPPING
DBMS_XDB_CONFIG ADDAUTHENTICATIONMETHOD
DBMS_XDB_CONFIG ADDTRUSTMAPPING
DBMS_XDB_CONFIG ADDTRUSTSCHEME
DBMS_XDB_CONFIG CLEARHTTPDIGESTS
DBMS_XDB_CONFIG DELETEAUTHENTICATIONMAPPING
DBMS_XDB_CONFIG DELETEAUTHENTICATIONMETHOD
DBMS_XDB_CONFIG DELETETRUSTMAPPING
DBMS_XDB_CONFIG DELETETRUSTSCHEME
DBMS_XDB_CONFIG ENABLECUSTOMAUTHENTICATION
DBMS_XDB_CONFIG ENABLECUSTOMTRUST
DBMS_XDB_CONFIG ENABLEDIGESTAUTHENTICATION
DBMS_XDB_CONFIG ISGLOBALPORTENABLED
DBMS_XDB_CONFIG SETDYNAMICGROUPSTORE
DBMS_XDB_CONFIG SETGLOBALPORTENABLED
DBMS_XDB_CONFIG SETHTTPCONFIGREALM
DBMS_XDB_VERSION CHECKIN_INT
DBMS_XDB_VERSION CHECKOUT
DBMS_XDB_VERSION MAKEVERSIONED_INT
DBMS_XDB_VERSION UNCHECKOUT_INT
DBMS_XMLINDEX CREATEDATEINDEX
DBMS_XMLINDEX CREATENUMBERINDEX
DBMS_XMLINDEX DROPPARAMETER
DBMS_XMLINDEX MODIFYPARAMETER
DBMS_XMLINDEX PROCESS_PENDING
DBMS_XMLINDEX REGISTERPARAMETER
DBMS_XMLINDEX SYNCINDEX
DBMS_XMLSCHEMA COMPILESCHEMA
DBMS_XMLSCHEMA COPYEVOLVE
DBMS_XMLSCHEMA DELETESCHEMA
DBMS_XMLSCHEMA GENERATEBEAN
DBMS_XMLSCHEMA INPLACEEVOLVE
DBMS_XMLSCHEMA PURGESCHEMA
DBMS_XMLSCHEMA_LSB COPYEVOLVE
DBMS_XMLSCHEMA_LSB REGISTERSCHEMA_BLOB
DBMS_XMLSCHEMA_LSB REGISTERSCHEMA_OID
DBMS_XMLSCHEMA_LSB REGISTERSCHEMA_STR
DBMS_XMLSCHEMA_LSB REGISTERSCHEMA_XML
SDO_META CHANGE_ALL_SDO_GEOM_METADATA
SDO_META CHANGE_IMP_SDO_GEOM_METADATA
SDO_META DELETEGEORSYSENTRY
SDO_META DELETE_ALL_SDO_GEOM_METADATA
SDO_META DELETE_TOPO_METADATA
SDO_META INSERTGEORSYSENTRY
SDO_META INSERT_ALL_SDO_GEOM_METADATA
SDO_META INSERT_TOPO_METADATA
SDO_META UPDATE_TOPO_METADATA
SDO_META_USER CREATEDMLTRIGGER
SDO_META_USER CREATE_TOPO_BTREE
XS_ACL ADD_ACL_PARAMETER
XS_ACL APPEND_ACES
XS_ACL CREATE_ACL
XS_ACL DELETE_ACL
XS_ACL REMOVE_ACES
XS_ACL REMOVE_ACL_PARAMETERS
XS_ACL SET_DESCRIPTION
XS_ACL SET_PARENT_ACL
XS_ACL SET_SECURITY_CLASS
XS_ADMIN_UTIL DROP_SCHEMA_OBJECTS
XS_ADMIN_UTIL GRANT_SYSTEM_PRIVILEGE
XS_ADMIN_UTIL REVOKE_SYSTEM_PRIVILEGE
XS_DATA_SECURITY ADD_COLUMN_CONSTRAINTS
XS_DATA_SECURITY APPEND_REALM_CONSTRAINTS
XS_DATA_SECURITY APPLY_OBJECT_POLICY
XS_DATA_SECURITY CREATE_ACL_PARAMETER
XS_DATA_SECURITY CREATE_POLICY
XS_DATA_SECURITY DELETE_ACL_PARAMETER
XS_DATA_SECURITY DELETE_POLICY
XS_DATA_SECURITY DISABLE_OBJECT_POLICY
XS_DATA_SECURITY ENABLE_OBJECT_POLICY
XS_DATA_SECURITY REMOVE_COLUMN_CONSTRAINTS
XS_DATA_SECURITY REMOVE_OBJECT_POLICY
XS_DATA_SECURITY REMOVE_REALM_CONSTRAINTS
XS_DATA_SECURITY SET_DESCRIPTION
XS_NAMESPACE ADD_ATTRIBUTES
XS_NAMESPACE CREATE_TEMPLATE
XS_NAMESPACE DELETE_TEMPLATE
XS_NAMESPACE REMOVE_ATTRIBUTES
XS_NAMESPACE SET_DESCRIPTION
XS_NAMESPACE SET_HANDLER
XS_PRINCIPAL ADD_PROXY_TO_DBUSER
XS_PRINCIPAL ADD_PROXY_USER
XS_PRINCIPAL CREATE_DYNAMIC_ROLE
XS_PRINCIPAL CREATE_ROLE
XS_PRINCIPAL CREATE_USER
XS_PRINCIPAL DELETE_PRINCIPAL
XS_PRINCIPAL ENABLE_BY_DEFAULT
XS_PRINCIPAL ENABLE_ROLES_BY_DEFAULT
XS_PRINCIPAL GRANT_ROLES
XS_PRINCIPAL REMOVE_PROXY_FROM_DBUSER
XS_PRINCIPAL REMOVE_PROXY_USERS
XS_PRINCIPAL REVOKE_ROLES
XS_PRINCIPAL SET_ACL
XS_PRINCIPAL SET_DESCRIPTION
XS_PRINCIPAL SET_DYNAMIC_ROLE_DURATION
XS_PRINCIPAL SET_DYNAMIC_ROLE_SCOPE
XS_PRINCIPAL SET_EFFECTIVE_DATES
XS_PRINCIPAL SET_GUID
XS_PRINCIPAL SET_PASSWORD
XS_PRINCIPAL SET_PROFILE
XS_PRINCIPAL SET_USER_SCHEMA
XS_PRINCIPAL SET_USER_STATUS
XS_PRINCIPAL SET_VERIFIER
XS_SECURITY_CLASS ADD_IMPLIED_PRIVILEGES
XS_SECURITY_CLASS ADD_PARENTS
XS_SECURITY_CLASS ADD_PRIVILEGES
XS_SECURITY_CLASS CREATE_SECURITY_CLASS
XS_SECURITY_CLASS DELETE_SECURITY_CLASS
XS_SECURITY_CLASS REMOVE_IMPLIED_PRIVILEGES
XS_SECURITY_CLASS REMOVE_PARENTS
XS_SECURITY_CLASS REMOVE_PRIVILEGES
XS_SECURITY_CLASS SET_DESCRIPTION

 

Enjoy!!!


Filed under: Golden Gate
Categories: DBA Blogs

#GoldenGate/#Database Patches

Wed, 2017-10-25 16:35

In searching for patches for Oracle GoldenGate you have to also look for Oracle Database patches. To make this search a bit easier, some of the great guys on the Oracle GoldenGate team has created a My Oracle Support (MOS) note or two. These notes should be upkept over time. For now, just know these are reference points for you to find out what patches are required for your Oracle GoldenGate environments!

Doc Id: 2193391.1 – Latest GoldenGate/Database (OGG/RDBMS) Patch recommendations

Doc Id: 1557031.1 – Oracle GoldenGate — Oracle RDBMS Server Recommended Patches

Enjoy!!!


Filed under: Golden Gate
Categories: DBA Blogs

OGG-00868/ORA-01291 – Missing log files with Integrated Extract

Sun, 2017-10-22 10:05

Oracle GoldenGate is reliant on the Oracle Redo Logs and Archive Logs when capturing transactions. Since I do a lot of testing on VMs (limited space) and in the cloud (limited space … don’t want to burn to much $), I often delete my archive logs. Normally this is not a problem; however, every once-in-awhile I delete more archivelogs than I should. This throws the IE into a state where it will not start because of OGG-00868/ORA-01291 – Missing Log Files.

On some level this is to be expected, but when you are using IE you have to remember that the extract is registered with the database. Since I’m using Integrated Extract, we have to reset how the extract is registered with the database. The below steps will show you how this should be done:

Note: Registering/Unregistering process have to be done at the container database (CDB) level.

adminclient> dblogin useridalias <alias> domain <domain>
adminclient> stop extract <extract_name>
adminclient> unregister extract <extract_name> database
adminclient> register extract <extract_name> database container <container_name>
adminclient> start extract <extract_name>
adminclient> info extract <extract_name>

Once the extract is re-registered with the database; the integrated extract will start.

Enjoy!!!


Filed under: Golden Gate
Categories: DBA Blogs

#GoldenGate Microservices (4 of 5) … Receiver Service

Mon, 2017-09-25 12:23

This is post 4 of a 5 part post related to Oracle GoldenGate 12.3 Microservices. This series of posts will provide some details over the new graphical user interface (GUI) that has been built into Oracle GoldenGate 12.3.

The Receiver Serivice is the least complicated of the microservices with Oracle GoldenGate 12.3 Microservices. This service is taking the place of the collectors in classic architectures. When you fist login to the Reciever Service, you land on the Overview page (Figure 1). On this page, you can clearly see that the host were it is running on what trail file it is writing to.

Figure 1:

If you click on the “Action” button, you willl be presented with a “Details” option. This option takes you to the Path Information page. On this page, you can see the Network Statistics and File IO Statistics (Figure 2). All this information provides you a view into how your transactions are being written to the trail file and at what rate.

Figure 2:

Lastly, if you go back to the overview page. On the context menu, just like other pages, you have a Diagnosis & Reports page (Figure 3). This page, provides you a quick and easy look at the log file associated with the services. It is color coded (greed, yellow, red) to help you quickly identify what needs to be looked at.

Figure 3:

Enjoy!!!


Filed under: Golden Gate
Categories: DBA Blogs

#GoldenGate Microservices (3 of 5) … Distribution Service

Mon, 2017-09-25 09:44

This is post 3 of a 5 part post related to Oracle GoldenGate 12.3 Microservices. This series of posts will provide some details over the new graphical user interface (GUI) that has been built into Oracle GoldenGate 12.3.

The third part of setting up the replication enviornment with Microservices is to understand what the Distributuion Server is. The component is the replacement for the Data Pump Extract in classic architecture. Distribution Server does all the same things as the Data Pump Extract except with from transformations. If you use transformations in the Data Pump, you really should relook where this is being done.

Note: Transformations should be done at the source, target, or with Oracle Data Integrator.

Distribution Server is your path way for shipping trail files from source to target in the Microservices architecture. Let’s take a closer look at this service.

Upon accessing the Distribution Server page, you see a blank page (Figure 1). This is the overview page and where you will configure you distribution paths needed for replication.

Figure 1:

To begin adding a distribution path, click the plus ( + ) sign on the right hand side of the page. This will take you to the Add Path page (Figure 2). On the Add Path page, you will provide the details for the path. These details include extract/trail file to read from, protocol to use for transmissions, log positioning and many other options. A lot of the items you could do in the data pump extract, can be configured on this page as well.

Figure 2:

After adding a distribution path, you will be able to see clearly where the transactions are coming from and being shipped to (Figure 3). You will also see what database, extract, and trail if being used for the transactions.

Figure 3:

If you click on the “Action” button, you are taken to a menu where you can perform operations on the Distribution Path. There is an option for “Details”. By looking at the details, you can quickly see, in more detail, what is happening with the Distrubtuion Path. You can see the source and target, database, extract, trail name, source trail file, as well as many other item (Figure 4). Additonally, on the details page, you can see the statistics for what is running through the Distribution path.

Figure 4:

Lastly, if you return to the context menu on the left hand side, you will see an option for “Diagnosis & Reports”. Here is where you will be able to take a look at the report file for the Distribution Server (Figure 5). This page provides you with a date sorted view of the log file for easy reading. You will also be able to quickly find items that are wrong or warnings by the color indicator used with the log file.

Figure 5:

Enjoy!!!


Filed under: Golden Gate
Categories: DBA Blogs

#GoldenGate sessions at #OOW17

Thu, 2017-09-21 14:52

We are a little over a week out from Oracle Open World 2017; hopefully you are filling out your schedules! This year there is a lot of items going on; so I figured I would help you out and point out where you can find the Oracle GoldenGate sessions this year! With the release of Oracle GoldenGate 12.3, there is a lot of new features and updates. So check them out (in no particular order)!

Sessions:

  • Oracle GoldenGate Product Update and Strategy
    • Tuesday, Oct 03, 5:45 p.m. – 6:30 p.m. | Moscone West – Room 3003
  • Maximizing Availability for Oracle GoldenGate Microservices
    • Tuesday, Oct 03, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3014
  • Oracle GoldenGate for Big Data
    • Wednesday, Oct 04, 4:30 p.m. – 5:15 p.m. | Moscone West – Room 3005
  • Deep Dive into Automating Oracle GoldenGate Using the New Microservices
    • Monday, Oct 02, 1:15 p.m. – 2:00 p.m. | Moscone West – Room 3010
  • Accelerate Cloud Onboarding Using Oracle GoldenGate Cloud Service
    • Tuesday, Oct 03, 3:45 p.m. – 4:30 p.m. | Moscone West – Room 3024
  • Oracle Data Integrator and Oracle GoldenGate for Big Data
    • Wednesday, Oct 04, 11:30 a.m. – 12:30 p.m. | Hilton San Francisco Union Square (Ballroom Level) – Continental 4
  • Replicate Data Across Data Centers with Equinix and Oracle GoldenGate
    • Wednesday, Oct 04, 10:30 a.m. – 10:50 a.m. | The Exchange @ Moscone South – Showcase Theater 1
  • An Enterprise Databus: Oracle GoldenGate in the Cloud Working with Kafka and Spark
    • Wednesday, Oct 04, 3:30 p.m. – 4:15 p.m. | Moscone West – Room 3003
  • Oracle GoldenGate Cloud Service: Real-Time Data Replication in the Cloud
    • Monday, Oct 02, 6:00 p.m. – 7:00 p.m. | Hilton San Francisco Union Square (Ballroom Level) – Continental 4
  • Best Practices and Deep Dive on Oracle GoldenGate 12.3 Microservices at Cloud
    • Wednesday, Oct 04, 3:30 p.m. – 4:15 p.m. | Moscone West – Room 3011
  • How to Replicate Data in the Cloud in Real Time
    • Wednesday, Oct 04, 11:00 a.m. – 1:00 p.m. | Hilton San Francisco Union Square (Lobby Level) – Golden Gate 2/3
  • Oracle Data Integration Platform Cloud Strategy and Roadmap
    • Monday, Oct 02, 12:15 p.m. – 1:00 p.m. | Moscone West – Room 3024
  • Oracle Data Integration Platform Empowers Enterprise-Grade Big Data Solutions
    • Monday, Oct 02, 4:45 p.m. – 5:30 p.m. | Marriott Marquis (Golden Gate Level) – Golden Gate C1/C2
  • Kafka’s Role in Implementing Oracle’s Big Data Reference Architecture  
    • Sunday, Oct 01, 1:45 p.m. – 2:30 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 12
  • Oracle Maximum Availability Architecture Best Practices: Oracle Database 12 c
    • Tuesday, Oct 03, 5:45 p.m. – 6:30 p.m. | Moscone West – Room 3006

As you can tell, there is a lot of information that is going to be helpful for your Oracle GoldenGate, BigData and various other areas. Make sure you check all these out.

Enjoy!!


Filed under: Golden Gate
Categories: DBA Blogs

Pages