Oracle 单实例 迁移到 RAC 实例 -- 使用RMAN 异机恢复

Oracle官网有关单实例迁移到RAC的一个步骤说明：

How to Convert 10g Single-Instance database to 10g RAC using Manual Conversion procedure [ID 747457.1]

http://blog.youkuaiyun.com/xujinyang/article/details/6833380

RMAN备份异机恢复并创建新DBID

http://blog.youkuaiyun.com/xujinyang/article/details/6830518

一.大致操作步骤如下：

1.安装Clusterware和ASM实例

2.备份源库，并将备份集copy到rac节点上

3.在RAC上还原并修改初始化参数文件，还原控制文件和数据文件

4.增加undo表空间和redo log线程组，创建密钥文件

5.配置RAC监听

6.将数据库等资源添加到CRS

注意：迁移的2个db版本版本要一致。包括小版本。比如10.2.0.4.0.

我这个测试的时候是从10.2.0.1.0迁移到10.2.0.4.0.结果在open resetlogs的时候报错了，必须要先升级后，才能打开。

Redhat 5.4 Orcle RAC数据库从10.2.0.1升级到10.2.0.4

http://blog.youkuaiyun.com/xujinyang/article/details/6837242

二.具体操作步骤

2.1安装Clusterware和ASM实例

这个参考Blog：

Redhat 5.4 Oracle 10g RAC Openfiler+Multipath + RAW+ ASM安装文档

http://blog.youkuaiyun.com/xujinyang/article/details/6833740

安装之后的进程如下：

[oracle@rac1 u01]$ sh crs_stat.sh

NameTargetStateHost

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

ora.rac1.ASM1.asmONLINEONLINErac1

ora.rac1.gsdONLINEONLINErac1

ora.rac1.onsONLINEONLINErac1

ora.rac1.vipONLINEONLINErac1

ora.rac2.ASM2.asmONLINEONLINErac2

ora.rac2.gsdONLINEONLINErac2

ora.rac2.onsONLINEONLINErac2

ora.rac2.vipONLINEONLINErac2

2.2在源库用RMAN备份，并将备份文件copy到rac上

相关备份脚本，参考Blog：

Linux平台下RMAN全备和增量备份shell脚本

http://blog.youkuaiyun.com/xujinyang/article/details/6838002

Nocatalog下的RMAN增量备份shell脚本

http://blog.youkuaiyun.com/xujinyang/article/details/6830525

源库实例名：anqing，为了确认迁移成功，我们现在源库上创建一个表dave，并插入一条记录。

SQL> create table dave(id number,hometown varchar2(100));

Table created.

SQL> insert into dave values(1,'安徽省安庆市怀宁县');

1 row created.

SQL> commit;

Commit complete.

用nocatalog方式进行0级备份。

备份之后的信息如下：

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups

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

KeyTY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

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

1B0A DISK23-MAY-1211NOANQING_LEV0

2B0A DISK23-MAY-1211NOANQING_LEV0

5B0A DISK23-MAY-1211NOANQING_LEV0

6B0A DISK23-MAY-1211NOANQING_LEV0

7BAA DISK23-MAY-1211NOARC_BAK

8BAA DISK23-MAY-1211NOARC_BAK

9BFA DISK23-MAY-1211NOBAK_CTLFILE

10BFA DISK23-MAY-1211NOSPFILE

注：我虚拟机上的系统时间没改，所以这里显示的时间是2012年5月23日。

将备份文件SCP到RAC服务器：

[oracle@singledb backup]$ scp * 192.168.2.42:/u01/backup

oracle@192.168.2.42's password:

anqing_lev0_01nbo3f3_1_1_20120523100%350MB1.8MB/s03:14

anqing_lev0_02nbo3f3_1_1_20120523100%142MB2.7MB/s00:53

anqing_lev0_03nbo3f3_1_1_20120523100%21MB3.1MB/s00:07

anqing_lev0_04nbo3f4_1_1_20120523100%376KB 376.0KB/s00:00

anqing_spfile_0anbo3jl_1_1_20120523100%96KB96.0KB/s00:00

arch_07nbo3je_1_1_20120523100%28MB3.1MB/s00:09

arch_08nbo3je_1_1_20120523100% 25602.5KB/s00:00

ctl_file_09nbo3jk_1_1_20120523100% 6944KB3.4MB/s00:02

2.3还原并修改初始化文件

2.3.1还原spfile到pfile

[oracle@rac1 ~]$ export ORACLE_SID=anqing1

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed May 18 22:54:27 2011

Copyright (c) 1982, 2007, Oracle.All rights reserved.

connected to target database (not started)

RMAN> startup nomount

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

LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initanqing1.ora'

starting Oracle instance without parameter file for retrival of spfile

Oracle instance started

Total System Global Area159383552 bytes

Fixed Size1266344 bytes

Variable Size58723672 bytes

Database Buffers96468992 bytes

Redo Buffers2924544 bytes

RMAN> restore spfile to pfile '/u01/app/oracle/product/10.2.0/db_1/dbs/initanqing1.ora'

2> from '/u01/backup/anqing_spfile_0anbo3jl_1_1_20120523';

Starting restore at 18-MAY-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=33 devtype=DISK

channel ORA_DISK_1: autobackup found: /u01/backup/anqing_spfile_0anbo3jl_1_1_20120523

channel ORA_DISK_1: SPFILE restore from autobackup complete

Finished restore at 18-MAY-11

RMAN>

看一下我们刚才恢复的参数文件：

[oracle@rac1 backup]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/

[oracle@rac1 dbs]$

[oracle@rac1 dbs]$ ls

ab_+ASM1.dathc_anqing.dathc_dave1.datinitanqing.orainit.oraorapw+ASM1

ab_+ASM2.dathc_+ASM1.dathc_rac1.datinit+ASM1.orainit.ora.4122011215823snapcf_dave1.f

core_10447hc_+ASM2.dathc_rac2.datinitdw.orainitrac1.oraspfilerac1.ora

[oracle@rac1 dbs]$ cat initanqing.ora

anqing.__db_cache_size=96468992

anqing.__java_pool_size=4194304

anqing.__large_pool_size=4194304

anqing.__shared_pool_size=58720256

anqing.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/anqing/adump'

*.background_dump_dest='/u01/app/oracle/admin/anqing/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/anqing/control01.ctl','/u01/app/oracle/oradata/anqing/control02.ctl','/u01/app/oracle/oradata/anqing/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/anqing/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='anqing'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=anqingXDB)'

*.job_queue_processes=10

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/anqing/udump'

这里面都是单实例的参数，我们需要把这个改成RAC的参数。

2.3.2查看ASM实例的相关目录信息

[oracle@rac1 ~]$ export ORACLE_SID=+ASM1

[oracle@rac1 ~]$ sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 18 23:09:32 2011

Copyright (c) 1982, 2007, Oracle.All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SYS@+ASM1(rac1)> col state format a10

SYS@+ASM1(rac1)> col name format a15

SYS@+ASM1(rac1)> col failgroup format a20

SYS@+ASM1(rac1)> set line 200

SYS@+ASM1(rac1)>selectstate,redundancy,total_mb,free_mb,name,failgroup from v$asm_disk;

STATEREDUNDATOTAL_MBFREE_MB NAMEFAILGROUP

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

NORMALUNKNOWN1199310088 DATADATA

NORMALUNKNOWN799338 FRA_0000FRA_0000

SYS@+ASM1(rac1)>selectgroup_number,name,state,type,total_mb,free_mb,unbalancedfrom v$asm_diskgroup;

GROUP_NUMBER NAMESTATETYPETOTAL_MBFREE_MB U

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

1DATAMOUNTEDEXTERN1199310088 N

2FRAMOUNTEDEXTERN799338 N

使用ASMCMD命令：

[oracle@rac1 ~]$ export ORACLE_SID=+ASM1

[oracle@rac1 ~]$ asmcmd

ASMCMD> help

asmcmd [-p] [command]

The environment variables ORACLE_HOME and ORACLE_SID determine the

instance to which the program connects, and ASMCMD establishes a

bequeath connection to it, in the same manner as a SQLPLUS / AS

SYSDBA.The user must be a member of the SYSDBA group.

Specifying the -p option allows the current directory to be displayed

in the command prompt, like so:

ASMCMD [+DATAFILE/ORCL/CONTROLFILE] >

[command] specifies one of the following commands, along with its

parameters.

Type "help [command]" to get help on a specific ASMCMD command.

commands:

--------

cd

du

find

help

ls

lsct

lsdg

mkalias

mkdir

pwd

rm

rmalias

ASMCMD> ls

DATA/

FRA/

--这个是我们的两个磁盘组

ASMCMD> cd DATA

ASMCMD> ls

DAVE/

DB_UNKNOWN/

RAC/

--每个实例都对应一个目录，如果我们在创建参数的时候，没有这个目录，可以手工的先创建一下这个目录

ASMCMD> cd ..

ASMCMD> ls

DATA/

FRA/

ASMCMD> cd FRA

ASMCMD> ls

DAVE/

RAC/

ASMCMD>

2.3.3修改初始话参数

注意事项：

（1）RMAN异机恢复的db_name必须和备份的一致，如果说想改成其他名称，可以等还原之后，在用nid命令修改。

（2）控制文件需要指定到共享设备上

（3）检查audit_file_dest，background_dump_dest，core_dump_dest，log_archive_dest_1，user_dump_dest等参数的位置。如果2个节点和共享位置没有对应的目录，先把目录建好。

[oracle@rac1 admin]$ pwd

/u01/app/oracle/admin

[oracle@rac1 admin]$ mkdir anqing

[oracle@rac1 admin]$ ls

anqing+ASMrac

[oracle@rac1 admin]$ cd anqing

[oracle@rac1 anqing]$ mkdir adump

[oracle@rac1 anqing]$ mkdir bdump

[oracle@rac1 anqing]$ mkdir cdump

[oracle@rac1 anqing]$ mkdir dpdump

[oracle@rac1 anqing]$ mkdir hdump

[oracle@rac1 anqing]$ mkdir pfile

[oracle@rac1 anqing]$ mkdir udump

[oracle@rac1 anqing]$ ll

total 28

drwxr-xr-x 2 oracle oinstall 4096 May 18 23:40 adump

drwxr-xr-x 2 oracle oinstall 4096 May 18 23:40 bdump

drwxr-xr-x 2 oracle oinstall 4096 May 18 23:40 cdump

drwxr-xr-x 2 oracle oinstall 4096 May 18 23:41 dpdump

drwxr-xr-x 2 oracle oinstall 4096 May 18 23:41 hdump

drwxr-xr-x 2 oracle oinstall 4096 May 18 23:41 pfile

drwxr-xr-x 2 oracle oinstall 4096 May 18 23:41 udump

之前一个RAC环境的ASM目录如下，我们可以参考这个目录来建：

ASMCMD> ls

DATA/

FRA/

ASMCMD> cd DATA

--看下DATA目录下的目录层级

ASMCMD> ls

RAC/

ASMCMD>cd RAC

ASMCMD>ls

CONTROLFILE/

DATAFILE/

TEMPFILE/

ONLINELOG/

PARAMETERFILE/

spfilerac.ora

ASMCMD>cd CONTROLFILE

ASMCMD> ls

Current.260.746634201

ASMCMD> cd ..

ASMCMD>cd DATAFILE

ASMCMD> ls

SYSAUX.257.746634087

SYSTEM.256.746634087

UNDOTBS1.258.746634089

UNDOTBS2.264.746634255

USERS.259.746634089

ASMCMD> cd ..

ASMCMD>cd TEMPFILE

ASMCMD> ls

TEMP.263.746634229

ASMCMD> cd ..

ASMCMD> ls

CONTROLFILE/

DATAFILE/

TEMPFILE/

spfilerac.ora

ASMCMD> cd ..

ASMCMD> cd ..

ASMCMD> ls

DATA/

FRA/

ASMCMD>cd FRA

--看下FRA目录下的层级

ASMCMD> ls

RAC/

ASMCMD> cd RAC

ASMCMD> ls

1_95_746989315.arc

1_96_746989315.arc

CONTROLFILE/

--归档文件因为我指定到这个目录了，所以限制在这里，这里可以在建一个目录，专门存放归档文件

ASMCMD> cd CONTROLFILE

ASMCMD> ls

Current.256.746634203

ASMCMD>

官网文档说明需要添加如下参数：

*.cluster_database = TRUE

*.cluster_database_instances = 2

*.undo_management=AUTO

<SID1>.undo_tablespace=undotbs (undo tablespace which already exists)

<SID1>.instance_name=<SID1>

<SID1>.instance_number=1

<SID1>.thread=1

<SID1>.local_listener=<LISTENERNAME>_<HOSTNAME1>

<SID2>.instance_name=<SID2>

<SID2>.instance_number=2

<SID2>.local_listener=<LISTENERNAME>_<HOSTNAME2>

<SID2>.thread=2

<SID2>.undo_tablespace=UNDOTBS2

<SID2>.cluster_database = TRUE

<SID2>.cluster_database_instances = 2

最终我们修改之后的pfile文件如下：

[oracle@rac1 dbs]$ cat initanqing.ora

*.audit_file_dest='/u01/app/oracle/admin/anqing/adump'

*.background_dump_dest='/u01/app/oracle/admin/anqing/bdump'

*.cluster_database_instances=2

*.cluster_database=true

*.compatible='10.2.0.1.0'

*.control_files='+DATA/anqing/controlfile/control01.ctl','+FRA/anqing/controlfile/control02.ctl'

*.core_dump_dest='/u01/app/oracle/admin/anqing/cdump'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='anqing'

*.db_recovery_file_dest='+FRA'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=racXDB)'

anqing1.instance_name='anqing1'

anqing2.instance_name='anqing2'

anqing1.instance_number=1

anqing2.instance_number=2

*.job_queue_processes=10

*.log_archive_dest_1='LOCATION=+FRA/anqing/archivelog'

*.log_archive_format='%t_%s_%r.arc'

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='exclusive'

*.sga_target=285212672

anqing1.thread=1

anqing2.thread=2

*.undo_management='AUTO'

anqing1.undo_tablespace='UNDOTBS1'

anqing2.undo_tablespace='UNDOTBS2'

*.user_dump_dest='/u01/app/oracle/admin/anqing/udump'

2.3.4用修改的pfile来创建spfile，注意放在共享设备上

[oracle@rac1 ~]$ sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 19 00:15:54 2011

Copyright (c) 1982, 2007, Oracle.All Rights Reserved.

Connected to an idle instance.

> startup nomount;

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initanqing1.ora'

>create spfile='+DATA/ANQING/PARAMETERFILE/spfileanqing.ora' from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initanqing1.ora';

File created.

>

到ASM里验证一下：

ASMCMD> pwd

+DATA/ANQING/PARAMETERFILE

ASMCMD> ls

spfileanqing.ora

在所有节点上，修改pfile内容，将其指向共享设备上的spfile：

[oracle@rac1 dbs]$ echo "SPFILE='+DATA/ANQING/PARAMETERFILE/spfileanqing.ora'" > /u01/app/oracle/product/10.2.0/db_1/dbs/initanqing1.ora

[oracle@rac1 dbs]$ cat initanqing.ora

SPFILE='+DATA/ANQING/PARAMETERFILE/spfileanqing.ora'

2.4创建口令文件

在所有节点执行：

[oracle@rac1 dbs]$ orapwd file=?/dbs/orapwanqing1 password=oracle

[oracle@rac2 admin]$ orapwd file=?/dbs/orapwanqing2 password=oracle

2.5还原控制文件

在其中一个节点上执行。

2.5.1用spfile，将DB启动到nomount状态

[oracle@rac1 dbs]$ sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 19 10:29:16 2011

Copyright (c) 1982, 2007, Oracle.All Rights Reserved.

Connected to an idle instance.

> startup nomount;

ORACLE instance started.

Total System Global Area285212672 bytes

Fixed Size1267068 bytes

Variable Size88083076 bytes

Database Buffers192937984 bytes

Redo Buffers2924544 bytes

> show parameter spfile

NAMETYPEVALUE

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

spfile string +DATA/anqing/parameterfile/spfileanqing.ora

这次启动没有报没有参数文件报错了。

2.5.2 RMAN执行对控制文件的恢复

[oracle@rac1 dbs]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu May 19 10:31:55 2011

Copyright (c) 1982, 2007, Oracle.All rights reserved.

connected to target database: anqing (not mounted)

RMAN> restore controlfile from '/u01/backup/ctl_file_09nbo3jk_1_1_20120523';

Starting restore at 19-MAY-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=150 instance=anqing1 devtype=DISK

channel ORA_DISK_1: restoring control file

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

output filename=+DATA/anqing/controlfile/control01.ctl

output filename=+FRA/anqing/controlfile/control02.ctl

Finished restore at 19-MAY-11

RMAN>

这个位置是我们在初始化参数里指定的，到ASM实例里验证下：

ASMCMD> cd FRA

ASMCMD> ls

ANQING/

DAVE/

RAC/

ASMCMD> cd anqing

ASMCMD> ls

ARCHIVELOG/

CONTROLFILE/

ASMCMD> cd controlfile

ASMCMD> ls

control02.ctl

current.429.751545173

这里除了我们指定的，还自动生成了一个，这个control02.ctl是我们current.429.751545173文件的一个别名。我们确认一下：

ASMCMD> ls -l

TypeRedundStripedTimeSysName

Ncontrol01.ctl => +DATA/ANQING/CONTROLFILE/current.272.751545171

CONTROLFILEUNPROTFINEMAY 19 10:00:00Ycurrent.272.751545171

有关ASM别名，参考我的Blog：Oracle ASM详解

http://blog.youkuaiyun.com/xujinyang/article/details/6837288

ASM文件名字的格式是固定的：+group/dbname/file type/tag.file.incarnation

在创建db时系统自动创建的几个表空间(system,undotbs,sysaux,users)对应的都是真实的数据文件，即ASM文件默认的命名格式。而且这个信息都写到了控制文件里。

如果我们使用别名的话，会方便很多。对于这些创建数据库时自动创建的表空间，我们要他们使用别名，除了手工创建对应别名外，还需要重建控制文件，并且在重建时，datafile里写别名的信息。这样数据库也就使用别名了。

对这个现象的一个说明：

ASM有自己的文件固定格式，如果我们在指定参数或者文件名时，指定了具体的文件名，那么这个文件名会以别名的方式指向ASM中的文件。

2.6 restore数据库

在其中一个节点执行

2.6.1将数据库启动到MOUNT状态

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

2.6.2查看源库数据文件存储位置信息

注意一点，我们单实例和RAC实例存储数据文件的位置不同，如果我们使用rman的duplicate，那么我们使用log_file_name_convert和db_file_name_convert来进行转换，在这个实验中，我们使用的是RMAN的异机恢复，所以只能在restore的时候用set newname来进行转换。

SQL> set linesize 80

SQL> select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME

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

4 /u01/app/oracle/oradata/anqing/users01.dbf

3 /u01/app/oracle/oradata/anqing/sysaux01.dbf

2 /u01/app/oracle/oradata/anqing/undotbs01.dbf

1 /u01/app/oracle/oradata/anqing/system01.dbf

SQL> select file_id,file_name from dba_temp_files;

FILE_ID FILE_NAME

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

1 /u01/app/oracle/oradata/anqing/temp01.dbf

2.6.3在RAC上restore数据文件

run {

set newname for datafile 1 to '+DATA/anqing/DATAFILE/system01.dbf';

set newname for datafile 2 to '+DATA/anqing/DATAFILE/undotbs01.dbf';

set newname for datafile 3 to '+DATA/anqing/DATAFILE/sysaux01.dbf';

set newname for datafile 4 to '+DATA';

set newname for tempfile 1 to '+DATA/anqing/TEMPFILE/temp01.dbf';

restore database;

switch datafile all;

switch tempfile all;

}

注意：

（1）我们datafile 3和4的区别，datafile 4我没有指定具体的文件名，那么在还原时会使用ASM自己的命名方式来命名datafile4.其他的文件会创建别名。

（2）对switch的说明：

对于nocatalog模式下，rman备份的信息是保存在控制文件里的，包括文件的路径信息。这里的switch的作用，就是更新控制文件里的信息。

（3）restore的时候不会对temp表空间进行restore。所以等restore之后，我们需要手工创建temp表空间。

不过在这个测试里，我们还是对tempfile进行了指定。但是这个操作只更新控制文件，不恢复数据文件。

脚本执行结果如下：

RMAN> run {

2> set newname for datafile 1 to '+DATA/anqing/DATAFILE/system01.dbf';

3> set newname for datafile 2 to '+DATA/anqing/DATAFILE/undotbs01.dbf';

4> set newname for datafile 3 to '+DATA/anqing/DATAFILE/sysaux01.dbf';

5> set newname for datafile 4 to '+DATA';

6> set newname for tempfile 1 to '+DATA/anqing/TEMPFILE/temp01.dbf';

restore database;

7> 8> switch datafile all;

9> switch tempfile all;

10> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 19-MAY-11

using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00004

input datafile copy recid=1 stamp=751548119 filename=/u01/app/oracle/product/10.2.0/db_1/dbs/ +DATA

destination for restore of datafile 00004: +DATA

channel ORA_DISK_1: copied datafile copy of datafile 00004

output filename=+DATA/anqing/datafile/users.273.751548233 recid=2 stamp=751548232

channel ORA_DISK_1: starting datafile backupset restore

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

restoring datafile 00002 to +DATA/anqing/datafile/undotbs01.dbf

channel ORA_DISK_1: reading from backup piece /u01/backup/anqing_lev0_03nbo3f3_1_1_20120523

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/backup/anqing_lev0_03nbo3f3_1_1_20120523 tag=ANQING_LEV0

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

channel ORA_DISK_1: starting datafile backupset restore

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

restoring datafile 00003 to +DATA/anqing/datafile/sysaux01.dbf

channel ORA_DISK_1: reading from backup piece /u01/backup/anqing_lev0_02nbo3f3_1_1_20120523

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/backup/anqing_lev0_02nbo3f3_1_1_20120523 tag=ANQING_LEV0

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

channel ORA_DISK_1: starting datafile backupset restore

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

restoring datafile 00001 to +DATA/anqing/datafile/system01.dbf

channel ORA_DISK_1: reading from backup piece /u01/backup/anqing_lev0_01nbo3f3_1_1_20120523

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/backup/anqing_lev0_01nbo3f3_1_1_20120523 tag=ANQING_LEV0

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

Finished restore at 19-MAY-11

datafile 1 switched to datafile copy

input datafile copy recid=6 stamp=751548297 filename=+DATA/anqing/datafile/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=7 stamp=751548297 filename=+DATA/anqing/datafile/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=8 stamp=751548297 filename=+DATA/anqing/datafile/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=9 stamp=751548297 filename=+DATA/anqing/datafile/users.273.751548233

renamed temporary file 1 to +DATA/anqing/TEMPFILE/temp01.dbf in control file

--从这里可以看到，对tempfile仅仅是rename。即更新控制文件。

到ASM实例里查看一下：

ASMCMD> cd DATAFILE

ASMCMD> ls

SYSAUX.275.751548237

SYSTEM.276.751548261

UNDOTBS1.274.751548233

USERS.273.751548233

sysaux01.dbf

system01.dbf

undotbs01.dbf

ASMCMD>

ASMCMD> ls -l

TypeRedundStripedTimeSysName

DATAFILEUNPROTCOARSEMAY 19 11:00:00YSYSAUX.275.751548237

DATAFILEUNPROTCOARSEMAY 19 11:00:00YSYSTEM.276.751548261

DATAFILEUNPROTCOARSEMAY 19 11:00:00YUNDOTBS1.274.751548233

DATAFILEUNPROTCOARSEMAY 19 11:00:00YUSERS.273.751548233

Nsysaux01.dbf => +DATA/ANQING/DATAFILE/SYSAUX.275.751548237

Nsystem01.dbf => +DATA/ANQING/DATAFILE/SYSTEM.276.751548261

Nundotbs01.dbf => +DATA/ANQING/DATAFILE/UNDOTBS1.274.751548233

--映射别名

ASMCMD> cd tempfile

ASMCMD> ls

ASMCMD>

--tempfile对应的目录为空，即没有恢复数据文件。

2.8 recover数据库

在执行restore的节点执行，因为备份文件在该节点上。

RMAN> recover database;

Starting recover at 19-MAY-11

using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=3

channel ORA_DISK_1: reading from backup piece /u01/backup/arch_08nbo3je_1_1_20120523

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/backup/arch_08nbo3je_1_1_20120523 tag=ARC_BAK

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

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=2

channel ORA_DISK_1: reading from backup piece /u01/backup/arch_07nbo3je_1_1_20120523

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/backup/arch_07nbo3je_1_1_20120523 tag=ARC_BAK

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

archive log filename=+FRA/anqing/archivelog/1_2_784072335.arc thread=1 sequence=2

archive log filename=+FRA/anqing/archivelog/1_3_784072335.arc thread=1 sequence=3

unable to find archive log

archive log thread=1 sequence=4

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 05/19/2011 12:13:52

RMAN-06054: media recovery requesting unknown log: thread 1 seq 4 lowscn 460025

最后一行的错误说明：

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 9 and starting SCN of 823627

这里是提醒恢复到一个未知的scn号。在alter database mount之后，通过set until scn或者set until time命令设置恢复到的scn号或时间。就可以避免这个错误。

2.9处理online redo

因为此时另一个节点还没有配好，所以我们只能在执行恢复的节点上处理redo。

[oracle@rac1 backup]$ sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 19 12:19:08 2011

Copyright (c) 1982, 2007, Oracle.All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SYS@anqing1(rac1)> set wrap off;

SYS@anqing1(rac1)> select * from v$logfile;

rows will be truncated

GROUP# STATUSTYPEMEMBER

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

3ONLINE/u01/app/oracle/oradata/anqing/redo03.log

2ONLINE/u01/app/oracle/oradata/anqing/redo02.log

1ONLINE/u01/app/oracle/oradata/anqing/redo01.log

SYS@anqing1(rac1)>alter database rename file '/u01/app/oracle/oradata/anqing/redo01.log' to '+DATA';

+DATA/anqing/onlinelog/redo02.log

SYS@anqing1(rac1)> alter database rename file '/u01/app/oracle/oradata/anqing/redo02.log' to '+DATA/anqing/onlinelog/redo02.log'; SYS@anqing1(rac1)> alter database rename file '/u01/app/oracle/oradata/anqing/redo03.log' to '+DATA/anqing/onlinelog/redo03.log';

SYS@anqing1(rac1)> select * from v$logfile;

rows will be truncated

GROUP# STATUSTYPEMEMBER

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

3ONLINE+DATA/anqing/onlinelog/redo03.log

2ONLINE+DATA/anqing/onlinelog/redo02.log

1ONLINE+DATA

ASMCMD> pwd

+DATA/anqing/onlinelog

ASMCMD> ls

ASMCMD>

注意这个时候，对应目录还是空的，当我们open db的时候，oracle会自动创建online redo log。我们这里修改的目的就是改变online redo的位置。

2.10open resetlogs打开DB

在恢复的节点执行该操作。

SYS@anqing1(rac1)> alter database open resetlogs;

我这里因为版本的问题,报错了，升级之后才成功open.

SYS@anqing1(rac1)> select name,open_mode from v$database;

NAMEOPEN_MODE

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

ANQINGREAD WRITE

SYS@anqing1(rac1)> select comp_name, version, status from sys.dba_registry;

COMP_NAMEVERSIONSTATUS

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

Oracle Database Catalog Views10.2.0.4.0VALID

Oracle Database Packages and T 10.2.0.4.0VALID

Oracle Workspace Manager10.2.0.4.3VALID

JServer JAVA Virtual Machine10.2.0.4.0VALID

Oracle XDK10.2.0.4.0VALID

Oracle Database Java Packages10.2.0.4.0VALID

Oracle Expression Filter10.2.0.4.0VALID

Oracle Data Mining10.2.0.4.0VALID

Oracle Text10.2.0.4.0VALID

Oracle XML Database10.2.0.4.0VALID

Oracle Rule Manager10.2.0.4.0VALID

COMP_NAMEVERSIONSTATUS

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

Oracle interMedia10.2.0.4.0VALID

OLAP Analytic Workspace10.2.0.4.0VALID

Oracle OLAP API10.2.0.4.0VALID

OLAP Catalog10.2.0.4.0VALID

Spatial10.2.0.4.0VALID

Oracle Enterprise Manager10.2.0.4.0VALID

17 rows selected.

因为我们在控制文件里已经指定了online redo和temp file。所以在open resetlog的时候会检查这些文件，如果不在，会自动创建这2个文件。

2.11检查并修改几个初始化参数

SYS@anqing1(rac1)> select * from v$option where parameter = 'Real Application Clusters';

PARAMETERVALUE

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

Real Application ClustersTRUE

SYS@anqing1(rac1)> show parameter cluster

NAMETYPEVALUE

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

cluster_databasebooleanFALSE

cluster_database_instancesinteger1

cluster_interconnectsstring

SYS@anqing1(rac1)> show parameter thread

NAMETYPEVALUE

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

parallel_threads_per_cpuinteger2

threadinteger1

SYS@anqing1(rac1)> show parameter instance_number

NAMETYPEVALUE

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

instance_numberinteger1

　　由上述返回结果可知，RAC特性是支持的，不过尚未启用集群数据库，因此接下来首先要改的，就是enable CLUSTER DATABASE，操作如下：

SYS@anqing1(rac1)> alter system set cluster_database=true scope=spfile;

System altered.

修改2个节点的配置参数：

SYS@anqing1(rac1)> alter system set cluster_database_instances=2 scope=spfile;

System altered.

SYS@anqing1(rac1)>alter system set instance_number=1 scope=spfile sid='anqing1';

System altered.

SYS@anqing1(rac1)>alter system set instance_number=2 scope=spfile sid='anqing2';

System altered.

SYS@anqing1(rac1)>alter system set thread=1 scope=spfile sid='anqing1';

System altered.

SYS@anqing1(rac1)>alter system set thread=2 scope=spfile sid='anqing2';

System altered.

2.12创建节点2的undo表空间

SYS@anqing1(rac1)> show parameter undo_tablespace

NAMETYPEVALUE

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

undo_tablespacestringUNDOTBS1

这个是rac1上节点的信息，我们在之前的在spfile参数指定的rac2节点的undo空间是UNDOTBS2.

所以这里我们创建该undo表空间，并指定相关参数。

SYS@anqing1(rac1)> create undo tablespace UNDOTBS2 datafile '+DATA/ANQING/datafile/undotbs02.dbf' size 500m;

Tablespace created.

--这里我们直接指定别名

ASMCMD> pwd

+DATA/ANQING/datafile

ASMCMD> ls

SYSAUX.275.751548237

SYSTEM.276.751548261

UNDOTBS1.274.751548233

UNDOTBS2.281.751559213

USERS.273.751548233

sysaux01.dbf

system01.dbf

undotbs01.dbf

undotbs02.dbf

SYS@anqing1(rac1)> alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='anqing2';

System altered.

2.13添加rac2节点的redo文件

rac的redo是接其他节点来的，我们之前的rac1上已经有3组，所以我们这里从4开始，在添加2组给rac2，使用thread 2.

SYS@anqing1(rac1)> alter database add logfile thread 2 group 4 '+DATA' size 100m;

Database altered.

SYS@anqing1(rac1)>alter database add logfile thread 2 group 5 '+DATA' size 100m;

Database altered.

SYS@anqing1(rac1)> alter database enable thread 2;

Database altered.

2.14重启节点1和节点2上的实例，使相关参数生效

节点1：

SYS@anqing1(rac1)> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@anqing1(rac1)> startup

ORACLE instance started.

Total System Global Area285212672 bytes

Fixed Size1267068 bytes

Variable Size146803332 bytes

Database Buffers134217728 bytes

Redo Buffers2924544 bytes

Database mounted.

Database opened.

SYS@anqing1(rac1)> show parameter cluster

NAMETYPEVALUE

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

cluster_databasebooleanTRUE

cluster_database_instancesinteger2

cluster_interconnectsstring

SYS@anqing1(rac1)>

节点2：

[oracle@rac2 dbs]$ sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 19 14:50:06 2011

Copyright (c) 1982, 2007, Oracle.All Rights Reserved.

Connected to an idle instance.

> startup

ORACLE instance started.

Total System Global Area285212672 bytes

Fixed Size1267068 bytes

Variable Size88083076 bytes

Database Buffers192937984 bytes

Redo Buffers2924544 bytes

Database mounted.

Database opened.

> show parameter cluster

NAMETYPEVALUE

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

cluster_databasebooleanTRUE

cluster_database_instancesinteger2

cluster_interconnectsstring

最后确认：

SYS@anqing1(rac1)> select instance_number,instance_name,host_name from gv$instance;

INSTANCE_NUMBER INSTANCE_NAMEHOST_NAME

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

2 anqing2rac2

1 anqing1rac1

到现在已经是集群了，不过还有一些收尾的工作要做。

2.15执行catclust.sql脚本来创建相关视图

$ORACLE_HOME/rdbms/admin/catclust.sql to create cluster database specific views within the existing instance

SYS@anqing1(rac1)> @$ORACLE_HOME/rdbms/admin/catclust.sql

2.16重建Temp表空间

在恢复的时候，会自动创建TEMP表空间。和一个前辈聊到过这个问题，他说这个temp文件好像没有起到作用。尤其是数据量大的时候，他建议重建添加temp数据文件。这个还是小心为好，反正重建一些也不费什么事。但是万一出问题，就是大麻烦。

步骤如下：

SYS@anqing1(rac1)> select name from v$tempfile;

NAME

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

+DATA/anqing/tempfile/temp01.dbf

SYS@anqing1(rac1)> alter tablespace temp add tempfile '+DATA/anqing/tempfile/temp02.dbf' size 100M;

Tablespace altered.

SYS@anqing1(rac1)> select name from v$tempfile;

NAME

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

+DATA/anqing/tempfile/temp01.dbf

+DATA/anqing/tempfile/temp02.dbf

SYS@anqing1(rac1)> alter database tempfile '+DATA/anqing/tempfile/temp01.dbf' offline;

Database altered.

SYS@anqing1(rac1)> alter database tempfile '+DATA/anqing/tempfile/temp01.dbf' drop including datafiles;

Database altered.

SYS@anqing1(rac1)>select name from v$tempfile;

NAME

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

+DATA/anqing/tempfile/temp02.dbf

2.17添加集群的监听

使用nomachine连上任一节点，用netca配置一下。参考：

远程桌面工具-- NoMachine

http://blog.youkuaiyun.com/xujinyang/article/details/6924796

添加监听之前：

[oracle@rac1 u01]$ sh crs_stat.sh

NameTargetStateHost

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

ora.rac1.ASM1.asmONLINEONLINErac1

ora.rac1.gsdONLINEONLINErac1

ora.rac1.onsONLINEONLINErac1

ora.rac1.vipONLINEONLINErac1

ora.rac2.ASM2.asmONLINEONLINErac2

ora.rac2.gsdONLINEONLINErac2

ora.rac2.onsONLINEONLINErac2

ora.rac2.vipONLINEONLINErac2

添加监听之后：

[oracle@rac1 u01]$ sh crs_stat.sh

NameTargetStateHost

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

ora.rac1.ASM1.asmONLINEONLINErac1

ora.rac1.LISTENER_RAC1.lsnrONLINEONLINErac1

ora.rac1.gsdONLINEONLINErac1

ora.rac1.onsONLINEONLINErac1

ora.rac1.vipONLINEONLINErac1

ora.rac2.ASM2.asmONLINEONLINErac2

ora.rac2.LISTENER_RAC2.lsnrONLINEONLINErac2

ora.rac2.gsdONLINEONLINErac2

ora.rac2.onsONLINEONLINErac2

ora.rac2.vipONLINEONLINErac2

2.18将其他信息注册到CRS里

[oracle@rac1 u01]$ srvctl add database -d anqing -o $ORACLE_HOME -p +DATA/ANQING/PARAMETERFILE/spfileanqing.ora

[oracle@rac1 u01]$ srvctl add instance -d anqing -i anqing1 -n rac1

[oracle@rac1 u01]$ srvctl add instance -d anqing -i anqing2 -n rac2

修改instance和asm之间的依赖关系:

srvctl modify instance -d <db_unique_name> -i <SID1> -s <+ASM1>

[oracle@rac1 u01]$ srvctl modify instance -d anqing -i anqing1 -s +ASM1

[oracle@rac1 u01]$ srvctl modify instance -d anqing -i anqing2 -s +ASM2

在来查看：

[oracle@rac1 u01]$ sh crs_stat.sh

NameTargetStateHost

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

ora.anqing.anqing1.instOFFLINEOFFLINE

ora.anqing.anqing2.instOFFLINEOFFLINE

ora.anqing.dbOFFLINEOFFLINE

ora.rac1.ASM1.asmONLINEONLINErac1

ora.rac1.LISTENER_RAC1.lsnrONLINEONLINErac1

ora.rac1.gsdONLINEONLINErac1

ora.rac1.onsONLINEONLINErac1

ora.rac1.vipONLINEONLINErac1

ora.rac2.ASM2.asmONLINEONLINErac2

ora.rac2.LISTENER_RAC2.lsnrONLINEONLINErac2

ora.rac2.gsdONLINEONLINErac2

ora.rac2.onsONLINEONLINErac2

ora.rac2.vipONLINEONLINErac2

这里并没有启动，因为我们的服务是刚添加上来的，还没有同步。我们启动一下就ok了。

[oracle@rac1 u01]$ srvctl start database -d anqing

启动之后在查，各个进程正常：

[oracle@rac1 u01]$ sh crs_stat.sh

NameTargetStateHost

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

ora.anqing.anqing1.instONLINEONLINErac1

ora.anqing.anqing2.instONLINEONLINErac2

ora.anqing.dbONLINEONLINErac1

ora.rac1.ASM1.asmONLINEONLINErac1

ora.rac1.LISTENER_RAC1.lsnrONLINEONLINErac1

ora.rac1.gsdONLINEONLINErac1

ora.rac1.onsONLINEONLINErac1

ora.rac1.vipONLINEONLINErac1

ora.rac2.ASM2.asmONLINEONLINErac2

ora.rac2.LISTENER_RAC2.lsnrONLINEONLINErac2

ora.rac2.gsdONLINEONLINErac2

ora.rac2.onsONLINEONLINErac2

ora.rac2.vipONLINEONLINErac2

2.19配置RAC的Failover和Loadbalance

这两块的理论知识参考我的Blog：

Oracle RAC LoadBalance

http://blog.youkuaiyun.com/xujinyang/article/details/6829635

Oracle RAC Failover详解

http://blog.youkuaiyun.com/xujinyang/article/details/6829647

在这里我们配置一下Client-side的TAF.具体步骤参考：

local_listener与remote_listener参数说明

http://blog.youkuaiyun.com/xujinyang/article/details/6831347

中的第三小节。

2.20最后创建查看一下我们开始创建的表：

C:/Users/Administrator.DavidDai>sqlplus sys/oracle@rac as sysdba;

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 19 16:05:57 2011

Copyright (c) 1982, 2010, Oracle.All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> set wrap off;

SQL> set linesize 80

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

anqing1

SQL> select * from dave;

ID HOMETOWN

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

1安徽省安庆市怀宁县

单实例迁移到RAC到此结束。步骤还是有点多，所以在操作的时候，仔细一点。

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