select current_scn from v$Database
	scn = 1415638

// do sql operations

Then I attempted to recover to scn 1415638 (the point before I did the 
sql operations that mucked up the database)

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 26 14:29:34 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

RMAN> connect target /

connected to target database: ORCL (DBID=1118541070)

RMAN> recover tablespace MY_DATA until scn 1415638 auxiliary destination 'c:\ee
k';

Starting recover at 26-MAY-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=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='wfcj'

initialization parameters used for automatic instance:
db_name=ORCL
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_ORCL_wfcj
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=c:\eek
control_files=c:\eek/cntrl_tspitr_ORCL_wfcj.f


starting up automatic instance ORCL

Oracle instance started

Total System Global Area     201326592 bytes

Fixed Size                     1248092 bytes
Variable Size                146801828 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2945024 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until  scn 1415638;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
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 26-MAY-06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\FLASH
_RECOVERY_AREA\ORCL\BACKUPSET\2006_05_26\O1_MF_NCSNF_TAG20060526T142307_27GWN1LK
_.BKP
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_05
_26\O1_MF_NCSNF_TAG20060526T142307_27GWN1LK_.BKP tag=TAG20060526T142307
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output filename=C:\EEK\CNTRL_TSPITR_ORCL_WFCJ.F
Finished restore at 26-MAY-06

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until  scn 1415638;
plsql <<<-- tspitr_2
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'MY_DATA' ||' offline for recover';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set an omf destination filename for restore
set newname for clone datafile  1 to new;
# set an omf destination filename for restore
set newname for clone datafile  2 to new;
# set an omf destination tempfile
set newname for clone tempfile  1 to new;
# set a destination filename for restore
set newname for datafile  6 to
 "C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\MY_DATA";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile  1, 2, 6;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile  1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace  "MY_DATA", "SYSTEM", "UNDOTBS1" delete archi
velog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
#           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace MY_DATA offline for recover

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to C:\EEK\TSPITR_O\DATAFILE\O1_MF_TEMP_%U_.TMP in contr
ol file

Starting restore at 26-MAY-06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=39 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\EEK\TSPITR_O\DATAFILE\O1_MF_SYSTEM_%U_.DBF
restoring datafile 00002 to C:\EEK\TSPITR_O\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF
restoring datafile 00006 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\MY_DATA
channel ORA_AUX_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\FLASH
_RECOVERY_AREA\ORCL\BACKUPSET\2006_05_26\O1_MF_NNNDF_TAG20060526T142307_27GWFVPG
_.BKP
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_05
_26\O1_MF_NNNDF_TAG20060526T142307_27GWFVPG_.BKP tag=TAG20060526T142307
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:46
Finished restore at 26-MAY-06

datafile 1 switched to datafile copy
input datafile copy recid=5 stamp=591460549 filename=C:\EEK\TSPITR_O\DATAFILE\O1
_MF_SYSTEM_27GX0JJ3_.DBF
datafile 2 switched to datafile copy
input datafile copy recid=6 stamp=591460549 filename=C:\EEK\TSPITR_O\DATAFILE\O1
_MF_UNDOTBS1_27GX0K3F_.DBF

sql statement: alter database datafile  1 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  6 online

Starting recover at 26-MAY-06
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 21 is already on disk as file C:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_05_26\O1_MF_1_21_27GWZNB8_.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2006_05_26\O1_MF_1_21_27GWZNB8_.ARC thread=1 sequence=21
media recovery complete, elapsed time: 00:00:04
Finished recover at 26-MAY-06

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=oracle)(ARGV0
=oraclewfcj)(ARGS=^'(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))^')(ENVS=^'
ORACLE_SID=wfcj^'))(CONNECT_DATA=(SID=wfcj))) as sysdba\" point_in_time_recover=
y tablespaces=
 MY_DATA file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace  MY_DATA online";
sql "alter tablespace  MY_DATA offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script


Export: Release 10.2.0.1.0 - Production on Fri May 26 14:36:58 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
EXP-00008: ORACLE error 29308 encountered
ORA-29308: view TS_PITR_CHECK failure
ORA-06512: at "SYS.DBMS_PITR", line 887
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
host command complete

database closed
database dismounted
Oracle instance shut down


Import: Release 10.2.0.1.0 - Production on Fri May 26 14:37:39 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
IMP-00009: abnormal end of export file
Import terminated successfully with warnings.
host command complete

sql statement: alter tablespace  MY_DATA online

Removing automatic instance
shutting down automatic instance
target database instance not started
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/26/2006 14:37:58
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on default channel at 05/26/2006 14:37:58
RMAN-11003: failure during parse/execution of SQL statement: alter tablespace  M
DB_DATA online
ORA-01190: control file or data file 6 is from before the last RESETLOGS
ORA-01110: data file 6: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\MY_DATA'

RMAN>

dbadmin@9i > get ts
  1  select obj1_name,ts1_name,obj2_name,ts2_name from sys.ts_pitr_check
  2* where obj1_owner='DBADMIN'
dbadmin@9i > /

OBJ1_NAME  TS1_NAME   OBJ2_NAME  TS2_NAME
---------- ---------- ---------- ----------
EMP        USERS      MYINDEX    TOOLS

RMAN> recover tablespace ts1,ts2 until ....

set ORACLE_SID ora9
rman target="/" cmdfile="do_backup"
sqlplus> select dbms_flashback.get_system_change_number()
<Remember that>

// do tablespace mucking stuff

set ORACLE_SID aux
rman> connect target /@ora9
<connected to target database ora9 ID=x>
connect auxiliary /
<connected to auxiliary database nomount>
restore tablespace my_tablespace until scn X

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
skipping datafile 11; already restored to file C:\ORACLE\ORADATA\MYPATH
restore not done; all files readonly, offline, or already restored
Finished restore at 30-JUN-06