之前实验做的表空间恢复是基于有热备的情况，那么再既没有热备，也没有RMAN备份集的情况下，丢失的表空间数据还能恢复吗？下面来看测试：
--查看现有表空间
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora10g/system01.dbf
/u01/app/oracle/oradata/ora10g/undotbs01.dbf
/u01/app/oracle/oradata/ora10g/sysaux01.dbf
/u01/app/oracle/oradata/ora10g/users01.dbf
/u01/app/oracle/oradata/ora10g/example01.dbf
/u01/app/oracle/oradata/ora10g/zlm01.dbf
/u01/app/oracle/oradata/ora10g/indx02.dbf
7 rows selected.
--创建测试表空间
SQL> create tablespace web datafile '/u01/app/oracle/oradata/ora10g/web01.bb' size 10m;
Tablespace created.
SQL> alter system switch logfile;
System altered.
SQL> conn zlm/zlm
Connected.
SQL> alter user zlm default tablespace web;
User altered.
--创建测试表并开始事务
SQL> create table tbl as select * from scott.emp;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> update tbl set sal=1000;
14 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> update tbl set sal=2000;
14 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> update tbl set sal=3000;
14 rows updated.
SQL> commit;
Commit complete.
SQL> select tablespace_name from dba_tables where table_name='TBL' and owner='ZLM';
TABLESPACE_NAME
------------------------------
WEB
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora10g/system01.dbf
/u01/app/oracle/oradata/ora10g/undotbs01.dbf
/u01/app/oracle/oradata/ora10g/sysaux01.dbf
/u01/app/oracle/oradata/ora10g/users01.dbf
/u01/app/oracle/oradata/ora10g/example01.dbf
/u01/app/oracle/oradata/ora10g/zlm01.dbf
/u01/app/oracle/oradata/ora10g/indx02.dbf
/u01/app/oracle/oradata/ora10g/web01.bb
8 rows selected.
--破坏8号数据文件
SQL> !
[oracle@ora10g ~]$ cp abc.txt /u01/app/oracle/oradata/ora10g/web01.bb
[oracle@ora10g ~]$ exit
exit
SQL> set lin 130 pages 130
SQL> select * from tbl;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 3000 20
7499 ALLEN SALESMAN 7698 1981-02-20 3000 300 30
7521 WARD SALESMAN 7698 1981-02-22 3000 500 30
7566 JONES MANAGER 7839 1981-04-02 3000 20
7654 MARTIN SALESMAN 7698 1981-09-28 3000 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 3000 30
7782 CLARK MANAGER 7839 1981-06-09 3000 10
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7839 KING PRESIDENT 1981-11-17 3000 10
7844 TURNER SALESMAN 7698 1981-09-08 3000 0 30
7876 ADAMS CLERK 7788 1987-05-23 3000 20
7900 JAMES CLERK 7698 1981-12-03 3000 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7934 MILLER CLERK 7782 1982-01-23 3000 10
14 rows selected.
--执行检查点后查询测试表数据
SQL> alter system checkpoint;
System altered.
SQL> select * from tbl;
select * from tbl
*
ERROR at line 1:
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/u01/app/oracle/oradata/ora10g/web01.bb'
--重新创建一个空文件web01.dbf替换掉原来的web01.bb文件
SQL> alter database create datafile '/u01/app/oracle/oradata/ora10g/web01.bb' as '/u01/app/oracle/oradata/ora10g/web01.dbf';
Database altered.
注意，这里使用这种方法来创建一个文件，就相当于用了RMAN的restore来还原出一个数据文件，并对该数据文件进行改名，这里是改成了web01.dbf
SQL> !
[oracle@ora10g ~]$ cd /u01/app/oracle/oradata/ora10g/
[oracle@ora10g ora10g]$ ll
total 1444316
-rw-r----- 1 oracle oinstall 7520256 Nov 30 21:12 control01.ctl
-rw-r----- 1 oracle oinstall 7520256 Nov 30 21:12 control02.ctl
-rw-r----- 1 oracle oinstall 7520256 Nov 30 21:12 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Nov 30 21:08 example01.dbf
-rw-r----- 1 oracle oinstall 172032 Nov 29 20:07 indx01.dbf
-rw-r----- 1 oracle oinstall 20979712 Nov 30 21:08 indx02.dbf
-rw-r----- 1 oracle oinstall 52429312 Nov 30 21:02 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Nov 30 21:02 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Nov 30 21:12 redo03.log
-rw-r----- 1 oracle oinstall 283123712 Nov 30 21:08 sysaux01.dbf
-rw-r----- 1 oracle oinstall 587210752 Nov 30 21:08 system01.dbf
-rw-r----- 1 oracle oinstall 52436992 Nov 29 09:20 temp01.dbf
-rw-r----- 1 oracle oinstall 173023232 Nov 30 21:08 undotbs01.dbf
-rw-r----- 1 oracle oinstall 41951232 Nov 30 21:08 users01.dbf
-rw-r----- 1 oracle oinstall 12 Nov 30 21:06 web01.bb --之前被abc.txt替换后就被破坏了
-rw-r----- 1 oracle oinstall 10493952 Nov 30 21:12 web01.dbf --web01.dbf的大小为10M，与之前的web01.bb一致
-rw-r----- 1 oracle oinstall 52436992 Nov 30 21:08 zlm01.dbf
[oracle@ora10g ora10g]$ exit
exit
SQL> alter tablespace web online;
alter tablespace web online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/u01/app/oracle/oradata/ora10g/web01.dbf'
当把web表空间online时，提示需要做介质恢复
SQL> select * from v$recovery_log;
THREAD# SEQUENCE# TIME
---------- ---------- ----------
ARCHIVE_NAME
----------------------------------------------------------------------------------------------------------------------------------
1 66 2014-11-29
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_30/o1_mf_1_66_b7p55ltn_.arc
1 67 2014-11-30
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_30/o1_mf_1_67_b7p5b9rk_.arc
这里2个归档是用于做恢复的（recover）
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 68 52428800 1 YES INACTIVE 1392188 2014-11-30
2 1 69 52428800 1 YES INACTIVE 1392210 2014-11-30
3 1 70 52428800 1 NO CURRENT 1392221 2014-11-30
由于我们要用到的用于做recover的归档是66、67，这里其实68，和69也已经做了归档，只是与恢复数据无关罢了
--恢复8号文件
SQL> recover datafile 8;
ORA-00279: change 1392041 generated at 11/30/2014 20:59:21 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_30/o1_mf_1_66_%u_.arc
ORA-00280: change 1392041 for thread 1 is in sequence #66
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1392066 generated at 11/30/2014 20:59:30 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_30/o1_mf_1_67_%u_.arc
ORA-00280: change 1392066 for thread 1 is in sequence #67
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_30/o1_mf_1_66_b7p55ltn_.arc' no longer needed
for this recovery
Log applied.
Media recovery complete.
当利用66、67两个归档昨晚recover以后，介质恢复其实就已经做完了
--把表空间online并校验数据
SQL> alter tablespace web online;
Tablespace altered.
SQL> select * from tbl;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 3000 20
7499 ALLEN SALESMAN 7698 1981-02-20 3000 300 30
7521 WARD SALESMAN 7698 1981-02-22 3000 500 30
7566 JONES MANAGER 7839 1981-04-02 3000 20
7654 MARTIN SALESMAN 7698 1981-09-28 3000 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 3000 30
7782 CLARK MANAGER 7839 1981-06-09 3000 10
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7839 KING PRESIDENT 1981-11-17 3000 10
7844 TURNER SALESMAN 7698 1981-09-08 3000 0 30
7876 ADAMS CLERK 7788 1987-05-23 3000 20
7900 JAMES CLERK 7698 1981-12-03 3000 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7934 MILLER CLERK 7782 1982-01-23 3000 10
14 rows selected.
SQL> col file_name for a45
SQL> col tablespace_name for a15
SQL> select file_name,tablespace_name,bytes/1024/1024 as "size(M)" from dba_data_files;
FILE_NAME TABLESPACE_NAME size(M)
--------------------------------------------- --------------- ----------
/u01/app/oracle/oradata/ora10g/web01.dbf WEB 10
/u01/app/oracle/oradata/ora10g/zlm01.dbf ZLM 50
/u01/app/oracle/oradata/ora10g/example01.dbf EXAMPLE 100
/u01/app/oracle/oradata/ora10g/users01.dbf USERS 40
/u01/app/oracle/oradata/ora10g/sysaux01.dbf SYSAUX 270
/u01/app/oracle/oradata/ora10g/undotbs01.dbf UNDOTBS1 165
/u01/app/oracle/oradata/ora10g/system01.dbf SYSTEM 560
/u01/app/oracle/oradata/ora10g/indx02.dbf INDX2 20
8 rows selected.
SQL> select tablespace_name from dba_tables where table_name='TBL' and owner='ZLM';
TABLESPACE_NAME
---------------
WEB
表仍然在表空间WEB上，只是数据文件替换成了web01.dbf，并且数据已经恢复出来了
总结
如果没有对表空间对应的数据文件做过热备份，也没有RMAN备份集，那么还是可以通过归档日志把数据恢复出来的。首先我们要创建一个与原表空间数据文件相关的空文件，相当于做一个还原（restore）操作，然后利用归档日志来进行恢复（recover），恢复数据其实也就是2个步骤，先restore，再recover，我们原来熟悉的restore操作，通常是基于RMAN备份集的，本测试用了一种偷梁换柱的方法，利用控制文件中记录的表空间文件信息，通过重建并替换数据文件名字的方式，做了一个restore操作，然后再通过归档日志来还原表空间数据。
150
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
