* Check the apply process latency:
SELECT (hwm_time-hwm_message_create_time)* 86400 "Latency in Seconds",
hwm_message_create_time "Event Creation",
hwm_time "Apply Time",
hwm_message_number "Applied Message #"
FROM v$STREAMS_APPLY_COORDINATOR;

Latency in Seconds Event Cre Apply Tim Applied Message #
------------------ --------- --------- -----------------
0 10-JAN-04 10-JAN-04 0

* Check the Error Queue:
SELECT apply_name, source_database,local_transaction_id, error_message
FROM DBA_APPLY_ERROR;

no rows selected

* Check for any defined conflict resolution:
COLUMN object_owner FORMAT a15
COLUMN object_name FORMAT a15
COLUMN method_name FORMAT a20
COLUMN column_name FORMAT a15
COLUMN resolution_column FORMAT a60
SELECT object_owner,object_name,method_name,resolution_column,column_name
FROM dba_apply_conflict_columns;

no rows selected

* Check object instantiated SCN:
SELECT * FROM dba_apply_instantiated_objects;

SMB0000.WORLD SMB SMB_TRANSAKSI14 TABLE 196832142 0

* Check the current SCN value for the database.
To get the current SCN, use the following:
SET SERVEROUTPUT ON
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE('Current SCN is ' || iscn);
END;
/

Current SCN is 196835162

* Check for trace files in the BACKGROUND_DUMP_DEST

Dump file c:\oracle\admin\smb\udump\smb_p000_1068.trc
Wed Jan 14 09:11:16 2004
ORACLE V9.2.0.4.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 3, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Windows 2000 Version 5.0 Service Pack 3, CPU type 586
Instance name: smb

Redo thread mounted by this instance: 1

Oracle process number: 22

Windows thread id: 1068, image: ORACLE.EXE (P000)


*** SESSION ID:(22.388) 2004-01-14 09:11:16.296
*** 2004-01-14 09:11:16.296
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=SMB0000.WORLD
knlldmm: objn=18388
knlldmm: objv=1
knlldmm: scn=196824351


---------------------------------------------------------------------
How to solve it? I've try to set the instantiation manually..but the apply process still freezing.