Recovery in NOARCHIVELOG Mode
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. If the database is not already down, do a shutdown abort.
2. Restore the lost file from a backup.
3. Mount the database.
4. 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.
5. 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 6.
If the CHANGE# is LESSER than the minimum FIRST_CHANGE# of your logs,
the file cannot be recovered.
6. Recover the datafile:
RECOVER DATABASE;
7. 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".
8. Open the database.
Note : If the database is running in NO ARCHIVELOG mode then as an added protection from media failure we should have a larger number of redo log groups having larger sized log members.