Tablespace Point In Time Recovery ( New Concept in 11gR2)

Step 1: Create Tablespace and User

SQL> create tablespace krish datafile '/u02/app/oracle/oradata/orcl/krish.dbf' size 100m;

Tablespace created.

SQL> create user krish identified by krish default tablespace krish;

User created.

SQL> grant connect,resource to krish;

Grant succeeded.

SQL> conn krish/krish

Connected.

SQL> create table test(name varchar2(20),id number(10));

Table created.

SQL> insert into test values('krishna',9490876084);

1 row created.

SQL> insert into test values('bhargava',9490049980);

1 row created.

Step 2: Backup database Plus archivelog

[oracle@mydb ORCL]$ rman target/

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Feb 14 12:24:50 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1302441650)

RMAN> backup database plus archivelog;

RMAN> exit

Recovery Manager complete.

Step 3: Note Current SCN

[oracle@mydb ORCL]$ sqlplus / as sysdba

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    1127273

Step 4: Drop tablespace

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

Step 5: I just added data for another table allocated for other tablespace  to check for data consistent after recovery.

SQL> conn hr/hr

Connected.

SQL> create table krishna(id number(10));

Table created.

SQL> insert into krishna values(123);

1 row created.

SQL> insert into krishna values(345);

1 row created.

SQL>commit;

Step 6: Create auxiliary & Recover tablespace using tablespace point in time recovery.

RMAN> recover tablespace krish until scn 1127273 auxiliary destination '/orabackup/new';

Starting recover at 14-FEB-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=147 device type=DISK

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='vCDv'

initialization parameters used for automatic instance:

db_name=ORCL

db_unique_name=vCDv_tspitr_ORCL

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/orabackup/new

log_archive_dest_1='location=/orabackup/new'

#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area     292933632 bytes

Fixed Size                     1336092 bytes

Variable Size                100666596 bytes

Database Buffers             184549376 bytes

Redo Buffers                   6381568 bytes

Automatic instance created

List of tablespaces that have been dropped from the target database:

Tablespace krish

contents of Memory Script:

{

# set requested point in time

set until  scn 1127273;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

# avoid unnecessary autobackups for structural changes during TSPITR

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';

}

executing Memory Script

executing command: SET until clause

Starting restore at 14-FEB-12

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=59 device type=DISK

allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: SID=10 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /orabackup/orcl/rman/c-1302441650-20120214-02

channel ORA_AUX_DISK_1: piece handle=/orabackup/orcl/rman/c-1302441650-20120214-02 tag=TAG20120214T104920

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/orabackup/new/ORCL/controlfile/o1_mf_7mmwwy85_.ctl

Finished restore at 14-FEB-12

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:

{

# set requested point in time

set until  scn 1127273;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  3 to new;

set newname for clone datafile  2 to new;

set newname for clone tempfile  1 to new;

set newname for datafile  7 to

 "/u02/app/oracle/oradata/orcl/krish.dbf";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 3, 2, 7;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /orabackup/new/ORCL/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 14-FEB-12

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00002 to /orabackup/new/ORCL/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /orabackup/orcl/rman/stream2_0tn39qc9_1_1

channel ORA_AUX_DISK_2: starting datafile backup set restore

channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_2: restoring datafile 00001 to /orabackup/new/ORCL/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_2: restoring datafile 00003 to /orabackup/new/ORCL/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_2: restoring datafile 00007 to /u02/app/oracle/oradata/orcl/krish.dbf

channel ORA_AUX_DISK_2: reading from backup piece /orabackup/orcl/rman/stream1_0sn39qc8_1_1

channel ORA_AUX_DISK_1: piece handle=/orabackup/orcl/rman/stream2_0tn39qc9_1_1 tag=TAG20120214T104632

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:20

channel ORA_AUX_DISK_2: piece handle=/orabackup/orcl/rman/stream1_0sn39qc8_1_1 tag=TAG20120214T104632

channel ORA_AUX_DISK_2: restored backup piece 1

channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:02:32

Finished restore at 14-FEB-12

datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=775221141 file name=/orabackup/new/ORCL/datafile/o1_mf_system_7mmwx6g9_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=775221141 file name=/orabackup/new/ORCL/datafile/o1_mf_undotbs1_7mmwx76q_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=7 STAMP=775221141 file name=/orabackup/new/ORCL/datafile/o1_mf_sysaux_7mmwx5r5_.dbf

contents of Memory Script:

{

# set requested point in time

set until  scn 1127273;

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  3 online";

sql clone "alter database datafile  2 online";

sql clone "alter database datafile  7 online";

# recover and open resetlogs

recover clone database tablespace  "KRISH", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  7 online

Starting recover at 14-FEB-12

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2

starting media recovery

archived log for thread 1 with sequence 20 is already on disk as file /u02/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_14/o1_mf_1_20_7mmvpptd_.arc

archived log for thread 1 with sequence 21 is already on disk as file /u02/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_14/o1_mf_1_21_7mmwx40t_.arc

archived log file name=/u02/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_14/o1_mf_1_20_7mmvpptd_.arc thread=1 sequence=20

archived log file name=/u02/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_14/o1_mf_1_21_7mmwx40t_.arc thread=1 sequence=21

media recovery complete, elapsed time: 00:00:01

Finished recover at 14-FEB-12

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace  KRISH read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/orabackup/new''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/orabackup/new''";

}

executing Memory Script

sql statement: alter tablespace  KRISH read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/orabackup/new''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/orabackup/new''

Performing export of metadata...

   EXPDP> Starting "SYS"."TSPITR_EXP_vCDv":

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   EXPDP> Master table "SYS"."TSPITR_EXP_vCDv" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_vCDv is:

   EXPDP>   /orabackup/new/tspitr_vCDv_68935.dmp

   EXPDP> ******************************************************************************

   EXPDP> Datafiles required for transportable tablespace KRISH:

   EXPDP>   /u02/app/oracle/oradata/orcl/krish.dbf

   EXPDP> Job "SYS"."TSPITR_EXP_vCDv" successfully completed at 11:15:10

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone immediate

}

executing Memory Script

database closed

database dismounted

Oracle instance shut down

Performing import of metadata...

   IMPDP> Master table "SYS"."TSPITR_IMP_vCDv" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_vCDv":

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   IMPDP> Job "SYS"."TSPITR_IMP_vCDv" successfully completed at 11:16:01

Import completed

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace  KRISH read write';

sql 'alter tablespace  KRISH offline';

# enable autobackups after TSPITR is finished

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';

}

executing Memory Script

sql statement: alter tablespace  KRISH read write

sql statement: alter tablespace  KRISH offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance

Automatic instance removed

auxiliary instance file /orabackup/new/ORCL/datafile/o1_mf_temp_7mmx2j08_.tmp deleted

auxiliary instance file /orabackup/new/ORCL/onlinelog/o1_mf_3_7mmx2b4x_.log deleted

auxiliary instance file /orabackup/new/ORCL/onlinelog/o1_mf_2_7mmx25nd_.log deleted

auxiliary instance file /orabackup/new/ORCL/onlinelog/o1_mf_1_7mmx217t_.log deleted

auxiliary instance file /orabackup/new/ORCL/datafile/o1_mf_sysaux_7mmwx5r5_.dbf deleted

auxiliary instance file /orabackup/new/ORCL/datafile/o1_mf_undotbs1_7mmwx76q_.dbf deleted

auxiliary instance file /orabackup/new/ORCL/datafile/o1_mf_system_7mmwx6g9_.dbf deleted

auxiliary instance file /orabackup/new/ORCL/controlfile/o1_mf_7mmwwy85_.ctl deleted

Finished recover at 14-FEB-12

RMAN>exit

SQL> select status,tablespace_name from dba_tablespaces where tablespace_name like 'KRISH%';

STATUS    TABLESPACE_NAME

--------- ------------------------------

OFFLINE   KRISH

SQL> alter tablespace krish online;

Tablespace altered.

SQL> select file#, status from v$datafile;

     FILE# STATUS

---------- -------

         1 SYSTEM

         2 ONLINE

         3 ONLINE

         4 ONLINE

         5 ONLINE

         6 ONLINE

         7 ONLINE

7 rows selected.

SQL> conn krish/krish

Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

TEST                           TABLE

SQL> select * from test;

NAME                         ID

-------------------- ----------

Krish                9490876084

Bhargava             9490049980

You can verify the other table which was created after step-5