表空间传输例子01(单个表空间)
12C表空间传输
环境:
OS:CentOS 7
|
原 库 |
目的库 |
||
|
Db版本 |
ip |
Db版本 |
ip |
|
11.2.0.4 |
192.168.1.85 |
12.2.0.1 pdb |
192.168.1.134 |
1.表空间传输
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.8 将源库设置为只读模式
SQL> alter tablespace tps_hxl read only;
Tablespace altered.
1.9 源端数据泵导出表空间元数据
目前授权给导出的用户,我们这里使用system导出
connect / as sysdba
grant write,read on directory datapump_dir to system;
|
[oracle@localhost ~]$ expdp system/oracle dumpfile=expdat.dmp directory=datapump_dir transport_tablespaces=tps_hxl logfile=tts_export.log
Export: Release 11.2.0.4.0 - Production on Tue Feb 18 14:53:36 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=expdat.dmp directory=datapump_dir transport_tablespaces=tps_hxl logfile=tts_export.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /u01/dumpdir/expdat.dmp ****************************************************************************** Datafiles required for transportable tablespace TPS_HXL: /u01/app/oracle/oradata/slnngkdg/tps_hxl01.dbf Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Tue Feb 18 14:54:08 2020 elapsed 0 00:00:32 |
1.9 转换
我这里两边的都是相同的操作系统，不需要进行转换，需要转换的化可以参考
https://www.cnblogs.com/hxlasky/p/12334747.html
1.10 使用ftp工具分别拷贝表空间和与表空间对应数据文件到相应的目录
导出的dump文件
scp /u01/dumpdir/expdat.dmp oracle@192.168.1.134:/u01/dumpdir/
表空间对应的数据文件
scp /u01/app/oracle/oradata/slnngkdg/tps_hxl01.dbf oracle@192.168.1.134:/u01/dumpdir/
1.11 目标库手工调制文件目录
从库拷贝过来的数据文件目前是放在/u01/dumpdir/,我们需要放到pdbs所在的目录下
首先查看当前pdbs的数据文件路径
|
SQL> alter session set container=ORA12CPDB1; SQL> set linesize 1000 SQL> column file_name format a64 SQL> column tablespace_name format a16 SQL> select file_name,tablespace_name from dba_data_files; SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME ---------------------------------------------------------------- ---------------- /u01/app/oracle/oradata/ora12c/ora12cpdb1/system01.dbf SYSTEM /u01/app/oracle/oradata/ora12c/ora12cpdb1/sysaux01.dbf SYSAUX /u01/app/oracle/oradata/ora12c/ora12cpdb1/undotbs01.dbf UNDOTBS1 /u01/app/oracle/oradata/ora12c/ora12cpdb1/users01.dbf USERS /u01/app/oracle/oradata/ora12c/ora12cpdb1/tps_goldengate01.dbf TPS_GOLDENGATE /u01/app/oracle/oradata/ora12c/ora12cpdb1/fda101.dbf FDA1
6 rows selected. |
拷贝文件
[oracle@localhost dumpdir]$ cp /u01/dumpdir/tps_hxl01.dbf /u01/app/oracle/oradata/ora12c/ora12cpdb1/
1.12 目标库创建用户并进行导入
|
SQL> alter session set container=ORA12CPDB1; Session altered. SQL> create user hxl identified by oracle; User created. SQL> grant connect ,resource to hxl; Grant succeeded. |
导入:
|
[oracle@localhost dumpdir]$ impdp system/oracle@ORA12CPDB1 dumpfile=expdat.dmp directory=datapump_dir transport_datafiles=/u01/app/oracle/oradata/ora12c/ora12cpdb1/tps_hxl01.dbf remap_schema=hxl:hxl logfile=import.log
Import: Release 12.2.0.1.0 - Production on Tue Feb 18 15:11:20 2020
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 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@ORA12CPDB1 dumpfile=expdat.dmp directory=datapump_dir transport_datafiles=/u01/app/oracle/oradata/ora12c/ora12cpdb1/tps_hxl01.dbf remap_schema=hxl:hxl logfile=import.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Tue Feb 18 15:11:34 2020 elapsed 0 00:00:09 |
1.13 目标库验证
数据验证
SQL> select count(1) from hxl.tb_test01;
COUNT(1)
----------
790000
修改用户的默认表空间
SQL> alter user hxl default tablespace tps_hxl;
1.14 修改表空间为可读
目标库导入完成后，相应的表空间是只读的，修改为可读写
目标端：
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TPS_HXL';
TABLESPACE_NAME STATUS
---------------- ---------
TPS_HXL READ ONLY
alter tablespace tps_hxl read write;
目标端:
alter tablespace tps_hxl read write;
