Skip to Main Content
  • Questions

  • Restore and Recover with no backup noarchivelog mode

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, oracle.

Asked: June 01, 2017 - 4:53 am UTC

Last updated: October 10, 2025 - 2:30 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hi team,

I have a database which is in no archivelog mode and NO Backup like rman and cold backup are available. Only logical Backup (export schema) is available. And suppose one datafile is corrupted or dropped like rm then how can i restore and recover the database or datafile with only export schema backup.


Please let me know as per my understanding only create a entire new database then import the exported backup but any another way to make less down time for the same Pls let me know.


Thanks

and Connor said...

"I have a database which is in no archivelog mode and NO Backup like rman and cold backup are available"

That sentence is like a statement saying "I dont care if I really lose all the data" so perhaps that is the first predicament to be tackled in your workplace.

If a datafile is corrupted, you need to work out what was present in that datafile. You can query DBA_EXTENTS to get the name of every segment that contained something in that datafile.

Then you could drop only those segments in the database, and import them from the dump file.

But of course, you are probably very likely to end up with a *logical* corruption, because some of your database tables are at the current point in time, and some of your tables will be at the point in time of your export dump. And logical corruptions are nasty because they can be "sleeper" problems, ie, you might discover them hours, days or weeks after the fact.

Let's say you import those tables - then in 2 weeks time you find a fault in your application. Is it *really* a bug in the application ? Is it just bad data from the half-recovery ? Lots of headaches there.

Perhaps the best way forward is to

a) restore the entire dump file so you have better consistency,
b) put some measures in place to make sure it never happens again.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

oracle oracle, March 25, 2018 - 5:35 pm UTC

Thanks for reply.

Ok i will restore entire dump but
Q.1 When system datafile dropped then db will not function at that time how to open the database.

Q.2 If Your created tablespace datafile dropped then how to tackle that need to drop that entire tablespace with datafile and then new create that tablespace and then import the dump ?

Thanks
Connor McDonald
March 27, 2018 - 5:14 am UTC

Q1. Without a system tablespace....you are toast.
Q2. You are back at what you surmised earlier - importing.

Good

Souleymane OUATTARA, October 02, 2025 - 1:51 pm UTC

When the user says that the database data file is corrupted, it assumes that he is unable to open the database. So when the database is not open, how can we execute the query on the DBA_EXTENTS view ?
Connor McDonald
October 03, 2025 - 1:25 am UTC

When the user says that the database data file is corrupted, it assumes that he is unable to open the database

Why? Every datafile except SYSTEM and SYSAUX can be corrupted and there is a very good chance your database is either still open, or can be opened

Souleymane OUATTARA, October 09, 2025 - 7:50 pm UTC

But sometimes, the only datafile in the SYSTEM tablespace is corrupted. and we don't have a physical backup (rman) and the database has never been put into archivelog mode. How can we restore this database?
Connor McDonald
October 10, 2025 - 2:30 am UTC

If SYSTEM is corrupted and you don't have a backup, then you're out of luck (with standard recovery) because we no longer have a record of what is actually even IN that database.

Even with block level recovery style tools, eg https://www.ora600.nl/DUDE_PRIMER.pdf

you would be doing something like

- extract objects definitions SYSTEM tablespace
- use those definitions to try extract data from the other datafiles

Or enlisting the help of external consultancy to try hack the SYSTEM datafile back to a reasonable state.

All of those options are a LOT of pain
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.