5 Replies Latest reply: Mar 12, 2010 9:20 AM by Hemant K Chitale RSS

    Restore single table

    759056
      I've accidentally truncated a table for the wrong schema and I need to get the data back. I realize this isn't an easy task with rman, but I'm looking for advice for the best method.

      I am running Oracle Standard Edition 11.1.0.6.0 on Linux. A cold backup of this database is created every week with rman. It is not in archivelog mode, since it would generate too many log files and all the changes are fairly easy to replicate. The only method I know of is to restore the entire database on another machine, then export the table/data from the restored database and import it into the truncated table. This will take quite a bit of time (a few or several days), and seems to contain a lot of unnecessary work for the computers involved. The table was only a few 100 MB, and the DB is 1TB+.

      Is there any way to restore just the tablespace with the truncated table on a new server? Can this somehow be done in another database instance on the same computer? If I can restore just one tablespace, but have to do it on a different computer, can I avoid having to transfer the entire rman backup to the new server? Is there anything like flashback that will help me on Oracle Standard Edition? Are there any other suggestions for restoring the one table more quickly?
        • 1. Re: Restore single table
          Hemant K Chitale
          Yes, you can do a manual restore of the SYSTEM + UNDO + Tablespace_containing_that_table Tablespaces alone.
          You would have to set all other datafiles offline.

          It would be safer to do this on another server -- which does mean that you have to copy the Backup (or at least all the backupsets that contain these tablespaces).


          In order to attempt to do this on the same server you'd have to change the DBID and DB_NAME of the "clone" even before you OPEN it. That means using the DBNEWID (nid) utility -- but that would expect to update all the datafiles from the controlfile (unless you try this with having done OFFLINE DROP for all the datafiles).

          On the other hand, if you install a new Oracle_Home and use that Oracle_Home you can configure a new init.ora file. Next, use RMAN to CATALOG the backupsets. Next RESTORE only the datafiles of the selected Tablespaces (SYSTEM+UNDO+RequiredTBS). Ensure that you use SET NEWNAME for the datafiles so that the Restore does not overwrite your production database !_ Then OPEN database RESETLOGS. (Note : Do not use the RECOVER command). You could also new nid to rename this database.

          Then export the table out.

          And import it into your production database.



          Hemant K Chitale
          http://hemantoracledba.blogspot.com

          Edited by: Hemant K Chitale on Mar 10, 2010 2:10 PM
          • 2. Re: Restore single table
            759056
            Thanks for the info. I tried installing Oracle 11g on my only other Linux server in our data center, but the version of Linux is too old. I will try installing another Oracle_Home on the same server and restoring the necessary tablespaces there.
            • 3. Re: Restore single table
              759056
              I think everything almost worked. I restored the datafile on a new instance, along with the system, sysaux, undo and users tables. I renamed and opened the new database, and set everything up so I could query it from the database where I accidentally truncated the table.

              I logged into the first database, and I ran "select count(*) from my_table@new_orcl;". It ran fine.

              I ran "insert into my_table (select * from my_table@new_orcl);", and it gave me an error:
              ORA-03113: end-of-file on communication channel
              Process ID: 28835
              Session ID: 295 Serial number:1679

              SQL> select count(*) from my_table;
              ERROR:
              ORA-03114: not connected to ORACLE

              SQL> quit
              servername $sqlplus my_username/my_password
              ERROR:
              ORA-01034: ORACLE not available
              ORA-27101: shared memory realm does not exist
              servername $ sqlplus / as sysdba
              SQL> startup
              ...
              ORA-00338: log 3 of thread 1 is more recent than control file
              ORA-00312 online log 3 thread 1: ...

              I issued a recover until cancel, and put in the file name of the current logfile when it asked for it, but it tells me file 1 needs more recovery to be consistent. The only error I have in the alert log is a reference to a .trc file with repeated warnings "WARNING:Oracle process running out of OS kernel I/O resources" and the ORA-00338/ORA-00312 errors before the instance terminated.

              I don't know what happened, other than there are a few relevant bugs about this on my oracle support and I need to restore the 1TB database now. Based on my experiences with Oracle support on severity 1 requests, I can probably get this database restored about as fast as they will decide to tell me to restore the database from a backup. Thanks for your help.
              • 4. Re: Restore single table
                611900
                Flashback could have helped you a lot in this case. Although it takes some space in UNDO but will be of great help in the errors cases like these.
                • 5. Re: Restore single table
                  Hemant K Chitale
                  SQL> select count(*) from my_table;
                  ERROR:
                  ORA-03114: not connected to ORACLE
                  This happened in your "first" database -- i.e. the existing one as it is right now. Not in the restored database.

                  You should then your database logs as to why your current (live) database instance went down.
                  ORA-00338: log 3 of thread 1 is more recent than control file
                  ORA-00312 online log 3 thread 1: ...
                  Your controlfile is corrupt or has been restored from a prior image. Did you happen to overwrite your current controlfile when restoring to the secondary instance ?


                  Hemant K Chitale