隐含参数_minimum_giga_scn的理解

郑全

Scn基本知识

 Mos 对scn的解释

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 inDocument: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.

 

在Oracle内部，SCN分为两部分存储，分别称之为scn wrap和scn base。实际上SCN长度为48位，即它其实就是一个48位的整数。只不过可能是由于在早些年通常只能处理32位甚至是16位的数据，所以人为地分成了低32位（scnbase）和高16位（scn wrap）

 SCN= (SCN_WRP * 4294967296) + SCN_BAS

一些字典表中记录SCN_WRP 和SCN_BAS的值

v$transaction;

smon_scn_time

_minimum_giga_scn参数理解

 

_minimum_giga_scn参数的作用

_minimum_giga_scn=n的含义是把SCN往前推进到nG，但请注意，只有在SCN小于nG的时候才会用到这个隐含参数，反之则Oracle会置这个隐含参数于不顾。

比如_minimum_giga_scn设置为1

这个开始最小scn为1*2^30=4294967296

注意_minimum_giga_scn不是调整SCN_WRP但是可以通过_minimum_giga_scn推算出SCN_WRP的值

通过_minimum_giga_scn也可以反推算出SCN_WRP的值 和 SCN_BASE的最小值

如果_minimum_giga_scn 6

取整数 (n*2^30)/2^32

如果n为4-8则为1

如果n为8-16 则为2

调整_minimum_giga_scn引起SCN_WRP，SCN_BASE 的变化测试

SQL> select current_scn from v$database;

 

CURRENT_SCN

-----------

    4472678

 

SQL> create table test(id number);

 

Table created.

 

SQL> insert into test values(1);

 

1 row created.

 

SQL> select start_scnw, start_scnb from v$transaction; 

 

START_SCNW START_SCNB

---------- ----------

         0    4490405

 

SQL> rollback;

 

Rollback complete.

 

SQL> alter system set "_MINIMUM_GIGA_SCN"=2 scope=spfile;

 

System altered.

 

SQL> startup force

ORACLE instance started.

 

Total System Global Area 1090519040 bytes

Fixed Size                  1266996 bytes

Variable Size             301992652 bytes

Database Buffers          771751936 bytes

Redo Buffers               15507456 bytes

Database mounted.

Database opened.

SQL> select start_scnw, start_scnb from v$transaction; 

 

no rows selected

 

SQL> insert into test values(1);

 

1 row created.

 

SQL> select start_scnw, start_scnb from v$transaction; 

 

START_SCNW START_SCNB

---------- ----------

         0 2147489696

 

SQL> rollback;

 

Rollback complete.

 

SQL>  alter system set "_MINIMUM_GIGA_SCN"=4 scope=spfile;

 

System altered.

 

SQL> startup force

ORACLE instance started.

 

Total System Global Area 1090519040 bytes

Fixed Size                  1266996 bytes

Variable Size             301992652 bytes

Database Buffers          771751936 bytes

Redo Buffers               15507456 bytes

Database mounted.

Database opened.

SQL> insert into test values(1);                      

 

1 row created.

 

SQL> select start_scnw, start_scnb from v$transaction; 

 

START_SCNW START_SCNB

---------- ----------

         1         86

 

SQL> rollback;

 

Rollback complete.

 

SQL>

SQL>  alter system set "_MINIMUM_GIGA_SCN"=8 scope=spfile;

 

System altered.

 

SQL> startup force

ORACLE instance started.

 

Total System Global Area 1090519040 bytes

Fixed Size                  1266996 bytes

Variable Size             301992652 bytes

Database Buffers          771751936 bytes

Redo Buffers               15507456 bytes

Database mounted.

Database opened.

SQL> insert into test values(1);

 

1 row created.

 

SQL> select start_scnw, start_scnb from v$transaction;

 

START_SCNW START_SCNB

---------- ----------

         2         84

 

SQL> rollback;

 

Rollback complete.

 

 

注意：

11.2.0.2.5 以后不再支持通过_minimum_giga_scn来调整scn

如果想调整scn 需要通过直接调整controlfile或者调整datafile header然后再重建控制文件