is there a way to restore oracle database from file system backup

1
votes

i have oracle DB 11g for tester and one junior DBA removed one of system tablespace files from OS by mistake and DB in no archive mode and i tried to recreate file in mount mode and recover database but couldn't as there are no archivelogs . if i have filesystem backup for datafiles and control files can i restore the database from it . or just use the old control file to restore database to earlier point before creation of that dropped system datafile ?

i tried to take it offline first and then tried to recreate it but for first step it tells me that file is offline and database can't start and for second method it ask me to recover datafile which i can't because of no archive mode .

1- alter database datafile '/data/oradata/umiasdb/users55.dbf' offline drop;

ORA-01147: SYSTEM tablespace file 139 is offline ORA-01110: data file 139: '/data/oradata/umiasdb/users55.dbf'

2- ALTER DATABASE CREATE DATAFILE '/data/oradata/umiasdb/users55.dbf' AS '/data/oradata/umiasdb/users55.dbf'; ORA-01113: file 139 needs media recovery ORA-01110: data file 139: '/data/oradata/umiasdb/users55.dbf'

1
sqloracle11goperating-systemrestoredatabase-administration
Was the database shut down before the file system backup (or is this a backup done by some sort of point-in-time-consistent SAN technology)? If you want to rely on a file system backup, you traditionally need to be doing a cold backup with the database shut down. It may be possible to do a hot backup with the database running using some SAN snapshot technologies as well but that'll be specific to your particular SAN. – Justin Cave
no database was up so i think i can't use filesystem backup . is there any way to use tablespace datafiles in newly created DB as i can't restore the system tablespace missing file ? . or do you have any suggestion how to use datafiles to extract important schemas to any new DB ? – saad
It sounds like you're probably out of luck unless you want to invest some serious cash in emergency recovery tools. A potentially painful lesson in the need for backups I'm afraid. – Justin Cave

1 Answers

0
votes

You can't easily open the database in this case. To force-open the database, you need: _ALLOW_RESETLOGS_CORRUPTION and bbed (block edit tool) to edit your datafile header in hex.

You can also try unload data by data unload software as can be seen here.