[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