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 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.
- 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.
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.
Currently I 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
[oracle@host01 ~]$ srvctl config database -d cfsdb Database unique name: cfsdb Database name: cfsdb Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1 Oracle user: oracle Spfile: <span style="color: red;"><strong>/mnt/acfs/oradata/cfsdb/spfilecfsdb.ora</strong></span> Password file: <span style="color: red;"><strong>/mnt/acfs/oradata/cfsdb/orapwcfsdb</strong></span> Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: Disk Groups: Mount point paths: <span style="color: red;"><strong>/mnt/acfs</strong></span> Services: Type: RAC Start concurrency: Stop concurrency: OSDBA group: dba OSOPER group: oper Database instances: cfsdb1 Configured nodes: host01 Database is administrator managed CFSDB > select name from v$controlfile; NAME ---------------------------------------- <span style="color: red;"><strong>/mnt/acfs</strong></span>/oradata/cfsdb/control01.ctl <span style="color: red;"><strong>/mnt/acfs</strong></span>/cfsdb/control02.ctl CFSDB > select member from v$logfile; MEMBER ---------------------------------------- <span style="color: red;"><strong>/mnt/acfs</strong></span>/oradata/cfsdb/redo03.log <span style="color: red;"><strong>/mnt/acfs</strong></span>/oradata/cfsdb/redo02.log <span style="color: red;"><strong>/mnt/acfs</strong></span>/oradata/cfsdb/redo01.log CFSDB > select name from v$archived_log; NAME ----------------------------------------------------------------- <span style="color: red;"><strong>/mnt/acfs</strong></span>/CFSDB/archivelog/2015_08_05/o1_mf_1_8_bw3nhvkn_.arc CFSDB > select name from v$datafile; NAME ----------------------------------------------------------------- <span style="color: red;"><strong>/mnt/acfs</strong></span>/oradata/cfsdb/system01.dbf <span style="color: red;"><strong>/mnt/acfs</strong></span>/oradata/cfsdb/sysaux01.dbf <span style="color: red;"><strong>/mnt/acfs</strong></span>/oradata/cfsdb/undotbs01.dbf <span style="color: red;"><strong>/mnt/acfs</strong></span>/oradata/cfsdb/example01.dbf <span style="color: red;"><strong>/mnt/acfs</strong></span>/oradata/cfsdb/users01.dbf CFSDB>sho parameter db_recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string <span style="color: red;"><strong>/mnt/acfs</strong></span> db_recovery_file_dest_size big integer 4560M |
Let’s confirm that the database cfsdb is in archivelog mode:
|
1 2 3 4 5 6 7 8 |
CFSDB > archive log list; Database log mode <span style="color: red;"><strong>Archive Mode</strong></span> Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8 |
Create a new tablespace named TEST with its data file stored on Oracle Cloud file system:
|
1 |
SQL>create tablespace test datafile '<span style="color: red;"><strong>/mnt/acfs</strong></span>/oradata/cfsdb/test01.dbf' size 50m; |
Create a table named HR.EMP with 107 rows in the TEST tablespace:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> create table hr.emp tablespace test as select * from hr.employees; SQL> select owner, table_name, tablespace_name from dba_tables where owner = 'HR' and table_name = 'EMP'; OWNER TABLE_NAME TABLESPACE_NAME ---------- --------------- ------------------------------ HR EMP <span style="color: red;"><strong>TEST</strong></span> SQL> select count(*) from hr.emp; COUNT(*) ---------- <span style="color: red;"><strong>107</strong></span> |
Take a backup of the control file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
RMAN> delete backup of controlfile; backup current controlfile format '/home/oracle/%U'; Starting backup at 06-AUG-15 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 06-AUG-15 channel ORA_DISK_1: finished piece 1 at 06-AUG-15 <span style="color: red;"><strong>piece handle=/home/oracle/03qdtpdo_1_1 tag=TAG20150806T111711 comment=NONE</strong></span> channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 06-AUG-15 |
Verify that currently there are no snapshots of the cloud file system hosting the database files:
|
1 2 3 4 |
[root@host01 oracle]# acfsutil snap info /mnt/acfs number of snapshots: <span style="color: red;"><strong>0</strong></span> snapshot space usage: 0 ( 0.00 ) |
Take a snapshot (example_snap) of the cloud file system while the database is in backup mode:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SQL> alter database begin backup; Database altered. [root@host01 acfs]# acfsutil snap create example_snap /mnt/acfs acfsutil snap create: Snapshot operation is complete. [root@host01 acfs]# acfsutil snap info /mnt/acfs snapshot name: <span style="color: red;"><strong>example_snap</strong></span> snapshot location: <span style="color: red;"><strong>/mnt/acfs/.ACFS/snaps/example_snap</strong></span> RO snapshot or RW snapshot: RO parent name: /mnt/acfs snapshot creation time: Thu Aug 6 11:18:25 2015 number of snapshots: 1 snapshot space usage: 17858560 ( 17.03 MB ) SQL> alter database end backup; Database altered. |
Check that all the datafiles are available in the snapshot:
|
1 2 3 4 5 6 7 8 9 |
[root@host01 acfs]# ls -l <span style="color: red;"><strong>/mnt/acfs</strong></span>/.ACFS/snaps/example_snap/oradata/cfsdb/*.dbf -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> -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> -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> -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> -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> -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> -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> |
Catalog all the data files in the snapshot example_snap in directory “/mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/“:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
RMAN> catalog start with '/mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb'; searching for all files that match the pattern /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb List of Files Unknown to the Database ===================================== File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/orapwcfsdb File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/sysaux01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/system01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/users01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/undotbs01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/control01.ctl File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/redo01.log File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/redo02.log File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/redo03.log File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/temp01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/example01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/spfilecfsdb.ora File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/test01.dbf Do you really want to catalog the above files (enter YES or NO)? <strong>yes</strong> cataloging files... cataloging done <span style="color: red;"><strong>List of Cataloged Files</strong></span> <span style="color: red;"><strong>=======================</strong></span> <span style="color: red;"><strong>File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/sysaux01.dbf</strong></span> <span style="color: red;"><strong>File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/system01.dbf</strong></span> <span style="color: red;"><strong>File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/users01.dbf</strong></span> <span style="color: red;"><strong>File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/undotbs01.dbf</strong></span> <span style="color: red;"><strong>File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/temp01.dbf</strong></span> <span style="color: red;"><strong>File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/example01.dbf</strong></span> <span style="color: red;"><strong>File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/test01.dbf</strong></span> List of Files Which Were Not Cataloged ======================================= File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/orapwcfsdb RMAN-07518: Reason: Foreign database file DBID: 0 Database Name: File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/control01.ctl RMAN-07519: Reason: Error while cataloging. See alert.log. File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/redo01.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/redo02.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/redo03.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/spfilecfsdb.ora RMAN-07518: Reason: Foreign database file DBID: 0 Database Name: |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
RMAN> list copy of database; List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - --------------- ---------- --------------- 14 1 A 06-AUG-15 1857785 06-AUG-15 Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/system01.dbf 18 2 A 06-AUG-15 1857785 06-AUG-15 Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/test01.dbf 13 3 A 06-AUG-15 1857785 06-AUG-15 Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/sysaux01.dbf 16 4 A 06-AUG-15 1857785 06-AUG-15 Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/undotbs01.dbf 17 5 A 06-AUG-15 1857785 06-AUG-15 Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/example01.dbf 15 6 A 06-AUG-15 1857785 06-AUG-15 Name: /mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/users01.dbf |
Check the current table has 107 rows:
|
1 2 3 4 5 |
CFSDB> select count(*) from hr.emp; COUNT(*) ---------- <span style="color: red;"><strong>107</strong></span> |
Create a restore point EMP_107:
|
1 2 3 4 5 6 7 8 9 |
CFSDB>create restore point EMP_107; Restore point created. CFSDB> select scn, name from v$restore_point SCN NAME ---------- ------------------------------ 1858125 <span style="color: red;"><strong>EMP_107</strong></span> |
Insert records into HR.EMP so that there are now 214 records:
|
1 2 3 4 5 6 7 8 9 10 11 |
CFSDB>insert into hr.emp select * from hr.emp; commit; Commit complete. CFSDB>select count(*) from hr.emp; COUNT(*) ---------- <span style="color: red;"><strong>214</strong></span> |
Perform TSPITR for TEST tablespace until restore point EMP_107 when there were 107 records in HR.EMP:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 |
SQL> alter tablespace test offline; Tablespace altered. RMAN> recover tablespace "TEST" until restore point emp_107 auxiliary destination '/home/oracle'; Starting recover at 06-AUG-15 current log archived using channel ORA_DISK_1 RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time List of tablespaces expected to have UNDO segments Tablespace SYSTEM Tablespace UNDOTBS1 Creating automatic instance, with SID='neph' initialization parameters used for automatic instance: db_name=CFSDB db_unique_name=neph_pitr_CFSDB compatible=12.1.0.2.0 db_block_size=8192 db_files=200 diagnostic_dest=/u01/app/oracle _system_trig_enabled=FALSE sga_target=412M processes=200 db_create_file_dest=/home/oracle log_archive_dest_1='location=/home/oracle' #No auxiliary parameter file used starting up automatic instance CFSDB Oracle instance started Total System Global Area 432013312 bytes Fixed Size 2925264 bytes Variable Size 155192624 bytes Database Buffers 268435456 bytes Redo Buffers 5459968 bytes Automatic instance created Running TRANSPORT_SET_CHECK on recovery set tablespaces TRANSPORT_SET_CHECK completed successfully contents of Memory Script: { # set requested point in time set until scn 1858126; # 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;'; } executing Memory Script executing command: SET until clause Starting restore at 06-AUG-15 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=29 device type=DISK channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: <span style="color: red;"><strong>restoring control file</strong></span> <span style="color: red;"><strong>channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/03qdtpdo_1_1</strong></span> <span style="color: red;"><strong>channel ORA_AUX_DISK_1: piece handle=/home/oracle/03qdtpdo_1_1 tag=TAG20150806T111711</strong></span> channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04 output file name=/home/oracle/CFSDB/controlfile/o1_mf_bw5xshb5_.ctl Finished restore at 06-AUG-15 sql statement: alter database mount clone database sql statement: alter system archive log current sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end; contents of Memory Script: { # set requested point in time set until scn 1858126; # set destinations for recovery set and auxiliary set datafiles set newname for clone datafile 1 to new; set newname for clone datafile 4 to new; set newname for clone datafile 3 to new; set newname for clone tempfile 1 to new; set newname for datafile 2 to "/mnt/acfs/oradata/cfsdb/test01.dbf"; # switch all tempfiles switch clone tempfile all; # restore the tablespaces in the recovery set and the auxiliary set restore clone datafile 1, 4, 3, 2; 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 renamed tempfile 1 to /home/oracle/CFSDB/datafile/o1_mf_temp_%u_.tmp in control file <span style="color: red;"><strong>Starting restore</strong></span> at 06-AUG-15 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: <span style="color: red;"><strong>restoring datafile 00001</strong></span> <span style="color: red;"><strong>input datafile copy RECID=13 STAMP=887023637 file</strong></span> <span style="color: red;"><strong>name=/mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/system01.dbf</strong></span> destination for restore of datafile 00001: /home/oracle/CFSDB/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_1: copied datafile copy of datafile 00001 output file name=/home/oracle/CFSDB/datafile/o1_mf_system_bw5xt0b5_.dbf RECID=17 STAMP=887023777 channel ORA_AUX_DISK_1: <span style="color: red;"><strong>restoring datafile 00004</strong></span> <span style="color: red;"><strong>input datafile copy RECID=14 STAMP=887023638 file</strong></span> <span style="color: red;"><strong>name=/mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/undotbs01.dbf</strong></span> destination for restore of datafile 00004: /home/oracle/CFSDB/datafile/o1_mf_undotbs1_%u_.dbf channel ORA_AUX_DISK_1: copied datafile copy of datafile 00004 output file name=/home/oracle/CFSDB/datafile/o1_mf_undotbs1_bw5xymgo_.dbf RECID=18 STAMP=887023811 channel ORA_AUX_DISK_1: <span style="color: red;"><strong>restoring datafile 00003</strong></span> <span style="color: red;"><strong>input datafile copy RECID=15 STAMP=887023639 file</strong></span> <span style="color: red;"><strong>name=/mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/sysaux01.dbf</strong></span> destination for restore of datafile 00003: /home/oracle/CFSDB/datafile/o1_mf_sysaux_%u_.dbf channel ORA_AUX_DISK_1: copied datafile copy of datafile 00003 output file name=/home/oracle/CFSDB/datafile/o1_mf_sysaux_bw5xzd5n_.dbf RECID=19 STAMP=887023962 channel ORA_AUX_DISK_1: <span style="color: red;"><strong>restoring datafile 00002</strong></span> <span style="color: red;"><strong>input datafile copy RECID=16 STAMP=887023640 file</strong></span> <span style="color: red;"><strong>name=/mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/test01.dbf</strong></span> destination for restore of datafile 00002: /mnt/acfs/oradata/cfsdb/test01.dbf channel ORA_AUX_DISK_1: copied datafile copy of datafile 00002 output file name=/mnt/acfs/oradata/cfsdb/test01.dbf RECID=0 STAMP=0 Finished restore at 06-AUG-15 datafile 1 switched to datafile copy input datafile copy RECID=20 STAMP=887023995 file name=/home/oracle/CFSDB/datafile/o1_mf_system_bw5xt0b5_.dbf datafile 4 switched to datafile copy input datafile copy RECID=21 STAMP=887023995 file name=/home/oracle/CFSDB/datafile/o1_mf_undotbs1_bw5xymgo_.dbf datafile 3 switched to datafile copy input datafile copy RECID=22 STAMP=887023995 file name=/home/oracle/CFSDB/datafile/o1_mf_sysaux_bw5xzd5n_.dbf contents of Memory Script: { # set requested point in time set until scn 1858126; # online the datafiles restored or switched sql clone "alter database datafile 1 online"; sql clone "alter database datafile 4 online"; sql clone "alter database datafile 3 online"; sql clone "alter database datafile 2 online"; # recover and open resetlogs recover clone database tablespace "TEST", "SYSTEM", "UNDOTBS1", "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 4 online sql statement: alter database datafile 3 online sql statement: alter database datafile 2 online Starting recover at 06-AUG-15 using channel ORA_AUX_DISK_1 starting media recovery archived log for thread 1 with sequence 13 is already on disk as file /mnt/acfs/CFSDB/archivelog/2015_08_06/o1_mf_1_13_bw5xn23v_.arc archived log file name=/mnt/acfs/CFSDB/archivelog/2015_08_06/o1_mf_1_13_bw5xn23v_.arc thread=1 sequence=13 media recovery complete, elapsed time: 00:00:03 Finished recover at 06-AUG-15 database opened contents of Memory Script: { # make read only the tablespace that will be exported sql clone 'alter tablespace "TEST" read only'; # create directory for datapump import sql "create or replace directory TSPITR_DIROBJ_DPDIR as '' /home/oracle''"; # create directory for datapump export sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as '' /home/oracle''"; } executing Memory Script sql statement: alter tablespace "TEST" read only sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle'' sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle'' Performing export of metadata... EXPDP> Starting "SYS"."TSPITR_EXP_neph_bFwg": EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK EXPDP> Master table "SYS"."TSPITR_EXP_neph_bFwg" successfully loaded/unloaded EXPDP> ****************************************************************************** EXPDP> Dump file set for SYS.TSPITR_EXP_neph_bFwg is: EXPDP> /home/oracle/tspitr_neph_53179.dmp EXPDP> ****************************************************************************** EXPDP> Datafiles required for transportable tablespace TEST: EXPDP> /mnt/acfs/oradata/cfsdb/test01.dbf EXPDP> Job "SYS"."TSPITR_EXP_neph_bFwg" successfully completed at Thu Aug 6 11:45:11 2015 elapsed 0 00:09:37 Export completed contents of Memory Script: { # shutdown clone before import shutdown clone abort # drop target tablespaces before importing them back sql 'drop tablespace "TEST" including contents keep datafiles cascade constraints'; } executing Memory Script Oracle instance shut down sql statement: drop tablespace "TEST" including contents keep datafiles cascade constraints Performing import of metadata... IMPDP> Master table "SYS"."TSPITR_IMP_neph_drcx" successfully loaded/unloaded IMPDP> Starting "SYS"."TSPITR_IMP_neph_drcx": IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK IMPDP> Job "SYS"."TSPITR_IMP_neph_drcx" successfully completed at Thu Aug 6 11:47:47 2015 elapsed 0 00:01:11 Import completed contents of Memory Script: { # make read write and offline the imported tablespaces sql 'alter tablespace "TEST" read write'; sql 'alter tablespace "TEST" offline'; # enable autobackups after TSPITR is finished sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;'; } executing Memory Script sql statement: alter tablespace "TEST" read write sql statement: alter tablespace "TEST" offline sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end; Removing automatic instance Automatic instance removed auxiliary instance file /home/oracle/CFSDB/datafile/o1_mf_temp_bw5y6b5p_.tmp deleted auxiliary instance file /home/oracle/CFSDB/onlinelog/o1_mf_3_bw5y5q6k_.log deleted auxiliary instance file /home/oracle/CFSDB/onlinelog/o1_mf_2_bw5y5lho_.log deleted auxiliary instance file /home/oracle/CFSDB/onlinelog/o1_mf_1_bw5y5jo5_.log deleted auxiliary instance file /home/oracle/CFSDB/datafile/o1_mf_sysaux_bw5xzd5n_.dbf deleted auxiliary instance file /home/oracle/CFSDB/datafile/o1_mf_undotbs1_bw5xymgo_.dbf deleted auxiliary instance file /home/oracle/CFSDB/datafile/o1_mf_system_bw5xt0b5_.dbf deleted auxiliary instance file /home/oracle/CFSDB/controlfile/o1_mf_bw5xshb5_.ctl deleted auxiliary instance file tspitr_neph_53179.dmp deleted Finished recover at 06-AUG-15 CFSDB> alter tablespace test online; Tablespace altered. |
Verify that TSPITR to restore point EMP_107 is successful and that there are 107 records in HR.EMP now:
|
1 2 3 4 5 |
CFSDB> select count(*) from hr.emp; COUNT(*) ---------- <span style="color: red;"><strong>107</strong></span> |
Thus, we have been able to perform TSPITR by employing the RMAN “RECOVER TABLESPACE UNTIL…” command which utilized:
- Backup of control file to restore the control file
- A snapshot taken while the database was in backup mode to restore the datafiles for the “TEST”, “SYSTEM”, “UNDOTBS1”, and “SYSAUX” tablespaces.
Conclusion:
- 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.
- ACFS snapshots taken while the database is in backup mode can be integrated with RMAN and employed to perform TSPITR using RMAN “RECOVER TABLESPACE UNTIL…” command.
Load comments