关于SCN与SCN WRAP，SCN BASE的关系

s.o.m

如题。



ERROR:
ORA-600 [2662] [a] [c] [d] [e]


ARGUMENTS:
Arg [a] Current SCN WRAP
Arg Current SCN BASE
Arg [c] dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA wherethe dependent SCN came from.





关于解决方法刘的blog上有，下面是个调整scn的算法，以前收集的，也请刘指正。



计算规则如下：Arg [c]*4得出一个数值，假设为V_Wrap,
如果Arg [d]=0，则V_Wrap值为需要的level
Arg [d] < 1073741824，V_Wrap+1为需要的level
Arg [d] < 2147483648，V_Wrap+2为需要的level
Arg [d] < 3221225472，V_Wrap+3为需要的level




另一个，调整scn有_minimum_giga_scn和10015事件，这两个有区别吗？

s.o.m

How to map SCN with Timestamp before 10g? [ID 365536.1]
________________________________________
        Modified 16-MAY-2006     Type HOWTO     Status PUBLISHED         
In this Document
  Goal
  Solution
________________________________________
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.6.0
Information in this document applies to any platform.
Goal
In this note, we will help Database Administrators with answers to two common questions:
How can I know the time of last transaction (time of last SCN)?
How can I map SCN with Timestamp prior to 10g?
Solution
SCN <-> TIMESTAMP conversion features are available only from Oracle 10g.  In 10g, there are two built-in functions to give the timestamp and SCN mapping details--Timestamp_to_scn() and Scn_to_timestamp().

For example: In 10g, you will issue code like,

SQL> select current_scn from v$database;
CURRENT_SCN
---------------------------
1086382

SQL> Select scn_to_timestamp(1086382) from dual;

SCN_TO_TIMESTAMP(1086382)
---------------------------------------------------------------------------
15-DEC-05 09.39.49.000000000 PM

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

However, in earlier releases, while there is a system object - SYS.SMON_SCN_TIME that will provide the SCN to TIME mapping information.   There is no conversion facility provided.  

SYS.SMON_SCN_TIME will have a maximum of 1440 rows and each record will be for a 5 minute period.  Oracle maintains this information for maximum of 5 days after which the records will be recycled.

This means that data is stored 12 times per hour * 24 hours * 5 days=1440 rows.  

SCN value is stored internally as :
i. SCN_wrap
ii. SCN_base

Whenever the SCN is incremented, the BASE component is incremented first unil it reaches it maximum.  Once the BASE reaches the maximum value allowed, it is initialized to zero again after incrementing the WRAP by 1.

Using this logic, we can calculate the timestamp of the SCN as follows:

(SCN_WRP * 4294967296) + SCN_BAS should give us the SCN in the number format

To get the time/date for an SCN value in 9i, use the following example:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- (a) Get the current SCN base.
SQL>select max(scn_bas) SCN_BASE from smon_scn_time;

1603342197

--(b) Get the complete SCN and the timestamp.

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YY HH:MI:SS';

SQL> select time_dp TIMESTAMP, scn_wrp*4294967296+SCN_bas SCN from smon_scn_time where
scn_bas='1603342197';

TIMESTAMP                 SCN
------------------            ----------
28-JUL-06 05:31:08        8252235517813
________________________________________

s.o.m

good！谢谢回复！

Maclean Liu(刘相兵

more info from meatlink


Information on the System Change Number (SCN) and how it is used in the Oracle Database [ID 1376995.1]

Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.5 to 11.2.0.3 - Release: 10.1 to 11.2
Information in this document applies to any platform.
Purpose
Read this article to get a high level overview of how a logical timestamp, called the System Change Number (SCN), is used to order database events, and how the advance of this logical timestamp is constrained.
Scope and Application
This document is intended for Oracle DBAs.

Information on the System Change Number (SCN) and how it is used in the Oracle Database
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 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 to an even larger number.

References
NOTE:1393363.1 - Installing, Executing and Interpreting output from the "scnhealthcheck.sql" script

Maclean Liu(刘相兵

10015 本身仅仅是一个事件， 它在当smon做transaction recovery时被触发，实际造成scn 增长的是 adjust_scn 这个事件；

alter session set events '10015 trace name adjust_scn level 1';

是指 在触发10015 事件时 做adjust_scn 这个action 调整scn ；

而MINIMUM_GIGA_SCN 则是一个隐藏参数， 可以让数据库startup时的current_scn 上升到  MINIMUM_GIGA_SCN * 2^30的值

Maclean Liu(刘相兵

10015 event  ODM Data:

Event 10015: Dump rollback segment headers excluding those not listed in undo$)
before and after transaction recovery. To set in init.ora:

/* 当数据库处于mount状态，可以使用10015事件来调整scn */

alter session  set events '10015 trace name adjust_scn level 1';

/* 这里可以设置level 2..10等 (level 1是在每次打开数据库时scn增加1000k)*/

/* 需要注意的是10g某些版本不同于9i，需要设置隐式参数_allow_error_simulation，才能真正增进scn */

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> col current_scn format 999,999,999,999

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1141408

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2213896 bytes
Variable Size             989857784 bytes
Database Buffers          654311424 bytes
Redo Buffers                7135232 bytes
Database mounted.

SQL> alter session set events '10015 trace name adjust_scn level 1';
Session altered.

SQL> alter database open;
Database altered.

SQL>  select current_scn from v$database;
CURRENT_SCN
-----------
    1142031

/* 可以看到current_scn并未大量增加，10.2.0.4上默认10015 adjust_scn不被触发 */

SQL>  alter system set "_allow_error_simulation"=true scope=spfile;
System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size                  2213896 bytes
Variable Size             989857784 bytes
Database Buffers          654311424 bytes
Redo Buffers                7135232 bytes
Database mounted.

SQL> alter session set events '10015 trace name adjust_scn level 1';
Session altered.

SQL> alter database open;
Database altered.

SQL>select current_scn from v$database;
     CURRENT_SCN
----------------
   1,073,741,980





Event: 10015
Text:  Undo Segment Recovery
-------------------------------------------------------------------------------
Note:
   Events should NEVER be set by customers unless advised to do so by
   Oracle Support Services.  See Note 75713.1 for more details.

Explanation:
~~~~~~~~~~~~
        To set this event in the init.ora file:
            event="10015 trace name context forever, level 10"

        This shows the rollback segment headers (transaction tables)
        before and after each chunk of transaction recovery.
        From this, it is possible to see which transactions are dead and
        being recovered.
        (e.g: This trace can be useful prior to Oracle 7.3 if it is necessary
              to corrupt/offline a single rollback segment and you do not know
              which one holds dead transactions).

        From 7.3 onwards (including Oracle8) rollback segment header
        information can be seen using the <View:X$KTUXE>.

Levels:
~~~~~~~
      1  Shows just the name as each segment is scanned for recovery.
      2+ Dumps the rollback segment header before and after recovery.


Sample output from diagnostic event 10015:
UNDO SEG (BEFORE RECOVERY): usn = 4  Extent Control Header
----------------------------------------------------------
Extent Control:: inc#: 33808  tsn: 1    object#: 0
                 #extents: 3            end of table: 1012
                 HWM ext#: 1  size: 65  offset: 47
                 Unlocked
UNDO SEG  (AFTER RECOVERY): usn = 4  Extent Control Header
----------------------------------------------------------
Extent Control:: inc#: 33808  tsn: 1    object#: 0
                 #extents: 3            end of table: 1012
                 HWM ext#: 1  size: 65  offset: 47
                 Unlocked

Maclean Liu(刘相兵

以上解释了  kscnbas(scn base) 与kscnwrp(scn wrap)的关系，以及 _MINIMUM_GIGA_SCN 隐藏参数的作用

Maclean Liu(刘相兵

今天有些累 ， 先 整理一些材料 明天总结一下：

ODM Data:

SCN Base and Wrap are the numbers which maintain the increment of the SCN.The SCN base is incremented for each SCN increment.When the base reaches a limit, SCN Wrap is incremented.


SCNs are just numbers.  Think of the base and the wrap as a way to store an even
bigger number --  The wrap is incremented whenever an increment to the base would result
in an overflow (when the SCN would roll over to zero again).

It will not always be zero, just depends on how many wraps you've done (roll overs if you
will)

SCN由SCN Base和Scn Wrap组成，是一种6个字节的结构(structure)。其中SCN Base占用4个字节，而SCN wrap占用2个字节。但在实际存储时SCN-like的stucture常会占用8个字节。

ub4 kscnbas
ub2 kscnwrp

struct kcvfhcrs, 8 bytes                 @100                              Creation Checkpointed at scn
      ub4 kscnbas                        @100      0x000a8849
      ub2 kscnwrp                        @104      0x0000

kscnbas          ub4=> 上限是 FF FF FF FF=>42 9496 7295

1. SQL> select current_scn from v$database;
   
2. 
   
3. CURRENT_SCN
   
4. -----------
   
5.    15103184
   
6. 
   
7. SQL> create table tl(t1 int);
   
8. 
   
9. Table created.
   
10. 
    
11. SQL>        
    
12. SQL>
    
13. SQL>
    
14. SQL> select current_scn from v$database;
    
15. 
    
16. CURRENT_SCN
    
17. -----------
    
18.    15103204
    
19. 
    
20. SQL> insert into tl values(1);
    
21. 
    
22. 1 row created.
    
23. 
    
24. SQL> select start_scnw, start_scnb from v$transaction;  
    
25. 
    
26. START_SCNW START_SCNB
    
27. ---------- ----------
    
28.          0   15103209
    
29. 
    
30. SQL> commit;
    
31. 
    
32. Commit complete.
    
33. 
    
34. 
    
35. 
    
36. 
    
37. SQL> select current_scn from v$database;
    
38. 
    
39. CURRENT_SCN
    
40. -----------
    
41.    15103298
    
42. 
    
43. SQL> shutdown immediate;
    
44. Database closed.
    
45. Database dismounted.
    
46. ORACLE instance shut down.
    
47. SQL> startup mount;
    
48. ORACLE instance started.
    
49. 
    
50. Total System Global Area 1043886080 bytes
    
51. Fixed Size                  2234960 bytes
    
52. Variable Size             784336304 bytes
    
53. Database Buffers          251658240 bytes
    
54. Redo Buffers                5656576 bytes
    
55. Database mounted.
    
56. SQL> alter system set "_MINIMUM_GIGA_SCN"=1 scope=spfile;
    
57. 
    
58. System altered.
    
59. 
    
60. SQL> startup force;
    
61. ORACLE instance started.
    
62. 
    
63. Total System Global Area 1043886080 bytes
    
64. Fixed Size                  2234960 bytes
    
65. Variable Size             784336304 bytes
    
66. Database Buffers          251658240 bytes
    
67. Redo Buffers                5656576 bytes
    
68. Database mounted.
    
69. Database opened.
    
70. SQL> select current_scn from v$database;
    
71. 
    
72. CURRENT_SCN
    
73. -----------
    
74. 1073742273
    
75. 
    
76. SQL> insert into tl values(1);
    
77. 
    
78. 1 row created.
    
79. 
    
80. SQL> select start_scnw, start_scnb from v$transaction;  
    
81. 
    
82. START_SCNW START_SCNB
    
83. ---------- ----------
    
84.          0 1073742447                ==> 1073742447    4000026F= 2^30+ 26f
    
85.                  
    
86.                  
    
87. SQL>  alter system set "_MINIMUM_GIGA_SCN"=6 scope=spfile;
    
88. 
    
89. System altered.
    
90. 
    
91. SQL> startup force;
    
92. ORACLE instance started.
    
93. 
    
94. Total System Global Area 1043886080 bytes
    
95. Fixed Size                  2234960 bytes
    
96. Variable Size             784336304 bytes
    
97. Database Buffers          251658240 bytes
    
98. Redo Buffers                5656576 bytes
    
99. Database mounted.
    
100. Database opened.
     
101. SQL> select current_scn from v$database;
     
102. 
     
103. CURRENT_SCN
     
104. -----------
     
105. 6442451243
     
106. 
     
107. SQL> insert into tl values(1);
     
108. 
     
109. 1 row created.
     
110. 
     
111. SQL>  select start_scnw, start_scnb from v$transaction;
     
112. 
     
113. START_SCNW START_SCNB
     
114. ---------- ----------
     
115.          1 2147483949                              6442451243= 6*2^30 + 299 即 _MINIMUM_GIGA_SCN* 2^30 + 299 = 1* 4294967295 + 2147483949 +1 = current_scn+1

复制代码