oracle 备份恢复 08逻辑备份恢复

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是否过小

转载于:https://my.oschina.net/peakfang/blog/2245445