ITPub博客

首页 > 数据库 > Oracle > [20151109]提升scn号11g测试.txt

[20151109]提升scn号11g测试.txt

Oracle 作者:lfree 时间:2015-11-09 15:58:16 0 删除 编辑

[20151109]提升scn号11g测试.txt

--以前的测试都在10g下进行的,在11.2.0.4下重复测试.

1.测试环境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.方法1使用oradebug:

SYS@book> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 001374E5 00000000 00000000 00000000 0000003C 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000

SYS@book> select  dbms_flashback.get_system_change_number ,current_scn from v$database  ;
GET_SYSTEM_CHANGE_NUMBER CURRENT_SCN
------------------------ -----------
                 1275114     1275114

SYS@book> @ &r/10to16 1275114
10 to 16 HEX   REVERSE16
-------------- -----------------------------------
00000001374ea 0xea741300-00000000

--与前面基本对上.
--假设要修改scn=1300000.

SYS@book> @ &r/10to16 1300000

10 to 16 HEX   REVERSE16
-------------- -----------------------------------
000000013d620 0x20d61300-00000000

--测试在关闭数据库下修改.mount状态下修改:
SYS@book> startup mount
ORACLE instance started.
Total System Global Area  626327552 bytes
Fixed Size                  2255832 bytes
Variable Size             369099816 bytes
Database Buffers          247463936 bytes
Redo Buffers                7507968 bytes
Database mounted.

SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000

SYS@book> oradebug poke 0x06001AE70 4 0x13d620
BEFORE: [06001AE70, 06001AE74) = 00000000
AFTER:  [06001AE70, 06001AE74) = 0013D620

SYS@book> oradebug peek 0x06001AE70 4
[06001AE70, 06001AE74) = 0013D620

SYS@book> alter database open read only;
Database altered.

SYS@book> select  dbms_flashback.get_system_change_number ,current_scn from v$database  ;
GET_SYSTEM_CHANGE_NUMBER CURRENT_SCN
------------------------ -----------
                 1300000     1300000

--ok 实现提升scn.注意这样关闭数据库修改失效,要应用不能以只读打开,必须正常open才有效.

3.方法2使用修改隐含参数_minimum_giga_scn:
--据说这种方式在11g下不行,我自己还是测试看看.http://blog.itpub.net/267265/viewspace-1710054/
--拿比较常见的ORA-600 [2662]错误:
ORA-600 [2662] "Block SCN is ahead of Current SCN",说明当前数据库的数据块的SCN早于当前的SCN,主要是和存储在UGA变量中的
dependent SCN进行比较,如果当前的SCN小于它,数据库就会产生这个ORA-600 [2662]的错误了。这个错误一共有五个参数,分别代表不同的含义
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.

一般常用的方法:
一种是在open的状态下:用alter session set events 'IMMEDIATE trace name adjust_scn level n';
一种是在mount状态下:用alter session set events '10015 trace name adjust_scn level n';

--说明以下level的计算,实际上level的单位是1g = 1024*1024*1024 =2^30.
--而1 wrap = 2^32 ,这样1wrap 必须乘以4,相当于 4 level。
--另外我的测试在10.2.0.4的情况下:以上两者方法都没有成功,不知道哪里出错了。

--这样ORA-600 [2662] "Block SCN is ahead of Current SCN"的错误,根据提示计算level的公式如下:

Arg {c}* 4得出一个数值,假设为V_Wrap
如果Arg [d]=0,则V_Wrap值为需要的level
Arg [d] < 1073741824(2^30*1),V_Wrap+1为需要的level
Arg [d] < 2147483648(2^30*2),V_Wrap+2为需要的level
Arg [d] < 3221225472(2^30*3),V_Wrap+3为需要的level

SYS@book> select 1300000,trunc(1300000/power(2,32)) scn_wrap,mod(1300000,power(2,32))  scn_base from dual
     1300000     SCN_WRAP     SCN_BASE
------------ ------------ ------------
     1300000            0      1300000

SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> create pfile='/tmp/initbookx.ora' from spfile ;
File created.

--加入如下参数,相当于提升了2^30 =1g.
*._minimum_giga_scn=1

SYS@book> startup nomount pfile='/tmp/initbookx.ora' ;
LRM-00101: unknown parameter name '_minimum_giga_scn'
ORA-01078: failure in processing system parameters

--很明显11.2.0.4已经不再支持这个参数.不能在使用这种方式修改.
SYS@book> @ &r/hide _minimum_giga_scn
no rows selected

--补充而在11.2.0.3下可以查询到隐含参数_minimum_giga_scn:
SYS@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

SYS@test> @hide '_minimum_giga_scn'
NAME               DESCRIPTION                              DEFAULT_VALUE  SESSION_VALUE SYSTEM_VALUE
------------------ ---------------------------------------- -------------- ------------- ------------
_minimum_giga_scn  Minimum SCN to start with in 2^30 units  TRUE           0             0

4.方法3修改控制文件:
--可以参考http://blog.itpub.net/267265/viewspace-1762333/.
--我使用bvi修改,也可以使用bbed完成.

SYS@book> alter database mount;
Database altered.

SYS@book> @ &r/db_status
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
     FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
---------- ------------------ --------------------- ------------ --------------- -------------- -------
         1            1300660                     0      1300660          925701         925702 SYSTEM
         2            1300660                     0      1300660          925701         925702 ONLINE
         3            1300660                     0      1300660          925701         925702 ONLINE
         4            1300660                     0      1300660          925701         925702 ONLINE
         5            1300660                     0      1300660          952864         952869 ONLINE

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy  FROM v$datafile_header;
     FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ
---------- ------------------ ---------------- ----------------- ------- ---------------- ---
         1            1300660                7            925702 ONLINE               107 NO
         2            1300660             1834            925702 ONLINE               107 NO
         3            1300660           923328            925702 ONLINE                28 NO
         4            1300660            16143            925702 ONLINE               106 NO
         5            1300660           952864            925702 ONLINE                24 NO

SYS@book> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode,current_scn FROM v$database;
SCN locatio NAME               CHECKPOINT_CHANGE# OPEN_MODE            CURRENT_SCN
----------- ------------------ ------------------ -------------------- -----------
controlfile SYSTEM checkpoint             1300660 MOUNTED                        0

SYS@book> show parameter control_files
NAME           TYPE    VALUE
-------------- ------- ----------------------------------------------------------------------------------------------------
control_files  string  /mnt/ramdisk/book/control01.ctl, /u01/app/oracle/fast_recovery_area/book/control02.ctl

--这是1个正常关闭的数据库.检查scn都一致1300660.

SYS@book> @ &r/10to16 1300660
10 to 16 HEX   REVERSE16
-------------- ------------------
000000013d8b4 0xb4d81300

--假设我现在要修改为scn=1400000.
SYS@book> @ &r/10to16 1400000
10 to 16 HEX   REVERSE16
-------------- ------------------
0000000155cc0 0xc05c1500
 
SYS@book> shutdown immediate ;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

--安全期间备份控制文件:
$ bvi /mnt/ramdisk/book/control01.ctl
:set columns=64
--注意:这样可以设置每行显示64个字符,这样可以不受屏幕的控制.(除非你屏幕不够宽,这样设置columns=32).
\b4d81300
--注:bvi下查询16进制数使用反斜线.

00043FF1  00 00 00 00 00 00 00 00 00 00 00 01 15 51 0D 15 C2 00 00 11 00 00 00 52 0D 00 00 FF FF 01 04 20 B8 00 00 00 00 00 00 00 00 .............Q.........R....... .........
                                                                                                    ^^^^^^^^
0004401A  00 00 85 88 55 35 42 4F 4F 4B 00 00 00 00 00 00 00 00 08 02 40 00 00 40 40 00 00 00 00 00 00 00 00 00 06 20 0E 00 00 00 00 ....U5BOOK..........@..@@.......... .....
00044043  00 87 88 55 35 01 00 00 00 00 00 00 00 7A C9 21 31 00 00 00 00 00 04 20 0B 05 00 00 00 05 00 00 00 01 00 00 00 B4 D8 13 00 ...U5........z.!1...... .................
                                                                                                                         ~~~~~~~~~~~
0004406C  00 00 00 00 01 00 00 00 00 00 00 00 02 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 .........................................


--偏移量在0x00044068处.
--继续查询:

00048001  C2 00 00 12 00 00 00 51 0D 00 00 FF FF 01 04 23 B8 00 00 00 00 00 00 00 00 00 00 85 88 55 35 42 4F 4F 4B 00 00 00 00 00 00 .......Q.......#.............U5BOOK......
                                                    ^^^^^^^^
0004802A  00 00 08 02 40 00 00 40 40 00 00 00 00 00 00 00 00 00 06 20 0E 00 00 00 00 00 87 88 55 35 01 00 00 00 00 00 00 00 7A C9 21 ....@..@@.......... ........U5........z.!
00048053  31 00 00 00 00 00 04 20 0B 05 00 00 00 05 00 00 00 01 00 00 00 B4 D8 13 00 00 00 00 00 01 00 00 00 00 00 00 00 02 00 00 00 1...... .................................

----偏移量在0x00048068处

-- 也就是修改这2处为c05c1500.

--另外不要忘记修改检查和为0,包括前面的字节04[注意看^^^^],迁移量在0x0004400F与0x0004800F,修改 00 00 00.
$ cd /u01/app/oracle/fast_recovery_area/book
$ mv control02.ctl control02.ctl_20151109
$ cp /mnt/ramdisk/book/control01.ctl control02.ctl

SYS@book> startup nomount  ;
ORACLE instance started.
Total System Global Area  626327552 bytes
Fixed Size                  2255832 bytes
Variable Size             369099816 bytes
Database Buffers          247463936 bytes
Redo Buffers                7507968 bytes

SYS@book> alter database mount;
Database altered.

SYS@book> @ &r/db_status
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
     FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
---------- ------------------ --------------------- ------------ --------------- -------------- -------
         1            1300660                     0      1300660          925701         925702 SYSTEM
         2            1300660                     0      1300660          925701         925702 ONLINE
         3            1300660                     0      1300660          925701         925702 ONLINE
         4            1300660                     0      1300660          925701         925702 ONLINE
         5            1300660                     0      1300660          952864         952869 ONLINE

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy  FROM v$datafile_header;
     FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ
---------- ------------------ ---------------- ----------------- ------- ---------------- ---
         1            1300660                7            925702 ONLINE               107 NO
         2            1300660             1834            925702 ONLINE               107 NO
         3            1300660           923328            925702 ONLINE                28 NO
         4            1300660            16143            925702 ONLINE               106 NO
         5            1300660           952864            925702 ONLINE                24 NO

SYS@book> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode,current_scn FROM v$database;
SCN locatio NAME               CHECKPOINT_CHANGE# OPEN_MODE            CURRENT_SCN
----------- ------------------ ------------------ -------------------- -----------
controlfile SYSTEM checkpoint             1400000 MOUNTED                        0

--注意看文件没有改,而查询v$database,到open状态看看是否有效.

SYS@book> alter database open ;
Database altered.

SYS@book> select  dbms_flashback.get_system_change_number ,current_scn from v$database  ;
GET_SYSTEM_CHANGE_NUMBER CURRENT_SCN
------------------------ -----------
                 1400287     1400287

--ok已经生效.

SYS@book> @ &r/db_status
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
     FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
---------- ------------------ --------------------- ------------ --------------- -------------- -------
         1            1400003                     0                       925701         925702 SYSTEM
         2            1400003                     0                       925701         925702 ONLINE
         3            1400003                     0                       925701         925702 ONLINE
         4            1400003                     0                       925701         925702 ONLINE
         5            1400003                     0                       952864         952869 ONLINE

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy  FROM v$datafile_header;
     FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ
---------- ------------------ ---------------- ----------------- ------- ---------------- ---
         1            1400003                7            925702 ONLINE               108 YES
         2            1400003             1834            925702 ONLINE               108 YES
         3            1400003           923328            925702 ONLINE                29 YES
         4            1400003            16143            925702 ONLINE               107 YES
         5            1400003           952864            925702 ONLINE                25 YES

SYS@book> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode,current_scn FROM v$database;
SCN locatio NAME               CHECKPOINT_CHANGE# OPEN_MODE            CURRENT_SCN
----------- ------------------ ------------------ -------------------- -----------
controlfile SYSTEM checkpoint             1400003 READ WRITE               1400309

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1826199/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2488
  • 访问量
    6293338