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;