oracle12cr1,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。

分享到：

更多