Oracle DBA experience: 2014

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

Tuesday, November 11, 2014

WARNING OGG-01519 Oracle GoldenGate Delivery for Oracle, r0000031.prm

Issue/Problem,

2014-11-11 13:06:58 WARNING OGG-01519 Oracle GoldenGate Delivery for Oracle, r0000031.prm: Waiting at EOF on input trail file /s01/ggsdata/dirdat/ig000035, which is not marked as complete; but succeeding trail file /s01/ggsdata/dirdat/ig000036 exists. If ALTER ETROLLOVER has been performed on source extract, ALTER EXTSEQNO must be performed on each corresponding downstream reader.

Reason for this problem

There has been a ETROLLOVER command issued on either Source Extract or Pump, due to which GG has moved to the new trail on the source Extract or Pump. While moving to the new trail file on the source environment, Golden Gate doesn’t make EOF comment or transaction mark on the old trail file. On the replicat site, GG will be waiting for the data or EOF message to come for the current trail from the source environment but another trail file started to write on the replicate site and replicat won’t be knowing it.

We have to manually move the trail file, else it will be staying on the current position.

Step involved in moving to the next trail

#1 Checking the status of current replicat

GGSCI (c959bhfengdnp) 11> info r0000031

REPLICAT R0000031 Last Started 2014-11-11 13:06 Status RUNNING

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

Log Read Checkpoint File /s01/ggsdata/dirdat/ig000035

First Record RBA 12629

When you check at the OS level, we can see 3 more trail files created and replicate won’t be knowing it and replicat will be staying on the sequence number 35.

GGSCI (c959bhfengdnp) 12> exit

ggs@bhuggs1:/s01/11.2: ls -lrt /s01/ggsdata/dirdat/ig*

-rw-r----- 1 ggs ggs 3523 Nov 5 11:50 /s01/ggsdata/dirdat/ig000026

-rw-r----- 1 ggs ggs 3015 Nov 5 20:44 /s01/ggsdata/dirdat/ig000027

-rw-r----- 1 ggs ggs 1288 Nov 5 20:56 /s01/ggsdata/dirdat/ig000028

-rw-r----- 1 ggs ggs 1422 Nov 5 21:27 /s01/ggsdata/dirdat/ig000029

-rw-r----- 1 ggs ggs 1288 Nov 5 21:34 /s01/ggsdata/dirdat/ig000030

-rw-r----- 1 ggs ggs 99999978 Nov 5 23:01 /s01/ggsdata/dirdat/ig000031

-rw-r----- 1 ggs ggs 99999929 Nov 5 23:05 /s01/ggsdata/dirdat/ig000032

-rw-r----- 1 ggs ggs 99999843 Nov 5 23:09 /s01/ggsdata/dirdat/ig000033

-rw-r----- 1 ggs ggs 60431010 Nov 8 11:35 /s01/ggsdata/dirdat/ig000034

-rw-r----- 1 ggs ggs 12629 Nov 10 14:00 /s01/ggsdata/dirdat/ig000035

-rw-r----- 1 ggs ggs 1560 Nov 10 16:12 /s01/ggsdata/dirdat/ig000036

-rw-r----- 1 ggs ggs 3859 Nov 11 01:58 /s01/ggsdata/dirdat/ig000037

-rw-r----- 1 ggs ggs 17533 Nov 11 14:37 /s01/ggsdata/dirdat/ig000038

ggs@bhuggs1:/s01/11.2: ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.27 19591627 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_141006.1156_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Oct 6 2014 16:45:49

GGSCI (c959bhfengdnp) 1> info r0000031

REPLICAT R0000031 Last Started 2014-11-11 13:06 Status RUNNING

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

Log Read Checkpoint File /s01/ggsdata/dirdat/ig000035

First Record RBA 12629

#2 To check whether GG has updated all the transaction in the current trail file;

To check whether any transaction left (or) it has reached the EOF; to check that we will be using send <REPLICAT NAME> logend, if the output is “YES” then it means it has reached the EOF if not then you have to use

'grouptransops 1 & maxtransops 1' in the parameter file of replicat and let it run, see that the replicat has applied all the records in the trail which is currently reading and check again the logend command and this should result in YES. Once you get Yes then you need to remove the parameter 'grouptransops 1 & maxtransops 1' from the Replicat file(if you are not clear, please don’t use it).

GGSCI (c959bhfengdnp) 2> send r0000031 logend

Sending LOGEND request to REPLICAT R0000031 ...

YES.

#3 Stop the Replicat to change the Sequence number of the trail file.

GGSCI (c959bhfengdnp) 3> stop r0000031

Sending STOP request to REPLICAT R0000031 ...

Request processed.

#4 Change the Sequence number of the trail file and RBA

GGSCI (c959bhfengdnp) 4> alter REPLICAT R0000031, EXTSEQNO 36, EXTRBA 0

REPLICAT altered.

#5 Just check the current state of the Replicat before starting it

GGSCI (c959bhfengdnp) 1> info r0000031

REPLICAT R0000031 Last Started 2014-11-11 13:06 Status RUNNING

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

Log Read Checkpoint File /s01/ggsdata/dirdat/ig000036

First Record RBA 0

#6 start the Replicat

GGSCI (c959bhfengdnp) 5> start REPLICAT R0000031

Sending START request to MANAGER ...

REPLICAT R0000031 starting

#7 checking the status of the replicat

GGSCI (c959bhfengdnp) 6> info REPLICAT R0000031

REPLICAT R0000031 Last Started 2014-11-11 14:53 Status RUNNING

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

Log Read Checkpoint File /s01/ggsdata/dirdat/ig000038

2014-11-11 14:52:42.769737 RBA 17852

It has reached end of the trail file and it is waiting for the information from the source.

Happy Reading :)

Tuesday, October 7, 2014

To Change Parallelism for a data pump job which is current running

To Change Parallelism for data pump job which is current running

To find the running jobs

SQL> select owner_name, job_name from dba_datapump_jobs;

OWNER_NAME JOB_NAME

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

SYSTEM GG1

$ expdp attach=SYSTEM.GG1

Export: Release 11.2.0.3.0 - Production on Tue Oct 7 10:47:53 2014

Username: system

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Job: GG1

Owner: SYSTEM

Operation: EXPORT

Creator Privs: TRUE

GUID: 04D26AD77698596AE0534F98340AEADE

Start Time: Tuesday, 07 October, 2014 9:59:52

Mode: TABLE

Instance: orq738a2

Max Parallelism: 1

EXPORT Job Parameters:

Parameter Name Parameter Value:

CLIENT_COMMAND system/******** parfile=exp.par

INCLUDE_METADATA 0

State: EXECUTING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /s01/oradata1/export/GG_07102014.dmp

bytes written: 4,096

Worker 1 Status:

Process Name: DW00

State: EXECUTING

Object Schema: ORAGGS

Object Name: GG_LEARNING

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 101

Completed Rows: 27,738

Worker Parallelism: 1

To Change the Parallelism

Export> parallel=10

After changing the Parallelism I could see that the value has changed.

To check the status after the existing table export is completed; So please wait for some more time.

Export> status

Job: GG1

Operation: EXPORT

Mode: TABLE

State: EXECUTING

Bytes Processed: 5,866,922,168

Percent Done: 18

Current Parallelism: 10

Job Error Count: 0

Dump File: /s01/oradata1/export/GG_07102014.dmp

bytes written: 5,866,930,176

Worker 1 Status:

Process Name: DW00

State: EXECUTING

Object Schema: ORAGGS

Object Name: GG_STRUCTURES

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 2

Total Objects: 101

Completed Rows: 18,758

Worker Parallelism: 1

Worker 2 Status:

Process Name: DW01

State: EXECUTING

Object Schema: ORAGGS

Object Name: SYN_GG_SCHEMES

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 101

Worker Parallelism: 1

Worker 3 Status:

Process Name: DW00

State: EXECUTING

Object Schema: ORAGGS

Object Name: GG_AUTHORS

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 101

Worker Parallelism: 1

Worker 4 Status:

Process Name: DW03

State: EXECUTING

Object Schema: ORAGGS

Object Name: GG_ABSTRACTS

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 101

Worker Parallelism: 1

Worker 5 Status:

Process Name: DW01

State: EXECUTING

Object Schema: ORAGGS

Object Name: GG_DDL_NEWS_SUBSECTIONS

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 101

Worker Parallelism: 1

Worker 6 Status:

Process Name: DW05

State: EXECUTING

Object Schema: ORAGGS

Object Name: GG_TEXTS

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 101

Worker Parallelism: 1

Worker 7 Status:

Process Name: DW02

State: EXECUTING

Object Schema: ORAGGS

Object Name: GG_DDN_NEWS

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 101

Worker Parallelism: 1

Worker 8 Status:

Process Name: DW07

State: EXECUTING

Object Schema: ORAGGS

Object Name: GG_SEQUENCES

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 101

Worker Parallelism: 1

Worker 9 Status:

Process Name: DW03

State: EXECUTING

Object Schema: ORAGGS

Object Name: GG_REF_REFERENCES

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 101

Worker Parallelism: 1

Worker 10 Status:

Process Name: DW09

State: EXECUTING

Object Schema: ORAGGS

Object Name: GG_STRUCTURES

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 101

Worker Parallelism: 1

Export>

To See the status of running job

$ expdp attach=SYSTEM.GG1

Export: Release 11.2.0.3.0 - Production on Tue Oct 7 14:26:04 2014

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Job: GG1

Owner: SYSTEM

Operation: EXPORT

Creator Privs: TRUE

GUID: 04D26AD77698596AE0534F98340AEADE

Start Time: Tuesday, 07 October, 2014 9:59:52

Mode: TABLE

Instance: orq738a2

Max Parallelism: 10

EXPORT Job Parameters:

Parameter Name Parameter Value:

CLIENT_COMMAND system/******** parfile=exp.par

INCLUDE_METADATA 0

State: EXECUTING

Bytes Processed: 0

Current Parallelism: 10

Job Error Count: 0

Dump File: /s01/oradata1/export/GG_07102014.dmp

bytes written: 4,096

Worker 1 Status:

Process Name: DW00

State: EXECUTING

Object Schema: ORAGGS

Object Name: GG_LEARNING

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 101

Completed Rows: 328,461

Worker Parallelism: 1

Export>

To See the Command prompt

select owner_name,JOB_NAME,OPERATION,JOB_MODE,STATE,DEGREE from dba_datapump_jobs;

SQL> desc dba_datapump_jobs

Name Null? Type

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

OWNER_NAME VARCHAR2(30)

JOB_NAME VARCHAR2(30)

OPERATION VARCHAR2(90)

JOB_MODE VARCHAR2(90)

STATE VARCHAR2(30)

DEGREE NUMBER

ATTACHED_SESSIONS NUMBER

DATAPUMP_SESSIONS NUMBER