[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

--//實際上修改後仔細再看看,這些細節錯誤都很容易避免.