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 /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, not orapwd): cd /dbs orapwd file=orapwdavidh password=xxxxxxxx Add the following to /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 /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) ) ) /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 '/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 move tablespace . 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.