频道栏目
IT货架 > > 正文
用热备+归档恢复损坏的非系统表空间
网友分享于:Jan 1, 1970 8:00:00 AM    来源: IT货架   

标签:oracle   热备份   非系统表空间   损坏   恢复   

通常,我们都是用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 << 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-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: {<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-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都已经顺利地恢复了






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

标签:oracle   热备份   非系统表空间   损坏   恢复   

广告服务联系QQ:1134687142 | 网站地图

版权所有: IT货架- 内容来自互联网,仅供用于技术学习,请遵循相关法律法规. 京ICP备11030978号-1