Last week I was simulating a crash of my database (Oracle 184.108.40.206, latest patchset) due to missing data files. So, I just removed the datafile where my web application is storing data to see what is going to happen. But nothing happened, the application was up and running, and I was able to create new items in the UI and save them. Normally, all this data is stored in oracle.
After I created a few items, I bounced the database. Then I restored and recovered the data file using RMAN. All the items I created after the datafile deletion were there.
I would like to know, in which order oracle will write the commited DML's to the redo logs and the datafile, so that I can explain the behaviour of the application. I was wondering how the commited changes after datafile deletion were preserved even after restoring the datafile from the last RMAN backup.
Thanks for any input.
whenever you commit any transaction the LGWR writes this changes from redo log buffer to redo log file. When redolog file becomes full then it initiates checkpoint and write every thin to archive log file.
DBWR writes data from buffer cache to datafile only if it doesnot have free buffer with it.
In your case what could have happened is when you removed the datafile and made the changes in database. It might have not written those changes to the datafile and only have made the entery in the redo logfile.
so after you have bounce the database and restore the datafile and recover it using archive log. you would get output as media recovery completed and every changes you have made in the database after you removed the datafile, All the changes has came back in recovery.
If you are on Linux, you can "remove" a file with the "rm" command but Oracle can continue to write to it. That is the behaviour -- the inode for the file remains available and Oracle keeps a file handle for the file open. It can continue writing to the file (the file continues to consume disk space for all it's blocks). It closes the file handle only at shutdown. On some platforms, it make take the file Offline if it cannot write to it. (On windows, the file is locked so you cannot even delete the file while the database instance is running).
When you RESTORED the datafile, you actually overwrote the updated datafile -- effectively meaning that your DML wasn't present in the file anymore. Then, when you did the RECOVER, Oracle replayed all changes -- your DML -- to the datafile by applying them from the archivelogs and online redo logs.
Hemant K Chitale
That sounds plausible.
If I have understood correctly, in order to simulate the crash, I could try setting the db_cache_size to minimum and generate DML's until the DBWR tries to write the data file.
Yes or you can just do a db restart which would close the Oracle process and next restart would for sure result in a datafile not found error.
Ideally DML first writes to redo logs using LGWR then it writes data to datafile using DBWR.
all the changes done using DML were recorded into redo logs as database was in archive log mode.
that's why when you restored datafile using last RMAN backup and applied required/available archived logs.
It got reflected into your database.