Oracle DBA experience

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

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'

Thursday, December 4, 2014

Replacing Pump on Golden Gate V11.2 with same Name

We had a Golden Gate replication with EXTRACT, PUMP & REPLICAT format. For some reason Pump was hanging in the same place; we plan to replace the PUMP with the new one with the same name and same details.

Note: we couldn’t stop the PUMP using normal stop. So we have used the kill

1 (/ggsbin/11.2)(SOURCE)

We tried to stop pump process (P0000037), but it cannot be stopped as before with timeout message, so we killed it:

GGSCI 1> KILL P0000037

2. Then we got detailed information about this process (I’ve highlighted information we used later to recreate the process): (SOURCE)

GGSCI 2> info P0000037, showch, detail

EXTRACT P0000037 Last Started 2014-12-03 15:36 Status ABENDED

Checkpoint Lag 00:00:00 (updated 171:44:59 ago)

Log Read Checkpoint File /ggsdata/dirdat/bhu/hr/tr000046

2014-11-26 11:57:03.000000 RBA 37973983

Current Checkpoint Detail:

Read Checkpoint #1

GGS Log Trail

Startup Checkpoint (starting position in the data source):

Sequence #: 46

RBA: 37973983

Timestamp: 2014-11-26 11:57:03.000000

Extract Trail: /ggsdata/dirdat/bhu/hr/tr

Current Checkpoint (position of last record read in the data source):

Sequence #: 46

RBA: 37973983

Timestamp: 2014-11-26 11:57:03.000000

Extract Trail: /ggsdata/dirdat/bhu/hr/tr

Write Checkpoint #1

GGS Log Trail

Current Checkpoint (current write position):

Sequence #: 46

RBA: 65832552

Timestamp: 2014-11-26 11:57:08.027390

Extract Trail: /ggsdata/dirdat/test/tr

CSN state information:

CRC: F1-F1-2F-27

Latest CSN: 2156609198684

Latest TXN: 10.18.1315688

Latest CSN of finished TXNs: 2156609198684

Completed TXNs: 10.18.1315688

Header:

Version = 2

Record Source = A

Type = 1

# Input Checkpoints = 1

# Output Checkpoints = 1

File Information:

Block Size = 2048

Max Blocks = 100

Record Length = 2048

Current Offset = 0

Configuration:

Data Source = 0

Transaction Integrity = 1

Task Type = 0

Status:

Start Time = 2014-12-03 15:36:47

Last Update Time = 2014-11-26 11:57:08

Stop Status = A

Last Result = 400

Target Extract Trails:

Remote Trail Name Seqno RBA Max MB

/ggsdata/dirdat/test/tr 46 65832552 100

Extract Source Begin End

/ggsdata/dirdat/bhu/hr/tr000046 2014-11-26 11:57 2014-11-26 11:57

/ggsdata/dirdat/bhu/hr/tr000046 * Initialized * 2014-11-26 11:57

/ggsdata/dirdat/bhu/hr/tr000034 * Initialized * First Record

/ggsdata/dirdat/bhu/hr/tr000030 2014-10-29 16:00 2014-10-29 16:00

/ggsdata/dirdat/bhu/hr/tr000030 2014-10-25 11:56 2014-10-29 16:00

/ggsdata/dirdat/bhu/hr/tr000028 2014-10-24 18:00 2014-10-25 11:56

/ggsdata/dirdat/bhu/hr/tr000028 * Initialized * 2014-10-24 18:00

/ggsdata/dirdat/bhu/hr/tr000020 * Initialized * First Record

/ggsdata/dirdat/bhu/hr/tr000020 2014-10-11 12:57 2014-10-11 12:57

/ggsdata/dirdat/bhu/hr/tr000020 2014-09-27 06:00 2014-10-11 12:57

/ggsdata/dirdat/bhu/hr/tr000012 2014-09-27 06:00 2014-09-27 06:00

Current directory /ggsbin/11.2

Report file /ggsbin/11.2/dirrpt/P0000037.rpt

Parameter file /ggsbin/11.2/dirprm/p0000037.prm

Checkpoint file /ggsbin/11.2/dirchk/P0000037.cpe

Process file /ggsbin/11.2/dirpcs/P0000037.pce

Stdout file /ggsbin/11.2/dirout/P0000037.out

Error log /ggsbin/11.2/ggserr.log

3. We checked what number of trail sequence was delivered to Replicat side: (TARGET)

GGSCI> info R0000037, detail

REPLICAT R0000037 Last Started 2014-12-02 14:13 Status RUNNING

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

Log Read Checkpoint File /ggsdata/dirdat/test/tr000046

First Record RBA 66056184

4. We backed up param, process (which remained even after kill of the process) and checkpoint files just in case: (SOURCE)

# mkdir /ggsbin/11.2/dirprm/bak

# cp /ggsbin/11.2/dirprm/p0000037.prm /ggsbin/11.2/dirprm/bak/

# mv /ggsbin/11.2/dirchk/P0000037.cpe /ggsbin/11.2/dirchk/P0000037.cpe.bak

# mv /ggsbin/11.2/dirpcs/P0000037.pce /ggsbin/11.2/dirpcs/P0000037.pce.bak

Note:

1) We have moved the checkpoint file & process file to a different name

2) We have checked the process ID from the process file, whether process is exist in the server by using ps –ef|grep <process_Id>

5. Then pump process has been deleted (SOURCE)

GGSCI 1> DBLOGIN USERID ggs@bhu, PASSWORD AADAAAAAAAAAAJNBOGKGNHZBOALCFBNHEERHZIQFQDUJCHKF AES128, ENCRYPTKEY PROD

Successfully logged into database.

GGSCI 2> DELETE EXTRACT P0000037

Deleted EXTRACT P0000037.

6. Then we checked that process file and checkpoint files are gone

7. Creating of pump process back (please, look at highlighted values used from p.2 and 3 outputs). Sequence 47 – is <last sequence of trails on REPLICAT side, which was 46 > + 1: (SOURCE)

GGSCI 1> DBLOGIN USERID ggs@bhu, PASSWORD AADAAAAAAAAAAJNBOGKGNHZBOALCFBNHEERHZIQFQDUJCHKF AES128, ENCRYPTKEY PROD

Successfully logged into database.

GGSCI 2> ADD EXTRACT P0000037, EXTTRAILSOURCE /ggsdata/dirdat/bhu/hr/tr

EXTRACT added.

GGSCI 8> ALTER EXTRACT P0000037, EXTSEQNO 46, EXTRBA 37973983

EXTRACT altered.

GGSCI 10> ADD RMTTRAIL /ggsdata/dirdat/test/tr, SEQNO 47, EXTRACT P0000037

RMTTRAIL added.

GGSCI 11> info RMTTRAIL /ggsdata/dirdat/test/tr

Extract Trail: /ggsdata/dirdat/test/tr

Extract: P0000037

Seqno: 47

RBA: 0

File Size: 100M

GGSCI 12> info P0000037, showch (SOURCE)

EXTRACT P0000037 Initialized 2014-12-03 15:48 Status STOPPED

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

Log Read Checkpoint File /ggsdata/dirdat/bhu/hr/tr000046

First Record RBA 37973983

Current Checkpoint Detail:

Read Checkpoint #1

GGS Log Trail

Startup Checkpoint (starting position in the data source):

Sequence #: 46

RBA: 37973983

Timestamp: Not Available

Extract Trail: /ggsdata/dirdat/bhu/hr/tr

Current Checkpoint (position of last record read in the data source):

Sequence #: 46

RBA: 37973983

Timestamp: Not Available

Extract Trail: /ggsdata/dirdat/bhu/hr/tr

Write Checkpoint #1

GGS Log Trail

Current Checkpoint (current write position):

Sequence #: 47

RBA: 0

Timestamp: 2014-12-03 15:49:44.703064

Extract Trail: /ggsdata/dirdat/test/tr

8. Stop replicat process on QA(TARGET)

GGSCI 4> stop R0000037

Sending STOP request to REPLICAT R0000037 ...

Request processed.

9. Alter replicat process on Target site: (TARGET)

GGSCI 2> ALTER REPLICAT R0000037, EXTSEQNO 47, EXTRBA 0

REPLICAT altered.

(SOURCE)

10. Start pump process on EXP:

GGSCI 13> start P0000037

Sending START request to MANAGER ...

EXTRACT P0000037 starting

GGSCI 14> info P0000037

EXTRACT P0000037 Last Started 2014-12-03 15:50 Status RUNNING

Description "Prous CMS - QAL: cms.exp -> cms.qa"

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

Log Read Checkpoint File /ggsdata/dirdat/bhu/hr/tr000046

First Record RBA 37973983

AND IT STARTED TO PROCEED WITH NEXT TRAIL FILES:

…

GGSCI 20> info P0000037

EXTRACT P0000037 Last Started 2014-12-03 15:50 Status RUNNING

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

Log Read Checkpoint File /ggsdata/dirdat/bhu/hr/tr000052

2014-12-03 15:00:00.000000 RBA 27682741

11. Start replicat process on QA: (TARGET)

GGSCI 3> start R0000037

Sending START request to MANAGER ...

REPLICAT R0000037 starting

GGSCI 11> info R0000037

REPLICAT R0000037 Last Started 2014-12-03 15:51 Status RUNNING

Checkpoint Lag 168:56:44 (updated 00:00:00 ago)

Log Read Checkpoint File /ggsdata/dirdat/test/tr000047

2014-11-26 14:56:55.891810 RBA 5711632

…

GGSCI 12> info R0000037

REPLICAT R0000037 Last Started 2014-12-03 15:51 Status RUNNING

Checkpoint Lag 24:52:53 (updated 00:00:00 ago)

Log Read Checkpoint File /ggsdata/dirdat/test/tr000050

2014-12-02 15:35:41.914083 RBA 25212268

Happy Learning!!!!