[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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2221324/