admin – 第12页 – Database SOS

ORA-00600 dbkif_find_next_record_1

数据库版本信息

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

数据库启动报ORA-00600 dbkif_find_next_record_1错误

alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
Started redo scan
Completed redo scan
 read 133 KB redo, 72 data blocks need recovery
Started redo application at
 Thread 1: logseq 49070, block 14720
Recovery of Online Redo Log: Thread 1 Group 2 Seq 49070 Reading mem 0
  Mem# 0: D:\APP\xff\ORADATA\ORCL\REDO02.LOG
Completed redo application of 0.09MB
Errors in file d:\app\xff\diag\rdbms\orcl\orcl\trace\orcl_ora_2340.trc  (incident=477756):
ORA-00600: ??????, ??: [dbkif_find_next_record_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\xff\diag\rdbms\orcl\orcl\incident\incdir_477756\orcl_ora_2340_i477756.trc
Wed Sep 26 10:52:07 2018
Trace dumping is performing id=[cdmp_20180926105207]
Wed Sep 26 10:52:08 2018
Aborting crash recovery due to error 600
Errors in file d:\app\xff\diag\rdbms\orcl\orcl\trace\orcl_ora_2340.trc:
ORA-00600: ??????, ??: [dbkif_find_next_record_1], [], [], [], [], [], [], [], [], [], [], []
Errors in file d:\app\xff\diag\rdbms\orcl\orcl\trace\orcl_ora_2340.trc:
ORA-00600: ??????, ??: [dbkif_find_next_record_1], [], [], [], [], [], [], [], [], [], [], []
ORA-600 signalled during: alter database open...

这里比较明显，数据库是在做实例恢复的时候遭遇到ORA-600 dbkif_find_next_record_1错误,无法正常应用日志导致该错误,具体原因由于:the NAB and finds it is less than the block# from the on-disk-RBA recorded，对于这种问题，通过人工修改next available block# in the Online Redo Log的相关记录,即可正常open数据库,而且理论上数据0丢失

ORA-07445 qcdlgcd

SQL执行报ORA-03113错误

SQL> SELECT *
  FROM USER.TABLE_NAME t
 WHERE t.YEAR = '2019'
   AND t.UPPCODE = '51010000'
   AND  t.MONTH = '01';
  2    3    4    5
SELECT *
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 68389
Session ID: 2419 Serial number: 34370

数据库版本等信息

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /oracle/product/db12cr1
System name:	Linux
Node name:	Tkfcsdb
Release:	2.6.32-431.el6.x86_64
Version:	#1 SMP Sun Nov 10 22:19:54 EST 2013
Machine:	x86_64

alert日志报错

Thu Sep 27 17:41:15 2018
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0xCDFB0C6, qcdlgcd()+70] [flags: 0x0, count: 1]
Errors in file /oracle/diag/rdbms/tst12uf/tst12uf/trace/tst12uf_ora_234433.trc  (incident=126129) (PDBNAME=PTST12UF):
ORA-07445:exception encountered:core dump[qcdlgcd()+70][SIGSEGV][ADDR:0x4][PC:0xCDFB0C6][Address not mapped to object][]
Incident details in: /oracle/diag/rdbms/tst12uf/tst12uf/incident/incdir_126129/tst12uf_ora_234433_i126129.trc
Use ADRCI or Support Workbench to package the incident.

trace文件

----- Beginning of Customized Incident Dump(s) -----
Dumping swap information
Memory (Avail / Total) = 948.73M / 63736.63M
Swap (Avail / Total) = 30025.52M /  32255.99M
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0xCDFB0C6, qcdlgcd()+70] [flags: 0x0, count: 1]
Registers:
%rax: 0x0000000000000000 %rbx: 0x000000020c3da3f0 %rcx: 0x00007fc492268a40
%rdx: 0x00007fc49239eab0 %rdi: 0x000000022cd5da20 %rsi: 0x00007fc492f2bc80
%rsp: 0x00007fffbd9c9430 %rbp: 0x00007fffbd9c94a0  %r8: 0x00000002372efcb8
 %r9: 0x000000022cd5da20 %r10: 0x00007fc49239eab0 %r11: 0x00007fc492268a40
%r12: 0x000000022cd5da20 %r13: 0x00007fc492f2bc80 %r14: 0x00007fc49239eab0
%r15: 0x00007fc492268a40 %rip: 0x000000000cdfb0c6 %efl: 0x0000000000010206
  qcdlgcd()+53 (0xcdfb0b5) mov %rdi,%r12
  qcdlgcd()+56 (0xcdfb0b8) mov 0x10(%rax),%rbx
  qcdlgcd()+60 (0xcdfb0bc) jz 0xcdfb29e
  qcdlgcd()+66 (0xcdfb0c2) mov 0x60(%r15),%rax
> qcdlgcd()+70 (0xcdfb0c6) movzwl 0x4(%rax),%r8d
  qcdlgcd()+75 (0xcdfb0cb) cmp $30,%r8d
  qcdlgcd()+79 (0xcdfb0cf) jnle 0xcdfb277
  qcdlgcd()+85 (0xcdfb0d5) pxor %xmm0,%xmm0
  qcdlgcd()+89 (0xcdfb0d9) movaps %xmm0,-0x40(%rbp)
*** 2018-09-27 17:41:15.110
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
[TOC00004]
----- Current SQL Statement for this session (sql_id=52chysuyh36t4) -----
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune
no_monitoring optimizer_features_enable(default)
no_parallel result_cache(snapshot=3600) */
SUM(C1) FROM (SELECT /*+ qb_name("innerQuery")
NO_INDEX_FFS( "T")  */ 1 AS C1 FROM "USER"."TABLE_NAME" "T"
WHERE ("T"."MONTH"='01') AND ("T"."UPPCODE"='51010000') AND ("T"."YEAR"='2019')) innerQuery
[TOC00004-END]
[TOC00005]
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+45        call     kgdsdst()            7FC492BAB678 000000003
                                                   7FC492B8D0B0 ? 7FC492B8D1C8 ?
                                                   7FC492BAAEA8 ? 000000083 ?
ksedst()+119         call     skdstdst()           7FC492BAB678 000000001
                                                   000000001 7FC492B8D1C8 ?
                                                   7FC492BAAEA8 ? 000000083 ?
dbkedDefDump()+1119  call     ksedst()             000000001 000000001 ?
                                                   000000001 ? 7FC492B8D1C8 ?
                                                   7FC492BAAEA8 ? 000000083 ?
ksedmp()+261         call     dbkedDefDump()       000000003 000000003
                                                   000000001 ? 7FC492B8D1C8 ?
                                                   7FC492BAAEA8 ? 000000083 ?
ssexhd()+2650        call     ksedmp()             00000044F 000000003 ?
                                                   000000001 ? 000000003
                                                   7FC492BAAEA8 ? 000000083 ?
sslsshandler()+456   call     ssexhd()             202F206C69617641
                                                   3D20296C61746F54
                                                   4D33372E38343920
                                                   3633373336202F20
                                                   6177530A4D33362E 000000083 ?
__sighandler()       call     sslsshandler()       000002000 000000000 000000000
                                                   3633373336202F20 ?
                                                   6177530A4D33362E ?
                                                   000000083 ?
qcdlgcd()+70         signal   __sighandler()       22CD5DA20 7FC492F2BC80
                                                   7FC49239EAB0 7FC492268A40 ?
                                                   2372EFCB8 ? 22CD5DA20 ?
kkdlgcd()+118        call     qcdlgcd()            22CD5DA20 ? 7FC492F2BC80 ?
                                                   7FC49239EAB0 ? 7FC492268A40 ?
                                                   2372EFCB8 ? 22CD5DA20 ?
kkmfbtic()+17        call     kkdlgcd()            7FC49239EAB0 ? 7FC492268A40 ?
                                                   7FC49239EAB0 ? 7FC492268A40 ?
                                                   2372EFCB8 ? 22CD5DA20 ?
qcsgcic()+163        call     kkmfbtic()           7FC49239EAB0 ? 7FC492268A40 ?
                                                   7FC49239EAB0 ? 7FC492268A40 ?
                                                   2372EFCB8 ? 22CD5DA20 ?
kkmgkc()+147         call     qcsgcic()            22CD5DA20 ? 7FC492F2BC80 ?
                                                   7FC49239EAB0 ? 7FC492268A40 ?
                                                   000000007 22CD5DA20 ?
kokscit()+65         call     kkmgkc()             22CD5DA20 ? 7FC492F2BC80 ?
                                                   7FC49239EAB0 ? 7FC492268A40 ?
                                                   000000007 ? 22CD5DA20 ?
qkebCreateColById()  call     kokscit()            22CD5DA20 ? 7FC492F2BC80 ?
+298                                               7FC49239EAB0 ? 7FC492268A40 ?
                                                   000000007 ? 22CD5DA20 ?
qksvcGetGuardCol()+  call     qkebCreateColById()  000000002 7FC4923A4078
161                                                7FC49239EAB0 ? 000000007 ?
                                                   7FC492268A40 ? 22CD5DA20 ?
qksvcProcessVCColum  call     qksvcGetGuardCol()   7FC49239D9D8 ? 7FC4923A4078 ?
ns()+1625                                          7FC49239EAB0 ? 000000007 ?
                                                   7FC492268A40 ? 22CD5DA20 ?
qkacol()+405         call     qksvcProcessVCColum  000000000 ? 000000000 ?
                              ns()                 7FC49239EAB0 ? 000000007 ?
                                                   7FC492268A40 ? 22CD5DA20 ?
qkadrv()+933         call     qkacol()             7FC49239EAB0 ? 000000000 ?
                                                   7FC49239EAB0 ? 000000007 ?
                                                   7FC492268A40 ? 22CD5DA20 ?
opitca()+2417        call     qkadrv()             7FC4923A4078 ? 000000001 ?
                                                   7FC49239EAB0 ? 000000007 ?
                                                   7FC492268A40 ? 22CD5DA20 ?
kksFullTypeCheck()+  call     opitca()             7FC4922F4BD0 22CD5DC40
79                                                 7FFFBD9CF3B0 000000007 ?
                                                   7FC492268A40 ? 22CD5DA20 ?
rpiswu2()+2235       call     kksFullTypeCheck()   7FFFBD9CDF08 ? 22CD5DC40 ?
                                                   7FFFBD9CF3B0 ? 000000007 ?
                                                   7FC492268A40 ? 22CD5DA20 ?
kksLoadChild()+7590  call     rpiswu2()            7FFFBD9CDF08 ? 22CD5DC40 ?
                                                   7FFFBD9CF3B0 ? 000000008 ?
                                                   7FC492F5D260 ? 22CD5DA20 ?
kxsGetRuntimeLock()  call     kksLoadChild()       7FFFBD9CDF08 ? 22CD5DC40 ?
+2155                                              000000000 000000008 ?
                                                   7FC492F5D260 ? 22CD5DA20 ?
kksfbc()+14306       call     kxsGetRuntimeLock()  7FC492F2BC80 7FC4922F4BD0
                                                   7FFFBD9CF330 22D1E7C28 ?
                                                   7FC492F5D260 ? 22D1E7C28
kkspsc0()+3146       call     kksfbc()             7FC4922F4BD0 7FC4922F4BD0 ?
                                                   7FFFBD9CF330 ? 22D1E7C28 ?
                                                   7FC492F5D260 ? 22D1E7C28 ?
kksParseCursor()+11  call     kkspsc0()            7FC4928321A0 7FC49235D198
8                                                  00000015F 000000003 000000006
                                                   000000020
opiosq0()+2210       call     kksParseCursor()     7FFFBD9D0178 ? 7FC49235D198 ?
                                                   00000015F ? 000000003 ?
                                                   000000006 ? 000000020 ?
opiall0()+4530       call     opiosq0()            000000003 7FC49235D198 ?
                                                   7FC492F5D260 ? 000000020
                                                   000000000 000000020 ?
opikpr()+567         call     opiall0()            000000003 ? 000000022
                                                   7FFFBD9D0A50 000000000
                                                   000000000 ? 000000020 ?
opiodr()+1165        call     opikpr()             000000065 ? 000000022 ?
                                                   7FFFBD9D22F0 000000000 ?
                                                   000000000 ? 000000020 ?
rpidrus()+206        call     opiodr()             000000065 00000001F
                                                   7FFFBD9D22F0 ? 000000000 ?
                                                   000000000 ? 100000001
skgmstack()+144      call     rpidrus()            7FFFBD9D1B70 00000001F ?
                                                   7FC492F2BE78 000000000 ?
                                                   000000000 ? 100000001 ?
rpiswu2()+723        call     skgmstack()          7FFFBD9D1B48 ? 7FC492F2B7A0 ?
                                                   00000F618 ? 00CBFE570 ?
                                                   7FFFBD9D1B70 ? 100000001 ?
kprball()+1163       call     rpiswu2()            7FFFBD9D1B48 ? 7FC492F2B7A0 ?
                                                   00000F618 ? 000000002 ?
                                                   7FC492F5D260 ? 100000001 ?
qksdsExeStmt()+2411  call     kprball()            7FFFBD9D22F0 004000180
                                                   00000F618 ? 000000002 ?
                                                   7FC492F5D260 ? 100000001 ?
qksdsExecute()+959   call     qksdsExeStmt()       7FFFBD9D2B98 7FC49235D150
                                                   000000001 000000008
                                                   7FFFBD9D28C8 100000001 ?
kkoatVerifyEst()+30  call     qksdsExecute()       7FFFBD9D2B98 7FC49235D150 ?
85                                                 000000001 ? 000000008 ?
                                                   7FFFBD9D28C8 ? 100000001 ?
kkeAdjSingTabCard()  call     kkoatVerifyEst()     300000001 7FC4923E9DA8
+714                                               1EE909D75846 7FC49235D108
                                                   000000000 100000001 ?
kkecdn()+3803        call     kkeAdjSingTabCard()  7FC4923E9DA8 7FC4923EA328
                                                   7FFFBD9D2E30 ? 7FC49235D108 ?
                                                   000000000 ? 100000001 ?
kkotap()+13019       call     kkecdn()             7FC4923ECF08 000000003 ?
                                                   7FFFBD9D2E30 ? 7FC4923EA328 ?
                                                   000000000 ? 100000001 ?
kkoiqb()+8331        call     kkotap()             7FC492F5D260 ? 000000000 ?
                                                   000000011 000000000 000000000
                                                   100000001 ?
kkooqb()+532         call     kkoiqb()             000000000 ? 000000000
                                                   000000000 000000000 ?
                                                   000000000 ? 100000001 ?
kkoqbc()+2385        call     kkooqb()             7FC4927F8BC0 ? 000000006
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 100000001 ?
apakkoqb()+182       call     kkoqbc()             7FC4927F8BC0 ? 7FC4927F8BC0 ?
                                                   000000001 000000000 ?
                                                   000000000 ? 100000001 ?
apaqbdDescendents()  call     apakkoqb()           000000000 ? 7FC4927F8BC0 ?
+488                                               21EE306B8 ? 000000000 ?
                                                   000000000 ? 100000001 ?
apadrv()+5383        call     apaqbdDescendents()  7FFFBD9DC2B0 ? 7FC4927F8BC0 ?
                                                   21EE306B8 ? 000000000 ?
                                                   000000000 ? 100000001 ?
opitca()+2106        call     apadrv()             21EE306B8 ? 7FC4927F8BC0 ?
                                                   21EE306B8 ? 000000000 ?
                                                   000000000 ? 100000001 ?
kksLoadChild()+7318  call     opitca()             7FC492845648 21EE306B8
                                                   7FFFBD9DE6F0 000000000 ?
                                                   000000000 ? 100000001 ?
kxsGetRuntimeLock()  call     kksLoadChild()       7FC492845648 ? 21EE306B8 ?
+2155                                              000000000 000000000 ?
                                                   000000000 ? 100000001 ?
kksfbc()+14306       call     kxsGetRuntimeLock()  7FC492F2BC80 7FC492845648
                                                   7FFFBD9DE670 232AE3E98 ?
                                                   000000000 ? 232AE3E98
kkspsc0()+3146       call     kksfbc()             7FC492845648 7FC492845648 ?
                                                   7FFFBD9DE670 ? 232AE3E98 ?
                                                   000000000 ? 232AE3E98 ?
kksParseCursor()+11  call     kkspsc0()            7FC492832108 7FFFBD9E0A28
8                                                  000000081 000000003 000000006
                                                   0000000A4
opiosq0()+2210       call     kksParseCursor()     7FFFBD9DF4B8 ? 7FFFBD9E0A28 ?
                                                   000000081 ? 000000003 ?
                                                   000000006 ? 0000000A4 ?
kpoal8()+1223        call     opiosq0()            000000003 7FFFBD9E0A28 ?
                                                   7FC492F5D260 ? 0000000A4
                                                   000000000 0000000A4 ?
opiodr()+1165        call     kpoal8()             00000005E 00000001F
                                                   7FFFBD9E3278 0000000A4 ?
                                                   000000000 ? 0000000A4 ?
ttcpip()+2699        call     opiodr()             00000005E 00000001F
                                                   7FFFBD9E3278 ? 0000000A4 ?
                                                   000000000 ? 100000000
opitsk()+1734        call     ttcpip()             7FC492F41070 ? 00000005E ?
                                                   7FFFBD9E3278 000000000 ?
                                                   7FFFBD9E2CD8 7FFFBD9E3484
opiino()+945         call     opitsk()             000000400 000000000
                                                   7FFFBD9E3278 ? 000000000 ?
                                                   7FFFBD9E2CD8 ? 7FFFBD9E3484 ?
opiodr()+1165        call     opiino()             00000003C 000000004
                                                   7FFFBD9E4918 000000000 ?
                                                   7FFFBD9E2CD8 ? 7FFFBD9E3484 ?
opidrv()+587         call     opiodr()             00000003C 000000004
                                                   7FFFBD9E4918 ? 000000000 ?
                                                   7FFFBD9E2CD8 ? 000000000
sou2o()+145          call     opidrv()             00000003C 000000004
                                                   7FFFBD9E4918 000000000 ?
                                                   7FFFBD9E2CD8 ? 000000000 ?
opimai_real()+154    call     sou2o()              7FFFBD9E48F0 00000003C
                                                   000000004 7FFFBD9E4918
                                                   7FFFBD9E2CD8 ? 000000000 ?
ssthrdmain()+412     call     opimai_real()        000000000 7FFFBD9E4C00
                                                   000000004 ? 7FFFBD9E4918 ?
                                                   7FFFBD9E2CD8 ? 000000000 ?
main()+236           call     ssthrdmain()         000000000 000000002
                                                   7FFFBD9E4C00 000000001
                                                   000000000 000000000 ?
__libc_start_main()  call     main()               7FFFBD9E559F 7FFFBD9E55AD
+253                                               7FFFBD9E4C00 ? 000000001 ?
                                                   000000000 ? 000000000 ?
_start()+41          call     __libc_start_main()  000BBD640 000000002
                                                   7FFFBD9E4E48 000000000 ?
                                                   000000000 ? 000000000 ?
[TOC00005-END]
[TOC00006]
--------------------- Binary Stack Dump ---------------------

解决方法
这里比较明显,我们可以发现这个sql执行报错，其实本质是由于递归执行动态采样报错而引起异常的.通过设置_fix_control来规避这个问题

SQL> alter session set "_fix_control"='14191778:0';
Session altered.
SQL> SELECT *
  FROM USER.TABLE_NAME t
 WHERE t.YEAR = '2019'
   AND t.UPPCODE = '51010000'
   AND  t.MONTH = '01';
  2    3    4    5
	ID EMPCODE		COMCODE 	     YEAR	QUARTER
---------- -------------------- -------------------- ---------- ----------
PUSHRATIO  D A CREATECODE	    UPDATECODE		 CREATEDATE
---------- - - -------------------- -------------------- -------------------
UPDATEDATE	    UPPCODE		 CLAIM		      MONTH
------------------- -------------------- -------------------- ----------
     77813 5101000166		51010702	     2019	1
50	   0 0 1777					 2018-07-18 17:44:19
		    51010000		 50		      01
     77909 8000545245		51010702	     2019	1
0	   0 0 1777		    1777		 2018-07-18 17:35:12
2018-07-18 17:41:20 51010000		 100		      01
	ID EMPCODE		COMCODE 	     YEAR	QUARTER
---------- -------------------- -------------------- ---------- ----------
PUSHRATIO  D A CREATECODE	    UPDATECODE		 CREATEDATE
---------- - - -------------------- -------------------- -------------------
UPDATEDATE	    UPPCODE		 CLAIM		      MONTH
------------------- -------------------- -------------------- ----------
     77912 5101000047		51010702	     2019	1
0	   0 0 1777		    1777		 2018-07-18 17:41:03
2018-07-18 17:44:28 51010000		 100		      01
SQL> SQL>

ORA-600 kddummy_blkchk导致数据库无法open

数据库启动报ORA-600 kddummy_blkchk

Sun Sep  9 21:45:28 2018
SMON: enabling tx recovery
Sun Sep  9 21:45:28 2018
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 24
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=35, OS id=53346452
Sun Sep  9 21:45:28 2018
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_smon_39714888.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [609], [6], [18018], [], [], [], []
Sun Sep  9 21:45:28 2018
Completed: ALTER DATABASE OPEN
Sun Sep  9 21:45:28 2018
db_recovery_file_dest_size of 89900 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Sun Sep  9 21:45:29 2018
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_smon_39714888.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kddummy_blkchk], [609], [6], [18018], [], [], [], []
Sun Sep  9 21:45:32 2018
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_smon_39714888.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [609], [6], [18018], [], [], [], []
Sun Sep  9 21:46:20 2018
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_pmon_11403488.trc:
ORA-00474: SMON process terminated with error
Sun Sep  9 21:46:20 2018
PMON: terminating instance due to error 474
Instance terminated by PMON, pid = 11403488

kddummy_blkchk错误mos记录

  Format: ORA-600 [kddummy_blkchk] [a] [b] 
VERSIONS:
  versions 9.2 to 10.2
DESCRIPTION:
  --what condition caused the error to be reported
ARGUMENTS:
  Arg [a] Absolute file number
  Arg [b] Bock number
  Arg  Internal error code returned from kcbchk() which indicates the problem encountered.

trace文件信息

Block after image:
buffer tsn: 14 rdba: 0x98400006 (609/6)
scn: 0x0007.9e675f6d seq: 0x01 flg: 0x04 tail: 0x5f6d1e01
frmt: 0x02 chkval: 0x61ae type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of corrupt header 3 = CHKVAL
Dump of memory from 0x070000043F034000 to 0x070000043F034014
*** 2018-09-09 21:45:28.531
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kddummy_blkchk], [609], [6], [18018], [], [], [], []
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              90000000032FBDC ? 000000000 ?
ksedmp+0290          bl       ksedst               104A2CDD0 ?
ksfdmp+0018          bl       03F26B04
kgerinv+00dc         bl       _ptrgl
kseinpre+0040        bl       kgerinv              110123BE0 ? 000000001 ?
                                                   104BD0420 ? 07FFFFFFF ?
                                                   000000000 ?
ksesin+0048          bl       kseinpre             104BD0420 ? 07FFFFFFF ?
                                                   000000000 ?
kco_blkchk+0798      bl       ksesin               104BD0888 ? 300000003 ?
                                                   000000000 ? 000000261 ?
                                                   000000000 ? 000000006 ?
                                                   000000000 ? 000004662 ?
kcoapl+0d24          bl       kco_blkchk           000000000 ? 70000046D293500 ?
                                                   FFFFFFFFFFF9BA0 ? 1101954D0 ?
                                                   FFFFFFFFFFF9AC0 ?
kcbapl+0174          bl       kcoapl               FFFFFFFFFFFAFD0 ?
                                                   70000043F034000 ? 1104C2884 ?
                                                   EDEADBEEF ? 200000000000 ?
                                                   110000790 ? 000000000 ?
kcrfw_redo_gen+2988  bl       kcbapl               FFFFFFFFFFFA040 ?
                                                   70000046D293500 ? 100FAF764 ?
                                                   700000010008000 ? 000000000 ?
kcbchg1_main+25d4    bl       kcrfw_redo_gen       000000000 ? 000000000 ?
                                                   000000000 ? 000000001 ?
                                                   000000000 ? 000000001 ?
                                                   000000000 ? 000000002 ?
kcbchg1+038c         bl       kcbchg1_main         000000000 ? 000000000 ?
                                                   000000018 ? 000000000 ?
                                                   000000000 ? 000000228 ?
ktbchgro+0380        bl       kcbchg1              062886020 ? 1601C0F50 ?
                                                   FFFFFFFFFFFAE80 ?
                                                   FFFFFFFFFFFAEB8 ? 000000000 ?
                                                   000000000 ?
ktfbbset+01e0        bl       ktbchgro             000000000 ? 100000001 ?
                                                   FFFFFFFFFFFB110 ?
                                                   FFFFFFFFFFFAFD0 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000001 ?
ktfbfset+039c        bl       ktfbbset             000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   700000010018078 ? 000000000 ?
                                                   110000790 ?
ktfbfundo+0208       bl       ktfbfset             00000000B ? 00000000B ?
ktfbhget+0760        bl       ktfbfundo            FFFFFFFFFFFBFF8 ? 200000000 ?
ktfbffpre+00d0       bl       ktfbhget             FFFFFFFFFFFBFF8 ? 200000002 ?
                                                   1100000000 ? 2033FBE8014 ?
kteopdelete+12dc     bl       ktfbffpre            FFFFFFFFFFFC678 ? 204BCDD80 ?
                                                   1CE04BCEEE8 ? 0FFFFEFFF ?
ktsxfastdele+0124    bl       kteopdelete          700000010018078 ?
                                                   700000010008000 ? 1104772A8 ?
                                                   700000464E35278 ?
                                                   700000464E35278 ? 000000000 ?
                                                   FFFFFFFFFFFBDA0 ?
kteopshrink+0308     bl       _ptrgl
ktssdrbm_segment+0a  bl       kteopshrink          E0000000E ? FFFFFFFFFFFC608 ?
b0                                                 000000001 ? 000000001 ?
                                                   110477768 ? 000000002 ?
                                                   1100EA4E0 ?
ktssdro_segment+06d  bl       ktssdrbm_segment     FFFFFFFFFFFCFC8 ?
c                                                  FFFFFFFFFFFD090 ? 100000010 ?
                                                   000000000 ?
ktssdt_segs+03f4     bl       ktssdro_segment      70000046D28EA18 ? 6FFFFD3A0 ?
                                                   0FFFFD320 ?
ktmmon+135c          bl       ktssdt_segs          000000000 ?
                                                   7FFFFFFF7FFFFFFF ?
                                                   7FFFFFFF7FFFFFFF ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
                                                   7FFFFFFC7FFFFFFC ?
                                                   05B9523F8 ?
ktmSmonMain+0030     bl       ktmmon               110000790 ?
ksbrdp+04b4          bl       _ptrgl
opirip+03fc          bl       03F26C94
opidrv+0458          bl       opirip               11029F970 ? 4102A12B0 ?
                                                   FFFFFFFFFFFF6B0 ?
sou2o+0090           bl       opidrv               3202D99A1C ? 4A004A628 ?
                                                   FFFFFFFFFFFF6B0 ?
opimai_real+0150     bl       01F93134
main+0098            bl       opimai_real          4500000000000000 ?
                                                   800200140000400 ?
__start+0070         bl       main                 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------

错误比较明显由于file 609 block 6异常，导致smon无法进行回滚,从使得数据库open之后,立马crash.这个问题可以通过使用bbed修复坏块解决,也可以通过设置合适的参数和事件,禁止smon的一些操作来规避
bbed修复block之后dbv检查

oracle@p740a:/tmp]$ dbv file=/u01/app/oracle/oradata/orcl/xifenfei38.dbf
DBVERIFY: Release 10.2.0.4.0 - Production on Sun Sep 16 11:01:54 2018
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/xifenfei38.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 1856000
Total Pages Processed (Data) : 1142647
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 710956
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2277
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 120
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 2658994127 (7.2658994127)

ORA 600 kddummy_blkchk相关bug列表

私网直连后遗症:一节点无法启动导致另外节点haip无法启动

该案例为两节点rac(11.2.0.4)，private 网络使用直连方式，其中一个节点主机异常无法启动，另外一个节点集群启动发现haip无法正常启动

# crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
     1        ONLINE  ONLINE       xifenfei1                  Started
ora.cluster_interconnect.haip                                                      >>>>  OFFLINE
     1        ONLINE  OFFLINE
ora.crf
     1        ONLINE  ONLINE       xifenfei1
ora.crsd
     1        ONLINE  OFFLINE                                                      >>>>  OFFLINE
ora.cssd
     1        ONLINE  ONLINE       xifenfei1
ora.cssdmonitor
     1        ONLINE  ONLINE       xifenfei1
ora.ctssd
     1        ONLINE  ONLINE       xifenfei1                  OBSERVER
ora.diskmon
     1        OFFLINE OFFLINE
ora.drivers.acfs
     1        ONLINE  ONLINE       xifenfei1
ora.evmd
     1        ONLINE  INTERMEDIATE xifenfei1
ora.gipcd
     1        ONLINE  ONLINE       xifenfei1
ora.gpnpd
     1        ONLINE  ONLINE       xifenfei1
ora.mdnsd
     1        ONLINE  ONLINE       xifenfei1

alerthostname日志

2018-09-02 10:38:56.767:
[/u01/app/11.2.0/grid/bin/orarootagent.bin(7866)]CRS-5818:Aborted command 'start' for resource 'ora.cluster_interconnect.haip'. Details at (:CRSAGF00113:) {0:0:2} in /u01/app/11.2.0/grid/log/xifenfei1/agent/ohasd/orarootagent_root/orarootagent_root.log.
2018-09-02 10:39:00.771:
[ohasd(7495)]CRS-2757:Command 'Start' timed out waiting for response from the resource 'ora.cluster_interconnect.haip'. Details at (:CRSPE00111:) {0:0:2} in /u01/app/11.2.0/grid/log/xifenfei1/ohasd/ohasd.log.
2018-09-02 10:40:00.802:
[/u01/app/11.2.0/grid/bin/orarootagent.bin(7866)]CRS-5818:Aborted command 'start' for resource 'ora.cluster_interconnect.haip'. Details at (:CRSAGF00113:) {0:0:2} in /u01/app/11.2.0/grid/log/xifenfei1/agent/ohasd/orarootagent_root/orarootagent_root.log.
2018-09-02 10:40:04.806:
[ohasd(7495)]CRS-2757:Command 'Start' timed out waiting for response from the resource 'ora.cluster_interconnect.haip'. Details at (:CRSPE00111:) {0:0:2} in /u01/app/11.2.0/grid/log/xifenfei1/ohasd/ohasd.log.

orarootagent_root日志

2018-09-02 10:37:56.805: [ USRTHRD][3650455296]{0:0:2} No HAIP info configured in GPNP, using defaults
2018-09-02 10:37:56.805: [ USRTHRD][3650455296]{0:0:2} The final CIDR subnet 169.254/16
2018-09-02 10:37:56.805: [ default][3650455296]clsvactversion:4: Retrieving Active Version from local storage.
2018-09-02 10:37:56.809: [ USRTHRD][3650455296]{0:0:2} HAIP: mbr num is 0.
[   CLWAL][3650455296]clsw_Initialize: OLR initlevel [70000]
2018-09-02 10:37:56.843: [ USRTHRD][3650455296]{0:0:2} HAIP: initializing to 1 interfaces
2018-09-02 10:37:56.844: [ USRTHRD][3650455296]{0:0:2} HAIP: configured to use 1 interfaces

gipcd.log日志

2018-09-02 10:38:56.787: [ CLSINET][2477147904] Returning NETDATA: 0 interfaces
2018-09-02 10:38:56.988: [GIPCDCLT][2477147904] gipcdClientInterfaceRequest: sent local interface list back to client
2018-09-02 10:38:56.822: [GIPCHDEM][2468742912] gipchaDaemonInfRequest: sent local interfaceRequest,  hctx 0x1369730 [0000000000000010] { gipchaContext : host 'xifenfei1', name 'gipcd_ha_name', luid '184dd356-00000000', numNode 0, numInf 0, usrFlags 0x0, flags 0x63 } to gipcd
2018-09-02 10:38:56.822: [GIPCDCLT][2477147904] gipcdClientThread: req from local client of type gipcdmsgtypeInterfaceRequest, endp 00000000000002cb
2018-09-02 10:38:56.822: [GIPCDCLT][2477147904] gipcdClientInterfaceRequest: Received type(gipcdmsgtypeInterfaceRequest), endp(00000000000002cb), len(1032), buf(0x7fab858b7a78):[hostname(xifenfei1), retStatus(gipcretSuccess)]
2018-09-02 10:38:56.822: [GIPCDCLT][2477147904] gipcdClientInterfaceQueryToMonitor: enqueue local interface query (2) to worklist
2018-09-02 10:38:56.823: [GIPCDCLT][2477147904] gipcdClientInterfaceRequest: sent local interface query
2018-09-02 10:38:56.823: [GIPCDMON][2472945408] gipcdMonitorCheckXfer: set new infQuery
2018-09-02 10:38:56.831: [ GIPCLIB][2477147904] gipclibSetTraceLevel: to set level to 0

ohasd.log日志

2018-09-02 10:38:52.494: [GIPCHDEM][1878710016]gipchaDaemonInfRequest: sent local interfaceRequest,  hctx 0x2749eb0 [0000000000000010] { gipchaContext : host 'xifenfei1', name 'CLSFRAME_oracler-cluster', luid '47624c02-00000000', numNode 0, numInf 0, usrFlags 0x0, flags 0x63 } to gipcd
2018-09-02 10:38:57.255: [    AGFW][3305629440]{0:0:2} Received the reply to the message: RESOURCE_START[ora.cluster_interconnect.haip 1 1] ID 4098:502 from the agent /u01/app/11.2.0/grid/bin/orarootagent_root
2018-09-02 10:38:57.255: [    AGFW][3305629440]{0:0:2} Agfw Proxy Server sending the reply to PE for message:RESOURCE_START[ora.cluster_interconnect.haip 1 1] ID 4098:500
2018-09-02 10:38:57.255: [   CRSPE][3295123200]{0:0:2} Received reply to action [Start] message ID: 500
2018-09-02 10:38:57.256: [   CRSPE][3295123200]{0:0:2} Got agent-specific msg: CRS-5017: The resource action "ora.cluster_interconnect.haip start" encountered the following error:
Start action for HAIP aborted. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/xifenfei1/agent/ohasd/orarootagent_root/orarootagent_root.log".
2018-09-02 10:38:57.500: [GIPCHDEM][1878710016]gipchaDaemonInfRequest: sent local interfaceRequest,  hctx 0x2749eb0 [0000000000000010] { gipchaContext : host 'xifenfei1', name 'CLSFRAME_oracler-cluster', luid '47624c02-00000000', numNode 0, numInf 0, usrFlags 0x0, flags 0x63 } to gipcd

检查私网状态,发现eth2网络链路状态为down,由于网络直连，而另外一台机器无法启动

[root@xifenfei1 rules.d]# ethtool eth1
Settings for eth1:
        Supported ports: [ TP ]
        Supported link modes:   10baseT/Half 10baseT/Full
                                100baseT/Half 100baseT/Full
                                1000baseT/Full
        Supported pause frame use: Symmetric
        Supports auto-negotiation: Yes
        Advertised link modes:  10baseT/Half 10baseT/Full
                                100baseT/Half 100baseT/Full
                                1000baseT/Full
        Advertised pause frame use: Symmetric
        Advertised auto-negotiation: Yes
        Speed: Unknown!
        Duplex: Unknown! (255)
        Port: Twisted Pair
        PHYAD: 1
        Transceiver: internal
        Auto-negotiation: on
        MDI-X: Unknown
        Supports Wake-on: d
        Wake-on: d
        Current message level: 0x00000007 (7)
                               drv probe link
        Link detected: no   ====>网卡链路状态异常
[root@xifenfei1 rules.d]# ifconfig
eth0      Link encap:Ethernet  HWaddr 6C:92:BF:2B:7B:36
          inet addr:10.10.17.42  Bcast:172.17.17.255  Mask:255.255.255.0
          inet6 addr: fe80::6e92:bfff:fe2b:7b36/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1     --------->注意
          RX packets:234424 errors:0 dropped:0 overruns:0 frame:0
          TX packets:160916 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:16926236 (16.1 MiB)  TX bytes:24269882 (23.1 MiB)
          Memory:91160000-91180000
eth1      Link encap:Ethernet  HWaddr 6C:92:BF:2B:7B:37
          inet addr:11.1.1.2  Bcast:11.1.1.255  Mask:255.255.255.0
          UP BROADCAST MULTICAST  MTU:1500  Metric:1      --------->注意少了RUNNING
          RX packets:0 errors:0 dropped:0 overruns:0 frame:0
          TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:0 (0.0 b)  TX bytes:0 (0.0 b)
          Memory:91140000-91160000

关于网卡链路异常导致haip无法启动的mos描述请参考:CRSD & HAIP Resources Remain In OFFLINE as Private Network Interface is Partially Up (Doc ID 1529721.1).该案例是11.2集群私网使用直连引起的直接后遗症(非常不建议集群私网使用直连方式)

由于bootstrap$异常导致数据库启动报ORA-03113 ORA-07445 lmebucp

数据库无法正常启动，报ORA-03113

SQL> startup
ORACLE 例程已经启动。
Total System Global Area 5016387584 bytes
Fixed Size                  2011136 bytes
Variable Size             905969664 bytes
Database Buffers         4093640704 bytes
Redo Buffers               14766080 bytes
数据库装载完毕。
ORA-03113: 通信通道的文件结束

alert日志报错ORA-07445 lmebucp

Mon Aug 27 15:31:37 2018
Thread 1 advanced to log sequence 21691
Thread 1 opened at log sequence 21691
  Current log# 2 seq# 21691 mem# 0: /data/oracle/orcl/redo02.log
Successful open of redo thread 1
Mon Aug 27 15:31:37 2018
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Aug 27 15:31:37 2018
SMON: enabling cache recovery
Mon Aug 27 15:31:37 2018
Errors in file /home/oracle/oracle/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_5827.trc:
ORA-07445: exception encountered: core dump [lmebucp()+24] [SIGSEGV]
[Address not mapped to object] [0x000000000] [] []

跟踪启动10046 trace

WAIT #1: nam='instance state change' ela= 822 layer=2 value=1 waited=1 obj#=-1 tim=1499370211971345
WAIT #1: nam='db file sequential read' ela= 29 file#=1 block#=257 blocks=1 obj#=-1 tim=1499370211971896
=====================
PARSING IN CURSOR #2 len=188 dep=1 uid=0 oct=1 lid=0 tim=1499370211972625 hv=2809067040 ad='b5fe2d00'
create table bootstrap$ ( line#         number not null,   obj#
number not null,   sql_text   varchar2(4000) not null)
storage (initial 50K objno 41 extents (file 1 block 257))
END OF STMT
PARSE #2:c=0,e=598,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1499370211972621
BINDS #2:
EXEC #2:c=1000,e=195,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1499370211972873
=====================
PARSING IN CURSOR #2 len=55 dep=1 uid=0 oct=3 lid=0 tim=1499370211973429 hv=2111436465 ad='b7bd0530'
select line#, sql_text from bootstrap$ where obj# != :1
END OF STMT
PARSE #2:c=0,e=472,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1499370211973426
BINDS #2:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2b8c5d50a4d0  bln=22  avl=02  flg=05
  value=41
EXEC #2:c=1000,e=838,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1499370211974375
WAIT #2: nam='db file sequential read' ela= 27 file#=1 block#=257 blocks=1 obj#=-1 tim=1499370211974522
WAIT #2: nam='db file sequential read' ela= 21 file#=1 block#=258 blocks=1 obj#=-1 tim=1499370211974855
FETCH #2:c=1000,e=479,p=2,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=1499370211974908
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object),
 addr: 0x0, PC: [0x348772c, lmebucp()+24]
*** 2018-08-27 15:31:37.074
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [lmebucp()+24] [SIGSEGV]
[Address not mapped to object] [0x000000000] [] []
Current SQL statement for this session:
alter database open
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
Cannot find symbol
Cannot find symbol
Cannot find symbol
ksedst()+31          call     ksedst1()            000000001 ? 000000001 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000001 ?
ksedmp()+610         call     ksedst()             000000001 ? 000000001 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000001 ?
ssexhd()+630         call     ksedmp()             000000003 ? 000000001 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000001 ?
<0x336800eca0>       call     ssexhd()             00000000B ? 2B8C5D238D70 ?
                                                   2B8C5D238C40 ? 000000000 ?
                                                   000000000 ? 000000001 ?
--------------------- Binary Stack Dump ---------------------

通过这里发现，数据库启动执行select line#, sql_text from bootstrap$ where obj# != :1然后报ORA-07445 lmebucp错误。这样的错误比较诡异,一般可能是由于bootstrap异常导致,但是这里再往上跟踪发现 create bootstrap$表指定的记录为file 1 block 257,根据经验知道数据库的bootstrap$表记录一般是377 或者520比较常见.通过工具对于file 1进行分析

DUL> dump datafile 1 block 257
Block Header:
block type=0x10 (data segment header block (unlimited extents))
block format=0xa2 (oracle 10)
block rdba=0x00400101 (file#=1, block#=257)
scn=0x0000.0000007e, seq=1, tail=0x007e1001
block checksum value=0xe75c=59228, flag=4
Data Segment Header:
  Extent Control Header
  -------------------------------------------------------------
  Extent Header:: extents: 1  blocks: 7
                  last map: 0x00000000  #maps: 0  offset: 4128
      Highwater:: 0x00400103  (rfile#=1,block#=259)
                  ext#: 0  blk#: 1   ext size:7
      #blocks in seg. hdr's freelists: 0
      #blocks below: 1
      mapblk: 0x00000000   offset: 0
      Map Header:: next: 0x00000000   #extents: 1  obj#: 41  flag: 0x40000000
  Extent Control Header
  -------------------------------------------------------------
   0x00400102  length: 7
  nfl = 1, nfb = 1, typ = 2, nxf = 0, ccnt = 0
  SEG LST:: flg:UNUSED lhd: 0x00000000 ltl: 0x00000000

发现异常比较明显,block 257为data_object_id=41,也就是
41|41|CREATE UNIQUE INDEX I_FILE1 ON FILE$(FILE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 41 EXTENTS (FILE 1 BLOCK 257))
这里看数据库的引导异常或者bootstrap$表中记录异常.通过修复bootstrap相关内容,数据库完美启动

又一例asm格式化文件系统恢复

又一个客户把win rac中的asm disk给格式化为ntfs了(data磁盘组由三个500G的磁盘组成,被格式化掉前面两个还剩下一个),而且格式化之后,还进行了一系列恢复(比如修复磁盘头,又进行分区等一些磁盘操作),导致恢复难度增加,也增加了一些数据覆盖
asm alert日志报错

Thu Aug 23 11:20:14 2018
NOTE: ASM client orcl1:orcl disconnected unexpectedly.
NOTE: check client alert log.
NOTE: Process state recorded in trace file d:\app\administrator\diag\asm\+asm\+asm1\trace\+asm1_ora_2260.trc
Thu Aug 23 11:20:28 2018
Errors in file d:\app\administrator\diag\asm\+asm\+asm1\trace\+asm1_lgwr_3820.trc:
ORA-27070: async read/write failed
OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 87) 参数错误。
WARNING: IO Failed. group:2 disk(number.incarnation):1.0xf0f0a1cb disk_path:\\.\ORCLDISKDATA1
	 AU:26 disk_offset(bytes):27566080 io_size:4096 operation:Write type:synchronous
	 result:I/O error process_id:3820
NOTE: unable to write any mirror side for diskgroup DATA
NOTE: cache initiating offline of disk 1 group DATA
NOTE: process 3268:3820 initiating offline of disk 1.4042301899 (DATA_0001) with mask 0x7e in group 2
WARNING: Disk DATA_0001 in mode 0x7f is now being taken offline
NOTE: initiating PST update: grp = 2, dsk = 1/0xf0f0a1cb, mode = 0x15
kfdp_updateDsk(): 22
Thu Aug 23 11:20:28 2018
kfdp_updateDskBg(): 22
ERROR: too many offline disks in PST (grp 2)
WARNING: Disk DATA_0001 in mode 0x7f offline aborted

数据库alert日志报错

WARNING: IO Failed. group:2 disk(number.incarnation):1.0xf0f0a1cb disk_path:\\.\ORCLDISKDATA1
	 AU:422 disk_offset(bytes):442515456 io_size:16384 operation:Read type:synchronous
	 result:I/O error process_id:11992
WARNING: failed to read mirror side 1 of virtual extent 5 logical extent 0 of file 260 in
group [2.1859146063] from disk DATA_0001  allocation unit 422 reason error; if possible,will try another mirror side
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_ora_11992.trc:
ORA-15080: 与磁盘的同步 I/O 操作失败
WARNING: failed to write mirror side 1 of virtual extent 5 logical extent 0 of file 260
in group 2 on disk 1 allocation unit 422
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_ora_11992.trc:
ORA-00202: 控制文件: ''+DATA/orcl/controlfile/current.260.944422981''
ORA-15081: 无法将 I/O 操作提交到磁盘
Thu Aug 23 11:20:13 2018
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_dbw1_3224.trc:
ORA-27070: 异步读取/写入失败
WARNING: IO Failed. group:2 disk(number.incarnation):1.0xf0f0a1cb disk_path:\\.\ORCLDISKDATA1
	 AU:841 disk_offset(bytes):882532352 io_size:131072 operation:Write type:asynchronous
	 result:I/O error process_id:3224
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_dbw1_3224.trc:
ORA-15080: 与磁盘的同步 I/O 操作失败
WARNING: failed to write mirror side 1 of virtual extent 240 logical extent 0 of file 259 in group 2 on disk 1
allocation unit 841 KCF: read, write or open error, block=0x7853 online=1
        file=4 '+DATA/orcl/datafile/users.259.944422883'
        error=15081 txt: ''
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_dbw1_3224.trc:
ORA-27070: 异步读取/写入失败
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 87) 参数错误。
WARNING: IO Failed. group:2 disk(number.incarnation):1.0xf0f0a1cb disk_path:\\.\ORCLDISKDATA1
	 AU:422 disk_offset(bytes):442515456 io_size:16384 operation:Read type:synchronous
	 result:I/O error process_id:3224
WARNING: failed to read mirror side 1 of virtual extent 5 logical extent 0 of file 260 in group [2.1859146063] from
disk DATA_0001  allocation unit 422 reason error; if possible,will try another mirror side
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_dbw1_3224.trc:
ORA-15080: 与磁盘的同步 I/O 操作失败
WARNING: failed to write mirror side 1 of virtual extent 5 logical extent 0 of file 260 in group 2 on disk 1
allocation unit 422
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_dbw1_3224.trc:
ORA-00202: 控制文件: ''+DATA/orcl/controlfile/current.260.944422981''
ORA-15081: 无法将 I/O 操作提交到磁盘
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_dbw1_3224.trc:
ORA-00204: 读取控制文件时出错 (块 41, # 块 1)
ORA-00202: 控制文件: ''+DATA/orcl/controlfile/current.260.944422981''
ORA-15081: 无法将 I/O 操作提交到磁盘
DBW1 (ospid: 3224): terminating the instance due to error 204

由于客户进行了一系列恢复恢复操作导致查看磁盘都不全

D:\>asmtool -list
NTFS                             \Device\Harddisk0\Partition1              100M
NTFS                             \Device\Harddisk0\Partition2           102298M
NTFS                             \Device\Harddisk1\Partition1           102397M
NTFS                             \Device\Harddisk2\Partition1           204797M
---这里还有一个磁盘没有正常显示
ORCLDISKDATA10                   \Device\Harddisk4\Partition1           511997M--客户尝试修复的磁盘
ORCLDISKDATA2                    \Device\Harddisk5\Partition1           511997M
ORCLDISKRECOVERY0                \Device\Harddisk6\Partition1            51197M
ORCLDISKRECOVERY1                \Device\Harddisk7\Partition1            51197M
ORCLDISKRECOVERY2                \Device\Harddisk8\Partition1            51197M
ORCLDISKCRS0                     \Device\Harddisk9\Partition1            10237M
ORCLDISKCRS1                     \Device\Harddisk10\Partition1           10237M
ORCLDISKCRS2                     \Device\Harddisk11\Partition1           10237M
NTFS                             \Device\Harddisk12\Partition2         4194174M

通过主机层面激活卷,删除分区等一系列操作,然后通过kfed构造磁盘头,让这些磁盘在os层面可以正常显示

C:\Users\Administrator>asmtool -list
NTFS                             \Device\Harddisk0\Partition1              100M
NTFS                             \Device\Harddisk0\Partition2           102298M
NTFS                             \Device\Harddisk1\Partition1           102397M
NTFS                             \Device\Harddisk2\Partition1           204797M
------需要处理的磁盘------
ORCLDISKDATA0                    \Device\Harddisk3\Partition1           511997M
ORCLDISKDATA1                    \Device\Harddisk4\Partition1           511997M
ORCLDISKDATA2                    \Device\Harddisk5\Partition1           511997M
-----------------------
ORCLDISKRECOVERY0                \Device\Harddisk6\Partition1            51197M
ORCLDISKRECOVERY1                \Device\Harddisk7\Partition1            51197M
ORCLDISKRECOVERY2                \Device\Harddisk8\Partition1            51197M
ORCLDISKCRS0                     \Device\Harddisk9\Partition1            10237M
ORCLDISKCRS1                     \Device\Harddisk10\Partition1           10237M
ORCLDISKCRS2                     \Device\Harddisk11\Partition1           10237M
NTFS                             \Device\Harddisk12\Partition2         4194174M

由于asm磁盘组内部目录au被彻底损坏,导致无法通过asm直接拷贝出来数据,通过底层扫描,按照au恢复出来相关数据,由于格式化ntfs和后续的误操作导致部分数据au被覆盖.其余数据均恢复,抢救了绝大部分数据.
数据文件恢复参考:asm disk header 彻底损坏恢复
另外有一次win平台类似恢复经历:asm disk格式化为ntfs恢复
如果您遇到此类情况,无法解决请联系我们，提供专业ORACLE数据库恢复技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

asm disk 大小限制

这个问题在12C之前争议很小,基本共识非XD环境不能超过2T,但是到了后面的版本中,发生了一些改变,主要是COMPATIBLE.ASM and COMPATIBLE.RDBMS disk group attributes are set to 12.1 or greater的时候asm disk 大小限制依赖au size,
1M ausize asm disk limit为4 PB
2M ausize asm disk limit为8 PB
4M ausize asm disk limit为16 PB
8M ausize asm disk limit为32 PB

参见:Oracle ASM Storage Limits
18C中COMPATIBLE.ASM和COMPATIBLE.RDBMS默认值（COMPATIBLE.RDBMS为10.1,也就是说默认情况下非XD情况还是只能支持不超过2T的asm disk）

增加默认值列exp DIRECT=Y导出，导入遭遇ORA-01400

最近的一次数据迁移中使用exp导出遭遇到ORA-01400: cannot insert NULL into 错误,凭着经验对其进行了重现,确定是由于增加默认值的列,使用DIRECT=Y 导出导致该问题
创建测试表
创建一张表,并且增加带默认值而且不为空的列，然后使用exp DIRECT=Y 导出数据

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>  create table t_xifenfei as select * from dba_objects;
Table created.
SQL> alter table t_xifenfei add (c_xifenfei varchar2(30) default 'www.xifenfei.com' not null);
Table altered.
SQL> select c_xifenfei from t_xifenfei where rownum<10;
C_XIFENFEI
------------------------------
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
9 rows selected.
[oracle@bogon ~]$ exp xff/oracle tables=t_xifenfei FEEDBACK=10000  COMPRESS=NO \
>  BUFFER=102400000 STATISTICS=none DIRECT=Y recordlength=65535 file=/tmp/t_xifenfei.dmp
Export: Release 11.2.0.4.0 - Production on Tue Aug 21 15:56:52 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Direct Path ...
. . exporting table                     T_XIFENFEI
........
                                                        86421 rows exported
Export terminated successfully without warnings.

测试导入数据
导入到另外一个用户下面

[oracle@bogon ~]$ imp xff1/oracle file=/tmp/t_xifenfei.dmp fromuser=xff touser=xff1
Import: Release 11.2.0.4.0 - Production on Tue Aug 21 15:57:53 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via direct path
Warning: the objects were exported by XFF, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing XFF's objects into XFF1
. . importing table                   "T_XIFENFEI"
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("XFF1"."T_XIFENFEI"."C_XIFENFEI")
Column : SYS
Column : ICOL$
Column :
Column : 20
Column : 2
Column : TABLE
Column : 24-AUG-2013:11:37:35
Column : 24-AUG-2013:11:47:37
Column : 2013-08-24:11:37:35
Column : VALID
Column : N
Column : N
Column : N
Column : 1
Column :
Column :
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("XFF1"."T_XIFENFEI"."C_XIFENFEI")

这里可以看到，故障已经重现ORA-01400: cannot insert NULL into,无法将空值插入到刚刚新加的默认值的列中

使用常规导出

[oracle@bogon ~]$ exp xff/oracle tables=t_xifenfei FEEDBACK=10000
COMPRESS=NO BUFFER=102400000 STATISTICS=none  file=/tmp/t_xifenfei1.dmp
Export: Release 11.2.0.4.0 - Production on Tue Aug 21 16:00:50 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                     T_XIFENFEI
........
                                                        86421 rows exported
Export terminated successfully without warnings.

导入数据

[oracle@bogon ~]$ imp xff1/oracle file=/tmp/t_xifenfei1.dmp fromuser=xff touser=xff1
Import: Release 11.2.0.4.0 - Production on Tue Aug 21 16:04:47 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by XFF, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing XFF's objects into XFF1
. . importing table                   "T_XIFENFEI"      86421 rows imported
Import terminated successfully without warnings.
[oracle@bogon ~]$
[oracle@bogon ~]$ sqlplus xff1/oracle
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 21 16:10:16 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select c_xifenfei from t_xifenfei where rownum<10;
C_XIFENFEI
------------------------------
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
9 rows selected.

通过测试证明,对于11g新特性增加默认值,而且非为空的列,如果使用exp常规方法导入,再导出没有任何问题,如果使用DIRECT=Y特性,将导致导入失败(ORA-01400).这是由于11g的快速增加列和默认值且不为空的新特性导致的(直接修改字典,并没有真正的去修改数据底层存储).
查询mos发现匹配bug: Bug 13961541 : EXP WITH DIRECT=Y CAN NOT HANDLE ADD COLUMN DEFAULT NOT NULL FEATURE IN 11G

数据库升级报ORA-07445 joxchrcnv

朋友在aix平台数据库10.2.0.3升级到10.2.0.5过程中,运行catupgrd.sql脚本,遭遇ORA-07445 joxchrcnv错误

--catupgrd.sql前台报错
Table altered.
Table altered.
BEGIN
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
ERROR:
ORA-03114: not connected to ORACLE
--trace文件报错
*** 2018-08-09 14:41:29.742
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [joxchrcnv+00a8]
   [SIGSEGV] [Address not mapped to object] [0x500000040] [] []
Current SQL statement for this session:
REVOKE all ON SYS.hs_bulk_seq FROM PUBLIC
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
7000000e9942b80         2  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              000000000 ? 000000001 ?
ksedmp+0290          bl       ksedst               104C1FC50 ?
ssexhd+03e0          bl       ksedmp               300001D15 ?
0000441C             ?        00000000
joxcdtu+0088         bl       joxchrcnv            11019B760 ? 7000000E2CEBC70 ?
                                                   FFFFFFFFFFE82A0 ?
                                                   7000000E37EFDC8 ? 000000000 ?
                                                   000000000 ? 000000002 ?
                                                   7000000E1A8B250 ?
joxcnas+007c         bl       joxcdtu              110008BE0 ? 7000000E5FD3670 ?
                                                   FFFFFFFFFFE71C0 ?
                                                   4642408300000004 ?
                                                   104BAF6F0 ? 7000000E6BE6B48 ?
joxsnm_+0020         bl       joxcnas              7000000E5FD4E30 ? 000000001 ?
                                                   000000000 ? 7000000E2CEB8D0 ?
                                                   7000000E991C6E0 ? 11019B760 ?
                                                   FFFFFFFFFFE82E0 ?
joxsnm+0010          bl       03754C58
kkdlJavasnmAltName+  bl       01FC3B1C
0048
qcdlgbo+0908         bl       _ptrgl
qcdlgob+0560         bl       qcdlgbo              FFFFFFFFFFEF8C0 ? 11019B760 ?
                                                   11057B380 ? 7000000E2CEC960 ?
                                                   000000000 ? 104DA95B0 ?
                                                   700000010017FA0 ? 400000000 ?
qcsfgob+0220         bl       qcdlgob              FFFFFFFFFFE8B20 ? 1058251F8 ?
                                                   FFFFFFFFFFE9000 ? 000000001 ?
                                                   00000000B ? 000000000 ?
                                                   0000000C7 ?
qcsprfro+04a4        bl       qcsfgob              1104300C0 ? 000000000 ?
                                                   1100C1848 ? 11019B760 ?
qcsprfro_tree+0454   bl       qcsprfro             000000000 ? 000000000 ?
                                                   FFFFFFFFFFE8DE8 ? 000000000 ?
qcsprfro_tree+0258   bl       qcsprfro_tree        000000001 ? 000000000 ?
                                                   FFFFFFFFFFE8DA0 ? 11057B380 ?
                                                   7000000E1ACA8F8 ? 000000000 ?
qcspafq+0068         bl       qcsprfro_tree        11058DEA0 ? FFFFFFFFFFE9040 ?
                                                   FFFFFFFFFFE8E50 ?
                                                   4822208200000010 ?
                                                   10277AD14 ? 110118F58 ?
qcspqbDescendents+0  bl       qcspafq              000000000 ? 110008BE0 ?
3a0                                                FFFFFFFFFFEFF70 ?
qcspqb+00ac          bl       qcspqbDescendents    10008CC20 ? 000000000 ?
                                                   7000000E2CEC638 ?
kkmdrv+003c          bl       qcspqb               FFFFFFFFFFE9080 ?
                                                   88832081E9999F10 ?
                                                   FFFFFFFFFFE9080 ?
opiSem+1450          bl       kkmdrv               000000000 ? 000001068 ?
                                                   11019E058 ? 11058DA68 ?
opiprs+01ac          bl       opiSem               FFFFFFFFFFEFF70 ? 110502A18 ?
                                                   00000002A ? 000000001 ?
kksParseChildCursor  bl       opiprs               10008CC20 ? 000000000 ?
+0640                                              104C29C50 ? 07FFFFFF8 ?
                                                   000000FE8 ? 000000001 ?
rpiswu2+0368         bl       _ptrgl
kksLoadChild+20cc    bl       rpiswu2              7000000EAA86FE0 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
kxsGetRuntimeLock+0  bl       kksLoadChild         11019B760 ? 7000000E9AE5520 ?
89c                                                7000000E38176D8 ?
kksfbc+2830          bl       kxsGetRuntimeLock    11019B760 ? 11057B380 ?
                                                   FFFFFFFFFFF18D8 ? 000000000 ?
                                                   000000000 ?
kkspsc0+1570         bl       kksfbc               11057B380 ? 3FFFF2858 ?
                                                   108E381B6C0 ? 110502A18 ?
                                                   00000002A ? 000000000 ?
                                                   000000000 ? 000000000 ?
kksParseCursor+00d4  bl       kkspsc0              110431A68 ? 110502A18 ?
                                                   00000002A ? 310231CF0 ?
                                                   610431A68 ?
                                                   222000000000000A ?
                                                   000000000 ?
opiosq0+0b40         bl       01FC69A4
opipls+0638          bl       opiosq0              3EA6B0DF8 ? 110008BE0 ?
                                                   110118F58 ?
                                                   22200000EAADBE88 ?
                                                   000000000 ?
opiodr+0b2c          bl       _ptrgl
rpidrus+01dc         bl       opiodr               6600000000 ? 6E2D017E0 ?
                                                   FFFFFFFFFFF6C80 ? AFFFF5CD0 ?
skgmstack+00c8       bl       _ptrgl
rpidru+0088          bl       skgmstack            7000000E37EFB20 ?
                                                   FFFFFFFFFFF5C30 ?
                                                   FFFFFFFFFFF6080 ?
                                                   26214040000010F0 ?
                                                   100417334 ?
rpiswu2+0368         bl       _ptrgl
rpidrv+097c          bl       rpiswu2              7000000EAA86FE0 ? 11019B760 ?
                                                   FFFFFFFFFFF6400 ? 000000004 ?
                                                   7000000E37EFB50 ? 0E9942BE0 ?
                                                   7000000E9942BF0 ? 000000000 ?
psddr0+02dc          bl       01FC6E18
psdnal+01d0          bl       psddr0               A00000000 ? 6600000000 ?
                                                   FFFFFFFFFFF6C80 ?
                                                   3100000000 ?
pevm_EXIM+0120       bl       _ptrgl
pfrinstr_EXIM+0034   bl       pevm_EXIM            1105BD288 ? 000000000 ?
pfrrun_no_tool+005c  bl       _ptrgl
pfrrun+1064          bl       pfrrun_no_tool       5800000000 ? 110231CF0 ?
                                                   FFFFFFFFFFF6FA0 ?
plsql_run+06e8       bl       pfrrun               110449078 ?
peicnt+0244          bl       plsql_run            110449078 ? 1000000000418 ?
                                                   000000000 ?
kkxexe+0250          bl       peicnt               FFFFFFFFFFF82D8 ? 110449078 ?
opiexe+3024          bl       kkxexe               1104DAEC8 ?
kpoal8+0ef0          bl       opiexe               FFFFFFFFFFFB954 ? 1103AA798 ?
                                                   FFFFFFFFFFF9AF0 ?
opiodr+0b2c          bl       _ptrgl
ttcpip+1020          bl       _ptrgl
opitsk+117c          bl       01FC6888
opiino+09d0          bl       opitsk               1EFFFFD920 ? 000000000 ?
opiodr+0b2c          bl       _ptrgl
opidrv+04a4          bl       opiodr               3C102A7758 ? 404C72B18 ?
                                                   FFFFFFFFFFFF8E0 ? 0102A7750 ?
sou2o+0090           bl       opidrv               3C02A288DC ? 400000010 ?
                                                   FFFFFFFFFFFF8E0 ?
opimai_real+01bc     bl       01FC30F4
main+0098            bl       opimai_real          000000000 ? 000000000 ?
__start+0098         bl       main                 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------

根据Catupgrd.sql Fails with ORA-3113/ORA-7445 During Grant Select On ALL_JOBS to Public with Grant Option (Doc ID 419309.1)文档,处理建议

1. Make sure LD_LIBRARY_PATH or LIBPATH is set correctly:
  AIX
  ====
  export LIBPATH=$ORACLE_HOME/lib:$LIBPATH
  HP-UX
  =====
  export LD_LIBRARY_PATH=$ORACLE_HOME/lib
  export SHLIB_PATH=$ORACLE_HOME/lib32
  HP Tru-64
  ========
  export LD_LIBRARY_PATH=$ORACLE_HOME/lib
  SUN Sparc Solaris-64Bit
  ==================
  export LD_LIBRARY_PATH_64=$ORACLE_HOME/lib
  export LD_LIBRARY_PATH=$ORACLE_HOME/lib32
  SUN Sparc Solaris-32Bit
  ==================
  export LD_LIBRARY_PATH=$ORACLE_HOME/lib
  Linux
  =====
  export LD_LIBRARY_PATH=$ORACLE_HOME/lib
2. Make sure ORA_NLS parameter  is set correctly. See the following notes for assistance:
Note 77442.1  (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained.
3. Make sure Listener is also started with the correct environment variables if you were doing
  remote connection from client through sqlnet rather than local connection to database.
4. Shutdown the database and rerun the upgrade:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
5.  If the above failed then it might be due to the libjox *.a file is not replaced during upgrade or install.

检查该环境发现libjox动态库丢失

由于软件升级不正常,导致数据库升级出现该问题,在升级软件过程中,一定检查升级软件和升级结果

PostgreSQL常见字符串操作函数

转载:pmars blog中关于pg字符串相关函数

函数：string || string
说明：String concatenation 字符串连接操作
例子：select 'Post' || 'greSQL';　＝　PostgreSQL
函数：string || non-string or non-string || string
说明：String concatenation with one non-string input 字符串与非字符串类型进行连接操作
例子：select 'Value: ' || 42; = Value: 42
函数：bit_length(string)
说明：Number of bits in string 计算字符串的位数
例子：select bit_length('pmars') = 40
函数：char_length(string) or character_length(string)
说明：Number of characters in string 计算字符串中字符个数
例子：select char_length('pmars'); = 5
函数：lower(string)
说明：Convert string to lower case 转换字符串为小写
例子：select lower('PmArS'); = "pmars"
函数：octet_length(string)
说明：Number of bytes in string 计算字符串的字节数
例子：select octet_length('我是pmars'); = 11  select octet_length('我');  = 3
函数：overlay(string placing string from int [for int])
说明：Replace substring 替换字符串中任意长度的子字串为新字符串
例子：select overlay('I am pmars' placing 'ming' from 6 for 5); = "I am ming"
函数：position(substring in string)
说明：Location of specified substring 子串在一字符串中的位置
例子：select position('ma' in 'pmars'); = 2
函数：substring(string [from int] [for int])
说明：Extract substring 截取任意长度的子字符串
例子：select substring('topmars' from 3 for 3); = "pma"
函数：substring(string from pattern)
说明：Extract substring matching POSIX regular expression.
 See Section 9.7 for more information on pattern matching.
  利用正则表达式对一字符串进行任意长度的字串的截取
例子：select substring('topmars' from 'p.*$'); = "pmars"
函数：substring(string from pattern for escape)
说明：Extract substring matching SQL regular expression. See Section 9.7 for more information on pattern matching.
      利于正则表达式对某类字符进行删除，以得到子字符串
例子：select substring('Thomas' from '%#"o_a#"_' for '#'); = "oma"
函数：trim([leading | trailing | both] [characters] from string)
说明：Remove the longest string containing only the characters
     (a space by default) from the start/end/both ends of the string
     去除尽可能长开始，结束或者两边的某类字符，默认为去除空白字符，当然可以自己指定，可同时指定多个要删除的字符串
例子：select trim(leading 'p' from 'pmars'); = "mars"
函数：upper(string)
说明：Convert string to uppercase 将字符串转换为大写
例子：select upper('pmars'); = "PMARS"
函数：ascii(string)
说明：ASCII code of the first character of the argument. For UTF8 returns
 the Unicode code point of the character. For other multibyte encodings.
 the argument must be a strictly ASCII character. 得到某一个字符的Assii值
例子：select ascii('pmars'); = select ascii('p'); = 112
函数：btrim(string text [, characters text])
说明：Remove the longest string consisting only of characters in characters
     (a space by default) from the start and end of string
     去除字符串两边的所有指定的字符，可同时指定多个字符
例子：select btrim('pmars','prs'); = "ma"
函数：chr(int)
说明：Character with the given code. For UTF8 the argument is treated as a Unicode code point.
 For other multibyte encodings the argument must designate a strictly ASCII character.
 The NULL (0) character is not allowed because text data types cannot store such bytes.
  得到某ACSII值对应的字符
例子：select chr(65); = A
函数：convert(string bytea, src_encoding name, dest_encoding name)
说明：Convert string to dest_encoding. The original encoding is specified by src_encoding.
   The string must be valid in this encoding.
  Conversions can be defined by CREATE CONVERSION. Also there are some predefined conversions.
  See Table 9-7 for available conversions. 转换字符串编码，指定源编码与目标编码
例子：select convert('我是pmars_in_utf8', 'UTF8', 'GBK'); = "\316\322\312\307pmars_in_utf8"
函数：convert_from(string bytea, src_encoding name)
说明：Convert string to the database encoding. The original encoding is specified by src_encoding.
  The string must be valid in this encoding. 转换字符串编码，自己要指定源编码,目标编码默认为数据库指定编码，
例子：select convert_from('\316\322\312\307pmars','GBK'); = "我是pmars"
函数：convert_to(string text, dest_encoding name)
说明：Convert string to dest_encoding.转换字符串编码，源编码默认为数据库指定编码，自己要指定目标编码,
例子：select convert_to('我是pmars_in_utf8','GBK'); = "\316\322\312\307pmars_in_utf8"
函数：decode(string text, type text)
说明：Decode binary data from string previously encoded with encode. Parameter type is same as in encode.
     对字符串按指定的类型进行解码
例子：select decode('MTIzAAE=', 'base64'); = "123\000\001"
函数：encode(data bytea, type text)
说明：Encode binary data to different representation. Supported types are: base64, hex, escape.
   Escape merely outputs null bytes as \000 and doubles backslashes. 与decode相反，对字符串按指定类型进行编码
例子：select encode('123\000\001','base64'); = "MTIzAAE="
函数：initcap(string)
说明：Convert the first letter of each word to uppercase and the rest to lowercase.
 Words are sequences of alphanumeric characters separated by non-alphanumeric characters.
  将字符串所有的单词进行格式化，首字母大写，其它为小写
例子：select initcap('I AM PMARs'); = "I Am Pmars"
函数：length(string)
说明：Number of characters in string 讲算字符串长度
例子：select length('我是pmars'); = 7
函数：length(stringbytea, encoding name )
说明：Number of characters in string in the given encoding.
   The string must be valid in this encoding. 计算字符串长度，指定字符串使用的编码
例子：select length('我是pmars','GBK'); = 8
函数：lpad(string text, length int [, fill text])
说明：Fill up the string to length length by prepending the characters fill (a space by default).
If the string is already longer than length then it is truncated (on the right).
 对字符串左边进行某类字符自动填充，即不足某一长度，则在左边自动补上指定的字符串，直至达到指定长度，可同时指定多个自动填充的字符
例子：select lpad('pmars', 10, 'to'); = "tototpmars"
函数：ltrim(string text [, characters text])
说明：Remove the longest string containing only characters from characters (a space by default) from the start of string
删除字符串左边某一些的字符，可以时指定多个要删除的字符
例子：select ltrim('pmars','amp'); = "rs"
函数：md5(string)
说明：Calculates the MD5 hash of string, returning the result in hexadecimal 将字符串进行md5编码
例子：select md5('pmars'); = "1018ceb949f1472f7252f7da1f5eff42"
函数：pg_client_encoding()
说明：Current client encoding name 得到pg客户端编码
例子：select pg_client_encoding(); = "UTF8"
函数：quote_ident(string text)
说明：Return the given string suitably quoted to be used as an identifier in an SQL statement string.
Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded).
Embedded quotes are properly doubled. 对某一字符串加上两引号
例子：quote_ident('Foo bar') = "Foo bar"
函数：quote_literal(string text)
说明：Return the given string suitably quoted to be used as a string literal in an SQL statement string.
Embedded single-quotes and backslashes are properly doubled.
对字符串里两边加上单引号，如果字符串里面出现sql编码的单个单引号，则会被表达成两个单引号
例子：quote_literal('O\'Reilly') = 'O''Reilly'
函数：quote_literal(value anyelement)
说明：Coerce the given value to text and then quote it as a literal.
     Embedded single-quotes and backslashes are properly doubled.
    将一数值转换为字符串，并为其两边加上单引号，如果数值中间出现了单引号，也会被表示成两个单引号
例子：quote_literal(42.5) = '42.5'
函数：regexp_matches(string text, pattern text [, flags text])
说明：Return all captured substrings resulting from matching a POSIX regular expression against the string.
See Section 9.7.3 for more information. 对字符串按正则表达式进行匹配，如果存在则会在结果数组中表示出来
例子：regexp_matches('foobarbequebaz', '(bar)(beque)') = {bar,beque}
函数：regexp_replace(string text, pattern text, replacement text [, flags text])
说明：Replace substring(s) matching a POSIX regular expression. See Section 9.7.3 for more information.
    利用正则表达式对字符串进行替换
例子：regexp_replace('Thomas', '.[mN]a.', 'M') = ThM
函数：regexp_split_to_array(string text, pattern text [, flags text ])
说明：Split string using a POSIX regular expression as the delimiter. See Section 9.7.3 for more information.
     利用正则表达式将字符串分割成数组
例子：regexp_split_to_array('hello world', E'\\s+') = {hello,world}
函数：regexp_split_to_table(string text, pattern text [, flags text])
说明：Split string using a POSIX regular expression as the delimiter. See Section 9.7.3 for more information.
      利用正则表达式将字符串分割成表格
例子：regexp_split_to_table('hello world', E'\\s+') =
hello
world
(2 rows)
函数：repeat(string text, number int)
说明：Repeat string the specified number of times 重复字符串一指定次数
例子：repeat('Pg', 4) = PgPgPgPg
函数：replace(string text, from text, to text)
说明：Replace all occurrences in string of substring from with substring to 将字符的某一子串替换成另一子串
例子：('abcdefabcdef', 'cd', 'XX') = abXXefabXXef
函数：rpad(string text, length int [, fill text])
说明：Fill up the string to length length by appending the characters fill (a space by default).
If the string is already longer than length then it is truncated. 对字符串进行填充，填充内容为指定的字符串
例子：rpad('hi', 5, 'xy') = hixyx
函数：rtrim(string text [, characters text])
说明：Remove the longest string containing only characters from characters
(a space by default) from the end of string 去除字符串右边指定的字符
例子：rtrim('trimxxxx', 'x') = trim
函数：split_part(string text, delimiter text, field int)
说明：Split string on delimiter and return the given field (counting from one)
 对字符串按指定子串进行分割，并返回指定的数值位置的值
例子：split_part('abc~@~def~@~ghi', '~@~', 2) = def
函数：strpos(string, substring)
说明：Location of specified substring (same as position(substring in string),
but note the reversed argument order) 指定字符串在目标字符串的位置
例子：strpos('high', 'ig') = 2
函数：substr(string, from [, count])
说明：Extract substring (same as substring(string from from for count)) 截取子串
例子：substr('alphabet', 3, 2) = ph
函数：to_ascii(string text [, encoding text])
说明：Convert string to ASCII from another encoding
(only supports conversion from LATIN1, LATIN2, LATIN9, and WIN1250 encodings)
将字符串转换成ascii编码字符串
例子：to_ascii('Karel') = Karel
函数：to_hex(number int or bigint)
说明：Convert number to its equivalent hexadecimal representation 　对数值进行十六进制编码
例子：to_hex(2147483647) = 7fffffff
函数：translate(string text, from text, to text)
说明：Any character in string that matches a
     character in the from set is replaced by the corresponding character in the to set
     将字符串中某些匹配的字符替换成指定字符串，目标字符与源字符都可以同时指定多个
例子：translate('12345', '14', 'ax') = a23x5

转载：https://www.cnblogs.com/pmars/archive/2013/02/17/2914046.html

作者：admin