Tuesday, December 24, 2013

GoldenGate: Integrated Capture and Apply on 12c Multitenant Databases

With the new features of GoldenGate 12c and the architectural changes brought on by the multitenant databases, there also come some new requirements for configuring replication.

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.

$ . 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




1 comment:

  1. Nice informative blog.. Keep updating these types of informative updates regularly... Also Visit this site for Oracle Goldengate

    ReplyDelete