Error: "Internal error: Error rolling back transaction for session #: ORA-00028" (4232927)

Error received in the event_log and maybe post had stopped due to error:

"Error: 12069 - OCITransRollback failed with ORA-28. [sp_opst(osp)/1166004]
Internal error: Error rolling back transaction for session 22: ORA-00028: your session has been killed. [sp_opst (for o.SPORT-o.DS queue jive)/1166004]"

1. Reusing the previous version of vardir, namely the SP_SYS_VARDIR/data/paramdb after upgrarding to to SharePlex 5.3+
2. Editing or replacing the paramdb, namely the parameter SP_OPO_TYPE on version 5.3+

The problem typically (but not exclusively) occurs when you're on SharePlex 5.3 or newer and in STP (Single Thread Post) mode. If your paramdb does not contain this parameter "SP_OPO_TYPE 0", by default your SharePlex 5.3 or newer will startup in MTP mode. Consequently, you will have 2 post reader processes (1 sp_opst_mt and 1 sp_opst) for one post queue.

For example, if you're in STP on SharePlex 5.3+, you should see SP_OPO_TYPE parameter in your paramdb:

SP_OPO_TYPE.<hostname>+<O.sourceSID-O.targetSID> "0" # poster type 0 = single-threaded, 1 = multi-threaded

To verify that you have two post READER accessing one post queue, use qview to identify:

NOTE: sp_cop must be running when you issue this qview command.

1. cd $prodir/bin
2. ./qview list

Sample output:

irvsupu15 # cd $p/bin
irvsupu15 # ./qview list

The following queues exist:
irvsupu15+P+o.hd920-o.hd920
subqueues range from 0 to 2
WRITER +PR+o.hd920+sp_ordr+o.hd920
READER +PP+irvsupu15+sp_opst+o.hd920-o.hd920
READER +PP+irvsupu15+sp_opst_mt+o.hd920-o.hd920

To resolve this issue please do the following:

1. Stop post (sp_ctrl> stop post) if it's not already.

2. Shutdown SharePlex (sp_ctrl> shutdown).

3. Delete the unwanted READER:

There are two ways to do this, the 1st one is easier but does not always work.

SOLUTION A. Run fixusers command. If you are running 5.3.+ then it will delete the STP reader. On prior versions it will delete the MTP reader. You can set the proper post type later

a. cd $prodir/bin
b. ./qview -i
c. qview> qsetup <portnumber>
d. qview> list <-- note the queue name that has 2 READER processes. In this example the queue name is irvsupu15+P+o.hd920-o.hd920
e. qview> exit
f. Run the fixusers command
1. cd $prodir/bin
2. ./qview -i
3. qview> fixusers <ueue name from step d)
g. repeat steps a - e. If there are still 2 READER processes then go to SOLUTION B


SOLUTION B.
a. cd $prodir/bin
b. ./qview -i
c. qview> qsetup <portnumber>
d. qview> list
e. Delete the STP if you are running 5.3+. Delete the MTP is you are running earlier versions.
The command is: (qview> deluser <queuename> <reader_sp_opst>)
For example: qview> deluser irvsupu15+P+o.hd920-o.hd920 +PP+irvsupu15+sp_opst+o.hd920-o.hd920
f. qview> exit

4. After performing SOLUTION A and/or B then start SharePlex (./sp_cop &).

5. Issue the status command to make sure post is still stopped

6. Set post to either single of multi. This will put an entry in SP_OPO_TYPE.<hostname>+<O.sourceSID-O.targetSID> "0" or "1" in your paramdb. (sp_ctrl> set post single|multi).

7. Start post (sp_ctrl> start post).