Oracle DBA experience

Friday, June 5, 2015

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

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

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'