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




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 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
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    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
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                                               12.1.2.0.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.