Oracle Rman Full Recovery case (v) No backup for new tablespace

Case 5:

--New table space is not backed up, datafile is corrupted

1) test environment

07:52:16 sql> Create tablespace LX

07:52:24 2 datafile '/u01/app/oracle/oradata/prod/lx01.dbf ' size 10m;

Tablespace created.

07:52:38 sql> Conn Scott/tiger

Connected.

07:52:40 sql>

07:52:40 sql> CREATE TABLE lx01 (id int) tablespace lx;

Table created.

07:52:49 sql> INSERT INTO lx01 values (1);

1 row created.

07:52:55 sql> INSERT INTO lx01 values (2);

1 row created.

07:52:57 sql> INSERT INTO LX01 values (3);

1 row created.

07:52:59 sql> commit;

Commit complete.

07:53:00 sql> SELECT * from lx01;

Id

----------

1

2

3

07:53:03 sql>

07:53:03 sql> Conn/as SYSDBA

Connected.

07:53:16 sql>

07:53:16 sql> shutdown Abort

ORACLE instance shut down.

07:53:19 sql>

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

2) Start database

07:53:19 sql> Startup

ORACLE instance started.

Total System Global area 314572800 bytes

Fixed Size 1219184 bytes

Variable Size 79693200 bytes

Database buffers 230686720 bytes

Redo buffers 2973696 bytes

Database mounted.

Ora-01157:cannot identify/lock data file 10-see DBWR trace file

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

Ora-01110:data file: '/u01/app/oracle/oradata/prod/lx01.dbf '

07:54:15 sql> Select File#,error from V$recover_file;

file# ERROR

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

Ten FILE not FOUND

07:54:25 sql>

3) Recovery using Rman

Rman> Run {

2> SQL ' ALTER DATABASE datafile offline ';

3> ALTER DATABASE open;

4> restore datafile 10;

5> recover datafile 10;

6> SQL ' ALTER DATABASE datafile online ';

7>}

Using target database control file instead of recovery catalog

SQL Statement:alter database datafile offline

Database opened

Starting restore at 18-aug-11

Allocated Channel:ora_disk_1

Channel ora_disk_1:sid=154 Devtype=disk

Creating datafile fno=10 name=/u01/app/oracle/oradata/prod/lx01.dbf; automatic creation of new LX01.DBF files

Restore not done; All files readonly, offline, or already restored

Finished restore at 18-aug-11

Starting recover at 18-aug-11

Using channel Ora_disk_1

Starting Media recovery

Media recovery complete, elapsed time:00:00:01

Finished recover at 18-aug-11

SQL Statement:alter Database datafile online

Rman>