One may want to know what SCN # from source is being processed by Post at this time. The reasons could be many. One may want to track replication activity by SCN at the source and want to know where Post is vis-Ã -vis that SCN, for instance. There could be many other reasons.
Want to know SCN # currently being processed by Shareplex for the purpose of tracking activity from source.
It is possible to quickly find out where Post is in terms of SCN while processing transactions. It needs Post to be stopped for a short duration. It involves running qview utility which should not be a problem if it is only run for reading a queue and not manipulating it. If in doubt the Support can be contacted. Avoid running qview session if currently processing a large transaction.
Here is a sample qview session that reads the first 2 messages that are waiting to be posted and determines their SCN.
sp_ctrl>stop post [queue <queuename>] (use queue option if using named queues so as not to stop other Post queues)
sp_ctrl>status (keep issuing status till it shows (keep issuing status till it shows Post is “stopped by user” and not “stopping”)
While in /proddir/bin, invoke qview as:
qview>open p r (when prompted for the queue name, select the appropriate queue, if using named queue)
Queue em-ora-06+P+o.SID1-o.SID2 (y/n) <n>?
Queue em-ora-06+P+o.SID1-o.SID3 (y/n) <n>? y
Current queue em-ora-06+P+o.SID1-o.SID3 user +PP+em-ora-06+sp_opst_mt+o.SID1-o.SID3
qview> oread 0 2 (here we are reading the first two messages in that named Post queue that are waiting to be applied)
Sque 2, qseq 3132, 02/09/08 05:03:55, 3367/21681680, Update, AAAieOAAyAAAKkvAAA "EMAG3"."EM_INFO_BUSQ_NEW", SCN 10671735190
Sque 2, qseq 4871, 02/09/08 05:03:55, 3367/21692024, COMMIT, AAAieOAAyAAAKkvAAA , SCN 10671735197
Total messages read: 2 (as you can see, the lines above prints out the SCN # involved in the queue messages, one of which is UPDATE and the other is a COMMIT)
Qview>seekback (this takes the pointer, which moved forward by two queue messages in the process of reading the queue, back to the start of the queue)
qview>exit (this takes us out of qview session, back to OS prompt)
One can then launch sp_ctrl and restart Post.
The following is a brief description of the term SCN:
The System Change Number (SCN) is a logical time stamp Oracle uses to order events within a single instance, and across all instances. Every time a user commits a transaction, Oracle records a new SCN. You can obtain SCNs in a number of ways, for example, from the alert log. You can then use the SCN as an identifier for purposes of recovery. For example, you can perform an incomplete recovery of a database up to SCN 1030. Oracle uses SCNs in control files, datafile headers, and redo records. Every redo log file has both a log sequence number, low and high SCN. The low SCN records the lowest SCN recorded in the log file, while the high SCN records the highest SCN in the log file.