Oracle11g RAC集群环境备份恢复到单机环境
前言
在工作中,会遇到将RAC集群环境恢复到单机测试环境的需求,RAC集群的备份恢复和单机环境的备份恢复略有不同,本文详细讲述其过程。
环境准备
oracle11g rac集群 SID=orcl
节点一:192.168.1.211 rac01
节点二:192.168.1.212 rac02
单 机:192.168.1.217 oracle
源端进行备份
备份脚本
#!/bin/bash
source ~/.bash_profile
backup_log=/home/oracle/ORCL_full_`date +"%Y%m%d"`.log
###backup ORCL###
rman target / nocatalog msglog $backup_log append << EOF
run {
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
allocate channel ch4 type disk;
backup AS COMPRESSED BACKUPSET incremental level=0 database format '/db_backup/%d_FULL_%T_%s_%p.bak';
backup AS COMPRESSED BACKUPSET archivelog all format '/db_backup/%d_ARC_%T_%s_%p.bak' delete input;
backup current controlfile format '/db_backup/%d_CTL_%T_%s_%p.bak';
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}
exit;
EOF
备份后将备份文件scp到目标端 这里目标端接收到备份文件后检查文件权限 防止后续因为权限问题恢复错误
[oracle@rac01 db_backup]$ scp *20221125* 192.168.1.217:/oradata/db_backup
恢复前准备
先准备好参数文件 可以从源端进行拷贝 并创建好参数文件中相对应的目录 这里省略
由于RAC集群环境,数据文件存放在ASM当中,恢复的时候需要路径转换,编写路径转换的脚本 目标端数据文件和临时文件存放路径根据情况自行修改
在RAC集群中任意节点执行
--数据文件路径转换脚本语句
set linesize 400;
set pagesize 2000;
SELECT 'set newname for datafile '
||''||''''||name||''''||''
|| ' to '||''''|| '/oradata/orcl/'
|| SUBSTR (name, INSTR (name, '/', -1) + 1)
|| ''';'
FROM v$dbfile
ORDER BY FILE#;
#执行结果如下 并做好保存
set newname for datafile '+DATA/orcl/datafile/system.256.1116693635' to '/oradata/orcl/system.256.1116693635';
set newname for datafile '+DATA/orcl/datafile/sysaux.257.1116693635' to '/oradata/orcl/sysaux.257.1116693635';
set newname for datafile '+DATA/orcl/datafile/undotbs1.258.1116693635' to '/oradata/orcl/undotbs1.258.1116693635';
set newname for datafile '+DATA/orcl/datafile/users.259.1116693635' to '/oradata/orcl/users.259.1116693635';
set newname for datafile '+DATA/orcl/datafile/undotbs2.264.1116693773' to '/oradata/orcl/undotbs2.264.1116693773';
--临时表空间文件路径转换脚本语句
set linesize 400;
set pagesize 2000;
SELECT 'set newname for tempfile '
||''||''''||name||''''||''
|| ' to '||''''|| '/oradata/orcl/'
|| SUBSTR (name, INSTR (name, '/', -1) + 1)
|| ''';'
FROM v$tempfile
ORDER BY FILE#;
set newname for tempfile '+DATA/orcl/tempfile/temp.264.939475087' to '/oradata/orcl/temp.264.939475087';
进行恢复
首先 目标端通过参数文件启动到nomount状态
SYS@orcl>create spfile from pfile;
File created.
SYS@orcl>startup nomount;
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes
#进入到RMAN 恢复拷贝过来的控制文件备份并启动到mount状态
RMAN> restore controlfile from '/oradata/db_backup/ORCL_CTL_20221125_13_1.bak';
RMAN> alter database mount;
#清理控制文件中无效的备份信息
RMAN> crosscheck backup;
RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired backup;
RMAN> delete noprompt expired archivelog all;
#注册拷贝过来的备份集
RMAN> catalog start with '/oradata/db_backup/';
#开始恢复数据 这里要写上之前脚本得出的路径转换语句
恢复的脚本
vim recover_orcl.sh
#!/bin/bash
source ~/.bash_profile
backup_log=/home/oracle/recover_`date +"%Y%m%d"`.log
rman target / nocatalog msglog $backup_log append << EOF
run {
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
allocate channel ch4 type disk;
RMAN> run{
set newname for datafile '+DATA/orcl/datafile/system.256.1116693635' to '/oradata/orcl/system.256.1116693635';
set newname for datafile '+DATA/orcl/datafile/sysaux.257.1116693635' to '/oradata/orcl/sysaux.257.1116693635';
set newname for datafile '+DATA/orcl/datafile/undotbs1.258.1116693635' to '/oradata/orcl/undotbs1.258.1116693635';
set newname for datafile '+DATA/orcl/datafile/users.259.1116693635' to '/oradata/orcl/users.259.1116693635';
set newname for datafile '+DATA/orcl/datafile/undotbs2.264.1116693773' to '/oradata/orcl/undotbs2.264.1116693773';
set newname for tempfile '+DATA/orcl/tempfile/temp.264.939475087' to '/oradata/orcl/temp.264.939475087';
restore database;
switch datafile all;
switch tempfile all;
recover database;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}
exit;
EOF
执行脚本进行恢复,恢复完成后检查日志中的scn号
#修复到1087645scn号位置
RMAN> recover database until scn 1087645;
完成恢复
recover后 数据库为mount状态 因为rman备份恢复并不能恢复redo日志 而此时查看redo信息 路径还是源端ASM磁盘组中redo日志的路径
col member for a60
set line 300
set pages 2000
select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ------------------------------------------------------------
2 (null) +DATA/orcl/onlinelog/group_2.262.1116693727
2 (null) +FRA/orcl/onlinelog/group_2.258.1116693727
1 (null) +DATA/orcl/onlinelog/group_1.261.1116693725
1 (null) +FRA/orcl/onlinelog/group_1.257.1116693727
3 (null) +DATA/orcl/onlinelog/group_3.265.1116693805
3 (null) +FRA/orcl/onlinelog/group_3.259.1116693805
4 (null) +DATA/orcl/onlinelog/group_4.266.1116693807
4 (null) +FRA/orcl/onlinelog/group_4.260.1116693807
#转换redo日志的路径 并进行CLEAR CLEAR后会自动创建出相对应的日志文件
--执行以下日志文件路径转换sql
set linesize 400;
set pagesize 2000;
SELECT 'alter database rename file '
||''||''''||member||''''||''
|| ' to '||''''|| '/oradata/orcl/'
|| SUBSTR (member, INSTR (member, '/', -1) + 1)
|| ''';'
FROM v$logfile
ORDER BY GROUP#;
alter database rename file '+FRA/orcl/onlinelog/group_1.257.1116693727' to '/oradata/orcl/group_1.257.1116693727';
alter database rename file '+DATA/orcl/onlinelog/group_1.261.1116693725' to '/oradata/orcl/group_1.261.1116693725';
alter database rename file '+FRA/orcl/onlinelog/group_2.258.1116693727' to '/oradata/orcl/group_2.258.1116693727';
alter database rename file '+DATA/orcl/onlinelog/group_2.262.1116693727' to '/oradata/orcl/group_2.262.1116693727';
alter database rename file '+DATA/orcl/onlinelog/group_3.265.1116693805' to '/oradata/orcl/group_3.265.1116693805';
alter database rename file '+FRA/orcl/onlinelog/group_3.259.1116693805' to '/oradata/orcl/group_3.259.1116693805';
alter database rename file '+DATA/orcl/onlinelog/group_4.266.1116693807' to '/oradata/orcl/group_4.266.1116693807';
alter database rename file '+FRA/orcl/onlinelog/group_4.260.1116693807' to '/oradata/orcl/group_4.260.1116693807';
--上面日志转换路径后,实际文件系统中并没有相应的日志文件,通过clear logfile来创建出redo日志 先看好redo日志组group号
select group#,bytes /1024/1024,members,archived,status from v$log;
ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 4;
--删除standby日志文件 先看好standby日志租group号
select group#,status,used from v$standby_log;
根据组号进行drop
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
alter database drop logfile group 8;
alter database drop logfile group 9;
alter database drop logfile group 10;
#现在可以正常用resetlogs方式打开数据库
SYS@orcl>alter database open resetlogs;