Configure #Oracle #GoldenGate #12c for testing between two or more #db12c PDBs.

Over the last few weeks I’ve been working and traveling a good bit.  Amongst all the travel I’ve been trying to get time to test a few things.  One test I wanted to get done was using Oracle GoldenGate 12c with Oracle Database 12c between Pluggable Databases (in the same instance).   As of today, I’ve finally gotten my Oracle Virtual Box environment configured and ready, so now I can travel and still test items I want to test.
With setting up this environment, I’ve identified a few things that may be of interest to the community.  Before I go into what I’ve had to configure to make this environment work, I have to say that the Oracle documentation for Oracle GoldenGate 12c is decent but still missing a few thing.  The organization of the documentation could improve a good bit as well (personal opinion).
Note: As far as the documentation goes, it seems like downloading the PDF is the better option and a bit more detailed in steps compared to the web version.
In my VM environment, I’m using the following software:

• Oracle Enterprise Linux 6.5
• Oracle Database 12c Enterprise Edition (12.1.0.1)
• Oracle GoldenGate 12c (12.1.2)

Linux:

I’ve just setup a simple Oracle Enterprise Linux 6.5 box.  Not very big, but for a VM it could be considered pretty large.  The VM consists of the following (screen shots provided for each part).

Memory:

3072MB
(could go smaller and may go smaller as I shrink the SGA/PGA requirements for testing)

CPU:

2 Processors with 100% execution cap

Storage:

Using 7 VMDK disks (File system and ASM disks).
The reason for so many disks is because I’m using Oracle ASM and Restart to keep database files and database instance consistent upon restarting of the VM.

Network:

NAT with port forwarding
For more information on port forwarding, a good blog post for this was written by my friend Maaz Anjum on his blog (VirtualBox, NAT, and Port Forwarding).

Database:

The database is the currently available version of Oracle Database 12c (12.1.0.1) and Grid Infrastructure that you can download from Oracle Technology Network (OTN).  Once the software is downloaded, it can be installed within the VM.  There are many different steps that need to be addressed with the Oracle Grid Infrastructure 12c to get ASM working right.  In my configuration I’m using ASMLib; however the Oracle RACSIG has provided some exceptional documentation on how to setup Oracle RAC 12c using Virtual Box VMs with ASM not using ASMLIB.  It is a good starting point if needed.
Once the Oracle Database 12c (12.1.0.1) software is installed, a Consolidated Database (CDB) with two Pluggable Databases (PDB) need to be created.  The DBCA will allow you to create a CDB with one PDB and additional PDBs with more runs of DBCA.  The way I created my second PDB  was to create the first PDB (PDB1)  and configure it for what I needed in it for Oracle GoldenGate.  Then I cloned PDB1 to create PDB2 with all the settings in place.
I don’t want to spend to much time on the cloning process of PDBs in this post; however, Oracle provides a lot of different ways for cloning PDBs.  I have listed a common ones below:

• SQL
• EM Express
• SQL Developer (easiest way)

Note: To make cloning of PDBs easy, recommend staying with Oracle Managed Files (OMF).

GoldenGate:

I will say configuring Oracle GoldenGate 12c (12.1.2) with Oracle Database 12c (12.1.0.1) using PDBs is the same yet different then configuring with traditional databases (11.2.0.4 and earlier).  I say this because, things that we typically use for setting up Oracle GoldenGate change just ever so slightly.

GoldenGate User:

One example that I’m talking about is the Oracle GoldenGate user.  In previous version, I could create the user grant DBA rights and run.  In Oracle Database 12c, not quite that simple.  In order to use Oracle GoldenGate with PDBs, a “common user” needs to be created.
Details: I discuss “common users” in detail in this post.
I created my common user with the name of “C##GGATE” in the Consolidated Database (CDB). This gives the GoldenGate user access to all the PDBs that are contained within the CDB.

GoldenGate User: Privileges:

With the common user created I needed to grant permissions to the user so the user can do whatever it needs too.  I granted DBA and CDB_DBA along with CONNECT to my common user.  Turns out there is an issue with the CDB_DBA role (I did not investigate fully), but granted DBA and CONNECT worked (to a degree).  So I resorted to Oracle GoldenGate documentation for what permissions were needed.
Reminder: As I mentioned earlier, the documentation is a bit scattered and trying to find this information took some time.
Although I found the documentation and granting the documented privileges for capture and apply, I still had issues.   Outlined below, I have provided the permissions that I’ve had to explicitly grant to my common user.

Privileges Granted:

SQL used to find this information is the same on all CDB and PDBs.  The results returned are for the common user C##GGATE (Roles and System Privileges).

SELECT distinct DECODE(SA1.GRANTEE#, 1, 'PUBLIC', U1.NAME) as "USER", SUBSTR(U2.NAME,1,20) as "ROLE", 
   /*SUBSTR(SPM.NAME,1,27) as "PRIV",*/ UAM.STATUS 
FROM SYS.SYSAUTH$ SA1, SYS.SYSAUTH$ SA2, SYS.USER$ U1, 
     SYS.USER$ U2, SYS.SYSTEM_PRIVILEGE_MAP SPM,SYS.USER_ASTATUS_MAP UAM
WHERE SA1.GRANTEE# = U1.USER#
  AND SA1.PRIVILEGE# = U2.USER#
  AND U2.USER# = SA2.GRANTEE#
  AND SA2.PRIVILEGE# = SPM.PRIVILEGE
  and U1.ASTATUS = UAM.STATUS# and U1.TYPE# =1
  and U1.name = upper('&user_name')
  ORDER BY 1, 2, 3;

Consolidated Database:

Pluggable Database 1:

Pluggable Database 2:

You will notice that the roles CONNECT, DBA and RESOURCE are granted at the CDB level and only CONNECT is needed at the PDB level.  This is needed to allow the common user to switch between PDB containers.
Although these roles are great for the common user, the documentation also states that the common user has to be granted access to the PDBs through a new procedure.

begin
  DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('C##GGATE');
end;
/
or
begin
  DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('C##GGATE', container=>'all');
end;
/

What the DBMS_GOLDENGATE_AUTH procedure actually grants is a set of system privileges that Oracle states are needed for the common user to interact with the PDBs.  In the end, I think the system privileges that are set through the DBMS_GOLDENGATE_AUTH package are not complete.  Below is a list of privileges that I have granted to my common user (in code block is the view I use to find these privileges under SYS (run on CDB and all PDBs)).

CREATE OR REPLACE VIEW DBA_USER_PRIVS (USERNAME, ROLENAME, PRIVILEGE) AS
SELECT DECODE(SA1.GRANTEE#, 1, 'PUBLIC', U1.NAME), SUBSTR(U2.NAME,1,20), 
   SUBSTR(SPM.NAME,1,27) 
FROM SYS.SYSAUTH$ SA1, SYS.SYSAUTH$ SA2, SYS.USER$ U1, 
     SYS.USER$ U2, SYS.SYSTEM_PRIVILEGE_MAP SPM
WHERE SA1.GRANTEE# = U1.USER#
  AND SA1.PRIVILEGE# = U2.USER#
  AND U2.USER# = SA2.GRANTEE#
  AND SA2.PRIVILEGE# = SPM.PRIVILEGE
UNION
SELECT U.NAME, NULL, SUBSTR(SPM.NAME,1,27)
FROM SYS.SYSTEM_PRIVILEGE_MAP SPM, SYS.SYSAUTH$ SA, SYS.USER$ U
WHERE SA.GRANTEE#=U.USER# 
  AND SA.PRIVILEGE#=SPM.PRIVILEGE
/ 
select * from dba_user_privs where username = 'C##GGATE' and rolename is null;

Consolidated Database:

Pluggable Database 1 (Capture):

Pluggable Database 2 (Apply):

Lastly, I figured out that I needed to set tablespace quotas both within the CDB and the PDB for the common user.  To make it easy, I just assigned UNLIMITED quotas to the USER tablespace in the CDB and PDB for the common user.
Note: The reason the quota on the USER tablespace is needed is due to the default tablespace for C##GGATE is USER in the CDB. The user needs the same access at the PDB level.
Now the user has been configured,  let’s get the GoldenGate processes configured and ready to run.

Environment Setup:

Just like any other GoldenGate environment, you will have an Extract, Replicat and possibly a Data Pump (optional, I always use one) plus the associated trail files.  The important thing to remember when setting up each process that there are a few changes in the TABLE and MAP statements.  I have provided my working, uni-directional parameter files below.
Tip: Extracts in Oracle Database 12c against PDBs MUST be setup as integrated extracts.  Classic capture extracts can be configured but will not start against a PDB (no error message will be logged as well).
Extract:

-- Verifies parameter file syntax. COMMENT OUT AFTER TESTING.
--CHECKPARAMS
--Specifies the name of the extract process
EXTRACT ext
--Oracle Login
USERID c##ggate, PASSWORD ggate
--Usa ASM API
--Not needed for integrated capture as of 12.1.2
--TRANLOGOPTIONS DBLOGREADER
--Set Oracle Environment Variables
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0/db12c")
SETENV (ORACLE_SID="bc12ctst")
--Warns for a long running transaction
WARNLONGTRANS 5m, CHECKINTERVAL 3m
--Specifies the location of the remote trail file on target machine
EXTTRAIL ./dirdat/lt
--Resolves the TABLES to be replicated ON START-UP
WILDCARDRESOLVE IMMEDIATE
--Table Mappings
SOURCECATALOG PDB1;
TABLE BOBBY.*;

Data Pump (Extract):

-- Verifies parameter file syntax. COMMENT OUT AFTER TESTING.
-- CHECKPARAMS
--Specifies the name of the extract pump process
EXTRACT pmp
--No logic applied just pass everything extract writes to the trail file to the target trail file
PASSTHRU
--Specifies the target system to connect to
RMTHOST localhost, MGRPORT 15000, COMPRESS
--Specifies the location of the remote trail file on target machine
RMTTRAIL ./dirdat/rt
--Table Mappings
SOURCECATALOG PDB1;
TABLE BOBBY.*;

Replicat:

--Specifies the name of the replicat load process.
REPLICAT rep
-- Verifies parameter file syntax. COMMENT OUT AFTER TESTING.
--CHECKPARAMS
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0/db12c")
SETENV (ORACLE_SID="bc12ctst")
--Oracle login.
USERID c##ggate@PDB2, PASSWORD ggate
--DBOPTIONS INTEGRATEDPARAMS(parallel 4)
--The source ddl and target ddl are identical
ASSUMETARGETDEFS
--Specifies name and location of a discard file.
DISCARDFILE ./dirrpt/rep.dsc, append, megabytes 5
--Resolves the TARGETs to be replicated during process start-up
WILDCARDRESOLVE IMMEDIATE
--Table Mappings
map PDB1.BOBBY.*, target BOBBY.*;

Notice in the Extract and Replicat parameter files that the TABLE and MAP statements are slightly different.  In both files, I use two different versions of how to associate tables within the PDBs.  In the Extract parameter file, the PDB is being assigned with the SOURCECATALOG option.  This tells GoldenGate to switch containers and to use PDB1 for the tables.  In the Replicat parameter file, the MAP statement is not using SOURCECATALOG; instead it is looking for source tables with a referenced inline format CATALOG.SCHEMA.TABLES (PDB1.BOBBY.*).  The target side does not need the CATALOG option because the replicat is logging into PDB2 directly.
Well, I hope I gave a good overview of how to setup Oracle GoldenGate 12c (12.1.2) within Oracle Database 12c (12.1.0.1) using PDBs.
Enjoy!
twitter: @dbasolved
blog:http://dbasolved.com

Please follow and like: