DBA Blogs

Modern Customer Experience 2018 was Legendary

During his keynote at Modern Customer Experience 2018, Des Cahill, Head CX Evangelist, stated that CX should stand for Continuous Experimentation. He encouraged 4,500 enthusiastic marketers, customer...

We share our skills to maximize your revenue!
Categories: DBA Blogs

The Most Important Stop on Your Java Journey

Howdy, Pardner. Have you moseyed over to JavaRanch lately? Pull up a stool at the OCJA or OCJP Wall of Fame and tell your tale or peruse the tales of others.  Ok - I'm not so great at the...

We share our skills to maximize your revenue!
Categories: DBA Blogs

What's New with Oracle Certification - May

Stay up to date with the Oracle Certification Program. Keep informed with new exams released into production, get information on current promotions, and learn about new program announcements. New...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Confusion surrounding "buffer busy waits" and "read by other session" wait events

Tom Kyte - Fri, 2018-05-18 05:06
Tom - I'm investigating a relatively minor but attention-getting issue that occurs from time to time around my workplace. I observe a few dozen identical queries (select only - no DML) generated by an Oracle Financials concurrent program being ex...
Categories: DBA Blogs

Get the length of CLOB columns in bytes

Tom Kyte - Fri, 2018-05-18 05:06
Hello Tom, I have a table with a CLOB column: <code> create table plch_clob (i int primary key, x clob); begin for indx in 1 .. 1000 loop insert into plch_clob( i, x) values (indx, 'CLOB Row: ' || indx); end loop; ...
Categories: DBA Blogs

Data Masking

Tom Kyte - Fri, 2018-05-18 05:06
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...
Categories: DBA Blogs

Read only partitions in 12.2

Tom Kyte - Fri, 2018-05-18 05:06
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...
Categories: DBA Blogs

Autonomous Oracle Visual Builder Cloud Service

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!
Categories: DBA Blogs

Partner Webcast – Autonomous Data Warehouse in the Cloud: Practical Use Case Guide

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!
Categories: DBA Blogs

Generating Duplicate Rows

Tom Kyte - Tue, 2018-05-15 03:46
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...
Categories: DBA Blogs

Loading Tables with Oracle GoldenGate and REST APIs

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

DB Change from 12.1 to 12.2 RAC getting ORA-02297

Tom Kyte - Mon, 2018-05-14 09:26
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...
Categories: DBA Blogs

Sorting and comparation alphanumeric

Tom Kyte - Mon, 2018-05-14 09:26
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 ------------------------------ -------------------...
Categories: DBA Blogs

SQL Query to remove duplicate values across columns

Tom Kyte - Mon, 2018-05-14 09:26
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 ...
Categories: DBA Blogs

Logical Storage Structures\chained and migrated rows

Tom Kyte - Mon, 2018-05-14 09:26
if we have multiple datafiles of 32GB and there are fully utilized. Can chained and migrated rows occurs due to the maximum datafile utilization?
Categories: DBA Blogs

performance tunning for Sql query

Tom Kyte - Mon, 2018-05-14 09:26
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...
Categories: DBA Blogs

Tables Access

Tom Kyte - Mon, 2018-05-14 09:26
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...
Categories: DBA Blogs

Connecting to Database.

Tom Kyte - Mon, 2018-05-14 09:26
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...
Categories: DBA Blogs

XMLSERIALIZE dynamic order by statement

Tom Kyte - Sat, 2018-05-12 02:26
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...
Categories: DBA Blogs

All Parent - Child tables in the database

Tom Kyte - Fri, 2018-05-11 08:06
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 ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs