Oracle 12c 单机到RAC数据迁移

头像
DQuery
    阅读 9 分钟

    背景

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

    阅读本文之前建议先阅读Oracle 12c RAC到单机数据迁移了解相关知识点

    实现

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

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

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


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  
Copyright (c) 1982, 2016, Oracle.  All rights reserved.  
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  
Copyright (c) 1982, 2016, Oracle.  All rights reserved.  
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进行单独转换，你可以登录到源数据库执行以下命令查询所有的datafile

    
SQL> 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
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 HRPROD                     MOUNTED

    可以看到clone完的pdb状态为MOUNTED，需要将其打开

    SQL> alter pluggable database HRPROD open;  
SQL> show pdbs  
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED  
---------- ------------------------------ ---------- ----------  
         2 PDB$SEED                       READ ONLY  NO  
         6 HRPROD                         READ WRITE NO

    如果状态是OPEN WRITE就是正常的。

    连接

    这次迁移完后，并没有配置额外的连接信息，客户端通过以下tns信息就能连上数据库

    HRPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HRPROD) # pdb名称
    )
  )

    碰到的问题

    本次迁移碰到的问题较多，主要是clone时参考RAC迁单机导致一直无法clone成功，一步步试错最终执行成功，并且在迁移结束后service name发生了变化，花了很长时间解决连接的问题，至今也不知道为何迁移完service name发生了变化，如果有知道的原因，欢迎在评论区留言。

    参考

    oracle-rac数据库数据迁移oracle
    DQuery
    300 声望93 粉丝

    幸福是奋斗出来的

    « 上一篇
    运维攻坚之压缩表空间解决oracle数据库磁盘满问题
    下一篇 »
    Oracle BPM如何通过Java获取审批人

    引用和评论

    推荐阅读
    头像
    SourceTree自定义操作的一个应用

    DQuery阅读 887

    头像
    数据集汇总｜18个电影/音乐数据集汇总，覆盖影片/歌曲推荐、电影评价、歌词识别、音乐流派······

    超神经HyperAI赞 3阅读 1.2k

    头像
    使用 MongoDB 构建 AI：Gradient Accelerator Block 如何在几秒钟内让您从零开发 AI

    MongoDB数据平台赞 1阅读 12.9k

    头像
    MongoDB Atlas与YoMio.AI近乎完美适配:推理更快速、查询更灵活、场景更丰富

    MongoDB数据平台赞 1阅读 12.4k

    头像
    MongoDB 8.0已全面可用

    MongoDB数据平台阅读 12k

    头像
    简道云搭载阿里云MongoDB数据库，帮助数以万计企业重构业务系统

    MongoDB数据平台阅读 11.3k

    头像
    记录一次Linux下安装Oracle数据库的过程

    少放香菜赞 1阅读 3.5k

    0 条评论
    得票最新