[20150129]关于取scn号.txt

  1. 云栖社区>
  2. 博客>
  3. 正文

[20150129]关于取scn号.txt

lfreeali 2015-01-29 16:41:52 浏览435
展开阅读全文

[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,这样可以正常访问了.

网友评论

登录后评论
0/500
评论
lfreeali
+ 关注