Oracle 12c recovery without system file

Question

Scenario:

• Deleted system files (dbf files); and trying to recover database back to normal using available control files, and some redo logs.
• Backup is not available, nor are archived redo log files

System information:
OS - RHEL Server Release 7.5 (Maipo)
Oracle Linux Server Release 7.5 (12c)

How did we land up in this situation:

• rm *.DBF in DBF_FILES/ folder; this deleted system01.dbf, system02.dbf (one file being of system tablespace and one sysaux)

• two instances of oracle were running and we were removing files of other instance

• restarted ec2 instance

Current Situation:

• database is starting in mounted mode only
• I have a AMI backup of the server after the deletion and restart of instance
• All data files are intact
• Control file is intact
• Redo archive logs not available; all we have is some redo logs (thought of using redo logs to re-create the entire thing; in line with what postgreSQL offers with WriteAheadLog); but could not find correct resource maybe. (more on this in approaches taken)
• A one year old backup of the same database is available which is working but there have been many changes from that time; unluckily the dev does not have any record of what he changed!

Approaches I've tried:

• Tried recovering the folder with extundelete, foremost, testdisk (sysadmin type approaches)
• The files removed were available on an old backup and I tried copying the same and starting the database
• I also created a trace file from the control file and removed the deleted files from trace file and tried starting the database.
• Also tried RECOVER DATAFILE '<empty_file.dbf>' by creating empty files of the same name

Happy to connect over video/audio/screen-share if need be.

Answer 1

OS backups of the data files will only be valid if the database was shutdown at the time the backup was made, otherwise they most likely will not work because they will not account for the state of any data that was cached being written to disk when the backup was made. The system tablespace in particular is problematic because it contains the entire data dictionary for the database. Without it, the other data files are largely useless.

All data files must be synced to the same system change number that is in the control files before you'll be able to open the database; if you don't have archived redo logs to reapply missing transactions then you'd have to restore all data files and control files from the same cold/offline backup and accept whatever data loss that implied.

I would advise opening a ticket with Oracle Support, but without an RMAN backup or archived redo log files I don't think you're going to have many options. Maybe a recent datapump export of the data that you could use to populate a new db instance?