RMAN异机异目录完全恢复

开始

先说一下为什么要写这个异机完全恢复的blog,作为一个年轻的DBA,相信大多数都是备份长做而恢复不常做,我会经常担心我的生产库的服务器挂了怎么办,我的备份完好,但我们又没有容灾,我能否保证我的数据不丢失呢,所以就要rman利用备份重建,而如果此时我能找到完好无损的在线日志,我是不是可以完全恢复了呢,理论上大家都说可以,确从未在网上见到过完全恢复的事例(完全其实很简单,只是网上阐述了太多的不完全恢复,会让我觉得set until time等等是必备的其中一步),甚至oracle的官方网文档上也使用了“SET UNTIL SCN 123456;” 的案例,有鉴于此,我做了这个实验,并记录下来,跟大家分享,不见得这篇原创有多高深的技术和见解O(∩_∩)O

1
2
3
4
5
6
7
8
9
环境:
源库:
os:redhat 6.3 x64
db: 11gr2 11.2.0.4
存储方式:ASM
目标库:
os:redhat 6.5 x64
db: 11gr2 11.2.0.4
存储方式:文件系统

1. 首先,源库进行rman备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--手动0级全库备份
run {
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+DATA/backup/ctl_atuo_%F';
ALLOCATE CHANNEL c1 TYPE DISK;
ALLOCATE CHANNEL c2 TYPE DISK;
CROSSCHECK ARCHIVELOG ALL;
BACKUP INCREMENTAL LEVEL 0
DATABASE FORMAT '+DATA/backup/db_%U' TAG 'db_rman';
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP ARCHIVELOG ALL FORMAT '+DATA/backup/arc_%U' TAG 'ARC_rman'
DELETE INPUT;
DELETE NOPROMPT OBSOLETE;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
}

观察备份的文件,此处是ASM分别用asmcmd和rman查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
ASMCMD> ls
arc_0cqhdnqq_1_1
arc_0dqhdnqq_1_1
ctl_atuo_c-2496627597-20150917-00
ctl_atuo_c-2496627597-20150917-01
db_09qhdno6_1_1
db_0aqhdno6_1_1
ASMCMD> pwd
+Data/backup
ASMCMD>

RMAN> list backup summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
9 B 0 A DISK 17-SEP-15 1 1 NO DB_RMAN
10 B 0 A DISK 17-SEP-15 1 1 NO DB_RMAN
11 B F A DISK 17-SEP-15 1 1 NO TAG20150917T221650
12 B A A DISK 17-SEP-15 1 1 NO ARC_RMAN
13 B A A DISK 17-SEP-15 1 1 NO ARC_RMAN
14 B F A DISK 17-SEP-15 1 1 NO TAG20150917T221703

为了试验restore之后recover应用归档日志的情况,我在0级备份之后再生成一些归档日志,脚本如下:

1
2
3
4
5
6
7
declare
i integer;
begin
for i in 1..3000000 loop
insert into t values(i,'aaaaaaaaaaaaaaaaaaaaa');
end loop;
end;

向测试表T中插入200万行数据,看归档的日志:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
ASMCMD> ls
1_22_854775184.dbf
1_23_854775184.dbf
1_24_854775184.dbf
1_25_854775184.dbf
1_26_854775184.dbf
1_27_854775184.dbf
1_28_854775184.dbf
1_29_854775184.dbf
1_30_854775184.dbf
1_31_854775184.dbf
1_32_854775184.dbf
1_33_854775184.dbf
1_34_854775184.dbf
1_35_854775184.dbf
1_36_854775184.dbf
1_37_854775184.dbf
1_38_854775184.dbf
1_39_854775184.dbf

查看T表的行数

1
2
3
4
5
SQL> select count(1) from t;

COUNT(1)
----------
3000000

2. 转移备份

现在要把rman备份,备份之后的归档,源库关闭之后的在线redo文件拷贝到备库,这里稍微麻烦一点,涉及到从asm中拷贝文件,我这里使用2中方法,rman的backup as cpoy以及asmcmd的cp命令,如下所示:

rman copy备份集

1
2
3
4
5
6
7
--这里是copy 0级备份
backup as copy backupset 9 format '/u01/backupset/db2.rman';
backup as copy backupset 10 format '/u01/backupset/db2.rman';
backup as copy backupset 11 format '/u01/backupset/ctl1.rman';
backup as copy backupset 12 format '/u01/backupset/arc1.rman';
backup as copy backupset 13 format '/u01/backupset/arc2.rman';
backup as copy backupset 14 format '/u01/backupset/ctl2.rman';

asmcmd转移归档

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
--生成的归档日志
ASMCMD> ls
1_22_854775184.dbf
1_23_854775184.dbf
1_24_854775184.dbf
1_25_854775184.dbf
1_26_854775184.dbf
1_27_854775184.dbf
1_28_854775184.dbf
1_29_854775184.dbf
1_30_854775184.dbf
1_31_854775184.dbf
1_32_854775184.dbf
1_33_854775184.dbf
1_34_854775184.dbf
1_35_854775184.dbf
1_36_854775184.dbf
1_37_854775184.dbf
1_38_854775184.dbf
1_39_854775184.dbf
--copy走,无法使用通配符是一件很痛苦的事情
ASMCMD> cp 1_23_854775184.dbf /u02/backupset
copying +Data/archive_log/1_23_854775184.dbf -> /u02/backupset/1_23_854775184.dbf
ASMCMD> cp 1_24_854775184.dbf /u02/backupset
copying +Data/archive_log/1_24_854775184.dbf -> /u02/backupset/1_24_854775184.dbf
ASMCMD> cp 1_25_854775184.dbf /u02/backupset
copying +Data/archive_log/1_25_854775184.dbf -> /u02/backupset/1_25_854775184.dbf
ASMCMD> cp 1_26_854775184.dbf /u02/backupset
copying +Data/archive_log/1_26_854775184.dbf -> /u02/backupset/1_26_854775184.dbf
ASMCMD> cp 1_27_854775184.dbf /u02/backupset
copying +Data/archive_log/1_27_854775184.dbf -> /u02/backupset/1_27_854775184.dbf
ASMCMD> cp 1_28_854775184.dbf /u02/backupset
copying +Data/archive_log/1_28_854775184.dbf -> /u02/backupset/1_28_854775184.dbf
ASMCMD> cp 1_29_854775184.dbf /u02/backupset
copying +Data/archive_log/1_29_854775184.dbf -> /u02/backupset/1_29_854775184.dbf
ASMCMD> cp 1_3* /u02/backupset
copying +Data/archive_log/1_30_854775184.dbf -> /u02/backupset/1_30_854775184.dbf
ASMCMD> cp 1_31_854775184.dbf /u02/backupset
copying +Data/archive_log/1_31_854775184.dbf -> /u02/backupset/1_31_854775184.dbf
ASMCMD> cp 1_32_854775184.dbf /u02/backupset
copying +Data/archive_log/1_32_854775184.dbf -> /u02/backupset/1_32_854775184.dbf
ASMCMD> cp 1_33_854775184.dbf /u02/backupset
copying +Data/archive_log/1_33_854775184.dbf -> /u02/backupset/1_33_854775184.dbf
ASMCMD> cp 1_34_854775184.dbf /u02/backupset
copying +Data/archive_log/1_34_854775184.dbf -> /u02/backupset/1_34_854775184.dbf
ASMCMD> cp 1_35_854775184.dbf /u02/backupset
copying +Data/archive_log/1_35_854775184.dbf -> /u02/backupset/1_35_854775184.dbf
ASMCMD> cp 1_36_854775184.dbf /u02/backupset
copying +Data/archive_log/1_36_854775184.dbf -> /u02/backupset/1_36_854775184.dbf
ASMCMD> cp 1_37_854775184.dbf /u02/backupset
copying +Data/archive_log/1_37_854775184.dbf -> /u02/backupset/1_37_854775184.dbf
ASMCMD> cp 1_38_854775184.dbf /u02/backupset
copying +Data/archive_log/1_38_854775184.dbf -> /u02/backupset/1_38_854775184.dbf
ASMCMD> cp 1_39_854775184.dbf /u02/backupset
copying +Data/archive_log/1_39_854775184.dbf -> /u02/backupset/1_39_854775184.dbf

查看当前的redo状态,记住当前redo的seq号

1
2
3
4
5
6
7
SQL> select group#,SEQUENCE# ,MEMBERS,ARCHIVED,STATUS from v$log;

GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 40 2 NO CURRENT
2 38 2 YES INACTIVE
3 39 2 YES INACTIVE

查看redo文件

1
2
3
4
5
6
7
8
9
10
 SQL> select GROUP#,STATUS,MEMBER  from v$logfile;

GROUP# STATUS MEMBER
---------- ------- --------------------------------------------------------------------------------
3 +DATA/min/onlinelog/group_3.266.854775193
3 +DATA/min/onlinelog/group_3.267.854775195
2 +DATA/min/onlinelog/group_2.264.854775189
2 +DATA/min/onlinelog/group_2.265.854775191
1 +DATA/min/onlinelog/group_1.262.854775185
1 +DATA/min/onlinelog/group_1.263.854775187

使用asmcmd拷贝并改名,这个时候我已经关闭了源库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
ASMCMD> cd onlinelog
ASMCMD>
ASMCMD> ls
group_1.262.854775185
group_1.263.854775187
group_2.264.854775189
group_2.265.854775191
group_3.266.854775193
group_3.267.854775195

ASMCMD> cp group_1.262.854775185 /u02/backupset/redo1a.log
copying +Data/min/onlinelog/group_1.262.854775185 -> /u02/backupset/redo1a.log
ASMCMD> cp group_1.263.854775187 /u02/backupset/redo1b.log
copying +Data/min/onlinelog/group_1.263.854775187 -> /u02/backupset/redo1b.log
ASMCMD> cp group_2.264.854775189 /u02/backupset/redo2a.log
copying +Data/min/onlinelog/group_2.264.854775189 -> /u02/backupset/redo2a.log
ASMCMD> cp group_2.265.854775191 /u02/backupset/redo2b.log
copying +Data/min/onlinelog/group_2.265.854775191 -> /u02/backupset/redo2b.log
ASMCMD> cp group_3.266.854775193 /u02/backupset/redo3a.log
copying +Data/min/onlinelog/group_3.266.854775193 -> /u02/backupset/redo3a.log
ASMCMD> cp group_3.267.854775195 /u02/backupset/redo3b.log
copying +Data/min/onlinelog/group_3.267.854775195 -> /u02/backupset/redo3b.log

3. 目标机器恢复

这一步之前已经在目标主机安装好oracle软件,安装过程不再赘述,直接记录恢复过程
这里直接从备份中恢复pfile(比较懒,不想手动准备),无参数文件,rman会启动一个傻瓜实例,供我们恢复参数文件。
###从备份中恢复参数文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initmin.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes


RMAN> restore spfile to pfile '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initmin.ora' from '/u01/rmanbackup/ctl2.rman';

Starting restore at 18-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/rmanbackup/ctl2.rman
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 18-SEP-15

RMAN> shutdown abort

Oracle instance shut down

创建目录,修改参数文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[oracle@rman_newhost u01]$ mkdir -p /u01/app/oracle/admin/min/adump
[oracle@rman_newhost u01]$ mkdir -p /u01/app/oracle/fast_recovery_area
[oracle@rman_newhost u01]$ mkdir -p /u01/app/oracle/archived_log
[oracle@rman_newhost u01]$ mkdir -p /u01/app/oracle/oradata/min
*.audit_file_dest='/u01/app/oracle/admin/min/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/fast_recovery_area/control01.ctl','/u01/app/oracle/oradata/min/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='min'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=minXDB)'
*.log_archive_dest_1='location=/u01/app/oracle/archived_log'
*.log_checkpoints_to_alert=TRUE
*.memory_target=1394606080
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.undo_tablespace='UNDOTBS1'

启动到nomount状态,恢复控制文件

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
RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area 1402982400 bytes

Fixed Size 2253184 bytes
Variable Size 1275072128 bytes
Database Buffers 117440512 bytes
Redo Buffers 8216576 bytes

RMAN> restore controlfile from '/u01/rmanbackup/ctl2.rman';

Starting restore at 18-SEP-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1146 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/fast_recovery_area/control01.ctl
output file name=/u01/app/oracle/oradata/min/control02.ctl
Finished restore at 18-SEP-15

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

将redo文件和归档日志移动到对应目录下

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
[oracle@rman_newhost rmanbackup]$ mv redo* /u01/app/oracle/oradata/min
[oracle@rman_newhost rmanbackup]$ ll /u01/app/oracle/oradata/min
总用量 316744
-rw-r-----. 1 oracle oinstall 9748480 9月 18 00:34 control02.ctl
-rw-r--r--. 1 oracle oinstall 52429312 9月 17 23:00 redo1a.log
-rw-r--r--. 1 oracle oinstall 52429312 9月 17 23:01 redo1b.log
-rw-r--r--. 1 oracle oinstall 52429312 9月 17 23:01 redo2a.log
-rw-r--r--. 1 oracle oinstall 52429312 9月 17 23:01 redo2b.log
-rw-r--r--. 1 oracle oinstall 52429312 9月 17 23:02 redo3a.log
-rw-r--r--. 1 oracle oinstall 52429312 9月 17 23:02 redo3b.log
[oracle@rman_newhost rmanbackup]$ mv 1_* /u01/app/oracle/archived_log
[oracle@rman_newhost rmanbackup]$ ll /u01/app/oracle/archived_log
总用量 809056
-rw-r--r--. 1 oracle oinstall 46381568 9月 17 22:48 1_22_854775184.dbf
-rw-r--r--. 1 oracle oinstall 39179776 9月 17 22:49 1_23_854775184.dbf
-rw-r--r--. 1 oracle oinstall 46381568 9月 17 22:49 1_24_854775184.dbf
-rw-r--r--. 1 oracle oinstall 46381568 9月 17 22:50 1_25_854775184.dbf
-rw-r--r--. 1 oracle oinstall 46381568 9月 17 22:50 1_26_854775184.dbf
-rw-r--r--. 1 oracle oinstall 48581120 9月 17 22:50 1_27_854775184.dbf
-rw-r--r--. 1 oracle oinstall 46381568 9月 17 22:51 1_28_854775184.dbf
-rw-r--r--. 1 oracle oinstall 42950144 9月 17 22:51 1_29_854775184.dbf
-rw-r--r--. 1 oracle oinstall 46381568 9月 17 22:51 1_30_854775184.dbf
-rw-r--r--. 1 oracle oinstall 50134016 9月 17 22:53 1_31_854775184.dbf
-rw-r--r--. 1 oracle oinstall 46381568 9月 17 22:53 1_32_854775184.dbf
-rw-r--r--. 1 oracle oinstall 46714368 9月 17 22:53 1_33_854775184.dbf
-rw-r--r--. 1 oracle oinstall 44308480 9月 17 22:54 1_34_854775184.dbf
-rw-r--r--. 1 oracle oinstall 46381568 9月 17 22:54 1_35_854775184.dbf
-rw-r--r--. 1 oracle oinstall 46381568 9月 17 22:54 1_36_854775184.dbf
-rw-r--r--. 1 oracle oinstall 46381568 9月 17 22:54 1_37_854775184.dbf
-rw-r--r--. 1 oracle oinstall 46381568 9月 17 22:55 1_38_854775184.dbf
-rw-r--r--. 1 oracle oinstall 46381568 9月 17 22:55 1_39_854775184.dbf

注册备份集

下面一步的操作是删掉控制文件中对备份的记录信息,因为这是源库备份恢复过来的,我们要删掉老的备份信息,将备份重新注册,不删也没关系,crosscheck以后你会发现,原来的备份状态都是expired的

利用crosscheck删除控制文件中的备份记录

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
RMAN> list backup summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
9 B 0 A DISK 17-SEP-15 1 1 NO DB_RMAN
10 B 0 A DISK 17-SEP-15 1 1 NO DB_RMAN
11 B F A DISK 17-SEP-15 1 1 NO TAG20150917T221650
12 B A A DISK 17-SEP-15 1 1 NO ARC_RMAN
13 B A A DISK 17-SEP-15 1 1 NO ARC_RMAN

RMAN> crosscheck backup;

Starting implicit crosscheck backup at 18-SEP-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1146 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=1147 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=11 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 18-SEP-15

Starting implicit crosscheck copy at 18-SEP-15
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
Finished implicit crosscheck copy at 18-SEP-15

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+DATA/backup/db_0aqhdno6_1_1 RECID=9 STAMP=890691334
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+DATA/backup/db_09qhdno6_1_1 RECID=10 STAMP=890691334
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+DATA/backup/ctl_atuo_c-2496627597-20150917-00 RECID=11 STAMP=890691411
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+DATA/backup/arc_0dqhdnqq_1_1 RECID=12 STAMP=890691418
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+DATA/backup/arc_0cqhdnqq_1_1 RECID=13 STAMP=890691418
Crosschecked 5 objects


RMAN> list backup;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Incr 0 433.65M DISK 00:01:10 17-SEP-15
BP Key: 9 Status: EXPIRED Compressed: NO Tag: DB_RMAN
Piece Name: +DATA/backup/db_0aqhdno6_1_1
List of Datafiles in backup set 9
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 0 Incr 1138043 17-SEP-15 +DATA/min/datafile/sysaux.257.854775097
3 0 Incr 1138043 17-SEP-15 +DATA/min/datafile/undotbs1.258.854775097

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10 Incr 0 638.84M DISK 00:01:10 17-SEP-15
BP Key: 10 Status: EXPIRED Compressed: NO Tag: DB_RMAN
Piece Name: +DATA/backup/db_09qhdno6_1_1
List of Datafiles in backup set 10
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 1138042 17-SEP-15 +DATA/min/datafile/system.256.854775095
4 0 Incr 1138042 17-SEP-15 +DATA/min/datafile/users.259.854775097

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11 Full 9.36M DISK 00:00:01 17-SEP-15
BP Key: 11 Status: EXPIRED Compressed: NO Tag: TAG20150917T221650
Piece Name: +DATA/backup/ctl_atuo_c-2496627597-20150917-00
SPFILE Included: Modification time: 17-SEP-15
SPFILE db_unique_name: MIN
Control File Included: Ckp SCN: 1138347 Ckp time: 17-SEP-15

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
12 2.00K DISK 00:00:00 17-SEP-15
BP Key: 12 Status: EXPIRED Compressed: NO Tag: ARC_RMAN
Piece Name: +DATA/backup/arc_0dqhdnqq_1_1

List of Archived Logs in backup set 12
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 21 1138364 17-SEP-15 1138372 17-SEP-15

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
13 30.99M DISK 00:00:02 17-SEP-15
BP Key: 13 Status: EXPIRED Compressed: NO Tag: ARC_RMAN
Piece Name: +DATA/backup/arc_0cqhdnqq_1_1

List of Archived Logs in backup set 13
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 20 1127220 17-SEP-15 1138364 17-SEP-15

RMAN> list archivelog all;

specification does not match any archived log in the repository


RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
no obsolete backups found

RMAN> list expired backup;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Incr 0 433.65M DISK 00:01:10 17-SEP-15
BP Key: 9 Status: EXPIRED Compressed: NO Tag: DB_RMAN
Piece Name: +DATA/backup/db_0aqhdno6_1_1
List of Datafiles in backup set 9
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 0 Incr 1138043 17-SEP-15 +DATA/min/datafile/sysaux.257.854775097
3 0 Incr 1138043 17-SEP-15 +DATA/min/datafile/undotbs1.258.854775097

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10 Incr 0 638.84M DISK 00:01:10 17-SEP-15
BP Key: 10 Status: EXPIRED Compressed: NO Tag: DB_RMAN
Piece Name: +DATA/backup/db_09qhdno6_1_1
List of Datafiles in backup set 10
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 1138042 17-SEP-15 +DATA/min/datafile/system.256.854775095
4 0 Incr 1138042 17-SEP-15 +DATA/min/datafile/users.259.854775097

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11 Full 9.36M DISK 00:00:01 17-SEP-15
BP Key: 11 Status: EXPIRED Compressed: NO Tag: TAG20150917T221650
Piece Name: +DATA/backup/ctl_atuo_c-2496627597-20150917-00
SPFILE Included: Modification time: 17-SEP-15
SPFILE db_unique_name: MIN
Control File Included: Ckp SCN: 1138347 Ckp time: 17-SEP-15

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
12 2.00K DISK 00:00:00 17-SEP-15
BP Key: 12 Status: EXPIRED Compressed: NO Tag: ARC_RMAN
Piece Name: +DATA/backup/arc_0dqhdnqq_1_1

List of Archived Logs in backup set 12
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 21 1138364 17-SEP-15 1138372 17-SEP-15

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
13 30.99M DISK 00:00:02 17-SEP-15
BP Key: 13 Status: EXPIRED Compressed: NO Tag: ARC_RMAN
Piece Name: +DATA/backup/arc_0cqhdnqq_1_1

List of Archived Logs in backup set 13
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 20 1127220 17-SEP-15 1138364 17-SEP-15

RMAN> delete noprompt expired backup;

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
9 9 1 1 EXPIRED DISK +DATA/backup/db_0aqhdno6_1_1
10 10 1 1 EXPIRED DISK +DATA/backup/db_09qhdno6_1_1
11 11 1 1 EXPIRED DISK +DATA/backup/ctl_atuo_c-2496627597-20150917-00
12 12 1 1 EXPIRED DISK +DATA/backup/arc_0dqhdnqq_1_1
13 13 1 1 EXPIRED DISK +DATA/backup/arc_0cqhdnqq_1_1
deleted backup piece
backup piece handle=+DATA/backup/db_0aqhdno6_1_1 RECID=9 STAMP=890691334
deleted backup piece
backup piece handle=+DATA/backup/db_09qhdno6_1_1 RECID=10 STAMP=890691334
deleted backup piece
backup piece handle=+DATA/backup/ctl_atuo_c-2496627597-20150917-00 RECID=11 STAMP=890691411
deleted backup piece
backup piece handle=+DATA/backup/arc_0dqhdnqq_1_1 RECID=12 STAMP=890691418
deleted backup piece
backup piece handle=+DATA/backup/arc_0cqhdnqq_1_1 RECID=13 STAMP=890691418
Deleted 5 EXPIRED objects


RMAN> list backup;

specification does not match any backup in the repository

重新注册备份,并观察控制文件中的备份信息,和数据库结构的信息

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
RMAN> catalog start with '/u01/rmanbackup';

searching for all files that match the pattern /u01/rmanbackup

List of Files Unknown to the Database
=====================================
File Name: /u01/rmanbackup/arc1.rman
File Name: /u01/rmanbackup/db1.rman
File Name: /u01/rmanbackup/ctl2.rman
File Name: /u01/rmanbackup/ctl1.rman
File Name: /u01/rmanbackup/db2.rman
File Name: /u01/rmanbackup/arc2.rman

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/rmanbackup/arc1.rman
File Name: /u01/rmanbackup/db1.rman
File Name: /u01/rmanbackup/ctl2.rman
File Name: /u01/rmanbackup/ctl1.rman
File Name: /u01/rmanbackup/db2.rman
File Name: /u01/rmanbackup/arc2.rman

RMAN> list backup summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
14 B A A DISK 17-SEP-15 1 1 NO ARC_RMAN
15 B 0 A DISK 17-SEP-15 1 1 NO DB_RMAN
16 B 0 A DISK 17-SEP-15 1 1 NO DB_RMAN
17 B A A DISK 17-SEP-15 1 1 NO ARC_RMAN

--数据文件、临时文件的记录还是源库的信息,一会儿恢复的时候要setnewname
RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name MIN

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** +DATA/min/datafile/system.256.854775095
2 0 SYSAUX *** +DATA/min/datafile/sysaux.257.854775097
3 0 UNDOTBS1 *** +DATA/min/datafile/undotbs1.258.854775097
4 0 USERS *** +DATA/min/datafile/users.259.854775097

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/min/tempfile/temp.268.854775211

--你会发现这是老的控制文件,是我们0级备份里的,其中关于redo的状态记录,当前redo还停留在序列号22
SQL> select GROUP#,SEQUENCE# ,STATUS,ARCHIVED from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 22 CURRENT NO
3 21 ACTIVE YES
2 20 ACTIVE YES

SQL> desc v$logfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)

--redo的记录还是源库的信息,一会儿恢复的时候要setnewname
SQL> col member for a80
SQL> set line 200
SQL> select GROUP#,STATUS,MEMBER from v$logfile;

GROUP# STATUS MEMBER
---------- ------- --------------------------------------------------------------------------------
3 +DATA/min/onlinelog/group_3.266.854775193
3 +DATA/min/onlinelog/group_3.267.854775195
2 +DATA/min/onlinelog/group_2.264.854775189
2 +DATA/min/onlinelog/group_2.265.854775191
1 +DATA/min/onlinelog/group_1.262.854775185
1 +DATA/min/onlinelog/group_1.263.854775187

6 rows selected.

还原数据库

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
RMAN> RUN
2> {
3> ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
4> ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
5> set archivelog destination to "/u01/app/oracle/archived_log";
6> set newname for datafile 1 to "/u01/app/oracle/oradata/min/system01.dbf";
7> set newname for datafile 2 to "/u01/app/oracle/oradata/min/sysaux01.dbf";
8> set newname for datafile 3 to "/u01/app/oracle/oradata/min/undotbs01.dbf";
9> set newname for datafile 4 to "/u01/app/oracle/oradata/min/users01.dbf";
10> set newname for tempfile 1 to "/u01/app/oracle/oradata/min/temp01.dbf";
11> SQL "ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_1.262.854775185'' to ''/u01/app/oracle/oradata/min/redo1a.log'' ";
12> SQL "ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_1.263.854775187'' to ''/u01/app/oracle/oradata/min/redo1b.log'' ";
13> SQL "ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_2.264.854775189'' to ''/u01/app/oracle/oradata/min/redo2a.log'' ";
14> SQL "ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_2.265.854775191'' to ''/u01/app/oracle/oradata/min/redo2b.log'' ";
15> SQL "ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_3.266.854775193'' to ''/u01/app/oracle/oradata/min/redo3a.log'' ";
16> SQL "ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_3.267.854775195'' to ''/u01/app/oracle/oradata/min/redo3b.log'' ";
17> RESTORE DATABASE;
18> SWITCH DATAFILE ALL;
19> switch tempfile all;
20> }

released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
allocated channel: c1
channel c1: SID=1146 device type=DISK

allocated channel: c2
channel c2: SID=11 device type=DISK

executing command: SET ARCHIVELOG DESTINATION

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

sql statement: ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_1.262.854775185'' to ''/u01/app/oracle/oradata/min/redo1a.log''

sql statement: ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_1.263.854775187'' to ''/u01/app/oracle/oradata/min/redo1b.log''

sql statement: ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_2.264.854775189'' to ''/u01/app/oracle/oradata/min/redo2a.log''

sql statement: ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_2.265.854775191'' to ''/u01/app/oracle/oradata/min/redo2b.log''

sql statement: ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_3.266.854775193'' to ''/u01/app/oracle/oradata/min/redo3a.log''

sql statement: ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_3.267.854775195'' to ''/u01/app/oracle/oradata/min/redo3b.log''

Starting restore at 18-SEP-15

skipping datafile 2; already restored to file /u01/app/oracle/oradata/min/sysaux01.dbf
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/min/system01.dbf
channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/min/users01.dbf
channel c1: reading from backup piece /u01/rmanbackup/db2.rman
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00003 to /u01/app/oracle/oradata/min/undotbs01.dbf
channel c2: reading from backup piece /u01/rmanbackup/db1.rman
channel c2: piece handle=/u01/rmanbackup/db1.rman tag=DB_RMAN
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:08
channel c1: piece handle=/u01/rmanbackup/db2.rman tag=DB_RMAN
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:36
Finished restore at 18-SEP-15

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=890701322 file name=/u01/app/oracle/oradata/min/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=890701322 file name=/u01/app/oracle/oradata/min/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=890701322 file name=/u01/app/oracle/oradata/min/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=890701322 file name=/u01/app/oracle/oradata/min/users01.dbf

renamed tempfile 1 to /u01/app/oracle/oradata/min/temp01.dbf in control file
released channel: c1
released channel: c2

RMAN>

restore完毕,我们再来看一下控制文件中的信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name MIN

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 750 SYSTEM *** /u01/app/oracle/oradata/min/system01.dbf
2 560 SYSAUX *** /u01/app/oracle/oradata/min/sysaux01.dbf
3 100 UNDOTBS1 *** /u01/app/oracle/oradata/min/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/oradata/min/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/min/temp01.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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
RMAN> recover database;

Starting recover at 18-SEP-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1146 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=11 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=1137 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=10 device type=DISK

starting media recovery

archived log for thread 1 with sequence 38 is already on disk as file /u01/app/oracle/oradata/min/redo2a.log
archived log for thread 1 with sequence 39 is already on disk as file /u01/app/oracle/oradata/min/redo3a.log
archived log for thread 1 with sequence 40 is already on disk as file /u01/app/oracle/oradata/min/redo1a.log
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=20
channel ORA_DISK_1: reading from backup piece /u01/rmanbackup/arc2.rman
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=21
channel ORA_DISK_2: reading from backup piece /u01/rmanbackup/arc1.rman
channel ORA_DISK_2: piece handle=/u01/rmanbackup/arc1.rman tag=ARC_RMAN
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: piece handle=/u01/rmanbackup/arc2.rman tag=ARC_RMAN
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/archived_log/1_20_854775184.dbf thread=1 sequence=20
archived log file name=/u01/app/oracle/archived_log/1_21_854775184.dbf thread=1 sequence=21
archived log file name=/u01/app/oracle/archived_log/1_22_854775184.dbf thread=1 sequence=22
archived log file name=/u01/app/oracle/archived_log/1_23_854775184.dbf thread=1 sequence=23
archived log file name=/u01/app/oracle/archived_log/1_24_854775184.dbf thread=1 sequence=24
archived log file name=/u01/app/oracle/archived_log/1_25_854775184.dbf thread=1 sequence=25
archived log file name=/u01/app/oracle/archived_log/1_26_854775184.dbf thread=1 sequence=26
archived log file name=/u01/app/oracle/archived_log/1_27_854775184.dbf thread=1 sequence=27
archived log file name=/u01/app/oracle/archived_log/1_28_854775184.dbf thread=1 sequence=28
archived log file name=/u01/app/oracle/archived_log/1_29_854775184.dbf thread=1 sequence=29
archived log file name=/u01/app/oracle/archived_log/1_30_854775184.dbf thread=1 sequence=30
archived log file name=/u01/app/oracle/archived_log/1_31_854775184.dbf thread=1 sequence=31
archived log file name=/u01/app/oracle/archived_log/1_32_854775184.dbf thread=1 sequence=32
archived log file name=/u01/app/oracle/archived_log/1_33_854775184.dbf thread=1 sequence=33
archived log file name=/u01/app/oracle/archived_log/1_34_854775184.dbf thread=1 sequence=34
archived log file name=/u01/app/oracle/archived_log/1_35_854775184.dbf thread=1 sequence=35
archived log file name=/u01/app/oracle/archived_log/1_36_854775184.dbf thread=1 sequence=36
archived log file name=/u01/app/oracle/archived_log/1_37_854775184.dbf thread=1 sequence=37
archived log file name=/u01/app/oracle/oradata/min/redo2a.log thread=1 sequence=38
archived log file name=/u01/app/oracle/oradata/min/redo3a.log thread=1 sequence=39
archived log file name=/u01/app/oracle/oradata/min/redo1a.log thread=1 sequence=40
media recovery complete, elapsed time: 00:00:43
Finished recover at 18-SEP-15

RMAN>

从恢复的日志可以看出,oracle会绕过38、39号的归档直接利用在线日志做恢复,oracle的聪明之处可见一斑!
试着正常方式打开数据库,会发现报错:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/18/2015 01:04:41
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

--看一下此时控制文件中的日志序列号,会发现没有前推,必须用resetlogs打开数据库
SQL> select group#,status,sequence# from v$log;

GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 CURRENT 22
3 ACTIVE 21
2 ACTIVE 20
RMAN> alter database open resetlogs;

database opened

使用resetlog的原因是recover命令只能修复控制文件中数据物理结构信息,而无法修改控制文件中的当前重做日志的序列号的信息,recover命令结束后,控制文件中的当前日志序列号还是陈旧的,若按常规方式打开数据库,将报错,为了抹去控制文件这个固执的念头,oracle采用重设日志的功能,日志序列号从1开始。此处虽然使用了resetlogs,但是因为“recover database”命令执行成功,所有提交的事务不会丢失,resetlogs仅仅是为了照顾还原的控制文件,与不完全恢复的resetlogs是不同的,至此恢复结束。

1
2
3
4
5
6
7
8
9
10
11
SQL> conn test/test
ERROR:
ORA-28002: the password will expire within 6 days


Connected.
SQL> select count(1) from t;

COUNT(1)
----------
3000000