1. where is the information of log_seq_number is store

2. if i loss all datafiles and ctl_f when i recover how it know begin and end of log_seq_number

3. is there any relation between log_squ_no and SCN

أخي الفاضل
رقم 1

each datafile header contains the most recent log sequence number (LSN)

that is written at the time the datafile was being written. Oracle uses this LSN info on datafile
headers to know from which log file to start to perform recovery incase i have restored the
datafile from backup.

also, control file keeps all of these info

Assuming that the database exceeded log seq 226, and say the present log seq number
is 300, now the entries are overwritten in the controlfile. Next time if i restored the file for
recovery assuming that file header has a checkpoint number corresponding to the FIRST_CHANGE# in
the archivelog file (say seq 5), how my current controlfile knows as the entry is overwritten.

2-
After you restore the ControlFile, you can query V$DATABASE for CHECKPOINT_CHANGE# and V$LOG for the FIRST_CHANGE# for each SEQUENCE# of the Online Redo Logs as of the point when the ControlFile was backed up. Of course, if the controlfile was backed up after the datafiles (as is usually the case), you still need to figure out the CHECKPOINT_CHANGE# of the first datafile in the backupset.

After you restore the the Datafiles, you can query V$DATAFILE_HEADER for CHECKPOINT_CHANGE# and CHECKPOINT_TIME of each datafile in the BackupSet. Then you do a Dump of the header of the first datafile in the BackupSet . This dump will also report the Log Sequence# , besides the Checkpoint SCN.


If you have a recovery catalog you could use the following sql to find the pieces and the log sequence numbers in the pieces of a certain day for a database.

select name||' '|| handle||' '|| backup_type||' '|| start_time||' '|| completion_time
from rman.rc_database d,
rman.rc_backup_piece p
where d.dbid=p.db_id
and name = '&db_name'
and start_time > to_date('&&date','DD-MM-YYYY')
and completion_time < to_date('&&date','DD-MM-YYYY') +1
order by start_time
/

select THREAD#, SEQUENCE#
from rman.RC_BACKUP_ARCHIVELOG_DETAILS d,
rman.rc_backup_piece p
where d.id2 = p.SET_COUNT
and d.id1 = p.set_stamp
and p.handle = '&handle'
/


3- بعد تعرفك علي log_squ_no اليك بعض المعلومات عن SCN
System Change Number (SCN)
The system change number (SCN) is an ever-increasing value that uniquely identifies
a committed version of the database. 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 and 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.