标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,674)
- DB2 (22)
- MySQL (73)
- Oracle (1,536)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (22)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (67)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (562)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (78)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
- 数据库启动报ORA-27102 OSD-00026 O/S-Error: (OS 1455)
- .[metro777@cock.li].Elbie勒索病毒加密数据库恢复
- 应用连接错误,初始化mysql数据库恢复
- RAC默认服务配置优先节点
标签归档:ORA-00283
Oracle 12C的第一次异常恢复—文件头坏块
接到第一个使用Oracle 12C作为生产库的恢复救援.有两个业务数据文件报文件头损坏,其他数据文件全部是9月份的一次备份,在当前的条件下,希望我们能够帮他们恢复出来业务文件中的数据
数据库版本信息
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 PL/SQL Release 12.1.0.1.0 - Production 0 CORE 12.1.0.1.0 Production 0 TNS for Linux: Version 12.1.0.1.0 - Production 0 NLSRTL Version 12.1.0.1.0 - Production 0
数据库故障
具体脚本请参考:数据库恢复检查脚本(Oracle Database Recovery Check)
控制文件信息
控制文件中关于数据文件信息
数据文件头信息
alert日志报错
Reading datafile '/app/oracle/oradata/freetouch/sales.dbf' for corruption at rdba: 0x00000001 (file 4, block 1) Reread (file 4, block 1) found same corrupt data (no logical check) Hex dump of (file 5, block 1) in trace file /app/oracle/diag/rdbms/valuenet/valuenet/trace/valuenet_ora_12384.trc Corrupt block relative dba: 0x00000001 (file 5, block 1) Fractured block found during kcvxfh v8 Data in bad block: type: 0 format: 2 rdba: 0x00000001 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000001 check value in block header: 0xa701 computed block checksum: 0x0 Reading datafile '/app/oracle/oradata/freetouch/drp_200200' for corruption at rdba: 0x00000001 (file 5, block 1) Reread (file 5, block 1) found same corrupt data (no logical check) Hex dump of (file 4, block 1) in trace file /app/oracle/diag/rdbms/valuenet/valuenet/trace/valuenet_ora_12384.trc Corrupt block relative dba: 0x00000001 (file 4, block 1) Fractured block found during kcvxfh v8 Data in bad block: type: 0 format: 2 rdba: 0x00000001 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000001 check value in block header: 0xa701 computed block checksum: 0x0
odu无法识别异常文件
[oracle@db odu]$ ./odu Oracle Data Unloader trial version 4.1.3 Copyright (c) 2008,2009,2010,2011 XiongJun. All rights reserved. loading default config....... byte_order little block_size 8192 db_timezone -7 Invalid db timezone:-7 client_timezone 8 Invalid client timezone:8 asmfile_extract_path /home/oracle/hongye/odu/data data_path /home/oracle/hongye/odu/data lob_path /home/oracle/hongye/odu/data charset_name ZHS16GBK ncharset_name AL16UTF16 output_form dmp error at line 10. lob_storage infile clob_byte_order big trace_level 1 delimiter | unload_deleted yes file_header_offset 0 is_tru64 no record_row_addr no convert_clob_charset yes load config file 'config.txt' successful loading default asm disk file ...... can not open file 'asmdisk.txt', error message:No such file or directory. loading default control file ...... unknown file format '/app/oracle/oradata/freetouch/sales.dbf' unknown file format '/app/oracle/oradata/freetouch/drp_200200' ts# fn rfn bsize blocks bf offset filename ---- ---- ---- ----- -------- -- ------ -------------------------------------------- 1 1 1 8192 194560 N 0 /app/oracle/oradata/freetouch/system01.dbf 6 2 10 8192 45840 N 0 /app/oracle/oradata/freetouch/example01.dbf 1 3 3 8192 907520 N 0 /app/oracle/oradata/freetouch/sysaux01.dbf 4 1024 10 8192 0 N 0 /app/oracle/oradata/freetouch/sales.dbf 5 1024 9 8192 0 N 0 /app/oracle/oradata/freetouch/drp_200200 4 6 6 8192 128320 N 0 /app/oracle/oradata/freetouch/users01.dbf 7 7 7 8192 780288 N 0 /app/oracle/oradata/freetouch/undotbs03.dbf 11 8 8 8192 25600 N 0 /app/oracle/oradata/freetouch/indx01.dbf load control file 'control.txt' successful loading dictionary data......done loading scanned data......done
dul无法识别异常文件
[oracle@db dul]$ ./dul Data UnLoader: 10.2.0.5.32 - Internal Only - on Sun Nov 2 23:34:42 2014 with 64-bit io functions Copyright (c) 1994 2014 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only DUL: Warning: ulimit process stack size is only 33554432 Found db_id = 270587870 Found db_name = VALUENET DUL: Warning: Cannot verify file number for /app/oracle/oradata/freetouch/sales.dbf DUL: Warning: First four bytes(76 162 0 0) of block 2 are not the start of a proper data block header DUL: Warning: Block corruption or configuration error DUL: Warning: Check db_block_size and/or osd_file_leader_size and/or file offset DUL: Error: File Number can only be zero for Single Tablespace Datafiles DUL: Warning: Cannot verify file number for /app/oracle/oradata/freetouch/drp_200200 DUL: Warning: First four bytes(76 162 0 0) of block 2 are not the start of a proper data block header DUL: Warning: Block corruption or configuration error DUL: Warning: Check db_block_size and/or osd_file_leader_size and/or file offset DUL: Error: File Number can only be zero for Single Tablespace Datafiles DUL> show datafiles; ts# rf# start blocks offs open err file name 0 1 0 194561 0 1 0 /app/oracle/oradata/freetouch/system01.dbf 1 3 0 907521 0 1 0 /app/oracle/oradata/freetouch/sysaux01.dbf 4 6 0 128321 0 1 0 /app/oracle/oradata/freetouch/users01.dbf 7 7 0 780289 0 1 0 /app/oracle/oradata/freetouch/undotbs03.dbf 11 8 0 25601 0 1 0 /app/oracle/oradata/freetouch/indx01.dbf 6 10 0 45841 0 1 0 /app/oracle/oradata/freetouch/example01.dbf
该异常文件使用dul/odu均无法正常识别.证明文件头确实已经损坏
dbv 检测
[oracle@db trace]$ dbv file=/app/oracle/oradata/freetouch/drp_200200 DBVERIFY: Release 12.1.0.1.0 - Production on Sun Nov 2 14:08:34 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /app/oracle/oradata/freetouch/drp_200200 DBVERIFY - Verification complete Total Pages Examined : 194560 Total Pages Processed (Data) : 114596 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 26198 Total Pages Failing (Index): 0 Total Pages Processed (Other): 37787 Total Pages Processed (Seg) : 1 Total Pages Failing (Seg) : 0 Total Pages Empty : 15979 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 129603862 (0.129603862) [oracle@db ~]$ dbv file=/app/oracle/oradata/freetouch/sales.dbf DBVERIFY: Release 12.1.0.1.0 - Production on Sun Nov 2 23:12:05 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /app/oracle/oradata/freetouch/sales.dbf DBVERIFY - Verification complete Total Pages Examined : 655360 Total Pages Processed (Data) : 294938 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 233404 Total Pages Failing (Index): 0 Total Pages Processed (Lob) : 38 Total Pages Failing (Lob) : 0 Total Pages Processed (Other): 23252 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 103728 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 134665298 (0.134665298)
dbv检测结果无坏块,但是v$datafile_header和alert日志中报坏块,初步判断是由于该文件是bigfile,dbv未检测到文件头坏块,实际该该数据文件头损坏,其他block正常.所幸的是该库有9月份的rman备份(中间归档丢失),因此使用rman还原出来9月份的数据文件,然后使用dd拷贝两个 block(block 0和block 1)到异常文件.
[root@db freetouch]# dd if=/app1/oracle/oradata/freetouch/sales.dbf bs=8192 count=2 of=/tmp/odu/sales.2 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 0.0176368 s, 929 kB/s [root@db freetouch]# dd if=/tmp/odu/sales.2 of=/app/oracle/oradata/freetouch/sales.dbf bs=8192 count=2 conv=notrunc 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 6.4281e-05 s, 255 MB/s [root@db freetouch]# dd if=/app1/oracle/oradata/freetouch/drp_200200 bs=8192 count=2 of=/tmp/odu/drp_200200.2 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 0.0185934 s, 881 kB/s [root@db freetouch]# dd if=/tmp/odu/drp_200200.2 of=/app/oracle/oradata/freetouch/drp_200200 bs=8192 count=2 conv=notrunc 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 6.4419e-05 s, 254 MB/s
尝试恢复数据库
SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done SQL> recover database using backup controlfile; ORA-00283: recovery session canceled due to errors ORA-19909: datafile 4 belongs to an orphan incarnation ORA-01110: data file 4: '/app/oracle/oradata/freetouch/sales.dbf'
使用bbed修改相关文件头,然后继续恢复
具体见:bbed解决ORA-01190类似方法处理
SQL> recover database using backup controlfile; ORA-00279: change 129603904 generated at 11/02/2014 19:19:54 needed for thread 1 ORA-00289: suggestion : /app/oracle/recovery_area/VALUENET/archivelog/2014_11_02/o1_mf_1_1_%u_.arc ORA-00280: change 129603904 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel; ORA-00308: cannot open archived log 'cancel;' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [0], [129603911], [0], [129603913], [29360256], [], [], [], [], [], [] Process ID: 19881 Session ID: 1 Serial number: 3
出现ORA-600[2662]错误,因为scn相差比较小,重启数据库机器,出现ORA-600[4194]错误
SQL> startup pfile='/tmp/pfile.txt' mount ORACLE instance started. Total System Global Area 2505338880 bytes Fixed Size 2291472 bytes Variable Size 973080816 bytes Database Buffers 1526726656 bytes Redo Buffers 3239936 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/app/oracle/oradata/freetouch/system01.dbf' SQL> recover database ; 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 open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [4194], [46], [19], [], [], [], [], [], [], [], [], [] Process ID: 20351 Session ID: 1 Serial number: 3
设置undo_management=MANUAL然后继续恢复
[oracle@db tmp]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Sun Nov 2 19:29:45 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup pfile='/tmp/pfile.txt' ORACLE instance started. Total System Global Area 2505338880 bytes Fixed Size 2291472 bytes Variable Size 973080816 bytes Database Buffers 1526726656 bytes Redo Buffers 3239936 bytes Database mounted. Database opened.
这次的恢复也证明Oracle 12C确实有着越来越多的用户在使用.
发表在 非常规恢复
标签为 ORA-00283, ORA-19909, ORA-600 2662, ORA=600 4194, Oracle 12C 恢复, Oracle 恢复, 数据库恢复
评论关闭
ORA-27069: skgfdisp: 尝试在文件范围外执行 I/O
接到网友技术支持请求,win 2003 ntfs格式文件系统,Oracle 8.1.7版本,主机重启后,数据库无法正常启动,offline datafile 15,数据库open成功,但是datafile 无法正常online,报错为:ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file,请求协助处理
SQL> recover datafile 'D:\ORACLE\ORADATA\ORCL\ZSF_DATA.DBF'; ORA-00283: 恢复会话因错误而取消 ORA-01115: 从文件 15 读取块时出现 IO 错误 (块 # 1030071) ORA-01110: 数据文件 15: 'D:\ORACLE\ORADATA\ORCL\ZSF_DATA.DBF' ORA-27069: skgfdisp: 尝试在文件范围外执行 I/O OSD-04026: 无效的参数经过. (OS 1030071)
使用bbed,成功online datafile 15
Tue Oct 28 16:30:35 2014 ALTER DATABASE RECOVER datafile 15 Tue Oct 28 16:30:35 2014 Media Recovery Datafile: 15 Media Recovery Start Media Recovery Log Recovery of Online Redo Log: Thread 1 Group 1 Seq 245110 Reading mem 0 Mem# 0 errs 0: D:\ORACLE\ORADATA\ORCL\REDO03.LOG Media Recovery failed with error 1115 ORA-283 signalled during: ALTER DATABASE RECOVER datafile 15 ... Tue Oct 28 16:32:53 2014 Shutting down instance (abort) License high water mark = 6 Instance terminated by USER, pid = 1548 Starting up ORACLE RDBMS Version: 8.1.7.0.0. System parameters with non-default values: processes = 600 shared_pool_size = 52428800 large_pool_size = 20971520 java_pool_size = 20971520 control_files = D:\oracle\oradata\ORCL\control01.ctl, D:\oracle\oradata\ORCL\control02.ctl db_block_buffers = 19200 db_block_size = 8192 compatible = 8.1.0 log_buffer = 32768 log_checkpoint_interval = 10000 log_checkpoint_timeout = 1800 db_files = 1024 db_file_multiblock_read_count= 8 max_enabled_roles = 30 remote_login_passwordfile= EXCLUSIVE global_names = TRUE distributed_transactions = 500 instance_name = ORCL service_names = ORCL mts_dispatchers = (PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer) open_links = 4 sort_area_size = 65536 sort_area_retained_size = 65536 db_name = ORCL open_cursors = 500 ifile = D:\oracle\admin\ORCL\pfile\init.ora os_authent_prefix = job_queue_processes = 4 job_queue_interval = 10 parallel_max_servers = 5 background_dump_dest = D:\oracle\admin\ORCL\bdump user_dump_dest = D:\oracle\admin\ORCL\udump max_dump_file_size = 10240 oracle_trace_collection_name= PMON started with pid=2 DBW0 started with pid=3 LGWR started with pid=4 CKPT started with pid=5 SMON started with pid=6 RECO started with pid=7 SNP0 started with pid=8 SNP1 started with pid=9 SNP2 started with pid=10 SNP3 started with pid=11 Tue Oct 28 16:33:01 2014 starting up 1 shared server(s) ... starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... Tue Oct 28 16:33:02 2014 ALTER DATABASE MOUNT Tue Oct 28 16:33:06 2014 Successful mount of redo thread 1, with mount id 1389958722. Tue Oct 28 16:33:06 2014 Database mounted in Exclusive Mode. Completed: ALTER DATABASE MOUNT Tue Oct 28 16:33:49 2014 ALTER DATABASE RECOVER database until cancel Tue Oct 28 16:33:49 2014 Media Recovery Start Media Recovery Log kcrrga: Warning. Log sequence in archive filename wrapped to fix length as indicated by %S in LOG_ARCHIVE_FORMAT. Old log archive with same name might be overwritten. ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel ... Tue Oct 28 16:34:03 2014 ALTER DATABASE RECOVER LOGFILE 'D:\ORACLE\ORADATA\ORCL\REDO02.LOG' Tue Oct 28 16:34:03 2014 Media Recovery Log D:\ORACLE\ORADATA\ORCL\REDO02.LOG Incomplete recovery applied all redo ever generated. Recovery completed through change %s139866389 Media Recovery Complete Completed: ALTER DATABASE RECOVER LOGFILE 'D:\ORACLE\ORADA Tue Oct 28 16:34:29 2014 alter database datafile 15 online Tue Oct 28 16:34:29 2014 Completed: alter database datafile 15 online Tue Oct 28 16:34:36 2014 alter database open resetlogs RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. RESETLOGS after incomplete recovery UNTIL CHANGE 139866389 Tue Oct 28 16:34:38 2014 Thread 1 opened at log sequence 1 Current log# 2 seq# 1 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO02.LOG Successful open of redo thread 1. Tue Oct 28 16:34:38 2014 SMON: enabling cache recovery Tue Oct 28 16:34:38 2014 Dictionary check beginning Dictionary check complete Tue Oct 28 16:34:39 2014 SMON: enabling tx recovery Tue Oct 28 16:34:44 2014 Completed: alter database open resetlogs
数据库datafile 15 online成功后,客户操作业务继续发生ORA-600[ktsxs_add2]错误
Tue Oct 28 17:07:42 2014 Errors in file D:\oracle\admin\ORCL\udump\ORA02340.TRC: ORA-00600: 内部错误代码,自变量: [ktsxs_add2], [14], [15], [42534], [5732], [5733], [], [] Tue Oct 28 17:07:53 2014 Errors in file D:\oracle\admin\ORCL\udump\ORA02340.TRC: ORA-00600: 内部错误代码,自变量: [ktsxs_add2], [14], [15], [42534], [5732], [5733], [], [] Tue Oct 28 17:08:03 2014 Errors in file D:\oracle\admin\ORCL\udump\ORA02340.TRC: ORA-00600: 内部错误代码,自变量: [ktsxs_add2], [14], [15], [42534], [5732], [5733], [], [] Tue Oct 28 17:08:16 2014 Errors in file D:\oracle\admin\ORCL\udump\ORA02340.TRC: ORA-00600: 内部错误代码,自变量: [ktsxs_add2], [14], [15], [42534], [5732], [5733], [], [] Tue Oct 28 17:08:23 2014 Errors in file D:\oracle\admin\ORCL\udump\ORA02308.TRC: ORA-00600: 内部错误代码,自变量: [ktsxs_add2], [14], [15], [42534], [5732], [5733], [], [] Tue Oct 28 17:08:31 2014 Errors in file D:\oracle\admin\ORCL\udump\ORA02340.TRC: ORA-00600: 内部错误代码,自变量: [ktsxs_add2], [14], [15], [42534], [5732], [5733], [], [] Tue Oct 28 17:08:38 2014 Errors in file D:\oracle\admin\ORCL\udump\ORA02308.TRC: ORA-00600: 内部错误代码,自变量: [ktsxs_add2], [14], [15], [42534], [5732], [5733], [], []
通过分析相关日志发现是insert插入表报错,很好理解,该库的datafile 15已经超过了系统的限制,现在继续插入数据,因此报错,查询可能异常对象
SQL> col segment_name for a20 SQL> SELECT distinct OWNER, SEGMENT_NAME, SEGMENT_TYPE, A.PARTITION_NAME 2 FROM DBA_EXTENTS A 3 WHERE FILE_ID = 15 4 AND 1030071 <= BLOCK_ID; OWNER SEGMENT_NAME SEGMENT_TYPE ------------------------------ -------------------- ------------------ PARTITION_NAME ------------------------------ ZSF DETAIL TABLE ZSF DETAIL1 INDEX ZSF DETAIL2 INDEX OWNER SEGMENT_NAME SEGMENT_TYPE ------------------------------ -------------------- ------------------ PARTITION_NAME ------------------------------ ZSF DETAIL3 INDEX ZSF DETAIL4 INDEX ZSF FK_RECI_ORD INDEX OWNER SEGMENT_NAME SEGMENT_TYPE ------------------------------ -------------------- ------------------ PARTITION_NAME ------------------------------ ZSF PREPAY1 INDEX ZSF RECEDETAIL1 INDEX
创建新表空间
Create tablespace zsf_new datafile 'D:\ORACLE\ORADATA\ORCL\ZSF_DATA_new01.dbf' size 4096m; alter tablespace zsf_new add datafile 'D:\ORACLE\ORADATA\ORCL\ZSF_DATA_new02.dbf' size 128m autoextend on next 128M maxsize 4096m;
迁移异常对象到新表空间
alter table ZSF.DETAIL move tablespace ZSF_new; alter index ZSF.DETAIL1 rebuild tablespace ZSF_new; alter index ZSF.DETAIL2 rebuild tablespace ZSF_new; alter index ZSF.DETAIL3 rebuild tablespace ZSF_new; alter index ZSF.DETAIL4 rebuild tablespace ZSF_new; alter index ZSF.FK_RECI_ORD rebuild tablespace ZSF_new; alter index ZSF.PREPAY1 rebuild tablespace ZSF_new; alter index ZSF.RECEDETAIL1 rebuild tablespace ZSF_new;
然后对于datafile 15所在表空间增加新文件,因为已经迁移了异常对象,然后resize datafile 15小于8G,关闭自扩展,至此该数据库恢复完成
发表在 非常规恢复
标签为 ktsxs_add2, ORA-00283, ORA-01115, ORA-27069, ORA-600 ktsxs_add2, OSD-04026, skgfdisp
评论关闭
分享一次ORA-01113 ORA-01110故障处理过程
近期编写了小工具,对于此类问题,可以实现一键恢复,参考:一键恢复ORA-01113 ORA-01110—Oracle Recovery Tools
数据库报ORA-01113 ORA-01110无法正常open
SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
alert日志
Wed Jul 16 17:17:56 2014 ALTER DATABASE MOUNT Successful mount of redo thread 1, with mount id 1380870980 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT Wed Jul 16 17:19:01 2014 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_3540.trc (incident=367575): ORA-00700: soft internal error, arguments: [dbgrmblcp_corrupt_page], [D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\metadata\HM_FINDING.ams], [1245], [], [], [], [], [], [], [], [], [] ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF' Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_367575\orcl_m000_3540_i367575.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 D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_3540.trc (incident=367576): ORA-00600: internal error code, arguments: [dbgrmblgp_get_page_1], [1245], [0], [80], [], [], [], [], [], [], [], [] ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF' Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_367576\orcl_m000_3540_i367576.trc Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_3540.trc: ORA-51106: check failed to complete due to an error. See error below ORA-00600: internal error code, arguments: [dbgrmblgp_get_page_1], [1245], [0], [80], [], [], [], [], [], [], [], [] ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
这里出现ORA-00700[dbgrmblcp_corrupt_page]与ORA-00600[dbgrmblgp_get_page_1]是Bug 10321285 : ORA-600 [DBGRMBLCP_CORRUPT_PAGE]
尝试recover database 遭遇ORA-00283 ORA-16433
SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-16433: The database must be opened in read/write mode.
alert日志信息
Wed Jul 16 17:36:33 2014 ALTER DATABASE RECOVER database Media Recovery Start started logmerger process Wed Jul 16 17:36:33 2014 Media Recovery failed with error 16433 Slave exiting with ORA-283 exception Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_pr00_3868.trc: ORA-00283: recovery session canceled due to errors ORA-16433: The database must be opened in read/write mode. Recovery Slave PR00 previously exited with exception 283 ORA-283 signalled during: ALTER DATABASE RECOVER database ... Wed Jul 16 17:36:45 2014 alter database open Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_3236.trc: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF' ORA-1113 signalled during: alter database open...
重建控制文件后恢复,遭遇ORA-600 2662错误
SQL> shutdown abort ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 10255212544 bytes Fixed Size 2264088 bytes Variable Size 4529849320 bytes Database Buffers 5704253440 bytes Redo Buffers 18845696 bytes SQL> @d:\ctl.sql Control file created. SQL> recover database using backup controlfile; ORA-00279: change 13953689551797 generated at 07/16/2014 17:53:41 needed for thread 1 ORA-00289: suggestion : D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_07_16\O1_MF_1_1_%U_ .ARC ORA-00280: change 13953689551797 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} d:\app\administrator\oradata\orcl\redo01.log Log applied. Media recovery complete. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [2662], [3248], [3635774399], [3249], [3580470066], [4194545], [], [], [], [], [], [] Process ID: 3932 Session ID: 200 Serial number: 1
因为该数据库是11.2.0.3未打上scn patch,直接推进scn(可以使用event,隐含参数,oradebug,bbed等),数据库就正常open