ADD SCHEMATRANDATA failing with OGG-06522 or OGG-01783

This issue can happen when you are trying to run "ADD SCHEMATRANDATA" data from the golden gate command line interface.Most probably this is the new setup and you are trying to configure goldengate  for the first time on this server.

Following is the example of the error message.

OGG-06522  Cannot verify existence of table function that is required to ADD schema level supplemental logging, failed.

1. In this case the first thing that you want to check is if you have all the required patches to support the SCHEMATRANDATA in oracle golden gate.If you have the MOS access following note will help you to identify all the required patches

DB Patches Needed to Support SCHEMATRANDATA in OGG (Oracle GoldenGate) (Doc ID 1426440.1)

2. If you have all the required patches and still you are getting the error while running the "ADD SCHEMATRANDATE" command then you need to check if there are any invalid objects inside the database.

You can run the $ORACLE_HOME/rdbms/admin/utlrp.sql to compile any invalid objects in the database.

3. If the issue occurs again then you may want to run sql trace on the goldengate session .

If the problem reproduces then enable the sql trace on the GG session using the following commands from sqlplus (Run ADD SCHEMATRANDATA in the gg sesssion) and analyze the trace files.

Specify OS Process ID.
SQL> oradebug setospid
SQL> oradebug unlimit
Enable SQL trace on that OS Process ID.
SQL> oradebug event 10046 trace name context forever, level 12

In the trace file you will be able to find the offending sql and you can take action based on that .You can also contact oracle support with the trace files.

If this solution fixed your issue or you need any help please leave a comment below.

Thanks !!

Oracle Orphaned Sessions: Killed blocking session is still in the Killed state and the corresponding row is no more found in the v$process

Hello folks,

I saw this interesting issue some time back .The problem in this case was that there were many sessions waiting for the lock on an object and there was a terminal blocker blocking these sessions.

To resolve this issue DBA did what he was supposed to do .The terminal blocker was killed using the commands "alter system kill session...." and the session was marked for kill.After some time the session was showing in the killed state but the session was still lingering around and it was never actually released out of the database.

Now to kill the session old ways we tried to get the corresponding process in the v$process view but there was no entry for matching the paddr column of v$session to the addr column of the v$process.

That is no matching row for that session in the v$process.So there is no way we can kill the process using the commands kill -9 .

The following query for that session will show no rows because there is no corresponding entry in the v$process view

SELECT s.sid, s.serial#, p.spid
FROM v$session s,v$process p
WHERE s.paddr = p.addr and s.sid=
/

If you take a sytemstate dump of the entire database this session will show up under ORPHAN SESSIONS (upper case if you are on unix based platform).

For some reason pmon was not able to cleanup this session and it is still there in the database as orphaned session.

The most irritating part of this problem is that this is locking many objects seen from select count(*) from v$locked_objects where sid= .

Now how to get rid of this session whose corresponding row is not there in the v$process.

SOLUTION

There are two solutions to this problem depending on what works best and is easy in your case.

1) Bouncing the database.

Most obvious solution anyone can think of is bouncing the database.This will no doubt fix this problem but it may not be acceptable in a prod environment to bounce the database just to get rid of an orphaned process.

2) Get help from oracle Doc ID 387077.1.

This is what I ended up doing.If you are in luck and you are on 11g then you can use this MOS note to try to resolve your issue.


Basically what happens when a session gets killed then a new process id gets created and the addr row  in v$process matching the addr row for the session which was there earlier will no longer be there.Instead a new row will be there.To uniquely identify the process and to fix this issue in 11g additional columns have been added to V$SESSION :

V$SESSION
CREATOR_ADDR - state object address of creating process
CREATOR_SERIAL# - serial number of creating process
CREATOR_ADDR is the column that can be joined with the ADDR column in V$PROCESS to uniquely identify the killed process corresponding to the former session.

Using these columns we can identify the process id of the killed session.In order to identify the new process id you can use the following query.

select * from v$process where addr=(select creator_addr from v$session where sid=);

Once you get the process id from the above query rest of the task is simple.Now we just need to kill the corresponding process id from the OS.

-> Kill -9

Now be patient , it will take some time for the pmon process to finally do the cleanup.

If this is the issue with the stuck cleanup of the killed session then killing the OS process will help to restart the cleanup.

In some cases it may not be able to cleanup and you may need to bounce the database.

If this solution works for you its great .

If not then you know what to do next =>BOUNCE !!

Good Luck!