----源庫 prod
---目標(biāo)庫 catdb
[oracle@ora11g ~]$ sqlplus sys/oracle@catdb as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 4 15:16:24 2015 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 name from v$tablespace; NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 USERS TEMP CATALOGTBS EXPTEST EXPTEST1 8 rows selected. SQL> select userenv('LANGUAGE') FROM dual; USERENV('LANGUAGE') ---------------------------------------------------- AMERICAN_AMERICA.AL32UTF8--遷移源庫prod test表空間到目標(biāo)庫catdb
-------檢測tablespace 的自包含 prod
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('test',true); PL/SQL procedure successfully completed. SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; no rows selected--修改表空間為read only狀態(tài)
SQL> alter tablespace test read only; Tablespace altered.--使用數(shù)據(jù)泵傳輸表空間
[oracle@ora11g ~]$ expdp system/oracle@prod directory=dump dumpfile=expdp_tbs_test_%U.dmp logfile=ttbs.log TRANSPORT_TABLESPACES = test Export: Release 11.2.0.4.0 - Production on Sat Jul 4 20:17:48 2015 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/********@prod directory=dump dumpfile=expdp_tbs_test_%U.dmp logfile=ttbs.log transport_tablespaces=test Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS 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: /ora_data/dump/expdp_tbs_test_01.dmp ****************************************************************************** Datafiles required for transportable tablespace TEST: /u01/app/oracle/oradata/prod/test02.dbf /u01/app/oracle/oradata/test01_new.dbf Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sat Jul 4 20:18:50 2015 elapsed 0 00:01:01------將源庫傳輸表空間的datafile 傳送到另一個庫上
SQL> select tablespace_name, file_name from dba_data_files; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- USERS /u01/app/oracle/oradata/prod/users01.dbf UNDOTBS1 /u01/app/oracle/oradata/prod/undotbs01.dbf SYSAUX /u01/app/oracle/oradata/prod/sysaux01.dbf SYSTEM /u01/app/oracle/oradata/prod/system01.dbf TEST /u01/app/oracle/oradata/test01_new.dbf TEST /u01/app/oracle/oradata/prod/test02.dbf TEST1 /u01/app/oracle/oradata/prod/test1.dbf EXPTEST /u01/app/oracle/oradata/prod/exptest01.dbf EXPTEST1 /u01/app/oracle/oradata/prod/exptest101.dbf 9 rows selected. [oracle@ora11g ~]$ cp /u01/app/oracle/oradata/test01_new.dbf /u01/app/oracle/oradata/catdb/test01.dbf [oracle@ora11g ~]$ cp /u01/app/oracle/oradata/prod/test02.dbf /u01/app/oracle/oradata/catdb/test02.dbf--在目標(biāo)庫上用impdp導(dǎo)入表空間
[oracle@ora11g ~]$ impdp system/oracle@catdb directory=dump dumpfile=expdp_tbs_test_%U.dmp TRANSPORT_DATAFILES='/u01/app/oracle/oradata/catdb/test01.dbf','/u01/app/oracle/oradata/catdb/test02.dbf' Import: Release 11.2.0.4.0 - Production on Sat Jul 4 20:29:22 2015 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 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@catdb directory=dump dumpfile=expdp_tbs_test_%U.dmp TRANSPORT_DATAFILES=/u01/app/oracle/oradata/catdb/test01.dbf,/u01/app/oracle/oradata/catdb/test02.dbf Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sat Jul 4 20:29:28 2015 elapsed 0 00:00:06--驗(yàn)證
[oracle@ora11g ~]$ sqlplus sys/oracle@catdb as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 4 20:34:55 2015 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 name from v$tablespace; NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 USERS TEMP CATALOGTBS EXPTEST EXPTEST1 TEST 9 rows selected. SQL> conn scott/oracle@catdb; Connected. SQL> select table_name, tablespace_name from user_tables where tablespace_name='TEST'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ T1 TEST TEST TEST T6 TEST T3 TEST T1_OLD TEST EMPLOYEE TEST EMP TEST DEPT TEST 8 rows selected.--最后記得把源庫和目標(biāo)庫的test表空間設(shè)置為read write模式
SQL> conn / as sysdba Connected. SQL> select name from v$database; NAME --------- PROD SQL> alter tablespace test read write; Tablespace altered. SQL> conn sys/oracle@catdb as sysdba Connected. SQL> select name from v$database; NAME --------- CATDB SQL> alter tablespace test read write; Tablespace altered. SQL>遷移最后完成O(∩_∩)O~
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價比高”等特點(diǎn)與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。
重慶分公司
重慶分公司
