In order to create an extract process for a multitenant database, it must be created at the root container level with a "common" database user and must be defined to run in the "integrated" capture mode. Replicats, on the other hand, must be created at the pluggable database level and can be defined to run in either the "classic" or "integrated" modes.
Below, I will step through the configuration of the databases, extract, and replicat for a multitenent environment. In this example, there are two databases sharing a host, so there is no pump and there is only one GoldenGate environment.
GoldenGate
Version - 12.1.2.0
Source Database
Version - 12.1.0.1
Root SID - orcl
Pluggable Databases - pdborcl, pdb2orcl
Target Database
Version - 12.1.0.1
Root SID - orcl2
Pluggable Databases - pdborcl2, pdb2orcl2
Preparing the source database includes the creation of a "common" user, adding supplemental logging at the database level, enabling flashback query, and properly setting the streams_pool_size init parameter.
On the source, enable supplemental logging in the root container.
On the source, enable supplemental logging in the root container.
$ . oraenv ORACLE_SID = [orcl] ? orcl The Oracle base remains unchanged with value /u01/app/oracle $ sqlplus / as sysdba SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> alter database add supplemental log data; Database altered. SQL> alter database force logging; Database altered. SQL> select supplemental_log_data_min, force_logging from v$database; SUPPLEME FORCE_LOGGING -------- --------------------------------------- YES YES SQL> alter system switch logfile; System altered.
On the source, enable flashback query by setting UNDO_MANAGEMENT to AUTO and UNDO_RETENTION to a value that makes sense for your environment.
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1
On the source, create a common GoldenGate admin user in the root and pluggable databases.
SQL> create user c##ggsadmin identified by ggsadmin
default tablespace ggsdata
temporary tablespace temp
container=all;
User created.
SQL> grant dba to c##ggsadmin container=all;
Grant succeeded.
SQL> grant flashback any table to c##ggsadmin container=all;
Grant succeeded.
Each extract process will use 1G of the streams pool. Make sure you add space to the streams pool based on the number of extracts in your GoldenGate environment.
SQL> show parameter streams NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ streams_pool_size big integer 1280M
On the source, create the extract parameter file ./dirprm/e1aa.prm. Notice that the TABLE parameter must include the container name along with the schema. Alternatively, the SOURCECATALOG parameter may be utilized. Examples of both are in this file. If configuring an integrated replicat, use the required LOGALLSUPCOLS parameter in the extract to capture the before and after values of the primary key, unique indexes, and foreign keys.
EXTRACT e1aa USERID c##ggsadmin@orcl, PASSWORD ggsadmin LOGALLSUPCOLS EXTTRAIL ./dirdat/aa TABLE pdborcl.scott.emp; TABLE pdborcl.scott.dept; SOURCECATALOG pdborcl TABLE scott.bonus; TABLE scott.salgrade;
On the source, start GGSCI and add supplemental logging for the objects to be replicated, create and register the extract, and create the trail file. The name of the container must precede the schema name.
$ ./ggsci GGSCI (oel1.localdomain) 2> dblogin userid c##ggsadmin, password ggsadmin Successfully logged into database CDB$ROOT. GGSCI (oel1.localdomain) 3> add schematrandata pdborcl.scott 2013-12-24 08:52:01 INFO OGG-01788 SCHEMATRANDATA has been added on schema scott. 2013-12-24 08:52:02 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema scott. GGSCI (oel1.localdomain) 2> add extract e1aa, integrated tranlog, begin now EXTRACT added. GGSCI (oel1.localdomain) 3> add exttrail ./dirdat/aa, extract e1aa, megabytes 100 EXTTRAIL added. GGSCI (oel1.localdomain) 6> register extract e1aa database container (pdborcl, pdb2orcl) Extract E1AA successfully registered with database at SCN 2139002. GGSCI (oel1.localdomain) 9> start e1aa Sending START request to MANAGER ... EXTRACT E1AA starting GGSCI (oel1.localdomain) 31> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING E1AA 00:00:06 00:00:09 GGSCI (oel1.localdomain) 32> exit
On the target, create a local GoldenGate admin user in the pluggable database.
[oracle@oel1 ggs]$ . oraenv ORACLE_SID = [orcl] ? orcl2 The Oracle base remains unchanged with value /u01/app/oracle [oracle@oel1 ggs]$ sqlplus / as sysdba SQL> alter session set container=pdborcl2; Session altered. SQL> show con_name CON_NAME ------------------------------ PDBORCL2 SQL> create user ggsadmin identified by ggsadmin default tablespace ggsdata temporary tablespace temp container=current; User created. SQL> grant dba to ggsadmin container=current; Grant succeeded. SQL> exit
On the target, create the replicat parameter file ./dirprm/r2aa.prm. There is no need to create a checkpoint table for a replicat in "integrated" mode. Again, the MAP parameter must include the source container name. This may also be accomplished with the SOURCECATALOG parameter. Examples of both are in this file.
REPLICAT r2aa USERID ggsadmin@pdborcl2, PASSWORD ggsadmin ASSUMETARGETDEFS MAP pdborcl.scott.emp, TARGET scott.emp; MAP pdborcl.scott.dept, TARGET scott.dept; SOURCECATALOG pdborcl MAP scott.bonus, TARGET scott.bonus; MAP scott.salgrade, TARGET scott.salgrade;
On the target, create the replicat.
$ ./ggsci GGSCI (oel1.localdomain) 2> dblogin userid ggsadmin@pdborcl2, password ggsadmin Successfully logged into database PDBORCL2. GGSCI (oel1.localdomain) 3> add replicat r2aa, integrated, exttrail ./dirdat/aa REPLICAT (Integrated) added. GGSCI (oel1.localdomain) 8> start r2aa Sending START request to MANAGER ... REPLICAT R2AA starting GGSCI (oel1.localdomain) 12> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING E1AA 00:00:05 00:00:05 REPLICAT RUNNING R2AA 00:00:00 00:00:09
