11 Monitoring XStream In
You can monitor an XStream In configuration by querying data dictionary views.
This chapter provides instructions for monitoring XStream.
With XStream In, an Oracle Apply process functions as an inbound server. Therefore, you can also use the data dictionary views for apply processes to monitor inbound servers.
Note:
Whenever possible, this chapter uses ALL_
static data dictionary views for query examples. In some cases, information in the ALL_
views is more limited than the information in the DBA_
views.
In SQL*Plus, trusted XStream administrators can query the ALL_
and DBA_
views. Untrusted XStream administrators can query the ALL_
views only.
- Displaying Session Information for Inbound Servers
An example illustrates displaying session information for inbound servers. - Displaying General Information About an Inbound Server
An example illustrates displaying general information about an inbound server. - Monitoring the History of Events for XStream In Components
An example illustrates monitoring the history of events for XStream In components by querying theDBA_REPLICATION_PROCESS_EVENTS
view. - Displaying the Status and Error Information for an Inbound Server
An example illustrates displaying the status and error information for an inbound server. - Displaying Apply Parameter Settings for an Inbound Server
An example illustrates displaying apply parameter settings for an inbound server. - Displaying the Position Information for an Inbound Server
An example illustrates displaying the position information for an inbound server. - Displaying Information About DML Conflict Handlers
TheDBA_APPLY_DML_CONF_HANDLERS
view displays information about DML conflict handlers. - Displaying Information About Error Handlers
TheDBA_APPLY_REPERROR_HANDLERS
view displays information about DML conflict handlers. - Checking for Apply Errors
An example illustrates checking for apply errors. - Displaying Detailed Information About Apply Errors
SQL scripts display detailed information about the error transactions in the error queue in a database.
See Also:
Parent topic: XStream In
11.1 Displaying Session Information for Inbound Servers
An example illustrates displaying session information for inbound servers.
The query in this section displays the following session information about each XStream component in a database:
-
The XStream component name
-
The session identifier
-
The serial number
-
The operating system process identification number
-
The XStream process number
This query is especially useful for determining the session information for specific XStream components when there are multiple XStream In components configured in a database.
To display this information for each XStream component in a database:
-
Connect to the database as the XStream administrator.
See Oracle Database Administrator’s Guide for information about connecting to a database in SQL*Plus.
-
Run the following query:
COLUMN ACTION HEADING 'XStream Component' FORMAT A30 COLUMN SID HEADING 'Session ID' FORMAT 99999 COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 99999999 COLUMN PROCESS HEADING 'Operating System|Process Number' FORMAT A17 COLUMN PROCESS_NAME HEADING 'XStream|Process|Number' FORMAT A7 SELECT /*+PARAM('_module_action_old_length',0)*/ ACTION, SID, SERIAL#, PROCESS, SUBSTR(PROGRAM,INSTR(PROGRAM,'(')+1,4) PROCESS_NAME FROM V$SESSION WHERE MODULE ='XStream';
Your output for an XStream In configuration looks similar to the following:
Session XStream Serial Operating System Process XStream Component Session ID Number Process Number Number ------------------------------ ---------- --------- ----------------- ------- XIN - Apply Reader 19 9 27304 AS01 XIN - Apply Server 22 5 27308 AS03 XIN - Apply Server 25 31 27313 AS05 XIN - Apply Coordinator 112 7 27302 AP01 XIN - Apply Server 113 5 27306 AS02 XIN - Propagation Receiver 114 17 27342 TNS XIN - Apply Server 115 39 27311 AS04
The row that shows TNS
for the XStream process number contains information about the session for the XStream client application that is attached to the inbound server.
Note:
To run this query, a user must have the necessary privileges to query the V$SESSION
view.
See Also:
Oracle Database Reference for more information about the V$SESSION
view
Parent topic: Monitoring XStream In
11.2 Displaying General Information About an Inbound Server
An example illustrates displaying general information about an inbound server.
You can display the following information for an inbound server by running the query in this section:
-
The inbound server name
-
The owner of the queue used by the inbound server
-
The name of the queue used by the inbound server
-
The apply user for the inbound server
To display general information about an inbound server:
-
Connect to the database as the XStream administrator.
See Oracle Database Administrator’s Guide for information about connecting to a database in SQL*Plus.
-
Run the following query:
COLUMN SERVER_NAME HEADING 'Inbound Server Name' FORMAT A20 COLUMN QUEUE_OWNER HEADING 'Queue Owner' FORMAT A15 COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A15 COLUMN APPLY_USER HEADING 'Apply User' FORMAT A15 SELECT SERVER_NAME, QUEUE_OWNER, QUEUE_NAME, APPLY_USER FROM ALL_XSTREAM_INBOUND;
Your output looks similar to the following:
Inbound Server Name Queue Owner Queue Name Apply User -------------------- --------------- --------------- --------------- XIN XSTRMADMIN XIN_QUEUE XSTRMADMIN
See Also:
Parent topic: Monitoring XStream In
11.3 Monitoring the History of Events for XStream In Components
An example illustrates monitoring the history of events for XStream In components by querying the DBA_REPLICATION_PROCESS_EVENTS
view.
For example, this view can display when a component was created or started. It can also display when a component parameter was changed. If the component encountered an error, then it can display information about the error.
The query in this topic displays the following information about XStream Out component events:
-
The XStream component name
-
The component type
-
The event name
-
The description of the event
-
The event time
To display this information for each XStream In component in a database:
-
Connect to the database as the XStream administrator.
See Oracle Database Administrator’s Guide for information about connecting to a database in SQL*Plus.
-
Run the following query:
COLUMN STREAMS_NAME FORMAT A12 COLUMN PROCESS_TYPE FORMAT A17 COLUMN EVENT_NAME FORMAT A10 COLUMN DESCRIPTION FORMAT A20 COLUMN EVENT_TIME FORMAT A15 SELECT STREAMS_NAME, PROCESS_TYPE, EVENT_NAME, DESCRIPTION, EVENT_TIME FROM DBA_REPLICATION_PROCESS_EVENTS;
Your output for an XStream In configuration looks similar to the following:
STREAMS_NAME PROCESS_TYPE EVENT_NAME DESCRIPTION EVENT_TIME ------------ ----------------- ---------- -------------------- --------------- APP_JOBS APPLY COORDINATOR CREATE SUCCESS 03-NOV-15 07.19 .27.238151 AM APP_JOBS APPLY COORDINATOR START SUCCESS 03-NOV-15 07.21 .50.812534 AM APP_JOBS APPLY READER START SUCCESS 03-NOV-15 07.21 .51.713367 AM APP_JOBS APPLY SERVER START SUCCESS 03-NOV-15 07.21 .51.895019 AM
Related Topics
Parent topic: Monitoring XStream In
11.4 Displaying the Status and Error Information for an Inbound Server
An example illustrates displaying the status and error information for an inbound server.
The DBA_APPLY
view shows XStream
In
in the PURPOSE
column for an apply process that is functioning as an inbound server.
To display the status of an inbound server:
-
Connect to the database as the XStream administrator.
See Oracle Database Administrator’s Guide for information about connecting to a database in SQL*Plus.
-
Run the following query:
COLUMN APPLY_NAME HEADING 'Inbound Server|Name' FORMAT A15 COLUMN STATUS HEADING 'Status' FORMAT A8 COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 9999999 COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A40 SELECT APPLY_NAME, STATUS, ERROR_NUMBER, ERROR_MESSAGE FROM DBA_APPLY WHERE PURPOSE = 'XStream In';
Your output looks similar to the following:
Inbound Server Name Status Error Number Error Message --------------- -------- ------------ ---------------------------------------- XIN ENABLED
This output shows that XIN
is an apply process that is functioning as an inbound server.
Note:
This example queries the DBA_APPLY
view. This view enables trusted users to see information for all apply users in the database. Untrusted users must query the ALL_APPLY
view, which limits information to the current user.
See Also:
Parent topic: Monitoring XStream In
11.5 Displaying Apply Parameter Settings for an Inbound Server
An example illustrates displaying apply parameter settings for an inbound server.
Apply parameters determine how an inbound server operates.
To display the apply parameter settings for an inbound server:
-
Connect to the database as the XStream administrator.
See Oracle Database Administrator’s Guide for information about connecting to a database in SQL*Plus.
-
Run the following query:
COLUMN APPLY_NAME HEADING 'Inbound Server|Name' FORMAT A15 COLUMN PARAMETER HEADING 'Parameter' FORMAT A30 COLUMN VALUE HEADING 'Value' FORMAT A22 COLUMN SET_BY_USER HEADING 'Set by|User?' FORMAT A10 SELECT APPLY_NAME, PARAMETER, VALUE, SET_BY_USER FROM ALL_APPLY_PARAMETERS a, ALL_XSTREAM_INBOUND i WHERE a.APPLY_NAME=i.SERVER_NAME ORDER BY a.PARAMETER;
Your output looks similar to the following:
Inbound Server Set by Name Parameter Value User? --------------- ------------------------------ ---------------------- ---------- XIN ALLOW_DUPLICATE_ROWS N NO XIN APPLY_SEQUENCE_NEXTVAL Y NO XIN COMMIT_SERIALIZATION DEPENDENT_TRANSACTIONS NO XIN COMPARE_KEY_ONLY Y NO XIN COMPUTE_LCR_DEP_ON_ARRIVAL N NO XIN DISABLE_ON_ERROR Y NO XIN DISABLE_ON_LIMIT N NO XIN EAGER_SIZE 9500 NO XIN ENABLE_XSTREAM_TABLE_STATS Y NO XIN EXCLUDETAG NO XIN EXCLUDETRANS NO XIN EXCLUDEUSER NO XIN EXCLUDEUSERID NO XIN GETAPPLOPS Y NO XIN GETREPLICATES N NO XIN GROUPTRANSOPS 250 NO XIN HANDLECOLLISIONS N NO XIN IGNORE_TRANSACTION NO XIN MAXIMUM_SCN INFINITE NO XIN MAX_PARALLELISM 50 NO XIN MAX_SGA_SIZE INFINITE NO XIN OPTIMIZE_PROGRESS_TABLE Y NO XIN OPTIMIZE_SELF_UPDATES Y NO XIN PARALLELISM 4 NO XIN PRESERVE_ENCRYPTION Y NO XIN RTRIM_ON_IMPLICIT_CONVERSION Y NO XIN STARTUP_SECONDS 0 NO XIN SUPPRESSTRIGGERS Y NO XIN TIME_LIMIT INFINITE NO XIN TRACE_LEVEL 0 NO XIN TRANSACTION_LIMIT INFINITE NO XIN TXN_AGE_SPILL_THRESHOLD 900 NO XIN TXN_LCR_SPILL_THRESHOLD 10000 NO XIN WRITE_ALERT_LOG Y NO
Inbound servers ignore some apply parameter settings.
Note:
If the Set
by
User?
column is NO
for a parameter, then the parameter is set to its default value. If the Set
by
User?
column is YES
for a parameter, then the parameter was set by a user and might or might not be set to its default value.
See Also:
-
Oracle Database PL/SQL Packages and Types Reference for information about apply parameters
Parent topic: Monitoring XStream In
11.6 Displaying the Position Information for an Inbound Server
An example illustrates displaying the position information for an inbound server.
For an inbound server, you can view position information by querying the ALL_XSTREAM_INBOUND_PROGRESS
view. Specifically, you can display the following position information by running the query in this section:
-
The inbound server name
-
The applied low position for the inbound server
-
The spill position for the inbound server
-
The applied high position for the inbound server
-
The processed low position for the inbound server
To display the position information for an inbound server:
-
Connect to the database as the XStream administrator.
See Oracle Database Administrator’s Guide for information about connecting to a database in SQL*Plus.
-
Run the following query:
COLUMN SERVER_NAME HEADING 'Inbound|Server|Name' FORMAT A10 COLUMN APPLIED_LOW_POSITION HEADING 'Applied Low|Position' FORMAT A15 COLUMN SPILL_POSITION HEADING 'Spill Position' FORMAT A15 COLUMN APPLIED_HIGH_POSITION HEADING 'Applied High|Position' FORMAT A15 COLUMN PROCESSED_LOW_POSITION HEADING 'Processed Low|Position' FORMAT A15 SELECT SERVER_NAME, APPLIED_LOW_POSITION, SPILL_POSITION, APPLIED_HIGH_POSITION, PROCESSED_LOW_POSITION FROM ALL_XSTREAM_INBOUND_PROGRESS;
Your output looks similar to the following:
Inbound Server Applied Low Applied High Processed Low Name Position Spill Position Position Position ---------- --------------- --------------- --------------- --------------- XIN C10A C11D C10A C11D
The values of the positions shown in the output were set by the client application that attaches to the inbound server. However, the inbound server determines which values are the current applied low position, spill position, applied high position, and processed low position.
See Also:
Parent topic: Monitoring XStream In
11.7 Displaying Information About DML Conflict Handlers
The DBA_APPLY_DML_CONF_HANDLERS
view displays information about DML conflict handlers.
SET_DML_CONFLICT_HANDLER
procedure in the DBMS_APPLY_ADM
package.
- Connect to the database as the XStream administrator.
- Query the
DBA_APPLY_DML_CONF_HANDLERS
view.
Example 11-1 Displaying Information About DML Conflict Handlers
COLUMN APPLY_NAME FORMAT A8
COLUMN OBJECT_OWNER FORMAT A5
COLUMN OBJECT_NAME FORMAT A12
COLUMN COMMAND_TYPE FORMAT A6
COLUMN CONFLICT_TYPE FORMAT A11
COLUMN METHOD_NAME FORMAT A12
COLUMN CONFLICT_HANDLER_NAME FORMAT A20
SELECT APPLY_NAME,
OBJECT_OWNER,
OBJECT_NAME,
COMMAND_TYPE,
CONFLICT_TYPE,
METHOD_NAME,
CONFLICT_HANDLER_NAME
FROM DBA_APPLY_DML_CONF_HANDLERS
ORDER BY OBJECT_OWNER, OBJECT_NAME, CONFLICT_HANDLER_NAME;
Your output looks similar to the following:
APPLY_NA OBJEC OBJECT_NAME COMMAN CONFLICT_TY METHOD_NAME CONFLICT_HANDLER_NAM
-------- ----- ------------ ------ ----------- ------------ --------------------
APP_JOBS HR JOBS DELETE ROW_MISSING IGNORE JOBS_HANDLER_DELETE
APP_JOBS HR JOBS INSERT ROW_EXISTS OVERWRITE JOBS_HANDLER_INSERT
APP_JOBS HR JOBS UPDATE ROW_EXISTS OVERWRITE JOBS_HANDLER_UPDATE
Related Topics
Parent topic: Monitoring XStream In
11.8 Displaying Information About Error Handlers
The DBA_APPLY_REPERROR_HANDLERS
view displays information about DML conflict handlers.
SET_REPERROR_HANDLER
procedure in the DBMS_APPLY_ADM
package.
- Connect to the database as the XStream administrator.
- Query the
DBA_APPLY_REPERROR_HANDLERS
view.
Example 11-2 Displaying Information About DML Conflict Handlers
COLUMN APPLY_NAME FORMAT A15
COLUMN OBJECT_OWNER FORMAT A15
COLUMN OBJECT_NAME FORMAT A15
COLUMN ERROR_NUMBER 999999999
COLUMN METHOD FORMAT A15
SELECT APPLY_NAME,
OBJECT_OWNER,
OBJECT_NAME,
ERROR_NUMBER,
METHOD
FROM DBA_APPLY_REPERROR_HANDLERS
ORDER BY OBJECT_OWNER, OBJECT_NAME;
Your output looks similar to the following:
APPLY_NAME OBJECT_OWNER OBJECT_NAME ERROR_NUMBER METHOD
--------------- --------------- --------------- ------------ ---------------
APP_OE OE ORDERS 26787 IGNORE
Related Topics
Parent topic: Monitoring XStream In
11.9 Checking for Apply Errors
An example illustrates checking for apply errors.
Trusted users can check for apply errors by querying the DBA_APPLY_ERROR
data dictionary view or by using Oracle Enterprise Manager Cloud Control. Untrusted users can check for apply errors by querying the ALL_APPLY_ERROR
data dictionary view.
To check for apply errors:
-
Connect to the database as the XStream administrator.
See Oracle Database Administrator’s Guide for information about connecting to a database in SQL*Plus.
-
Run the following query:
COLUMN APPLY_NAME HEADING 'Inbound|Server|Name' FORMAT A7 COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A8 COLUMN SOURCE_TRANSACTION_ID HEADING 'Source|Transaction|ID' FORMAT A11 COLUMN MESSAGE_NUMBER HEADING 'Failed Message|in Error|Transaction' FORMAT 99999999 COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999 COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A10 COLUMN MESSAGE_COUNT HEADING 'Messages in|Error|Transaction' FORMAT 99999999 SELECT APPLY_NAME, SOURCE_DATABASE, SOURCE_TRANSACTION_ID, MESSAGE_NUMBER, ERROR_NUMBER, ERROR_MESSAGE, MESSAGE_COUNT FROM ALL_APPLY_ERROR;
Note:
Trusted users should replace ALL_APPLY_ERROR
with DBA_APPLY_ERROR
in the query.
If there are any apply errors, then your output looks similar to the following:
Inbound Source Failed Message Messages in Server Source Transaction in Error Error Name Database ID Transaction Error Number Error Mess Transaction ------- -------- ----------- -------------- ------------ ---------- ----------- XIN OUTX.EXA 19.20.215 1 1031 ORA-01031: 1 MPLE.COM insuffici ent privil eges XIN OUTX.EXA 11.21.158 1 1031 ORA-01031: 1 MPLE.COM insuffici ent privil eges
If there are apply errors, then you can either try to reexecute the transactions that encountered the errors, or you can delete the transactions. To reexecute a transaction that encountered an error, first correct the condition that caused the transaction to raise an error.
If you want to delete a transaction that encountered an error, and if you are sharing data between multiple databases, then you might need to resynchronize data manually. Remember to set an appropriate session tag, if necessary, when you resynchronize data manually.
See Also:
Parent topic: Monitoring XStream In
11.10 Displaying Detailed Information About Apply Errors
SQL scripts display detailed information about the error transactions in the error queue in a database.
- Step 1: Grant Explicit SELECT Privilege on the ALL_APPLY_ERROR View
Running theGRANT_ADMIN_PRIVILEGE
procedure in theDBMS_XSTREAM_AUTH
package on a user grants theSELECT
privilege on theALL_APPLY_ERROR
view to the user. - Step 2: Create a Procedure that Prints the Value in an ANYDATA Object
Create a procedure that prints the value in a specifiedANYDATA
object for some selected data types. Optionally, you can add more data types to this procedure. - Step 3: Create a Procedure that Prints a Specified LCR
Create a procedure that prints a specified LCR. - Step 4: Create a Procedure that Prints All the LCRs in the Error Queue
Create a procedure that prints all of the LCRs in all of the error queues. - Step 5: Create a Procedure that Prints All the Error LCRs for a Transaction
Create a procedure that prints all the LCRs in the error queue for a particular transaction.
See Also:
Parent topic: Monitoring XStream In
11.10.1 Step 1: Grant Explicit SELECT Privilege on the ALL_APPLY_ERROR View
Running the GRANT_ADMIN_PRIVILEGE
procedure in the DBMS_XSTREAM_AUTH
package on a user grants the SELECT
privilege on the ALL_APPLY_ERROR
view to the user.
The user who creates and runs the print_errors
and print_transaction
procedures described in the following sections must be granted explicit SELECT
privilege on the ALL_APPLY_ERROR
data dictionary view. This privilege cannot be granted through a role.
To grant explicit SELECT privilege on the ALL_APPLY_ERROR
view:
-
In SQL*Plus, connect as an administrative user who can grant privileges.
See Oracle Database Administrator’s Guide for information about connecting to a database in SQL*Plus.
-
Grant
SELECT
privilege on theALL_APPLY_ERROR
data dictionary view to the appropriate user. For example, to grant this privilege to thexstrmadmin
user, run the following statement:GRANT SELECT ON ALL_APPLY_ERROR TO xstrmadmin;
-
Grant
EXECUTE
privilege on theDBMS_APPLY_ADM
package. For example, to grant this privilege to thexstrmadmin
user, run the following statement:GRANT EXECUTE ON DBMS_APPLY_ADM TO xstrmadmin;
-
Connect to the database as the user to whom you granted the privilege in Step 2 and 3.
Parent topic: Displaying Detailed Information About Apply Errors
11.10.2 Step 2: Create a Procedure that Prints the Value in an ANYDATA Object
Create a procedure that prints the value in a specified ANYDATA
object for some selected data types. Optionally, you can add more data types to this procedure.
CREATE OR REPLACE PROCEDURE print_any(data IN ANYDATA) IS tn VARCHAR2(61); str VARCHAR2(4000); chr VARCHAR2(1000); num NUMBER; dat DATE; rw RAW(4000); res NUMBER; BEGIN IF data IS NULL THEN DBMS_OUTPUT.PUT_LINE('NULL value'); RETURN; END IF; tn := data.GETTYPENAME(); IF tn = 'SYS.VARCHAR2' THEN res := data.GETVARCHAR2(str); DBMS_OUTPUT.PUT_LINE(SUBSTR(str,0,253)); ELSIF tn = 'SYS.CHAR' then res := data.GETCHAR(chr); DBMS_OUTPUT.PUT_LINE(SUBSTR(chr,0,253)); ELSIF tn = 'SYS.VARCHAR' THEN res := data.GETVARCHAR(chr); DBMS_OUTPUT.PUT_LINE(chr); ELSIF tn = 'SYS.NUMBER' THEN res := data.GETNUMBER(num); DBMS_OUTPUT.PUT_LINE(num); ELSIF tn = 'SYS.DATE' THEN res := data.GETDATE(dat); DBMS_OUTPUT.PUT_LINE(dat); ELSIF tn= 'SYS.TIMESTAMP' THEN res := data.GETTIMESTAMP(dat); DBMS_OUTPUT.PUT_LINE(TO_CHAR(dat,'DD-MON-RR HH.MI.SSXFF AM')); ELSIF tn= 'SYS.TIMESTAMPTZ' THEN res := data.GETTIMESTAMPTZ(dat); DBMS_OUTPUT.PUT_LINE(TO_CHAR(dat,'DD-MON-RR HH.MI.SSXFF AM')); ELSIF tn= 'SYS.TIMESTAMPLTZ' THEN res := data.GETTIMESTAMPLTZ(dat); DBMS_OUTPUT.PUT_LINE(TO_CHAR(dat,'DD-MON-RR HH.MI.SSXFF AM')); ELSIF tn = 'SYS.RAW' THEN -- res := data.GETRAW(rw); -- DBMS_OUTPUT.PUT_LINE(SUBSTR(DBMS_LOB.SUBSTR(rw),0,253)); DBMS_OUTPUT.PUT_LINE('BLOB Value'); ELSIF tn = 'SYS.BLOB' THEN DBMS_OUTPUT.PUT_LINE('BLOB Found'); ELSE DBMS_OUTPUT.PUT_LINE('typename is ' || tn); END IF; END print_any; /
Parent topic: Displaying Detailed Information About Apply Errors
11.10.3 Step 3: Create a Procedure that Prints a Specified LCR
Create a procedure that prints a specified LCR.
The procedure calls the print_any
procedure created in "Step 2: Create a Procedure that Prints the Value in an ANYDATA Object".
CREATE OR REPLACE PROCEDURE print_lcr(lcr IN ANYDATA) IS typenm VARCHAR2(61); ddllcr SYS.LCR$_DDL_RECORD; proclcr SYS.LCR$_PROCEDURE_RECORD; rowlcr SYS.LCR$_ROW_RECORD; res NUMBER; newlist SYS.LCR$_ROW_LIST; oldlist SYS.LCR$_ROW_LIST; ddl_text CLOB; ext_attr ANYDATA; BEGIN typenm := lcr.GETTYPENAME(); DBMS_OUTPUT.PUT_LINE('type name: ' || typenm); IF (typenm = 'SYS.LCR$_DDL_RECORD') THEN res := lcr.GETOBJECT(ddllcr); DBMS_OUTPUT.PUT_LINE('source database: ' || ddllcr.GET_SOURCE_DATABASE_NAME); DBMS_OUTPUT.PUT_LINE('owner: ' || ddllcr.GET_OBJECT_OWNER); DBMS_OUTPUT.PUT_LINE('object: ' || ddllcr.GET_OBJECT_NAME); DBMS_OUTPUT.PUT_LINE('is tag null: ' || ddllcr.IS_NULL_TAG); DBMS_LOB.CREATETEMPORARY(ddl_text, TRUE); ddllcr.GET_DDL_TEXT(ddl_text); DBMS_OUTPUT.PUT_LINE('ddl: ' || ddl_text); -- Print extra attributes in DDL LCR ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('serial#'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('serial#: ' || ext_attr.ACCESSNUMBER()); END IF; ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('session#'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('session#: ' || ext_attr.ACCESSNUMBER()); END IF; ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('thread#'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('thread#: ' || ext_attr.ACCESSNUMBER()); END IF; ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('tx_name'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('transaction name: ' || ext_attr.ACCESSVARCHAR2()); END IF; ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('username'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('username: ' || ext_attr.ACCESSVARCHAR2()); END IF; DBMS_LOB.FREETEMPORARY(ddl_text); ELSIF (typenm = 'SYS.LCR$_ROW_RECORD') THEN res := lcr.GETOBJECT(rowlcr); DBMS_OUTPUT.PUT_LINE('source database: ' || rowlcr.GET_SOURCE_DATABASE_NAME); DBMS_OUTPUT.PUT_LINE('owner: ' || rowlcr.GET_OBJECT_OWNER); DBMS_OUTPUT.PUT_LINE('object: ' || rowlcr.GET_OBJECT_NAME); DBMS_OUTPUT.PUT_LINE('is tag null: ' || rowlcr.IS_NULL_TAG); DBMS_OUTPUT.PUT_LINE('command_type: ' || rowlcr.GET_COMMAND_TYPE); oldlist := rowlcr.GET_VALUES('old'); FOR i IN 1..oldlist.COUNT LOOP IF oldlist(i) IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('old(' || i || '): ' || oldlist(i).column_name); print_any(oldlist(i).data); END IF; END LOOP; newlist := rowlcr.GET_VALUES('new', 'n'); FOR i in 1..newlist.count LOOP IF newlist(i) IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('new(' || i || '): ' || newlist(i).column_name); print_any(newlist(i).data); END IF; END LOOP; -- Print extra attributes in row LCR ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('row_id'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('row_id: ' || ext_attr.ACCESSUROWID()); END IF; ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('serial#'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('serial#: ' || ext_attr.ACCESSNUMBER()); END IF; ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('session#'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('session#: ' || ext_attr.ACCESSNUMBER()); END IF; ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('thread#'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('thread#: ' || ext_attr.ACCESSNUMBER()); END IF; ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('tx_name'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('transaction name: ' || ext_attr.ACCESSVARCHAR2()); END IF; ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('username'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('username: ' || ext_attr.ACCESSVARCHAR2()); END IF; ELSE DBMS_OUTPUT.PUT_LINE('Non-LCR Message with type ' || typenm); END IF; END print_lcr; /
Parent topic: Displaying Detailed Information About Apply Errors
11.10.4 Step 4: Create a Procedure that Prints All the LCRs in the Error Queue
Create a procedure that prints all of the LCRs in all of the error queues.
The procedure calls the print_lcr
procedure created in "Step 3: Create a Procedure that Prints a Specified LCR".
CREATE OR REPLACE PROCEDURE print_errors IS CURSOR c IS SELECT LOCAL_TRANSACTION_ID, SOURCE_DATABASE, MESSAGE_NUMBER, MESSAGE_COUNT, ERROR_NUMBER, ERROR_MESSAGE FROM ALL_APPLY_ERROR ORDER BY SOURCE_DATABASE, SOURCE_COMMIT_SCN; i NUMBER; txnid VARCHAR2(30); source VARCHAR2(128); msgno NUMBER; msgcnt NUMBER; errnum NUMBER := 0; errno NUMBER; errmsg VARCHAR2(2000); lcr ANYDATA; r NUMBER; BEGIN FOR r IN c LOOP errnum := errnum + 1; msgcnt := r.MESSAGE_COUNT; txnid := r.LOCAL_TRANSACTION_ID; source := r.SOURCE_DATABASE; msgno := r.MESSAGE_NUMBER; errno := r.ERROR_NUMBER; errmsg := r.ERROR_MESSAGE; DBMS_OUTPUT.PUT_LINE('*************************************************'); DBMS_OUTPUT.PUT_LINE('----- ERROR #' || errnum); DBMS_OUTPUT.PUT_LINE('----- Local Transaction ID: ' || txnid); DBMS_OUTPUT.PUT_LINE('----- Source Database: ' || source); DBMS_OUTPUT.PUT_LINE('----Error in Message: '|| msgno); DBMS_OUTPUT.PUT_LINE('----Error Number: '||errno); DBMS_OUTPUT.PUT_LINE('----Message Text: '||errmsg); FOR i IN 1..msgcnt LOOP DBMS_OUTPUT.PUT_LINE('--message: ' || i); lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid); print_lcr(lcr); END LOOP; END LOOP; END print_errors; /
To run this procedure after you create it, enter the following:
SET SERVEROUTPUT ON SIZE 1000000 EXEC print_errors
Parent topic: Displaying Detailed Information About Apply Errors
11.10.5 Step 5: Create a Procedure that Prints All the Error LCRs for a Transaction
Create a procedure that prints all the LCRs in the error queue for a particular transaction.
The procedure calls the print_lcr
procedure created in "Step 3: Create a Procedure that Prints a Specified LCR".
CREATE OR REPLACE PROCEDURE print_transaction(ltxnid IN VARCHAR2) IS i NUMBER; txnid VARCHAR2(30); source VARCHAR2(128); msgno NUMBER; msgcnt NUMBER; errno NUMBER; errmsg VARCHAR2(2000); lcr ANYDATA; BEGIN SELECT LOCAL_TRANSACTION_ID, SOURCE_DATABASE, MESSAGE_NUMBER, MESSAGE_COUNT, ERROR_NUMBER, ERROR_MESSAGE INTO txnid, source, msgno, msgcnt, errno, errmsg FROM ALL_APPLY_ERROR WHERE LOCAL_TRANSACTION_ID = ltxnid; DBMS_OUTPUT.PUT_LINE('----- Local Transaction ID: ' || txnid); DBMS_OUTPUT.PUT_LINE('----- Source Database: ' || source); DBMS_OUTPUT.PUT_LINE('----Error in Message: '|| msgno); DBMS_OUTPUT.PUT_LINE('----Error Number: '||errno); DBMS_OUTPUT.PUT_LINE('----Message Text: '||errmsg); FOR i IN 1..msgcnt LOOP DBMS_OUTPUT.PUT_LINE('--message: ' || i); lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid); -- gets the LCR print_lcr(lcr); END LOOP; END print_transaction; /
To run this procedure after you create it, pass to it the local transaction identifier of an error transaction. For example, if the local transaction identifier is 1.17.2485
, then enter the following:
SET SERVEROUTPUT ON SIZE 1000000 EXEC print_transaction('1.17.2485')
Parent topic: Displaying Detailed Information About Apply Errors