Friday, May 24, 2013

Rman Targetless Duplication Of Oacle Database 11gr2

Since Oracle 11gr2 you can now duplicate an Oracle database without the need to connect to the target database. This is convenient option when the target database is not availiable for duplication.
You must have the dbid of the target database.

You can get that to logon to the database where the rman catalog is in a run th following sql script

SQL>select a.db_name,b.db_id
         from dbinc a, db b
        where a.db_name = 'ODBA3'
         and a.db_key = b.db_key;
DB_NAME                       DB_ID
------------------------ ----------
ODBA3                     431532821

and here is the commando to start the duplicate

RMAN>connect catalog rmn_owner/password@catdb;
RMAN>connect auxiliary /;
RMAN>set dbid 431532821;
RMAN>run
{
set until time "to_date('22-May-2013 08:00:00','dd-mon-yyyy hh24:mi:ss')";
allocate auxiliary channel ch1  TYPE 'SBT_TAPE' PARMS  'BLKSIZE=1048576, ENV=(TDPO_OPTFILE=/u01/app/oracle/admin/tdp/dat/ODBA3_tdpo.opt)';
duplicate database 'ODBA3' to 'ODBA6';
}

connected to recovery catalog database
connected to auxiliary database: ODBA6 (not mounted)
executing command: SET DBID
executing command: SET until clause
allocated channel: ch1
channel ch1: SID=2 device type=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.4.1.0
Starting Duplicate Db at 23-05-2013:18:24:18
contents of Memory Script:
{
   set until scn  17458231;
   sql clone "alter system set  db_name =
 ''ODBA3'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script
executing command: SET until clause
sql statement: alter system set  db_name =  ''ODBA3'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area    1603411968 bytes
Fixed Size                     2228784 bytes
Variable Size               1358958032 bytes
Database Buffers             234881024 bytes
Redo Buffers                   7344128 bytes
allocated channel: ch1
channel ch1: SID=872 device type=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.4.1.0
Starting restore at 23-05-2013:18:24:42
channel ch1: starting datafile backup set restore
channel ch1: restoring control file
channel ch1: reading from backup piece c-431532821-20130522-08
channel ch1: piece handle=c-431532821-20130522-08 tag=TAG20130522T072120
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
output file name=+DGO1/odba6_01/control01.ctl
output file name=+DGOFRA1/odba6_01/control02.ctl
Finished restore at 23-05-2013:18:24:46
database mounted
contents of Memory Script:
{
   set until scn  17458231;
   sql clone 'alter database flashback off';
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   restore
   clone database
   ;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database flashback off
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 23-05-2013:18:24:56
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to +DGO1
channel ch1: restoring datafile 00002 to +DGO1
channel ch1: restoring datafile 00003 to +DGO1
channel ch1: restoring datafile 00004 to +DGO1
channel ch1: restoring datafile 00005 to +DGO1
channel ch1: restoring datafile 00006 to +DGO1
channel ch1: reading from backup piece beo9qq2o_1_1
channel ch1: piece handle=beo9qq2o_1_1 tag=TAG20130517T014350
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:02:45
Finished restore at 23-05-2013:18:27:56
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=175 STAMP=816200876 file name=+DGO1/odba6_01/datafile/system.589.816200763
datafile 2 switched to datafile copy
input datafile copy RECID=176 STAMP=816200876 file name=+DGO1/odba6_01/datafile/sysaux.590.816200761
datafile 3 switched to datafile copy
input datafile copy RECID=177 STAMP=816200876 file name=+DGO1/odba6_01/datafile/undotbs1.592.816200757
datafile 4 switched to datafile copy
input datafile copy RECID=178 STAMP=816200876 file name=+DGO1/odba6_01/datafile/tools.842.816200763
datafile 5 switched to datafile copy
input datafile copy RECID=179 STAMP=816200876 file name=+DGO1/odba6_01/datafile/undotbs2.591.816200759
datafile 6 switched to datafile copy
input datafile copy RECID=180 STAMP=816200876 file name=+DGO1/odba6_01/datafile/users.847.816200763
contents of Memory Script:
{
   set until time  "to_date('MAY 22 2013 08:00:00', 'MON DD YYYY HH24:MI:SS')";
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 23-05-2013:18:27:57
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DGO1/odba6_01/datafile/system.589.816200763
destination for restore of datafile 00002: +DGO1/odba6_01/datafile/sysaux.590.816200761
destination for restore of datafile 00003: +DGO1/odba6_01/datafile/undotbs1.592.816200757
destination for restore of datafile 00004: +DGO1/odba6_01/datafile/tools.842.816200763
destination for restore of datafile 00005: +DGO1/odba6_01/datafile/undotbs2.591.816200759
destination for restore of datafile 00006: +DGO1/odba6_01/datafile/users.847.816200763
channel ch1: reading from backup piece cjo9tdom_1_1
channel ch1: piece handle=cjo9tdom_1_1 tag=TAG20130518T013203
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DGO1/odba6_01/datafile/system.589.816200763
destination for restore of datafile 00002: +DGO1/odba6_01/datafile/sysaux.590.816200761
destination for restore of datafile 00003: +DGO1/odba6_01/datafile/undotbs1.592.816200757
destination for restore of datafile 00004: +DGO1/odba6_01/datafile/tools.842.816200763
destination for restore of datafile 00005: +DGO1/odba6_01/datafile/undotbs2.591.816200759
destination for restore of datafile 00006: +DGO1/odba6_01/datafile/users.847.816200763
channel ch1: reading from backup piece e7oa024o_1_1
channel ch1: piece handle=e7oa024o_1_1 tag=TAG20130519T013205
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:16
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DGO1/odba6_01/datafile/system.589.816200763
destination for restore of datafile 00002: +DGO1/odba6_01/datafile/sysaux.590.816200761
destination for restore of datafile 00003: +DGO1/odba6_01/datafile/undotbs1.592.816200757
destination for restore of datafile 00004: +DGO1/odba6_01/datafile/tools.842.816200763
destination for restore of datafile 00005: +DGO1/odba6_01/datafile/undotbs2.591.816200759
destination for restore of datafile 00006: +DGO1/odba6_01/datafile/users.847.816200763
channel ch1: reading from backup piece froa2mi2_1_1
channel ch1: piece handle=froa2mi2_1_1 tag=TAG20130520T013247
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DGO1/odba6_01/datafile/system.589.816200763
destination for restore of datafile 00002: +DGO1/odba6_01/datafile/sysaux.590.816200761
destination for restore of datafile 00003: +DGO1/odba6_01/datafile/undotbs1.592.816200757
destination for restore of datafile 00004: +DGO1/odba6_01/datafile/tools.842.816200763
destination for restore of datafile 00005: +DGO1/odba6_01/datafile/undotbs2.591.816200759
destination for restore of datafile 00006: +DGO1/odba6_01/datafile/users.847.816200763
channel ch1: reading from backup piece hfoa5ati_1_1
channel ch1: piece handle=hfoa5ati_1_1 tag=TAG20130521T013231
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DGO1/odba6_01/datafile/system.589.816200763
destination for restore of datafile 00002: +DGO1/odba6_01/datafile/sysaux.590.816200761
destination for restore of datafile 00003: +DGO1/odba6_01/datafile/undotbs1.592.816200757
destination for restore of datafile 00004: +DGO1/odba6_01/datafile/tools.842.816200763
destination for restore of datafile 00005: +DGO1/odba6_01/datafile/undotbs2.591.816200759
destination for restore of datafile 00006: +DGO1/odba6_01/datafile/users.847.816200763
channel ch1: reading from backup piece j3oa7v96_1_1
channel ch1: piece handle=j3oa7v96_1_1 tag=TAG20130522T013219
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
starting media recovery
channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=2 sequence=642
channel ch1: restoring archived log
archived log thread=1 sequence=643
channel ch1: reading from backup piece j4oa7va8_1_1
channel ch1: piece handle=j4oa7va8_1_1 tag=TAG20130522T013254
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_643.2924.816200949 thread=1 sequence=643
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_642.1165.816200949 thread=2 sequence=642
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_643.2924.816200949 RECID=2938 STAMP=816200948
channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=1 sequence=644
channel ch1: restoring archived log
archived log thread=2 sequence=643
channel ch1: reading from backup piece j6oa824v_1_1
channel ch1: piece handle=j6oa824v_1_1 tag=TAG20130522T022117
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_644.2924.816200951 thread=1 sequence=644
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_642.1165.816200949 RECID=2939 STAMP=816200948
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_643.383.816200951 thread=2 sequence=643
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_644.2924.816200951 RECID=2941 STAMP=816200950
channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=1 sequence=645
channel ch1: restoring archived log
archived log thread=2 sequence=644
channel ch1: reading from backup piece j8oa85lb_1_1
channel ch1: piece handle=j8oa85lb_1_1 tag=TAG20130522T032112
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_645.2924.816200953 thread=1 sequence=645
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_643.383.816200951 RECID=2940 STAMP=816200950
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_644.1165.816200953 thread=2 sequence=644
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_645.2924.816200953 RECID=2943 STAMP=816200952
channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=1 sequence=646
channel ch1: restoring archived log
archived log thread=2 sequence=645
channel ch1: reading from backup piece jaoa895r_1_1
channel ch1: piece handle=jaoa895r_1_1 tag=TAG20130522T042113
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_646.2924.816200955 thread=1 sequence=646
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_644.1165.816200953 RECID=2942 STAMP=816200952
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_645.383.816200955 thread=2 sequence=645
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_646.2924.816200955 RECID=2945 STAMP=816200954
channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=1 sequence=647
channel ch1: restoring archived log
archived log thread=2 sequence=646
channel ch1: reading from backup piece jcoa8cmc_1_1
channel ch1: piece handle=jcoa8cmc_1_1 tag=TAG20130522T052114
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_647.2924.816200957 thread=1 sequence=647
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_645.383.816200955 RECID=2944 STAMP=816200954
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_646.1165.816200957 thread=2 sequence=646
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_646.1165.816200957 RECID=2946 STAMP=816200956
channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=2 sequence=647
channel ch1: restoring archived log
archived log thread=1 sequence=648
channel ch1: reading from backup piece jeoa8g70_1_1
channel ch1: piece handle=jeoa8g70_1_1 tag=TAG20130522T062118
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_647.383.816200959 thread=2 sequence=647
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_647.2924.816200957 RECID=2947 STAMP=816200956
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_648.1165.816200959 thread=1 sequence=648
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_648.1165.816200959 RECID=2949 STAMP=816200958
channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=1 sequence=649
channel ch1: restoring archived log
archived log thread=2 sequence=648
channel ch1: reading from backup piece jgoa8jnc_1_1
channel ch1: piece handle=jgoa8jnc_1_1 tag=TAG20130522T072113
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_649.1165.816200961 thread=1 sequence=649
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_647.383.816200959 RECID=2948 STAMP=816200958
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_648.2924.816200961 thread=2 sequence=648
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_648.2924.816200961 RECID=2950 STAMP=816200960
channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=2 sequence=649
channel ch1: restoring archived log
archived log thread=1 sequence=650
channel ch1: reading from backup piece jioa8n7u_1_1
channel ch1: piece handle=jioa8n7u_1_1 tag=TAG20130522T082116
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_649.383.816200961 thread=2 sequence=649
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_649.1165.816200961 RECID=2951 STAMP=816200960
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_650.2924.816200963 thread=1 sequence=650
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_1_seq_650.2924.816200963 RECID=2953 STAMP=816200962
channel clone_default: deleting archived log(s)
archived log file name=+DGOFRA1/odba6_01/archivelog/2013_05_23/thread_2_seq_649.383.816200961 RECID=2952 STAMP=816200961
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-05-2013:18:29:23
Oracle instance started
Total System Global Area    1603411968 bytes
Fixed Size                     2228784 bytes
Variable Size               1358958032 bytes
Database Buffers             234881024 bytes
Redo Buffers                   7344128 bytes
contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ODBA6'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
sql statement: alter system set  db_name =  ''ODBA6'' comment= ''Reset to original value by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area    1603411968 bytes
Fixed Size                     2228784 bytes
Variable Size               1358958032 bytes
Database Buffers             234881024 bytes
Redo Buffers                   7344128 bytes
allocated channel: ch1
channel ch1: SID=872 device type=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.4.1.0
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ODBA6" RESETLOGS ARCHIVELOG
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY     1232
 LOGFILE
  GROUP   1  SIZE 256 M ,
  GROUP   2  SIZE 256 M ,
  GROUP   3  SIZE 256 M
 DATAFILE
  '+DGO1/odba6_01/datafile/system.589.816200763'
 CHARACTER SET AL32UTF8
sql statement: ALTER DATABASE ADD LOGFILE

  INSTANCE 'i2'
  GROUP   4  SIZE 256 M ,
  GROUP   5  SIZE 256 M ,
  GROUP   6  SIZE 256 M
contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "+DGO1/odba6_01/datafile/sysaux.590.816200761",
 "+DGO1/odba6_01/datafile/undotbs1.592.816200757",
 "+DGO1/odba6_01/datafile/tools.842.816200763",
 "+DGO1/odba6_01/datafile/undotbs2.591.816200759",
 "+DGO1/odba6_01/datafile/users.847.816200763";
   switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DGO1 in control file
cataloged datafile copy
datafile copy file name=+DGO1/odba6_01/datafile/sysaux.590.816200761 RECID=1 STAMP=816200999
cataloged datafile copy
datafile copy file name=+DGO1/odba6_01/datafile/undotbs1.592.816200757 RECID=2 STAMP=816200999
cataloged datafile copy
datafile copy file name=+DGO1/odba6_01/datafile/tools.842.816200763 RECID=3 STAMP=816200999
cataloged datafile copy
datafile copy file name=+DGO1/odba6_01/datafile/undotbs2.591.816200759 RECID=4 STAMP=816200999
cataloged datafile copy
datafile copy file name=+DGO1/odba6_01/datafile/users.847.816200763 RECID=5 STAMP=816200999
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=816200999 file name=+DGO1/odba6_01/datafile/sysaux.590.816200761
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=816200999 file name=+DGO1/odba6_01/datafile/undotbs1.592.816200757
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=816200999 file name=+DGO1/odba6_01/datafile/tools.842.816200763
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=816200999 file name=+DGO1/odba6_01/datafile/undotbs2.591.816200759
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=816200999 file name=+DGO1/odba6_01/datafile/users.847.816200763
Reenabling controlfile options for auxiliary database
Executing: alter database force logging
Executing: alter database enable block change tracking
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script
database opened
Executing: alter database flashback on
Finished Duplicate Db at 23-05-2013:18:30:19
released channel: ch1
Recovery Manager complete.

Wednesday, May 22, 2013

srvctl commands 11gr2

Here are some very handy srvctl commands in11gr2

$ srvctl status database -d GSXQ9_01

Instance GSXQ91 is running on node oesv5071
Instance GSXQ92 is running on node oesv6071
-d is db_unique_name of the database

$ srvctl status database -d GSXQ9_01 -v
Instance GSXQ91 is running on node oesv5071. Instance status: Open.
Instance GSXQ92 is running on node oesv6071 with online services SRV0GSXQ901,SRVGSXQ9RMN. Instance status: Open.
-v verbose mode as you see that when you login with service_name SRV0GSXQ901,SRVGSXQ9RMN
you be logon to instance GSXQ92.

to see witch instance is preferd or available use the following command

$ srvctl config service -d GSXQ9_01 -s SRV0GSXQ901
Service name: SRV0GSXQ901
Service is enabled
Server pool: GSXQ9_01_SRV0GSXQ901
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: true
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 5
TAF failover delay: 180
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: THROUGHPUT
TAF policy specification: BASIC
Edition:
Preferred instances: GSXQ91
Available instances: GSXQ92

Now set instance to preferred

srvctl modify service -d gsxq9_01 -s srv0gsxq901 -i GSXQ91,GSXQ92 -n

-d db_unique_name of database
-s service name
-i instance name
-n Only uses the instances named for this service (unnamed instances already assigned to the service are removed).

now lets see if it has worked

$ srvctl config service -d GSXQ9_01 -s SRV0GSXQ901
Service name: SRV0GSXQ901
Service is enabled
Server pool: GSXQ9_01_SRV0GSXQ901
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: true
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 5
TAF failover delay: 180
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: THROUGHPUT
TAF policy specification: BASIC
Edition:
Preferred instances: GSXQ91,GSXQ92
Available instances:
Oke it worked now lets make 1 instance available.

$ srvctl modify service -d gsxq9_01 -s srv0gsxq901 -n -i GSXQ91 -a GSXQ92

oke lets see if GSXQ92 is now an available instance again.

$ srvctl config service -d GSXQ9_01 -s SRV0GSXQ901
Service name: SRV0GSXQ901
Service is enabled
Server pool: GSXQ9_01_SRV0GSXQ901
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: true
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 5
TAF failover delay: 180
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: THROUGHPUT
TAF policy specification: BASIC
Edition:
Preferred instances: GSXQ91
Available instances: GSXQ92
the following command gives more information on the database

$ srvctl config service -d GSXQ9_01
Service name: SRV0GSXQ901
Service is enabled
Server pool: GSXQ9_01_SRV0GSXQ901
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: true
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 5
TAF failover delay: 180
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: THROUGHPUT
TAF policy specification: BASIC
Edition:
Preferred instances: GSXQ91
Available instances: GSXQ92
Service name: SRVGSXQ9RMN
Service is enabled
Server pool: GSXQ9_01_SRVGSXQ9RMN
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: true
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 5
TAF failover delay: 180
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: THROUGHPUT
TAF policy specification: BASIC
Edition:
Preferred instances: GSXQ91
Available instances: GSXQ92
now lets add a service_name

$ srvctl add  service -d GSXQ9_01 -s SRV0GSXQ999 -r GSXQ91 -a GSXQ92

now lets see if it exsist and which is the available an preferred instance.

$ srvctl config service -d GSXQ9_01 -s SRV0GSXQ999
Service name: SRV0GSXQ999
Service is enabled
Server pool: GSXQ9_01_SRV0GSXQ999
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Preferred instances: GSXQ91
Available instances: GSXQ92
and yes GSXQ91 is preferred and GSXQ92 is the available instance.

now how to remove the service_name

$ srvctl remove service -d GSXQ9_01 -s SRV0GSXQ999

$ srvctl config service -d GSXQ9_01 -s SRV0GSXQ999
PRCR-1001 : Resource ora.gsxq9_01.srv0gsxq999.svc does not exist
and yes it is gone

now lets relocate the service to an other instance

srvctl status database -d gsxq9_01 -v
Instance GSXQ91 is running on node oesv5071. Instance status: Open.
Instance GSXQ92 is running on node oesv6071 with online services SRV0GSXQ901,SRVGSXQ9RMN. Instance status: Open.
$ srvctl relocate service -s SRV0OGSXQ901 -d gsxq9_01 -i GSXQ92  -t GSXQ91

This will relocate the the service SRV0OGSXQ901  from instance GSXQ92  to GSXQ91

$ srvctl status database -d gsxq9_01 -v
Instance GSXQ91 is running on node oesv5071 with online services SRV0GSXQ901. Instance status: Open.
Instance GSXQ92 is running on node oesv6071 with online services SRVGSXQ9RMN. Instance status: Open.
you can run the same command with the -f (Force) option

$ srvctl relocate service -s SRV0OGSXQ901 -d gsxq9_01 -i GSXQ92  -t GSXQ91 -f

this will do the same but with -f all connection are disconnected immediately and where update/insert/delete statements will be rollback as without -f the statements will finished on the instance where the service was running before the will be closed.

now some basic commando as stopping/starting the database or an instance.

stop the whole database

$ srvctl stop database -d gsxq9_01

start the whole database

$ srvctl start database -d gsxq9_01

stop an instance

$srvctl stop instance -d  gsxq9_01 -i GSXQ92 
this will only stop instance GSXQ92 .

$srvctl stop instance -d  gsxq9_01 -i GSXQ92  -o abort
this stop the instance with the abort option

start an instance

$srvctl start instance -d  gsxq9_01 -i GSXQ92 
$srvctl start instance -d  gsxq9_01 -i GSXQ92  -o mount
the second command start the instance in mount fase.

stop a service_name

$ srvctl stop service -d odba5_01 -s SRV0ODBA501

start a service_name

$ srvctl start service -d odba5_01 -s SRV0ODBA501

$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:39000
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:39000
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:39000

$ srvctl config scan
SCAN name: prodcl21-scan, Network: 1/10.233.130.0/255.255.254.0/bond0
SCAN VIP name: scan1, IP: /10.233.130.221/10.233.130.221
SCAN VIP name: scan2, IP: /10.233.130.222/10.233.130.222
SCAN VIP name: scan3, IP: /10.233.130.223/10.233.130.223

$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node oesv3031
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node oesv3032
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node oesv3030

modify scan listener

$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:39000
$ srvctl modify scan_listener -p TCP:39000/TCPS:39003

$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:39000/TCPS:39003

as you see port tcps:39003 is added

$ srvctl stop scan_listener
$ srvctl start scan_listener



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.



Drop Oracle Rac Database

Here is an example of how to drop a 4 node rac database manually.

First stop the database

$ srvctl stop database -d gaer4_01

Now remove the database entry  from crs

-d is the db_unuique_name of the database
-i  is the name of the instance

$ srvctl remove instance -d gaer4_01 -i GAER41
Remove instance from the database gaer4_01? (y/[n]) y
$ srvctl remove instance -d gaer4_01 -i GAER42
Remove instance from the database gaer4_01? (y/[n]) y
$ srvctl remove instance -d gaer4_01 -i GAER43
Remove instance from the database gaer4_01? (y/[n]) y
$ srvctl remove instance -d gaer4_01 -i GAER44
Remove instance from the database gaer4_01? (y/[n]) y
PRKO-3147 : Instance GAER44 cannot be removed because it is the only preferred instance for service(s) SRV0GAER401,SRVGAER4RMN for database gaer4_01

Oeps  yes we must remove the service_name of the database first before we can remove the database.

$ srvctl remove service -d gaer4_01 -s SRV0GAER401
$ srvctl remove service -d gaer4_01 -s SRVGAER4RMN

now we can remove the last instance from CRS.

$ srvctl remove instance -d gaer4_01 -i GAER44
Remove instance from the database gaer4_01? (y/[n]) y

Now we can remove the database

$ srvctl remove database -d gaer4_01
Remove the database gaer4_01? (y/[n]) y
Now we start the first instance so we can adjust some parameters.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup

we now set the cluster_database parameter to false.

SQL>alter system set cluster_database=false scope=spfile;

System altered

and we set the cluster_database_instance to 1

SQL>alter system set cluster_database_instances=1 scope=spfile;

System altered.
Now we diable the thread from all instance's execpt the active instance.

SQL> alter database disable thread 2;
Database altered.
SQL> alter database disable thread 3;
Database altered.
SQL>alter database disable thread 4;
Database altered.

Delete the unwanted thread and redo logfiles. Thread 1 is for active instance and other is for another instance. Drop all redo group of other thread. Ex: Group 4,5,6 are for other thread then drop as follows.

SQL>select thread#, group# from v$log;

   THREAD#     GROUP#
---------- ----------
         1          1
         1          2
         1          3
         2          4
         2          5
         2          6
         3          7
         3          8
         3          9
         4         10
         4         11
         4         12
12 rows selected.

so drop group 4 till 12 beloging to thread 2 till 4

SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 7;
Database altered.
SQL> alter database drop logfile group 8;
Database altered.
SQL> alter database drop logfile group 9;
Database altered.
SQL> alter database drop logfile group 10;
Database altered.
SQL> alter database drop logfile group 11;
Database altered.
SQL> alter database drop logfile group 12;
Database altered.
SQL> select thread#, group# from v$log;
   THREAD#     GROUP#
---------- ----------
         1          1
         1          2
         1          3

Now drop all undo tablespace execpt the one from the active instance

SQL> drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace UNDOTBS3 including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace UNDOTBS4 including contents and datafiles;
Tablespace dropped.

Now we cretae pfile from spfile

SQL> create pfile from spfile;
shutdown database and start it again in mount fase restrict

SQL>shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount restrict
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size            1241517520 bytes
Database Buffers          352321536 bytes
Redo Buffers                7344128 bytes
Database mounted.

Now we can drop the database.

SQL> drop database;

And that's it.