Oracle GoldenGate and threaded_execution

Oracle database version: 12.1.0.2.0
Oracle GoldenGate version: Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

threaded_execution

What is this parameter all about?

The multi-threaded Oracle model enables Oracle processes on UNIX and Linux to run as operating system threads in separate address spaces. It applies to both BACKGROUND PROCESSES and SERVER PROCESSES. By default, some background processes on UNIX and Linux always use threaded execution; the remaining Oracle processes run as operating system processes. This feature can be helpful to reduce CPU and memory usage because it reduces the number of Oracle processes. It is disabled by default.

In one of our environment, we have enabled this parameter considering this a new 12c feature helping to utilize resources in an efficient way. So setting is as below

SQL> sho parameter threaded

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
threaded_execution                   boolean     TRUE
SQL>

The detailed notes on this parameter or multi-threaded Oracle model can be found in the support note Multi-Threaded Oracle Model : 12c New (Doc ID 1958348.1)

With this setting turned on, I was trying to register an integrated extract on the multi tenant database (which is what we can do as we can't use classic extract if the database is multi tenant) which is throwing the below error.

GGSCI (exdevqadbadm01.wm.cginet as C##GGADMIN@pl360cdb2/CDB$ROOT) 2> register extract eo2dtfsa database container (ptetfsa) 

2017-08-02 12:26:51 ERROR OGG-08221 Cannot register or unregister EXTRACT EO2DTFSA because of the following SQL error: OCI Error 1,031. 

If you look at the error, this states that the error is due to insufficient privileges. The oracle user we use has has all the privileges granted as per Oracle documentation and the user has DBA role in addition as well.

There are many notes regarding the impacts of the threaded_execution setting to TRUE. Below are few examples and you will get a lot of notes related to the parameter

12c: threaded_execution=true Prevents OS Login As Sysdba
How to connect Non-sysdba Account via RMAN, When threaded_execution=TRUE

We have raised a support request with Oracle and they are working on the same.

Things tried as per Oracle's suggestion

Try to register extract with threaded_execution=FALSE and check what happens when flip the setting back.

With the setting changed to FALSE, I was able to register the process and add extract and the parameter flip to TRUE doesn't affect the extract process until I tried to stop the process after testing. 

The process couldn't find it's parent and became a zombie that I couldn't stop the process via GGSCI and the process in due course utilized all the cpu available on the server making the database to hang. I had to kill the process from OS using  "kill -9".

Now after sending all the required information, Oracle has filed a bug against the case 

Bug 26694412 - REGISTER EXTRACT DATABASE FAILS WHEN THREADED_EXECUTION IS TRUE

We are waiting for Oracle's development team to work on this bug. I'll update this post when I'm successfully able to register the extract in the environment. 

Currently the database is running with threaded_execution=FALSE

Happy registering!!! :)

Update - 21 Sep 2017
Oracle has updated the ticket as below before closing

The following enhancement has been filed: 

Bug 26834790 - IE Support for db in THREADED_EXECUTION mode. 

So for now, to have GoldenGate to work with integrated extract one needs to set threaded_execution=FALSE.

Update - 06 Nov 2017
Enhancement  has been done. The fix for the bug is introduced in Oracle GoldenGate version 12.3.0.1.0
Hence if you are running OGG 12.3.0.1.0, then you can have threaded_execution=TRUE

Parallelism on single object

Today as my long time dream come true, I presented my maiden session in an user group meet up, AIOUG Bengaluru Chapter meet up which was a small session but received accolades from the attendees making me feel encouraged to do more such presentations in the future.

I'm attaching the presentation slide as a pdf file which can be downloaded from here.

The content actually speaks about how to efficiently utilize parallel processes when working with single object such as a single data file or a single table. The slides are self explanatory and you might always utilize the comments section to get your doubts clarified.

Happy Meet up!!! :)

Basic DB2 commands - Part 2

The first part of this post can be found in this link

7. DB2 database version

The database version can be found on Oracle database by the v$version view. In DB2 this can be found using a few methods as given below. 

$ db2 connect to oracnv1

   Database Connection Information

 Database server        = DB2/AIX64 10.5.7
 SQL authorization ID   = ORACLEI1
 Local database alias   = ORACNV1

$ db2 "select VERSIONNUMBER, VERSION_TIMESTAMP, VERSIONBUILDLEVEL from SYSIBM.SYSVERSIONS"

VERSIONNUMBER VERSION_TIMESTAMP          VERSIONBUILDLEVEL
------------- -------------------------- ------------------------------
      7020400 2004-12-02-20.08.48.824019 -
      8020000 2005-02-07-10.32.54.570402 -
      9050300 2009-02-09-11.30.01.831943 -
      9070900 2014-10-29-17.32.22.790717 s140512
     10050400 2014-10-29-17.34.55.809574 s140813

  5 record(s) selected.

$ db2level
DB21085I  This instance or install (instance name, where applicable:
"oraclei1") uses "64" bits and DB2 code release "SQL10057" with level
identifier "0608010E".
Informational tokens are "DB2 v10.5.0.7", "s151221", "IP23949", and Fix Pack
"7".
Product is installed at "/opt/IBM/db2/V10.5".

$

SYSIBM.SYSVERSIONS shows the history of the database versions along with its timestamp on when it has been upgraded.

8. DDL for objects

In Oracle one can use dbms_metadata package to get the DDL information for the objects such as Tables, Indexes, etc., Showing an example of getting the DDL of a table in Oracle.

SQL> set heading off echo off pages 999 long 90000
SQL> select DBMS_METADATA.GET_DDL('TABLE','AB_DOMAIN','PLANS360') FROM DUAL;


  CREATE TABLE "PLANS360"."AB_DOMAIN"
   (    "DOMAIN_COID" CHAR(26 CHAR) NOT NULL ENABLE,
        "SEQUENCE_NO" NUMBER(18,0) NOT NULL ENABLE,
        "PRIMARY_PLATFORM" CHAR(2 CHAR) NOT NULL ENABLE,
        "DATA_" RAW(1024) DEFAULT '2727' NOT NULL ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PLANS360_DATA"


SQL>

This is very easy to get in the DB2 database using db2look utility. Usage is given as an example below. If one likes to list all the tables under a particular schema, he/she can issue the following command to get the list of tables.

$ db2 list tables for schema dba

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
AB_COARSEOBJECT                 DBA             T     2004-12-02-20.12.51.662416
AB_CODEMAP                      DBA             T     2004-12-02-20.12.55.041893
AB_CONFIG                       DBA             T     2004-12-02-20.12.51.779508
AB_DOMAIN                       DBA             T     2004-12-02-20.12.54.818882
....
....
<< snipped few lines >>
....
....
AB_PARALLELMESSAGE              DBA             T     2005-04-11-12.17.27.384100
AB_POSTDATELOGDET               DBA             T     2004-12-02-20.12.51.917250
$ 
$ db2look -d oracnv1 -e -z dba -t AB_DOMAIN # -o BA_ADDRESS.sql
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: ORACLEI1
-- Specified SCHEMA is: DBA
-- The db2look utility will consider only the specified tables
-- Creating DDL for table(s)

-- Schema name is ignored for the Federated Section
-- This CLP file was created using DB2LOOK Version "10.5"
-- Timestamp: Tue Mar 21 09:15:50 EDT 2017
-- Database Name: ORACNV1
-- Database Manager Version: DB2/AIX64 Version 10.5.7
-- Database Codepage: 819
-- Database Collating Sequence is: UNIQUE
-- Alternate collating sequence(alt_collate): null
-- varchar2 compatibility(varchar2_compat): OFF


CONNECT TO ORACNV1;

------------------------------------------------
-- DDL Statements for Table "DBA     "."AB_DOMAIN"
------------------------------------------------


CREATE TABLE "DBA     "."AB_DOMAIN"  (
                  "DOMAIN_COID" CHAR(26) NOT NULL ,
                  "SEQUENCE_NO" DECIMAL(18,0) NOT NULL ,
                  "PRIMARY_PLATFORM" CHAR(2) NOT NULL ,
                  "DATA_" VARCHAR(1024) FOR BIT DATA NOT NULL WITH DEFAULT  )
                 IN "USERSPACE1"
                 ORGANIZE BY ROW;

-- DDL Statements for Indexes on Table "DBA     "."AB_DOMAIN"

SET NLS_STRING_UNITS = 'SYSTEM';

CREATE UNIQUE INDEX "DBA     "."DOMAINIX" ON "DBA     "."AB_DOMAIN"
                ("DOMAIN_COID" ASC)

                COMPRESS NO
                INCLUDE NULL KEYS DISALLOW REVERSE SCANS;

COMMIT WORK;

CONNECT RESET;

TERMINATE;

$

Here
-d stands for database name
-e stands for extract DDL statements
-z stands for schema name
-t stands for table name; up to 30 table names can be provided
-o stands for output file name which I have commented in this example. If -o is added to the db2look command, the output will be redirected to output file name mentioned and will not be displayed on screen.
If -t is not used, DDL for all the objects under schema DBA will be extracted.
For more on this tool, check on this link.

9. Finding object sizes

The sizes for Oracle objects can be found using the dba_segments views. The DB2 counter part would be SYSIBMADM.ADMINTABINFO administrative view. One can also get the information by joining syscat.tables and syscat.tablespaces views. Examples given for both..

$ db2 "SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,
> SUBSTR(TABNAME,1,30) TABNAME,
> (SUM(DATA_OBJECT_P_SIZE)+SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE)) AS TOTAL_SIZE_IN_KB
> --,(SUM(DATA_OBJECT_P_SIZE)+SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE))/1024 AS TOTAL_SIZE_IN_MB
> --,(SUM(DATA_OBJECT_P_SIZE)+SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE)) / (1024*1024) AS TOTAL_SIZE_IN_GB
> FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA ='DBA' group by tabname,tabschema"

TABSCHEMA          TABNAME                        TOTAL_SIZE_IN_KB
------------------ ------------------------------ --------------------
DBA                AB_COARSEOBJECT                                1704
DBA                AB_CODEMAP                                     1540
....
....
<< snipped a few lines >>
....
....
DBA                AB_CONFIG                                        24
DBA                AB_DOMAIN                                        20
DBA                AB_ENVIRONMENT                                   44
$
$ db2 "select sum(a.fpages*PAGESIZE/1024) as size_of_schemainKB
> from syscat.tables a, syscat.tablespaces b
> where a.TBSPACEID=b.TBSPACEID and a.tabschema='DBA' group by a.tabschema"

SIZE_OF_SCHEMAINKB
--------------------
              147680

  1 record(s) selected.

$

10. Details on DB2 backup image

When there is a DB2 image is provided, we can use db2ckbkp tool to verify the backup image and get the details of the image.

$ db2ckbkp -h CBQC.0.db2v10q1.DBPART000.20170227162646.001


=====================
MEDIA HEADER REACHED:
=====================
        Server Database Name           -- CBQC
        Server Database Alias          -- CBQC
        Client Database Alias          -- CBQC
        Timestamp                      -- 20170227162646
        Database Partition Number      -- 0
        Instance                       -- db2v10q1
<< snipped a few lines >>
        Platform                       -- 0x14 (AIX-64)
        Encrypt Info Flags             -- 0x0

 The proper image file name would be:
CBQC.0.db2v10q1.DBPART000.20170227162646.001


[1] Buffers processed:  ######################################################################################################
##############################################################################################################################
##############################################################################################################################
##############################################################################################################################
#######################################################################################################

Image Verification Complete - successful.

$

You get all the information such as database name, instance that the DB belongs to, etc., Once the image is verified, we can restore the image if required.

11. Restoring DB2 backup image

The restore of the image can be done as below.

$ db2 restore database CBQC from /dbbackup/oracnv1 taken at 20170227162646 into oracnv1
SQL2528W  Warning!  Restoring to an existing database that is the same as the
backup image database, but the alias name "ORACNV1" of the existing database
does not match the alias "CBQC" of backup image, and the database name
"ORACNV1" of the existing database does not match the database name "CBQC" of
the backup image.  The target database will be overwritten by the backup
version.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.
$

The database name CBQC is obtained from the db2ckbkp command.
The path /dbbackup/oracnv1 is where I have stored my backup image.
I have multiple backups store taken at different time and hence the usage of "taken at" clause.
oracnv1 is an already existing database. If the database doesn't exist, create one and restore.

I'll try to update the post as much as possible..

Happy DB2ing...!!!

Basic DB2 commands - Part 1

I'm working on a migration project where the environment involves migration of DB2 database data on to Oracle database. To perform this migration, I started learning DB2 basics. A few hiccups, a few here and there, a few big sighs... I have collected a few useful commands and info from DB2 world which we will discuss in this post with it's Oracle counterpart. I've also planned to update this post as and when possible to keep me as well as the readers updated on DB2. Let's get into DB2 world...

1. Main system process

In Oracle world, one would check whether any instance is running on Linux or Unix server by issuing the following command.

[oracle@exdevdbadm01 ~]$ ps -ef|grep pmon
oracle     5440      1  0 Feb07 ?        00:11:57 ora_pmon_MV43DRAC1
oracle     6493      1  0 Feb07 ?        00:20:55 ora_pmon_NBDEV3601
oracle   282503 279581  0 08:52 pts/4    00:00:00 grep pmon
oracle   387464      1  0 Feb28 ?        00:03:49 ora_pmon_pl360cdb1
[oracle@exdevdbadm01 ~]$

The equivalent command to check whether a DB2 instance is running or not would be given by this below command

$ ps -ef|grep db2sysc
oraclei1 14155954 60424412   0 08:54:41  pts/3  0:00 grep db2sysc
oraclei1 17891578 31326286   0   Feb 08      - 21:00 db2sysc 0
devptei1 39518344 19333232  39   Jan 31      - 582:44 db2sysc 0
$

where db2sysc - DB2 system controller is a main system controller EDU; it handles critical DB2 server events

There is also a db2 command "db2ilist" which can be used to find the instances available on the server.

$ db2ilist
oraclei1
devptei1
$

2. List databases

Until Oracle version 11g or Oracle version 12c where parameter enable_pluggable_database is set to FALSE, we would have an instance mapped to a database which are meant as one unless there specifically is a change of database name from the instance name.
While coming to the Oracle database version 12c container configuration, we may have one or more pluggable databases under one database or instance which is the exact model when it comes to DB2. DB2 has a single instance which can contain multiple databases under the instance. Let's see how to get the list of databases under a particular instance in both Oracle and DB2.

[oracle@adm01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 20 09:07:35 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PLSAND1                        READ WRITE NO
         4 PLBASE                         READ WRITE NO
         5 PLBASED1                       READ WRITE NO
SQL>

In Oracle, one must connect to the database and issue the "show pdbs" command to list the databases under the container.

$ export DB2INSTANCE=oraclei1
$ db2 list database directory

 System Database Directory

 Number of entries in the directory = 2

Database 1 entry:

 Database alias                       = ORACNV1
 Database name                        = ORACNV1
 Local database directory             = /database/plans360
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = DEVPTE2
 Database name                        = DEVPTE2
 Node name                            = PTENODE
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

In the above DB2 example, one can see there are 2 databases listed for the current instance connected. The Database 1 entry is a local database which can be identified using the "Local database directory"
Database 2 entry is a database which is cataloged to the current instance, meaning the remote database can be accesses using the current instance. I'll discuss what is cataloging later in this post.

3. Connecting to database

In DB2, one can connect to a database using the below command.

$ db2 connect to oracnv1

   Database Connection Information

 Database server        = DB2/AIX64 10.5.7
 SQL authorization ID   = ORACLEI1
 Local database alias   = ORACNV1

$

So when connected, all the db2 queries issued are against the connected database unless the connectivity is changed again.
Or, one may also connect to the db2 prompt and then issue all the commands under db2 prompt as below.

$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.7

You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to oracnv1

   Database Connection Information

 Database server        = DB2/AIX64 10.5.7
 SQL authorization ID   = ORACLEI1
 Local database alias   = ORACNV1

db2 => quit
DB20000I  The QUIT command completed successfully.
$

4. Instance configurations

All the instance parameters can be queried from the Oracle database using the "show" command or the v$parameter view. In DB2 world this can be done by issuing the below command.

$ db2 get database manager configuration

          Database Manager Configuration

     Node type = Enterprise Server Edition with local and remote clients

 Database manager configuration release level            = 0x1000

 CPU speed (millisec/instruction)             (CPUSPEED) = 2.046825e-07
 Communications bandwidth (MB/sec)      (COMM_BANDWIDTH) = 1.000000e+02

 Max number of concurrently active databases     (NUMDB) = 32
 Federated Database System Support           (FEDERATED) = NO
 Transaction processor monitor name        (TP_MON_NAME) =
....
....
<< snipped a few lines >>
....
....
 Current effective arch level         (CUR_EFF_ARCH_LVL) = V:10 R:5 M:0 F:7 I:0 SB:0
 Current effective code level         (CUR_EFF_CODE_LVL) = V:10 R:5 M:0 F:7 I:0 SB:0

 Keystore type                           (KEYSTORE_TYPE) = NONE
 Keystore location                   (KEYSTORE_LOCATION) =

$

Now this can further be filtered by using "grep" command to find the particular configuration that we are interested in. I'll show the example of getting the PORT details to connect to the particular instance from remote clients if allowed in the next point.

5. Finding the db2 port

This is similar to what Oracle's listener port does. One should know the db2 port on which the instance is listening to make any connection from client outside the server.
To facilitate external connections the Database manager authentication (AUTHENTICATION) configuration parameter should be set to SERVER or SERVER_ENCRYPT.
Take a look at this link to understand more.

$ db2 get dbm cfg | grep SVCE
 TCP/IP Service name                          (SVCENAME) = DB2_oraclei1
 SSL service name                         (SSL_SVCENAME) =
$ grep DB2_oraclei1 /etc/services
DB2_oraclei1    60058/tcp
DB2_oraclei1_1  60059/tcp
DB2_oraclei1_2  60060/tcp
DB2_oraclei1_3  60061/tcp
DB2_oraclei1_4  60062/tcp
DB2_oraclei1_END        60063/tcp
$

We first get the service name of the instance from the database manager configuration and then get the PORT number used by the service using the services file under /etc.
This PORT number is required if you tend to connect to the DB2 database using SQL Developer as a client to handle data in a graphical environment.

6. Catalog a DB2 database

Catalog is a term used in DB2 world to add the database details to the current instance so that the database can be accessed from the instance. This is similar to adding the tnsnames configuration to the tnsnames.ora file under Oracle home. To catalog a DB2 database, follow the procedure below.

First add a NODE to the server. This might be of two types - Adding a local node or adding a remote node.
The commands will be as below.

For Local node:

$ db2 CATALOG LOCAL NODE <node name> INSTANCE <instance name>

For remote node:

$ db2 catalog tcpip node <node name> remote <server name> server <port>
$ ## For eg:
$ db2 catalog tcpip node TEST remote alpha.dev.com server 50001

Here I'll be attempting to add a local node and catalog a database to the node.

$ db2 CATALOG LOCAL NODE PTENODE INSTANCE devptei1
SQL1018N  The node name "PTENODE" specified in the CATALOG NODE command
already exists.
$ db2 list node directory

 Node Directory

 Number of entries in the directory = 1

Node 1 entry:

 Node name                      = PTENODE
 Comment                        =
 Directory entry type           = LOCAL
 Protocol                       = LOCAL
 Instance name                  = devptei1

We can see the node is already added which can be confirmed by "list node directory" command. Proceeding further to catalog the database to the node.

$ db2 CATALOG DATABASE DEVPTE1 AT NODE PTENODE
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.
$ db2 list database directory

 System Database Directory

 Number of entries in the directory = 3

Database 1 entry:

 Database alias                       = DEVPTE1
 Database name                        = DEVPTE1
 Node name                            = PTENODE
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = DEVPTE2
 Database name                        = DEVPTE2
 Node name                            = PTENODE
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

Database 3 entry:

 Database alias                       = ORACNV1
 Database name                        = ORACNV1
 Local database directory             = /database/plans360
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

$ db2 connect to devpte1

   Database Connection Information

 Database server        = DB2/AIX64 10.5.7
 SQL authorization ID   = ORACLEI1
 Local database alias   = DEVPTE1

$

Now the database is cataloged to the instance and can be accessed without any issues.

Many more on this link

Happy DB2ing!!!