1.验证数据文件
[oracle@oracle-slave ~]$ dbv file=/oradisk/oradata/iddbsvr/system01.dbf DBVERIFY: Release 10.2.0.1.0 - Production on Wed Sep 18 07:38:17 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.DBVERIFY - Verification starting : FILE = /oradisk/oradata/iddbsvr/system01.dbfDBV-00200: Block, dba 4195962, already ed corruptedDBV-00200: Block, dba 4195978, already ed corrupted
DBVERIFY - Verification completeTotal Pages Examined : 81920
Total Pages Processed (Data) : 49691
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 12483
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1821
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 17925
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Highest block SCN : 254626859 (0.254626859)
我这个数据库的的system01.dbf文件有坏块,启动使用都没什么异常,查询表都没问题。再创建物化视图时才知道有错误。
2.创建一个异库表的本地物化视图时出错3.查询业务系统运行日志,错误从做备份策略当天开始就出现了错误
我查了日志,从我做了备份以后开始就有错误了,rman的备份也都是成功的。这些备份能否用户恢复system表空间。日志显示9月4日开始就有错误
我是在3号晚上做得备份,备份之前把所有数据文件做了物理拷贝,我刚测试了物理拷贝的system01.dbf没问题,结果如下:
[oracle@oracle-slave iddbsvr]$ dbv file=../../oradata_20130903/iddbsvr/system01.dbf DBVERIFY: Release 10.2.0.1.0 - Production on Wed Sep 18 08:00:24 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.DBVERIFY - Verification starting : FILE = ../../oradata_20130903/iddbsvr/system01.dbf
DBVERIFY - Verification completeTotal Pages Examined : 81920
Total Pages Processed (Data) : 49690
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 12483
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1821
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 17926
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 252052428 (0.252052428)
求指点一个安全的恢复方法,业务系统白天不能关闭,目前使用没发现其他异常,是不是应该早停止业务系统来做数据库的修复?数据库oracle异常dba备份
[oracle@oracle-slave ~]$ dbv file=/oradisk/oradata/iddbsvr/system01.dbf DBVERIFY: Release 10.2.0.1.0 - Production on Wed Sep 18 07:38:17 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.DBVERIFY - Verification starting : FILE = /oradisk/oradata/iddbsvr/system01.dbfDBV-00200: Block, dba 4195962, already ed corruptedDBV-00200: Block, dba 4195978, already ed corrupted
DBVERIFY - Verification completeTotal Pages Examined : 81920
Total Pages Processed (Data) : 49691
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 12483
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1821
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 17925
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Highest block SCN : 254626859 (0.254626859)
我这个数据库的的system01.dbf文件有坏块,启动使用都没什么异常,查询表都没问题。再创建物化视图时才知道有错误。
2.创建一个异库表的本地物化视图时出错3.查询业务系统运行日志,错误从做备份策略当天开始就出现了错误
我查了日志,从我做了备份以后开始就有错误了,rman的备份也都是成功的。这些备份能否用户恢复system表空间。日志显示9月4日开始就有错误
我是在3号晚上做得备份,备份之前把所有数据文件做了物理拷贝,我刚测试了物理拷贝的system01.dbf没问题,结果如下:
[oracle@oracle-slave iddbsvr]$ dbv file=../../oradata_20130903/iddbsvr/system01.dbf DBVERIFY: Release 10.2.0.1.0 - Production on Wed Sep 18 08:00:24 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.DBVERIFY - Verification starting : FILE = ../../oradata_20130903/iddbsvr/system01.dbf
DBVERIFY - Verification completeTotal Pages Examined : 81920
Total Pages Processed (Data) : 49690
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 12483
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1821
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 17926
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 252052428 (0.252052428)
求指点一个安全的恢复方法,业务系统白天不能关闭,目前使用没发现其他异常,是不是应该早停止业务系统来做数据库的修复?数据库oracle异常dba备份
刚做了一个实验:
1)rman备份
RMAN> backup database;Starting backup at 18-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oracle/oradata/bj/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/bj/sysaux01.dbf
input datafile file number=00005 name=/u01/oracle/oradata/bj/example01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/bj/undotbs01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/bj/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-SEP-13
channel ORA_DISK_1: finished piece 1 at 18-SEP-13
piece handle=/oracle/rman/BJ_1071506218_10_1_20130918.bak tag=TAG20130918T013200 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 18-SEP-13
channel ORA_DISK_1: finished piece 1 at 18-SEP-13
piece handle=/oracle/rman/BJ_1071506218_11_1_20130918.bak tag=TAG20130918T013200 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-SEP-13
2)先把system表空间搞出坏块
ORA-01578: ORACLE data block corrupted (file # 1, block # 146)
ORA-01110: data file 1: '/u01/oracle/oradata/bj/system01.dbf'
Process ID: 3672
Session ID: 1 Serial number: 5
RMAN> blockrecover datafile 1 block 146;Starting recover at 18-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISKchannel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00001
channel ORA_DISK_1: reading from backup piece /oracle/rman/BJ_1071506218_10_1_20130918.bak
channel ORA_DISK_1: piece handle=/oracle/rman/BJ_1071506218_10_1_20130918.bak tag=TAG20130918T013200
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:25starting media recovery
media recovery complete, elapsed time: 00:00:01Finished recover at 18-SEP-13我这是模拟你的system表空间坏块 为了保险起见你可以现在测试库恢复试一试,这种方法。
2. 定位坏块的内容,考虑重建坏块对象,如果是index rebuild
---------------------------------------------------------------
qq号:107644445
BLOG:http://www.xifenfei.com
Oracle instance startedTotal System Global Area 2248146944 bytesFixed Size 1220580 bytes
Variable Size 754974748 bytes
Database Buffers 1476395008 bytes
Redo Buffers 15556608 bytesRMAN> alter database mount;database mountedRMAN> blockrecover datafile 1 block 1674;Starting blockrecover at 18-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=320 devtype=DISKchannel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00001
channel ORA_DISK_1: reading from backup piece /oradisk/oradiskbak/iddbsvr/rmanbak/databak/0/data.IDDBSVR.live.0.826164326.1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/oradisk/oradiskbak/iddbsvr/rmanbak/databak/0/data.IDDBSVR.live.0.826164326.1 tag=TAG20130915T020145
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:08
failover to previous backupchannel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00001
channel ORA_DISK_1: reading from backup piece /oradisk/oradiskbak/iddbsvr/rmanbak/databak/4/data.IDDBSVR.live.0.825905120.1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/oradisk/oradiskbak/iddbsvr/rmanbak/databak/4/data.IDDBSVR.live.0.825905120.1 tag=TAG20130912T020159
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:08
failover to previous backupchannel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00001
channel ORA_DISK_1: reading from backup piece /oradisk/oradiskbak/iddbsvr/rmanbak/databak/3/data.IDDBSVR.live.0.825216917.1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/oradisk/oradiskbak/iddbsvr/rmanbak/databak/3/data.IDDBSVR.live.0.825216917.1 tag=TAG20130904T025126
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:16
failover to previous backupRMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 09/18/2013 22:11:08
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 1 found to restore----------------------------------------------------------------------------
从备份一开始,system表中间就有坏块,好像没办法从备份中做块恢复。
将原来dbv验证没坏块的物理拷贝,还原以后就有坏块。坏块数据已经被加到备份里了?
----------------------------------------------
RMAN> restore datafile 1;Starting restore at 18-SEP-13
using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradisk/oradata/iddbsvr/system01.dbf
channel ORA_DISK_1: reading from backup piece /oradisk/oradiskbak/iddbsvr/rmanbak/databak/0/data.IDDBSVR.live.0.826164326.1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradisk/oradiskbak/iddbsvr/rmanbak/databak/0/data.IDDBSVR.live.0.826164326.1 tag=TAG20130915T020145
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 18-SEP-13RMAN> recover datafile 1;Starting recover at 18-SEP-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /oradisk/oradata/iddbsvr/system01.dbf
channel ORA_DISK_1: reading from backup piece /oradisk/oradiskbak/iddbsvr/rmanbak/databak/2/data.IDDBSVR.live.1.826336975.1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradisk/oradiskbak/iddbsvr/rmanbak/databak/2/data.IDDBSVR.live.1.826336975.1 tag=TAG20130917T020104
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16starting media recovery
media recovery complete, elapsed time: 00:00:18Finished recover at 18-SEP-13RMAN> alter database open;database openedRMAN> exit
Recovery Manager complete.
[oracle@oracle-slave iddbsvr]$ dbv file=/oradisk/oradata/iddbsvr/system01.dbfDBVERIFY: Release 10.2.0.1.0 - Production on Wed Sep 18 22:31:54 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.DBVERIFY - Verification starting : FILE = /oradisk/oradata/iddbsvr/system01.dbfDBV-00200: Block, dba 4195962, already ed corruptedDBV-00200: Block, dba 4195978, already ed corrupted
DBVERIFY - Verification completeTotal Pages Examined : 81920
Total Pages Processed (Data) : 49691
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 12483
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1821
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 17925
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Highest block SCN : 254705434 (0.254705434)
----------------------------------------------
使用restore datafile 1; 以后 system01.dbf就有坏块。
我除了导出全库,重做实例意外还有办法吗?
SYS.I_JOB_NEXT 可以rebuild INDEX
SYS.I_JOB_NEXT 是否可以重建,需要考虑下
整体来说,这两个东西问题不大
添加了应有的user_jobs.
验证system01.dbf没有坏块。
但是使用
SQL> select * from V$DATABASE_BLOCK_CORRUPTION; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
1 1674 1 252052885 LOGICAL
1 1658 1 252052881 LOGICAL还有有一样的坏块结果
这个需要rman重新扫描,才会除掉