Oracle – 第8页 – Database SOS

WRI$_ADV_OBJECTS表过大,导致SYSAUX表空间不足

12.2的sysaux表空间使用过大

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                        0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0
SQL> @tbs
Name      TS Type      All Size(MB)   Max Size(MB)  Free Size(MB)  Max Free Pct. Free Max Free%
--------- ---------- -------------- -------------- -------------- --------- --------- ---------
SYSAUX    PERMANENT          32,760         32,768             26        34         0         0
USERS     PERMANENT           1,784         32,768             85    31,069         5        95
SYSTEM    PERMANENT             860         32,768             10    31,917         1        97
R_INDEX   PERMANENT           5,900        229,376            927   224,403        16        98
RICHMAN   PERMANENT           3,000        196,608          1,895   195,503        63        99
UNDOTBS1  UNDO                1,600         32,768          1,560    32,728        97       100
6 rows selected.

awrinfo查看

********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| SM/ADVISOR           SYS                       30,422.9 MB
| SM/OPTSTAT           SYS                        1,222.7 MB
| SM/AWR               SYS                          588.2 MB
| SM/OTHER             SYS                          152.4 MB
**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************
COMPONENT        MB SEGMENT_NAME                                      SEGMENT_TYPE
--------- --------- ------------------------------------------------- -------------
NON_AWR    15,675.0 SYS.WRI$_ADV_OBJECTS                              TABLE
NON_AWR     8,764.0 SYS.WRI$_ADV_OBJECTS_IDX_01                       INDEX
NON_AWR     5,959.0 SYS.WRI$_ADV_OBJECTS_PK                           INDEX
NON_AWR       488.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                INDEX
NON_AWR       249.0 SYS.I_WRI$_OPTSTAT_H_ST                           INDEX

这里为ADVISOR功能模块导致，而且主要是WRI$_ADV_OBJECTS表及其索引
分析主要对象

SQL>  COL SEGMENT_NAME FORMAT A30
SQL>  COL OWNER FORMAT A10
SQL>  COL TABLESPACE_NAME FORMAT A10
SQL>  COL SEGMENT_TYPE FORMAT A15
SQL> SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024
 2  "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE
 3  TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10;
SEGMENT_NAME                   OWNER      Name         SIZE(MB) SEGMENT_TYPE
------------------------------ ---------- ---------- ---------- ---------------
WRI$_ADV_OBJECTS               SYS        SYSAUX          15675 TABLE
WRI$_ADV_OBJECTS_IDX_01        SYS        SYSAUX           8764 INDEX
WRI$_ADV_OBJECTS_PK            SYS        SYSAUX           5959 INDEX
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST SYS        SYSAUX            488 INDEX
I_WRI$_OPTSTAT_H_ST            SYS        SYSAUX            249 INDEX
SYS_LOB0000007350C00005$$      SYS        SYSAUX       133.1875 LOBSEGMENT
SYS_LOB0000010641C00038$$      SYS        SYSAUX       110.1875 LOBSEGMENT
WRH$_SQL_PLAN                  SYS        SYSAUX             64 TABLE
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST  SYS        SYSAUX             51 INDEX
SYS_LOB0000067470C00006$$      MDSYS      SYSAUX        50.1875 LOBSEGMENT
10 rows selected.

这里也比较明显主要是由于WRI$_ADV_OBJECTS表及其index占用空间较多导致.WRI$_ADV_OBJECTS表主要是12.2新特性Optimizer Statistics Advisor功能使用到的表,用来存储相关数据

清理WRI$_ADV_OBJECTS相关数据

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
EXEC DBMS_STATS.INIT_PACKAGE();
ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

禁用Optimizer Statistics Advisor Task

DECLARE
filter1 CLOB;
BEGIN
filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER('AUTO_STATS_ADVISOR_TASK','EXECUTE',NULL,'DISABLE');
END;
/

参考文档：SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor (Doc ID 2305512.1)

ORA-00702: bootstrap verison '' inconsistent with version '8.0.0.0.0'

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00702: bootstrap verison '' inconsistent with version '8.0.0.0.0'

数据库启动报ORA-01092 ORA-00704 ORA-00702错误

SQL> startup
ORACLE instance started.
Total System Global Area 3056513024 bytes
Fixed Size                  2257152 bytes
Variable Size             704646912 bytes
Database Buffers         2332033024 bytes
Redo Buffers               17575936 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00702: bootstrap verison '' inconsistent with version '8.0.0.0.0'
Process ID: 27344
Session ID: 191 Serial number: 3

查看alert日志

Mon Apr 09 16:22:34 2018
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 1383493834
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Mon Apr 09 16:22:39 2018
ALTER DATABASE OPEN
Thread 1 opened at log sequence 3
  Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_27344.trc:
ORA-00704: bootstrap process failure
ORA-00702: bootstrap verison '' inconsistent with version '8.0.0.0.0'
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_27344.trc:
ORA-00704: bootstrap process failure
ORA-00702: bootstrap verison '' inconsistent with version '8.0.0.0.0'
Error 704 happened during db open, shutting down database
USER (ospid: 27344): terminating the instance due to error 704
Instance terminated by USER, pid = 27344
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (27344) as a result of ORA-1092
Mon Apr 09 16:22:40 2018
ORA-1092 : opitsk aborting process

错误比较明显是由于数据库open过程中核心基表异常导致,出现此类错误一般是由于软件介质和db不匹配或者字典表的block故障导致.
官方说明

Versions 9.2, 10.1, 10.2, 11.1, 11.2, 12.1
Error:  ORA-00702 bootstrap verison '%s' inconsistent with version '%s'
---------------------------------------------------------------------------
Cause:  The reading version of the boostrap is incompatible with the current
	bootstrap version.
Action: Restore a version of the software that is compatible with this
	bootstrap version

由于核心基表等相关表处理比较特殊,如果您遭遇此类相关异常无法解决,需要恢复支持,请联系我们
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

数据库不能open 报ORA-7445 lmebucp错

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：数据库不能open 报ORA-7445 lmebucp错

有一个朋友数据库启动报错ORA-03113

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
ORA-03113: end-of-file on communication channel

alert日志报ORA-7445 lmebucp()+24错误

Sun Apr 08 08:05:07 CST 2018
ALTER DATABASE   MOUNT
Sun Apr 08 08:05:11 CST 2018
Setting recovery target incarnation to 2
Sun Apr 08 08:05:11 CST 2018
Successful mount of redo thread 1, with mount id 2650526067
Sun Apr 08 08:05:11 CST 2018
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Sun Apr 08 08:05:11 CST 2018
ALTER DATABASE OPEN
Sun Apr 08 08:05:11 CST 2018
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/t10g/redo01.log
Successful open of redo thread 1
Sun Apr 08 08:05:11 CST 2018
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Apr 08 08:05:11 CST 2018
SMON: enabling cache recovery
Sun Apr 08 08:05:11 CST 2018
Errors in file /u01/app/oracle/admin/t10g/udump/t10g_ora_32810.trc:
ORA-07445: exception encountered: core dump [lmebucp()+24] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []

对应trace文件

Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x0, PC: [0x37e6418, lmebucp()+24]
*** 2014-04-08 08:05:11.793
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)
-------------------- -------- -------------------- ----------------------------
ksedst()+31          call     ksedst1()            000000000 ? 000000001 ?
                                                   7F4238A22BD0 ? 7F4238A22C30 ?
                                                   7F4238A22B70 ? 000000000 ?
ksedmp()+610         call     ksedst()             000000000 ? 000000001 ?
                                                   7F4238A22BD0 ? 7F4238A22C30 ?
                                                   7F4238A22B70 ? 000000000 ?
ssexhd()+629         call     ksedmp()             000000003 ? 000000001 ?
                                                   7F4238A22BD0 ? 7F4238A22C30 ?
                                                   7F4238A22B70 ? 000000000 ?
__restore_rt()+0     call     ssexhd()             00000000B ? 7F4238A23BF0 ?
                                                   7F4238A23AC0 ? 7F4238A22C30 ?
                                                   7F4238A22B70 ? 000000000 ?
lmebucp()+24         signal   __restore_rt()       000000000 ? 000008C00 ?
                                                   7FFF0F778790 ? 000000009 ?
                                                   000000000 ? 00000000D ?
kqlbebs()+1468       call     lmebucp()            000000000 ? 000008C00 ?
                                                   7FFF0F778790 ? 000000009 ?
                                                   000000000 ? 00000000D ?
kqlblfc()+172        call     kqlbebs()            000000000 ? 0BC119DE0 ?
                                                   7FFF0F778790 ? 000000009 ?
                                                   000000000 ? 00000000D ?
adbdrv()+58009       call     kqlblfc()            000000000 ? 7FFF0F77F610 ?
                                                   7FFF0F778790 ? 000000009 ?
                                                   000000000 ? 00000000D ?
opiexe()+13745       call     adbdrv()             000000000 ? 7FFF0F77F610 ?
                                                   0B9FFF9A8 ? 000000009 ?
                                                   000000000 ? 00000000D ?
opiosq0()+3398       call     opiexe()             000000004 ? 000000000 ?
                                                   7FFF0F7807CC ? 000000001 ?
                                                   000000000 ? 00000000D ?
kpooprx()+318        call     opiosq0()            000000003 ? 00000000E ?
                                                   7FFF0F780AF8 ? 0000000A4 ?
                                                   000000000 ? 600000013 ?
kpoal8()+783         call     kpooprx()            7FFF0F783CDC ? 7FFF0F781D30 ?
                                                   000000013 ? 000000001 ?
                                                   000000000 ? 600000013 ?
opiodr()+1184        call     kpoal8()             00000005E ? 000000017 ?
                                                   7FFF0F783CD8 ? 000000001 ?
                                                   000000001 ? 600000013 ?
ttcpip()+1226        call     opiodr()             00000005E ? 000000017 ?
                                                   7FFF0F783CD8 ? 000000000 ?
                                                   005BEBD70 ? 600000013 ?
opitsk()+1310        call     ttcpip()             006AF1FB0 ? 0054A6760 ?
                                                   7FFF0F783CD8 ? 000000000 ?
                                                   7FFF0F7837D8 ? 7FFF0F783E40 ?
opiino()+1024        call     opitsk()             000000003 ? 000000000 ?
                                                   7FFF0F783CD8 ? 000000001 ?
                                                   000000000 ? 721000200000001 ?
opiodr()+1184        call     opiino()             00000003C ? 000000004 ?
                                                   7FFF0F784ED8 ? 000000001 ?
                                                   000000000 ? 721000200000001 ?
opidrv()+548         call     opiodr()             00000003C ? 000000004 ?
                                                   7FFF0F784ED8 ? 000000000 ?
                                                   005BEB820 ? 721000200000001 ?
sou2o()+114          call     opidrv()             00000003C ? 000000004 ?
                                                   7FFF0F784ED8 ? 000000000 ?
                                                   005BEB820 ? 721000200000001 ?
opimai_real()+163    call     sou2o()              7FFF0F784EB0 ? 00000003C ?
                                                   000000004 ? 7FFF0F784ED8 ?
                                                   005BEB820 ? 721000200000001 ?
main()+116           call     opimai_real()        000000002 ? 7FFF0F784F40 ?
                                                   000000004 ? 7FFF0F784ED8 ?
                                                   005BEB820 ? 721000200000001 ?
__libc_start_main()  call     main()               000000002 ? 7FFF0F784F40 ?
+244                                               000000004 ? 7FFF0F784ED8 ?
                                                   005BEB820 ? 721000200000001 ?
_start()+41          call     __libc_start_main()  00072D108 ? 000000002 ?
                                                   7FFF0F785098 ? 000000000 ?
                                                   005BEB820 ? 000000002 ?
--------------------- Binary Stack Dump ---------------------

10046定位语句

SQL> staRTUP MOUNT
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/t10g/udump/t10g_ora_32908.trc
SQL> alter database open;
ERROR:
ORA-03113: end-of-file on communication channel
SQL>
PARSING IN CURSOR #2 len=55 dep=1 uid=0 oct=3 lid=0 tim=1463569438249596 hv=2111436465 ad='beb13e10'
select line#, sql_text from bootstrap$ where obj# != :1
END OF STMT
PARSE #2:c=0,e=471,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1463569438249594
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=7f8f940fadc0  bln=22  avl=02  flg=05
  value=56
EXEC #2:c=2000,e=61246,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1463569438310929
WAIT #2: nam='db file sequential read' ela= 41 file#=1 block#=377 blocks=1 obj#=56 tim=1463569438311099
WAIT #2: nam='db file scattered read' ela= 73 file#=1 block#=378 blocks=3 obj#=56 tim=1463569438311528
FETCH #2:c=1000,e=775,p=4,cr=5,cu=0,mis=0,r=0,dep=1,og=4,tim=1463569438311772
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x0, PC: [0x37e6418, lmebucp()+24]
*** 2018-04-08 08:11:44.840
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

通过这里基本上可以定位，报错是由于bootstrap$中对象异常导致。由于该对象比较特殊,使用一些特殊方法进行处理,数据库正常启动成功。如果需要相关技术支持请联系我们，提供专业ORACLE数据库恢复技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

分布式存储crash导致ORACLE坏块原因分析

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：分布式存储crash导致ORACLE坏块原因分析

oracle运行在分布式存储中，我们在实验室模拟所有存储节点故障(整个存储断电),然后加电，数据库启动没有坏块，但是有客户在自己的环境做测试，发现数据库启动有大量坏块(几次测试问题依旧)。
客户异常环境报错汇总
模拟存储故障后，数据库报错信息,主要体现犹豫存储模拟故障,数据库读写io失败ORA-15081，ORA-27072，ORA-27061

Thread 2 cannot allocate new log, sequence 488
Checkpoint not complete
  Current log# 3 seq# 487 mem# 0: +DATADG/testdb/onlinelog/group_3.318.970157559
  Current log# 3 seq# 487 mem# 1: +ARCHDG/testdb/onlinelog/group_3.616.970157559
Thu Mar 08 16:17:08 2018
WARNING: Read Failed. group:2 disk:0 AU:6925 offset:49152 size:16384
WARNING: failed to read mirror side 1 of virtual extent 4 logical extent 0 of
file 313 in group [2.1286527068] from disk DATADG_0000  allocation unit 6925 reason error;
if possible, will try another mirror side
Thu Mar 08 16:17:08 2018
WARNING: Read Failed. group:2 disk:0 AU:6921 offset:16384 size:16384
WARNING: failed to read mirror side 1 of virtual extent 0 logical extent 0
of file 313 in group [2.1286527068] from disk DATADG_0000  allocation unit 6921 reason error;
 if possible, will try another mirror side
Errors in file /oracle/app/db/diag/rdbms/testdb/testdb2/trace/testdb2_lmon_21091.trc:
ORA-00202: control file: '+DATADG/testdb/controlfile/current.313.970157461'
ORA-15081: failed to submit an I/O operation to a disk
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 56729696
Additional information: -1
Errors in file /oracle/app/db/diag/rdbms/testdb/testdb2/trace/testdb2_lgwr_21111.trc:
ORA-00202: control file: '+DATADG/testdb/controlfile/current.313.970157461'
ORA-15081: failed to submit an I/O operation to a disk
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 56696864
Additional information: -1
Thu Mar 08 16:17:08 2018
WARNING: Write Failed. group:1 disk:0 AU:56970 offset:65536 size:16384
WARNING: Write Failed. group:2 disk:0 AU:6921 offset:65536 size:16384
…………
WARNING: failed to write mirror side 1 of virtual extent 147 logical extent
0 of file 335 in group 2 on disk 0 allocation unit 11789
Errors in file /oracle/app/db/diag/rdbms/testdb/testdb2/trace/testdb2_dbw0_21109.trc:
ORA-15080: synchronous I/O operation to a disk failed
ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 8192
WARNING: failed to write mirror side 1 of virtual extent 147 logical extent
0 of file 335 in group 2 on disk 0 allocation unit 11789
KCF: read, write or open error, block=0xddec online=1
        file=18 '+DATADG/testdb/datafile/soe.333.970157833'
        error=15081 txt: ''
Errors in file /oracle/app/db/diag/rdbms/testdb/testdb2/trace/testdb2_dbw0_21109.trc:
Thu Mar 08 16:17:09 2018
System state dump requested by (instance=2, osid=21113 (CKPT)), summary=[abnormal instance termination].
System State dumped to trace file /oracle/app/db/diag/rdbms/testdb/testdb2/trace/testdb2_diag_21081_20180308161709.trc
Thu Mar 08 16:17:09 2018
ORA-1092 : opitsk aborting process
Thu Mar 08 16:17:12 2018
ORA-1092 : opitsk aborting process
Instance terminated by CKPT, pid = 21113

asm日志报错信息，主要也是体现在io的读写错误上Linux-x86_64 Error: 5: Input/output error

Thu Mar 08 16:17:20 2018
Errors in file /oracle/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_9636.trc:
ORA-17503: ksfdopn:2 Failed to open file +DATADG/testdb/spfiletestdb.ora
ORA-15001: diskgroup "DATADG" does not exist or is not mounted
Thu Mar 08 16:17:37 2018
WARNING: Write Failed. group:3 disk:0 AU:1 offset:4190208 size:4096
Thu Mar 08 16:17:37 2018
WARNING: Write Failed. group:3 disk:0 AU:22 offset:143360 size:4096
Errors in file /oracle/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_20679.trc:
ORA-15080: synchronous I/O operation to a disk failed
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 180504
Additional information: -1
WARNING: Hbeat write to PST disk 0.3916310704 in group 3 failed. [4]
WARNING: failed to write mirror side 1 of virtual extent 0 logical extent 0
of file 255 in group 3 on disk 0 allocation unit 22
Errors in file /oracle/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_20679.trc:
ORA-15081: failed to submit an I/O operation to a disk
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 180504
Additional information: -1
Thu Mar 08 16:17:37 2018
NOTE: process _b001_+asm2 (9546) initiating offline of
disk 0.3916310704 (OCRDG_0000) with mask 0x7e in group 3
NOTE: initiating PST update: grp = 3, dsk = 0/0xe96e28b0, mask = 0x6a, op = clear
GMON updating disk modes for group 3 at 15 for pid 27, osid 9546
ERROR: no read quorum in group: required 1, found 0 disks
Thu Mar 08 16:17:37 2018
NOTE: cache dismounting (not clean) group 3/0x4CAED85D (OCRDG)
WARNING: Offline for disk OCRDG_0000 in mode 0x7f failed.
NOTE: messaging CKPT to quiesce pins Unix process pid: 9505, image: oracle@dbtest02 (B000)
Thu Mar 08 16:17:37 2018
NOTE: halting all I/Os to diskgroup 3 (OCRDG)
Thu Mar 08 16:17:37 2018
NOTE: LGWR doing non-clean dismount of group 3 (OCRDG)
NOTE: LGWR sync ABA=11.6 last written ABA 11.6
WARNING: Write Failed. group:3 disk:0 AU:22 offset:102400 size:4096
Errors in file /oracle/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_20679.trc:
ORA-15080: synchronous I/O operation to a disk failed
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 180424
Additional information: -1
WARNING: failed to write mirror side 1 of virtual extent 0 logical extent
0 of file 255 in group 3 on disk 0 allocation unit 22
Errors in file /oracle/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_20679.trc:
ORA-15081: failed to submit an I/O operation to a disk
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 180424
Additional information: -1
WARNING: Write Failed. group:3 disk:0 AU:22 offset:147456 size:4096
Errors in file /oracle/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_20679.trc:
ORA-15080: synchronous I/O operation to a disk failed
ORA-27072: File I/O error
…………
Thu Mar 08 16:17:37 2018
ERROR: -9(Error 27061, OS Error (Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 512)
)
ERROR: -9(Error 27061, OS Error (Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 512)
)
ERROR: -9(Error 27061, OS Error (Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 512)
)

存储恢复之后，asm mount磁盘组没有报任何错误

SQL> ALTER DISKGROUP ALL MOUNT /* asm agent call crs *//* {0:0:2} */
NOTE: Diskgroups listed in ASM_DISKGROUPS are
         ARCHDG
         DATADG
NOTE: Diskgroup used for Voting files is:
         OCRDG
Diskgroup with spfile:OCRDG
Diskgroup used for OCR is:OCRDG
NOTE: cache registered group ARCHDG number=1 incarn=0xb81889ff
NOTE: cache began mount (first) of group ARCHDG number=1 incarn=0xb81889ff
NOTE: cache registered group DATADG number=2 incarn=0xb8288a00
NOTE: cache began mount (first) of group DATADG number=2 incarn=0xb8288a00
NOTE: cache registered group OCRDG number=3 incarn=0xb8288a01
NOTE: cache began mount (first) of group OCRDG number=3 incarn=0xb8288a01
NOTE: Loaded library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so
NOTE: Assigning number (1,0) to disk (/dev/oracleasm/disks/ARCHDISK01)
NOTE: Assigning number (2,0) to disk (/dev/oracleasm/disks/DATADISK01)
NOTE: Assigning number (3,0) to disk (/dev/oracleasm/disks/OCRDISK01)
NOTE: GMON heartbeating for grp 1
GMON querying group 1 at 5 for pid 23, osid 3303
NOTE: cache opening disk 0 of grp 1: ARCHDG_0000 path:/dev/oracleasm/disks/ARCHDISK01
NOTE: F1X0 found on disk 0 au 2 fcn 0.0
NOTE: cache mounting (first) external redundancy group 1/0xB81889FF (ARCHDG)
* allocate domain 1, invalid = TRUE
NOTE: attached to recovery domain 1
NOTE: starting recovery of thread=1 ckpt=13.6287 group=1 (ARCHDG)
NOTE: starting recovery of thread=2 ckpt=12.7985 group=1 (ARCHDG)
NOTE: advancing ckpt for group 1 (ARCHDG) thread=1 ckpt=13.6287
NOTE: advancing ckpt for group 1 (ARCHDG) thread=2 ckpt=12.7985
NOTE: cache recovered group 1 to fcn 0.122934
NOTE: redo buffer size is 256 blocks (1053184 bytes)
NOTE: LGWR attempting to mount thread 1 for diskgroup 1 (ARCHDG)
Process LGWR (pid 3287) is running at high priority QoS for Exadata I/O
NOTE: LGWR found thread 1 closed at ABA 13.6286
NOTE: LGWR mounted thread 1 for diskgroup 1 (ARCHDG)
NOTE: LGWR opening thread 1 at fcn 0.122934 ABA 14.6287
NOTE: cache mounting group 1/0xB81889FF (ARCHDG) succeeded
NOTE: cache ending mount (success) of group ARCHDG number=1 incarn=0xb81889ff
NOTE: GMON heartbeating for grp 2
GMON querying group 2 at 7 for pid 23, osid 3303
NOTE: cache opening disk 0 of grp 2: DATADG_0000 path:/dev/oracleasm/disks/DATADISK01
NOTE: F1X0 found on disk 0 au 2 fcn 0.0
NOTE: cache mounting (first) external redundancy group 2/0xB8288A00 (DATADG)
* allocate domain 2, invalid = TRUE
NOTE: attached to recovery domain 2
NOTE: starting recovery of thread=1 ckpt=12.2323 group=2 (DATADG)
NOTE: starting recovery of thread=2 ckpt=11.2681 group=2 (DATADG)
NOTE: advancing ckpt for group 2 (DATADG) thread=1 ckpt=12.2326
NOTE: advancing ckpt for group 2 (DATADG) thread=2 ckpt=11.2687
NOTE: cache recovered group 2 to fcn 0.21395
NOTE: redo buffer size is 512 blocks (2101760 bytes)
NOTE: LGWR attempting to mount thread 1 for diskgroup 2 (DATADG)
NOTE: LGWR found thread 1 closed at ABA 12.2325
NOTE: LGWR mounted thread 1 for diskgroup 2 (DATADG)
NOTE: LGWR opening thread 1 at fcn 0.21395 ABA 13.2326
NOTE: cache mounting group 2/0xB8288A00 (DATADG) succeeded
NOTE: cache ending mount (success) of group DATADG number=2 incarn=0xb8288a00
NOTE: GMON heartbeating for grp 3
GMON querying group 3 at 9 for pid 23, osid 3303
NOTE: cache opening disk 0 of grp 3: OCRDG_0000 path:/dev/oracleasm/disks/OCRDISK01
NOTE: F1X0 found on disk 0 au 2 fcn 0.0
NOTE: cache mounting (first) external redundancy group 3/0xB8288A01 (OCRDG)
* allocate domain 3, invalid = TRUE
Thu Mar 08 17:00:24 2018
NOTE: attached to recovery domain 3
NOTE: starting recovery of thread=1 ckpt=13.55 group=3 (OCRDG)
NOTE: starting recovery of thread=2 ckpt=11.7 group=3 (OCRDG)
NOTE: advancing ckpt for group 3 (OCRDG) thread=1 ckpt=13.55
NOTE: advancing ckpt for group 3 (OCRDG) thread=2 ckpt=11.7
NOTE: cache recovered group 3 to fcn 0.851
NOTE: redo buffer size is 512 blocks (2101760 bytes)
Thu Mar 08 17:00:24 2018
NOTE: LGWR attempting to mount thread 1 for diskgroup 3 (OCRDG)
NOTE: LGWR found thread 1 closed at ABA 13.54
NOTE: LGWR mounted thread 1 for diskgroup 3 (OCRDG)
NOTE: LGWR opening thread 1 at fcn 0.851 ABA 14.55
NOTE: cache mounting group 3/0xB8288A01 (OCRDG) succeeded
NOTE: cache ending mount (success) of group OCRDG number=3 incarn=0xb8288a01
Thu Mar 08 17:00:24 2018
NOTE: Instance updated compatible.asm to 11.2.0.0.0 for grp 1
SUCCESS: diskgroup ARCHDG was mounted
NOTE: Instance updated compatible.asm to 11.2.0.0.0 for grp 2
SUCCESS: diskgroup DATADG was mounted
NOTE: Instance updated compatible.asm to 11.2.0.0.0 for grp 3
SUCCESS: diskgroup OCRDG was mounted
SUCCESS: ALTER DISKGROUP ALL MOUNT /* asm agent call crs *//* {0:0:2} */

数据库虽然启动成功了,但是大量的坏块生产ORA-01578，ORA-01110

ALTER DATABASE OPEN /* db agent *//* {2:40834:2} */
This instance was first to open
Beginning crash recovery of 2 threads
 parallel recovery started with 7 processes
Started redo scan
Completed redo scan
 read 107566 KB redo, 10569 data blocks need recovery
Started redo application at
 Thread 1: logseq 767, block 101851
 Thread 2: logseq 486, block 91861
Recovery of Online Redo Log: Thread 1 Group 1 Seq 767 Reading mem 0
  Mem# 0: +DATADG/testdb/onlinelog/group_1.314.970157463
  Mem# 1: +ARCHDG/testdb/onlinelog/group_1.614.970157463
Recovery of Online Redo Log: Thread 2 Group 4 Seq 486 Reading mem 0
  Mem# 0: +DATADG/testdb/onlinelog/group_4.319.970157561
  Mem# 1: +ARCHDG/testdb/onlinelog/group_4.617.970157561
Recovery of Online Redo Log: Thread 1 Group 2 Seq 768 Reading mem 0
  Mem# 0: +DATADG/testdb/onlinelog/group_2.315.970157463
  Mem# 1: +ARCHDG/testdb/onlinelog/group_2.615.970157463
Recovery of Online Redo Log: Thread 2 Group 3 Seq 487 Reading mem 0
  Mem# 0: +DATADG/testdb/onlinelog/group_3.318.970157559
  Mem# 1: +ARCHDG/testdb/onlinelog/group_3.616.970157559
Thu Mar 08 17:01:11 2018
Hex dump of (file 12, block 126469) in trace file /oracle/app/db/diag/rdbms/testdb/testdb2/trace/testdb2_p001_4090.trc
Corrupt block relative dba: 0x0301ee05 (file 12, block 126469)
Fractured block found during crash/instance recovery
Data in bad block:
 type: 32 format: 2 rdba: 0x0301ee05
 last change scn: 0x0000.00446cbb seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xeac22001
 check value in block header: 0xbd0c
 computed block checksum: 0x8679
Reading datafile '+DATADG/testdb/datafile/soe.327.970157825' for corruption at rdba: 0x0301ee05 (file 12, block 126469)
Reread (file 12, block 126469) found same corrupt data (no logical check)
Completed redo application of 84.56MB
Completed crash recovery at
 Thread 1: logseq 768, block 102395, scn 4586960
 Thread 2: logseq 487, block 101664, scn 4587050
 10569 data blocks read, 10569 data blocks written, 107566 redo k-bytes read
ARCH: STARTING ARCH PROCESSES
Thu Mar 08 17:01:12 2018
ARC0 started with pid=43, OS id=4151
Thu Mar 08 17:01:13 2018
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thu Mar 08 17:01:13 2018
ARC1 started with pid=44, OS id=4153
Thu Mar 08 17:01:13 2018
ARC2 started with pid=45, OS id=4155
ARC1: Archival started
ARC2: Archival started
Thu Mar 08 17:01:13 2018
ARC3 started with pid=46, OS id=4157
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 advanced to log sequence 769 (thread recovery)
Picked broadcast on commit scheme to generate SCNs
Thu Mar 08 17:01:13 2018
Thread 2 advanced to log sequence 488 (thread open)
Thread 2 opened at log sequence 488
  Current log# 4 seq# 488 mem# 0: +DATADG/testdb/onlinelog/group_4.319.970157561
  Current log# 4 seq# 488 mem# 1: +ARCHDG/testdb/onlinelog/group_4.617.970157561
Successful open of redo thread 2
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Mar 08 17:01:13 2018
SMON: enabling cache recovery
Archived Log entry 1249 added for thread 2 sequence 487 ID 0xa41f5c11 dest 1:
[4046] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:4294332240 end:4294332460 diff:220 (2 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
Starting background process GTX0
Thu Mar 08 17:01:14 2018
GTX0 started with pid=55, OS id=4181
Starting background process RCBG
Thu Mar 08 17:01:14 2018
RCBG started with pid=57, OS id=4185
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Mar 08 17:01:14 2018
QMNC started with pid=61, OS id=4195
Thu Mar 08 17:01:15 2018
Hex dump of (file 9, block 43523) in trace file /oracle/app/db/diag/rdbms/testdb/testdb2/trace/testdb2_p009_4175.trc
Corrupt block relative dba: 0x0240aa03 (file 9, block 43523)
Fractured block found during buffer read
Data in bad block:
 type: 32 format: 2 rdba: 0x0240aa03
 last change scn: 0x0000.0044ccc4 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xd94f2001
 check value in block header: 0xb09c
 computed block checksum: 0x158b
Reading datafile '+DATADG/testdb/datafile/soe.324.970157821' for corruption at rdba: 0x0240aa03 (file 9, block 43523)
Reread (file 9, block 43523) found same corrupt data (no logical check)
Errors in file /oracle/app/db/diag/rdbms/testdb/testdb2/trace/testdb2_p009_4175.trc  (incident=216418):
ORA-01578: ORACLE data block corrupted (file # 9, block # 43523)
ORA-01110: data file 9: '+DATADG/testdb/datafile/soe.324.970157821'
Incident details in: /oracle/app/db/diag/rdbms/testdb/testdb2/incident/incdir_216418/testdb2_p009_4175_i216418.trc
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Thu Mar 08 17:01:15 2018
Hex dump of (file 14, block 71173) in trace file /oracle/app/db/diag/rdbms/testdb/testdb2/trace/testdb2_p003_4094.trc
Corrupt block relative dba: 0x03811605 (file 14, block 71173)
Fractured block found during buffer read
Data in bad block:
 type: 32 format: 2 rdba: 0x03811605
 last change scn: 0x0000.0043916d seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x93662001
 check value in block header: 0xad98
 computed block checksum: 0x20b
Reading datafile '+DATADG/testdb/datafile/soe.329.970157831' for corruption at rdba: 0x03811605 (file 14, block 71173)
Reread (file 14, block 71173) found same corrupt data (no logical check)
Errors in file /oracle/app/db/diag/rdbms/testdb/testdb2/trace/testdb2_p003_4094.trc  (incident=216306):
ORA-01578: ORACLE data block corrupted (file # 14, block # 71173)
ORA-01110: data file 14: '+DATADG/testdb/datafile/soe.329.970157831'
Incident details in: /oracle/app/db/diag/rdbms/testdb/testdb2/incident/incdir_216306/testdb2_p003_4094_i216306.trc
Hex dump of (file 12, block 112385) in trace file /oracle/app/db/diag/rdbms/testdb/testdb2/trace/testdb2_smon_4005.trc
Corrupt block relative dba: 0x0301b701 (file 12, block 112385)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x0301b701
 last change scn: 0x0000.003cb146 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1d030601
 check value in block header: 0x1e62
 computed block checksum: 0x7a47
Reading datafile '+DATADG/testdb/datafile/soe.327.970157825' for corruption at rdba: 0x0301b701 (file 12, block 112385)
Reread (file 12, block 112385) found same corrupt data (no logical check)
Errors in file /oracle/app/db/diag/rdbms/testdb/testdb2/trace/testdb2_smon_4005.trc  (incident=216170):
ORA-01578: ORACLE data block corrupted (file # 12, block # 112385)
ORA-01110: data file 12: '+DATADG/testdb/datafile/soe.327.970157825'
Incident details in: /oracle/app/db/diag/rdbms/testdb/testdb2/incident/incdir_216170/testdb2_smon_4005_i216170.trc
Errors in file /oracle/app/db/diag/rdbms/testdb/testdb2/trace/testdb2_p003_4094.trc  (incident=216307):
ORA-01578: ORACLE data block corrupted (file # , block # )
Incident details in: /oracle/app/db/diag/rdbms/testdb/testdb2/incident/incdir_216307/testdb2_p003_4094_i216307.trc
Completed: ALTER DATABASE OPEN /* db agent *//* {2:40834:2} */

rman 检查坏块信息，发现有大量坏块，而且主要坏块类型是FRACTURED(折断)

SQL> select * from v$database_block_corruption;
     FILE#     BLOCK#	  BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
	12     126469	       1		  0 FRACTURED
	12     112385	       1		  0 FRACTURED
	14	71173	       1		  0 FRACTURED
	 9	43523	       1		  0 FRACTURED
	15	44801	       1		  0 FRACTURED
	15	44855	       1		  0 FRACTURED
	15	44862	       1		  0 FRACTURED
	11	56252	       1		  0 FRACTURED
	12     116481	       1		  0 CHECKSUM
	12     116535	       1		  0 FRACTURED
	15	46903	       1		  0 FRACTURED
	12     108289	       1		  0 FRACTURED
	12     108343	       1		  0 FRACTURED
	 8	95872	       1		  0 FRACTURED
	 8	99712	       1		  0 FRACTURED
	 8     102783	       1		  0 FRACTURED
	 8     104974	       1		  0 FRACTURED
	 8     105856	       1		  0 FRACTURED
	 8     105998	       1		  0 FRACTURED
	 8     109120	       1		  0 FRACTURED
	 8     110016	       1		  0 FRACTURED
	 8     110092	       1		  0 FRACTURED
	 8     116226	       1		  0 FRACTURED
	 8     116231	       1		  0 FRACTURED
	 8     119295	       1		  0 FRACTURED
	 8     130559	       1		  0 FRACTURED
	12     113163	       1		  0 FRACTURED
	12     118283	       1		  0 FRACTURED
	12     119553	       1		  0 FRACTURED
	12     119608	       1		  0 FRACTURED
	12     120576	       1		  0 FRACTURED
	12     120632	       1		  0 FRACTURED
	12     120639	       1		  0 FRACTURED
	12     123407	       1		  0 FRACTURED
	 9	38411	       1		  0 FRACTURED
	 9	67647	       1		  0 FRACTURED
	 9     109068	       1		  0 FRACTURED
	 9     109070	       1		  0 FRACTURED
	11	99839	       1		  0 FRACTURED
	11     104960	       1		  0 FRACTURED
	13	63214	       1		  0 FRACTURED
	13	65097	       1		  0 FRACTURED
	13	71183	       1		  0 FRACTURED
	13	77319	       1		  0 FRACTURED
	13     103104	       1		  0 FRACTURED
	13     107072	       1		  0 FRACTURED
	13     110082	       1		  0 FRACTURED
	13     111106	       1		  0 FRACTURED
	13     114752	       1		  0 FRACTURED
	14	72196	       1		  0 FRACTURED
	14	75275	       1		  0 FRACTURED
	 7	76293	       1		  0 FRACTURED
	 7	76296	       1		  0 FRACTURED
	 7	76415	       1		  0 FRACTURED
	 7	76864	       1		  0 FRACTURED
	15	49976	       1		  0 FRACTURED
	15	81413	       1		  0 FRACTURED
	 2	61512	       1		  0 FRACTURED
	19	35338	       1		  0 FRACTURED
	19	42687	       1		  0 FRACTURED
60 rows selected.

对比实验室和客户环境配置
实验室环境

[oracle@i-q2ghx82t ~]$ more /etc/issue
CentOS release 6.8 (Final)
Kernel \r on an \m
SQL> select path from v$asm_disk;
PATH
----------------------------------------
/dev/asm-disk011
/dev/asm-disk001
/dev/asm-disk014
/dev/asm-disk002
/dev/asm-disk003
/dev/asm-disk015
/dev/asm-disk012
/dev/asm-disk013
8 rows selected.
[root@i-q2ghx82t ~]# fdisk -l|grep vd
Disk /dev/vda: 107.4 GB, 107374182400 bytes
/dev/vda1   *           1       13055   104856576   83  Linux
Disk /dev/vdb: 34.4 GB, 34359738368 bytes
Disk /dev/vdc: 107.4 GB, 107374182400 bytes
Disk /dev/vdd: 268.4 GB, 268435456000 bytes
/dev/vdd1               1       32635   262140606   83  Linux
Disk /dev/vde: 1073.7 GB, 1073741824000 bytes
Disk /dev/vdf: 53.7 GB, 53687091200 bytes
/dev/vdf1               1      104025    52428568+  83  Linux
Disk /dev/vdg: 107.4 GB, 107374182400 bytes
Disk /dev/vdh: 1073.7 GB, 1073741824000 bytes
Disk /dev/vdi: 107.4 GB, 107374182400 bytes
Disk /dev/vdj: 1073.7 GB, 1073741824000 bytes
Disk /dev/vdk: 1073.7 GB, 1073741824000 bytes
Disk /dev/vdl: 1073.7 GB, 1073741824000 bytes
[oracle@i-q2ghx82t ~]$ ls -l /dev/asm-*
lrwxrwxrwx 1 root root 3 Mar 10 17:05 /dev/asm-disk001 -> vdg
lrwxrwxrwx 1 root root 3 Mar 10 17:05 /dev/asm-disk002 -> vdc
lrwxrwxrwx 1 root root 3 Mar 10 17:05 /dev/asm-disk003 -> vdi
lrwxrwxrwx 1 root root 3 Mar 10 17:05 /dev/asm-disk011 -> vde
lrwxrwxrwx 1 root root 3 Mar 10 17:05 /dev/asm-disk012 -> vdh
lrwxrwxrwx 1 root root 3 Mar 10 17:05 /dev/asm-disk013 -> vdl
lrwxrwxrwx 1 root root 3 Mar 10 17:05 /dev/asm-disk014 -> vdj
lrwxrwxrwx 1 root root 3 Mar 10 17:05 /dev/asm-disk015 -> vdk
[root@i-q2ghx82t ~]# fdisk -l /dev/vdj
Disk /dev/vdj: 1073.7 GB, 1073741824000 bytes
16 heads, 63 sectors/track, 2080507 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 4096 bytes
Disk identifier: 0x00000000

客户环境

[oracle@dbtest02 ~]$ more /etc/issue
Red Hat Enterprise Linux Server release 6.6 (Santiago)
Kernel \r on an \m
SQL> select path from v$asm_disk;
PATH
----------------------------------------
/dev/oracleasm/disks/ARCHDISK01
/dev/oracleasm/disks/OCRDISK01
/dev/oracleasm/disks/DATADISK01
[root@dbtest02 ~]# oracleasm listdisks
ARCHDISK01
DATADISK01
OCRDISK01
[root@dbtest02 ~]# oracleasm querydisk -p DATADISK01
Disk "DATADISK01" is a valid ASM disk
/dev/vdc1: LABEL="DATADISK01" TYPE="oracleasm"
[root@dbtest02 ~]# oracleasm querydisk -p ARCHDISK01
Disk "ARCHDISK01" is a valid ASM disk
/dev/vde1: LABEL="ARCHDISK01" TYPE="oracleasm"
[root@dbtest02 ~]# oracleasm querydisk -p OCRDISK01
Disk "OCRDISK01" is a valid ASM disk
/dev/vdd1: LABEL="OCRDISK01" TYPE="oracleasm"
[root@dbtest02 ~]# fdisk -l|grep vd
Disk /dev/vda: 53.7 GB, 53687091200 bytes
/dev/vda1               1        6528    52427776   83  Linux
Disk /dev/vdb: 17.2 GB, 17179869184 bytes
Disk /dev/vdc: 1073.7 GB, 1073741824000 bytes
/dev/vdc1               1     2080507  1048575496+  83  Linux
Disk /dev/vdd: 107.4 GB, 107374182400 bytes
/dev/vdd1               1      208050   104857168+  83  Linux
Disk /dev/vde: 536.9 GB, 536870912000 bytes
/dev/vde1               1     1040253   524287480+  83  Linux
Disk /dev/vdf: 859.0 GB, 858993459200 bytes
/dev/vdf1               1     1664406   838860592+  83  Linux
[root@dbtest02 ~]# fdisk -l /dev/vdf
Disk /dev/vdf: 859.0 GB, 858993459200 bytes
16 heads, 63 sectors/track, 1664406 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 4096 bytes
Disk identifier: 0x1bdbf439
   Device Boot      Start         End      Blocks   Id  System
/dev/vdf1               1     1664406   838860592+  83  Linux
Partition 1 does not start on physical sector boundary.

这里比较明显:1)实验室环境使用的是udev,而客户环境使用的是asmlib.2)客户环境使用了分区,而实验室环境直接使用裸盘.

尝试在实验室环境使用asmlib

[root@i-q2ghx82t tmp]# ls -l *oracleasm*
-rw-r--r-- 1 root root 36288 Mar 10 13:09 kmod-oracleasm-2.0.8-13.el6_8.x86_64.rpm
-rw-r--r-- 1 root root 17168 Mar 10 13:09 oracleasmlib-2.0.12-1.el6.x86_64.rpm
-rw-r--r-- 1 root root 74984 Mar 10 13:09 oracleasm-support-2.1.8-1.el6.x86_64.rpm
[root@i-q2ghx82t tmp]# rpm -ivh kmod-oracleasm-2.0.8-13.el6_8.x86_64.rpm
warning: kmod-oracleasm-2.0.8-13.el6_8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:kmod-oracleasm         ########################################### [100%]
[root@i-q2ghx82t tmp]# rpm -ivh oracleasm*.rpm
warning: oracleasmlib-2.0.12-1.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:oracleasm-support      ########################################### [ 50%]
   2:oracleasmlib           ########################################### [100%]
[root@i-q2ghx82t tmp]#
[root@i-q2ghx82t tmp]# rpm -aq|grep oracleasm
kmod-oracleasm-2.0.8-13.el6_8.x86_64
oracleasmlib-2.0.12-1.el6.x86_64
oracleasm-support-2.1.8-1.el6.x86_64
[root@i-q2ghx82t dev]# oracleasm createdisk ASMVDL /dev/vdl
Device "/dev/vdl" is not a partition

测试比较明显，对于没有分区的分布式存储中的盘,无法直接被asmlib创建asm disk.如果要使用asmlib,需要对磁盘进行分区.

存储特征
通过咨询分布式存储开发,确认几点:
0. 整个存储是基于网络技术实现分布式存储内部数据同步
1. 整个分布式存储使用ssd卡,默认三副本
2. 三副本中有两个副本写入成功才会反馈给应用(数据库),io成功
3. 整个分布式存储，没有引入任何的cache.
4. 存储的最小io单元是4k,由于ssd卡决定的
5. 分布式存储划分出来的lun是自动实现快对齐的

故障原因
由于存储最小单元是4k,但是在os层面物理扇区为4k，逻辑扇区为512，客户为了使用asmlib,对磁盘进行了默认分区,而没有考虑块对齐.会使得在存储整体crash的时候,很多block由于没有块对齐,很可能被进行了拆分(本来一个4k的io，存储上对对应的一个原子io完成，但是现在这个os层面的4k被拆分成了多个io的可能性，使得os层面的4k的io无法有了原则性)，进而使得在数据库主机,存储突发异常的时候,有可能发生坏块.

处理建议
1. 在linux 6开始,对于asmlib,建议使用udev
2. 如果坚持asmlib,分区的时候考虑块对齐,不然会出现较大的性能下降,而且还会引起坏块的风险

Oracle中的主要字母缩写含义

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle中的主要字母缩写含义

ACD = Active Change Directory
ACFS = ASM Cluster File System
ADDM = Automatic Database Diagnostic Monitor
ADR = Automatic Diagnostic Repository
ADVM = ASM Dynamic Volume Manager
AIO = Asynchronous I/O
AMDU = ASM Metadata Dump Utility
AMM = Automatic Memory Management
ARC = Archive process
ASH = Active Session History
ASM = Automatic Storage Management
ASMB = ASM Background process
ASMCA = ASM Configuration Assistant
ASMCMD = ASM CoMmanD line utility
ASMLIB = ASM LIBrary tool
ASMM = Automatic Shared Memory Management
ASMSNMP = ASM Simple Network Management Protocol
AT = Allocation Table
ATA = Advanced Technology Attachment
AU = Allocation Unit
AWR = Automatic Workload Repository
BH = Block Header
BMC = Baseboard Management Controller
BS = Block Size
CBO = Cost-Based Optimizer
CF = Control File
CFS = Cluster FileSystem
CHM = Cluster Health Monitor
CIO = Concurrent I/O
CKPT = ChecKPoinT process
CLUVFY = CLUster VeriFy utility
COD = Continuing Operation Directory
CPU = Central Processing Unit or Critical Patch Update
CRM = Customer Relationship Management
CRS = Cluster Ready Services
CSS = Cluster Synchronization Services
CSV = Comma-Separated Values
CVM = Cluster Volume Manager
DB = DataBase
DBCA = DataBase Configuration Assistant
DBFS = DataBase File System
DBM = DataBase Machine
DBMS = DataBase Management Systems
DBPERF = DataBase PERFormance
DBV = DataBase Verification tool
DBW = DataBase Writer process
DD = Disk Directory or Data Description tool
DES = Database Excelleration Systems Inc.
DG = DiskGroup
DH = Disk Header
DIO = Direct I/O
DISM = Dynamic Intimate Shared Memory
DM = Device Mapper
DNFS = Direct Network File System
DRAM = Dynamic Random Access Memory
DSS = Decision Support System
DUL = Data UnLoader
DW = Data Warehouse
EIDE = Enhanced Integrated Drive Electronics
ERP = Enterprise Resource Planning
ETA = Estimated Time of Arrival
ETL = Extract Transform Load
FD = File Directory
FG = FailGroup
FRA = Flash Recovery Area or Fast Recovery Area
FS = FileSystem
FST = Free Segments Table
FTS = Full Table Scan
GC = Grid Control
GI = Grid Infrastructure
GUI = Graphical User Interface
HA = High Availability
HARD = Hardware Assisted Resilient Data
HB = Heart Beat
HBA = Host Bus Adapter
IDE = Integrated Drive Electronics
IIS = Internet Information Services
INST = INSTance
IO = Input/Output
IOPS = IO Per Second
IOT = Index Organized Table
IP = Internet Protocol
IPMI = Intelligent Platform Management Interface
JET = Joint Escalation Team
JFS = Journaled FileSystem
KB = KiloByte
KFED = Kernel Files metadata EDitor
KFOD = Kernel Files Osm disk[group] Discovery
LGWR = redo LoG WRiter process
LLT = Veritas Low Latency Transport protocol
LSNR = LiSteNeR
LUN = Logical Unit Number
LVM = Logical Volume Manager
MAA = Maximum Availability Architecture
MB = MegaByte
NAS = Network Attached Storage
NetApp = Network Appliance
NETCA = NETwork Configuration Assistant
NFS = Network FileSystem
NIC = Network Interface Controller
OCFS = Oracle Cluster FileSystem
OCR = Oracle Cluster Registry
ODM = Oracle Disk Manager
ODS = Operational Data Store
OEL = Oracle Enterprise Linux
OEM = Oracle Enterprise Manager
OID = Oracle Internet Directory
OLAP = OnLine Analytical Processing
OLTP = OnLine Transaction Processing
OMS = Oracle Management Service
OPATCH = Oracle PATCHing utility
OS = Operating System
OSCP = Oracle Storage Compatibility Program
OSW = OS Watcher
OSWFW = OS Watcher For Windows
OTN = Oracle Technology Network
OUI = Oracle Universal Installer
PB = PetaByte
PFILE = Parameter FILE
PGA = Program Global Area
PID = Proces ID
PL/SQL = Procedural Language/Structured Query Language
POC = Proof Of Concept
PROCWATCHER = PROCess WATCHER
PST = Partnership Status Table
PSU = Patch Set Update
PX = Parrallel eXecution
RAC = Real Application Cluster
RAID = Redundant Array of Independent Disks
RAM = Random Access Memory
RBAL = ReBALance process
RCA = Root Cause Analysis
RDA = Remote Diagnostic Agent
RDBMS = Relational DataBase Management System
RHEL = RedHat Enterprise Linux
RM = Resource Manager
RMAN = Recovery MANager
RPM = Resource Package Manager
SAN = Storage Area Network
SAS = Serial Attached SCSI
SATA = Serial Advanced Technology Attachment
SCAN = Single Client Access Name
SCSI = Small Computer System Interface
SGA = System Global Area
SLA = Service Level Agreement
SMF = Service Management Facility
SPFILE = Server Parameter FILE
SQL = Structured Query Language
SRDF = EMC Symmetrix Remote Data Facility
SSD = Solid State Disk
SVCTM = average SerViCe TiMe
SVM = Solaris Volume Manager
TB = TeraByte
TCP = Transmission Control Protocol
TDE = Transparent Data Encryption
TKPROF = Transient Kernel PROFile
TNS = Transparent Network Substrate
UDEV = Unix DEVice manager
UDP = User Datagram Protocol
UFG = Umbilicus ForeGround process
UFS = User FileSystem
VBG = Volume BackGround process
VCS = Veritas Cluster Server
VD = Voting Disk
VDBG = Volume Driver BackGround process
VIP = Virtual Internet Portocol
VLDB = Very Large DataBase
VM = Virtual Machine
VMB = Volume Membership BackGround process
VxFS = Veritas File System
VxVM = Veritas Volume Manager
XDB = XML DataBase

osw配置私网监控(oswprvtnet)

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：osw配置私网监控(oswprvtnet)

OS Watcher Black Box (OSWbb) 是一种 UNIX shell 脚本的集合，主要用于收集和归档操作系统和网络的度量，以便为诊断性能问题提供支持。OSWbb 作为服务器上的一组后台进程来运行，定期收集OS 数据，其调用 Unix 实用程序，如 vmstat、netstat 和 iostat 等。在最近的gi psu中已经把osw包含在tfa中了，但是默认不启用私网的监控，如果需要可以进行人工配置
OSWbb 使用 traceroute 命令获取这些私有网络的状态。每个操作系统对 traceroute 命令使用的参数都略有不同。基本 oswbb 目录中的示例Exampleprivate.net 文件中包含每个操作系统使用的语法。因此，这将导致在 UNIX 平台中的输出显示有所不同。OSWbb 按指定间隔运行 private.net 文件，并在归档目录下的 oswprvtnet 子目录中存储数据。数据存储在每小时归档的文件中。文件中的每个条目都包含一个时间戳，前缀为 ***，嵌入在顶部输出中。
tfa中含osw

[root@xifenfei01 ~]# ps -ef|grep osw
grid      17673      1  0 Jan30 ?        00:00:07 /bin/sh ./OSWatcher.sh 30 48 NONE /u01/app/grid/tfa/repository/suptools/xifenfei01/oswbb/grid/archive
grid      17841  17673  0 Jan30 ?        00:00:01 /bin/sh ./OSWatcherFM.sh 48 /u01/app/grid/tfa/repository/suptools/xifenfei01/oswbb/grid/archive
root      64159  64120  0 00:30 pts/0    00:00:00 grep osw

测试prvtnet.sh

[root@xifenfei01 oswbb]# cd /u01/app/grid/tfa/repository/suptools/xifenfei01/oswbb/grid/oswbb
[root@xifenfei01 oswbb]# more prvtnet.sh
echo "zzz ***"`date`
traceroute -r -F xifenfei01-priv
traceroute -r -F xifenfei02-priv
######################################################################
# DO NOT DELETE THE FOLLOWING LINE!!!!!!!!!!!!!!!!!!!!!
######################################################################
rm locks/lock.file
[root@xifenfei01 oswbb]# ./prvtnet.sh
zzz ***Tue Jan 30 18:02:49 CST 2018
traceroute to xifenfei01-priv (192.168.102.11), 30 hops max, 60 byte packets
 1  xifenfei01-priv (192.168.102.11)  0.010 ms  0.003 ms  0.002 ms
traceroute to xifenfei02-priv (192.168.102.14), 30 hops max, 60 byte packets
 1  xifenfei02-priv (192.168.102.14)  0.081 ms  0.076 ms  0.087 ms
rm: cannot remove `locks/lock.file': No such file or directory

配置private.net正式收集私网信息

[root@xifenfei01 oswbb]# find . -name OSWatcher.sh | xargs grep -i "private"
# Run traceroute for private networks if file private.net exists
if [ -x private.net ]; then
   ./private.net >> $OSWBB_ARCHIVE_DEST/oswprvtnet/${hostn}_prvtnet_$hour 2>&1 &

这里重要的是：
./OSWatcher.sh:# Run traceroute for private networks if file private.net exists
表示，在OSWatcher.sh中调用的文件名称是private.net
将prvtnet.sh复制为供OSWatcher.sh脚本调用的文件名称

[root@xifenfei01 oswbb]# cp prvtnet.sh private.net
[root@xifenfei01 oswbb]# chown grid:oinstall prvtnet.net
[root@xifenfei01 oswbb]# chmod +x prvtnet.net

关注内容

示例 1： 接口处于启动和响应状态：
traceroute to X.X.X.X, (X.X.X.X) 30 hops max, 1492 byte packets
1 X.X.X.X 1.015 ms 0.766 ms 0.755 ms
示例 2： 目标接口不是在直接连接的网络上，所以需要验证地址
         是否正确或其插入的交换机是否位于相同的 VLAN 上（或其他问题）：
traceroute to X.X.X.X, (X.X.X.X) 30 hops max, 40 byte packets
traceroute: host X.X.X.X is not on a directly-attached network
示例 3： 网络无法访问：
traceroute to X.X.X.X, (X.X.X.X) 30 hops max, 40 byte packets
Network is unreachable
示例 4： 网络无法访问：
xifenfei01_prvtnet_18.01.30.2000.dat-zzz ***Tue Jan 30 20:27:43 CST 2018
xifenfei01_prvtnet_18.01.30.2000.dat-traceroute to xifenfei01-priv (192.168.102.11), 30 hops max, 60 byte packets
xifenfei01_prvtnet_18.01.30.2000.dat- 1  xifenfei01-priv (192.168.102.11)  0.014 ms  0.004 ms  0.005 ms
xifenfei01_prvtnet_18.01.30.2000.dat-traceroute to xifenfei02-priv (192.168.102.14), 30 hops max, 60 byte packets
xifenfei01_prvtnet_18.01.30.2000.dat- 1  * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 2  * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 3  * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 4  * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 5  * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 6  * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 7  * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 8  * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 9  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-10  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-11  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-12  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-13  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-14  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-15  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-16  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-17  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-18  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-19  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-20  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-21  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-22  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-23  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-24  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-25  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-26  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-27  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-28  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-29  * * *
xifenfei01_prvtnet_18.01.30.2000.dat-30  * * *
xifenfei01_prvtnet_18.01.30.2000.dat:zzz ***Warning. Traceroute response is spanning snapshot intervals.

reliable message等待

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：reliable message等待

在一次给客户的rac poc测试中，发现大量reliable message等待，严重影响性能
数据库版本
11.2.0.4+最新psu(180116)

SYS@xffdb1>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
Elapsed: 00:00:00.00
SYS@xffdb1>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@xifenfei01 ~]$ opatch lspatches
26609929;OCW Patch Set Update : 11.2.0.4.170814 (26609929)
26925576;Database Patch Set Update : 11.2.0.4.180116 (26925576)
OPatch succeeded.

监控em发现大量other等待

查询发现大量reliable message等待

SYS@xffdb1>@event
         INST_ID USERNAME                       EVENT                                                      COUNT(*)
---------------- ------------------------------ -------------------------------------------------- ----------------
               1 SOE                            reliable message                                                 16
               1 SOE                            gc current request                                                4
               1 SOE                            gc cr request                                                     5
               1 SOE                            db file sequential read                                          17
               1 SYS                            db file scattered read                                            1
               2 SOE                            library cache: mutex X                                            2
               2 SOE                            latch: ges resource hash list                                     2
               2 SOE                            gc current request                                                1
               2 SOE                            gc cr request                                                     4
               2 SOE                            db file sequential read                                          12
               2                                db file parallel write                                            1
               2 SOE                            SQL*Net message to client                                         1
12 rows selected.

进一步查询发现大量Result Cache: Channel

SYS@xffdb1>SELECT CHANNEL,
  2    SUM(wait_count) sum_wait_count
  3  FROM GV$CHANNEL_WAITS
  4  GROUP BY CHANNEL
  5  ORDER BY SUM(wait_count) DESC;
CHANNEL                                                            SUM_WAIT_COUNT
---------------------------------------------------------------- ----------------
Result Cache: Channel                                                   379367698
RBR channel                                                                128968
kxfp control signal channel                                                115091
MMON remote action broadcast channel                                         4646
obj broadcast channel                                                         395
LCK0 ksbxic channel                                                            17
parameters to cluster db instances - broadcast channel                          4
service operations - broadcast channel                                          3
kill job broadcast - broadcast channel                                          2

查看result_cache_max_size参数

SYS@xffdb1>show parameter result_cache_max_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size                big integer 335552K

调整result_cache_max_size参数

SYS@xffdb1>alter system set result_cache_max_size=0;
System altered.
SYS@xffdb1>show parameter result_cache_max_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size                big integer 0

重启数据库，再次测试，发现相关等待消失，系统也更加稳定

SYS@xffdb1>@event
         INST_ID USERNAME                       EVENT                                                      COUNT(*)
---------------- ------------------------------ -------------------------------------------------- ----------------
               1 SOE                            library cache: mutex X                                            1
               1 SOE                            gc current request                                                3
               1 DBSNMP                         gc cr request                                                     1
               1 SOE                            gc cr request                                                     7
               1 SOE                            db file sequential read                                          18
               1                                db file parallel write                                            1
               2 SOE                            library cache: mutex X                                            2
               2 SOE                            latch free                                                        1
               2 SOE                            gc current request                                                4
               2 SOE                            gc cr request                                                     4
               2 SOE                            db file sequential read                                          12
               2                                db file parallel write                                            3
12 rows selected.

参考：Very High Waits for ‘reliable message’ After Upgrade to 11.2.0.4 When Using Result Cache (Doc ID 1951729.1)

Assistant: Download Reference for Oracle Database/GI PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases—201801

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Assistant: Download Reference for Oracle Database/GI PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases—201801

Patchsets
12.1.0.2 (12.1.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER)	21419221
11.2.0.4 (11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER)	13390677
11.2.0.3 (11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER)	10404530
11.2.0.2 (11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER)	10098816
11.1.0.7 (11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER)	6890831
10.2.0.5 (10.2.0.5 PATCH SET FOR ORACLE DATABASE SERVER)	8202632
^A10.2.0.4 (10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER)	6810189
^B10.2.0.3 (10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER)	5337014
10.2.0.2 (10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER)	4547817
10.1.0.5 (10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER)	4505133
10.1.0.4 (10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER)	4163362
10.1.0.3 (10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER)	3761843
9.2.0.8 (9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER)	4547809
9.2.0.7 (9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER)	4163445
9.2.0.6 (9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER)	3948480
9.2.0.5 (ORACLE 9I DATABASE SERVER RELEASE 2 – PATCH SET 4 VERSION 9.2.0.5.0)	3501955
9.2.0.4 (9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER)	3095277
9.2.0.3 (9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER)	2761332
9.2.0.2 (9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER)	2632931
9.0.1.5 (9.0.1.5 PATCHSET)	3301544
9.0.1.4 (9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER)	2517300
9.0.1.3 (9.0.1.3. PATCH SET FOR ORACLE DATA SERVER)	2271678
8.1.7.4 (8.1.7.4 PATCH SET FOR ORACLE DATA SERVER)	2376472
8.1.7.3 (8.1.7.3 PATCH SET FOR ORACLE DATA SERVER)	2189751
8.1.7.2 (8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER)	1909158

12.2.0.1
Description	Database RU	GI RU	Windows Bundle Patch
JAN2018 (12.2.0.1.180116)	27105253	27100009	27162931
NOV2017 (12.2.0.1.171121)		27010638
OCT2017 (12.2.0.1.171017)	26710464	26737266	26758841
AUG2017 (12.2.0.1.170814)	26609817	26610291	26204214
JUL2017 (12.2.0.1.170718)	26123830	26133434	26204212

12.1.0.2
Description	PSU	GI PSU	Proactive Bundle Patch	Bundle Patch(Windows 32bit & 64bit)
JAN2017 (12.1.0.2.180116)	26925311	27010872	27010930	27162953
OCT2017 (12.1.0.2.171017)	26713565	26635815	26635880	26720785
AUG2017(12.1.0.2.170814)	26609783	26610308	26610322	26161726
JUL2017 (12.1.0.2.170718)	25755742	25901062	26022196	26161724
APR2017 (12.1.0.2.170418)	25171037	25434003	25433352	25632533
JAN2017 (12.1.0.2.170117)	24732082	24917825	24968615	25115951
OCT2016 (12.1.0.2.161018)	24006101	24412235	24448103	24591642
JUL2016 (12.1.0.2.160719)	23054246	23273629	23273686	23530387
APR2016 (12.1.0.2.160419)	22291127	22646084	22899531	22809813
JAN2016 (12.1.0.2.160119)	21948354	22191349	22243551	22310559
OCT2015	21359755(12.1.0.2.5)	21523234(12.1.0.2.5)	21744410(12.1.0.2.13)	21821214(12.1.0.2.10)
JUL2015	20831110(12.1.0.2.4)	20996835(12.1.0.2.4)	21188742(12.1.0.2.10)	21126814(12.1.0.2.7)
APR2015	20299023(12.1.0.2.3)	20485724(12.1.0.2.3)	20698050(12.1.0.2.7)	20684004(12.1.0.2.4)
JAN2015	19769480(12.1.0.2.2)	19954978(12.1.0.2.2)	20141343(12.1.0.2.4)	19720843(12.1.0.2.1)
OCT2014	19303936(12.1.0.2.1)	19392646(12.1.0.2.1)	19404326(12.1.0.2.1)	N/A

12.1.0.1
Description	PSU	GI PSU		Bundle Patch
Description	PSU	A	B	Windows 64 bit	Windows 32 bit
JUL2016 (12.1.0.1.160719)	23054354	23273935	23273958	23530410
APR2016 (12.1.0.1.160419)	22291141	22654153	22654166	22839614
JAN2016 (12.1.0.1.160119)	21951844	22191492	22191511	22494866
OCT2015	21352619(12.1.0.1.9)	21551666(12.1.0.1.9)	21551685(12.1.0.1.9)	21744907 (12.1.0.1.21)
JUL2015	20831107(12.1.0.1.8)	20996901(12.1.0.1.8)	20996911(12.1.0.1.8)	21076681 (12.1.0.1.20)
APR2015	20299016(12.1.0.1.7)	20485762(12.1.0.1.7)	19971331(12.1.0.1.7)	20558101 (12.1.0.1.18)
JAN2015	19769486(12.1.0.1.6)	19971324(12.1.0.1.6)	19971331(12.1.0.1.6)	20160748 (12.1.0.1.16)
OCT2014	19121550(12.1.0.1.5)	19392372(12.1.0.1.5)	19392451(12.1.0.1.5)	19542943 (12.1.0.1.14)
JUL2014	18522516(12.1.0.1.4)	18705901(12.1.0.1.4)	18705972(12.1.0.1.4)	19062327 (12.1.0.1.11)
APR2014	18031528(12.1.0.1.3)	18139660(12.1.0.1.3)	18413105(12.1.0.1.3)	18448604 (12.1.0.1.7)
JAN2014	17552800(12.1.0.1.2)	17735306 (12.1.0.1.2)		17977915 (12.1.0.1.3)
OCT2013	17027533(12.1.0.1.1)	17272829 (12.1.0.1.1)		17363796(12.1.0.1.1)	17363795(12.1.0.1.1)

11.2.0.4
Description	PSU	SPU(CPU)	GI PSU	Bundle Patch (Windows 32bit & 64bit)
JAN2018 (11.2.0.4.180116)	26925576	N/A	27107360	27162965
OCT2017 (11.2.0.4.171017)	26392168	26474853	26635745	26581376
AUG2017 (11.2.0.4.170814)	26609445	N/A	26610246	26194138
JUL2017 (11.2.0.4.170718)	25869727	25879656	26030799	26194136
APR2017 (11.2.0.4.170418)	24732075	25369547	25476126	25632525
JAN2017	N/A	N/A	N/A	N/A
OCT2016 (11.2.0.4.161018)	24006111	24433711	24436338	24591646
JUL2016 (11.2.0.4.160719)	23054359	23177648	23274134	23530402
APR2016 (11.2.0.4.160419)	22502456	22502493	22646198	22839608
JAN2016 (11.2.0.4.160119)	21948347	21972320	22191577	22310544
OCT2015	21352635 (11.2.0.4.8)	21352646	21523375 (11.2.0.4.8)	21821802 (11.2.0.4.20)
JUL2015	20760982 (11.2.0.4.7)	20803583	20996923 (11.2.0.4.7)	21469106 (11.2.0.4.18)
APR2015	20299013 (11.2.0.4.6)	20299015	20485808 (11.2.0.4.6)	20544696 (11.2.0.4.15)
JAN2015	19769489 (11.2.0.4.5)	19854503	19955028 (11.2.0.4.5)	20127071 (11.2.0.4.12)
OCT2014	19121551 (11.2.0.4.4)	19271443	19380115 (11.2.0.4.4)	19651773 (11.2.0.4.10)
JUL2014	18522509 (11.2.0.4.3)	18681862	18706472 (11.2.0.4.3)	18842982 (11.2.0.4.7)
APR2014	18031668 (11.2.0.4.2)	18139690	18139609 (11.2.0.4.2)	18296644 (11.2.0.4.4)
JAN2014	17478514 (11.2.0.4.1)	17551709	N/A	17987366 (11.2.0.4.1)

11.2.0.3
Description	PSU	SPU(CPU)	GI PSU	Bundle Patch (Windows 64bit)	Bundle Patch (Windows 32bit)
JUL2015	20760997 (11.2.0.3.15)	20803576	20996944 (11.2.0.3.15)	21104036	21104035
APR2015	20299017 (11.2.0.3.14)	20299010	20485830 (11.2.0.3.14)	20420395	20420394
JAN2015	19769496 (11.2.0.3.13)	19854461	19971343 (11.2.0.3.13)	20233168	20233167
OCT2014	19121548 (11.2.0.3.12)	19271438	19440385 (11.2.0.3.12)	19618575	19618574
JUL2014	18522512 (11.2.0.3.11)	18681866	18706488 (11.2.0.3.11)	18940194	18940193
APR2014	18031683 (11.2.0.3.10)	18139695	18139678 (11.2.0.3.10)	18372244	18372243
JAN2014	17540582 (11.2.0.3.9)	17478415	17735354 (11.2.0.3.9)	18075406	17906981
OCT2013	16902043 (11.2.0.3.8)	17082364	17272731 (11.2.0.3.8)	17363850	17363844
JUL2013	16619892 (11.2.0.3.7)	16742095	16742216 (11.2.0.3.7)	16803775	16803774
APR2013	16056266 (11.2.0.3.6)	16294378	16083653 (11.2.0.3.6)	16345834	16345833
JAN2013	14727310 (11.2.0.3.5)	14841409	14727347 (11.2.0.3.5)	16042648	16042647
OCT2012	14275605 (11.2.0.3.4)	14390252	14275572 (11.2.0.3.4)	14613223	14613222
JUL2012	13923374 (11.2.0.3.3)	14038787	13919095 (11.2.0.3.3)	14223718	14223717
APR2012	13696216 (11.2.0.3.2)	13632717	13696251 (11.2.0.3.2)	13885389	13885388
JAN2012	13343438 (11.2.0.3.1)	13466801	13348650 (11.2.0.3.1)	13413168	13413167

11.2.0.2
Description	PSU	SPU(CPU)	GI PSU	Bundle Patch (Windows 64bit)	Bundle Patch (Windows 32bit)
OCT2013	17082367 (11.2.0.2.12)	17082375	17272753 (11.2.0.2.12)	17363838	17363837
JUL2013	16619893 (11.2.0.2.11)	16742100	16742320 (11.2.0.2.11)	16345852	16345851
APR2013	16056267 (11.2.0.2.10)	16294412	16166868 (11.2.0.2.10)	16345846	16345845
JAN2013	14727315 (11.2.0.2.9)	14841437	14841385 (11.2.0.2.9)	16100399	16100398
OCT2012	14275621 (11.2.0.2.8)	14390377	14390437 (11.2.0.2.8)	14672268	14672267
JUL2012	13923804 (11.2.0.2.7)	14038791	14192201 (11.2.0.2.7)	14134043	14134042
APR2012	13696224 (11.2.0.2.6)	13632725	13696242 (11.2.0.2.6)	13697074	13697073
JAN2012	13343424 (11.2.0.2.5)	13343244	13653086 (11.2.0.2.5)	13413155	13413154
OCT2011	12827726 (11.2.0.2.4)	12828071	12827731 (11.2.0.2.4)	13038788	13038787
JUL2011	12419331 (11.2.0.2.3)	12419321	12419353 (11.2.0.2.3)	12714463	12714462
APR2011	11724916 (11.2.0.2.2)	11724984	12311357 (11.2.0.2.2)	11896292	11896290
JAN2011	10248523 (11.2.0.2.1)	N/A	N/A	10432053	10432052

11.2.0.1
Description	PSU	CPU	Bundle Patch (Windows 64bit)	Bundle Patch (Windows 32bit)
JUL2011	12419378 (11.2.0.1.6)	12419278	12429529	12429528
APR2011	11724930 (11.2.0.1.5)	11724991	11731176	11883240
JAN2011	10248516 (11.2.0.1.4)	10249532	10432045	10432044
OCT2010	9952216 (11.2.0.1.3)	9952260	10100101	10100100
JUL2010	9654983 (11.2.0.1.2)	9655013	9736865	9736864
APR2010	9352237 (11.2.0.1.1)	9369797	N/A	N/A

10.2.0.5
Description	PSU	SPU(CPU)	Bundle Patch (Windows 64bit)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows Itanium)
JUL2015	20299014(10.2.0.5.19)	20299021	20420387	20420386	N/A
APR2015	N/A	N/A	N/A	N/A	N/A
JAN2015	19769505(10.2.0.5.18)	19854436	20126868	20126867	N/A
OCT2014	19274523(10.2.0.5.17)	19274521	19618565	19618563	N/A
JUL2014	18522511(10.2.0.5.16)	18681879	18940198	18940196	N/A
APR2014	18031728(10.2.0.5.15)	18139709	18372261	18372259	N/A
JAN2014	17465584(10.2.0.5.14)	17551414	17906974	17906972	N/A
OCT2013	17082365(10.2.0.5.13)	17082371	N/A	17363822	N/A
JUL2013	16619894(10.2.0.5.12)	16742123	16803782	16803780	16803781
APR2013	16056270(10.2.0.5.11)	16270946	16345857	16345855	16345856
JAN2013	14727319(10.2.0.5.10)	14841459	15848062	15848060	15848061
OCT2012	14275629(10.2.0.5.9)	14390396	14553358	14553356	14553357
JUL2012	13923855(10.2.0.5.8)	14038805	14134053	14134051	14134052
APR2012	13632743(10.2.0.5.7)	13632738	13654815	13654814	13870404
JAN2012	13343471(10.2.0.5.6)	13343467	13460968	13460967	N/A
OCT2011	12827745(10.2.0.5.5)	12828105	N/A	12914911	N/A
JUL2011	12419392(10.2.0.5.4)	12419258	12429524	12429523	N/A
APR2011	11724962(10.2.0.5.3)	11725006	12328269	12328268	N/A
JAN2011	10248542(10.2.0.5.2)	10249537	10352673	10352672	N/A
OCT2010	9952230(10.2.0.5.1)	9952270	10099855	10058290	N/A

10.2.0.4
Description	PSU	SPU(CPU)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows 64bit)	Bundle Patch (Windows Itanium)
JUL2013	16619897 (10.2.0.4.17)	16742253	N/A	N/A	N/A
APR2013	16056269 (10.2.0.4.16)	16270931	N/A	N/A	N/A
JAN2013	14736542 (10.2.0.4.15)	14841471	N/A	N/A	N/A
OCT2012	14275630 (10.2.0.4.14)	14390410	N/A	N/A	N/A
JUL2012	13923851 (10.2.0.4.13)	14038814	N/A	N/A	N/A
APR2012	12879933 (10.2.0.4.12)	12879926	13928775	13928776	N/A
JAN2012	12879929 (10.2.0.4.11)	12879912	13654060	N/A	N/A
OCT2011	12827778 (10.2.0.4.10)	12828112	12914908	12914910	12914909
JUL2011	12419397 (10.2.0.4.9)	12419249	12429519	12429521	12429520
APR2011	11724977 (10.2.0.4.8)	11725015	12328501	12328503	12328502
JAN2011	10248636 (10.2.0.4.7)	10249540	10349197	10349200	10349198
OCT2010	9952234 (10.2.0.4.6)	9952272	10084980	10084982	10084981
JUL2010	9654991 (10.2.0.4.5)	9655017	9777076	9777078	9777077
APR2010	9352164 (10.2.0.4.4)	9352191	9393548	9393550	9393549
JAN2010	9119284 (10.2.0.4.3)	9119226	9169457	9169460	9169458
OCT2009	8833280 (10.2.0.4.2)	8836308	8880857	8880861	8880858
JUL2009	8576156 (10.2.0.4.1)	8534387	8559466	8559467	8541782
APR2009	N/A	8290506	8307237	8307238	8333678
JAN2009	N/A	7592346	7584866	7584867	N/A
OCT2008	N/A	7375644	7386320	7386321	N/A
JUL2008	N/A	7150470	7218676	7218677	N/A

10.2.0.3
Description	CPU (Unix/Linux)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows Itanium)	Bundle Patch (Windows 64bit)
JAN2009	7592354	7631956	7631958	7631957
OCT2008	7369190	7353782	7353784	7353785
JUL2008	7150622	7252496	7252497	7252498
APR2008	6864068	6867054	6867055	6867056
JAN2008	6646853	6637237	6637238	6637239
OCT2007	6394981	6430171	6430173	6430174
JUL2007	6079591	6116131	6038242	6116139
APR2007	5901891	5948242	5916262	5948243
JAN2007	5881721	5846376	5846377	5846378

10.2.0.2
Description	CPU (Unix/Linux)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows 64bit)	Bundle Patch (Windows Itanium)
JAN2009	7592355	N/A	N/A	N/A
OCT2008	7375660	N/A	N/A	N/A
JUL2008	7154083	N/A	N/A	N/A
APR2008	6864071	N/A	N/A	N/A
JAN2008	6646850	N/A	N/A	N/A
OCT2007	6394997	6397028	6397030	6397029
JUL2007	6079588	6013105	6013121	6013118
APR2007	5901881	5912173	5912179	5912176
JAN2007	5689957	5716143	5699839	5699824
OCT2006	5490848	5502226	5500921	5500894
JUL2006	5225799	5251025	5251028	5251026
APR2006	5079037	5140461	5140567	5140508

10.2.0.1
Description	CPU (Unix/Linux)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows 64bit)	Bundle Patch (Windows Itanium)
APR2007	5901880	N/A	N/A	N/A
JAN2007	5689937	5695784	5695786	5695785
OCT2006	5490846	5500927	5500954	5500951
JUL2006	5225798	5239698	5239701	5239699
APR2006	5049080	5059238	5059261	5059251
JAN2006	4751931	4751539	4770480	4751549

10.1.0.5
Description	CPU (Unix/Linux)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows Itanium)
JAN2012	13343482	13413002	13413003
OCT2011	12828135	12914905	12914906
JUL2011	12419228	12429517	12429518
APR2011	11725035	11731119	11731120
JAN2011	N/A	N/A	N/A
OCT2010	9952279	10089559	10089560
JUL2010	9655023	9683651	9683652
APR2010	9352208	9390288	9390289
JAN2010	9119261	9187104	9187105
OCT2009	8836540	8785211	8785212
JUL2009	8534394	8656224	8656226
APR2009	8290534	8300356	8300360
JAN2009	7592360	7486619	7586049
OCT2008	7375686	7367493	7367494
JUL2008	7154097	7047034	7047037
APR2008	6864078	6867107	6867108
JAN2008	6647005	6637274	6637275
OCT2007	6395024	6408393	6408394
JUL2007	6079585	6115804	6115818
APR2007	5901877	5907304	5907305
JAN2007	5689908	5716295	5634747
OCT2006	5490845	5500883	5500885
JUL2006	5225797	5251148	5251140
APR2006	5049074	5057606	5057609
JAN2006	4751932	4882231	4882236

10.1.0.4
Description	CPU (Unix/Linux)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows Itanium)
APR2007	5901876	5909871	5909879
JAN2007	5689894	5695771	5695772
OCT2006	5490844	5500878	5500880
JUL2006	5225796	5239736	5239737
APR2006	5049067	5059200	5059227
JAN2006	4751928	4751259	4745040
OCT2005	4567866	4579182	4579188
JUL2005	4392423	4440706	4404600
APR2005	4210374	4287619	4287611

10.1.0.3
Description	CPU (Unix/Linux)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows Itanium)
JAN2007	5923277	N/A	N/A
OCT2006	5566825	N/A	N/A
JUL2006	5435164	N/A	N/A
APR2006	5158022	N/A	N/A
JAN2006	4751926	4741077	4741084
OCT2005	4567863	4567518	4567523
JUL2005	4392409	4389012	4389014
APR2005	4193286	4269715	4158888
JAN2005	4003062	4074232	3990812

10.1.0.2
Description	CPU (Unix/Linux)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows Itanium)
APR2005	4193293	4181849	4213305
JUL2005	4400766	4388944	4388948
JAN2005	4003051	4104364	4083038

9.2.0.8
Description	CPU (Unix/Linux)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows Itanium)
JUL2010	9655027	9683644	9683645
APR2010	9352224	9390286	N/A
JAN2010	9119275	9187106	N/A
OCT2009	8836758	8785185	8785186
JUL2009	8534403	8427417	8427418
APR2009	8290549	8300340	8300346
JAN2009	7592365	7703210	7703212
OCT2008	7375695	7394394	7394402
JUL2008	7154111	7047026	7047029
APR2008	6864082	6867138	6867139
JAN2008	6646842	6637265	6637266
OCT2007	6395038	6417013	6417014
JUL2007	6079582	6130293	6130295
APR2007	5901875	5916268	5916275
JAN2007	N/A	N/A	N/A
OCT2006	5490859	5652380	5639519

9.2.0.7
Description	CPU (Unix/Linux)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows Itanium)
JUL2007	6079579	6146759	6146748
APR2007	5901872	5907274	5907275
JAN2007	5689875	5654905	5654909
OCT2006	5490841	5500873	5500874
JUL2006	5225794	5250980	5250981
APR2006	5049060	5064365	5064364
JAN2006	4751923	4751528	4741074
OCT2005	4567854	4579590	4579599
JUL2005	4547566	N/A	N/A

9.2.0.6
Description	CPU (Unix/Linux)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows Itanium)
OCT2006	5490840	5500865	5500871
JUL2006	5225793	5239794	5239793
APR2006	5049051	5059614	5059615
JAN2006	4751921	4751261	4751262
OCT2005	4567846	4579093	4579097
JUL2005	4392392	4445852	4401917
APR2005	4193295	4269928	4213298

9.2.0.5
Description	CPU (Unix/Linux)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows Itanium)
OCT2006	5689708	N/A	N/A
JUL2006	5435138	N/A	N/A
APR2006	5219762	N/A	N/A
OCT2005	4560421	N/A	N/A
JUL2005	4392256	4387563	4391819
APR2005	4193299	4195791	4214192
JAN2005	4003006	4104374	3990809

9.2.0.4
Description	CPU (Unix/Linux)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows Itanium)
JAN2005	4002994	4104369	4083202

8.1.7.4
Description	CPU (Unix/Linux)	Bundle Patch (Windows 32bit)
JAN2007	5689799	5686514
OCT2006	5490835	5496067
JUL2006	5225788	5236412
APR2006	5045247	5057601
JAN2006	4751906	4751570
OCT2005	4560405	4554818
JUL2005	4392446	4437058
APR2005	4193312	4180163
JAN2005	4002909	3921893

参考:Assistant: Download Reference for Oracle Database/GI PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (文档 ID 2118136.2)

dbconsole和sysman用户

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：dbconsole和sysman用户

dbconsole一直是我不怎么推荐的oracle工具,但是总有一些客户使用,这里对于dbconsole中sysman用户状态,密码修改做了一些基本的测试
sysman用户被锁

SQL>  alter user sysman account  lock;
User altered.

重启dbconsole

[oracle@localhost ~]$ emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.
[oracle@localhost ~]$ emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ..... started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log

部分错误日志提示（这里可以明显dbconsole无法正常使用是由于sysman被锁）

解锁sysman用户

SQL> alter user sysman account unlock;
User altered.

重启dbconsole

[oracle@localhost ~]$ emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.
[oracle@localhost ~]$ emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ..... started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log

sysman解锁之后重启dbconsole恢复正常
修改sysman密码

SQL> alter user sysman identified by xifenfei;
User altered.
SQL> select account_status from dba_users where username='SYSMAN';
ACCOUNT_STATUS
--------------------------------
OPEN

重启dbconsole

[oracle@localhost ~]$  emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.
[oracle@localhost ~]$  emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ..... started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log

SQL>  select account_status from dba_users where username='SYSMAN';
ACCOUNT_STATUS
--------------------------------
LOCKED(TIMED)

由于dbconsole的密码存储在配置文件中，修改sysman密码，而配置文件的没有修改，因此使用错误是syman密码登录，超过了profile里面设置的10次，导致用户被锁.

解决sysman密码不对导致异常问题

[oracle@localhost ~]$ emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.
[oracle@localhost config]$ pwd
/u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/config
[oracle@localhost config]$ ls -l emoms.properties
-rw-r--r--. 1 oracle oinstall 1669 Nov 25 15:20 emoms.properties
[oracle@localhost config]$ grep emdRepPwd emoms.properties|grep -v emdRepPwdSeed
oracle.sysman.eml.mntr.emdRepPwd=e3299b5db6162f5d
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
[oracle@localhost config]$ grep emdRepPwd emoms.properties|grep -v emdRepPwdSeed
oracle.sysman.eml.mntr.emdRepPwd=xifenfei
oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

SQL>  alter user sysman account unlock;
User altered.

[oracle@localhost ~]$ emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ..... started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log

通过人工修改$ORACLE_HOME/localhost_sid/sysman/config/emoms.properties文件中的oracle.sysman.eml.mntr.emdRepPwd=密码，oracle.sysman.eml.mntr.emdRepPwdEncrypted=false实现修改密码

[oracle@localhost config]$ grep emdRepPwd emoms.properties|grep -v emdRepPwdSeed
oracle.sysman.eml.mntr.emdRepPwd=aa537afcbab41e05
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE

dbconsole重启之后，密码又重新被修改为加密方式,在11.2之前修改sysman密码，注意要同步修改emoms.properties中相关密码信息

sysman修改密码( 11.2 and higher)

[oracle@localhost config]$ emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.

SQL> alter user sysman identified by xifenfei123;
User altered.

[oracle@localhost ~]$ emctl setpasswd dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Please enter new repository password:
Repository password successfully updated.
[oracle@localhost ~]$ emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ..... started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log

move lob导致index失效

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：move lob导致index失效

在一次数据库升级过程中,因为blug需要move lob，导致表相关index失效,这里通过实验重现
数据库版本

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 user xff identified by oracle;
User created.
SQL> grant dba to xff;
Grant succeeded.

创建测试表和插入数据

SQL> conn xff/oracle
Connected.
SQL> create table t_xff (id int primary key not null,name varchar2(20),c_lob clob);
Table created.
SQL> insert into t_xff values(1,'www.xifenfei.com','http://www.xifenfei.com');
1 row created.
SQL>  insert into t_xff values(2,'www.xifenfei.com','http://www.xifenfei.com');
1 row created.
SQL> commit;
Commit complete.
SQL> select index_name,status from user_indexes;
INDEX_NAME                     STATUS
------------------------------ --------
SYS_IL0000090094C00003$$       VALID
SYS_C0011148                   VALID

move lob

SQL> ALTER TABLE t_xff MOVE LOB (c_lob) store as (tablespace users);
Table altered.
SQL>  select index_name,status from user_indexes;
INDEX_NAME                     STATUS
------------------------------ --------
SYS_IL0000090094C00003$$       VALID
SYS_C0011148                   UNUSABLE
SQL>  insert into t_xff values(3,'www.xifenfei.com','http://www.xifenfei.com');
 insert into t_xff values(3,'www.xifenfei.com','http://www.xifenfei.com')
*
ERROR at line 1:
ORA-01502: index 'XFF.SYS_C0011148' or partition of such index is in unusable
state

这里很明显，当我们move lob之后，表的index变为无效,插入操作无法进行

rebuind index

SQL> alter index xff.SYS_C0011148 rebuild;
Index altered.
SQL> select index_name,status from user_indexes;
INDEX_NAME                     STATUS
------------------------------ --------
SYS_IL0000090094C00003$$       VALID
SYS_C0011148                   VALID
SQL> insert into t_xff values(3,'www.xifenfei.com','http://www.xifenfei.com');
1 row created.
SQL> commit;
Commit complete.

这个测试就是告诫自己,做oracle 不要想当然，move lob之后，表相关的index 都会失效，需要rebuild。具体参见:
Bug 6525073 : STBH: INDEX IS IN UNUSABLE STATE AFTER A LOB COLUMN IS MOVED
ALTER TABLE MOVE LOB makes indexes unusable on the parent table (Doc ID 1228324.1)

分类目录归档：Oracle