Oracle 11g dataguard主库坏块修复

最理想的情况是开启redo日志实时传输，可利用备库来修复主库的坏块：

查看DG模式：

alter database recover managed standby database using current logfile disconnect from session;SQL> select database_role,protection_mode,protection_level from v$database;DATABASE_ROLE PROTECTION_MODE      PROTECTION_LEVEL---------------- -------------------- --------------------PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

主库

SQL> select file_id, block_id, blocks from dba_extents where owner = 'LILC' and segment_name = 'EMP';   FILE_ID   BLOCK_ID  BLOCKS---------- ---------- ---------- 6 6528       8SQL> select min(rowid), max(rowid) from EMP;MIN(ROWID)   MAX(ROWID)------------------ ------------------AAAVrKAAGAAABmDAAA AAAVrKAAGAAABmDAAN

自动段空间管理的数据是从第四个块开始。
可以通过dbms_rowid验证一下。

SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAVrKAAGAAABmDAAA') min_block, DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAVrKAAGAAABmDAAN') max_block from dual; MIN_BLOCK  MAX_BLOCK---------- ----------      6531 6531

在构造坏块前，用dbv检查file# 6

主库：

[oracle@cwogg ~]$ dbv userid=grid/grid file=+DATA/phub/datafile/llc01.dbfDBVERIFY: Release 11.2.0.4.0 - Production on Tue Sep 22 21:09:04 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.DBVERIFY - Verification starting : FILE = +DATA/phub/datafile/llc01.dbfDBVERIFY - Verification completeTotal Pages Examined         : 655360Total Pages Processed (Data) : 7494Total Pages Failing   (Data) : 0Total Pages Processed (Index): 1200Total Pages Failing   (Index): 0Total Pages Processed (Other): 646162Total Pages Processed (Seg)  : 0Total Pages Failing   (Seg)  : 0Total Pages Empty            : 504Total Pages Marked Corrupt   : 0Total Pages Influx           : 0Total Pages Encrypted        : 0Highest block SCN            : 0 (0.0)

备库：

[oracle@dg ~]$ dbv userid=grid/grid file=+DATA/mecbs/datafile/llc.258.891103925DBVERIFY: Release 11.2.0.4.0 - Production on Tue Sep 22 21:11:16 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.DBVERIFY - Verification starting : FILE = +DATA/mecbs/datafile/llc.258.891103925DBVERIFY - Verification completeTotal Pages Examined         : 655360Total Pages Processed (Data) : 7494Total Pages Failing   (Data) : 0Total Pages Processed (Index): 1200Total Pages Failing   (Index): 0Total Pages Processed (Other): 646162Total Pages Processed (Seg)  : 0Total Pages Failing   (Seg)  : 0Total Pages Empty            : 504Total Pages Marked Corrupt   : 0Total Pages Influx           : 0Total Pages Encrypted        : 0Highest block SCN            : 0 (0.0)

把主库的备份删掉：

RMAN> list backup of database;specification does not match any backup in the repository

主库构造坏块：

RMAN> recover datafile 6 block 6531 clear;Starting recover at 22-SEP-15using channel ORA_DISK_1using channel ORA_DISK_2Finished recover at 22-SEP-15RMAN> backup check logical validate datafile 6;Starting backup at 22-SEP-15using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=198 device type=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: SID=11 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00006 name=+DATA/phub/datafile/llc01.dbfchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:35List of Datafiles=================File Status Marked Corrupt Empty Blocks Blocks Examined High SCN---- ------ -------------- ------------ --------------- ----------6    FAILED 0              504          655364          1906190     File Name: +DATA/phub/datafile/llc01.dbf  Block Type Blocks Failing Blocks Processed  ---------- -------------- ----------------  Data       1              7494              Index      0              1200              Other      0              646162          validate found one or more corrupt blocksSee trace file /u01/app/oracle/diag/rdbms/phub/PHUB/trace/PHUB_ora_13272.trc for detailsFinished backup at 22-SEP-15

alert日志

Hex dump of (file 6, block 6531) in trace file /u01/app/oracle/diag/rdbms/phub/PHUB/trace/PHUB_ora_13272.trcCorrupt block relative dba: 0x01801983 (file 6, block 6531)Bad check value found during validationData in bad block: type: 6 format: 2 rdba: 0x01801983 last change scn: 0x0000.0010a27d seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xa27d0601 check value in block header: 0x6bc3 computed block checksum: 0x2230Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataReread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataReread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataReread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataReread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataTue Sep 22 21:22:25 2015Checker run found 1 new persistent data failures

因为没有备份，所以尝试从备库自动修复：

RMAN> recover datafile 6 block 6531;Starting recover at 22-SEP-15using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=198 device type=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: SID=13 device type=DISKfinished standby search, restored 1 blocksstarting media recoverymedia recovery complete, elapsed time: 00:00:01Finished recover at 22-SEP-15RMAN> backup check logical validate datafile 6;Starting backup at 22-SEP-15using channel ORA_DISK_1using channel ORA_DISK_2channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00006 name=+DATA/phub/datafile/llc01.dbfchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:45List of Datafiles=================File Status Marked Corrupt Empty Blocks Blocks Examined High SCN---- ------ -------------- ------------ --------------- ----------6    OK     0              504          655364          1906190     File Name: +DATA/phub/datafile/llc01.dbf  Block Type Blocks Failing Blocks Processed  ---------- -------------- ----------------  Data       0              7494              Index      0              1200              Other      0              646162          Finished backup at 22-SEP-15

==修复成功===

再次构造坏块,重启主库：

RMAN> recover datafile 6 block 6531 clear;Starting recover at 22-SEP-15using channel ORA_DISK_1using channel ORA_DISK_2Finished recover at 22-SEP-15[oracle@cwogg ~]$ dbv userid=grid/grid file=+DATA/phub/datafile/llc01.dbfDBVERIFY: Release 11.2.0.4.0 - Production on Tue Sep 22 21:34:43 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.DBVERIFY - Verification starting : FILE = +DATA/phub/datafile/llc01.dbfPage 6531 is marked corruptCorrupt block relative dba: 0x01801983 (file 6, block 6531)Bad check value found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x01801983 last change scn: 0x0000.0010a27d seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xa27d0601 check value in block header: 0x6bc3 computed block checksum: 0x47c2DBVERIFY - Verification completeTotal Pages Examined         : 655360Total Pages Processed (Data) : 7493Total Pages Failing   (Data) : 0Total Pages Processed (Index): 1200Total Pages Failing   (Index): 0Total Pages Processed (Other): 646162Total Pages Processed (Seg)  : 0Total Pages Failing   (Seg)  : 0Total Pages Empty            : 504Total Pages Marked Corrupt   : 1Total Pages Influx           : 0Total Pages Encrypted        : 0Highest block SCN            : 0 (0.0)

备库数据文件完好：

[oracle@dg ~]$ dbv userid=grid/grid file=+DATA/mecbs/datafile/llc.258.891103925DBVERIFY: Release 11.2.0.4.0 - Production on Tue Sep 22 21:34:43 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.DBVERIFY - Verification starting : FILE = +DATA/mecbs/datafile/llc.258.891103925DBVERIFY - Verification completeTotal Pages Examined         : 655360Total Pages Processed (Data) : 7494Total Pages Failing   (Data) : 0Total Pages Processed (Index): 1200Total Pages Failing   (Index): 0Total Pages Processed (Other): 646162Total Pages Processed (Seg)  : 0Total Pages Failing   (Seg)  : 0Total Pages Empty            : 504Total Pages Marked Corrupt   : 0Total Pages Influx           : 0Total Pages Encrypted        : 0Highest block SCN            : 0 (0.0)

重启主库：

SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area  835104768 bytesFixed Size    2257840 bytesVariable Size  511708240 bytesDatabase Buffers  314572800 bytesRedo Buffers    6565888 bytesDatabase mounted.Database opened.

alert日志：

db_recovery_file_dest_size of 4182 MB is 29.65% used. This is auser-specified limit on the amount of space that will be used by thisdatabase for recovery-related files, and does not reflect the amount ofspace available in the underlying filesystem or ASM diskgroup.Tue Sep 22 21:39:57 2015Hex dump of (file 6, block 6531) in trace file /u01/app/oracle/diag/rdbms/phub/PHUB/trace/PHUB_ora_13853.trcCorrupt block relative dba: 0x01801983 (file 6, block 6531)Bad check value found during buffer readData in bad block: type: 6 format: 2 rdba: 0x01801983 last change scn: 0x0000.0010a27d seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xa27d0601 check value in block header: 0x6bc3 computed block checksum: 0x47c2Reading datafile '+DATA/phub/datafile/llc01.dbf' for corruption at rdba: 0x01801983 (file 6, block 6531)Reread (file 6, block 6531) found same corrupt data (no logical check)Starting background process ABMRTue Sep 22 21:39:57 2015ABMR started with pid=40, OS id=13868 Automatic block media recovery service is active.Automatic block media recovery requested for (file# 6, block# 6531)Tue Sep 22 21:39:58 2015Automatic block media recovery successful for (file# 6, block# 6531)Automatic block media recovery successful for (file# 6, block# 6531)

=======在日志实时应用的情况下可以自动修复主库的坏块====

2.改变备库日志应用方式：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;Database altered.SQL> alter database recover managed standby database disconnect from session;Database altered.RMAN> recover datafile 6 block 6531 clear;Starting recover at 22-SEP-15using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=202 device type=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: SID=11 device type=DISKFinished recover at 22-SEP-15alert日志：Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataReread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataReread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataReread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataReread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataRMAN> backup check logical validate datafile 6;SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area  835104768 bytesFixed Size    2257840 bytesVariable Size  511708240 bytesDatabase Buffers  314572800 bytesRedo Buffers    6565888 bytesDatabase mounted.Database opened.SQL> conn lilc/lilcConnected.SQL> select * from emp;select * from emp              *ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 6, block # 6531)ORA-01110: data file 6: '+DATA/phub/datafile/llc01.dbf'--查询报错

alter日志：

Reading datafile '+DATA/phub/datafile/llc01.dbf' for corruption at rdba: 0x01801983 (file 6, block 6531)Reread (file 6, block 6531) found same corrupt data (no logical check)Errors in file /u01/app/oracle/diag/rdbms/phub/PHUB/trace/PHUB_ora_14390.trc  (incident=28985):ORA-01578: ORACLE data block corrupted (file # 6, block # 6531)ORA-01110: data file 6: '+DATA/phub/datafile/llc01.dbf'Incident details in: /u01/app/oracle/diag/rdbms/phub/PHUB/incident/incdir_28985/PHUB_ora_14390_i28985.trcTue Sep 22 21:54:02 2015Corrupt Block Found         TSN = 7, TSNAME = LLC         RFN = 6, BLK = 6531, RDBA = 25172355         OBJN = 88778, OBJD = 88778, OBJECT = EMP, SUBOBJECT =          SEGMENT OWNER = LILC, SEGMENT TYPE = Table SegmentTue Sep 22 21:54:06 2015Dumping diagnostic data in directory=[cdmp_20150922215406], requested by (instance=1, osid=14390), summary=[incident=28985].Tue Sep 22 21:54:08 2015Sweep [inc][28985]: completedHex dump of (file 6, block 6531) in trace file /u01/app/oracle/diag/rdbms/phub/PHUB/incident/incdir_28985/PHUB_m000_14420_i28985_a.trcCorrupt block relative dba: 0x01801983 (file 6, block 6531)Bad check value found during validationData in bad block: type: 6 format: 2 rdba: 0x01801983 last change scn: 0x0000.0010a27d seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xa27d0601 check value in block header: 0x6bc3 computed block checksum: 0xbacbReread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataReread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataReread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataReread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataReread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt dataTue Sep 22 21:56:03 2015alter database recover datafile list clearCompleted: alter database recover datafile list clear

修复办法一（通过RMAN和或者从备库修复）：

[oracle@cwogg ~]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Tue Sep 22 21:55:49 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: PHUB (DBID=536511065)RMAN> recover datafile 6 block 6531;Starting recover at 22-SEP-15using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=72 device type=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: SID=12 device type=DISKfinished standby search, restored 1 blocksstarting media recoverymedia recovery complete, elapsed time: 00:00:01Finished recover at 22-SEP-15

修复办法二：

停备库，从备库得到最新的llc.258.891103925.dbf，改名为llc.258.891103925.dbf.bak传到主库

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;Database altered.[oracle@dg ~]$ sqlplus  / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 22 22:27:43 2015Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsSQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.

这次尝试把开启日志实时传输，看能否修复：

SQL> alter database recover managed standby database using current logfile disconnect from session;Database altered.

查询仍然报错：

SQL> select * from emp;select * from emp              *ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 6, block # 6531)ORA-01110: data file 6: '+DATA/phub/datafile/llc01.dbf'

那用RMAN修复：

[grid@dg tmp]$ scp LLC.258.891103925 172.16.30.227:/tmp/The authenticity of host '172.16.30.227 (172.16.30.227)' can't be established.RSA key fingerprint is ad:9e:4c:ce:16:45:ff:a2:19:52:e7:dd:d9:39:7b:a8.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added '172.16.30.227' (RSA) to the list of known hosts.grid@172.16.30.227's password: LLC.258.891103925                                       100% 5120MB  28.6MB/s   02:59[root@cwogg ~]# chown oracle:oinstall /tmp/LLC.258.891103925 [root@cwogg ~]# mv /tmp/LLC.258.891103925 /tmp/LLC01.dbf.bak[root@cwogg ~]# ls -l /tmp/LLC01.dbf.bak -rw-r----- 1 oracle oinstall 5368717312 Sep 22 22:15 /tmp/LLC01.dbf.bakRMAN> catalog datafilecopy '/tmp/LLC01.dbf.bak';using target database control file instead of recovery catalogcataloged datafile copydatafile copy file name=/tmp/LLC01.dbf.bak RECID=3 STAMP=891123793RMAN> list copy of database;List of Datafile Copies=======================Key     File S Completion Time Ckp SCN    Ckp Time       ------- ---- - --------------- ---------- ---------------3       6    A 22-SEP-15       1911293    22-SEP-15              Name: /tmp/LLC01.dbf.bak        Tag: TAG20150922T165212RMAN> blockrecover datafile 6 block 6531;Starting recover at 22-SEP-15using channel ORA_DISK_1using channel ORA_DISK_2searching flashback logs for block p_w_picpaths until SCN 1911293finished flashback log search, restored 0 blockschannel ORA_DISK_1: restoring block(s) from datafile copy /tmp/LLC01.dbf.bakstarting media recoverymedia recovery complete, elapsed time: 00:00:01Finished recover at 22-SEP-15

查询数据（正常）

SQL> select count(*) from emp;  COUNT(*)----------14

启动备库

总结：如果没有备库的情况下，一定要定期备份，保证数据库的恢复性，不要裸奔！

=====end======================