Friday, June 5, 2015

Move object or schema from one replicate group to another replicate group

Move object or schema from one replicate group to another replicate group

           E0000018  --> P0000018 --> R0000018
           E0000050  --> P0000050 --> R0000050


Note
1)     We are moving one schema from the Replicat(R0000050) to Replicat(R0000018)
2)     Schema name CG_DATA
3)     Take the backup of all the param which are planned to modify during the change


#1 Add table on the new extract & Pump process

GGSCI> stop E0000018
GGSCI> stop P0000018

Add the below line in the param file(Extract & Pump)
TABLE CG_DATA.*;

GGSCI> start E0000018
GGSCI> start P0000018

Note: make sure that new schema object are seen in the extract & pump stats
To see it
GGSCI> STATS EXTRACT E0000018,TABLE CG_DATA.*
GGSCI> STATS EXTRACT P0000018,TABLE CG_DATA.*




#2 Make sure Extract & PUMP & Replicat (R0000050) lag is “0”
GGSCI> info R0000050
REPLICAT   R0000050  Last Started 2015-05-31 08:12   Status RUNNING
Description          "CG_DATA@editorial -> CG_DATA@staging"
Checkpoint Lag       00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint  File /n01/oraggsdata/dirdat/ora269a/CG_DATA/tr000025
                     2015-06-03 11:42:06.082537  RBA 1061441

Note: if the Extract & PUMP & Replicat lag are not “0”, then we have to make sure it is LAG is zero.

#3 Stop the Replicat(R0000050 & R0000018 ) and find the CSN number from replicat R0000050 trail file
A)    Stop the replicat process
GGSCI> stop R0000050
GGSCI> stop R0000018

B)     Remove the schema entire from the replicat(R0000050)
C)     You need to find the CSN number from the trail file of replicat(R0000050) using the logdump utility.
Note:
CASE#1 In our case, after we stopped there is no update to the trail file; so we have followed the below process.

/n01/oraggsbin/11.2: ./logdump
Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.28 19820451 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_150116.0606
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

Logdump 25 >open /n01/oraggsdata/dirdat/ora269a/CG_DATA/tr000025
Current LogTrail is /n01/oraggsdata/dirdat/ora269a/CG_DATA/tr000025
Logdump 26 >pos  1061441
Reading forward from RBA 1061441
Logdump 27 >detail data
Logdump 28 >ggstoken detail
Logdump 29 >sfh
Scan reached EOF
Logdump 30 >sfh prev

2015/06/03 11:37:06.085.185 GGSPKUpdate          Len   346 RBA 1060211
Name: GGS.HEARTBEAT
After  Image:                                             Partition 4   GU s  
 000f 0001 000b 0000 0007 6f72 7437 3330 6100 0000 | ..........ort730a... 
 04ff ff00 0000 0100 0b00 0000 076f 7274 3733 3061 | .............ort730a 
 0002 0004 ffff 0000 0003 001f ffff 3139 3030 2d30 | ..............1900-0 
 312d 3031 3a30 303a 3030 3a30 302e 3030 3030 3030 | 1-01:00:00:00.000000 
 3030 3000 0400 1fff ff31 3930 302d 3031 2d30 313a | 000......1900-01-01: 
 3030 3a30 303a 3030 2e30 3030 3030 3030 3030 0005 | 00:00:00.000000000.. 
 001f ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 | ....1900-01-01:00:00 
Before Image          Len    17 (x00000011)
KeyLen    15 (x0000000f)
KeyCol     1 (x0001), Len    11 (x000b) 
 0000 0007 6f72 7437 3330 61                       | ....ort730a 

After Image           Len   329 (x00000149)
Column     0 (x0000), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     1 (x0001), Len    11 (x000b) 
 0000 0007 6f72 7437 3330 61                       | ....ort730a 
Column     2 (x0002), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     3 (x0003), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     4 (x0004), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     5 (x0005), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     6 (x0006), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     7 (x0007), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     8 (x0008), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     9 (x0009), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    10 (x000a), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column    11 (x000b), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    12 (x000c), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    13 (x000d), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    14 (x000e), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    15 (x000f), Len    31 (x001f) 
 0000 3230 3135 2d30 362d 3033 3a31 313a 3337 3a31 | ..2015-06-03:11:37:1 
 312e 3033 3937 3338 3030 30                       | 1.039738000 
Column    16 (x0010), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    17 (x0011), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    18 (x0012), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    19 (x0013), Len     4 (x0004) 
 ffff 0000                                         | .... 
 
GGS tokens:
TokenID x52 'R' ORAROWID         Info x00  Length   20
 4141 4162 3055 4142 6341 4141 4676 6a41 4141 0001 | AAAb0UABcAAAFvjAAA.. 
TokenID x4c 'L' LOGCSN           Info x00  Length   13
 3231 3831 3138 3630 3139 3832 32                  | 2181186019822 
TokenID x36 '6' TRANID           Info x00  Length   12
 3231 2e32 372e 3834 3835 3530                     | 21.27.848550 
  
Logdump 31 >

CASE#2
   If the trail is getting regular update, as soon you entire the sfh, it will throw output

Logdump 31 >open /n01/oraggsdata/dirdat/ord639a/CHEM_PROC/tr000025
Current LogTrail is /n01/oraggsdata/dirdat/ord639a/CHEM_PROC/tr000025
Logdump 32 >detail data
Logdump 33 >ggstoken detail
Logdump 34 >pos 10000
Reading forward from RBA 10000
Logdump 35 >sfh

2015/05/28 13:12:10.591.876 GGSPKUpdate          Len   346 RBA 10436
Name: GGS.HEARTBEAT
After  Image:                                             Partition 4   GU s  
 000f 0001 000b 0000 0007 6f72 7437 3330 6100 0000 | ..........ort730a... 
 04ff ff00 0000 0100 0b00 0000 076f 7274 3733 3061 | .............ort730a 
 0002 0004 ffff 0000 0003 001f ffff 3139 3030 2d30 | ..............1900-0 
 312d 3031 3a30 303a 3030 3a30 302e 3030 3030 3030 | 1-01:00:00:00.000000 
 3030 3000 0400 1fff ff31 3930 302d 3031 2d30 313a | 000......1900-01-01: 
 3030 3a30 303a 3030 2e30 3030 3030 3030 3030 0005 | 00:00:00.000000000.. 
 001f ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 | ....1900-01-01:00:00 
Before Image          Len    17 (x00000011)
KeyLen    15 (x0000000f)
KeyCol     1 (x0001), Len    11 (x000b) 
 0000 0007 6f72 7437 3330 61                       | ....ort730a 

After Image           Len   329 (x00000149)
Column     0 (x0000), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     1 (x0001), Len    11 (x000b) 
 0000 0007 6f72 7437 3330 61                       | ....ort730a 
Column     2 (x0002), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     3 (x0003), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     4 (x0004), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     5 (x0005), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     6 (x0006), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     7 (x0007), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     8 (x0008), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     9 (x0009), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    10 (x000a), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column    11 (x000b), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    12 (x000c), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    13 (x000d), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    14 (x000e), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    15 (x000f), Len    31 (x001f) 
 0000 3230 3135 2d30 352d 3238 3a31 333a 3132 3a31 | ..2015-05-28:13:12:1 
 312e 3034 3136 3339 3030 30                       | 1.041639000 
Column    16 (x0010), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    17 (x0011), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    18 (x0012), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    19 (x0013), Len     4 (x0004) 
 ffff 0000                                         | .... 
 
GGS tokens:
TokenID x52 'R' ORAROWID         Info x00  Length   20
 4141 4162 3055 4142 6341 4141 4676 6a41 4141 0001 | AAAb0UABcAAAFvjAAA.. 
TokenID x4c 'L' LOGCSN           Info x00  Length   13
 3231 3739 3734 3138 3533 3835 32                  | 2179741853852 
TokenID x36 '6' TRANID           Info x00  Length   11
 3231 2e36 2e38 3233 3133 32                       | 21.6.823132 
  

D)    Add the schema entire to the replicat(R0000018); you need to use the CSN from the previous step

è SCN number is 2181186019822 as per the CASE#1;

MAP CG_DATA.*, TARGET CG_DATA.*, FILTER (@GETENV("TRANSACTION", "CSN") > 2181186019822);

E)     Start the replicat process
GGSCI> start R0000050
GGSCI> start R0000018

F)     Check the status with the below command

GGSCI> STATS EXTRACT R0000018,TABLE CG_DATA.*

#4 Post movement steps
A)    Once you are happy with the changes, you can remove the schema capture from the E0000050 & P0000050.
B)     You can leave the Replicat(R0000018) running for sometime; then we can remove the filter condition from the param file
Before modification
MAP CG_DATA.*, TARGET CG_DATA.*, FILTER (@GETENV("TRANSACTION", "CSN") > 2181186019821);
After Modification
             MAP CG_DATA.*, TARGET CG_DATA.*;
C)     You can monitor the complete process for next 2 hours.



Monday, April 13, 2015

REPLICAT RUNNING VERY SLOW OR REPLICAT LAGGING

REPLICAT RUNNING VERY SLOW OR REPLICAT LAGGING

My current status of my Replicat

GGSCI (bhuogg03) 23> info R0000112

REPLICAT   R0000112  Last Started 2015-04-10 07:17   Status RUNNING
Checkpoint Lag       48:58:28 (updated 58:56:56 ago)
Log Read Checkpoint  File /u01/oracle/gg/prod-bhu/dirdat/ST/tr000629
                     2015-04-05 19:22:09.005598  RBA 91206293

You can see what is happened from the status of the Replicat using the below command

GGSCI (bhuogg03) 24> stats R0000112

To check what is the current progressing in the GG REPLICAT

GGSCI> STATS R0000112  RESET
GGSCI> STATS R0000112  LATEST
n  LATEST option will give the latest activity which are currently happening;
n  We can see the progress and how quick it is.



WHAT/HOW TO CHECK

1)       We need to identify what kind of query is running; I will check the DB session which are part of GG and it is in active. We can check the table_name from gv$session by identify the sql_id
2)      You can login into the OEM or SQLDEVELOPER to identify the query easily.
3)       I will check for the index on the table and whether those columns are part of the DML or DR queries
4)       We need to look for the plan; if there is a FTS then create an index.

Before creating the index, you need to stop the Replicate, else index cant be created; you can try with the below option
alter session set ddl_lock_timeout=100000;

After the index is created; start the replicat and try to monitor the progress of the replicat. It should move the process quickly; if it is not moving quickly you have start analyzing again.
n  Noteè we have to restart the replicate; else index won’t be part of the plan.
n  If you have heavy usage on the source side, then you should split the Replicat using  the RANGE option;

n  You can use the RANGE option only for the Tables;

Wednesday, April 8, 2015

WARNING OGG-06530 Berkeley Database encountered a critical error

WARNING OGG-06530 Berkeley Database encountered a critical error

If you are seeing this error message either in the GGSCI prompt or GG log file(ggserr.log). You can safely ignore it.

Reason for this message

You might included the ENABLEMONITORING in the GLOBALS file

What to do 

Ignore it.

Affected Environment


OGG-11gR2

Monday, March 30, 2015

OGG-01755 or OGG-01759


2015-03-29 01:55:00  ERROR   OGG-01755  Oracle GoldenGate Command Interpreter for Oracle:  Cannot register or unregister EXTRACT E0000019 because of the following SQL error: OCI Error 26,665. See Extract user privileges in the Oracle GoldenGate for Oracle Installation and Setup Guide.

2015-03-29 00:03:53  ERROR   OGG-01759  Oracle GoldenGate Command Interpreter for Oracle:  Cannot unregister EXTRACT E0000019 from database because this Extract is not currently registered


Problem

We are not able to clean up or remove the Integrated Exact from the Golden Gate after removing it manually.

Mainly check the below object
select owner,object_name,object_type,status from dba_objects where object_name in ('DDLREPLICATION','DBMS_CAPTURE_ADM_INTERNAL');


Solution
1)     Check whether all the sys object are valid.
2)     Check whether all the goldengate user objects are valid; you need to re-create but check with oracle support or oracle documentation.


Couple of useful queries

To check the current capturing extract in your environment.
select capture_name,status from dba_capture;

To stop the Extract

SQL> exec dbms_capture_adm.stop_capture('OGG$CAP_E0000019',true);

PL/SQL procedure successfully completed.

To Drop the Extract from the Registry

SQL> exec dbms_capture_adm.drop_capture('OGG$CAP_E0000019',true);

PL/SQL procedure successfully completed.

Note: Check with oracle support, if you want to make some changes in the production environment.

Wednesday, February 4, 2015

OGG-00868 Error code 1291, error message: ORA-01291: missing logfile

OGG-00868 Error code 1291, error message: ORA-01291: missing logfile

When you are working on GoldenGate with INTEGRATED CAPTURE and you are missing some of the old archive logs; you can’t start the extract like classic capture; INTEGRATED CAPTURE will work differently; it will try to start from the registered SCN number or if it is running then you will provide all the old archive logs till the latest one;

Note: if you are missing some old archive logs then you have to rebuild the entire GG setup

You need to do the below activity

GGSCI> STOP EXTRACT E0000032
GGSCI> UNREGISTER EXTRACT E0000032 DATABASE
GGSCI> REGISTER EXTRACT E0000032 DATABASE
GGSCI> START EXTRACT E0000032
GGSCI> INFO E0000032

You should see the extract changing from STARTING to RUNNING STATE;

if you feel still it is not getting started; then you have to follow like below(wait for atleast 10 min, some time INTEGRATED CAPTURE would take time depending on your database load)

GGSCI> STOP EXTRACT E0000032
GGSCI> UNREGISTER EXTRACT E0000032 DATABASE
GGSCI> delete EXTRACT E0000032
GGSCI> REGISTER EXTRACT E0000032 DATABASE
GGSCI> ADD EXTRACT E0000032, INTEGRATED TRANLOG, BEGIN NOW, Description "Some details about extract"
GGSCI> ADD EXTTRAIL ./dirdat/app/ap, EXTRACT E0000032
GGSCI> START EXTRACT E0000032
GGSCI> INFO E0000032


If you have any issue, you can see the ggserr.log or view report <extract_name> would give more details about the error message.

Happy Reading :)

Friday, January 30, 2015

How to set the NLS_LANG in GoldenGate


You need to set NLS_LANG when you run GoldenGate on the super-set character set for the database

you can see the NLS_LANG setting on the OS level

echo $NLS_LANG 
American_America.AL32UTF8

On the DB Side

it is the combination of below parameter, which will get it from NLS_DATABASE_PARAMETERS vuew
NLS_LANGUAGE_NLS_TERRITORY.NLS_CHARACTERSET

You can use the following commands to check the database character sets. (Each Database might have different setting; check your DB before you set this value)

col VALUE format a30
SQL> SELECT * from NLS_DATABASE_PARAMETERS; 

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               AL32UTF8
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.3.0


20 rows selected.

Note:
1) if you set a wrong value; your data might be corrupted;
2) it might abended the Replicate process on the target.

Wednesday, January 7, 2015

ERROR OGG-00403 There can be only one DDL filtering statement.

When i have to include multiple schema name for the DDL filtering, i got the below error message

2014-12-25 07:58:48  ERROR   OGG-00403  There can be only one DDL filtering statement. If DDL filter is long, use ampersand (&) sign to continue it on another line.
2014-12-25 07:58:48  ERROR   OGG-01668  PROCESS ABENDING.


then i have modified like below. Below are the different example on how to include multiple schema and how to restrict the filtering in the EXTRACT for the Golden Gate.

Ex#1 - Including all the schema with all type of objects; 

DDL INCLUDE MAPPED OBJNAME DISCOVER.* INCLUDE MAPPED OBJNAME RESEARCH.* INCLUDE MAPPED OBJNAME LOAD.* INCLUDE MAPPED OBJNAME TEST.* INCLUDE MAPPED OBJNAME EVENT.* INCLUDE MAPPED OBJNAME FAST.*;

-- All the schema DDLReplication for TABLES ONLY
DDL INCLUDE MAPPED OBJNAME DISCOVER.* INCLUDE MAPPED OBJNAME RESEARCH.* INCLUDE MAPPED OBJNAME LOAD.* INCLUDE MAPPED OBJNAME TEST.* INCLUDE MAPPED OBJNAME EVENT.* INCLUDE MAPPED OBJNAME FAST.*,OBJTYPE 'TABLE';

EX#2 - Including all the object, Except the exclusion specified below.

DDL INCLUDE MAPPED &
   EXCLUDE INSTR 'drop partition' &
   EXCLUDE OBJTYPE 'TRIGGER' &
   EXCLUDE OBJTYPE 'INDEX' &
   EXCLUDE OBJTYPE 'CONSTRAINT' &
   EXCLUDE INSTRWORDS 'ALTER TABLE "DROP"' &
   EXCLUDE INSTR 'DROP TABLE'

EX#3 - Excluding the DDL for MV's & MV LOG's

DDL INCLUDE MAPPED, EXCLUDE OBJTYPE 'SNAPSHOT', EXCLUDE OBJTYPE 'SNAPSHOT LOG'