ORA-600 4193 错误说明和解决

ORA-600 4193 解释说明

ERROR:              

  Format: ORA-600 [4193] [a] [b]

VERSIONS:           
  versions 6.0 to 12.1

DESCRIPTION:        

  A mismatch has been detected between Redo records and Rollback (Undo) 
  records.

  We are validating the Undo block sequence number in the undo block against 
  the Redo block sequence number relating to the change being applied.

  This error is reported when this validation fails.

ARGUMENTS:
  Arg [a] Undo record seq number
  Arg [b] Redo record seq number

FUNCTIONALITY:
  KERNEL TRANSACTION UNDO





ORA-600 [4193] [a] [b] [ ] [ ]  [ ]        
Versions: 7.2.2  - 9.2.0                              Source: ktuc.c
===========================================================================
Meaning: seq# mismatch while adding an undo record to an undo block. This 
         is done by the application of redo. 
---------------------------------------------------------------------------
Argument Description:

    a. (ktubhseq): undo record seq# - this is the seq# of the block that 
                                      this undo record WILL BE APPLIED TO. 
                                      This is from the Undo Block. It is 
                                      NOT the seq# of the undo block itself.
                                      
    b. (ktudbseq): redo RECORD seq# - this is the seq# number in the block 
                                      that this redo WILL BE APPLIED TO. 
                                      This is from the Redo Record. 

---------------------------------------------------------------------------
Diagnosis:

    This error is raised in kturdb which handles the adding of undo records 
    by the application of redo. 
    
    When we try to apply redo to an undo block (forward changes are made by 
    the application of redo to a block) we check that the seq# in the undo 
    record matches the seq# in the redo record. These seq# should be the 
    same because when we apply a redo record we must apply it to the 
    correct version of the block. We can only apply a redo record to a 
    block that contains the same seq# as in the redo record. 

    If the seq# do not match then this error is raised. This implies some 
    kind of block corruption in either the redo or the undo block. 

7.3.x - 8.1.7.x
ASSERT2(ubh->ktubhseq == db->ktudbseq, OERI(4193), KSESVSGN,
            ubh->ktubhseq, db->ktudbseq);
9.2.x
ksesic2(OERI(4193), ksenrg(ubh->ktubhseq), ksenrg(db->ktudbseq));

struct ktubh
{
  kxid  ktubhxid;      /* txid of tx currently using or last used this block */
  ub2   ktubhseq;                              /* undo block sequence number */
  ub1   ktubhcnt;    /* high water mark record index, number of undo entries */
  ub1   ktubhirb;  /* rollback record index, rec index to start the rollback */
  ub1   ktubhicl;  /* collecting record index, rec index to start retrieving col info */
  ub1   ktubhflg;                                                 /* dummy */
  ub2   ktubhidx[1];     /* byte offset of record in block, grows at runtime */
};

struct ktudb   Kernel Transaction Undo Data operation Block (redo)
{
  ub2    ktudbsiz;                                          /* size of entry */
  ub2    ktudbspc;                 /* verification: space left in undo block */
  ub2    ktudbflg;            /* flag to indicate the kind of redo operation */
  kxid   ktudbxid;                                          /* current tx id */
  ub2    ktudbseq;                                  /* block sequence number */
  ub1    ktudbrec;                       /* new record index for this change */
};

ORA 600 4193 处理方法同How to resolve ORA-600 [4194] errors

发表在 Oracle备份恢复 | 标签为 | 留下评论

How to resolve ORA-600 [4194] errors

在oracle恢复中ORA-600 4194是一个非常常见的错误,该错误的主要原因是由于redo记录和undo(rollback)记录不匹配.
ORA 600 4194错误原因以及含义

ERROR:              

  Format: ORA-600 [4194] [a] [b]
 
VERSIONS:           
  versions 6.0 to 12.1 

DESCRIPTION:

  A mismatch has been detected between Redo records and rollback (Undo) 
  records.

  We are validating the Undo record number relating to the change being 
  applied against the maximum undo record number recorded in the undo block.

  This error is reported when the validation fails.

ARGUMENTS:
  Arg [a] Maximum Undo record number in Undo block
  Arg [b] Undo record number from Redo block

ORA 600 4194 错误处理思路
第一步

Confirm whether the database is up and running or not.  If the database fails to start or crashes shortly 
after startup due to this error occurring, then try setting event 10513 at level 2 in the init.ora/spfile 
to disable transaction recovery and restart the instance, e.g.:

      event = "10513 trace name context forever, level 2"

This may allow the database to successfully open and stay up so that 
the required diagnostics/actions can be performed.

第二步

In the trace file there should be an undo segment header dump, and so check 
to see if the undo segment header shows an active transaction after recovery, e.g.:


TRN TBL    <---- Represents the Transaction table for the particular undo segment

index state cflags wrap# uel scn dba
--------------------------------------------------------------------------------------------- 
0x41 9 0x80 0x35ab6 0xffff 0x0695.38f6b959 0x1081e796 
0x42 9 0x80 0x35bb1 0x000e 0x0695.38f6b028 0x1081e793 
0x43 9 0x80 0x35b11 0x005d 0x0695.38f6b7ae 0x1081e795 
0x44 9 0x80 0x359f0 0x0036 0x0695.38f69a91 0x1081e78e 
0x45 10 0x80 0x35b1b 0x0000 0x0695.3a0aba4d 0x1081e796 
0x46 9 0x80 0x35bb7 0x001c 0x0695.38f69bde 0x1081e78f 
===================================

State ---> This column specifies the status of the transaction 
                  9 -----> represents a commited transaction
                  10 ---> Represents a active transaction
Dba -----> Undo block containing the undo records
                  Strictly speaking this is the block at the end of the undo chain.

You can see from the transaction table that there is an active transaction 
for this particular rollback/undo segment after recovery.
Therefore this rollback/undo segment and/or undo tablespace cannot be dropped without corrupting the database!  
Therefore recreating the UNDO tablespace is not an option.

第三步

From the trace file determine the affected undo segment, e.g.:

Block image after block recovery:

UNDO BLK: 
xid: 0x0015.02b.0001544b seq: 0x163e cnt: 0x12 irb: 0x12 icl: 0x0 flg: 0x0000

XID ==> Undo segment no + Slot no + Sequence no

Therefore, in this case the Undo Segment is:

USN# 0x15 (Hex) ==> 21 (Dec)  ==> _SYSSMU21$

So if and ONLY IF the transaction table shows no active transaction can the
 rollback/undo segment be offlined and dropped.Note however, 
that before you can confirm if the entire UNDO tablespace can be dropped, you would need to check the 
transaction tables of ALL active rollback/undo segments in the same wasy as the above.  
The steps required to drop the rollback/undo segment are fully detailed in Note:179952.1, 
but are briefly listed here for completeness:

If using Automatic Undo Management


Offline the undo segment using the _OFFLINE_ROLLBACK_SEGMENTS parameter and bounce the database as follows:

1.  Create  and edit the init.ora file for the instance to set the following parameters:

UNDO_MANAGEMENT=MANUAL 
_OFFLINE_ROLLBACK_SEGMENTS=(_SYSSMU21$)

2.  Open the database in restricted mode to prevent user access, e.g.:

connect / as sysdba
startup restrict pfile = '<Full path to init.ora file>';

3.  Drop the rollback/undo segment, e.g.:

drop rollback segment "_SYSSMU21";

4.  Shutdown the instance, and remove the init.ora parameters added in point 1 and restart the instance, e.g.:

shutdown immediate
startup


If SMON was recovering the transaction then this may not work as we cannot open the database if it is determined 
to be in an inconsistent state. I have reviewed a number of SRs where this approach was successful, 
so it is important to try it first but understand that it may fail and you will have to resort to 
a point in time recovery or forcing open the DB and recreating it.

第四步

Now we need to dump the undo block to see which object was affected.  
We noted in Step 2 that this is the active transaction (from the trace file): 

TRN TBL 

index state cflags wrap# uel scn dba 
0x45 10 0x80 0x35b1b 0x0000 0x0695.3a0aba4d 0x1081e796 

Dba----------------> Undo block containing the undo records 

dba--->0x1081e796 is the block containing the active transaction . 


Use the WebIV tools to convert this RDBA to block number (block#) and file number (file#), e.g.: 

V SPLIT ==> DBA (Hex) = File#,Block# (Hex File#,Block#) 
= ===== === ===== ============ 
V8 10,10 ==> 276948886 (0x1081e796) = 66,124822 (0x42 0x1e796) 


So the file# is 66 and the block# is 124822, so dump the block by issuing: 

SQL> Alter system dump datafile 66 block 124822; 


This will generate a trace file in the user_dump_dest.  The following is a sample of the information in the undo block:

UNDO BLK: 
xid: 0x000c.045.00035b1b seq: 0x1e14 cnt: 0x17 irb: 0x17 icl: 0x0 flg: 0x0000 

Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset 
--------------------------------------------------------------------------- 
0x01 0x1f8c 0x02 0x1f30 0x03 0x1ed4 0x04 0x1e78 0x05 0x1e1c 
0x06 0x1dc0 0x07 0x1d64 0x08 0x1d08 0x09 0x1cac 0x0a 0x1c50 
0x0b 0x1bf4 0x0c 0x1b98 0x0d 0x1b3c 0x0e 0x1ae0 0x0f 0x1a74 
0x10 0x1a18 0x11 0x19bc 0x12 0x1960 0x13 0x1904 0x14 0x187c 
0x15 0x181c 0x16 0x1798 0x17 0x173c 

* Rec #0x16 slt: 0x45 objn: 1485619(0x0016ab33) objd: 1485619 tblspc: 71(0x00000047) 
* Layer: 11 (Row) opc: 1 rci 0x00 
Undo type: Regular undo Begin trans Last buffer split: No 
Temp Object: No 
Tablespace Undo: No 
rdba: 0x00000000 
*----------------------------- 
uba: 0x1081e796.1e14.14 ctl max scn: 0x0695.38f69853 prv tx scn: 0x0695.38f698a1 
KDO undo record: 
KTB Redo 
op: 0x04 ver: 0x01 
op: L itl: scn: 0x0019.009.00034237 uba: 0x36c0cce4.1d2f.19 
flg: C--- lkc: 0 scn: 0x0695.38f6b96b 
KDO Op code: URP xtype: XA bdba: 0x35406893 hdba: 0x35406892 
itli: 1 ispac: 0 maxfr: 4863 
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0 
ncol: 1 nnew: 1 size: -1 
col 0: [ 4] c3 0e 36 2e 
*----------------------------- 

* Rec #0x17 slt: 0x45 objn: 1485619(0x0016ab33) objd: 1485619 tblspc: 71(0x00000047) 
* Layer: 11 (Row) opc: 1 rci 0x16 
Undo type: Regular undo Last buffer split: No 
Temp Object: No 
Tablespace Undo: No 
rdba: 0x00000000 
*----------------------------- 

From the trace file above:

UNDO BLK: 
xid: 0x000c.045.00035b1b seq: 0x1e14 cnt: 0x17 irb: 0x17 icl: 0x0 flg: 0x0000

The undo segment with the active transaction is segment is 0x000c (Hex) which is 12 (Dec) as the XID is:

      Undo segment no + Slot no + Sequence no

This step is often skipped because it was performed earlier in step 3, however it is a good idea to do this 
again now to make sure that the XID from the UNDO block matches the UNDO SEGMENT HEADER, 
this way you have followed all the chain, from the UNDO SEGMENT to UNDO BLOCK, back and forth.  
If there is a conflict here please check and make sure that the customer dumped the correct undo block.

Check for the value of irb which is an index which points you to the latest change done to the undo block.
This is the point from which a rollback would begin if one was issued.

From the trace file we see: 'irb: 0x17' so this points to record 0x17, 
so search for this particular string i.e 0x17 and it will take you to undo record 'REC #0x17', e.g.:

* Rec #0x17 slt: 0x45 objn: 1485619(0x0016ab33) objd: 1485619 tblspc: 71(0x00000047) 
* Layer: 11 (Row) opc: 1 rci 0x16 
Undo type: Regular undo Last buffer split: No 
Temp Object: No 
Tablespace Undo: No 
rdba: 0x00000000 
*----------------------------- 

Note the slot number (slt) is 0x45, the object number (objn) is the OBJECT_ID from dba_objects 
and data object number (objd) is the DATA_OBJECT_ID from dba_objects.  
These numbers may be the same but not necessarily, and so if the database is open then identify this object, e.g.:

        select object_name, owner, object_type, data_object_id from dba_objects where object_id = <objn>;

This is the object, which has an active transaction.  Note in the above trace file extract that rci 
has a value of 0x16 which means that this record is at the end of an undo chain.  
This means that the chain continues in another UNDO BLOCK.  
Please refer to unpublished Note:281504.1 for information on Undo chains.

So the next record that needs to be rolled back is present in REC #X016.  
If rci is 0x00 then it means that this is the first record present in the undo chain 
and so you can check to see if there is rdba info, e.g.:


* Rec #0x16 slt: 0x45 objn: 1485619(0x0016ab33) objd: 1485619 tblspc: 71(0x00000047) 
* Layer: 11 (Row) opc: 1 rci 0x00 
Undo type: Regular undo Begin trans Last buffer split: No 
Temp Object: No 
Tablespace Undo: No 
rdba: 0x00000000 
*----------------------------- 
uba: 0x1081e796.1e14.14 ctl max scn: 0x0695.38f69853 prv tx scn: 0x0695.38f698a1 
KDO undo record: 
KTB Redo 
op: 0x04 ver: 0x01 
op: L itl: scn: 0x0019.009.00034237 uba: 0x36c0cce4.1d2f.19 
flg: C--- lkc: 0 scn: 0x0695.38f6b96b 
KDO Op code: URP xtype: XA bdba: 0x35406893 hdba: 0x35406892 
itli: 1 ispac: 0 maxfr: 4863 
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0 
ncol: 1 nnew: 1 size: -1 
col 0: [ 4] c3 0e 36 2e 
*----------------------------- 


If the object is an Index, drop and recreate it.  If it is a table, 
then again the table would need to be dropped and recreated (or truncated) 
so that its object number changes and hence the rollback/undo is no longer required.  
If this isn't possible, then you have two options:

First take a backup of the database in its current state.  
This is critical in case anything goes wrong and you lose the opportunity to salvage the data! 

Option 1

 - Restore the undo segment datafile and the datafile containing the object and perform a full recovery.
   This can only be done if you have all the archived redo as you will need to do full recovery on these files.

OR 

Option 2

If option 1 is not possible, you can use the unsupported method, e.g.:

Specify the undo segment in the _OFFLINE_ROLLBACK_SEGMENTS parameter and try to drop the rollback segment.
If there is an active transaction then this is not likely to work and you will probably need 
to set the _CORRUPTED_ROLLBACK_SEGMENTS parameter as well

温馨提示:
1.隐含参数_OFFLINE_ROLLBACK_SEGMENTS/_CORRUPTED_ROLLBACK_SEGMENTS属于Oracle内部隐含参数,建议在Oracle support认可的情况下使用,因为使用之后可能导致数据库事务完整性彻底损坏
2.进行屏蔽事务之前,如果条件允许建议使用txchecker检查
2.使用上述方法恢复数据库之后,建议通过逻辑方式导出导入重建数据库

发表在 Oracle备份恢复 | 标签为 , | 留下评论

Exadata火线救援:10TB级数据恢复—强制拉库篇

这个库的恢复有一些历史故事(【力荐】Exadata火线救援:10TB级数据修复经典案例详解!):xx运营商x2的1/4配置的oracle exadata机器,跑了近6年,最近有一个cell节点主机异常,在rebalance过程中,只有两个节点的cell其中一个节点坏了一个硬盘导致.导致asm diskgroup无法正常mount,最后该运营商运维三方通过amdu把该一体机中的数据文件全部抽出来,然后在恢复过程中出现大量错误无法解决,请求我们支持
数据库open过程报ORA-01555错误

Thu Jul  14 00:01:04 2016
alter database open
Thu Jul  14 00:01:04 2016
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /data/amdu/redo/DATA_EC_260.f
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jul  14 00:01:05 2016
SMON: enabling cache recovery
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0b26.9f080238):
select ctime, mtime, stime from obj$ where obj# = :1
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_59546.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 83 with name &quot;_SYSSMU83_1078760807$&quot; too small
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_59546.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 83 with name &quot;_SYSSMU83_1078760807$&quot; too small
Error 704 happened during db open, shutting down database
USER (ospid: 59546): terminating the instance due to error 704
Instance terminated by USER, pid = 59546
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (59546) as a result of ORA-1092

这个错误比较常见,可以通过推scn就可以解决,由于已经安装了scn patch,通过oradebug推scn解决该问题.

ORA-600 4194
这个ora 600 4194相对比较特殊,在SMON: enabling cache recovery之后立马报出来,然后实例直接open失败.

Thu Jul  14 00:06:15 2016
alter database open
Thu Jul  14 00:06:15 2016
Thread 1 advanced to log sequence 3 (thread open)
Thread 1 opened at log sequence 3
  Current log# 3 seq# 3 mem# 0: /data/amdu/redo/DATA_EC_263.f
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jul  14 00:06:15 2016
SMON: enabling cache recovery
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_60038.trc  (incident=1080450):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Block recovery from logseq 3, block 3 to scn 12269096776739
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
  Mem# 0: /data/amdu/redo/DATA_EC_263.f
Block recovery stopped at EOT rba 3.5.16
Block recovery completed at rba 3.5.16, scn 2856.2670179361
Block recovery from logseq 3, block 3 to scn 12269096776736
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
  Mem# 0: /data/amdu/redo/DATA_EC_263.f
Block recovery completed at rba 3.5.16, scn 2856.2670179361
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_60038.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_60038.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 60038): terminating the instance due to error 600
Instance terminated by USER, pid = 60038
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (60038) as a result of ORA-1092

trace文件分析
打开数据库报ORA-600[4194]错误,对启动过程进行10046跟踪并且分析trace文件发现

PARSING IN CURSOR #140375370511672 len=148 dep=1 uid=0 oct=6 lid=0 tim=3501342849457766 hv=3540833987 
ad='a47df47a8' sqlid='5ansr7r9htpq3'
update undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,
scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1
END OF STMT
PARSE #140375370511672:c=27996,e=28041,p=66,cr=224,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=3501342849457765
BINDS #140375370511672:
 Bind#0
  oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=178 siz=32 off=0
  kxsbbbfp=a47e093ca  bln=32  avl=20  flg=09
  value=&quot;_SYSSMU1_2856534670$&quot;
 Bind#1
  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=7fabae3b92b0  bln=24  avl=03  flg=05
  value=1024
 Bind#2
  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=7fabae3b9280  bln=24  avl=03  flg=05
  value=128
 Bind#3
  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=7fabae3b9248  bln=24  avl=02  flg=05
  value=5
 Bind#4
  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=7fabae3b9218  bln=24  avl=02  flg=05
  value=1
 Bind#5
  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=7fabae3b91e8  bln=24  avl=03  flg=05
  value=3398
 Bind#6
  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=7fabae3b91b8  bln=24  avl=05  flg=05
  value=1485261
 Bind#7
  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=7fabae3b9180  bln=24  avl=06  flg=05
  value=1946693999
 Bind#8
  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=7fabae3b8ec8  bln=24  avl=03  flg=05
  value=2847
 Bind#9
  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=7fabae3b8e98  bln=24  avl=02  flg=05
  value=1
 Bind#10
  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=7fabae3b8e68  bln=24  avl=02  flg=05
  value=2
 Bind#11
  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=7fabae3b8e38  bln=24  avl=02  flg=05
  value=2
 Bind#12
  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=7fabae3b92e0  bln=22  avl=02  flg=05
  value=1
WAIT #140375370511672: nam='db file sequential read' ela= 21 file#=1 block#=179020 blocks=1 obj#=0 tim=3501342849459353

*** 2016-07-14 03:14:09.548
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

很明显数据库是在update undo$的时候,读取到file 1 block 179020的时候报错,继续分析trace文件

Error 600 in redo application callback
Dump of change vector:
TYP:0 CLS:16 AFN:1 DBA:0x0042bb4c OBJ:4294967295 SCN:0x0b26.a88e815e SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 272 spc: 4906 flg: 0x0012 seq: 0x0f4c rec: 0x0d
            xid:  0x0000.01b.00000b63  
ktubl redo: slt: 27 rci: 0 opc: 11.1 [objn: 15 objd: 15 tsn: 0]
Undo type:  Regular undo        Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
             0x00000000  prev ctl uba: 0x0042bb4c.0f4c.0c 
prev ctl max cmt scn:  0x0b23.ccb9eb89  prev tx cmt scn:  0x0b23.ccb9ebac 
txn start scn:  0xffff.ffffffff  logon user: 0  prev brb: 4373321  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo 
op: 0x04  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0000.01c.00000b65 uba: 0x0042bb4a.0f4c.1d
                      flg: C---    lkc:  0     scn: 0x0b25.7391ee5c
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x004000e1  hdba: 0x004000e0
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
ncol: 17 nnew: 12 size: 0
col  1: [20]  5f 53 59 53 53 4d 55 31 5f 32 38 35 36 35 33 34 36 37 30 24
col  2: [ 2]  c1 02
col  3: [ 3]  c2 0b 19
col  4: [ 3]  c2 02 1d
col  5: [ 6]  c5 14 2f 46 28 64
col  6: [ 3]  c2 1d 30
col  7: [ 5]  c4 02 31 35 3e
col  8: [ 3]  c2 22 63
col  9: [ 2]  c1 02
col 10: [ 2]  c1 04
col 11: [ 2]  c1 03
col 16: [ 2]  c1 03
Block after image is corrupt: 
buffer tsn: 0 rdba: 0x0042bb4c (1/179020)
scn: 0x0b26.a88e815e seq: 0x01 flg: 0x04 tail: 0x815e0201
frmt: 0x02 chkval: 0xf022 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000094E07A000 to 0x000000094E07C000
94E07A000 0000A202 0042BB4C A88E815E 04010B26  [....L.B.^...&amp;...]
94E07A010 0000F022 004E0000 00000B5B 1D1D0F4C  [&quot;.....N.[...L...]

我们知道在file 1 block 179020的时候redo和undo信息不匹配,出现了上述的ORA 600 4194的错误.进一步分析

Block image after block recovery:
buffer tsn: 0 rdba: 0x00400080 (1/128)
scn: 0x0b26.6389e19d seq: 0x01 flg: 0x04 tail: 0xe19d0e01
frmt: 0x02 chkval: 0x7c95 type: 0x0e=KTU UNDO HEADER W/UNLIMITED EXTENTS
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000094DAB2000 to 0x000000094DAB4000
94DAB2000 0000A20E 00400080 6389E19D 04010B26  [......@....c&amp;...]
94DAB2010 00007C95 00000000 00000000 00000000  [.|..............]
94DAB2020 00000000 00000015 000002FF 00001020  [............ ...]
94DAB2030 0000000D 0000004C 00000080 0042BB4C  [....L.......L.B.]


  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 21     #blocks: 767   
                  last map  0x00000000  #maps: 0      offset: 4128  
      Highwater::  0x0042bb4c  ext#: 13     blk#: 76     ext size: 128   
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 0     
  mapblk  0x00000000  offset: 13    
                   Unlocked
     Map Header:: next  0x00000000  #extents: 21   obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x00400081  length: 7     
   0x00413a38  length: 8     
   0x00400088  length: 8     
   0x00413a30  length: 8     
   0x0042b888  length: 8     
   0x0042b890  length: 8     
   0x0042b898  length: 8     
   0x0042b8a0  length: 8     
   0x0042b8a8  length: 8     
   0x0042b8b0  length: 8     
   0x0042b8b8  length: 8     
   0x0042b8c0  length: 8     
   0x0042ba80  length: 128   
   0x0042bb00  length: 128   
   0x0042bc00  length: 128   
   0x0042bc80  length: 128   
   0x0042bb80  length: 128   
   0x00400210  length: 8     
   0x00400218  length: 8     
   0x00400220  length: 8     
   0x00400228  length: 8     
  
  TRN CTL:: seq: 0x0f4c chd: 0x001b ctl: 0x0043 inc: 0x00000000 nfb: 0x0001
            mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x0042bb4c.0f4c.0c scn: 0x0b23.ccb9eb89
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x0042bb4c.0f4c.0c ext: 0xd  spc: 0x132a  
    uba: 0x00000000.0f4c.0c ext: 0xd  spc: 0x12f6  
    uba: 0x00000000.0f4c.01 ext: 0xd  spc: 0x1ec8  
    uba: 0x00000000.0f4c.04 ext: 0xd  spc: 0x1b86  
    uba: 0x00000000.0f4c.09 ext: 0xd  spc: 0x162c  
  TRN TBL::
 
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x0b62  0x0011  0x0b25.2dacaf61  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x01    9    0x00  0x0b64  0x0024  0x0b24.a6a2cf7b  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x02    9    0x00  0x0b65  0x0036  0x0b25.7391eda0  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x03    9    0x00  0x0b4f  0x0007  0x0b24.337bf49b  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x04    9    0x00  0x0b64  0x0051  0x0b23.ff22c637  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x05    9    0x00  0x0b64  0x0022  0x0b26.4393eb1e  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x06    9    0x00  0x0b66  0x0058  0x0b24.335c794d  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x07    9    0x00  0x0b4f  0x001d  0x0b24.4e05f2af  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x08    9    0x00  0x0b65  0x005e  0x0b23.ff22c618  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x09    9    0x00  0x0b5f  0x0035  0x0b24.337bf3d9  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x0a    9    0x00  0x0b64  0x004f  0x0b25.7391ee5f  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x0b    9    0x00  0x0b64  0x0040  0x0b24.335c7bd7  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x0c    9    0x00  0x0b65  0x0002  0x0b25.7391e929  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x0d    9    0x00  0x0b4f  0x0033  0x0b24.a6a2caa5  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x0e    9    0x00  0x0b65  0x0008  0x0b23.ff22c616  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x0f    9    0x00  0x0b61  0x0038  0x0b26.6389e195  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x10    9    0x00  0x0b4f  0x002a  0x0b24.bcff18b3  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x11    9    0x00  0x0b5c  0x0059  0x0b25.2dacaf69  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x12    9    0x00  0x0b65  0x0026  0x0b25.2dacb0a8  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x13    9    0x00  0x0b66  0x0021  0x0b24.a6a2caaa  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x14    9    0x00  0x0b62  0x0009  0x0b24.337bf3d7  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x15    9    0x00  0x0b63  0x0031  0x0b25.1b4e13ba  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x16    9    0x00  0x0b66  0x003b  0x0b25.2dacee5d  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x17    9    0x00  0x0b63  0x0034  0x0b26.6389e199  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x18    9    0x00  0x0b5d  0x002f  0x0b24.bcff18af  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x19    9    0x00  0x0b5b  0x004d  0x0b24.d60f78e3  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x1a    9    0x00  0x0b60  0x005b  0x0b25.1b4e13be  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x1b    9    0x00  0x0b62  0x003e  0x0b23.ccb9ebac  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x1c    9    0x00  0x0b65  0x000a  0x0b25.7391ee5c  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x1d    9    0x00  0x0b64  0x002c  0x0b24.4e05f2b1  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x1e    9    0x00  0x0b64  0x0045  0x0b24.33255ae9  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x1f    9    0x00  0x0b64  0x0015  0x0b25.1b4e13b5  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x20    9    0x00  0x0b63  0x0050  0x0b24.335c79a4  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x21    9    0x00  0x0b5d  0x0001  0x0b24.a6a2caf0  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x22    9    0x00  0x0b65  0x000f  0x0b26.4393eb20  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x23    9    0x00  0x0b62  0x0042  0x0b24.337bf3d2  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x24    9    0x00  0x0b65  0x003c  0x0b24.a6a2d137  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x25    9    0x00  0x0b62  0x0020  0x0b24.335c795d  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x26    9    0x00  0x0b63  0x0052  0x0b25.2dacee48  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x27    9    0x00  0x0b4e  0x003a  0x0b25.7391ee58  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x28    9    0x00  0x0b65  0x0049  0x0b25.2dacb089  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x29    9    0x00  0x0b61  0x0030  0x0b24.bcff18bb  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x2a    9    0x00  0x0b65  0x0057  0x0b24.bcff18b5  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x2b    9    0x00  0x0b64  0x0054  0x0b25.2dacee55  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x2c    9    0x00  0x0b61  0x000d  0x0b24.4e05f2b3  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x2d    9    0x00  0x0b64  0x000e  0x0b23.ff22c611  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x2e    9    0x00  0x0b65  0x0053  0x0b25.7391e78a  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x2f    9    0x00  0x0b66  0x0010  0x0b24.bcff18b1  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x30    9    0x00  0x0b63  0x0019  0x0b24.d60f78e1  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x31    9    0x00  0x0b65  0x001a  0x0b25.1b4e13bc  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x32    9    0x00  0x0b65  0x0037  0x0b24.a6a2d369  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x33    9    0x00  0x0b4f  0x0013  0x0b24.a6a2caa7  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x34    9    0x00  0x0b63  0x0043  0x0b26.6389e19b  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x35    9    0x00  0x0b65  0x0046  0x0b24.337bf409  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x36    9    0x00  0x0b52  0x0061  0x0b25.7391eda2  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x37    9    0x00  0x0b64  0x0018  0x0b24.bcff18ad  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x38    9    0x00  0x0b65  0x0017  0x0b26.6389e197  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x39    9    0x00  0x0b62  0x0006  0x0b24.335c7947  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x3a    9    0x00  0x0b64  0x001c  0x0b25.7391ee5a  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x3b    9    0x00  0x0b4d  0x002e  0x0b25.7391e730  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x3c    9    0x00  0x0b65  0x0032  0x0b24.a6a2d144  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x3d    9    0x00  0x0b65  0x0016  0x0b25.2dacee59  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x3e    9    0x00  0x0b63  0x002d  0x0b23.ff22c60b  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x3f    9    0x00  0x0b63  0x005f  0x0b24.335c7b57  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x40    9    0x00  0x0b65  0x0044  0x0b24.335c7bd9  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x41    9    0x00  0x0b65  0x0029  0x0b24.bcff18b9  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x42    9    0x00  0x0b61  0x0014  0x0b24.337bf3d4  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x43    9    0x00  0x0b5b  0xffff  0x0b26.6389e19d  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x44    9    0x00  0x0b4c  0x004b  0x0b24.335c7bdb  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x45    9    0x00  0x0b61  0x0039  0x0b24.335c7945  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x46    9    0x00  0x0b65  0x005a  0x0b24.337bf421  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x47    9    0x00  0x0b65  0x0027  0x0b25.7391eda8  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x48    9    0x00  0x0b62  0x004e  0x0b24.335c7953  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x49    9    0x00  0x0b63  0x0012  0x0b25.2dacb09f  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x4a    9    0x00  0x0b63  0x0023  0x0b24.335c7bf8  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x4b    9    0x00  0x0b5b  0x004a  0x0b24.335c7bf3  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x4c    9    0x00  0x0b63  0x003f  0x0b24.335c7b55  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x4d    9    0x00  0x0b61  0x001f  0x0b25.1b4e13b3  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x4e    9    0x00  0x0b5a  0x0025  0x0b24.335c795b  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x4f    9    0x00  0x0b5f  0x005c  0x0b25.8ff588bb  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x50    9    0x00  0x0b63  0x004c  0x0b24.335c79a7  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x51    9    0x00  0x0b64  0x005d  0x0b24.0c84cbf4  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x52    9    0x00  0x0b65  0x002b  0x0b25.2dacee49  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x53    9    0x00  0x0b64  0x000c  0x0b25.7391e927  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x54    9    0x00  0x0b63  0x003d  0x0b25.2dacee56  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x55    9    0x00  0x0b64  0x0005  0x0b26.4393eada  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x56    9    0x00  0x0b64  0x0055  0x0b26.4393ead3  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x57    9    0x00  0x0b60  0x0041  0x0b24.bcff18b7  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x58    9    0x00  0x0b65  0x0060  0x0b24.335c794f  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x59    9    0x00  0x0b60  0x0028  0x0b25.2dacaf74  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x5a    9    0x00  0x0b61  0x0003  0x0b24.337bf499  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x5b    9    0x00  0x0b62  0x0000  0x0b25.1b4e13c0  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x5c    9    0x00  0x0b62  0x0056  0x0b25.950a6e4b  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x5d    9    0x00  0x0b63  0x001e  0x0b24.33255ae7  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x5e    9    0x00  0x0b5f  0x0004  0x0b23.ff22c635  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x5f    9    0x00  0x0b64  0x000b  0x0b24.335c7bd5  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x60    9    0x00  0x0b61  0x0048  0x0b24.335c7950  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x61    9    0x00  0x0b65  0x0047  0x0b25.7391eda6  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
KQRCMT: Write failed with error=600 po=0xa47e092c0 cid=3
diagnostics : cid=3 hash=35e74caf flag=2a
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

到这里我们基本上明白了报错的file 1 block 179020是由FREE BLOCK POOL分配出来的,现在解决给问题的思路就是直接使用bbed分配一个新块即可.

ORA-600 6711
数据库无法正常open报ORA 600 6711错误

Thu Jul  14 04:04:28 2016
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 1 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 2, block 3
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: /data/amdu/redo/DATA_EC_260.f
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 2, block 5, scn 12269633687653
 0 data blocks read, 0 data blocks written, 1 redo k-bytes read
Thu Jul  14 04:04:29 2016
Thread 1 advanced to log sequence 3 (thread open)
Thread 1 opened at log sequence 3
  Current log# 3 seq# 3 mem# 0: /data/amdu/redo/DATA_EC_263.f
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
After successful startup of the database, please remove
the parameters _allow_error_simulation and _smu_debug_mode
and restart the database
Thu Jul  14 04:04:29 2016
SMON: enabling cache recovery
Undo initialization finished serial:0 start:270626334 end:270626544 diff:210 (2 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Corrected file 19 plugged in read-only status in control file
Corrected file 81 plugged in read-only status in control file
Corrected file 88 plugged in read-only status in control file
Corrected file 93 plugged in read-only status in control file
Corrected file 128 plugged in read-only status in control file
Corrected file 130 plugged in read-only status in control file
Corrected file 131 plugged in read-only status in control file
Corrected file 163 plugged in read-only status in control file
Corrected file 181 plugged in read-only status in control file
Corrected file 184 plugged in read-only status in control file
Corrected file 186 plugged in read-only status in control file
Corrected file 191 plugged in read-only status in control file
Corrected file 214 plugged in read-only status in control file
Corrected file 220 plugged in read-only status in control file
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
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE &lt;tablespace_name&gt; ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Updating character set in controlfile to ZHS16GBK
WARNING: event 8105 is set. This event disables failed
         online index [re]build cleanup
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Jul  14 04:04:30 2016
QMNC started with pid=57, OS id=3549 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_3401.trc  (incident=1440450):
ORA-00600: internal error code, arguments: [6711], [4293062], [1], [4318348], [0], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_1440450/xifenfei_ora_3401_i1440450.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Error 600 in kwqmnpartition(), aborting txn 
Thu Jul  14 04:04:32 2016
Dumping diagnostic data in directory=[cdmp_20801214040432], requested by (instance=1, osid=3401), summary=[incident=1440450].
Thu Jul  14 04:04:32 2016
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_mmon_3329.trc  (incident=1440178):
ORA-00600: internal error code, arguments: [6711], [4293062], [1], [4318348], [0], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_1440178/xifenfei_mmon_3329_i1440178.trc
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_3401.trc  (incident=1440451):
ORA-00600: internal error code, arguments: [6711], [4293062], [1], [4318348], [0], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_1440451/xifenfei_ora_3401_i1440451.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_mmon_3329.trc  (incident=1440179):
ORA-00600: internal error code, arguments: [6711], [4293062], [1], [4318348], [0], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_1440179/xifenfei_mmon_3329_i1440179.trc
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 open...

数据库正常open失败,但是可以upgrade启动.根据对trace文件的分析,定位到问题在HISTGRM$表上面,进一步分析该表结构为

CREATE TABLE SYS.HISTGRM$
(
  OBJ#      NUMBER,
  COL#      NUMBER,
  ROW#      NUMBER,
  BUCKET    NUMBER,
  ENDPOINT  NUMBER,
  INTCOL#   NUMBER,
  EPVALUE   VARCHAR2(1000 BYTE),
  SPARE1    NUMBER,
  SPARE2    NUMBER
)
CLUSTER SYS.C_OBJ#_INTCOL#(OBJ#, INTCOL#);

这个和ORA-600 6711错误相匹配了

ERROR:
ORA-600 [6711] [a] [b] 1 [d]
VERSIONS:
versions 6.0 to 12.1
DESCRIPTION:
This error is generated when we find more blocks on a cluster key
chain than are supposed to be there.
Usually this indicates that the chain contains a loop within itself. We
cannot have more than 65535 blocks in a chain.
ARGUMENTS:
Arg [a] beginning DBA
Arg [b] table slot number (in table index)
Arg {c} dba of key next in chain
Arg [d] row slot of key next in chain

需要处理该错误,也就是需要处理CLUSTER SYS.C_OBJ#_INTCOL#,这个可以通过重建来实现,但是当重建之时发生

ORA-00600: internal error code, arguments: [kkoipt:invalid aptyp], [0], [0], [], [], [], [], [], [], [], [], []
ORA-08102: index key not found, obj# 39, file 1, block 1374829 (2)

这里错误比较明显obj#=39为obj$的i_obj4的index的记录和表不匹配,导致任何ddl无法执行,因此如果要处理C_OBJ#_INTCOL#就必须要先处理i_obj4的问题.通过一些技巧重建i_obj4,然后重建C_OBJ#_INTCOL#,数据库终于可以正常打开.由于大量数据字典不一致,exp/expdp导出依旧有问题,通过dblink直接拉数据到新库,完成本次恢复
补充说明:1. 在这个库的恢复过程中,我们还使用了大量的event和隐含参数,因为比较常规而且不涉及核心环节,因为未列举出来;2. 由于当时操作记录未能够保留日志因此相关操作步骤无法贴出来,本文只能提供恢复处理思路
再次提醒各位数据库做好备份,做好巡检工作,哪怕是强大的Oracle exadata也禁不起无备份折腾,数据重于一切

发表在 非常规恢复 | 标签为 , , , , | 留下评论

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

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

Proactive Bundle Patch

 Bundle Patch(Windows 32bit & 64bit)

 JUL2016

 23054246 (12.1.0.2.160719)

 23273629 (12.1.0.2.160719)

 23273686 (12.1.0.2.160719)

 23530387(12.1.0.2.160719)

 APR2016

 22291127 (12.1.0.2.160419)

 22646084 (12.1.0.2.160419)

 22899531

 22809813(12.1.0.2.160419)

 JAN2016

 21948354 (12.1.0.2.160119)

 22191349 (12.1.0.2.160119)

 22243551

 22310559(12.1.0.2.160119)

 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 (Windows64bit)

 Bundle Patch (Windows32bit)

 JUL2016

 23054354 (12.1.0.1.160719)

 i23273935 / k23273958 (12.1.0.1.160719)

23530410 (12.1.0.1.160719)

 APR2016

 22291141 (12.1.0.1.160419)

 i22654153 / k22654166(12.1.0.1.160419)

22617408 (12.1.0.1.160419)

 JAN2016

 21951844 (12.1.0.1.160119)

 j22191492 / k22191511(12.1.0.1.160119)

22494866 (12.1.0.2.160119)

 OCT2015

 21352619 (12.1.0.1.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)

 JUL2016

 23054359 (11.2.0.4.160719)

 23177648 (11.2.0.4.160719)

 23274134 (11.2.0.4.160719)

 23530402 (11.2.0.4.160719)

 APR2016

 22502456 (11.2.0.4.160419)

 22502493 (11.2.0.4.160419)

 22646198 (11.2.0.4.160419)

 22839608 (11.2.0.4.160419)

 JAN2016

 21948347 (11.2.0.4.160119)

 21972320 (11.2.0.4.160119)

 22191577 (11.2.0.4.160119)

 22310544 (11.2.0.4.160119)

 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 (Windows64bit)

 Bundle Patch(Windows32bit)

 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 (Windows64bit)

 Bundle Patch(Windows32bit)

 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 (Windows64bit)

 Bundle Patch (Windows32bit)

 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 (Windows64bit)

 Bundle Patch (Windows32bit)

JUL2015

 20761024 (11.1.0.7.24)

 20803573

 21104030

 21104029

APR2015

 20299012 (11.1.0.7.23)

 20299020

 20420391

 20420390

JAN2015

 19769499 (11.1.0.7.22)

 19854433

 20126915

 20126914

OCT2014

 19152553 (11.1.0.7.21)

 19274522

 19609034

 19609032

JUL2014

 18522513 (11.1.0.7.20)

 18681875

 18944208

 18944207

APR2014

 18031726 (11.1.0.7.19)

 18139703

 18372258

 18372257

JAN2014

 17465583 (11.1.0.7.18)

 17551415

 17906936

 17906935

OCT2013

 17082366 (11.1.0.7.17)

 17082374

 17363760

 17363759

JUL2013

 16619896 (11.1.0.7.16)

 16742110

 16803788

 16803787

APR2013

 16056268 (11.1.0.7.15)

 16308394

 16345862

 16345861

JAN2013

 14739378 (11.1.0.7.14)

 14841452

 15848067

 15848066

OCT2012

 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 (Windows64bit)

 Bundle Patch (Windows32bit)

 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 (Windows64bit)

 Bundle Patch (Windows32bit)

 Bundle Patch(WindowsItanium)

 bJUL2015

 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

 OCT2014

 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

 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 (Windows32bit)

 Bundle Patch (Windows64bit)

 Bundle Patch(WindowsItanium)

 gJUL2013

 16619897 (10.2.0.4.17)

 16742253

 N/A

 N/A

 N/A

 gAPR2013

 16056269 (10.2.0.4.16)

 16270931

 N/A

 N/A

 N/A

 gJAN2013

 14736542 (10.2.0.4.15)

 14841471

 N/A

 N/A

 N/A

gOCT2012

 14275630 (10.2.0.4.14)

 14390410

 N/A

 N/A

 N/A

gJUL2012

 13923851 (10.2.0.4.13)

 14038814

 N/A

 N/A

 N/A

 aAPR2012

 12879933 (10.2.0.4.12)

 12879926

 13928775

 13928776

 N/A

 JAN2012

 12879929 (10.2.0.4.11)

 12879912

 b13654060

 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 (Windows32bit)

 Bundle Patch (WindowsItanium)

 Bundle Patch (Windows64bit)

 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 (Windows32bit)

  Bundle Patch (Windows64bit)

 Bundle Patch (WindowsItanium)

 iJAN2009

 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 (Windows32bit)

 Bundle Patch (Windows64bit)

 Bundle Patch (WindowsItanium)

 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 (Windows32bit)

  Bundle Patch (WindowsItanium)

 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 (Windows32bit)

 Bundle Patch (WindowsItanium)

 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 (Windows32bit)

 Bundle Patch (WindowsItanium)

 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 (Windows32bit)

 Bundle Patch (WindowsItanium)

 APR2005

 4193293

 4181849

 4213305

 JUL2005

 4400766

 4388944

 4388948

 JAN2005

 4003051

 4104364

 4083038

9.2.0.8

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

 Bundle Patch (WindowsItanium)

 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 (Windows32bit)

 Bundle Patch (WindowsItanium)

 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 (Windows32bit)

 Bundle Patch (WindowsItanium)

 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 (Windows32bit)

 Bundle Patch (WindowsItanium)

 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 (Windows32bit)

 Bundle Patch (WindowsItanium)

 JAN2005

 4002994

 4104369

 4083202

8.1.7.4

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

 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

 JUL2016

 23177536 (12.1.0.2.160719)

 23515290 (12.1.0.2.160719)

 23615289 (12.1.0.2.160719)

 23615308 (12.1.0.2.160719)

 APR2016

 22674709 (12.1.0.2.160419)

 22839633 (12.1.0.2.160419)

 22738582 (12.1.0.2.160419)

 22738641 (12.1.0.2.160419)

 JAN2016

 22139226 (12.1.0.2.160119)

 22311086 (12.1.0.2.160119)

 22191659 (12.1.0.2.160119)

 22191676 (12.1.0.2.160119)

 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

 JUL2016 (12.1.0.1.160719)

 23177541

 23515285

 23615355

 23615368

 Included in OJVM PSU

 APR2016 (12.1.0.1.160419)

 22674703

 22839627

 22738678

 22738715

 JAN2016 (12.1.0.1.160119)

 22139235

 22311072

 22191711

 22191721

 OCT2015 (12.1.0.1.5)

 21555669

 21788365

 21744318

 21744328

 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

 JUL2016 (11.2.0.4.160719)

 23177551

 23515277

 23615392

 23615381

 23615403

 Included in OJVM PSU

 APR2016 (11.2.0.4.160419)

 22674697

 22839614

 22738777

 22738732

 22738793

 JAN2016 (11.2.0.4.160119)

 22139245

 22311053

 22378146

 22378121

 22378167

 OCT2015 (11.2.0.4.5)

 21555791

 21788344

 21744343

 21744335

 21744348

 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 | 留下评论

ORA-28040: No matching authentication protocol

电脑上面安装了三个版本的数据库10.2.0.3,11.2.0.1,12.1.0.2版本,使用他们分别尝试连接另外一个12.2.0.3的环境数据库发现只有12.1的版本客户端可以连接到12.2上面,其他版本报ORA-28040错误
分别测试连接,报ORA-28040错误

C:\Users\XIFENFEI>sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on 星期三 7月 20 00:03:01 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

SQL>
SQL>

C:\Users\XIFENFEI>D:\app\FAL\product\11.2.0\dbhome_1\bin\sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 7月 20 00:10:33 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-28040: No matching authentication protocol


C:\Users\XIFENFEI>D:\app\product\10.2.0\db_1\bin\sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on 星期三 7月 20 00:09:30 2016

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-28040: 没有匹配的验证协议


请输入用户名:

ORA-28040错误说明

28040, 0000, "No matching authentication protocol"
// *Cause:  There was no acceptable authentication protocol for 
//          either client or server.
// *Action: The administrator should set the values of the
//          SQLNET.ALLOWED_LOGON_VERSION_SERVER and 
//          SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters, on both the
//          client and on the server, to values that match the minimum 
//          version software supported in the system. 
//          This error is also raised when the client is authenticating to 
//          a user account which was created without a verifier suitable for
//          the client software version. In this situation, that account's 
//          password must be reset, in order for the required verifier to
//          be generated and allow authentication to proceed successfully.

解决方法
在服务端的sqlnet.ora文件中加入上如下信息,然后重启监听

[oracle@ora1221 admin]$ vi sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

[oracle@ora1221 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 12.2.0.0.3 - Production on 17-JUN-2016 06:36:13

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@ora1221 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.0.3 - Production on 17-JUN-2016 06:36:17

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.2.0/db_2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.0.3 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/ora1221/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora1221)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.0.3 - Production
Start Date                17-JUN-2016 06:36:17
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora1221/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora1221)(PORT=1521)))
The listener supports no services
The command completed successfully

sqlnet中参数说明
SQLNET.ALLOWED_LOGON_VERSION_SERVER 是服务端参数对于jdbc和oci都生效,该参数不是只具体数据库版本,而是指授权协议的版本
SQLNET.ALLOWED_LOGON_VERSION_CLIENT 是指作为客户端连接其他实例的时候生效,也是只授权协议版本,而且该参数只对oci生效,jdbc 需要通过在代码中类似实现

OracleDataSource ods = new OracleDataSource();
ods.setURL(jdbcURL);
ods.setUser("scott");
ods.setPassword("tiger");
Properties props = new Properties();
props.put("oracle.jdbc.allowedLogonVersion", 12);
ods.setConnectionProperties(props);
Connection con = ods.getConnection();

上述两个参数可以填写值
12a for Oracle Database 12c release 12.1.0.2 or later authentication protocols (strongest protection)
12 for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended)
11 for Oracle Database 11g authentication protocols (default)
10 for Oracle Database 10g authentication protocols
9 for Oracle9i Database authentication protocol
8 for Oracle8i Database authentication protocol
allowed_logon_version_server


具体描述请见:http://docs.oracle.com/database/121/NETRF/sqlnet.htm#NETRF2010

再次测试连接

C:\Users\XIFENFEI>D:\app\FAL\product\11.2.0\dbhome_1\bin\sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 7月 20 00:20:21 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production 断开

C:\Users\XIFENFEI>D:\app\product\10.2.0\db_1\bin\sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on 星期三 7月 20 00:20:28 2016

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

SQL>

C:\Users\XIFENFEI>sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on 星期三 7月 20 00:20:55 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

该问题在jdbc中也表现明显,建议参考Starting With Oracle JDBC Drivers (文档 ID 401934.1)和Client / Server Interoperability Support Matrix for Different Oracle Versions (文档 ID 207303.1)选择完全兼容性的客户端和jdbc版本,另外可以关注相关文章:
ORA-28040 and SQLNET.ALLOWED_LOGON_VERSION_CLIENT for JDBC Thin Clients (文档 ID 2000339.1)
ORA-28040 Using JDBC Connection to 12c Database (文档 ID 2111118.1)
JDBC Version 10.2.0.4 Produces ORA-28040 Connecting To Oracle 12c (12.1.0.2) Database (文档 ID 2023160.1)
ORA-28040 and SQLNET.ALLOWED_LOGON_VERSION_CLIENT for JDBC Thin Clients (文档 ID 2000339.1)

发表在 ORACLE 12C | 标签为 | 留下评论