用热备+归档恢复损坏的非系统表空间
来源：程序员人生 发布时间：2014-12-24 08:29:41 阅读次数：3050次
通常，我们都是用RMAN去还原数据文件，再利用归档做恢复，如果没有有效的备份集，但是有热备份生成的文件，那末1样可以进行恢复，这里演示的是非系统表空间对应的数据文件破坏后的恢复。
--首先获得热备份的语句
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⑴1⑵8
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⑴2⑴7 800 20
7499 ALLEN SALESMAN 7698 1981-02⑵0 1600 300 30
7521 WARD SALESMAN 7698 1981-02⑵2 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09⑵8 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⑴9 3000 20
7839 KING PRESIDENT 1981⑴1⑴7 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-05⑵3 1100 20
7900 JAMES CLERK 7698 1981⑴2-03 950 30
7902 FORD ANALYST 7566 1981⑴2-03 3000 20
7934 MILLER CLERK 7782 1982-01⑵3 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.
1共履行了4次update操作，并切换了4第二天志
--查看当前的数据
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980⑴2⑴7 1200 20
7499 ALLEN SALESMAN 7698 1981-02⑵0 2000 300 30
7521 WARD SALESMAN 7698 1981-02⑵2 1650 500 30
7566 JONES MANAGER 7839 1981-04-02 3375 20
7654 MARTIN SALESMAN 7698 1981-09⑵8 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⑴9 3400 20
7839 KING PRESIDENT 1981⑴1⑴7 5400 10
7844 TURNER SALESMAN 7698 1981-09-08 1900 0 30
7876 ADAMS CLERK 7788 1987-05⑵3 1500 20
7900 JAMES CLERK 7698 1981⑴2-03 1350 30
7902 FORD ANALYST 7566 1981⑴2-03 3400 20
7934 MILLER CLERK 7782 1982-01⑵3 1700 10
14 rows selected.
--破坏数据文件
SQL> !
[oracle@ora10g ~]$ cat >> abc.txt << EOF
> 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⑴1⑵9
可以看到6号文件现在是offline状态，需要做恢复
SQL> select * from v$recovery_log;
THREAD# SEQUENCE# TIME
---------- ---------- ----------
ARCHIVE_NAME
----------------------------------------------------------------------------------------------------------------------------------
1 58 2014⑴1⑵9
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_58_b7mbppk8_.arc
1 59 2014⑴1⑵9
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_59_b7mbsb96_.arc
1 60 2014⑴1⑵9
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_60_b7mbt18j_.arc
1 61 2014⑴1⑵9
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_61_b7mbtoy8_.arc
1 62 2014⑴1⑵9
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_62_b7mbw693_.arc
1 63 2014⑴1⑵9
/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: {<RET>=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⑴2⑴7 1200 20
7499 ALLEN SALESMAN 7698 1981-02⑵0 2000 300 30
7521 WARD SALESMAN 7698 1981-02⑵2 1650 500 30
7566 JONES MANAGER 7839 1981-04-02 3375 20
7654 MARTIN SALESMAN 7698 1981-09⑵8 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⑴9 3400 20
7839 KING PRESIDENT 1981⑴1⑴7 5400 10
7844 TURNER SALESMAN 7698 1981-09-08 1900 0 30
7876 ADAMS CLERK 7788 1987-05⑵3 1500 20
7900 JAMES CLERK 7698 1981⑴2-03 1350 30
7902 FORD ANALYST 7566 1981⑴2-03 3400 20
7934 MILLER CLERK 7782 1982-01⑵3 1700 10
14 rows selected.
此时，表空间ZLM及对应的数据文件zlm01.dbf都已顺利地恢复了
生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
