oracle备份或还原数据库（expdp、impdp）

1.首先新建或更改虚拟目录

因为数据泵方式——expdp（导出）和impdp（导入）需要虚拟目录路径。

-- 语法
create or replace directory 虚拟目录名 as '虚拟目录绝对路径';
-- 例子：创建或修改data_backup逻辑目录
create or replace directory data_backup as 'D:\app\Administrator\admin\bysjdb\dbdump\20210709';

如果是第一次新建data_backup这个逻辑目录的话，还需要给数据用户赋可读可写的权限。这样才可以备份和还原。

-- 语法
grant read,write on directory 虚拟目录名 to 数据库用户;
-- 例子
grant read,write on directory data_backup to 数据库用户;

2.批量导出数据库（备份）

提示：expdp help=Y可查看提示
假设本地oracle有以下用户BYSJtest、BYSJuser、System。下面语句则会将把以BYSJ开头命名的用户数据库备份到指定的虚拟目录下，以用户名命名备份产生的dmp文件。
注意：%exp_pwd%为调用exp_pwd变量，所以在执行expdp语句前，先对oracle_sid、exp_pwd、exp_ip变量赋值

--说明
--username:导出数据的用户名，必写;
--password:导出数据的密码，必写;
--@:地址符号，必写;
--serviceip和exp_sid:oracle的服务名，必写[ip+端口+sid];
--1521:端口号，1521是默认的可以不写,非默认要写;
--schemas：导出操作的用户名;
--dumpfile：导出的文件;
--logfile:导出的日志文件,可以不写；
--directory:创建的虚拟文件夹名称;
--remap_schema=源数据库用户名:目标数据库用户名,二者不同时必写，相同可以省略;

--语法
expdp username/password@serviceip/servicename directory=虚拟目录 dumpfile=username.dmp logfile=username.log schemas=username

--批量生成expdp语句
select 
distinct
'expdp '||lower(atc.owner)||'/%exp_pwd%@%exp_ip%/%oracle_sid% directory=data_backup dumpfile='||lower(atc.owner)||'.dmp logfile='||lower(atc.owner)||'.log schemas='||lower(atc.owner)||''
from all_tab_columns atc
where atc.owner like 'BYSJ%';

编写bat文件并执行

--设置NLS_LANG变量来指定字符属性
rem set NLS_LANG=CHINESE_CHINA.AL32UTF8
@echo [输入对应的sid]
set oracle_sid=bysj
@echo [输入对应的用户密码]
set exp_pwd=123456
@echo [输入对应的ip地址、端口]
set exp_ip=192.168.x.xxx:1521
@echo [输入对应的用户名]

--导出expdp语句
......

@echo [完成导出]
--等待用户确认才关闭窗口
pause

3.批量导入数据库（还原）

根据directory指定的虚拟目录下根据用户名找到对应的dumpfile，然后导入到指定的oracle_sid的oracle数据库中。

--批量生成impdp语句
select 
distinct
'impdp '||lower(atc.owner)||'/%imp_pwd%@%imp_ip%/%oracle_sid% directory=data_backup dumpfile='||lower(atc.owner)||'.dmp full=Y ignore=Y logfile='||lower(atc.owner)||'.log'
from all_tab_columns atc
where atc.owner like 'BYSJ%';

编写bat文件并执行

--设置NLS_LANG变量来指定字符属性
rem set NLS_LANG=CHINESE_CHINA.AL32UTF8
@echo [输入对应的sid]
set oracle_sid=bysj
@echo [输入对应的用户密码]
set imp_pwd=123456
@echo [输入对应的ip地址、端口]
set imp_ip=192.168.x.xxx:1521
@echo [输入对应的用户名]

--导出impdp语句
......

@echo [完成导入]
--等待用户确认才关闭窗口
pause

4.批量生成创建用户语句

先生成用户在批量导入数据库

select 
distinct
--语句开始
'start
drop user '||atc.owner||' cascade;
--创建表空间，指定自动扩容为5m，最大不限制
CREATE SMALLFILE TABLESPACE '||atc.owner||' DATAFILE ''D:\database\oracle_dbf\bysj\'||atc.owner||'.dbf'' SIZE 5M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--创建用户
CREATE USER '||atc.owner||' IDENTIFIED BY "123456" 
--指定表空间
DEFAULT TABLESPACE '||atc.owner||' TEMPORARY TABLESPACE TEMP;
--分配角色
GRANT "DBA" TO '||atc.owner||';
GRANT "CONNECT" TO '||atc.owner||';
GRANT "RESOURCE" TO '||atc.owner||';
ALTER USER '||atc.owner||' DEFAULT ROLE "DBA","CONNECT","RESOURCE";
--用户配额不限制
GRANT UNLIMITED TABLESPACE TO '||atc.owner||';
end'
--语句结束
from all_tab_columns atc
where atc.owner like 'BYSJ%'; --查询指定用户

其他

或者使用exp、imp的方式也可行，这样速度偏慢，但是可以在本地进行备份，无需创建虚拟目录。

--说明
--username:导出数据的用户名，必写;
--password:导出数据的密码，必写;
--@:地址符号，必写;
--serviceip和exp_sid:oracle的服务名，必写[ip+端口+sid];
--1521:端口号，1521是默认的可以不写,非默认要写;
--file：导出的文件路径;
--full=Y:代表完全导入导出

--语法
exp(imp) username/password@serviceip/servicename file=D:/db/username.dmp full=Y;

根据用户表导出部分

exp(imp) username/password@serviceip/servicename file='D:/db/username.dmp' tabels=                                                                  (table1,table2,table3,...);

根据表空间导出部分

exp(imp) username/password@serviceip/servicename file='D:/db/username.dmp' tablespaces=                                                         (tablespace1,tablespace2,tablespace3,...);

根据用户导出部分

exp(imp) username/password@serviceip/servicename file='D:/db/username.dmp' owner(username1,username2,username3);