1. 在源数据库与目标数据库检查字符集是不是相同
2. 源数据库需要导入表空间是不是自包含的
3. 检查操作系统信息是不是被支持
4. 检查DB FILE的位置
1
col PARAMETER for a30
col VALUE for a30
select * from v$nls_parameters where PARAMETERin('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
2
------结果应当是no result
exec sys.dbms_tts.transport_set_check('TEST_DATA',true);
select * from sys.transport_set_violations;
3 --有值就说明支持
select PLATFORM_NAME from v$database where PLATFORM_NAME in (selectPLATFORM_NAME from v$transportable_platform);
4
select tablespace_name, file_name from dba_data_files whereupper(tablespace_name) like upper('%TEST_DATA%')
union all
select tablespace_name, file_name from dba_temp_files whereupper(tablespace_name) like upper('%TEST_DATA%');
开始导出:
1. 将源数据库需要导出的表空间设为只读
2. 导出DMP文件
3. 转换DBF文件
4. FTP DMP,DBF文件到目标数据库
5. 将源数据库需要导出的表空间设为可读写(一定要在FTP之后,不然会有ORA-19722 版本对不上的错误)
6. 如果目标数据库没有对应的USER,创建一下,并授权
7. 开始导入
1
ALTER TABLESPACE TEST_DATA READ ONLY;
2
exp "'/ as sysdba'" file=TEST_DATA.dmp log=test_data.logtransport_tablespace=y tablespaces=TEST_DATA
3
rman target /
CONVERT TABLESPACE TEST_DATA TO PLATFORM 'Linux x86 64-bit'db_file_name_convert='/home/vdqa1/data/test_data.dbf', 'Ntest_data.dbf';
4
FTP xxxxx
Bin
Put Ntest_data.dbf
Put TEST_DATA.dmp
5
ALTER TABLESPACE TEST_DATA READ WRITE;
6
CREATE USER testerIDENTIFIED BY tester ;
GRANT CONNECT,Resourceto tester ;
7.
imp "'/ as sysdba'" file=TEST_DATA.dmp log=imp_test_data.logtransport_tablespace=y datafiles='/home/oracle/11.2.0.4/dbs/Ntest_data.dbf'
使用DATA PUMP:
2
create or replace directory dpump_dir AS '/home/XXX/admin/dpump';
grant read, write on directory dpump_dir to sys;
expdp userid=\'sys as sysdba\' DUMPFILE=Tester_2_19.dmp DIRECTORY=dpump_dir transport_tablespaces = TEST_DATA
create or replace directory dpump_dir AS '/home/XXX/admin/dpump';
grant read, write on directory dpump_dir to sys;
impdp userid=\'sys as sysdba\' DUMPFILE=Tester_2_19.dmp DIRECTORY=dpump_dir transport_datafiles = '/home/oracle/11.2.0.4/dbs/Ntest_data.dbf' REMAP_SCHEMA=tester:tester