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:
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
or if we are extracting from two different PDBs (sales,hr) located in the same container database we can specify it like this
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 220.127.116.11.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 18.104.22.168.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 22.214.171.124.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 126.96.36.199.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select distinct cust_city from customers; CUST_CITY ------------------------------ Perth