程序员人生 网站导航

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

栏目：数据库应用时间：2014-12-24 08:29:41
通常，我们都是用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都已顺利地恢复了






------分隔线----------------------------

上一篇 JSP表单填写验证---JSP+JavaBean

下一篇 因添加两个webservice引用时提示：{"无法加载协定为“sms.WebServiceSoap”的终结点配置部分，找到了该协定的多个终结点配置。请

------分隔线----------------------------

最新技术推荐

1cassandra 3.x官方文档(5)---探测器 2[置顶] Aerospike数据库实战(四) -- Aerospike 安装部署 3Oracle 12c for GIS解决方案 4[置顶] Aerospike数据库实战(五) -- Aerospike C Client 开发 5MySQL Group Replication [Single-Primary Mode] 详细搭建部署过程 6mysql进阶(三十)MySQL Workbench出错KERNELBASE.dll停止工作 + MySQL服务消失 + 服务启动失败解决方法 7[置顶] Aerospike数据库实战(六) -- 踩坑&经验总结