[oracle@node1 auxiliary]$ rman nocatalog target / Recovery Manager: Release 10.2.0.1.0 - Production on Fri Dec 11 10:14:59 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: TOMSYAN (DBID=3691867871)
using target database control file instead of recovery catalog RMAN> recover tablespace data
2> until time "to_date('2009-12-11 10:11:31','YYYY-MM-DD HH24:MI:SS')"
3> auxiliary destination '/u01/app/auxiliary'; Starting recover at 11-DEC-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 instance=tomsyan1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=134 instance=tomsyan1 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=143 instance=tomsyan1 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 UNDOTBS2 Creating automatic instance, with SID='aAwu' initialization parameters used for automatic instance:
db_name=TOMSYAN
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_TOMSYAN_aAwu
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/u01/app/auxiliary
control_files=/u01/app/auxiliary/cntrl_tspitr_TOMSYAN_aAwu.f
starting up automatic instance TOMSYAN
Oracle instance started Total System Global Area 201326592 bytes Fixed Size 1218508 bytes
Variable Size 146802740 bytes
Database Buffers 50331648 bytes
Redo Buffers 2973696 bytes
Automatic instance created contents of Memory Script:
{
# set the until clause
set until time "to_date('2009-12-11 10:11:31','YYYY-MM-DD HH24:MI:SS')";
# 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 11-DEC-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=33 devtype=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=32 devtype=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: sid=31 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 +RECOVERYDEST/tomsyan/backupset/2009_12_10/ncnnf0_tag20091210t182229_0.267.705263089
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=+RECOVERYDEST/tomsyan/backupset/2009_12_10/ncnnf0_tag20091210t182229_0.267.705263089 tag=TAG20091210T182229
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
output filename=/u01/app/auxiliary/cntrl_tspitr_TOMSYAN_aAwu.f
Finished restore at 11-DEC-09 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_DISK_2
released channel: ORA_DISK_3
released channel: ORA_AUX_DISK_1
released channel: ORA_AUX_DISK_2
released channel: ORA_AUX_DISK_3 contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "to_date('2009-12-11 10:11:31','YYYY-MM-DD HH24:MI:SS')";
plsql <<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| '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 filename for restore
set newname for clone datafile 6 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 7 to
"+DGDATA/tomsyan/datafile/data.273.705254497";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 6, 7;
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";
#online the datafiles restored or flipped
sql clone "alter database datafile 7 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "DATA", "SYSTEM", "UNDOTBS1", "UNDOTBS2" 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 sql statement: alter tablespace DATA offline for recover 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 /u01/app/auxiliary/TSPITR_TOMSYAN_AAWU/datafile/o1_mf_temp_%u_.tmp in control file Starting restore at 11-DEC-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=32 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 00007 to +DGDATA/tomsyan/datafile/data.273.705254497
channel ORA_AUX_DISK_1: reading from backup piece +RECOVERYDEST/tomsyan/backupset/2009_12_10/nnndf0_tag20091210t175557_0.262.705261361
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=+RECOVERYDEST/tomsyan/backupset/2009_12_10/nnndf0_tag20091210t175557_0.262.705261361 tag=TAG20091210T175557
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/auxiliary/TSPITR_TOMSYAN_AAWU/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +RECOVERYDEST/tomsyan/backupset/2009_12_10/nnndf0_tag20091210t182229_0.265.705262957
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=+RECOVERYDEST/tomsyan/backupset/2009_12_10/nnndf0_tag20091210t182229_0.265.705262957 tag=TAG20091210T182229
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /u01/app/auxiliary/TSPITR_TOMSYAN_AAWU/datafile/o1_mf_undotbs2_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +RECOVERYDEST/tomsyan/backupset/2009_12_10/nnndf0_tag20091210t182229_0.266.705263073
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=+RECOVERYDEST/tomsyan/backupset/2009_12_10/nnndf0_tag20091210t182229_0.266.705263073 tag=TAG20091210T182229
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
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 /u01/app/auxiliary/TSPITR_TOMSYAN_AAWU/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +RECOVERYDEST/tomsyan/backupset/2009_12_10/nnndf0_tag20091210t182229_0.264.705262951
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=+RECOVERYDEST/tomsyan/backupset/2009_12_10/nnndf0_tag20091210t182229_0.264.705262951 tag=TAG20091210T182229
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 11-DEC-09 datafile 1 switched to datafile copy
input datafile copy recid=6 stamp=705320394 filename=/u01/app/auxiliary/TSPITR_TOMSYAN_AAWU/datafile/o1_mf_system_5l3c1v6g_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=7 stamp=705320394 filename=/u01/app/auxiliary/TSPITR_TOMSYAN_AAWU/datafile/o1_mf_undotbs1_5l3c1jy2_.dbf
datafile 6 switched to datafile copy
input datafile copy recid=8 stamp=705320394 filename=/u01/app/auxiliary/TSPITR_TOMSYAN_AAWU/datafile/o1_mf_undotbs2_5l3c1q70_.dbf sql statement: alter database datafile 1 online sql statement: alter database datafile 2 online sql statement: alter database datafile 6 online sql statement: alter database datafile 7 online Starting recover at 11-DEC-09
using channel ORA_AUX_DISK_1 starting media recovery archive log thread 1 sequence 9 is already on disk as file +DGDATA/tomsyan/1_9_705244901.dbf
archive log thread 1 sequence 10 is already on disk as file +DGDATA/tomsyan/1_10_705244901.dbf
archive log thread 1 sequence 11 is already on disk as file +DGDATA/tomsyan/1_11_705244901.dbf
archive log thread 1 sequence 12 is already on disk as file +DGDATA/tomsyan/1_12_705244901.dbf
archive log thread 1 sequence 13 is already on disk as file +DGDATA/tomsyan/1_13_705244901.dbf
archive log thread 2 sequence 2 is already on disk as file +DGDATA/tomsyan/2_2_705244901.dbf
archive log thread 2 sequence 3 is already on disk as file +DGDATA/tomsyan/2_3_705244901.dbf
archive log thread 2 sequence 4 is already on disk as file +DGDATA/tomsyan/2_4_705244901.dbf
archive log filename=+DGDATA/tomsyan/1_9_705244901.dbf thread=1 sequence=9
archive log filename=+DGDATA/tomsyan/2_2_705244901.dbf thread=2 sequence=2
archive log filename=+DGDATA/tomsyan/1_10_705244901.dbf thread=1 sequence=10
archive log filename=+DGDATA/tomsyan/1_11_705244901.dbf thread=1 sequence=11
archive log filename=+DGDATA/tomsyan/1_12_705244901.dbf thread=1 sequence=12
archive log filename=+DGDATA/tomsyan/2_3_705244901.dbf thread=2 sequence=3
archive log filename=+DGDATA/tomsyan/1_13_705244901.dbf thread=1 sequence=13
archive log filename=+DGDATA/tomsyan/2_4_705244901.dbf thread=2 sequence=4
media recovery complete, elapsed time: 00:00:04
Finished recover at 11-DEC-09 database opened contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\") as sysdba\" point_in_time_recover=y tablespaces=
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 DATA online";
sql "alter tablespace 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 Dec 11 10:25:04 2009
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, Real Application Clusters, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace DATA ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TEST
. 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 Fri Dec 11 10:25:45 2009
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, Real Application Clusters, 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 US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SYS's objects into SYS
. importing YSP's objects into YSP
. . importing table "TEST"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete sql statement: alter tablespace DATA online sql statement: alter tablespace DATA offline sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end; Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/auxiliary/cntrl_tspitr_TOMSYAN_aAwu.f deleted
auxiliary instance file /u01/app/auxiliary/TSPITR_TOMSYAN_AAWU/datafile/o1_mf_system_5l3c1v6g_.dbf deleted
auxiliary instance file /u01/app/auxiliary/TSPITR_TOMSYAN_AAWU/datafile/o1_mf_undotbs1_5l3c1jy2_.dbf deleted
auxiliary instance file /u01/app/auxiliary/TSPITR_TOMSYAN_AAWU/datafile/o1_mf_undotbs2_5l3c1q70_.dbf deleted
auxiliary instance file /u01/app/auxiliary/TSPITR_TOMSYAN_AAWU/datafile/o1_mf_temp_5l3c2syr_.tmp deleted
auxiliary instance file /u01/app/auxiliary/TSPITR_TOMSYAN_AAWU/onlinelog/o1_mf_1_5l3c2pph_.log deleted
auxiliary instance file /u01/app/auxiliary/TSPITR_TOMSYAN_AAWU/onlinelog/o1_mf_2_5l3c2px8_.log deleted
auxiliary instance file /u01/app/auxiliary/TSPITR_TOMSYAN_AAWU/onlinelog/o1_mf_5_5l3c2qxf_.log deleted
Finished recover at 11-DEC-09 RMAN> | 