Oracle---逻辑备份

逻辑备份：以逻辑结构（segment）为单位备份数据

逻辑备份的作用：
跨数据库移动表
跨用户移动表
为测试库保留历史数据状态
传输表空间
对数据库进行版本升级

exp/imp : 数据库库open之前不能使用逻辑导出/逻辑导入
exp（逻辑导出）: 对数据库做select，把查询结果写成操作系统二进制文件！*如果同名文件存在，直接覆盖！
imp（逻辑导入）: 读二进制的备份文件，对数据库做create table & insert ...

*注意字符集!
select userenv('language') from dual;
export LANG=zh_CN.utf8
export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

创建保存导出文件的目录
su - oracle
mkdir -p /home/oracle/expbk

查看帮助
$ exp help=y
$ imp help=y

实验1：导出单张表
exp userid=scott/tiger tables=ob1 file=/home/oracle/expbk/ob1.dmp buffer=10485760 log=/home/oracle/expbk/exp_ob1.log

实验2：导出远程数据库的表
exp userid=scott/tiger@5 tables=emp file=emp.dmp buffer=1048576 log=exp_emp.log

实验3：导入单张表
imp userid=scott/tiger tables=emp file=emp.dmp buffer=1048576 log=imp_emp.log

实验4：将表导入远程数据库
imp userid=scott/tiger@5 tables=emp file=emp.dmp buffer=1048576 log=imp_emp.log

实验5：使用shell脚本调用exp
vi /home/oracle/expbk/expob1.sh
----------------------------------------------------------------------------------
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=db01
export LANG=zh_CN.utf8
export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
name=`date '+%Y%m%d_%H%M%S'`
`$ORACLE_HOME/bin/exp userid=scott/tiger tables=ob1 file=/home/oracle/expbk/ob1\_$name.dmp buffer=10485760 feedback=10000 log=/home/oracle/expbk/ob1\_$name.log`
----------------------------------------------------------------------------------
chmod +x /home/oracle/expbk/expob1.sh

定时测试:
crontab -e
*/5 * * * * /home/oracle/expbk/expob1.sh

service crond restart

实验6：使用主机管道压缩备份文件
mknod /home/oracle/expbk/exp_pipe p
exp userid=scott/tiger tables=ob1 log=/home/oracle/expbk/exp_ob1.log file=/home/oracle/expbk/exp_pipe & gzip </home/oracle/expbk/exp_pipe> exp_ob1.dmp.gz

实验7：只显示导入的过程而不导入数据 show=y 打印导入动作的ddl语句
imp userid=scott/tiger@5 tables=e01 file=e01.dmp buffer=1048576 log=imp_emp.log show=y

实验8：导出时增加反馈信息 feedback=10000 每成功导出1万行屏幕上打一个点
exp userid=scott/tiger@5 tables=e01 file=e01.dmp buffer=1048576 log=exp_e01.log feedback=10000

导出数据时使用where条件：
exp scott/tiger tables=ob1 file=ob1_tab.dmp query=\'where object_type=\'\'TABLE\'\'\' buffer=1048576

实验9：闪回导出，需要使用system用户
exp userid=system/uplooking tables=scott.ob1 file=ob1_fl.dmp buffer=1048576 log=exp_ob1_fl.log feedback=10000 flashback_time=\"to_timestamp\(\'20170919 11:50:00\',\'yyyymmdd hh24:mi:ss\'\)\"

exp userid=system/uplooking@5 tables=scott.e01 file=e01_fl.dmp buffer=1048576 log=exp_e01_fl.log feedback=10000 flashback_time=\"to_timestamp\(\'20170621 14:29:00\',\'yyyymmdd hh24:mi:ss\'\)\"

imp userid=system/uplooking@5 full=y file=e01.dmp buffer=1048576 log=imp_e01.log feedback=10000

imp userid=system/uplooking@5 tables=e01 fromuser=scott touser=scott file=e01.dmp buffer=1048576 log=imp_e01.log feedback=10000

实验10：导入时更换表空间
1.在新的表空间自己先创建空表，使用ignore=y追加数据

2.回收unlimited tablespace,修改用户默认表空间，打开用户在表空间下的空间使用份额，关闭延迟段创建
revoke unlimited tablespace from scott;
alter user scott default tablespace example;
alter user scott quota unlimited on example;
alter system set deferred_segment_creation=false;
-------------------------------------------------
IMP-00058: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'TBS250'
-------------------------------------------------

实验11：跨用户导入数据 fromuser-->touser,导入时用户的权限可以高于导出时的用户权限
imp userid=system/uplooking@5 tables=e01 fromuser=scott touser=tom file=e01.dmp buffer=1048576 log=imp_e01.log feedback=10000

实验12：导出元数据compress=n
compress=y的含义是将表的当前大小作为将来创建表的初始大小
exp scott/tiger tables=ob1 file=/home/oracle/expbk/ob1_metadata2.dmp rows=n compress=n log=/home/oracle/expbk/ob1_metadata2.log

实验13：导出多张表 CONSISTENT 多张表的导出捆绑为一个读一致性
exp scott/tiger tables=dept,emp CONSISTENT=y file=/home/oracle/expbk/ed.dmp buffer=1048576 log=/home/oracle/expbk/ed.log

实验14：导入多张表 full=y将dmp文件中的所有表全都导入数据库
imp scott/tiger file=/home/oracle/expbk/ed.dmp full=y buffer=1048576 log=/home/oracle/expbk/ed.log

实验15：跨用户导入全部
imp system/oracle fromuser=scott touser=tom file=/home/oracle/expbk/ed.dmp buffer=1048576 log=/home/oracle/expbk/imp_e.log

实验16：跨用户导入指定表
imp system/oracle fromuser=scott touser=tom tables=emp file=/home/oracle/expbk/ed.dmp buffer=1048576 log=/home/oracle/expbk/imp_e.log

实验17：导出用户模式，需要临时表空间否则程序代码导出失败
exp scott/tiger owner=scott file=scott.dmp buffer=1048576 log=exp_scott.log

drop user scott cascade;
grant connect,resource to scott identified by tiger;

imp scott/tiger file=scott.dmp full=y buffer=1048576 log=imp_scott.log
imp scott/tiger file=scott.dmp tables=dept,emp buffer=1048576 log=imp_scott.log

CREATE INDEX "I_OB2_ID" ON "OB2" ("OBJECT_ID" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 10485760 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TBS04" LOGGING

begin
  for i in 1..100 loop
    insert into t01 values (round(dbms_random.value(1,100)));
  end loop;
  commit;
end;
/

实验18：导出表空间模式
exp system/oracle tablespaces=tbs02 file=tbs02.dmp buffer=1048576 log=exp_tbs02.log

drop tablespace tbs02 including contents and datafiles cascade constraints;

如果表空间不存在，数据回到用户的默认表空间
imp system/oracle tablespaces=tbs02 file=tbs02.dmp buffer=1048576 log=imp_tbs02.log

ALTER TABLE "EMP" ADD  CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE "SYSTEM" LOGGING;

实验19：传输表空间模式，字符集相同！！！
create tablespace tbs010 datafile '/home/oracle/prod/tbs010.dbf' size 10m;
create table tom.t010 tablespace tbs010 as select * from scott.emp;

alter tablespace tbs010 read only;

exp \'/ as sysdba\' tablespaces=tbs010 transport_tablespace=y file=tbs010.dmp buffer=1048576 log=exp_tbs010.log

scp /home/oracle/prod/tbs010.dbf grid@install13:/home/grid/tbs010.dbf

su - grid
ASMCMD [+data/asmdb/datafile] > cp /home/grid/tbs010.dbf +data/asmdb/datafile/tbs010.dbf

grant connect,resource to tom identified by tom;

imp \'sys/oracle@13 as sysdba\' tablespaces=tbs010 transport_tablespace=y file=tbs010.dmp buffer=1048576 datafiles=\'+data/asmdb/datafile/tbs010.dbf\' log=exp_tbs010.log

alter tablespace tbs010 read write;
alter tablespace tbs010 read write;

实验20：导出全库模式，sys用户下自己创建的表无法导出！
exp system/oracle full=y file=full.dmp buffer=10000000 log=full.log
exp system/oracle full=y filesize=50m file=full_1.dmp,full_2.dmp buffer=10000000 log=full.log

实验21：参数文件模式
exp parfile=/home/oracle/expbk/exp.txt
--------------------------------------
userid=scott/tiger
owner=scott
file=/home/oracle/expbk/scott.dmp
feedback=10000
buffer=104857600
log=/home/oracle/expbk/exp_scott.log
--------------------------------------