RMAN Create Historical Subset Data

[Update 2015-08]
If the scope of data to be restored exactly or almost exactly matches one or a few tablespaces, RECOVER TABLESPACE may be a better option.
Example: recover tablespace vpx until time '20150821 06:00:00' auxiliary destination '/ext/temp/restorevpx';
The above RMAN command restores and recovers the VPX tablespace to the point in time specified (assume NLS_DATE_FORMAT is yyyymmdd hh24:mi:ss), after mkdir /ext/temp/restorevpx where enough space is available. There's no need for manual creation of the auxiliary instance, data pump export and import. The only other command to type is alter tablespace vpx online at the end.
Warning: Since the whole tablespace is restored, anything created in the tablespace after '20150821 06:00:00' will disappear after recovery. You can export them out before the restore and import them after it. Also, the trace files in /u01/app/oracle/diag/rdbms/rhff_tspitr_oracs10 and dumpfile and empty directories in /ext/temp/restorevpx should be manually removed after all is done.
Ref: http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmtspit.htm#BRADV89796
[End of update 2015-08]


Problem: Table data is deleted from a production database oracp3. Flashback query no longer returns data:

SQL> select * from INSIDE_ILORACLE.IL_FAQ as of timestamp sysdate - 0.9;
 create table temp as select * from INSIDE_ILORACLE.IL_FAQ as of timestamp sysdate - 0.9
                                                    * 
 ERROR at line 1: 
 ORA-01555: snapshot too old: rollback segment number 51 with name "_SYSSMU51$" too small


Solution: Need to create a subset auxiliary database to restore data.

Comment out unnecessary cron jobs. (And make a calendar item as a reminder to uncomment back later.)
sudo service splx stop, or in RHEL7: sudo systemctl stop ds_agent
Notify the backup team to not do maintenance on Tivoli
IMPORTANT: If use NFS, note that NFS will have a severe bottleneck after certain amount of data is copied.

On production (assume there's enough free space on /u01):

mkdir /u01/temp/davidh
cd /u01/temp/davidh
mkdir audit archive cdump

vi initdavidh.ora 
Note: Change compatible parameter and all paths
      mkdir all paths in this init.ora
      you can also create the file in <db ORACLE_HOME>/dbs
      db_file_name_convert and log_file_name_convert assumes /u01/temp/davidh can store all the datafiles needed
      If not, choose some other filesystem. See Note 1 below.
*.audit_file_dest='/u01/temp/davidh/audit'
#*.background_dump_dest='/u01/temp/davidh/bdump'
*.compatible='11.2.0.4.0'
*.control_files='/u01/temp/davidh/control.ctl'
*.core_dump_dest='/u01/temp/davidh/cdump'
*.db_block_size=8192
*.db_cache_advice='OFF'
#*.db_domain='mdacc.tmc.edu'
*.db_name='davidh'
*.dispatchers=''
*.processes=1000
*.log_archive_dest_1='location=/u01/temp/davidh/archive'
*.remote_login_passwordfile='exclusive'
*.sga_target=3G
*.streams_pool_size=150m
*.undo_management='AUTO'
#*.user_dump_dest='/u01/temp/davidh/udump'
#Very carefully specify the name convert params. Trailing / matters. Don't forget tempfile location.
db_file_name_convert=('+DATA/oracp3/datafile','/u01/temp/davidh','+DATA/oracp3/tempfile','/u01/temp/davidh')
#Logfile conversion is probably not needed for the duplicate command:
log_file_name_convert=('+DATA/oracp3/datafile','/u01/temp/davidh','+FRA/oracp3/onlinelog','/u01/temp/davidh')
#See Note:334899.1
_no_recovery_through_resetlogs=TRUE

Add davidh to /etc/oratab

. oraenv
davidh

Create password file (note the filename, orapw<SID>, not orapwd<SID>):
cd <db ORACLE_HOME>/dbs
orapwd file=orapwdavidh password=xxxxxxxx


Add the following to <db ORACLE_HOME>/network/admin/tnsnames.ora (note the port is not 1521; assume the current host is dcprpcora2b):
davidh =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dcprpcora2b)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SID = davidh)
    )
  )

Modify <ASM ORACLE_HOME>/network/admin/listener.ora to add a new listener
(String DCPRPCORA2B as part of listener name can be changed but it's optional; it's just a name):

LISTENER_DCPRPCORA2B =
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=dcprpcora2b.mdanderson.edu)(PORT=1522))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))
    )
  )
SID_LIST_LISTENER_DCPRPCORA2B =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/grid)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = davidh)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
      (SID_NAME = davidh)
    )
  )

<ASM ORACLE_HOME>/bin/lsnrctl start LISTENER_DCPRPCORA2B

Make sure sqlplus can connect to sys through SQL*Net:
sqlplus sys/xxxxxxxx@davidh as sysdba


sqlplus / as sysdba
startup nomount pfile=/u01/temp/davidh/initdavidh.ora
May not need: If NFS is used (see Note 1): alter system set events '10298 trace name context forever, level 32';


May not need:
If NFS is used (see Note 1), and assume production is oracp3:
sqlplus sys/oracp3syspassword@oracp32 as sysdba
alter system set events '10298 trace name context forever, level 32';


(optional, see Note 4 below) alter database default tablespace system;
Make a calendar item to change them back later. The latter should be changed back to users.


To run duplicate, Use one of the two ways to login RMAN depending on the current DB environment:
either
rman target sys/oracp3syspassword@oracp3 catalog rman@odrp auxiliary / <-- use oraenv to set current SID to davidh
or
rman target / catalog rman@odrp auxiliary sys/davidhsyspassword@davidh <-- set SID to oracp3; make sure davidh is in tnsnames.ora

(For 10g database duplicate command syntax, see Note 10)

Specify the tablespace(s) you need (no need to specify SYSTEM, UNDO, SYSAUX, which are included
automatically), plus all tablespaces with materialized views and indexes. If included tablespaces 
add up to a huge size, it's important to limit to the minimum tablespace(s). See Note 7 for details.

run {
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate auxiliary channel a1 device type disk;
allocate auxiliary channel a2 device type disk;
duplicate target database to davidh
pfile=/u01/temp/davidh/initdavidh.ora
tablespace 'BATCH_DATA'
until time "to_date('20090309 08:00','yyyymmdd hh24:mi')"
logfile '/u01/temp/davidh/redo01.log' size 100m,
        '/u01/temp/davidh/redo02.log' size 100m,
        '/u01/temp/davidh/redo03.log' size 100m;
}

*** Change tracking file issue

Most source databases have change tracking enabled (check v$block_change_tracking for a row). Before 12c, several 
serious bugs will abort the duplicate at the end of duplicate, because the file name convert parameter doesn't 
convert the change tracking file (ctf) from e.g. +DATA/oracp3/changetracking/ctf.xxx.xxx to 
+DATA/davidh/changetracking/ctf.xxx.xxx. (At the point-in-time you plan to restore to, the database most likely 
had change tracking enabled.) You must follow this workaround:

Not long after duplicate starts, the auxiliary database will change from nomount to mount state. Log into it as 
sysdba and re-enable change tracking so the local ctf is pre-created:

Disable to wipe out the wrong ctf file path in auxiliary instance:
alter database disable block change tracking;

Create a ctf file with the correct location:
alter database enable block change tracking using file '+DATA';

select * from v$block_change_tracking;
you should see the correct one: +DATA/davidh/changetracking/ctf.xxx

The workarounds on the Oracle website (e.g. specify spfile set db_file_name_convert=... for the duplicate 
command) do not work. (An example is in Note 12 if you want to try.)

*** Tape access issue

If it needs to access tape, make sure /opt/tivoli/tsm/client/oracle/bin64/tdpoerror.log is writable on this node:
 sudo setfacl -m u:oracle:rw tdpoerror.log
or simply `sudo chmod 666 tdpoerror.log' or `sudo chown oracle tdpoerror.log'
 



After duplicate finishes, open davidh by `alter database open resetlogs'. (11g may automatically do this.)


Duplicate may throw all kinds of errors. They're documented below in the Note section.
In case of mysterious errors, run the RMAN command in debug mode:
rman ... trace=rman_trace.trc log=rman_output.txt 
 set echo on
 debug on
 duplicate ...
 debug off 



Note:

1. Estimate storage needed by adding all datafiles for these tablespaces: system, sysaux, undo*, and the one you need, and 
add all archive logs that start from the restore starting point (which is usually an incremental0 or full backup) to the restore 
end point:
select trunc(first_time), count(*), round(sum(blocks)*512/1048576) mb
from v$archived_log
where first_time > &restored_to
group by trunc(first_time) order by 1;
Add the sizes of the tablespaces indicated in the error related to missing tablespaces for mviews and indexes.

If an NFS mount is needed, follow this instruction (take ddrrpcora1a as an example for NFS server).

On NFS server ddrrpcora1a:
vi /etc/exports		#add or uncomment /ext/export *(rw,async) to the file
exportfs -a		#provide NFS mount
showmount		#should show no NFS client connected yet
#if not:
service rpcbind start; service nfs start; vi /etc/hosts.allow (add: rpcbind: ALL)
mkdir -p /ext/export/tmp
chmod 777 /ext/export/tmp
service splx stop	#speed up I/O

On client (dcprpcora2a):

mkdir -p /ext/export/tmp
mount -t nfs -o hard,intr,rsize=32768,wsize=32768 ddrrpcora1a:/ext/export/tmp /ext/export/tmp
	#intr so you can kill a hanging cmd if NFS server hangs, rsize and wsize to speed up I/O
service splx stop	#don't waste CPU

Since /ext/export/tmp is used to store datafiles, the parameters db_file_name_convert and log_file_name_convert should 
have that instead of /u01/temp/davidh. Paths in other params can stay the same or can change to /ext/export/tmp, too.


2. Since oracp3 is RAC and davidh is not, the duplicate command may throw an error:

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/10/2009 15:34:46
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

The solution is to add _no_recovery_through_resetlogs=TRUE according to Note:334899.1 and restart the auxiliary and open resetlogs.

(It's possible we could have avoided this by having instance_number=1 in initdavidh.ora without setting the undocumented param. Untested.)


3. Note:335851.1 (Automatic TSPITR in 10G RMAN -A walk Through) should NOT be followed because the command

recover tablespace "TEST" until time "23-SEP-2005 10:00:00','DD-MON-YYYY HH24:MI:SS"
      auxiliary destination 'D:\BAckup\temp';

run in prod will revert the live production tablespace to the old state, not creating another auxiliary database and staying there for you to explore.


4. The last phase of duplicate tries to drop skipped tablespaces. It may throw errors:

executing Memory Script

database opened

contents of Memory Script:
{
# drop offline and skipped tablespaces
sql clone "drop tablespace  USERS including contents cascade constraints";
# drop offline and skipped tablespaces
sql clone "drop tablespace  TSS_INDEX including contents cascade constraints";
...
sql statement: drop tablespace  USERS including contents cascade constraints
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/10/2010 10:56:29
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 06/10/2010 10:56:29
RMAN-11003: failure during parse/execution of SQL statement: drop tablespace  USERS including contents cascade constraints
ORA-12919: Can not drop the default permanent tablespace

It's because USERS is the database default tablespace (select * from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE').
You can safely ignore it. Alternatively, we can temporarily change the production DB default tablespace to either system or the restored 
tablespace before doing all the work:
alter database default tablespace system;
and change it back to users after the work is done.


5. If duplicate fails because only a few archive logs are not found:

RMAN-06054: media recovery requesting unknown log: thread 1 seq 49184 lowscn 65583552185

see if the archivelogs were deleted:

select distinct sequence# from v$backup_archivelog_details where thread# = 1 and sequence# between 49180 and 49190 order by 1;
select * from v$archived_log where thread# = 1 and sequence# between 49180 and 49190; --focus on DELETED column

Check other threads (thread 2 e.g.) as well.

If there's a standby where the archivelogs still exist, logon standby:

$ rman target / #do NOT specify catalog
run {
allocate channel t device type disk format '/tmp/arc_%u';  <-- assume /tmp has enough space
backup archivelog from sequence 49183 until sequence 49185 thread 1;
}

scp the files to primary (dcprpcora2a), where you run
$ rman target / catalog ...
catalog start with '<dir path>/arc';

Also, see point 9 below.


6. If you see this error for duplicate:
	RMAN-03014: implicit resync of recovery catalog failed
	RMAN-03009: failure of partial resync command on clone_default channel at 03/27/2012 15:25:16
just retry.


7. If you see ORA-05589, RMAN-05589, ORA-39911, ORA-39908, ORA-39906:

[Update 2016-02] According to
http://www.freelists.org/post/oracle-l/Any-way-to-force-RMAN-to-skip-TTS-selfcontainment-check,4
we may be able to work around this problem by not connecting to target. It's said that no TTS check will be done.
Relevant articles:
http://www.askmlabs.com/2014/04/duplicate-database-rman-target-database-incarnation.html
https://oracle-base.com/articles/11g/duplicate-database-using-rman-11gr2
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#BRADV89929
Perform Backup Based RMAN DUPLICATE Without Connecting To Target Database For Both Disk & Tape Backups (Doc ID 1375864.1)
RMAN DUPLICATE without TARGET fails with RMAN-03002 / RMAN-06171 (Doc ID 1428544.1)

(If the workaround above does not work) Add the tablespaces that RMAN says contain materialized views to the list for the duplicate 
command and try again. Re-check file system free space if needed. If a huge tablespace must be added due to a few unimportant mviews, 
see if they can be dropped, or moved, i.e. alter table <mview's container name, check dba_mviews> move tablespace <tablespace you need to 
restore anyway>. Indexes associated with the mview (technically, its container table) may need to be rebuilt into the tablespace too. 
Grant quota as needed. Remember to move/rebuild back later.

If an index of an included table is built in a non-included tablespace, you'll get
     Violation: ORA-39911: Index EIW.T_PROF_INVC_FACT_PK in tablespace USERS points to partition INVC_93_94 of table EIW.T_PROF_INVC_FACT in tablespace EIW outside of transportable set.
If your goal is to restore EIW but not USERS and there're not many indexes in USERS, just rebuild the indexes to EIW.
Conversely, the following error
     Violation: ORA-39908: Index EIW.T_AU_LOG_THRESHHOLD_PK in tablespace EIW enforces primary constraints  of table EIW.T_AU_LOG_THRESHHOLD in tablespace USERS.
or
     Violation: ORA-39906: Constraint T_AU_LOG_LOAD_CALL_FK between table EIW.T_AU_REF_SUBJECT_AREA in tablespace USERS and table EIW.T_AU_LOG_LOAD_ON_CALL in tablespace EIW.
means you can alter the tables, EIW.T_AU_LOG_THRESHHOLD and EIW.T_AU_REF_SUBJECT_AREA respectively, to move them to tablespace EIW (if there're not many in USERS).
If you get
     Violation: ORA-39921: Default Partition (Table) Tablespace USERS for T_CL_AGGR_PROF_LNITEM_ALL not contained in transportable set.
change the partitioned table's default tablespace: alter table EIW.T_CL_AGGR_PROF_LNITEM_ALL modify default attributes tablespace EIW;
Very important: Make a note or calendar item to remind yourself to rebuild unusable indexes and partitions.



8. If the auxiliary instance wants to create change tracking file in the same path as the target database and so can't, work around by 
temporarily disable it in target: alter database disable block change tracking.


9. If duplicate looks for a very old archive log whose timestamp is older than your level 0 backup you used for the restore (error is 
usually RMAN-6054), there're two possibilities.

* Bug 12625205 or 8554110: The root cause (my paraphrase) is that if you specify "until time...", and Oracle's buggy algorithm to 
convert time to log sequence fails to find the log, it automatically assumes a very old log, which normally no longer exists. The 
workaround is to check v$log_history and find the log sequence yourself and use it in "until sequence...", or in some cases, use SCN.

* If you already ran duplicate earlier (either just a few minutes ago or a few months ago), leaving restored datafiles in the path 
you're using this time, duplicate by default has the optimization to not restore it again. If the previously restored datafile required 
a very old log (either due to the bug said above or because the datafile was there a few months ago genuinely needing the old log back 
then), your new duplicate will keep asking for the old log, even if you move "until time..." to very recent. The solution is to append 
"noresume" to the end of the duplicate command. Ref: 1549172.1

10. The duplicate syntax for 10g is:

duplicate target database to davidh
pfile=/u01/temp/davidh/initdavidh.ora
skip tablespace 
'APEP_DATA','APEP_INDEX','AUDIT_DATA','BOXI2_DATA','CT_DATA','CT_INDEX','EBP_DATA',
'EBP_INDEX','INSIDE3_ISD1','MYMDA','NPC_DATA','NPC_INDEX','PSFT_DATA','QC_DATA',
'RXMASTER','SABA_DATA','TRACS_DATA','TRACS_INDEX','TSS_DATA','TSS_INDEX','USERS'
until time "to_date('20090309 08:00','yyyymmdd hh24:mi')"
logfile '/u01/temp/davidh/redo01.log' size 10m,
        '/u01/temp/davidh/redo02.log' size 10m,
        '/u01/temp/davidh/redo03.log' size 10m;

Make sure SYSTEM, UNDO, SYSAUX, TEMP and the needed tablespace, in our case ISDC which contains the messed up table, 
are NOT skipped.

11. If you see error ORA-279:

Fri Jan 08 16:16:49 2016
ORA-279 signalled during: alter database recover logfile '/dmp1_exp/temp/davidh/archive/1_22527_856182470.dbf'...
alter database recover logfile '/dmp1_exp/temp/davidh/archive/2_23927_856182470.dbf'
Media Recovery Log /dmp1_exp/temp/davidh/archive/2_23927_856182470.dbf

it can be ignored.

12. According to Docs 1098638.1, 10193846.8, specifying "spfile set db_file_name_convert" options for duplicate 
can work around the error "ORA-19755: could not open change tracking file", e.g.
run {
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate auxiliary channel a1 device type disk;
allocate auxiliary channel a2 device type disk;
duplicate target database to davidh
until time "to_date('20180712 09:15','yyyymmdd hh24:mi')"
logfile '/ext2/davidh/redo01.log' size 200m, '/ext2/davidh/redo02.log' size 200m, '/ext2/davidh/redo03.log' size 200m
spfile parameter_value_convert('provp','davidh')
set db_file_name_convert='+DATA/provp/datafile','/ext2/davidh','+DATA/provp/tempfile','/ext2/davidh'
set log_file_name_convert='+DATA/provp/datafile','/ext2/davidh','+FRA/provp/onlinelog','/ext2/davidh'
set db_unique_name='davidh'
set sga_max_size='3g'
set sga_target='3g'
set audit_file_dest='/ext2/davidh/audit'
set control_files='/ext2/davidh/control.ctl'
set core_dump_dest='/ext2/davidh/cdump'
set log_archive_dest_1='location=/ext2/davidh/archive'
reset db_cache_size
reset log_archive_dest_2;
}
That does not work. Note that specifying spfile does not allow pfile. So all parameters previously in pfile have to be 
specified here. All paths in the parameter values must exist. Contrary to documentation examples, values beginning with 
numbers must be quoted (e.g. sga_max_size=3g is incorrect). To reduce SGA, db_cache_size must be small or unset.