[草稿]oracle的逻辑备份与恢复

[TOC]

概述

• exp与expdp 都是备份数据库的，不同之处在于：

准备工作

• 准备2个测试环境

• [所有实例] 配置环境

# oracle-11g 安装目录：/data/oracle_1522/product/11.2.0/dbhome_1/bin
# oracle-11g ORACLE_HOME目录：/data/oracle_1522/product/11.2.0/dbhome_1

#.1.配置安装目录-简化exp_imp等命令-针对21c
ocnt=`cat /etc/bashrc | grep oracle | grep -v grep | wc -l`
if [ $ocnt -eq 0 ];then echo 'export PATH=$PATH:/opt/oracle/bin' >> /etc/bashrc; source /etc/bashrc; fi

#.2.设置ORACLE_HOME-针对21c
export ORACLE_SID=orcl
export ORACLE_HOME=/opt/oracle

#.3.创建表空间及备份目录
mkdir -p /data/oracle_1522/{tablespace,dump}
chown -R oracle:oinstall /data/oracle_1522/tablespace
chown -R oracle:oinstall /data/oracle_1522/dump

• [实例1] 准备测试数据

--.1.登录oracle实例
su - oracle -c "sqlplus /nolog"

--.2.在管理员账号下，创建表空间（表空间200M,每次扩展100M,最大不限制），示例 tbs_backup
conn sys/oracle as sysdba;
drop tablespace tbs_backup including contents and datafiles;
create tablespace tbs_backup datafile '/data/oracle_1522/tablespace/tbs_backup.dbf' size 200M autoextend on next 100M maxsize unlimited;

--.3.在管理员账号下，新增备份管理员(指定表空间)并授予权限，示例 dba_backup
conn sys/oracle as sysdba;
alter session set "_ORACLE_SCRIPT"=true;
drop user dba_backup cascade;
create user dba_backup identified by Admin_147 default tablespace tbs_backup temporary tablespace TEMP;
grant connect,resource,dba to dba_backup with admin option;

--.4.在管理员账号下，新增测试账号，示例 pay01
conn sys/oracle as sysdba;
alter session set "_ORACLE_SCRIPT"=true;
create user pay01 identified by Admin_147;
grant connect,resource to pay01;
ALTER USER pay01 QUOTA UNLIMITED ON USERS;

--.5.在测试账号下，创建测试表
conn pay01/Admin_147@orcl;
CREATE TABLE tab01(id number(6,0) primary key,name varchar2(10),city varchar(10));
INSERT INTO tab01 VALUES(1,'sam','beijing');
CREATE TABLE tab02(id number(6,0) primary key,name varchar2(10),city varchar(10));
INSERT INTO tab02 VALUES(1,'joy','shanghai');

• [实例2] 准备基础环境

--.1.登录oracle实例
su - oracle -c "sqlplus /nolog"

--.2.在管理员账号下，创建表空间（表空间200M,每次扩展100M,最大不限制），示例 tbs_restore
conn sys/oracle as sysdba;
drop tablespace tbs_restore including contents and datafiles;
create tablespace tbs_restore datafile '/data/oracle_1522/tablespace/tbs_restore.dbf' size 200M autoextend on next 100M maxsize unlimited;

--.3.在管理员账号下，新增恢复管理员(指定表空间)并授予权限，示例 dba_restore
conn sys/oracle as sysdba;
alter session set "_ORACLE_SCRIPT"=true;
drop user dba_restore cascade;
create user dba_restore identified by Admin_258 default tablespace tbs_restore temporary tablespace TEMP;
grant connect,resource,dba to dba_restore with admin option;

--.4.在管理员账号下，新增测试账号，示例 pay02
conn sys/oracle as sysdba;
alter session set "_ORACLE_SCRIPT"=true;
create user pay02 identified by Admin_258;
grant connect,resource to pay02;
ALTER USER pay02 QUOTA UNLIMITED ON USERS;

利用 exp 与 imp

• [实例1] 确认测试数据

#.实例1：确认测试数据
echo "select table_name from all_tables where table_name like 'TAB0%';" | su - oracle -c "sqlplus pay01/Admin_147@192.168.31.101:1522/orcl"

• [实例1] 利用 exp 导出

#.导出整个数据库
exp dba_backup/Admin_147@192.168.31.101:1522/orcl file=/mnt/orcl_exp_full.dmp log=/tmp/orcl_exp_full.log full=y
tail -n10 /tmp/orcl_exp_full.log | grep successfully

#.按照用户导出
exp dba_backup/Admin_147@192.168.31.101:1522/orcl file=/mnt/orcl_exp_user.dmp log=/tmp/orcl_exp_user.log owner=pay01
tail -n10 /tmp/orcl_exp_user.log | grep successfully

#.指定表名导出
exp dba_backup/Admin_147@192.168.31.101:1522/orcl file=/mnt/orcl_exp_tabs.dmp log=/tmp/orcl_exp_tabs.log tables=tab01,tab02
tail -n10 /tmp/orcl_exp_tabs.log | grep successfully

#.附.exp参数
full=y          导出整个数据库
file=xxx.log    输出的文件
log=xxx.log     屏幕输出的日志文件
buffer=64000

• [实例2] 利用 imp 导入

#.导入整个数据库
imp dba_restore/Admin_258@192.168.31.102:1522/orcl file=/mnt/orcl_exp_full.dmp log=/tmp/orcl_imp_full.log full=y
tail -n10 /tmp/orcl_imp_full.log | grep successfully

#.按照用户导入，示例将 pay01 用户下的2张表恢复到 pay02 用户下
imp dba_restore/Admin_258@192.168.31.102:1522/orcl file=/mnt/orcl_exp_user.dmp log=/tmp/orcl_imp_user.log fromuser=pay01 touser=pay02
tail -n10 /tmp/orcl_imp_user.log | grep successfully

#############################################################
#.指定表名导入，示例备份2张表、只恢复1张表
imp dba_restore/Admin_258@192.168.31.102:1522/orcl file=/mnt/orcl_exp_tabs.dmp log=/tmp/orcl_imp_tabs.log tables=tab01 fromuser=pay01 touser=pay02
tail -n10 /tmp/orcl_imp_tabs.log | grep successfully
#############################################################

#.附.imp参数
full=y            导入整个数据库
file=xxx.log    输入的文件
log=xxx.log     屏幕输出的日志文件
ignore=y        是忽略创建错误

• [实例2] 确认测试数据

#.实例2：确认测试数据
echo "select table_name from all_tables where table_name like 'TAB0%';" | su - oracle -c "sqlplus pay02/Admin_258@192.168.31.102:1522/orcl"

#.实例2：删除测试数据
echo "drop table tab01;" | su - oracle -c "sqlplus pay02/Admin_258@192.168.31.102:1522/orcl"
echo "drop table tab02;" | su - oracle -c "sqlplus pay02/Admin_258@192.168.31.102:1522/orcl"

利用 expdp 与 impdp

• [实例1] 使用 expdp/impdp 必须首先创建dump目录

--.登录oracle实例
su - oracle -c "sqlplus /nolog"

--.在管理员账号下，创建expdp/impdp所必须的dump目录，授予用户对导出目录的读写权限
conn sys/oracle as sysdba;
drop directory dumpdir;
create or replace directory dumpdir as '/data/oracle_1522/dump';
grant read,write on directory dumpdir to dba_backup;
select directory_path from dba_directories WHERE directory_name='DUMPDIR';

• [实例1] 使用 expdp 导出，通过 directory 导出，不可以在 logfile 再使用自定义路径，比如 logfile=/tmp/expdp_full.log

#.1.导出全库，full=y
expdp dba_backup/Admin_147@192.168.31.101:1522/orcl directory=dumpdir dumpfile=orcl_expdp_full.dmp logfile=orcl_expdp_full.log full=y
tail -n10 /data/oracle_1522/dump/orcl_expdp_full.log | grep successfully

#.2.按用户导出，schemas=pay01
expdp dba_backup/Admin_147@192.168.31.101:1522/orcl directory=dumpdir dumpfile=orcl_expdp_user.dmp logfile=orcl_expdp_user.log schemas=pay01
tail -n10 /data/oracle_1522/dump/orcl_expdp_user.log | grep successfully

#.3.按表名导出（不能跨用户，只能导出本账号创建的表，需要将该用户添加dba权限），tables=tab01,tab02
echo "grant dba to pay01;" | su - oracle -c "sqlplus dba_backup/Admin_147@192.168.31.101:1522/orcl"
expdp pay01/Admin_147@192.168.31.101:1522/orcl directory=dumpdir dumpfile=orcl_expdp_tabs.dmp logfile=orcl_expdp_tabs.log tables=tab01,tab02
tail -n10 /data/oracle_1522/dump/orcl_expdp_tabs.log | grep successfully

#.4.按表空间导出，tablespaces=tbs_backup
expdp dba_backup/Admin_147@192.168.31.101:1522/orcl directory=dumpdir dumpfile=orcl_expdp_tbsp.dmp logfile=orcl_expdp_tbsp.log tablespaces=tbs_backup
tail -n10 /data/oracle_1522/dump/orcl_expdp_tbsp.log | grep successfully

#############################################################
#.5.按查询条件导出
expdp dba_backup/Admin_147@192.168.31.101:1522/orcl directory=dumpdir dumpfile=orcl_expdp_qury.dmp logfile=orcl_expdp_qury.log tables=tab01 query='WHERE id=1'
tail -n10 /data/oracle_1522/dump/orcl_expdp_qury.log | grep successfully
#############################################################


#.附.expdp参数
parallel=4      增大并行度,可减少备份时间,但会增加CPU负载

• [实例2] 使用 expdp/impdp 必须首先创建dump目录

--.登录oracle实例
su - oracle -c "sqlplus /nolog"

--.在管理员账号下，创建expdp/impdp所必须的dump目录，授予用户对导出目录的读写权限
conn sys/oracle as sysdba;
drop directory dumpdir;
create or replace directory dumpdir as '/data/oracle_1522/dump';
grant read,write on directory dumpdir to dba_restore;
select directory_path from dba_directories WHERE directory_name='DUMPDIR';

• [实例2] 利用 impdp 导入

#.8.改变表的owner：
impdp pay02/Admin_258@192.168.31.102:1522/orcl directory=dumpdir dumpfile=orcl_expdp_tabs.dmp logfile=orcl_impdp_tabs.log remap_schema=pay01:pay02



#############################################################
#.1.按表名导入
impdp amber/oracle@orcl tables=tb2,tb3 directory=oracle_bak dumpfile=bak01.dmp 

#.2.还原该用户下的所有表(经常使用)
impdp pay02/Admin_258@192.168.31.102:1522/orcl schemas=pay01 directory=dumpdir dumpfile=orcl_expdp_tabs.dmp logfile=orcl_expdp_tabs.log;

#.3.整库导入
impdp amber/oracle@orcl full=y directory=oracle_bak dumpfile=bak03.dmp

#.4.追加，如果表结构存在用append
impdp scott/tiger dumpfile=expdp:scott.bak tables=emp tables_exists_action=append

#.5.替换，不会释放表空间
impdp scott/tiger dumpfile=expdp:scott.bak tables=emp tables_exists_action=replace

#.6.截断，会释放空间，重新插入新的数据
impdp scott/tiger dumpfile=expdp:scott.bak tables=emp tables_exists_action=truncate

#.7.只恢复emp表的时候，另外两个表会跳过恢复
impdp scott/tiger dumpfile=expdp:scott.bak tables=emp,dept,salgrade tables_exists_action=skip

#.8.改变表的owner：
impdp user/passwd directory=xxx dumpfile=xxx.dmp remap_schema=oldschema:newschema logfile=xxx.log

#.9.导入表空间
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;

#.按用户导入
#############################################################

以 load data 恢复/导入

#.导入csv格式的文件,以逗号划分
load data infile '/tmp/insert_tab2.csv' into table "tab_import" fields terminated by ',';