streams and applied_scn [message #542981] |
Sat, 11 February 2012 13:35 |
|
piotrtal
Messages: 168 Registered: June 2011 Location: Poland/Czestochowa
|
Senior Member |
|
|
hi,
I have configured unidirectional streams schema replication between ora19 and ora20.
The thing which is confusing for me is what applied_scn column from dba_apply table means, and aditionally what app_scn column from dba_captured table means.
Propably i am wrong but in corectly configured streams replication this two values should be equal.
But they dont.
After i make some changes on source database, the applied_scn from dba_capture table (source) is different than app_scn from dba_apply table (destination).
I thoutht that this two values should be the same.
But after dml on source , and apply this dml on destination only LAST_ENQUEUED_SCN from source equals APP_SCN on destination.
The question is (because i don't understand meaning of applied_scn from source):
- what this column means for source database?
SQL> @script
SQL> select count(*) from piotrtal1.tabela
2 union all
3 select count(*) from piotrtal1.tabela@ora20.world
4 /
COUNT(*)
----------
5065
5065
SQL>
SQL> select
2 cap.captured_scn, cap.applied_scn, cap.last_enqueued_scn, cap.status, cap.capture_name, cap.checkpoint_retention_time, db.CURRENT_SCN
3 from dba_capture cap
4 cross join v$database db
5 /
CAPTURED_SCN APPLIED_SCN LAST_ENQUEUED_SCN STATUS CAPTURE_NAME CHECKPOINT_RETENTION_TIME CURRENT_SCN
------------ ----------- ----------------- -------- ------------------------------ ------------------------- -----------
24706643 24706272 24711771 ENABLED ORA19_CAP .006944444 24711880
SQL>
SQL> begin
2 for i in 1..100 loop
3 insert into piotrtal1.tabela ( select max(col1)+1 from piotrtal1.tabela);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> pause
SQL>
SQL> select
2 cap.captured_scn, cap.applied_scn, cap.last_enqueued_scn, cap.status, cap.capture_name, cap.checkpoint_retention_time, db.CURRENT_SCN
3 from dba_capture cap
4 cross join v$database db
5 /
CAPTURED_SCN APPLIED_SCN LAST_ENQUEUED_SCN STATUS CAPTURE_NAME CHECKPOINT_RETENTION_TIME CURRENT_SCN
------------ ----------- ----------------- -------- ------------------------------ ------------------------- -----------
24706643 24706272 24711883 ENABLED ORA19_CAP .006944444 24711898
SQL>
SQL> pause
SQL>
SQL> select count(*) from piotrtal1.tabela
2 union all
3 select count(*) from piotrtal1.tabela@ora20.world
4 /
COUNT(*)
----------
5165
5165
SQL>
SQL>
SQL> select sid,
2 serial#,
3 server_id snbr,
4 state,
5 total_assigned txn_assigned,
6 total_messages_applied msgs_applied,
7 applied_message_number app_scn
8 from v$streams_apply_server@ora20.world
9 where apply_name = 'ORA19_APP'
10 /
SID SERIAL# SNBR STATE TXN_ASSIGNED MSGS_APPLIED APP_SCN
---------- ---------- ---------- -------------------- ------------ ------------ ----------
132 3 1 IDLE 22 2222 24711883
SQL>
SQL>
SQL>
[Updated on: Sat, 11 February 2012 14:33] Report message to a moderator
|
|
|
Re: streams and applied_scn [message #542982 is a reply to message #542981] |
Sat, 11 February 2012 14:55 |
|
piotrtal
Messages: 168 Registered: June 2011 Location: Poland/Czestochowa
|
Senior Member |
|
|
from oracle tutorial
--------------------------
The captured SCN is the SCN that corresponds to the most recent change scanned in
the redo log by a capture process. The applied SCN for a capture process is the SCN of
the most recent message dequeued by the relevant apply processes.
--------------
co why this two values are different in my case after LCR dequeue was successfull? - see log from above
[Updated on: Sat, 11 February 2012 14:58] Report message to a moderator
|
|
|
|