... and the queue start to empty.
2 Check the propagation that sends events to
the apply site.
We need to assess if the capture
process is running and if it is propagating. for that, we will use at
source DB
prop -l : Verify the
destination queue is correct
[POLDEV]:> prop -l
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 03rd January 2006 16:52:26
Username - SYSTEM
List propagation process
Propagation name Source queue Remote queue Local or remot Rule set name
---------------------- ------------------------------- ------------------------------- -------------- --------------------
PROPAG_TO_DESTINATION STRMADMIN.POLDEV_QUEUE STRMADMIN.BDCUSTP_QUEUE RMANTST
cap-l : Verify the status of the
capture process:
[POLDEV]:> cap -l
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 03rd January 2006 16:55:40
Username - SYSTEM
List capture process
Capture name Status
------------------ --------
CAPTURE_CUSTOMER ENABLED
Next Last Last
captured captured applied
Capture name Queue Owner and Name Rule set name Scn Scn Scn
------------------ ------------------------------ --------------------------- ------------- ------------- -------------
CAPTURE_CUSTOMER STRMADMIN.POLDEV_QUEUE STRMADMIN.CPT_RS_POLDEV 187475794067 187485167440 187485167440
aq -s : Check the propagation
schedule time and check for failures and total number of events
propagted
[POLDEV]:> aq -s
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 03rd January 2006 16:56:52
Username - SYSTEM
Queue Owner and Name Disable FAILURES
------------------------------------ -------- ----------
STRMADMIN.POLDEV_QUEUE N 0
Number of
Messages Avg Avg time
Queue Owner and Name Destination Queue Start Last run propagated TOTAL_BYTES Size propagate
------------------------------------ ------------ ------------ -------------- ----------- ----------- -------- ----------
STRMADMIN.POLDEV_QUEUE RMANTST 23-12 10:58 03-01 16:32:49 24835 11443478 460.8 0.0020
3 Transactions are reaching destination
site.
Transaction are not
being dequeued : check the reader figures and status
Run many time app -r and you
will see if Last dequeue Time and Processed messages is advancing.
However this does not tell you if the dequeued messages are applied
successfuly. for that monitor the the servers (app -s), check the errors queue
table (app -err and app -erc to obtain a count on
err type)
[RMANTST]:> app -r
MACHINE befsam18 - ORACLE_SID : RMANTST Page: 1
Date - Tuesday 03rd January 2006 17:18:33
Username - SYS
Show reader process
Reader Total Messages Last process Time(s)
APPLY_NAME Status Sid Dequeued Dequeue Time messages Dequeuing SGA_USED
------------------------------ ---------- ------ -------------- --------------- ---------- ---------- ----------
STRM_APL_TO_USERB DEQUEUE ME 26 822789 03-01 18:55:45 75370 99 12788
SSAGES
Check the
apply process latency:
[RMANTST]:> app -lat
MACHINE befsam18 - ORACLE_SID : RMANTST Page: 1
Date - Tuesday 03rd January 2006 17:28:12
Username - SYS
Show elapse time between creation and apply
Latency
in Last Dequeued
APPLY_NAME Seconds Event Creation Dequeue Time Message Number
------------------------------ ------- ----------------- --------------- --------------
STRM_APL_TO_USERB 233 03-01 17:09:34 03-01 17:13:27 187485176761
4. Transactions
are reaching destination site but not applied.
Check the
apply server process:
[RMANTST]:> app -s
MACHINE befsam18 - ORACLE_SID : RMANTST Page: 1
Date - Tuesday 03rd January 2006 17:23:35
Username - SYS
Show aplly server processes
Message Total Last Last creation Total Total
APPLY_NAME SERVER_ID Status Sequence Applied apply Time at source DB Admin Assigned
------------------------------ ---------- ---------- -------- -------- --------------- --------------- -------- --------
STRM_APL_TO_USERB 1 IDLE 2 2981 03-01 17:13:27 03-01 17:09:34 37 1987
5. Unset the
starting scn for an apply process
In
order to reset the scn for an apply process you need to set this
scn to a null value. We use for that the commed 'app -fi'. In this
example we made a typo during the setting. We meant POLDEV and typed
POL_DEV. We need to remove this instantiation rule:
MACHINE befsam18 - ORACLE_SID : RMANTST Page: 1
Date - Wednesday 04th January 2006 09:36:21
Username - SYS
Show instantiated objects
Object Using
Source Database Object Name Type Instantiation SCN Dblink
---------------------- --------------------------------------------- --------- ----------------- ------------------
POLDEV USR_A.ORDER_INFO_LINES TABLE 187485160765
POLDEV USR_A.CUTSOMER_LINE_ID TABLE 187485176658
POL_DEV USR_A.CUTSOMER_LINE_ID TABLE 187485160708
We omits to add '-scn <nnn>' so it default its scn to NULL with the effect of removing the prepare instantiation setting:
[RMANTST]:> app -fi
-so USR_A -t CUTSOMER_LINE_ID -src_sid POLDEV -x
Statement generated and executed:
execute
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name=> 'USR_A.CUTSOMER_LINE_ID', source_database_name
=> 'POLDEV' , instantiation_scn => NULL );
[RMANTST]:> app -i
Now
we check
MACHINE befsam18 - ORACLE_SID : RMANTST Page: 1
Date - Wednesday 04th January 2006 09:37:31
Username - SYS
Show instantiated objects
Object Using
Source Database Object Name Type Instantiation SCN Dblink
---------------------- --------------------------------------------- --------- ----------------- ------------------
POLDEV USR_A.ORDER_INFO_LINES TABLE 187485160765
POLDEV USR_A.CUTSOMER_LINE_ID TABLE 187485176658
6 Set the degree of
parallelism of prepare servers for a capture process
The rule of thunb is to set the degree of parallelism for prepare
server for a capture
process to 3*CPU as long as your total amount of processes is within
the margin of the parallel_max_server. How ever you are still
limited to one Logminer session per capture process. If you reach
a limit in your speed in capture and need more power, you will need to
create additional capture process and split your tables among theses
capture process.
For 4 CPU you can have up to 12 worker for a capture
[RMANTST]:> cap -par 12
-cn
MY_CAPTURE_PROCESS -x
Statement
generated and executed:
execute
dbms_capture_adm.set_parameter(capture_name=>
'MY_CAPTURE_PROCESS' , parameter=> 'parallelism', value => 3);
7 set the degree of
parallelism for an apply process
You have one reader process that dequeues (app -r) however you may
defines many apply server process to insert rows into yours
tables. The number of the apply server must remains withing the
boundaries of the max_parallel_server. you will define a maximum of 3
apply server pere CPU.
[RMANTST]:> app -par 12
-an
STRM_APL_TO_USERB
Statement generated and executed:
execute
dbms_apply_adm.set_parameter(apply_name=> 'STRM_APL_TO_USERB' ,
parameter=> 'parallelism', value => 12 );
Check :
[RMANTST]:> app -s
MACHINE befsam18 - ORACLE_SID : RMANTST Page: 1
Date - Friday 06th January 2006 13:56:44
Username - SYS
Show aplly server processes (app -h for help)
Message Total Last Last creation Total Total
APPLY_NAME SERVER_ID Status Sequence Applied apply Time at source DB Admin Assigned
------------------------------ ---------- ---------- -------- -------- --------------- --------------- -------- --------
STRM_APL_TO_USERB 1 IDLE 101 293916 06-01 10:19:31 06-01 10:15:24 2063 43627
STRM_APL_TO_USERB 2 IDLE 101 78039 06-01 10:19:04 06-01 10:15:21 466 23582
STRM_APL_TO_USERB 3 IDLE 100 22641 06-01 10:11:13 06-01 10:07:17 35 2393
STRM_APL_TO_USERB 4 IDLE 2 1092 05-01 18:22:11 05-01 17:14:06 1 546
STRM_APL_TO_USERB 5 IDLE 2 398 05-01 18:22:11 05-01 17:14:06 0 199
STRM_APL_TO_USERB 6 IDLE 2 162 05-01 18:22:11 05-01 17:14:06 1 81
STRM_APL_TO_USERB 8 IDLE 2 52 05-01 18:21:48 05-01 17:14:04 0 26
STRM_APL_TO_USERB 7 IDLE 2 86 05-01 18:22:11 05-01 17:14:06 0 43
STRM_APL_TO_USERB 10 IDLE 2 30 05-01 18:21:48 05-01 17:14:04 0 15
STRM_APL_TO_USERB 9 IDLE 2 42 05-01 18:21:48 05-01 17:14:04 0 21
STRM_APL_TO_USERB 12 IDLE 2 10 05-01 18:16:28 05-01 17:13:43 0 5
STRM_APL_TO_USERB 11 IDLE 2 12 05-01 18:16:28 05-01 17:13:43 0 6
8 set the
capture checkpoint frequency
The
parameter _CHECKPOINT_FREQUENCY
Increases
the frequency of logminer checkpoints especially in a database with
significant LOB or DDL activity.
A logminer checkpoint
is requested by default every 10Mb of redo mined. This
parameter is set through functions. It is not set with an init.ora or
alter session parameter.
It is implemented in smenu within 'cap'.
[POLDEV]:> cap | grep frequency
-chk : Set Check point frequency for capture process
To set checkpoint frequency : cap -chk <nn> -cn <CAPTURE_NAME>
[RMANTST]:> cap -chk 100
-cn
my_capture
Statement generated and executed:
execute
dbms_capture_adm.set_parameter(capture_name=> 'MY_CAPTURE' ,
parameter=> '_CHECKPOINT_FREQUENCY', value => 100);
9 List minimum archive
needed for capture proces to restart
[POLDEV]:> cap -la
Statement generated and executed:
set serveroutput on
DECLARE
hScn number := 0;
lScn number := 0;
sScn number;
ascn number;
alog varchar2(1000);
begin
select min(start_scn), min(applied_scn) into sScn, ascn
from dba_capture ;
DBMS_OUTPUT.ENABLE(2000);
for cr in (select distinct(a.ckpt_scn)
from system.logmnr_restart_ckpt$ a
where a.ckpt_scn <= ascn and a.valid = 1
and exists (select * from system.logmnr_log$ l
where a.ckpt_scn between l.first_change# and
l.next_change#)
order by a.ckpt_scn desc)
loop
if (hScn = 0) then
hScn := cr.ckpt_scn;
else
lScn := cr.ckpt_scn;
exit;
end if;
end loop;
if lScn = 0 then
lScn := sScn;
end if;
select min(name) into alog from v$archived_log where lScn between first_change# and next_change#;
dbms_output.put_line('Capture will restart from SCN ' || lScn ||' in log '||alog);
end;
/
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 10th January 2006 10:43:33
Username - SYSTEM
List minimum requiered archived log (cap -h for help)
Capture will restart from SCN 188072252959 in log /archive/POLDEV/arch_1_632.arc
PL/SQL procedure successfully completed.
10 Show capture time and
enqueue time of these captured rows
This statement is usefull to
determine the gap between the capture and its enqueue time. If there is
too much time, consider setting parallelism on the capture queue or
create one capture process per heavy load table. To achieve this, you
will need to increase logminer_perssitant_session, increase
max_parallel_server and create one dedicated queue, create one
dedicated ruleset, create the capture rule and add the rule to the
dedicated ruleset and use these dedicated ruleset and queue to create
the dedicated capture process. Oracle assign one logminer session per
capture name. On oracle 9ir2 you will need to create a multiple
flowcontrol queue (this is built in on 10g)
[POLDEV]:> cap -s
MACHINE befsam18 -
ORACLE_SID : POLDEV
Page: 1
Date
- Thursday 12th January 2006 13:14:35
Username -
SYSTEM
List execute capture server process stats (cap -h for help)
Elapsed Elapsed Elapsed Captured Enqueued Total Total
capture Enqueue LCR Message Message Message Message
Capture name Time Time Time Number Number Captured Enqueued
---------------------- -------- -------- -------- ------------- ------------- ----------- -----------
CAPTURE_CUSTOMER_MID 855789 0 25 188211427233 207015 0
CAPTURE_CUSTOMER_SORT 839554 0 12 188211427233 207015 0
CAPTURE_CUSTOMER_POD 859039 841 492 188211427233 188188302167 306678 3049
Last enqueued
Last Capture Last message Message creation
Capture name STARTUP Capture time Create time Enqueue time Time
---------------------- -------------- ------------------ ------------------ ------------------ ------------------
CAPTURE_CUSTOMER_MID 01/12 10:51:56 01/12 13:14:34 01/12 13:14:34
CAPTURE_CUSTOMER_SORT 01/12 10:54:39 01/12 13:14:35 01/12 13:14:34
CAPTURE_CUSTOMER_POD 01/12 10:51:11 01/12 13:14:35 01/12 13:14:34 01/12 10:51:40 01/12 10:38:34
11 List
propagation statistics
If a propagation does not
work well or does not work at all, you will see an inflated figures in
the execution due to the retry feature.
[POLDEV]> prop -s
MACHINE befsam18 -
ORACLE_SID : POLDEV
Page: 1
Date - Friday 13th January 2006 21:31:59
Username - SYSTEM List Stats for propagation process
Total Time Executing
PROPAGATION_NAME in Seconds Total Events Propagated Total Bytes Propagated
------------------------------ -------------------- ----------------------- ----------------------
PROP_POLDEV_TO_RMANTST_1 10247 1246534 543794912
PROP_POLDEV_TO_RMANTST_2 419 131678 74212402
PROP_POLDEV_TO_RMANTST_3 592 233603 131768035
PROP_POLDEV_TO_RMANTST_4 520 135394 89946272
12 Set trace level on
apply or caputre process
This procedure will put a trace
on on the apply process. The effect is a trace file in bdump. To set
the trace off set the trace level to 0
Apply: [RMANTST]:> app -trace 127 -an APPLY_TO_RMANTST
Statement
generated and executed:
execute
dbms_apply_adm.set_parameter(apply_name=> 'APPLY_TO_RMANTST' ,
parameter=> 'trace_level', value => 127);
Capture : [POLDEV]:>
cap -trace 127 -an CAPTURE_POLDEV
Statement
generated and executed:
execute
dbms_apply_adm.set_parameter(apply_name=> 'CAPTURE_POLDEV' ,
parameter=> 'trace_level', value => 127);
13. How to get the
SQL statement of a apply rejected objects.
Your apply process is not at
his best, you want to know what happening and which statement are
rejected. We start by looking in our exception queues:
ORIGIN DB : POLDEV
TARGET DB : RMANTST
[RMANTST]:>
aq -lq
MACHINE befsam18
- ORACLE_SID : RMANTST
Page: 1
Date
- Tuesday 17th January 2006 15:48:44
Username -
SYS
(aq -h for help)
.
Rows in rows in
Queues
name
AQ$<>_P Queue Table Queue Type
--------------------------------------- ---------- -----------
----------
Q_RECEIVER_CUSTP_POD
0
0 APPLY
Q_RECEIVER_CUSTP
357513
4001 APPLY
Q_RECEIVER_CUSTP_MID
0
0 APPLY
Q_RECEIVER_CUSTP_SORT
0
0 APPLY
PL/SQL procedure successfully completed.
We see plenty of rows still to comes in the queue
[POLDEV]:>
aq -l
MACHINE befsam18
- ORACLE_SID : RMANTST
Page: 1
Date
- Tuesday 17th January 2006 15:48:57
Username -
SYS
List Queues in system, type < aq -l -ex > for exception
queues (aq -h for help)
Queue
Message Count
Qid Queue Owner and
Name
Queue
Table
Type Enq Deq in Queue
------ -------------------------------------- ----------------------
--------- --- --- -------------
9214
STRMADMIN.AQ$_Q_RECEIVER_CUSTP_E
Q_RECEIVER_CUSTP EXCEPTION NO NO
9216 STRMADMIN.Q_RECEIVER_CUSTP
Q_RECEIVER_CUSTP NORMAL YES
YES 465408
9237 STRMADMIN.AQ$_Q_RECEIVER_CUSTP_POD_E
Q_RECEIVER_CUSTP_POD EXCEPTION NO NO
9239
STRMADMIN.Q_RECEIVER_CUSTP_POD
Q_RECEIVER_CUSTP_POD NORMAL YES
YES
0
9260 STRMADMIN.AQ$_Q_RECEIVER_CUSTP_MID_E
Q_RECEIVER_CUSTP_MID EXCEPTION NO NO
9262
STRMADMIN.Q_RECEIVER_CUSTP_MID
Q_RECEIVER_CUSTP_MID NORMAL YES
YES
0
9283 STRMADMIN.AQ$_Q_RECEIVER_CUSTP_SORT_E Q_RECEIVER_CUSTP_SORT
EXCEPTION NO NO
9285
STRMADMIN.Q_RECEIVER_CUSTP_SORT
Q_RECEIVER_CUSTP_SORT NORMAL YES
YES
0
So we deicde to read the execption queue whose reals name is
AQ$_Q_RECEIVER_CUSTP_P. It contains rows whose column name
user_data is of type AnyData. This is in fact an object. The command
'aq -read' will loop in this queue and take out the relevant
informations:
[POLDEV]:> aq -read
Q_RECEIVER_CUSTP
Statement
generated and executed:
set
serveroutput on size 999999
declare
l_varchar2 varchar2(4000);
l_rc
number;
lcr
SYS.LCR$_ROW_RECORD;
rc
PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(40);
object_scn number ;
dmlcommand VARCHAR2(10);
trn VARCHAR2(14);
begin
dbms_output.put_line('Owner.table
SCN Command Transaction
ID');
dbms_output.put_line('================================================================================');
for p_x in (select USER_DATA from
AQ$_Q_RECEIVER_CUSTP_P)
LOOP
rc := p_x.user_data.GETOBJECT(lcr);
object_owner :=
lcr.GET_OBJECT_OWNER();
object_name :=
lcr.GET_OBJECT_NAME();
object_scn :=
lcr.GET_SCN();
dmlcommand :=
lcr.GET_COMMAND_TYPE();
trn :=
lcr.GET_TRANSACTION_ID();
l_varchar2 := rpad(object_owner||'.'|| object_name,35,' ')
|| ' ' ||rpad(object_scn,12,' ') ||' '||rpad(dmlcommand,
12,' ') ||' ' ||rpad(trn,14,' ') ;
dbms_output.put_line(l_varchar2);
END LOOP;
end;
/
MACHINE
befsam18 - ORACLE_SID
: RMANTST
Page: 1
Date
- Tuesday 17th January 2006 15:51:26
Username -
STRMADMIN
(aq -h for help)
Owner.table
SCN Command Transaction
ID
================================================================================
CLIENT.CUSTOMER_OPERATIONS
188221335450 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335450 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335450 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335450 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335450 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335450 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335450 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335450 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335450 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335450 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335450 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335450 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335451 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335453 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335454 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335454 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335454 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335454 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335455 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335455 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335455 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335455 DELETE 19.37.67670
.
.
.
CLIENT.CUSTOMER_OPERATIONS
188221335658 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335658 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335658 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335658 DELETE 19.37.67670
CLIENT.CUSTOMER_OPERATIONS
188221335658 DELETE 19.37.67670
^CCLIENT.CUSTOMER_OPERATIONS
188221335661
DELETE 19.37.67670
declare
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 999999 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at line 26
If I take one of this DELETE SCN check
with logminer on the source database the archive log time and SQL:
[POLDEV]:> apl -s 188221335453
MACHINE befsam18 - ORACLE_SID : POLDEV
Page: 1
Date
- Tuesday 17th January 2006 15:50:43
Username -
SYS
Report
- Applied archive logs
Given an number if you want to see more
lines : apl 50 or apl -d <dest_id>
Display single archive info : apl -a <arch nbr>
or apl -s <SCN>
Show higher applied up to now : apl
-m Show SCN instead of
time apl -n
Standby Deleted
DEST_ID SEQUENCE#
First
time
Next time APP
Status Dest By Rman REGISTR
---------- ---------- --------------------- --------------------- ---
---------- ------- ------- -------
1 750 2006-01-16
07:23:10 2006-01-16 09:41:15
NO
A
NO NO ARCH
[POLDEV]:> lgm -s 188221335453
MACHINE befsam18
- ORACLE_SID :
MISPROD
Page: 1
Date
- Tuesday 17th January 2006 15:50:48
Username -
SYS
(help: lgm -h) Show SQL command for a given SCN
PL/SQL procedure successfully
completed.
PL/SQL procedure successfully
completed.
SQL
--------------------------------------------------------------------------------------------------------------------------set
transaction read write;
update "SYSTEM"."LOGMNR_RESTART_CKPT$" set "VALID" = '1' where "VALID"
= '0' and ROWID = 'AAAJPUAABAAALIZAAX';
delete from
"CLIENT"."CUSTOMER_OPERATIONS" where "WS_ID" = '99006' and
"WKS_DES_NUM" = '1011697' and "CSP_NUM" = '-1' and "STATUS" = '0' and
"DELIVERY_DT" = TO_DATE('14-DEC-05', 'DD-MON-RR') and "TIME_STAMP" =
TO_DATE('14-DEC-05', 'DD-MON-RR') and "CREATION_DATE" =
TO_DATE('14-DEC-05', 'DD-MON-RR') and ROWID = 'AAAH82AAIAAANbBAAB';
Email : Bpolarsk@yahoo.com
Last update : 16 January 2006
