oracle把日志文件全部删除,归档日志文件丢失，数据文件为RECOVER状态如何删除或恢复...

借鉴下:

Problem Description:

====================

This is a recovery scenario in which a datafile in a user tablespace has

been lost or damaged to a point that Oracle cannot recognize it anymore.

Trying to startup the database will result in ORA-1157, ORA-1110, and possibly

an operating system level error such as ORA-7360. Trying to shut down the

database in normal or immediate mode will result in ORA-1116, ORA-1110, and

possibly an operating system level error such as ORA-7368.

This entry contains the solution for this problem.

Solution Description:

=====================

Two approaches are possible:

I. Recreate the tablespace to which the datafile belongs

II. Restore the file from a backup and do media recovery on it

Solution Explanation:

=====================

There are two possible scenarios here:

I. THE USER TABLESPACE CAN BE EASILY RECREATED

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

This means that reasonably recent exports of the objects in the tablespace are

available, or that the tables in the tablespace can be repopulated by running

a script or program, loading the data through SQL*Loader, etc.

In this case, the simplest solution is to offline drop the datafile, drop the

tablespace, recreate it, and then recreate all the objects in it.  The steps

involved are:

1. If the database is down, mount it.

2. Offline drop the datafile.

ALTER DATABASE DATAFILE '' OFFLINE DROP;

3. If the database is at mount point, open it.

4. Drop the user tablespace.

DROP TABLESPACE INCLUDING CONTENTS;

5. Recreate the tablespace.

6. Recreate all the previously existing objects in the tablespace.

II.  THE USER TABLESPACE CANNOT BE EASILY RECREATED

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

In many situations, recreating the user tablespace is impossible or too

laborious.  The solution then is to restore the lost datafile from a backup

and do media recovery on it.  If the database is in NOARCHIVELOG mode, you

will only succeed in recovering the datafile if the redo to be applied to it

is within the range of your online logs.

These are the steps:

1. Restore the lost file from a backup.

2. If the database is down, mount it.

3. Issue the following query:

SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#

FROM V$LOG V1, V$LOGFILE V2

WHERE V1.GROUP# = V2.GROUP# ;

This will list all your online redolog files and their respective

sequence and first change numbers.

4. If the database is in NOARCHIVELOG mode, issue the query:

SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;

If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your

logs, the datafile can be recovered.  Just keep in mind that all the

logs to be applied will be online logs, and move on to step 5.

If the CHANGE# is LESSER than the minimum FIRST_CHANGE# of your logs,

the file cannot be recovered. Your options at this point would be to

restore the most recent        full backup (and thus lose all changes

to the         database since) OR recreate the tablespace as

explained in scenario I.

5. Recover the datafile:

RECOVER DATAFILE ''

6. Confirm each of the  logs that you are prompted for until you

receive the message "Media recovery complete".  If you are prompted for a

non-existing archived log, Oracle probably needs one or more of the

online logs to proceed with the recovery.  Compare the sequence number

referenced in the ORA-280 message with the sequence numbers of your online

logs.  Then enter the full path name of one of the members of the redo group

whose sequence number matches the one you are being asked for.  Keep entering

online logs as requested until you receive the message "Media recovery

complete".

7. If the database is at mount point, open it.