2019独角兽企业重金招聘Python工程师标准>>> 
一、逻辑备份相关设置
1.逻辑备份主要是编码格式设置
export ORACLE_SID=
export NLS_LANG=
select PROPERTY_NAME,PROPERTY_VALUE from database_properties;
二、EXP/IMP客户端工具导出导入实验
1.导出导入用户的对象
exp scott/tiger@ipemsdb file=/u01/app/backdir/logico/scott_lxtb1_lxtb11.dmp tables=lxtb1,lxtb11 log=/u01/app/backdir/logico/scott_lxtb1_lxtb11.log [rows=n]
只导入表结构及数据[只导入表结构]，导入前删掉对应对象，不能添加导入
imp scott/tiger@ipemsdb file=/u01/app/backdir/logico/scott_lxtb1_lxtb11.dmp tables=lxtb1,lxtb11 log=/u01/app/backdir/logico/in_scott_lxtb1_lxtb11.log [rows=n]
2.导出导入方案
exp scott/tiger@ipemsdb owner=scott file=/u01/app/backdir/logico/owner_scott.dmp log=/u01/app/backdir/logico/owner_scott.log
导入自己的方案，重建用户后，导入之前用户导出自己的方案
imp scott/tiger@ipemsdb file=/u01/app/backdir/logico/owner_scott.dmp log=/u01/app/backdir/logico/in_owner_scott.log full=y
导出导入其他用户方案
exp system/oracle@ipemsdb owner=(hr,scott) file=/u01/app/backdir/logico/hr_scott.dmp log=/u01/app/backdir/logico/hr_scott.log
imp system/oracle@ipemsdb file=/u01/app/backdir/logico/hr_scott.dmp log=/u01/app/backdir/logico/in_hr_scott.log fromuser=hr,scott touser=hr,scott[full=y]
3.导出导入全库
exp system/oracle@ipemsdb file=/u01/app/backdir/logico/exp_full.dmp log=/u01/app/backdir/logico/exp_full.log full=y inctype=complete
imp system/oracle@ipemsdb file=/u01/app/backdir/logico/exp_full.dmp log=/u01/app/backdir/logico/in_exp_full.log full=y ignore=y
参考资料：http://blog.csdn.net/wwww1988600/article/details/14120949
三、EXPDP/IMPDP服务端工具导出导入实验
1.设置导出导入目录
create directory dump_dir as '/u01/app/backdir/logico';
grant read , write on directory dump_dir to scott;
drop directory dump_dir;
2.导出导入表
expdp scott/tiger directory=dump_dir dumpfile=tab.dmp TABLES=dept,emp logfile=expdp_tab.log
impdp scott/tiger directory=dump_dir dumpfile=tab.dmp tables=dept,emp logfile=impdp_tab.log
impdp system/oracle directory=dump_dir dumpfile=tab.dmp tables=dept,emp logfile=impdp_tab.log remap_schema=SCOTT:scott_a
3.导出导入方案，导入时可以不用创建方案
expdp system/oracle directory=dump_dir dumpfile=schema.dmp schemas=scott_a,scott logfile=e_schema.log
impdp system/oracle directory=dump_dir dumpfile=schema.dmp schemas=scott_a,scott logfile=i_schema.log
4.导出导入数据库,不会导出sys，ordsys，ordplugins，ctxsys。mdsys，lbacsys，xdb等方案对象
expdp system/oracle directory=dump_dir dumpfile=full.dmp full=y
impdp system/oracle directory=dump_dir dumpfile=full.dmp full=y
四、迁移表空间（跨库迁移）
1.使用expdp/impdp数据泵，字符集自动转换，不用特殊设置
导出库搭建测试数据，包括用户，表存放同一表空间，
create tablespace oltp datafile '/u01/app/oracle/oradata/ipemsdb/datafile/oltp01.dbf' size 20M;
create user trans identified by trans default tablespace oltp;
grant connect,resource to trans;
create table trans.emp as select * from scott.emp;
create table trans.dept as select * from scott.emp;
select USERNAME,DEFAULT_TABLESPACE from dba_users where USERNAME='TRANS';
select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where owner='TRANS';
两库创导出导入目录并授予操作权限
mkdir -p /home/oracle/dir
create directory dir as '/home/oracle/dir';
grant read , write on directory dir to public;
目标上创建用户、解锁、授权（无法指定用户的默认表空间）
create user trans identified by trans;
alter user trans account unlock;
grant connect,resource to trans;
检查自包含，如存在清理包含（删除外键）
execute dbms_tts.transport_set_check('OLTP',true);
select * from transport_set_violations;
查数据文件，将表空间设为只读
alter tablespace oltp read only;
expdp导出表空间
expdp system/oracle DUMPFILE=oltp.dmp DIRECTORY=dir TRANSPORT_TABLESPACES=oltp
拷贝导出的dump文件 oltp.dump 至新服务器目录，同时拷贝数据文件至对应目录
scp oltp.dmp 192.168.0.200:/home/oracle/dir/
scp /u01/app/oracle/oradata/ipemsdb/datafile/oltp01.dbf 192.168.0.200:/u01/app/oracle/oradata/ipemsdb/datafile/
expdp导入指定数据文件里（导入后用户的默认表空间为users对象表空为导入表空间）
impdp system/oracle dumpfile=oltp.dmp directory=dir TRANSPORT_DATAFILES='/u01/app/oracle/oradata/ipemsdb/datafile/oltp_01.dbf'
表空间设为可读写模式;
alter tablespace oltp read write;
2.使用exp/imp导入导出
检查 NLS_LANG 设置确认字符集
搭建测试数据
检查自包含
execute dbms_tts.transport_set_check('OLTP',true);
select * from transport_set_violations;
设置表空间未只读模式
alter tablespace oltp read only;
只能用sys用户操作 sys as sysdba
exp导出表空间
exp file=/u01/app/backdir/logico/exp_oltp.dmp tablespaces=oltp TRANSPORT_TABLESPACE=y
Username: sys as sysdba
Password:
拷贝 oltp.dump, 数据文件
imp导入
imp file=/u01/app/dir/exp_oltp.dmp tablespaces=oltp TRANSPORT_TABLESPACE=y datafiles='/u01/app/oracle/oradata/ipemsdb/datafile/oltp_01.dbf'
Username: sys as sysdba
Password:
设置表空间为读写模式
alter tablespace oltp read write;
五、过程错误收集
ORA-00600: 内部错误代码, 参数: [kcratr1_lastbwr], [], [], [], [], [], [], []
启动时报以上错误，多半是操作系统问题，如机器名改了等
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_IMPORT_TRANSPORTABLE_01 for user SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20151127071451" and "KUPC$S_1_20151127071451" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1555
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
检查表空间是否空间不足，sga_target是否过小
