Home » Server Options » Replication » streams and applied_scn (11.1.0.6.0)
streams and applied_scn [message #542981] Sat, 11 February 2012 13:35 Go to next message
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 Go to previous messageGo to next message
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

Re: streams and applied_scn [message #542983 is a reply to message #542982] Sat, 11 February 2012 15:43 Go to previous message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
thats me again.

i found great example how I expected it should work: http://wedostreams.blogspot.com/2009/02/how-does-streams-capture-follow-up.html

but in my case dba_capture.applied_scn don't change even after 1 hour - unlike from above link.
so i have no idea why in my case LCR's are propagated and applyed corectly but dba_capture.applied_scn doesn't change.

applied_scn changes in my environment only when i restart db or restart captur process.

could some explain me this issue?
thanks

[Updated on: Sat, 11 February 2012 15:43]

Report message to a moderator

Previous Topic: Golden gate for production cutover
Next Topic: streams and required_checkpoint_scn
Goto Forum:
  


Current Time: Thu Mar 28 13:34:57 CDT 2024