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

首页 > 代码库 > 用热备+归档恢复损坏的非系统表空间

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

2024-08-05 05:15:10 240人阅读

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

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

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 用热备+归档恢复损坏的非系统表空间

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

看完仍有疑问？有类似问题直接问程序猿