Oracle 11g数据库恢复：场景10：新建表空间没有备份-CSDN博客

场景10：新建表空间没有备份但从数据文件创建开始一直到当前所有的日志都存在

sys@TESTDB11>select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

ROTBS

NEWUNDOTBS

8 rows selected.

--创建新的表空间newts

sys@TESTDB11>create tablespace newts datafile '/u01/app/oracle/oradata/TestDB11/newts01.dbf' size 20m;

Tablespace created.

--在新表空间中创建一个表

scott@TESTDB11>create table emp_newts tablespace newts as select * from emp;

Table created.

scott@TESTDB11>exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@S1011:/export/home/oracle]$

--文件损坏

sys@TESTDB11>!rm /u01/app/oracle/oradata/TestDB11/newts01.dbf

--清除缓存

sys@TESTDB11>alter system flush buffer_cache;

System altered.

--查询报错

[oracle@S1011:/export/home/oracle]$ sqlplus scott/scott

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 10 13:41:15 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

scott@TESTDB11>select * from emp_newts;

select * from emp_newts

ERROR at line 1:

ORA-01116: error in opening database file 8

ORA-01110: data file 8: '/u01/app/oracle/oradata/TestDB11/newts01.dbf'

ORA-27041: unable to open file

Solaris-AMD64 Error: 2: No such file or directory

Additional information: 3

--脱机

sys@TESTDB11>alter database datafile 8 offline;

Database altered.

--创建文件,相当于还原（不需要指定文件大小和位置，因为控制文件中有记录)

sys@TESTDB11>alter database create datafile '/u01/app/oracle/oradata/TestDB11/newts01.dbf';

Database altered.

--介质恢复

sys@TESTDB11>recover datafile 8;

Media recovery complete.

--联机

sys@TESTDB11>alter database datafile 8 online;

Database altered.

--执行查询,确认已经得到恢复

scott@TESTDB11>select * from emp_newts;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 1800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 6000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 2600 10

14 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17013648/viewspace-1153192/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17013648/viewspace-1153192/