Detailed explanation of System Change number (SCN) and change number scn

Detailed explanation of System Change number (SCN) and change number scn
Detailed description of oracle026 System Change number (SCN) the SCN System Change number is to generate a certain number of time through some functions; ensure the consistency of data files, comparison, old and new;
Why use time to generate numbers, because the comparison time is relatively slow, and the use of numbers is relative to the block point, just as the shared pool compares the SQL
Use the calculated hash value for comparison.

Select dbms_flashback.get_system_change_number,
SCN_TO_TIMESTAMP (dbms_flashback.get_system_change_number) from dual;

// Obtain the SCN value of the current time. dual is a temporary table similar to the temp table of MYSQL.
1153633-14 03.04.02.000000000 AM



There are three SCN in the control file: Start SCN end SCN and file SCN

The file SCN is the file SCN number generated for each data file.
The file SCN is the ending SCN number generated for each data file.

There is a start SCN before each data file

When the database ends SCN normally, it is NULL, and the file's start SCN, file SCN, and system SCN are the same.
When the database is shut down, the system, file, end, and start are all the same,
If the ending SCN is not properly closed, it is empty because the information is not saved in time. When the current data is enabled. Yes
When the data is abnormally closed, the data instance will be restored: some logs, control files LRBA, HRBA, and on disk rba need to be redo.

When we change the data file to the old file, when the system detects that the starting SCN of the old file is different from the ending SCN, data recovery is required:
Run logs: Run SCN to a new one.
Raise SCN

View several SCN:
Select checkpoint_change # from v $ database; // system SCN, which exists in each control file
1148314
Select name, checkpoint_change # from v $ datafile; // file SCN, which exists in each control file
/U01/app/oracle Data/jiagulun/system01.dbf 1148314
/U01/app/oracle/oradata/jiagulun/sysaux01.dbf 1148314
/U01/app/oracle/oradata/jiagulun/undotbs01.dbf 1148314
/U01/app/oracle/oradata/jiagulun/users01.dbf 1148314
/U01/app/oracle/oradata/jiagulun/example01.dbf 1148314

Select name, last_change # from v $ datafile; // end SCN, which exists in each control file
/U01/app/oracle/oradata/jiagulun/system01.dbf
/U01/app/oracle/oradata/jiagulun/sysaux01.dbf
/U01/app/oracle/oradata/jiagulun/undotbs01.dbf
/U01/app/oracle/oradata/jiagulun/users01.dbf
/U01/app/oracle/oradata/jiagulun/example01.dbf

Select name, checkpoint_change # from v $ datafile_header // the file header SCN, which exists in the header of each data file
/U01/app/oracle Data/jiagulun/system01.dbf 1148314
/U01/app/oracle/oradata/jiagulun/sysaux01.dbf 1148314
/U01/app/oracle/oradata/jiagulun/undotbs01.dbf 1148314
/U01/app/oracle/oradata/jiagulun/users01.dbf 1148314
/U01/app/oracle/oradata/jiagulun/example01.dbf 1148314

The redo log file has a first SCN and a next SCN.
First and next are the log records in this file.
Each log entry has an SCN. You can use the frist SCN number as the SCN number of the first record in this file, and use the next SCN number as the start SCN number recorded in the next file log.
Select * from v $ log;
Name null type
---------------------------
GROUP # NUMBER // log GROUP
THREAD # NUMBER
SEQUENCE # NUMBER // Unique Identifier
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2 (3)
STATUS VARCHAR2 (16) // STATUS: active, inactive, current
FIRST_CHANGE # NUMBER // first SCN, and the start of the next log is the next SCN of the previous log (this is what we think at 10 Gb)
FIRST_TIME DATE
NEXT_CHANGE # NUMBER // next SCN (10g is different)
NEXT_TIME DATE

1 1 10 52428800 512 1 no inactive 1108520 months-14 1148313 months-14
2 1 11 52428800 512 1 no current 1148313 22-12 months-14 281474976710655
3 1 9 52428800 512 1 no inactive 1087024 months-14 1108520 months-14


Make an experiment:
1. select * from v $ log;

1 1 10 52428800 512 1 no inactive 1108520 months-14 1148313 months-14
2 1 11 52428800 512 1 no active 1148313 months-14 1156305 months-14
3 1 12 52428800 512 1 no current 1156305 22-12 months-14 281474976710655

2. alter system switch logfile;
3. alter system switch logfile;
4. select * from v $ log;

1 1 13 52428800 512 1 no active 1157154 months-14 1157173 months-14
2 1 14 52428800 512 1 no current 1157173 22-12 months-14 281474976710655
3 1 12 52428800 512 1 no active 1156305 months-14 1157154 months-14
5. select name, last_change # from v $ datafile;

/U01/app/oracle Data/jiagulun/system01.dbf 1157173
/U01/app/oracle/oradata/jiagulun/sysaux01.dbf 1157173
/U01/app/oracle/oradata/jiagulun/undotbs01.dbf 1157173
/U01/app/oracle/oradata/jiagulun/users01.dbf 1157173
/U01/app/oracle/oradata/jiagulun/example01.dbf 1157173

The SCN in the data file and the data file header remains unchanged, while the SCN value in the log changes. Because the main purpose of the SCN in the control file and the data file header is to control
Data File consistency

Active indicates that the corresponding dirty buffer in the log file has not been written to the database and cannot be overwritten. This means that the instance needs to be restored.
Try again:
1. alter system flush buffer_cache; // flush the buffer data
2. select name, checkpoint _ change # from v $ datafile;

/U01/app/oracle Data/jiagulun/system01.dbf 1157173
/U01/app/oracle/oradata/jiagulun/sysaux01.dbf 1157173
/U01/app/oracle/oradata/jiagulun/undotbs01.dbf 1157173
/U01/app/oracle/oradata/jiagulun/users01.dbf 1157173
/U01/app/oracle/oradata/jiagulun/example01.dbf 1157173

3. select * from v $ log;
1 13 52428800 512 1 no inactive 1157154 months-14 1157173 months-14
2 1 14 52428800 512 1 no current 1157173 22-12 months-14 281474976710655
3 1 12 52428800 512 1 no inactive 1156305 months-14 1157154 months-14

After the above data is flushed, the SCN in the control file and data file remains unchanged, only the status in the log file has changed.
Because the dirty data in the original buffer zone is cleared, the logs corresponding to it can be overwritten, that is, inactive.
In addition, the SCN value in the file header and control file is the value of the SCN that records ACTIVIE/CURRENT in the log file is equal to the active log in the oldest redo log.

In fact, the SCN in the control file is used to locate which file is running logs, while the LRBA in the control file is used to determine where to start running logs.


Note: The incremental checkpoint does not update the data file header, as well as the SCN information of the database and data file entries in the control file, instead, the CKPT process updates the low cache rba information in the control file every three seconds, that is, the checkpoint location.
So when will they be updated?
Update when the database is shut down normally when the SCN ends.
Other SCN files are updated only when the log file status changes from active to inactive. The test above shows that

Fast_start_mttr_target: this parameter is used to set the instance's recovery time, because the number of values will affect the DBWR write database frequency,
Shortening the recovery time indicates that there is less dirty data because of the high write frequency.

1. What is the significance of SCN? System change number Time Succession, new and old Select dbms_flashback.get_system_change_number, SCN_TO_TIMESTAMP (dbms_flashback.get_system_change_number) from dual; 2. Common SCN Control File System SCN Select checkpoint_change # from v $ database; File SCN Select name, checkpoint_change # from v $ datafile; End SCN Select name, last_change # from v $ datafile; Checkpoint Information The incremental checkpoint does not update the data file header, as well as the database SCN in the control file and the SCN information of the data file entries, instead, the CKPT process updates the low cache rba information in the control file every three seconds, that is, the checkpoint location. Select CPDRT, CPLRBA_SEQ | '.' | CPLRBA_BNO | '.' | CPLRBA_BOF "Low RBA ", CPODR_SEQ | '.' | CPODR_BNO | '.' | CPODR_BOF" On disk RBA ", CPODS, CPODT, CPHBT from x $ kcccp; The CPDRT column is the number of dirty blocks in the checkpoint queue. The CPODS column is the scn of on disk rba. The CPODT column is the timestamp of on disk rba. The CPHBT column is the heartbeat Data File Header Start SCN Select name, checkpoint_change # from v $ datafile_header; ITL transaction slot of the data block Header Log change vector Run logs and empty run logs Rollback segment transaction table Log File Header First, next Select recid, sequence #, first_change #, next_change # from v $ log_history where rownum <6; Select * from v $ log; Select * from v $ archived_log 3. instance recovery Only need redo log: active, current Instance recovery judgment basis Demonstrate SCN changes If an instance crashes, you only need to find the checkpoint location (low cache rba) in the log file, and then apply all the redo log files to complete the rollback operation. After the instance crashes, the database is started again. oracle reads the low cache rba from the control file, which is the checkpoint location. Redo logs are applied to the on disk rba. On disk rba is the rba of the last redo record of the redo log file in the disk. 4. fast_start_mttr_target Related Operations Select checkpoint_change # from v $ database Alter system checkpoint Alter system switch logfile Select name, checkpoint_change # from v $ datafile Select name, checkpoint_change # from v $ datafile_header Select * from v $ log; Begin For I in 1 .. 10000 loop Insert into t2 values (1, 'xkj '); Commit; End loop; End; Select * from t2 Alter system flush buffer_cache