Home » RDBMS Server » Backup & Recovery » How to recover the database when running only on buffer cache (oracle 12c OEL 7.x)
How to recover the database when running only on buffer cache [message #684131] Thu, 08 April 2021 10:46 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi all,
I submitted
QL> alter database begin backup;

Database altered.

Then I removed all archive logs, redo logs , data files from the OS.



- RMAN backups are there from a day before but the archive logs were removed so I believe using it to restore today's data is not an option
- Data pump not working due to a already corrupted DBMS_REGISTRY and other SYS objects

The application system works fine, but queries on v$ get error


SQL> select * from v$database;
select * from v$database
              *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/....../control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL>
So I have a Database still running with the cached data but in jeopardy to run out of cache and be broken for good.

What other options can be used in such a situation, to save the database?

Thanks,
Andrey


Re: How to recover the database when running only on buffer cache [message #684133 is a reply to message #684131] Thu, 08 April 2021 11:15 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Quote:
Then I removed all archive logs, redo logs , data files from the OS.
Why did you remove those files?

Quote:
So I have a Database still running with the cached data but in jeopardy to run out of cache and be broken for good.
It's already broken for good.

Quote:
What other options can be used in such a situation, to save the database?
If you deleted the files and archivelogs without a backup, you cannot. The best you might be able to do restore from that last rman backup. What type of backup was it (full, incremental 0, incremental 1?) Did you also backup the archivelogs?

Why did you issue the 'alter database begin backup;'? That does _not_ backup the database. It merely merely changes how redo gets logged, so that you can backup the data files at the OS level instead of using oracle's very capable and very intelligent rman.

What version of Oracle? What OS?


Re: How to recover the database when running only on buffer cache [message #684134 is a reply to message #684133] Thu, 08 April 2021 11:35 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Quote:
Why did you remove those files?
User error ( not realizing which instance the deletion is done on ).





Quote:
What type of backup was it (full, incremental 0, incremental 1?)
Full plus archivelogs




Quote:
Why did you issue the 'alter database begin backup;'?
As a part of a datafile copy based repliaction scenario from one machine to another similar one




Quote:
What version of Oracle? What OS?

12.2.0.1.0 , OEL 7.9
Re: How to recover the database when running only on buffer cache [message #684137 is a reply to message #684134] Thu, 08 April 2021 13:03 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
When you "deleted" those files, you didn't actually delete anything: all you did was remove the references to them from their directory, which unlinks them from their inode. As long as a process has the file open, it will still be able to access the file contents. Only when all processes have closed the file will the file actually be deleted. You will likely find that any new sessions cannot do anything, as they will not be able open any of the files.

What you should be able to do is use lsof to list the inodes of all open files. Then terminate the instance, and create a new temporary filenames pointing to them. Work out which files they are, and rename them appropriately. I don't believe in luck (because I am a scientist) but you are going to need some.
Re: How to recover the database when running only on buffer cache [message #684139 is a reply to message #684137] Thu, 08 April 2021 13:36 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
John Watson wrote on Thu, 08 April 2021 21:03
When you "deleted" those files, you didn't actually delete anything: all you did was remove the references to them from their directory, which unlinks them from their inode. As long as a process has the file open, it will still be able to access the file contents. Only when all processes have closed the file will the file actually be deleted. You will likely find that any new sessions cannot do anything, as they will not be able open any of the files.

What you should be able to do is use lsof to list the inodes of all open files. Then terminate the instance, and create a new temporary filenames pointing to them. Work out which files they are, and rename them appropriately. I don't believe in luck (because I am a scientist) but you are going to need some.
Thank you very much.
the session was disconnected as I tried to issue "select scn_to_timestamp(current_scn) from v$databse".
Then hope was gone and a restore to the last rman backup was performed and database opened with resetlogs.

However, I did see that while the database was running based on "ALTER DATABASE BEGIN BACKUP.." - the files were gone - directories were empty.
When I tried to query some data from v$database from the session that issued the "begin backup" - it gave errors about sys..dbf file missing


Andrey

[Updated on: Thu, 08 April 2021 13:41]

Report message to a moderator

Re: How to recover the database when running only on buffer cache [message #684143 is a reply to message #684137] Thu, 08 April 2021 15:15 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
John Watson wrote on Thu, 08 April 2021 21:03
When you "deleted" those files, you didn't actually delete anything: all you did was remove the references to them from their directory, which unlinks them from their inode. As long as a process has the file open, it will still be able to access the file contents. Only when all processes have closed the file will the file actually be deleted. You will likely find that any new sessions cannot do anything, as they will not be able open any of the files.

What you should be able to do is use lsof to list the inodes of all open files. Then terminate the instance, and create a new temporary filenames pointing to them. Work out which files they are, and rename them appropriately. I don't believe in luck (because I am a scientist) but you are going to need some.

I read and re-thought what you said and understand it a bit better.
Fascinating. I may create a testcase and try it at some point and post it.

Thanks again.
Re: How to recover the database when running only on buffer cache [message #684148 is a reply to message #684143] Fri, 09 April 2021 01:06 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I've been caught out by this behaviour a few times, the last occasion was with renaming and deleting oversized listener log files. This article gives some interesting information,
https://unix.stackexchange.com/questions/68523/find-and-remove-large-files-that-are-open-but-have-been-deleted
Previous Topic: Incremental level backup 1 without level 0
Next Topic: How to restore Oracle after reinstalling Windows Server
Goto Forum:
  


Current Time: Thu Mar 28 17:31:19 CDT 2024