RMAN可以恢复单个的损坏的数据块。
当RMAN为了备份而对文件执行完全扫描之后,任何损坏的数据块都会列在V$DATABASE_BLOCK__CORRUPTION中。
损坏也会在预警日志、跟踪文件、SQL查询的结果中报告。
练习:恢复数据块步骤
--1. 获得损坏的数据块的块号,首先获得预警日志文件、跟踪文件的位置 sys@TESTDB11>select * from v$diag_info; NAME VALUE ------------------------- ---------------------------------------------------------------------- Diag Enabled TRUE ADR Base /u01/app/oracle ADR Home /u01/app/oracle/diag/rdbms/testdb12/TestDB12 Diag Trace /u01/app/oracle/diag/rdbms/testdb12/TestDB12/trace Diag Alert /u01/app/oracle/diag/rdbms/testdb12/TestDB12/alert Diag Incident /u01/app/oracle/diag/rdbms/testdb12/TestDB12/incident Diag Cdump /u01/app/oracle/diag/rdbms/testdb12/TestDB12/cdump Health Monitor /u01/app/oracle/diag/rdbms/testdb12/TestDB12/hm Default Trace File /u01/app/oracle/diag/rdbms/testdb12/TestDB12/trace/TestDB12_ora_5852.trc
Active Problem Count 0 Active Incident Count 0
11 rows selected. --2. 开启RMAN连接到目标数据库 --3. 执行RECOVER命令,恢复数据块 --3.1 恢复所有损坏的数据块 RMAN> RECOVER CORRUPTION LIST; --3.2 恢复单个的数据块 RMAN> RECOVER DATAFILE 1 BLOCK 233, 235 DATAFILE 2 BLOCK 100 TO 200; |
# 1. 先对数据库进行备份 # 2. 在/home/oracle目录下创建文件damage_datafile.sh sqlplus -s system/system < set echo off set heading off set feed off set verify off
spool rowid.sh select min(dbms_rowid.rowid_block_number(rowid)) from scott.emp; spool off
spool blksize.sh select value from v\$parameter where name ='db_block_size'; spool off
spool file.sh select file_name from dba_data_files where tablespace_name = (select tablespace_name from dba_tables where table_name = 'EMP' and owner = 'SCOTT'); spool off exit stop
FILE=`cat file.sh` BLKSIZ=`cat blksize.sh` START1=`cat rowid.sh`; START2=`expr ${START1} + 1`
dd if=`echo ${FILE}` of=tab1 bs=`echo ${BLKSIZ}` count=`echo ${START1}` dd if=`echo ${FILE}` of=tab2 bs=`echo ${BLKSIZ}` count=1 dd if=`echo ${FILE}` of=tab3 bs=`echo ${BLKSIZ}` skip=${START2}
cat tab1 > file cat tab2 >> file cat tab3 >> file
cp file ${FILE}
# 3. 修改脚本文件的权限并执行它,对数据文件进行破坏 oracle@Redhat55.cuug.net:/home/oracle> chmod u+x damage_datafile.sh oracle@Redhat55.cuug.net:/home/oracle> ./damage_datafile.sh
151
8192
/u01/app/oracle/oradata/TestDB12/users001.dbf 151+0 records in 151+0 records out 1236992 bytes (1.2 MB) copied, 0.022293 seconds, 55.5 MB/s 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 3.3e-05 seconds, 248 MB/s 489+0 records in 489+0 records out 4005888 bytes (4.0 MB) copied, 0.044283 seconds, 90.5 MB/s
# 4. 再次查看emp表,会提示数据文件有坏块 sys@TESTDB12>select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected. # 4.1 感觉好像没有影响,因为数据来自于database buffer cache,所以对buffer cache清空 sys@TESTDB12>alter system flush buffer_cache;
System altered. # 4.2 提示有坏块 sys@TESTDB12>select * from scott.emp * ERROR at line 1: |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1081183/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1081183/