Chen Shapira wrote me a suggestion regarding losing a data file.
“Try moving a data file while the DB is up and running, and try to make Oracle "notice"
that it is gone. On Linux, Oracle does not notice anything! You can move a datafile
and all selects, updates, inserts, will work. The OS prevents the DB from seeing
the change. But I noticed that your test system is on windows, so I'm curious
whether it is the same.”
Once my company’s operator TARed the rest of the database into the system tablespace datafile.
Oracle ‘noticed’! Are you sure that you really lost the file? Was there some sort of mirroring
of the file?
Anyway, here is what happened when I tried to lose a data file while the DB is up on a Windows based DB…
SQL> select name from v$datafile;
NAME
-------------------------------------------------------------------------------
C:\ORACLE\ORA10\ORADATA\DB10\SYSTEM01.DBF
C:\ORACLE\ORA10\ORADATA\DB10\UNDOTBS01.DBF
C:\ORACLE\ORA10\ORADATA\DB10\SYSAUX01.DBF
C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF
C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01.DBF
SQL> host ren C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF users.dbf
The process cannot access the file because it is being used by another process.
SQL> host del C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF
C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF
The process cannot access the file because it is being used by another process.
SQL> host ren C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01.DBF example02.dbf
The process cannot access the file because it is being used by another process.
Also a straight O/S effort:
C:\Windows\System32>del C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF
C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF
The process cannot access the file because it is being used by another process.
Monday, February 11, 2008
Trying to lose a datafile.
Subscribe to:
Post Comments (Atom)
7 comments:
I think the behavior on Windows is expected. Windows is very protective of files that are opened by other processes.
In the Linux case, you move a file, but the programs that have already opened said file don't know it, cause they maintain a reference count on the inode. In other words, once the file is closed, the move really happens, and then the next attempt to open the file will fail.
I don't have a system handy that I can test this on, at the moment, however, try moving the file and then doing an alter system checkpoint, and then something that you know will access the file.
Yes, AIX is different too. I know that because I deleted a file while the database was open the other day, deliberately I might add, to illustrate this to someone.
a) The file can be deleted.
b) You'll know all about the problem when you restart the instance :-(
Thank you for the explanation. Do you mean that the checkpoint is sufficient to lose the reference to the inode? I don't have access to a Linux or Unix based DB to run a trial. Maybe someone else can do it and let me know.
It looks like an alter system checkpoint will work. The dml and queries will work to a point without the alter system, but some sort of buffer must fill and then attempt to open the file like mbobak suggested.
SQL> !rm /u02/oradata/FUN1/users01.dbf
SQL> declare
2 mynum number := 0;
3 begin
4 for i in 1..10000 loop
5 execute immediate 'insert into test values (:1)' using mynum;
6 mynum := mynum + 1;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select count(9) from test;
COUNT(9)
----------
10000
SQL> alter system checkpoint;
System altered.
SQL> select count(9) from test;
select count(9) from test
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u02/oradata/FUN1/users01.dbf'
If the datafile is on OCFS (Linux) then the behavior is the same as Windows.
I do monthly partition maintenance - export then drop last month's data then drop the tablespace/datafiles.
Even after taking the tablespace offline - it's possible for a sessions server process to still have the datafile locked (confirmed with fuser). After killing the session the rm works.
DavidB
I think that you may be interested in another application that quickly eliminates data corruption issues in database files, please take a look at repair sql 2005 database tool and let me know what do you think
Post a Comment