oracle11g rac转换单机,Oracle 12c 单机到RAC数据迁移

最新推荐文章于 2024-01-03 11:13:05 发布

weixin_39575758

最新推荐文章于 2024-01-03 11:13:05 发布

阅读量836

点赞数

文章标签： oracle11g rac转换单机

本文详细介绍从Oracle单机环境迁移到RAC环境的过程，包括创建用户、建立dblink、设置数据文件路径及执行clone命令等关键步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景

之前发表过一篇Oracle 12c RAC到单机数据迁移的文章，介绍了Oracle 12c从RAC到单机的数据迁移过程，本文在此基础上继续介绍从单机到RAC的迁移过程。

实现下文远程数据库指的是需要迁移的远程数据库，本地数据库指的是迁入的目标数据库在远程数据库上创建用户，本地数据库将通过该用户创建dblink进行克隆[oracle]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 24 10:10:47 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 HRPROD MOUNTED

SQL> alter session set container=HRPROD;

Session altered.

SQL> CREATE USER cloner IDENTIFIED BY oracle;

User created.

SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO cloner;

Grant succeeded.本地数据库新建连接描述符，修改tnsnames.ora

tnsnames.ora文件位于$ORACLE_HOME/network/admin目录下，如/home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin，如果目录下没有该文件就手动创建一个，向该文件添加远程数据库的连接(oracle用户)HRPROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.11.6)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = HRPROD)

)

)本地数据库创建dblink[oracle]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 24 10:25:08 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> drop database link XL1;

Database link dropped.

SQL> create database link XL1 connect to cloner identified by oracle using 'HRPROD';

Database link created.

SQL> desc user_tables@XL1;

如果desc user_tables@XL1能正常返回说明连接正常将远程数据修改为只读模式[oracle]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 24 10:35:22 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter pluggable database HRPROD close;

Pluggable database altered.

SQL> alter pluggable database HRPROD open read only;

Pluggable database altered.

如果alter pluggable database HRPROD close;长时间未响应，可以直接进入数据库执行shutdown命令，效果是一样的SQL> alter session set container=HRPROD

SQL> shutdown immediate;本地数据库创建datafile目录

由于目标数据库是RAC，RAC存储方式采用共享存储，并且通过ASM做磁盘管理，我们需要预先在asm中创建好datafile目录，通过asmcmd命令可以对asm磁盘进行管理[oracle]$ asmcmd

ASMCMD> ls

ARCH/

DATA/

GIMR/

OCR/

ASMCMD> cd DATA/DEMO/DATAFILE

ASMCMD> mkdir HRPROD

ASMCMD> cd ../TEMPFILE

ASMCMD> mkdir HRPROD

ASMCMD> ls

HRPROD/

ASMCMD> exit

asmcmd命令跟linux磁盘操作命令类似，并不复杂。本地数据库执行clone命令

在RAC到单机的迁移过程中，通过dblink统一做一次file_name_convert就行，但单机迁RAC必须对所有的datafile进行单独转换，你可以登录到源数据库执行以下命令查询所有的datafileSQL> select file_name from dba_data_files;

FILE_NAME

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

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/system.274.1027852675

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/sysaux.275.1027852675

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/undotbs1.273.1027852675

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/undo_2.277.1027852725

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/users.278.1027852727

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_ias_portlet.279.1028114765

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_wls.280.1028114765

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_iau.281.1028114765

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_mds.282.1028114767

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_stb.283.1028114767

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_ias_opss.284.1028114767

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_ias_activity.285.1028114769

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_ias_webcenter.286.1028114771

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/soa_wls.288.1028125223

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/soa_ias_ums.289.1028125223

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/soa_stb.290.1028125225

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/soa_mds.291.1028125225

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/soa_iau.293.1028125227

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/soa_ias_opss.294.1028125227

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/soa_soainfra.295.1028125229

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

/home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/MISSING00092

21 rows selected

那么clone命令如下create pluggable database HRPROD from HRPROD@XL1 file_name_convert=('/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_ias_activity.285.1028114769','+DATA/DEMO/DATAFILE/HRPROD/portal_ias_activity.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_ias_opss.284.1028114767','+DATA/DEMO/DATAFILE/HRPROD/portal_ias_opss.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_ias_portlet.279.1028114765','+DATA/DEMO/DATAFILE/HRPROD/portal_ias_portlet.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_ias_webcenter.286.1028114771','+DATA/DEMO/DATAFILE/HRPROD/portal_ias_webcenter.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_iau.281.1028114765','+DATA/DEMO/DATAFILE/HRPROD/portal_iau.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_mds.282.1028114767','+DATA/DEMO/DATAFILE/HRPROD/portal_mds.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_stb.283.1028114767','+DATA/DEMO/DATAFILE/HRPROD/portal_stb.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_wls.280.1028114765','+DATA/DEMO/DATAFILE/HRPROD/portal_wls.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/soa_ias_opss.294.1028125227','+DATA/DEMO/DATAFILE/HRPROD/soa_ias_opss.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/soa_ias_ums.289.1028125223','+DATA/DEMO/DATAFILE/HRPROD/soa_ias_ums.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/soa_iau.293.1028125227','+DATA/DEMO/DATAFILE/HRPROD/soa_iau.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/soa_mds.291.1028125225','+DATA/DEMO/DATAFILE/HRPROD/soa_mds.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/soa_soainfra.295.1028125229','+DATA/DEMO/DATAFILE/HRPROD/soa_soainfra.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/soa_stb.290.1028125225','+DATA/DEMO/DATAFILE/HRPROD/soa_stb.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/soa_wls.288.1028125223','+DATA/DEMO/DATAFILE/HRPROD/soa_wls.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/sysaux.275.1027852675','+DATA/DEMO/DATAFILE/HRPROD/sysaux.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/system.274.1027852675','+DATA/DEMO/DATAFILE/HRPROD/system.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/undo_2.277.1027852725','+DATA/DEMO/DATAFILE/HRPROD/undo_2.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/undotbs1.273.1027852675','+DATA/DEMO/DATAFILE/HRPROD/undotbs1.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/users.278.1027852727','+DATA/DEMO/DATAFILE/HRPROD/users.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/TEMPFILE/portal_ias_temp.287.1028114771','+DATA/DEMO/TEMPFILE/HRPROD/portal_ias_temp.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/TEMPFILE/soa_ias_temp.292.1028125225','+DATA/DEMO/TEMPFILE/HRPROD/soa_ias_temp.dbf',

'/home/oracle/app/oracle/oradata/HRPROD/TEMPFILE/temp.276.1027852677','+DATA/DEMO/TEMPFILE/HRPROD/temp.dbf');

有几个地方需要注意：必须换行，建议一个datafile一行，因为sql有大小限制，如果字符过大会报以下错误SP2-0341:

line overflow during variable substitution (>3000 characters at line 1)必须列出所有的datafile，如果按照单机迁移的方式，会报以下错误ERROR at line 1:

ORA-65180: duplicate file name encountered -

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/sysaux.275.1027852675必须把TEMPFILE文件也要带上，不然会报TEMPFILE文件无法匹配ERROR at line 1:

ORA-65005: missing or invalid file name pattern for file -

/home/oracle/app/oracle/oradata/HRPROD/TEMPFILE/temp.276.1027852677

临时文件可以通过查询dba_temp_files获取SQL> select file_name from dba_temp_files;

FILE_NAME

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

/home/oracle/app/oracle/oradata/HRPROD/TEMPFILE/portal_ias_temp.287.1028114771

/home/oracle/app/oracle/oradata/HRPROD/TEMPFILE/soa_ias_temp.292.1028125225

/home/oracle/app/oracle/oradata/HRPROD/TEMPFILE/temp.276.1027852677目标文件的后缀不能带上原数字，比如源文件是undo_2.277.1027852725，目标文件不能带上后面的数字，可以为undo_2.dbf，不然会报以下错误ERROR at line 1:

ORA-65169: error encountered while attempting to copy file

/home/oracle/app/oracle/oradata/HRPROD/DATAFILE/portal_stb.283.1028114767

ORA-19504: failed to create file

"+DATA/DEMO/DATAFILE/HRPROD/portal_stb.283.1028114767"

ORA-17502: ksfdcre:4 Failed to create file

+DATA/DEMO/DATAFILE/HRPROD/portal_stb.283.1028114767

ORA-15046: ASM file name

'+DATA/DEMO/DATAFILE/HRPROD/portal_stb.283.1028114767' is not in

single-file creation form

如果提示以下信息说明clone成功，可以通过show pdbs命令查看pdb状态。Pluggable database created.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED