Oracle RMAN恢复案例(一)

IT小Chen 2021-07-12

1342

需求：

恢复1天前(05/06/2021 08:05:05)数据库内一个用户的数据。

环境说明：

DB:Oracle 11204 RAC
OS:AIX 7.1

操作：

1.由于之前只有rman备份，没有逻辑备份，只能进行rman恢复。
2.全库有3T多，数据量较大，恢复机剩余空间不到1T，无法进行全库恢复。
3.查看用户所在表空间很小，只有300G，考虑只恢复用户数据所在表空间，在加上system,sysaux,undotbs表空间，不恢复其他业务用户的表空间。
4.启动数据库时，把其他没有恢复的表空间offline后，就可以正常打开数据库。

具体操作如下：

1.恢复控制文件

###Rman Script:
run {
SET DBID 3311060971;
allocate channel ch1 type 'sbt_tape'
PARMS="###省略###"
TRACE 0;
restore controlfile  from autobackup  until time = "TO_DATE('05/06/2021 08:05:05','MM/DD/YYYY HH24:MI:SS')" ;
}
exit;

问题:

此处恢复控制文件的方式是有问题的，因为最终是想将数据库恢复到05/06/2021 08:05:05时间点。
而在恢复控制文件时，由于并没有05/06/2021 08:05:05这一时刻的控制文件备份。
在恢复过程中没有报错，而是直接恢复了离05/06/2021 08:05:05时间点最近的一次控制文件进行恢复。
这就导致了控制文件恢复的时间点提前了，很多归档等备份信息不全，最终导致数据库在recovery时提示找不到归档，恢复出的数据库也提前了。
实际上要恢复的控制文件备份的时间可以稍往后推一些。

恢复日志如下：

RMAN> 2> 3> 4> 5> 6> 7>
allocated channel: ch1
channel ch1: SID=541 device type=SBT_TAPE
channel ch1: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
sent command to channel: ch1
Starting restore at May 04 2021 17:34:56
channel ch1: looking for AUTOBACKUP on day: 20210506
channel ch1: AUTOBACKUP found: c-3311060971-20210506-01
channel ch1: restoring control file from AUTOBACKUP c-3311060971-20210506-01
channel ch1: control file restore from AUTOBACKUP complete
output file name=/oradata/chenj/control01
output file name=/oradata/chenj/control02
Finished restore at May 04 2021 17:40:07
released channel: ch1
RMAN>
Recovery Manager complete.
]

2.执行restore

run {
allocate channel ch1 type 'sbt_tape'
PARMS="###省略###"
TRACE 0;
set newname for datafile '+AA_BB_CJCXX_DATA/chenjch/datafile/cccc_xxxxxx_data_01.dbf' to '/oradata/chenj/cccc_xxxxxx_data_01.dbf';
set newname for datafile '+AA_BB_CJCXX_DATA/chenjch/datafile/cccc_xxxxxx_data_02.dbf' to '/oradata/chenj/cccc_xxxxxx_data_02.dbf';
set newname for datafile '+AA_BB_CJCXX_DATA/chenjch/datafile/sysaux.302.1042642447' to '/oradata/chenj/sysaux.302.1042642447';
set newname for datafile '+AA_BB_CJCXX_DATA/chenjch/datafile/system.285.1042646103' to '/oradata/chenj/system.285.1042646103';
set newname for datafile '+AA_BB_CJCXX_DATA/chenjch/datafile/undotbs1.301.1042643625' to '/oradata/chenj/undotbs1.301.1042643625';
set newname for datafile '+AA_BB_CJCXX_DATA/chenjch/datafile/undotbs2.322.1042633105' to '/oradata/chenj/undotbs2.322.1042633105';
set newname for datafile '+AA_BB_CJCXX_DATA/chenjch/datafile/users.284.1042646223' to '/oradata/chenj/users.284.1042646223';
restore (tablespace 'SYSTEM','SYSAUX','USERS','UNDOTBS1','UNDOTBS2','AAAA_XXXXX_UUUU')  until time = "TO_DATE('05/06/2021 08:05:05','MM/DD/YYYY HH24:MI:SS')" ;
switch datafile all; 
}
exit;

日志：

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 
allocated channel: ch1
channel ch1: SID=464 device type=SBT_TAPE
channel ch1: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ch2
channel ch2: SID=541 device type=SBT_TAPE
channel ch2: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
sent command to channel: ch1
sent command to channel: ch2
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
Starting restore at May 04 2021 18:30:45
new media label is V_241064_3217058 for piece 484407_chenj_aj038d7e_1_1
new media label is V_241064_3217091 for piece 484407_chenj_ak038ehq_1_1
new media label is V_241064_3217092 for piece 484407_chenj_al038emc_1_1
new media label is V_241064_3217061 for piece 484407_chenj_ai038d7d_1_1
new media label is V_241064_3217058 for piece 484407_chenj_aj038d7e_1_1
new media label is V_241064_3217091 for piece 484407_chenj_ak038ehq_1_1
new media label is V_241064_3217092 for piece 484407_chenj_al038emc_1_1
new media label is V_241064_3217061 for piece 484407_chenj_ai038d7d_1_1
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to oradata/chenj/system.285.1042646103
channel ch1: restoring datafile 00004 to oradata/chenj/undotbs2.322.1042633105
channel ch1: reading from backup piece 484407_chenj_aj038d7e_1_1
channel ch2: starting datafile backup set restore
channel ch2: specifying datafile(s) to restore from backup set
channel ch2: restoring datafile 00051 to oradata/chenj/cccc_xxxxxx_data_01.dbf
channel ch2: reading from backup piece 484407_chenj_ai038d7d_1_1
channel ch2: piece handle=484407_chenj_ai038d7d_1_1 tag=TAG20210506T040045
channel ch2: restored backup piece 1
channel ch2: restore complete, elapsed time: 00:31:05
channel ch2: starting datafile backup set restore
channel ch2: specifying datafile(s) to restore from backup set
channel ch2: restoring datafile 00002 to oradata/chenj/sysaux.302.1042642447
channel ch2: reading from backup piece 484407_chenj_ak038ehq_1_1
channel ch1: piece handle=484407_chenj_aj038d7e_1_1 tag=TAG20210506T040045
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 01:05:11
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00005 to oradata/chenj/users.284.1042646223
channel ch1: restoring datafile 00003 to /oradata/chenj/undotbs1.301.1042643625
channel ch1: restoring datafile 00052 to /oradata/chenj/cccc_xxxxxx_data_02.dbf
channel ch1: reading from backup piece 484407_chenj_al038emc_1_1
channel ch2: piece handle=484407_chenj_ak038ehq_1_1 tag=TAG20210506T040045
channel ch2: restored backup piece 1
channel ch2: restore complete, elapsed time: 01:02:30
channel ch1: piece handle=484407_chenj_al038emc_1_1 tag=TAG20210506T040045
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:57:05
Finished restore at May 04 2021 20:33:24
datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=1077050007 file name=/oradata/chenj/system.285.1042646103
datafile 2 switched to datafile copy
input datafile copy RECID=13 STAMP=1077050008 file name=/oradata/chenj/sysaux.302.1042642447
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=1077050008 file name=/oradata/chenj/users.284.1042646223
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1077050008 file name=/oradata/chenj/undotbs1.301.1042643625
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=1077050008 file name=/oradata/chenj/undotbs2.322.1042633105
datafile 51 switched to datafile copy
input datafile copy RECID=17 STAMP=1077050008 file name=/oradata/chenj/cccc_xxxxxx_data_01.dbf
datafile 52 switched to datafile copy
input datafile copy RECID=18 STAMP=1077050008 file name=/oradata/chenj/cccc_xxxxxx_data_02.dbf
released channel: ch1
released channel: ch2
RMAN> 
Recovery Manager complete.

3.执行recovery

run {
allocate channel ch1 type 'sbt_tape'
PARMS="###省略###"
TRACE 0;
recover database skip tablespace 'DATA_TBS1','DATA_TBS2','DATA_TBS3','DATA_TBS4','DATA_TBS5','...'
until time = "TO_DATE('05/06/2021 08:05:05','MM/DD/YYYY HH24:MI:SS')";
}
exit;

日志：

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 
allocated channel: ch1
channel ch1: SID=541 device type=SBT_TAPE
channel ch1: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ch2
channel ch2: SID=618 device type=SBT_TAPE
channel ch2: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
sent command to channel: ch1
sent command to channel: ch2
Starting recover at May 04 2021 20:33:57
###没有restore出来的数据文件自动执行了offline操作###
Executing: alter database datafile 8 offline
Executing: alter database datafile 9 offline
Executing: alter database datafile 11 offline
Executing: alter database datafile 13 offline
Executing: alter database datafile 14 offline
Executing: alter database datafile 15 offline
Executing: alter database datafile 16 offline
Executing: alter database datafile 17 offline
Executing: alter database datafile 18 offline
Executing: alter database datafile 20 offline
Executing: alter database datafile 21 offline
Executing: alter database datafile 24 offline
Executing: alter database datafile 25 offline
Executing: alter database datafile 26 offline
Executing: alter database datafile 27 offline
Executing: alter database datafile 28 offline
Executing: alter database datafile 29 offline
Executing: alter database datafile 30 offline
Executing: alter database datafile 31 offline
Executing: alter database datafile 32 offline
Executing: alter database datafile 33 offline
Executing: alter database datafile 34 offline
Executing: alter database datafile 35 offline
Executing: alter database datafile 36 offline
Executing: alter database datafile 37 offline
Executing: alter database datafile 38 offline
Executing: alter database datafile 39 offline
Executing: alter database datafile 40 offline
Executing: alter database datafile 41 offline
Executing: alter database datafile 42 offline
Executing: alter database datafile 43 offline
Executing: alter database datafile 44 offline
Executing: alter database datafile 45 offline
Executing: alter database datafile 46 offline
Executing: alter database datafile 47 offline
Executing: alter database datafile 48 offline
Executing: alter database datafile 49 offline
Executing: alter database datafile 50 offline
Executing: alter database datafile 54 offline
Executing: alter database datafile 55 offline
Executing: alter database datafile 56 offline
Executing: alter database datafile 57 offline
Executing: alter database datafile 58 offline
Executing: alter database datafile 59 offline
Executing: alter database datafile 60 offline
Executing: alter database datafile 63 offline
Executing: alter database datafile 64 offline
Executing: alter database datafile 65 offline
Executing: alter database datafile 66 offline
Executing: alter database datafile 67 offline
Executing: alter database datafile 71 offline
Executing: alter database datafile 72 offline
Executing: alter database datafile 73 offline
Executing: alter database datafile 74 offline
Executing: alter database datafile 75 offline
Executing: alter database datafile 76 offline
Executing: alter database datafile 77 offline
Executing: alter database datafile 78 offline
Executing: alter database datafile 79 offline
Executing: alter database datafile 80 offline
Executing: alter database datafile 83 offline
Executing: alter database datafile 84 offline
Executing: alter database datafile 85 offline
Executing: alter database datafile 86 offline
Executing: alter database datafile 87 offline
Executing: alter database datafile 88 offline
Executing: alter database datafile 89 offline
Executing: alter database datafile 90 offline
Executing: alter database datafile 91 offline
Executing: alter database datafile 92 offline
Executing: alter database datafile 93 offline
Executing: alter database datafile 94 offline
Executing: alter database datafile 95 offline
Executing: alter database datafile 96 offline
Executing: alter database datafile 97 offline
Executing: alter database datafile 98 offline
Executing: alter database datafile 99 offline
Executing: alter database datafile 100 offline
Executing: alter database datafile 101 offline
Executing: alter database datafile 102 offline
Executing: alter database datafile 103 offline
Executing: alter database datafile 104 offline
Executing: alter database datafile 105 offline
Executing: alter database datafile 106 offline
Executing: alter database datafile 107 offline
Executing: alter database datafile 108 offline
Executing: alter database datafile 109 offline
Executing: alter database datafile 110 offline
Executing: alter database datafile 111 offline
Executing: alter database datafile 112 offline
Executing: alter database datafile 113 offline
Executing: alter database datafile 114 offline
Executing: alter database datafile 115 offline
Executing: alter database datafile 116 offline
Executing: alter database datafile 6 offline
Executing: alter database datafile 7 offline
Executing: alter database datafile 23 offline
Executing: alter database datafile 10 offline
Executing: alter database datafile 12 offline
Executing: alter database datafile 19 offline
Executing: alter database datafile 22 offline
Executing: alter database datafile 53 offline
Executing: alter database datafile 68 offline
Executing: alter database datafile 69 offline
Executing: alter database datafile 70 offline
Executing: alter database datafile 82 offline
Executing: alter database datafile 81 offline
Executing: alter database datafile 61 offline
Executing: alter database datafile 62 offline
starting media recovery
new media label is V_241064_3217122 for piece 484407_chenj_ap038g7b_1_1
new media label is V_241064_3217123 for piece 484407_chenj_aq038gaa_1_1
new media label is V_241064_3217122 for piece 484407_chenj_ap038g7b_1_1
new media label is V_241064_3217123 for piece 484407_chenj_aq038gaa_1_1
channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=1 sequence=180348
channel ch1: restoring archived log
archived log thread=2 sequence=182735
channel ch1: restoring archived log
archived log thread=1 sequence=180349
channel ch1: restoring archived log
archived log thread=1 sequence=180350
channel ch1: reading from backup piece 484407_chenj_ap038g7b_1_1
channel ch2: starting archived log restore to default destination
channel ch2: restoring archived log
archived log thread=2 sequence=182736
channel ch2: reading from backup piece 484407_chenj_aq038gaa_1_1
channel ch2: piece handle=484407_chenj_aq038gaa_1_1 tag=TAG20210506T045152
channel ch2: restored backup piece 1
channel ch2: restore complete, elapsed time: 00:00:07
channel ch1: piece handle=484407_chenj_ap038g7b_1_1 tag=TAG20210506T045152
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:02:25
archived log file name=/oradata/chenj/arch/1_180348_864829931.dbf thread=1 sequence=180348
archived log file name=/oradata/chenj/arch/2_182735_864829931.dbf thread=2 sequence=182735
archived log file name=/oradata/chenj/arch/1_180349_864829931.dbf thread=1 sequence=180349
archived log file name=/oradata/chenj/arch/1_180350_864829931.dbf thread=1 sequence=180350
archived log file name=/oradata/chenj/arch/2_182736_864829931.dbf thread=2 sequence=182736
unable to find archived log
archived log thread=1 sequence=180351
released channel: ch1
released channel: ch2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/07/2021 08:37:27
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 180351 and starting SCN of 542755860991
RMAN> 
Recovery Manager complete.

recover最后一步报错了，提示找不到180351。

查看180351归档生成时间，发现是5月5日的归档，说明数据也只恢复到了5月5日，丢失了1天的数据。

出现这个问题的原因就是前面提到的，控制文件恢复的时间不对，导致控制文件记录的备份信息不全。

解决方案：

由于恢复完成后，数据库执行了open read only操作，而并没有执行open resetlogs操作，说明数据库还可以继续执行recovery操作，之前还原的数据文件不需要在执行restore操作了。

具体操作：

1.停库备份控制文件。
2.恢复出较新的控制文件(时间略大约05/06/2021 08:05:05)。
3.用新的控制文件挂载数据库。
4.restore出需要的归档文件。
5.停库，用备份的控制文件挂载数据库(不在需要转换路径了)。
6.将新恢复出的归档日志注册到老控制文件中。
7.继续执行recovery操作。

###chenjuchao 20210711 11:30###

oracle

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle RMAN恢复案例(一)

评论