Oracle DBA experience: December 2014

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!!!!

Oracle DBA experience

Thursday, December 4, 2014

Replacing Pump on Golden Gate V11.2 with same Name