12c中rman恢复表和分区

在12c以前的版本中,如果想恢复单表到某个时间点之前是一件非常麻烦的事情,一般方法可能只能通过把全库备份基于某个时间点恢复到别处,然后再从新库当中把数据导出来插入回原库中,如果源库很大的话整个时间可想而知。而在12c新的rman特性中,提供了一个非常棒的功能,可以直接通过简单的命令就能恢复单表或者分区到某个时间点,还可以直接恢复到其他的schema下面。

如果想通过rman备份当中恢复出表或者分区时,主要是做了以下步骤:

  • 根据recover指定的目标时间,来判断哪些备份包含需要恢复的表或者分区
  • 判断目标主机上是否含有足够的空间来创建辅助实例,用于恢复过程使用。如果空间不足,rman进程会报错然后退出recover
  • 在目标主机上创建辅助实例,基于指定的恢复时间点,恢复指定的表或分区到辅助实例当中
  • 创建包含需要恢复的表或分区的数据泵导出文件,可以指定文件的名称和路径,主要用于存放表和分区的元数据信息
  • (可选)将数据泵文件导入目标库,这个操作你可以之后手动操作
  • (可选)可以将恢复的表或分区导入新库当中的不同用户和不同表空间下

查看当前的数据文件和备份

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
[oracle@xb ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Mar 26 13:14:19 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORA12C (DBID=380590835)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORA12C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 800 SYSTEM YES /u01/app/oracle/oradata/ora12c/system01.dbf
3 500 SYSAUX NO /u01/app/oracle/oradata/ora12c/sysaux01.dbf
4 70 UNDOTBS1 YES /u01/app/oracle/oradata/ora12c/undotbs01.dbf
5 250 PDB$SEED:SYSTEM NO /u01/app/oracle/oradata/ora12c/pdbseed/system01.dbf
6 330 PDB$SEED:SYSAUX NO /u01/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf
7 5 USERS NO /u01/app/oracle/oradata/ora12c/users01.dbf
8 100 PDB$SEED:UNDOTBS1 NO /u01/app/oracle/oradata/ora12c/pdbseed/undotbs01.dbf
9 250 PDB12C:SYSTEM YES /u01/app/oracle/oradata/ora12c/pdb12c/system01.dbf
10 360 PDB12C:SYSAUX NO /u01/app/oracle/oradata/ora12c/pdb12c/sysaux01.dbf
11 100 PDB12C:UNDOTBS1 YES /u01/app/oracle/oradata/ora12c/pdb12c/undotbs01.dbf
12 5 PDB12C:USERS NO /u01/app/oracle/oradata/ora12c/pdb12c/users01.dbf
13 70 PDB12C:TBS1 NO /u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_g8kd5pwo_.dbf
15 10 PDB12C:TBS2 NO /u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs2_g8pl709m_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 131 TEMP 32767 /u01/app/oracle/oradata/ora12c/temp01.dbf
2 64 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ora12c/pdbseed/temp012019-03-13_09-14-08-192-AM.dbf
3 130 PDB12C:TEMP 32767 /u01/app/oracle/oradata/ora12c/pdb12c/temp01.dbf

RMAN> list backup;

specification does not match any backup in the repository




-- 当前并没有可用备份,对CDB和它的PDBs做一个全备

RMAN> backup database plus archivelog;


Starting backup at 26-MAR-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=1002984384
input archived log thread=1 sequence=4 RECID=2 STAMP=1002984802
input archived log thread=1 sequence=5 RECID=3 STAMP=1003929462
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/02ttdfrm_1_1 tag=TAG20190326T131742 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 26-MAR-19

Starting backup at 26-MAR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ora12c/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ora12c/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ora12c/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ora12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1 tag=TAG20190326T131745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/ora12c/pdb12c/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/ora12c/pdb12c/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/ora12c/pdb12c/undotbs01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_g8kd5pwo_.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs2_g8pl709m_.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ora12c/pdb12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 tag=TAG20190326T131745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ora12c/pdbseed/system01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/ora12c/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/05ttdfsc_1_1 tag=TAG20190326T131745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 26-MAR-19

Starting backup at 26-MAR-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=4 STAMP=1003929491
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/06ttdfsj_1_1 tag=TAG20190326T131811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-MAR-19

Starting Control File and SPFILE Autobackup at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/c-380590835-20190326-00 comment=NONE
Finished Control File and SPFILE Autobackup at 26-MAR-19

构建测试表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
drop table t1 purge;

create table t1 (id number);
insert into t1 values(1);
commit;

-- 检查当前scn
xb@PDB12C> select current_scn from v$database;

CURRENT_SCN
-----------
1612170

insert into t1 values(2);
commit;

执行恢复,指定一个辅助目录,时间的指定可以通过UNTIL SCN,UNTIL TIMEUNTIL SEQUENCE,可以只导出dmp,并不立即做导入表的操作。

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
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
recover table xb.t1 of pluggable database PDB12C
until scn 1612170
auxiliary destination '/u01/aux' >>>>==== 指定辅助目录,用于建立辅助实例
datapump destination '/u01/aux' >>>>==== 指定datapump目录
dump file 't1_meta.dmp' >>>>==== 指定datapump文件名
remap table 'XB'.'T1':'XBDBA'.'T2' >>>>==== 12.2中可以恢复到不同schema
remap tablespace 'TBS1':'TBS2'; >>>>==== 恢复到不同tablespace


Starting recover at 26-MAR-19
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 PDB12C:SYSTEM
Tablespace UNDOTBS1
Tablespace PDB12C:UNDOTBS1

Creating automatic instance, with SID='AEuy'

initialization parameters used for automatic instance:
db_name=ORA12C
db_unique_name=AEuy_pitr_PDB12C_ORA12C
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=504M
processes=200
db_create_file_dest=/u01/aux
log_archive_dest_1='location=/u01/aux'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance ORA12C

Oracle instance started

Total System Global Area 528482304 bytes

Fixed Size 8794744 bytes
Variable Size 167775624 bytes
Database Buffers 348127232 bytes
Redo Buffers 3784704 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until scn 1612170;
# 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';
}
executing Memory Script

executing command: SET until clause

Starting restore at 26-MAR-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 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/product/12.2.0/dbhome_1/dbs/c-380590835-20190326-00
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/c-380590835-20190326-00 tag=TAG20190326T131812
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/aux/ORA12C/controlfile/o1_mf_g9mfwx6s_.ctl
Finished restore at 26-MAR-19

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{
# set requested point in time
set until scn 1612170;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 3 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 9, 4, 11, 3, 10;

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/aux/ORA12C/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 26-MAR-19
using channel ORA_AUX_DISK_1

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 00001 to /u01/aux/ORA12C/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/aux/ORA12C/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/aux/ORA12C/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1 tag=TAG20190326T131745
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
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/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 tag=TAG20190326T131745
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 26-MAR-19

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=1003929916 file name=/u01/aux/ORA12C/datafile/o1_mf_system_g9mfx5j9_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1003929916 file name=/u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_system_g9mfxnvd_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=1003929916 file name=/u01/aux/ORA12C/datafile/o1_mf_undotbs1_g9mfx5jf_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1003929916 file name=/u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_undotbs1_g9mfxnvf_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=1003929916 file name=/u01/aux/ORA12C/datafile/o1_mf_sysaux_g9mfx5jc_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=15 STAMP=1003929916 file name=/u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_sysaux_g9mfxnvc_.dbf

contents of Memory Script:
{
# set requested point in time
set until scn 1612170;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'PDB12C' "alter database datafile
9 online";
sql clone "alter database datafile 4 online";
sql clone 'PDB12C' "alter database datafile
11 online";
sql clone "alter database datafile 3 online";
sql clone 'PDB12C' "alter database datafile
10 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "PDB12C":"SYSTEM", "UNDOTBS1", "PDB12C":"UNDOTBS1", "SYSAUX", "PDB12C":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 9 online

sql statement: alter database datafile 4 online

sql statement: alter database datafile 11 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 10 online

Starting recover at 26-MAR-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/arch/1_6_1002791605.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/arch/1_7_1002791605.dbf
archived log file name=/u01/arch/1_6_1002791605.dbf thread=1 sequence=6
archived log file name=/u01/arch/1_7_1002791605.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-MAR-19

sql statement: alter database open read only

contents of Memory Script:
{
sql clone 'alter pluggable database PDB12C open read only';
}
executing Memory Script

sql statement: alter pluggable database PDB12C open read only

contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/u01/aux/ORA12C/controlfile/o1_mf_g9mfwx6s_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 528482304 bytes

Fixed Size 8794744 bytes
Variable Size 167775624 bytes
Database Buffers 348127232 bytes
Redo Buffers 3784704 bytes

sql statement: alter system set control_files = ''/u01/aux/ORA12C/controlfile/o1_mf_g9mfwx6s_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 528482304 bytes

Fixed Size 8794744 bytes
Variable Size 167775624 bytes
Database Buffers 348127232 bytes
Redo Buffers 3784704 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until scn 1612170;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 13 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 13;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 26-MAR-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK

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 00013 to /u01/aux/AEUY_PITR_PDB12C_ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 tag=TAG20190326T131745
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 26-MAR-19

datafile 13 switched to datafile copy
input datafile copy RECID=17 STAMP=1003929974 file name=/u01/aux/AEUY_PITR_PDB12C_ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_g9mfzojn_.dbf

contents of Memory Script:
{
# set requested point in time
set until scn 1612170;
# online the datafiles restored or switched
sql clone 'PDB12C' "alter database datafile
13 online";
# recover and open resetlogs
recover clone database tablespace "PDB12C":"TBS1", "SYSTEM", "PDB12C":"SYSTEM", "UNDOTBS1", "PDB12C":"UNDOTBS1", "SYSAUX", "PDB12C":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 13 online

Starting recover at 26-MAR-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/arch/1_6_1002791605.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/arch/1_7_1002791605.dbf
archived log file name=/u01/arch/1_6_1002791605.dbf thread=1 sequence=6
archived log file name=/u01/arch/1_7_1002791605.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-MAR-19

database opened

contents of Memory Script:
{
sql clone 'alter pluggable database PDB12C open';
}
executing Memory Script

sql statement: alter pluggable database PDB12C open

contents of Memory Script:
{
# create directory for datapump import
sql 'PDB12C' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/aux''";
# create directory for datapump export
sql clone 'PDB12C' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/aux''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux''

Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_AEuy_Aopu":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> . . exported "XB"."T1" 5.046 KB 1 rows
EXPDP> Master table "SYS"."TSPITR_EXP_AEuy_Aopu" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_AEuy_Aopu is:
EXPDP> /u01/aux/t1_meta.dmp
EXPDP> Job "SYS"."TSPITR_EXP_AEuy_Aopu" successfully completed at Tue Mar 26 13:28:07 2019 elapsed 0 00:00:57
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_AEuy_ovnl" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_AEuy_ovnl":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "XBDBA"."T2" 5.046 KB 1 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Job "SYS"."TSPITR_IMP_AEuy_ovnl" successfully completed at Tue Mar 26 13:28:21 2019 elapsed 0 00:00:05
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_temp_g9mfy1gj_.tmp deleted
auxiliary instance file /u01/aux/ORA12C/datafile/o1_mf_temp_g9mfxz6f_.tmp deleted
auxiliary instance file /u01/aux/AEUY_PITR_PDB12C_ORA12C/onlinelog/o1_mf_3_g9mfzxjf_.log deleted
auxiliary instance file /u01/aux/AEUY_PITR_PDB12C_ORA12C/onlinelog/o1_mf_2_g9mfzrc3_.log deleted
auxiliary instance file /u01/aux/AEUY_PITR_PDB12C_ORA12C/onlinelog/o1_mf_1_g9mfzr7d_.log deleted
auxiliary instance file /u01/aux/AEUY_PITR_PDB12C_ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_g9mfzojn_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_sysaux_g9mfxnvc_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/datafile/o1_mf_sysaux_g9mfx5jc_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_undotbs1_g9mfxnvf_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/datafile/o1_mf_undotbs1_g9mfx5jf_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_system_g9mfxnvd_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/datafile/o1_mf_system_g9mfx5j9_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/controlfile/o1_mf_g9mfwx6s_.ctl deleted
auxiliary instance file t1_meta.dmp deleted
Finished recover at 26-MAR-19

重新检查当前数据

1
2
3
4
5
xb@PDB12C> select * from xbdba.t2;

ID
----------
1

12.2中,在做recover表或分区的时候,因为需要创建辅助实例,所以会提前检查可以空间,如果指定的辅助目录空间不足,则操作无法进行。

归档模式下恢复offline drop的datafile

先说一下常见的offline drop 和offline之间的区别

Alter database datafile offline

一般如下场景下会选择这种方式

  • 离线备份数据文件
  • 重命名或更换文件路径,必须要offline datafile或者tablespace
  • 数据文件写入出现问题时,会自动offline这个datafile,当解决问题后得手动online
  • 数据文件丢失或出错时,你必须手动offline才能open database

Alter database datafile offline drop

这里的关键字_DROP_并不是真正删除文件,只是标记为不用了,可能未来会删除,数据文件仍然存在于数据字典中,如果你非要删除可以通过第三种方式

ALTER TABLESPACE … DROP DATAFILE 或 DROP TABLESPACE … INCLUDING CONTENTS AND DATAFILES

前者删除datafile也有不少限制

  • 数据库必须为打开状态
  • 数据datafile非空,你不能直接删除这个文件,必须要先移除对象或者直接删除这个datafile所在的表空间
  • 不能删除表空间里的第一个文件或者唯一的一个文件,意味着不能删除bigfile tablespace下的数据文件
  • 不能删除read-only的数据文件
  • 不能删除system表空间下的数据文件
  • 不能删除offline状态的locally managed tablespace管理的数据文件
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
SYS@xb> alter database datafile 6 offline drop;

数据库已更改。

SYS@xb> select file#,status from v$datafile where file#=6;

FILE# STATUS
---------- -------
6 RECOVER

SYS@xb> c/datafile/datafile_header
1* select file#,status from v$datafile_header where file#=6
SYS@xb> /

FILE# STATUS
---------- -------
6 OFFLINE

SYS@xb> alter system switch logfile;

系统已更改。

SYS@xb> recover datafile 6; >>>>====这里涉及到recover操作,如果恢复的够快,redo未被覆盖则不需要归档,否则必须要archivelog模式下
完成介质恢复。
SYS@xb> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- ------------ --------------
1 1 125 1073741824 512 1 YES ACTIVE 1276783 23-10月-18 1278142 23-10月-18
2 1 124 1073741824 512 1 YES INACTIVE 1255472 23-10月-18 1276783 23-10月-18
3 1 126 1073741824 512 1 NO CURRENT 1278142 23-10月-18 2.8147E+14

SYS@xb> select file#,status from v$datafile where file#=6;

FILE# STATUS
---------- -------
6 OFFLINE

SYS@xb> select file#,status from v$datafile_header where file#=6;

FILE# STATUS
---------- -------
6 OFFLINE

SYS@xb> alter database datafile 6 online;

数据库已更改。

SYS@xb> select file#,status from v$datafile_header where file#=6;

FILE# STATUS
---------- -------
6 ONLINE

SYS@xb> select file#,status from v$datafile where file#=6;

FILE# STATUS
---------- -------
6 ONLINE

归档模式下恢复无备份的lost datafile

场景:

  1. 你在os层面丢失了数据文件,并且没有相应的备份
  2. 数据库处于archivelog模式
  3. 从数据文件创建到目前所有的归档日志都完好

由于没有备份,数据库无法正常打开,除非将数据文件或表空间删除,这样就会造成数据的丢失

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
SYS@xb> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 /u01/arch
最早的联机日志序列 121
下一个存档日志序列 123
当前日志序列 123

SYS@xb> create tablespace tbs_ts datafile '/u01/app/oracle/oradata/xb/tbs_ts01.dbf' size 20m;

表空间已创建。

SYS@xb> create table tb_test (id number) tablespace tbs_ts;

表已创建。

SYS@xb> insert into tb_test values (1);

已创建 1 行。

SYS@xb> commit;

提交完成。

SYS@xb> select file_id,file_name from dba_data_files where tablespace_name='TBS_TS';

FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
6 /u01/app/oracle/oradata/xb/tbs_ts01.dbf

删除物理文件,模拟文件丢失

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
[oracle@xb xb]$ mv tbs_ts01.dbf tbs_ts01.dbf.bak

SYS@xb> shutdown immediate;
ORA-01116: 打开数据库文件 6 时出错
ORA-01110: 数据文件 6: '/u01/app/oracle/oradata/xb/tbs_ts01.dbf'
ORA-27041: 无法打开文件
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@xb> select status from v$instance;

STATUS
------------
OPEN

SYS@xb> shutdown abort;
ORACLE 例程已经关闭。
SYS@xb> startup mount
ORACLE 例程已经启动。

Total System Global Area 943669248 bytes
Fixed Size 2258880 bytes
Variable Size 666896448 bytes
Database Buffers 268435456 bytes
Redo Buffers 6078464 bytes
数据库装载完毕。

SYS@xb> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- --------------
6 ONLINE ONLINE FILE NOT FOUND 0

使用alter database create datafile <> as &#8230;.的方式,重建这个丢失的数据文件:

1
2
3
SYS@xb> alter database create datafile 6;

数据库已更改。

使用归档日志和redo恢复datafile

1
2
3
4
5
6
7
8
9
10
11
SYS@xb> recover datafile 6;
完成介质恢复。
SYS@xb> alter database open;

数据库已更改。

SYS@xb> select * from tb_test;

ID
----------
1

TSPITR恢复删除的表空间

表空间时间点恢复,是Oracle在基于冷备,热备恢复以外的一种以表空间为粒度的,不完全恢复的形式来将表空间恢复到过去某个特定的时间点的一种恢复方式。它整合了RMAN以及DataPump这2个备份恢复工具来实现时间点恢复。

TSPITR试用的场景

  • 恢复单独的逻辑对象而不影响数据库的其他对象
  • 可以恢复table到DDL操作之前,这是flashback table无法做到的
  • 可以恢复 drop purge的表
  • 恢复逻辑错误的表
  • 恢复被drop的表空间,即使没有catalog也可以

TSPITR限制条件

上面说了一些很方便的场景,但是也有一些限制条件

  • 必须要在archivelog模式下
  • 如果要恢复表空间到其rename之前,则在恢复的时候必须要用之前的名称(这样在恢复完成之后,会发现数据库中存在两个一样的表空间)
  • 如果table A同时用到了tablespace B和C,则你必须要同时对两个表空间做TSPITR
  • 如果表和索引分别出在不同的表空间,则必须先将索引drop叼
  • 你不能对现有的default tablespace做恢复,要先修改default tablespace
  • 你不能恢复含有以下对象的表空间
    1. 对象含有下级对象(例如物化视图),容器对象(分区表)
    2. 回滚段
    3. 属于SYS用户的对象

TSPITR过程中自动做了哪些操作

  • 如果要被恢复的表空间目前仍存在于数据库中,则检查这个表空间是否有其他依赖的对象,如果是则需要解决这些依赖关系
  • 检查是否可以连到辅助实例,如果没有则会新建一个
  • 如果表空间目前没有被drop的话,则会在目标库先将表空间offline
  • restore一个目标时间之前的控制文件到辅助实例
  • 将备份集里的数据文件恢复到辅助实例(包含system,sysaux,undo和要恢复的数据文件)
  • recover辅助实例的数据文件到指定时间
  • 用resetlogs打开辅助实例
  • 将辅助实例的tablespace置于read-only
  • 用数据泵的方式将辅助实例的表空间导出生成一个用于传输表空间的dump file
  • 关闭辅助实例
  • drop源库的表空间(如果存在的话)
  • 数据泵工具读取导出的dump file,然后将表空间插入到源库
  • 将插入的表空间置于read-write,然后立刻offline之
  • 删除所有的辅助实例文件

现在做个小测试

创建表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SYS@xb> create tablespace tbs_xb datafile '/u01/app/oracle/oradata/xb/tbs_xb01.dbf' size 10m reuse;

表空间已创建。

SYS@xb> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TBS_XB

创建用户和表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SYS@xb> create user xb identified by xb;

用户已创建。

SYS@xb> grant dba to xb;

授权成功。

SYS@xb> conn xb/xb
已连接。

XB@xb> create table tb_xb tablespace tbs_xb as select * from dba_tables;

表已创建。

XB@xb> select count(1) from tb_xb;

COUNT(1)
----------
2797

查看当前logfile sequence

1
2
3
4
5
XB@xb> select sequence# from v$log where status='CURRENT';

SEQUENCE#
----------
117

rman备份

1
2
3
[oracle@xb ContentsXML]$ rman target /

RMAN> backup database plus archivelog;

drop tablespace

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SYS@xb> alter system switch logfile;

系统已更改。

SYS@xb> /

系统已更改。

SYS@xb> /

系统已更改。

SYS@xb> drop tablespace tbs_xb including contents and datafiles;

表空间已删除。

SYS@xb> select sequence# from v$log where status='CURRENT';

SEQUENCE#
----------
122

当前我们删除完表空间以后所在的sequence no为122,如果我们要恢复到表空间删除之前,那么必须要恢复到122之前

TSPITR

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
RMAN> recover tablespace tbs_xb until logseq 122 auxiliary destination '/u01/app/oracle/oradata/';

启动 recover 于 20-10月-18
使用通道 ORA_DISK_1
RMAN-05026: 警告: 假定以下表空间集适用于指定的时间点

表空间列表要求具有 UNDO 段
表空间 SYSTEM
表空间 UNDOTBS1

使用 SID='phsf' 创建自动实例

供自动实例使用的初始化参数:
db_name=XB
db_unique_name=phsf_tspitr_XB
compatible=11.2.0.4.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
db_create_file_dest=/u01/app/oracle/oradata/ >>>>====这里就是设置的辅助目标路径
log_archive_dest_1='location=/u01/app/oracle/oradata/'
#No auxiliary parameter file used


启动自动实例 XB

Oracle 实例已启动

系统全局区域总计 1068937216 字节

Fixed Size 2260088 字节
Variable Size 281019272 字节
Database Buffers 780140544 字节
Redo Buffers 5517312 字节
自动实例已创建


已从目标数据库中删除的表空间的列表:
表空间 tbs_xb

内存脚本的内容:
{
# set requested point in time
set until logseq 122 thread 1;
# 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;';
}
正在执行内存脚本

正在执行命令: SET until clause

启动 restore 于 20-10月-18
分配的通道: ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: SID=18 设备类型=DISK

通道 ORA_AUX_DISK_1: 正在开始还原数据文件备份集
通道 ORA_AUX_DISK_1: 正在还原控制文件
通道 ORA_AUX_DISK_1: 正在读取备份片段 /u01/app/oracle/fast_recovery_area/XB/backupset/2018_10_20/o1_mf_ncsnf_TAG20181020T132437_fwoh15g3_.bkp
通道 ORA_AUX_DISK_1: 段句柄 = /u01/app/oracle/fast_recovery_area/XB/backupset/2018_10_20/o1_mf_ncsnf_TAG20181020T132437_fwoh15g3_.bkp 标记 = TAG20181020T132437
通道 ORA_AUX_DISK_1: 已还原备份片段 1
通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:00:01
输出文件名=/u01/app/oracle/oradata/XB/controlfile/o1_mf_fwohcoyp_.ctl
完成 restore 于 20-10月-18

sql 语句: alter database mount clone database

sql 语句: alter system archive log current

sql 语句: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

内存脚本的内容:
{
# set requested point in time
set until logseq 122 thread 1;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 5 to
"/u01/app/oracle/oradata/xb/tbs_xb01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2, 5;
switch clone datafile all;
}
正在执行内存脚本

正在执行命令: SET until clause

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

临时文件 1 在控制文件中已重命名为 /u01/app/oracle/oradata/XB/datafile/o1_mf_temp_%u_.tmp

启动 restore 于 20-10月-18
使用通道 ORA_AUX_DISK_1

通道 ORA_AUX_DISK_1: 正在开始还原数据文件备份集
通道 ORA_AUX_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_AUX_DISK_1: 将数据文件 00001 还原到 /u01/app/oracle/oradata/XB/datafile/o1_mf_system_%u_.dbf
通道 ORA_AUX_DISK_1: 将数据文件 00003 还原到 /u01/app/oracle/oradata/XB/datafile/o1_mf_undotbs1_%u_.dbf
通道 ORA_AUX_DISK_1: 将数据文件 00002 还原到 /u01/app/oracle/oradata/XB/datafile/o1_mf_sysaux_%u_.dbf
通道 ORA_AUX_DISK_1: 将数据文件 00005 还原到 /u01/app/oracle/oradata/xb/tbs_xb01.dbf
通道 ORA_AUX_DISK_1: 正在读取备份片段 /u01/app/oracle/fast_recovery_area/XB/backupset/2018_10_20/o1_mf_nnndf_TAG20181020T132437_fwoh0och_.bkp
通道 ORA_AUX_DISK_1: 段句柄 = /u01/app/oracle/fast_recovery_area/XB/backupset/2018_10_20/o1_mf_nnndf_TAG20181020T132437_fwoh0och_.bkp 标记 = TAG20181020T132437
通道 ORA_AUX_DISK_1: 已还原备份片段 1
通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:00:15
完成 restore 于 20-10月-18

数据文件 1 已转换成数据文件副本
输入数据文件副本 RECID=5 STAMP=990019850 文件名=/u01/app/oracle/oradata/XB/datafile/o1_mf_system_fwohcvqx_.dbf
数据文件 3 已转换成数据文件副本
输入数据文件副本 RECID=6 STAMP=990019850 文件名=/u01/app/oracle/oradata/XB/datafile/o1_mf_undotbs1_fwohcvr1_.dbf
数据文件 2 已转换成数据文件副本
输入数据文件副本 RECID=7 STAMP=990019850 文件名=/u01/app/oracle/oradata/XB/datafile/o1_mf_sysaux_fwohcvqz_.dbf

内存脚本的内容:
{
# set requested point in time
set until logseq 122 thread 1;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 5 online";
# recover and open resetlogs
recover clone database tablespace "TBS_XB", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
正在执行内存脚本

正在执行命令: SET until clause

sql 语句: alter database datafile 1 online

sql 语句: alter database datafile 3 online

sql 语句: alter database datafile 2 online

sql 语句: alter database datafile 5 online

启动 recover 于 20-10月-18
使用通道 ORA_AUX_DISK_1

正在开始介质的恢复

线程 1 序列 118 的归档日志已作为文件 /u01/arch/1_118_984998420.dbf 存在于磁盘上
线程 1 序列 119 的归档日志已作为文件 /u01/arch/1_119_984998420.dbf 存在于磁盘上
线程 1 序列 120 的归档日志已作为文件 /u01/arch/1_120_984998420.dbf 存在于磁盘上
线程 1 序列 121 的归档日志已作为文件 /u01/arch/1_121_984998420.dbf 存在于磁盘上
归档日志文件名=/u01/arch/1_118_984998420.dbf 线程=1 序列=118
归档日志文件名=/u01/arch/1_119_984998420.dbf 线程=1 序列=119
归档日志文件名=/u01/arch/1_120_984998420.dbf 线程=1 序列=120
归档日志文件名=/u01/arch/1_121_984998420.dbf 线程=1 序列=121
介质恢复完成, 用时: 00:00:00
完成 recover 于 20-10月-18

数据库已打开

内存脚本的内容:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace TBS_XB read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/oradata/''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/oradata/''";
}
正在执行内存脚本

sql 语句: alter tablespace TBS_XB read only

sql 语句: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata/''

sql 语句: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata/''

正在执行元数据导出...
EXPDP> 启动 "SYS"."TSPITR_EXP_phsf":
EXPDP> 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> 处理对象类型 TRANSPORTABLE_EXPORT/TABLE
EXPDP> 处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> 已成功加载/卸载了主表 "SYS"."TSPITR_EXP_phsf"
EXPDP> ******************************************************************************
EXPDP> SYS.TSPITR_EXP_phsf 的转储文件集为:
EXPDP> /u01/app/oracle/oradata/tspitr_phsf_68494.dmp
EXPDP> ******************************************************************************
EXPDP> 可传输表空间 TBS_XB 所需的数据文件:
EXPDP> /u01/app/oracle/oradata/xb/tbs_xb01.dbf
EXPDP> 作业 "SYS"."TSPITR_EXP_phsf" 已于 星期六 10月 20 13:31:26 2018 elapsed 0 00:00:22 成功完成
导出完毕


内存脚本的内容:
{
# shutdown clone before import
shutdown clone immediate
}
正在执行内存脚本

数据库已关闭
数据库已卸装
Oracle 实例已关闭

正在执行元数据导入...
IMPDP> 已成功加载/卸载了主表 "SYS"."TSPITR_IMP_phsf"
IMPDP> 启动 "SYS"."TSPITR_IMP_phsf":
IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/TABLE
IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> 作业 "SYS"."TSPITR_IMP_phsf" 已于 星期六 10月 20 13:31:43 2018 elapsed 0 00:00:02 成功完成
导入完毕


内存脚本的内容:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace TBS_XB read write';
sql 'alter tablespace TBS_XB offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
正在执行内存脚本

sql 语句: alter tablespace TBS_XB read write

sql 语句: alter tablespace TBS_XB offline

sql 语句: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

删除自动实例
自动实例已删除
已删除辅助实例文件 /u01/app/oracle/oradata/XB/datafile/o1_mf_temp_fwohdmto_.tmp
已删除辅助实例文件 /u01/app/oracle/oradata/XB/onlinelog/o1_mf_3_fwohdjsz_.log
已删除辅助实例文件 /u01/app/oracle/oradata/XB/onlinelog/o1_mf_2_fwohdg6h_.log
已删除辅助实例文件 /u01/app/oracle/oradata/XB/onlinelog/o1_mf_1_fwohdcgm_.log
已删除辅助实例文件 /u01/app/oracle/oradata/XB/datafile/o1_mf_sysaux_fwohcvqz_.dbf
已删除辅助实例文件 /u01/app/oracle/oradata/XB/datafile/o1_mf_undotbs1_fwohcvr1_.dbf
已删除辅助实例文件 /u01/app/oracle/oradata/XB/datafile/o1_mf_system_fwohcvqx_.dbf
已删除辅助实例文件 /u01/app/oracle/oradata/XB/controlfile/o1_mf_fwohcoyp_.ctl
完成 recover 于 20-10月-18

rman 其实是用了transportable tablespace机制将删除的表空间重新插回database

1
2
3
4
5
6
7
8
9
10
11
12
SYS@xb> select tablespace_name,status,plugged_in from dba_tablespaces;

TABLESPACE_NAME STATUS PLU
------------------------------ --------- ---
SYSTEM ONLINE NO
SYSAUX ONLINE NO
UNDOTBS1 ONLINE NO
TEMP ONLINE NO
USERS ONLINE NO
TBS_XB OFFLINE YES >>>>====表示plug

已选择6行。

恢复表

1
2
3
4
5
6
7
8
9
10
11
SYS@xb> alter tablespace tbs_xb online;

表空间已更改。

SYS@xb> conn xb/xb

XB@xb> select count(1) from tb_xb;

COUNT(1)
----------
2797