用热备+归档恢复损坏的非系统表空间

通常，我们都是用RMAN去还原数据文件，再利用归档做恢复，如果没有有效的备份集，但是有热备份生成的文件，那么一样可以进行恢复，这里演示的是非系统表空间对应的数据文件损坏后的恢复。 --首先获取热备份的语句 SQL select 'alter tablespace '||tablespace

通常，我们都是用RMAN去还原数据文件，再利用归档做恢复，如果没有有效的备份集，但是有热备份生成的文件，那么一样可以进行恢复，这里演示的是非系统表空间对应的数据文件损坏后的恢复。
--首先获取热备份的语句 SQL> select 'alter tablespace '||tablespace_name|| ' begin backup;'
2 ||chr(10) 3 ||'cp '||file_name||' /u01/' 4 ||chr(10) 5 ||'alter tablespace '||tablespace_name|| ' end backup;' as "script" 6 from dba_data_files where tablespace_name='ZLM';
script -------------------------------------------------------------------------------- alter tablespace ZLM begin backup; cp /u01/app/oracle/oradata/ora10g/zlm01.dbf /u01/ alter tablespace ZLM end backup;

SQL> alter tablespace ZLM begin backup;
SQL> ! [oracle@ora10g ~]$ cp /u01/app/oracle/oradata/ora10g/zlm01.dbf /u01/ [oracle@ora10g ~]$ exit exit
SQL> alter tablespace ZLM end backup;
--验证热备已经产生 SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- ---------- 1 NOT ACTIVE 0 2 NOT ACTIVE 0 3 NOT ACTIVE 0 4 NOT ACTIVE 0 5 NOT ACTIVE 0 6 NOT ACTIVE 1340174 2014-11-28
6 rows selected.
--连接到测试用户开始执行事务 SQL> conn zlm/zlm Connected. SQL> create table emp as select * from scott.emp;
Table created.
SQL> set lin 130 SQL> set pages 130 SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7902 FORD ANALYST 7566 1981-12-03 3000 20 7934 MILLER CLERK 7782 1982-01-23 1300 10
14 rows selected.
SQL> update emp set sal=sal+100;
14 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> update emp set sal=sal+100;
14 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> update emp set sal=sal+100;
14 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> update emp set sal=sal+100;
14 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
一共执行了4次update操作，并切换了4次日志
--查看当前的数据 SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 1200 20 7499 ALLEN SALESMAN 7698 1981-02-20 2000 300 30 7521 WARD SALESMAN 7698 1981-02-22 1650 500 30 7566 JONES MANAGER 7839 1981-04-02 3375 20 7654 MARTIN SALESMAN 7698 1981-09-28 1650 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 3250 30 7782 CLARK MANAGER 7839 1981-06-09 2850 10 7788 SCOTT ANALYST 7566 1987-04-19 3400 20 7839 KING PRESIDENT 1981-11-17 5400 10 7844 TURNER SALESMAN 7698 1981-09-08 1900 0 30 7876 ADAMS CLERK 7788 1987-05-23 1500 20 7900 JAMES CLERK 7698 1981-12-03 1350 30 7902 FORD ANALYST 7566 1981-12-03 3400 20 7934 MILLER CLERK 7782 1982-01-23 1700 10
14 rows selected.
--破坏数据文件 SQL> ! [oracle@ora10g ~]$ cat >> abc.txt abc > efg > hij > EOF [oracle@ora10g ~]$ cat abc.txt abc efg hij [oracle@ora10g ~]$ cp abc.txt /u01/app/oracle/oradata/ora10g/zlm01.dbf [oracle@ora10g ~]$ cat /u01/app/oracle/oradata/ora10g/zlm01.dbf abc efg hij [oracle@ora10g ~]$
--切换日志3次后继续执行查看 SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from emp; select * from emp * ERROR at line 1: ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'
SQL> ! [oracle@ora10g ~]$ cp /u01/zlm01.dbf /u01/app/oracle/oradata/ora10g/zlm01.dbf [oracle@ora10g ~]$ exit exit
SQL> select * from emp; select * from emp * ERROR at line 1: ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'
把原来热备生成的文件替换掉损坏的6号文件，依然提示无法读取
--把故障文件offline后再online SQL> alter database datafile 6 offline;

Database altered.
SQL> alter database datafile 6 online; alter database datafile 6 online * ERROR at line 1: ORA-01113: file 6 needs media recovery ORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'
此时，会提示6号文件需要做介质恢复
SQL> col error for a10 SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- ---------- ---------- ---------- 6 OFFLINE OFFLINE 1385889 2014-11-29
可以看到6号文件现在是offline状态，需要做恢复
SQL> select * from v$recovery_log;
THREAD# SEQUENCE# TIME ---------- ---------- ---------- ARCHIVE_NAME ---------------------------------------------------------------------------------------------------------------------------------- 1 58 2014-11-29 /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_58_b7mbppk8_.arc
1 59 2014-11-29 /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_59_b7mbsb96_.arc
1 60 2014-11-29 /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_60_b7mbt18j_.arc
1 61 2014-11-29 /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_61_b7mbtoy8_.arc
1 62 2014-11-29 /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_62_b7mbw693_.arc
1 63 2014-11-29 /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_63_b7mc54hm_.arc

6 rows selected.
v$recovery_log这个视图中查询到的，都是恢复需要用到的归档日志文件
SQL> recover datafile 6; ORA-00279: change 1385889 generated at 11/29/2014 18:46:26 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_58_%u_.arc ORA-00280: change 1385889 for thread 1 is in sequence #58

Specify log: {=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 1387492 generated at 11/29/2014 19:15:34 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_59_%u_.arc ORA-00280: change 1387492 for thread 1 is in sequence #59 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_58_b7mbppk8_.arc' no longer needed for this recovery

ORA-00279: change 1387536 generated at 11/29/2014 19:16:58 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_60_%u_.arc ORA-00280: change 1387536 for thread 1 is in sequence #60 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_59_b7mbsb96_.arc' no longer needed for this recovery

ORA-00279: change 1387553 generated at 11/29/2014 19:17:21 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_61_%u_.arc ORA-00280: change 1387553 for thread 1 is in sequence #61 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_60_b7mbt18j_.arc' no longer needed for this recovery

ORA-00279: change 1387562 generated at 11/29/2014 19:17:41 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_62_%u_.arc ORA-00280: change 1387562 for thread 1 is in sequence #62 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_61_b7mbtoy8_.arc' no longer needed for this recovery

ORA-00279: change 1387587 generated at 11/29/2014 19:18:30 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_63_%u_.arc ORA-00280: change 1387587 for thread 1 is in sequence #63 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_62_b7mbw693_.arc' no longer needed for this recovery

Log applied. Media recovery complete.
当把v$recovery_log中列出的5个归档日志全部应用后，介质恢复完成
--再次把6号文件online SQL> alter database datafile 6 online;

Database altered.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 1200 20 7499 ALLEN SALESMAN 7698 1981-02-20 2000 300 30 7521 WARD SALESMAN 7698 1981-02-22 1650 500 30 7566 JONES MANAGER 7839 1981-04-02 3375 20 7654 MARTIN SALESMAN 7698 1981-09-28 1650 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 3250 30 7782 CLARK MANAGER 7839 1981-06-09 2850 10 7788 SCOTT ANALYST 7566 1987-04-19 3400 20 7839 KING PRESIDENT 1981-11-17 5400 10 7844 TURNER SALESMAN 7698 1981-09-08 1900 0 30 7876 ADAMS CLERK 7788 1987-05-23 1500 20 7900 JAMES CLERK 7698 1981-12-03 1350 30 7902 FORD ANALYST 7566 1981-12-03 3400 20 7934 MILLER CLERK 7782 1982-01-23 1700 10
14 rows selected.
此时，表空间ZLM及对应的数据文件zlm01.dbf都已经顺利地恢复了