SCN and Checkpoint (Doc ID 1431133.1)
What is a Checkpoint?
A checkpoint is the act of flushing modified, cached database blocks to disk. Normally, when you make a change to a block the modifications of that block are made to a memory copy of the block. When you commit the block, it is not written to the datafile but it is written to the REDO LOG, so we can "replay" a transaction in the event of an instance failure. Eventually, the system will checkpoint your modified blocks to the datafiles on disk.
A checkpoint number is the SCN number at which all the dirty buffers are written to disk. There can be a checkpoint at object/tablespace/datafile/database level.
A successful checkpoint guarantees that all database changes up to the checkpoint SCN have been recorded in the datafiles. As a result, only those changes made after the checkpoint need to be applied during instance recovery.
The goal of a checkpoint is to get dirty buffers from the SGA onto disk safely.
Events that make checkpoint to occur:
The checkpoint process updates the control file when a checkpoint is performed. Remember that a checkpoint operation writes all changed (dirty) blocks to disk. By recording this fact in the control file, Oracle knows what redo records need to be applied in the event of an instance failure. To recover from an instance failure, Oracle needs to apply all redo generated after the last checkpoint recorded in the control file.
DBWR writes dirty blocks from the buffer cache to disk -- that does not happen when you "commit" -- LGWR is involved during a commit.
When the log buffer is 1 MB full, 1/3 full, every 3 seconds or every commit - whichever comes first - those all trigger redo writes
DBWR <---> checkpoint
LGWR <---> commit
NOTE:
System Change Number (SCN), Headroom, Security and Patch Information (Doc ID 1376995.1)
PURPOSE
This article provides information regarding SCN and Checkpoint
QUESTIONS AND ANSWERS
What is System Change Number (SCN)?
The SCN is a stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN. The value of a SCN is the logical point in time at which changes are made to a database. This number is utilized by Oracle to log the changes made to the database.
SCN is a 6 Byte (48 bit) number whose value is 281,474,976,710,656 and represented as 2 parts - SCN_BASE and SCN_WRAP. An SCN_BASE is a 4 Byte (32 bit) number and SCN_WRAP is a 2 Byte (16 bit) number. Whenever SCN_BASE reaches its maximum (2 power 32 = 4294967296 ), SCN_WRAP is increased by one and SCN_BASE will be reset to 0. This continues until SCN_WRAP reaches its maximum, i.e. 2 power 16 = 65536.
SCN = (SCN_WRAP * 4294967296) + SCN_BASE
The SCN is a stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN. The value of a SCN is the logical point in time at which changes are made to a database. This number is utilized by Oracle to log the changes made to the database.
SCN is a 6 Byte (48 bit) number whose value is 281,474,976,710,656 and represented as 2 parts - SCN_BASE and SCN_WRAP. An SCN_BASE is a 4 Byte (32 bit) number and SCN_WRAP is a 2 Byte (16 bit) number. Whenever SCN_BASE reaches its maximum (2 power 32 = 4294967296 ), SCN_WRAP is increased by one and SCN_BASE will be reset to 0. This continues until SCN_WRAP reaches its maximum, i.e. 2 power 16 = 65536.
SCN = (SCN_WRAP * 4294967296) + SCN_BASE
When the base exceeds 4 billion, then the wrap is incremented by 1. Essentially, wrap counts the number of times base wrapped around 4 billion.
Logically, The maximum value of the wrap defines the maximum value of SCN i.e. maximum value of wrap*4 billion = 65536* 4* power(2,30) = 281474976710656 = 281 trillion values.
SCN is incremented with the completion of each and every transaction. A commit does not write to datafiles at all. It does not update control files at all.
The SCN is written to the controlfiles when the control files are updated - which happens as the result of a few things, one being "end of official checkpoint".
SCN's are written to redo logs continuously - when you commit they are emitted into the redo stream, and semantically speaking, after the checkpoint COMPLETES, not after the checkpoint is INITIATED.
The current SCN can be obtained by either of the following queries:
The SCN is written to the controlfiles when the control files are updated - which happens as the result of a few things, one being "end of official checkpoint".
SCN's are written to redo logs continuously - when you commit they are emitted into the redo stream, and semantically speaking, after the checkpoint COMPLETES, not after the checkpoint is INITIATED.
The current SCN can be obtained by either of the following queries:
select dbms_flashback.get_system_change_number scn from dual;
select current_scn from v$database;
select current_scn from v$database;
What is a Checkpoint?
A checkpoint is the act of flushing modified, cached database blocks to disk. Normally, when you make a change to a block the modifications of that block are made to a memory copy of the block. When you commit the block, it is not written to the datafile but it is written to the REDO LOG, so we can "replay" a transaction in the event of an instance failure. Eventually, the system will checkpoint your modified blocks to the datafiles on disk.
A checkpoint number is the SCN number at which all the dirty buffers are written to disk. There can be a checkpoint at object/tablespace/datafile/database level.
A successful checkpoint guarantees that all database changes up to the checkpoint SCN have been recorded in the datafiles. As a result, only those changes made after the checkpoint need to be applied during instance recovery.
The goal of a checkpoint is to get dirty buffers from the SGA onto disk safely.
Events that make checkpoint to occur:
- When all dirty buffers in the Oracle SGA are written to datafiles by DBWR.
- When a redo log switch occurs.
- Whenever the time set by the LOG_CHECKPOINT_TIMEOUT parameter is reached.
- By issuing the command 'alter system switch logfile' or 'alter system checkpoint'.
The checkpoint process updates the control file when a checkpoint is performed. Remember that a checkpoint operation writes all changed (dirty) blocks to disk. By recording this fact in the control file, Oracle knows what redo records need to be applied in the event of an instance failure. To recover from an instance failure, Oracle needs to apply all redo generated after the last checkpoint recorded in the control file.
DBWR writes dirty blocks from the buffer cache to disk -- that does not happen when you "commit" -- LGWR is involved during a commit.
When the log buffer is 1 MB full, 1/3 full, every 3 seconds or every commit - whichever comes first - those all trigger redo writes
DBWR <---> checkpoint
LGWR <---> commit
NOTE:
- Log switches cause checkpoints. Checkpoints do not cause log switches.
- When the checkpoint is complete, the redo logs that protected the now checkpointed data are not needed for instance recovery anymore.
=======================================================================
System Change Number (SCN), Headroom, Security and Patch Information (Doc ID 1376995.1)
SCOPE
This document is intended for Oracle DBAs.
DETAILS
The system change number (SCN) is a logical, internal timestamp used by the Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction.
The database uses SCNs to query and track changes. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction typically have the same SCN. When a transaction commits, the database records an SCN for this commit. Multiple transactions that commit at the same time may share the same SCN.
SCNs occur in a monotonically increasing sequence, and there is a very large upper limit to how many SCNs an Oracle Database can use - that limit is currently 281 trillion, or specifically 281,474,976,710,656 (is 2^48) SCN values.
Given that there is an upper limit, it is important that any given Oracle Database does not run out of available SCNs. The Oracle Database uses a time based rationing system to ensure that this does not happen.
At any point in time, the Oracle Database calculates a "not to exceed" limit for the number of SCNs a database can have used, based on the number of seconds elapsed since 1988, multiplied by 16,384. This is known as the database's current maximum SCN limit. Doing this ensures that Oracle Databases will ration SCNs over time, allowing over 500 years of data processing for any Oracle Database.
The difference between the current SCN the database is using, and the "not to exceed" upper limit, is known as the SCN headroom. For almost all Oracle Databases, this headroom is constantly increasing every second.
However, Oracle has determined that some software bugs could cause the database to attempt to exceed the current maximum SCN value (or get closer to the limit than was warranted).
Generally if the database does try to exceed the current maximum SCN value, the transaction that caused this event would be cancelled by the database, and the application would see an error. The next second the limit increases, so typically the application then continues with a slight hiccough in processing. However, in some very rare cases, the database does need to shut down to preserve its integrity. In no cases is data lost or corrupted.
Similar to how clocks are kept synchronized in a computer network, when two databases communicate with each other over a database link, they synchronize their SCNs by picking the largest SCN in use by the two. So in some cases, databases experienced rapidly decreasing SCN headroom not because of a bug in that specific database, but because the bug was active in one or more of the databases that database was connected to. Since the database always rejects SCNs that exceed the current maximum SCN, the provision of being able to run Oracle Databases for more than 500 years was not affected in any of the cases.
All the associated bugs have been fixed in the January 2012 CPU (and associated PSU). The same fixes are also available in the database Patchset Update (PSU) and the latest Oracle Exadata and Windows bundled patches.
Some customers expressed concerns that they may be getting closer to the current maximum SCN limit faster than the data processing they are doing would warrant. In all cases Oracle has found this to be a factor of one of the bugs fixed in the January 2012 CPU - and customers that have applied the fixes find that their SCN headroom starts to increase again, as it should.
To make sure they are not seeing these potential issues in their systems, customers can run a script that checks how far any particular database is away from the current maximum SCN limit for that database. The script is available in Document:1393363.1. The script will alert customers that they may be close to the maximum SCN limit, in which case Oracle recommends they should apply the CPU to the affected database (and interconnected databases) without delay. The expectation is then that these databases will start to grow their available SCN headroom, and for the affected customers that have applied the CPU, this has indeed been the case. The vast majority of customers will find their databases are not even close to the maximum SCN limit, in which case they can apply the CPU (or associated PSU) as part of their normal patching procedures. As always, Oracle recommends that CPUs be applied as soon as possible to address any additional security issues fixed in the CPU.
Longer term Oracle will be raising the upper limit from 281 trillion (i.e. 2^48) to an even larger number.
The database uses SCNs to query and track changes. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction typically have the same SCN. When a transaction commits, the database records an SCN for this commit. Multiple transactions that commit at the same time may share the same SCN.
SCNs occur in a monotonically increasing sequence, and there is a very large upper limit to how many SCNs an Oracle Database can use - that limit is currently 281 trillion, or specifically 281,474,976,710,656 (is 2^48) SCN values.
Given that there is an upper limit, it is important that any given Oracle Database does not run out of available SCNs. The Oracle Database uses a time based rationing system to ensure that this does not happen.
At any point in time, the Oracle Database calculates a "not to exceed" limit for the number of SCNs a database can have used, based on the number of seconds elapsed since 1988, multiplied by 16,384. This is known as the database's current maximum SCN limit. Doing this ensures that Oracle Databases will ration SCNs over time, allowing over 500 years of data processing for any Oracle Database.
The difference between the current SCN the database is using, and the "not to exceed" upper limit, is known as the SCN headroom. For almost all Oracle Databases, this headroom is constantly increasing every second.
However, Oracle has determined that some software bugs could cause the database to attempt to exceed the current maximum SCN value (or get closer to the limit than was warranted).
Generally if the database does try to exceed the current maximum SCN value, the transaction that caused this event would be cancelled by the database, and the application would see an error. The next second the limit increases, so typically the application then continues with a slight hiccough in processing. However, in some very rare cases, the database does need to shut down to preserve its integrity. In no cases is data lost or corrupted.
Similar to how clocks are kept synchronized in a computer network, when two databases communicate with each other over a database link, they synchronize their SCNs by picking the largest SCN in use by the two. So in some cases, databases experienced rapidly decreasing SCN headroom not because of a bug in that specific database, but because the bug was active in one or more of the databases that database was connected to. Since the database always rejects SCNs that exceed the current maximum SCN, the provision of being able to run Oracle Databases for more than 500 years was not affected in any of the cases.
All the associated bugs have been fixed in the January 2012 CPU (and associated PSU). The same fixes are also available in the database Patchset Update (PSU) and the latest Oracle Exadata and Windows bundled patches.
Some customers expressed concerns that they may be getting closer to the current maximum SCN limit faster than the data processing they are doing would warrant. In all cases Oracle has found this to be a factor of one of the bugs fixed in the January 2012 CPU - and customers that have applied the fixes find that their SCN headroom starts to increase again, as it should.
To make sure they are not seeing these potential issues in their systems, customers can run a script that checks how far any particular database is away from the current maximum SCN limit for that database. The script is available in Document:1393363.1. The script will alert customers that they may be close to the maximum SCN limit, in which case Oracle recommends they should apply the CPU to the affected database (and interconnected databases) without delay. The expectation is then that these databases will start to grow their available SCN headroom, and for the affected customers that have applied the CPU, this has indeed been the case. The vast majority of customers will find their databases are not even close to the maximum SCN limit, in which case they can apply the CPU (or associated PSU) as part of their normal patching procedures. As always, Oracle recommends that CPUs be applied as soon as possible to address any additional security issues fixed in the CPU.
Longer term Oracle will be raising the upper limit from 281 trillion (i.e. 2^48) to an even larger number.
===================================================================
Comments
Post a Comment
Oracle DBA Information