Oracle Rman Full recovery case (iv) Recovery tablespace (open State)

Case 4:

--Recover tablespace (open state)

1) test environment

07:47:00 sql> INSERT INTO EMP1 SELECT * from emp where rownum <3;

2 rows created.

07:47:14 sql> commit;

Commit complete.

07:47:17 sql> SELECT * from EMP1;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

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

7369 SMITH Clerk 7902 17-dec-80 800 20

7499 ALLEN salesman 7698 20-feb-81 1600 300 30

07:47:20 sql> Conn/as SYSDBA

Connected.

07:47:23 sql>

[Oracle@work ~]$ rm/u01/app/oracle/oradata/prod/users01.dbf

This column more highlights: http://www.bianceng.cn/database/Oracle/

07:47:23 sql> alter system flush Buffer_cache;

System altered.

07:47:41 sql> Conn Scott/tiger

Connected.

07:47:44 sql>

07:47:44 sql> SELECT * from EMP1;

SELECT * FROM EMP1

*

ERROR at line 1:

Ora-01116:error in opening database file 2

Ora-01110:data file 2: '/U01/APP/ORACLE/ORADATA/PROD/USERS01.DBF '

Ora-27041:unable to open File

Linux error:2: No such file or directory

Additional Information:3

07:47:48 sql>

2) Restore the table space

Rman> Run {

2> SQL ' ALTER DATABASE datafile 2 offline ';

3> restore tablespace users;

4> Recover tablespace users;

5> SQL ' ALTER DATABASE datafile 2 online ';

6>}

3) Verify

07:47:48 sql> SELECT * from EMP1;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

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

7369 SMITH Clerk 7902 17-dec-80 800 20

7499 ALLEN salesman 7698 20-feb-81 1600 300 30

07:50:31 sql>