Tablespace Point In Time Recovery

*************************TABLESPACE POINT IN TIME RECOVERY******************
 Download this file

-->Purpose of RMAN TSPITR

Recovery Manager (RMAN) TSPITR enables quick recovery of one or more tablespaces in a database to an earlier time without affecting the rest of the tablespaces and objects in the database.

RMAN TSPITR is most useful for the following situations:

    To recover a logical database to a point different from the rest of the physical database, when multiple logical databases exist in separate tablespaces of one physical database. For example, you maintain logical databases in the orders and personnel tablespaces. An incorrect batch job or data manipulation language (DML) statement corrupts the data in only one tablespace.

   1) To recover data lost after data definition language (DDL) operations that change the structure of tables. You cannot use Flashback Table to rewind a table to before the point of a structural change such as a truncate table operation.

    2) To recover a table after it has been dropped with the PURGE option.

    3) To recover from the logical corruption of a table.

    4)To recover dropped tablespaces. In fact, RMAN can perform TSPITR on dropped tablespaces even when a recovery catalog is not used.

EXAMPLE STEP BY STEP FOR TSPITR
-------------------------------------
1) CREATE A TABLESPACE;
2) CREATE AN USER AND ASSIGNED THIS NEWLY CREATED TABLESPACE AS DEFAULT TABLESPACE
3) CREATE ANY DATABASE OBJECT IN THIS USER SCHAMA.(WE WILL CREATE A TABLE)
4) PERFORM SOME DML OPERATION
5) NOTE THE TIME OR SCN WHERE YOU WANT TO GO BACK IN TIME/SCN
6) DROP TABLE WITH PURGE OPTION
7) CONNECT TO RMAN TO PERFORM THE RECOVERY
8) CHECK THE TABLE, YOU WILL GET BACK THE TABLE OF DESIRE TIME/SCN.WE WILL BE ABLE TO SEE THE PURGE TABLE BACK


COMMANDS  lets start :)
----------
1) CREATE TABLESPACE TBS100 DATAFILE '/u01/app/oracle/oradata/orcl/tbs100.dbf' size 10m;
2) CREATE USER PUSHPJEET IDENTIFIED BY ORACLE DEFAULT TABLESPACE TBS100;
   GRANT CONNECT,RESOURCE TO PUSHPJEET;
3) CONN PUSHPJEET/PUSHPJEET
   CREATE TABLE EXAMPLE(ID NUMBER,NAME VARCHAR2(20));
4) INSERT INTO EXAMPLE VALUES(1,'INDIA');
   INSERT INTO EXAMPLE VALUES(2,'USA');
   COMMIT;
5) SELECT TIMESTAMP_TO_SCN(SYSDATE) SCN FROM DUAL;
 
6) DROP TABLE EXAMPLE PURGE;
 --CHECK IT IS PURGED OR NOT
   SELECT * FROM EXAMPLE;
--YOU WILL GET ERROR TABLE OR VIEW DOES NOT EXIST
7) $rman target /
    RMAN>recover tablespace tbs100
         until scn 12345                                   --give the scn you recorded earlier
         auxiliary destination '/u01/';
8) conn PUSHPJEET/ORACLE
   SELECT * FROM EXAMPLE;

--YOU WILL BE ABLE TO SEE YOUR TABLE IS BACK...