Disaster handling method for Oracle data files after being mistakenly deleted ____oracle

010-06-18 17:47
Disaster handling method after the Oracle data file was mistakenly deleted
Key:oracle data File (datafile) is mistakenly deleted after the method is not restored, only the data file offline drop off, and delete the entire table space.
Steps
How to handle an Oracle data file after it has been mistakenly deleted.
1. Close the database:
?? Sqlplus "/as sysdba";
?? Sqlplus>shutdown abort;
2. Loading 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 space
?? Sqlplus> drop tablespace surevy including contents;
?? Sqlplus> drop tablespace surevytemp including contents;
??

---DTL----

Table Space GTI40_DATA01 data file gti40_data01.dbf I deleted it.
I run the drop tablespace qwer again
Will complain

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 (wait!!!!!!!! on line)

Simple questions are so complicated.
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 me explain ALTER DATABASE datafile ... offline drop:

In non-archive mode, you can use ... offline drop, in archive mode, use ... offline. The meaning of this command is not to delete the data file from the tablespace, but to "freeze" the data file: its SCN does not change and no more data is written to it; but the existing objects and data in this file can still be used. So after using this command, by modifying the control file to delete it, the database at startup will report the error of file loss, and will produce a misgxxxxx record in the data dictionary. As a result, a data file cannot be deleted directly from the database.

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

Export in addition to tablespace content: Exp ... tablespaces=xxx;

Use the Include content clause to remove tablespace: Drop tablespace xxx include content;

Re-view 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: Remove a data file from the tablespace completely [reply]
Oh, 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 steps above, you'll have to check dba_data_files there's definitely a record of that data file, just a document labeled D:oracleora92databasemissing00005. In fact, because you delete the data file in the control file, and the data dictionary contains the information of the file, and now does not correspond, so the identity of this strange file name, indicating that the data file needs to be restored.

How to handle an Oracle data file after it has been mistakenly deleted.
1, close the database:
?? sqlplus "/as sysdba";
??? sqlplus>shutdown Abort;
2, Mount Database
?? sqlplus>startup mount;< br> 3, delete invalid database file
?? Sqlplus>alter db datafile '/home/oracle/survey/survey.dbf ' offline Drop
?? sqlplus& Gt;alter database datafile '/home/oracle/survey/surveytemp.dbf ' offline drop
4, open databases
Sqlplus>alter db Open
5, delete invalid Tablespace
?? sqlplus> drop tablespace surevy including contents
?? sqlplus> drop Tablespace Surevytem P including contents;
??
??
Good:
The disaster-handling method after the Oracle data file was mistakenly deleted-
Tag:oracle??? datafile??? Data file??? mistakenly deleted??? disaster???????????????????????????????? ??????????
Environment: Red Hat Linux 7.2 SMP
Database: Oracle 8.1.7

Cause, a colleague created a table space and created a table and found that the table space was 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 default St Orage (initial 10m NEXT 10m minextents 1 pctincrease 0);
CREATE TABLE R_csh (
????? Useddate?? Char default ',
????? Cardnumber char (=) Default ',
????? Usernumber char default ',
????? Credit Suisse???? 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 failed to connect, and the database was found to have stopped.
To view the log display of 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 63
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 21 15:31:47 2005
Ckpt:terminating instance due to error 1242
Instance terminated by CKPT, PID = 31638
Fri Oct 21 15:55:09 2005

To reboot the 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 63

Analyze the problem:
Since the data file was removed without being offline, the Oracle data was 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 screen output is omitted.

Summary: Oracle data file (datafile) has been mistakenly deleted without recovery, only the data file offline drop off

This is our actual operation record!