表空间时间点恢复(TSPITR)：概念
通过执行TSPITR 可将一个或多个表空间快速恢复到以前的某个时间。
执行TSPITR 不会影响数据库中其它表空间或对象的状态。
使用RMAN 自动表空间时间点恢复(TSPITR) 可将Oracle DB 中的一个或多个表空间快速恢复到以前的某个时间，而不会影响数据库中其它表空间和对象的状态。
表空间时间点恢复(TSPITR)：术语
目标时间：表空间恢复到的时间点或SCN
恢复集：组成要恢复的表空间的数据文件
辅助集：对恢复集执行TSPITR 时所必需的，但不属于恢复集的数据文件。辅助集通常包括：
– SYSTEM表空间
– 还原段表空间
– 临时表空间
辅助目标：存储文件的磁盘位置
讨论TSPITR 时使用了以下术语：
目标时间：执行TSPITR 期间表空间将恢复到的时间点或系统更改号(SCN)。
恢复集：组成要恢复的表空间的数据文件。
辅助集：对恢复集执行TSPITR 时所必需的，但本身不属于恢复集的数据文件。辅助集通常包括：
- SYSTEM表空间的副本
- 包含来自目标实例的还原段的数据文件
- 某些情况下从辅助实例导出数据库对象期间使用的临时表空间
辅助目标：执行TSPITR 期间在磁盘上存储辅助实例的任何辅助集数据文件、控制文件和联机日志的位置。执行完TSPITR 之后可删除辅助目标中存储的文件。
表空间时间点恢复：体系结构
图表中显示了下列TSPITR 实体：
目标数据库：包含要恢复的表空间
控制文件：向RMAN 提供备份信息
备份集：来自目标数据库，用作重建表空间的来源
归档重做日志：来自目标数据库，用作重建表空间的来源
辅助实例：在恢复过程中用来执行恢复的Oracle DB 实例
RMAN 在执行表空间时间点恢复期间会执行下列步骤：
1.将目标时间之前某个时间点的备份控制文件还原到辅助实例。将恢复集的数据文件还原到目标数据库，将辅助集的数据文件还原到辅助实例。
2.将还原的数据文件恢复到指定的时间点。
3.将已恢复表空间中对象的字典元数据导出到目标数据库。
4.在目标数据库上发出SWITCH命令，以使目标数据库控制文件指向辅助实例上已恢复的恢复集中的数据文件。
5.将辅助实例中的字典元数据导入目标实例，以便能访问已恢复对象。
执行基本RMAN TSPITR
注意：要恢复的表空间必须是自包含的表空间
完全自动执行的TSPITR
– 指定辅助目标。
– RMAN 会管理TSPITR 的所有方面。
– 这是推荐的方法。
使用自动辅助实例的定制TSPITR
– 这是在完全自动执行TSPITR 的基础上进行的。
– 定制文件位置。
– 指定初始化参数。
– 指定通道配置。
使用您自己辅助实例的TSPITR
– 配置并管理辅助实例。
执行TSPITR 时有下列选项：
完全自动执行的TSPITR：指定辅助目标，RMAN 会管理TSPITR 操作的所有方面。这是最简单的执行TSPITR 的方法，建议采用这种方法，除非明确指出需要在执行
TSPITR 之后控制恢复集的位置，或者在执行TSPITR 期间控制辅助集文件的位置，或者需要控制辅助实例的通道配置或其它某个方面。
使用自动辅助实例的定制TSPITR：TSPITR 基于完全自动执行的TSPITR 的行为，可能仍使用辅助目标。可以自定义其行为的一个或多个方面，如辅助集文件或恢复集
文件的位置。可以为RMAN 创建并管理的辅助实例指定初始化参数或通道配置。
使用你自己辅助实例的TSPITR：设置、启动、停止和清除在TSPITR 中使用的辅助实例。另外，可以借助使用自动辅助实例的定制TSPITR 中的一些方法来管理TSPITR 过程。
RMAN自动执行TSPITR案例：
1、工作环境
|
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
|
12
:
21
:
32
SCOTT@ prod>create table emp1
as
select *
from
emp;
Table created.
Elapsed:
00
:
00
:
00.65
14
:
07
:
41
SCOTT@ prod>
insert
into
emp1 select *
from
emp
where
rownum <
5
;
4
rows created.
Elapsed:
00
:
00
:
00.02
14
:
08
:
18
SCOTT@ prod>commit;
Commit complete.
Elapsed:
00
:
00
:
00.04
14
:
08
:
19
SCOTT@ prod>select count(*)
from
emp1;
COUNT(*)
----------
18
Elapsed:
00
:
00
:
00.02
emp1表数据被误删除：
14
:
08
:
20
SCOTT@ prod>truncate table emp1;
Table truncated.
Elapsed:
00
:
00
:
00.32
14
:
08
:
30
SCOTT@ prod>
insert
into
emp1 select *
from
emp
where
empno=
7788
;
1
row created.
Elapsed:
00
:
00
:
00.03
14
:
08
:
46
SCOTT@ prod>commit;
Commit complete.
Elapsed:
00
:
00
:
00.06
14
:
08
:
48
SCOTT@ prod>select *
from
emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788
SCOTT ANALYST
7566
19
-APR
-87
3000
20
Elapsed:
00
:
00
:
00.08
14
:
08
:
55
SCOTT@ prod>update emp1 set empno=
8888
;
1
row updated.
Elapsed:
00
:
00
:
00.02
14
:
09
:
06
SCOTT@ prod>commit;
Commit complete.
Elapsed:
00
:
00
:
00.01
14
:
09
:
08
SCOTT@ prod>select *
from
emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
8888
SCOTT ANALYST
7566
19
-APR
-87
3000
20
Elapsed:
00
:
00
:
00.01
14
:
09
:
12
SCOTT@ prod>
|
2、做恢复前的检测
|
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
|
检测tablespace是否自包含：
10
:
39
:
16
SCOTT@ prod>CONN /
as
sysdba
Connected.
10
:
39
:
25
SYS@ prod>exec DBMS_TTS.TRANSPORT_SET_CHECK(
'TBS1'
,TRUE);
PL/SQL procedure successfully completed.
Elapsed:
00
:
01
:
58.45
10
:
41
:
43
SYS@ prod>select *
from
TRANSPORT_SET_VIOLATIONS t;
no rows selected
确认所要恢复的数据文件：
14
:
13
:
34
SYS@ prod>select *
from
dba_data_files t
where
T.TABLESPACE_NAME=
'TBS1'
;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ----------
INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
------------ ---------- ----------- -------
/u01/app/oracle/oradata/prod/tbs1.dbf
6
TBS1
10485760
1280
AVAILABLE
6
NO
0
0
0
9437184
1152
ONLINE
Elapsed:
00
:
00
:
00.02
14
:
14
:
42
SYS@ prod>
检查是否包含辅助集SYSTEM UNDO和CONTROLFILE：
14
:
14
:
38
SYS@ prod>select file_name name
from
dba_data_files t
where
T.TABLESPACE_NAME IN(
'SYSTEM'
,
'UNDOTBS1'
)
14
:
14
:
40
2
union
14
:
14
:
41
3
select max(name)
from
v$controlfile t;
NAME
-------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/control01.ctl
/u01/app/oracle/oradata/prod/system01.dbf
/u01/app/oracle/oradata/prod/undotbs01.dbf
Elapsed:
00
:
00
:
00.03
检查TSP之后可能丢失的对象：
14
:
14
:
42
SYS@ prod>select *
from
TS_PITR_OBJECTS_TO_BE_DROPPED T
where
T.OWNER=
'TBS1'
;
no rows selected
Elapsed:
00
:
00
:
00.17
|
3、执行基于RMAN的TSPITR
[root@rh6 prod]# mkdir /home/oracle/prod
存放辅助库文件
|
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
|
[oracle@rh6 ~]$ rman target /
Recovery Manager: Release
11.2.
0.1.
0
- Production
on
Tue Jun
24
14
:
24
:
46
2014
Copyright (c)
1982
,
2009
, Oracle
and
/
or
its affiliates. All rights reserved.
connected to target database: PROD (DBID=
239333010
)
执行recover tablespace：
RMAN> recover tablespace tbs1 until time
"to_date('2014-06-24 14:08:18','yyyy-mm-dd hh24:mi:ss')"
auxiliary destination
'/home/oracle/prod'
;
Starting recover
at
24
-JUN
-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=
40
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=
'nhxv'
建立Auxiliary instance：
initialization parameters used
for
automatic instance:
db_name=PROD
db_unique_name=nhxv_tspitr_PROD
compatible=
11.2.
0.0.
0
db_block_size=
8192
db_files=
200
sga_target=280M
processes=
50
db_create_file_dest=/home/oracle/prod
log_archive_dest_1=
'location=/home/oracle/prod'
#No auxiliary parameter file used
starting up automatic instance PROD
Oracle instance started
Total System Global Area
292278272
bytes
Fixed Size
2212736
bytes
Variable Size
100666496
bytes
Database Buffers
184549376
bytes
Redo Buffers
4849664
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 time
"to_date('2014-06-24 14:08:18','yyyy-mm-dd 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;'
;
}
通过备份建立Auxiliary database：
executing Memory Script
executing command: SET until clause
Starting restore
at
24
-JUN
-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=
19
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 /dsk4/bak/PROD_10.bak
channel ORA_AUX_DISK_1: piece handle=/dsk4/bak/PROD_10.bak tag=TAG20140624T121907
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time:
00
:
00
:
01
output file name=/home/oracle/prod/PROD/controlfile/o1_mf_9tl6lyhz_.ctl
Finished restore
at
24
-JUN
-14
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('2014-06-24 14:08:18','yyyy-mm-dd hh24:mi:ss')"
;
plsql <<<-- tspitr_2
declare
sqlstatement varchar2(
512
);
offline_not_needed exception;
pragma exception_init(offline_not_needed,
-01539
);
begin
sqlstatement :=
'alter tablespace '
||
'TBS1'
||
' offline immediate'
;
krmicd.writeMsg(
6162
, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed
then
null
;
end; >>>;
# 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
6
to
"/u01/app/oracle/oradata/prod/tbs1.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
,
6
;
switch clone datafile all;
}
restore 数据到Auxiliary database：
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace TBS1 offline immediate
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/prod/PROD/datafile/o1_mf_temp_%u_.tmp
in
control file
Starting restore
at
24
-JUN
-14
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 /home/oracle/prod/PROD/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile
00003
to /home/oracle/prod/PROD/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile
00002
to /home/oracle/prod/PROD/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile
00006
to /u01/app/oracle/oradata/prod/tbs1.dbf
channel ORA_AUX_DISK_1: reading
from
backup piece /dsk4/bak/PROD_9.bak
channel ORA_AUX_DISK_1: piece handle=/dsk4/bak/PROD_9.bak tag=TAG20140624T121907
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time:
00
:
01
:
55
Finished restore
at
24
-JUN
-14
datafile
1
switched to datafile copy
input datafile copy RECID=
5
STAMP=
851092186
file name=/home/oracle/prod/PROD/datafile/o1_mf_system_9tl6m7sd_.dbf
datafile
3
switched to datafile copy
input datafile copy RECID=
6
STAMP=
851092186
file name=/home/oracle/prod/PROD/datafile/o1_mf_undotbs1_9tl6m7x5_.dbf
datafile
2
switched to datafile copy
input datafile copy RECID=
7
STAMP=
851092186
file name=/home/oracle/prod/PROD/datafile/o1_mf_sysaux_9tl6m7vy_.dbf
contents of Memory Script:
{
# set requested point
in
time
set until time
"to_date('2014-06-24 14:08:18','yyyy-mm-dd hh24:mi:ss')"
;
# 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 6 online"
;
# recover
and
open resetlogs
recover clone database tablespace
"TBS1"
,
"SYSTEM"
,
"UNDOTBS1"
,
"SYSAUX"
delete
archivelog;
alter clone database open resetlogs;
}
将Auxiliary database的datafile激活，只需要system、undo、sysaux和要恢复的表空间（tbs1）：
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile
1
online
sql statement: alter database datafile
3
online
sql statement: alter database datafile
2
online
sql statement: alter database datafile
6
online
Starting recover
at
24
-JUN
-14
using channel ORA_AUX_DISK_1
starting media recovery
archived log
for
thread
1
with
sequence
25
is already
on
disk
as
file /dsk4/arch1/arch_1_25_847900609.log
archived log file name=/dsk4/arch1/arch_1_25_847900609.log thread=
1
sequence=
25
media recovery complete, elapsed time:
00
:
00
:
05
Finished recover
at
24
-JUN
-14
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone
'alter tablespace TBS1 read only'
;
# create directory
for
datapump
import
sql "create
or
replace
directory TSPITR_DIROBJ_DPDIR
as
''
/home/oracle/prod
''
";
# create directory
for
datapump export
sql clone "create
or
replace
directory TSPITR_DIROBJ_DPDIR
as
''
/home/oracle/prod
''
";
}
通过Auxiliary database导出恢复的数据：
executing Memory Script
sql statement: alter tablespace TBS1 read only
sql statement: create
or
replace
directory TSPITR_DIROBJ_DPDIR
as
''
/home/oracle/prod
''
sql statement: create
or
replace
directory TSPITR_DIROBJ_DPDIR
as
''
/home/oracle/prod
''
Performing export of metadata...
EXPDP> Starting
"SYS"
.
"TSPITR_EXP_nhxv"
:
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table
"SYS"
.
"TSPITR_EXP_nhxv"
successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set
for
SYS.TSPITR_EXP_nhxv is:
EXPDP> /home/oracle/prod/tspitr_nhxv_69835.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required
for
transportable tablespace TBS1:
EXPDP> /u01/app/oracle/oradata/prod/tbs1.dbf
EXPDP> Job
"SYS"
.
"TSPITR_EXP_nhxv"
successfully completed
at
14
:
32
:
23
Export completed
contents of Memory Script:
{
# shutdown clone
before
import
shutdown clone immediate
# drop target tablespaces
before
importing them back
sql
'drop tablespace TBS1 including contents keep datafiles'
;
}
在目标库导入要恢复的数据：
executing Memory Script
database closed
database dismounted
Oracle instance shut down
sql statement: drop tablespace TBS1 including contents keep datafiles
Performing
import
of metadata...
IMPDP> Master table
"SYS"
.
"TSPITR_IMP_nhxv"
successfully loaded/unloaded
IMPDP> Starting
"SYS"
.
"TSPITR_IMP_nhxv"
:
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job
"SYS"
.
"TSPITR_IMP_nhxv"
successfully completed
at
14
:
32
:
55
Import completed
contents of Memory Script:
{
# make read write
and
offline the imported tablespaces
sql
'alter tablespace TBS1 read write'
;
sql
'alter tablespace TBS1 offline'
;
# enable autobackups
after
TSPITR is finished
sql
'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;'
;
}
executing Memory Script
sql statement: alter tablespace TBS1 read write
sql statement: alter tablespace TBS1 offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /home/oracle/prod/PROD/datafile/o1_mf_temp_9tl6qbyc_.tmp deleted
auxiliary instance file /home/oracle/prod/PROD/onlinelog/o1_mf_3_9tl6q6v9_.log deleted
auxiliary instance file /home/oracle/prod/PROD/onlinelog/o1_mf_2_9tl6q41j_.log deleted
auxiliary instance file /home/oracle/prod/PROD/onlinelog/o1_mf_1_9tl6q1bx_.log deleted
auxiliary instance file /home/oracle/prod/PROD/datafile/o1_mf_sysaux_9tl6m7vy_.dbf deleted
auxiliary instance file /home/oracle/prod/PROD/datafile/o1_mf_undotbs1_9tl6m7x5_.dbf deleted
auxiliary instance file /home/oracle/prod/PROD/datafile/o1_mf_system_9tl6m7sd_.dbf deleted
auxiliary instance file /home/oracle/prod/PROD/controlfile/o1_mf_9tl6lyhz_.ctl deleted
Finished recover
at
24
-JUN
-14
RMAN>
|
4、恢复完成校验
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
14
:
33
:
44
SCOTT@ prod>conn /
as
sysdba
Connected.
14
:
34
:
17
SYS@ prod>select tablespace_name,status
from
dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TBS1 OFFLINE
7
rows selected.
Elapsed:
00
:
00
:
00.03
14
:
34
:
20
SYS@ prod>alter tablespace tbs1 online;
Tablespace altered.
Elapsed:
00
:
00
:
00.24
14
:
34
:
30
SYS@ prod>select count(*)
from
scott.emp1;
COUNT(*)
----------
18
|
当执行表空间基于时间的恢复，通过使用当前数据文件的镜像副本可以避免转储数据文件，从而提高TSPITR的性能。
|
1
2
3
4
5
6
7
8
|
建立数据文件镜像的两种方法:
RMAN> backup as copy
format
=
'/dsk4/bak/system.bak'
datafile 1;
RMAN> copy datafile 2 to
'/dsk4/bak/sysaux.bak'
;
RMAN> configure auxname
for
datafile 1 to
'/dsk4/bak/system.bak'
;
RMAN> configure auxname
for
datafile 2 to
'/dsk4/bak/sysaux.bak'
;
RMAN> recover tablespace tbs1 untiltime
'2014-06-24 14:08:18'
auxiliary destination
'/home/oracle/prod'
;
RMAN> configure auxname
for
datafile 1 clear;
RMAN> configure auxname
for
datafile 2 clear;
|
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1430322，如需转载请自行联系原作者
