Using GoldenGate 12c with an Oracle 12c Multitenant database

This note illustrates an example of using GoldenGate 12c with Oracle 12c Multitenant Container databases.

While in most ways, Oracle GoldenGate operates in a multitenant container database the same way that it operates in a regular Oracle database, we will examine some of the main differences when it comes to configuring extract and replicat processes when they are connecting to a pluggable database.

Remember that all PDBs or Pluggable Databases belonging to one single container database share the same redo stream. So GoldenGate has to filter out the redo records for PDBs which it does not need. At the same time each PDB has its own data dictionary so GoldenGate needs to track multiple data dictionaries.

Here are some of the things to keep in mind when dealing with OGG and 12c multitenant architecture.

• It is available only in integrated capture mode and not classic capture.

• One extract can be configured to capture changes from multiple PDBs

• Since we have to use integrated capture mode, a log mining server is involved and this is only accessible from the root container (CDB$ROOT).

• We have to connect as a common user to attach to the log mining server. For example a user called C##GGADMIN is what we are using in our example.

• There is a 3 part naming convention in GGSCI and Parameter file. For example Container Name.Schema.Table(or Sequence)

• The parameter SOURCECATALOG when used enables us to use the earlier Schema.Table type naming convention.

• Replicat can only connect and apply to one pluggable database.

• The dbms_goldengate_auth.grant_admin_privilege package grants the appropriate privileges for capture and apply within a multitenant container database. This includes the container parameter, which must be set to ALL, as shown in the following example:

dbms_goldengate_auth.grant_admin_privilege(‘C##GGADMIN’,container=>all)

Let us now look at an example.

We have our source 12c container database (CONDB2) and we want to replicate the SH schema located in the pluggable database SALES to a target 12c pluggable database SALES_DR.

Note that in our source database we have set up supplemental logging by running the following SQL statements:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL>SELECT SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING FROM v$database;

We have also created a common user called C##GGADMIN and granted all the required privileges

Add supplemental logging for the SH schema

Note: here we are connecting to the pluggable database SALES

GGSCI (orasql-001-dev.mydomain) 1> dblogin userid C##GGADMIN@sales password welcome1
Successfully logged into database SALES.

GGSCI (orasql-001-dev.mydomain) 2> ADD SCHEMATRANDATA SH ALLCOLS

2014-01-09 16:02:29  INFO    OGG-01788  SCHEMATRANDATA has been added on schema SH.

2014-01-09 16:02:30  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema SH.

2014-01-09 16:02:30  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema SH.

Register the Integrated Extract

Note: here we are connecting to the root container database.

GGSCI (orasql-001-dev.mydomain) 1>  dblogin userid C##GGADMIN password welcome1

Successfully logged into database CDB$ROOT.

GGSCI (orasql-001-dev.mydomain) 6> REGISTER EXTRACT ext1 DATABASE  CONTAINER (sales)

Extract EXT1 successfully registered with database at SCN 2147029.

Add the Extract and Data Pump process groups

GGSCI (orasql-001-dev.mydomain) 7> ADD EXTRACT ext1 INTEGRATED TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (orasql-001-dev.mydomain) 8> ADD EXTTRAIL ./dirdat/lt EXTRACT ext1
EXTTRAIL added.

GGSCI (orasql-001-dev.mydomain) 9> ADD EXTRACT extdp1 EXTTRAILSOURCE ./dirdat/lt BEGIN NOW
EXTRACT added.

GGSCI (orasql-001-dev.mydomain) 10> ADD RMTTRAIL ./dirdat/rt EXTRACT extdp1
RMTTRAIL added.

Note the use of the parameter SOURCECATALOG in the extract parameter file.

SOURCECATALOG specifies a default container in an Oracle multitenant container database for subsequent TABLE or SEQUENCE statements. Enables the use of legacy two-part naming convention (schema.object) where three-part names otherwise would be required for those databases.

So instead of having to use the PDB name SALES and then the schema name SH followed by the table or sequence name,we can just specify the PDB name via the SOURCECATALOG keyword and this will apply until another PDB name is specified by another SOURCECATALOG parameter

SOURCECATALOG sales
TABLE sh.*;

or if we are extracting from two different PDBs (sales,hr) located in the same container database we can specify it like this

SOURCECATALOG sales
TABLE sh.*;
TABLE oe.*;
SOURCECATALOG hr
TABLE hr.*

Note – we will discuss the new 12c Credential Store feature and USERIDALIAS parameter in a subsequent post

GGSCI (orasql-001-dev.mydomain) 11> edit params ext1
EXTRACT ext1
SETENV (ORACLE_SID='condb2')
USERIDALIAS gg_owner
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
EXTTRAIL ./dirdat/lt
SOURCECATALOG sales
TABLE sh.*;

GGSCI (orasql-001-dev.mydomain) 12> edit params extdp1

EXTRACT extdp1
SETENV (ORACLE_SID='condb2')
USERIDALIAS gg_owner
RMTHOST orasql-001-test, MGRPORT 7809 
RMTTRAIL ./dirdat/rt
SOURCECATALOG sales
TABLE sh.*;

Add the Replicat process group connected to the target PDB SALES_DR

Note – we are using something new in 12c OGG called Integrated Replicat – we will discuss more about Integrated Replicats in another post.

GGSCI (orasql-001-test.mydomain) 2> DBLOGIN USERID C##ggadmin@sales_dr, PASSWORD welcome1
Successfully logged into database SALES_DR.

GGSCI (orasql-001-test.mydomain) 4> ADD REPLICAT rep1 INTEGRATED EXTTRAIL ./dirdat/rt
REPLICAT (Integrated) added.

GGSCI (orasql-001-test.mydomain) 5> edit params rep1
REPLICAT rep1
SETENV (ORACLE_SID='condb2')
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
USERID C##GGADMIN@sales_dr, PASSWORD welcome1
ASSUMETARGETDEFS
MAP sales.sh.*, TARGET sales_dr.sh.*;

Start the Integrated Extract and Data Pump

GGSCI (orasql-001-dev.mydomain) 11> start extract ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI (orasql-001-dev.mydomain) 12> info extract ext1

EXTRACT    EXT1      Initialized   2014-01-09 16:14   Status STARTING
Checkpoint Lag       00:00:00 (updated 02:03:22 ago)
Process ID           1139
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2014-01-09 16:14:32
                     SCN 0.0 (0)

GGSCI (orasql-001-dev.mydomain) 14> start extract extdp1

Sending START request to MANAGER ...
EXTRACT EXTDP1 starting

GGSCI (orasql-001-dev.mydomain) 15> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:06      00:00:01
EXTRACT     RUNNING     EXTDP1      00:00:00      01:59:48

GGSCI (orasql-001-dev.mydomain) 18> info extract ext1

EXTRACT    EXT1      Last Started 2014-01-09 18:18   Status RUNNING
Checkpoint Lag       00:00:06 (updated 00:00:09 ago)
Process ID           1139
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2014-01-09 18:21:29
                     SCN 0.2195794 (2195794)

GGSCI (orasql-001-dev.mydomain) 19>  info extract extdp1

EXTRACT    EXTDP1    Last Started 2014-01-09 18:18   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Process ID           1216
Log Read Checkpoint  File ./dirdat/lt000000
                     2014-01-09 16:18:48.000000  RBA 1478

Connect to source PDB and make some changes

[oracle@orasql-001-dev goldengate]$ sqlplus sh/sh@localhost:1525/sales

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 9 18:22:38 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Thu Jan 09 2014 15:35:34 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> update customers set cust_city='Perth';

55500 rows updated.

SQL> commit;

Connect to target PDB and confirm changes have been applied

[oracle@orasql-001-test]$ sqlplus sh/sh@localhost:1525/sales_dr

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jan 15 10:55:56 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Fri Jan 10 2014 13:35:21 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  select distinct cust_city from customers;

CUST_CITY
------------------------------
Perth