Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

SCN values for Streams

Oracle Tips by Burleson 

The System Change Number (SCN) is the main controlling function that is used to keep track of database transactional activity. SCN is a stamp that defines a committed version of a database at a particular point in time. Oracle assigns every committed transaction a unique SCN. To support the multi-version read-consistency capabilities, Oracle keeps records of all database changes with the help of SCN numbers. SCN is a running number for the database changes.

SCN is a vital tool utilized by the Streams Methodology. The processes of reading, propagating and applying database changes make use of the SCN numbers. SCN values provide a time-based tracking number that can be used to coordinate the various Streams processes. Based on SCN numbers, various Streams processes are able to restart from where they left off when a particular process is interrupted and has to be restarted.

Oracle provides a DBMS procedure that is used to query the SCN that was in effect at any point. As shown in the following SQL statement, the get_system_change_number procedure shows the current SCN value of the database.

Select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual
/

During the database checkpoint, Oracle records the SCN number into the control files and also all data files. The same SCN number is recorded in all the files.

Most of the Streams-related dynamic performance views show the SCN number.  This helps the DBA understand the progress of the Streams process. This also helps in troubleshooting and monitoring. For example, the dba_capture view records the following SCN values which helps track the Capture process progress.

  • captured_scn: The captured_scn is the one that corresponds to the most recent change scanned in the redo log by a Capture process.  This value keeps incrementing to a higher number as the database transactional activity goes on. As long as the Capture process is running, this value should change gradually.
     

  • applied_scn: The applied_scn for a Capture process is the SCN of the most recent event dequeued by the relevant Apply processes. It indicates that all events with SCNs lower than this one have been dequeued by all Apply processes that apply changes captured by the Capture process. The applied_scn for a Capture process is equivalent to the low-watermark SCN for such an Apply process.
     

  • first_scn: The first_scn is the lowest SCN in the redo log from which a Capture process can capture changes. If a first_scn is specified during creation of the Capture process, the database must be able to access redo log information from the specified SCN and those with higher values. 
     

  • start_scn: The start_scn is the SCN from which a Capture process begins to capture changes.
     

  • required_checkpoint_scn: This value indicates the lowest checkpoint SCN for which the Capture process requires redo information. At very regular intervals, the Capture process records a checkpoint where it records its current state persistently in the data dictionary of the database running the Capture process. The redo log file that contains the required checkpoint SCN, and all subsequent redo log files must be available to the Capture process. If a Capture process is stopped and restarted, it starts scanning the redo log from the SCN that corresponds to its required_checkpoint_scn.

  • max_checkpoint_scn: This is the SCN value at which the last checkpoint was taken by the Capture process. The Capture process has its own check point schedule.

At the same time, the v$streams_capture view provides additional information about the Capture process SCN values. As a supplement to the above shown information, the v$log and v$archived_log will show the SCN numbers.

A glance at various SCN values helps the DBA monitor and keep tight control over the Streams performance issues, if any. Analysis of the time lags helps in monitoring latency levels between the creation of a transaction, flushing to the redo log file, enqueue of the event and the dequeue process.

In summary, from the dba_capture view, the following columns can be seen:

START_SCN
CAPTURED_SCN
APPLIED_SCN
FIRST_SCN 
MAX_CHECKPOINT_SCN
REQUIRED_CHECKPOINT_SCN

From the v$streams_capture view, the following columns can be seen:

ENQUEUE_MESSAGE_NUMBER  (Last Enqueued Msg#)
AVAILABLE_MESSAGE_NUMBER (Last redo SCN flushed to log)
CAPTURE_MESSAGE_NUMBER (Most recently captured message)
TOTAL_MSG_CAPT (From last start of Capture process)
TOTAL_MESSAGES_CAPTURED
ENQUEUE_MESSAGE_NUMBER ('Last EnqueSCN )

A script called show_capture_details.sql, which can be obtained from the code depot, has been developed based on some selected columns of the following dictionary views: 

SELECT [columns] from dba_capture
SELECT [columns] from v$streams_capture
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from DUAL
SELECT CHECKPOINT_CHANGE# from v$database
SELECT [columns] from v$log where status in ('ACTIVE', 'CURRENT') 

The typical output from the script shows all the relevant SCNs in one place:

DNYTST10 SQL>@show_capture_details

CAPTURE_PROFILE
-----------------------------------------------------------------
Capture_name (dba_capture)  ---> : NY3_CAPTURE
Queue Name                       : NY3_QUEUE
RULE_SET_NAME                    : RULESET$_156
Capture_User and Type            : STRMADM Type : LOCAL
START_SCN                        : 13089644
Captured_SCN                     : 13089673
Applied_SCN                      : 13089644
FIRST_SCN                        : 13089644
MAX_CHECKPOINT_SCN               : 13091418
Reqd CKPT SCN                    : 13089644
Total Delta SCN Captured         : 20814
Delta SCN Applied   (Delta)      : 20843
Error Number                     :
Error Message ..                 :

Now Time                -------> : 16:20:24 10/15/2004
Current SCN (from DBMS_FLASHBACK): 13110487
CKPT SCN (from v$database)       : 13105814
Current Redo Log Begin SCN       : 13107332
Redo Group Number and Status     : 1   CURRENT
Last Capt.SCN(v$streams_capture) : 13093098  At 16:19:06 10/15/2004
Last EnqueSCN (v$streams_capture): 13093098  At 16:19:06 10/15/2004

 

Now Time                -------> : 16:20:24 10/15/2004
Current SCN (from DBMS_FLASHBACK): 13110488
CKPT SCN (from v$database)       : 13105814
Current Redo Log Begin SCN       : 13105814
Redo Group Number and Status     : 3   ACTIVE
Last Capt.SCN (v$streams_capture): 13093098 At 16:19:06 10/15/2004
Last EnqueSCN (v$streams_capture): 13093098 At 16:19:06 10/15/2004

Process SID (v$streams_capture)  : 1
CAPTURE_NAME  ..                 : NY3_CAPTURE
State                            : PAUSED FOR FLOW CONTROL
TOTAL_MESSAGES_CREATED           : 17622
TOTAL_FULL_EVALUATIONS           : 5138
Tot Msg Enqueued from start      : 5157
Last Enqueued Messg #            : 13093098
Last redo SCN flushed to the Log : 13110487
Most recently captured message   : 13093098
Time when most recent msg captd  : 16:20:18 10/15/2004
TOTAL_MSG_CAPT(from last start)  : 7328
DNYTST10 SQL>

 

 


The above text is an excerpt from:

Oracle Streams
High Speed Replication and Data Sharing

ISBN 0-9745993-5-2

by Madhu Tumma
 


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks