隱含引數_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然後再重建控制檔案
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15747463/viewspace-1142993/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20190417]隱含引數_SPIN_COUNT.txt
- 常用指令碼:獲取隱含引數指令碼
- [20190401]隱含引數_mutex_spin_count.txtMutex
- Oracle direct path read相關隱含引數Oracle
- 使用隱含引數testMappingSpeed排查GoldenGate抽取慢的步驟APPGo
- 【PARANETERS】Oracle異常恢復相關的隱含引數Oracle
- Oracle 11G 隱含引數“_controlfile_autobackup_delay”Oracle
- v$parameter gv$parameter 檢視 DDL 與隱含引數
- [20191206]隱含引數_db_always_check_system_ts.txt
- [20200420]V$SES_OPTIMIZER_ENV 查不到剛修改的隱含引數.txt
- 日誌損壞時,加入隱含引數開啟資料庫的總結資料庫
- linux,mtime引數的理解Linux
- php引數3個點的含義PHP
- jmeter 引數理解JMeter
- Python 中 key 引數的含義及用法Python
- Python中key引數的含義及用法Python
- histb 引導核心 boot_cmd 引數含義boot
- SpringDataJpa列印Sql詳情(含引數)SpringSQL
- [20191204]hugepage相關引數含義.txt
- [20220913]hugepage相關引數含義.txt
- SAP Fiori 應用 url 中的 DraftUUID 引數的含義RaftUI
- 1.5 - Numpy的方法中,axis引數的理解
- 含兩個引數的三元函式的高階偏導數函式
- 檢視oralce10g,11g隱含引數,並在SQLPLUS視窗格式化輸出SQL
- SAP Fiori Launchpad url 引數 sap-app-origin-hint 的含義APP
- 關於隱藏引數:_no_recovery_through_resetlogs
- 淺談對python pandas中 inplace 引數的理解Python
- 深入理解RabbitMQ中的prefetch_count引數MQ
- innodb_flush_log_at_trx_commit引數的直白理解MIT
- git merge合併程式碼時各引數含義Git
- 電腦顯示器各項引數的含義科普大全:買液晶顯示器主要看哪些引數?
- 10.使用隱含規則
- 帶你深入理解傳遞引數
- Request 接收引數亂碼原理解析
- 你都理解建立執行緒池的引數嗎?執行緒
- 記一下rgb螢幕時序引數對應的含義
- 坑坑坑,刪庫跑路的多種隱含命令
- 【Data Pump】理解expdp中的ESTIMATE和ESTIMATE_ONLY引數
- [高頻面試]解釋執行緒池的各個引數含義面試執行緒