Oracle mistakenly removes DBF remediation measures

Disaster handling methods for Oracle data files being mistakenly deleted

steps:
when the Oracle data file is mistakenly deleted after the processing Method!!
1. Close the database:
sqlplus "/as sysdba";
Sqlplus>shutdown abort;
2. Loading the database
Sqlplus>startup Mount;
3. Delete Invalid database files
sqlplus>alter database datafile '/home/oracle/survey/survey.dbf ' offline drop
sqlplus>alter database datafile '/home/oracle/survey/surveytemp.dbf ' offline drop
4. Open the database
sqlplus>alter database open;
5. Delete invalid table spaces
sqlplus> drop tablespace surevy including contents;
sqlplus> drop tablespace surevytemp including contents;
  

---DTL----

Tablespace gti40_data01 data File gti40_data01.dbf I deleted it.
I'll run the drop tablespace qwer again.
Will error

ORA-01116: Error opening database file 48 o'clock
ORA-01110: Data file: '/DATA/GTI40_DATA01.DBF '
ORA-27041: Unable to open file
Linux error:2: No such file or directory
Additional Information:3

How can I delete this tablespace
Now I can't build this space (online and other urgent!!!!!!!!)

The simple question is so complicated?
You can do this by following these steps:
1.shutdown Immediate
2.startup Mount
3.alter database datafile ' xxx ' offline drop;
4.alter Database Open
5.drop tablespace xxxx including contents and datafiles;

Let's explain the ALTER database datafile. Offline Drop:

In non-archive mode, you can use ... offline drop, in archive mode, using ... offline. This command does not mean to remove the data file from the tablespace completely, but rather to "freeze" the data file: its SCN is not changing, no more data is written to it, but the existing object and data in this file can continue to be used. Therefore, after using this command, by modifying the control file to delete it, the database at startup will report the loss of the file error, and in the data dictionary generates a MISGXXXXX record. Therefore, a data file cannot be deleted directly from the database.

If you need to reduce table space data files, you can use the following methods:

Export contents except table space: Exp ... tablespaces=xxx;

To delete a tablespace using the Include content clause: Drop tablespace xxx include content;

See the table space so that it does not include data files that need to be removed;

All data objects for the import table space.

Re: Completely remove a data file from the tablespace [Reply]
Hehe, before the drop empty datafile of 10g R2, it is virtually impossible to delete a single data file unless you change the data dictionary or delete the tablespace. If you follow the above steps, you check dba_data_files there will be a record of that data file, just identify as d:oracleora92databasemissing00005 such files, In fact, because you delete the data file in the control file, and the data dictionary contains the information of the file, now does not correspond, so identified as this strange file name, indicating that the data file needs to be restored.

when the Oracle data file is mistakenly deleted after the processing Method!!
1. Close the database:
sqlplus "/as sysdba";
Sqlplus>shutdown abort;
2. Loading the database
Sqlplus>startup Mount;
3. Delete Invalid database files
sqlplus>alter database datafile '/home/oracle/survey/survey.dbf ' offline drop
sqlplus>alter database datafile '/home/oracle/survey/surveytemp.dbf ' offline drop
4. Open the database
sqlplus>alter database open;
5. Delete invalid table spaces
sqlplus> drop tablespace surevy including contents;
sqlplus> drop tablespace surevytemp including contents;
   
   
Good:
disaster handling method after the Oracle data file is mistakenly deleted--
tag:oracle datafile data file for accidental deletion of disaster
Environment: Red Hat Linux 7.2 SMP
database: Oracle 8.1.7

cause, a colleague creates a table space and creates a table and finds that the tablespace is not used to delete the data file directly. The operation is as follows:
Create tablespace r_csh_20051001 datafile '/webdata/dbdata/ry/csh20051001.dat ' size 50M autoextend on next 500M Defau Lt Storage (initial 10m NEXT 10m minextents 1 pctincrease 0);
CREATE TABLE R_csh (
useddate char (+) default ',
Cardnumber char (+) default ',
Usernumber char (+) default ',
Credit Number (5) default 0,
Userarea Number (6) default 0,
Cardarea Number (6) default 0,
validdate char (+) Default '
);

Rm/webdata/dbdata/ry/csh20051001.dat
After this operation, the database fails to connect, and the database is discovered to be stopped.
To view the log display for Oracle:

Errors in FILE/ORACLE/ADMIN/WEBDB/BDUMP/CKPT_31638.TRC:
ora-01110:data file: '/webdata/dbdata/ry/csh20051001.dat '
Ora-01115:io Error reading block from file (Block # 1)
ora-27041:unable to open file
Linux error:2: No such file or directory
Additional Information:3
ora-01242:data file suffered media failure:database in Noarchivelog mode
ora-01116:error in opening database file
ora-01110:data file: '/webdata/dbdata/ry/csh20051001.dat '
ora-27041:unable to open file
Linux error:2: No such file or directory
Additional Information:3
Fri Oct 15:31:47 2005
ckpt:terminating instance due to error 1242
Instance terminated by CKPT, PID = 31638
Fri Oct 15:55:09 2005

If you want to restart Oracle, the discovery will not start:

sql> shutdown immediate;
Sql> startup;
ORACLE instance started.

Total System Global area 40104096 bytes
Fixed Size 73888 bytes
Variable Size 23080960 bytes
Database buffers 16777216 bytes
Redo buffers 172032 bytes
Database mounted.
ora-01122:database file failed verification check
ora-01110:data file: '/webdata/dbdata/ry/csh20051001.dat '
ora-01251:unknown file Header Version read for File number

analysis problem:
Because the data file was deleted without being offline, the data in Oracle is inconsistent and therefore failed to start.

Workaround:
Lsnrctl stop
Sqlplus Internal
sql> Shutdown Abort
Sql> Startup Mount
sql> ALTER DATABASE datafile '/webdata/dbdata/ry/csh20051001.dat ' offline drop;
sql> ALTER DATABASE open;
sql> drop tablespace r_csh_20051001;
Lsnrctl start

The contents of the screen output are omitted.

Summary: Oracle data file (datafile) was mistakenly deleted after the method is not restored, only the data file offline after the drop

This is our actual operation record!

Oracle mistakenly removes DBF remediation measures