This command returns information for use in troubleshooting a specified database transaction log operation.
This command is available only for Oracle.
ra_helpop “[opid | locator]”
The Replication Agent operation ID for a database operation.
The Replication Agent locator for a database operation.
ra_helpop
0x0000.01783d96.0000:0001.000003fe.00000034.00e8
go
This command returns information about the operation specified by the Replication Agent operation ID:
Name Value
-------------- ------------------------------------------
OPERATION ID 0x0000.01783d96.0000:0001.000003fe.00000034.00e8
SCN 24657302
THREAD 1
USERNAME AUSER
EXECUTION TIME 2010-07-12 10:28:14.0
OBJECT ID 51809
OBJECT NAME BLL$TEST
OPERATION INSERT
REPLICATE YES
TRANSACTION ID 0004.0016.00000016
REDO SQL insert into "AUSER"."BLL$TEST"("QUANTITY",
"ORDER_NUMBER") values ('85','1234567890');
UNDO SQL delete from "AUSER"."BLL$TEST" where "QUANTITY" =
'85' and "ORDER_NUMBER" = '1234567890' and ROWID = 'AAAMphAAEAAAYrWAAC';
SKIP COMMAND pdb_skip_op add, 24657302, 1, 1022.52.2322
(13 rows affected)
ra_helpop
0000000001783d9600020001000003fe0000003400e8000001783d9500000000
go
This command returns information about the operation specified by the Replication Agent locator:
Name Value
-------------- ------------------------------------------
OPERATION ID 0x0000.01783d96.0000:0001.000003fe.00000033.010c
SCN 24657302
THREAD 1
USERNAME AUSER
EXECUTION TIME 2010-07-12 10:28:14.0
OBJECT ID 51809
OBJECT NAME BLL$TEST
OPERATION INSERT
REPLICATE YES
TRANSACTION ID 0004.0016.00000016
REDO SQL insert into "AUSER"."BLL$TEST"("QUANTITY",
"ORDER_NUMBER") values ('85','1234567890');
UNDO SQL delete from "AUSER"."BLL$TEST" where "QUANTITY" =
'85' and "ORDER_NUMBER" = '1234567890' and ROWID = 'AAAMphAAEAAAYrWAAC';
SKIP COMMAND pdb_skip_op add, 24657302, 1, 1022.52.232
(13 rows affected)
The ra_helpop command displays database and Replication Agent information for a specified operation for use in troubleshooting a failed operation or transaction. The result set returned by ra_helpop includes the following rows:
OPERATION ID – the Replication Agent operation ID in the format wrap.scn.subscn.thread.lsn.block.offset, where:
wrap.scn.subscn is the system change number (SCN) for the specified operation.
thread is the database thread number.
lsn is the log sequence number for the specified operation.
block is the block where the specified operation resides.
offset is the offset into the operation where the specified operation resides.
SCN – the SCN for the operation as logged in a redo log file.
THREAD – the thread that executed the operation.
USERNAME – the name of the user that executed the operation.
EXECUTION TIME – the date and time at which the operation was executed.
OBJECT ID – the database ID of the affected object.
OBJECT NAME – the name of the affected object.
OPERATION – the operation type.
REPLICATE – whether or not (YES or NO) the object affected by the operation is marked for replication by Replication Agent.
TRANSACTION ID – the ID of the transaction that the operation is a part of.
REDO SQL – the SQL that can be used to replay the operation.
UNDO SQL – the SQL that can be used to undo the operation.
SKIP COMMAND – the Replication Agent command that causes the operation to be skipped by Replication Agent during replication.
The ra_helpop command can display information for more than one operation. Operation information is returned for each operation that has the SCN specified by locator or opid.
The ra_helpop command cannot operate properly unless the Oracle LogMiner script, $ORACLE_HOME/rdbms/admin/dbmslm.sql, has been installed at the primary database. If this script has not been installed, ra_helpop will return an error.
After LogMiner is installed, create a public synonym so that you do not have to log in as the owner to execute LogMiner functions:
CREATE PUBLIC SYNONYM DBMS_LOGMNR FOR SYS.DBMS_LOGMNR;
This is required if you are using Oracle 10g.
The following privileges must be granted to pds_username for the ra_helpop command to function properly:
EXECUTE_CATALOG_ROLE
SELECT ON V_$LOGMNR_CONTENTS
SELECT ON V_$LOGMNR_LOGS
SELECT ANY TRANSACTION
A single DML command may be represented in the Oracle redo log as a succession of two or more operations. Oracle LogMiner, however, will display only the SCN of the first operation. Consequently, ra_helpop may return no result for an operation that occurs in the middle of a succession of operations in the Oracle redo log. If ra_helpop returns no result for a specified opid or locator value, use the ra_dumptran command, specifying the transaction ID of the transaction to which the database operation belongs, and dump the entire transaction from the Oracle redo log. Then, search the dump for the operation that Oracle LogMiner did not find.
