oracle026 System Change Number (SCN) of the detailed SCN system change number, is through some functions to generate a certain number of time, to ensure consistency of data files, compared successively, new and old;
Why use time to generate numbers, because the comparison time is slower, and the number is relative to the block point, just like the shared pool comparison of 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;
Gets the value of the SCN for the current time, dual is a temporary table that resembles the usefulness of the MySQL temp table.
1153633 2 February-December-14 03.04.02.000000000 am
There are three SCN in the control file: Start SCN end SCN and file SCN
File SCN is the file SCN number generated for each data file
File SCN is the end SCN number generated for each data file
There is a start SCN in front of each data file
NULL when the SCN ends when the database is normal, and the file's starting SCN, file SCN, and system SCN number are the same
When the database is closed, the system, file, end, start are the same,
When not properly closed, the end SCN is empty due to the lack of timely saving of information; The next time the data is opened. would have
When the data is checked for abnormal shutdown, the recovery of the data instance will be done: Redo log section, control file Lrba, Hrba, on DISK RBA
When we replace the data file with the old file, when the system detects the beginning of the old file SCN and the end of the SCN is not the same as the need for data recovery to do:
Run the log: Run the SCN into a new
Enhanced SCN
To view several SCN:
Select checkpoint_change# from V$database; System SCN, stored in each control file
1148314
Select name,checkpoint_change# from v$datafile;//file SCN, which exists in each control file
/U01/APP/ORACLE/ORADATA/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, stored 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//file header SCN, in the head of each data file
/U01/APP/ORACLE/ORADATA/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
Redo log file has a first SCN and a next SCN
First, next is the range of log records in this file
Each log entry has an SCN; you can take the Frist SCN number as the SCN number for the first record of this file, and the next SCN number as the starting SCN number recorded in the next file log.
SELECT * from V$log;
Name null value type
------------- -- ------------
group# number//log Group
thread# number
sequence# number//Unique identifier
BYTES number
BLOCKSIZE number
Members number
Archived VARCHAR2 (3)
Status VARCHAR2 (16)//state: Active,inactive,current
first_change# number//first SCN, and the start of the next log is the next SCN for the previous log (as you would think at 10g)
First_time DATE
next_change# number//next SCN (10g different)
Next_time DATE
1 1 52428800 1 NO INACTIVE 1108520 2 January-December-14 1148313 2 February-December-14
2 1 52428800 1 NO current 1148313 2 February-December-14 281474976710655
3 1 9 52428800 1 NO INACTIVE 1087024 2 January-December-14 1108520 2 January-December-14
Do an experiment:
1. Select * from V$log;
1 1 52428800 1 NO INACTIVE 1108520 2 January-December-14 1148313 2 February-December-14
2 1 52428800 1 NO ACTIVE 1148313 2 February-December-14 1156305 2 February-December-14
3 1 52428800 1 NO current 1156305 2 February-December-14 281474976710655
2. alter system switch logfile;
3. alter system switch logfile;
4. Select * from V$log;
1 1 52428800 1 NO ACTIVE 1157154 2 February-December-14 1157173 2 February-December-14
2 1 52428800 1 NO current 1157173 2 February-December-14 281474976710655
3 1 52428800 1 NO ACTIVE 1156305 2 February-December-14 1157154 2 February-December-14
5. Select name, last_change# from V$datafile;
/U01/APP/ORACLE/ORADATA/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, the data file header, has not changed, and the value of the SCN in the log has changed. Because the main purpose of the SCN in the control file, the data file header is to control
Consistency of data files
Active indicates that the corresponding dirty buffer in the log file has not yet been written to the database and cannot be overwritten, which means that instance recovery is required.
Let's do another experiment:
1. Alter system flush buffer_cache;//flushing buffer data
2. Select name, checkpoint__change# from V$datafile;
/U01/APP/ORACLE/ORADATA/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 1 52428800 1 NO INACTIVE 1157154 2 February-December-14 1157173 2 February-December-14
2 1 52428800 1 NO current 1157173 2 February-December-14 281474976710655
3 1 52428800 1 NO INACTIVE 1156305 2 February-December-14 1157154 2 February-December-14
The above data you will find that when the data in the buffer cache is flush, the control file, the SCN in the data file does not change, only the state in the log file has changed
is because the original buffer of dirty data is emptied, then its corresponding log can become can be overwritten, that is, inactive
And the value of the SCN in the file header, control file is the value of the SCN that records the activie/current in the log file equal to the active log in the oldest redo log
In fact, the SCN in the control file is used to locate which file to run the log, while the Lrba in the control file is to determine where to start running the log
Note: The incremental checkpoint does not update the data file header, and it controls the SCN information for the database SCN and data file entries in the file, except that the CKPT process updates the low cache RBA information in the control file every 3 seconds, that is, the location of the checkpoint.
So when are they going to be updated?
Update when the database shuts down properly when the SCN is closed
The other SCN is updated when the state of the log file is changed from active to inactive, and the test above can prove
Fast_start_mttr_target: This parameter is used to set the instance at the time of recovery, because the values will affect the DBWR write database frequency,
Shorten the recovery time, indicating that there is less dirty data, because the frequency of writing is large.
1, the meaning of SCN? System Change Number
Time
Successively, the old and the new
Select Dbms_flashback.get_system_change_number,
Scn_to_timestamp (Dbms_flashback.get_system_change_number) from dual;
2. Common SCN
Control files
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, and the SCN information for the database SCN and data file entries in the file, but only the CKPT process to update the low cache RBA information in the control file every 3 seconds, that is, the location of the checkpoint.
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 for on disk RBA
Cpodt column is the timestamp of on disk RBA
CPHBT is the heartbeat.
Data File Header
Start SCN
Select name,checkpoint_change# from V$datafile_header;
Data block head ITL transaction slot
Log change in vector
Run log, empty run log
Rollback Segment in 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
Just need redo log:active, current
Instance Recovery judgment basis
Demonstrating SCN changes
If an instance crash occurs, you only need to find the checkpoint location (low cache RBA) in the log file, and the roll-forward operation begins when all the redo log files are applied from here. After the instance crashes, start the database again, and Oracle reads the low cache RBA into the control file, which is the checkpoint location. From here, the redo log is applied to the location of the on disk RBA. On disk RBA is the RBA of the last redo record of the redo log file in the disc.
4, Fast_start_mttr_target
Related actions
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
|
Explanation of System change number (SCN)
