用熱備+歸檔恢復損壞的非系統表空間
來源：程序員人生 發布時間：2014-12-24 08:29:41 閱讀次數：3255次
通常，我們都是用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都已順利地恢復了
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
