admin – 第30页 – Database SOS

ORA-10562 故障恢复—allow 1 corruption

朋友数据库由于存储变动,导致数据库瞬间hang住,然后直接crash,之后无法正常启动,请求技术支持.
数据库报ORA-00600[2131]错误
不能mount,可以通过重建控制文件解决

Mon Nov 30 20:35:38 2015
alter database mount
Mon Nov 30 20:35:38 2015
NOTE: Loaded library: System
Mon Nov 30 20:35:38 2015
SUCCESS: diskgroup DATADG was mounted
Mon Nov 30 20:35:38 2015
NOTE: dependency between database xifenfei and diskgroup resource ora.DATADG.dg is established
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_26450.trc  (incident=3032256):
ORA-00600: internal error code, arguments: [2131], [33], [32], [], [], [], [], [], [], [], [], []
ORA-600 signalled during: alter database mount...

尝试recover数据库

Mon Nov 30 20:45:53 2015
ALTER DATABASE RECOVER  database
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 80 slaves
Mon Nov 30 20:45:56 2015
Recovery of Online Redo Log: Thread 2 Group 11 Seq 617 Reading mem 0
  Mem# 0: +DATADG/xifenfei/redo011.log
Recovery of Online Redo Log: Thread 1 Group 4 Seq 5410 Reading mem 0
  Mem# 0: +DATADG/xifenfei/redo04.log
Recovery of Online Redo Log: Thread 1 Group 5 Seq 5411 Reading mem 0
  Mem# 0: +DATADG/xifenfei/redo05.log
Mon Nov 30 20:46:07 2015
Recovery of Online Redo Log: Thread 1 Group 6 Seq 5412 Reading mem 0
  Mem# 0: +DATADG/xifenfei/redo06.log
Mon Nov 30 20:46:13 2015
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xC] [PC:0x95FB502, kdxlin()+4088] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_pr13_30480.trc  (incident=3032568):
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4088] [SIGSEGV] [ADDR:0xC] [PC:0x95FB502] [Address not mapped to object] []
Mon Nov 30 20:46:17 2015
Sweep [inc][3032568]: completed
Sweep [inc2][3032568]: completed
Mon Nov 30 20:46:31 2015
Slave exiting with ORA-10562 exception
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_pr13_30480.trc:
ORA-10562: Error occurred while applying redo to data block (file# 2, block# 165054)
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 2: '+DATADG/xifenfei/datafile/sysaux.265.861925867'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 271
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4088] [SIGSEGV] [ADDR:0xC] [PC:0x95FB502] [Address not mapped to object] []
Mon Nov 30 20:46:31 2015
Recovery Slave PR13 previously exited with exception 10562
Mon Nov 30 20:46:33 2015
Checker run found 28 new persistent data failures
Mon Nov 30 20:46:35 2015
Media Recovery failed with error 448
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_pr00_30400.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00448: 后台进程正常结束
ORA-10562 signalled during: ALTER DATABASE RECOVER  database  ...

通过这里可以看到,由于在recover 操作之时,由于某种原因redo的数据无法apply到file 2 block 165054中,导致数据库recover database失败.

按照数据文件recover操作

SQL> recover datafile 1;
Media recovery complete.
SQL> recover datafile 3,4,5,6,7;
Media recovery complete.
SQL> recover datafile 8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28;
Media recovery complete.
SQL> recover datafile 2;
ORA-00283: recovery session canceled due to errors
ORA-10562: Error occurred while applying redo to data block (file# 2, block#
165054)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '+DATADG/xifenfei/datafile/sysaux.265.861925867'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 271
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kdxlin()+4088] [SIGSEGV]
[ADDR:0xC] [PC:0x95FB502] [Address not mapped to object] []

错误提示和recover database一样,那我们只能让恢复跳过该block继续恢复,因为根据经验判断data object# 271不是系统核心对象，不会影响数据库的启动

跳过坏块继续恢复

SQL> recover  datafile 2 allow 1 corruption;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [2], [69793], [8458401], [],
[], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 69793, file
offset is 571744256 bytes)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '+DATADG/xifenfei/datafile/sysaux.265.861925867'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 272
SQL> recover  datafile 2 allow 1 corruption;
Media recovery complete.
SQL> alter database open;
Database altered.

出现了ORA-600[3020] 继续跳过坏块,然后数据库顺利open,别忘记加tempfile

处理异常对象

SQL> select object_name,object_type from dba_objects where data_object_id in(272,271);
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
SMON_SCN_TIME_TIM_IDX
INDEX
SMON_SCN_TIME_SCN_IDX
INDEX
SQL> select index_name from dba_indexes where table_name='SMON_SCN_TIME';
INDEX_NAME
------------------------------
SMON_SCN_TIME_TIM_IDX
SMON_SCN_TIME_SCN_IDX
SQL> set pages 1000
SQL> set long 1000
SQL> Select dbms_metadata.get_ddl('TABLE','SMON_SCN_TIME','SYS') FROM DUAL ;
DBMS_METADATA.GET_DDL('TABLE','SMON_SCN_TIME','SYS')
--------------------------------------------------------------------------------
  CREATE TABLE "SYS"."SMON_SCN_TIME"
   (    "THREAD" NUMBER,
        "TIME_MP" NUMBER,
        "TIME_DP" DATE,
        "SCN_WRP" NUMBER,
        "SCN_BAS" NUMBER,
        "NUM_MAPPINGS" NUMBER,
        "TIM_SCN_MAP" RAW(1200),
        "SCN" NUMBER DEFAULT 0,
        "ORIG_THREAD" NUMBER DEFAULT 0           /* for downgrade */
   ) CLUSTER "SYS"."SMON_SCN_TO_TIME_AUX" ("THREAD")
SQL> analyze table smon_scn_time validate structure cascade online;
analyze table smon_scn_time validate structure cascade online
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 2, block # 165054)
ORA-01110: data file 2: '+DATADG/xifenfei/datafile/sysaux.265.861925867'
SQL> truncate CLUSTER "SYS"."SMON_SCN_TO_TIME_AUX";
Cluster truncated.

关于SMON_SCN_TIME部分处理,可以参考:关于SMON_SCN_TIME若干问题说明.至此数据库基本上恢复完成，而且运气非常好，恢复的非常完美，数据实现0丢失.

分享某客户存储异常恢复之后oracle故障恢复—ORA-600 4155

某客户使用win 2003，Oracle 11.2.0.1+ASM架构方式,由于存储异常并且做了存储恢复之后,ASM可以正常mount起来,但是数据库无法打开
使用dbv检查system发现有少量坏块

DBVERIFY - 开始验证: FILE = +DATA/xifenfei/datafile/system.256.764288125
页 3117 标记为损坏
Corrupt block relative dba: 0x00400c2d (file 1, block 3117)
Bad header found during dbv:
Data in bad block:
 type: 11 format: 2 rdba: 0x00400001
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000b01
 check value in block header: 0xfeec
 computed block checksum: 0x0
Corrupt block relative dba: 0x0042002d (file 1, block 131117)
Bad header found during dbv:
Data in bad block:
 type: 11 format: 2 rdba: 0x00400001
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000b01
 check value in block header: 0xfeec
 computed block checksum: 0x0
Corrupt block relative dba: 0x0042003d (file 1, block 131133)
Bad header found during dbv:
Data in bad block:
 type: 11 format: 2 rdba: 0x00400001
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000b01
 check value in block header: 0xfeec
 computed block checksum: 0x0
DBVERIFY - 验证完成
检查的页总数: 222208
处理的页总数 (数据): 188939
失败的页总数 (数据): 19
处理的页总数 (索引): 17375
失败的页总数 (索引): 0
处理的页总数 (其他): 3190
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 12701
标记为损坏的总页数: 3
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 0 (0.0)

很多”页 131125 失败, 校验代码为 6125″类似错误忽略.
我们对于这些坏块进行分析，这些坏块未涉及oracle 最核心的基表数据,从理论上可以open数据库

尝试打开数据库

ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Mon Nov 16 14:12:45 2015
NOTE: dependency between database xifenfei and diskgroup resource ora.DATA.dg is established
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_5672.trc  (incident=937262):
ORA-00353: 日志损坏接近块 12509 更改 14199034494312 时间 02/05/2015 03:09:12
ORA-00312: 联机日志 2 线程 1: '+DATA/xifenfei/onlinelog/group_2.265.764288315'
ORA-00312: 联机日志 2 线程 1: '+DATA/xifenfei/onlinelog/group_2.264.764288315'
Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_937262\xifenfei_ora_5672_i937262.trc
Media Recovery failed with error 399
ORA-355 signalled during: ALTER DATABASE RECOVER  DATABASE  ...

可以确定存储恢复的redo有问题(ORA-00353,ORA-00312),数据库无法直接打开

使用参数_allow_resetlogs_corruption屏蔽redo异常resetlogs库

Mon Nov 16 15:26:41 2015
alter database open resetlogs
Mon Nov 16 15:26:41 2015
Starting background process ASMB
Mon Nov 16 15:26:41 2015
ASMB started with pid=25, OS id=6612
Starting background process RBAL
Mon Nov 16 15:26:41 2015
RBAL started with pid=26, OS id=6940
NOTE: initiating MARK startup
Starting background process MARK
Mon Nov 16 15:26:41 2015
MARK started with pid=27, OS id=1720
NOTE: MARK has subscribed
NOTE: Loaded library: System
SUCCESS: diskgroup DATA was mounted
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
Mon Nov 16 15:26:44 2015
NOTE: dependency between database xifenfei and diskgroup resource ora.DATA.dg is established
Archived Log entry 1 added for thread 1 sequence 1390429 ID 0xf6320db5 dest 1:
Archived Log entry 2 added for thread 1 sequence 1390427 ID 0xf6320db5 dest 1:
ARCH: Log corruption near block 16350 change 14199035261082 time ?
CORRUPTION DETECTED: thread 1 sequence 1390428 log 3 at block 16350. Arch found corrupt blocks
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc  (incident=951271):
ORA-00353: 日志损坏接近块 16350 更改 14199035261082 时间 02/05/2015 03:12:49
ORA-00312: 联机日志 3 线程 1: '+DATA/xifenfei/onlinelog/group_3.267.764288315'
ORA-00312: 联机日志 3 线程 1: '+DATA/xifenfei/onlinelog/group_3.266.764288315'
Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_951271\xifenfei_ora_8132_i951271.trc
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc:
ORA-00354: 损坏重做日志块标头
ORA-00353: 日志损坏接近块 16350 更改 14199035261082 时间 02/05/2015 03:12:49
ORA-00312: 联机日志 3 线程 1: '+DATA/xifenfei/onlinelog/group_3.267.764288315'
ORA-00312: 联机日志 3 线程 1: '+DATA/xifenfei/onlinelog/group_3.266.764288315'
ARCH: All Archive destinations made inactive due to error 354
Committing creation of archivelog '+DATA/xifenfei/archivelog/2015_11_16/thread_1_seq_1390428.276.895937207' (error 354)
Deleted Oracle managed file +DATA/xifenfei/archivelog/2015_11_16/thread_1_seq_1390428.276.895937207
******************************************************
Detected premature EOF of log 3 at block 16350; re-trying archival
******************************************************
Mon Nov 16 15:26:49 2015
Sweep [inc][951271]: completed
Mon Nov 16 15:26:49 2015
Trace dumping is performing id=[cdmp_20151116152649]
Archived Log entry 3 added for thread 1 sequence 1390428 ID 0xf6320db5 dest 1:
RESETLOGS after incomplete recovery UNTIL CHANGE 14199033899179
Resetting resetlogs activation ID 4130475445 (0xf6320db5)
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_m001_800.trc  (incident=951319):
ORA-00353: log corruption near block 2270 change 14199035131016 time 02/05/2015 03:12:58
ORA-00334: archived log: '+DATA/xifenfei/archivelog/2015_11_16/thread_1_seq_1390428.276.895937209'
Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_951319\xifenfei_m001_800_i951319.trc
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_m001_800.trc  (incident=951320):
ORA-00355: change numbers out of order
ORA-00353: log corruption near block 2270 change 14199035131016 time 02/05/2015 03:12:58
ORA-00334: archived log: '+DATA/xifenfei/archivelog/2015_11_16/thread_1_seq_1390428.276.895937209'
Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_951320\xifenfei_m001_800_i951320.trc
Trace dumping is performing id=[cdmp_20151116152651]
Mon Nov 16 15:26:51 2015
Sweep [inc][951320]: completed
Sweep [inc][951319]: completed
Sweep [inc2][951320]: completed
Sweep [inc2][951319]: completed
Sweep [inc2][951271]: completed
Trace dumping is performing id=[cdmp_20151116152653]
Checker run found 1 new persistent data failures
Mon Nov 16 15:26:53 2015
Setting recovery target incarnation to 2
Mon Nov 16 15:26:54 2015
Assigning activation ID 4262085362 (0xfe0a42f2)
LGWR: STARTING ARCH PROCESSES
Mon Nov 16 15:26:54 2015
ARC0 started with pid=29, OS id=2896
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Nov 16 15:26:55 2015
ARC1 started with pid=30, OS id=1748
Mon Nov 16 15:26:55 2015
ARC2 started with pid=31, OS id=1920
ARC1: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: +DATA/xifenfei/onlinelog/group_1.262.764288315
  Current log# 1 seq# 1 mem# 1: +DATA/xifenfei/onlinelog/group_1.263.764288315
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Nov 16 15:26:55 2015
SMON: enabling cache recovery
Mon Nov 16 15:26:55 2015
ARC3 started with pid=32, OS id=7236
ARC2: Archival started
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc  (incident=951272):
ORA-00600: 内部错误代码, 参数: [4155], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_951272\xifenfei_ora_8132_i951272.trc
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc:
ORA-00600: 内部错误代码, 参数: [4155], [], [], [], [], [], [], [], [], [], [], []
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc:
ORA-00600: 内部错误代码, 参数: [4155], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
Trace dumping is performing id=[cdmp_20151116152658]
USER (ospid: 8132): terminating the instance due to error 600
Mon Nov 16 15:27:07 2015
Instance terminated by USER, pid = 8132
ORA-1092 signalled during: alter database open resetlogs...

在resetlogs过程中由于遭遇了ORA-600[4155]导致数据库无法正常打开.

分析相关trace文件

*** 2015-11-16 15:26:56.921
*** SESSION ID:(145.3) 2015-11-16 15:26:56.921
*** CLIENT ID:() 2015-11-16 15:26:56.921
*** SERVICE NAME:(SYS$USERS) 2015-11-16 15:26:56.921
*** MODULE NAME:(sqlplus.exe) 2015-11-16 15:26:56.921
*** ACTION NAME:() 2015-11-16 15:26:56.921
Dump continued from file: d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc
ORA-00600: 内部错误代码, 参数: [4155], [], [], [], [], [], [], [], [], [], [], []
========= Dump for incident 951272 (ORA 600 [4155]) ========
----- Beginning of Customized Incident Dump(s) -----
XID passed in =xid: 0x000b.001.00fcfb45
XID from Undo block = xid: 0x000b.009.00fcc561
----- End of Customized Incident Dump(s) -----
*** 2015-11-16 15:26:57.203
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=7j16t46cacjt9) -----
alter database open resetlogs
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1()+129        CALL???  skdstdst()           009233DA2 000000000 000000000
                                                   000000000
ksedst()+69          CALL???  ksedst1()            000000002 000000000 006F605E0
                                                   000000000
dbkedDefDump()+4536  CALL???  ksedst()             000000287 000000000 000000000
                                                   000000000
ksedmp()+43          CALL???  dbkedDefDump()       000000003 000000002 000000000
                                                   000468E71
ksfdmp()+87          CALL???  ksedmp()             000000000 000000000 000000000
                                                   000000000
dbgexPhaseII()+1819  CALL???  ksfdmp()             000000000 000000000 000000000
                                                   000000000
dbgexExplicitEndInc  CALL???  dbgexPhaseII()       000000000 000000000 000000000
()+755                                             000000000
dbgeEndDDEInvocatio  CALL???  dbgexExplicitEndInc  00CFB0570 00CFB7540 01F9012D8
nImpl()+748                   ()                   01F901300
dbgeEndDDEInvocatio  CALL???  dbgeEndDDEInvocatio  00CFB0570 00CFB7540 01F903130
n()+47                        nImpl()              00000000A
ktugce()+610         CALL???  dbgeEndDDEInvocatio  006E24498 01F8FF91E 00000002E
                              n()                  035636366
ktdgti()+609         CALL???  ktugce()             000000000 000001F68 000001F68
                                                   000000001
k2vGetCollectingInf  CALL???  ktdgti()             000000000 008A34E71 01F902470
o()+324                                            000000018
k2vcbk()+182         CALL???  k2vGetCollectingInf  000000000 000000000 000000000
                              o()                  000000008
kturRecoverTxn()+82  CALL???  k2vcbk()             000000000 FCFB450000000B
67                                                 000610001 7FFDF055860
kturRecoverUndoSegm  CALL???  kturRecoverTxn()     01F903448 009460001 000000000
ent()+1371                                         00147AE14
ktuiup()+1520        CALL???  kturRecoverUndoSegm  7FF0000000B 01F903628
                              ent()                000000000 00147AE14
ktuini()+80          CALL???  ktuiup()             000000001 00000000E 01F9038A0
                                                   003CB3D06
adbdrv()+44263       CALL???  ktuini()             000000000 01F9090C8 000000000
                                                   000000000
opiexe()+20842       CALL???  adbdrv()             000000023 000000003
                                                   7FF00000102 000000000
opiosq0()+5129       CALL???  opiexe()+16981       000000004 000000000 01F90A8E0
                                                   009361AB3
kpooprx()+357        CALL???  opiosq0()            000000003 00000000E 01F90ABB0
                                                   0000000A4
kpoal8()+940         CALL???  kpooprx()            000020C80 01E65CCD0 00CE91AD8
                                                   000000001
opiodr()+1662        CALL???  kpoal8()             00000005E 00000001C 01F90E120
                                                   00A4EF224
ttcpip()+1325        CALL???  opiodr()             480000000000005E
                                                   49004D000000001C 01F90E120
                                                   4100200000000000
opitsk()+2040        CALL???  ttcpip()             01E735200 000000000 000000000
                                                   000000000
opiino()+1258        CALL???  opitsk()             00000001E 000000000 000000000
                                                   01F90FA18
opiodr()+1662        CALL???  opiino()             00000003C 000000004 01F90FAD0
                                                   000000000
opidrv()+864         CALL???  opiodr()             00000003C 000000004 01F90FAD0
                                                   6F5C3A6400000000
sou2o()+98           CALL???  opidrv()+150         00000003C 000000004 01F90FAD0
                                                   000000000
opimai_real()+158    CALL???  sou2o()              01F90FB00 01F90FBC4
                                                   F0010000B07DF 1009C002B0019
opimai()+191         CALL???  opimai_real()        00000001B 01F90FC88 000000036
                                                   000000000
OracleThreadStart()  CALL???  opimai()             01F90FE90 01F60FF38 000000002
+724                                               01F90FC88
0000000078D3B6DA     CALL???  OracleThreadStart()  01F60FF38 000000000 000000000
                                                   01F90FFA8
--------------------- Binary Stack Dump ---------------------
UNDO BLK:
xid: 0x000b.009.00fcc561  seq: 0x513d cnt: 0x2f  irb: 0x2f  icl: 0x0   flg: 0x0000
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f64     0x02 0x1ee4     0x03 0x1e30     0x04 0x1d7c     0x05 0x1cc8
0x06 0x1c14     0x07 0x1b60     0x08 0x1aac     0x09 0x19f8     0x0a 0x1944
0x0b 0x1890     0x0c 0x17dc     0x0d 0x1728     0x0e 0x1674     0x0f 0x15c0
0x10 0x150c     0x11 0x1458     0x12 0x13a4     0x13 0x12f0     0x14 0x123c
0x15 0x1188     0x16 0x10d4     0x17 0x1050     0x18 0x0fd0     0x19 0x0f1c
0x1a 0x0e98     0x1b 0x0de4     0x1c 0x0d30     0x1d 0x0c7c     0x1e 0x0bc8
0x1f 0x0b44     0x20 0x0ac4     0x21 0x0a10     0x22 0x095c     0x23 0x08a8
0x24 0x07f4     0x25 0x0770     0x26 0x06f0     0x27 0x063c     0x28 0x0588
0x29 0x04d4     0x2a 0x0420     0x2b 0x039c     0x2c 0x031c     0x2d 0x0298
0x2e 0x0218     0x2f 0x0164

ORA-600 4155是由于在恢复过程中发现事务的id和undo segment中的事务表中id不匹配从而出现此类问题.针对此问题,可以通过bbed修改事务表记录,或者直接丢弃该事务,从而绕过该错误.

处理掉异常事务id后,继续open库

Mon Nov 16 16:16:07 2015
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
parallel recovery started with 32 processes
Started redo scan
Completed redo scan
read 8 KB redo, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 1, block 2, scn 14199161880578
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: +DATA/xifenfei/onlinelog/group_1.262.764288315
Mem# 1: +DATA/xifenfei/onlinelog/group_1.263.764288315
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 1, block 19, scn 14199161900601
0 data blocks read, 0 data blocks written, 8 redo k-bytes read
Current SCN is not changed: _minimum_giga_scn (scn 14199161880576) is too small
Mon Nov 16 16:16:08 2015
LGWR: STARTING ARCH PROCESSES
Mon Nov 16 16:16:08 2015
ARC0 started with pid=62, OS id=7612
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Nov 16 16:16:09 2015
ARC1 started with pid=63, OS id=5620
Mon Nov 16 16:16:09 2015
ARC2 started with pid=64, OS id=7308
ARC1: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: +DATA/xifenfei/onlinelog/group_2.264.764288315
Current log# 2 seq# 2 mem# 1: +DATA/xifenfei/onlinelog/group_2.265.764288315
Successful open of redo thread 1
Archived Log entry 1 added for thread 1 sequence 1 ID 0xfe09f6df dest 1:
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Nov 16 16:16:10 2015
SMON: enabling cache recovery
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
Mon Nov 16 16:16:09 2015
ARC3 started with pid=65, OS id=7064
been restored. It may be necessary to add files to these
ARC2: Archival started
tablespaces. That can be done using the SQL statement:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
ARC0: Becoming the heartbeat ARCH
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is ZHS16GBK
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
WARNING: AQ_TM_PROCESSES is set to 0. System operation might be adversely affected.
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: ALTER DATABASE OPEN

因为该数据库是经过了存储恢复,除system之外,其他文件也有大量坏块,因为恢复过程相对比较麻烦,除了上面列出来的ORA-00353,ORA-00312,ORA-600 4155等各种错误之外,还有大量的ORA-01578,ORA-01110.由于11G比较常见的ORA-00283,ORA-16433问题。

windows平台listener.log超过4G导致监听异常

今天有个朋友咨询生产库非常慢,应用无法连接数据库,通过分析是由于win 32位数据库11.2.0.1版本文件系统为ntfs,但是listener.log为4G,导致监听工作不正常.
tnsping几乎hang住
tnsping

lsnrctl status 也几乎hang住
lsnrctl-status

直接ping ip正常
证明不是网络问题,导致监听异常,现在判断是监听问题
ping-ip

监听日志超过4G
listener_file

关闭监听日志
log_status_off

监听正常
关闭监听日志之后,监听恢复正常

文件系统为ntfs格式
这里证明，不是由于文件系统格式的限制ntfs最大文件允许64T,这里4G肯定不是系统的极限
ntfs

相关文章
Bug 9879101 : THE CONNECT THROUGH LISTENER WAS SLOW WHEN LISTNER LOG GROWED 4GB
WINDOWS: Listener Hangs & Lsnrctl Commands Are Slow or Hang

Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets—201510

Base Releases
11.2.0.1.0	11.2.0.1.0 Download Page
Older Versions	Raise a non-technical SR for Physical Shipment or Download URL. Refer Doc ID 1071023.1

Patchsets
^l12.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
^d10.2.0.4 (10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER)	6810189
^e10.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

PSU, SPU(CPU), Bundle Patches


*12.1.0.2*
Description	PSU	GI PSU	Bundle Patch (Windows 32bit & 64bit)
OCT2015	21359755 (12.1.0.2.5)	21523234 (12.1.0.2.5)	21821214 (12.1.0.2.10)
JUL2015	20831110 (12.1.0.2.4)	20996835 (12.1.0.2.4)	21126814 (12.1.0.2.7)
APR2015	20299023 (12.1.0.2.3)	20485724 (12.1.0.2.3)	20684004 (12.1.0.2.4)
JAN2015	19769480 (12.1.0.2.2)	19954978 (12.1.0.2.2)	19720843 (12.1.0.2.1)
OCT2014	19303936 (12.1.0.2.1)	19392646 (12.1.0.2.1)	N/A

*12.1.0.1*
Description	PSU	GI PSU	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
OCT2015	21352619 (12.10.2.9)	^j21551666 / ^k21551685 (12.1.0.1.9)	21744907 (12.1.0.1.21)
JUL2015	20831107 (12.1.0.1.8)	^j20996901 / ^k20996911(12.1.0.1.8)	21076681 (12.1.0.1.20)
APR2015	20299016 (12.1.0.1.7)	^j20485762 / ^k19971331(12.1.0.1.7)	20558101 (12.1.0.1.18)
JAN2015	19769486 (12.1.0.1.6)	^j19971324 / ^k19971331 (12.1.0.1.6)	20160748 (12.1.0.1.16)
OCT2014	19121550 (12.1.0.1.5)	^j19392372 / ^k19392451 (12.1.0.1.5)	19542943 (12.1.0.1.14)
JUL2014	18522516 (12.1.0.1.4)	^j18705901 / ^k18705972 (12.1.0.1.4)	19062327 (12.1.0.1.11)
APR2014	18031528 (12.1.0.1.3)	^j18139660 / ^k18413105 (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)
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)
^aJUL2015	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)
^aOCT2013	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)
^aJUL2011	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


*11.1.0.7*
Description	PSU	SPU(CPU)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
^bJUL2015	20761024 (11.1.0.7.24)	20803573	21104030	21104029
^bAPR2015	20299012 (11.1.0.7.23)	20299020	20420391	20420390
^bJAN2015	19769499 (11.1.0.7.22)	19854433	20126915	20126914
^bOCT2014	19152553 (11.1.0.7.21)	19274522	19609034	19609032
^bJUL2014	18522513 (11.1.0.7.20)	18681875	18944208	18944207
^bAPR2014	18031726 (11.1.0.7.19)	18139703	18372258	18372257
^bJAN2014	17465583 (11.1.0.7.18)	17551415	17906936	17906935
^bOCT2013	17082366 (11.1.0.7.17)	17082374	17363760	17363759
^bJUL2013	16619896 (11.1.0.7.16)	16742110	16803788	16803787
^bAPR2013	16056268 (11.1.0.7.15)	16308394	16345862	16345861
^bJAN2013	14739378 (11.1.0.7.14)	14841452	15848067	15848066
^bOCT2012	14275623 (11.1.0.7.13)	14390384	14672313	14672312
JUL2012	13923474 (11.1.0.7.12)	14038803	14109868	14109867
APR2012	13621679 (11.1.0.7.11)	13632731	13715810	13715809
JAN2012	13343461 (11.1.0.7.10)	13343453	13460956	13460955
OCT2011	12827740 (11.1.0.7.9)	12828097	12914916	12914915
JUL2011	12419384 (11.1.0.7.8)	12419265	12695278	12695277
APR2011	11724936 (11.1.0.7.7)	11724999	11741170	11741169
JAN2011	10248531 (11.1.0.7.6)	10249534	10350788	10350787
OCT2010	9952228 (11.1.0.7.5)	9952269	9773825	9773817
JUL2010	9654987 (11.1.0.7.4)	9655014	9869912	9869911
APR2010	9352179 (11.1.0.7.3)	9369783	9392335	9392331
JAN2010	9209238 (11.1.0.7.2)	9114072	9166861	9166858
OCT2009	8833297 (11.1.0.7.1)	8836375	8928977	8928976
JUL2009	N/A	8534338	8553515	8553512
APR2009	N/A	8290478	8343070	8343061


*11.1.0.6*
Description	CPU	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
^aJUL2009	8534378	8563155	8563154
APR2009	8290402	8333657	8333655
JAN2009	7592335	7631981	7631980
OCT2008	7375639	7378393	7378392
JUL2008	7150417	7210197	7210195
APR2008	6864063	6867180	6867178


*10.2.0.5*
Description	PSU	SPU(CPU)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)	Bundle Patch(Windows^Itanium)
^abJUL2013	16619894 (10.2.0.5.12)	16742123	16803782	16803780	16803781
^bAPR2013	16056270 (10.2.0.5.11)	16270946	16345857	16345855	16345856
^bJAN2013	14727319 (10.2.0.5.10)	14841459	15848062	15848060	15848061
^bOCT2012	14275629 (10.2.0.5.9)	14390396	14553358	14553356	14553357
^bJUL2012	13923855 (10.2.0.5.8)	14038805	14134053	14134051	14134052
^bAPR2012	13632743 (10.2.0.5.7)	13632738	13654815	13654814	13870404
JAN2012	13343471 (10.2.0.5.6)	13343467	^b13460968	^b13460967	N/A
^bOCT2011	12827745 (10.2.0.5.5)	12828105	^c12914913	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)
^bgJUL2013	16619897 (10.2.0.4.17)	16742253	N/A	N/A	N/A
^bgAPR2013	16056269 (10.2.0.4.16)	16270931	N/A	N/A	N/A
^bgJAN2013	14736542 (10.2.0.4.15)	14841471	N/A	N/A	N/A
^bgOCT2012	14275630 (10.2.0.4.14)	14390410	N/A	N/A	N/A
^bgJUL2012	13923851 (10.2.0.4.13)	14038814	N/A	N/A	N/A
^abAPR2012	12879933 (10.2.0.4.12)	12879926	13928775	13928776	N/A
JAN2012	12879929 (10.2.0.4.11)	12879912	^b13654060	N/A	N/A
^bOCT2011	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)
^aJAN2009	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
^hOCT2008	7375660	N/A	N/A	N/A
^hJUL2008	7154083	N/A	N/A	N/A
^hAPR2008	6864071	N/A	N/A	N/A
^aJAN2008	6646850	N/A	N/A	N/A
^fOCT2007	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

OJVM PSU Patches


*12.1.0.2*
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + GI PSU
OCT2015	21555660 (12.1.0.2.5)	21788394 (12.1.0.2.4)	21520444	21523260
JUL2015	21068507 (12.1.0.2.4)	21153530 (12.1.0.2.3)	21150768	21150782
APR2015	20415564 (12.1.0.2.3)	20391199 (12.1.0.2.2)	20834354	20834538
JAN2015	19877336 (12.1.0.2.2)	20225938 (12.1.0.2.1)	20132434	20132450
OCT2014 (12.1.0.2.1)	19282028		19791366	19791375


*12.1.0.1*
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + GI PSU	Generic JDBC
OCT2015 (12.1.0.1.5)	21555669	21788365	21744318	21744328	Included in OJVM PSU
JUL2015 (12.1.0.1.4)	21068523	21153513	21150806	21150817
APR2015 (12.1.0.1.3)	20406245	20225909	20834568	20834579
JAN2015 (12.1.0.1.2)	19877342	20225916	20132482	20132489
OCT2014 (12.1.0.1.1)	19282024	19801531	19791363	19791360	19852357


*11.2.0.4*
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + DB SPU	Combo OJVM + GI PSU	Generic JDBC
OCT2015 (11.2.0.4.5)	21555791	21788344	21744343	21744335	21744348	Included in OJVM PSU
JUL2015 (11.2.0.4.4)	21068539	21153498	21150851	21150829	21150864
APR2015 (11.2.0.4.3)	20406239	20225988	20834611	20834597	20834621
JAN2015 (11.2.0.4.2)	19877440	20225982	20132580	20132517	20132615
OCT2014 (11.2.0.4.1)	19282021	19799291	19791364	19791358	19791420	19852360


*11.2.0.3*
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + DB SPU	Combo OJVM + GI PSU	Generic JDBC
JUL2015 (11.2.0.3.4)	21068553	21153470	21150891	21150885	21150904	Included in OJVM PSU
APR2015 (11.2.0.3.3)	20406220	20391185	20834670	20834653	20834686
JAN2015 (11.2.0.3.2)	19877443	20227195	20132646	20132635	20132651
OCT2014 (11.2.0.3.1)	19282015	19806120	19791427	19791426	19791428	19852361


*11.1.0.7*
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + DB SPU	Combo OJVM + GI PSU	Generic JDBC
JUL2015 (11.1.0.7.4)	21068565	21153423	21150939	21150929	N/A	Included in OJVM PSU
APR2015 (11.1.0.7.3)	20406213	20391156	20834724	20834712	N/A
JAN2015 (11.1.0.7.2)	19877446	20227146	20132677	20132669	N/A
OCT2014 (11.1.0.7.1)	19282002	19806118	19791436	19791434	N/A	19852363

参考：Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (文档 ID 1454618.1)

某医院存储掉线导致Oracle数据库故障恢复

xx医院存储突然掉线,导致数据库异常,现场工程师折腾了一天,问题依旧没有解决,无奈之下找到我们,希望我们能够帮忙恢复数据库.
启动报ORA-00600[2131]错误

Fri Nov 06 14:50:59 2015
ALTER DATABASE   MOUNT
This instance was first to mount
Fri Nov 06 14:50:59 2015
ALTER SYSTEM SET local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.4.4)(PORT=1521))' SCOPE=MEMORY SID='xifenfei1';
NOTE: Loaded library: System
SUCCESS: diskgroup DATA was mounted
NOTE: dependency between database xifenfei and diskgroup resource ora.DATA.dg is established
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13221.trc  (incident=191085):
ORA-00600: internal error code, arguments: [2131], [33], [32], [], [], [], [], [], [], [], [], []
Incident details in: /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_191085/xifenfei1_ora_13221_i191085.trc
Fri Nov 06 14:51:10 2015
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORA-600 signalled during: ALTER DATABASE   MOUNT...

出现该错误的原因是由于：We are attempting to write a controlfile checkpoint progress record, but find we do not have the progress record generating this exception.由于控制文件异常导致,出现此类情况,我们一般使用单个控制文件一次尝试,如果都不可以考虑重建控制文件

由于坏块(逻辑/物理)导致数据库实例恢复无法进行

Beginning crash recovery of 2 threads
Started redo scan
kcrfr_rnenq: use log nab 393216
kcrfr_rnenq: use log nab 2
Completed redo scan
 read 4427 KB redo, 500 data blocks need recovery
Started redo application at
 Thread 1: logseq 5731, block 391398
 Thread 2: logseq 4252, block 520815
Recovery of Online Redo Log: Thread 1 Group 2 Seq 5731 Reading mem 0
  Mem# 0: +DATA/xifenfei/onlinelog/group_2.266.835331047
Recovery of Online Redo Log: Thread 2 Group 8 Seq 4252 Reading mem 0
  Mem# 0: +DATA/xifenfei/onlinelog/group_8.331.835330421
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc  (incident=197486):
ORA-00600: internal error code, arguments: [kdxlin:psno out of range], [], [], [], [], [], [], [], [], [], [], []
Incident details in:/home/app/oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_197486/xifenfei1_ora_13770_i197486.trc
Fri Nov 06 15:03:09 2015
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc  (incident=197487):
ORA-01578: ORACLE data block corrupted (file # 2, block # 65207)
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 81045
ORA-00600: internal error code, arguments: [kdxlin:psno out of range], [], [], [], [], [], [], [], [], [], [], []
Incident details in:/home/app/oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_197487/xifenfei1_ora_13770_i197487.trc
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc:
ORA-01578: ORACLE data block corrupted (file # 2, block # 65207)
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 81045
ORA-00600: internal error code, arguments: [kdxlin:psno out of range], [], [], [], [], [], [], [], [], [], [], []
Recovery of Online Redo Log: Thread 2 Group 3 Seq 4253 Reading mem 0
  Mem# 0: +DATA/xifenfei/onlinelog/group_3.332.835330505
Hex dump of (file 14, block 62536) in trace file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc
Reading datafile '+DATA/xifenfei/datafile/ht01.dbf' for corruption at rdba: 0x0380f448 (file 14, block 62536)
Reread (file 14, block 62536) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 62536 OF FILE 14
Fri Nov 06 15:03:13 2015
Abort recovery for domain 0
Aborting crash recovery due to error 1172
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc:
ORA-01172: recovery of thread 1 stuck at block 62536 of file 14
ORA-01151: use media recovery to recover block, restore backup if needed
Abort recovery for domain 0
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc:
ORA-01172: recovery of thread 1 stuck at block 62536 of file 14
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: ALTER DATABASE OPEN...

查看资料发现和Bug 14301592 – Several errors by corrupt blocks shifted by 2 bytes in buffer cache during recovery caused by INDEX redo apply，可以通过ALLOW 1 CORRUPTION临时解决

使用ALLOW 1 CORRUPTION进行恢复,出现ORA-07445[kdxlin]错误

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/xifenfei/onlinelog/group_3.332.835330505
ORA-00279: change 700860458 generated at 11/05/2015 21:20:15 needed for thread
1
ORA-00289: suggestion : +ARCHIVE/xifenfei/xifenfei_1_5731_835324843.arc
ORA-00280: change 700860458 for thread 1 is in sequence #5731
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/xifenfei/onlinelog/group_2.266.835331047
ORA-00283: recovery session canceled due to errors
ORA-10562: Error occurred while applying redo to data block (file# 2, block#
70104)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 82289
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kdxlin()+4088] [SIGSEGV]
[ADDR:0xC] [PC:0x95FB572] [Address not mapped to object] []
ORA-01112: media recovery not started

ORA-07445[kdxlin()+4088]未找到类似说明,到了这一步,无法简单的恢复成功,只能通过设置隐含参数跳过实例恢复,尝试resetlog库

通过设置_allow_resetlogs_corruption参数继续恢复

SQL> startup pfile='/tmp/pfile.ora' mount;
ORACLE instance started.
Total System Global Area 7315603456 bytes
Fixed Size                  2267384 bytes
Variable Size            2566915848 bytes
Database Buffers         4731174912 bytes
Redo Buffers               15245312 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [700869927],
[0], [700860464], [], [], [], [], [], [], []
Process ID: 13563
Session ID: 157 Serial number: 3

alert日志报错

Fri Nov 06 19:26:39 2015
SMON: enabling cache recovery
Instance recovery: looking for dead threads
Instance recovery: lock domain invalid but no dead threads
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13563.trc  (incident=319140):
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [700869927], [0], [700860464], [], [], [], [], [], [], []
Incident details in:/home/app/oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_319140/xifenfei1_ora_13563_i319140.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Redo thread 2 internally disabled at seq 1 (CKPT)
ARC1: Archiving disabled thread 2 sequence 1
Archived Log entry 9956 added for thread 2 sequence 1 ID 0x0 dest 1:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13563.trc:
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [700869927], [0], [700860464], [], [], [], [], [], [], []
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13563.trc:
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [700869927], [0], [700860464], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 13563): terminating the instance due to error 600
Fri Nov 06 19:26:42 2015
Instance terminated by USER, pid = 13563
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (13563) as a result of ORA-1092
Fri Nov 06 19:26:42 2015
ORA-1092 : opitsk aborting process

这里是比较熟悉的ora-600[kclchkblk_4]错误,和ora-600[2662]错误类似,需要调整scn,由于数据库版本为11.2.0.4,无法使用常规方法调整scn,在修改控制文件,oradebug,bbed方法可供选择

使用oradebug方法处理
因为是asm环境,其他方法处理起来都相对麻烦

[oracle@wisetop1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 6 19:30:59 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup pfile='/tmp/pfile.ora' mount;
ORACLE instance started.
Total System Global Area 7315603456 bytes
Fixed Size                  2267384 bytes
Variable Size            2566915848 bytes
Database Buffers         4731174912 bytes
Redo Buffers               15245312 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x06001AE70 4 0x2FAF0800
BEFORE: [06001AE70, 06001AE74) = 00000000
AFTER:  [06001AE70, 06001AE74) = 2FAF0800
SQL> alter database open;
Database altered.

至此数据库open成功,后续就是处理一些坏块的工作,并建议客户逻辑重建库.

Oracle 12C Active Data Guard Far Sync 配置

Active Data Guard Far Sync是Oracle 12c的新功能（也称为Far Sync Standby），Far Sync功能的实现是通过在距离主库(Primary Database)相对较近的地点配置Far Sync实例，主库(Primary Database) 同步(synchronous)传输redo到Far Sync实例，然后Far Sync实例再将redo异步(asynchronous)传输到终端备库(Standby Database)。这样既可以保证零数据丢失又可以降低主库压力。Far Sync实例只有密码文件，init参数文件和控制文件，而没有数据文件。如果redo 传输采用Maximum Availability模式，我们可以在距离生产中心(Primary Database)相对较近的地点配置Far Sync实例，主库(Primary Database)同步(synchronous)传输redo到Far Sync实例，保证零数据丢失（zero data loss），同时主库和Far Sync距离较近，网络延时很小，因此对主库性能影响很小。然后Far Sync实例再将redo异步(asynchronous)发送到终端备库(Standby Database)。如果redo 传输采用Maximum Performance模式，我们可以在距离生产中心(Primary Database)相对较近的地点配置Far Sync实例，主库(Primary Database) 异步传输redo到Far Sync实例，然后Far Sync实例再负责传输redo到其他多个终端备库(Standby Database)。这样可以减少主库向多个终端备库(Standby Database)传输redo的压力（offload）。 Far Sync配置对于Data Guard 角色转换(role transitions)是透明的，即switchover/failover命令方式与12c之前相同。
1.主库配置fra

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=200G;
System altered.
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';
System altered.

2.启用归档模式和强制日志

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size                  4585912 bytes
Variable Size             671090248 bytes
Database Buffers         1828716544 bytes
Redo Buffers               12189696 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> alter database open;
Database altered.

3.主机规划

192.168.137.121  ora1221   --->主库(ORCL12C)
192.168.137.122  ora1222   --->Far Sync实例(ORCL12CFS)
192.168.137.123  ora1223   --->备库(ORCL12CDG)

4.tns配置

ORCL12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora1221)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl12c)
    )
  )
ORCL12CDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora1223)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl12c)
    )
  )
ORCL12CFS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora1222)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl12c)
    )
  )

5.参数文件配置

--主库
db_unique_name='orcl12c'
service_names='orcl12c'
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl12c'
log_archive_dest_2='service=orcl12cfs lgwr  sync  AFFIRM MAX_FAILURE=1 ALTERNATE=LOG_ARCHIVE_DEST_3 valid_for=(online_logfiles,primary_role) db_unique_name=orcl12cfs'
LOG_ARCHIVE_DEST_3='SERVICE=orcl12cdg  lgwr ASYNC ALTERNATE=LOG_ARCHIVE_DEST_2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl12cdg'
LOG_ARCHIVE_DEST_STATE_3=ALTERNATE
log_archive_config='dg_config=(orcl12c,orcl12cdg,orcl12cfs)'
standby_file_management=auto
db_file_name_convert='/u01/app/oracle/oradata/orcl12c/','/u01/app/oracle/oradata/orcl12c/'
log_file_name_convert='/u01/app/oracle/oradata/orcl12c/','/u01/app/oracle/oradata/orcl12c/'
fal_server='orcl12cdg','orcl12cfs'
--Far Sync实例
db_unique_name='orcl12cfs'
service_names='orcl12c'
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl12cfs'
log_archive_dest_2='service=orcl12cdg lgwr  async valid_for=(STANDBY_LOGFILES,STANDBY_ROLE) db_unique_name=orcl12cdg'
log_archive_config='dg_config=(orcl12c,orcl12cdg,orcl12cfs)'
standby_file_management=manual
fal_server='orcl12c'
--备库
db_unique_name='orcl12cdg'
service_names='orcl12c'
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl12cdg'
log_archive_dest_2='service=orcl12c lgwr  async   valid_for=(online_logfiles,primary_role) db_unique_name=orcl12c'
log_archive_config='dg_config=(orcl12c,orcl12cdg,orcl12cfs)'
standby_file_management=auto
db_file_name_convert='/u01/app/oracle/oradata/orcl12c/','/u01/app/oracle/oradata/orcl12c/'
log_file_name_convert='/u01/app/oracle/oradata/orcl12c/','/u01/app/oracle/oradata/orcl12c/'
fal_server='orcl12c','orcl12cfs'

6.密码文件
拷贝主库的密码文件到Far Sync实例和备库$ORACLE_HOME/dbs下(如果是win在%ORACLE_HOME%/database中)

7.创建Far Sync实例
创建和参数文件匹配的fra,adump目录

--主库
ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/tmp/controlfs01.ctl';
拷贝到Far Sync实例对应的控制文件位置
--Far Sync实例
startup pfile='/tmp/pfile' mount
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 10 '/u01/app/oracle/oradata/orcl12c/std_redo10.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 11 '/u01/app/oracle/oradata/orcl12c/std_redo11.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 12 '/u01/app/oracle/oradata/orcl12c/std_redo12.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 13 '/u01/app/oracle/oradata/orcl12c/std_redo13.log' size 50m reuse;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT  LOGFILE DISCONNECT FROM SESSION;

8.创建备库
创建和参数文件匹配的fra,adump目录

--主库备份（传输至备库）
backup database format '/tmp/ora12c_%U';
--主库创建standby ctl（传输至备库和备库参数文件路径一致）
alter database create standby controlfile as '/tmp/controlst.ctl';
--启动备库至mount
startup pfile='/tmp/pfile' mount;
--备库注册备份
catalog start with '/tmp/xifenfei/';
--备库还原数据文件并恢复
restore database;
recover database;
--增加standby redo
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 10 '/u01/app/oracle/oradata/orcl12c/std_redo10.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 11 '/u01/app/oracle/oradata/orcl12c/std_redo11.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 12 '/u01/app/oracle/oradata/orcl12c/std_redo12.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 13 '/u01/app/oracle/oradata/orcl12c/std_redo13.log' size 50m reuse;
--备库开启mrp进程
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT  LOGFILE DISCONNECT FROM SESSION;
--主库增加standby redo
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 10 '/u01/app/oracle/oradata/orcl12c/std_redo10.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 11 '/u01/app/oracle/oradata/orcl12c/std_redo11.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 12 '/u01/app/oracle/oradata/orcl12c/std_redo12.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 13 '/u01/app/oracle/oradata/orcl12c/std_redo13.log' size 50m reuse;

9.配置结果

SQL> select * from V$DATAGUARD_CONFIG;
DB_UNIQUE_NAME                 PARENT_DBUN                    DEST_ROLE         CURRENT_SCN     CON_ID
------------------------------ ------------------------------ ----------------- ----------- ----------
orcl12c                        NONE                           PRIMARY DATABASE      1950551          0
orcl12cfs                      orcl12c                        FAR SYNC INSTANCE     1950390          0
orcl12cdg                      orcl12cfs                      PHYSICAL STANDBY      1950390          0
SQL> select group#, status, thread#, sequence#, first_change#, next_change# from v$standby_log;
    GROUP# STATUS        THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------- ---------- ------------- ------------
        10 ACTIVE              1         27       1863140
        11 UNASSIGNED          1          0
        12 UNASSIGNED          1          0
        13 UNASSIGNED          1          0
SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

10.调整保护级别
从MAXIMUM PERFORMANCE调整为MAXIMUM AVAILABILITY

--主库
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size                  4585912 bytes
Variable Size             671090248 bytes
Database Buffers         1828716544 bytes
Redo Buffers               12189696 bytes
Database mounted.
SQL> alter database set standby database to maximize availability;
Database altered.
SQL>  select PROTECTION_MODE,PROTECTION_LEVEL from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY UNPROTECTED
SQL> alter database open;
Database altered.
SQL>  select PROTECTION_MODE,PROTECTION_LEVEL from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
--Far Sync实例
SQL>  select PROTECTION_MODE,PROTECTION_LEVEL from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
--备库
SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY RESYNCHRONIZATION

dns解析导致opiodr aborting process unknown ospid (7266) as a result of ORA-609类似错误

数据库alert日志中出现大量类似opiodr aborting process unknown ospid (7266) as a result of ORA-609错误

Tue Oct 27 07:51:10 2015
opiodr aborting process unknown ospid (7266) as a result of ORA-609
Tue Oct 27 08:16:56 2015
opiodr aborting process unknown ospid (7660) as a result of ORA-609
Tue Oct 27 08:17:16 2015
opiodr aborting process unknown ospid (7666) as a result of ORA-609
Tue Oct 27 08:21:50 2015
opiodr aborting process unknown ospid (7725) as a result of ORA-609
Tue Oct 27 08:37:43 2015
opiodr aborting process unknown ospid (7940) as a result of ORA-609
Tue Oct 27 08:51:25 2015
opiodr aborting process unknown ospid (8126) as a result of ORA-609
Tue Oct 27 09:07:16 2015
opiodr aborting process unknown ospid (8359) as a result of ORA-609
Tue Oct 27 09:26:09 2015
opiodr aborting process unknown ospid (8600) as a result of ORA-609

测试tnsping

[oracle@Sql admin]$ tnsping orcl
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 28-OCT-2015 01:40:55
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
/opt/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.162)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.Sql.xifenfei.com)))
OK (20120 msec)
[oracle@Sql admin]$ tnsping orcl
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 28-OCT-2015 01:42:35
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
/opt/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.162)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.Sql.xifenfei.com)))
OK (41600 msec)

hosts文件配置

[oracle@Sql admin]$ vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.2.162 Sql.xifenfei.com
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

同时测试lsnrctl start/stop/status都很慢,但是ping localhost非常快,证明问题在监听之上.search 关键字lsnrctl slow/tnsping slow关键字发现有相关文档:
Bug 8307164 : TNSPING 11G USING DNS AND NOT HOSTS FILE
Listener Startup or Connections Hang in 11g (Doc ID 803838.1)
11g: Remote Connections Take Very Long to Establish (Doc ID 856820.1)
通过这些文档,可以知道在oracle 10g中解析主机名使用gethostbyname()函数,11g中使用了使用getaddrinfo()函数.这个函数对于/etc/nsswitch.conf的调用和ethostbyname()不一样,从而出现此类问题.根据官方建议:

Linux
hosts: files [NOTFOUND=continue] dns OR  hosts:files/dns
HPUX and Solaris
ipnodes: files [NOTFOUND=continue] dns

本次解决方法
因为对于没有使用到dns服务器的数据库环境而言,没有必要配置这玩意,给自己埋雷，估计是当时安装yum之时没有清理掉

vi /etc/nsswitch.conf
hosts:      files nds  -->hosts:      files

再次测试tnsping,lsnrctl等命令,速度ok

[oracle@Sql"]$ tnsping orcl
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 28-OCT-2015 01:58:30
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
/opt/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.162)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.Sql.xifenfei.com)))
OK (10 msec)

对于此类监听慢的问题，可以通过对client/server端trace进一步跟踪

TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = client/server
TRACE_DIRECTORY_CLIENT = [any valid directory path]
TRACE_TIMESTAMP_CLIENT = ON
DIAG_ADR_ENABLED=off

Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets—201507

11.2.0.1.0	11.2.0.1.0 Download Page
Older Versions	Raise a non-technical SR for Physical Shipment or Download URL. Refer Doc ID 1071023.1

Patchsets
^l12.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
^d10.2.0.4 (10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER)	6810189
^e10.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

PSU, SPU(CPU), Bundle Patches


12.1.0.2
Description	PSU	GI PSU	Bundle Patch (Windows 32bit & 64bit)
JUL2015	20831110 (12.1.0.2.4)	20996835 (12.1.0.2.4)	21126814 (12.1.0.2.7)
APR2015	20299023 (12.1.0.2.3)	20485724 (12.1.0.2.3)	20684004 (12.1.0.2.4)
JAN2015	19769480 (12.1.0.2.2)	19954978 (12.1.0.2.2)	19720843 (12.1.0.2.1)
OCT2014	19303936 (12.1.0.2.1)	19392646 (12.1.0.2.1)	N/A

12.1.0.1
Description	PSU	GI PSU	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
JUL2015	20831107 (12.1.0.1.8)	^j20996901 / ^k20996911 (12.1.0.1.8)	21076681 (12.1.0.1.20)
APR2015	20299016 (12.1.0.1.7)	^j20485762 / ^k19971331 (12.1.0.1.7)	20558101 (12.1.0.1.18)
JAN2015	19769486 (12.1.0.1.6)	^j19971324 / ^k19971331 (12.1.0.1.6)	20160748 (12.1.0.1.16)
OCT2014	19121550 (12.1.0.1.5)	^j19392372 / ^k19392451 (12.1.0.1.5)	19542943 (12.1.0.1.14)
JUL2014	18522516 (12.1.0.1.4)	^j18705901 / ^k18705972 (12.1.0.1.4)	19062327 (12.1.0.1.11)
APR2014	18031528 (12.1.0.1.3)	^j18139660 / ^k18413105 (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)
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)
^aOCT2013	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)
^aJUL2011	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


11.1.0.7
Description	PSU	SPU(CPU)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
^bJUL2015	20761024 (11.1.0.7.24)	20803573	21104030	21104029
^bAPR2015	20299012 (11.1.0.7.23)	20299020	20420391	20420390
^bJAN2015	19769499 (11.1.0.7.22)	19854433	20126915	20126914
^bOCT2014	19152553 (11.1.0.7.21)	19274522	19609034	19609032
^bJUL2014	18522513 (11.1.0.7.20)	18681875	18944208	18944207
^bAPR2014	18031726 (11.1.0.7.19)	18139703	18372258	18372257
^bJAN2014	17465583 (11.1.0.7.18)	17551415	17906936	17906935
^bOCT2013	17082366 (11.1.0.7.17)	17082374	17363760	17363759
^bJUL2013	16619896 (11.1.0.7.16)	16742110	16803788	16803787
^bAPR2013	16056268 (11.1.0.7.15)	16308394	16345862	16345861
^bJAN2013	14739378 (11.1.0.7.14)	14841452	15848067	15848066
^bOCT2012	14275623 (11.1.0.7.13)	14390384	14672313	14672312
JUL2012	13923474 (11.1.0.7.12)	14038803	14109868	14109867
APR2012	13621679 (11.1.0.7.11)	13632731	13715810	13715809
JAN2012	13343461 (11.1.0.7.10)	13343453	13460956	13460955
OCT2011	12827740 (11.1.0.7.9)	12828097	12914916	12914915
JUL2011	12419384 (11.1.0.7.8)	12419265	12695278	12695277
APR2011	11724936 (11.1.0.7.7)	11724999	11741170	11741169
JAN2011	10248531 (11.1.0.7.6)	10249534	10350788	10350787
OCT2010	9952228 (11.1.0.7.5)	9952269	9773825	9773817
JUL2010	9654987 (11.1.0.7.4)	9655014	9869912	9869911
APR2010	9352179 (11.1.0.7.3)	9369783	9392335	9392331
JAN2010	9209238 (11.1.0.7.2)	9114072	9166861	9166858
OCT2009	8833297 (11.1.0.7.1)	8836375	8928977	8928976
JUL2009	N/A	8534338	8553515	8553512
APR2009	N/A	8290478	8343070	8343061


11.1.0.6
Description	CPU	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
^aJUL2009	8534378	8563155	8563154
APR2009	8290402	8333657	8333655
JAN2009	7592335	7631981	7631980
OCT2008	7375639	7378393	7378392
JUL2008	7150417	7210197	7210195
APR2008	6864063	6867180	6867178


10.2.0.5
Description	PSU	SPU(CPU)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
^abJUL2013	16619894 (10.2.0.5.12)	16742123	16803782	16803780	16803781
^bAPR2013	16056270 (10.2.0.5.11)	16270946	16345857	16345855	16345856
^bJAN2013	14727319 (10.2.0.5.10)	14841459	15848062	15848060	15848061
^bOCT2012	14275629 (10.2.0.5.9)	14390396	14553358	14553356	14553357
^bJUL2012	13923855 (10.2.0.5.8)	14038805	14134053	14134051	14134052
^bAPR2012	13632743 (10.2.0.5.7)	13632738	13654815	13654814	13870404
JAN2012	13343471 (10.2.0.5.6)	13343467	^b13460968	^b13460967	N/A
^bOCT2011	12827745 (10.2.0.5.5)	12828105	^c12914913	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)
^bgJUL2013	16619897 (10.2.0.4.17)	16742253	N/A	N/A	N/A
^bgAPR2013	16056269 (10.2.0.4.16)	16270931	N/A	N/A	N/A
^bgJAN2013	14736542 (10.2.0.4.15)	14841471	N/A	N/A	N/A
^bgOCT2012	14275630 (10.2.0.4.14)	14390410	N/A	N/A	N/A
^bgJUL2012	13923851 (10.2.0.4.13)	14038814	N/A	N/A	N/A
^abAPR2012	12879933 (10.2.0.4.12)	12879926	13928775	13928776	N/A
JAN2012	12879929 (10.2.0.4.11)	12879912	^b13654060	N/A	N/A
^bOCT2011	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)
^aJAN2009	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
^hOCT2008	7375660	N/A	N/A	N/A
^hJUL2008	7154083	N/A	N/A	N/A
^hAPR2008	6864071	N/A	N/A	N/A
^aJAN2008	6646850	N/A	N/A	N/A
^fOCT2007	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

OJVM PSU Patches


12.1.0.2
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + GI PSU
JUL2015	21068507 (12.1.0.2.4)	21153530 (12.1.0.2.3)	21150768	21150782
APR2015	20415564 (12.1.0.2.3)	20391199 (12.1.0.2.2)	20834354	20834538
JAN2015	19877336 (12.1.0.2.2)	20225938 (12.1.0.2.1)	20132434	20132450
OCT2014 (12.1.0.2.1)	19282028		19791366	19791375


12.1.0.1
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + GI PSU	Generic JDBC
JUL2015 (12.1.0.1.4)	21068523	21153513	21150806	21150817	Included in OJVM PSU
APR2015 (12.1.0.1.3)	20406245	20225909	20834568	20834579
JAN2015 (12.1.0.1.2)	19877342	20225916	20132482	20132489
OCT2014 (12.1.0.1.1)	19282024	19801531	19791363	19791360	19852357


11.2.0.4
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + DB SPU	Combo OJVM + GI PSU	Generic JDBC
JUL2015 (11.2.0.4.4)	21068539	21153498	21150851	21150829	21150864	Included in OJVM PSU
APR2015 (11.2.0.4.3)	20406239	20225988	20834611	20834597	20834621
JAN2015 (11.2.0.4.2)	19877440	20225982	20132580	20132517	20132615
OCT2014 (11.2.0.4.1)	19282021	19799291	19791364	19791358	19791420	19852360


11.2.0.3
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + DB SPU	Combo OJVM + GI PSU	Generic JDBC
JUL2015 (11.2.0.3.4)	21068553	21153470	21150891	21150885	21150904	Included in OJVM PSU
APR2015 (11.2.0.3.3)	20406220	20391185	20834670	20834653	20834686
JAN2015 (11.2.0.3.2)	19877443	20227195	20132646	20132635	20132651
OCT2014 (11.2.0.3.1)	19282015	19806120	19791427	19791426	19791428	19852361


11.1.0.7
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + DB SPU	Combo OJVM + GI PSU	Generic JDBC
JUL2015 (11.1.0.7.4)	21068565	21153423	21150939	21150929	N/A	Included in OJVM PSU
APR2015 (11.1.0.7.3)	20406213	20391156	20834724	20834712	N/A
JAN2015 (11.1.0.7.2)	19877446	20227146	20132677	20132669	N/A
OCT2014 (11.1.0.7.1)	19282002	19806118	19791436	19791434	N/A	19852363

EXP-00104: datatype (BINARY_DOUBLE) 错误

进行了一次使用exp数据迁移,客户和我说丢失了表,我觉得比较奇怪,分析日志发现EXP-00104: datatype (BINARY_DOUBLE)错误,对其单表进行导出,发现问题重现

[oracle@app73 ~]$ exp system/oracle123 file=/Data/1.dmp tables=CHF.T_XIFENFEI
Export: Release 11.1.0.6.0 - Production on Wed Oct 14 23:25:31 2015
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to CHF
. . exporting table                  T_XIFENFEI
EXP-00104: datatype (BINARY_DOUBLE) of column NUMCOMMISSIONRATE in table
 CHF.T_XIFENFEI is not supported, table will not be exported

数据库版本

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

表结构

SQL> DESC CHF.T_XIFENFEI
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
…………
 NUMPRO                                             NUMBER
 NUMCOMMISSIONRATE                                  BINARY_DOUBLE
 NUMSEQ                                             BINARY_DOUBLE
 NUMMARK                                            NUMBER
…………

解决办法,使用expdp/impdp代替exp/imp

[oracle@app73 ~]$ expdp system/manager dumpfile=exp.dmp tables=CHF.T_XIFENFEI  directory=exp_dir
Export: Release 11.1.0.6.0 - 64bit Production on Wednesday, 14 October, 2015 23:30:38
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":system/******** dumpfile=exp.dmp tables=CHF.T_XIFENFEI directory=exp_dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 959 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "CHF"."T_XIFENFEI"                    814.7 MB 1601202 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /tmp/exp.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 23:31:31
[oracle@app101-2 Data]$ impdp system/oracle dumpfile=T_XIFENFEI.dmp tables=CHF.T_XIFENFEI directory=exp_dir
Import: Release 11.2.0.4.0 - Production on Wed Oct 14 23:44:35 2015
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 and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":system/******** dumpfile=T_XIFENFEI.dmp tables=CHF.T_XIFENFEI directory=exp_dir
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "CHF"."T_XIFENFEI"                    814.7 MB 1601202 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Wed Oct 14 23:45:37 2015 elapsed 0 00:01:02

出现此类错误,也和自己的粗心有关系,没有仔细检查导出日志,另外该问题从10.1.0.2就有了,直到现在才发现,证明知识点欠缺.同样对于binary_float也有一样问题.
具体可以参考：Export Failed Due To EXP-104 Error, Datatype (Binary_double) Is Not Supported [ID 421591.1]

作者：admin