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

[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

时间： 2024-10-12 01:24:05

[20151109]提升scn号11g测试.txt的相关文章

[20170419]关于块scn号.txt

[20170419]关于块scn号.txt --//数据块里面有许多scn号相关. --//数据块本身有三处记录的相应的SCN:数据块头的SCN(block scn).ktbbh结构下的 kscnbas,kscnwrp(cleanout scn).ITL信息中的 --//scn/fsc(commit scn 有时候会是control scn),有时候会存在一点点混乱,通过例子说明: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                

[20150129]关于取scn号.txt

[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

[20150624]提升scn.txt

[20150624]提升scn.txt --有时候修复数据库,要避免一些ora-00600错误,要提升数据库的scn.自己花了1点时间google许多资料,做1个总结: --安全起见,我做了一个冷备份.便于重复测试. --拿比较常见的ORA-600 [2662]错误: ORA-600 [2662] "Block SCN is ahead of Current SCN",说明当前数据库的数据块的SCN早于当前的SCN,主要是和存储在UGA变量中的 dependent SCN进行比较,如果

[20170428]延迟块清除测试.txt

[20170428]延迟块清除测试.txt --//做一个延迟块清除测试,前面的测试太乱了,思路非常不清楚. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ------------------------------------------------------------------

[20170302]异常恢复scn到那里3.txt

[20170302]异常恢复scn到那里3.txt --//如果oracle数据库异常关闭,打开数据库自动执行实例恢复,这个恢复scn到那里呢? --//通过例子说明:实际上http://blog.itpub.net/267265/viewspace-2134551/链接已经提到,重复测试: 1.环境: SYS@book> @ &r/ver BANNER ---------------------------------------------------------------------

[20120813]11GR2下flashback data archive的测试.txt

[20120813]11GR2下flashback data archive的测试.txt         oracle 11GR2有一个新特性,就是flashback data archive,就是通过一个表空间记录表的一些变化,查询历史数据.自己知道这个特性,从来也没有测试过.实际上这个就是flashback table的扩展(个人认为)!. 测试如下: 1.测试环境: SQL> select * from v$version ; BANNER -----------------------

[20140425]11GR2 truncate后恢复测试.txt

[20140425]11GR2 truncate后恢复测试.txt --做一个truncate后恢复测试在11GR2下,我的测试环境使用使用dataguard.备用库打开flashback. --可以利用在备用库flashback到truncate前,然后在传输到主库的方式看看. 1.建立测试环境: -- 我的测试环境:primary的tnsnames别名test,standby主机的tnsnames别名testdg.数据库版本:11GR2. -- 备用库打开在read only模式,并且rea

蜗牛移动恢复虚拟运营商号卡测试放号

5月28日消息,根据蜗牛移动的公告,5月28日零点,蜗牛移动999免卡正式测试放号,用户登录蜗牛移动官网(www.snail.com)即可照常选号,申请参与友好体验.蜗牛移动是最早宣布预约放号的联通虚拟运营商合作伙伴,但于5月21日被联通叫停,联通给出的理由是,联通与虚拟运营商合作伙伴签署过协议,虚拟运营商放号需要与联通双方进行书面确认,而蜗牛移动此次预约放号没有与联通进行书面确认.从蜗牛移动官网同时发布的<999免卡正式测试放号公告>中,可以看到本次测试放号,免卡预订价格依然为699元,但不

微信公众号默默测试评论功能，博客重生！

微信公众号默默测试评论功能,博客重生! 时间:2014-12-19 11:18 来源:百度百家 作者:魏武挥 摘要 : 如果将来微信公号是这样一种评论机制,运营能力的强调,将大幅超过对原创能力的强调. 这事空穴来风,有端倪可循. 一个叫"骑行西藏"的公众账号底下,已经出现了这个模块.这个账号据称是微信团队员工所为,过去经常会在这个账号下看到微信日后会推出的功能(比如阅读量这个功能骑行西藏就是首个测试号). 评论区会推出当无可疑.但这里依然个问题,就是评论的显示,以何机制运作? 第一个做