4 Replies Latest reply: Jan 30, 2013 11:54 AM by EdStevens RSS

    Recover a Database to an older state

    David_Pasternak
      Hi everybody,

      i have the following problem:

      At a testdatabase instance of Oracle 11g (11.2.0.3.0) at CentOS 6 i've imported a tablespace from another instance to test my backup strategy.

      First i've done a fullbackup with RMAN:

      ----------
      # rman target /
      RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
      ----------

      Then i thought to test the database i could delete something and restore and recover the database from the backup. So i deleted a table in the imported tablespace:

      ----------
      # sqlplus / as sysdba
      SQL> drop table TESTTABLE;
      ----------

      After that i would start the restore and recover process by restoring and recovering the tablespace in wich I deleted the table with RMAN. At first i've set the tablespace offline:

      ----------
      SQL> alter tablespace TESTTABLESPACE offline immediate;
      ----------

      Then I go to RMAN and start the restoring:

      ----------
      RMAN> restore tablespace TESTTABLESPACE;
      ----------

      Now there is a problem when i execute the recover command. The recover command includes the recovering from the redolog files, right? As I droped the table from the tablespace this operation was written in the redolog files. So when i make a :

      ----------
      RMAN> recover tablespace TESTTABLESPACE;
      ----------

      The dropped tablespace would be dropped also, because the last redolog file told RMAN to drop the table. So i think I have to restore and recover my tablespace from an older timestamp, so RMAN would ignore the dropping of the table in the redolog file. Is that right so?

      And when it is right how can i restore a tablespace from an older date so RMAN would ignore the dropping?

      Thanks for help,

      Best regards,
      David
        • 1. Re: Recover a Database to an older state
          UweHesse
          There is an operation for that called Tablespace Point In Time Recovery (TSPITR)
          http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmtspit.htm#i1014116

          It's a more advanced technique, though, nothing for starters in my opinion.

          Kind regards
          Uwe Hesse

          "Don't believe it, test it!"
          http://uhesse.com
          • 2. Re: Recover a Database to an older state
            vlethakula
            Do tablespace point in time recovery (TS-PITR).
            Check: http://docs.oracle.com/cd/B13789_01/server.101/b10734/rcmtspit.htm
            • 3. Re: Recover a Database to an older state
              David_Pasternak
              Thats is TSPITR helps!!!!
              • 4. Re: Recover a Database to an older state
                EdStevens
                985104 wrote:
                Hi everybody,

                i have the following problem:

                At a testdatabase instance of Oracle 11g (11.2.0.3.0) at CentOS 6 i've imported a tablespace from another instance to test my backup strategy.

                First i've done a fullbackup with RMAN:

                ----------
                # rman target /
                RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
                ----------

                Then i thought to test the database i could delete something and restore and recover the database from the backup. So i deleted a table in the imported tablespace:

                ----------
                # sqlplus / as sysdba
                SQL> drop table TESTTABLE;
                ----------

                After that i would start the restore and recover process by restoring and recovering the tablespace in wich I deleted the table with RMAN. At first i've set the tablespace offline:

                ----------
                SQL> alter tablespace TESTTABLESPACE offline immediate;
                ----------

                Then I go to RMAN and start the restoring:

                ----------
                RMAN> restore tablespace TESTTABLESPACE;
                ----------

                Now there is a problem when i execute the recover command. The recover command includes the recovering from the redolog files, right? As I droped the table from the tablespace this operation was written in the redolog files. So when i make a :

                ----------
                RMAN> recover tablespace TESTTABLESPACE;
                ----------

                The dropped tablespace would be dropped also, because the last redolog file told RMAN to drop the table. So i think I have to restore and recover my tablespace from an older timestamp, so RMAN would ignore the dropping of the table in the redolog file. Is that right so?

                And when it is right how can i restore a tablespace from an older date so RMAN would ignore the dropping?

                Thanks for help,

                Best regards,
                David
                Hint: those long lines you use to seperate code from comment really make the head spin. Better to use the "code" tags - \
                 - on  a seperate line just before and just after the "code".  So as you write it, it looks like this:
                
                \
                rman> show all;
                \
                and when posted it looks like this:
                rman> show all;
                BTW, the reason the tags actually show in the first example is because I 'escaped' the opening "{"  with a "\"