1 2 Previous Next 29 Replies Latest reply: Aug 12, 2013 12:12 AM by yxes2013 RSS

    Test recovery with unsupported parameters

    yxes2013

      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

          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

            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

              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

                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

                  >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

                    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

                      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

                        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
                          JustinCave

                          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

                            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

                              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

                                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

                                  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

                                    >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