Sunday, October 21, 2012

What will happen if dbf file accidentally deleted when database is still open and how to recover it?


What will happen if dbf file (non system) accidentally deleted when database is still open? Only for Unix and Linux platforms if accidentally deleted dbf file OS will not delete it, OS will rename it. Also be aware that process (pid) will goes on and use deleted file. You can find deleted file under /proc/<pid>/...

Let`s check.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>  

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

6 rows selected.

SQL> 

SQL> select file_id, file_name from dba_data_files order by 1;

   FILE_ID  FILE_NAME
------------  -------------------------------------------------------------------
         1 /oracle/product/11.2.0/oradata/testdb/system01.dbf

         2 /oracle/product/11.2.0/oradata/testdb/sysaux01.dbf

         3 /oracle/product/11.2.0/oradata/testdb/undotbs01.dbf

         4 /oracle/product/11.2.0/oradata/testdb/users01.dbf

         5 /oracle/product/11.2.0/oradata/testdb/example01.dbf


SQL>  

We will simulate on USERS tablespace`s datafile.

Now we will create new user (Ulfet), grant to him necessary privileges.  

SQL> create user ulfet identified by ulfet default tablespace users;

User created.

SQL> grant connect, resource to ulfet;

Grant succeeded.

SQL> conn ulfet/ulfet
Connected.

--Now create table and insert a record, after recover we will check data.
SQL> create table test (id number);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>  

Now, delete /oracle/product/11.2.0/oradata/testdb/users01.dbf data file.

[oracle@db-server testdb]$ pwd
/oracle/product/11.2.0/oradata/testdb
[oracle@db-server testdb]$ ls -l
total 1533652
-rw-r----- 1 oracle oinstall   9748480 Oct 21 14:17 control01.ctl
-rw-r----- 1 oracle oinstall 104865792 Oct 21 14:02 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 Oct 21 13:57 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Oct 21 14:17 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Oct 21 13:53 redo03.log
-rw-r----- 1 oracle oinstall 513810432 Oct 21 14:17 sysaux01.dbf
-rw-r----- 1 oracle oinstall 713039872 Oct 21 14:17 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Oct 21 13:58 temp01.dbf
-rw-r----- 1 oracle oinstall  62922752 Oct 21 14:17 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Oct 21 14:02 users01.dbf
[oracle@db-server testdb]$ rm users01.dbf
[oracle@db-server testdb]$

Now check to be sure file not exists:

[oracle@db-server testdb]$ ls -lrt users01.dbf
ls: users01.dbf: No such file or directory


[oracle@db-server testdb]$

There is no any such file...
Also check table is acceptable 

SQL> conn ulfet/ulfet
Connected.
SQL> select * from test;

        ID
----------
         1

SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME                             TABLESPACE_NAME
------------------------------        ------------------------------
TEST                                                 USERS

SQL>

Hmmm, table acceptable yet, need to flush buffer cache or restart db.

SQL> conn /as sysdba
Connected.

SQL> alter system flush buffer_cache;

System altered.

--Check again
SQL> conn ulfet/ulfet
Connected.
SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oracle/product/11.2.0/oradata/testdb/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


SQL> 

Oracle retrieve information about that users01.dbf is missing...

However, the datafile is missing, dbwriter backgound process still works.

Check dbwr process: 
[oracle@db-server testdb]$ ps -ef | grep dbw
oracle    3482     1  0 13:53 ?        00:00:04 ora_dbw0_testdb
oracle    4675  3296  0 14:25 pts/3    00:00:00 grep dbw
[oracle@db-server testdb]$

[oracle@db-server testdb]$ ls -lrt /proc/3482/fd/ | grep users
lrwx------ 1 oracle oinstall 64 Oct 21 14:28 23 -> /oracle/product/11.2.0/oradata/testdb/users01.dbf (deleted)


[oracle@db-server testdb]$ ls -lrt /proc/3482/fd/23
lrwx------ 1 oracle oinstall 64 Oct 21 14:28 /proc/3482/fd/23 -> /oracle/product/11.2.0/oradata/testdb/users01.dbf (deleted)


first we set a symbolic link so that oracle can see it as it was before the delete:

ln -s /proc/3482/fd/23 /oracle/product/11.2.0/oradata/testdb/users01.dbf

Switch tablespace to read only before restoring data file.

SQL> conn /as sysdba
Connected.

SQL> alter tablespace users read only;

Tablespace altered.

SQL>


We drop the symbolic link: 

[oracle@db-server testdb]$ rm users01.dbf

Copy the file to there where it was deleted.

cp -p /proc/3482/fd/23 /oracle/product/11.2.0/oradata/testdb/users01.dbf
[oracle@db-server testdb]$ ls -lrt users01.dbf
-rw-r----- 1 oracle oinstall 5251072 Oct 21 16:09 users01.dbf

So, datafile is available and time to take tablespace back to read write.


SQL> alter tablespace users read write;

Tablespace altered.

SQL>

Check Ulfet`s table is available ?

SQL> conn ulfet/ulfet
Connected.

SQL> select * from test;

        ID
----------
         1

SQL> 

Yes, we did it.


P.S: This is not acceptable for Windows.

1 comment:

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...