Practical Oracle Goldengate12c Workshop

Oracle VirtualBox and virtual machines Preview 01:40

How will we simulate we have two servers with two databases to test replication through the network? With Oracle VirtualBox. It's a free virtualization tool perfect to create your test labs. Here is an introduction to the topic

OraLinux Oracle12c with GoldenGate Preview 01:50

The OraLinux7 virtual machine we will use has a Oracle12c database with multitenant. Let's take a look at it.

Other possible installations (optional) Preview 02:01

Of course, this workshop is one example of many different scenarios. Here are some ideas if you plan to repeat the course trying different virtual servers or different database versions.

Download and Install Oracle VirtualBox Preview 01:39

First of all we will use the Oracle VirtualBox software. It's an application to create virtual servers in our laptop and we will use to create two servers: CLAPTON and MOORE.

Download and install Oracle VirtualBox

In this task you must install Oracle VirtualBox in order to manage the two servers we will use in our lab. Remember, you have to download the version of the PC or laptop you are using for creating your lab environment

Set up the virtual network Preview 01:33

The virtual servers must be able to establish connections between them, so they must be placed in a virtual network and will have IP ranges assigned to their own mac addresses.

Set up the virtual network

Oracle VirtualBox will emulate a host where two OraLinux servers will be connected to a virtual network. The next step is to set up Oracle VirtualBox virtual networks to allow the two virtual machines to be seen under the same network.

Download the VM template and generate two virtual machines Preview 03:19

We will use a VirtualBox template to generate the two virtual machines. They will be initially identical. This is the virtual server based on OraLinux, with an Oracle12c database with multitenant.

Download the VM template and generate two virtual machines

We need two virtual servers in our VirtualBox, so we will download a template and we will generate two virtual machines for our tests.

Set up host connectivity Preview 01:16

Let's make both virtual servers be placed in the same virtual network we just created. Those servers will receive an IP within the range we already set up.

Set up host connectivity

Now, we will set up the virtual servers to use our NAT network and we will check they can ping each other though their own IP address

Set up the /etc/hosts file Preview 01:48

This step will easy the communication between hosts. Once we know the IP addresses for both servers moore and clapton, we will be now ready to name them by their given names updating the file /etc/hosts file as shown in the class.

Set up the /etc/hosts file

Instead of working with each IP address, we may set up the file /etc/hosts to resolve the name locally for our servers clapton and moore.

Set up database connectivity Preview 01:59

And as same as we did with the hosts, it's also important that we can call each database by their name. We will achieve this by modifying the tnsnames.ora file.

Set up database connectivity

In this step we will create an alias for each database. We also want to name it clapton and moore, so we will have to edit the file $ORACLE_HOME/network/admin/tnsnames.ora

Install the Oracle GoldenGate software Preview 04:08

We have the servers and the databases ready. Now is time to install the Oracle VirtualBox 12c product.

Install the Oracle GoldenGate software

Now we have to install the Oracle GoldenGate 12c software on each virtual machine.

Set up the Oracle GoldenGate environment Preview 02:56

This software requires an initial setup. Let's prepare it.

Setup Oracle GoldenGate environment

We need to set up some variables, like LD_LIBRARY_PATH and OGG_HOME, and undefine another named TWO_TASKS for the GoldenGate and sqlplus run properly. Once the environment is set up, let's execute the GoldenGate command line and create the directories that GoldenGate will use.

Database setup for Oracle GoldenGate 12c Preview 04:04

And so, we also have to prepare some stuff in the database, for example the GoldenGate user we will name gg_user and assign privileges to it.

Database setup for Oracle GoldenGate12c

The Oracle GoldenGate application stores information in a repository and uses a database user to manage the extraction and replication processes. We will create the tablespace and the user, with all the needing privileges

REVIEW - Check your environment Preview 01:45

Take two minutes to ensure both servers have the installation performed and all tests are successfull.

The oracle listener is a process that enables remote connections to the database, and the status may be queried by the command lsnrctl status OS command.

[oracle@clapton ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 23-JUN-2018 09:37:08

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                21-JUN-2018 17:29:24
Uptime                    1 days 16 hr. 7 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           orcl12c
Listener Parameter File   /u01/app/oracle/product/12.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/clapton/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=clapton)(PORT=8081))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "51c99766d7e2568de0530100007f4fae" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12c" has 2 instance(s).
  Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12cXDB" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
The command completed successfully

Connection to the database from the GoldenGate command line interface should be done with the login command like this:

[oracle@clapton OGG]$ ./ggsci 

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Apr 16 2018 00:53:30
Operating system character set identified as UTF-8.

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

GGSCI (clapton) 1> dblogin userid c##gg_user@clapton, password gg_user
Successfully logged into database ORCL.

The importance of SCN Preview 02:45

It is important to know the SCN number usage at database level, because it's the key for a proper replication after a point in time safely.

Understanding database logging Preview 01:52

Set up the database logging

There are three things needed for a proper GoldenGate replication about database logging. This is an easy task to set those three features ready.

Restore database at a point in time (SCN) Preview 02:13

When we will start replicating data, we must ensure both databases are identical or, in other words, that the replicated database is identical to the source at a certain SCN. We can make it restoring a physical backup and applying redolog until the desired SCN or, something quite easier, using the utility export Data Pump with the parameter FLASHBACK_SCN

Some notes about Oracle12c multitenant architecture Preview 03:21

In the workshop we will use a multitenant featured Oracle12c database based on container database and pluggable databases. It's important to understand the syntax concepts of the user creation and the privileges granted with the clause CONTAINER to adapt it if we finally implement GoldenGate in a non multitenant environment.

Create the manager process Preview 02:26

First we will start the manager process. It controls and manages all GoldenGate processes.

Create the manager process

Now it's turn to hands on the ggsci command line interface and start creating the first process: the manager.

Methods of data capture: classic and integrated Preview 01:40

In the course we will use a extraction method called "integrated". This chapter discusses about this method and the classic one for us to evaluate, in our production environment, which one to use.

Create the extract process - The ADD command Preview 02:40

Everything is ready to create the first extract process. We will only extract the transactions from one table: HR.EMPLOYEES

Create the extract process

Now it's your turn to create the extract process.

Replicate data in the same database Preview 03:16

This chapter replicates the source table EMPLOYEES to EMPLOYEES_CLONE. It's done in the same database and no file transfer over the network is done.

Create the replicat process

As described in the class, now create the replicat process and start replicating data from HR.EMPLOYEES to HR.EMPLOYEES_CLONE

Monitoring the replication Preview 01:14

We should query the INFO and STATS for our processes to get information about the performance, the usage and the statistics of the replication.

Some useful commands Preview 00:53

Let's take a look at some easy commands really useful for the command line interface.

The GLOBALS parameter file Preview 01:55

The GLOBALS file and the command OBEY can help us a lot to easy the logins, for example, and some global parameter definition

Error checking Preview 02:07

Debuging errors of abended processes is the key. Learn how to check errors easy and manage the report and discarded files.

Modify extract process on source server Preview 02:03

We can use the initial ext1 extract process to replicate to the remote server, but we have to perform some changes before to extract all tables from the HR schema.

Modify the extract process ext1

The extract ext1 has to extract now all tables in HR schema. Follow the steps to modify the extraction

Consistent schema copy based on a given SCN Preview 02:33

We need at the target database an exact copy of the source schema at a given SCN. We will use this number for syncronize the extract, pump and replicate processes.

Duplicate HR schema at a consistent SCN using Export and Import Data Pump utilit

Now you have to clone the HR schema and create it in the remote database moore.

Create the pump process based on SCN number Preview 03:25

We will create a new extract process to pump the files to the remote server. This process will take the extracted files and place them in the remote target host.

Create the extract process for PUMP

As I mentioned in the class, it's a good practice to separate the extract process to the pump process, so let's create the pump

Create the replicat process on the target server Preview 01:56

And now we have the files in the remote server, we just have to create the replicat process, as same as we did in the clapton server.

Create the replicat process in the remote database

Now in the remote server moore you have to create the replicat process to apply the files pumped from clapton with the new transactions

Some considerations about sequences and triggers Preview 01:52

If your replication source environment does have triggers and sequences, you must check the extraction is properly set up to update sequences and the triggers are disabled at the target side to avoid duplication of the trigger coding transactions.

List of software used in the course. Preview 00:07

Other VM's and additional products Preview 00:07

Official documentation of Oracle GoldenGate12c Preview 00:03

Dictionary - glossary of terms used in the course Preview 01:40

Basic troubleshooting Preview 03:34