[20150129]关于取scn号.txt
--SCN有称系统改变号或者系统提交号,在oracle占有重要的位置.可以讲scn无处不在.
--取scn号一般使用查询dbms_flashback.get_system_change_number或者查询视图v$database的current_scn字段.
--两种实际上还是有1点点小区别,通过例子来说明:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> set numw 12
SCOTT@test> select dbms_flashback.get_system_change_number scn from dual;
SCN
------------
11998658487
SCOTT@test> select current_scn from v$database ;
CURRENT_SCN
------------
11998658489
select current_scn from v$database
union all
select dbms_flashback.get_system_change_number scn from dual;
CURRENT_SCN
------------
11998658549
11998658549
select dbms_flashback.get_system_change_number scn from dual
union all
select current_scn from v$database ;
SCN
------------
11998658609
11998658610
SCOTT@test> /
SCN
------------
11998658615
11998658616
-- 可以发现1个小细节,第2种执行方式,后面的值表前面的大1.而第1种的执行两种都是相等的.
select dbms_flashback.get_system_change_number scn1, current_scn from v$database
union all
select current_scn scn1,dbms_flashback.get_system_change_number from v$database;
SCN1 CURRENT_SCN
------------ ------------
11998659577 11998659577
11998659578 11998659578
--也就是讲通过视图v$database取实际上是当前的scn号+1,而dbms_flashback.get_system_change_number取的是当前的scn号.
--看看mount时候的情况:
SYS@test> select current_scn from v$database ;
CURRENT_SCN
-----------
0
SYS@test> alter database open read only ;
Database altered.
SYS@test> select dbms_flashback.get_system_change_number from dual ;
GET_SYSTEM_CHANGE_NUMBER
------------------------
11998660622
SYS@test> select current_scn from v$database ;
CURRENT_SCN
------------
11998660622
--如果你在10g下测试:
SYS@test> startup open read only ;
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
Database opened.
SYS@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SYS@test> select dbms_flashback.get_system_change_number scn from dual;
SCN
------------
11996045618
SYS@test> select current_scn from v$database ;
CURRENT_SCN
------------
0
--10g下有点小小的不同,在只读情况下为0,取dbms_flashback.get_system_change_number正常.
--11g下在只读情况下两者相等.
--当然知道这个细节没什么用,用它来提高scn号太慢,不过如果可以使用看看.如果数据库异常崩溃,在使用特殊方法恢复后,会出现数据块
--里面的scn好大于文件头的scn号.这样在访问这些块时,会报错.
ORA-00600: internal error code, arguments: [2662], [0], [897694446], [0], [897695488], [8388697], [], []
--可以参考 http://blog.itpub.net/267265/viewspace-750075/.
--再重复模拟这个错误看看.
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SCOTT@test> select ora_rowscn ,rowid ,t2.* from t2;
ORA_ROWSCN ROWID DEPTNO DNAME LOC
------------ ------------------ ------------ -------------- -------------
11996045788 AAAOQJAAEAAAAGGAAA 10 ACCOUNTING NEW YORK
11996045788 AAAOQJAAEAAAAGGAAB 20 RESEARCH DALLAS
11996045788 AAAOQJAAEAAAAGGAAC 30 SALES CHICAGO
11996045788 AAAOQJAAEAAAAGGAAD 40 OPERATIONS BOSTON
SCOTT@test> @ &r/lookup_rowid AAAOQJAAEAAAAGGAAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
58377 4 390 0 4,390 alter system dump datafile 4 block 390 ;
--在只读打开下看scn号:
SYS@test> select dbms_flashback.get_system_change_number scn from dual;
SCN
------------
11996046173
--假设给数据块的scn增加1e4,11996045788+10000=11996055788.这样增加11996055788-11996046173=9615.
SYS@test> @ &r/10to16 11996045788
10 to 16 HEX REVERSE16
-------------- -----------------------------------
00002cb0521dc 0xdc2105cb-02000000
SYS@test> @ &r/10to16 11996055788
10 to 16 HEX REVERSE16
-------------- -----------------------------------
00002cb0548ec 0xec4805cb-02000000
--也就是使用bbed修改 dc21 => ec48.我使用的cpu是intel,小头在前.
BBED> set dba 4,390
DBA 0x01000186 (16777606 4,390)
BBED> find /x dc21 top
File: /mnt/ramdisk/test/users01.dbf (4)
Block: 390 Offsets: 8 to 8191 Dba:0x01000186
------------------------------------------------------------------------------------------------------------------------------------------------
dc2105cb 02000106 534d0000 01000000 09e40000 db2105cb 02000000 02003200 81010001 02001a00 17020000 c4008000 01032200 04200000 dc2105cb 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00010400 ffff1a00 3c1f221f 221f0000 04003c1f 561f6c1f 801f0000 00000000 00000000
....
BBED> find
File: /mnt/ramdisk/test/users01.dbf (4)
Block: 390 Offsets: 64 to 8191 Dba:0x01000186
------------------------------------------------------------------------------------------------------------------------------------------------
dc2105cb 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00010400 ffff1a00 3c1f221f 221f0000 04003c1f 561f6c1f 801f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
BBED> find
File: /mnt/ramdisk/test/users01.dbf (4)
Block: 390 Offsets: 8190 to 8191 Dba:0x01000186
------------------------------------------------------------------------------------------------------------------------------------------------
dc21
-- 一共3处. 我使用bvi修改,这样简单一些.害怕改错最好做一个冷备份. 390*8192=3194880
# bvi -b 3194880 -s 8192 /mnt/ramdisk/test/users01.dbf
-- bvi 的一些修改技巧做一些总结: set memmove打开编辑模式.这个应该不常用.检索16进制使用反斜杠\.
BBED> set dba 4,390
DBA 0x01000186 (16777606 4,390)
BBED> sum
Check value for File 4, Block 390:
current = 0x4d53, required = 0x2463
BBED> sum apply
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Check value for File 4, Block 390:
current = 0x2463, required = 0x2463
SYS@test> alter system flush BUFFER_CACHE;
System altered.
SYS@test> select * from scott.t2;
select * from scott.t2
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2662], [2], [3406111582], [2], [3406121196], [16777606], [], []
ORA-600 [2662] [a] [b] {c} [d] [e]
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg {c} dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.
--最后的参数是dba.
SYS@test> @ &r/dfb10 16777606
RFILE# BLOCK#
------------ ------------
4 390
TEXT
-----------------------------------------
alter system dump datafile 4 block 390 ;
--正好对上.
--相差SCN3406121196-3406111582=9614.相差不是很大.建立脚本.(注意要使用v_$databse)
$ cat f1.sql
declare
m_id number;
begin
for i in 1 .. 9700 loop
select current_scn into m_id from v_$database;
end loop;
end ;
/
SYS@test> select current_scn from v$database ;
CURRENT_SCN
------------
11996055511
SYS@test> @f1.sql
PL/SQL procedure successfully completed.
SYS@test> select * from scott.t2;
DEPTNO DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--OK,这样可以正常访问了.
