[20181123]快速提升scn注意.txt

[20181123]快速提升scn注意.txt

--//有时候修复oracle数据库,需要提升scn号,现在许多方法不能再用,最快的方式适用oradebug修改kcsgscn_地址内容.

--//今天做这方面测试遇到一些问题,应该引起注意,特别scn_wrap>0的情况下.做一个记录.

1.环境:

SYS@book> @ 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

SYS@book> set numw 12

SYS@book> select current_scn from v$database ;

 CURRENT_SCN

------------

 13813808388

SYS@book> @ tx 13813808388 32

select 13813808388,trunc(13813808388/power(2,32)) scn_wrap,mod(13813808388,power(2,32))  scn_base from dual

 13813808388     SCN_WRAP     SCN_BASE SCN_WRAP16 SCN_BASE16

------------ ------------ ------------ ---------- ----------

 13813808388            3    928906500          3   375dfd04

SYS@book> oradebug setmypid

Statement processed.

SYS@book> oradebug DUMPvar SGA kcsgscn_

kcslf kcsgscn_ [06001AE70, 06001AEA0) = 375DFD2F 00000003 00000000 00000000 00000063 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000

--//scn_wrap=3

2.千万不要一次修改完成.

--//实际上scn占48位.

SYS@book> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@book> startup mount

ORACLE instance started.

Total System Global Area    643084288 bytes

Fixed Size                    2255872 bytes

Variable Size               205521920 bytes

Database Buffers            427819008 bytes

Redo Buffers                  7487488 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

--//在mount状态看到是0.如果这样修改:

SYS@book> oradebug poke 0x06001AE70 8 0x375DFD2F00000003

BEFORE: [06001AE70, 06001AE78) = 00000000 00000000

AFTER:  [06001AE70, 06001AE78) = 00000003 375DFD2F

SYS@book> oradebug DUMPvar SGA kcsgscn_

kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000003 375DFD2F 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000

--//对比前面,可以发现实际上scn_wrap=0xFD2F,scn_base=0x00000003.这样scn提升太快了.

SYS@book> alter database open ;

alter database open

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 54638

Session ID: 274 Serial number: 3

--//数据库根本无法打开.alert.log报如下错误:

Completed: ALTER DATABASE   MOUNT

Fri Nov 23 10:41:19 2018

alter database open

Fri Nov 23 10:41:19 2018

LGWR: STARTING ARCH PROCESSES

Fri Nov 23 10:41:19 2018

ARC0 started with pid=22, OS id=54655

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Fri Nov 23 10:41:20 2018

ARC1 started with pid=23, OS id=54657

Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_lgwr_54618.trc  (incident=2281965):

ORA-00600: internal error code, arguments: [2252], [64815], [4], [3787], [3407085568], [], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/book/book/incident/incdir_2281965/book_lgwr_54618_i2281965.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_lgwr_54618.trc:

ORA-00600: internal error code, arguments: [2252], [64815], [4], [3787], [3407085568], [], [], [], [], [], [], []

LGWR (ospid: 54618): terminating the instance due to error 470

Fri Nov 23 10:41:20 2018

System state dump requested by (instance=1, osid=54618 (LGWR)), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/book/book/trace/book_diag_54604_20181123104120.trc

Dumping diagnostic data in directory=[cdmp_20181123104120], requested by (instance=1, osid=54618 (LGWR)), summary=[abnormal instance termination].

Instance terminated by LGWR, pid = 54618

3.必须分2次完成修改:

SYS@book> startup mount

ORACLE instance started.

Total System Global Area  643084288 bytes

Fixed Size                  2255872 bytes

Variable Size             205521920 bytes

Database Buffers          427819008 bytes

Redo Buffers                7487488 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 0x376DFD2F

BEFORE: [06001AE70, 06001AE74) = 00000000

AFTER:  [06001AE70, 06001AE74) = 376DFD2F

SYS@book> oradebug poke 0x06001AE74 2 0x0003

BEFORE: [06001AE74, 06001AE78) = 00000000

AFTER:  [06001AE74, 06001AE78) = 00000003

SYS@book> oradebug DUMPvar SGA kcsgscn_

kcslf kcsgscn_ [06001AE70, 06001AEA0) = 376DFD2F 00000003 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000

--//如下修改1个字节也可以.

SYS@book> oradebug poke 0x06001AE74 1 0x03

BEFORE: [06001AE74, 06001AE78) = 00000003

AFTER:  [06001AE74, 06001AE78) = 00000003

SYS@book> oradebug DUMPvar SGA kcsgscn_

kcslf kcsgscn_ [06001AE70, 06001AEA0) = 376DFD2F 00000003 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000

--//你可以发现等号后面实际上反过来排列,估计与intel endian有关.

--//补充一些测试:

SYS@book> oradebug poke 0x06001AE77 1 0x03

BEFORE: [06001AE74, 06001AE78) = 00000003

AFTER:  [06001AE74, 06001AE78) = 03000003

SYS@book> oradebug DUMPvar SGA kcsgscn_

kcslf kcsgscn_ [06001AE70, 06001AEA0) = 376DFD2F 03000003 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000

--//你可以发现0x06001AE77地址的内容,显示实际上开头.

SYS@book> oradebug poke 0x06001AE77 1 0x00

BEFORE: [06001AE74, 06001AE78) = 03000003

AFTER:  [06001AE74, 06001AE78) = 00000003

SYS@book> oradebug DUMPvar SGA kcsgscn_

kcslf kcsgscn_ [06001AE70, 06001AEA0) = 376DFD2F 00000003 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000

SYS@book> alter database open ;

Database altered.

SYS@book> set numw 12

SYS@book> select current_scn from v$database ;

 CURRENT_SCN

------------

 13814857318

--//实际上修改后仔细再看看,这些细节错误都很容易避免.

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