[20150804]提升scn3.txt
--上午做了做了提升scn的试验，在10g下费劲周折到下午才测试通过。参考连接。
http://blog.itpub.net/267265/viewspace-1761508/
--下午在11g下测试看看。
1.测试环境：
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 1006636496 bytes
Database Buffers 587202560 bytes
Redo Buffers 7344128 bytes
Database mounted.
SYS@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SYS@test> 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 12718739566 0 12718739566 0 0 SYSTEM
2 12718739566 0 12718739566 0 0 ONLINE
3 12718739566 0 12718739566 0 0 ONLINE
4 12718739566 0 12718739566 11991583673 11991583744 ONLINE
5 12718739566 0 12718739566 0 0 ONLINE
6 12718739566 0 12718739566 11992552382 11992552417 ONLINE
7 12718739566 0 12718739566 0 0 ONLINE
8 12718739566 0 12718739566 11736877818 11736877876 ONLINE
9 12718739566 0 12718739566 0 0 ONLINE
10 12718739566 0 12718739566 0 0 ONLINE
11 12718739566 0 12718739566 11737627980 11737628036 ONLINE
12 12718739566 0 12718739566 0 0 ONLINE
13 12718739566 0 12718739566 12688348217 12688352040 ONLINE
14 12718739566 0 12718739566 0 0 ONLINE
14 rows selected.
SYS@test> 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 12718739566 7 3011113647 ONLINE 856623668 NO
2 12718739566 2140 3011113647 ONLINE 856623662 NO
3 12718739566 3241444492 3011113647 ONLINE 856623663 NO
4 12718739566 17993 3011113647 ONLINE 856623687 NO
5 12718739566 973735 3011113647 ONLINE 856623665 NO
6 12718739566 1412559 3011113647 ONLINE 856623631 NO
7 12718739566 4383251 3011113647 ONLINE 856623660 NO
8 12718739566 13169364 3011113647 ONLINE 856623663 NO
9 12718739566 3223747107 3011113647 ONLINE 856623660 NO
10 12718739566 12002485849 3011113647 ONLINE 1179 NO
11 12718739566 11673111577 3011113647 ONLINE 2145 NO
12 12718739566 11994962958 3011113647 ONLINE 1504 NO
13 12718739566 11992635787 3011113647 ONLINE 1618 NO
14 12718739566 11992670578 3011113647 ONLINE 1614 NO
14 rows selected.
SYS@test> 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 12718739566 MOUNTED 0
SYS@test> @10to16 12718739566
10 to 16 HEX REVERSE16
-------------- -----------------------------------
00002f618906e 0x6e9018f6-02000000
SYS@test> show parameter control_files
NAME TYPE VALUE
-------------- ------- ----------------------------------------------------------------------------------------------------
control_files string /u01/app/oracle11g/oradata/test/control01.ctl, /u01/app/oracle11g/oradata/test/control02.ctl
SYS@test> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
2.开始修改控制文件:
--使用bvi修改:
\6e 90 18 f6
00044000 15 C2 00 00 11 00 00 00 60 3D 11 33 FF FF 01 04 59 64 00 00 00 00 00 00 00 00 00 00 CD 2F B7 2F 54 45 53 54 00 00 00 00 00 00 00 00 08 02 40 00 01 40 40 50 00 00 00 00 00 00 00 00 AF F2 79 B3
00044040 00 00 00 00 48 EF 98 2F AD 36 F1 00 00 00 00 00 A7 DE 24 2F 00 00 00 00 00 00 20 0B 0E 00 00 00 0E 00 00 00 01 00 00 00 6E 90 18 F6 02 00 00 00 01 00 00 00 00 00 00 00 02 00 00 00 00 00 00 00
~~~~~~~~~~~
00044080 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 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 00 00 00 00 00 00 00 00
000440C0 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 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 00 00 00 00 03 00 01 00
--修改为6F 91 19 f7。
--使用bbed修改检查和。
SYS@test> @16to10 44000
16 to 10 DEC
------------
278528
SYS@test> select 278528/16384 from dual ;
278528/16384
------------
17
BBED> set dba 20,17
DBA 0x05000011 (83886097 20,17)
BBED> set count 16384
COUNT 16384
BBED> set count 256
COUNT 256
BBED> set blocksize 16384
BLOCKSIZE 16384
BBED> dump /v
File: /u01/app/oracle11g/oradata/test/control01.ctl (20)
Block: 17 Offsets: 0 to 255 Dba:0x05000011
---------------------------------------------------------------------------------------------------------------------------------------------------------------
15c20000 11000000 603d1133 ffff0104 59640000 00000000 00000000 cd2fb72f 54455354 00000000 00000000 08024000 l ........`=.3....Yd..........././TEST..........@.
01404050 00000000 00000000 aff279b3 00000000 48ef982f ad36f100 00000000 a7de242f 00000000 0000200b 0e000000 l .@@P..........y.....H../.6........$/...... .....
0e000000 01000000 6f9119f7 02000000 01000000 00000000 02000000 00000000 00000000 00000000 00000000 00000000 l ........o.......................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................................
00000000 00000000 00000000 03000100 l ................
<48 bytes per line>
BBED> sum
Check value for File 20, Block 17:
current = 0x6459, required = 0x6459
--不需要修改。注意我修改正好巧合，如果全部偶数修改为奇数，+1就没有问题。
3.ok，打开数据库到mount:
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 973082064 bytes
Database Buffers 620756992 bytes
Redo Buffers 7344128 bytes
Database mounted.
SYS@test> 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 12718739566 0 12718739566 0 0 SYSTEM
2 12718739566 0 12718739566 0 0 ONLINE
3 12718739566 0 12718739566 0 0 ONLINE
4 12718739566 0 12718739566 11991583673 11991583744 ONLINE
5 12718739566 0 12718739566 0 0 ONLINE
6 12718739566 0 12718739566 11992552382 11992552417 ONLINE
7 12718739566 0 12718739566 0 0 ONLINE
8 12718739566 0 12718739566 11736877818 11736877876 ONLINE
9 12718739566 0 12718739566 0 0 ONLINE
10 12718739566 0 12718739566 0 0 ONLINE
11 12718739566 0 12718739566 11737627980 11737628036 ONLINE
12 12718739566 0 12718739566 0 0 ONLINE
13 12718739566 0 12718739566 12688348217 12688352040 ONLINE
14 12718739566 0 12718739566 0 0 ONLINE
14 rows selected.
SYS@test> 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 12718739566 7 3011113647 ONLINE 856623668 NO
2 12718739566 2140 3011113647 ONLINE 856623662 NO
3 12718739566 3241444492 3011113647 ONLINE 856623663 NO
4 12718739566 17993 3011113647 ONLINE 856623687 NO
5 12718739566 973735 3011113647 ONLINE 856623665 NO
6 12718739566 1412559 3011113647 ONLINE 856623631 NO
7 12718739566 4383251 3011113647 ONLINE 856623660 NO
8 12718739566 13169364 3011113647 ONLINE 856623663 NO
9 12718739566 3223747107 3011113647 ONLINE 856623660 NO
10 12718739566 12002485849 3011113647 ONLINE 1179 NO
11 12718739566 11673111577 3011113647 ONLINE 2145 NO
12 12718739566 11994962958 3011113647 ONLINE 1504 NO
13 12718739566 11992635787 3011113647 ONLINE 1618 NO
14 12718739566 11992670578 3011113647 ONLINE 1614 NO
14 rows selected.
SYS@test> 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 12718739566 MOUNTED 0
--还是不行,为什么？
BBED> set dba 20,18
DBA 0x05000012 (83886098 20,18)
BBED> set count 256
COUNT 256
BBED> set blocksize 16384
BLOCKSIZE 16384
BBED> dump /v
File: /u01/app/oracle11g/oradata/test/control01.ctl (20)
Block: 18 Offsets: 0 to 255 Dba:0x05000012
---------------------------------------------------------------------------------------------------------------------------------------------------------------
15c20000 12000000 6d3d1133 ffff0104 5a640000 00000000 00000000 cd2fb72f 54455354 00000000 00000000 08024000 l ........m=.3....Zd..........././TEST..........@.
01404050 00000000 00000000 aff279b3 00000000 48ef982f ad36f100 00000000 a7de242f 00000000 0000200b 0e000000 l .@@P..........y.....H../.6........$/...... .....
0e000000 01000000 6f9119f7 02000000 01000000 00000000 02000000 00000000 00000000 00000000 00000000 00000000 l ........o.......................................
~~~~~~~~
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................................
00000000 00000000 00000000 03000100 l ................
<48 bytes per line>
BBED> sum
Check value for File 20, Block 18:
current = 0x645a, required = 0x645a
--难道要修改2处?以上是已经修改的(我喜欢使用bvi修改)
SYS@test> 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 12718739566 0 12718739566 0 0 SYSTEM
2 12718739566 0 12718739566 0 0 ONLINE
3 12718739566 0 12718739566 0 0 ONLINE
4 12718739566 0 12718739566 11991583673 11991583744 ONLINE
5 12718739566 0 12718739566 0 0 ONLINE
6 12718739566 0 12718739566 11992552382 11992552417 ONLINE
7 12718739566 0 12718739566 0 0 ONLINE
8 12718739566 0 12718739566 11736877818 11736877876 ONLINE
9 12718739566 0 12718739566 0 0 ONLINE
10 12718739566 0 12718739566 0 0 ONLINE
11 12718739566 0 12718739566 11737627980 11737628036 ONLINE
12 12718739566 0 12718739566 0 0 ONLINE
13 12718739566 0 12718739566 12688348217 12688352040 ONLINE
14 12718739566 0 12718739566 0 0 ONLINE
14 rows selected.
SYS@test> 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 12718739566 7 3011113647 ONLINE 856623668 NO
2 12718739566 2140 3011113647 ONLINE 856623662 NO
3 12718739566 3241444492 3011113647 ONLINE 856623663 NO
4 12718739566 17993 3011113647 ONLINE 856623687 NO
5 12718739566 973735 3011113647 ONLINE 856623665 NO
6 12718739566 1412559 3011113647 ONLINE 856623631 NO
7 12718739566 4383251 3011113647 ONLINE 856623660 NO
8 12718739566 13169364 3011113647 ONLINE 856623663 NO
9 12718739566 3223747107 3011113647 ONLINE 856623660 NO
10 12718739566 12002485849 3011113647 ONLINE 1179 NO
11 12718739566 11673111577 3011113647 ONLINE 2145 NO
12 12718739566 11994962958 3011113647 ONLINE 1504 NO
13 12718739566 11992635787 3011113647 ONLINE 1618 NO
14 12718739566 11992670578 3011113647 ONLINE 1614 NO
14 rows selected.
SYS@test> 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 12735582575 MOUNTED 0
SYS@test> @16to10 2f719916f
16 to 10 DEC
------------
12735582575
--这次正确了。
-- 增加了 12735582575-12718739566=16843009
SYS@test> alter database open ;
Database altered.
SYS@test> 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 12735582578 READ WRITE 12735582968
--ok没有认为问题。
4.重复测试:
--重复测试要修改前面2处，检查和要清除3位(加前面1位0x40)，也可以使用bbed 计算，注意控制文件的块大小16384.
