Wednesday, May 22, 2013

Oracle 11GR2 Recover Tablespace To Point In Time

After a change in an Oracle 11gr2 database we got the call if we could rewind the database to the time before the change.
No problem we had made a guaranteed restore point. Oeps one problem this a an database with more the 1 application schema in it. A flashback to the restore point would flashback the whole database.
That was no option. So Oracle of course have a solution recover a tablespace to a point in time.
and here's what i did

First as Oracle create an auxiliary database automaticaly i had to find a file system where the auxiliary database will reside. As that is the place where the system,sysaux,undo wil be restored i needed a file system with 300 gyg free space.
I created a new directory called /eco2mig/new.

now i could start the rman command to start the restore
RMAN> recover tablespace ANCD,ANCX until  time "to_date('2013-04-25 23:00:00','YYYY-MM-DD HH24:MI:SS')" auxiliary destination '/eco2mig/new';
Oracle will create it's own spfile with a sga_target from 280M witch could cause a problem and that's the ORA-04030: out of process memory when trying to allocate. So it better to create a pfile yourself

vi /eco2mig/new/init_tspitr.ora

db_name=GAER2
compatible=11.2.0.2.0
db_block_size=8192
db_files=200
db_unique_name=tmrn_tspitr_GAER2
memory_max_target=2000M
memory_target=2000M
processes=50

As you see a put memory_target in it with 2gyg of memory.

The rman statement change also a little bit.

RMAN> run
 {
SET AUXILIARY INSTANCE PARAMETER FILE TO '/eco2mig/new/init_tspitr.ora';
recover tablespace ANCD,ANCX until  time "to_date('2013-04-25 23:00:00','YYYY-MM-DD HH24:MI:SS')" auxiliary destination '/eco2mig/new';
}

As you see i use now the set auxiliary instance parameter file
recover tablespace are they 2  tablespace i want to restore to April 25 23:00
They will be restored at there original location.
At the auxiliary destination is where the system,sysaux and undo tablespaces will reside.
After completion everything will be removed in the  auxiliary destination.

So let's start the restore of the 2 tablespaces.

executing command: SET auxiliary parameter file
Starting recover at 29-04-2013:17:41:07
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=900 instance=GAER21 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.4.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1092 instance=GAER21 device type=DISK
Creating automatic instance, with SID='wxja'
using contents of file /eco2mig/new/init_tspitr.ora
initialization parameters used for automatic instance:
db_name=GAER2
db_unique_name=wxja_tspitr_GAER2
compatible=11.2.0.2.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/eco2mig/new
log_archive_dest_1='location=/eco2mig/new'
ifile=/eco2mig/new/init_tspitr.ora

starting up automatic instance GAER2
Oracle instance started
Total System Global Area    2087780352 bytes
Fixed Size                     2229944 bytes
Variable Size               1962936648 bytes
Database Buffers             100663296 bytes
Redo Buffers                  21950464 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2013-04-25 23:00:00','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
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog
resync catalog;
}
executing Memory Script
executing command: SET until clause
Starting restore at 29-04-2013:17:41:23
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=17 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protection for Oracle: version 5.4.1.0
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=22 device type=DISK
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: restoring control file
channel ORA_AUX_SBT_TAPE_1: reading from backup piece c-2807859137-20130425-17
channel ORA_AUX_SBT_TAPE_1: piece handle=c-2807859137-20130425-17 tag=TAG20130425T224113
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:25
output file name=/eco2mig/new/GAER2_01/controlfile/o1_mf_8qx57w2k_.ctl
Finished restore at 29-04-2013:17:42:55
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
starting full resync of recovery catalog
full resync complete
contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2013-04-25 23:00:00','YYYY-MM-DD HH24:MI:SS')";
# 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  5 to new;
set newname for clone datafile  6 to new;
set newname for clone datafile  7 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  12 to new;
set newname for datafile  13 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 5, 6, 7, 2, 12, 13;
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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /eco2mig/new/GAER2_01/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 29-04-2013:17:43:12
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_DISK_1
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00001 to /eco2mig/new/GAER2_01/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00005 to /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs2_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00006 to /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs3_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00007 to /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs4_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00002 to /eco2mig/new/GAER2_01/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00012 to +DGGAER
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00013 to +DGGAER
channel ORA_AUX_SBT_TAPE_1: reading from backup piece iao7h4m0_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=iao7h4m0_1_1 tag=TAG20130419T180024
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 01:22:15
Finished restore at 29-04-2013:19:05:34
datafile 12 switched to datafile copy
input datafile copy RECID=21 STAMP=814043143 file name=+DGGAER/gaer2_01/datafile/ancd.287.814038315
datafile 13 switched to datafile copy
input datafile copy RECID=22 STAMP=814043143 file name=+DGGAER/gaer2_01/datafile/ancx.306.814038319
datafile 1 switched to datafile copy
input datafile copy RECID=23 STAMP=814043144 file name=/eco2mig/new/GAER2_01/datafile/o1_mf_system_8qx5dm50_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=24 STAMP=814043144 file name=/eco2mig/new/GAER2_01/datafile/o1_mf_undotbs1_8qx5d7wj_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=25 STAMP=814043149 file name=/eco2mig/new/GAER2_01/datafile/o1_mf_undotbs2_8qx5d7pv_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=26 STAMP=814043149 file name=/eco2mig/new/GAER2_01/datafile/o1_mf_undotbs3_8qx5d7rb_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=27 STAMP=814043149 file name=/eco2mig/new/GAER2_01/datafile/o1_mf_undotbs4_8qx5d7sm_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=28 STAMP=814043150 file name=/eco2mig/new/GAER2_01/datafile/o1_mf_sysaux_8qx5d7v0_.dbf
contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2013-04-25 23:00:00','YYYY-MM-DD HH24:MI:SS')";
# 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  5 online";
sql clone "alter database datafile  6 online";
sql clone "alter database datafile  7 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  12 online";
sql clone "alter database datafile  13 online";
# recover and open resetlogs
recover clone database tablespace  "ANCD", "ANCX", "SYSTEM", "UNDOTBS1", "UNDOTBS2", "UNDOTBS3", "UNDOTBS4", "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  5 online
sql statement: alter database datafile  6 online
sql statement: alter database datafile  7 online
sql statement: alter database datafile  2 online
sql statement: alter database datafile  12 online
sql statement: alter database datafile  13 online
Starting recover at 29-04-2013:19:05:59
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_DISK_1
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00012: +DGGAER/gaer2_01/datafile/ancd.287.81
destination for restore of datafile 00013: +DGGAER/gaer2_01/datafile/ancx.306.81
destination for restore of datafile 00001: /eco2mig/new/GAER2_01/datafile/o1_mf_
destination for restore of datafile 00003: /eco2mig/new/GAER2_01/datafile/o1_mf_
destination for restore of datafile 00005: /eco2mig/new/GAER2_01/datafile/o1_mf_
destination for restore of datafile 00006: /eco2mig/new/GAER2_01/datafile/o1_mf_
destination for restore of datafile 00007: /eco2mig/new/GAER2_01/datafile/o1_mf_
destination for restore of datafile 00002: /eco2mig/new/GAER2_01/datafile/o1_mf_
channel ORA_AUX_SBT_TAPE_1: reading from backup piece jdo7iopq_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=jdo7iopq_1_1 tag=TAG20130420T180023
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:02:15
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00012: +DGGAER/gaer2_01/datafile/ancd.287.814038315
destination for restore of datafile 00013: +DGGAER/gaer2_01/datafile/ancx.306.814038319
destination for restore of datafile 00001: /eco2mig/new/GAER2_01/datafile/o1_mf_system_8qx5dm50_.dbf
destination for restore of datafile 00003: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs1_8qx5d7wj_.dbf
destination for restore of datafile 00005: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs2_8qx5d7pv_.dbf
destination for restore of datafile 00006: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs3_8qx5d7rb_.dbf
destination for restore of datafile 00007: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs4_8qx5d7sm_.dbf
destination for restore of datafile 00002: /eco2mig/new/GAER2_01/datafile/o1_mf_sysaux_8qx5d7v0_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece l1o7ld5v_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=l1o7ld5v_1_1 tag=TAG20130421T180025
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:35
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00012: +DGGAER/gaer2_01/datafile/ancd.287.814038315
destination for restore of datafile 00013: +DGGAER/gaer2_01/datafile/ancx.306.814038319
destination for restore of datafile 00001: /eco2mig/new/GAER2_01/datafile/o1_mf_system_8qx5dm50_.dbf
destination for restore of datafile 00003: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs1_8qx5d7wj_.dbf
destination for restore of datafile 00005: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs2_8qx5d7pv_.dbf
destination for restore of datafile 00006: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs3_8qx5d7rb_.dbf
destination for restore of datafile 00007: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs4_8qx5d7sm_.dbf
destination for restore of datafile 00002: /eco2mig/new/GAER2_01/datafile/o1_mf_sysaux_8qx5d7v0_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece mlo7o1hu_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=mlo7o1hu_1_1 tag=TAG20130422T180026
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:25
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00012: +DGGAER/gaer2_01/datafile/ancd.287.814038315
destination for restore of datafile 00013: +DGGAER/gaer2_01/datafile/ancx.306.814038319
destination for restore of datafile 00001: /eco2mig/new/GAER2_01/datafile/o1_mf_system_8qx5dm50_.dbf
destination for restore of datafile 00003: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs1_8qx5d7wj_.dbf
destination for restore of datafile 00005: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs2_8qx5d7pv_.dbf
destination for restore of datafile 00006: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs3_8qx5d7rb_.dbf
destination for restore of datafile 00007: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs4_8qx5d7sm_.dbf
destination for restore of datafile 00002: /eco2mig/new/GAER2_01/datafile/o1_mf_sysaux_8qx5d7v0_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece o9o7qlu1_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=o9o7qlu1_1_1 tag=TAG20130423T180027
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:55
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00012: +DGGAER/gaer2_01/datafile/ancd.287.814038315
destination for restore of datafile 00013: +DGGAER/gaer2_01/datafile/ancx.306.814038319
destination for restore of datafile 00001: /eco2mig/new/GAER2_01/datafile/o1_mf_system_8qx5dm50_.dbf
destination for restore of datafile 00003: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs1_8qx5d7wj_.dbf
destination for restore of datafile 00005: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs2_8qx5d7pv_.dbf
destination for restore of datafile 00006: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs3_8qx5d7rb_.dbf
destination for restore of datafile 00007: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs4_8qx5d7sm_.dbf
destination for restore of datafile 00002: /eco2mig/new/GAER2_01/datafile/o1_mf_sysaux_8qx5d7v0_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece pto7taa3_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=pto7taa3_1_1 tag=TAG20130424T180031
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:03:05
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00012: +DGGAER/gaer2_01/datafile/ancd.287.814038315
destination for restore of datafile 00013: +DGGAER/gaer2_01/datafile/ancx.306.814038319
destination for restore of datafile 00001: /eco2mig/new/GAER2_01/datafile/o1_mf_system_8qx5dm50_.dbf
destination for restore of datafile 00003: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs1_8qx5d7wj_.dbf
destination for restore of datafile 00005: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs2_8qx5d7pv_.dbf
destination for restore of datafile 00006: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs3_8qx5d7rb_.dbf
destination for restore of datafile 00007: /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs4_8qx5d7sm_.dbf
destination for restore of datafile 00002: /eco2mig/new/GAER2_01/datafile/o1_mf_sysaux_8qx5d7v0_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece rho7vuls_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=rho7vuls_1_1 tag=TAG20130425T180025
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:02:25
starting media recovery
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=447
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=445
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=3 sequence=459
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=4 sequence=480
channel ORA_AUX_SBT_TAPE_1: reading from backup piece rio7vuo3_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=rio7vuo3_1_1 tag=TAG20130425T180138
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:01
archived log file name=/eco2mig/new/1_445_812006499.dbf thread=1 sequence=445
archived log file name=/eco2mig/new/2_447_812006499.dbf thread=2 sequence=447
archived log file name=/eco2mig/new/3_459_812006499.dbf thread=3 sequence=459
archived log file name=/eco2mig/new/4_480_812006499.dbf thread=4 sequence=480
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/4_480_812006499.dbf RECID=1860 STAMP=814043937
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=4 sequence=481
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=448
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=446
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=3 sequence=460
channel ORA_AUX_SBT_TAPE_1: reading from backup piece rko8010m_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=rko8010m_1_1 tag=TAG20130425T184021
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:07
archived log file name=/eco2mig/new/4_481_812006499.dbf thread=4 sequence=481
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/2_447_812006499.dbf RECID=1863 STAMP=814043937
archived log file name=/eco2mig/new/2_448_812006499.dbf thread=2 sequence=448
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/1_445_812006499.dbf RECID=1861 STAMP=814043937
archived log file name=/eco2mig/new/1_446_812006499.dbf thread=1 sequence=446
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/3_459_812006499.dbf RECID=1862 STAMP=814043937
archived log file name=/eco2mig/new/3_460_812006499.dbf thread=3 sequence=460
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/4_481_812006499.dbf RECID=1867 STAMP=814043941
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=4 sequence=482
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=449
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=447
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=3 sequence=461
channel ORA_AUX_SBT_TAPE_1: reading from backup piece rmo804hi_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=rmo804hi_1_1 tag=TAG20130425T194033
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:03
archived log file name=/eco2mig/new/4_482_812006499.dbf thread=4 sequence=482
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/2_448_812006499.dbf RECID=1864 STAMP=814043940
archived log file name=/eco2mig/new/2_449_812006499.dbf thread=2 sequence=449
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/1_446_812006499.dbf RECID=1866 STAMP=814043940
archived log file name=/eco2mig/new/1_447_812006499.dbf thread=1 sequence=447
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/3_460_812006499.dbf RECID=1865 STAMP=814043940
archived log file name=/eco2mig/new/3_461_812006499.dbf thread=3 sequence=461
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/4_482_812006499.dbf RECID=1871 STAMP=814043950
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=4 sequence=483
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=450
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=448
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=3 sequence=462
channel ORA_AUX_SBT_TAPE_1: reading from backup piece roo8081o_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=roo8081o_1_1 tag=TAG20130425T204024
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:03
archived log file name=/eco2mig/new/4_483_812006499.dbf thread=4 sequence=483
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/2_449_812006499.dbf RECID=1868 STAMP=814043949
archived log file name=/eco2mig/new/2_450_812006499.dbf thread=2 sequence=450
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/1_447_812006499.dbf RECID=1870 STAMP=814043949
archived log file name=/eco2mig/new/1_448_812006499.dbf thread=1 sequence=448
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/3_461_812006499.dbf RECID=1869 STAMP=814043949
archived log file name=/eco2mig/new/3_462_812006499.dbf thread=3 sequence=462
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/4_483_812006499.dbf RECID=1874 STAMP=814043963
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=4 sequence=484
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=451
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=3 sequence=463
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=449
channel ORA_AUX_SBT_TAPE_1: reading from backup piece rqo80bi5_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=rqo80bi5_1_1 tag=TAG20130425T214020
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:03
archived log file name=/eco2mig/new/4_484_812006499.dbf thread=4 sequence=484
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/2_450_812006499.dbf RECID=1872 STAMP=814043962
archived log file name=/eco2mig/new/2_451_812006499.dbf thread=2 sequence=451
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/3_462_812006499.dbf RECID=1873 STAMP=814043963
archived log file name=/eco2mig/new/3_463_812006499.dbf thread=3 sequence=463
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/1_448_812006499.dbf RECID=1875 STAMP=814043963
archived log file name=/eco2mig/new/1_449_812006499.dbf thread=1 sequence=449
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/2_451_812006499.dbf RECID=1876 STAMP=814043969
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=452
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=3 sequence=464
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=4 sequence=485
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=450
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=453
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=454
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=455
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=3 sequence=465
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=451
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=4 sequence=486
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=456
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=457
channel ORA_AUX_SBT_TAPE_1: reading from backup piece rso80f2p_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=rso80f2p_1_1 tag=TAG20130425T224024
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
archived log file name=/eco2mig/new/2_452_812006499.dbf thread=2 sequence=452
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/3_463_812006499.dbf RECID=1877 STAMP=814043969
archived log file name=/eco2mig/new/3_464_812006499.dbf thread=3 sequence=464
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/4_484_812006499.dbf RECID=1878 STAMP=814043969
archived log file name=/eco2mig/new/4_485_812006499.dbf thread=4 sequence=485
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/1_449_812006499.dbf RECID=1879 STAMP=814043969
archived log file name=/eco2mig/new/1_450_812006499.dbf thread=1 sequence=450
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/2_452_812006499.dbf RECID=1890 STAMP=814044010
archived log file name=/eco2mig/new/2_453_812006499.dbf thread=2 sequence=453
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/2_453_812006499.dbf RECID=1889 STAMP=814044007
archived log file name=/eco2mig/new/2_454_812006499.dbf thread=2 sequence=454
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/2_454_812006499.dbf RECID=1888 STAMP=814044004
archived log file name=/eco2mig/new/2_455_812006499.dbf thread=2 sequence=455
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/3_464_812006499.dbf RECID=1882 STAMP=814043977
archived log file name=/eco2mig/new/3_465_812006499.dbf thread=3 sequence=465
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/1_450_812006499.dbf RECID=1881 STAMP=814043977
archived log file name=/eco2mig/new/1_451_812006499.dbf thread=1 sequence=451
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/4_485_812006499.dbf RECID=1880 STAMP=814043977
archived log file name=/eco2mig/new/4_486_812006499.dbf thread=4 sequence=486
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/2_455_812006499.dbf RECID=1887 STAMP=814044000
archived log file name=/eco2mig/new/2_456_812006499.dbf thread=2 sequence=456
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/2_456_812006499.dbf RECID=1891 STAMP=814044013
archived log file name=/eco2mig/new/2_457_812006499.dbf thread=2 sequence=457
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/3_465_812006499.dbf RECID=1884 STAMP=814043978
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=3 sequence=466
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=452
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=4 sequence=487
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=458
channel ORA_AUX_SBT_TAPE_1: reading from backup piece ruo80ij6_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=ruo80ij6_1_1 tag=TAG20130425T234021
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:03
archived log file name=/eco2mig/new/3_466_812006499.dbf thread=3 sequence=466
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/1_451_812006499.dbf RECID=1883 STAMP=814043978
archived log file name=/eco2mig/new/1_452_812006499.dbf thread=1 sequence=452
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/4_486_812006499.dbf RECID=1885 STAMP=814043978
archived log file name=/eco2mig/new/4_487_812006499.dbf thread=4 sequence=487
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/2_457_812006499.dbf RECID=1886 STAMP=814043982
archived log file name=/eco2mig/new/2_458_812006499.dbf thread=2 sequence=458
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/1_452_812006499.dbf RECID=1895 STAMP=814044056
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/2_458_812006499.dbf RECID=1893 STAMP=814044056
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/3_466_812006499.dbf RECID=1892 STAMP=814044056
channel clone_default: deleting archived log(s)
archived log file name=/eco2mig/new/4_487_812006499.dbf RECID=1894 STAMP=814044056
media recovery complete, elapsed time: 00:00:02
Finished recover at 29-04-2013:19:21:00
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  ANCD read only';
sql clone 'alter tablespace  ANCX read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/eco2mig/new''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/eco2mig/new''";
}
executing Memory Script
sql statement: alter tablespace  ANCD read only
sql statement: alter tablespace  ANCX read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/eco2mig/new''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/eco2mig/new''
Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_wxja":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_wxja" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_wxja is:
   EXPDP>   /eco2mig/new/tspitr_wxja_32638.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace ANCD:
   EXPDP>   +DGGAER/gaer2_01/datafile/ancd.287.814038315
   EXPDP> Datafiles required for transportable tablespace ANCX:
   EXPDP>   +DGGAER/gaer2_01/datafile/ancx.306.814038319
   EXPDP> Job "SYS"."TSPITR_EXP_wxja" successfully completed at 19:23:26
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_wxja" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_wxja":
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_wxja" successfully completed at 19:25:44
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  ANCD read write';
sql 'alter tablespace  ANCD offline';
sql 'alter tablespace  ANCX read write';
sql 'alter tablespace  ANCX offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog
resync catalog;
}
executing Memory Script
sql statement: alter tablespace  ANCD read write
sql statement: alter tablespace  ANCD offline
sql statement: alter tablespace  ANCX read write
sql statement: alter tablespace  ANCX offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
starting full resync of recovery catalog
full resync complete
Removing automatic instance
Automatic instance removed
auxiliary instance file /eco2mig/new/GAER2_01/datafile/o1_mf_temp_8qxc1tm9_.tmp deleted
auxiliary instance file /eco2mig/new/GAER2_01/onlinelog/o1_mf_12_8qxc1n5k_.log deleted
auxiliary instance file /eco2mig/new/GAER2_01/onlinelog/o1_mf_11_8qxc1fgm_.log deleted
auxiliary instance file /eco2mig/new/GAER2_01/onlinelog/o1_mf_10_8qxc18k4_.log deleted
auxiliary instance file /eco2mig/new/GAER2_01/onlinelog/o1_mf_9_8qxc0znc_.log deleted
auxiliary instance file /eco2mig/new/GAER2_01/onlinelog/o1_mf_8_8qxc0tls_.log deleted
auxiliary instance file /eco2mig/new/GAER2_01/onlinelog/o1_mf_7_8qxc0ppb_.log deleted
auxiliary instance file /eco2mig/new/GAER2_01/onlinelog/o1_mf_6_8qxc0lvm_.log deleted
auxiliary instance file /eco2mig/new/GAER2_01/onlinelog/o1_mf_5_8qxc0gh6_.log deleted
auxiliary instance file /eco2mig/new/GAER2_01/onlinelog/o1_mf_4_8qxc09hq_.log deleted
auxiliary instance file /eco2mig/new/GAER2_01/onlinelog/o1_mf_3_8qxc06fp_.log deleted
auxiliary instance file /eco2mig/new/GAER2_01/onlinelog/o1_mf_2_8qxc0398_.log deleted
auxiliary instance file /eco2mig/new/GAER2_01/onlinelog/o1_mf_1_8qxbzywx_.log deleted
auxiliary instance file /eco2mig/new/GAER2_01/datafile/o1_mf_sysaux_8qx5d7v0_.dbf deleted
auxiliary instance file /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs4_8qx5d7sm_.dbf deleted
auxiliary instance file /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs3_8qx5d7rb_.dbf deleted
auxiliary instance file /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs2_8qx5d7pv_.dbf deleted
auxiliary instance file /eco2mig/new/GAER2_01/datafile/o1_mf_undotbs1_8qx5d7wj_.dbf deleted
auxiliary instance file /eco2mig/new/GAER2_01/datafile/o1_mf_system_8qx5dm50_.dbf deleted
auxiliary instance file /eco2mig/new/GAER2_01/controlfile/o1_mf_8qx57w2k_.ctl deleted
Finished recover at 29-04-2013:19:25:58
RMAN>

And you see the 2 tablespaces where restored at there original location +DGGAER
the export of the metadata and import where also done and of course all files in the auxiliary location where deleted.

There is one thing you have to do manually and that is to set the restored tablespaces online again.

SQL>alter tablespace ANCD online;
SQL>alter tablespace ANCX online;

So now you have restored 2 tablespaces without the need of restoring the whole database.
and it's fully automatic. Nice very nice.

That's it.



No comments:

Post a Comment