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: March 27, 2018 - 5:14 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

  (1 rating)

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.
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 latest video and Chris's latest video 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.