史上最全，呕心沥血总结oracle推进SCN方法（六）

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG数据库运维（如安装迁移，性能优化、故障应急处理等）
公众号：老苏畅谈运维
欢迎关注本人公众号，更多精彩与您分享。

前面介绍了4种推进SCN方法
（1）event 10015 来增加 scn 的值
（2）隐含参数_minimum_giga_scn 来增加 scn 的值
（3）gdb/dbx 来直接修改内存中的值
（4）使用oradebug poke 直接修改内存中的值
（5）通过修改控制文件来修改 scn 的值

现在来说一下oracle推进SCN方法6：通过修改数据文件头来修改 scn 的值

1、查看当前SCN

--最好将数据库启动到mount状态
SQL> startup mount;
ORACLE instance started.

Total System Global Area 3691200512 bytes
Fixed Size		    2258680 bytes
Variable Size		  788531464 bytes
Database Buffers	 2885681152 bytes
Redo Buffers		   14729216 bytes
Database mounted.
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
	   3433886
SQL> col name for a80
SQL> set linesize 400;
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
	 1 /u01/oradata/dbbbed/system01.dbf
	 2 /u01/oradata/dbbbed/sysaux01.dbf
	 3 /u01/oradata/dbbbed/undotbs01.dbf
	 4 /u01/oradata/dbbbed/users01.dbf
	 5 /u01/oradata/dbbbed/tbst01.dbf
	 6 /u01/oradata/dbbbed/szr01.dbf
	 7 /u01/oradata/dbbbed/mssm01.dbf
	 8 /u01/oradata/dbbbed/test0529.dbf

8 rows selected.

SQL> col name for a60;
set linesize 400;
select a.file#,
       a.name,
       (select checkpoint_change# from v$database) system_ckpt_scn,
       a.checkpoint_change# df_ckpt_scn,
       a.last_change# end_scn,
       b.checkpoint_change# start_scn,
       b.rSQL> SQL>   2    3    4    5    6    7  ecover,
       a.status
  from v$datafile a, v$datafile_header b
 where a.file# = b.file#;  8    9   10  

     FILE# NAME 							SYSTEM_CKPT_SCN DF_CKPT_SCN    END_SCN	START_SCN REC STATUS
---------- ------------------------------------------------------------ --------------- ----------- ---------- ---------- --- -------
	 1 /u01/oradata/dbbbed/system01.dbf					3433886     3433886    3433886	  3433886 NO  SYSTEM
	 2 /u01/oradata/dbbbed/sysaux01.dbf					3433886     3433886    3433886	  3433886 NO  ONLINE
	 3 /u01/oradata/dbbbed/undotbs01.dbf					3433886     3433886    3433886	  3433886 NO  ONLINE
	 4 /u01/oradata/dbbbed/users01.dbf					3433886     3433886    3433886	  3433886 NO  ONLINE
	 5 /u01/oradata/dbbbed/tbst01.dbf					3433886     3433886    3433886	  3433886 NO  ONLINE
	 6 /u01/oradata/dbbbed/szr01.dbf					3433886     3433886    3433886	  3433886 NO  ONLINE
	 7 /u01/oradata/dbbbed/mssm01.dbf					3433886     3433886    3433886	  3433886 NO  ONLINE
	 8 /u01/oradata/dbbbed/test0529.dbf					3433886     3433886    3433886	  3433886 NO  ONLINE

2、bbed修改文件头SCN

–将数据文件加入bbed filelist,使用bbed进行编辑

$ cat filelist.txt 
1	/u01/oradata/dbbbed/system01.dbf	775946240
2	/u01/oradata/dbbbed/sysaux01.dbf	513802240
3	/u01/oradata/dbbbed/undotbs01.dbf	78643200
4	/u01/oradata/dbbbed/users01.dbf	5242880
5	/u01/oradata/dbbbed/tbst01.dbf	10485760
6	/u01/oradata/dbbbed/szr01.dbf	10485760
7      /u01/oradata/dbbbed/mssm01.dbf   10485760
8      /u01/oradata/dbbbed/test0529.dbf 5242880

$ bbed

BBED: Release 2.0.0.0.0 - Limited Production on Thu Jun 6 11:01:03 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /u01/oradata/dbbbed/system01.dbf                                 94720
     2  /u01/oradata/dbbbed/sysaux01.dbf                                 62720
     3  /u01/oradata/dbbbed/undotbs01.dbf                                 9600
     4  /u01/oradata/dbbbed/users01.dbf                                    640
     5  /u01/oradata/dbbbed/tbst01.dbf                                    1280
     6  /u01/oradata/dbbbed/szr01.dbf                                     1280
     7  /u01/oradata/dbbbed/mssm01.dbf                                    1280
     8  /u01/oradata/dbbbed/test0529.dbf                                   640

–将所有数据文件头的scn由3433886改成4433886

SQL> select to_char(3433886,'xxxxxxxxxxx'),to_char(4433886,'xxxxxxxxxxxx') from dual;

TO_CHAR(3433 TO_CHAR(44338
------------ -------------
      34659e	    43a7de
BBED> set file 1 block 1
	FILE#          	1
	BLOCK#         	1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x0034659e
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x45cb01f0
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x00000007
         ub4 kcrbabno                       @504      0x00000223
         ub2 kcrbabof                       @508      0x0010
##注意上面的红色字体部分，我们需要将offset 484 这个地方的值改成0x0043a7de

BBED> assign dba 1,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas  = 0x0043a7de
或者直接用10进制的也行
BBED> assign dba 1,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas  = 4433886
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub4 kscnbas                                 @484      0x0043a7de

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x0043a7de
继续修改其他的数据文件
assign dba 2,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas  = 0x0043a7de
assign dba 3,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas  = 0x0043a7de
assign dba 4,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas  = 0x0043a7de
assign dba 5,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas  = 0x0043a7de
assign dba 6,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas  = 0x0043a7de
assign dba 7,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas  = 0x0043a7de
assign dba 8,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas  = 0x0043a7de

请添加图片描述
–保存修改

BBED> sum apply dba 1,1
Check value for File 1, Block 1:
current = 0x73a2, required = 0x73a2

BBED> sum apply dba 2,1
Check value for File 2, Block 1:
current = 0x06ea, required = 0x06ea

BBED> sum apply dba 3,1
Check value for File 3, Block 1:
current = 0x2aca, required = 0x2aca

BBED> sum apply dba 4,1
Check value for File 4, Block 1:
current = 0x44a3, required = 0x44a3

BBED> sum apply dba 5,1
Check value for File 5, Block 1:
current = 0x4dbe, required = 0x4dbe

BBED> sum apply dba 6,1
Check value for File 6, Block 1:
current = 0x4c4b, required = 0x4c4b

BBED> sum apply dba 7,1
Check value for File 7, Block 1:
current = 0x7012, required = 0x7012

BBED> sum apply dba 8,1
Check value for File 8, Block 1:
current = 0xa710, required = 0xa710

3、查看修改后的SCN

--打开数据库
SQL> alter database open;

Database altered.

--查看数据文件头的scn
SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
	   4433887
	   4433887
	   4433887
	   4433887
	   4433887
	   4433887
	   4433887
	   4433887

8 rows selected.

SQL> col name for a60;
set linesize 400;
select a.file#,
       a.name,
       (select checkpoint_change# from v$database) system_ckpt_scn,
       a.checkpoint_change# df_ckpt_scn,
       a.last_change# end_scn,
       b.checkpoint_change# start_scn,
       b.rSQL> SQL>   2    3    4    5    6    7  ecover,
       a.status
  from v$datafile a, v$datafile_header b
 where a.file# = b.file#;  8    9   10  

     FILE# NAME 							SYSTEM_CKPT_SCN DF_CKPT_SCN    END_SCN	START_SCN REC STATUS
---------- ------------------------------------------------------------ --------------- ----------- ---------- ---------- --- -------
	 1 /u01/oradata/dbbbed/system01.dbf					4433887     4433887		  4433887 NO  SYSTEM
	 2 /u01/oradata/dbbbed/sysaux01.dbf					4433887     4433887		  4433887 NO  ONLINE
	 3 /u01/oradata/dbbbed/undotbs01.dbf					4433887     4433887		  4433887 NO  ONLINE
	 4 /u01/oradata/dbbbed/users01.dbf					4433887     4433887		  4433887 NO  ONLINE
	 5 /u01/oradata/dbbbed/tbst01.dbf					4433887     4433887		  4433887 NO  ONLINE
	 6 /u01/oradata/dbbbed/szr01.dbf					4433887     4433887		  4433887 NO  ONLINE
	 7 /u01/oradata/dbbbed/mssm01.dbf					4433887     4433887		  4433887 NO  ONLINE
	 8 /u01/oradata/dbbbed/test0529.dbf					4433887     4433887		  4433887 NO  ONLINE

8 rows selected.

--查看数据库的scn
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    4434186

可以看到，SCN已修改，成功推进SCN。

链接：
史上最全，呕心沥血总结oracle推进SCN方法（一）
史上最全，呕心沥血总结oracle推进SCN方法（二）
史上最全，呕心沥血总结oracle推进SCN方法（三）
史上最全，呕心沥血总结oracle推进SCN方法（四）
史上最全，呕心沥血总结oracle推进SCN方法（五）

请添加图片描述