0racle 之数据备份和采集
本文是以Oracle 11g为基础来讲解Oracle数据备份的方式。 可以根据实际情况,选择 DBeaver、Oracle SQL
Developer 、Oracle expdp dump等方式进行数据备份和采集。
DBeaver 工具采集(推荐使用方式)
DBA权限用户,可以获取全部信息。非DBA用户可以生成DDL的sql语句,但是无法直接执行成功,需要整体手动调整,同时无法获取表大小等元信息。
1.DBeaver登陆oracle
2.采集DDL-创建表sql
2.1 选择数据库 -> schema -> 批量选择表
2.2 批量选择表 或者选择表,点击右键,选择生成SQL,选择DDL
2.3 生成DDL(创建表语句),点击复制,手动存储到相应的文件中。
其中,使用完全限定名称:带有schema信息,去掉勾选则不带schema信息。其他两项看需求而选。
3.采集数据
3.1 导出数据
3.2 导出数据-导出目标选择SQL INSERT语句
导出目标配置,可以根据需求选择SQL\CSV\JSON等不同的格式。
同时检查最右侧的列表是否完整。
3.3 导出数据-抽取设置
抽取设置,
最大线程数:可以设置导出任务的线程数,如果导出只有一张表,则此处指定选择1.
抽取类型:一般选择单个查询,当然如果追求性能,类型就选择多个查询,可以和最大线程数配合使用。
打开新连接:一般都会选择,选择行计数,如果备份数据量较少就无所谓,大数据量的话,需要根据情况填写合适的数量。
3.4 导出数据-格式设置-每条语句的数据行数,填写1
导出配置:可以根据自己数据的特性做不同的配置,但是有一点一定要注意,图中标红的一定要修改成1,因为oracle不支持批量insert语法。
3.5 导出数据-输出,需要选择文件夹,是否压缩等,压缩后每个表一个文件,不压缩可以选择导出一个文件。
3.6 导出数据-确认,需要检查下对象和设置
3.7 导出完成后,如果想要导入,直接执行SQL即可。
4.DBA权限统计表大小
方式1: Sql统计,导出结果
4.1.1 统计表大小sql,需要修改owner = UPPER(‘LG_ROOT’) 中的 schema名称。其他无需修改。
SELECT segment_name AS table_name, ROUND(SUM(bytes) / 1024 / 1024, 4) AS mb_size FROM dba_segments WHERE owner = UPPER('LG_ROOT') AND segment_type = 'TABLE' GROUP BY segment_name, segment_type;
4.1.2 数据库-右键选择 SQL编辑器-打开SQL控制台,将上面的sql输入,点击执行
4.1.3 选中执行结果,点击右键,导出数据
4.1.4 选择导出的数据格式,可以根据需求选择相应的格式,和【3】中的导出数据一样。
4.1.5 修改输出目录,点击Proceed 就会生成结果数据。
方式2:Copy方式
4.2.1 点击表右键,选择查看表
4.2.2 配置统计信息,可以根据需求选择需要统计的指标
4.2.3 选中多张表,点击右键,选择复制高级信息
4.2.4 将复制的信息copy到备份的文件中即可。
Oracle SQL Developer 工具采集
使用权限条件说明
- DBA权限用户,可以导出sql(insert)、csv、txt等格式,sql格式可以导出创建表的语句等。
- 非DBA权限用户,可以导出sql(insert)、csv、txt等格式,但是只能导出数据,无法导出创建表等表的元信息数据。
环境安装
# 下载地址
https://www.oracle.com/database/sqldeveloper/technologies/download/
# Ubuntu 上安装 SQL Developer 资料
https://www.cnblogs.com/buxizhizhoum/p/9040109.html
https://www.jb51.net/database/325785eq5.htm
工具的使用
1.创建连接
2.导出DDL-创建表的sql
统计表大小sql,需要修改owner = UPPER(‘LG_ROOT’) 中的 schema名称
SELECT segment_name AS table_name, ROUND(SUM(bytes) / 1024 / 1024, 4) AS mb_size FROM dba_segments WHERE owner = UPPER('LG_ROOT') AND segment_type = 'TABLE' GROUP BY segment_name, segment_type;
2.1 选中对用的库,会生成sql执行器
2.2 将上面的sql语句复制到执行器中,点击运行。选择所有的执行结果,右键点击导出。
2.3 导出配置-选择csv以及导出文件目录,点下一步,完成即可
导出完成。
3.导出所有的sql数据
3.0 设置时间格式
注意时间格式需要更改,要不然数据的时间格式会有问题。将日期格式改为YYYY-MM-DD HH24:MI:SS
3.1 在创建的库中找到 【其他用户】 模块
3.2 在【其他用户】模块中找到相应的 schema,点击表,可以看到所有的表信息,可以按住shift + 多张表,批量操作
3.3 批量选中表后,点击右键,选择导出
3.4 导出配置,导出DDL,只有DBA权限才会生效,按照图中选择即可。
导出数据中,格式可以自由选择。另存为要选择单独文件(一个文件的话,如果只有数据,会将所有表的数据都放到一起了,如果是DBA可以选择一个文件的insert),可选择是否压缩。
后面直接点击下一步即可。
导出完成。
Oracle expdp dump方式
前提:用户要有DBA权限 或者是 dump权限才能操作。
dmp文件为二进制文件,无法直接验证数据完整性,并且以后需要使用的话,得灌入到库里面才行。
dmp文件导入限制太多,比较复杂,用户、表空间等要提前安装之前的创建好才行,或者根据导入报错进行调整。
# 查看oracle 容器
docker ps | grep oracle
# 进入oracle container 中
docker exec -it {oracle容器名称或 ID} bash
# 进入tmp文件夹
cd tmp
# 创建dump文件夹
mkdir dump
# 链接当前oracle 实例,如果没有则需要新建
sqlplus test/oracle@XE
# 创建dump dir
CREATE OR REPLACE DIRECTORY dump_dir AS '/tmp/dump/';
# 创建远程dump link, 需要修改用户名、密码、ip、port、sid
CREATE DATABASE LINK db_link
CONNECT TO ${user} IDENTIFIED BY ${password}
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SID=XE)))';
# 退出sql
exit
# 执行dump操作
expdp test/oracle DIRECTORY=dump_dir DUMPFILE=export.dmp LOGFILE=export.log SCHEMAS=LG_ROOT NETWORK_LINK=db_link
# 如果expdp找不到命令,则执行以下的命令
echo $ORACLE_HOME
cd 到上面查询到的路径中,在执行expdp
#退出oracle 容器,将容器中的文件导出
docker cp <docker容器名称或 ID>:/tmp/dump/export.dmp /home/user/export.dmp
# 导入数据
impdp test/oracle@helowin DIRECTORY=DPUMP_DIR DUMPFILE=export1.dmp SCHEMAS=TEST
写完收工。
最后,愿您拥有一颗不老的心,永远保持对生活的热爱和好奇。无论世界如何变化,你都能以微笑面对。加油!!!
祝您一切顺利,幸福满满!😉