Block Corruption and Recovery

Step 1: Identify the corrupt blocks
-----------------------------------
1. Run below command to populate v$database_block_corruption view with information of all the corrupted blocks.

RMAN> backup validate check logical database;

“CHECK LOGICAL" option is used to identify both Physical and Logical Block Corruptions.

Select the view to identify the corrupted blocks detected by RMAN.
SQL> select * from v$database_block_corruption;

Please note, After a corrupt block is repaired, the row identifying the block is deleted from the view.

2. Check alert. log file for corrupted blocks, data file list.
For ex:- ORA-01578: ORACLE data block corrupted (file # 5, block # 15)
ORA-01110: data file 5: '/oracle/oradata/trgt/users01.dbf'

3. You can also use dbverify utility to identify Physical and Logical Intra Block Corruptions.
dbv file=datafile_name blocksize=datafile_block_size

Step 2: Recovering Data blocks
-------------------------------
1. Recovering Data blocks By Using All Available Backups

Run the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks

RMAN>BLOCKRECOVER DATAFILE 5 BLOCK 15;

Recover multiple blocks in single command
RMAN>BLOCKRECOVER DATAFILE 5 BLOCK 15 DATAFILE 2 BLOCK 10;

2. Recovering Data blocks Using Selected Backups

Run the BLOCKRECOVER command at the RMAN prompt, Specifying the data file and block numbers for the corrupted blocks and limiting the backup candidates by means of the available options. For example, specify what type of backup should be used to restore the blocks.

# restore from backupset
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 FROM BACKUPSET;

# restore from datafile image copy
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 FROM DATAFILECOPY;

# restore from backup set with tag "Sunday"
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 FROM TAG = Sunday;

# restore using backups created before log sequence 100
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 RESTORE UNTIL SEQUENCE 100;

# restore using one week ago backups
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 RESTORE UNTIL 'SYSDATE-7';

# restore using backups until SCN 100
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 RESTORE UNTIL SCN 100;

3 . Recovering blocks listed in V$DATABASE_BLOCK_CORRUPTION view

Run the below command to recover all blocks marked corrupt in V$DATABASE_BLOCK_CORRUPTION
RMAN> BLOCKRECOVER CORRUPTION LIST;

Restores blocks from backup sets created more than 7 days ago
RMAN> BLOCKRECOVER CORRUPTION LIST FROM BACKUPSET RESTORE UNTIL TIME 'SYSDATE-7';

Note:-Block corruptions in RMAN backups and copies is kept in V$BACKUP_CORRUPTION and V$COPY_CORRUPTION

Step 3: Allow Recovery to corrupt blocks
-----------------------------------------
During recovery database finds corrupt blocks then recovery stops. Run the below command in order to skip the corrupt blocks and proceed with recovery.

SQL>RECOVER DATABASE ALLOW n CORRUPTION;

Where n is the number of allowable corrupt blocks

Recover an accidentally deleted file when the database is still open.

On Unix/Linux, when a file is deleted, but a process still has the file open, the file is still there in the filesystem, and only the inode is removed.
But the process can continue to use its file handle, and the file can also be accessible under /proc//fd .

In the following example, we use that behavior to recover a lost datafile after is has been dropped from the os (with rm) but the datafile is still open by the background processes.

First, we create a tablespace, and populate a table in it.

SQL> REM we create a tablespace:
SQL> create tablespace TEST_RM datafile ‘/var/tmp/test_rm.dbf’ size 10M;
Tablespace created.

SQL> REM we create a table in it:
SQL> create table FRANCK tablespace test_rm as select * from dba_objects;
Table created.

SQL> REM we check that table data is accessible:
SQL> select count(*) from FRANCK;
COUNT(*)
———-
12708

SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Then, we drop the datafile from unix prompt.

here is the datafile
ls -l /var/tmp/test_rm.dbf
-rw-r—– 1 oracle dba 10493952 Mar 26 14:25 /var/tmp/test_rm.dbf

we ‘accidently’ drop the datafile
rm /var/tmp/test_rm.dbf
ls -l /var/tmp/test_rm.dbf
ls: /var/tmp/test_rm.dbf: no such file or directory


Here the datafile is lost.
Now we connect again.

sqlplus / as sysdba

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> REM and we check if table data is accessible:
SQL> select count(*) from FRANCK;

select * from franck
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: ‘/var/tmp/test_rm.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

The datafile is lost and data is not accessible.

However, the datafile should still have an open file descriptor by an oracle background process

we check the dbwriter pid:
ps -edf | grep dbw
oracle 2661 1 0 Mar25 ? 00:00:06 xe_dbw0_XE
oracle 7044 7037 0 14:40 pts/1 00:00:00 /bin/bash -c ps -edf | grep dbw
oracle 7046 7044 0 14:40 pts/1 00:00:00 grep dbw

and we check its opened file descriptors for our file:
ls -l /proc/2661/fd | grep test_rm
lrwx—— 1 oracle dba 64 Mar 26 14:02 66 -> /var/tmp/test_rm.dbf (deleted)

here it is:
ls -l /proc/2661/fd/66
lrwx—— 1 oracle dba 64 Mar 26 14:02 /proc/2661/fd/66 -> /var/tmp/test_rm.dbf (deleted)

In some other unix, lsof may be needed to map the file descriptor with the deleted file name

first we set a symbolic link so that oracle can see it as it was before the delete:
ln -s /proc/2661/fd/66 /var/tmp/test_rm.dbf

here data is accessible, but that will be lost if dbwriter closes it file handle (i.e if the database is closed)

However we can now set the tablespace read only so that it is checkpointed, and no writes occurs on it.

SQL> alter tablespace TEST_RM read only;
Tablespace altered.

We can now copy the file safely.

then we drop the symbolic link:
rm /var/tmp/test_rm.dbf
ls -l /var/tmp/test_rm.dbf

ls: /var/tmp/test_rm.dbf: No such file or directory

and we can now copy the file
cp -p /proc/2661/fd/66 /var/tmp/test_rm.dbf
ls -l /var/tmp/test_rm.dbf

-rw-r—– 1 oracle dba 10493952 Mar 26 14:54 /var/tmp/test_rm.dbf

And datafile is now available again.

SQL> REM we have it back, lets put the tablespace back in read/write
SQL> alter tablespace test_rm read write;
Tablespace altered.

SQL> REM and we check data is still there:
SQL> select count(*) from FRANCK;

COUNT(*)
———-
12708

This is not to be used like that in production. This is unsupported and may behave differently on different unix/linux or oracle versions.

Retrieve deleted files on Unix / Linux using File Descriptors

Doc ID: Note:444749.1

Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7 to 10.2
Linux x86-64
Solaris Operating System (SPARC 64-bit)
Linux x86
Goal

Retrieve deleted datafiles/logfiles from the operating system if the database has not been restarted.
Solution

Using the PROC file system available on Unix/Linux, we can retrieve deleted datafile/logfile when all of the following hold good:-

1.) Database is not restarted.
2.) Server is not restarted.
3.) The file was not offline before deletion.


Background processes (DBWR, PMON, SMON etc.) have access to all the datafiles currently opened by the database. So, with the PID of a background procss, list of files currently opened by that process can be obtained by using 'lsof' command.
Also, there is a file descriptor associated with every file opened by a process. If the file gets accidently deleted from the operating system, its entry is not removed from the proc file system. Using that entry, we can recreate the deleted file.

This is explained with an example below.

1.) Create a tablespace

SQL> create tablespace my_test datafile '/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf' size 200k;

Tablespace created.

2.) Accidently, datafile belonging to this tablespace got deleted

$ rm /emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf

3.) Try resizing the datafile

SQL> alter database datafile '/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf' resize 250k;
alter database datafile '/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf' resize 250k
*
ERROR at line 1:
ORA-01565: error in identifying file
'/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Rescuing the file


1.) Find the process id of DBWr process:-

Format is:-

--> $ ps -ef |grep ''| grep ''

$ ps -ef |grep EMR102U6|grep dbw
emrdbms 21943 1 0 10:27:08 ? 0:00 ora_dbw0_EMR102U6

Note that Process ID for DBW0 here is 21943.

2.) Find open files for this Process Id using 'lsof' command :-

$ lsof -p 21943 |grep /emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf

Command PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
oracle 21943 emrdbms 270uW VREG 304,25 212992 11273825 /emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf

Note:- If you are using NAS then the file name in above command may not be displayed properly and hence this procedure should not be used under these circumstances.

Note the value of FD in the table above (270) .

3.) Go to the file descriptors directory :-

Format is :-

--> $ cd /proc/ / '/

$ cd /proc/21943/fd/

4.) Make the tablespace containing "deleted" datafiles READ-ONLY:

alter tablespace my_test read only;

Making the tablespace read only freezes the file header, preventing updates
from being made to the file header. Since this datafile is then at a read
only state, it is possible to copy the file while the database is open.

This will allow users to select from the tablespace, but prevents them from
doing inserts, updates, and deletes.

5.) Make a copy of file using file descriptor (270uW) :-

$ cat 270 > /emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf

6.) To make sure that the old "deleted" copies of files will not be used after
the copy has been done, do the following:

a) Take datafile offline
alter tablespace my_test offline;

Query the view v$datafile to verify the datafile is offline:
select status from v$datafile where file#=;

b) Bring datafile back online
alter tablespace my_test online;

7.) Put tablespace back in read write mode:
alter tablespace my_test read write;

Query view dba_tablespaces to check status of the tablespace:
select tablespace_name,status from dba_tablespaces where tablespace_name='MY_TEST';

8.) Now datafile resize operation goes through fine.

SQL> alter database datafile '/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf' resize 250k;

Database altered.

Note:- This procedure can also be used for retrieving the deleted current redo logfile.
References
Note 115424.1 - How to Rename or Move Datafiles and Logfiles

Recovery of deleted files on linux

A recent thread on the oracle-l maillist (http://www.freelists.org/list/oracle-l) called “REDO LOG Concepts” caught my attention. Someone asked about the transaction state of the (oracle) database when the online redologfiles have been deleted prior to a commit.

Most people know that on linux and unixes a file keeps on living (=available) in the context of a process as long as the file is kept open by the process, despite deletion by another process. This file is gone for all processes that try to access it after the deletion, but the deleted files will only get really disposed once all processes that have the file opened close the the connection (file descriptor) to it.

I guess everybody who is doing/has done linux/unix administration has encountered the situation at least once where a filesystem gets filled with some kind of logfile or tracefile which grows rapidly and keeps on growing at a very fast pace, and deleting it does not release any space (because the process which is filling the file is not stopped, so the file is held open, so the file still exists, despite deletion)

Some suggestions where made for recovery and protecting against it (mirroring online redologfiles). I guess most people know recovery fundamentals, so I will not dive further into that matter.

A fellow oracle-l member (stefan knecht) however suggested:

I’ve run into this situation before as well. It happens, people accidentally delete files.

What would be interesting to research, is if you still can somehow recover those files. As they’re still there, and locked by the process accessing them (in your case LGWR), there might be a way to “steal” the filehandle from that process and just write the file to another location before it’s being closed. Though you would have to intercept the syscall closing the file.

That made me think: linux has the proc filesystem, which gives system information, but also process information. This process information is quite extensive. One of the sections (directory actually) is a list of all the file descriptors for a process. These file descriptors are shown as symlinks:


[oracle@centos50-oracle10203 bin]$ ./sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 17 16:24:01 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 130024296 bytes
Database Buffers 33554432 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@centos50-oracle10203 bin]$ ps -ef | grep pmon
oracle 8634 1 0 16:24 ? 00:00:00 ora_pmon_frits
oracle 8680 2642 0 16:24 pts/1 00:00:00 grep pmon
[oracle@centos50-oracle10203 bin]$ cd /proc/8634/fd
[oracle@centos50-oracle10203 fd]$ ls -ls
total 0
0 lr-x------ 1 oracle oracle 64 May 17 16:24 0 -> /dev/null
0 lr-x------ 1 oracle oracle 64 May 17 16:24 1 -> /dev/null
0 lrwx------ 1 oracle oracle 64 May 17 16:24 10 -> /oracle/db/10.2.0.3/dbs/hc_frits.dat
0 lrwx------ 1 oracle oracle 64 May 17 16:24 11 -> /oracle/db/10.2.0.3/admin/frits/adump/ora_8632.aud
0 lr-x------ 1 oracle oracle 64 May 17 16:24 12 -> /dev/zero
0 lr-x------ 1 oracle oracle 64 May 17 16:24 13 -> /dev/zero
0 lr-x------ 1 oracle oracle 64 May 17 16:24 14 -> /oracle/db/10.2.0.3/rdbms/mesg/oraus.msb
0 lrwx------ 1 oracle oracle 64 May 17 16:24 15 -> /oracle/db/10.2.0.3/dbs/hc_frits.dat
0 lrwx------ 1 oracle oracle 64 May 17 16:24 16 -> socket:[33865]
0 lrwx------ 1 oracle oracle 64 May 17 16:24 17 -> /oracle/db/10.2.0.3/dbs/lkFRITS
0 lr-x------ 1 oracle oracle 64 May 17 16:24 18 -> /oracle/db/10.2.0.3/rdbms/mesg/oraus.msb
0 lr-x------ 1 oracle oracle 64 May 17 16:24 2 -> /dev/null
0 lr-x------ 1 oracle oracle 64 May 17 16:24 3 -> /dev/null
0 lr-x------ 1 oracle oracle 64 May 17 16:24 4 -> /dev/null
0 l-wx------ 1 oracle oracle 64 May 17 16:24 5 -> /oracle/db/10.2.0.3/admin/frits/udump/frits_ora_8632.trc
0 l-wx------ 1 oracle oracle 64 May 17 16:24 6 -> /oracle/db/10.2.0.3/admin/frits/bdump/alert_frits.log
0 lrwx------ 1 oracle oracle 64 May 17 16:24 7 -> /oracle/db/10.2.0.3/dbs/lkinstfrits (deleted)
0 l-wx------ 1 oracle oracle 64 May 17 16:24 8 -> /oracle/db/10.2.0.3/admin/frits/bdump/alert_frits.log
0 l-wx------ 1 oracle oracle 64 May 17 16:24 9 -> pipe:[11466]

As you can see, these are the open files of the pmon process of an oracle 10.2.0.3 database just after startup.
(if you want to use ltrace, strace or gdb on a process, this is the place to find the mapping of file descriptor to a file, socket or pipe!)

Can I use a file descriptor to get data out of the file? Probably, because it’s a symlink which is perfectly normal to use. Well then, back to the suggestion made:

“As they’re still there, and locked by the process accessing them (in your case LGWR), there might be a way to “steal” the filehandle from that process and just write the file to another location before it’s being closed.”

A quick test on a test system (centos 4.4, oracle 10.2.0.3):


SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------------------------------------------------------------------------ ---
3 ONLINE /oracle/frits/data/FRITS/onlinelog/o1_mf_3_31l7kp85_.log NO
3 ONLINE /oracle/frits/flashreco/FRITS/onlinelog/o1_mf_3_31l7kqsl_.log YES
2 ONLINE /oracle/frits/data/FRITS/onlinelog/o1_mf_2_31l7ko7c_.log NO
2 ONLINE /oracle/frits/flashreco/FRITS/onlinelog/o1_mf_2_31l7koq3_.log YES
1 ONLINE /oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log NO
1 ONLINE /oracle/frits/flashreco/FRITS/onlinelog/o1_mf_1_31l7knrh_.log YES

6 rows selected.

SQL>

Let’s see which log group is the current one:


SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# IRST_TIM
---------- ---------- ---------- ---------- ---------- --- ----------------------------- ---------
1 1 38 52428800 2 NO INACTIVE 1198861 10-APR-07
2 1 39 52428800 2 NO INACTIVE 1238951 10-APR-07
3 1 40 52428800 2 NO CURRENT 1274534 17-MAY-07

SQL>

Log group 3 is the current one. Well, lets delete one of the files of group 1 (I have two members in every log group, lets pretend we have only one member):


[oracle@filetest bin]$ rm /oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log

Now look at the file descriptors of the logwriter:


...
1 lrwx------ 1 root root 64 May 17 17:07 21 -> /oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log (deleted)
...

And lets make a copy of the file using the file descriptor:


[root@filetest ~]# cat /proc/4703/fd/21 > o1_mf_1_31l7knbs_.log
[root@filetest ~]# ls -ls o1_mf_1_31l7knbs_.log
51260 -rw-r--r-- 1 root root 52429312 May 17 17:11 o1_mf_1_31l7knbs_.log

It looks like we’ve made a copy of a deleted file, using the file descriptor in the proc filesystem. Now let’s shutdown the database, and start it up. This will go wrong, because a member of the online redolog group 1 can not be identified:


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 109052776 bytes
Database Buffers 54525952 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.

SQL>

Excuse me? Shouldn’t we get an error message saying an online logfile could not be identified? Let’s investigate further:


SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ----------------------------------------------------------------- ---
3 ONLINE /oracle/frits/data/FRITS/onlinelog/o1_mf_3_31l7kp85_.log NO
3 ONLINE /oracle/frits/flashreco/FRITS/onlinelog/o1_mf_3_31l7kqsl_.log YES
2 ONLINE /oracle/frits/data/FRITS/onlinelog/o1_mf_2_31l7ko7c_.log NO
2 ONLINE /oracle/frits/flashreco/FRITS/onlinelog/o1_mf_2_31l7koq3_.log YES
1 INVALID ONLINE /oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log NO
1 ONLINE /oracle/frits/flashreco/FRITS/onlinelog/o1_mf_1_31l7knrh_.log YES

6 rows selected.

SQL>

Well, apparently not….I recall situations where a lost online redologfile would cause the startup to fail, and needing to copy an existing one in the group to the missing one. I guess this is altered in some version of the database. Anybody know which version?

Well, it makes sense. The status of the file is set to ‘INVALID’ and the surviving file(s) are used. A message is printed in the alert.log:


Thu May 17 17:43:13 2007
Errors in file /oracle/db/10.2.0.3/admin/frits/bdump/frits_lgwr_13960.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May 17 17:43:13 2007
Errors in file /oracle/db/10.2.0.3/admin/frits/bdump/frits_lgwr_13960.trc:
ORA-00321: log 1 of thread 1, cannot update log file header
ORA-00312: online log 1 thread 1: '/oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log'

Now, let’s replace the missing one with our copy from the file descriptor:


[root@filetest ~]# cat o1_mf_1_31l7knbs_.log > /oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log
[root@filetest ~]# chown oracle:oinstall /oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log

Now switch from logfile until we reach the log group in which the invalid file resides (log group 1)…


SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------------------------------------------------------------------------ ---
3 ONLINE /oracle/frits/data/FRITS/onlinelog/o1_mf_3_31l7kp85_.log NO
3 ONLINE /oracle/frits/flashreco/FRITS/onlinelog/o1_mf_3_31l7kqsl_.log YES
2 ONLINE /oracle/frits/data/FRITS/onlinelog/o1_mf_2_31l7ko7c_.log NO
2 ONLINE /oracle/frits/flashreco/FRITS/onlinelog/o1_mf_2_31l7koq3_.log YES
1 ONLINE /oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log NO
1 ONLINE /oracle/frits/flashreco/FRITS/onlinelog/o1_mf_1_31l7knrh_.log YES

6 rows selected.

SQL>

Look! The file is valid again!

Once a logfile is invalid for whatever reason, oracle continues to work as long as there are valid members in the group. During a logswitch, the files in the log group where oracle switches to are identified, even if they are invalid, and reset to a valid state if they are usable again.

Also, it is possible to make a copy of an already deleted file (as root on centos 4.4 (centos 4.4 is a free version of RHEL4) and as the owner of the file or root on centos 5 (RHEL5)) using the filedescriptor in proc.

How to Recover Deleted Oracle Datafiles with No Downtime

So you have accidentally removed a datafile from your production database? First thing, DON’T PANIC! There’s an easy way to recover deleted datafiles, for as long as your database remains up. The procedure below works on linux, however this method conceivably can work for other platforms.

This procedure will even work if your database is in NOARCHIVELOG mode.

You may have reached this posting through Google, and in a rush to get the solution right away, so here it is.

The recovery is done in two phases.

Phase 1: instant recovery to prevent Oracle errors (like ORA-27041 “unable to open file”, ORA-01110, ORA-01116)

1. Find the PID of DBWRITER for the right database.

ps -ef | grep dbw0_SID_OF_YOUR_DB
oracle 12272 1 0 10:55 ? 00:00:00 ora_dbw0_test
oracle 12941 11501 0 12:36 pts/0 00:00:00 grep dbw0_test

2. List the deleted file handles for that DBWRITER process.

ls -l /proc/_PID_OF_DBWRITER/fd | grep deleted

lrwx------ 1 oracle oinstall 64 Oct 15 11:24 10 -> /home/oracle/product/10.2.0.2/dbs/lkinsttest (deleted)
lrwx------ 1 oracle oinstall 64 Oct 15 11:24 23 -> /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf (deleted)


3. Create a symbolic link to your datafile with the original name.

ln -s /proc/PID_OF_DBWRITER/fd/23 /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf

That’s all. Now you are no longer going to get errors. However, if your database goes down now, you will lose that datafile for good.

Phase 2: restore the file

ARCHIVELOG database

1. (Optional.) Issue a checkpoint. This is to reduce the recovery time when bringing the file online, depending on activity for that datafile. Unfortunately, you can’t checkpoint a single file, so the checkpoint may take some time.

alter system checkpoint;

2. Backup the datafile with rman. Why rman? It’s much easier then you think. Total downtime is about one second for inactive datafiles, and more for active ones (with writes).

rman target /
report schema;
backup as copy datafile YOUR_DATAFILE_NUMBER format '/location_of_your_database/new_name_for_File.dbf';
sql 'alter database datafile YOUR_DATAFILE_NUMBER offline';
switch datafile YOUR_DATAFILE_NUMBER to copy;
recover datafile YOUR_DATAFILE_NUMBER;
sql 'alter database datafile YOUR_DATAFILE_NUMBER online';
exit;


NOARCHIVELOG database

1. Make the tablespace with that datafile read only

select distinct tablespace_name from dba_data_files where file_name = 'YOUR_DELETED_FILE';
alter tablespace THE_TABLESPACE read only;

2. Copy the file from the symlink to a new name

cp SIM_LINK_DATA_FILE NEW_NAME_FOR_DATAFILE.dbf

3. WARNING: Ensure your copy is complete! Then, crash the database.

/*WAIT FOR COPY!!!*/
shutdown abort;


4. Remove the now invalid symlink, and rename the datafile to its original name. Be careful not to remove the wrong file now — that would be a disaster:

rm -i SIM_LINK_DATA_FILE
mv NEW_NAME_FOR_DATAFILE.dbf SIM_LINK_DATA_FILE

5. Startup your database normally and make the tablespace read/write.

startup
alter tablespace THE_TABLESPACE read write;

I hope this helps you to get out of a nasty situation.

Here’s both cases fully captured from terminal. Note, I am using Oracle-managed files. This doesn’t change the steps.

/ra5a/orabkp/test/TEST/datafile> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:31:55 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL>
SQL> select tablespace_name, file_name from dba_data_files

TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------------------------------------
USERS /ra5a/orabkp/test/TEST/datafile/o1_mf_users_3k6xgwhb_.dbf
SYSAUX /ra5a/orabkp/test/TEST/datafile/o1_mf_sysaux_3k6xgwdf_.dbf
UNDOTBS1 /ra5a/orabkp/test/TEST/datafile/o1_mf_undotbs1_3k6xgwg9_.dbf
SYSTEM /ra5a/orabkp/test/TEST/datafile/o1_mf_system_3k6xgwd4_.dbf
EXAMPLE /ra5a/orabkp/test/TEST/datafile/o1_mf_example_3k6xjdjw_.dbf
LOST /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf

6 rows selected.

SQL> select tablespace_name, table_name from dba_tables where owner = 'TESTING';

TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
LOST LOST_TABLE

SQL> connect testing/testing
SQL> select count(*) from lost_table;

COUNT(*)
----------
50070

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from lost_table;

COUNT(*)
----------
50070

SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

/ra5a/orabkp/test/TEST/datafile> ls -lF
total 1015132
-rw-r----- 1 oracle oinstall 157294592 Oct 15 12:22 o1_mf_example_3k6xjdjw_.dbf
-rw-r----- 1 oracle oinstall 104865792 Oct 15 12:22 o1_mf_lost_3k74mq08_.dbf
-rw-r----- 1 oracle oinstall 241180672 Oct 15 12:32 o1_mf_sysaux_3k6xgwdf_.dbf
-rw-r----- 1 oracle oinstall 503324672 Oct 15 12:32 o1_mf_system_3k6xgwd4_.dbf
-rw-r----- 1 oracle oinstall 20979712 Oct 15 10:17 o1_mf_temp_3k6xj9xn_.tmp
-rw-r----- 1 oracle oinstall 26222592 Oct 15 12:32 o1_mf_undotbs1_3k6xgwg9_.dbf
-rw-r----- 1 oracle oinstall 5251072 Oct 15 12:22 o1_mf_users_3k6xgwhb_.dbf

/ra5a/orabkp/test/TEST/datafile> rm o1_mf_lost_3k74mq08_.dbf
/ra5a/orabkp/test/TEST/datafile> sqlplus testing/testing

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:35:24 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> select count(*) from lost_table;

COUNT(*)
----------
50070

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from lost_table;
select count(*) from lost_table
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6:
'/ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL>
SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

/ra5a/orabkp/test/TEST/datafile> ps -ef|grep dbw0_test
oracle 12272 1 0 10:55 ? 00:00:00 ora_dbw0_test
oracle 12941 11501 0 12:36 pts/0 00:00:00 grep dbw0_test
/ra5a/orabkp/test/TEST/datafile> ls -l /proc/12272/fd|grep deleted
lrwx------ 1 oracle oinstall 64 Oct 15 11:24 10 -> /home/oracle/product/10.2.0.2/dbs/lkinsttest (deleted)
lrwx------ 1 oracle oinstall 64 Oct 15 12:17 26 -> /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf (deleted)

/ra5a/orabkp/test/TEST/datafile> ln -s /proc/12272/fd/26 /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf

/ra5a/orabkp/test/TEST/datafile> ls -lF
total 912620
-rw-r----- 1 oracle oinstall 157294592 Oct 15 12:22 o1_mf_example_3k6xjdjw_.dbf
lrwxrwxrwx 1 oracle oinstall 17 Oct 15 12:37 o1_mf_lost_3k74mq08_.dbf -> /proc/12272/fd/26
-rw-r----- 1 oracle oinstall 241180672 Oct 15 12:32 o1_mf_sysaux_3k6xgwdf_.dbf
-rw-r----- 1 oracle oinstall 503324672 Oct 15 12:32 o1_mf_system_3k6xgwd4_.dbf
-rw-r----- 1 oracle oinstall 20979712 Oct 15 10:17 o1_mf_temp_3k6xj9xn_.tmp
-rw-r----- 1 oracle oinstall 26222592 Oct 15 12:32 o1_mf_undotbs1_3k6xgwg9_.dbf
-rw-r----- 1 oracle oinstall 5251072 Oct 15 12:22 o1_mf_users_3k6xgwhb_.dbf

/ra5a/orabkp/test/TEST/datafile> sqlplus testing/testing

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:38:18 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> select count(*) from lost_table;

COUNT(*)
----------
50070

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from lost_table;

COUNT(*)
----------
50070

SQL>
SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

/ra5a/orabkp/test/TEST/datafile> rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Mon Oct 15 12:39:48 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: TEST (DBID=1934173752)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /ra5a/orabkp/test/TEST/datafile/o1_mf_system_3k6xgwd4_.dbf
2 25 UNDOTBS1 *** /ra5a/orabkp/test/TEST/datafile/o1_mf_undotbs1_3k6xgwg9_.dbf
3 230 SYSAUX *** /ra5a/orabkp/test/TEST/datafile/o1_mf_sysaux_3k6xgwdf_.dbf
4 5 USERS *** /ra5a/orabkp/test/TEST/datafile/o1_mf_users_3k6xgwhb_.dbf
5 150 EXAMPLE *** /ra5a/orabkp/test/TEST/datafile/o1_mf_example_3k6xjdjw_.dbf
6 100 LOST *** /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /ra5a/orabkp/test/TEST/datafile/o1_mf_temp_3k6xj9xn_.tmp

RMAN> backup as copy datafile 6 format '/ra5a/orabkp/test/TEST/datafile/lost.dbf';

Starting backup at 2007-10-15 12:40:45
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf
output filename=/ra5a/orabkp/test/TEST/datafile/lost.dbf tag=TAG20071015T124045 recid=13 stamp=636036046
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2007-10-15 12:40:46

RMAN> sql 'alter database datafile 6 offline';

sql statement: alter database datafile 6 offline

RMAN> switch datafile 6 to copy;

datafile 6 switched to datafile copy "/ra5a/orabkp/test/TEST/datafile/lost.dbf"

RMAN> recover datafile 6;

Starting recover at 2007-10-15 12:41:07
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 2007-10-15 12:41:07

RMAN> sql 'alter database datafile 6 online';

sql statement: alter database datafile 6 online

RMAN>

Recovery Manager complete.

/ra5a/orabkp/test/TEST/datafile> ls -lF
total 1015132
-rw-r----- 1 oracle oinstall 104865792 Oct 15 12:41 lost.dbf
-rw-r----- 1 oracle oinstall 157294592 Oct 15 12:22 o1_mf_example_3k6xjdjw_.dbf
lrwxrwxrwx 1 oracle oinstall 17 Oct 15 12:37 o1_mf_lost_3k74mq08_.dbf -> /proc/12272/fd/26
-rw-r----- 1 oracle oinstall 241180672 Oct 15 12:32 o1_mf_sysaux_3k6xgwdf_.dbf
-rw-r----- 1 oracle oinstall 503324672 Oct 15 12:38 o1_mf_system_3k6xgwd4_.dbf
-rw-r----- 1 oracle oinstall 20979712 Oct 15 10:17 o1_mf_temp_3k6xj9xn_.tmp
-rw-r----- 1 oracle oinstall 26222592 Oct 15 12:38 o1_mf_undotbs1_3k6xgwg9_.dbf
-rw-r----- 1 oracle oinstall 5251072 Oct 15 12:22 o1_mf_users_3k6xgwhb_.dbf

/ra5a/orabkp/test/TEST/datafile> rm o1_mf_lost_3k74mq08_.dbf
/ra5a/orabkp/test/TEST/datafile> sqlplus testing/testing

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:42:03 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> select count(*) from lost_table;

COUNT(*)
----------
50070

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from lost_table;

COUNT(*)
----------
50070

SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

RMAN incremental backup

RMAN> connect target *
run
{
allocate channel t1 type disk format
'/u01/OraApps/oracle/product/11.5.10.2/rman_backup/%d_%s_%t.bak';
allocate channel t2 type disk format
'/u01/OraApps/oracle/product/11.5.10.2/rman_backup/%d_%s_%t.bak';
allocate channel t3 type disk format
'/u01/OraApps/oracle/product/11.5.10.2/rman_backup/%d_%s_%t.bak';
backup incremental level 1 skip inaccessible
TAG='INCREMENTAL_LEVEL_1' database include current controlfile;
release channel t1;
release channel t2;
release channel t3;
allocate channel t1 type disk format
'/u01/OraApps/oracle/product/11.5.10.2/rman_backup/al_%d_%p_%u_%t';
sql 'alter system archive log current';
change archivelog all crosscheck;
backup skip inaccessible archivelog all delete input;
crosscheck backup;
delete obsolete;
delete expired backup;
delete expired archivelog all;
release channel t1;
}

Script to Archive Automation

#!/usr/bin/ksh
# Functions
#A function to format the date

function formatDate {
date +%Y%m%d"."%H%M%S
}

#A function to generate a time stamp
function getTimestamp {
echo `formatDate` -- "${*}"
}

#A function to exit after an error condition
function exitError {
getTimestamp ${exitCodeArr[${1}]}

exit ${1}
}

#Function to set the environmental varriable from oratab.

function ToSetEnvVariables ()
{
if [ -f /etc/oratab ]; then
OraTab=/etc/oratab
elif [ -f /var/opt/oracle/oratab ]; then
OraTab=/var/opt/oracle/oratab
fi
unset ORACLE_HOME
OH=`cat /etc/oratab |grep -v "#"|grep -v "*."|grep "$1"|cut -d ":" -f2`
ORACLE_SID=$1;export ORACLE_SID
ORACLE_HOME=$OH;export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH:.;export PATH
}
#Function to backup the archive log file aftre reaching to threshold of archive log file system

function cleararchivelog ()
{
ToSetEnvVariables $1
$ORACLE_HOME/bin/rman target / cmdfile /u01/Scripts/backup_arch_rman.cmd msglog /u01/Scripts/backup_arch_rman.log
}
sqlplus -s "/as sysdba" < /dev/null
set feed off echo off head off trimspool on timing off set pagesize 1000 verify off
spool CheckarchDest.lst
select round(PERCENT_SPACE_USED) from v\$FLASH_RECOVERY_AREA_USAGE where FILE_TYPE='ARCHIVED LOG';
spool off
set feed on
eof
cat CheckarchDest.lst|grep -v "rows selected"|grep -v "SQL" |grep -v ^$ > check_percent_arch_full
while read line
do
Percent=`echo $line |awk '{print $1}'`
if [ ${Percent} -gt $2 ]; then
cleararchivelog $1
else
echo "Looks Okay"
fi
done < check_percent_arch_full