Oracle Manual full Restore (v) New tablespace, no backup, datafile deleted

Case 4:recover DataFile

--New table space, no backup, datafile deleted

Recovery steps: From the library, through the v$recover_file to see the files that need to be recovered, because there is no backup, only the data file offline, data file reconstruction, and then recover datafile, online can

(1) Simulation environment

sql> Create Tablespace Cuug

2 datafile '/u01/app/oracle/oradata/anny/cuug01.dbf ' size 10m;

Tablespace created.

Sql> Conn Scott/tiger

Connected.

Sql> CREATE TABLE TB02 (id int) tablespace cuug;

Table created.

sql> INSERT INTO TB02 values (1);

1 row created.

sql> INSERT INTO TB02 values (2);

1 row created.

sql> INSERT into TB02 values (3);

1 row created.

Sql> commit;

Commit complete.

Sql> select * from TB02;

Id

----------

1

2

3

Sql> Conn/as SYSDBA

Connected.

sql> Shutdown Abort

ORACLE instance shut down.

Sql>!

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45395.htm

[ORACLE@SOLARIS10 ~] $rm/u01/app/oracle/oradata/anny/cuug01.dbf--Delete table space

[Oracle@solaris10 ~]$!exp

Export Oracle_sid=anny

[Oracle@solaris10 ~]$!sql

(2) Start database

Sql> Startup

ORACLE instance started.

Total System Global area 314572800 bytes

Fixed Size 1279964 bytes

Variable Size 58722340 bytes

Database buffers 251658240 bytes

Redo buffers 2912256 bytes

Database mounted.

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

Ora-01110:data file: '/u01/app/oracle/oradata/anny/cuug01.dbf '

Sql> Select File#,error from v$recover_file;--find file information that needs to be recovered

file# ERROR

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

FILE not FOUND

(3) Recovery

sql> ALTER DATABASE datafile offline;

Database altered.

sql> ALTER DATABASE open;

Database altered.

--No backups, no restore, only data files "deleted via OS, controlfile files still have datafile records", then recover datafile

sql> ALTER DATABASE Create datafile '/u01/app/oracle/oradata/anny/cuug01.dbf ';--Rebuilding data files

Database altered.

sql> recover datafile 12;--recover data files

Media recovery complete.

Alarm Log information:

ALTER DATABASE RECOVER datafile 12

Wed Mar 21 16:18:20 2012

Media Recovery Start

Wed Mar 21 16:18:20 2012

Recovery of Online Redo log:thread 1 Group 4 Seq 4 Reading mem 0

mem# 0 errs 0:/disk3/oradata/anny/redo04a.log

mem# 1 errs 0:/disk1/oradata/anny/redo04b.log

mem# 2 errs 0:/disk2/oradata/anny/redo04c.log

Wed Mar 21 16:18:20 2012

Recovery of Online Redo log:thread 1 Group 3 Seq 5 Reading mem 0

mem# 0 errs 0:/disk3/oradata/anny/redo03a.log

mem# 1 errs 0:/disk1/oradata/anny/redo03b.log

mem# 2 errs 0:/disk2/oradata/anny/redo03c.log

Wed Mar 21 16:18:20 2012

Media Recovery Complete (Anny)

Completed:alter DATABASE RECOVER datafile 12

(4) Verify

sql> ALTER DATABASE datafile online;

Database altered.

Sql> select * from SCOTT.TB02;

Id

----------

1

2

3