Oracle11gdataguard主庫壞塊修復(fù)-創(chuàng)新互聯(lián)

最理想的情況是開啟redo日志實時傳輸，可利用備庫來修復(fù)主庫的壞塊：

目前成都創(chuàng)新互聯(lián)已為近1000家的企業(yè)提供了網(wǎng)站建設(shè)、域名、網(wǎng)絡(luò)空間、網(wǎng)站改版維護(hù)、企業(yè)網(wǎng)站設(shè)計、連江網(wǎng)站維護(hù)等服務(wù)，公司將堅持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

查看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 8 SQL> select min(rowid), max(rowid) from EMP; MIN(ROWID) MAX(ROWID) ------------------ ------------------ AAAVrKAAGAAABmDAAA AAAVrKAAGAAABmDAAN

自動段空間管理的數(shù)據(jù)是從第四個塊開始。
可以通過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

在構(gòu)造壞塊前，用dbv檢查file# 6

主庫：

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

備庫：

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

把主庫的備份刪掉：

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

主庫構(gòu)造壞塊：

RMAN> recover datafile 6 block 6531 clear; Starting recover at 22-SEP-15 using channel ORA_DISK_1 using channel ORA_DISK_2 Finished recover at 22-SEP-15 RMAN> backup check logical validate datafile 6; Starting backup at 22-SEP-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=198 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=11 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=00006 name=+DATA/phub/datafile/llc01.dbf channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 List 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 blocks See trace file /u01/app/oracle/diag/rdbms/phub/PHUB/trace/PHUB_ora_13272.trc for details Finished 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.trc Corrupt block relative dba: 0x01801983 (file 6, block 6531) Bad check value found during validation 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: 0x2230 Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data Tue Sep 22 21:22:25 2015 Checker run found 1 new persistent data failures

因為沒有備份，所以嘗試從備庫自動修復(fù)：

RMAN> recover datafile 6 block 6531; Starting recover at 22-SEP-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=198 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=13 device type=DISK finished standby search, restored 1 blocks starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 22-SEP-15 RMAN> backup check logical validate datafile 6; Starting backup at 22-SEP-15 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00006 name=+DATA/phub/datafile/llc01.dbf channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 List 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

==修復(fù)成功===

再次構(gòu)造壞塊,重啟主庫：

RMAN> recover datafile 6 block 6531 clear; Starting recover at 22-SEP-15 using channel ORA_DISK_1 using channel ORA_DISK_2 Finished recover at 22-SEP-15 [oracle@cwogg ~]$ dbv userid=grid/grid file=+DATA/phub/datafile/llc01.dbf DBVERIFY: Release 11.2.0.4.0 - Production on Tue Sep 22 21:34:43 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = +DATA/phub/datafile/llc01.dbf Page 6531 is marked corrupt Corrupt 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: 0x47c2 DBVERIFY - Verification complete Total Pages Examined : 655360 Total Pages Processed (Data) : 7493 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 1200 Total Pages Failing (Index): 0 Total Pages Processed (Other): 646162 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 504 Total Pages Marked Corrupt : 1 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 0 (0.0)

備庫數(shù)據(jù)文件完好：

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

重啟主庫：

SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 511708240 bytes Database Buffers 314572800 bytes Redo Buffers 6565888 bytes Database mounted. Database opened.

alert日志：

db_recovery_file_dest_size of 4182 MB is 29.65% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Tue Sep 22 21:39:57 2015 Hex dump of (file 6, block 6531) in trace file /u01/app/oracle/diag/rdbms/phub/PHUB/trace/PHUB_ora_13853.trc Corrupt block relative dba: 0x01801983 (file 6, block 6531) Bad check value found during buffer read 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: 0x47c2 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) Starting background process ABMR Tue Sep 22 21:39:57 2015 ABMR 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 2015 Automatic block media recovery successful for (file# 6, block# 6531) Automatic block media recovery successful for (file# 6, block# 6531)

=======在日志實時應(yīng)用的情況下可以自動修復(fù)主庫的壞塊====

2.改變備庫日志應(yīng)用方式：

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-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=202 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=11 device type=DISK Finished recover at 22-SEP-15 alert日志： Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data RMAN> backup check logical validate datafile 6; SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 511708240 bytes Database Buffers 314572800 bytes Redo Buffers 6565888 bytes Database mounted. Database opened. SQL> conn lilc/lilc Connected. 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.trc Tue Sep 22 21:54:02 2015 Corrupt 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 Segment Tue Sep 22 21:54:06 2015 Dumping diagnostic data in directory=[cdmp_20150922215406], requested by (instance=1, osid=14390), summary=[incident=28985]. Tue Sep 22 21:54:08 2015 Sweep [inc][28985]: completed Hex dump of (file 6, block 6531) in trace file /u01/app/oracle/diag/rdbms/phub/PHUB/incident/incdir_28985/PHUB_m000_14420_i28985_a.trc Corrupt block relative dba: 0x01801983 (file 6, block 6531) Bad check value found during validation 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: 0xbacb Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data Reread of blocknum=6531, file=+DATA/phub/datafile/llc01.dbf. found same corrupt data Tue Sep 22 21:56:03 2015 alter database recover datafile list clear Completed: alter database recover datafile list clear

修復(fù)辦法一（通過RMAN和或者從備庫修復(fù)）：

[oracle@cwogg ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Tue Sep 22 21:55:49 2015 Copyright (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-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=72 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=12 device type=DISK finished standby search, restored 1 blocks starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 22-SEP-15

修復(fù)辦法二：

停備庫，從備庫得到最新的llc.258.891103925.dbf，改名為llc.258.891103925.dbf.bak傳到主庫

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. [oracle@dg ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 22 22:27:43 2015 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, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.

這次嘗試把開啟日志實時傳輸，看能否修復(fù)：

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

查詢?nèi)匀粓箦e：

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修復(fù)：

[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)? yes Warning: 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.bak RMAN> catalog datafilecopy '/tmp/LLC01.dbf.bak'; using target database control file instead of recovery catalog cataloged datafile copy datafile copy file name=/tmp/LLC01.dbf.bak RECID=3 STAMP=891123793 RMAN> 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: TAG20150922T165212 RMAN> blockrecover datafile 6 block 6531; Starting recover at 22-SEP-15 using channel ORA_DISK_1 using channel ORA_DISK_2 searching flashback logs for block p_w_picpaths until SCN 1911293 finished flashback log search, restored 0 blocks channel ORA_DISK_1: restoring block(s) from datafile copy /tmp/LLC01.dbf.bak starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 22-SEP-15

查詢數(shù)據(jù)（正常）

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

啟動備庫

總結(jié)：如果沒有備庫的情況下，一定要定期備份，保證數(shù)據(jù)庫的恢復(fù)性，不要裸奔！

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

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價比高”等特點(diǎn)與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。

分享標(biāo)題：Oracle11gdataguard主庫壞塊修復(fù)-創(chuàng)新互聯(lián)
URL分享：http://muchs.cn/article16/dddhgg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站設(shè)計、網(wǎng)站建設(shè)、做網(wǎng)站、網(wǎng)站改版、Google、企業(yè)建站

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容