--源库:10.2.0.5 csdb
--目标库:11.2.0.4 orcl
源库和目标库tnsnames.ora文件中添加对方内容
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
CSDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = csdb)
)
)
源端 tnsping orcl
目标端 tnsping csdb
源库创建测试用户
create tablespace abctest
datafile '/oradata/CSDB/datafile/test_01.dbf' size 10M;
create user test identified by oracle_4U
default tablespace abctest
temporary tablespace temp;
grant create session, resource to test;
CREATE TABLE test.worker
(Wno CHAR(9) PRIMARY KEY,
Wname CHAR(20) NOT NULL);
insert into test.worker values('003','sakiel');
commit;
select * from test.worker;
源端数据库相关参数
--parallel_max_servers设置为0,避免Bug 5453502
SQL> show parameter parallel_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 20
SQL> alter system set parallel_max_servers=0;
System altered.
目标端数据库参数
--确保cursor_sharing为EXACT
SQL> show parameter cursor_sha
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
目标端导数据路径
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS XMLDIR
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
SYS ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/11g/state
SYS DATA_PUMP_DIR
/u01/app/oracle/admin/orcl/dpdump/
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS ORACLE_OCM_CONFIG_DIR2
/u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
目标端创建dblink
SQL> create database link dumplink connect to system identified by oracle_4U using 'CSDB';
Database link created.
SQL> select * from dual@dumplink;
D
-
X
由于源数据库的表空间不存在于目标数据库,导入时创建对象会报错,因此我们需要在目标数据库创建表空间
目标端运行下面语句获得创建表空间语句
SELECT 'create tablespace ' || tablespace_name || ' datafile ''' || REPLACE (file_name, 'CSDB', 'ORCL') || ''' size '
|| TO_CHAR (CEIL (size_mb)) || ' M autoextend on;' as cmd
FROM (SELECT tablespace_name, file_name, (bytes / 1024 / 1024) size_mb
FROM dba_data_files@dumplink
WHERE tablespace_name IN (SELECT DISTINCT tablespace_name
FROM dba_segments@dumplink
WHERE owner IN ('TEST')));
目标端运行下面语句获得创建临时表空间语句,被导入的schema可能缺省的临时表空间并非temp
SELECT 'create temporary tablespace '
|| tablespace_name
|| ' tempfile '''
|| REPLACE (file_name, 'CSDB', 'ORCL')
|| ''' size '
|| TO_CHAR (CEIL (bytes / 1024 / 1024))
|| ' M autoextend on;' as cmd
FROM dba_temp_files@dumplink
WHERE tablespace_name IN (SELECT temporary_tablespace
FROM dba_users@dumplink
WHERE username IN ('TEST') AND temporary_tablespace <> 'TEMP');
目标端运行导入
impdp \'\/ as sysdba \' directory=DATA_PUMP_DIR network_link=dumplink logfile=imp_dump.log version=10.2.0.5 parallel=2 schemas='test'