DBA Blogs

Does Oracle provide a package or function for data masking ? For example, in a development environment, for data protection purposes, the information of the table customer needs to be masked. create table customer (last_name varchar2(25), first_n...

Team, Started reading about Read Only partitions in 12.2 <u>http://docs.oracle.com/database/122/VLDBG/partition-create-tables-indexes.htm#VLDBG-GUID-9D7149B6-A2FF-47CA-8F00-47CBFD33F82B</u> <quote> A higher level setting of the read-only cl...

Visual Application Development and Hosting The new Autonomous Oracle Visual Builder Cloud Service (AVBCS) introduces a new architecture that turns VBCS into an ideal development and hosting...

We share our skills to maximize your revenue!

Today’s leading-edge organizations differentiate themselves through analytics to further their competitive advantage by extracting value from all their data sources. However, the velocity and...

We share our skills to maximize your revenue!

Hi I am new to oracle plsql and want advise on a Biz scenario: Biz want to run a shipping label report and each shipping has one record. They will choose particular shipping record and based on provided parameter they want to see number of labe...

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!!!

We recently changed from a 12.1 Single Instance to a 12.2 Version running in a RAC environment with two nodes. Wa are now facing the problem that a PL/SQL procedure nearly takes factor 3 longer to run than on the single instance. Our Adminis...

Hello Tom, We have configure the client this way: <code>SQL> select parameter, value from nls_session_parameters where parameter in ('NLS_COMP','NLS_SORT','NLS_LANGUAGE'); PARAMETER VALUE ------------------------------ -------------------...

I have three field in the table say from_city,to_city and distance. <code> Table name: City_distance From_city to_city distance ---------------------------------------- A B 100 B A 100 C B 200 C A 300 E F 700 F E 700 </code> Here I ...

if we have multiple datafiles of 32GB and there are fully utilized. Can chained and migrated rows occurs due to the maximum datafile utilization?

hi Team, I am struggling in one of query tunning , below are details assoicated with , please analyse and recommend on it . <code> SELECT * FROM (SELECT /*+ INDEX(A INDX14_TABLE1) INDEX(B IDX51_TABLE2) */ * F...

In our Production database is has been decided to drop a tablespace because already a datafile was lost due to accidentally file was dropped at OS level with command rm -rf, somehow luckily there was not huge loss but some of index which were cre...

Hi, I have two versions of Oracle Databses(12C,11G) installed on my personal computer running on Windows 8. When i execute below command its connecting to 12C instance. sqlplus system/welcome; i would like to know how can i connect to 11G instan...

Hi, I've the following problem. I call the function with the order-by parameter, but it will not be used. When I use the order-by hardcoded, the values ??are sorted. Do you have any idee what is wrong in the function or the calling? Than...

Hi Tom, Can you please explain the way to get a list of all parent child relation in the database. The list should have the Grand parent as the first item and the last item will be the grand child. For Example, Parent ...

Hi , I have faced a situation where sql id plan hash value is changed due stats gather on one of table currently i dont understand why this stats gathering cause chnage in plan and due to which execution time is poor now can you guide...

Hi Tom, i would like to apply the Orignial SQL Statement from Oracle MOS DOC ID 1317265.1 and 1309070.1 for license and healthcheck for all of my database instances. My Goal is to create a centralized repository with informations of my databases. Un...

My question why import is not failing even the source and target have different table structure <b>Source DB</b> has below table (with additional column COL3 and populated SQL> desc tab1 Name Null? Type ---------------------------...

Dear tom, How can i enforce unique key across multiple tables. Table1 and Table2 both have ID primary key column. Is it possible to restrict, while inserting and updating into these tables, the union of ID values from two tables are unique. r...

hi tom, during one interview i got one question in sequence i.e if there is one sequnce whose max value is 40,but after got nextval 20.without execute the select query 20,000 times and without alter the sequence i want to get 20,000 in the nextva...