TSPITR issue
I am trying to do a TSPITR as follows and having issues. I am using Oracle 10.2.0.1 on RHEL 4.
I use incrementally updated RMAN image copies for backup of my DB.
The issue is at the bottom. Don't get overwhelmed by the output. The only command I used is recover the tablespace example. Everything else is RMAN's standard procedure.
/home/oracle/pct:>rman target=/
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jan 25 21:28:57 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: STREAMS2 (DBID=798347868)
RMAN> sql 'alter tablespace example offline immediate';
using target database control file instead of recovery catalog
sql statement: alter tablespace example offline immediate
RMAN> recover tablespace example until scn 324331798 auxiliary destination '/home/oracle/pct/tmp';
Starting recover at 25-JAN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 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='kueh'
initialization parameters used for automatic instance:
db_name=STREAMS2
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_STREAMS2_kueh
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/home/oracle/pct/tmp
control_files=/home/oracle/pct/tmp/cntrl_tspitr_STREAMS2_kueh.f
starting up automatic instance STREAMS2
Oracle instance started
Total System Global Area 205520896 bytes
Fixed Size 1218508 bytes
Variable Size 146802740 bytes
Database Buffers 50331648 bytes
Redo Buffers 7168000 bytes
Automatic instance created
contents of Memory Script:
{
# set the until clause
set until scn 324331798;
# 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 25-JAN-07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 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 /u03/flash_recovery_area/STREAMS2/backupset/2007_01_25/o1_mf_ncsn1_TAG200701
25T020047_2vjomgs7_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u03/flash_recovery_area/STREAMS2/backupset/2007_01_25/o1_mf_ncsn1_TAG20070125T020047_2vjomgs7_.bkp tag=TAG200701
25T020047
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/home/oracle/pct/tmp/cntrl_tspitr_STREAMS2_kueh.f
Finished restore at 25-JAN-07
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 324331798;
# 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 5 to
"/u02/oradata/STREAMS2/datafile/example01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 5;
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 5 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "EXAMPLE", "SYSTEM", "UNDOTBS1" 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
renamed temporary file 1 to /home/oracle/pct/tmp/TSPITR_STREAMS2_KUEH/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 25-JAN-07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=39 devtype=DISK
channel ORA_AUX_DISK_1: restoring datafile 00001
input datafile copy recid=6853 stamp=612756031 filename=/u03/flash_recovery_area/STREAMS2/datafile/o1_mf_system_2j257yjf_.dbf
destination for restore of datafile 00001: /home/oracle/pct/tmp/TSPITR_STREAMS2_KUEH/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00001
output filename=/home/oracle/pct/tmp/TSPITR_STREAMS2_KUEH/datafile/o1_mf_system_2vlsy0nq_.dbf recid=6856 stamp=612826255
channel ORA_AUX_DISK_1: restoring datafile 00002
input datafile copy recid=6821 stamp=612756025 filename=/u03/flash_recovery_area/STREAMS2/datafile/o1_mf_undotbs1_2j25b1tp_.db
f
destination for restore of datafile 00002: /home/oracle/pct/tmp/TSPITR_STREAMS2_KUEH/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00002
output filename=/home/oracle/pct/tmp/TSPITR_STREAMS2_KUEH/datafile/o1_mf_undotbs1_2vlsz3pt_.dbf recid=6857 stamp=612826275
channel ORA_AUX_DISK_1: restoring datafile 00005
input datafile copy recid=6855 stamp=612826224 filename=/u03/flash_recovery_area/STREAMS2/datafile/example01.dbf
destination for restore of datafile 00005: /u02/oradata/STREAMS2/datafile/example01.dbf
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00005
output filename=/u02/oradata/STREAMS2/datafile/example01.dbf recid=6858 stamp=612826289
Finished restore at 25-JAN-07
datafile 1 switched to datafile copy
input datafile copy recid=6859 stamp=612826291 filename=/home/oracle/pct/tmp/TSPITR_STREAMS2_KUEH/datafile/o1_mf_system_2vlsy0
nq_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=6860 stamp=612826291 filename=/home/oracle/pct/tmp/TSPITR_STREAMS2_KUEH/datafile/o1_mf_undotbs1_2vls
z3pt_.dbf
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 5 online
Starting recover at 25-JAN-07
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting incremental datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /home/oracle/pct/tmp/TSPITR_STREAMS2_KUEH/datafile/o1_mf_system_2vlsy0nq_.dbf
destination for restore of datafile 00002: /home/oracle/pct/tmp/TSPITR_STREAMS2_KUEH/datafile/o1_mf_undotbs1_2vlsz3pt_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u03/flash_recovery_area/STREAMS2/backupset/2007_01_25/o1_mf_nnnd1_TAG200701
25T020047_2vjof16k_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u03/flash_recovery_area/STREAMS2/backupset/2007_01_25/o1_mf_nnnd1_TAG20070125T020047_2vjof16k_.bkp tag=TAG200701
25T020047
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
starting media recovery
archive log thread 1 sequence 771 is already on disk as file /u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_7
71_2vk06s28_.arc
archive log thread 1 sequence 772 is already on disk as file /u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_7
72_2vkyv1g5_.arc
archive log thread 1 sequence 773 is already on disk as file /u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_7
73_2vkywn4z_.arc
archive log thread 1 sequence 774 is already on disk as file /u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_7
74_2vlfzvfh_.arc
archive log thread 1 sequence 775 is already on disk as file /u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_7
75_2vlp63b2_.arc
archive log thread 1 sequence 776 is already on disk as file /u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_7
76_2vlqkxqo_.arc
archive log thread 1 sequence 777 is already on disk as file /u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_7
77_2vlr6pvs_.arc
archive log thread 1 sequence 778 is already on disk as file /u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_7
78_2vlsxykr_.arc
archive log filename=/u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_771_2vk06s28_.arc thread=1 sequence=771
archive log filename=/u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_772_2vkyv1g5_.arc thread=1 sequence=772
archive log filename=/u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_773_2vkywn4z_.arc thread=1 sequence=773
archive log filename=/u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_774_2vlfzvfh_.arc thread=1 sequence=774
archive log filename=/u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_775_2vlp63b2_.arc thread=1 sequence=775
archive log filename=/u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_776_2vlqkxqo_.arc thread=1 sequence=776
archive log filename=/u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_777_2vlr6pvs_.arc thread=1 sequence=777
archive log filename=/u03/flash_recovery_area/STREAMS2/archivelog/2007_01_25/o1_mf_1_778_2vlsxykr_.arc thread=1 sequence=778
media recovery complete, elapsed time: 00:00:11
Finished recover at 25-JAN-07
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/25/2007 21:31:59
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01135: file 51 accessed for DML/query is offline
ORA-01110: data file 51: '/u02/oradata/STREAMS2/datafile/o1_mf_test_tts_2o9nd7jn_.dbf'
RMAN>
What's file# 51 have anything to do with my recovery file (which is no. 5). File 51 IS online. Recovery is done. Auxiliary instance is removed and I get an error? I don't understand. The file# 5 status in v$datafile is RECOVER and all other files in my target database are online.
Help!