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>
