Configuring GoldenGate 12c in Oracle 12c Multitenant Environments pt 2

Configuring GoldenGate 12c in Oracle 12c Multitenant Environments pt 2

In part 1 of this series, I showed how to set up the capture process in an Oracle 12c multitenant database using Oracle GoldenGate 12c.  In part 2 I will complete this blog by covering the apply process in a multitenant database.

Oracle Database 12c introduced the Multitenant Architecture for Oracle databases.  This allows for multiple pluggable databases to exist within the same container database.  This also complicates the GoldenGate 12c configuration.  In this blog I will attempt to clear up some of the confusion and provide an example on how to setup GoldenGate 12c in this environment.

This blog will is split into two parts, the Capture and the Apply.  They both have some commonalities and some differences.  One of the main things to remember is that the Capture (Extract) runs against the container database (CDB$ROOT) and the Apply (Replicat) runs against each Pluggable Database (PDB).

In addition, when working with Multitenant Databases it is required to use the Integrated Extract and Integrated Replicat.  The configuration of the Integrated Extract/Replicat is essentially the same as with the Classic Extract/Replicat, but the multitenant option does cause some complication.

The replicat process works by connecting into the pluggable database.  Considerations must be made to be able to connect to the PDBs by using a common user and providing permissions for each PDB.  The configuration of the apply process can be divided into two steps:

  1. Configure the GoldenGate Administrator.
  2. Configure the GoldenGate Apply process.

The configuration of the apply process is similar.

The Configuration

The configuration for this example uses two servers, each with a 12.1.0.2 container database with 3 pluggable databases.  A summary of the source and target is shown here:

Source                                  Target
Host:                      gg21a                                    gg21b
Database:             gg21adb                               gg21bdb
PDB1:                    pdb21a1                               pdb21b1
PDB2:                    pdb21a2                               pdb21b2
PDB3:                    pdb21a3                               pdb21b3

The configuration of the two systems is comparable.  The test schema for each PDB is test.  The test user is not a common user, but the same name is used in all PDBs.

Configure the GoldenGate Administrator

The GoldenGate administrator must be created as a common user with permissions to allow access to PDB information.  An example of creating the common user is shown here:

Note:  The connection to create the user is as system to the CDB$ROOT database.

create user c##ggadmin identified by ora123

default tablespace users

temporary tablespace temp;

 

grant dba TO c##ggadmin CONTAINER=all;

 

exec dbms_goldengate_auth.grant_admin_privilege(‘c##ggadmin’,container=>’all’);

This creates a common user c##ggadmin with DBA privilege on all PDBs.  The option to the dbms_goldengate_auth package requires container=>’all’.

Note:  I will be blogging about the minimal privileges needed for the GoldenGate admin in a future blog, but for now, DBA gets the job done.

In GoldenGate this user should be used for configuring GoldenGate as well.

The GoldenGate user should be accessed via the CredentialStore.  The credential store requires it to be created then users added.  Unlike the previous blog, connections must be made to the pluggable databases, thus the credential store must reflect this.

GGSCI (gg21a as c##ggadmin@gg21adb/CDB$ROOT) 17> add credentialstore

 

Credential store created in ./dircrd/.

 

Once the credential store and alias are created it can be used both in the parameter files and the database connection:

GGSCI (gg21b) 4> alter credentialstore add user c##ggadmin password ora123 alias c##ggadm

 

Credential store in ./dircrd/ altered.

 

GGSCI (gg21b) 5> alter credentialstore add user c##ggadmin@pdb21b1 password ora123 alias c##ggadm1

 

Credential store in ./dircrd/ altered.

 

GGSCI (gg21b) 6> alter credentialstore add user c##ggadmin@pdb21b2 password ora123 alias c##ggadm2

 

Credential store in ./dircrd/ altered.

 

GGSCI (gg21b) 7> alter credentialstore add user c##ggadmin@pdb21b3 password ora123 alias c##ggadm3

 

Credential store in ./dircrd/ altered.

 

GGSCI (gg21b) 8> info credentialstore

 

Reading from ./dircrd/:

 

Default domain: OracleGoldenGate

 

Alias: c##ggadm2

Userid: c##ggadmin@pdb21b2

 

Alias: c##ggadm

Userid: c##ggadmin

 

Alias: c##ggadm1

Userid: c##ggadmin@pdb21b1

 

Alias: c##ggadm3

Userid: c##ggadmin@pdb21b3

Note that each aliases logs into either the container CDB$ROOT, or a PDB.

Now that the GoldenGate administrator is configured you are ready to configure the Capture Process.

Configure the GoldenGate Apply Process

When configuring GoldenGate 12c I always prefer to script everything so that it can be reused as a template.  This is done by creating an obey file that will be used to configure the apply process.  The configuration requires several things; the parameter file and the obey file.

The next step is to put together the parameter files.  There are a few things to note in the parameter file:

Note:  There are several tags excluded.  This is to avoid looping.  See my blog on GoldenGate looping.  By setting the tag in the replicat, the transactions are tagged and can be excluded by an extract that might be added.
Note:  The MAP statement is of the format <PDB>.<schema>.<table>

 

REPLICAT RTEST1

 

— Login to database

USERIDALIAS c##ggadm1

 

REPERROR (DEFAULT, ABEND)

REPERROR (-1, IGNORE)

DISCARDFILE ./dirdsc/rtest.dsc, append

DISCARDROLLOVER AT 05:30 ON Wednesday

 

DBOPTIONS INTEGRATEDPARAMS(max_sga_size 200, parallelism 1, COMMIT_SERIALIZATION FULL)

DBOPTIONS SETTAG 01

 

ReportCount Every 30 Minutes, Rate

Report at 01:00

ReportRollover at 01:15 on SUNDAY

 

— MAP STATEMENTS

 

–SOURCECATALOG pdb21a1

MAP pdb21a1.test.*, TARGET test.* ;

The other two replicats only have minor changes so they will not be shown here.

Finally, the replicats are registered as shown in my obey file.

— Login to DB

DBLOGIN USERIDALIAS c##ggadm

 

— Add the CDC replicat processes

ADD REPLICAT rtest1, INTEGRATED, EXTTRAIL /u02/ggtrails/test/rc

ADD REPLICAT rtest2, INTEGRATED, EXTTRAIL /u02/ggtrails/test/rd

ADD REPLICAT rtest3, INTEGRATED, EXTTRAIL /u02/ggtrails/test/re

Once the configuration has completed, you can start the replicats.  I prefer to use the stats command in order to verify that the processes are working.  Once load is applied to the system you should see transactions from sample tables via the stats command.  If you use stats <replicat>, total you will not see as much data, making it easier to go through.

Note:  The replicat is specific to one PDB.  You must run stats for all replicats in order to validate them.

GGSCI (gg21b) 2> stats replicat rtest1, total

 

Sending STATS request to REPLICAT RTEST1 …

 

Start of Statistics at 2016-08-20 18:47:00.

 

 

Integrated Replicat Statistics:

 

Total transactions                                15.00

Redirected                                         0.00

DDL operations                                     0.00

Stored procedures                                  0.00

Datatype functionality                             0.00

Event actions                                      0.00

Direct transactions ratio                          0.00%

 

Replicating from PDB21A1.TEST.TEST1 to PDB21B1.TEST.TEST1:

 

*** Total statistics since 2016-08-20 15:46:36 ***

Total inserts                                     50.00

Total updates                                      0.00

Total deletes                                      0.00

Total discards                                     0.00

Total operations                                  50.00

 

Replicating from PDB21A1.TEST.TEST2 to PDB21B1.TEST.TEST2:

 

*** Total statistics since 2016-08-20 15:46:36 ***

Total inserts                                     50.00

Total updates                                      0.00

Total deletes                                      0.00

Total discards                                     0.00

Total operations                                  50.00

 

Replicating from PDB21A1.TEST.TEST3 to PDB21B1.TEST.TEST3:

 

*** Total statistics since 2016-08-20 15:46:36 ***

Total inserts                                     50.00

Total updates                                      0.00

Total deletes                                      0.00

Total discards                                     0.00

Total operations                                  50.00

 

End of Statistics.

Once you have gotten the replicat working you in business.  You can now validate data on the target.

I hope you are enjoying the blogs.  Happy replicating!

By |2017-09-20T08:04:20+00:00September 2nd, 2016|Performance Wire|Comments Off on Configuring GoldenGate 12c in Oracle 12c Multitenant Environments pt 2