Oracle 11g dataguard main library bad block repair

Ideally, real-time transmission of redo logs is enabled, and the standby database can be used to repair the bad blocks of the main database:

View the DG mode:

Alter database recover managed standby database using current logfile disconnect from session;SQL > select database_role,protection_mode,protection_level from vested database share database-- PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

Main library

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

The data of automatic segment space management starts with the fourth block.

It can be verified by 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

Check file# 6 with dbv before building bad blocks

Main library:

[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)

Prepare the library:

[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)

Delete the backup of the main library:

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

The main library constructs bad blocks:

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 0504 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 log

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

Because there is no backup, try to repair automatically from the standby library:

RMAN > recover datafile 6 block 6531 boot 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 0504 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

= repaired successfully = =

Construct the bad block again and restart the main library:

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: 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)

The database data file is intact:

[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)

Restart the main library:

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 log:

Db_recovery_file_dest_size of 4182 MB is 29.65% used. This is auser-specified limit on the amount ofspace 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)

In the case of real-time application of the log, the bad block of the main library can be repaired automatically.

two。 Change the application method of database log:

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 log: 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 + select > 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: datafile 6:'+ DATA/phub/datafile/llc01.dbf'-- query error

Alter log:

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: datafile 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 2221: 54:06 2015Dumping diagnostic data in directory= [CDMP _ 20150922215406], requested by (instance=1, osid=14390), summary= [instance=1 = 28985] .Tue Sep 2221: 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

Fix method 1 (through RMAN and or from the repository):

[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 startup 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

Repair method 2:

Disable the library, get the latest llc.258.891103925.dbf from the standby database, rename it to llc.258.891103925.dbf.bak and transfer it to the main database

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.

This time, try to enable real-time log transmission to see if it can be repaired:

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

The query still reported an error:

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

Then fix it with 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

Query data (normal)

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

Start the standby library

Summary: if there is no preparation of the database, be sure to back up regularly to ensure the recovery of the database, do not run naked!

= end==