//
you're reading...
Oracle Internal

Oracle SCN – In Details

SCN(system change number) in Oracle Database System and the time of our lives meaning are the same, SCN is Oracle in time number.

Why Oracle not use Operation System time to define it?

If I perform a DML in the US New York Time at 8:00, and then modify the OS time of the machine to at 7:00, and then execute another DML statement. If you use the Operation System Time on the distinction, then Oracle simply do not come out the execution order to distinguish these two DML statements – and this is very important for Oracle. So it uses to generate its own SCN distinguish the order of all operations.

You can view the system’s current SCN Number by:

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
464640326

You can also view the current system saved SCN number:

SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE #
------------------
464639929

If at this time the database is damaged, DBAs need to repair the restart.For the data generated between the two SCN are stored in the online redo log file:

SQL> select GROUP#, STATUS, FIRST_CHANGE# from v$log;
GROUP# STATUS FIRST_CHANGE#
---------- ------------------------------ ---------- ---
1 INACTIVE 464633029
2 INACTIVE 464637664
3 CURRENT 464638303
  • group1 saved data generated SCN Number 464633029-464637664
  • group2 in the SCN number 464637664-464638303
  • group3 the SCN number 464638303-464640326 (current SCN number)

So, if at this time we do an shutdown abort or restart, Oracle database will perform an instance recovery from restart, online redo log files group3 will be used. Through v$log.status field can be seen: The status of the group3 is current.

Part I – Dialysis SCN Number

SCN is updated when the Oracle database transaction occurs, the DBMS automatically maintained to accumulate increasing a number. When the transaction is committed, LGWR will make sure log buffer is written to redo log file, but also the SCN of the transaction wil be written in to the redo log file(wait-until-completed) at the same time. After so when you commit transaction, before the transaction successful message is returned, LGWR must first complete the above behavior, otherwise you can not see submitted successful response message.

Check the latest SCN

SQL> select dbms_flashback.get_system_change_number from dual;

It will be appreciated here returned SCN, is currently the latest SCN redo log file records. Because after the transaction committed, the Oracle Database will generated a new SCN, and Once commit occurs, it will be immediately written into the redo log file.

The purpose of CHECKPOINT to correlate with SCN is to make sure the transaction has been submitted inside the buffer space is written back to disk. Otherwise, once the crash occurs or when the need for recovery, It is necessary to spend a lot of time to recovery from the last transaction within the redo log file, so this recovery process is a waste of time and inefficient.

When commit a transaction, the redo buffer will immediately written into the redo log file, but oracle database will not schronous write the updated block (dirty block) back to datafile, this is to reduce excessive disk IO , so using batch writing mode instead.

When a checkpoint occurs Oracle Database must update the headers of all datafiles to record the details of the checkpoint. This is done by the CKPT process, the CKPT process does not write blocks to disk;.. DBWn always performs that task.

In shutdown normal or shutdown immediate (also known as clean shutdown), checkpoint will be automatically triggered. When checkpoint process occurs, it will write SCN in four places. Three places in the control file, another one in datafile header.

Three places in control file and One in datafile header(start SCN) are:

  1. System checkpoint SCN
  2. 		SQL> select to_char(checkpoint_change#, 'XXXXXXXXXXXX') from v$database;
    		TO_CHAR (CHECKPOINT_CHANGE #, 'XX
    		-----------------------------------
    		1E36D8AC
    		
  3. Datafile checkpoint SCN
  4. 		SQL> select name, to_char(checkpoint_change#, 'XXXXXXXXXXXX') from v$datafile where  name like '%user01%';
    		NAME                              TO_CHAR(CHECK
    		--------------------------------- -------------
    		/data01/oradata/testdb/user01.dbf 1E36D8AC
    
  5. Stop SCN
  6. 		SQL> select name, last_change# from v$datafile where name like '%user01%';
    		NAME                              LAST_CHANGE#
    		--------------------------------- ------------
    		/data01/oradata/testdb/user01.dbf
    		--Normal datafile in read-write mode of operation, last_change# must be null
    		--There is also a SCN within the datafile header
    
  7. Start SCN
  8. 		SQL> select name, to_char(checkpoint_change#, 'XXXXXXXXXXXX') from v$datafile_header where name like '%user01%';
    		NAME                              TO_CHAR(CHECK
    		--------------------------------- -------------
    		/data01/oradata/testdb/user01.dbf 1E36D8AC
    

Why SCN are stored in the control file to be divided into two areas (system checkpoint SCN, datafile checkpoint SCN).

When one tablespace is set to read-only, his SCN will freeze to stop, then datafile checkpoint SCN is no longer increasing change, but the overall system checkpoint SCN still will continue to increase advance. So this is why Oracle Database need stored SCN in two places.

After normal shutdown database, what happens to SCN?

You can open the database in mount mode

SQL> select to_char(checkpoint_change#, 'XXXXXXXXXXXX') from v$database;
TO_CHAR(CHECKPOINT_CHANGE #, 'XX
--------------------------------
1E36D8AC
SQL> select name, to_char(checkpoint_change#, 'XXXXXXXXXXXX'), to_char (last_change#, 'XXXXXXXXXXXX') from v$datafile where name like '%user01%';

NAME                              TO_CHAR(CHECK TO_CHAR(LAST_
--------------------------------- ------------- -------------
/data01/oradata/testdb/user01.dbf 1E36D8AC      1E36D8AC

The three SCN values stored in the control file is the same. At this point of time, the system will not set stop SCN to null, but equal to start SCN.

The SCN in Datafile header:

SQL> select name, to_char(checkpoint_change#, 'XXXXXXXXXXXX') from v$datafile_header where name like '%user01%';

NAME                              TO_CHAR(CHECK
--------------------------------- -------------
/data01/oradata/testdb/user01.dbf 1E36D8AC

When a clean shutdown happens, checkpoint will be conducted, and at this time the stop SCN of the datafile and the start SCN are the same. And so when we open the database, Oracle will need to check whether the start SCN in the datafile header and the SCN stored in the control file are the same; if the same, then check the start SCN and stop SCN are the same; if still the same, the database will start properly. Otherwise, The database need recovery …. Wait until the database open, stop SCN stored in the control file will be restored to a null value this time. It represents datafile is open in the normal mode.

If not normal shutdown(shutdown abort), then mount the database, you will find the stop SCN is not same as other SCN, but equal to null. This means no checkpoint occurred during the database shutdown, next startup must execute a crash recovery.

Part II – Oracle SCN mechanism to resolve

Oracle SCN(System Chang Number) is an important mechanism for data recovery, Data Guard, Streams replication, synchronization, for each functions of the RAC nodes plays an important role. To understand the SCN operating mechanism that can help you to deeper understand these functions.

Before understanding SCN, we look at how to change oracle transaction data is written to the data file:

  1. The beginning of the transaction;
  2. Find the required data block in the buffer cache, if not found, loaded it from the buffer cache into the data file ;
  3. Transaction modifies data block in buffer cache, the data is marked as “dirty”, and is written in the log buffer;
  4. The transaction commits, LGWR process will make sure the “dirty data” in log buffer is written into the redo log file;
  5. When a checkpoint occurs, CKPT process updates all file header information for all datafiles

DBWn process is responsible for the dirty data of the Buffer Cache is written to the data file.

After these five steps, the final transaction data changes are written to the data file. However, during the five steps above, if the database has an unplanned downtime, and how to know when you restart, which data has been written to the data file, which did not write it (again, in the DG, streams, there are also similar question: redo log of what is the last synchronization data has been copied and what does not)? SCN mechanism can be more complete solution to these problems.

SCN is a number, precisely, is a only increase, not decrease the number. It is precisely this only adds features to ensure that the Oracle knows what should be restored, which should be copied.

There are 4 SCN:

  1. System Checkpoint SCN
  2. Datafile Checkpoint SCN
  3. End SCN (Stop SCN)
  4. Start SCN

The first 3 SCN is present in the control file, the last one is present in the header of the datafile.

In the control file, the System Checkpoint SCN is global for the entire database, thus there is only one, rather than Datafile Checkpoint SCN and stop SCN for each data file, the SCN in datafile header will correspond to an SCN in the control file (Datafile Checkpoint SCN and stop SCN).

During normal operation of the database, Stop SCN(through the view v$datafile the field last_change# can query) is an infinite number or a NULL.

After a transaction is committed (the fourth step), there will be a redo record in the redo log; and at the same time, the system to provide a new SCN (by function dbms_flashback.get_system_change_number can know the most current SCN), recording in which recording. If this record is in the redo log is cleared (the redo log file is full to do switching or checkpoint occurs, all the changes have been written to the data log file), then the SCN is recorded as a redo log of low SCN. Later, the SCN in the redo log after being emptied again written to become the Next SCN.

When the log switch or the occurrence of checkpoint (the fifth step), the DBWn process is written the data recorded by redo between the Low SCN to the Next SCN to the datafile, and the CKPT process (either redo whether the data log in impact to the data files) will update the Start SCN (through the view v$datafile_header field checkpoint_change# can query) in all datafiles header to Next SCN; at the same time, the System Checkpoint SCN in the control file (through over view v$database fields can be queried checkpoint_change#), the Datafile Checkpoint corresponding with each datafile(through the view v$datafile the field checkpoint_change# can query) is also updated to Next SCN. However, if the data file where the table space is set to read-only, the Start SCN in the control file and the Datafile Checkpoint SCN will not be updated.

How Oracle Database is to produce a new SCN? In fact, this figure is then converted from the timestamp. Whenever the need to produce a new SCN to redo the recording, the system acquisition time timestamp, convert it to digital as SCN. We can (10g or later) convert the SCN back to timestamp by using function SCN_TO_TIMESTAMP:

SQL> select dbms_flashback.get_system_change_number,SCN_TO_TIMESTAMP(dbms_flashback.get_system_change_number) from dual;
GET_SYSTEM_CHANGE_NUMBER SCN_TO_TIMESTAMP(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
------------------------ ---------------------------------------------------------
3797076356               11-MAR-08 03.14.27.000000000 PM

Timestamp_to_scn function can also be used to convert a timestamp SCN:

SQL> select timestamp_to_scn(SYSTIMESTAMP) as scn from dual;
SCN
----------
3766078529

Finally, SCN not only as a reflection of changes in the transaction data and maintain data-synchronization, also act as a “heartbeat” The role of the system – about every three seconds the system will refresh the system SCN.

Here, in brief SCN how it works in the database recovery.

Database normally closed (shutdown immediate/normal), it will do a checkpoint first; it will write the data in the log data file to datafile; then the SCN in control files, datafiles (including the Stop SCN in control file ) are updated with the latest SCN.

Database error/accident will not update or partially update with the latest SCN.

When the database is started, Oracle first checks whether each Datafile Checkpoint SCN in the control file and Start SCN in datafiles header are the same, then check whether each Datafile Checkpoint SCN and Stop SCN are the same. If the database notice a difference, it will find the missing SCN from Redo Log file, and re-write to the data file for recovery. Specific data recovery process will not repeat them here.

Oracle SCN as an important mechanism to play a role “controller” in a number of important functions. Learn SCN generation and implementation, to help DBA to understand and deal with recovery, DG, Streams replication problems. Finally mention that the use of SCN mechanism, Oracle10g, 11g has added some very useful features – database flashback and database replay.

Part III – SCN(System Change Number) Information and Recovery

There are three control file SCN. Respectively, system SCN, datafile SCN and last SCN, Another one in the data file header start SCN

SCN

system SCN obtained from the view v$database in correspondence with checkpoint_change# field; datafile SCN, last SCN corresponding view v$datafile the checkpoint_change#, last_change# field, and start SCN from v$datafile_header in correspondence with checkpoint_change# field.

After the database normal start, system SCN, datafile SCN, start SCN must be equal, and last SCN will be placed in infinity, so the value is null. Normally closed (shutdown immediate, showdown normal, showdown translate), the system SCN, datafile SCN, start SCN and last SCN will implementation of full checkpoint and the four kind of SCN number should be equal.

When the database had non-normal shutdown, such as shutdown abort; this time last SCN still is infinite; then when you restart the instance, the system will first compare start SCN and system SCN; if consistent, then another comparison will happen between start SCN and last SCN, because database had non-normal shutdown, the SCN number is different, so the database need a instance recovery.

If the system SCN is great than datafile SCN during the database opening, the database that use the old data file, which is required media recovery.

If the system SCN is less than datafile SCN, and control file SCN is old, on behalf of the use of the old control files, the database need to recover using backup controlfile.

1. The normal startup

		SQL> select checkpoint_change# from v$database; --control file SCN
		CHECKPOINT_CHANGE#
		------------------
		55534071

		SQL> select file#, checkpoint_change# from v$datafile_header; --start SCN
		FILE #     CHECKPOINT_CHANGE #
		---------- ------------------
		15534071
		25534071
		35534071
		45534071
		55534071

		SQL> select file#, checkpoint_change#, last_change# from v$datafile; --datafile scn & last SCN
		FILE #     CHECKPOINT_CHANGE# LAST_CHANGE #
		---------- ------------------ ------------
		15534071
		25534071
		35534071
		45534071
		55534071
		

2. The normal shutdown, and then startup mount

		SQL> shutdown immediate;
		Database closed.
		Database has been uninstalled.
		ORACLE instance shut down.

		SQL> startup mount;
		ORACLE instance started.
		Total System Global Area 437888364 bytes
		Fixed Size 521612 bytes
		Variable Size 211937984 bytes
		Database Buffers 135827120 bytes
		Redo Buffers 713648 bytes
		Database loading is completed.

		SQL> select file#, checkpoint_change# from v$datafile_header;
		FILE #     CHECKPOINT_CHANGE #
		---------- ------------------
		15534485
		25534485
		35534485
		45534485
		55534485

		SQL> select checkpoint_change# from v$database;
		CHECKPOINT_CHANGE #
		------------------
		5534485

		SQL>select file#, checkpoint_change#, last_change# from v$datafile;
		FILE#      CHECKPOINT_CHANGE# LAST_CHANGE#
		---------- ------------------ ------------
		155,344,855,534,485
		255,344,855,534,485
		355,344,855,534,485
		455,344,855,534,485
		555,344,855,534,485
		- Discover start scn = last scn, demonstrate that the system is shut down

		SQL> alter database open;
		Database has changed.
		

3. In a normal state of affairs open operation

		SQL> create table t (a number);
		Table has been created.
		SQL> insert into t values ??(1);
		It has created a row.
		SQL> commit;
		Submit completed.
		SQL> insert into t values ??(2);
		It has created a row.
		

4. Abnormal shutdown

		SQL> shutdown abort;
		ORACLE instance shut down.
		SQL>
		

5. Open to the lower mount state, watch scn

		SQL> startup mount;
		ORACLE instance started.
		Total System Global Area 319888364 bytes
		Fixed Size 453612 bytes
		Variable Size 192937984 bytes
		Database Buffers 125829120 bytes
		Redo Buffers 667648 bytes
		Database loading is completed.
		SQL> select file#, checkpoint_change#, last_change# from v$datafile;
		FILE#      HECKPOINT_CHANGE#  LAST_CHANGE#
		---------- ------------------ ------------
		15534486
		25534486
		35534486
		45534486
		55534486

		SQL> select checkpoint_change# from v$database;
		CHECKPOINT_CHANGE#
		------------------
		5534486

		SQL> select file#, checkpoint_change# from v$datafile_header;
		FILE #     CHECKPOINT_CHANGE#
		---------- ------------------
		15534486
		25534486
		35534486
		45534486
		55534486
		-- Then found start scn and last scn range, last scn is infinite, you need routine recovery
		

6. Change the database state is open, and view the log phase of operation

		SQL> select * from wen.t;
		select * from wen.t
		*
		ERROR at line 1:
		ORA-01219: database not open: queries allowed only in a fixed table/view
		SQL> alter database open;
		Database has changed.

		SQL> select * from wen.t;
		A
		----------
		1
		- Found that the transaction did not submit the missing.
		View log is as follows:
		Completed: ALTER DATABASE MOUNT
		Wed May 17 21:35:46 2006
		alter database open
		Wed May 17 21:35:46 2006
		Beginning crash recovery of 1 threads - will automatically determine whether you need to recover, restore routine here
		Wed May 17 21:35:46 2006
		Started first pass scan
		Wed May 17 21:35:47 2006
		Completed first pass scan
		206 redo blocks read, 90 data blocks need recovery
		Wed May 17 21:35:47 2006
		Started recovery at
		Thread 1: logseq 167, block 271, scn 0.0
		Recovery of Online Redo Log: Thread 1 Group 2 Seq 167 Reading mem 0 - restoration work online redo logs
		Mem # 0 errs 0: D: ORACLEORADATADB1REDO02.LOG
		Wed May 17 21:35:47 2006
		Ended recovery at
		Thread 1: logseq 167, block 477, scn 0.5554724
		90 data blocks read, 90 data blocks written, 206 redo blocks read
		Crash recovery completed successfully - to restore complete
		Wed May 17 21:35:47 2006
		LGWR: Primary database is in CLUSTER CONSISTENT mode
		Thread 1 advanced to log sequence 168
		Thread 1 opened at log sequence 168
		Current log # 3 seq # 168 mem # 0: D: ORACLEORADATADB1REDO03.LOG
		Successful open of redo thread 1.
		Wed May 17 21:35:48 2006
		SMON: enabling cache recovery
		Wed May 17 21:35:48 2006
		ARC0: Evaluating archive log2 thread 1 sequence 167
		ARC0: Beginning to archive log 2 thread 1 sequence 167
		Creating archive destination LOG_ARCHIVE_DEST_1: 'D: DBBKARC00167.001'
		ARC0: Completed archiving log 2 thread 1 sequence 167
		Wed May 17 21:35:48 2006
		Undo Segment 1 Onlined
		Undo Segment 2 Onlined
		Undo Segment 3 Onlined
		Undo Segment 4 Onlined
		Undo Segment 5 Onlined
		Undo Segment 6 Onlined
		
Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: