Initialization Parameter log_checkpoints_to_alert to TRUE

You can view your database SCN information in several ways.

You can use Logminer to determine an SCN accociated with a DDL or DML statement.

Alert.log file

Trace Files

You can query the FIRST_CHANGE# column of v$LOG, v$LOG_HISTORY and V$archived_log.

If you have set the Init parameter “log_checkpoints_to_alert to TRUE”, then every time a log switch occurs, Oraclw will write the SCN of each checkpoint to your target database alert.log file.

” How do I Find Alert log File in Oracle 11g ?

Note : An interesting , you cannot look your alert log file location as like Oracle previous Version location (Oracle/admin). It should be in another location, you can use this view v$diag_info. The log file should be as a XML file and also you can find your alert file in trace locations.

“C:\app\mazar\diag\rdbms\own\own\trace ” “

SQL> alter system set log_checkpoints_to_alert=TRUE;

System altered.

After I set this parameter, you can view SCN  in your alert log file every log switch occurs.

SQL> alter system switch logfile;

System altered.

You can see alert.log file

“Beginning log switch checkpoint up to RBA [0x2.2.10], SCN: 1055739
Thread 1 advanced to log sequence 2 (LGWR switch)
  Current log# 2 seq# 2 mem# 0: C:\APP\MAZAR\ORADATA\OWN\REDO02.LOG
Sat Sep 18 10:12:05 2010
Archived Log entry 17 added for thread 1 sequence 1 ID 0x5c50fd4d dest 1:”