oracle11g单实例数据库迁移到RAC集群上

来源：这里教程网时间：2026-03-03 14:00:54 作者：

系统环境：centos6 数据库软件版本：oracle11g 目标：单实例数据库迁移到RAC集群上过程：首先为单实例数据库搭建ADG到rac一个节点上，然后通过切换主备库将单实例变成备库，rac成为主库，启用第二个节点。一、搭建单实例数据库二、搭建rac环境装GI软件装oracle软件不创建数据库三、搭建DG架构 1. 主库准备 1.1 检查主库是否是开启归档模式开启归档 #开启归档模式 #一致性停库 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. #开启到mount状态 SQL> startup mount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 541068368 bytes Database Buffers 289406976 bytes Redo Buffers 2371584 bytes Database mounted. #打开归档模式 SQL> alter database archivelog; Database altered. #打开数据库 SQL> alter database open; Database altered. #修改归档路径 SQL> alter system set log_archive_dest_1='location=/u02/arcdata'; System altered. #检查归档是否开启 archive log list 1.2 启用强日志模式 SQL> alter database force logging; Database altered. 1.3 设置DG相关参数 #检查remote_login_passwordfile 参数（EXCLUSIVE） SQL> show parameter remote_login_passwordfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string EXCLUSIVE #检查standby_file_management 参数（AUTO） SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string MANUAL #不是AUTO，修改为AUTO SQL> alter system set standby_file_management=AUTO scope=both; System altered. #检查db_unique_name （有唯一名） SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string orasgl #打开DG开关（orasgl为本库唯一名，orarac为RAC库唯一名） SQL> alter system set log_archive_config='dg_config=(orasgl,orarac)'; System altered. #配置本地归档 SQL> alter system set log_archive_dest_1='location=/home/oracle/archlog valid_for=(all_logfiles,all_roles) db_unique_name=orasgl'; System altered. #激活归档路径1 SQL> alter system set log_archive_dest_state_1='enable'; System altered. #配置远程归档 SQL> alter system set log_archive_dest_2='service=orarac valid_for=(online_logfiles,primary_role) db_unique_name=orarac'; System altered. #激活归档路径2 SQL> alter system set log_archive_dest_state_2='enable'; System altered. #配置fal_client、fal_server SQL> alter system set fal_client='orasgl'; System altered. SQL> alter system set fal_server='orarac'; System altered. #配置文件路径转换（前面为远程库的数据文件路径，后面为本地库的数据文件路径） SQL> alter system set db_file_name_convert='+RACDATA/oradata/orarac/','/u01/app/oracle/oradata/orasgl/' scope=spfile; System altered. SQL> alter system set log_file_name_convert='+RACDATA/oradata/orarac/','/u01/app/oracle/oradata/orasgl/' scope=spfile; System altered. #重启数据库，检查scope=spfile选项的配置 SQL> show parameter convert; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string +RACDATA/oradata/orarac/, /u01/app/or acle/oradata/orasgl/ log_file_name_convert string +RACDATA/oradata/orarac/, /u01/app/or acle/oradata/orasgl/ 1.4 将参数文件、密码文件传输给RAC库 #创建pfile文件 SQL> create pfile from spfile; File created. #将参数文件传输给rac1节点 cd $ORACLE_HOME/dbs #将密码文件传输给rac1、rac2节点 scp orapworasgl rac1：$ORACLE_HOME/dbs scp orapworasgl RAC2: $ORACLE_HOME/dbs 2. 备库准备（rac1） 2.1 将rac1、rac2节点的文件拷贝到指定位置 #rac1节点 mv orapwRAC1 initorasgl.ora $ORACLE_HOME/dbs mv orapwRAC1 $ORACLE_HOME/dbs #根据SID修改文件名 #rac1节点 [oracle@rac1 ~]$ cd $ORACLE_HOME/dbs [oracle@rac1 dbs]$ mv initorasgl.ora initorarac1.ora [oracle@rac1 dbs]$ mv orapworasgl orapworarac1 #rac2节点 [oracle@rac2 ~]$ cd $ORACLE_HOME/dbs [oracle@rac2 dbs]$ mv orapworasgl orapworarac2 2.2 将pfile修改为适用于备库的参数文件 #依照如下配置做修改 [oracle@rac1 dbs]$ vim initorarac1.ora -------------------------------------------- *.db_domain='' *.db_recovery_file_dest_size=4385144832 *.dispatchers='(PROTOCOL=TCP) (SERVICE=orasglXDB)' *.memory_target=838860800 *.open_cursors=300 *.processes=150 *.undo_tablespace='UNDOTBS1' #以下参数需要注意 #该参数为数据库名，不可修改 *.db_name='orasgl' #唯一名，修改为RAC库的唯一名 *.db_unique_name='orarac' #审计文件所在路径 *.audit_file_dest='/u01/app/oracle/admin/orarac/adump' #自诊断档案库文件所在路径 *.diagnostic_dest='/u01/app/oracle' #控制文件放于ASM磁盘组对应位置 *.control_files='+RACDATA/oradata/orarac/control01.ctl','+RACFRA/oracle/fast_recovery_area/orarac/control02.ctl' #快速恢复区对应路径（直接用磁盘组） *.db_recovery_file_dest='+RACFRA' #DG配置，不用改 *.log_archive_config='dg_config=(orasgl,orarac)' #本地归档路径修改为ASM磁盘组路径，唯一名修改为RAC库唯一名 *.log_archive_dest_1='location=+RACFRA/oracle/archlog valid_for=(all_logfiles,all_roles) db_unique_name=orarac' #服务名和唯一名修改为单实例库对应的名字 *.log_archive_dest_2='service=orasgl valid_for=(online_logfiles,primary_role) db_unique_name=orasgl' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' #修改为RAC库唯一名 *.fal_client='orarac' #修改为单实例库唯一名 *.fal_server='orasgl' #与单实例库配置正好相反 *.db_file_name_convert='/u01/app/oracle/oradata/orasgl/','+RACDATA/oradata/orarac/' #与单实例库配置正好相反 *.log_file_name_convert='/u01/app/oracle/oradata/orasgl/','+RACDATA/oradata/orarac/' *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' -------------------------------------------- 2.3 根据参数文件中出现的路径，创建对应的目录 #本地目录 #rac1节点 [oracle@rac1 dbs]$ mkdir -p /u01/app/oracle/admin/orarac/adump #rac2节点 [oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/orarac/adump #ASM磁盘组目录（切换至grid用户，在其中一个节点上操作即可） ********************************************* 注：在ASM创建相应目录（归档地址，数据文件，控制文件）很重要rman恢复的时候没有目录会报错 ********************************************* [grid@rac2 ~]$ asmcmd ASMCMD> cd +RACDATA ASMCMD> mkdir oradata ASMCMD> cd oradata ASMCMD> mkdir orarac #通过命令检查配置文件中涉及到的目录是否均已创建 #rac1节点 [oracle@rac1 dbs]$ ll /u01/app/oracle/admin/orarac/adump #rac2节点 [oracle@rac2 ~]$ ll /u01/app/oracle/admin/orarac/adump 2.4 在rac1节点通过pfile启动数据库到nomount状态 [oracle@rac1 dbs]$ sqlplus / as sysdba startup nomount 3.配置监听 3.1 配置主库监听（xxdb1）配置主库的listener.ora文件 cd $ORACLE_HOME/network/admin [oracle@xxdb1 admin]$ vi listener.ora ------------------------------------------- LISTENER= (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.80.11)(PORT=1521)) ) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=xxdb1) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME=xxdb1) ) ) ------------------------------------------- 启动监听 $ lsnrctl start 配置主库的tnsnames.ora文件 $ vi tnsnames.ora ------------------------------------------- xxdb1= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.80.11)(PORT=1521)) ) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=xxdb1) ) ) rac= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.80.12)(PORT=1521)) ) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=rac) ) ) ------------------------------------------- 3.2 配置RAC集群监听（ rac）配置备库的listener.ora文件注意RAC集群的监听需在grid用户下配置（节点1配置） #加入静态监听配置 [grid@rac1 ~]$ cd /u01/grid/network/admin/ [grid@rac1 admin]$ vim listener.ora ------------------------------------- SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=orarac) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) #此处为oracle的ORACLE_HOME (SID_NAME=orarac1) ) ) ------------------------------------- 重载监听 [grid@rac1 admin]$ lsnrctl reload LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-DEC-2018 19:19:45 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) The command completed successfully [grid@rac1 admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-DEC-2018 19:19:48 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 24-DEC-2018 13:04:17 Uptime 0 days 6 hr. 15 min. 30 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/rac1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) Services Summary... Service "orarac" has 1 instance(s). Instance "orarac1", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully 配置备库的tnsnames.ora文件在oracle用户下建 [oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/ [oracle@rac1 admin]$ vi tnsnames.ora ------------------------------------- orasgl= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=172.17.10.209)(PORT=1521)) ) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=orasgl) ) ) orarac= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=172.17.10.203)(PORT=1521)) ) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=orarac) ) ) ------------------------------------- 配置完成后传输给rac2节点一份 [oracle@rac1 admin]$ scp tnsnames.ora rac2:/u01/app/oracle/product/11.2.0/db_1/network/admin/ tnsnames.ora 100% 388 0.4KB/s 00:00 3.3 监听连接测试单实例测试： [oracle@xxdb1 ~]$ sqlplus sys/oracle@xxdb1 as sysdba show parameter db_unique_name [oracle@xxdb1 ~]$ sqlplus sys/oracle@rac as sysdba show parameter db_unique_name 集群测试： [oracle@rac1 ~]$ sqlplus sys/oracle@xxdb1 as sysdba show parameter db_unique_name [oracle@rac1 ~]$ sqlplus sys/oracle@rac as sysdba SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string rac 4. 数据复制在单实例数据库上通过RMAN连接单实例数据和RAC集群 [oracle@xxdb1 ~]$ rman target sys/oracle@xxdb1 auxiliary sys/oracle@rac RMAN> duplicate target database for standby from active database; ... ... #过程略 #复制脚本自动运行完exit退出rman即可 #此时rac1节点库已经是mount状态可以登录grid用户，查看文件是否复制正确（主要看数据文件和归档文件） [root@rac2 ~]# su - grid [grid@rac2 ~]$ asmcmd ASMCMD> cd +RACDATA/oradata/orarac ASMCMD> ls control01.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf 检查归档是否可以正常传输 #主库操作（xxdb1） SQL> select group#,sequence#,status from v$log; #切换日志组 SQL> alter system switch logfile; System altered. #查看归档是否生成 #备库操作（rac） grid用户asmcmd查看产生的归档日志是否正确 #验证成功，归档可以正常传输 5. 添加日志组 5.1 主库（xxdb1）添加日志组（为主库切换为备库做准备） SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string AUTO #先改为手动模式 SQL> alter system set standby_file_management=manual; System altered. SQL> select a.group#,member,thread#,bytes/1024/1024 MB from v$logfile a, v$log b where a.group#=b.group#; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ alter system set standby_file_management=manual; alter database add standby logfile thread 1 '/u01/app/oracle/oradata/xxdb1/standby01.log' size 50m; alter database add standby logfile thread 1 '/u01/app/oracle/oradata/xxdb1/standby02.log' size 50m; alter database add standby logfile thread 1 '/u01/app/oracle/oradata/xxdb1/standby03.log' size 50m; alter database add logfile thread 2 '/u01/app/oracle/oradata/xxdb1/redo04.log' size 50m; alter database add logfile thread 2 '/u01/app/oracle/oradata/xxdb1/redo05.log' size 50m; alter database add logfile thread 2 '/u01/app/oracle/oradata/xxdb1/redo06.log' size 50m; alter database add standby logfile thread 2 '/u01/app/oracle/oradata/xxdb1/standby04.log' size 50m; alter database add standby logfile thread 2 '/u01/app/oracle/oradata/xxdb1/standby05.log' size 50m; alter database add standby logfile thread 2 '/u01/app/oracle/oradata/xxdb1/standby06.log' size 50m; alter database enable thread 2; alter system set standby_file_management=auto; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 备库rac添加standby日志 alter database add standby logfile thread 1 '+RACDATA/oradata/rac/standby01.log' size 50m; alter database add standby logfile thread 1 '+RACDATA/oradata/rac/standby02.log' size 50m; alter database add standby logfile thread 1 '+RACDATA/oradata/rac/standby03.log' size 50m; alter database add logfile thread 2 '+RACDATA/oradata/rac/redo04.log' size 50m; alter database add logfile thread 2 '+RACDATA/oradata/rac/redo05.log' size 50m; alter database add logfile thread 2 '+RACDATA/oradata/rac/redo06.log' size 50m; alter database add standby logfile thread 2 '+RACDATA/oradata/rac/standby04.log' size 50m; alter database add standby logfile thread 2 '+RACDATA/oradata/rac/standby05.log' size 50m; alter database add standby logfile thread 2 '+RACDATA/oradata/rac/standby06.log' size 50m; alter system set standby_file_management=auto; 6.打开数据库，测试数据同步 #在rac1节点操作 #打开数据库 SQL> alter database open; Database altered. #开启日志实时应用 SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. #检查日志同步情况 SQL> select sequence#,applied from v$archived_log order by 1; #在主库（xxdb1）操作更新表，检查同步 #在备库（rac1）检查同步情况 #可以通过以下SQL检查DG备库运行状态 SQL> select * from v$dataguard_stats; 8. 创建spfile到ASM磁盘组在rac1节点操作 #通过pfile创建spfile create spfile='+RACDATA/oradata/spfilerac.ora' from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initrac1.ora'; #修改rac1节点的pfile #备份原pfile [oracle@rac1 dbs]$ cp initorarac1.ora initorarac1.ora_bak #修改rac1的pfile（清空文件内容，修改为如下内容） [oracle@rac1 dbs]$ vim initorarac1.ora ------------------------------------------- spfile='+RACDATA/oradata/spfilerac.ora' ------------------------------------------- #关闭数据库，重启，验证spfile是否正常 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 541068368 bytes Database Buffers 289406976 bytes Redo Buffers 2371584 bytes Database mounted. Database opened. #开启实时同步 SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. #将pfile传给rac2节点，并修改成对应的文件名 [oracle@rac1 dbs]$ scp initrac1.ora rac2:/u01/app/oracle/product/11.2.0/db_1/dbs/initorarac2.ora initorarac1.ora 100% 43 0.0KB/s 00:00 7. 注册到CRS资源管理在rac1节点操作即可（oracle用户操作） #注册数据库 [oracle@orarac01 ~]$ srvctl add database -d rac -n xxdb1 -o /u01/app/oracle/product/11.2.0/dbhome_1 -p +RACDATA/oradata/spfilerac.ora -r physical_standby -a "RACDATA" [oracle@orarac01 ~]$ #注册节点 [oracle@orarac01 ~]$ srvctl add instance -d rac -i rac1 -n orarac01 [oracle@orarac01 ~]$ srvctl add instance -d rac -i rac2 -n orarac02 #检查资源配置 [oracle@rac1 ~]$ srvctl config database -d rac 8. 将rac2节点开启 #为rac2节点配置自己的UNDOTBS #主库（orasgl）操作 SQL> select file_name,bytes/1024/1024 from dba_data_files; #在主库添加UNDOTBS2，RAC库同步（加完可以切一下日志） SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/xxdb1/undotbs02.dbf' size 100m; Tablespace created. #备库（rac）操作 #检查UNDOTBS2是否已经同步过来 SYS@rac1> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- +RACDATA/oradata/rac/users01.dbf +RACDATA/oradata/rac/undotbs01.dbf +RACDATA/oradata/rac/sysaux01.dbf +RACDATA/oradata/rac/system01.dbf +RACDATA/oradata/rac/undotbs02.dbf SYS@rac1> #将数据库转换为rac模式注：千万不能漏，漏会报错只能把本地pfile恢复重新来 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ SQL> alter system set cluster_database=true scope=spfile; SQL> alter system set cluster_database_instances=2 scope=spfile; SQL> alter system set instance_number=1 scope=spfile sid='rac1'; SQL> alter system set instance_number=2 scope=spfile sid='rac2'; SQL> alter system set thread=1 scope=spfile sid='rac1'; SQL> alter system set thread=2 scope=spfile sid='rac2'; SQL> alter system set undo_tablespace=undotbs1 scope=spfile sid='rac1'; SQL> alter system set undo_tablespace=undotbs2 scope=spfile sid='rac2'; ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ #重启数据库（rac1节点） SQL> shutdown immediate; SQL> startup SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. #启动数据库（rac2节点）注意asm权限 [root@orarac02 ~]# usermod -a -G asmadmin oracle [root@orarac02 ~]# id oracle uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1200(asmadmin),1201(asmdba),1300(dba),1301(oper) [root@orarac02 ~]# id grid uid=1000(grid) gid=1000(oinstall) groups=1000(oinstall),1200(asmadmin),1201(asmdba),1202(asmoper) SQL> startup #测试数据同步 #至此完成DG部分的所有操作 ----------------------------------------------------------------------------------- 单实例+ADG+双节点RAC 架构搭建成功 ———————————————————————— 四、主备切换（主库变为rac架构） #关闭rac2节点 [oracle@rac1 ~]$ srvctl stop instance -d rac -i rac2 #检查主库（xxdb1）状态 SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PRIMARY TO STANDBY #将主库（xxdb1）切换为备库 SQL> alter database commit to switchover to physical standby with session shutdown; Database altered. #将新备库（xxdb1）启动到mount状态 SQL> startup mount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 541068368 bytes Database Buffers 289406976 bytes Redo Buffers 2371584 bytes Database mounted. #将备库（rac）切换为主库 SQL> alter database commit to switchover to primary; Database altered. #将新主库（rac）打开 SQL> alter database open; Database altered. #将新备库（xxdb1）打开 SQL> alter database open; Database altered. &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 备库xxdb1此时是mount状态主库rac起来之后不能查询其他窗口 ora-01219 将备库shutdown，之后再startup，执行日志实时应用即ok &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& #检查新主备状态 #主库（rac）状态 SQL> select name,database_role,switchover_status from v$database; NAME DATABASE_ROLE SWITCHOVER_STATUS --------- ---------------- -------------------- ORASGL PRIMARY TO STANDBY #备库（xxdb1）状态 SQL> select name,database_role,switchover_status from v$database; NAME DATABASE_ROLE SWITCHOVER_STATUS --------- ---------------- -------------------- ORASGL PHYSICAL STANDBY RECOVERY NEEDED #备库开启日志应用 SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. #开启rac2节点 [oracle@rac1 ~]$ srvctl start instance -d rac -i rac2 #两个节点分别执行切换日志命令 SQL> alter system switch logfile; #在备库（xxdb1）检查是否有归档过来 ll /u02/arcdata #测试数据同步 #主库（rac）rac1节点操作 insert #备库（xxdb1）检查 #主库（rac）rac2节点操作 #备库（xxdb1）检查