DB2 10.1 SQLCODE=-302, SQLSTATE=22001

Users reported below error after a deployment in DB2 LUW 10.1

The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use.. SQLCODE=-302, SQLSTATE=22001, DRIVER=3.61.86

Cause

Data being inserted into the database table column is larger than the assigned column length.

Identified that for one column data being inserted was larger than what is defined in the table.

mongoDB - Handy

-- Check mongoDB version
> db.version();
3.0.1

--default conf file location 
/etc/mongod.conf

--default utility loc
ls -l /usr/bin/mongo*

-- Run JSON file from Shell prompt

# mongo --port <port#> <filename>

# mongo --port 27004 repl.status.js

MongoDB shell version: 3.0.1

connecting to: 127.0.0.1:27004/test

optime diff: 0

optimeDate diff: 0

-- Run JSON file from mongo prompt

> load("repl.status.js")

optime diff: 0

optimeDate diff: 0

true

-- Run commands from shell
mongo --port 27107 --eval "\
rs.initiate(); sleep(3000);\
rs.add ('<HOSTNAME>:27108');\
rs.addArb('<HOSTNAME>:27109')"
      (or)
echo 'sh.addShard( "shard0/localhost:27107" ); \
sh.addShard("shard1/localhost:27117" ); \
sh.addShard( "shard2/localhost:27127" ); sh.status()' | mongo
      (or)
echo 'for (i=0; i<10000; i++) { docArr = []; for (j=0; j<1000; j++) { \
docArr.push( { a : i, b : j, c : "Filler String 000000000000000000000 \
000000000000000000000000000000000000000000000000000000000000000000 \
000000000000000" } ) }; db.testcol.insert(docArr) }' | mongo

--hidden:true ["priority must be 0 when hidden=true"]
I have 4 hosts in replicat set as shown in "rs.conf"
rs0:PRIMARY> rs.conf().members.length
4
Where as "db.isMaster" showing only 3 hosts.
rs0:PRIMARY> db.isMaster().hosts
[
        "hostd001:27001",
        "hostd001:27002",
        "hostd001:27003"
]

Further digging in conf shows that node4 is defined as "hidden".

 "host" : "sl73vcasvdbd001:27004",  "hidden" : true
Hence "db.isMaster" is not showing it.

--Collections in 'local' database are not replicated.

ORACLE Golden Gate Replicat failed with OGG-01431

One of the Replicat failed with below error.

Aborted grouped transaction on 'SCH.TAB', Mapping error.

On source side - Primary key was modified for this table. This was done by disabling and enabling PK.

--> Failed while updating a record on table SCH.TAB

Aborting transaction on ./dirdat/e2 beginning at seqno 36 rba 31942126

                         error at seqno 36 rba 31942126

Problem replicating SCH.TAB to SCH.TAB

Mapping problem with compressed update record (target format)...

*

ABSOLUTE_NODE_VALUE = 1010110253210001

SOFT_DELETE = Y

DATE_MODIFIED = 2015-06-22 16:16:34

MODIFIED_BY = xxxx

*

à There was no record matching this criteria during issue time. However there is a record now.

SQL>  select * from SCH.TAB where ABSOLUTE_NODE_VALUE = 1010110253210001 and SOFT_DELETE = 'Y';

no rows selected

à All constraints are “ENABLED” and “VALIDATED”

SQL> select constraint_name, constraint_type, status, validated from dba_constraints where owner='SCH' and table_name='TAB';

CONSTRAINT_NAME                C STATUS   VALIDATED

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

SYS_C0048522                   C ENABLED  VALIDATED

SYS_C0048521                   C ENABLED  VALIDATED

SYS_C0048520                   C ENABLED  VALIDATED

SYS_C0048519                   C ENABLED  VALIDATED

SYS_C0048518                   C ENABLED  VALIDATED

SYS_C0048517                   C ENABLED  VALIDATED

SYS_C0048516                   C ENABLED  VALIDATED

SYS_C0048515                   C ENABLED  VALIDATED

à  Supplemental logging is disabled as this is target and uni directional replication

SQL>  select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO

--> Tried by deleting and adding trandata. However Replicat failed to start.

Process Abending : 2015-06-23 01:00:28

2015-06-23 02:49:37  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): delete trandata SCH.TAB.

2015-06-23 02:50:39  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): delete trandata SCH.TAB.

2015-06-23 02:50:44  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): add trandata SCH.TAB  nokeycols(NODE_ID) .

2015-06-23 02:50:57  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start replicat  REPL1.

2015-06-23 02:50:57  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host [10.173.133.36]:10051 (START REPLICAT REPL1).

2015-06-23 02:50:57  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  REPLICAT REPL1 starting.

2015-06-23 02:50:57  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, re2ocfg.prm:  REPLICAT REPL1 started.

2015-06-23 02:50:58  WARNING OGG-01431  Oracle GoldenGate Delivery for Oracle, repl1.prm:  Aborted grouped transaction on 'SCH.TAB', Mapping

error.

à Now configuration was changed for this replicat and started successfully.

MAP SCH.TAB                   , TARGET sch.tab

//Keycols for this table was removed ( This is the fix)

2015-06-23 03:02:11  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): edit param  repl1.

2015-06-23 03:02:35  INFO    OGG-01735  Oracle GoldenGate Collector for Oracle:  Synchronizing ./dirdat/e2014683 to disk.

2015-06-23 03:02:35  INFO    OGG-01735  Oracle GoldenGate Collector for Oracle:  Synchronizing ./dirdat/e2014683 to disk.

2015-06-23 03:02:35  INFO    OGG-01670  Oracle GoldenGate Collector for Oracle:  Closing ./dirdat/e2014683.

2015-06-23 03:02:35  INFO    OGG-01669  Oracle GoldenGate Collector for Oracle:  Opening ./dirdat/e2014684 (byte -1, current EOF 0).

2015-06-23 03:02:41  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start replicat  repl1.

2015-06-23 03:04:31  INFO    OGG-01735  Oracle GoldenGate Collector for Oracle:  Synchronizing ./dirdat/e2003725 to disk.

2015-06-23 03:04:31  INFO    OGG-01670  Oracle GoldenGate Collector for Oracle:  Closing ./dirdat/e2003725.

2015-06-23 03:04:31  INFO    OGG-01669  Oracle GoldenGate Collector for Oracle:  Opening ./dirdat/e2003726 (byte -1, current EOF 0).

à Once the replicat completed the update (that was failed) we stopped the replicat. 

From Table SCH.TAB to SCH.TAB:

       #                   inserts:         0

       #                   updates:         1

       #                   deletes:         0

       #                  discards:         0

2015-06-23 03:04:33  INFO    OGG-01021  Oracle GoldenGate Delivery for Oracle, repl1.prm:  Command received from GGSCI: GETLAG.

2015-06-23 03:04:45  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): stop  replicat  REPL1.

2015-06-23 03:04:46  INFO    OGG-01021  Oracle GoldenGate Delivery for Oracle, repl1.prm:  Command received from GGSCI: STOP.

2015-06-23 03:04:46  INFO    OGG-00994  Oracle GoldenGate Delivery for Oracle, repl1.prm:  REPLICAT REPL1 stopped normally.

à Now keycol is added back for hierarchy table and started replicat.

2015-06-23 03:05:20  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): edit param repl1.

2015-06-23 03:05:26  INFO    OGG-01735  Oracle GoldenGate Collector for Oracle:  Synchronizing ./dirdat/e2014684 to disk.

2015-06-23 03:05:50  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  REPLICAT REPL1 starting.

2015-06-23 03:05:50  INFO    OGG-00995  Oracle GoldenGate Delivery for Oracle, re2ocfg.prm:  REPLICAT REPL1 starting.

2015-06-23 03:05:50  INFO    OGG-03035  Oracle GoldenGate Delivery for Oracle, re2ocfg.prm:  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

Oracle Golden Gate Pump failed with ERROR OGG-01496

One of the Golden gate Pump failed with "OGG-01496" error message as mentioned below.

2015-06-20 05:51:15  ERROR   OGG-01496  Oracle GoldenGate Capture for Oracle, pump1.prm:  Failed to open target trail file ./dirdat/g1000531, at RBA 25867744.

2015-06-20 05:51:15  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, pump1.prm:  PROCESS ABENDING.

Keyword in the error message is "Failed to open target trail file".

This means pump is not able to place files in the target File system.  

Logged in the target server and found that "./dirdat/" FS was unmounted. 

Mounted the FS in the Target and Started the pump at source.

Add/Register new RAC database to Cluster resources

Recently we created test database and below were the steps followed to add this new database to cluster resources.

[oracle@ ~]$ . oraenv
ORACLE_SID = [+ASM1] ? test1
The Oracle base has been changed from /u01/app/grid to /u01/app/oracle

-- Checking the location of PFILE or SPFILE

[oracle@ ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 11 01:40:02 2015

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1119043584 bytes
Fixed Size                  2227624 bytes
Variable Size             838861400 bytes
Database Buffers          268435456 bytes
Redo Buffers                9519104 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATADG/test/spfiletest1.ora

-- Checking if TEST DB is configured already

[oracle@~]$ srvctl config database -d test

PRCD-1120 : The resource for database test could not be found.

PRCR-1001 : Resource ora.test.db does not exist

--Adding TEST DB with ORACLE_HOME (-o) and SPFILE (-p_ location

[oracle@~]$ srvctl add database -d test -o /u01/app/oracle/product/11.2.0.3/dbhome_1 -p +DATADG/test/spfiletest1.ora

[oracle@~]$ srvctl config database -d test

Database unique name: test

Database name:

Oracle home: /u01/app/oracle/product/11.2.0.3/dbhome_1

Oracle user: oracle

Spfile: +DATADG/test/spfiletest1.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: test

Database instances:   <<< Here no instances  are configured yet >>>>

Disk Groups:

Mount point paths:

Services:

Type: RAC

Database is administrator managed

[oracle@~]$ srvctl status database -d test

Database is not running.

--Below error comes as we have not configured any instances

[oracle@~]$ srvctl start database -d test

PRKO-3119 : Database test cannot be started since it has no configured instances. 

--Configure instances now

[oracle@~]$ srvctl add instance -d test -i test1 -n node1

[oracle@~]$ srvctl add instance -d test -i test2 -n node2

[oracle@~]$ srvctl status database -d test

Instance test1 is not running on node node1

Instance test2 is not running on node node2

[oracle@~]$ srvctl config database -d test

Database unique name: test

Database name:

Oracle home: /u01/app/oracle/product/11.2.0.3/dbhome_1

Oracle user: oracle

Spfile: +DATADG/test/spfiletest1.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: test

Database instances: test1,test2  <<Compare with previous values>>

Disk Groups:

Mount point paths:

Services:

Type: RAC

Database is administrator managed

[oracle@~]$ srvctl start database -d test

[oracle@~]$ srvctl status database -d test

Instance test1 is running on node node1

Instance test2 is running on node node2

ora.test.db

      1        ONLINE  ONLINE       node1                   Open

      2        ONLINE  ONLINE       node2                   Open

ORACLE GG - OGG-01705 - Version 11.2.1.0.12 - Replicat Abend

GG version - V11.2One of the replicat abended with following error message.----

015-05-28 06:43:12  ERROR   OGG-01705  Oracle GoldenGate Delivery for Oracle, repl1.prm:  

Input checkpoint position 81020055 for input trail file './dirdat/e2000117' is greater than the size of the file (80914922).  

Please consult Oracle Knowledge Management Doc ID 1138409.1. for instructions.

2015-05-28 06:44:23  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, repl1.prm:  PROCESS ABENDING.

Replicat Status Abended as below-----

REPLICAT   REPL1 Last Started 2015-05-28 06:43   Status ABENDEDCheckpoint Lag       00:00:00 (updated 01:31:57 ago)
Log Read Checkpoint  File ./dirdat/e2000117    
2015-05-28 05:46:24.000166  RBA 81020055

Started Replicat  with below option-----
start replicat <rep name> filterduptransactions


Ref:
OGG Replicat Checkpoint RBA Is Larger than Local Trail Size- Ogg v11.2 (Doc ID 1536741.1)

ORACLE GG: EXTRACT ABEND with OGG-01629,OGG-01423,OGG-01449

1.   We started receiving alerts for /backup FS is full and apparently EX01 extract ABENDED with following messages in log.

2015-05-17 06:12:22  INFO    OGG-01629  Oracle GoldenGate Capture for Oracle, EX01.prm:  BOUNDED RECOVERY: PERSISTED OBJECTS RECOVERED: <<NONE TO RECOVER>>.

2015-05-17 06:12:22  WARNING OGG-01423  Oracle GoldenGate Capture for Oracle, EX01.prm:  No valid default archive log destination directory found for thread 1.

2.   Restart of EX01 extract failed with below error message

2015-05-17 06:12:26  ERROR   OGG-01449  Oracle GoldenGate Capture for Oracle, EX01.prm:  Scan failed in trail file /u01/app/oracle/product/GoldenGate11.2/dirdat/aa017499, with scan start seqno 17499, rba 185844458.

3.   At this point we decided to rollover EX01 to next trail file.

ALTER EXT EX01, ETROLLOVER

2015-05-17 07:14:52  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, EX01.prm:  EXTRACT EX01 started.

2015-05-17 07:14:52  INFO    OGG-01517  Oracle GoldenGate Capture for Oracle, EX01.prm:  Position of first record processed for Thread 3, Sequence 91429, RBA 62615056, SCN 14.602109362, May 17, 2015 5:47:18 AM.

2015-05-17 07:14:52  INFO    OGG-01052  Oracle GoldenGate Capture for Oracle, EX01.prm:  No recovery is required for target file /u01/app/oracle/product/GoldenGate11.2/dirdat/aa017500, at RBA 0 (file not opened).

4.  However data pumps DP01, DP02 were still waiting at aa017499 (as shown below) where as EX01 moved to aa017587

GGSCI 5> info extract DP01

EXTRACT    DP01      Last Started 2015-05-17 06:49   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:08 ago)

Log Read Checkpoint  File /u01/app/oracle/product/GoldenGate11.2/dirdat/aa017499

                     First Record  RBA 187818293

GGSCI 6> info extract DP02

EXTRACT    DP02      Last Started 2015-05-17 06:49   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:08 ago)

Log Read Checkpoint  File /u01/app/oracle/product/GoldenGate11.2/dirdat/aa017499

                     First Record  RBA 187818293

GGSCI 7> info extract EX01

EXTRACT    EX01      Last Started 2015-05-17 07:14   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:01 ago)

Log Read Checkpoint  Oracle Redo Logs

                     2015-05-18 14:14:52  Thread 1, Seqno 75662, RBA 55462912

                     SCN 14.728406038 (60857948182)

Log Read Checkpoint  Oracle Redo Logs

                     2015-05-18 14:14:50  Thread 2, Seqno 162005, RBA 382260556

                     SCN 14.728404148 (60857946292)

Log Read Checkpoint  Oracle Redo Logs

                     2015-05-18 14:14:52  Thread 3, Seqno 91632, RBA 189293056

                     SCN 14.728405751 (60857947895)

5.  Then we decided to stop DP01/2 and start from next trail file and this fixed the issue.

alter extract DP01,EXTSEQNO 17500,EXTRBA 0

alter extract DP02,EXTSEQNO 17500,EXTRBA 0

6.  Similarly we worked with other team to restart replicats at their end.

2015-05-17 06:49:54  INFO    OGG-01670  Oracle GoldenGate Collector for Oracle:  Closing /main/input/tr017538.

2015-05-17 06:49:54  INFO    OGG-01669  Oracle GoldenGate Collector for Oracle:  Opening /main/input/tr017539 (byte -1, current EOF 0).

alter REPLICAT MAINP,EXTSEQNO 17539,EXTRBA 0

2015-05-17 06:49:54  INFO    OGG-01735  Oracle GoldenGate Collector for Oracle:  Synchronizing /standby/input/tr017537 to disk.

2015-05-17 06:49:54  INFO    OGG-01735  Oracle GoldenGate Collector for Oracle:  Synchronizing /standby/input/tr017537 to disk.

2015-05-17 06:49:54  INFO    OGG-01670  Oracle GoldenGate Collector for Oracle:  Closing /standby/input/tr017537.

2015-05-17 06:49:54  INFO    OGG-01669  Oracle GoldenGate Collector for Oracle:  Opening /standby/input/tr017538 (byte -1, current EOF 0).

alter REPLICAT STANDBYP,EXTSEQNO 17538,EXTRBA 0

This is what we followed to fix the issue.