背景

之前发表过一篇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发生了变化,如果有知道的原因,欢迎在评论区留言。

参考

Trackback

no comment untill now

Add your comment now