测试环境下模拟真实数据库数据移植
实验目的：实验数据库数据移植
实验环境：
源数据库:两个节点的RAC数据库，数据库版本11203
目标数据库：单实例数据库，版本11203
要求：源数据库schema所在表空间为USERS,移植到目标数据库后调整为新表空间APP_FGPS
1、实验准备
1.1、倒出数据库
[oracle@rac1 mydump]$ expdp ARCER/ARCER dumpfile=fgps.dmp directory=dmp_dir logfile=fgps.log Export: Release 11.2.0.1.0 - Production on Tue Jul 9 15:31:31 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "ARCER"."SYS_EXPORT_SCHEMA_01": ARCER/******** dumpfile=fgps.dmp directory=dmp_dir logfile=fgps.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.5 MB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ . . exported "ARCER"."FRX_OA_WORKLOG" 43.15 KB 89 rows . . exported "ARCER"."FRX_OA_ATTENDANCE" 353.5 KB 4486 rows . . exported "ARCER"."NETSTORE" 66.53 KB 243 rows . . exported "ARCER"."NETSTORE_INCOMEEXPENDITURELIST" 71.5 KB 266 rows . . exported "ARCER"."COILS_ASSETS_DEPRECIATION" 19.81 KB 55 rows . . exported "ARCER"."COILS_CODE_ASSESS" 12.84 KB 38 rows . . exported "ARCER"."COILS_COMPANY_LICENSE" 13.57 KB 16 rows . . exported "ARCER"."COILS_EMP" 6.023 KB 8 rows . . exported "ARCER"."COILS_LOG_ASSESS" 11.32 KB 14 rows . . exported "ARCER"."COILS_STORES" 13.66 KB 37 rows . . exported "ARCER"."COMPANY_ANNOUNCEMENT" 11.86 KB 3 rows . . exported "ARCER"."CRM_CUSTOMER" 23.26 KB 52 rows . . exported "ARCER"."FGPS_EMPLOYEE" 11.57 KB 35 rows . . exported "ARCER"."FGPS_FINANCEUNIT" 7.218 KB 3 rows . . exported "ARCER"."FGPS_GROUP" 8.914 KB 20 rows . . exported "ARCER"."FGPS_GROUP_VS_MENU" 7.632 KB 212 rows . . exported "ARCER"."FGPS_MENU" 11.56 KB 51 rows . . exported "ARCER"."FGPS_MYRECEIVER" 14.09 KB 40 rows . . exported "ARCER"."FGPS_PEOPLE" 34.10 KB 800 rows . . exported "ARCER"."FGPS_SYSTEMUSER" 10.39 KB 7 rows . . exported "ARCER"."FGPS_USER" 14.90 KB 36 rows . . exported "ARCER"."FGPS_USERGRANTACCOUNT" 11.34 KB 30 rows . . exported "ARCER"."FGPS_VOUCHER" 29.18 KB 68 rows . . exported "ARCER"."FRX_DEV_SUGGEST" 10.23 KB 26 rows . . exported "ARCER"."FRX_OA_DICT" 40.74 KB 486 rows . . exported "ARCER"."FRX_OA_LOG_TEMPLATE" 10.60 KB 26 rows . . exported "ARCER"."INVESTMENT_INCOME_STATEMENT" 19.35 KB 57 rows . . exported "ARCER"."SYSTEM_GROUP" 9.265 KB 35 rows . . exported "ARCER"."SYSTEM_MENU" 8.617 KB 34 rows . . exported "ARCER"."SYS_CATEGORY" 9.234 KB 33 rows . . exported "ARCER"."SYS_GROUP_MENU_CORRESPONDENCE" 6.429 KB 100 rows . . exported "ARCER"."WAITTINGHANDLER" 19.37 KB 88 rows . . exported "ARCER"."WAITTING_HANDLER_DETAIL" 13.33 KB 100 rows Master table "ARCER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for ARCER.SYS_EXPORT_SCHEMA_01 is: /home/oracle/mydump/fgps.dmp Job "ARCER"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:32:28 [oracle@rac1 mydump]$ ll total 2360 -rw-r----- 1 oracle asmadmin 172032 Jul 9 15:17 arcer.dmp -rw-r--r-- 1 oracle asmadmin 1394 Jul 9 15:17 arcer.log -rw-r----- 1 oracle asmadmin 2224128 Jul 9 15:32 fgps.dmp -rw-r--r-- 1 oracle asmadmin 4884 Jul 9 15:32 fgps.log
1.2、ftp上传到目标数据库服务器
2、创建目录对象的物理目录/home/oracle/dmp_dir、表空间app_fgps
11203ora-> id uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1300(dba),1301(oper) 11203ora-> ll total 4 drwxr-xr-x 8 oracle oinstall 4096 Sep 22 2011 database 11203ora-> pwd /home/oracle 11203ora-> mkdir mydmp 11203ora-> ll total 8 drwxr-xr-x 8 oracle oinstall 4096 Sep 22 2011 database drwxr-xr-x 2 oracle oinstall 4096 Jul 10 00:08 mydmp 11203ora-> cd mydmp/ 11203ora-> ll total 0 11203ora-> pwd /home/oracle/mydmp 11203ora->
11203ora-> id uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1300(dba),1301(oper) 11203ora-> sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 10 00:09:54 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> CREATE TABLESPACE APP_FGPS DATAFILE '/u01/app/oracle/oradata/testdb/DATACENTER01.dbf' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE 500M; Tablespace created. SQL> col name for a60 SQL> set linesize 200 SQL> select name,status from v$datafile; NAME STATUS ------------------------------------------------------------ ------- /u01/app/oracle/oradata/testdb/system01.dbf SYSTEM /u01/app/oracle/oradata/testdb/sysaux01.dbf ONLINE /u01/app/oracle/oradata/testdb/undotbs01.dbf ONLINE /u01/app/oracle/oradata/testdb/users01.dbf ONLINE /u01/app/oracle/oradata/testdb/example01.dbf ONLINE /u01/app/oracle/oradata/testdb/DATACENTER01.dbf ONLINE 6 rows selected. SQL> col file_name for a60 SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME ------------------------------------------------------------ ------------------------------ /u01/app/oracle/oradata/testdb/users01.dbf USERS /u01/app/oracle/oradata/testdb/undotbs01.dbf UNDOTBS1 /u01/app/oracle/oradata/testdb/sysaux01.dbf SYSAUX /u01/app/oracle/oradata/testdb/system01.dbf SYSTEM /u01/app/oracle/oradata/testdb/example01.dbf EXAMPLE /u01/app/oracle/oradata/testdb/DATACENTER01.dbf APP_FGPS 6 rows selected. SQL>
3、创建用户ARCER、用户授权、创建该用户下的目录对象
SQL> create user ARCER identified by ARCER default tablespace app_fgps; User created. SQL> grant connect,dba,resource to ARCER; Grant succeeded. SQL> conn ARCER/ARCER Connected. SQL> create directory dmp_dir as '/home/oracle/mydmp/'; Directory created. SQL> select * from tab; no rows selected SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
4、执行导入操作
11203ora-> ll total 2180 -rw-r--r-- 1 oracle oinstall 2224128 Jul 9 15:32 fgps.dmp -rw-r--r-- 1 oracle oinstall 5882 Jul 9 15:59 fgps.log 11203ora-> pwd /home/oracle/mydmp 11203ora-> impdp ARCER/ARCER dumpfile=fgps.dmp directory=dmp_dir logfile=fgps.log remap_schema=ARCER:ARCER remap_tablespace=users:app_fgps Import: Release 11.2.0.3.0 - Production on Wed Jul 10 00:14:47 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "ARCER"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "ARCER"."SYS_IMPORT_FULL_01": ARCER/******** dumpfile=fgps.dmp directory=dmp_dir logfile=fgps.log remap_schema=ARCER:ARCER remap_tablespace=users:app_fgps Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"ARCER" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "ARCER"."FRX_OA_WORKLOG" 43.15 KB 89 rows . . imported "ARCER"."FRX_OA_ATTENDANCE" 353.5 KB 4486 rows . . imported "ARCER"."NETSTORE" 66.53 KB 243 rows . . imported "ARCER"."NETSTORE_INCOMEEXPENDITURELIST" 71.5 KB 266 rows . . imported "ARCER"."COILS_ASSETS_DEPRECIATION" 19.81 KB 55 rows . . imported "ARCER"."COILS_CODE_ASSESS" 12.84 KB 38 rows . . imported "ARCER"."COILS_COMPANY_LICENSE" 13.57 KB 16 rows . . imported "ARCER"."COILS_EMP" 6.023 KB 8 rows . . imported "ARCER"."COILS_LOG_ASSESS" 11.32 KB 14 rows . . imported "ARCER"."COILS_STORES" 13.66 KB 37 rows . . imported "ARCER"."COMPANY_ANNOUNCEMENT" 11.86 KB 3 rows . . imported "ARCER"."CRM_CUSTOMER" 23.26 KB 52 rows . . imported "ARCER"."FGPS_EMPLOYEE" 11.57 KB 35 rows . . imported "ARCER"."FGPS_FINANCEUNIT" 7.218 KB 3 rows . . imported "ARCER"."FGPS_GROUP" 8.914 KB 20 rows . . imported "ARCER"."FGPS_GROUP_VS_MENU" 7.632 KB 212 rows . . imported "ARCER"."FGPS_MENU" 11.56 KB 51 rows . . imported "ARCER"."FGPS_MYRECEIVER" 14.09 KB 40 rows . . imported "ARCER"."FGPS_PEOPLE" 34.10 KB 800 rows . . imported "ARCER"."FGPS_SYSTEMUSER" 10.39 KB 7 rows . . imported "ARCER"."FGPS_USER" 14.90 KB 36 rows . . imported "ARCER"."FGPS_USERGRANTACCOUNT" 11.34 KB 30 rows . . imported "ARCER"."FGPS_VOUCHER" 29.18 KB 68 rows . . imported "ARCER"."FRX_DEV_SUGGEST" 10.23 KB 26 rows . . imported "ARCER"."FRX_OA_DICT" 40.74 KB 486 rows . . imported "ARCER"."FRX_OA_LOG_TEMPLATE" 10.60 KB 26 rows . . imported "ARCER"."INVESTMENT_INCOME_STATEMENT" 19.35 KB 57 rows . . imported "ARCER"."SYSTEM_GROUP" 9.265 KB 35 rows . . imported "ARCER"."SYSTEM_MENU" 8.617 KB 34 rows . . imported "ARCER"."SYS_CATEGORY" 9.234 KB 33 rows . . imported "ARCER"."SYS_GROUP_MENU_CORRESPONDENCE" 6.429 KB 100 rows . . imported "ARCER"."WAITTINGHANDLER" 19.37 KB 88 rows . . imported "ARCER"."WAITTING_HANDLER_DETAIL" 13.33 KB 100 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ Job "ARCER"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 00:15:18 11203ora->
5、验证数据
11203ora-> sqlplus ARCER/ARCER SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 10 00:16:15 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- COILS_ASSETS_DEPRECIATION TABLE COILS_CODE_ASSESS TABLE COILS_COMPANY_LICENSE TABLE COILS_EMP TABLE COILS_LOG_ASSESS TABLE COILS_STORES TABLE COMPANY_ANNOUNCEMENT TABLE CRM_CUSTOMER TABLE FGPS_EMPLOYEE TABLE FGPS_FINANCEUNIT TABLE FGPS_GROUP TABLE TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- FGPS_GROUP_VS_MENU TABLE FGPS_MENU TABLE FGPS_MYRECEIVER TABLE FGPS_PEOPLE TABLE FGPS_SYSTEMUSER TABLE FGPS_USER TABLE FGPS_USERGRANTACCOUNT TABLE FGPS_VOUCHER TABLE FRX_DEV_SUGGEST TABLE FRX_OA_ATTENDANCE TABLE FRX_OA_DICT TABLE TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- FRX_OA_LOG_TEMPLATE TABLE FRX_OA_WORKLOG TABLE INVESTMENT_INCOME_STATEMENT TABLE NETSTORE TABLE NETSTORE_INCOMEEXPENDITURELIST TABLE RECHARGE_SUM_VW VIEW SYSTEM_GROUP TABLE SYSTEM_MENU TABLE SYS_CATEGORY TABLE SYS_GROUP_MENU_CORRESPONDENCE TABLE WAITTINGHANDLER TABLE TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- WAITTING_HANDLER_DETAIL TABLE 34 rows selected. SQL> col segment_name al 30 SP2-0158: unknown COLUMN option "al" SQL> col segment_name a30 SP2-0158: unknown COLUMN option "a30" SQL> col segment_name for a30 SQL> select segment_name,tablespace_name from user_segments; SEGMENT_NAME TABLESPACE_NAME ------------------------------ ------------------------------ CATEGORY_PK_CID APP_FGPS CATEGORY_UC_KEY APP_FGPS CL_ID_PK APP_FGPS CL_NAME_UK APP_FGPS COILS_ASSETS_DEPRECIATION APP_FGPS COILS_CODE_ASSESS APP_FGPS COILS_COMPANY_LICENSE APP_FGPS COILS_EMP APP_FGPS COILS_LOG_ASSESS APP_FGPS COILS_STORES APP_FGPS COMPANY_ANNOUNCEMENT APP_FGPS SEGMENT_NAME TABLESPACE_NAME ------------------------------ ------------------------------ CRM_CID_PK APP_FGPS CRM_CUSTOMER APP_FGPS DICT_ID APP_FGPS FGPS_EMPLOYEE APP_FGPS FGPS_FINANCEUNIT APP_FGPS FGPS_GROUP APP_FGPS FGPS_GROUP_VS_MENU APP_FGPS FGPS_MENU APP_FGPS FGPS_MYRECEIVER APP_FGPS FGPS_PEOPLE APP_FGPS FGPS_SYSTEMUSER APP_FGPS SEGMENT_NAME TABLESPACE_NAME ------------------------------ ------------------------------ FGPS_USER APP_FGPS FGPS_USERGRANTACCOUNT APP_FGPS FGPS_VOUCHER APP_FGPS FRX_DEV_SUGGEST APP_FGPS FRX_OA_ATTENDANCE APP_FGPS FRX_OA_DICT APP_FGPS FRX_OA_LOG_TEMPLATE APP_FGPS FRX_OA_WORKLOG APP_FGPS ID_PK APP_FGPS INVESTMENT_INCOME_STATEMENT APP_FGPS NETSTORE APP_FGPS SEGMENT_NAME TABLESPACE_NAME ------------------------------ ------------------------------ NETSTORE_INCOMEEXPENDITURELIST APP_FGPS PK_ATTENDANCE_ID APP_FGPS PK_CID APP_FGPS PK_EID APP_FGPS PK_FGPS_FINANCEUNIT APP_FGPS PK_FGPS_GROUPID APP_FGPS PK_FGPS_MENUID APP_FGPS PK_FGPS_SYSTEMUSER APP_FGPS PK_FGPS_USERGRANTACCOUNT APP_FGPS PK_FGPS_USERID APP_FGPS PK_FGPS_VOUCHER APP_FGPS SEGMENT_NAME TABLESPACE_NAME ------------------------------ ------------------------------ PK_GROUPID APP_FGPS PK_ID APP_FGPS PK_ID_FRX_DEV_SUGGEST APP_FGPS PK_LID APP_FGPS PK_LOG_CATEGORY_TEMPLATE_ID APP_FGPS PK_MENUID APP_FGPS PK_NETSTORE_INCOMEEXPENDITUREL APP_FGPS PK_SYS_ASSET_NO APP_FGPS STORE_ID_PK APP_FGPS SYSTEM_GROUP APP_FGPS SYSTEM_MENU APP_FGPS SEGMENT_NAME TABLESPACE_NAME ------------------------------ ------------------------------ SYS_C0011356 APP_FGPS SYS_C0011372 APP_FGPS SYS_C0011378 APP_FGPS SYS_C0011379 APP_FGPS SYS_CATEGORY APP_FGPS SYS_GROUP_MENU_CORRESPONDENCE APP_FGPS WAITTINGHANDLER APP_FGPS WAITTING_HANDLER_DETAIL APP_FGPS WORKLOG_PK_WID APP_FGPS 64 rows selected. SQL>
