Oracle12cR1 new feature-Transportable Database

可传输表空间是8还是9i推出的功能,它允许你直接移动数据文件,然后再通过导入导出数据字典,帮助迁移大量的数据,而在12cR1里面推出一个新功能,它可以将整个库传输到PDB里面。
1.首先我在11.2.0.4数据库上创建一个表空间,用户,和表。并创建导出所需要使用的目录.

[oracle@s2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 16 20:04:46 2018

Copyright (c) 1982, 2013, Oracle.  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

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/DB11G/users01.dbf          USERS
/u01/app/oracle/oradata/DB11G/undotbs01.dbf        UNDOTBS1
/u01/app/oracle/oradata/DB11G/sysaux01.dbf         SYSAUX
/u01/app/oracle/oradata/DB11G/system01.dbf         SYSTEM
/u01/app/oracle/oradata/DB11G/example01.dbf        EXAMPLE

create tablespace tbs_test datafile '/u01/app/oracle/oradata/DB11G/tbs_test.dbf' size 20m; 

SQL> create user u1 identified by oracle;
User created.

SQL> alter user u1 quota unlimited on tbs_test;
User altered.

SQL> create table u1.t1 tablespace tbs_test as select * from dba_objects;
Table created.

SQL> create directory exp_dir as '/u01/app';
Directory created.

2.从11g库导出数据

[oracle@s2 dbs]$ expdp '"sys/oracle as sysdba"' full=y transportable=always version=12.0 dumpfile=export1.dmp logfile=export1.log directory=exp_dir;
Export: Release 11.2.0.4.0 - Production on Wed May 16 20:25:05 2018
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 "SYS"."SYS_EXPORT_FULL_01":  "sys/******** AS SYSDBA" full=y transportable=always version=12.0 dumpfile=export1.dmp logfile=export1.log directory=exp_dir 
Estimate in progress using BLOCKS method...
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is

ORA-29335: tablespace 'EXAMPLE' is not read only
ORA-29335: tablespace 'TBS_TEST' is not read only
ORA-29335: tablespace 'USERS' is not read only
Job "SYS"."SYS_EXPORT_FULL_01" stopped due to fatal error at Wed May 16 20:25:13 2018 elapsed 0 00:00:04

[oracle@s2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 16 20:26:37 2018
Copyright (c) 1982, 2013, Oracle.  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

SQL> alter tablespace EXAMPLE read only;
Tablespace altered.

SQL> alter tablespace TBS_TEST read only;
Tablespace altered.

SQL> alter tablespace USERS read only;
Tablespace altered.

[oracle@s2 dbs]$ expdp '"sys/oracle as sysdba"' full=y transportable=always version=12.0 dumpfile=export1.dmp logfile=export1.log directory=exp_dir;
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /u01/app/export1.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
  /u01/app/oracle/oradata/DB11G/example01.dbf
Datafiles required for transportable tablespace TBS_TEST:
  /u01/app/oracle/oradata/DB11G/tbs_test.dbf
Datafiles required for transportable tablespace USERS:
  /u01/app/oracle/oradata/DB11G/users01.dbf
Job "SYS"."SYS_EXPORT_FULL_01" completed with 2 error(s) at Wed May 16 20:33:45 2018 elapsed 0 00:02:56
第一次导的时候报错了,是因为我们没有把除了系统外的其他几个表空间设置为read only模式。这里我们使用了选项transportable=always与full=y。transportable=always这个选项就是传输文件时候用到的。它有两种模式,一种是full模式,就是导出所有对象的元数据。而另外一种模式table,它就只会导出指定table、分区和自分区对象的元数据。这里还有一个version参数需要注意,往12c库里面导入需要设置version=12.0这个参数。

3.12cR1数据库新建PDB并导入数据

CREATE PLUGGABLE DATABASE PRODPDB1 ADMIN USER pdbadmin IDENTIFIED BY pdbadmin
 FILE_NAME_CONVERT=(
  '/u01/app/oracle/oradata/DB12C/pdbseed/system01.dbf', '/u01/app/oracle/oradata/DB12C/PRODPDB1/system01.dbf',
  '/u01/app/oracle/oradata/DB12C/pdbseed/sysaux01.dbf', '/u01/app/oracle/oradata/DB12C/PRODPDB1/sysaux01.dbf',
  '/u01/app/oracle/oradata/DB12C/pdbseed/pdbseed_temp012018-05-18_03-33-11-PM.dbf', '/u01/app/oracle/oradata/DB12C/PRODPDB1/temp01.dbf'
  )
 STORAGE UNLIMITED

[oracle@s2 DB11G]$ cp example01.dbf /u01/app/oracle/oradata/DB12C/PRODPDB1
[oracle@s2 DB11G]$ cp users01.dbf /u01/app/oracle/oradata/DB12C/PRODPDB1
[oracle@s2 DB11G]$ cp tbs_test.dbf /u01/app/oracle/oradata/DB12C/PRODPDB1
[oracle@s2 DB12C]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri May 18 16:14:27 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PRODPDB1                       READ WRITE NO
SQL> alter session set container=PRODPDB1;

SQL> create directory exp_dir as '/u01/app'; 
Directory created.

impdp '"sys/oracle@//localhost/PRODPDB1 as sysdba"' full=y directory=exp_dir dumpfile=export1.dmp transport_datafile='/u01/app/oracle/oradata/DB12C/PRODPDB1/example01.dbf','/u01/app/oracle/oradata/DB12C/PRODPDB1/tbs_test.dbf','/u01/app/oracle/oradata/DB12C/PRODPDB1/users01.dbf' logfile=import_pdb.log remap_tablespace=users:users_pdb1;


SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
EXAMPLE                        ONLINE
TBS_TEST                       ONLINE
USERS_PDB1                     ONLINE

SQL> select count(1) from u1.t1; 

  COUNT(1)
----------
     86953
在这里我们先在12c下创建了一个PDB,然后我们把11g的几个数据文件拷贝到12c下面,然后直接连接到pdb环境进行导入。最后我们发现整个11g数据库除了系统的表空间以外,都被我们传输到12c的PDB下面并实现了导入。整个过程还是很快的。如果数据量不是很大,可以考虑使用这个新的Transportable Database技术实现迁移到PDB。

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569