Here is a little trick that can be useful in case of a accidental delete of one datafile.
I do not recommend or encourage anyone to do it, but could be useful under certain specific situations.
PRO SYS @ LAB >select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------------------------------------------------------------------------------
SYSTEM /opt/oracle/LAB/datafile/o1_mf_system_d1mb81gx_.dbf
SYSAUX /opt/oracle/LAB/datafile/o1_mf_sysaux_d1mb872s_.dbf
UNDOTBS1 /opt/oracle/LAB/datafile/o1_mf_undotbs1_d1mb8cp3_.dbf
PERFSTAT /opt/oracle/LAB/datafile/o1_mf_perfstat_d1mb8x94_.dbf
USERS /opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf
PRO SYS @ LAB >
Let's create one test table in USERS and put some data:
PRO SYS @ LAB >create table TEST_DEL(
2 campo number) tablespace users;
Table created.
PRO SYS @ LAB >insert into test_del select dbms_random.value(1,100) from dual connect by level <= 100;
100 rows created.
PRO SYS @ LAB >commit;
Commit complete.
PRO SYS @ LAB >select sum(campo) from test_del;
SUM(CAMPO)
----------
5118.65753
PRO SYS @ LAB >
Now, I exit from my sqlplus prompt, and delete from the operating system the datafile:
[servertest].oracle:/home/oracle > rm -f /opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf
[servertest].oracle:/home/oracle >
I run sqlplus and flush the buffer cache (otherwise, the select would get the data from memory).
PRO SYS @ LAB >alter system flush buffer_cache;
System altered.
PRO SYS @ LAB >select * from test_del;
select * from test_del
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
PRO SYS @ LAB >
Of course, if I try to insert one row, it won't work:
insert into test_del values(1000)
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
PRO SYS @ LAB >
OK, so now, the trick. Very important, do not close the database. It must be opened.
We'll use lsof. The idea is a pointer to the file structure on the operating system has been deleted, but the process
still has the underlying data structure held. So, the question is to recover this pointer. (this is a rough description, but
will give a hint).
So, we run lsof:
[servertest].oracle:/home/oracle > /usr/sbin/lsof | grep mf_users
oracle 3545 oracle 261uW REG 253,10 524296192 491528 /opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf (deleted)
oracle 3547 oracle 264u REG 253,10 524296192 491528 /opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf (deleted)
oracle 3551 oracle 260u REG 253,10 524296192 491528 /opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf (deleted)
[servertest].oracle:/home/oracle >
The first column gives you the name of the command associated with the process, the second column is the process id, and the number in the fourth column is the file descriptor (the "u" means that is a file open for read and write access and the "W", that there is a write lock on the entire file ).
Now you know that process 3545 still has the file open, and you know the file descriptor, 261.
So, now, we go to find the corresponding file descriptor in the /proc filesystem
[servertest].oracle:/home/oracle > ls -l /proc/3545/fd/261
lrwx------ 1 oracle oinstall 64 Jan 4 11:16 /proc/3545/fd/261 -> /opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf (deleted)
[servertest].oracle:/home/oracle >
And copy the contents to the original place:
[servertest].oracle:/home/oracle > dd if=/proc/3545/fd/261 of=/opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf
1024016+0 records in
1024016+0 records out
524296192 bytes (524 MB) copied, 5.49037 seconds, 95.5 MB/s
[gsilkasp32].oracle:/home/oracle >
Now, we run sqlplus again and query the table:
PRO SYS @ LAB >select sum(campo) from test_del;
SUM(CAMPO)
----------
5118.65753
PRO SYS @ LAB >
It worked!. Now we try to insert one register:
PRO SYS @ LAB >insert into test_del values(1000);
1 row created.
PRO SYS @ LAB >commit;
Commit complete.
PRO SYS @ LAB >select sum(campo) from test_del;
SUM(CAMPO)
----------
6118.65753
PRO SYS @ LAB >
OK. Great. Now we should restart the instance:
PRO SYS @ LAB >shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
PRO SYS @ LAB >startup
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 427821080 bytes
Database Buffers 83886080 bytes
Redo Buffers 7974912 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf'
PRO SYS @ LAB >
As you see, even if we could access the data, the datafile still needs recovery.
PRO SYS @ LAB >recover database;
Media recovery complete.
PRO SYS @ LAB >alter database open;
Database altered.
PRO SYS @ LAB >select status from v$instance;
STATUS
------------
OPEN
PRO SYS @ LAB >
PRO SYS @ LAB >shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
PRO SYS @ LAB >startup
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 427821080 bytes
Database Buffers 83886080 bytes
Redo Buffers 7974912 bytes
Database mounted.
Database opened.
PRO SYS @ LAB >
And it is done.
Of course, I do not encourage you to do this. (as I do not believe it is supported by Oracle) But is something that could be useful in some situations.
Also, i recommend you to keep an eye on the alert.log, just in case appeared errors after the operation. (I have not seen any, but is worth to keep an eye).
This trick will not work with online redo logs. It seems that while the datafile cannot be accessed, modifications keep being tracked on redo and undo. That should be the reason why the datafile need to be recovered when instance is restarted.
I suppose it will not work on UNDO as well (I have not tried) and have my doubts regarding SYSTEM.
No hay comentarios:
Publicar un comentario