环境:
|
原 库 |
目的库 |
||
|
Db版本 |
ip |
Db版本 |
ip |
|
11.2.0.4 |
192.168.1.85 |
12.2.0.1 新创建pdb为: ORA12CPDB2 |
192.168.1.134 |
1.1 表空间传输介绍
数据库表空间无论是字典管理还是手动管理，亦或者是目标端跟源端数据库大小不一致，都可以使用传输表空间，而且，相对来说，传输表空间要比使用数据泵导入导出迁移数据要快，这是因为传输表空间，是只把实际物理数据文件复制到指定的目标端位置，再灌入元数据，而数据泵导入导出则是需要把数据库对象按要求导出，然后目标端导入.
1.2 检查字节顺序
原库(11g)
|
SQL> set linesize 1000 SQL> column PLATFORM_NAME format a64 SQL> column ENDIAN_FORMAT format a16 SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT 2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d 3 WHERE tp.PLATFORM_ID = d.PLATFORM_ID;
PLATFORM_NAME ENDIAN_FORMAT ---------------------------------------------------------------- ---------------- Linux x86 64-bit Little |
目的库(12c pdb)
|
SQL> set linesize 1000 SQL> column PLATFORM_NAME format a64 SQL> column ENDIAN_FORMAT format a16 SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT 2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d 3 WHERE tp.PLATFORM_ID = d.PLATFORM_ID;
PLATFORM_NAME ENDIAN_FORMAT ---------------------------------------------------------------- ---------------- Linux x86 64-bit Little |
这里的字节顺序一致不需要转换
1.3 检查字符集
原库:
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
目的库:
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
1.4 检查Compatible参数
原库:
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
目的库:
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0
1.5 源端创建表空间(用户、表以及测试数据)
查看当前的数据文件分布
|
SQL> column file_name format a64 SQL> column tablespace_name format a16 SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME ---------------------------------------------------------------- ---------------- /u01/app/oracle/oradata/slnngkdg/users01.dbf USERS /u01/app/oracle/oradata/slnngkdg/undotbs01.dbf UNDOTBS1 /u01/app/oracle/oradata/slnngkdg/sysaux01.dbf SYSAUX /u01/app/oracle/oradata/slnngkdg/system01.dbf SYSTEM /u01/app/oracle/oradata/slnngkdg/tps_goldengate01.dbf TPS_GOLDENGATE |
|
SQL> connect / as sysdba Connected. SQL> create tablespace tps_hxl datafile '/u01/app/oracle/oradata/slnngkdg/tps_hxl01.dbf' size 100m autoextend on; SQL> create user hxl identified by oracle default tablespace tps_hxl account unlock; SQL> grant resource,connect to hxl; SQL> connect hxl/oracle Connected. SQL> create table tb_test01 as select * from hxl01.tb_test01; ##(这里hxl需要有访问该表权限) Table created. SQL> select count(1) from tb_test01; COUNT(1) ---------- 790000 |
1.6 源端表空间自包含(独立性)检查
|
SQL> connect / as sysdba Connected. SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK('TPS_HXL',TRUE,TRUE); PL/SQL procedure successfully completed. SQL> select * from transport_set_violations; no rows selected |
查询无返回结果说明检查通过，否则需要根据violation字段的说明解决各类参照完整性问题，比如说主键、外键约束、分区等问题，一般这些问题是指对象不在同一表空间.
1.7 创建目录原库
源库:
Os创建目录
mkdir -p /u01/dumpdir
SQL> connect / as sysdba
Connected.
SQL> create directory datapump_dir as '/u01/dumpdir';
Directory created.
目的pdb下创建
Os创建目录
mkdir -p /u01/dumpdir
|
SQL> connect / as sysdba Connected. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORA12CPDB1 READ WRITE NO SQL> alter session set container=ORA12CPDB1; Session altered. SQL> create directory datapump_dir as '/u01/dumpdir'; Directory created.
|
1.7 将原库的用户自定义表空间都设置为只读模式
alter tablespace users read only;
alter tablespace tps_goldengate read only;
alter tablespace tps_hxl read only;
1.8 源端数据泵导出表空间元数据
目前授权给导出的用户,我们这里使用system导出
connect / as sysdba
grant write,read on directory datapump_dir to system;
因为我这里原库是11G的，导入到12C 需要指定version=12参数
|
expdp system/oracle full=y transportable=always directory=datapump_dir dumpfile=full_tts.dmp metrics=y exclude=statistics logfile=full_tts_export.log version=12
最后会出现如下的提示 Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is: /u01/dumpdir/full_tts.dmp ****************************************************************************** Datafiles required for transportable tablespace TPS_GOLDENGATE: /u01/app/oracle/oradata/slnngkdg/tps_goldengate01.dbf Datafiles required for transportable tablespace TPS_HXL: /u01/app/oracle/oradata/slnngkdg/tps_hxl01.dbf Datafiles required for transportable tablespace USERS: /u01/app/oracle/oradata/slnngkdg/users01.dbf Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Wed Feb 19 14:36:41 2020 elapsed 0 00:05:30 |
1.9 原库进行文件转换
需要转换的化可以参考
https://www.cnblogs.com/hxlasky/p/12334747.html
1.10 将表空间数据文件和dump文件拷贝到目的库
scp /u01/dumpdir/full_tts.dmp oracle@192.168.1.134:/u01/dumpdir/
scp /u01/app/oracle/oradata/slnngkdg/users01.dbf oracle@192.168.1.134:/u01/dumpdir/
scp /u01/app/oracle/oradata/slnngkdg/tps_goldengate01.dbf oracle@192.168.1.134:/u01/dumpdir/
scp /u01/app/oracle/oradata/slnngkdg/tps_hxl01.dbf oracle@192.168.1.134:/u01/dumpdir/
数据文件传输完成到了目的库之后，原来的表空间可以设置为可读写了
alter tablespace users read write;
alter tablespace tps_goldengate read write;
alter tablespace tps_hxl read write;
1.11 目标库创建pdb
create pluggable database ora12cpdb2
admin user hxl01 identified by oracle
file_name_convert = ('/u01/app/oracle/oradata/ora12c/pdbseed', '/u01/app/oracle/oradata/ora12c/ora12cpdb2');
alter pluggable database ORA12CPDB2 open;
1.12 数据文件拷贝到相应的目录下
将源库拷贝过来的数据文件拷贝到pdb实例所在的数据文件目录下面
cp /u01/dumpdir/tps_goldengate01.dbf /u01/app/oracle/oradata/ora12c/ora12cpdb2/
cp /u01/dumpdir/tps_hxl01.dbf /u01/app/oracle/oradata/ora12c/ora12cpdb2/
cp /u01/dumpdir/users01.dbf /u01/app/oracle/oradata/ora12c/ora12cpdb2/
1.13 目标库创建目录
目的pdb下创建
Os创建目录
mkdir -p /u01/dumpdir
|
SQL> connect / as sysdba Connected. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORA12CPDB1 READ WRITE NO SQL> alter session set container=ORA12CPDB2 Session altered. SQL> create directory datapump_dir as '/u01/dumpdir'; Directory created.
|
1.14 目标库导入
impdp system/oracle@ORA12CPDB2 directory=datapump_dir dumpfile=full_tts.dmp logfile=full_tts_imp.log metrics=y transport_datafiles='/u01/app/oracle/oradata/ora12c/ora12cpdb2/users01.dbf','/u01/app/oracle/oradata/ora12c/ora12cpdb2/tps_hxl01.dbf','/u01/app/oracle/oradata/ora12c/ora12cpdb2/tps_goldengate01.dbf'
@ORA12CPDB2 这里是连接pdb2的tns连接名
提示报错:
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
W-1 Startup took 1 seconds
W-1 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@ORA12CPDB2 directory=datapump_dir dumpfile=full_tts.dmp logfile=full_tts_imp.log metrics=y transport_datafiles=/u01/app/oracle/oradata/ora12c/ora12cpdb2/users01.dbf,/u01/app/oracle/oradata/ora12c/ora12cpdb2/tps_hxl01.dbf,/u01/app/oracle/oradata/ora12c/ora12cpdb2/tps_goldengate01.dbf
W-1 Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
W-1 Completed 1 MARKER objects in 2 seconds
W-1 Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
W-1 Completed 1 MARKER objects in 3 seconds
W-1 Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29349: tablespace 'TPS_GOLDENGATE' already exists
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Wed Feb 19 14:48:04 2020 elapsed 0 00:00:56
原因是我这里原来已经有该表空间了，修改如下:
impdp system/oracle@ORA12CPDB2 directory=datapump_dir dumpfile=full_tts.dmp logfile=full_tts_imp.log metrics=y transport_datafiles='/u01/app/oracle/oradata/ora12c/ora12cpdb2/users01.dbf','/u01/app/oracle/oradata/ora12c/ora12cpdb2/tps_hxl01.dbf','/u01/app/oracle/oradata/ora12c/ora12cpdb2/tps_goldengate01.dbf' remap_tablespace=tps_goldengate:tps_goldengate01
1.15 验证
源库的数据库账号和表空间会自动导入进来