Option#1: Recover the deleted .dbf file from Linux process in-memory location
I you have accidentally deleted the Oracle database .dbf files when database is Open, then you can recover the deleted dbf file from Linux in-memory held by the Oracle DB process. This is possible because when you delete a file in Linux only inode of it is deleted but processes that are already connected to the file will remain using it via already opened file handle. This file will be available until the running process is active.
Find the DB writer process:
[root@ProdDB01 /]# ps -edf | grep dbw
oracle 29245 1 0 Mar20 ? 00:00:00 ora_dbw0_inst3
root 21439 18560 0 10:29 pts/1 00:00:00 grep dbw
[root@ProdDB01 /]#
File the file descriptor to the deleted file:
[root@ProdDB01 /]# ls -l /proc/29245/fd | grep system01ins3
lrwx—— 1 oracle dba 64 Mar 26 14:02 66 -> /var/tmp/system01ins3.dbf (deleted)
[root@ProdDB01 /]# ls -l /proc/29245/fd/66 (below you see it has symlink to path in /var/tmp/)
lrwx—— 1 oracle dba 64 Mar 26 14:02 /proc/29245/fd/66 -> /var/tmp/system01ins3.dbf (deleted)
[root@ProdDB01 /]#
Once you have identified the deleted .dbf file, take the running DB into Read only mode,
SQL> alter tablespace My_DBSpace read only;
Tablespace altered.
Then copy over the by removing the symlink, and cp.
[root@ProdDB01 /]# rm /var/tmp/system01ins3.dbf
[root@ProdDB01 /]# ls: /var/tmp/system01ins3.dbf: No such file or directory
[root@ProdDB01 /]# cp -p /proc/29245/fd/66 /var/tmp/system01ins3.dbf
Then make the DB read and write mode:
SQL> alter tablespace My_DBSpace read write;
Tablespace altered.
Reference:
How to Recover Deleted Oracle Datafiles with No Downtime
Recover an accidentally deleted file when the database is still open.
Oracle steps: To recover from loss of an active online log group in NOARCHIVELOG mode
Losing an Active Online Redo Log Group
If the database is still running and the lost active redo log is not the current log, then issue the ALTER SYSTEM CHECKPOINT statement. If successful, then the active redo log becomes inactive, and you can follow the procedure in "Losing an Inactive Online Redo Log Group". If unsuccessful, or if your database has halted, then perform one of procedures in this section, depending on the archiving mode.
The current log is the one LGWR is currently writing to. If a LGWR I/O fails, then LGWR terminates and the instance crashes. In this case, you must restore a backup, perform incomplete recovery, and open the database with the RESETLOGS option.
To recover from loss of an active online log group in NOARCHIVELOG mode:
If the media failure is temporary, then correct the problem so that the database can reuse the group when required.
Restore the database from a consistent, whole database backup (datafiles and control files) as described in "Restoring Datafiles Before Performing Incomplete Recovery". For example, enter:
% cp /disk2/backup/*.dbf $ORACLE_HOME/oradata/trgt/
Mount the database:
STARTUP MOUNT
Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow the database to reset the online redo logs, you must first mimic incomplete recovery:
RECOVER DATABASE UNTIL CANCEL
CANCEL
Open the database using the RESETLOGS option:
ALTER DATABASE OPEN RESETLOGS;
Shut down the database consistently. For example, enter:
SHUTDOWN IMMEDIATE
Make a whole database backup.
To recover from loss of an active online redo log group in ARCHIVELOG mode:
If the media failure is temporary, then correct the problem so that the database can reuse the group when required. If the media failure is not temporary, then use the following procedure.
Begin incomplete media recovery, recovering up through the log before the damaged log.
Ensure that the current name of the lost redo log can be used for a newly created file. If not, then rename the members of the damaged online redo log group to a new location. For example, enter:
ALTER DATABASE RENAME FILE "?/oradata/trgt/redo01.log" TO "/tmp/redo01.log";
ALTER DATABASE RENAME FILE "?/oradata/trgt/redo01.log" TO "/tmp/redo02.log";
Open the database using the RESETLOGS option:
ALTER DATABASE OPEN RESETLOGS;
Note:
All updates executed from the endpoint of the incomplete recovery to the present must be re-executed.
Loss of Multiple Redo Log Groups
If you have lost multiple groups of the online redo log, then use the recovery method for the most difficult log to recover. The order of difficulty, from most difficult to least difficult, follows:
The current online redo log
An active online redo log
An unarchived online redo log
An inactive online redo log
Reference: Recovering After the Loss of Online Redo Log Files: Scenarios
About Oracle DataFiles and their importance:
Datafile Media Recovery
Datafile media recovery is used to recover from a lost or damaged current datafile or control file. It is also used to recover changes that were lost when a tablespace went offline without the OFFLINE NORMAL option. Both datafile media recovery and instance recovery must repair database integrity. However, these types of recovery differ with respect to their additional features. Media recovery has the following characteristics:
-
Applies changes to restored backups of damaged datafiles.
-
Can use archived logs as well as online logs.
-
Requires explicit invocation by a user.
-
Does not detect media failure (that is, the need to restore a backup) automatically. After a backup has been restored, however, detection of the need to recover it through media recovery is automatic.
-
Has a recovery time governed solely by user policy (for example, frequency of backups, parallel recovery parameters, number of database transactions since the last backup) rather than by Oracle Database internal mechanisms.
The database cannot be opened if any of the online datafiles needs media recovery, nor can a datafile that needs media recovery be brought online until media recovery is complete. The followin
g scenarios necessitate media recovery:
-
You restore a backup of a datafile.
-
You restore a backup control file (even if all datafiles are current).
-
A datafile is taken offline (either by you or automatically by Oracle Database) without the
OFFLINENORMALoption.
Unless the database is not open by any instance, datafile media recovery can only operate on offline datafiles.
Note that when a file requires media recovery, you must perform media recovery even if all necessary changes are contained in the online logs. In other words, you must still run recovery even though the archived logs are not needed. Media recovery may find nothing to do — and signal the "no recovery required" error — if invoked for files that do not need recovery.
Reference: Datafile Media Recovery
Media Failures
A media failure occurs when a problem external to the database prevents Oracle Database from reading from or writing to a file during database operations. Typical media failures include physical failures, such as head crashes, and the overwriting, deletion or corruption of a database file. Media failures are less common than user or application errors, but your backup and recovery strategy should prepare for them.
Database operation after a media failure of online redo log files or control files depends on whether the files are protected by multiplexing. When an online redo log or control file is multiplexed, the database maintains multiple copies of the file.
If a media failure damages a disk containing one copy of a multiplexed online redo log, then the database can usually continue to operate without significant interruption. Damage to a nonmultiplexed online redo log causes database operation to halt and may cause permanent loss of data.
Damage to any control file, whether it is multiplexed or not, halts the database when it attempts to read or write to the damaged control file. The database accesses the control file frequently, for example, at every checkpoint and online redo log switch.
Media failures are either read errors or write errors. In a read error, the instance cannot read a datafile and an operating system error is returned to the application, along with an error indicating that the file cannot be found, cannot be opened, or cannot be read. The database continues to run, but the error is returned each time an unsuccessful read occurs. At the next checkpoint, a write error will occur when the database attempts to write to the datafile header as part of the checkpoint process.
The effect of a datafile write error depends upon which tablespace the datafile is in. If the instance cannot write to a datafile in the SYSTEM tablespace, an undo tablespace, or a datafile with active rollback segments, then the database issues an error and shuts down. All files in the SYSTEM tablespace and all datafiles containing undo or rollback segments must be online in order for the database to operate properly.
If the instance cannot write to a datafile other than those in the preceding list, then the result depends on whether the database is running in ARCHIVELOGmode. In ARCHIVELOG mode, the database records an error in the database writer trace file and takes the affected datafile offline. All other datafiles in the tablespace containing this datafile remain online. You can then rectify the underlying problem and restore and recover the affected tablespace.
In NOARCHIVELOG mode, the database writer background process fails and the instance fails. The cause of the problem determines the required response. If the problem is temporary, then crash recovery usually can be performed using the online redo log files. In such situations, the instance can be restarted without resorting to media recovery. If a datafile is damaged, however, then you must restore a consistent backup of the entire database.
Reference: Media Failures
Taking Tablespaces Offline
You may want to take a tablespace offline for any of the following reasons:
-
To make a portion of the database unavailable while allowing normal access to the remainder of the database
-
To perform an offline tablespace backup (even though a tablespace can be backed up while online and in use)
-
To make an application and its group of tables temporarily unavailable while updating or maintaining the application
-
To rename or relocate tablespace datafiles
See "Renaming and Relocating Datafiles" for details.
When a tablespace is taken offline, the database takes all the associated files offline.
You cannot take the following tablespaces offline:
-
SYSTEM -
The undo tablespace
-
Temporary tablespaces
Before taking a tablespace offline, consider altering the tablespace allocation of any users who have been assigned the tablespace as a default tablespace. Doing so is advisable because those users will not be able to access objects in the tablespace while it is offline.
You can specify any of the following parameters as part of the ALTER TABLESPACE...OFFLINE statement:
Reference: Taking Tablespaces Offline
If your DB is in archive Log mode, Mark the lost DBF file as offline in Oracle DB and then bring the DB up, you may loose any of the data that is in that .dbf file
Quick steps:
Verifying the “ARCHIVE LOG” status:
SQL> select log_mode from v$database;
LOG_MODE
————
ARCHIVELOGSQL>
SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/dbfiles/ins3/archive
Oldest online log sequence 2253817
Current log sequence 2253823
SQL>
1. startup mount
2. alter database datafile 6 offline drop;
3. alter database open
Reference: Accidentally deleted DBF file
