Oracle GoldenGate 12c database replication workshop

Name: Oracle GoldenGate 12c database replication workshop
Rating: 4.4 (500 reviews)

Learn database replication by creating virtual machines with Oracle GoldenGate 12c on Oracle 12c Multitenant databases.

Created byJavier Morales Carreras

Last updated 10/2021

English

EnglishSpanish

What you'll learn

Understand all the basics about Oracle databases replication with Oracle GoldenGate tool
Create a duplicated and synchronized environment for emergency or reporting
Perform a database migration with (almost) zero downtime
Learn a lot of stuff about Oracle12c architecture, Multitenant databases, new features of GoldenGate12c, ...
Create a lab test environment with two OraLinux servers with Oracle to test logical replications

Course content

7 sections • 39 lectures • 1h 37m total length

Welcome to the Practical Oracle GoldenGate12c course1:33
Welcome to the course. Let me introduce a brief summary of what you will learn in this practical workshop about Oracle GoldenGate12c
Overview of Oracle GoldenGate12c2:17
Let's take a look at what the software Oracle GoldenGate does and let's know a bit more of the different configurations we may set up for integrating different databases with GoldenGate.
Logical replication vs. physical replication3:02
First of all, we need to understand the difference between logical and physical replication. It will restrict the operations and possibilities of each replication environment.
Description of other GoldenGate products2:14
There are other products apart from the main Oracle GoldenGate12c that can be useful. For example, we will need to monitor the GoldenGate processes and check the integrity between data on source and target. Here is a list of other complementary tools we may download from eDelivery site with our OTN account.
Quiz

Oracle VirtualBox and virtual machines1: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 GoldenGate1: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)2: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 VirtualBox1: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
Set up the virtual network1: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
Download the VM template and generate two virtual machines3: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
Set up host connectivity1: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
Set up the /etc/hosts file1: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
Set up database connectivity1: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
Install the Oracle GoldenGate software4:08
We have the servers and the databases ready. Now is time to install the Oracle VirtualBox 12c product.
Install the Oracle GoldenGate software
Set up the Oracle GoldenGate environment2:56
This software requires an initial setup. Let's prepare it.
Setup Oracle GoldenGate environment
Database setup for Oracle GoldenGate 12c4: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
REVIEW - Check your environment1: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 SCN2: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 logging1:52
Set up the database logging
Restore database at a point in time (SCN)2: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 architecture3: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 process2:26
First we will start the manager process. It controls and manages all GoldenGate processes.
Create the manager process
Methods of data capture: classic and integrated1: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 command2: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
Replicate data in the same database3: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
Monitoring the replication1: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 commands0:53
Let's take a look at some easy commands really useful for the command line interface.
The GLOBALS parameter file1: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 checking2: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 server2: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
Consistent schema copy based on a given SCN2: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
Create the pump process based on SCN number3: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
Create the replicat process on the target server1: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
Some considerations about sequences and triggers1: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.

Requirements

Basic knowledge of database architecture
Some knowledge of Oracle databases and linux Operative Systems
Some knowledge about virtual machines
Having a PC or laptop with 4GB of RAM (optimal) available to create two virtual servers (2GB each) and disk space available of 50GB for the virtual servers disks.

Description

Are you ready to create your own database replication environment with Oracle GoldenGate 12c? and have in your PC a complete database environment with two oracle servers and Oracle GoldenGate 12c installed and set up to replicate data from one server to another? and do you want to test it all in real, with all scripts and logs, to make it all work in your self-made test environment?

Then this course is for you. This is a practical workshop where you will learn how to replicate Oracle12c databases using Oracle GoldenGate12c by building your own replication environment. The course will give you the basics and the step by step practices to reproduce, in your virtual machines, a feel like production environment where you will replicate a database schema keeping it in sync with the source database.

In the course you will be guided step by step to set up and verify all steps in the Oracle GoldenGate12c installation and configuration, by using VirtualBox virtual machines with a pre-built Oracle12c multitenant database ready to use. You will start up in your laptop or PC two virtual machines with OraLinux servers and you will practice all the exercises in a total real environment.

So, even the workshop is based on OraLinux and Oracle12c multitenant, the replication theory would be valid for any supported environment you want to replicate: SQLServer through an Oracle server, or an Oracle Server to a DB2, etc. and including different versions like replicating data from an old Oracle11g to a Oracle12c with or without multitenant.

The course is divided into three main sections:

1.- Laboratory installation, which will include detailed instructions to install and set up: Oracle VirtualBox, two virtual machines with OraLinux, each Oracle database server for GoldenGate, The installation and configuration of Oracle GoldenGate12c and a sample schema.

2.- Basic replication, using the same database as source and target, for a single table.

3.- Advanced replication, using the two servers to propagate all transactions from the source database to the target database at schema level (all tables and sequences). You woulc use this method to perform a database version upgrade to a new database release, having two environments in sync, and doing the upgrade by just stopping the source environment and starting the target database to the users. This is often called "upgrade with almost zero downtime", and it's one of the most valuable upgrading procedures when it comes to upgrade critical servers.

In other words, you will learn how to build a new replicated environment you may use as reporting database, or quality assurance test servers where you may test live your "go to production" procedures, or using it as a DR (disaster recovery) database copy ready to use in production in a severe lost of the main database, with all up to date transactions!.

And so, you will find lots of chapters with important information about the Oracle GoldenGate funcionality and the particular considerations about Oracle database at administration level, such as setting up the supplemental login, how to manage multitenant Oracle12c database, important aspects about the logging files and transactional processing, or how to copy a schema consistent to a point in time to create an identical target based on a SCN.

The course also includes quizzes, checks, scripts, dictionary of terms and concepts, and basic troubleshooting guide it may be useful in your daily work. It's a complete guide and workshop with a practical laboratory you may use and keep for further tests and checks.

Who this course is for:

Database Administrators
Application developers or administrators
Anyone who wants to know the tech aspects of a logical database replication between Oracle databases with Oracle GoldenGate tool
Project leaders who want to know or participate in replication projects

Oracle GoldenGate 12c database replication workshop

What you'll learn

Explore related topics

Course content

Introducction to Oracle Golden Gate4 lectures • 9min

Preparing your environment13 lectures • 30min

Oracle GoldenGate infrastructure and other things you must know before continue4 lectures • 10min

Table replication to the same database5 lectures • 11min

Managing the Oracle GoldenGate12c command line interface3 lectures • 5min

Schema replication to a remote database5 lectures • 12min

EXTRA: Software, official documentation and other resources5 lectures • 6min

Requirements

Description

Who this course is for: