This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Aug 11, 2013 10:12 PM by yxes2013 RSS

Test recovery with unsupported parameters

yxes2013 Newbie
Currently Being Moderated

Hi all,

 

10g (10.2.0.3)

WinXP

 

I have a TEST database  whose undo tablespace has been corrupted with ORA 600.

I tried all means but to no avail It wont start up. Since this is just a TEST. I want to recover it by force,

using the unsupported parameters, just to extract my big test data.

Is there such parameter to up the database even if inconsistent?

What really matters are my test data and I dont need them to be consistent.

I tried:

_corrupted_rollback_segments but still not working.

 

Can I attach the tablespace to another database?

Just like in mysql and sqlserver you can attach dbf to another newdb easily.

 

 

Thanks a lot,

 

zxy

  • 1. Re: Test recovery with unsupported parameters
    sb92075 Guru
    Currently Being Moderated

    My car is not working.

    Tell me how to make my car  go.

     

    >Can I attach the tablespace to another database?

    NO!

  • 2. Re: Test recovery with unsupported parameters
    Alvaro Pro
    Currently Being Moderated

    Hello,

     

    First of all please post your ORA-0600 that you are getting, the complete message.

     

    The arguements will tell you what kind of corruptiong you're suffering from there you can try to adress it. However what I can tell you is this, if your data is corrupted it's already gone!!

     

    You have 2 options:

     

    1) Restore/recover the data: You can do this at multiple levels: block, datafile, tablespace or entire database

     

    2) Salvage whats left and drop the corrupted rest.

     

    This is for when corruption is on data segments, from what you've told us it looks like you're dealing with either UNDO or Redo log corruption which is a bit trickier to deal with. Please post full Alert log from the time of startup to the crash.

     

    Regards,

    Alvaro

  • 3. Re: Test recovery with unsupported parameters
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Al , I hope Sb will be as good as you

     

    Here are my error logs, The  were caused by power flunctuations + the server are very old hence the corruption

     

    https://app.box.com/s/9r0tdlgw73i08qc9bbp4

    https://app.box.com/s/kq98tofw96q8l6l8pxgf

    https://app.box.com/s/vxne1qfb18anb3byoa9e

    https://app.box.com/s/spk3ukw80bny3dlwhu9a

     

    Thanks a lot,

  • 4. Re: Test recovery with unsupported parameters
    Alvaro Pro
    Currently Being Moderated

    Thanks Al , I hope Sb will be as good as you

    SB is an old and respected member of the community which does not have to prove anything. I'm glad to help but please refrain from these type of comments... I will review your logs shortly

  • 5. Re: Test recovery with unsupported parameters
    sb92075 Guru
    Currently Being Moderated

    >I have a TEST database  whose undo tablespace has been corrupted with ORA 600.

    >I tried all means but to no avail It wont start up.

     

    You most likely made a bad situation worse & guaranteed the DB is not recoverable.

     

    In the future should you ever find yourself in a similar situation, the FIRST thing to do is take a complete cold backup of every disk volume that contains any file owned by oracle.

    By doing so, you can always get back to the same starting point.

     

    Oracle Service can recover some or most of the data if you are willing to write them a check for a VERY large amount.

     

    How is it that you post a new disaster almost weekly here?

    You seem to have a rare talent for blowing up any software that you access with anything more than read-only privileges.

  • 6. Re: Test recovery with unsupported parameters
    yxes2013 Newbie
    Currently Being Moderated

    Thanks SB

    >Can I attach the tablespace to another database?

    NO!


    Then what is the use of  this "transportable" tablespace when it can not help during disasters?



  • 7. Re: Test recovery with unsupported parameters
    sb92075 Guru
    Currently Being Moderated

    yxes2013 wrote:

     

    Thanks SB

    >Can I attach the tablespace to another database?

    NO!


    Then what is the use of  this "transportable" tablespace when it can not help during disasters?



    So prove me wrong by showing how it can be done  using COPY & PASTE so we can see all the steps.

  • 8. Re: Test recovery with unsupported parameters
    yxes2013 Newbie
    Currently Being Moderated

    Hi All,

     

    I converted uploaded file to text as it cant be viewed as (.ora , .trc, .ctl)

    Here are the new  links:

    https://app.box.com/s/tpcvkbu36uyjclb71fyj

    (trace file)

    https://app.box.com/s/vjlhtokolkdqy2py9fgv

    (control file)

    https://app.box.com/s/hpj3pjlrpc4nk0ide3zb 

    alert.log

    https://app.box.com/s/wreptfkd7vl94p9d2lb2

    init.ora

     

    Thanks a lot

  • 9. Re: Test recovery with unsupported parameters
    Justin Cave Oracle ACE
    Currently Being Moderated

    1) Realistically, given your history, I'm going to strongly suggest that you log a ticket with Oracle Support and get them to assist you with the recovery.  Otherwise, it is highly likely that this thread is going to go on to many, many pages and you're probably going to make multiple mistakes that will make the recovery more difficult.  Your organization pays support, you're better off taking advantage of that. 

     

    Plus, if your previous claims are correct that your organization is dealing with sensitive data and has some security concerns, posting the amount of data you already have and will need to in order to resolve this problem to a public forum is insane.  You're giving a potential attacker a wealth of information that they can use to attack your organization.  Sure, this is only a test database.  But, presumably, your test database has at least a passing resemblance to your production database.

     

    2) Transportable tablespaces are used to quickly move data between databases.  If you read anything about transportable tablespaces, though, you'll see that you need to prepare the tablespace to be unplugged which requires that the database instance is up and running.

     

    Justin

  • 10. Re: Test recovery with unsupported parameters
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Justin,

    I just thought you, and Al, and Sb are equally better off with the support 

    3 heads are better than 1

  • 11. Re: Test recovery with unsupported parameters
    Alvaro Pro
    Currently Being Moderated

    Hi yxes,

     

    After reviewing your logs there is two things I can tell you:

     

    1. You are facing UNDO corruption, and the worst kind, which is System undo segment corruption. This means you can't workaround by setting the UNDO MANUAL and forcing it open with _corrupted_rollback_segments as you have attempted.

     

    2. I'm afraid you have only one (supported) choice left: Full restore and recover

     

    There are some other parameters which you can set to force the database to avoid the roll forward operation and force the database open in an unknown state but I'm not going to tell you to use them as they are unsupported.

     

    What I would recommend is this: Either do a complete recovery from last backup and archivelogs or log an SR with support.

     

    This database is doomed, if you absolutely need the data there which hasn't been backed up, SR will guide you in forcing it open to salvage the data and then importing into a new database.

     

    Good luck!!

  • 12. Re: Test recovery with unsupported parameters
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Al,

     

    Honestly, our license  just lapsed huhuhu for 10g, and Our backup is overwritten with a corrupt backup too.

    I just want to extract the inconsistent data as we know them very well and we can reconstruct the corrupted ones.

    Please share me this procedure:

    There are some other parameters which you can set to force the database to avoid the roll forward operation and force the database open in an unknown state but I'm not going to tell you to use them as they are unsupported.


    Pleaseeeee.....I will be terminated soon


    Thanks

  • 13. Re: Test recovery with unsupported parameters
    Alvaro Pro
    Currently Being Moderated

    Hi yxes,

     

    I'm sorry I cannot guide you in this as I don't have access to you environment and there are too many variables unknown to us here at the forums. You never use unsupported parameters without support telling you so! Without extensive analysis it can makes things worse.

  • 14. Re: Test recovery with unsupported parameters
    sb92075 Guru
    Currently Being Moderated

    >Honestly, our license  just lapsed huhuhu

    Honestly, you are a known & repeated liar.

     

    >.I will be terminated soon

    Not soon enough; since you have survived this long simply by mooching off of strangers.

     

1 2 Previous Next

Incoming Links

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points