Backup And Recovery User's Guide-從RMAN開始-恢復單個的資料塊

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