一、expdp,impdp 导入要求：
1、源地址和目标地址版本一致、数据库字符集一致
2.需要给导入用户增加sys的权限（如下三）
二、NC脚本创建：
1、表空间
sql> CREATE tablespace NNC_DATA01 logging DATAFILE'/home/oracle/app/oradata/orcl/NNC_DATA01_1.dbf' size 10240m autoextend on next 1000m ; sql> ALTER tablespace NNC_DATA01 ADD DATAFILE'/home/oracle/app/oradata/orcl/NNC_DATA01_2.dbf' size 10240m autoextend on next 1000m ; sql> CREATE tablespace NNC_DATA02 logging DATAFILE'/home/oracle/app/oradata/orcl/NNC_DATA02.dbf' size 10240m autoextend on next 1000m ; sql> CREATE tablespace NNC_DATA03 logging DATAFILE'/home/oracle/app/oradata/orcl/NNC_DATA03.dbf' size 10240m autoextend on next 1000m ; sql> CREATE tablespace NNC_INDEX01 logging DATAFILE'/home/oracle/app/oradata/orcl/NNC_INDEX01.dbf' size 10240m autoextend on next 1000m ; sql> CREATE tablespace NNC_INDEX02 logging DATAFILE'/home/oracle/app/oradata/orcl/NNC_INDEX02.dbf' size 10240m autoextend on next 1000m ; sql> CREATE tablespace NNC_INDEX03 logging DATAFILE'/home/oracle/app/oradata/orcl/NNC_INDEX03.dbf' size 10240m autoextend on next 1000m ;
2、用户：
create tablespace NNC_DATA01 datafile '/home/oracle/app/oradata/orcl/NNC_DATA01.DBF' size 10000M autoextend on next 1000M maxsize unlimited logging extent management local autoallocate segment space management auto; sqlplus / as sysdba; sql> create user LLLNZ identified by "LLLjtnz1qaz#EDC" default tablespace NNC_DATA01 temporary tablespace TEMP profile DEFAULT; sql> create user LLLWZ identified by "LLL1qaz#EDC" default tablespace NNC_DATA01 temporary tablespace TEMP profile DEFAULT; sql> grant connect,dba,resource to LLLNZ ; sql> grant connect,dba,resource to LLLWZ ;
3、给用户授权sys权限
授予用户sys权限（有些角色，或者视图，本用户是没有权限的，可能是有sys创建的，所以需要加上，导入时此不报错）
（1）LLLWZ授予sys权限
SQL>grant execute on SYS.DBMS_ALERT to LLLWZ; SQL>grant execute on SYS.DBMS_AQ to LLLWZ with grant option; SQL>grant execute on SYS.DBMS_AQADM to LLLWZ with grant option; SQL>grant execute on SYS.DBMS_AQELM to LLLWZ with grant option; SQL>grant execute on SYS.DBMS_AQ_IMPORT_INTERNAL to LLLWZ with grant option; SQL>grant execute on SYS.DBMS_DEFER_IMPORT_INTERNAL to LLLWZ; SQL>grant execute on SYS.DBMS_REPCAT to LLLWZ; SQL>grant execute on SYS.DBMS_RULE_EXIMP to LLLWZ with grant option; SQL>grant execute on SYS.DBMS_SQL to LLLWZ; SQL>grant execute on SYS.DBMS_SYS_ERROR to LLLWZ; SQL>grant execute on SYS.DBMS_SYS_SQL to LLLWZ; SQL>grant execute on SYS.DBMS_TRANSFORM_EXIMP to LLLWZ with grant option; SQL>grant select, insert, update, delete, references, alter, index on SYS.INCEXP to LLLWZ; SQL>grant select, insert, update, delete, references, alter, index on SYS.INCFIL to LLLWZ; SQL>grant select, insert, update, delete, references, alter, index on SYS.INCVID to LLLWZ; SQL>grant select on WMSYS.WM$UDTRIG_INFO to LLLWZ; SQL>grant aq_administrator_role to LLLWZ with admin option; SQL>grant dba to LLLWZ with admin option; SQL>grant unlimited tablespace to LLLWZ with admin option;
（2）LLLNZ授予sys权限：
SQL>grant execute on SYS.DBMS_ALERT to LLLNZ; SQL>grant execute on SYS.DBMS_AQ to LLLNZ with grant option; SQL>grant execute on SYS.DBMS_AQADM to LLLNZ with grant option; SQL>grant execute on SYS.DBMS_AQELM to LLLNZ with grant option; SQL>grant execute on SYS.DBMS_AQ_IMPORT_INTERNAL to LLLNZ with grant option; SQL>grant execute on SYS.DBMS_DEFER_IMPORT_INTERNAL to LLLNZ; SQL>grant execute on SYS.DBMS_REPCAT to LLLNZ; SQL>grant execute on SYS.DBMS_RULE_EXIMP to LLLNZ with grant option; SQL>grant execute on SYS.DBMS_SQL to LLLNZ; SQL>grant execute on SYS.DBMS_SYS_ERROR to LLLNZ; SQL>grant execute on SYS.DBMS_SYS_SQL to LLLNZ; SQL>grant execute on SYS.DBMS_TRANSFORM_EXIMP to LLLNZ with grant option; SQL>grant select, insert, update, delete, references, alter, index on SYS.INCEXP to LLLNZ; SQL>grant select, insert, update, delete, references, alter, index on SYS.INCFIL to LLLNZ; SQL>grant select, insert, update, delete, references, alter, index on SYS.INCVID to LLLNZ; SQL>grant select on WMSYS.WM$UDTRIG_INFO to LLLNZ; SQL>grant aq_administrator_role to LLLNZ with admin option; SQL>grant dba to LLLNZ with admin option; SQL>grant unlimited tablespace to LLLNZ with admin option;
4、创建dmp路径
SQL>create directory backup_NCP as '/home/BACKUP/backup_NCP'; SQL>GRANT READ,WRITE ON DIRECTORY backup_NCP to LLLNZ; SQL>GRANT READ,WRITE ON DIRECTORY backup_NCP to LLLWZ;
5、导入，导出命令
[oracle@LLLNCPOAP ~]$ expdp LLLnz/LLLjtnz1qaz#EDC@127.0.0.1/orcl DIRECTORY=backup_NCP DUMPFILE=LLLNZ-20181016.dmpdp logfile=LLLNZ-20181016_dmpdp.log [oracle@LLLNCPOAP ~]$ expdp LLLnz/LLLjtnz1qaz#EDC@127.0.0.1/orcl DIRECTORY=backup_NCP DUMPFILE=LLLNZ-20181016.dmpdp logfile=LLLNZ-20181016_dmpdp.log [oracle@LLLNCPOAP ~]$ impdp LLLnz/LLLjtnz1qaz#EDC@127.0.0.1/orcl DIRECTORY=backup_NCP DUMPFILE=LLLNZ-20181016.dmpdp logfile=LLLNZ-20181016_dmpdp.log [oracle@LLLNCPOAP ~]$ impdp LLLwz/LLL1qaz#EDC@127.0.0.1/orcl DIRECTORY=backup_NCP DUMPFILE=LLLWZ-20181016.dmpdp logfile=LLLWZ-20181016_dmpdp.log
如果三不好用，可以直接sys导
[oracle@LLLNCPOAP ~]$ impdp sys/oraclehdsoft@orcl directory=backup_NCP dumpfile=LLLNZ-20181016.dmpdp REMAP_SCHEMA=LLLNZ:LLLNZ
[oracle@LLLNCPOAP ~]$ impdp sys/oraclehdsoft@orcl directory=backup_NCP dumpfile=LLLWZ-20181016.dmpdp REMAP_SCHEMA=LLLWZ:LLLWZ
6、删除表空间（包含数据文件）
SQL> drop tablespace NNC_DATA01 including contents and datafiles
7、存在的问题：
（1）角色问题，因为用sys创建的select_role，导入时，用的是LLLnz，所以导入失败，手动执行即可：
ORA-01919: role 'SELECT_ROLE' does not exist
Failing sql is: GRANT "SELECT_ROLE" TO "LLLNZ" WITH ADMIN OPTION
创建角色
--CREATE ROLE SELECT_ROLE;
（2）给角色分配权限
--grant SELECT ANY DICTIONARY to SELECT_ROLE; --grant SELECT ANY TABLE to SELECT_ROLE; --grant SELECT ANY TRANSACTION to SELECT_ROLE; --grant SELECT ANY SEQUENCE to SELECT_ROLE;
（3）创建用户：
--create user BigData_Select_NC identified by NCSelectRole;
--grant connect,resource to BigData_Select_NC;
（4）把角色赋予指定账户
--grant SELECT_ROLE to BigData_Select_NC;
（5）删除角色
--drop role SELECT_ROLE;
（6）检查角色的权限
--select * from dba_sys_privs where grantee='SELECT_ROLE'
测试权限的时候需要加上用户的对象例如
--select count(*) from hr.ex_user;
（7）视图问题：没有自动导入，手动创建即可
ORA-39082: Object type VIEW:"LLLNZ"."V_XX_IDCONTWZPK" created with compilation warnings
解决：
（1）create or replace view V_XX_IDCONTWZPK as select distinct pk_docid ,billpk from xx_idcontrastpk@dblink_wzdb where billtype='gl' and nvl(dr,0)=0 （2）create or replace view v_xx_idcontwzpk as select distinct substr(pk_docid,-20,20) pk_docid ,billpk from xx_idcontrastpk@dblink_wzdb where billtype='gl' and nvl(dr,0)=0
（8）用户问题：LLLNZ已经存在：不影响
ORA-31684: Object type USER:"LLLNZ" already exists
三、OA脚本创建：
1、表空间
无创建，默认
2、用户：
create user LLLoa identified by "LLLjt" ; grant connect,dba,resource to LLLoa ;
3、给用户授权sys权限
授予用户sys权限（有些角色，或者视图，本用户是没有权限的，可能是有sys创建的，所以需要加上，导入时此不报错）
4、LLLWZ授予sys权限
grant execute on SYS.DBMS_ALERT to LLLoa; grant execute on SYS.DBMS_AQ to LLLoa with grant option; grant execute on SYS.DBMS_AQADM to LLLoa with grant option; grant execute on SYS.DBMS_AQELM to LLLoa with grant option; grant execute on SYS.DBMS_AQ_IMPORT_INTERNAL to LLLoa with grant option; grant execute on SYS.DBMS_DEFER_IMPORT_INTERNAL to LLLoa; grant execute on SYS.DBMS_REPCAT to LLLoa; grant execute on SYS.DBMS_RULE_EXIMP to LLLoa with grant option; grant execute on SYS.DBMS_SQL to LLLoa; grant execute on SYS.DBMS_SYS_ERROR to LLLoa; grant execute on SYS.DBMS_SYS_SQL to LLLoa; grant execute on SYS.DBMS_TRANSFORM_EXIMP to LLLoa with grant option; grant select, insert, update, delete, references, alter, index on SYS.INCEXP to LLLoa; grant select, insert, update, delete, references, alter, index on SYS.INCFIL to LLLoa; grant select, insert, update, delete, references, alter, index on SYS.INCVID to LLLoa; grant select on WMSYS.WM$UDTRIG_INFO to LLLoa; grant aq_administrator_role to LLLoa with admin option; grant dba to LLLoa with admin option; grant unlimited tablespace to LLLoa with admin option; select receivedpersons, receivedpersonids from workflow_requestlog
5、创建dmp路径
create directory backup_OAP as '/home/BACKUP/backup_OAP'; GRANT READ,WRITE ON DIRECTORY backup_OAP to LLLoa;
6、导入，导出命令
impdp LLLoa/LLLjtoaq@127.0.0.1/oraecology DIRECTORY=backup_OAP DUMPFILE=LLLOAP-20181016.dmpdp logfile=LLLOAP-20181016-impdp.log EXCLUDE=STATISTICS
如果三不好用，可以直接sys导
impdp sys/oraclehdsoft@orcl directory=backup_NCP dumpfile=LLLNZ-20181016.dmpdp REMAP_SCHEMA=LLLoa:LLLoa impdp sys/oraclehdsoft@orcl directory=backup_NCP dumpfile=LLLWZ-20181016.dmpdp REMAP_SCHEMA=LLLoa:LLLoa
7、出现问题
（1）在进行到SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS如下错误，据说是11R2的bug,直接排除掉即可
impdp LLLoa/LLLjt@127.0.0.1/oraecology DIRECTORY=backup_OAP DUMPFILE=LLLOAP-20181016.dmpdp logfile=LLLOAP-20181016-impdp.log EXCLUDE=STATISTICS
expdp LLLoa/LLLjt@oraecology schemas=LLLoa directory=DUMP_OA dumpfile=OA_8_0_2_expdp.dmp logfile=OA_8_0_2_expdp.log' Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [TABLE_STATISTICS] ORA-06502: PL/SQL: numeric or value error LPX-00225: end-element tag "HIST_GRAM_LIST_ITEM" does not match start-element tag "EPVALUE" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPW$WORKER", line 9001 ----- PL/SQL Call Stack ----- object line object handle number name 0xde985aa0 20462 package body SYS.KUPW$WORKER 0xde985aa0 9028 package body SYS.KUPW$WORKER 0xde985aa0 16665 package body SYS.KUPW$WORKER 0xde985aa0 3956 package body SYS.KUPW$WORKER 0xde985aa0 9725 package body SYS.KUPW$WORKER 0xde985aa0 1775 package body SYS.KUPW$WORKER 0xdd42bc68 2 anonymous block ORA-39097: Data Pump job encountered unexpected error -1427 ORA-39065: unexpected master process exception in DISPATCH ORA-01427: single-row subquery returns more than one row
（2）--ORA-39082:
--ORA-39082: Object type ALTER_PROCEDURE:"LLLOA"."HRMROLEMEMBERSSHARE" created with compilation warnings --ORA-39082: Object type ALTER_PROCEDURE:"LLLOA"."" created with compilation warnings --ORA-39082: Object type ALTER_PROCEDURE:"LLLOA"."PRO_GETEMPLFUNCOBJMAXRIGHTTYPE" created with compilation warnings ORA-39082: Object type VIEW:"LLLOA"."VIACCOUNTTITLE" created with compilation warnings ORA-39082: Object type VIEW:"LLLOA"."VIACCOUNTTITLETOCOMPANY" created with compilation warnings ORA-39082: Object type TRIGGER:"LLLOA"."IMAGELIBRARY_ID_TRI" created with compilation warnings ORA-39082: Object type TRIGGER:"LLLOA"."IMAGELIBRARY_ID_TRI" created with compilation warnings ORA-39082: Object type TRIGGER:"LLLOA"."IMAGEFOLDER_ID_TRI" created with compilation warnings ORA-39082: Object type TRIGGER:"LLLOA"."IMAGEFOLDER_ID_TRI" created with compilation warnings ORA-39082: Object type TRIGGER:"LLLOA"."HRM_RP_SUB_TEP_CON_TRIGGER" created with compilation warnings ORA-39082: Object type TRIGGER:"LLLOA"."HRM_RP_SUB_TEP_CON_TRIGGER" created with compilation warnings ORA-39082: Object type TRIGGER:"LLLOA"."TRG_TBACCOUNTTITLETYPE_INSERT" created with compilation warnings ORA-39082: Object type TRIGGER:"LLLOA"."TRG_TBACCOUNTTITLETYPE_INSERT" created with compilation warnings ORA-39082: Object type TRIGGER:"LLLOA"."COWORKQUITER_TRIGGER" created with compilation warnings ORA-39082: Object type TRIGGER:"LLLOA"."COWORKQUITER_TRIGGER" created with compilation warnings ORA-39082: Object type TRIGGER:"LLLOA"."HRMPROVINCE_TRIGGER" created with compilation warnings ORA-39082: Object type TRIGGER:"LLLOA"."HRMPROVINCE_TRIGGER" created with compilation warnings ORA-39082: Object type TRIGGER:"LLLOA"."HRMCITY_TRIGGER" created with compilation warnings ORA-39082: Object type TRIGGER:"LLLOA"."HRMCITY_TRIGGER" created with compilation warnings
8、删除用户及对象
drop user itp cascade;
9、exp,expdp,imp,impdp
exp,imp导出导入到oa表中目前ok expdp,impdp导出导入到oA表中，有表workflow_requestlog的列receivedpersons, receivedpersonids 值对调了
文章评论