逻辑备份中的数据泵备份(EXPDP\IMPDP)
逻辑备份与物理备份、EXP/IMP备份见 逻辑备份中的EXP/IMP备份
一、SQL*Loader的相关介绍(将txt文件导入数据库中)
将外部数据加载到数据库表，是oracle自带的，主要包含Input data file(外部文件)、control file(控制文件)、Log file(日志文件)、bad file(记录发生错误的信息)、discard file(可以被丢弃的数据)，可以装入不同数据类型文件。装载类型有conventional load(解析成sql语句，基于insert和commit的sql语句，会产生redo，对所有约束生效，每次插入完成立即维护索引)和Direct Path Load方式(将要插入的数据组织到数据块中，再将数据库块整体插入表中，不经过缓存，只有在特定条件下产生redo，在数据load完成之后再维护索引)。
装载数据导数据库表有以下步骤：
1、Input data file内容
vi /tmp/test.txt; "DEPARTMENT_ID","DEPARTMENT_NAME","MANAGER_ID","LOCATION_ID" "10","Administration","200","1700" "20","Marketing","201","1800" "30","Purchasing","114","1700" "40","Human Resources","203","2400" "50","Shipping","121","1500" "60","IT","103","1400" "70","Public Relations","204","2700" "80","Sales","145","2500" "90","Executive","100","1700" "100","Finance","108","1700"
2、创建表DEPT
create table DEPT ( DEPARTMENT_ID NUMBER(4) not null, DEPARTMENT_NAME VARCHAR2(30), MANAGER_ID NUMBER(6), LOCATION_ID NUMBER(4) );
3、Control file内容
vi /tmp/dept.ctl
load data infile '/tmp/test.txt' into table dept fields terminated by ',' OPTIONALLY ENCLOSED BY '"' ( DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID )
在表中加入一列ID，设置其为自增序列，可以在control file中department_id前面加上一行 ID sequence(max,1)。
4、导入数据
$sqlldr hr/hr123 control=/tmp/dept.ctl skip=1; //skip=1表示跳过第一行 //$sqlldr hr/hr123 control=/tmp/dept.ctl skip=1 direct-true; //表示使用Direct-in-path方法来加载数据
拓展：sqluldr2是阿里自己开发的一个软件，数据量很大时可以使用。
二、expdp导出数据(生成一个data pump job，且对应一个mater table，记录导出的属性，是服务端到服务端的导出)
查看相关参数
expdp help=y
1、常用的相关参数项(默认按表的大到小来导出)
USERID --用户名/密码
DIRECTORY --导出的逻辑目录，在oracle中创建，并给用户授权读写权限 DUMPFILE --导出的数据文件的名称 EXCLUDE --指定不导出的对象 FULL --导出整个数据库 INCLUDE --指定导出的对象 LOGFILE --指定日志文件名 NOLOGFILE --不写入日志文件 SCHEMAS --导出指定的模式 TABLES --导出指定的表 TABLESPACES --导出指定的表空间
2、sql命令指定目录
mkdir /home/oracle/expdpdir; //先建立expdpdir文件夹 //sql命令创建虚拟目录，映射到操作系统的expdpdir，因为操作系统只能识别到目录；该文件一定要存在，而且oracle用户对其有读写权限 create or replace directory expdpdir as '/home/oracle/expdpdir'; col owner for a5; col DIRECTORY_NAME for a30; col DIRECTORY_PATH for a50; select owner,directory_name,directory_path from dba_directories;
3、expdp导出数据(操作系统执行expdp/impdp，directory也可以不用指定expdpdir，直接将expdpdir加到dumpfile里面，即dumpfile=expdpdir:'date+%y%m%d'.dump)
expdp scott/tiger schemas=scott directory=expdpdir dumpfile=`date +%y%m%d`.dump logfile=expdp_`date +%y%m%d`.log //这里的directory使用的是create directory创建的对,可以指定schemas，也可以指定full=y、tables、tablespace
执行之后出现directory name EXPDPDIR is invalid，再三检查确定目录没有写错，查询之后了解到是scott没有该目录的读写权限，赋予该用户对目录的操作权限。为把expdp和impdp的logfile区分开，logfile的前面加上expdp。
sqlplus / as sysdba; grant read,write on directory expdpdir to scott; expdp scott/tiger schemas=scott directory=expdpdir dumpfile=`date +%y%m%d`.dump logfile=expdp_`date +%y%m%d`.log; //现在执行就没有错误了 more 221011.log //查看导出日志
如果scott用户没有hr用户的权限，通过scott用户导出hr用户数据会出现错误：Unprivileged users may not operate upon other users' schemas。当出现unable to allocate 10720 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","bt_qentry")说明SGA内存不够了
4、估算导出数据的大小
expdp scott/tiger schemas=scott directory=expdpdir estimate_only=y estimate=BLOCKS logfile=esti_`date +%y%m%d`.log; //估算大小
三、impdp导入数据
查看相关参数
impdp help=y
1、常用相关参数项
USERID --用户名/密码
DIRECTORY --导入的逻辑目录 DUMPFILE --导入的数据文件名称 EXCLUDE --指定不导入的对象 FULL --导入整个数据库 INCLUDE --指定导入的对象 LOGFILE --写入的日志文件 NOLOGFILE --不写入日志文件 QUERY --导入查询语句 SCHEMAS --导入的用户 TABLESPACES --导入的表空间
REMAP_SCHEMA --重新映射模式，比如HR->SCOTT
REMAP_TABLE --重新映射表，从DEPT->TEST
2、impdp导入数据(按用户导入时如果用户默认的表空间和临时表空间不存在会出现错误)
impdp scott/tiger schemas=scott dumpfile=221011.dump directory=expdpdir logfile=impdp_`date +%y%m%d`.log; //如果导入前表已经存在，会出现错误，这时要指定TABLE_EXISTS_ACTION
参考连接：http://blog.itpub.net/22969361/viewspace-1218579/
impdp scott/tiger schemas=scott dumpfile=221011.dump directory=expdpdir logfile=impdp_`date +%y%m%d`.log table_exists_action=replace;
导入成功。
如果想要数据更干净，可以先将对象drop掉再导入。
3、远程导入(不用在源数据库导出文件再传输到目标数据库了，直接在目标数据库创建link指向源数据库)
将文件导入100.2主机上，要重新映射名称(在目标数据库执行，且目标数据库需要先创建用户和源数据库也使用表空间，并且指定空间配额；目标数据库也要存在directory；在目标数据库创建link指向源数据库)
create user hr_test identified by hr123 default tablespace users temporary tablespace temp account unlock;
grant unlimited tablespace to hr_test; grant connect,resource to hr_test; conn system/oracle; //进入system用户下创建私有dblink，因为要以system用户导入 create database link hr_link connect to hr identified by hr123 using '172.25.100.137:1521/chenmu'; select owner,object_name from dba_objects where object_type='DATABASE LINK'; //查看dblink impdp system/oracle schemas=hr network_link=hr_link REMAP_SCHEMA=hr:hr_test directory=expdpdir logfile=HR_impdp_`date +%y%m%d`.log;
//要授予用户读写这个目录的权限且密码要正确
在执行过程中出现错误，只有DEPT表导入成功，查看导入日志，发现是没有CHENMU表空间导致(DEPT的表空间是USERS，是存在的，所以导入成功)，通过select tablespace_name,table_name from tabs;查询源数据库HR用户的表，发现使用了两个表空间，还有些表是外部文件创建的，只导表空间导致这些表导不过去。
CREATE TABLESPACE CHENMU DATAFILE '+DATA/MDM19C/DATAFILE/chenmu.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE; //这里使用的是ASM内存管理 impdp system/oracle schemas=hr network_link=hr_link REMAP_SCHEMA=hr:hr_test directory=expdpdir logfile=HR_impdp_`date +%y%m%d`.log; //执行还有一些表已存在的错误，是因为之前导入过了
四、数据泵在多租户架构下的运用
1、导出non-cdb并导入pdb
2、导出源pdb并导入目标pdb
源PDB：172.25.100.137的orclpdb；目标PDB：172.25.100.2
(1)、开启172.25.100.137下的orclpdb，并创建公共用户C##DUMP和本地用户pdb_hr
sqlplus / as sysdba; alter pluggable database orclpdb open; create user C##DUMP identified by oracle; alter session set container=orclpdb; //切换到pdb并创建本地用户pdb_hr create user pdb_hr identified by hr123; grant connect,resource,unlimited tablespace to pdb_hr;
(2)、在orclpdb的本地用户pdb_hr下创建测试表
conn pdb_hr/hr123@172.25.100.137:1521/orclpdb; create table t_tab as select * from all_tables;
(3)、在PDB级别通过system用户创建目录，并导出源数据库的orclpdb
conn system/oracle@172.25.100.137:1521/orclpdb; create or replace directory expdpdir as '/home/oracle/expdpdir'; expdp system/oracle@172.25.100.137/orclpdb full=y directory=expdpdir dumpfile=orclpdb_`date +%y%m%d`.dmp logfile=orclpdb_`date +%y%m%d`.log
(4)、在目标库172.25.100.2创建pdb库orclpdb4
$sqlplus / as sysdba; show pdbs; alter session set pdb_file_name_convert='pdbseed','orclpdb4'; CREATE PLUGGABLE DATABASE orclpdb4 ADMIN USER hradmin IDENTIFIED BY hr123; alter pluggable database orclpdb4 open;
(5)、在目标库下的orclpdb4中创建目录，创建本地用户所需的表空间(orclpdb4是通过pdbseed创建，没有users01等表空间)
alter session set container=orclpdb4; create or replace directory expdpdir as '/home/oracle/expdpdir'; select tablespace_name,file_name from dba_data_files; //查看源数据库拥有的表空间SYSTEM、SYSAUX、UNDOTBS1、USERS、HR select tablespace_name,file_name from dba_data_files; //查看目标数据库拥有的表空间SYSTEM、SYSAUX、UNDOTBS1
需要手动创建users表空间和HR表空间，可以通过dbms_metadata包来获取源库的两个表空间
select dbms_lob.substr(dbms_metadata.get_ddl('TABLESPACE','USERS')) from dual; select dbms_lob.substr(dbms_metadata.get_ddl('TABLESPACE','HR')) from dual;
将获取的两个sql在目标数据库运行创建两个缺失的表空间，但是要注意修改目录路径，将orclpdb修改成orclpdb4
CREATE TABLESPACE "USERS" DATAFILE '/u01/app/oracle/oradata/ORCLCDB/orclpdb4/users01.dbf' SIZE 5242880 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE "HR" DATAFILE '/u01/app/oracle/oradata/ORCLCDB/orclpdb4/hr01.dbf' SIZE 157286400 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
(6)、并导入dmp文件到orclpdb4中(目标数据库要存在和源数据库同名的表空间否则会报错)
scp -r oracle@172.25.100.137:/home/oracle/expdpdir/orclpdb_221203.dmp /home/oracle/expdpdir; //将源数据库的dmp文件拷贝到目标数据库 impdp system/oracle@172:25.100.2:1521/orclpdb4 FULL=y DUMPFILE= orclpdb_221203.dmp DIRECTORY=expdpdir LOGFILE=imp_orclpdb_`date +%y%m%d`.log
3、导出源pdb并导入non-cdb
五、可传输的全库导出/导入(full transportable export/import)
导出导入的有整库级别、表空间级别、表级别(不常用)的可传输技术，system、sysaux是不可传输的表空间导出导入；全量可传输导出使用混合方式导出数据，在可传输的表空间的对象仅导出它们的元数据，数据文件需要一起拷贝到目标数据库；不可传输表空间的对象会将元数据和数据均导出到dmp文件中。
1、通过表空间级别可传输实现全库迁移
(1)、在orclpdb下创建目录(上面实验已创建)，确保用户自定义表(USERS、HR)空间处于read only状态
alter tablespace hr read only;
alter tablespace users read only;
(2)、导出orclpdb全量的可传输dmp文件(USERS、HR表空间的只导出元数据)，导出之后将源数据库表空间设置为读写状态
expdp system/oracle@172.25.100.137:1521/orclpdb full=y directory=expdpdir dumpfile=expdp_trans_`date +%y%m%d`.dmp transportable=always logfile=expdp_trans_`date +%y%m%d`.log; alter tablespace hr read write; alter tablespace users read write;
(3)、将用户自定义的表空间USERS、HR的数据文件和dmp文件一起拷贝到目标数据库对应的目录(在目标数据库拷贝源数据库的东西)
scp -r oracle@172.25.100.137:/home/oracle/expdpdir/expdp_trans_221206.dmp /home/oracle/expdpdir; scp -r oracle@172.25.100.137:/u01/app/oracle/oradata/ORCLCDB/orclpdb/hr01.dbf /u01/app/oracle/oradata/ORCLCDB/orclpdb4; scp -r oracle@172.25.100.137:/u01/app/oracle/oradata/ORCLCDB/orclpdb/users01.dbf /u01/app/oracle/oradata/ORCLCDB/orclpdb4;
(4)、导入可传输的全量数据(最后这一步未测试成功)
impdp system/oracle@172.25.100.2:1521/orclpdb4 FULL=y DUMPFILE=expdp_trans_221206.dmp DIRECTORY=expdpdir LOGFILE=impdp_trans_`date +%y%m%d`.log \ TRANSPORT_DATAFILES='/u01/app/oracle/oradata/ORCLCDB/orclpdb4/hr01.dbf','/u01/app/oracle/oradata/ORCLCDB/orclpdb4/users01.dbf';
2、通过dblink实现全库迁移(不用在源数据库导出数据，只需将用户自定义表空间拷贝到目标库对应的目录即可)
(1)、创建目标库，创建目标库到源库的link，确保源数据库的表处于read only状态
(2)、将源数据库自定义表空间的数据文件拷贝到目标数据库对应的目录
(3)、执行导入命令
