0

At a client site with a development Oracle 11.2.0.3/linux DB

power outage and crash - no archivelog mode - no backups - Don't know how this was even running

select file#,substr(name,1,70) name, recover, status, fuzzy, checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time from v$datafile_header; FILE# NAME REC STATUS FUZ CHECKPOINT_CHANGE# CHECKPOINT_TIME RESETLOGS_CHANGE# RESETLOGS_TIME 1 /oracle/oradata/orcl/system01.dbf NO ONLINE YES 4663440487 08-MAR-2018 02:57:17 945184 04-APR-2012 12:07:59 2 /oracle/oradata/orcl/sysaux01.dbf YES ONLINE YES 3759618229 21-DEC-2016 22:19:41 945184 04-APR-2012 12:07:59 11 /oracle/oradata/orcl/rmdata01.dbf YES ONLINE YES 3759618229 21-DEC-2016 22:19:41 945184 04-APR-2012 12:07:59

Basic plan: alter database datafile 2 offline drop; alter database datafile 11 offline drop; recover database ; export/import rebuild DB

Don't care about sysaux, however, I hope to keep whatever is in datafile 11.

I was hoping to force the DB open even with datafile 11 in a bad state.

Oracle says any hidden parameters will not help due to very old SCNs.

Have you faced this situation and successfully recovered the data?

Improve this question
6
  • 1
    The one time I tried to recover with a no archivelog mode and no backups, Oracle support closed the ticket with "Nothing can be done."
    – CaM
    Commented Mar 15, 2018 at 19:41
  • @CaM Agreed, but just grasping for one final trick to help the client.
    – Marinaio
    Commented Mar 15, 2018 at 19:49
  • Your development code is in a Code Repository. Right?
    – Michael Kutz
    Commented Mar 15, 2018 at 20:58
  • I've heard people talk about reading the data directly from disk and piecing the data together that way. Would be painful but I guess possible if you really need it.
    – Bobby Durrett
    Commented Mar 15, 2018 at 22:40
  • @Bobby Durrett Is that something you've heard through Oracle or did someone come up with their own code?
    – Marinaio
    Commented Mar 15, 2018 at 22:48

1 Answer 1

Reset to default
0

For anyone who wants to know how this was resovled...

Bobby Durrett was correct, Oracle does have the ability to pull data from blocks.

I contacted Oracle ACS. They sent a top consultant and using the Data UnLoader tool (DUL), which by the way is not their product, were able to extract data from the blocks that survived the corruption.

Bascially you configure the tool with a few parameters and identify the datafiles of interest. It helps a lot if the system data file is present. You can tell it to create SQL*Loader or dmp files. It will skip any block that is corrupt and move on to the next block. The datafiles are read cold so no need to mount or create a new instance or any such thing.

Caveat: All you get is table DDL and data. No users, contraints, FK so your are not recovering the database, just the data.

It works beautifully - on any block not corrupted!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.