In non-archival mode, the data file is mistakenly deleted

1. No recovery of data files:

In the afternoon in the operation of the database, accidentally will look at the uncomfortable file deleted, press the return button before remembering that this is an Oracle database data file. Fortunately this database is a test, data file lost relationship is not big, but still surprised a cold sweat, use RM-RF must think twice before.

The following refer to the article on the network to remove the mistakenly deleted data file from the database, so that it does not affect the normal startup of the database.

Deleted data file:/u02/oracle/oradata/sm_space

Belongs to Tablespace: Sm_space

Belongs to User: SM

Note: How to view the current data file name: SQL > select name from V$datafile;

1.1 Stripping the data file:

If the database is not started, start it to the Mount State:

sql> startup Mount;

If the database is running, do the following directly:

sql> ALTER DATABASE datafile '/u02/oracle/oradata/sm_space ' offline drop;

Medium none; Text-align:left; Mso-pagination:widow-orphan; Mso-margin-top-alt:auto; Mso-margin-bottom-alt:auto; Mso-border-alt:solid windowtext 5pt; mso-padding-alt:1.0pt 4.0pt 1.0pt 4.0pt "align=left>/u01/app/oracle/product/10.2.0/mingya/dbs/rhhtest

DataFile offline drop differs from datafile offline:

There is no difference in archive mode, and the non-archive mode must be offline drop.

To open a database:

sql> ALTER DATABASE open;

1.2 Deleting a table space

To view table spaces:

Method One:

Sql> select Tablespace_name,status from Dba_tablespaces;

Tablespace_name STATUS

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

SYSTEM ONLINE

UNDOTBS1 ONLINE

Sysaux ONLINE

padding-right:0cm; Border-top:medium none; padding-left:0cm; padding-bottom:0cm; margin:0cm 0cm 0pt; Border-left:medium none; padding-top:0cm; Border-bottom:medium none; Mso-border-alt:solid windowtext 5pt; mso-padding-alt:1.0pt 1.0pt 1.0pt 1.0pt ">temp ONLINE"

USERS ONLINE

Pa_data ONLINE

Pa_index ONLINE

Sm_space ONLINE

Method Two:

Select T.tablespace_name, round (SUM (bytes/(1024*1024)), 0 ts_size
From Dba_tablespaces T, Dba_data_files D
where t.tablespace_name = D.tablespace_name
Group BY T.tablespace_name;

Tablespace_name ts_size

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

Sysaux 360

UNDOTBS1 145

Sm_space

MINGYA2 7000

USERS &NB

Sp 479

SYSTEM 630

Pa_data 50

Pa_index 50

To delete a table space:

Sql> drop tablespace sm_space including contents;

2. If the data file needs to be restored

2.1 If there is a cold backup:

Enter the Mount state, and then copy the backed-up data files to the destination directory, and then use the command

SQL > ALTER DATABASE open.

If you have a hot backup:

Go to the Mount state, take the data file offline, then copy the backed-up data files to the destination directory, and then use the command

SQL > Recover datafile ' filename '