[20160816]11G dataguard坏块修复.txt

[20160816]11G dataguard坏块修复.txt

--11GR2 不仅仅支持在备库在只读的情况下,日志应用(ACTIVE Data Guard),还提供主备库的坏块修复.自己以前也做过相关测试,
--我记得上次测试的仅仅是主库数据块损坏,没有测试备库的数据块损坏.补充一些测试:

1.环境:
SYS@test> @ ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

DGMGRL> show configuration
Configuration - study
  Protection Mode: MaxPerformance
  Databases:
    test   - Primary database
    testdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

2.建立测试数据块:

create table empx tablespace testmssm as select * fromo emp ;

SCOTT@test> select rowid,empx.* from empx where rownum=1;
ROWID                   EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AABLsNAAJAAAACJAAA       7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

SCOTT@test> @ &r/rowid AABLsNAAJAAAACJAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
    310029          9        137          0  0x2400089           9,137                alter system dump datafile 9 block 137 ;

--//安全起见,备份数据文件.
RMAN> backup datafile 9 format '/data/testtest/testmssm_%U';
Starting backup at 2016-08-16 10:11:44
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle11g/oradata/test/testmssm01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-08-16 10:11:46
channel ORA_DISK_1: finished piece 1 at 2016-08-16 10:11:49
piece handle=/data/testtest/testmssm_bhrdcsr2_1_1 tag=TAG20160816T101145 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2016-08-16 10:11:50

Starting Control File and SPFILE Autobackup at 2016-08-16 10:11:51
piece handle=/u01/app/oracle11g/flash_recovery_area/TEST/autobackup/2016_08_16/o1_mf_s_920023912_cv4xmb2m_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2016-08-16 10:11:55

SCOTT@test> alter system checkpoint;
System altered.

SCOTT@test> alter system flush buffer_cache;
System altered.

3.使用bbed破坏(主库):

BBED> set dba 9,137
        DBA             0x02400089 (37748873 9,137)

BBED> corrupt
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Block marked media corrupt.

--测试:
SCOTT@test> select rowid,empx.* from empx where rownum=1;
select rowid,empx.* from empx where rownum=1
                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 137)
ORA-01110: data file 9: '/u01/app/oracle11g/oradata/test/testmssm01.dbf'

SCOTT@test> select rowid,empx.* from empx where rownum=1;
ROWID                   EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AABLsNAAJAAAACJAAA       7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

--检查主库alert*.log
Tue Aug 16 10:13:11 2016
Errors in file /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_11093_127_0_0_1.trc  (incident=3145807):
ORA-01578: ORACLE data block corrupted (file # 9, block # 137)
ORA-01110: data file 9: '/u01/app/oracle11g/oradata/test/testmssm01.dbf'
Incident details in: /u01/app/oracle11g/diag/rdbms/test/test/incident/incdir_3145807/test_ora_11093_i3145807.trc
Tue Aug 16 10:13:16 2016
Sweep [inc][3145807]: completed
Tue Aug 16 10:13:16 2016
Dumping diagnostic data in directory=[cdmp_20160816101316], requested by (instance=1, osid=11093), summary=[incident=3145807].
Tue Aug 16 10:13:17 2016
Sweep [inc2][3145807]: completed
Checker run found 1 new persistent data failures
Tue Aug 16 10:13:26 2016
Errors in file /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_11093_127_0_0_1.trc  (incident=3145808):
ORA-01578: ORACLE data block corrupted (file # 9, block # 137)
ORA-01110: data file 9: '/u01/app/oracle11g/oradata/test/testmssm01.dbf'
Incident details in: /u01/app/oracle11g/diag/rdbms/test/test/incident/incdir_3145808/test_ora_11093_i3145808.trc
Tue Aug 16 10:13:28 2016
Dumping diagnostic data in directory=[cdmp_20160816101328], requested by (instance=1, osid=11093), summary=[incident=3145808].
Starting background process ABMR
Tue Aug 16 10:13:29 2016
ABMR started with pid=46, OS id=17268
Automatic block media recovery service is active.
Automatic block media recovery requested for (file# 9, block# 137)
Tue Aug 16 10:13:30 2016
Automatic block media recovery successful for (file# 9, block# 137)
Automatic block media recovery successful for (file# 9, block# 137)

4.使用bbed破坏(备库):
BBED> set dba 9,137
        DBA             0x02400089 (37748873 9,137)

BBED> corrupt
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Block marked media corrupt.

--测试:
SCOTT@testdg> select rowid,empx.* from empx where rownum=1;
select rowid,empx.* from empx where rownum=1
                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 137)
ORA-01110: data file 9: '/u01/app/oracle11g/oradata/test/testmssm01.dbf'

SCOTT@testdg> select rowid,empx.* from empx where rownum=1;
ROWID                   EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AABLsNAAJAAAACJAAA       7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

--检查备库alert*.log
Dumping diagnostic data in directory=[cdmp_20160816101617], requested by (instance=1, osid=6960), summary=[incident=152714].
Automatic block media recovery requested for (file# 9, block# 137)
Automatic block media recovery successful for (file# 9, block# 137)
Errors in file /u01/app/oracle11g/diag/rdbms/testdg/testdg/trace/testdg_ora_6960_127_0_0_1.trc  (incident=152715):
ORA-01578: ORACLE data block corrupted (file # 9, block # 137)
ORA-01110: data file 9: '/u01/app/oracle11g/oradata/test/testmssm01.dbf'
Incident details in: /u01/app/oracle11g/diag/rdbms/testdg/testdg/incident/incdir_152715/testdg_ora_6960_i152715.trc
Tue Aug 16 10:16:20 2016
Automatic block media recovery requested for (file# 9, block# 137)
Automatic block media recovery successful for (file# 9, block# 137)
Dumping diagnostic data in directory=[cdmp_20160816101621], requested by (instance=1, osid=6960), summary=[incident=152715].

--仔细检查可以发现如果备库发现问题，不会启动ambr进程,而是通过主库传输
--备库：
$ ps -ef | grep abm[r]

--主库：
$ ps -ef | grep abm[r]
503      17268     1  0 10:13 ?        00:00:00 ora_abmr_test

时间： 2024-07-29 07:06:06

[20160816]11G dataguard坏块修复.txt的相关文章

[20150811]模拟坏块处理.txt

[20150811]模拟坏块处理.txt --如果存在备份,修复坏块还是相对简单的.在11g下: select * from V$DATABASE_BLOCK_CORRUPTION; --在rman下执行: blockrecover corruption list; --如果数据块没有使用,没有分配data_object_id而出现坏块,如何恢复呢?一般采用的方法建立新对象的方法,格式化这个数据块. --具体测试如下: 1.建立测试环境: SCOTT@test> @ver1 PORT_STRIN

oracle中一次dataguard坏块的修复

客户有个11g的active dataguard库,mrp进程停了,看alertlog,可以看到有关ora-7445[kdxlin]的报错: cat alert*.log .... Exception [type:SIGSEOV,Address not mapped to object] [ADDR:0xC] |PC:0x96504C7,kdxlin()+4153][flags: 0x0,count:1] Errors in le /aabb/app/oracle/rdbms/diag/rdbm

Oracle坏块问题处理 Oracle坏块修复 Oracle坏块怎么办

Oracle数据库出现坏块现象是指:在Oracle数据库的一个或多个数据块(一个数据块的容量在创建数据库时由db_block_size参数指定,缺省为8K)内出现内容混乱的现象.由于正常的数据块都有固定的合法内容格式,坏块的出现,导致数据库进程无法正常解析数据块的内容,进而使数据库进程报错乃至挂起,并级联导致整个数据库实例出现异常. 一.坏块分类 物理坏块:也可以称为介质坏块,指的是块格式本身是坏的,块内的数据没有任何意义. 逻辑坏块:指的是块内的数据在逻辑是存在问题.比如说索引块的索引值没有按

oracle中file 1 block 128 corrupted/坏块恢复—system rollback坏块修复

有个数据库file 1 block 128 坏块导致数据库无法启动报错如下 该数据库版本是11.2.0.1,根据我们的经验该block是system rollback 的segment header,以下为我在正常哭查询结果SQL> select file_id,block_id,blocks from dba_extents where segment_name='SYSTEM'    FILE_ID   BLOCK_ID     BLOCKS---------- ---------- ---

oracle坏块修复实例

最近几天发现库里有坏块了,环境是11gR2, linux平台的64位的库.以下是我的修复办法,基于dbms_repair做的在线修复,也可以基于备份rman来修复,archivelog,noarchive log可能修复的方式有所不同.-->首先从alert.log里面发现如下的错误. DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident) ORA-01110: data file 8: '/dbTS2/oracl

使用 DBMS_REPAIR 修复坏块

       对于Oracle数据块物理损坏的情形,在我们有备份的情况下可以直接使用备份来恢复.对于通过备份恢复,Oracel为我们提供了很多种方式,冷备,基于用户管理方式,RMAN方式等等.对于这几种方式我们需要实现基于数据库以及文件级别的恢复.RMAN同时也提供了基于块介质方式的恢复.也就是说我们根本不需要还原数据文件,而是直接从备份文件基于块来提取以实现联机恢复.可参考基于RMAN实现坏块介质恢复(blockrecover) .这是比较理想的情形.如果没有任何备份怎么办?我们可以使用Ora

[20121016]坏块处理以及数据恢复.txt

[20121016]坏块处理以及数据恢复.txt 如果产生坏块,在没有备份的情况下,如何最大程度的恢复信息,减少损失. SQL> select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Produ

[20160722]对象C_OBJ#_INTCOL#有坏块.txt

[20160722]对象C_OBJ#_INTCOL#有坏块.txt --前几天看到的帖子,一直没时间测试,链接如下: http://www.itpub.net/thread-2063836-1-1.html --我以前按照eygle的链接http://www.eygle.com/archives/2012/05/event_38003_c_obj_intcol.html做过测试,测试在11.2.0.2下做的. --通过设置alter system set event='38003 trace n

[20140424]oracle的逻辑坏块.txt

[20140424]oracle的逻辑坏块.txt 今天上午本来想做一个11GR2的Automatic block media repair,链接如下:http://blog.itpub.net/267265/viewspace-1148315/ 但是我遇到一个奇怪的问题,检查和的计算问题: SYS@test> @ver BANNER --------------------------------------------------------------------------------