为了方便大家更容易的查看相关asm内容,今天(2016年7月28日)对asm的相关文章进行了汇总整理.如果有asm相关的其他问题,可以通过手机(17813235971)或者QQ(107644445)交流
远程访问ASM
ASMCMD常用命令
ASM简单管理(1)
ASM简单管理(2)
找回ASM中数据文件
bbed修改ASM中数据
ASM迁移至文件系统
普通库迁移至ASM存储
使用dd复制asm中文件
配置Oracle ASM磁盘
ASM中磁盘组权限设置
监控asm disk磁盘性能
create spfile to asm
pvid=yes导致asm无法mount—ASM恢复案例
asm数据文件迁移(os–>asm)
asm数据文件迁移(asm–>asm)
asm数据文件迁移(asm–>os)
通过ftp/http拷贝asm中文件
ASM DISK HEADER 备份与恢复
手工修复ASM DISK HEADER 异常
asm disk header 彻底损坏恢复—ASM恢复案例
ASM未正常启动,使用dd找回数据文件
ORACLE 12C ASM 新特性：共享密码文件
asm备份元数据之md_backup和md_restore
分区无法识别导致asm diskgroup无法mount—ASM恢复案例
使用losetup实现linux普通文件做asm disk
asm 加磁盘导致磁盘组损坏恢复—ASM恢复案例
asm磁盘头全部损坏数据0丢失恢复—ASM恢复案例
Oracle异常恢复前备份保护现场建议—ASM环境
多cpu环境中运行root.sh失败,asm报ORA-04031
asmlib异常报ORA-00600[kfklLibFetchNext00]
存储精简卷导致asm磁盘组异常
因asm sga_target设置不当导致11gr2 rac无法正常启动
asm disk误设置pvid导致asm diskgroup无法mount恢复—ASM恢复案例
oracle asm disk格式化恢复—格式化为ntfs文件系统—ASM恢复案例
aoracle asm disk格式化恢复—格式化为ext4文件系统—ASM恢复案例
使用_asm_allow_only_raw_disks实现普通文件做asm disk
ORACLE 12C RAC修改ocr/votedisk/asm spfile所在磁盘组名称
使用asm disk header 自动备份信息恢复异常asm disk header
分享oracleasm createdisk重新创建asm disk后数据0丢失恢复案例—ASM恢复案例
asm磁盘组操作不当导致数据文件丢失恢复—ASM恢复案例
ADHU(ASM Disk Header Utility)—asm disk header备份恢复工具
How to Get the Contents of an Spfile on ASM when ASM/GRID is down
ORA-15042: ASM disk “N” is missing from group number “M” 故障恢复—ASM恢复案例
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
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.使用上述方法恢复数据库之后，建议通过逻辑方式导出导入重建数据库
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 "_SYSSMU83_1078760807$" 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 "_SYSSMU83_1078760807$" 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="_SYSSMU1_2856534670$" 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.^...&...]
94E07A010 0000F022 004E0000 00000B5B 1D1D0F4C [".....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&...]
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 <tablespace_name> 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] [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) |
|
|
11.2.0.4 (11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
11.2.0.3 (11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
11.2.0.2 (11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
11.1.0.7 (11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.2.0.5 (10.2.0.5 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
d10.2.0.4 (10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
e10.2.0.3 (10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.2.0.2 (10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.1.0.5 (10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.1.0.4 (10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.1.0.3 (10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.8 (9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.7 (9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.6 (9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.5 (ORACLE 9I DATABASE SERVER RELEASE 2 – PATCH SET 4 VERSION 9.2.0.5.0) |
|
|
9.2.0.4 (9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.3 (9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.2 (9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.0.1.5 (9.0.1.5 PATCHSET) |
|
|
9.0.1.4 (9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.0.1.3 (9.0.1.3. PATCH SET FOR ORACLE DATA SERVER) |
|
|
8.1.7.4 (8.1.7.4 PATCH SET FOR ORACLE DATA SERVER) |
|
|
8.1.7.3 (8.1.7.3 PATCH SET FOR ORACLE DATA SERVER) |
|
|
8.1.7.2 (8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER) |
|
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) |
22809813(12.1.0.2.160419) |
|
|
JAN2016 |
21948354 (12.1.0.2.160119) |
22191349 (12.1.0.2.160119) |
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) |
23530410 (12.1.0.1.160719) |
||
|
APR2016 |
22291141 (12.1.0.1.160419) |
22617408 (12.1.0.1.160419) |
||
|
JAN2016 |
21951844 (12.1.0.1.160119) |
22494866 (12.1.0.2.160119) |
||
|
OCT2015 |
21352619 (12.1.0.1.9) |
21744907 (12.1.0.1.21) |
||
|
JUL2015 |
20831107 (12.1.0.1.8) |
21076681 (12.1.0.1.20) |
||
|
APR2015 |
20299016 (12.1.0.1.7) |
20558101 (12.1.0.1.18) |
||
|
JAN2015 |
19769486 (12.1.0.1.6) |
20160748 (12.1.0.1.16) |
||
|
OCT2014 |
19121550 (12.1.0.1.5) |
19542943 (12.1.0.1.14) |
||
|
JUL2014 |
18522516 (12.1.0.1.4) |
19062327 (12.1.0.1.11) |
||
|
APR2014 |
18031528 (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) |
21523375 (11.2.0.4.8) |
21821802 (11.2.0.4.20) |
|
|
JUL2015 |
20760982 (11.2.0.4.7) |
20996923 (11.2.0.4.7) |
21469106 (11.2.0.4.18) |
|
|
APR2015 |
20299013 (11.2.0.4.6) |
20485808 (11.2.0.4.6) |
20544696 (11.2.0.4.15) |
|
|
JAN2015 |
19769489 (11.2.0.4.5) |
19955028 (11.2.0.4.5) |
20127071 (11.2.0.4.12) |
|
|
OCT2014 |
19121551 (11.2.0.4.4) |
19380115 (11.2.0.4.4) |
19651773 (11.2.0.4.10) |
|
|
JUL2014 |
18522509 (11.2.0.4.3) |
18706472 (11.2.0.4.3) |
18842982 (11.2.0.4.7) |
|
|
APR2014 |
18031668 (11.2.0.4.2) |
18139609 (11.2.0.4.2) |
18296644 (11.2.0.4.4) |
|
|
JAN2014 |
17478514 (11.2.0.4.1) |
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) |
20996944 (11.2.0.3.15) |
|||
|
APR2015 |
20299017 (11.2.0.3.14) |
20485830 (11.2.0.3.14) |
|||
|
JAN2015 |
19769496 (11.2.0.3.13) |
19971343 (11.2.0.3.13) |
|||
|
OCT2014 |
19121548 (11.2.0.3.12) |
19440385 (11.2.0.3.12) |
|||
|
JUL2014 |
18522512 (11.2.0.3.11) |
18706488 (11.2.0.3.11) |
|||
|
APR2014 |
18031683 (11.2.0.3.10) |
18139678 (11.2.0.3.10) |
|||
|
JAN2014 |
17540582 (11.2.0.3.9) |
17735354 (11.2.0.3.9) |
|||
|
OCT2013 |
16902043 (11.2.0.3.8) |
17272731 (11.2.0.3.8) |
|||
|
JUL2013 |
16619892 (11.2.0.3.7) |
16742216 (11.2.0.3.7) |
|||
|
APR2013 |
16056266 (11.2.0.3.6) |
16083653 (11.2.0.3.6) |
|||
|
JAN2013 |
14727310 (11.2.0.3.5) |
14727347 (11.2.0.3.5) |
|||
|
OCT2012 |
14275605 (11.2.0.3.4) |
14275572 (11.2.0.3.4) |
|||
|
JUL2012 |
13923374 (11.2.0.3.3) |
13919095 (11.2.0.3.3) |
|||
|
APR2012 |
13696216 (11.2.0.3.2) |
13696251 (11.2.0.3.2) |
|||
|
JAN2012 |
13343438 (11.2.0.3.1) |
13348650 (11.2.0.3.1) |
|||
|
11.2.0.2 |
|||||
|
Description |
PSU |
SPU(CPU) |
GI PSU |
Bundle Patch (Windows64bit) |
Bundle Patch(Windows32bit) |
|
aOCT2013 |
17082367 (11.2.0.2.12) |
17272753 (11.2.0.2.12) |
|||
|
JUL2013 |
16619893 (11.2.0.2.11) |
16742320 (11.2.0.2.11) |
|||
|
APR2013 |
16056267 (11.2.0.2.10) |
16166868 (11.2.0.2.10) |
|||
|
JAN2013 |
14727315 (11.2.0.2.9) |
14841385 (11.2.0.2.9) |
|||
|
OCT2012 |
14275621 (11.2.0.2.8) |
14390437 (11.2.0.2.8) |
|||
|
JUL2012 |
13923804 (11.2.0.2.7) |
14192201 (11.2.0.2.7) |
|||
|
APR2012 |
13696224 (11.2.0.2.6) |
13696242 (11.2.0.2.6) |
|||
|
JAN2012 |
13343424 (11.2.0.2.5) |
13653086 (11.2.0.2.5) |
|||
|
OCT2011 |
12827726 (11.2.0.2.4) |
12827731 (11.2.0.2.4) |
|||
|
JUL2011 |
12419331 (11.2.0.2.3) |
12419353 (11.2.0.2.3) |
|||
|
APR2011 |
11724916 (11.2.0.2.2) |
12311357 (11.2.0.2.2) |
|||
|
JAN2011 |
10248523 (11.2.0.2.1) |
N/A |
N/A |
||
|
|
||||
|
11.2.0.1 |
||||
|
Description |
PSU |
CPU |
Bundle Patch (Windows64bit) |
Bundle Patch (Windows32bit) |
|
aJUL2011 |
12419378 (11.2.0.1.6) |
|||
|
APR2011 |
11724930 (11.2.0.1.5) |
|||
|
JAN2011 |
10248516 (11.2.0.1.4) |
|||
|
OCT2010 |
9952216 (11.2.0.1.3) |
|||
|
JUL2010 |
9654983 (11.2.0.1.2) |
|||
|
APR2010 |
9352237 (11.2.0.1.1) |
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) |
|||
|
APR2015 |
20299012 (11.1.0.7.23) |
|||
|
JAN2015 |
19769499 (11.1.0.7.22) |
|||
|
OCT2014 |
19152553 (11.1.0.7.21) |
|||
|
JUL2014 |
18522513 (11.1.0.7.20) |
|||
|
APR2014 |
18031726 (11.1.0.7.19) |
|||
|
JAN2014 |
17465583 (11.1.0.7.18) |
|||
|
OCT2013 |
17082366 (11.1.0.7.17) |
|||
|
JUL2013 |
16619896 (11.1.0.7.16) |
|||
|
APR2013 |
16056268 (11.1.0.7.15) |
|||
|
JAN2013 |
14739378 (11.1.0.7.14) |
|||
|
OCT2012 |
14275623 (11.1.0.7.13) |
|||
|
JUL2012 |
13923474 (11.1.0.7.12) |
|||
|
APR2012 |
13621679 (11.1.0.7.11) |
|||
|
JAN2012 |
13343461 (11.1.0.7.10) |
|||
|
OCT2011 |
12827740 (11.1.0.7.9) |
|||
|
JUL2011 |
12419384 (11.1.0.7.8) |
|||
|
APR2011 |
11724936 (11.1.0.7.7) |
|||
|
JAN2011 |
10248531 (11.1.0.7.6) |
|||
|
OCT2010 |
9952228 (11.1.0.7.5) |
|||
|
JUL2010 |
9654987 (11.1.0.7.4) |
|||
|
APR2010 |
9352179 (11.1.0.7.3) |
|||
|
JAN2010 |
9209238 (11.1.0.7.2) |
|||
|
OCT2009 |
8833297 (11.1.0.7.1) |
|||
|
JUL2009 |
N/A |
|||
|
APR2009 |
N/A |
|||
|
|
|||
|
11.1.0.6 |
|||
|
Description |
CPU |
Bundle Patch (Windows64bit) |
Bundle Patch (Windows32bit) |
|
aJUL2009 |
|||
|
APR2009 |
|||
|
JAN2009 |
|||
|
OCT2008 |
|||
|
JUL2008 |
|||
|
APR2008 |
|||
|
|
|||||
|
10.2.0.5 |
|||||
|
Description |
PSU |
SPU(CPU) |
Bundle Patch (Windows64bit) |
Bundle Patch (Windows32bit) |
Bundle Patch(WindowsItanium) |
|
bJUL2015 |
20299014 (10.2.0.5.19) |
N/A |
|||
|
APR2015 |
N/A |
N/A |
N/A |
N/A |
N/A |
|
JAN2015 |
19769505 (10.2.0.5.18) |
N/A |
|||
|
OCT2014 |
19274523 (10.2.0.5.17) |
N/A |
|||
|
JUL2014 |
18522511 (10.2.0.5.16) |
N/A |
|||
|
APR2014 |
18031728 (10.2.0.5.15) |
N/A |
|||
|
JAN2014 |
17465584 (10.2.0.5.14) |
N/A |
|||
|
OCT2014 |
17082365 (10.2.0.5.13) |
N/A |
N/A |
||
|
JUL2013 |
16619894 (10.2.0.5.12) |
||||
|
APR2013 |
16056270 (10.2.0.5.11) |
||||
|
JAN2013 |
14727319 (10.2.0.5.10) |
||||
|
OCT2012 |
14275629 (10.2.0.5.9) |
||||
|
JUL2012 |
13923855 (10.2.0.5.8) |
||||
|
APR2012 |
13632743 (10.2.0.5.7) |
||||
|
JAN2012 |
13343471 (10.2.0.5.6) |
N/A |
|||
|
OCT2011 |
12827745 (10.2.0.5.5) |
c12914913 |
N/A |
||
|
JUL2011 |
12419392 (10.2.0.5.4) |
N/A |
|||
|
APR2011 |
11724962 (10.2.0.5.3) |
N/A |
|||
|
JAN2011 |
10248542 (10.2.0.5.2) |
N/A |
|||
|
OCT2010 |
9952230 (10.2.0.5.1) |
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) |
N/A |
|||
|
JAN2012 |
12879929 (10.2.0.4.11) |
N/A |
N/A |
||
|
OCT2011 |
12827778 (10.2.0.4.10) |
||||
|
JUL2011 |
12419397 (10.2.0.4.9) |
||||
|
APR2011 |
11724977 (10.2.0.4.8) |
||||
|
JAN2011 |
10248636 (10.2.0.4.7) |
||||
|
OCT2010 |
9952234 (10.2.0.4.6) |
||||
|
JUL2010 |
9654991 (10.2.0.4.5) |
||||
|
APR2010 |
9352164 (10.2.0.4.4) |
||||
|
JAN2010 |
9119284 (10.2.0.4.3) |
||||
|
OCT2009 |
8833280 (10.2.0.4.2) |
||||
|
JUL2009 |
8576156 (10.2.0.4.1) |
||||
|
APR2009 |
N/A |
||||
|
JAN2009 |
N/A |
N/A |
|||
|
OCT2008 |
N/A |
N/A |
|||
|
JUL2008 |
N/A |
N/A |
|||
|
|
||||
|
10.2.0.3 |
||||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
Bundle Patch (Windows64bit) |
|
aJAN2009 |
||||
|
OCT2008 |
||||
|
JUL2008 |
||||
|
APR2008 |
||||
|
JAN2008 |
||||
|
OCT2007 |
||||
|
JUL2007 |
||||
|
APR2007 |
||||
|
JAN2007 |
||||
|
|
||||
|
10.2.0.2 |
||||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (Windows64bit) |
Bundle Patch (WindowsItanium) |
|
iJAN2009 |
N/A |
N/A |
N/A |
|
|
hOCT2008 |
N/A |
N/A |
N/A |
|
|
hJUL2008 |
N/A |
N/A |
N/A |
|
|
hAPR2008 |
N/A |
N/A |
N/A |
|
|
aJAN2008 |
N/A |
N/A |
N/A |
|
|
fOCT2007 |
||||
|
JUL2007 |
||||
|
APR2007 |
||||
|
JAN2007 |
||||
|
OCT2006 |
||||
|
JUL2006 |
||||
|
APR2006 |
||||
|
|
||||
|
10.2.0.1 |
||||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (Windows64bit) |
Bundle Patch (WindowsItanium) |
|
APR2007 |
N/A |
N/A |
N/A |
|
|
JAN2007 |
||||
|
OCT2006 |
||||
|
JUL2006 |
||||
|
APR2006 |
||||
|
JAN2006 |
||||
|
|
|||
|
10.1.0.5 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
|
JAN2012 |
|||
|
OCT2011 |
|||
|
JUL2011 |
|||
|
APR2011 |
|||
|
JAN2011 |
N/A |
N/A |
N/A |
|
OCT2010 |
|||
|
JUL2010 |
|||
|
APR2010 |
|||
|
JAN2010 |
|||
|
OCT2009 |
|||
|
JUL2009 |
|||
|
APR2009 |
|||
|
JAN2009 |
|||
|
OCT2008 |
|||
|
JUL2008 |
|||
|
APR2008 |
|||
|
JAN2008 |
|||
|
OCT2007 |
|||
|
JUL2007 |
|||
|
APR2007 |
|||
|
JAN2007 |
|||
|
OCT2006 |
|||
|
JUL2006 |
|||
|
APR2006 |
|||
|
JAN2006 |
|||
|
|
|||
|
10.1.0.4 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
|
APR2007 |
|||
|
JAN2007 |
|||
|
OCT2006 |
|||
|
JUL2006 |
|||
|
APR2006 |
|||
|
JAN2006 |
|||
|
OCT2005 |
|||
|
JUL2005 |
|||
|
APR2005 |
|||
|
|
|||
|
10.1.0.3 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
|
JAN2007 |
N/A |
N/A |
|
|
OCT2006 |
N/A |
N/A |
|
|
JUL2006 |
N/A |
N/A |
|
|
APR2006 |
N/A |
N/A |
|
|
JAN2006 |
|||
|
OCT2005 |
|||
|
JUL2005 |
|||
|
APR2005 |
|||
|
JAN2005 |
|||
|
|
|||
|
10.1.0.2 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
|
APR2005 |
|||
|
JUL2005 |
|||
|
JAN2005 |
|||
|
|
|||
|
9.2.0.8 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
|
JUL2010 |
|||
|
APR2010 |
N/A |
||
|
JAN2010 |
N/A |
||
|
OCT2009 |
|||
|
JUL2009 |
|||
|
APR2009 |
|||
|
JAN2009 |
|||
|
OCT2008 |
|||
|
JUL2008 |
|||
|
APR2008 |
|||
|
JAN2008 |
|||
|
OCT2007 |
|||
|
JUL2007 |
|||
|
APR2007 |
|||
|
JAN2007 |
N/A |
N/A |
N/A |
|
OCT2006 |
|||
|
|
|||
|
9.2.0.7 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
|
JUL2007 |
|||
|
APR2007 |
|||
|
JAN2007 |
|||
|
OCT2006 |
|||
|
JUL2006 |
|||
|
APR2006 |
|||
|
JAN2006 |
|||
|
OCT2005 |
|||
|
JUL2005 |
N/A |
N/A |
|
|
|
|||
|
9.2.0.6 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
|
OCT2006 |
|||
|
JUL2006 |
|||
|
APR2006 |
|||
|
JAN2006 |
|||
|
OCT2005 |
|||
|
JUL2005 |
|||
|
APR2005 |
|||
|
|
|||
|
9.2.0.5 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
|
OCT2006 |
N/A |
N/A |
|
|
JUL2006 |
N/A |
N/A |
|
|
APR2006 |
N/A |
N/A |
|
|
OCT2005 |
N/A |
N/A |
|
|
JUL2005 |
|||
|
APR2005 |
|||
|
JAN2005 |
|||
|
|
|||
|
9.2.0.4 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
|
JAN2005 |
|||
|
|
||
|
8.1.7.4 |
||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
|
JAN2007 |
||
|
OCT2006 |
||
|
JUL2006 |
||
|
APR2006 |
||
|
JAN2006 |
||
|
OCT2005 |
||
|
JUL2005 |
||
|
APR2005 |
||
|
JAN2005 |
||
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) |
||
|
JUL2015 |
21068507 (12.1.0.2.4) |
21153530 (12.1.0.2.3) |
||
|
APR2015 |
20415564 (12.1.0.2.3) |
20391199 (12.1.0.2.2) |
||
|
JAN2015 |
19877336 (12.1.0.2.2) |
20225938 (12.1.0.2.1) |
||
|
OCT2014 (12.1.0.2.1) |
|
|||
|
|
|||||
|
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) |
Included in OJVM PSU |
||||
|
APR2016 (12.1.0.1.160419) |
|||||
|
JAN2016 (12.1.0.1.160119) |
|||||
|
OCT2015 (12.1.0.1.5) |
|||||
|
JUL2015 (12.1.0.1.4) |
|||||
|
APR2015 (12.1.0.1.3) |
|||||
|
JAN2015 (12.1.0.1.2) |
|||||
|
OCT2014 (12.1.0.1.1) |
|||||
|
|
||||||
|
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) |
Included in OJVM PSU |
|||||
|
APR2016 (11.2.0.4.160419) |
||||||
|
JAN2016 (11.2.0.4.160119) |
||||||
|
OCT2015 (11.2.0.4.5) |
||||||
|
JUL2015 (11.2.0.4.4) |
||||||
|
APR2015 (11.2.0.4.3) |
||||||
|
JAN2015 (11.2.0.4.2) |
||||||
|
OCT2014 (11.2.0.4.1) |
||||||
|
|
||||||
|
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) |
Included in OJVM PSU |
|||||
|
APR2015 (11.2.0.3.3) |
||||||
|
JAN2015 (11.2.0.3.2) |
||||||
|
OCT2014 (11.2.0.3.1) |
||||||
|
|
||||||
|
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 |
N/A |
Included in OJVM PSU |
|||
|
APR2015 (11.1.0.7.3) |
N/A |
|||||
|
JAN2015 (11.1.0.7.2) |
N/A |
|||||
|
OCT2014 (11.1.0.7.1) |
N/A |
|||||
参考：Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (文档 ID 1454618.1)
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
具体描述请见: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 undo异常处理—root pdb undo异常
在12c pdb环境中如果root pdb的undo文件异常，数据库该如何恢复呢？这篇文章模拟undo丢失的情况下进行恢复
模拟环境
三个会话，其中第一个会话对pdb1中的表进行操作，并且有事务未提交,第二个会话对pdb2进行操作，也未提交事务；第三个会话直接abort库，模拟突然库异常，然后删除root pdb下面的undo文件
--会话1
[oracle@ora1221 oradata]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.0.3 Production on Thu Jun 16 22:24:20 2016
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size 8260048 bytes
Variable Size 671090224 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8515584 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
SQL> alter session set container=pdb1;
Session altered.
SQL> alter database open;
Database altered.
SQL> create user chf identified by oracle;
User created.
SQL> grant dba to chf;
Grant succeeded.
SQL> create table chf.t_xifenfei_p1 as
2 select * from dba_objects;
Table created.
SQL> insert into chf.t_xifenfei_p1
2 select * from dba_objects;
72427 rows created.
SQL> select count(*) from chf.t_xifenfei_p1;
COUNT(*)
----------
144853
--会话2
[oracle@ora1221 ~]$ ss
SQL*Plus: Release 12.2.0.0.3 Production on Thu Jun 16 22:34:01 2016
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production
SQL> alter session set container=pdb2;
Session altered.
SQL> alter database open;
Database altered.
SQL> create user chf identified by oracle;
User created.
SQL> grant dba to chf;
Grant succeeded.
SQL> create table chf.t_xifenfei_p2
2 as select * from dba_objects;
Table created.
SQL> delete from chf.t_xifenfei_p2;
72426 rows deleted.
SQL> select count(*) from chf.t_xifenfei_p2;
COUNT(*)
----------
0
--会话3
[oracle@ora1221 ~]$ ss
SQL*Plus: Release 12.2.0.0.3 Production on Thu Jun 16 22:36:16 2016
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production
SQL> shutdown abort
ORACLE instance shut down.
--删除cdb undo文件
[oracle@ora1221 orcl12c2]$ ls -ltr
total 2040912
drwxr-x---. 2 oracle oinstall 4096 Jun 16 18:26 pdbseed
drwxr-x---. 2 oracle oinstall 4096 Jun 16 18:27 pdb2
drwxr-x---. 2 oracle oinstall 4096 Jun 16 18:28 pdb1
-rw-r-----. 1 oracle oinstall 209715712 Jun 16 22:24 redo03.log
-rw-r-----. 1 oracle oinstall 5251072 Jun 16 22:24 users01.dbf
-rw-r-----. 1 oracle oinstall 34611200 Jun 16 22:25 temp01.dbf
-rw-r-----. 1 oracle oinstall 849354752 Jun 16 22:35 system01.dbf
-rw-r-----. 1 oracle oinstall 73408512 Jun 16 22:35 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 492838912 Jun 16 22:35 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 209715712 Jun 16 22:36 redo02.log
-rw-r-----. 1 oracle oinstall 209715712 Jun 16 22:36 redo01.log
-rw-r-----. 1 oracle oinstall 18726912 Jun 16 22:36 control02.ctl
-rw-r-----. 1 oracle oinstall 18726912 Jun 16 22:36 control01.ctl
[oracle@ora1221 orcl12c2]$ rm undotbs01.dbf
[oracle@ora1221 orcl12c2]$ ls -l un*
ls: cannot access un*: No such file or directory
启动数据库
由于有undo文件丢失数据库在启动的时候检测到文件丢失(ORA-01157)，无法open,offline文件后依旧无法启动(ORA-00376)
[oracle@ora1221 orcl12c2]$ ss SQL*Plus: Release 12.2.0.0.3 Production on Thu Jun 16 22:51:21 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup pfile='/tmp/pfile' ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes Database mounted. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl12c2/undotbs01.dbf' offline 数据文件 SQL> alter database datafile 4 offline ; alter database datafile 4 offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled SQL> alter database datafile 4 offline drop; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 4 cannot be read at this time ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl12c2/undotbs01.dbf' Process ID: 7547 Session ID: 16 Serial number: 56234
把undo_management修改为manual后启动库，依旧报ORA-00376
SQL> startup pfile='/tmp/pfile' mount; ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes Database mounted. SQL> show parameter undo_management; NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ undo_management string MANUAL SQL> alter database open; alter database open * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 4 cannot be read at this time ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl12c2/undotbs01.dbf' Process ID: 7981 Session ID: 16 Serial number: 56572
设置_corrupted_rollback_segments参数
SQL> startup pfile='/tmp/pfile' mount; ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes Database mounted. SQL> show parameter _corrupted_rollback_segments; NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ _corrupted_rollback_segments string _SYSSMU1_3200770482$, _SYSSMU2 _3597554035$, _SYSSMU3_2898427 493$, _SYSSMU4_670955920$, _SY SSMU5_1233449977$, _SYSSMU6_32 67641983$, _SYSSMU7_2822479342 $, _SYSSMU8_1645196706$, _SYSS MU9_3032014485$, _SYSSMU10_474 465626$ SQL> alter database open; Database altered.
通过设置_corrupted_rollback_segments参数之后，数据库正常启动，下面继续其他pdb
open pdb1
SQL> alter session set container=pdb1;
Session altered.
SQL> alter database open;
Database altered.
SQL> select count(*) from chf.t_xifenfei_p1;
COUNT(*)
----------
72426
pdb2 open
SQL> alter session set container=pdb2;
Session altered.
SQL> alter database open;
Database altered.
SQL> select count(*) from chf.t_xifenfei_p2;
COUNT(*)
----------
72426
至此数据库基本上恢复完成，但是看到的pdb里面两个测试表的数据和我们预测的有一定的偏差,看来cdb中的undo和pdb中的undo还是有一定的依赖关系.同时也说明了root的undo异常对于其他pdb的open最少在恢复上面影响不大.下一篇测试业务pdb中undo异常处理
Oracle 12c redo 丢失恢复
模拟redo丢失
对数据库的一个pdb模拟事务操作，然后abort库，并且删除所有redo，模拟生产环境redo丢失的case
[oracle@ora1221 oradata]$ ss
SQL*Plus: Release 12.2.0.0.3 Production on Wed Jun 15 10:13:20 2016
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size 8260048 bytes
Variable Size 671090224 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8515584 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> set pages 100
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
SQL> select con_id,file#,checkpoint_change# from v$datafile_header order by 1;
CON_ID FILE# CHECKPOINT_CHANGE#
---------- ---------- ------------------
1 1 1500157
1 3 1500157
1 4 1500157
1 7 1500157
2 5 1371280
2 6 1371280
2 8 1371280
3 9 1499902
3 12 1499902
3 11 1499902
3 10 1499902
4 15 1499903
4 14 1499903
4 13 1499903
4 16 1499903
15 rows selected.
SQL> alter PLUGGABLE database pdb1 open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
SQL> alter session set container=pdb1;
Session altered.
SQL> create user chf identified by oracle;
User created.
SQL> grant dba to chf;
Grant succeeded.
SQL> create table chf.t_pdb1_xifenfei as select * from dba_objects;
Table created.
SQL> delete from chf.t_pdb1_xifenfei;
72426 rows deleted.
--另外一个节点
[oracle@ora1221 ~]$ ss
SQL*Plus: Release 12.2.0.0.3 Production on Wed Jun 15 10:19:21 2016
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production
SQL> shutdown abort
ORACLE instance shut down.
[oracle@ora1221 orcl12c2]$ ls redo*
redo01.log redo02.log redo03.log
[oracle@ora1221 orcl12c2]$ rm redo0*
[oracle@ora1221 orcl12c2]$ ls -l redo*
ls: cannot access redo*: No such file or directory
尝试启动数据库
[oracle@ora1221 orcl12c2]$ ss SQL*Plus: Release 12.2.0.0.3 Production on Wed Jun 15 10:26:20 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl12c2/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7
使用隐含参数启动
----pfile里面增加 _allow_error_simulation=TRUE _allow_resetlogs_corruption=true ~ SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down SQL> startup pfile='/tmp/pfile' mount ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes Database mounted. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], [] Process ID: 36797 Session ID: 16 Serial number: 24277
继续重启库
ORA-600 kcbzib_kcrsds_1错误尝试重启数据库,如果不行,考虑使用bbed修改文件头信息
SQL> startup mount pfile='/tmp/pfile' ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes Database mounted. SQL> recover database until cancel; ORA-00283: recovery session canceled due to errors ORA-16433: The database or pluggable database must be opened in read/write mode. SQL> alter database backup controlfile to trace as '/tmp/ctl'; alter database backup controlfile to trace as '/tmp/ctl' * ERROR at line 1: ORA-16433: The database or pluggable database must be opened in read/write mode.
重建控制文件
SQL> startup nomount pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size 8260048 bytes
Variable Size 671090224 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8515584 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "orcl12c2" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 50
3 MAXLOGMEMBERS 5
4 MAXDATAFILES 100
5 MAXINSTANCES 1
6 MAXLOGHISTORY 226
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/orcl12c2/redo01.log' SIZE 200M,
9 GROUP 2 '/u01/app/oracle/oradata/orcl12c2/redo02.log' SIZE 200M,
10 GROUP 3 '/u01/app/oracle/oradata/orcl12c2/redo03.log' SIZE 200M
11 DATAFILE
12 '/u01/app/oracle/oradata/orcl12c2/system01.dbf',
13 '/u01/app/oracle/oradata/orcl12c2/sysaux01.dbf',
14 '/u01/app/oracle/oradata/orcl12c2/undotbs01.dbf',
15 '/u01/app/oracle/oradata/orcl12c2/pdbseed/system01.dbf',
16 '/u01/app/oracle/oradata/orcl12c2/pdbseed/sysaux01.dbf',
17 '/u01/app/oracle/oradata/orcl12c2/users01.dbf',
18 '/u01/app/oracle/oradata/orcl12c2/pdbseed/undotbs01.dbf',
19 '/u01/app/oracle/oradata/orcl12c2/pdb1/system01.dbf',
20 '/u01/app/oracle/oradata/orcl12c2/pdb1/sysaux01.dbf',
21 '/u01/app/oracle/oradata/orcl12c2/pdb1/undotbs01.dbf',
22 '/u01/app/oracle/oradata/orcl12c2/pdb1/users01.dbf',
23 '/u01/app/oracle/oradata/orcl12c2/pdb2/system01.dbf',
24 '/u01/app/oracle/oradata/orcl12c2/pdb2/sysaux01.dbf',
25 '/u01/app/oracle/oradata/orcl12c2/pdb2/undotbs01.dbf',
26 '/u01/app/oracle/oradata/orcl12c2/pdb2/users01.dbf'
27 CHARACTER SET AL32UTF8
28 ;
Control file created.
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1500161 generated at 06/15/2016 10:40:42 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/12.2.0/db_2/dbs/arch1_1_914582438.dbf
ORA-00280: change 1500161 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl12c2/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
<strong>open过程alert日志</strong>
<strong>open pdb1</strong>
SQL> alter PLUGGABLE database pdb1 open;
Pluggable database altered.
pdb1 open alert日志
2016-06-15T11:13:39.423057+08:00 alter PLUGGABLE database pdb1 open PDB1(3):Autotune of undo retention is turned on. 2016-06-15T11:13:39.495559+08:00 PDB1(3):Endian type of dictionary set to little PDB1(3):[40547] Successfully onlined Undo Tablespace 2. PDB1(3):Undo initialization finished serial:0 start:371149831 end:371149872 diff:41 ms (0.0 seconds) PDB1(3):Database Characterset for PDB1 is AL32UTF8 PDB1(3):********************************************************************* PDB1(3):WARNING: The following temporary tablespaces in container(PDB1) PDB1(3): contain no files. PDB1(3): This condition can occur when a backup controlfile has PDB1(3): been restored. It may be necessary to add files to these PDB1(3): tablespaces. That can be done using the SQL statement: PDB1(3): PDB1(3): ALTER TABLESPACE <tablespace_name> ADD TEMPFILE PDB1(3): PDB1(3): Alternatively, if these temporary tablespaces are no longer PDB1(3): needed, then they can be dropped. PDB1(3): Empty temporary tablespace: TEMP PDB1(3):********************************************************************* PDB1(3):Opatch validation is skipped for PDB PDB1 (con_id=0) PDB1(3):Opening pdb with no Resource Manager plan active Pluggable database PDB1 opened read write Completed: alter PLUGGABLE database pdb1 open
open pdb2
SQL> alter PLUGGABLE database pdb2 open; alter PLUGGABLE database pdb2 open * ERROR at line 1: ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []
分析alert日志和trace文件
--alert日志部分 PDB1(3):alter PLUGGABLE database pdb2 open PDB1(3):ORA-65118 signalled during: alter PLUGGABLE database pdb2 open... 2016-06-15T11:28:57.439963+08:00 PDB1(3):Unified Audit: Audit record write to table failed due to ORA-25153. Writing the audit record to OS spillover file. Please grep in the trace files for ORA-25153 for more diagnostic information. Errors in file /u01/app/oracle/diag/rdbms/orcl12c2/orcl12c2/trace/orcl12c2_ora_40547.trc (incident=29073) (PDBNAME=PDB1): ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], [] PDB1(3):Incident details in: /u01/app/oracle/diag/rdbms/orcl12c2/orcl12c2/incident/incdir_29073/orcl12c2_ora_40547_i29073.trc PDB1(3):***************************************************************** PDB1(3):An internal routine has requested a dump of selected redo. PDB1(3):This usually happens following a specific internal error, when PDB1(3):analysis of the redo logs will help Oracle Support with the PDB1(3):diagnosis. PDB1(3):It is recommended that you retain all the redo logs generated (by PDB1(3):all the instances) during the past 12 hours, in case additional PDB1(3):redo dumps are required to help with the diagnosis. PDB1(3):***************************************************************** 2016-06-15T11:28:59.123041+08:00 PDB1(3):Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. 2016-06-15T11:28:59.945667+08:00 Dumping diagnostic data in directory=[cdmp_20160615112859], requested by (instance=1, osid=40547), summary=[incident=29073]. 2016-06-15T11:35:59.987419+08:00 PDB1(3): alter PLUGGABLE database pdb2 open PDB1(3):ORA-65118 signalled during: alter PLUGGABLE database pdb2 open... --trace部分 PARSING IN CURSOR #0x7f051a3d7650 len=118 dep=1 uid=0 oct=3 lid=0 tim=372490287736 hv=1128335472 ad='0x6ca82f00' sqlid='gu930gd1n223h' select tablespace_name, tablespace_size, allocated_space, free_space, con_id from cdb_temp_free_space order by con_id END OF STMT EXEC #0x7f051a3d7650:c=0,e=144,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2538033465,tim=372490287732 FETCH #0x7f051a3d7650:c=0,e=290,p=0,cr=14,cu=0,mis=0,r=0,dep=1,og=4,plh=2538033465,tim=372490288109 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 52 FileOperation=2 fileno=0 filetype=36 obj#=402 tim=372490288373 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 17 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490288577 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 3 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490288655 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 690 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490289365 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 6 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490289470 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 445 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490289934 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 3 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490289983 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 375 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490290374 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 6 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490290453 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 367 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490290839 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 3 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490290882 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 355 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291252 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 4 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291298 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 276 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291590 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 1 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291614 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 256 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291879 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 2 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291903 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 261 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490292172 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 30 FileOperation=3 fileno=0 filetype=36 obj#=402 tim=372490292225 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 934 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293171 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 3 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293208 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 245 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293465 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 262 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293755 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 1 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293780 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 250 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490294039 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 256 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490294323 WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 8 FileOperation=5 fileno=0 filetype=36 obj#=402 tim=372490294359 2016-06-15T11:36:00.055196+08:00 Incident 29074 created, dump file: /u01/app/oracle/diag/rdbms/orcl12c2/orcl12c2/incident/incdir_29074/orcl12c2_ora_40547_i29074.trc ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []
从中可以判断出来是由于CDB$ROOT的未增加tempfile导致
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl12c2/temp01.dbf' reuse; Tablespace altered. SQL> alter PLUGGABLE database pdb2 open; Pluggable database altered.
查看数据库恢复情况
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL> select con_id,file#,checkpoint_change#,resetlogs_change# from v$datafile_header;
CON_ID FILE# CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------- ---------- ------------------ -----------------
1 1 2500167 1500164
1 3 2500167 1500164
1 4 2500167 1500164
2 5 1371280 1341067
2 6 1371280 1341067
1 7 2500167 1500164
2 8 1371280 1341067
3 9 2501017 1500164
3 10 2501017 1500164
3 11 2501017 1500164
3 12 2501017 1500164
4 13 2502748 1500164
4 14 2502748 1500164
4 15 2502748 1500164
4 16 2502748 1500164
15 rows selected.
至此基本上测试完成在在cdb环境中丢失redo的恢复。在生产中，需要把temp加全，并且建议重建数据库
oradebug poke ORA-32521/ORA-32519故障解决
最近有不少朋友咨询12.1.0.2及其以后的版本使用oradebug去修改scn失败,这里做了一个测试正常情况下确实无法修改(oradebug poke报 ORA-32519 或者 ORA-32521) ,这里进行了一系列修改测试最后修改成功.
数据库版本
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for 64-bit Windows: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - Production 0
oradebug poke测试
SQL> oradebug setmypid 已处理的语句 SQL> oradebug DUMPvar SGA kcsgscn_ kcslf kcsgscn_ [14C8D6270, 14C8D62A0) = 009EA333 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 4C8D5CF0 00000001 SQL> oradebug poke 0x14C8D6274 4 0x00000001 ORA-32521: 对 ORADEBUG 命令 进行语法分析时出错 --或者该提示 SQL> oradebug setmypid 已处理的语句 SQL> oradebug DUMPvar SGA kcsgscn_ kcslf kcsgscn_ [14C8D6270, 14C8D62A0) = 009EAE3D 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 4C8D5CF0 00000001 SQL> oradebug poke 0x14C8D6274 4 0x0000000a ORA-32519: 权限不足, 无法执行 ORADEBUG 命令: execution of ORADEBUG commands is disabled for this instance
通过测试确定oradebug正常情况无法执行poke,不是提示ORA-32521就是提示ORA-32519错误导致scn无法修改.
通过一些修改之后oradebug 修改scn
SQL> select dbid, name,open_mode,
2 created created,
3 open_mode, log_mode,
4 checkpoint_change# as checkpoint_change#,
5 controlfile_type ctl_type,
6 controlfile_created ctl_created,
7 controlfile_change# as ctl_change#,
8 controlfile_time ctl_time,
9 resetlogs_change# as resetlogs_change#,
10 resetlogs_time resetlogs_time
11 from v$database;
DBID NAME OPEN_MODE CREATED OPEN_MODE
LOG_MODE
---------- ---------------------------------------------------- -------------------- -------------- ------------
------------
CHECKPOINT_CHANGE# CTL_TYP CTL_CREATED CTL_CHANGE# CTL_TIME RESETLOGS_CHANGE# RESETLOGS_TIME
------------------ ------- -------------- ----------- -------------- ----------------- --------------
1504692401 XIFENFEI READ WRITE 16-8月 -15 READ WRITE
ARCHIVELOG
10407853 CURRENT 16-8月 -15 10408361 07-7月 -16 1 16-8月 -15
SQL> select con_id,file#,checkpoint_change# from v$datafile_header;
CON_ID FILE# CHECKPOINT_CHANGE#
---------- ---------- ------------------
1 1 10407853
2 2 9457324
1 3 10407853
2 4 9457324
1 5 10407853
1 6 10407853
3 7 10407853
3 8 10407853
3 9 10407853
4 10 9559964
4 11 9559964
4 12 9559964
3 13 10407853
已选择 13 行。
SQL> shutdown abort;
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 3221225472 bytes
Fixed Size 3837232 bytes
Variable Size 838861520 bytes
Database Buffers 2365587456 bytes
Redo Buffers 12939264 bytes
数据库装载完毕。
SQL> oradebug setmypid
已处理的语句
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [14BE16270, 14BE162A0) = 009ED5C6 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 4BE15CF0 00000001
SQL> oradebug poke 0x14BE16274 4 0x0001
BEFORE: [14BE16274, 14BE16276) = 0000
AFTER: [14BE16274, 14BE16276) = 0001
SQL> alter database open;
数据库已更改。
SQL> select dbid, name,open_mode,
2 created created,
3 open_mode, log_mode,
4 checkpoint_change# as checkpoint_change#,
5 controlfile_type ctl_type,
6 controlfile_created ctl_created,
7 controlfile_change# as ctl_change#,
8 controlfile_time ctl_time,
9 resetlogs_change# as resetlogs_change#,
10 resetlogs_time resetlogs_time
11 from v$database;
DBID NAME OPEN_MODE CREATED OPEN_MODE
LOG_MODE
---------- ---------------------------------------------------- -------------------- -------------- -----------
------------
CHECKPOINT_CHANGE# CTL_TYP CTL_CREATED CTL_CHANGE# CTL_TIME RESETLOGS_CHANGE# RESETLOGS_TIME
------------------ ------- -------------- ----------- -------------- ----------------- --------------
1504692401 XIFENFEI READ WRITE 16-8月 -15 READ WRITE
ARCHIVELOG
4305478053 CURRENT 16-8月 -15 4305478245 07-7月 -16 1 16-8月 -15
SQL> select con_id,file#,checkpoint_change# from v$datafile_header;
CON_ID FILE# CHECKPOINT_CHANGE#
---------- ---------- ------------------
1 1 4305478053
2 2 9457324
1 3 4305478053
2 4 9457324
1 5 4305478053
1 6 4305478053
3 7 4305478053
3 8 4305478053
3 9 4305478053
4 10 9559964
4 11 9559964
4 12 9559964
3 13 4305478053
已选择 13 行。
SQL> select con_id,file#,checkpoint_change# from v$datafile;
CON_ID FILE# CHECKPOINT_CHANGE#
---------- ---------- ------------------
1 1 4305478053
2 2 9457324
1 3 4305478053
2 4 9457324
1 5 4305478053
1 6 4305478053
3 7 4305478053
3 8 4305478053
3 9 4305478053
4 10 9559964
4 11 9559964
4 12 9559964
3 13 4305478053
已选择 13 行。
通过上述测试证明scn已经被完美修改.证明我们已经具备了不使用bbed的情况下推进12.1.0.2版本的scn问题,为12c的一系列需要推scn的恢复提供完美技术支持.
Resize operation completed for file#
Orale 12c DataGuard环境中备库出现Resize operation completed for file# 现象
数据库版本
[oracle@ray01 ~]$ opatch lspatches 22291127;Database Patch Set Update : 12.1.0.2.160419 (22291127) OPatch succeeded. SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for Linux: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - Production 0
alert日志提示
Mon Jun 27 20:56:37 2016 Resize operation completed for file# 18, old size 25600000K, new size 30720000K Mon Jun 27 20:56:56 2016 Archived Log entry 210 added for thread 1 sequence 286 rlc 915405135 ID 0x2316988c dest 2: Mon Jun 27 20:57:01 2016 Primary database is in MAXIMUM PERFORMANCE mode RFS[211]: Assigned to RFS process (PID:22867) RFS[211]: Selected log 11 for thread 1 sequence 287 dbid 588725388 branch 915405135 Mon Jun 27 20:57:14 2016 Resize operation completed for file# 17, old size 25600000K, new size 30720000K Mon Jun 27 07:57:15 2016 Archived Log entry 211 added for thread 1 sequence 287 ID 0x2316988c dest 1: Mon Jun 27 20:57:15 2016 Resize operation completed for file# 3, old size 972800K, new size 983040K Mon Jun 27 20:57:15 2016 Primary database is in MAXIMUM PERFORMANCE mode RFS[212]: Assigned to RFS process (PID:22873) RFS[212]: Selected log 11 for thread 1 sequence 288 dbid 588725388 branch 915405135 Mon Jun 27 20:57:15 2016 Resize operation completed for file# 3, old size 983040K, new size 1024000K Resize operation completed for file# 3, old size 1024000K, new size 1034240K Mon Jun 27 20:57:54 2016 Resize operation completed for file# 15, old size 25600000K, new size 30720000K Mon Jun 27 20:58:15 2016 Resize operation completed for file# 18, old size 30720000K, new size 33554416K Mon Jun 27 20:58:34 2016 Resize operation completed for file# 17, old size 30720000K, new size 33554416K Mon Jun 27 20:58:54 2016 Resize operation completed for file# 15, old size 30720000K, new size 33554416K
大量Resize operation completed for file# 操作记录,给人感觉比较烦,根据多年使用oracle的经验,这种现象很可能有隐含参数或者event屏蔽,隐含参数可以猜测到,event需要查询官方资料.
查询汗resize的隐含参数
SQL> col name for a52
SQL> col value for a24
SQL> col description for a50
set linesize 150
select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
from x$ksppi a,x$ksppcv b
where a.inst_id = USERENV ('Instance')
and b.inst_id = USERENV ('Instance')
and a.indx = b.indx
SQL> SQL> 2 3 4 5 6 and upper(a.ksppinm) LIKE upper('%¶m%')
7 order by name
/
8 Enter value for param: resize
old 6: and upper(a.ksppinm) LIKE upper('%¶m%')
new 6: and upper(a.ksppinm) LIKE upper('%resize%')
NAME VALUE DESCRIPTION
---------------------------------------------------- ------------------------ --------------------------------------------------
_asm_skip_resize_check FALSE skip the checking of the clients for s/w compatibi
lity for resize
_bct_public_dba_buffer_dynresize 2 allow dynamic resizing of public dba buffers, zero
to disable
_disable_file_resize_logging FALSE disable file resize logging to alert log
从这里可以发现_disable_file_resize_logging参数默认值为false,表示显示文件resize的提示,设置为true应该就可以解决该问题.