This discussion is archived
4 Replies Latest reply: Jan 30, 2013 9:54 AM by EdStevens RSS

Recover a Database to an older state

David_Pasternak Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thats is TSPITR helps!!!!
  • 4. Re: Recover a Database to an older state
    EdStevens Guru
    Currently Being Moderated
    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 "\"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Legend

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