12c新特性:Rman中通过网络恢复文件

从12.1版本开始,rman提供了一个新功能,RESOTORE/RECOVER DATBASE USING SERVICE,可以直接通过网络对包含所需文件的物理备库进行文件的还原和恢复。可以恢复整库,数据文件,控制文件,参数文件或者表空间等。当你需要将备库与主库同步时通过网络还原文件非常有用。

通过网络使用备份集来还原和恢复文件,所有你可以使用多部分备份,加密和压缩等来提高备份和恢复的效率。

一般可以用在这些常见的场景:

  • 你需要前滚物理备库让它能与主库保持一致。

    一般通过创建主库的增量备份,然后恢复到备库。

  • 需要还原主库丢失的数据文件、控制文件或者表空间等,那么可以通过一致的物理备库上的文件来进行恢复。

12c以前,如果想要通过增量备份来让备库与主库同步成一致:

  1. 在主库上创建一个备库的控制文件
  2. 以备库的当前SCN号为起点,在主库上做一个增量备份
  3. 将增量备份拷贝到备库上,然后注册到RMAN的catalog
  4. 使用新的控制文件将备库启动到mount状态
  5. 取消备库的恢复应用,恢复增量备份
  6. 开启备库的恢复进程

而12c以后,步骤就简化了许多,只需要执行RECOVER … FROM SERVICE即可,这个命令则完成了下面这些过程:

  1. 以备库数据文件头里记录的SCN号为起点,在主库创建了增量备份
  2. 通过网络传输增量备份到备库机器
  3. 应用这些增量备份到备库

这样可以将备库的文件恢复到与主库保持一致,但是这种情况下备库的控制文件仍然记录的是旧的SCN号,要比现在备库数据文件里记录的SCN号要小。因此要完成整个同步工作,则需要更新备库的控制文件来使SCN号保持一致。

这里就模拟以前的一个例子处理一个dg gap同步问题,看看新功能带来的便利性。

查看主备库目前状态都是正常

1
2
3
4
5
6
7
8
9
10
11
12
13
--primary
sys@ORA12C> select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database;

DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
ora12c READ WRITE PRIMARY

--standby
sys@ORA12C> select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database;

DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
ORA12C_DG READ ONLY WITH APPLY PHYSICAL STANDBY

查看同步情况

1
2
3
4
5
6
7
8
9
10
11
12
13
DGMGRL> show configuration;

Configuration - ora12ccfg

Protection Mode: MaxAvailability
Members:
ora12c - Primary database
ora12c_dg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 43 seconds ago)

将备库重启到mount状态

1
2
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

停止同步,切换几个日志文件,然后删除主库归档

1
2
3
edit database ora12c_dg set state='apply-off';

alter system swithc logfile;

检查日志出现告警

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DGMGRL> show configuration

Configuration - ora12ccfg

Protection Mode: MaxPerformance
Members:
ora12c - Primary database
ora12c_dg - Physical standby database
Warning: ORA-16853: apply lag has exceeded specified threshold

Fast-Start Failover: DISABLED

Configuration Status:
WARNING (status updated 19 seconds ago)

检查主备库数据文件的scn号

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
--primary
sys@ORA12C> select HXFIL File_num,substr(HXFNM,1,40) HXFNM,fhscn from x$kcvfh;

FILE_NUM HXFNM FHSCN
---------- ------------------------------------------------------------ --------------------
1 /u01/app/oracle/oradata/ORA12C/datafile/ 11049071
3 /u01/app/oracle/oradata/ORA12C/datafile/ 11049071
4 /u01/app/oracle/oradata/ORA12C/datafile/ 11049071
5 /u01/app/oracle/oradata/ORA12C/datafile/ 1439827
6 /u01/app/oracle/oradata/ORA12C/datafile/ 1439827
7 /u01/app/oracle/oradata/ORA12C/datafile/ 11049071
8 /u01/app/oracle/oradata/ORA12C/datafile/ 1439827
9 /u01/app/oracle/oradata/ORA12C/86DF3648F 11049071
10 /u01/app/oracle/oradata/ORA12C/86DF3648F 11049071
11 /u01/app/oracle/oradata/ORA12C/86DF3648F 11049071
12 /u01/app/oracle/oradata/ORA12C/86DF3648F 11049071
43 /u01/app/oracle/oradata/ORA12C/8838793F1 11049071
44 /u01/app/oracle/oradata/ORA12C/8838793F1 11049071
45 /u01/app/oracle/oradata/ORA12C/8838793F1 11049071

--standby
FILE_NUM HXFNM FHSCN
---------- ------------------------------------------------------------ --------------------
1 /u01/app/oracle/oradata/ORA12C_DG/datafi 11049003
3 /u01/app/oracle/oradata/ORA12C_DG/datafi 11049003
4 /u01/app/oracle/oradata/ORA12C_DG/datafi 11049003
5 /u01/app/oracle/oradata/ORA12C_DG/86DF14 1439827
6 /u01/app/oracle/oradata/ORA12C_DG/86DF14 1439827
7 /u01/app/oracle/oradata/ORA12C_DG/datafi 11049003
8 /u01/app/oracle/oradata/ORA12C_DG/86DF14 1439827
9 /u01/app/oracle/oradata/ORA12C_DG/86DF36 11049003
10 /u01/app/oracle/oradata/ORA12C_DG/86DF36 11049003
11 /u01/app/oracle/oradata/ORA12C_DG/86DF36 11049003
12 /u01/app/oracle/oradata/ORA12C_DG/86DF36 11049003
43 /u01/app/oracle/oradata/ORA12C_DG/883879 11049003
44 /u01/app/oracle/oradata/ORA12C_DG/883879 11049003
45 /u01/app/oracle/oradata/ORA12C_DG/883879 11049003

这里看到除了5、6、8三个文件以外,其他的数据文件主备的scn号都不一致,检查备库当前的scn

1
2
3
4
5
sys@ORA12C> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
11049002

采用rman的新功能,recover standby using service,通过RMAN连接到target备库,然后用主库的service执行恢复备库命令。

语法:SYNTAX – RECOVER DATABASE FROM SERVICE < PRIMARY DB SERVICE NAME > NOREDO USING COMPRESSED BACKUPSET;

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



RMAN> recover database from service ora12c noredo using compressed backupset;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=619 device type=DISK
skipping datafile 5; already restored to SCN 1439827
skipping datafile 6; already restored to SCN 1439827
skipping datafile 8; already restored to SCN 1439827
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00001: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_system_gfnobxo0_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00003: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_sysaux_gfnoc19w_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00004: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_undotbs1_gfnod4tg_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00007: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_users_gfnog41g_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00009: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_system_gfnog8nr_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00010: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_sysaux_gfnogcrs_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00011: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_undotbs1_gfnogmbq_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00012: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_users_gfnoh25p_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00043: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_system_gfnohkg4_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00044: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_sysaux_gfnohrhf_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00045: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_undotbs1_gfnoj06c_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16

可以看到这里只有数据文件被还原了,没有归档日志应用,没有控制文件,这表示目前备库不是一致的,

重新检查两边的数据文件的scn号

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
--primary
sys@ORA12C> select HXFIL File_num,substr(HXFNM,1,40) HXFNM,fhscn from x$kcvfh;

FILE_NUM HXFNM FHSCN
---------- ------------------------------------------------------------ --------------------
1 /u01/app/oracle/oradata/ORA12C/datafile/ 11055068
3 /u01/app/oracle/oradata/ORA12C/datafile/ 11055098
4 /u01/app/oracle/oradata/ORA12C/datafile/ 11055131
5 /u01/app/oracle/oradata/ORA12C/datafile/ 1439827
6 /u01/app/oracle/oradata/ORA12C/datafile/ 1439827
7 /u01/app/oracle/oradata/ORA12C/datafile/ 11055143
8 /u01/app/oracle/oradata/ORA12C/datafile/ 1439827
9 /u01/app/oracle/oradata/ORA12C/86DF3648F 11055152
10 /u01/app/oracle/oradata/ORA12C/86DF3648F 11055166
11 /u01/app/oracle/oradata/ORA12C/86DF3648F 11055181
12 /u01/app/oracle/oradata/ORA12C/86DF3648F 11055200
43 /u01/app/oracle/oradata/ORA12C/8838793F1 11055225
44 /u01/app/oracle/oradata/ORA12C/8838793F1 11055239
45 /u01/app/oracle/oradata/ORA12C/8838793F1 11055266

--standby
sys@ORA12C> select HXFIL File_num,substr(HXFNM,1,40) HXFNM,fhscn from x$kcvfh;

FILE_NUM HXFNM FHSCN
---------- ------------------------------------------------------------ --------------------
1 /u01/app/oracle/oradata/ORA12C_DG/datafi 11055068
3 /u01/app/oracle/oradata/ORA12C_DG/datafi 11055098
4 /u01/app/oracle/oradata/ORA12C_DG/datafi 11055131
5 /u01/app/oracle/oradata/ORA12C_DG/86DF14 1439827
6 /u01/app/oracle/oradata/ORA12C_DG/86DF14 1439827
7 /u01/app/oracle/oradata/ORA12C_DG/datafi 11055143
8 /u01/app/oracle/oradata/ORA12C_DG/86DF14 1439827
9 /u01/app/oracle/oradata/ORA12C_DG/86DF36 11055152
10 /u01/app/oracle/oradata/ORA12C_DG/86DF36 11055166
11 /u01/app/oracle/oradata/ORA12C_DG/86DF36 11055181
12 /u01/app/oracle/oradata/ORA12C_DG/86DF36 11055200
43 /u01/app/oracle/oradata/ORA12C_DG/883879 11055225
44 /u01/app/oracle/oradata/ORA12C_DG/883879 11055239
45 /u01/app/oracle/oradata/ORA12C_DG/883879 11055266

可以看到现在两边的scn都同步到了一致状态。

因为数据文件都进行了恢复,控制文件还是老的,所以要从主库还原最新的备库控制文件

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
RMAN> shutdown immediate

database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area 5083496448 bytes

Fixed Size 8803264 bytes
Variable Size 1207962688 bytes
Database Buffers 3858759680 bytes
Redo Buffers 7970816 bytes

RMAN> restore standby controlfile from service ora12c;

Starting restore at 22-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=743 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ora12c
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
output file name=/u01/app/oracle/oradata/ORA12C/controlfile/o1_mf_gcm27myg_.ctl
output file name=/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/controlfile/o1_mf_gcm27ndj_.ctl
Finished restore at 22-MAY-19

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_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
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
RMAN> catalog start with '/u01/app/oracle/oradata/ORA12C_DG';

Starting implicit crosscheck backup at 22-MAY-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=253 device type=DISK
Crosschecked 20 objects
Finished implicit crosscheck backup at 22-MAY-19

Starting implicit crosscheck copy at 22-MAY-19
using channel ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck copy at 22-MAY-19

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ora12c/ORA12C/ORA12C_DG/autobackup/2019_04_23/o1_mf_s_1006353206_gcxdzml6_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ora12c/ORA12C/ORA12C_DG/autobackup/2019_04_24/o1_mf_s_1006442710_gd03trx4_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ora12c/ORA12C/ORA12C_DG/autobackup/2019_04_24/o1_mf_s_1006454159_gd0hmrfs_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ora12c/ORA12C/ORA12C_DG/autobackup/2019_05_06/o1_mf_s_1007567615_gdztcxvr_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ora12c/ORA12C/ORA12C_DG/autobackup/2019_05_06/o1_mf_s_1007571825_gdzy5vg5_.bkp

searching for all files that match the pattern /u01/app/oracle/oradata/ORA12C_DG

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_system_gcxbppbo_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_sysaux_gcxbq4jy_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_undotbs1_gcxbqfcq_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_users_gcxbsd21_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_temp_gcxbvd7s_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_system_gfnobxo0_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_sysaux_gfnoc19w_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_undotbs1_gfnod4tg_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_users_gfnog41g_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_temp_gfnonot8_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_system_gcxbqvmf_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_sysaux_gcxbqyr5_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_undotbs1_gcxbsf50_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_temp_gcxbvg7d_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_system_gfnodmhj_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_sysaux_gfnodpls_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_undotbs1_gfnog77f_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_temp_gfnonr04_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_system_gcxbsgb6_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_sysaux_gcxbsknh_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_undotbs1_gcxbsnt4_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_users_gcxbt3z2_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_temp_gczp8ojm_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_tbs_noco_gf2fxhlt_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_tbs_comp_gf2fxkmm_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_system_gfnog8nr_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_sysaux_gfnogcrs_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_undotbs1_gfnogmbq_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_users_gfnoh25p_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_1_gcxbtmj1_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_2_gcxbtnfs_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_3_gcxbtopn_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_4_gcxbtq2x_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_5_gcxbtsh9_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_6_gcxbttm1_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_7_gcxbtvgx_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_1_gfnojotd_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_2_gfnojtn0_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_3_gfnojzln_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_4_gfnok35r_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_5_gfnok7fj_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_6_gfnol2wo_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_7_gfnolyr2_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_system_gd0fcdjn_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_sysaux_gd0fcmgq_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_undotbs1_gd0fcpow_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_system_gd0fp8hg_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_sysaux_gd0fpcmw_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_undotbs1_gd0fpgxn_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_system_gdzoy61r_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_sysaux_gdzoy61s_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_undotbs1_gdzoy61t_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_temp_gdzst0jh_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_system_gdzxf5td_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_sysaux_gdzxf5tf_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_undotbs1_gdzxf5tg_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_temp_gdzxmo4z_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_system_gf0c32gr_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_sysaux_gf0c32gs_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_undotbs1_gf0c32gt_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_system_gfnohkg4_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_sysaux_gfnohrhf_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_undotbs1_gfnoj06c_.dbf

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_system_gcxbppbo_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_sysaux_gcxbq4jy_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_undotbs1_gcxbqfcq_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_users_gcxbsd21_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_temp_gcxbvd7s_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_system_gfnobxo0_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_sysaux_gfnoc19w_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_undotbs1_gfnod4tg_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_users_gfnog41g_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_temp_gfnonot8_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_system_gcxbqvmf_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_sysaux_gcxbqyr5_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_undotbs1_gcxbsf50_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_temp_gcxbvg7d_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_system_gfnodmhj_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_sysaux_gfnodpls_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_undotbs1_gfnog77f_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_temp_gfnonr04_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_system_gcxbsgb6_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_sysaux_gcxbsknh_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_undotbs1_gcxbsnt4_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_users_gcxbt3z2_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_temp_gczp8ojm_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_tbs_noco_gf2fxhlt_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_tbs_comp_gf2fxkmm_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_system_gfnog8nr_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_sysaux_gfnogcrs_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_undotbs1_gfnogmbq_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_users_gfnoh25p_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_system_gd0fcdjn_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_sysaux_gd0fcmgq_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_undotbs1_gd0fcpow_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_system_gd0fp8hg_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_sysaux_gd0fpcmw_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_undotbs1_gd0fpgxn_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_system_gdzoy61r_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_sysaux_gdzoy61s_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_undotbs1_gdzoy61t_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_temp_gdzst0jh_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_system_gf0c32gr_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_sysaux_gf0c32gs_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_undotbs1_gf0c32gt_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_system_gfnohkg4_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_sysaux_gfnohrhf_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_undotbs1_gfnoj06c_.dbf

List of Files Which Were Not Cataloged
=======================================
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_1_gcxbtmj1_.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_2_gcxbtnfs_.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_3_gcxbtopn_.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_4_gcxbtq2x_.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_5_gcxbtsh9_.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_6_gcxbttm1_.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_7_gcxbtvgx_.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_1_gfnojotd_.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_2_gfnojtn0_.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_3_gfnojzln_.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_4_gfnok35r_.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_5_gfnok7fj_.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_6_gfnol2wo_.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_7_gfnolyr2_.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_system_gdzxf5td_.dbf
RMAN-07519: Reason: Error while cataloging. See alert.log.
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_sysaux_gdzxf5tf_.dbf
RMAN-07519: Reason: Error while cataloging. See alert.log.
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_undotbs1_gdzxf5tg_.dbf
RMAN-07519: Reason: Error while cataloging. See alert.log.
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_temp_gdzxmo4z_.tmp
RMAN-07518: Reason: Foreign database file DBID: 980071166 Database Name: CDB1

Switch to cataloged copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_system_gfnobxo0_.dbf"
datafile 3 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_sysaux_gfnoc19w_.dbf"
datafile 4 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_undotbs1_gfnod4tg_.dbf"
datafile 5 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_system_gfnodmhj_.dbf"
datafile 6 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_sysaux_gcxbqyr5_.dbf"
datafile 7 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_users_gfnog41g_.dbf"
datafile 8 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_undotbs1_gfnog77f_.dbf"
datafile 9 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_system_gfnog8nr_.dbf"
datafile 10 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_sysaux_gfnogcrs_.dbf"
datafile 11 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_undotbs1_gfnogmbq_.dbf"
datafile 12 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_users_gfnoh25p_.dbf"
datafile 43 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_system_gfnohkg4_.dbf"
datafile 44 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_sysaux_gfnohrhf_.dbf"
datafile 45 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_undotbs1_gfnoj06c_.dbf"

如果在备库进行recover from service的过程中,主库新增了数据文件,那么需要单独将这个文件恢复到备库,也就是之前步骤查询的数据库SCN号

1
2
3
4
5
6
7
8
9
10
11
SELECT file# FROM V$DATAFILE WHERE creation_change# >= v$database.current_scn(STANDBY);

-- NON-CATALOG:
RUN
{
SET NEWNAME FOR DATABASE TO '/u01/app/oracle/oradata/ORA12C_DG';
RESTORE DATAFILE XX FROM SERVICE ORA12C;
}

-- CATALOG:
RESTORE DATAFILE XX FROM SERVICE ORA12C;

更新备库控制文件中redo日志文件的名称,这里直接clear备库日志,等下RMAN会自动重建

1
2
3
4
5
6
7
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;

主库做几个日志切换

1
ALTER SYSTEM ARCHIVE LOG CURRENT;

备库开启恢复进程

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
RMAN> recover database;

Starting recover at 22-MAY-19
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 175 is already on disk as file /u01/arch/ora12c/1_175_1006450609.dbf
archived log for thread 1 with sequence 176 is already on disk as file /u01/arch/ora12c/1_176_1006450609.dbf
archived log for thread 1 with sequence 177 is already on disk as file /u01/arch/ora12c/1_177_1006450609.dbf
archived log file name=/u01/arch/ora12c/1_175_1006450609.dbf thread=1 sequence=175
archived log file name=/u01/arch/ora12c/1_176_1006450609.dbf thread=1 sequence=176
archived log file name=/u01/arch/ora12c/1_177_1006450609.dbf thread=1 sequence=177
media recovery complete, elapsed time: 00:00:59
Finished recover at 22-MAY-19

RMAN> ALTER DATABASE OPEN READ ONLY;

Statement processed

DGMGRL> edit database ora12c_dg set state='apply-on';
Succeeded.
DGMGRL> show configuration

Configuration - ora12ccfg

Protection Mode: MaxPerformance
Members:
ora12c - Primary database
ora12c_dg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 1 second ago)

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表或分区的时候,因为需要创建辅助实例,所以会提前检查可以空间,如果指定的辅助目录空间不足,则操作无法进行。

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