分类目录归档：Oracle备份恢复

raid强制上线后数据库无法启动故障处理

发表于 2021 年 4 月 20 日 由 惜分飞

由于raid掉盘过多,强制raid上线,然后启动数据库报以下错误

Mon Apr 19 23:19:28 2021
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 106750 KB redo, 9080 data blocks need recovery
Mon Apr 19 23:19:45 2021
Slave exiting with ORA-1115 exception
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p000_3277.trc:
ORA-01115: IO error reading block from file 9 (block # 339)
ORA-01110: data file 9: '/u01/app/oracle/oradata/orcl/dev02.dbf'
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I
Mon Apr 19 23:19:45 2021
Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 106750 KB redo, 9080 data blocks need recovery
Aborting crash recovery due to error 1115
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3275.trc:
ORA-01115: IO error reading block from file 9 (block # 329)
ORA-01110: data file 9: '/u01/app/oracle/oradata/orcl/dev02.dbf'
ORA-1115 signalled during: ALTER DATABASE OPEN...

错误提示比较明显IO error,结合客户强行上线raid的操作,比较明显是由于底层io问题导致该错误,直接对此文件dbv检查

[oracle@database orcl]$ dbv file=dev02.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Mon Apr 19 23:59:03 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/dev02.dbf

DBV-00600: Fatal Error - [28] [27061] [0] [0]

对于此类情况,通过工具进行处理

DUL> copy file from  /u01/app/oracle/oradata/orcl/dev02.dbf to /oradata/dev02.dbf
 
starting copy datafile '/u01/app/oracle/oradata/orcl/dev02.dbf' to '/oradata/dev02.dbf'
read data error from file '/u01/app/oracle/oradata/orcl/dev02.dbf'.error message:Input/output error
read block# error: 303
read data error from file '/u01/app/oracle/oradata/orcl/dev02.dbf'.error message:Input/output error
read block# error: 304
read data error from file '/u01/app/oracle/oradata/orcl/dev02.dbf'.error message:Input/output error
read block# error: 329
datafile copy completed with 3 block error.

dbv校验文件

[oracle@database oradata]$ dbv file=dev02.dbf 

DBVERIFY: Release 11.2.0.4.0 - Production on Tue Apr 20 00:28:31 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /oradata/dev02.dbf
Page 303 is marked corrupt
Corrupt block relative dba: 0x0240012f (file 9, block 303)
Completely zero block found during dbv: 

Page 304 is marked corrupt
Corrupt block relative dba: 0x02400130 (file 9, block 304)
Completely zero block found during dbv: 

Page 329 is marked corrupt
Corrupt block relative dba: 0x02400149 (file 9, block 329)
Completely zero block found during dbv: 



DBVERIFY - Verification complete

Total Pages Examined         : 3932160
Total Pages Processed (Data) : 3213723
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 714294
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 4139
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1
Total Pages Marked Corrupt   : 3
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 85078875 (6.85078875)

通过对io error的文件进行处理,最终损坏三个block,最大限度抢救数据.使用被恢复出来的文件,尝试open库遭遇以下错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [6], [85035771], [6],
[85084136], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [6], [85035770], [6],
[85084136], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [6], [85035764], [6],
[85084136], [12583040], [], [], [], [], [], []
Process ID: 6733
Session ID: 570 Serial number: 3

ora-600 2662这个错误比较明显,处理文件头scn,继续open库

SQL> alter database open ;
alter database open 
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 6840
Session ID: 570 Serial number: 3

查看alert日志信息

Tue Apr 20 01:22:27 2021
alter database open upgrade
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 1 KB redo, 3 data blocks need recovery
Started redo application at
 Thread 1: logseq 1, block 3
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/orcl/redo01.log
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 1, block 5, scn 25854859541
 3 data blocks read, 3 data blocks written, 1 redo k-bytes read
Tue Apr 20 01:22:28 2021
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Apr 20 01:22:28 2021
SMON: enabling cache recovery
[6840] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:5902014 end:5905574 diff:3560 (35 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_6824.trc  (incident=63970):
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_63970/orcl_smon_6824_i63970.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORACLE Instance orcl (pid = 14) - Error 600 encountered while recovering transaction (24, 2) on object 89023.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_6824.trc:
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Tue Apr 20 01:22:38 2021
ORACLE Instance orcl (pid = 14) - Error 600 encountered while recovering transaction (63, 3) on object 89023.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_6824.trc:
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_6824.trc  (incident=63974):
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_63974/orcl_smon_6824_i63974.trc
Tue Apr 20 01:22:55 2021
PMON (ospid: 6798): terminating the instance due to error 474

这个错误是比较常见的错误,参考:ORACLE Instance XFF (pid = 18) – Error 600 encountered while recovering transaction ，通过处理之后,数据库open成功

SQL> startup mount pfile='/tmp/pfile';
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size            1023413408 bytes
Database Buffers          570425344 bytes
Redo Buffers                7319552 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

后续安排逻辑导出,导入新库

发表在 Oracle备份恢复 | 标签为 , , , , | 评论关闭

记录一次oracle现场故障处理经过

发表于 2021 年 4 月 3 日 由 惜分飞

近期到现场进行了一个数据库恢复,我在恢复之前该库先由于硬件进行恢复,然后由其他人对其进行了一系列数据库恢复,但是未恢复成功,客户希望我们到现场进行处理(因为网络原因无法远程).接手库之后,处理第一个问题,是客户在进行现场备份的时候(把linux数据拷贝到win的过程中)发现有几个文件拷贝异常,这个错误很可能是由于当初的硬件故障修复之后留下的后遗症(由于io设备错误,无法运行此项请求),通过工具进行拷贝,恢复出来
20210403210131

DUL> copy file from  /oradata2/xifenfeidata.dbf to /oradata2/xifenfeidata.dbf

starting copy datafile '/oradata1/xifenfeidata.dbf' to '/oradata2/xifenfeidata.dbf'
read data error from file '/oradata1/xifenfeidata.dbf'.error message:Input/output error
read block# error: 560171
read data error from file '/oradata1/xifenfeidata.dbf'.error message:Input/output error
read block# error: 560179
datafile copy completed with 2 block error.

[oracle@localhost ~]$ dbv file=/oradata2/xifenfeidata.dbf blocksize=16384

DBVERIFY: Release 11.2.0.3.0 - Production on Mon Mar 29 17:28:17 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /oradata2/xifenfeidata.dbf
Page 560171 is marked corrupt
Corrupt block relative dba: 0x3bc88c2b (file 239, block 560171)
Completely zero block found during dbv: 

Page 560179 is marked corrupt
Corrupt block relative dba: 0x3bc88c33 (file 239, block 560179)
Completely zero block found during dbv: 



DBVERIFY - Verification complete

Total Pages Examined         : 4194302
Total Pages Processed (Data) : 2230726
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1936953
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 26618
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 3
Total Pages Marked Corrupt   : 2
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 304929867 (106.304929867)

修复完相关无法拷贝文件之后,启动数据库报控制文件异常

Mon Mar 29 15:03:38 2021
alter database mount
USER (ospid: 29044): terminating the instance
Mon Mar 29 15:03:42 2021
System state dump requested by (instance=1, osid=29044), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/xff/xff/trace/xff_diag_28961.trc
Instance terminated by USER, pid = 29044

尝试重建ctl

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 29 17:40:17 2021

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

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 1.7704E+10 bytes
Fixed Size                  2235568 bytes
Variable Size            2348811088 bytes
Database Buffers         1.5301E+10 bytes
Redo Buffers               52580352 bytes
SQL> @/tmp/ctl.sql
CREATE CONTROLFILE REUSE DATABASE xff NORESETLOGS  NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01110: data file 249: '/oradata/xff/system03.dbf'

初步判断是由于对方之前恢复导致部分文件resetlogs scn异常,通过bbed进行判断确认

BBED> set file 1
        FILE#           1

BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116     
   ub4 kscnbas                              @116      0x00000001
   ub2 kscnwrp                              @120      0x0000

BBED> set file 249
        FILE#           249

BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116     
   ub4 kscnbas                              @116      0x00000001
   ub2 kscnwrp                              @120      0x0000

通过bbed修改相关值,然后重建控制文件成功,尝试resetlogs库，报ORA-01248错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01248: file 234 was created in the future of incomplete recovery
ORA-01110: data file 234: '/oradata1/xifenfeidata5.DBF'

关于ORA-01248的错误解释

01248, 00000, "file %s was created in the future of incomplete recovery"
// *Cause:  Attempting to do a RESETLOGS open with a file entry in the
//          control file that was originally created after the UNTIL time 
//          of the incomplete recovery.
//          Allowing such an entry may hide the version of the file that 
//          is needed at this time.  The file number may be in use for 
//          a different file which would be lost if the RESETLOGS was allowed.
// *Action: If more recovery is desired then apply redo until the creation
//          time of the file is reached. If the file is not wanted and the
//          same file number is not in use at the stop time of the recovery,
//          then the file can be taken offline with the FOR DROP option.
//          Otherwise a different control file is needed to allow the RESETLOGS.
//          Another backup can be restored and recovered, or a control file can
//          be created via CREATE CONTROLFILE.

大概的意思是文件的创建时间大于文件当前的scn,通过查询确实如此

SQL> select file#,CREATION_CHANGE#,CREATION_TIME from v$datafile_header where file#=234;

           FILE# CREATION_CHANGE# CREATION_
---------------- ---------------- ---------
             234     419298664864 02-AUG-19

SQL> SELECT status,  
  2  to_char(checkpoint_change#,'9999999999999999') "SCN",
  3  to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,
  4  count(*) ROW_NUM
  5  FROM v$datafile_header
  6  GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy
  7  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  SCN               CHECKPOINT_TIME     FUZ          ROW_NUM
------- ----------------- ------------------- --- ----------------
ONLINE       417750848223 2021-02-23 23:50:46 YES                7
ONLINE       417750848223 2021-03-21 11:44:25 NO               396

通过对部分scn进行修改（比如减小创建时间的scn)，然后尝试resetlogs库

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-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 5 with name
"_SYSSMU5_2708889888$" too small
Process ID: 3182
Session ID: 1 Serial number: 3

这个错误比较简单,参考以前的部分文章:在数据库open过程中常遇到ORA-01555汇总数据库open过程遭遇ORA-1555对应sql语句补充,处理之后,数据库open成功

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.7704E+10 bytes
Fixed Size                  2235568 bytes
Variable Size            2348811088 bytes
Database Buffers         1.5301E+10 bytes
Redo Buffers               52580352 bytes
Database mounted.
SQL> alter database open;

Database altered.

本次数据库恢复基本上完成,已经最大限度恢复数据,导出数据到新库,完成恢复任务

发表在 非常规恢复 | 标签为 , , | 评论关闭

ORA-600 16703直接把orachk备份表插入到tab$恢复

发表于 2021 年 3 月 24 日 由 惜分飞

有一个朋友和我说，他们数据库出现了以下错误ORA-600 16703 错误
20210324195416

他们是在虚拟化环境中,可以恢复到上一个快照点,但是主机启动之后,数据库依旧异常,让我们进行处理

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 24 17:04:01 2021

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select count(1) from tab$;

  COUNT(1)
----------
         0

很明显tab$已经被清空,数据库无法正常使用.因为库没有crash,尝试把备份的orachk表插入进来

SQL> insert into tab$ select * from ORACHKB514061BDCB10EBA9CF58F3;

6318 rows created.

SQL> commit;

Commit complete.

SQL> select 'DROP TRIGGER '||owner||'."'||TRIGGER_NAME||'";' from dba_triggers w
here TRIGGER_NAME like 'DBMS_%_INTERNAL% '
  2  union all
  3  select 'DROP PROCEDURE '||owner||'."'||a.object_name||'";' from dba_procedu
res a where a.object_name like 'DBMS_%_INTERNAL% '
  4  union all
  5  select 'drop '||object_type||' '||owner||'.'||object_name||';' from dba_obj
ects where object_name in('DBMS_SUPPORT_DBMONITOR','DBMS_SUPPORT_DBMONITORP');

'DROPTRIGGER'||OWNER||'."'||TRIGGER_NAME||'";'
--------------------------------------------------------------------------------

drop PROCEDURE SYS.DBMS_SUPPORT_DBMONITORP;
drop TRIGGER SYS.DBMS_SUPPORT_DBMONITOR;

SQL> drop PROCEDURE SYS.DBMS_SUPPORT_DBMONITORP;

Procedure dropped.

SQL> drop TRIGGER SYS.DBMS_SUPPORT_DBMONITOR;

Trigger dropped.

SQL> commit;

Commit complete.

SQL>

重启数据库,该故障恢复完成,数据完美恢复0丢失.

发表在 非常规恢复 | 标签为 , , , | 评论关闭