ITPub博客

首页 > 数据库 > Oracle > [20150804]提升scn3.txt

[20150804]提升scn3.txt

原创 Oracle 作者:lfree 时间:2015-08-05 09:21:05 0 删除 编辑

[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.

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

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

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6292103