Back to basics: controlfile autobackup and recovery
Sometimes it’s not bad to look back to the basics. Due to circumstances I’m focusing more on backups nowadays. This time I wanted to have a closer look to controlfile autobackups. Oh by the way, for the one who follow me on twitter, yes, it’s this post which is related to this picture 🙂
Most of the tests I do, I do them at night (not this one, because I needed it for my customer) and yes, I write most of my drafts on a very relaxed place. This is almost a picture perfect evening, so sorry for my photography skills, but here we go.
Imagine following situation, let’s say a 65TB database, running in archivelog mode and taking a weekly full backup with daily incrementals which “normally succeed”. Then a release moment comes up and new tablespaces are to be added and, despite int and uat and other environments for quality testing, a problem arises and you got an e-mail “Dear Dba, During the process the wrong tablespace is dropped, can you please get it back?”.
First answer is … yes, it depends. Well that’s the imaginary situation I had in mind and the idea is to demonstrate a TSPITR (tablespace point in time recovery ) in a pluggable database without having to restore the full database and export import it. It is not too hard, but it is nothing you do every day, so I decided to document it.
Controlfile autobackup
While doing this tests, I ran into an issue. In Oracle 12c the controlfile autobackup is “ON” by default in rman.
1 2 3 4 |
RMAN configuration parameters for database with db_unique_name CDBS04N02 are: CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN> |
So we’re good with that. Starting from Oracle 11.2 (check the documentation here) the controlfile autobackup kicks off after several structural changes.
“Beginning with Oracle Database Release 11g Release 2, RMAN takes only one control file autobackup when multiple structural changes contained in a script (for example, adding tablespaces, altering the state of a tablespace or data file, adding a new online redo log, renaming a file, and so on) have been applied.”
So this leaves a place of doubt in my opinion. How many changes? How much time, it’s not documented. But … as always… there is a solution for this. There exists an underscore parameter “_controlfile_autobackup_delay” Which specifies a hard delay when Oracle should perform controlfile autobackups (if necessary).
I give the query to list the underscore parameters, you can be creative with it and get out of the db what you want know.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> ; 1 select * from x$ksppi 2 where substr(ksppinm,1,1) = '_' 3* and ksppinm = '_controlfile_autobackup_delay' SQL> / ADDR INDX INST_ID CON_ID KSPPINM KSPPITY KSPPDESC KSPPIFLG KSPPILRMFLG KSPPIHASH ---------------- ---------- ---------- ---------- ------------------------------ ---------- ---------------------------------------------------------------------- ---------- ----------- ---------- 00007F18DE384940 1790 1 1 _controlfile_autobackup_delay 3 time delay (in seconds) for performing controlfile autobackups 65536 0 2717531645 00007F18DE384940 5768 1 2 _controlfile_autobackup_delay 3 time delay (in seconds) for performing controlfile autobackups 65536 0 2717531645 00007F18DE384940 9746 1 3 _controlfile_autobackup_delay 3 time delay (in seconds) for performing controlfile autobackups 65536 0 2717531645 SQL> |
So anyhow, before 11.2 the database was doing controlfile autobackups at every change and that caused performance overhead, so now Oracle decided to bundle some operations (what’s the risk in a controlled environment anyway) and perform the backups less frequently. Which is, I think, a good thing. It’s only annoying for testing, so for testing I have put my delay to 0 which basically instructs the database to perform a controlfile autobackup when a structural change occurs. I’m not telling you to do this in production environments, but I’m raising the awareness that this parameter exists and if the default does not fit you, you might want to consider to change it. The default is 300 seconds and this is, according me, a good trade-off between performance and safety.
So to demonstrate the tests, I did this in my cdb$root. I set the auto backup to 0 (to speed things up for the tests a little) and switched to my pdb:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> alter system set "_controlfile_autobackup_delay"=0 scope=both; System altered. SQL> select name from v$pdbs; NAME ------------------------------ PDB$SEED S04N02PDB1 SQL> alter session set container=S04N02PDB1; Session altered. SQL> |
Recover a tablespace
The scenario is pretty simple:
- Create a tablespace
- create a table in the tablespace
- add a record in the table
- check it
- drop the tablespace
- get it back without restoring the full database
then we want to get the tablespace back without having to restore everything. The rest of the database remains running so that means that we will have to do an incomplete recovery of one tablespace. Cool thing is … rman does everything for you. So you don’t have to do “alter database create datafile … ” anymore, just tell rman “recover tablespace …” and he will create an auxiliary instance for you, restore only the bare necessities and perform the export/import for you. This without restoring the full 65TB. So, this is how it works:
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 |
SQL> create tablespace droptst datafile '/u01/app/oracle/oradata/cdbs04n02/pdb/droptst01.dbf' size 1m; Tablespace created. SQL> create table testtable(id number) tablespace droptst; Table created. SQL> insert into testtable(id) values(1); 1 row created. SQL> commit; Commit complete. SQL> select * from testtable; ID ---------- 1 SQL> select segment_name from dba_segments where tablespace_name='DROPTST'; SEGMENT_NAME ----------------------- TESTTABLE SQL> select timestamp_to_scn(sysdate) from dual; TIMESTAMP_TO_SCN(SYSDATE) ------------------------- 1819973 SQL> select file_id from dba_data_files where tablespace_name='DROPTST'; FILE_ID ---------- 14 SQL> select sysdate from dual; SYSDATE ------------------- 20/07/2017 14:52:47 SQL> drop tablespace droptst including contents and datafiles; Tablespace dropped. SQL> select * from v$tablespace where name='DROPTST'; no rows selected SQL> select * from testtable; select * from testtable * ERROR at line 1: ORA-00942: table or view does not exist SQL> |
you see I selected AND the scn AND the file_id AND the date/time. Basically to show how to gather the information on which you can to the recovery. The date/time is also available from the alertlog. It will tell you when the tablespace has been dropped. Basically that will be the most realistic situation and I’ll demonstrate also how to recover it from to a given timestamp.
VERY IMPORTANT. And this is what has bitten me in my own lab. I know, blame me… but rman/oracle cannot recover things it doesn’t know. That’s also why I wrote the first part about the auto backup. This is where all pieces start to fit together. If you don’t have an autobackup from you controlfile, the tablespace/datafile information is not included and Oracle/rman doesn’t know so no recovery possible. So make sure your controlfile backups are working properly. That is also why I spend a bit time to search for the underscore parameter, to facilitate my tests. Remember … before 11.2 changes were recorded automatically. Starting from 11.2 … changes are grouped before autobackup occurs. So pay attention.
Recovery
So it’s time to get our data back isn’t it? I always use my recovery catalog for doing backups and recovery and this is one of the cases (without a resync) it’s not going to work. The information is only available in the controlfile and not yet in the catalog, therefor i connected to rman just very easy as
1 |
# rman target / |
then the recovery is actually a oneliner:
1 |
RMAN> recover tablespace s04n02pdb1:droptst until time "to_date('20/07/2017 14:52:47','dd/mm/yyyy hh24:mi:ss')" auxiliary destination '/u01/app/oracle/oradata'; |
Here we tell rman to recover a tablespace in pdb s04n02pdb1 which has tablespace name “droptst”. We would like rman to recover it until a certain timestamp (which we retrieved earlier, alert log/query,… remember?) and to do so, rman will need an auxiliary instance, so we need to tell rman where he can put it. That’s pretty it. Here is the log it creates:
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 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 |
RMAN> recover tablespace s04n02pdb1:droptst until time "to_date('20/07/2017 14:52:47','dd/mm/yyyy hh24:mi:ss')" auxiliary destination '/u01/app/oracle/oradata'; Starting recover at 20/07/2017 14:55:00 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=248 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=33 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=35 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=279 device type=DISK 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='aaFD' initialization parameters used for automatic instance: db_name=CDBS04N0 db_unique_name=aaFD_pitr_s04n02pdb1_CDBS04N0 compatible=12.1.0.2.0 db_block_size=8192 db_files=200 diagnostic_dest=/u01/app/oracle _system_trig_enabled=FALSE db_domain=labo.internal.stepi.net sga_target=1504M processes=200 db_create_file_dest=/u01/app/oracle/oradata log_archive_dest_1='location=/u01/app/oracle/oradata' enable_pluggable_database=true _clone_one_pdb_recovery=true #No auxiliary parameter file used starting up automatic instance CDBS04N0 Oracle instance started Total System Global Area 1577058304 bytes Fixed Size 2924832 bytes Variable Size 402656992 bytes Database Buffers 1157627904 bytes Redo Buffers 13848576 bytes Automatic instance created List of tablespaces that have been dropped from the target database: Tablespace S04N02PDB1:DROPTST contents of Memory Script: { # set requested point in time set until time "to_date('20/07/2017 14:52:47','dd/mm/yyyy hh24:mi:ss')"; # restore the controlfile restore clone controlfile; # mount the controlfile sql clone 'alter database mount clone database'; # archive current online log sql 'alter system archive log current'; # avoid unnecessary autobackups for structural changes during TSPITR sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;'; } executing Memory Script executing command: SET until clause Starting restore at 20/07/2017 14:55:32 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=12 device type=DISK allocated channel: ORA_AUX_DISK_2 channel ORA_AUX_DISK_2: SID=173 device type=DISK allocated channel: ORA_AUX_DISK_3 channel ORA_AUX_DISK_3: SID=13 device type=DISK allocated channel: ORA_AUX_DISK_4 channel ORA_AUX_DISK_4: SID=175 device type=DISK channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/orabackup/c-3397181599-20170720-06 channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/orabackup/c-3397181599-20170720-06 tag=TAG20170720T145214 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/CDBS04N02/controlfile/o1_mf_dq1b26nh_.ctl Finished restore at 20/07/2017 14:55:35 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 time "to_date('20/07/2017 14:52:47','dd/mm/yyyy hh24:mi:ss')"; # set destinations for recovery set and auxiliary set datafiles set newname for clone datafile 1 to new; set newname for clone datafile 4 to new; set newname for clone datafile 3 to new; set newname for clone datafile 8 to new; set newname for clone datafile 9 to new; set newname for clone tempfile 1 to new; set newname for clone tempfile 3 to new; set newname for datafile 14 to "/u01/app/oracle/oradata/cdbs04n02/pdb/droptst01.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, 8, 9, 14; switch clone datafile all; } executing Memory Script executing command: SET until clause executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_temp_%u_.tmp in control file renamed tempfile 3 to /u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_temp_%u_.tmp in control file Starting restore at 20/07/2017 14:55:41 using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2 using channel ORA_AUX_DISK_3 using channel ORA_AUX_DISK_4 creating datafile file number=14 name=/u01/app/oracle/oradata/cdbs04n02/pdb/droptst01.dbf channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_sysaux_%u_.dbf channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/orabackup/cdbs04n02/ora_df949848271_s25_s1 channel ORA_AUX_DISK_2: starting datafile backup set restore channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_2: restoring datafile 00003 to /u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_sysaux_%u_.dbf channel ORA_AUX_DISK_2: reading from backup piece /u01/app/oracle/orabackup/cdbs04n02/ora_df949848271_s24_s1 channel ORA_AUX_DISK_3: starting datafile backup set restore channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_3: restoring datafile 00004 to /u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_undotbs1_%u_.dbf channel ORA_AUX_DISK_3: reading from backup piece /u01/app/oracle/orabackup/cdbs04n02/ora_df949848334_s27_s1 channel ORA_AUX_DISK_4: starting datafile backup set restore channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_4: restoring datafile 00008 to /u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_4: reading from backup piece /u01/app/oracle/orabackup/cdbs04n02/ora_df949848341_s28_s1 channel ORA_AUX_DISK_3: piece handle=/u01/app/oracle/orabackup/cdbs04n02/ora_df949848334_s27_s1 tag=TAG20170720T144429 channel ORA_AUX_DISK_3: restored backup piece 1 channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:45 channel ORA_AUX_DISK_3: starting datafile backup set restore channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_3: restoring datafile 00001 to /u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_3: reading from backup piece /u01/app/oracle/orabackup/cdbs04n02/ora_df949848271_s23_s1 channel ORA_AUX_DISK_4: piece handle=/u01/app/oracle/orabackup/cdbs04n02/ora_df949848341_s28_s1 tag=TAG20170720T144429 channel ORA_AUX_DISK_4: restored backup piece 1 channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:01:00 channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/orabackup/cdbs04n02/ora_df949848271_s25_s1 tag=TAG20170720T144429 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/orabackup/cdbs04n02/ora_df949848271_s25_s2 channel ORA_AUX_DISK_2: piece handle=/u01/app/oracle/orabackup/cdbs04n02/ora_df949848271_s24_s1 tag=TAG20170720T144429 channel ORA_AUX_DISK_2: restored backup piece 1 channel ORA_AUX_DISK_2: reading from backup piece /u01/app/oracle/orabackup/cdbs04n02/ora_df949848271_s24_s2 channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/orabackup/cdbs04n02/ora_df949848271_s25_s2 tag=TAG20170720T144429 channel ORA_AUX_DISK_1: restored backup piece 2 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:39 channel ORA_AUX_DISK_2: piece handle=/u01/app/oracle/orabackup/cdbs04n02/ora_df949848271_s24_s2 tag=TAG20170720T144429 channel ORA_AUX_DISK_2: restored backup piece 2 channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:49 channel ORA_AUX_DISK_3: piece handle=/u01/app/oracle/orabackup/cdbs04n02/ora_df949848271_s23_s1 tag=TAG20170720T144429 channel ORA_AUX_DISK_3: restored backup piece 1 channel ORA_AUX_DISK_3: reading from backup piece /u01/app/oracle/orabackup/cdbs04n02/ora_df949848271_s23_s2 channel ORA_AUX_DISK_3: piece handle=/u01/app/oracle/orabackup/cdbs04n02/ora_df949848271_s23_s2 tag=TAG20170720T144429 channel ORA_AUX_DISK_3: restored backup piece 2 channel ORA_AUX_DISK_3: reading from backup piece /u01/app/oracle/orabackup/cdbs04n02/ora_df949848271_s23_s3 channel ORA_AUX_DISK_3: piece handle=/u01/app/oracle/orabackup/cdbs04n02/ora_df949848271_s23_s3 tag=TAG20170720T144429 channel ORA_AUX_DISK_3: restored backup piece 3 channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:01:54 Finished restore at 20/07/2017 14:58:21 datafile 1 switched to datafile copy input datafile copy RECID=12 STAMP=949849101 file name=/u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_system_dq1b3vvr_.dbf datafile 4 switched to datafile copy input datafile copy RECID=13 STAMP=949849101 file name=/u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_undotbs1_dq1b2go5_.dbf datafile 3 switched to datafile copy input datafile copy RECID=14 STAMP=949849101 file name=/u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_sysaux_dq1b2gns_.dbf datafile 8 switched to datafile copy input datafile copy RECID=15 STAMP=949849101 file name=/u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_system_dq1b2gol_.dbf datafile 9 switched to datafile copy input datafile copy RECID=16 STAMP=949849101 file name=/u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_sysaux_dq1b2gnd_.dbf contents of Memory Script: { # set requested point in time set until time "to_date('20/07/2017 14:52:47','dd/mm/yyyy hh24:mi:ss')"; # 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 'S04N02PDB1' "alter database datafile 8 online"; sql clone 'S04N02PDB1' "alter database datafile 9 online"; sql clone 'S04N02PDB1' "alter database datafile 14 online"; # recover and open resetlogs recover clone database tablespace "S04N02PDB1":"DROPTST", "SYSTEM", "UNDOTBS1", "SYSAUX", "S04N02PDB1":"SYSTEM", "S04N02PDB1":"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 8 online sql statement: alter database datafile 9 online sql statement: alter database datafile 14 online Starting recover at 20/07/2017 14:58:22 using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2 using channel ORA_AUX_DISK_3 using channel ORA_AUX_DISK_4 starting media recovery archived log for thread 1 with sequence 28 is already on disk as file /u01/app/oracle/fast_recovery_area/CDBS04N02/archivelog/2017_07_20/o1_mf_1_28_dq19jjjc_.arc archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/fast_recovery_area/CDBS04N02/archivelog/2017_07_20/o1_mf_1_29_dq19yyd8_.arc archived log file name=/u01/app/oracle/fast_recovery_area/CDBS04N02/archivelog/2017_07_20/o1_mf_1_28_dq19jjjc_.arc thread=1 sequence=28 archived log file name=/u01/app/oracle/fast_recovery_area/CDBS04N02/archivelog/2017_07_20/o1_mf_1_29_dq19yyd8_.arc thread=1 sequence=29 media recovery complete, elapsed time: 00:00:01 Finished recover at 20/07/2017 14:58:26 database opened contents of Memory Script: { sql clone 'alter pluggable database S04N02PDB1 open'; } executing Memory Script sql statement: alter pluggable database S04N02PDB1 open contents of Memory Script: { # make read only the tablespace that will be exported sql clone 'S04N02PDB1' 'alter tablespace DROPTST read only'; # create directory for datapump import sql 'S04N02PDB1' "create or replace directory TSPITR_DIROBJ_DPDIR as '' /u01/app/oracle/oradata''"; # create directory for datapump export sql clone 'S04N02PDB1' "create or replace directory TSPITR_DIROBJ_DPDIR as '' /u01/app/oracle/oradata''"; } executing Memory Script sql statement: alter tablespace DROPTST read only sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata'' sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata'' Performing export of metadata... EXPDP> Starting "SYS"."TSPITR_EXP_aaFD_xBEv": 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_aaFD_xBEv" successfully loaded/unloaded EXPDP> ****************************************************************************** EXPDP> Dump file set for SYS.TSPITR_EXP_aaFD_xBEv is: EXPDP> /u01/app/oracle/oradata/tspitr_aaFD_16644.dmp EXPDP> ****************************************************************************** EXPDP> Datafiles required for transportable tablespace DROPTST: EXPDP> /u01/app/oracle/oradata/cdbs04n02/pdb/droptst01.dbf EXPDP> Job "SYS"."TSPITR_EXP_aaFD_xBEv" successfully completed at Thu Jul 20 15:01:11 2017 elapsed 0 00:01:56 Export completed contents of Memory Script: { # shutdown clone before import shutdown clone abort } executing Memory Script Oracle instance shut down Performing import of metadata... IMPDP> Master table "SYS"."TSPITR_IMP_aaFD_xlzr" successfully loaded/unloaded IMPDP> Starting "SYS"."TSPITR_IMP_aaFD_xlzr": 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_aaFD_xlzr" successfully completed at Thu Jul 20 15:01:41 2017 elapsed 0 00:00:19 Import completed contents of Memory Script: { # make read write and offline the imported tablespaces sql 'S04N02PDB1' 'alter tablespace DROPTST read write'; sql 'S04N02PDB1' 'alter tablespace DROPTST offline'; # enable autobackups after TSPITR is finished sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;'; } executing Memory Script sql statement: alter tablespace DROPTST read write sql statement: alter tablespace DROPTST offline sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end; Removing automatic instance Automatic instance removed auxiliary instance file /u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_temp_dq1b7wcr_.tmp deleted auxiliary instance file /u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_temp_dq1b7rsd_.tmp deleted auxiliary instance file /u01/app/oracle/oradata/CDBS04N02/onlinelog/o1_mf_3_dq1b7obg_.log deleted auxiliary instance file /u01/app/oracle/oradata/CDBS04N02/onlinelog/o1_mf_2_dq1b7m91_.log deleted auxiliary instance file /u01/app/oracle/oradata/CDBS04N02/onlinelog/o1_mf_1_dq1b7lqt_.log deleted auxiliary instance file /u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_sysaux_dq1b2gnd_.dbf deleted auxiliary instance file /u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_system_dq1b2gol_.dbf deleted auxiliary instance file /u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_sysaux_dq1b2gns_.dbf deleted auxiliary instance file /u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_undotbs1_dq1b2go5_.dbf deleted auxiliary instance file /u01/app/oracle/oradata/CDBS04N02/datafile/o1_mf_system_dq1b3vvr_.dbf deleted auxiliary instance file /u01/app/oracle/oradata/CDBS04N02/controlfile/o1_mf_dq1b26nh_.ctl deleted auxiliary instance file tspitr_aaFD_16644.dmp deleted Finished recover at 20/07/2017 15:01:46 RMAN> |
You see that he crates an auxiliary instance, only containing the data it needs. And thus not the full database, which is the behavior we want. When the auxiliary instance has been setup and recovered, rman will launch datapump to get the data back in the database. Let this be exactly what we want 🙂
So, depending on the size, this will take a while. But when it finishes the tablespace is back in the pdb. Except that is offline. As long as you know that, it’s not a big deal. Just put it online (if you’re sure this is the data you want).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SQL> select * from v$tablespace where name='DROPTST'; TS# NAME INC BIG FLA ENC CON_ID ---------- ------------------------------ --- --- --- --- ---------- 5 DROPTST YES NO YES 3 SQL> select segment_name from dba_segments where tablespace_name='DROPTST'; SEGMENT_NAME -------------------------------------------------------------------------------------------------------------------------------- TESTTABLE SQL> alter tablespace DROPTST online; Tablespace altered. SQL> SQL> select * from testtable; ID ---------- 1 SQL> |
So this is how you get your data back very easily.
Only take into account that, if you want to do this, you will need a good backup of the controlfile, which autobackup provides to you.
So that’s all for now folks.
As always, questions, remarks? find me on twitter @vanpupi