Oracle Notes

Sharing Oracle technology knowledge and experiences.

Useful Oracle GoldenGate Troubleshooting Commands

Posted by sdefilip on July 15, 2010

Troubleshooting Commands

Extract

If there are any errors, the extract would appear as STOPPED. In this case, you have to investigate the errors.

First, check the report file C:\OGG10G\dirrpt\EMP_EXT.rpt.

Next, see if there are any indicative messages in the log files ggserr.log and sqlnet.log, which are in the main C:\OGG10G directory.

The ggserr.log file contains event information such as:

2009-12-02  14:53:26  GGS INFO        301   Oracle GoldenGate

Manager for Oracle, mgr.prm:  Command received from GGSCI on host 10.10.10.10 (START EXTRACT  EMP_EXT ).

2009-12-02  14:53:26  GGS INFO        302   Oracle GoldenGate Manager for Oracle, mgr.prm:

EXTRACT EMP_EXT starting.

These events can also be seen in the following way:

GGSCI (AnonDB) 1> VIEW GGSEVT

….

2009-12-02 15:09:34  GGS INFO        302  Oracle GoldenGate Manager for Oracle,

mgr.prm:  EXTRACT EMP_EXT starting.

2009-12-02 15:13:26  GGS INFO        399  Oracle GoldenGate Command Interpreter

for Oracle:  GGSCI command (AnonDB): EDIT PARAM emp_ext.

The sqlnet.log file may show errors such as

TNS-12557: TNS:protocol adapter not loadable

This particular error indicates a database connection issue, so you need to explore at the listener level. Make sure the Path includes the Oracle Home bin subdirectory right at the start. There may be other TNS errors that may indicate other solutions.

EMP_EXT.rpt may show an error such as the following:

2009-12-08 13:01:27  GGS ERROR       182  OCI Error beginning

session (status = 28009-ORA-28009: connection as SYS should be as SYSDBA or SYSOPER).

2009-12-08 13:01:27  GGS ERROR       190  PROCESS ABENDING.

This indicates that the Extract process is trying to log on as sys without being the SYSDBA. Simply change the login in the parameter file for the Extract to “system”.

I should note here that specifying SYSDBA at the end of the login line in the Extract parameter file doesn’t work, although it does work with the DBLOGIN command in GGSCI that you will see next.

The ggserr.log file may show similar errors:

2009-12-03 00:43:16  GGS INFO        399  Oracle GoldenGate

Command Interpreter for Oracle:  GGSCI command (AnonDB): start manager.

2009-12-03 00:43:25  GGS ERROR       182  Oracle GoldenGate

Manager for Oracle, mgr.prm:  OCI Error during OCIServerAttach

(status = 12154-ORA-12154: TNS:could not resolve the connect identifier specified).

2009-12-03 00:43:25  GGS ERROR       190  Oracle GoldenGate

Manager for Oracle, mgr.prm:  PROCESS ABENDING.

This indicates an issue with the login credentials supplied in the Manager parameter file, mgr.prm. The Manager has not started as a result.

Another possible error can be seen in the Extract report file. For example, EMP_EXT.rpt can have this error:

2009-12-07 16:40:08  GGS ERROR       190  No minimum supplemental

logging is enabled. This may cause extract process to handle key

update incorrectly if key column is not in first row piece.

2009-12-07 16:40:08  GGS ERROR       190  PROCESS ABENDING.

The solution to this is obviously to enable supplemental logging at the database level. C

Replicat

The first step is to use the GGSCI command DBLOGIN to connect to the database, so that other commands can be issued that will work on the database.

Note that DBLOGIN works fine as follows:

GGSCI ( AnonDB) 1>

DBLOGIN USERID system@localhost:1521/FIPRD3 PASSWORD fipassword1

Successfully logged into database.

Now you need to add a checkpoint table for the employees table that you are replicating:

GGSCI ( AnonDB) 2> ADD CHECKPOINTTABLE HR.EMPLOYEES_CHKPT

Successfully created checkpoint table HR.EMPLOYEES_CHKPT.

Checkpoints that are stored in this table refer to the current read and write positions of the Replicat process. This is used to prevent the loss of data in case the process needs to be restarted, or if there is any fault in the server or a hiccup in the network that would otherwise result in data loss. The other advantage is that multiple Extract or Replicat processes can be read from the same set of trails by using checkpoints.

Checkpoints are optional in the sense that they are not required for Extract and Replicat processes that run in batch mode, because such processes can always be restarted. However, checkpoints are necessary in the case of continuously operating Extract and Replicat processes.. They are normally maintained as files in the dirchk subdirectory, but in the case of Replicat they can optionally be stored in the database in the checkpoint table.

If you specify the checkpoint table in the GLOBALS parameter file, the above command can use that specification. In this scenario, the command could simply be

GGSCI (AnonDB) 3> ADD CHECKPOINTTABLE

No checkpoint table specified, using GLOBALS specification (hr.employees_chkpt).

Successfully created checkpoint table HR.EMPLOYEES_CHKPT.

You can now add the Replicat group as follows, specifying the exact same EXTTRAIL that was used by the Extract group set up in the first database. So the Replicat group feeds on or consumes the trail created by the Extract group:

GGSCI (AnonDB) 4> ADD REPLICAT emp_rep, EXTTRAIL C:\OGG10G\dirdat\et, CHECKPOINTTABLE hr.employees_chkpt,

REPLICAT added.

Edit the parameter file for this Replicat group as follows:

GGSCI (AnonDB) 5> EDIT PARAM emp_rep

In the new file, enter the following:

REPLICAT emp_rep

USERID system@localhost:1521/FIPRD3, PASSWORD fipassword1

ASSUMETARGETDEFS

MAP hr.employees, TARGET hr.employees;

Because the tables have exactly the same DDL structure, you use the ASSUMETARGETDEFS parameter.

Now you can start the Replicat group:

GGSCI (AnonDB) 6> start REPLICAT emp_rep

Sending START request to MANAGER (‘GGSMGR’) …

REPLICAT EMP_REP starting

Wait a few seconds to see the status; if you try immediately, the status may say “stopped.” When you see the status asrunning”, check the detailed information, and also issue an info all command to show all running processes:

GGSCI (AnonDB) 7> status REPLICAT emp_rep

REPLICAT EMP_REP: STOPPED

GGSCI (AnonDB) 8> status REPLICAT emp_rep

REPLICAT EMP_REP: RUNNING

GGSCI (AnonDB) 11> info REPLICAT emp_rep detail

REPLICAT   EMP_REP   Last Started 2009-12-08 13:35   Status RUNNING

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

Log Read Checkpoint  File C:\OGG10G\dirdat\et000001

2009-12-08 13:33:24.000000  RBA 985

Extract Source                          Begin             End

C:\OGG10G\dirdat\et000001               2009-12-08 13:33  2009-12-08 13:33

C:\OGG10G\dirdat\et000000               * Initialized *   2009-12-08 13:33

Current directory    C:\OGG10G

Report file          C:\OGG10G\dirrpt\EMP_REP.rpt

Parameter file       C:\OGG10G\dirprm\EMP_REP.prm

Checkpoint file      C:\OGG10G\dirchk\EMP_REP.cpr

Checkpoint table     HR.EMPLOYEES_CHKPT

Process file         C:\OGG10G\dirpcs\EMP_REP.pcr

Error log            C:\OGG10G\ggserr.log

GGSCI (AnonDB) 12> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     EMP_EXT     00:00:00      00:00:03

REPLICAT    RUNNING     EMP_REP     00:00:00      00:00:06

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: