Test case: flashback table

scott@TEST10G> drop table t;

Table dropped.

scott@TEST10G> create table t(a int);

Table created.

scott@TEST10G> insert into t values(1);

1 row created.

scott@TEST10G> insert into t values(2);

1 row created.

scott@TEST10G> commit;

Commit complete.

scott@TEST10G> select sysdate from dual;

SYSDATE
-------------------
2008-11-16 13:23:44


scott@TEST10G> insert into t values(3);

1 row created.

scott@TEST10G> insert into t values(4);

1 row created.

scott@TEST10G> commit;

Commit complete.

scott@TEST10G> select * from t;

         A
----------
         1
         2
         3
         4


scott@TEST10G> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

scott@TEST10G> alter table t enable row movement; 

Table altered.

scott@TEST10G> flashback table t to timestamp to_date('2008-11-16 13:23:44');

Flashback complete.

scott@TEST10G> select * from t;

         A
----------
         1
         2



Must enable row movment:

scott@TEST10G> alter table t disable row movement;

Table altered.

scott@TEST10G>
scott@TEST10G> select table_name, row_movement from user_tables where table_name='T';

TABLE_NAME                     ROW_MOVE
------------------------------ --------
T                              DISABLED

7 rows selected.

scott@TEST10G> select * from t;

         A
----------
         1
         2

scott@TEST10G> flashback table t to timestamp to_date('2008-11-16 13:23:44');
flashback table t to timestamp to_date('2008-11-16 13:23:44')
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


scott@TEST10G>

Note: to flashback to 15 min ago, we can use the following syntax:

SQL> flashback table hr.employees, hr.departments
to timestamp systimestamp - interval '15' minute;

DBVerify can not deal with datafile which name spans two lines

In one case, we need to issue 'DBV' to check the block corruption of a 9.2.0.8 production database datafiles. However, there were two datafiles which names span two lines and dbv command failed on such filenames even with quotation marks. (of course multiple line filename is not desired and must be there due to carelessness).

We end up copying these two files with proper names, then run the DBV on the copied files.

Though it looks like a very small maintenance task, I tested the following procedure in order to feel more comfortable when fixing it in the maintenance windows.

Test Steps:
(1) create the bad file name - multiple line file name

SQL> create tablespace temp_tbs datafile '/tmp/tmp_ 
  2  data_01.dbf' size 10M;
 

Tablespace created.



bxxxxd2:/tmp [sxxxxxt2] $ ls -l tmp*
-rw-r-----   1 oracle   dba      10493952 Mar 30 19:59 tmp_
data_01.dbf

(2) shutdown database

(3) copy
cp "tmp_
data_01.dbf" tmp_data_01_good.dbf

Note: the double quotation mark is necessary.

(4) mount database


(5) rename

SQL> ALTER DATABASE RENAME FILE '/tmp/tmp_
data_01.dbf' to '/tmp/tmp_data_01_good.dbf';  2  
 
Database altered.
 

(6) open the database

SQL> alter database open;

Database altered.

(7) drop the temp_tbs

SQL> drop tablespace temp_tbs including contents and datafiles;

Tablespace dropped.

A test case - Compare two methods to archive a table

In this test, our goal is to archive a table called archsim_smt under schema dennis. We delete the rows from this table and insert the rows into another table under dennis_arch schema. The row that is selected to be delete/insert is based on column order_id. Each order_id has 100 rows in the table archsim_smt. Order_id has a FK constraint with reference to the table archsim_ssd. Each master_order_id in the table archsim_ssd has 50 order_id. So when we input a particular master_order_id, we will end up archiving 5000 rows in the table archsim_smt.

The objects used in the test are set up by following script:

----- setup.sql ---------------

 
-- create two tables in dennis
create table dennis.archsim_ssd 
nologging
as
select
        rownum                  order_id,
        1+ mod(rownum,20)        master_order_id,
        rpad(rownum,10)         small_vc_ssd,
        rpad(rownum,&m_pad)     padding_ssd
from all_objects
where rownum <= 1000
;

/* one master_order_id has 50 order_id;
   one order_id has 100 id_smt;
*/
create table dennis.archsim_smt 
nologging
as
with generator as (
        select  --+ materialize
                rownum  id
        from    all_objects
        where   rownum <= 1000
)
select
        rownum                  id_smt,
        1+mod(rownum,1000)      order_id,
        trunc((rownum-1)/5)     small_num_smt,
        rpad(rownum,10)         small_vc_smt,
        rpad(rownum,&m_pad)     padding_smt
from 
        generator v1,
        generator v2
where rownum <= 100000
;

-- create two empty table in dennis_arch
create table dennis_arch.archsim_ssd
as select * from dennis.archsim_ssd
where 1=0;

create table dennis_arch.archsim_smt
as select * from dennis.archsim_smt
where 1=0;

create unique index dennis.archsim_ssd_pk on dennis.archsim_ssd(order_id); 
create unique index dennis.archsim_smt_pk on dennis.archsim_smt(id_smt); 
alter table dennis.archsim_ssd add constraint archsim_ssd_pk primary key(order_id);
alter table dennis.archsim_smt add constraint archsim_smt_pk primary key(id_smt);
alter table dennis.archsim_smt add constraint archsim_smt_fk foreign key(order_id) references dennis.archsim_ssd;

begin
        dbms_stats.gather_table_stats(
                user,
                'archsim_ssd',
                cascade => true,
                estimate_percent => null,
                method_opt => 'for all columns size 1'
        );
end;
/

begin
        dbms_stats.gather_table_stats(
                user,
                'archsim_smt',
                cascade => true,
                estimate_percent => null,
                method_opt => 'for all columns size 1'
        );
end;
/

This test was performed by executing a script: comp_test.sql, which is shown below.

-------------- Script: comp_test.sql -------------------

rem  comp_test.sql
rem
rem    Comparing two methods to archive a table archsim_smt in 
rem    the dennis schema.
rem
rem
rem    Two methods are different base on how to select the rows for archiving:
rem
rem    Method 1 - single SQL
rem
rem      insert into dennis_arch.archsim_smt
rem      select * from archsim_smt
rem      where order_id in (
rem          select order_id
rem            from archsim_ssd
rem           where master_order_id = 1 );
rem
rem     delete from dennis.archsim_smt
rem      where order_id in (
rem      select order_id
rem        from archsim_ssd
rem       where master_order_id = 1 );
rem
rem      commit;
rem
rem    Method 2 - PLSQL LOOP
rem     ( similar approach is used in the production database right now)
rem       declare
rem         inorderid number;
rem         cursor allorderids is
rem             select distinct order_id
rem              from dennis.archsim_ssd
rem             where  master_order_id = 1;
rem       begin
rem         open allorderids;
rem
rem         FETCH allorderids INTO inorderid;
rem
rem         WHILE allorderids % FOUND
rem         LOOP
rem           INSERT
rem           INTO dennis_arch.archsim_smt
rem           SELECT *
rem             FROM dennis.archsim_smt
rem            WHERE order_id = inorderid;
rem
rem           delete from dennis.archsim_smt
rem             where order_id = inorderid;
rem
rem           inorderid := NULL;
rem           FETCH allorderids INTO inorderid;
rem         END LOOP;
rem
rem         commit;
rem         close allorderids;
rem       end;
rem       /
rem
rem   A tool called Runstat is used to compare two methods. 
rem   
rem   Runstats is a tool developed by Tom Kyte to compare two 
rem   different methods of doing the same thing and show which
rem   one is superior. (http://asktom.oracle.com/tkyte/runstats.html)
rem
rem   Test was done in a 11 g database.
rem

spool comp_test

exec runstats_pkg.rs_start;

-- method 1

@@setup
@@method_1
select count(*) from dennis.archsim_smt;
select count(*) from dennis_arch.archsim_smt;

exec runstats_pkg.rs_middle;

-- method 2

@@setup
@@method_2
select count(*) from dennis.archsim_smt;
select count(*) from dennis_arch.archsim_smt;

exec runstats_pkg.rs_stop;

Results

Run1 ran in 1339 hsecs
Run2 ran in 3333 hsecs
run1 ran in 40.17% of the time

Name                                          Run1      Run2      Diff
STAT...write clones created in           0           1           1
LATCH.PL/SQL warning settings          123         122          -1
LATCH.cp sga latch                       1           0          -1
LATCH.ncodef allocation latch            1           0          -1
LATCH.threshold alerts latch             0           1           1
LATCH.begin backup scn array           301         300          -1
LATCH.dml lock allocation               13          12          -1
LATCH.sort extent pool                  60          59          -1
LATCH.deferred cleanup latch             1           0          -1

...

STAT...redo size                 7,364,552   7,400,712      36,160
LATCH.object queue header oper      33,375      70,180      36,805
LATCH.simulator lru latch            7,650      69,629      61,979
LATCH.simulator hash latch          13,851     125,011     111,160
STAT...consistent gets from ca      55,489     665,732     610,243
STAT...no work - consistent re      53,477     665,735     612,258
STAT...table scan blocks gotte      45,247     657,746     612,499
STAT...consistent gets from ca      60,627     674,250     613,623
STAT...consistent gets              60,627     674,250     613,623
STAT...session logical reads       100,356     714,181     613,825
LATCH.cache buffers chains         197,356     851,159     653,803
STAT...table scan rows gotten      726,518  10,526,397   9,799,879
STAT...physical read total byt 109,912,064 182,550,528  72,638,464
STAT...physical read bytes     109,502,464 182,140,928  72,638,464

Run1 latches total versus Run2 -- different and pct
Run1      Run2      Diff     Pct
391,297   1,279,691     888,394  30.58%

It can be seen from the the Runstat tool, the method 1 has more than 10 times less consistent gets than method 2 ( 60,627 vs 674,250); and also method 1 has much less latches than method 2 (391,297 vs 1,279,691 ). So it is clear that Method 1 is superior. As Tom Kyte said, if someting can be done in simple sql statements,it will be better than PL/SQL precedures generally.

A Testcase for Transportable Tablespace

To be familiar myself with the transportable tablespace feature, I conducted this test:

Test Steps

1. Create a tablespace called TEST_TTS for test

connect / as sysdba
create tablespace test_tts datafile='e:\temp\test_tts01.dbf' size 2M;
connect scott/tiger
create table t tablespace test_tts as
select rownum id, a.* from all_objects a where rownum <100;


2. Check if the tablespace is self-contained (optional)

sys@TEST10GR> execute dbms_tts.transport_set_check('test_tts',true);

PL/SQL procedure successfully completed.

3. Make the tablespace READ ONLY:

SQL>  alter tablespace test_tts read only;

scott@TEST10GR> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
~
TEST_TTS                       READ ONLY

4. Export the tablespace. At the source host, from the OS prompt, issue:

#> exp tablespaces=test_tts transport_tablespace=y file=exp_test_tts.dmp

Export: Release 10.2.0.1.0 - Production on Mon Dec 17 13:38:45 2007

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


Username: sys/oracle as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
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 transportable tablespace metadata...
For tablespace TEST_TTS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                              T
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings

Notes: Must log in as SYSDBA to perform the exp

5. Copy the files exp_test_tts.dmp and e:\temp\test_tts01.dbf to the directory e:\Denis_File\temp at the target host

6. Make the tablespace read write at source database

SQL> alter tablespace test_tts read write;

7. At the target host, plug the tablespace into the target database.
From the OS command prompt, issue:

cd E:\Denis_Files\temp
E:\Denis_Files\temp>ls -lh  *.DBF *.dmp
-rw-r--r-- 1 Denis Sun None 2.1M Dec 17 09:28 TEST_TTS01.DBF
-rw-r--r-- 1 Denis Sun None 4.0K Dec 17 09:40 exp_test_tts.dmp

E:\Denis_Files\temp>imp tablespaces=test_tts transport_tablespace=y file=exp_tes
t_tts.dmp datafiles='E:\Denis_Files\temp\TEST_TTS01.DBF' tts_owners=scott

Import: Release 10.2.0.1.0 - Production on Mon Dec 17 13:45:42 2007

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

Username: sys/oracle as sysdba

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 transportable tablespace(s) metadata...
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table                            "T"
. importing SYS's objects into SYS
Import terminated successfully without warnings.

Notes: we can specify fromuser, touser to map differect schemas.

8. Verify the target database

SQL> select name from v$datafile;

NAME
-------------------------------------------------------------

D:\ORADATA\DB10G\SYSTEM01.DBF
D:\ORADATA\DB10G\UNDOTBS01.DBF
D:\ORADATA\DB10G\SYSAUX01.DBF
D:\ORADATA\DB10G\USERS01.DBF
E:\DENIS_FILES\TEMP\TEST_TTS01.DBF


9. Log into the source and target database, issue

SQL> drop tablespace test_tts including contents and datafiles;




Notes:
-----

Find the byte order of operating systems

select * from v$transportable_platform order by platform_id

PLATFORM_ID PLATFORM_NAME                       ENDIAN_FORMAT
----------- ----------------------------------- --------------
1           Solaris[tm] OE (32-bit)             Big
2           Solaris[tm] OE (64-bit)             Big
3           HP-UX (64-bit)                      Big
4           HP-UX IA (64-bit)                   Big
5           HP Tru64 UNIX                       Little
6           AIX-Based Systems (64-bit)          Big
7           Microsoft Windows IA (32-bit)       Little
8           Microsoft Windows IA (64-bit)       Little
9           IBM zSeries Based Linux             Big
10          Linux IA (32-bit)                   Little
11          Linux IA (64-bit)                   Little
12          Microsoft Windows 64-bit for AMD    Little
13          Linux 64-bit for AMD                Little
15          HP Open VMS                         Little
16          Apple Mac OS                        Big
17          Solaris Operating System (x86)      Little
18          IBM Power Based Linux               Big

RMAN : tablespace point-in-time recovery

I have been asked a question about how to recover a table to a point in the past time using RMAN. I response by saying if in Oracle 10g, we can use Flashback technology. But if using RMAN is only option, we can do tablespace point-in-time recovery(TSPITR). However I was unable to describe the clear conceptual steps about TSPITR. Here I did a test based on the document.

The test case assumes there is only one table: AUTO_TABLE in the tablespace AUTO_TBS.

The situation could be more complicated than what will be shown below if there are other tables in the tablespace that have been updated after the target time to which the tablespace will be recovered.

ref: http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtspit002.htm#i1010246

1. Information about the auto_table

a. auto_table resides in the AUTO_TBS tablespace

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
.....
AUTO_TABLE AUTO_TBS


b. Number of rows

scott@ORCL> select count(*) from auto_table;

COUNT(*)
----------
66065

c. Timestamp just before truncate

scott@ORCL> select sysdate from dual;

SYSDATE
--------------------
07-OCT-2007 19:43:11

scott@ORCL> truncate table auto_table;

Table truncated.

The objective is to recover the auto_table to the point before the truncate.

2. Planing and Preparing for TSPITR


2.1 Choosing the Right Target Time for TSPITR
07-OCT-2007 19:43:11


2.2 Determining the Recovery Set: Analyzing Data Relationships

SELECT *
FROM SYS.TS_PITR_CHECK
WHERE (
TS1_NAME IN ('AUTO_TBS')
AND TS2_NAME NOT IN ('AUTO_TBS')
)
OR (
TS1_NAME NOT IN ('AUTO_TBS')
AND TS2_NAME IN ('AUTO_TBS')
);

Note: There are no complications for this case - no dependencies of auto_table on other objects in auto_tbs or other tablespaces

2.3 Identifying and Preserving Objects That Will Be Lost After TSPITR

SELECT OWNER, NAME, TABLESPACE_NAME,
TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS')
FROM TS_PITR_OBJECTS_TO_BE_DROPPED
WHERE TABLESPACE_NAME IN ('AUTO_TBS')
AND CREATION_TIME > TO_DATE('07-OCT-2007 19:43:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;

Note:
You can preserve such objects, once they are identified, by exporting them before TSPITR using an Oracle export utility (Data Pump Export or Original Export) and re-importing them afterwards using the corresponding import utility.

In this case, we don't have any such objects.

3. Performing Basic RMAN TSPITR

Three options:

A. Fully automated TSPITR
B. Customized TSPITR with an automatic auxiliary instance
C. TSPITR with your own auxiliary instance

3.1 Take the tablespace AUTO_TBS offline

sys@ORCL> alter tablespace auto_tbs offline;

Tablespace altered.

3.2 Rocover using 'Fully automated TSPITR'

Note: Using an Auxiliary Destination for Automated RMAN TSPITR
e:\auxdest

start the RMAN client, connecting to the target database and, if applicable, a recovery catalog.

The following is the RMAN command:

RMAN> RECOVER TABLESPACE auto_tbs
2> UNTIL TIME '07-OCT-2007 19:43:11'
3> AUXILIARY DESTINATION 'e:\auxdest'
4> ;


Appendix A shows the full RMAN screen output.

3.3 Backing Up Recovered Tablespaces After TSPITR
RMAN> BACKUP TABLESPACE AUTO_TBS

3.4 Bring the tablespaces online, as follows:

RMAN> SQL "ALTER TABLESPACE AUTO_TBS ONLINE";


Appendix A:

===========

RMAN> RECOVER TABLESPACE auto_tbs
2>      UNTIL TIME '07-OCT-2007 19:43:11'
3>       AUXILIARY DESTINATION 'e:\auxdest'
4> ;
Starting recover at 07-OCT-2007 20:10:36
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 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
tablespace UNDO_BATCH

Creating automatic instance, with SID='aFbc'

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_aFbc
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=e:\auxdest
control_files=e:\auxdest/cntrl_tspitr_ORCL_aFbc.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  time "07-OCT-2007 19:43:11";
# 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 07-OCT-2007 20:12:09
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 E:\ORAFLASH\ORCL\BACKUPSET\2007_10_07\O1_MF_NCSN0_TAG2007100
LND6K2_.BKP
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=E:\ORAFLASH\ORCL\BACKUPSET\2007_10_07\O1_MF_NCSN0_TAG20071007T173142_3JLND6K2_.BKP tag=TAG2007100
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output filename=E:\AUXDEST\CNTRL_TSPITR_ORCL_AFBC.F
Finished restore at 07-OCT-2007 20:12:48

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  time "07-OCT-2007 19:43:11";
# 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 filename for restore
set newname for clone datafile  8 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
"E:\ORADATA\ORCL\AUTO_TBS_DATA01.DBF";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile  1, 2, 8, 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  8 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  "AUTO_TBS", "SYSTEM", "UNDOTBS1", "UNDO_BATCH" delete archivelog;
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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_TEMP_%U_.TMP in control file

Starting restore at 07-OCT-2007 20:16:10
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 E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_SYSTEM_%U_.DBF
restoring datafile 00002 to E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF
restoring datafile 00008 to E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDO_BAT_%U_.DBF
restoring datafile 00006 to E:\ORADATA\ORCL\AUTO_TBS_DATA01.DBF
channel ORA_AUX_DISK_1: reading from backup piece E:\ORAFLASH\ORCL\BACKUPSET\2007_10_07\O1_MF_NNND0_TAG20071007T173142_3J
LN26F5_.BKP
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=E:\ORAFLASH\ORCL\BACKUPSET\2007_10_07\O1_MF_NNND0_TAG20071007T173142_3JLN26F5_.BKP tag=TAG20071007T173142
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:06:26
Finished restore at 07-OCT-2007 20:23:10

datafile 1 switched to datafile copy
input datafile copy recid=9 stamp=635372617 filename=E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_SYSTEM_3JLXQD89_.DBF
datafile 2 switched to datafile copy
input datafile copy recid=10 stamp=635372617 filename=E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDOTBS1_3JLXQDRW_.DBF
datafile 8 switched to datafile copy
input datafile copy recid=11 stamp=635372618 filename=E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDO_BAT_3JLXQF00_.DBF

sql statement: alter database datafile  1 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  8 online

sql statement: alter database datafile  6 online

Starting recover at 07-OCT-2007 20:24:23
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 9 is already on disk as file E:\ORAFLASH\ORCL\ARCHIVELOG\2007_10_07\O1_MF_1_9_3JLOS56T_.ARC

archive log thread 1 sequence 10 is already on disk as file E:\ORAFLASH\ORCL\ARCHIVELOG\2007_10_07\O1_MF_1_10_3JLXKK6T_.A
RC
archive log filename=E:\ORAFLASH\ORCL\ARCHIVELOG\2007_10_07\O1_MF_1_9_3JLOS56T_.ARC thread=1 sequence=9
archive log filename=E:\ORAFLASH\ORCL\ARCHIVELOG\2007_10_07\O1_MF_1_10_3JLXKK6T_.ARC thread=1 sequence=10
media recovery complete, elapsed time: 00:00:13
Finished recover at 07-OCT-2007 20:25:37

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=oracle)(ARGV0=oracleaFbc)(ARGS=^'(DESCRIPTION=(LOCAL=Y
ES)(ADDRESS=(PROTOCOL=beq)))^')(ENVS=^'ORACLE_SID=aFbc^'))(CONNECT_DATA=(SID=aFbc))) as sysdba\" point_in_time_recover=y
tablespaces=
AUTO_TBS 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  AUTO_TBS online";
sql "alter tablespace  AUTO_TBS 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 Sun Oct 7 20:26:49 2007

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
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...
For tablespace AUTO_TBS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                     AUTO_TABLE
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete

database closed
database dismounted
Oracle instance shut down


Import: Release 10.2.0.1.0 - Production on Sun Oct 7 20:30:59 2007

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
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
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table                   "AUTO_TABLE"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace  AUTO_TBS online

sql statement: alter tablespace  AUTO_TBS offline

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

Removing automatic instance
Automatic instance removed
auxiliary instance file E:\AUXDEST\CNTRL_TSPITR_ORCL_AFBC.F deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_SYSTEM_3JLXQD89_.DBF deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDOTBS1_3JLXQDRW_.DBF deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDO_BAT_3JLXQF00_.DBF deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_TEMP_3JLY97MK_.TMP deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\ONLINELOG\O1_MF_1_3JLY8G4W_.LOG deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\ONLINELOG\O1_MF_2_3JLY8OX9_.LOG deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\ONLINELOG\O1_MF_3_3JLY8WJM_.LOG deleted
Finished recover at 07-OCT-2007 20:32:10

RMAN>