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.


Version -

Source Database

Version -
Root SID - orcl
Pluggable Databases - pdborcl, pdb2orcl

Target Database

Version -
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


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;

-------- ---------------------------------------

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

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

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

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

USERID c##ggsadmin@orcl, PASSWORD ggsadmin
EXTTRAIL ./dirdat/aa

TABLE pdborcl.scott.emp;
TABLE pdborcl.scott.dept;

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


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


SQL> create user ggsadmin identified by ggsadmin
     default tablespace ggsdata
     temporary tablespace temp

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.

USERID ggsadmin@pdborcl2, PASSWORD ggsadmin

MAP pdborcl.scott.emp, TARGET scott.emp;
MAP pdborcl.scott.dept, TARGET scott.dept;

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

Thursday, December 19, 2013

GoldenGate: Installation of 12c Using Oracle Universal Installer (OUI)

As you know, Oracle bought GoldenGate four years ago. In its new 12c release, Oracle has now integrated the product further into its standards by offering an installation option using the OUI.

For those of you who have installed earlier versions, you're probably thinking that there's not much to the old install of unzipping a file and creating the subdirs. I agree, but it makes sense why Oracle is moving in this direction. As you'll see, the installation process updates the Oracle Inventory and there is a new OPatch directory allowing for a standard method of patching.

The documentation specifies that this version of the OUI does not support upgrades, so you'll have to revert to the old method if that is your current situation.

Here are the steps to installing GoldenGate with the new OUI.

Once you have downloaded the software, unzip it in a temporary location. Don't unzip it in the GoldenGate home as you would with the previous versions.

For my environment, the file name is 121200_fbo_ggs_Linux_x64_shiphome.zip, and it creates the subdirectory fbo_ggs_Linux_x64_shiphome.

$ cd fbo_ggs_Linux_x64_shiphome/Disk1

$ ./runInstaller

Select the version of Oracle that the capture and/or apply process will be running against.

Enter the GoldenGate software home and the location of the database home that GoldenGate will be operating against.  You can also check whether or not you want the manager process to be started and customize the desired port.

Review the options and click "Install"

And that's it.  The installation is complete.  The subdirs have been created and the manager process has been started.

If you navigate to your new GoldenGate home that you specified above and list the contents, you'll see the OPatch directory along with all of the subdirs.

Additionally, you can navigate to the OPatch directory and list the Oracle inventory as follows.

[oracle@oel1 ggs]$ export ORACLE_HOME=/u01/app/oracle/product/ggs
[oracle@oel1 ggs]$ cd $ORACLE_HOME/OPatch
[oracle@oel1 OPatch]$ ./opatch lsinventory
Invoking OPatch

Oracle Interim Patch Installer version
Copyright (c) 2011, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/ggs
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    :
OUI version       :
Log file location : /u01/app/oracle/product/ggs/cfgtoollogs/opatch/opatch2013-12-19_14-21-54PM.log

Lsinventory Output file location : /u01/app/oracle/product/ggs/cfgtoollogs/opatch/lsinv/lsinventory2013-12-19_14-21-54PM.txt

Installed Top-level Products (1):

Oracle GoldenGate Core                                     
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.


OPatch succeeded.