oracle11g单机数据通过RMAN恢复至RAC

一、概述

随着越来越多的企业部署oracle的集群RAC，增强了数据的安全性、提高数据库性能及保障数据库实时在线，将原有单机数据库数据迁移至集群环境中也成了我们工作中的一部分，迁移的方法也有许多，下面咱就看一下怎样通过RMAN将单机数据恢复至oracle的RAC中。

二、详细步骤

将备份文件上传至服务器中，并查看大小

[oracle@rac01 backup]$ ls -lrt

total 266496

-rw-r----- 1 oracle oinstall 261455872 Feb 24 12:17 full_02p1e2cu_1_1.bak

-rw-r----- 1 oracle oinstall 1097728 Feb 24 12:17 full_03p1e2ev_1_1.bak

-rw-r----- 1 oracle oinstall 10338304 Feb 24 12:20 arch_06p1e2kn_1_1.bak

根据之前备份及文件大小，我们可以得知参数文件和控制文件存放的文件。

下面恢复参数文件，恢复之前设置环境变量，并强制启动数据库至nomount阶段

RMAN> startup nomount force

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/oracle/app/oracle/product/11.2.0/dbs/initxysoul1.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 from '/oracle/backup/full_03p1e2ev_1_1.bak';

Starting restore at 2014-02-24 16:27:35

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /oracle/backup/full_03p1e2ev_1_1.bak

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 2014-02-24 16:27:36

RMAN>

查看生成的参数文件，并创建pfile文件

[oracle@rac01 backup]$ cd $ORACLE_HOME/dbs

[oracle@rac01 dbs]$ ls -lrt

total 18092

-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora

-rw-rw---- 1 oracle asmadmin 1544 Feb 24 16:26 hc_xysoul1.dat

-rw-r----- 1 oracle asmadmin 2560 Feb 24 16:27 spfilexysoul1.ora

SQL> create pfile from spfile;

File created.

修改参数文件，修改相关文件目录并创建对应目录

修改完成后，生成spfile文件，并启动数据库到nomount阶段

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 534462464 bytes

Fixed Size 2254952 bytes

Variable Size 213911448 bytes

Database Buffers 314572800 bytes

Redo Buffers 3723264 bytes

恢复控制文件并启动数据库到mount阶段

RMAN> restore controlfile from '/oracle/backup/full_03p1e2ev_1_1.bak';

Starting restore at 2014-02-24 16:33:52

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=30 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=+DATA/o1_mf_9jhz38tl_.ctl

Finished restore at 2014-02-24 16:33:56

RMAN> sql 'alter database mount';

sql statement: alter database mount

released channel: ORA_DISK_1

下面可以列出备份集内容

RMAN> list backup;

List of Backup Sets

===================

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ -------------------

1 24.57M DISK 00:00:04 2014-02-24 12:16:26

BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20140224T121622

Piece Name: /oracle/archivelog/XYSOUL/backupset/2014_02_24/o1_mf_annnn_TAG20140224T121622_9jokwpf5_.bkp

List of Archived Logs in backup set 1

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- ------------------- ---------- ---------

1 18 763748 2014-02-22 10:22:45 798098 2014-02-22 18:31:34

1 19 798098 2014-02-22 18:31:34 819527 2014-02-24 08:48:16

1 20 819527 2014-02-24 08:48:16 827856 2014-02-24 12:15:15

1 21 827856 2014-02-24 12:15:15 827892 2014-02-24 12:16:21

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

2 Full 249.34M DISK 00:00:59 2014-02-24 12:17:29

BP Key: 2 Status: AVAILABLE Compressed: YES Tag: TAG20140224T121629

Piece Name: /oracle/backup/full_02p1e2cu_1_1.bak

List of Datafiles in backup set 2

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- ------------------- ----

1 Full 827904 2014-02-24 12:16:30 /oracle/oradata/XYSOUL/datafile/o1_mf_system_9jhz3xx6_.dbf

2 Full 827904 2014-02-24 12:16:30 /oracle/oradata/XYSOUL/datafile/o1_mf_sysaux_9jhz4vkj_.dbf

3 Full 827904 2014-02-24 12:16:30 /oracle/oradata/XYSOUL/datafile/o1_mf_undotbs1_9jhz5gsj_.dbf

4 Full 827904 2014-02-24 12:16:30 /oracle/oradata/XYSOUL/datafile/o1_mf_users_9jhz6fw2_.dbf

5 Full 827904 2014-02-24 12:16:30 /oracle/oradata/XYSOUL/datafile/xysoul01.dbf

也可以使用一下命令添加备份集，追加归档等

RMAN> catalog backuppiece '/oracle/backup/arch_06p1e2kn_1_1.bak';

--恢复归档并指定目录
RMAN> run{

2> set archivelog destination to '/oracle/archivelog';

3> restore archivelog from sequence 23;

4> }

恢复数据文件，恢复前创建相关数据文件存放目录

RMAN> run{

3> set newname for datafile '/oracle/oradata/XYSOUL/datafile/o1_mf_system_9jhz3xx6_.dbf' to '+DATA/oradata/xysoul/system01.dbf';

4> set newname for datafile '/oracle/oradata/XYSOUL/datafile/o1_mf_sysaux_9jhz4vkj_.dbf' to '+DATA/oradata/xysoul/sysaux01.dbf';

5> set newname for datafile '/oracle/oradata/XYSOUL/datafile/o1_mf_undotbs1_9jhz5gsj_.dbf' to '+DATA/oradata/xysoul/undotbs1_01.dbf';

6> set newname for datafile '/oracle/oradata/XYSOUL/datafile/o1_mf_users_9jhz6fw2_.dbf' to '+DATA/oradata/xysoul/user01.dbf';

7> set newname for datafile '/oracle/oradata/XYSOUL/datafile/xysoul01.dbf' to '+DATA/oradata/xysoul/xysoul01.dbf';

8> restore database;

9> switch datafile all;

10> }

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 2014-02-24 16:53:13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=27 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to +DATA/oradata/xysoul/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to +DATA/oradata/xysoul/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to +DATA/oradata/xysoul/undotbs1_01.dbf

channel ORA_DISK_1: restoring datafile 00004 to +DATA/oradata/xysoul/user01.dbf

channel ORA_DISK_1: restoring datafile 00005 to +DATA/oradata/xysoul/xysoul01.dbf

channel ORA_DISK_1: reading from backup piece /oracle/backup/full_02p1e2cu_1_1.bak

channel ORA_DISK_1: piece handle=/oracle/backup/full_02p1e2cu_1_1.bak tag=TAG20140224T121629

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:45

Finished restore at 2014-02-24 16:55:59

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=840387360 file name=+DATA/oradata/xysoul/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=7 STAMP=840387360 file name=+DATA/oradata/xysoul/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=8 STAMP=840387360 file name=+DATA/oradata/xysoul/undotbs1_01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=9 STAMP=840387360 file name=+DATA/oradata/xysoul/user01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=10 STAMP=840387360 file name=+DATA/oradata/xysoul/xysoul01.dbf

选择恢复点，还原数据库

RMAN> run{

2> set until scn 827904;

3> recover database;

4> }

executing command: SET until clause

Starting recover at 2014-02-24 16:59:27

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 2014-02-24 16:59:28

启动数据库，查看日志组

SQL> alter database open resetlogs;

Database altered.

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

+DATA/xysoul/onlinelog/group_1.290.840387629

+DATA/xysoul/onlinelog/group_2.304.840387667

+DATA/xysoul/onlinelog/group_3.306.840387705

+DATA/xysoul/onlinelog/group_1.289.840387645

+DATA/xysoul/onlinelog/group_2.305.840387687

+DATA/xysoul/onlinelog/group_3.307.840387723

6 rows selected.

添加日志组，4、5、6

SQL> alter database add logfile thread 2 group 4 '+DATA/xysoul/onlinelog/group_4_01.log' size 200M;

Database altered.

SQL> alter database add logfile member '+DATA/xysoul/onlinelog/group_4_02.log' to group 4;

Database altered.

激活thread 2，并修改实例对应thread

SQL> select THREAD#, STATUS, ENABLED from v$thread;

THREAD# STATUS ENABLED

---------- ------ --------

1 OPEN PUBLIC

2 CLOSED DISABLED

SQL> alter database enable thread 2;

Database altered.

SQL> alter system set thread=1 scope=spfile sid='xysoul1';

System altered.

SQL> alter system set thread=2 scope=spfile sid='xysoul2';

System altered.

设置集群相关参数

SQL> alter system set instance_number=1 scope=spfile sid='xysoul1';

alter system set instance_number=2 scope=spfile sid='xysoul2';

System altered.

SQL>

System altered.

SQL>

SQL> alter system set cluster_database_instances=2 scope=spfile;

System altered.

SQL> alter system set cluster_database=true scope=spfile;

System altered.

添加undo表空间

SQL> create undo tablespace UNDOTBS2 datafile '+DATA/oradata/xysoul/undotbs2.dbf' size 755M;

Tablespace created.

SQL> alter system set undo_tablespace='UNDOTBS2'scope=spfile sid='xysoul2';

System altered.

创建spfile文件，并修改pfile文件（两节点都需修改）

SQL> create spfile='+DATA/xysoul/spfilexysoul.ora' from pfile;

File created.

[oracle@rac01 dbs]$ cat initxysoul1.ora

spfile='+DATA/xysoul/spfilexysoul.ora'

两个节点尝试启动数据库

SQL> startup nomount

ORACLE instance started.

Total System Global Area 534462464 bytes

Fixed Size 2254952 bytes

Variable Size 276826008 bytes

Database Buffers 251658240 bytes

Redo Buffers 3723264 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

下面将数据库添加到集群资源中（此处需注意，添加数据库资源时，需用oracle用户）

[oracle@rac02 dbs]$ srvctl add database -d xysoul -o /oracle/app/oracle/product/11.2.0 -p +DATA/xysoul/spfilexysoul.ora

[oracle@rac02 ~]$ srvctl add instance -d xysoul -i xysoul1 -n rac01

[oracle@rac02 ~]$ srvctl add instance -d xysoul -i xysoul2 -n rac02

启动数据库，并查看（之前已经将两个节点实例关闭）

[grid@rac02 ~]$ srvctl start database -d xysoul

[grid@rac02 ~]$ crs_stat -t

Name Type Target State Host

------------------------------------------------------------

ora.CRS.dg ora....up.type ONLINE ONLINE rac01

ora.DATA.dg ora....up.type ONLINE ONLINE rac01

ora....ER.lsnr ora....er.type ONLINE ONLINE rac01

ora....N1.lsnr ora....er.type ONLINE ONLINE rac02

ora.asm ora.asm.type ONLINE ONLINE rac01

ora.cvu ora.cvu.type ONLINE ONLINE rac02

ora.gsd ora.gsd.type OFFLINE OFFLINE

ora....network ora....rk.type ONLINE ONLINE rac01

ora.oc4j ora.oc4j.type ONLINE ONLINE rac02

ora.ons ora.ons.type ONLINE ONLINE rac01

ora....SM1.asm application ONLINE ONLINE rac01

ora....01.lsnr application ONLINE ONLINE rac01

ora.rac01.gsd application OFFLINE OFFLINE

ora.rac01.ons application ONLINE ONLINE rac01

ora.rac01.vip ora....t1.type ONLINE ONLINE rac01

ora....SM2.asm application ONLINE ONLINE rac02

ora....02.lsnr application ONLINE ONLINE rac02

ora.rac02.gsd application OFFLINE OFFLINE

ora.rac02.ons application ONLINE ONLINE rac02

ora.rac02.vip ora....t1.type ONLINE ONLINE rac02

ora.racdb.db ora....se.type OFFLINE OFFLINE

ora....ry.acfs ora....fs.type ONLINE ONLINE rac01

ora.scan1.vip ora....ip.type ONLINE ONLINE rac02

ora.xysoul.db ora....se.type ONLINE ONLINE rac01

也可通过此命令查看更详细状态
crsctl stat res –t

至此数据库恢复工作已接近完成，根据相关需求请配置监听、优化系统和数据库参数等。

三、总结

在此恢复过程中，也遇到过一些问题，比如，相关目录没有建全、添加集群资源及相关命令不熟悉等导致的问题，在解决过程中我更多的是依赖官方文档，虽然官方也有错误吧，但比一些网络资料要好。虽然我的技术不怎么好，但有一个原则，既然写了，就写的详细点，至少不漏下关键步骤，分享技术、分享快乐，在路上。

【RMAN】oracle11g单机数据通过RMAN恢复至RAC