Flash Back Concepts

Files stored in Flash Recovery Area(FRA)

Redo log file and control file are permant file resides in FRA remainig file are deleted by FRA
become obsolete or already backed up to media

(1)Flashback logs:
if  Flashback enabled option enabled in the database. every changes in blocks belongs to the datafile
will be recorded as a image copy in flash recovery area named flashback log.

(2)Control file:
The multiplexed copy of current control file will e stored in FRA

(3)Control file of Autobackups:
In RMAN settin "CONFIGURE CONTROLFILE AUTOBACKUP ON" then it copies
(RMAN automatically backs up the control file and the current server parameter file)
(a) when a successful backup must be recorded in the RMAN repository
(b) when a structural change to the database affects the contents of the control file
(c) Whenever you (add /drop) ,changing the status in(online/offline/read only),  (rename/resize,/increase /decrease) the size of datafileor tablespace
(d) When adding a new online redo log or adding a new redo thread.

(4)Online Redo log files:
multiplexed copy of redo log file are allow to store in FRA

(5)Archived Redo log files
FRA allow store your archived redo log file but any space issue in FRA Rman automatically will delete the files 

Flash Recovey feature of 11g

SQL> desc v$flashback_database_log;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------
 OLDEST_FLASHBACK_SCN          NUMBER - (minimum SCN to which you can flash back the database)  
 OLDEST_FLASHBACK_TIME                  DATE   - (earliest time to which you can flash back the database)
 RETENTION_TARGET                        NUMBER - (how long flashback logs are retained, in minutes)
 FLASHBACK_SIZE                              NUMBER - (size of flashback logs as of now)
 ESTIMATED_FLASHBACK_SIZE                  NUMBER - ( The estimated size of the total flashback     logs retained to satisfy the retention target at the end of this time interval, shown in the column END_TIME)

***To calculate (using logs available in flash recovery area) how far to rewind the database to the previous states.

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
             1040830 21-SEP-12             1440       15941632
                       0
The value of the column OLDEST_FLASHBACK_SCN is 1040830, which indicates you can
flash back to the SCN up to that number only, not before that.

***(OR)

SQL> select to_char(oldest_flashback_time,'mm/dd/yy hh24:mi:ss') from v$flashback_database_log;

TO_CHAR(OLDEST_FL
-----------------
09/21/12 16:57:23

***To Calculate the total Amount of Logs Generated in Flashback Area at several timestamps
SQL> select * from v$flashback_database_stat order by begin_time;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
21-SEP-12 21-SEP-12        2998272    2785280    1826816                        0

***(OR)-- To get the exact BEGIN TIME AND END TIME of the log generation

SQL> alter session set nls_date_format = 'mm/dd/yy hh24:mi:ss';
Session altered.

SQL> select * from v$flashback_database_stat order by begin_time;

BEGIN_TIME        END_TIME          FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
----------------- ----------------- -------------- ---------- ---------- ------------------------
09/21/12 16:57:23 09/21/12 17:20:49        2998272    2785280    1876992                        0


***To get the individual Size of Logs Generated in Flashback Area at every period

SQL> select end_time, estimated_flashback_size from v$flashback_database_stat order by 1;


END_TIME          ESTIMATED_FLASHBACK_SIZE
----------------- -----------------------0
09/21/12 17:22:04                154224128
09/25/12 20:37:57                137567258
10/25/12 22:28:20                194224128
              

***To Calculate the total Space used by Logs in the Flash Recovery Area

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_ RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ----------------- ---------------- -------------- ------------------------
             1040830 09/21/12 16:57:23             1440       15941632                169648128


***Flashing Back a Database from "RMAN Prompt" (or) "SQL prompt"

***Presteps for FRA using "RMAN Prompt" (or) "SQL prompt"
1.To calculate (using logs available in flash recovery area) how far to rewind the database to the previous states.
SQL> select * from v$flashback_database_log;
2. rman target=/
3. RMAN> shutdown immediate (or) SQL> shutdown immediate
4. RMAN> startup mount      (or) SQL> startup mount

***Poststeps of FRA using "RMAN Prompt" (or) "SQL prompt"
1. RMAN> alter database open read only (or) SQL> alter database open read only
NOTE: to verify desired information available or againg follow the presteps of FRA THEN FOLLOW STEP 2
2.RMAN> alter database open resetlogs;  (or) SQL> alter database open resetlogs;
Note: always use open the database with resetlogs to create new incarnation of scn number

***Flashing Back a Database using specific point-in-time, specified by date and time
1.presteps of FRA
2.RMAN (or) SQL> flashback database to timestamp to_date('5/18/2007 00:00:00','mm/dd/yyyy hh24:mi:ss');
(OR)
RMAN> flashback database to time 'sysdate-2/60/24';
3.poststep of FRA


***Flashing Back a Database using specific SCN number
SQL> select current_scn from v$database;

1.prestep of FRA
2.RMAN (or) SQL> flashback database to scn 4587962;
3.poststep of FRA

***Flashing Back a Database using last Resetlogs operation
SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
1548632

1.prestep of FRA
2.RMAN> flashback database to before resetlogs;
3.poststep of FRA

***Flashing Back a Database using named Restore-Point
1.prestep of FRA
RMAN (or) SQL> flashback database to restore point restore_point1;
3.poststep of FRA

Note: restore point can be (create, create with guarantee, list, drop)
SQL> create restore point rp1;
SQL> create restore point rp2 guarantee flashback database;-- create guarantee RS-point
SQL> select * from v$restore_point order by 2,1; --- list the RS-point
SQL> drop restore point rp2;


Recovering a object from Recycle bin

SQL> show recyclebin
SQL> select * from user_recyclebin;
SQL> alter session set recyclebin = on; ---The recycle bin is enabled by default.
SQL> alter session set recyclebin = off;
SQL> alter system set recyclebin = off; ---To disable for the entire database (not recommended):
SQL> alter system set recyclebin = on;

SQL> purge recyclebin;-- remove all dropped objects from the recyclebin (system wide):


***Recovering table related objects( constraint, index, trigger, )
***Display the Constraints of the table:

SQL> select constraint_type, constraint_name from user_constraints where table_name = 'SAMPLE';
C CONSTRAINT_NAME
- -----------------------------
P BIN$sdlkolirkfjHDFsdfskjks==$0 ---- P(PRIMARY)
C BIN$584ASRDJGThTINjOISVM3Q==$0 ---- C(CHECK )

SQL> alter table accounts rename constraint "BIN$sdlkolirkfjHDFsdfskjks==$0" to pk_SAMPLE;
Table altered.

SQL> alter table accounts rename constraint "BIN$584ASRDJGThTINjOISVM3Q" to ck_SAMPLE_01;
Table altered.


***Display the Index of the table:

SQL> select index_name from user_indexes where table_name = 'SAMPLE';
INDEX_NAME
-----------------------------
BIN$sdlkolirkfjHDFsdfskjks==$0
BIN$584ASRDJGThTINjOISVM3Q==$0

SQL> alter index "BIN$sdlkolirkfjHDFsdfskjks==$0" rename to IN_SAMPLE_01;
Index altered.

SQL> alter index "BIN$584ASRDJGThTINjOISVM3Q==$0" rename to IN_SAMPLE_02;
Index altered.



***To check the index restored properly

Finally, make sure the indexes are in place and have correct names:
SQL> select index_name from user_indexes where table_name = 'SAMPLE';

INDEX_NAME
-----------------------------
IN_SAMPLE_01
IN_SAMPLE_02

***Display the trigger of the table:

SQL> select trigger_name from user_triggers;
TRIGGER_NAME
-----------------------------
"BIN$sdlkolirkfjHDFsdfskjks==$0"


***Rename triggers to their original names:

SQL> alter trigger "BIN$sdlkolirkfjHDFsdfskjks==$0" rename to tr_sample_01;
Trigger altered.

*Check the triggers now to make sure they are named as they were originally:
SQL> select trigger_name from user_triggers;
TRIGGER_NAME
-----------------------------
TR_SAMPLE_01

Recover Dropped Table (No Flashback enable )

No Flashback required to be enable in the database  just set the parameter value RECYCLEBIN='TRUE' then we can recover dropped table from the recyclebin
flashback table <dropped_table> to before drop )

SQL> conn /as sysdba
Connected.


SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
SAMTBS1                        ONLINE

6 rows selected.

SQL> conn tom/tom
Connected.

SQL> select * from tab; 

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TABLE1                         TABLE
TABLE2                         TABLE
TABLE3                         TABLE

SQL> select * from table2;

        ID
----------
       987

SQL> insert into table2 VALUES (675);

1 row created.

SQL> insert into table2 VALUES (698);

1 row created.

SQL> insert into table2 VALUES (695);

1 row created.

SQL> select * from table2;

        ID
----------
       987
       675
       698
       695

SQL> commit;
Commit complete.

SQL> conn /as sysdba
Connected.

SQL> drop table tom.table2;
Table dropped.


SQL> commit;
Commit complete.

SQL> select * from tom.table2;
select * from tom.table2
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn /as sysdba
Connected.


SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin;
no rows selected

SQL> conn tom/tom
Connected.

SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$yj4kFALrck/gQKjAAqgojA==$0 TABLE2

SQL> flashback table table2 to before drop;
Flashback complete.

SQL> select * from table2;

        ID
----------
       987
       675
       698
       695

SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin;

no rows selected

RMAN Commands (DISK, TAPE)

RMAN> list backupset;
RMAN> list backupset of database;
RMAN> list backupset of database completed before '14-JAN-11';
RMAN> list backupset of archivelog all;
RMAN> list backupset of tablespace users;
RMAN> list backupset of datafile 1;

RMAN> list backup summary;
RMAN> list backup;
RMAN> list backup of controlfile;
RMAN> list backup of tablespace SYSTEM;
RMAN> list backup by file;
RMAN> list backup of archivelog all ;

RMAN> list copy of database;
RMAN> list copy of database archivelog all;

RMAN> allocate channel for maintenance type disk;
RMAN> configure channel device type disk clear ;

RMAN> report need backup days=2 database;
RMAN> report need backup days=10 tablespace TEMP;
RMAN> report need backup days=4 datafile '/u01/app/oracle/backup/sample/TEMP01.DBF';
RMAN> report schema;
RMAN> report obsolete;

RMAN> crosscheck backup;
RMAN> crosscheck backupset;
RMAN> crosscheck copy;
RMAN> crosscheck archivelog all;
RMAN> crosscheck controlfilecopy '/u01/app/oracle/admin/BACKUP/demo/demo_controlfile_bak_03-15-11_19:59:11' ;

RMAN> delete noprompt expired backup ;
RMAN> delete noprompt obsolete;
RMAN> delete archivelog all;
RMAN> delete expired archivelog all;
RMAN> delete archivelog all completed before 'sysdate -1';
RMAN> delete noprompt archivelog until time 'sysdate - 1';
RMAN> delete archivelog all backed up 1 times to device type disk completed before 'sysdate-1';
 
***configure controlfile using RMAN
$ rman target / nocatalog
RMAN>configure controlfile autobackup on;
RMAN>configure retention policy to recovery window of 12 days;

***Init.ora or (spfile) Parameters shows Backup media and retention policy of controlfile
backup_tape_io_slaves=TRUE
control_file_record_keep_time=12

***Init.ora parameters to speed up backup

sort_area_size = 20971520
sort_area_retained_size = 20971520

***Allocating Channels For disk Backup

RMAN>allocate channel c1 type disk format '/u01/app/oracle/backup/sample/%U';

***Allocating Channels for Tape Backup

RMAN> allocate channel ch1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_MTBackup_Tier3)';

***Backup database+archivelog+controlfile

$ rman target / nocatalog
RMAN>backup as compressed backupset
full
maxsetsize = 32G
filesperset = 1024
diskratio=0
tag 'Full'

****Backup database + current controlfile

PLUS ARCHIVELOG skip inaccessible format '/u01/app/oracle/backup/sample/ar_%d_%t_%c_%s_%p';

***Backup database+archive log

$ rman target / nocatalog
#Backup to disk
RMAN> backup device type disk format '/u01/app/oracle/backup/sample/%U' database
plus archivelog skip inaccessible delete input;

#Backup to Tape
RMAN> backup as compressed backupset
full
filesperset = 1024
diskratio = 0
tag = 'Full'
(database format 'SBT_TAPE/RMAN_%d.%t.full.%c.%s.%p')
plus archivelog
skip inaccessible
delete input
format 'SBT_TAPE/RMAN_%d.%t.arch.%c.%s.%p';

***Backup database+controlfile

RMAN>backup as compressed backupset
full
maxsetsize = 32G
filesperset = 1024
diskratio = 0
tag = 'Full'
(database include current controlfile) keep until time 'SYSDATE+365' nologs;


***Backup Controlfile for standby database

RMAN> backup device type disk format '/u01/app/oracle/backup/sample/%U' current controlfile for standby;

***Backup Controlfile

Option # 1
RMAN>copy current controlfile to '/u01/app/oracle/backup/sample/backup/demo_controlfile.bak';

Option # 2
RMAN>backup current controlfile format '/u01/app/oracle/backup/sample/%d_controlfile_bak_%T_s%s';

*****Note : Control file is automatically backed up when
RMAN>configure controlfile autobackup on;


***Backup the archive log
#Backup to disk
RMAN>backup as compressed backupset
maxsetsize =5G
diskratio = 0
skip inaccessible
tag 'Archive Logs'
(archivelog all NOT BACKED UP 1 TIMES format '/u01/app/oracle/backup/sample/ar_%d_%t_%c_%s_%p');

#Backup To Tape
RMAN> backup as compressed backupset
maxsetsize = 5G
diskratio = 0
skip inaccessible
tag = 'Archive Logs'
(archivelog all delete input format'SBT_TAPE/RMAN_%d.%t.arch.%c.%s.%p');


***Command to maintain the backup by RMAN

delete force noprompt obsolete recovery window of 12 days;
crosscheck backup;
delete force noprompt expired backup;

***Report the backupsets
list backup;

Tablespace Point-in-Time Recovery (TSPITR) 11gR2 using RMAN

11g Release 2 has new features, which enables to recover from a dropped tablespace using TSPITR. In the earlier version of oracle we could not recover a dropped tablespace using TSPITR (tablespace point in time recovery)

SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/jetway/jatway/users01.dbf
USERS

/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
UNDOTBS1

/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSAUX

/u01/app/oracle/oradata/jetway/jatway/system01.dbf
SYSTEM

SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/jetway/jatway/users01.dbf
USERS

/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
UNDOTBS1

/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSAUX

/u01/app/oracle/oradata/jetway/jatway/system01.dbf
SYSTEM

/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SAMTBS1

SQL> create user tom identified by tom default tablespace samtbs1;
User created.

SQL> grant connect,resource to tom;
Grant succeeded.

SQL> conn tom/tom                 
Connected.

SQL> create table table1 ( id number);
Table created.

SQL> insert into table1  values (123);
1 row created.

SQL> create table table2 ( id number);
Table created.

SQL> insert into table2  values (987);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from tab; 
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TABLE1                         TABLE
TABLE2                         TABLE

SQL> conn / as sysdba
Connected.

SQL> alter system switch logfile;
System altered.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1022086

[oracle@sfo ~]$ .  oraenv 
ORACLE_SID = [jetway] ? jatway
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

[oracle@sfo ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 21 12:42:42 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: JATWAY (DBID=3196377116)

RMAN>

RMAN> backup as compressed backupset database format '/u01/app/oracle/backup/backupjatway/%d_%s_%I_%T' plus archivelog format '/u01/app/oracle/backup/backupjatway/%d_%s_%I_%T';

Starting backup at 21-SEP-12
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=10 STAMP=793527386
input archived log thread=1 sequence=12 RECID=11 STAMP=793527425
input archived log thread=1 sequence=13 RECID=12 STAMP=793527610
input archived log thread=1 sequence=14 RECID=13 STAMP=793530126
input archived log thread=1 sequence=15 RECID=14 STAMP=793530172
input archived log thread=1 sequence=16 RECID=15 STAMP=793530212
input archived log thread=1 sequence=17 RECID=16 STAMP=793531476
input archived log thread=1 sequence=18 RECID=17 STAMP=793532833
input archived log thread=1 sequence=19 RECID=18 STAMP=793532851
input archived log thread=1 sequence=20 RECID=19 STAMP=793533374
input archived log thread=1 sequence=21 RECID=20 STAMP=793533390
input archived log thread=1 sequence=22 RECID=21 STAMP=793533982
input archived log thread=1 sequence=23 RECID=22 STAMP=793545410
input archived log thread=1 sequence=24 RECID=23 STAMP=793545414
input archived log thread=1 sequence=25 RECID=24 STAMP=793545621
input archived log thread=1 sequence=26 RECID=25 STAMP=794570848
input archived log thread=1 sequence=27 RECID=26 STAMP=794570853
input archived log thread=1 sequence=28 RECID=27 STAMP=794580052
input archived log thread=1 sequence=29 RECID=28 STAMP=794580314
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_29_3196377116_20120921 tag=TAG20120921T124515 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 21-SEP-12

Starting backup at 21-SEP-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/jetway/jatway/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/jetway/jatway/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_30_3196377116_20120921 tag=TAG20120921T124519 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_31_3196377116_20120921 tag=TAG20120921T124519 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-SEP-12

Starting backup at 21-SEP-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=29 STAMP=794580386
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_32_3196377116_20120921 tag=TAG20120921T124627 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-SEP-12

RMAN> exit

Recovery Manager complete.
[oracle@sfo ~]$

[oracle@sfo ~]$ .  oraenv 
ORACLE_SID = [jetway] ? jatway
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

[oracle@sfo ~]$sqlplus "/as sysdba"

SQL> conn tom/tom
Connected.

SQL> create table table3 ( id number);
table created

SQL> select * from table3;
        ID
----------
       654

SQL> commit;
Commit complete.

SQL> alter system switch logfile;
System altered.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1022829

SQL> drop tablespace samtbs1 including contents and datafiles;
Tablespace dropped.

SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/jetway/jatway/users01.dbf
USERS
/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
UNDOTBS1
/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSAUX
/u01/app/oracle/oradata/jetway/jatway/system01.dbf
SYSTEM


[oracle@sfo ~]$ . oraenv
ORACLE_SID = [jatway] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@sfo ~]$ rman target=/

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 21 13:03:47 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: JATWAY (DBID=3196377116)

RMAN> recover tablespace samtbs1 until scn 1022829  auxiliary destination '/u01/app/oracle/backup/backupjatway';

Starting recover at 21-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 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='csxD'

initialization parameters used for automatic instance:
db_name=JATWAY
db_unique_name=csxD_tspitr_JATWAY
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/u01/app/oracle/backup/backupjatway
log_archive_dest_1='location=/u01/app/oracle/backup/backupjatway'
#No auxiliary parameter file used

starting up automatic instance JATWAY
Oracle instance started

Total System Global Area     292278272 bytes

Fixed Size                     2212736 bytes
Variable Size                100666496 bytes
Database Buffers             184549376 bytes
Redo Buffers                   4849664 bytes
Automatic instance created


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

contents of Memory Script:
{
# set requested point in time
set until  scn 1022829;
# 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 21-SEP-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 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 /u01/app/oracle/backup/backupjatway/JATWAY_31_3196377116_20120921
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_31_3196377116_20120921 tag=TAG20120921T124519
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/backup/backupjatway/JATWAY/controlfile/o1_mf_85sl2cxp_.ctl
Finished restore at 21-SEP-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 1022829;
# 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  5 to
 "/u01/app/oracle/oradata/jetway/jatway/samtbs01.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, 5;
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 /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 21-SEP-12
using channel ORA_AUX_DISK_1

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 00001 to /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/backupjatway/JATWAY_30_3196377116_20120921
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_30_3196377116_20120921 tag=TAG20120921T124519
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 21-SEP-12

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=794581539 file name=/u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_system_85sl2l55_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=794581539 file name=/u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_undotbs1_85sl2l5l_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=794581539 file name=/u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_sysaux_85sl2l5g_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 1022829;
# 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  5 online";
# recover and open resetlogs
recover clone database tablespace  "SAMTBS1", "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  5 online

Starting recover at 21-SEP-12
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 30 is already on disk as file /u01/app/oracle/oradata/jetway/jatway/arch/1_30_793069279.dbf
archived log for thread 1 with sequence 31 is already on disk as file /u01/app/oracle/oradata/jetway/jatway/arch/1_31_793069279.dbf
archived log for thread 1 with sequence 32 is already on disk as file /u01/app/oracle/oradata/jetway/jatway/arch/1_32_793069279.dbf
archived log file name=/u01/app/oracle/oradata/jetway/jatway/arch/1_30_793069279.dbf thread=1 sequence=30
archived log file name=/u01/app/oracle/oradata/jetway/jatway/arch/1_31_793069279.dbf thread=1 sequence=31
archived log file name=/u01/app/oracle/oradata/jetway/jatway/arch/1_32_793069279.dbf thread=1 sequence=32
media recovery complete, elapsed time: 00:00:01
Finished recover at 21-SEP-12

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  SAMTBS1 read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/backup/backupjatway''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/backup/backupjatway''";
}
executing Memory Script

sql statement: alter tablespace  SAMTBS1 read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/backup/backupjatway''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/backup/backupjatway''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_csxD": 
   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_csxD" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_csxD is:
   EXPDP>   /u01/app/oracle/backup/backupjatway/tspitr_csxD_55279.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace SAMTBS1:
   EXPDP>   /u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_csxD" successfully completed at 13:06:40
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_csxD" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_csxD": 
   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_csxD" successfully completed at 13:06:57
Import completed


contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  SAMTBS1 read write';
sql 'alter tablespace  SAMTBS1 offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace  SAMTBS1 read write

sql statement: alter tablespace  SAMTBS1 offline

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

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_temp_85sl4ssp_.tmp deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/onlinelog/o1_mf_3_85sl4q6w_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/onlinelog/o1_mf_2_85sl4pbv_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/onlinelog/o1_mf_1_85sl4oct_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_sysaux_85sl2l5g_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_undotbs1_85sl2l5l_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_system_85sl2l55_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/controlfile/o1_mf_85sl2cxp_.ctl deleted
Finished recover at 21-SEP-12

RMAN>exit

SQL> select file_name,tablespace_name from dba_data_files; 

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/jetway/jatway/users01.dbf
USERS
/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
UNDOTBS1
/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSAUX
/u01/app/oracle/oradata/jetway/jatway/system01.dbf
SYSTEM
/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SAMTBS1

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                          ONLINE
SYSAUX                          ONLINE
UNDOTBS1                     ONLINE
TEMP                               ONLINE
USERS                             ONLINE
SAMTBS1                        OFFLINE

SQL> alter tablespace samtbs1 online;
Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                          ONLINE
SYSAUX                          ONLINE
UNDOTBS1                     ONLINE
TEMP                               ONLINE
USERS                             ONLINE
SAMTBS1                        ONLINE

SQL> conn tom/tom
Connected.

SQL> select * from tab; 
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TABLE1                         TABLE
TABLE2                         TABLE
TABLE3                         TABLE