ORACLE数据库导入导出——NC

2020年5月16日 1425点热度 0人点赞 0条评论

exp导出审查

[oracle@*TWE~]$exp ****/****#EDC file='/home/BACKUP/exp_NC/20210129.exp' log='/home/BACKUP/exp_NC/20210129.log' compress=y statistics=none

一、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;
create user YCZBNZ identified by "******" default tablespace NNC_DATA01 temporary tablespace TEMP profile DEFAULT;
create user YCZBWZ identified by "****" default tablespace NNC_DATA01 temporary tablespace TEMP profile DEFAULT;
grant connect,dba,resource to YCZBNZ;
grant connect,dba,resource to YCZBWZ ;

3、给用户授权sys权限

授予用户sys权限（有些角色，或者视图，本用户是没有权限的，可能是有sys创建的，所以需要加上，导入时此不报错）

（1）、YCZBWZ授予sys权限

 grant execute on SYS.DBMS_ALERT to YCZBWZ;
 grant execute on SYS.DBMS_AQ to YCZBWZ with grant option;
 grant execute on SYS.DBMS_AQADM to YCZBWZ with grant option;
 grant execute on SYS.DBMS_AQELM to YCZBWZ with grant option;
 grant execute on SYS.DBMS_AQ_IMPORT_INTERNAL to YCZBWZ with grant option;
 grant execute on SYS.DBMS_DEFER_IMPORT_INTERNAL to YCZBWZ;
 grant execute on SYS.DBMS_REPCAT to YCZBWZ;
 grant execute on SYS.DBMS_RULE_EXIMP to YCZBWZ with grant option;
 grant execute on SYS.DBMS_SQL to YCZBWZ;
 grant execute on SYS.DBMS_SYS_ERROR to YCZBWZ;
 grant execute on SYS.DBMS_SYS_SQL to YCZBWZ;
 grant execute on SYS.DBMS_TRANSFORM_EXIMP to YCZBWZ with grant option;
 grant select, insert, update, delete, references, alter, index on SYS.INCEXP to YCZBWZ;
 grant select, insert, update, delete, references, alter, index on SYS.INCFIL to YCZBWZ;
 grant select, insert, update, delete, references, alter, index on SYS.INCVID to YCZBWZ;
 grant select on WMSYS.WM$UDTRIG_INFO to YCZBWZ;
 grant aq_administrator_role to YCZBWZ with admin option;
 grant dba to YCZBWZ with admin option;
 grant unlimited tablespace to YCZBWZ with admin option;

（2）、YCZBNZ授予sys权限：

 grant execute on SYS.DBMS_ALERT to YCZBNZ;
 grant execute on SYS.DBMS_AQ to YCZBNZ with grant option;
 grant execute on SYS.DBMS_AQADM to YCZBNZ with grant option;
 grant execute on SYS.DBMS_AQELM to YCZBNZ with grant option;
 grant execute on SYS.DBMS_AQ_IMPORT_INTERNAL to YCZBNZ with grant option;
 grant execute on SYS.DBMS_DEFER_IMPORT_INTERNAL to YCZBNZ;
 grant execute on SYS.DBMS_REPCAT to YCZBNZ;
 grant execute on SYS.DBMS_RULE_EXIMP to YCZBNZ with grant option;
 grant execute on SYS.DBMS_SQL to YCZBNZ;
 grant execute on SYS.DBMS_SYS_ERROR to YCZBNZ;
 grant execute on SYS.DBMS_SYS_SQL to YCZBNZ;
 grant execute on SYS.DBMS_TRANSFORM_EXIMP to YCZBNZ with grant option;
 grant select, insert, update, delete, references, alter, index on SYS.INCEXP to YCZBNZ;
 grant select, insert, update, delete, references, alter, index on SYS.INCFIL to YCZBNZ;
 grant select, insert, update, delete, references, alter, index on SYS.INCVID to YCZBNZ;
 grant select on WMSYS.WM$UDTRIG_INFO to YCZBNZ;
 grant aq_administrator_role to YCZBNZ with admin option;
 grant dba to YCZBNZ with admin option;
 grant unlimited tablespace to YCZBNZ with admin option;

4、创建dmp路径

 create directory backup_NCP as '/home/BACKUP/backup_NCP';
 GRANT READ,WRITE ON DIRECTORY backup_NCP to YCZBNZ; 
 GRANT READ,WRITE ON DIRECTORY backup_NCP to YCZBWZ;

5、导入，导出命令

 expdp yczbnz/Yczbjtnz1qaz#EDC@127.0.0.1/orcl DIRECTORY=backup_NCP DUMPFILE=YCZBNZ-20181016.dmpdp  logfile=YCZBNZ-20181016_dmpdp.log
 expdp yczbnz/Yczbjtnz1qaz#EDC@127.0.0.1/orcl DIRECTORY=backup_NCP DUMPFILE=YCZBNZ-20181016.dmpdp  logfile=YCZBNZ-20181016_dmpdp.log
 impdp yczbnz/Yczbjtnz1qaz#EDC@127.0.0.1/orcl    DIRECTORY=backup_NCP DUMPFILE=YCZBNZ-20181016.dmpdp  logfile=YCZBNZ-20181016_dmpdp.log
 impdp yczbwz/yczb1qaz#EDC@127.0.0.1/orcl    DIRECTORY=backup_NCP DUMPFILE=YCZBWZ-20181016.dmpdp  logfile=YCZBWZ-20181016_dmpdp.log

如果不好用，可以直接sys导

 impdp sys/oraclehdsoft@orcl directory=backup_NCP dumpfile=YCZBNZ-20181016.dmpdp REMAP_SCHEMA=YCZBNZ:YCZBNZ
 impdp sys/oraclehdsoft@orcl directory=backup_NCP dumpfile=YCZBWZ-20181016.dmpdp REMAP_SCHEMA=YCZBWZ:YCZBWZ

6、删除表空间（包含数据文件）,用户

 drop tablespace NNC_DATA01 including contents and datafiles

drop user YCZBNZ cascade;

7、存在的问题：

（1）、角色问题

因为用sys创建的select_role，导入时，用的是yczbnz，所以导入失败，手动执行即可：

ORA-01919: role 'SELECT_ROLE' does not exist

Failing sql is: GRANT "SELECT_ROLE" TO "YCZBNZ" WITH ADMIN OPTION

a、创建角色

CREATE ROLE SELECT_ROLE;

b、给角色分配权限

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;

c、创建用户：

create user BigData_Select_NC identified by NCSelectRole;
grant connect,resource to BigData_Select_NC;

d、把角色赋予指定账户

grant SELECT_ROLE to BigData_Select_NC;

e、删除角色

drop role SELECT_ROLE;

f、检查角色的权限

select * from dba_sys_privs where grantee='SELECT_ROLE'

（2）视图问题：

ORA-39082: Object type VIEW:"YCZBNZ"."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

（3）用户问题：

YCZBNZ已经存在：不影响

ORA-31684: Object type USER:"YCZBNZ" already exists

本作品采用知识共享署名 4.0 国际许可协议进行许可