{"id":73151,"date":"2015-08-10T16:58:06","date_gmt":"2015-08-10T16:58:06","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/tspitr-using-acfs-snapshots\/"},"modified":"2021-07-14T13:07:21","modified_gmt":"2021-07-14T13:07:21","slug":"tspitr-using-acfs-snapshots","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/tspitr-using-acfs-snapshots\/","title":{"rendered":"TSPITR Using ACFS Snapshots"},"content":{"rendered":"<p>Starting with Oracle Grid Infrastructure 12c (12.1), Oracle Cloud File system (ASM Cluster File System in cluster environment) supports database files (database version 11.2.0.4 and up) in addition to general-purpose files so that entire Oracle databases can be stored inside Oracle Cloud FS. In my earlier articles I demonstrated that:<\/p>\n<ul>\n<li>For a database having its files stored on Oracle Cloud file system, Oracle ACFS Snapshots may serve as point-in-time backups of the database which can be used for online recovery of database files.<\/li>\n<li>ACFS snapshots taken while the database is in backup mode can be integrated with RMAN and employed to perform complete recovery using RMAN RESTORE \/ RECOVER commands.<\/li>\n<\/ul>\n<p>In this article, I will demonstrate that ACFS snapshots taken while the database is in backup mode can be integrated with RMAN and employed to perform Tablespace Point In Time Recovery (TSPITR) as well.<\/p>\n<p>Currently\u00a0I am working in Oracle Database 12.1.0.2 cluster environment and have created a database named cfsdb, with all of its files stored on the cloud file system as shown below:<\/p>\n<pre>[oracle@host01 ~]$ srvctl config database -d cfsdb\r\n\r\nDatabase unique name: cfsdb\r\nDatabase name: cfsdb\r\nOracle home: \/u01\/app\/oracle\/product\/12.1.0\/dbhome_1\r\nOracle user: oracle\r\nSpfile: <span style=\"color: red;\"><strong>\/mnt\/acfs\/oradata\/cfsdb\/spfilecfsdb.ora<\/strong><\/span>\r\nPassword file: <span style=\"color: red;\"><strong>\/mnt\/acfs\/oradata\/cfsdb\/orapwcfsdb<\/strong><\/span>\r\nDomain: \r\nStart options: open\r\nStop options: immediate\r\nDatabase role: PRIMARY\r\nManagement policy: AUTOMATIC\r\nServer pools: \r\nDisk Groups: \r\nMount point paths: <span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\r\nServices: \r\nType: RAC\r\nStart concurrency: \r\nStop concurrency: \r\nOSDBA group: dba\r\nOSOPER group: oper\r\nDatabase instances: cfsdb1\r\nConfigured nodes: host01\r\nDatabase is administrator managed\r\n\r\nCFSDB &gt; select name from v$controlfile;\r\n\r\nNAME\r\n----------------------------------------\r\n<span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\/oradata\/cfsdb\/control01.ctl\r\n<span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\/cfsdb\/control02.ctl\r\n\r\n\r\nCFSDB &gt; select member from v$logfile;\r\nMEMBER\r\n----------------------------------------\r\n<span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\/oradata\/cfsdb\/redo03.log\r\n<span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\/oradata\/cfsdb\/redo02.log\r\n<span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\/oradata\/cfsdb\/redo01.log\r\n\r\n\r\n\r\nCFSDB &gt;   select name from v$archived_log;\r\n\r\nNAME\r\n-----------------------------------------------------------------\r\n<span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\/CFSDB\/archivelog\/2015_08_05\/o1_mf_1_8_bw3nhvkn_.arc\r\n\r\nCFSDB &gt; select name from v$datafile; \r\n\r\nNAME\r\n-----------------------------------------------------------------\r\n<span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\/oradata\/cfsdb\/system01.dbf\r\n<span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\/oradata\/cfsdb\/sysaux01.dbf\r\n<span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\/oradata\/cfsdb\/undotbs01.dbf\r\n<span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\/oradata\/cfsdb\/example01.dbf\r\n<span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\/oradata\/cfsdb\/users01.dbf\r\n\r\nCFSDB&gt;sho parameter db_recovery\r\n\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\ndb_recovery_file_dest                string      <span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\r\ndb_recovery_file_dest_size           big integer 4560M<\/pre>\n<p>Let&#8217;s confirm that the database cfsdb is in archivelog mode:<\/p>\n<pre>CFSDB &gt; archive log list;\r\n\r\nDatabase log mode              <span style=\"color: red;\"><strong>Archive Mode<\/strong><\/span>\r\nAutomatic archival             Enabled\r\nArchive destination            USE_DB_RECOVERY_FILE_DEST\r\nOldest online log sequence     6\r\nNext log sequence to archive   8\r\nCurrent log sequence           8<\/pre>\n<p>Create a new tablespace named TEST with its data file stored on Oracle Cloud file system:<\/p>\n<pre>SQL&gt;create tablespace test datafile '<span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\/oradata\/cfsdb\/test01.dbf' size 50m;<\/pre>\n<p>Create a table named HR.EMP with 107 rows in the TEST tablespace:<\/p>\n<pre>SQL&gt; create table hr.emp tablespace test as select * from hr.employees;\r\n\r\nSQL&gt;  select owner, table_name, tablespace_name \r\n           from dba_tables\r\n           where owner = 'HR' and table_name = 'EMP';\r\n\r\nOWNER      TABLE_NAME      TABLESPACE_NAME\r\n---------- --------------- ------------------------------\r\nHR         EMP             <span style=\"color: red;\"><strong>TEST<\/strong><\/span>\r\n\r\nSQL&gt; select count(*) from hr.emp;\r\n\r\n  COUNT(*)\r\n----------\r\n       <span style=\"color: red;\"><strong>107<\/strong><\/span><\/pre>\n<p>Take a backup of the control file:<\/p>\n<pre>RMAN&gt; delete backup of controlfile;\r\n      backup current controlfile format '\/home\/oracle\/%U';\r\n\r\nStarting backup at 06-AUG-15\r\nusing channel ORA_DISK_1\r\nchannel ORA_DISK_1: starting full datafile backup set\r\nchannel ORA_DISK_1: specifying datafile(s) in backup set\r\nincluding current control file in backup set\r\nchannel ORA_DISK_1: starting piece 1 at 06-AUG-15\r\nchannel ORA_DISK_1: finished piece 1 at 06-AUG-15\r\n<span style=\"color: red;\"><strong>piece handle=\/home\/oracle\/03qdtpdo_1_1 tag=TAG20150806T111711 comment=NONE<\/strong><\/span>\r\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03\r\nFinished backup at 06-AUG-15<\/pre>\n<p>Verify that currently there are no snapshots of the cloud file system hosting the database files:<\/p>\n<pre>[root@host01 oracle]# acfsutil snap info \/mnt\/acfs\r\n\r\n    number of snapshots:  <span style=\"color: red;\"><strong>0<\/strong><\/span>\r\n    snapshot space usage: 0  ( 0.00 )<\/pre>\n<p>Take a snapshot (example_snap) of the cloud file system while the database is in backup mode:<\/p>\n<pre>SQL&gt; alter database begin backup;\r\n\r\nDatabase altered.\r\n\r\n[root@host01 acfs]# acfsutil snap create example_snap \/mnt\/acfs\r\n\r\nacfsutil snap create: Snapshot operation is complete.\r\n \r\n[root@host01 acfs]# acfsutil snap info \/mnt\/acfs\r\n \r\nsnapshot name:               <span style=\"color: red;\"><strong>example_snap<\/strong><\/span>\r\nsnapshot location:           <span style=\"color: red;\"><strong>\/mnt\/acfs\/.ACFS\/snaps\/example_snap<\/strong><\/span>\r\nRO snapshot or RW snapshot:  RO\r\nparent name:                 \/mnt\/acfs\r\nsnapshot creation time:      Thu Aug  6 11:18:25 2015\r\n\r\n    number of snapshots:  1\r\n    snapshot space usage: 17858560  (  17.03 MB )\r\n\r\nSQL&gt; alter database end backup;\r\n\r\nDatabase altered.<\/pre>\n<p>Check that all the datafiles are available in the snapshot:<\/p>\n<pre>[root@host01 acfs]# ls -l <span style=\"color: red;\"><strong>\/mnt\/acfs<\/strong><\/span>\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/*.dbf\r\n\r\n-rw-r----- 1 oracle oinstall 1342840832 Aug 6 10:57 \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/<span style=\"color: red;\"><strong>example01.dbf<\/strong><\/span>\r\n-rw-r----- 1 oracle oinstall 650125312  Aug 6 11:15 \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/<span style=\"color: red;\"><strong>sysaux01.dbf<\/strong><\/span>\r\n-rw-r----- 1 oracle oinstall 828383232  Aug 6 10:57 \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/<span style=\"color: red;\"><strong>system01.dbf<\/strong><\/span>\r\n-rw-r----- 1 oracle oinstall 62922752   Aug 5 15:08 \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/<span style=\"color: red;\"><strong>temp01.dbf<\/strong><\/span>\r\n-rw-r----- 1 oracle oinstall 52436992   Aug 6 11:05 \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/<span style=\"color: red;\"><strong>test01.dbf<\/strong><\/span>\r\n-rw-r----- 1 oracle oinstall 99622912   Aug 6 10:57 \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/<span style=\"color: red;\"><strong>undotbs01.dbf<\/strong><\/span>\r\n-rw-r----- 1 oracle oinstall 5251072    Aug 6 10:57 \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/<span style=\"color: red;\"><strong>users01.dbf<\/strong><\/span><\/pre>\n<p>Catalog all the data files in the snapshot example_snap in directory &#8220;<code>\/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/<\/code>&#8220;:<\/p>\n<pre>RMAN&gt; catalog start with '\/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb';\r\n\r\nsearching for all files that match the pattern \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\r\n\r\nList of Files Unknown to the Database\r\n=====================================\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/orapwcfsdb\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/sysaux01.dbf\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/system01.dbf\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/users01.dbf\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/undotbs01.dbf\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/control01.ctl\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/redo01.log\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/redo02.log\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/redo03.log\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/temp01.dbf\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/example01.dbf\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/spfilecfsdb.ora\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/test01.dbf\r\n\r\nDo you really want to catalog the above files (enter YES or NO)? <strong>yes<\/strong>\r\ncataloging files...\r\ncataloging done\r\n\r\n<span style=\"color: red;\"><strong>List of Cataloged Files<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>=======================<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>File Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/sysaux01.dbf<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>File Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/system01.dbf<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>File Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/users01.dbf<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>File Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/undotbs01.dbf<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>File Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/temp01.dbf<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>File Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/example01.dbf<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>File Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/test01.dbf<\/strong><\/span>\r\n\r\nList of Files Which Were Not Cataloged\r\n=======================================\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/orapwcfsdb\r\nRMAN-07518: Reason: Foreign database file DBID: 0 Database Name:\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/control01.ctl\r\nRMAN-07519: Reason: Error while cataloging. See alert.log.\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/redo01.log\r\nRMAN-07529: Reason: catalog is not supported for this file type\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/redo02.log\r\nRMAN-07529: Reason: catalog is not supported for this file type\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/redo03.log\r\nRMAN-07529: Reason: catalog is not supported for this file type\r\nFile Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/spfilecfsdb.ora\r\nRMAN-07518: Reason: Foreign database file DBID: 0 Database Name:<\/pre>\n<p>It can be seen that all the data file copies have been catalogued whereas redo log files, SPfile and control file have not been registered with RMAN.<\/p>\n<pre>RMAN&gt; list copy of database;\r\n\r\nList of Datafile Copies\r\n=======================\r\n\r\nKey     File S Completion Time Ckp SCN    Ckp Time       \r\n------- ---- - --------------- ---------- ---------------\r\n14      1    A 06-AUG-15       1857785    06-AUG-15      \r\n        Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/system01.dbf\r\n\r\n18      2    A 06-AUG-15       1857785    06-AUG-15      \r\n        Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/test01.dbf\r\n\r\n13      3    A 06-AUG-15       1857785    06-AUG-15      \r\n        Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/sysaux01.dbf\r\n\r\n16      4    A 06-AUG-15       1857785    06-AUG-15      \r\n        Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/undotbs01.dbf\r\n\r\n17      5    A 06-AUG-15       1857785    06-AUG-15      \r\n        Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/example01.dbf\r\n\r\n15      6    A 06-AUG-15       1857785    06-AUG-15      \r\n        Name: \/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/users01.dbf<\/pre>\n<p>Check the current table has 107 rows:<\/p>\n<pre>CFSDB&gt; select count(*) from hr.emp;\r\n\r\n  COUNT(*)\r\n----------\r\n       <span style=\"color: red;\"><strong>107<\/strong><\/span><\/pre>\n<p>Create a restore point EMP_107:<\/p>\n<pre>CFSDB&gt;create restore point EMP_107;\r\nRestore point created.\r\n\r\nCFSDB&gt; select scn, name from v$restore_point\r\n \r\n\r\n       SCN NAME\r\n---------- ------------------------------\r\n   1858125 <span style=\"color: red;\"><strong>EMP_107<\/strong><\/span><\/pre>\n<p>Insert records into HR.EMP so that there are now 214 records:<\/p>\n<pre>CFSDB&gt;insert into hr.emp select * from hr.emp;\r\n\r\n              commit;\r\n\r\nCommit complete.\r\n\r\nCFSDB&gt;select count(*) from hr.emp;\r\n\r\n  COUNT(*)\r\n----------\r\n       <span style=\"color: red;\"><strong>214<\/strong><\/span><\/pre>\n<p>Perform TSPITR for TEST tablespace until restore point EMP_107 when there were 107 records in HR.EMP:<\/p>\n<pre>SQL&gt; alter tablespace test offline;\r\n\r\nTablespace altered.\r\n\r\nRMAN&gt; recover tablespace \"TEST\" until restore point emp_107 auxiliary destination '\/home\/oracle'; \r\n\r\nStarting recover at 06-AUG-15\r\ncurrent log archived\r\nusing channel ORA_DISK_1\r\nRMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time\r\n\r\nList of tablespaces expected to have UNDO segments\r\nTablespace SYSTEM\r\nTablespace UNDOTBS1\r\n\r\nCreating automatic instance, with SID='neph'\r\n\r\ninitialization parameters used for automatic instance:\r\ndb_name=CFSDB\r\ndb_unique_name=neph_pitr_CFSDB\r\ncompatible=12.1.0.2.0\r\ndb_block_size=8192\r\ndb_files=200\r\ndiagnostic_dest=\/u01\/app\/oracle\r\n_system_trig_enabled=FALSE\r\nsga_target=412M\r\nprocesses=200\r\ndb_create_file_dest=\/home\/oracle\r\nlog_archive_dest_1='location=\/home\/oracle'\r\n#No auxiliary parameter file used\r\n\r\n\r\nstarting up automatic instance CFSDB\r\n\r\nOracle instance started\r\n\r\nTotal System Global Area     432013312 bytes\r\n\r\nFixed Size                     2925264 bytes\r\nVariable Size                155192624 bytes\r\nDatabase Buffers             268435456 bytes\r\nRedo Buffers                   5459968 bytes\r\nAutomatic instance created\r\nRunning TRANSPORT_SET_CHECK on recovery set tablespaces\r\nTRANSPORT_SET_CHECK completed successfully\r\n\r\ncontents of Memory Script:\r\n{\r\n# set requested point in time\r\nset until  scn 1858126;\r\n# restore the controlfile\r\nrestore clone controlfile;\r\n \r\n# mount the controlfile\r\nsql clone 'alter database mount clone database';\r\n \r\n# archive current online log \r\nsql 'alter system archive log current';\r\n# avoid unnecessary autobackups for structural changes during TSPITR\r\nsql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';\r\n}\r\nexecuting Memory Script\r\n\r\nexecuting command: SET until clause\r\n\r\nStarting restore at 06-AUG-15\r\nallocated channel: ORA_AUX_DISK_1\r\nchannel ORA_AUX_DISK_1: SID=29 device type=DISK\r\n\r\nchannel ORA_AUX_DISK_1: starting datafile backup set restore\r\nchannel ORA_AUX_DISK_1: <span style=\"color: red;\"><strong>restoring control file<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>channel ORA_AUX_DISK_1: reading from backup piece \/home\/oracle\/03qdtpdo_1_1<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>channel ORA_AUX_DISK_1: piece handle=\/home\/oracle\/03qdtpdo_1_1 tag=TAG20150806T111711<\/strong><\/span>\r\nchannel ORA_AUX_DISK_1: restored backup piece 1\r\nchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04\r\noutput file name=\/home\/oracle\/CFSDB\/controlfile\/o1_mf_bw5xshb5_.ctl\r\nFinished restore at 06-AUG-15\r\n\r\nsql statement: alter database mount clone database\r\n\r\nsql statement: alter system archive log current\r\n\r\nsql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;\r\n\r\ncontents of Memory Script:\r\n{\r\n# set requested point in time\r\nset until  scn 1858126;\r\n# set destinations for recovery set and auxiliary set datafiles\r\nset newname for clone datafile  1 to new;\r\nset newname for clone datafile  4 to new;\r\nset newname for clone datafile  3 to new;\r\nset newname for clone tempfile  1 to new;\r\nset newname for datafile  2 to \r\n \"\/mnt\/acfs\/oradata\/cfsdb\/test01.dbf\";\r\n# switch all tempfiles\r\nswitch clone tempfile all;\r\n# restore the tablespaces in the recovery set and the auxiliary set\r\nrestore clone datafile  1, 4, 3, 2;\r\n \r\nswitch clone datafile all;\r\n}\r\nexecuting Memory Script\r\n\r\nexecuting command: SET until clause\r\n\r\nexecuting command: SET NEWNAME\r\n\r\nexecuting command: SET NEWNAME\r\n\r\nexecuting command: SET NEWNAME\r\n\r\nexecuting command: SET NEWNAME\r\n\r\nexecuting command: SET NEWNAME\r\n\r\nrenamed tempfile 1 to \/home\/oracle\/CFSDB\/datafile\/o1_mf_temp_%u_.tmp in control file\r\n\r\n<span style=\"color: red;\"><strong>Starting restore<\/strong><\/span> at 06-AUG-15\r\nusing channel ORA_AUX_DISK_1\r\n\r\nchannel ORA_AUX_DISK_1: <span style=\"color: red;\"><strong>restoring datafile 00001<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>input datafile copy RECID=13 STAMP=887023637 file<\/strong><\/span> \r\n\r\n<span style=\"color: red;\"><strong>name=\/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/system01.dbf<\/strong><\/span>\r\ndestination for restore of datafile 00001: \/home\/oracle\/CFSDB\/datafile\/o1_mf_system_%u_.dbf\r\nchannel ORA_AUX_DISK_1: copied datafile copy of datafile 00001\r\noutput file name=\/home\/oracle\/CFSDB\/datafile\/o1_mf_system_bw5xt0b5_.dbf RECID=17 STAMP=887023777\r\nchannel ORA_AUX_DISK_1: <span style=\"color: red;\"><strong>restoring datafile 00004<\/strong><\/span> \r\n<span style=\"color: red;\"><strong>input datafile copy RECID=14 STAMP=887023638 file<\/strong><\/span> \r\n\r\n<span style=\"color: red;\"><strong>name=\/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/undotbs01.dbf<\/strong><\/span> \r\ndestination for restore of datafile 00004: \/home\/oracle\/CFSDB\/datafile\/o1_mf_undotbs1_%u_.dbf\r\nchannel ORA_AUX_DISK_1: copied datafile copy of datafile 00004\r\noutput file name=\/home\/oracle\/CFSDB\/datafile\/o1_mf_undotbs1_bw5xymgo_.dbf RECID=18 STAMP=887023811\r\nchannel ORA_AUX_DISK_1: <span style=\"color: red;\"><strong>restoring datafile 00003<\/strong><\/span> \r\n<span style=\"color: red;\"><strong>input datafile copy RECID=15 STAMP=887023639 file<\/strong><\/span> \r\n\r\n<span style=\"color: red;\"><strong>name=\/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/sysaux01.dbf<\/strong><\/span> \r\ndestination for restore of datafile 00003: \/home\/oracle\/CFSDB\/datafile\/o1_mf_sysaux_%u_.dbf\r\nchannel ORA_AUX_DISK_1: copied datafile copy of datafile 00003\r\noutput file name=\/home\/oracle\/CFSDB\/datafile\/o1_mf_sysaux_bw5xzd5n_.dbf RECID=19 STAMP=887023962\r\nchannel ORA_AUX_DISK_1: <span style=\"color: red;\"><strong>restoring datafile 00002<\/strong><\/span> \r\n<span style=\"color: red;\"><strong>input datafile copy RECID=16 STAMP=887023640 file<\/strong><\/span> \r\n\r\n<span style=\"color: red;\"><strong>name=\/mnt\/acfs\/.ACFS\/snaps\/example_snap\/oradata\/cfsdb\/test01.dbf<\/strong><\/span> \r\ndestination for restore of datafile 00002: \/mnt\/acfs\/oradata\/cfsdb\/test01.dbf\r\nchannel ORA_AUX_DISK_1: copied datafile copy of datafile 00002\r\noutput file name=\/mnt\/acfs\/oradata\/cfsdb\/test01.dbf RECID=0 STAMP=0\r\nFinished restore at 06-AUG-15\r\n\r\ndatafile 1 switched to datafile copy\r\ninput datafile copy RECID=20 STAMP=887023995 file  \r\n\r\nname=\/home\/oracle\/CFSDB\/datafile\/o1_mf_system_bw5xt0b5_.dbf\r\ndatafile 4 switched to datafile copy\r\ninput datafile copy RECID=21 STAMP=887023995 file  \r\n\r\nname=\/home\/oracle\/CFSDB\/datafile\/o1_mf_undotbs1_bw5xymgo_.dbf\r\ndatafile 3 switched to datafile copy\r\ninput datafile copy RECID=22 STAMP=887023995 file  \r\n\r\nname=\/home\/oracle\/CFSDB\/datafile\/o1_mf_sysaux_bw5xzd5n_.dbf\r\n\r\ncontents of Memory Script:\r\n{\r\n# set requested point in time\r\nset until  scn 1858126;\r\n# online the datafiles restored or switched\r\nsql clone \"alter database datafile  1 online\";\r\nsql clone \"alter database datafile  4 online\";\r\nsql clone \"alter database datafile  3 online\";\r\nsql clone \"alter database datafile  2 online\";\r\n# recover and open resetlogs\r\nrecover clone database tablespace  \"TEST\", \"SYSTEM\", \"UNDOTBS1\", \"SYSAUX\" delete archivelog;\r\nalter clone database open resetlogs;\r\n}\r\nexecuting Memory Script\r\n\r\nexecuting command: SET until clause\r\n\r\nsql statement: alter database datafile  1 online\r\n\r\nsql statement: alter database datafile  4 online\r\n\r\nsql statement: alter database datafile  3 online\r\n\r\nsql statement: alter database datafile  2 online\r\n\r\nStarting recover at 06-AUG-15\r\nusing channel ORA_AUX_DISK_1\r\n\r\nstarting media recovery\r\n\r\narchived log for thread 1 with sequence 13 is already on disk as file  \r\n\/mnt\/acfs\/CFSDB\/archivelog\/2015_08_06\/o1_mf_1_13_bw5xn23v_.arc\r\narchived log file name=\/mnt\/acfs\/CFSDB\/archivelog\/2015_08_06\/o1_mf_1_13_bw5xn23v_.arc thread=1  \r\nsequence=13\r\nmedia recovery complete, elapsed time: 00:00:03\r\nFinished recover at 06-AUG-15\r\n\r\ndatabase opened\r\n\r\ncontents of Memory Script:\r\n{\r\n# make read only the tablespace that will be exported\r\nsql clone 'alter tablespace  \"TEST\" read only';\r\n# create directory for datapump import\r\nsql \"create or replace directory TSPITR_DIROBJ_DPDIR as ''\r\n\/home\/oracle''\";\r\n# create directory for datapump export\r\nsql clone \"create or replace directory TSPITR_DIROBJ_DPDIR as ''\r\n\/home\/oracle''\";\r\n}\r\nexecuting Memory Script\r\n\r\nsql statement: alter tablespace  \"TEST\" read only\r\n\r\nsql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''\/home\/oracle''\r\n\r\nsql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''\/home\/oracle''\r\n\r\nPerforming export of metadata...\r\n   EXPDP&gt; Starting \"SYS\".\"TSPITR_EXP_neph_bFwg\":  \r\n   EXPDP&gt; Processing object type TRANSPORTABLE_EXPORT\/PLUGTS_BLK\r\n   EXPDP&gt; Processing object type TRANSPORTABLE_EXPORT\/TABLE\r\n   EXPDP&gt; Processing object type TRANSPORTABLE_EXPORT\/TABLE_STATISTICS\r\n   EXPDP&gt; Processing object type TRANSPORTABLE_EXPORT\/STATISTICS\/MARKER\r\n   EXPDP&gt; Processing object type TRANSPORTABLE_EXPORT\/POST_INSTANCE\/PLUGTS_BLK\r\n   EXPDP&gt; Master table \"SYS\".\"TSPITR_EXP_neph_bFwg\" successfully loaded\/unloaded\r\n   EXPDP&gt; ******************************************************************************\r\n   EXPDP&gt; Dump file set for SYS.TSPITR_EXP_neph_bFwg is:\r\n   EXPDP&gt;   \/home\/oracle\/tspitr_neph_53179.dmp\r\n   EXPDP&gt; ******************************************************************************\r\n   EXPDP&gt; Datafiles required for transportable tablespace TEST:\r\n   EXPDP&gt;   \/mnt\/acfs\/oradata\/cfsdb\/test01.dbf\r\n   EXPDP&gt; Job \"SYS\".\"TSPITR_EXP_neph_bFwg\" successfully completed at Thu Aug 6 11:45:11 2015 elapsed  \r\n0 00:09:37\r\nExport completed\r\n\r\n\r\ncontents of Memory Script:\r\n{\r\n# shutdown clone before import\r\nshutdown clone abort\r\n# drop target tablespaces before importing them back\r\nsql 'drop tablespace  \"TEST\" including contents keep datafiles cascade constraints';\r\n}\r\nexecuting Memory Script\r\n\r\nOracle instance shut down\r\n\r\nsql statement: drop tablespace  \"TEST\" including contents keep datafiles cascade constraints\r\n\r\nPerforming import of metadata...\r\n   IMPDP&gt; Master table \"SYS\".\"TSPITR_IMP_neph_drcx\" successfully loaded\/unloaded\r\n   IMPDP&gt; Starting \"SYS\".\"TSPITR_IMP_neph_drcx\":  \r\n   IMPDP&gt; Processing object type TRANSPORTABLE_EXPORT\/PLUGTS_BLK\r\n   IMPDP&gt; Processing object type TRANSPORTABLE_EXPORT\/TABLE\r\n   IMPDP&gt; Processing object type TRANSPORTABLE_EXPORT\/TABLE_STATISTICS\r\n   IMPDP&gt; Processing object type TRANSPORTABLE_EXPORT\/STATISTICS\/MARKER\r\n   IMPDP&gt; Processing object type TRANSPORTABLE_EXPORT\/POST_INSTANCE\/PLUGTS_BLK\r\n   IMPDP&gt; Job \"SYS\".\"TSPITR_IMP_neph_drcx\" successfully completed at Thu Aug 6 11:47:47 2015 elapsed  \r\n0 00:01:11\r\nImport completed\r\n\r\n\r\ncontents of Memory Script:\r\n{\r\n# make read write and offline the imported tablespaces\r\nsql 'alter tablespace  \"TEST\" read write';\r\nsql 'alter tablespace  \"TEST\" offline';\r\n# enable autobackups after TSPITR is finished\r\nsql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';\r\n}\r\nexecuting Memory Script\r\n\r\nsql statement: alter tablespace  \"TEST\" read write\r\n\r\nsql statement: alter tablespace  \"TEST\" offline\r\n\r\nsql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;\r\n\r\nRemoving automatic instance\r\nAutomatic instance removed\r\nauxiliary instance file \/home\/oracle\/CFSDB\/datafile\/o1_mf_temp_bw5y6b5p_.tmp deleted\r\nauxiliary instance file \/home\/oracle\/CFSDB\/onlinelog\/o1_mf_3_bw5y5q6k_.log deleted\r\nauxiliary instance file \/home\/oracle\/CFSDB\/onlinelog\/o1_mf_2_bw5y5lho_.log deleted\r\nauxiliary instance file \/home\/oracle\/CFSDB\/onlinelog\/o1_mf_1_bw5y5jo5_.log deleted\r\nauxiliary instance file \/home\/oracle\/CFSDB\/datafile\/o1_mf_sysaux_bw5xzd5n_.dbf deleted\r\nauxiliary instance file \/home\/oracle\/CFSDB\/datafile\/o1_mf_undotbs1_bw5xymgo_.dbf deleted\r\nauxiliary instance file \/home\/oracle\/CFSDB\/datafile\/o1_mf_system_bw5xt0b5_.dbf deleted\r\nauxiliary instance file \/home\/oracle\/CFSDB\/controlfile\/o1_mf_bw5xshb5_.ctl deleted\r\nauxiliary instance file tspitr_neph_53179.dmp deleted\r\nFinished recover at 06-AUG-15\r\n\r\n\r\nCFSDB&gt; alter tablespace test online;\r\n\r\nTablespace altered.<\/pre>\n<p>Verify that TSPITR to restore point EMP_107 is successful and that there are 107 records in HR.EMP now:<\/p>\n<pre>CFSDB&gt; select count(*) from hr.emp;\r\n\r\n  COUNT(*)\r\n----------\r\n       <span style=\"color: red;\"><strong>107<\/strong><\/span><\/pre>\n<p>Thus, we have been able to perform TSPITR by employing the RMAN \u201cRECOVER TABLESPACE UNTIL\u2026\u201d command which utilized:<\/p>\n<ul>\n<li>Backup of control file to restore the control file<\/li>\n<li>A snapshot taken while the database was in backup mode to restore the datafiles for the \u201cTEST&#8221;, &#8220;SYSTEM&#8221;, &#8220;UNDOTBS1&#8221;, and &#8220;SYSAUX\u201d tablespaces.<\/li>\n<\/ul>\n<h2>Conclusion:<\/h2>\n<ul>\n<li>Starting with Oracle Grid Infrastructure 12c (12.1), ACFS supports database files in cluster environment so that entire Oracle databases can be stored inside a cloud file system.<\/li>\n<li>ACFS snapshots taken while the database is in backup mode can be integrated with RMAN and employed to perform TSPITR using RMAN \u201cRECOVER TABLESPACE UNTIL&#8230;\u201d command.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Starting with Oracle Grid Infrastructure 12c (12.1), Oracle Cloud File system (ASM Cluster File System in cluster environment) supports database files (database version 11.2.0.4 and up) in addition to general-purpose files so that entire Oracle databases can be stored inside Oracle Cloud FS. In my earlier articles I demonstrated that: For a database having its files stored on Oracle Cloud&hellip;<\/p>\n","protected":false},"author":316217,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48347,48349,48350,48458,48530],"coauthors":[],"class_list":["post-73151","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-12c","tag-acfs","tag-acfs-snapshots","tag-oracle-cloud-file-system","tag-tspitr"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73151","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316217"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73151"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73151\/revisions"}],"predecessor-version":[{"id":91646,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73151\/revisions\/91646"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73151"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73151"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73151"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73151"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}