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

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

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 Oracle 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 in this environment.

This blog will be 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 extract process works by connecting into the container or CDB$ROOT 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 capture process can be divided into two steps:

  1. Configure the GoldenGate Administrator.
  2. Configure the GoldenGate Capture 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 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 CredentialStore requires it to be created then users added.

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

 

Credential store created in ./dircrd/.

 

GGSCI (gg21a as c##ggadmin@gg21adb/CDB$ROOT) 18> alter credentialstore add user c##ggadmin@gg21adb password ora123 alias c##ggadm21a

 

Credential store in ./dircrd/ altered.

If you prefer to identify your connection in the environment (i.e. ORACLE_SID=gg21adb) you can add the user without the database qualifier.  I prefer the first method.

GGSCI (gg21a as c##ggadmin@gg21adb/CDB$ROOT) 19> alter credentialstore add user c##ggadmin password ora123 alias c##ggadm

 

Credential store in ./dircrd/ altered.

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

GGSCI (gg21a) 31> dblogin useridalias c##ggadm21a

Successfully logged into database CDB$ROOT.

GGSCI (gg21a) 29> dblogin useridalias c##ggadm

Successfully logged into database CDB$ROOT.

Note that both aliases log into the container CDB$ROOT.

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

Configure the GoldenGate 12c Capture 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 capture process.  The configuration requires several things; the parameter file, the obey file and trandata.  When working with Oracle 11.2.0.4 or 12.x it is always best to use schematrandata.

Since there are three PDBs, schematrandata must be run against all of them.

add schematrandata pdb21a1.test

add schematrandata pdb21a2.test

add schematrandata pdb21a2.test

The output should indicate that it has been added.

Note:  It is always a good idea to verify with info schematrandata.

Once schematrandata has run you can proceed with configuring the extract and pump.  The next step is to put together the parameter files.  There are a few things to note in the parameter file:

Note:  If the SID is set with SETENV so it is not necessary to use the alias with c##ggadmin@gg21adb, however, this is the preferred method.
Note:  There are several tags excluded.  This is to avoid looping.  See my blog on GoldenGate 12c looping.  This will become more apparent in part 2 when the apply process is covered.
Note:  The TABLE statement is of the format <PDB>.<schema>.<table>

 

EXTRACT ETEST

 

SETENV (ORACLE_SID = gg21adb)

SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)

SETENV (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1)

 

USERIDALIAS c##ggadm21a

 

— IGNORETRUNCATES

 

ReportCount Every 30 Minutes, Rate

Report at 01:00

ReportRollover at 01:15 on SUNDAY

 

TRANLOGOPTIONS USE_ROOT_CONTAINER_TIMEZONE

TRANLOGOPTIONS EXCLUDETAG 01

TRANLOGOPTIONS EXCLUDETAG 02

TRANLOGOPTIONS EXCLUDETAG 03

 

LOGALLSUPCOLS

 

EXTTRAIL /u02/ggtrails/test/lc

 

— Tables to pull from

TABLE pdb21a1.test.* ;

TABLE pdb21a2.test.* ;

TABLE pdb21a3.test.* ;

The pump process is where I decided to split the trail into three trail files.  Since the apply process is per PDB, I decided to split the trails.  The pump parameter file looks like this:  Note that the SOURCECATALOG and three part TABLE parameters are required for the pump.

EXTRACT PTEST

 

PASSTHRU

 

RMTHOST gg21b, MGRPORT 7809, COMPRESS, TCPBUFSIZE 1024000, TCPFLUSHBYTES 1024000

 

ReportCount Every 30 Minutes, Rate

Report at 01:00

ReportRollover at 01:15 on SUNDAY

 

–Tables to pull from

 

RMTTRAIL /u02/ggtrails/test/rc

SOURCECATALOG pdb21a1

TABLE pdb21a1.test.*;

RMTTRAIL /u02/ggtrails/test/rd

SOURCECATALOG pdb21a2

TABLE pdb21a2.test.*;

RMTTRAIL /u02/ggtrails/test/re

SOURCECATALOG pdb21a3

TABLE pdb21a3.test.*;

Finally, the extracts are registered and the exttrails, pump and rmttrails are configured within an obey file:  Note that the extract contains the CONTAINER parameter.

— ADD CDC EXTRACTS – TEST

 

— Login to DB

DBLOGIN USERIDALIAS c##ggadm21a

 

— Add Extract

REGISTER EXTRACT etest, DATABASE CONTAINER (pdb21a, pdb21a2, pdb21a3)

ADD EXTRACT etest, INTEGRATED TRANLOG, BEGIN NOW

 

— Add Trail File

ADD EXTTRAIL /u02/ggtrails/test/lc, EXTRACT etest, MEGABYTES 500

 

— Add Data Pump Process

ADD EXTRACT ptest, EXTTRAILSOURCE /u02/ggtrails/test/lc

 

— Add remote trail file

ADD RMTTRAIL /u02/ggtrails/test/rc, EXTRACT ptest, megabytes 500

ADD RMTTRAIL /u02/ggtrails/test/rd, EXTRACT ptest, megabytes 500

ADD RMTTRAIL /u02/ggtrails/test/re, EXTRACT ptest, megabytes 500

Once the configuration has been completed, you can start the extract and pump processes.  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 <extract>, total you will not see as much data, making it easier to go through.

Note:  I have removed some of the data to shorten the blog.  What is important is to see data from each PDB.

GGSCI (gg21a) 2> stats etest, total

 

Sending STATS request to EXTRACT ETEST …

 

Start of Statistics at 2016-08-20 18:26:32.

 

Output to /u02/ggtrails/test/lc:

 

Extracting from PDB21A1.TEST.TEST1 to PDB21A1.TEST.TEST1:

 

*** Total statistics since 2016-08-20 18:26:12 ***

Total inserts                                     10.00

Total updates                                      0.00

Total deletes                                      0.00

Total discards                                     0.00

Total operations                                  10.00

 

Extracting from PDB21A2.TEST.TEST2 to PDB21A2.TEST.TEST2:

 

*** Total statistics since 2016-08-20 18:26:12 ***

Total inserts                                     10.00

Total updates                                      0.00

Total deletes                                      0.00

Total discards                                     0.00

Total operations                                  10.00

 

Extracting from PDB21A3.TEST.TEST3 to PDB21A3.TEST.TEST3:

 

*** Total statistics since 2016-08-20 18:26:12 ***

Total inserts                                     10.00

Total updates                                      0.00

Total deletes                                      0.00

Total discards                                     0.00

Total operations                                  10.00

 

End of Statistics.

As with the extract, you should also validate the pump process with the stats command.  Note the information about the specific trails.

GGSCI (gg21a) 3> stats ptest, total

 

Sending STATS request to EXTRACT PTEST …

 

Start of Statistics at 2016-08-20 18:30:23.

 

DDL replication statistics (for all trails):

 

*** Total statistics since extract started     ***

Operations                                         0.00

Mapped operations                                  0.00

Unmapped operations                                0.00

Other operations                                   0.00

Excluded operations                                0.00

 

Output to /u02/ggtrails/test/rc:

 

Extracting from PDB21A1.TEST.TEST1 to PDB21A1.TEST.TEST1:

 

*** Total statistics since 2016-08-20 15:49:54 ***

Total inserts                                     50.00

Total updates                                      0.00

Total deletes                                      0.00

Total discards                                     0.00

Total operations                                  50.00

 

Output to /u02/ggtrails/test/rd:

 

Extracting from PDB21A2.TEST.TEST2 to PDB21A2.TEST.TEST2:

 

*** Total statistics since 2016-08-20 15:49:54 ***

Total inserts                                     40.00

Total updates                                      0.00

Total deletes                                      0.00

Total discards                                     0.00

Total operations                                  40.00

 

Output to /u02/ggtrails/test/re:

 

Extracting from PDB21A3.TEST.TEST3 to PDB21A3.TEST.TEST3:

 

*** Total statistics since 2016-08-20 15:49:54 ***

Total inserts                                     40.00

Total updates                                      0.00

Total deletes                                      0.00

Total discards                                     0.00

Total operations                                  40.00

 

End of Statistics.

Note:  Because I am not sending pump data to the ./dirdat directory I was required to add ALLOWOUTPUTDIR /u02/ggtrails to the GLOBALS file on the target.

Once you have gotten the extract and pump working you are ready to tackle the replicat.  That will be covered in part 2 of this blog.

I hope you are enjoying the blogs.  Happy replicating!

By |2017-09-20T08:06:03+00:00September 1st, 2016|Performance Wire|Comments Off on Configuring GoldenGate 12c in Oracle 12c Multitenant Environments pt 1