目录
1. 基础概念与理论知识
1.1 误操作类型与危害
误操作类型：
- 误删表：DROP TABLE操作导致表结构及数据丢失
- 误删数据：DELETE操作导致数据行丢失
- 误更新：UPDATE操作导致数据错误修改
- 误删除表空间：DROP TABLESPACE导致表空间及数据丢失
- 误删除用户：DROP USER导致用户及对象丢失
风哥提示：
误操作危害：
- 数据丢失：可能导致业务数据永久丢失
- 业务中断：影响业务正常运行
- 经济损失：造成直接或间接经济损失
- 信誉损失：影响企业信誉和客户信任
1.2 误操作恢复原理
恢复原理：
- 闪回技术：利用回收站和UNDO数据快速恢复
- 时间点恢复：基于备份和归档日志恢复到指定时间点
- 数据泵恢复：从导出文件中恢复数据
- 日志挖掘：从重做日志中提取SQL语句进行恢复
# 误操作恢复技术对比
闪回恢复：快速恢复，依赖UNDO表空间，时间窗口有限
时间点恢复：可恢复到任意时间点，依赖备份和归档日志，恢复时间较长
数据泵恢复：从导出文件恢复，需要定期导出备份，恢复粒度灵活
日志挖掘：从日志中提取SQL，可精确恢复，但技术复杂度高 学习交流加群风哥微信: itpux-com
闪回恢复：快速恢复，依赖UNDO表空间，时间窗口有限
时间点恢复：可恢复到任意时间点，依赖备份和归档日志，恢复时间较长
数据泵恢复：从导出文件恢复，需要定期导出备份，恢复粒度灵活
日志挖掘：从日志中提取SQL，可精确恢复，但技术复杂度高 学习交流加群风哥微信: itpux-com
1.3 闪回技术概述
闪回技术特点：
- 闪回表：恢复表到过去某个时间点的状态
- 闪回查询：查询过去某个时间点的数据
- 闪回版本查询：查询数据的版本历史
- 闪回事务查询：查询事务的历史信息
- 回收站：存储被删除的对象，可快速恢复
# 闪回技术依赖参数
UNDO_RETENTION：UNDO数据保留时间（秒）
ENABLE_FLASHBACK：是否启用闪回功能
RECYCLEBIN：是否启用回收站功能
UNDO_RETENTION：UNDO数据保留时间（秒）
ENABLE_FLASHBACK：是否启用闪回功能
RECYCLEBIN：是否启用回收站功能
1.4 时间点恢复技术
时间点恢复原理：
- 基于物理备份恢复数据库到指定时间点
- 应用归档日志到指定时间点
- 支持精确到秒的时间点恢复
- 需要完整的备份链和归档日志
# 时间点恢复流程
1. 恢复最近的完整备份
2. 应用增量备份（如果有）
3. 应用归档日志到指定时间点
4. 打开数据库（RESETLOGS）
1. 恢复最近的完整备份
2. 应用增量备份（如果有）
3. 应用归档日志到指定时间点
4. 打开数据库（RESETLOGS）
2. 生产环境规划与建议
2.1 误操作预防措施
预防措施：
- 学习交流加群风哥QQ113257174
- 权限控制：严格控制DROP、DELETE、UPDATE权限
- 操作确认：重要操作前进行二次确认
- 操作审计：记录所有DDL和DML操作
- 备份验证：定期验证备份可用性
- 演练测试：定期进行误操作恢复演练
# 权限控制示例
— 撤销普通用户的DROP权限
REVOKE DROP ANY TABLE FROM fgedu_user;
— 只授予SELECT权限
GRANT SELECT ON fgedu.order TO fgedu_user;
— 创建只读角色
CREATE ROLE fgedu_readonly;
GRANT SELECT ON fgedu.* TO fgedu_readonly;
GRANT fgedu_readonly TO fgedu_user;
— 撤销普通用户的DROP权限
REVOKE DROP ANY TABLE FROM fgedu_user;
— 只授予SELECT权限
GRANT SELECT ON fgedu.order TO fgedu_user;
— 创建只读角色
CREATE ROLE fgedu_readonly;
GRANT SELECT ON fgedu.* TO fgedu_readonly;
GRANT fgedu_readonly TO fgedu_user;
2.2 备份策略规划
备份策略：
- 全量备份：每天一次全量备份
- 增量备份：每小时一次增量备份
- 归档日志：实时归档，保留30天
- 逻辑备份：每天一次数据泵导出
- 异地备份：备份文件异地存储
# 备份策略配置
— 全量备份脚本
/dm/scripts/full_backup.sh
— 增量备份脚本
/dm/scripts/incremental_backup.sh
— 归档日志备份脚本
/dm/scripts/archive_backup.sh
— 逻辑备份脚本
/dm/scripts/logical_backup.sh
— 全量备份脚本
/dm/scripts/full_backup.sh
— 增量备份脚本
/dm/scripts/incremental_backup.sh
— 归档日志备份脚本
/dm/scripts/archive_backup.sh
— 逻辑备份脚本
/dm/scripts/logical_backup.sh
更多视频教程www.fgedu.net.cn
2.3 权限管理规范
权限管理规范：
- 最小权限原则：只授予必要的权限
- 角色分离：开发、测试、生产环境权限分离
- 权限审批：重要权限变更需要审批
- 定期审查：定期审查用户权限
- 权限回收：离职员工权限及时回收
# 权限管理脚本
— 查看用户权限
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = ‘FGEDU_USER’;
— 查看角色权限
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = ‘FGEDU_USER’;
— 回收权限
REVOKE ALL ON fgedu.order FROM fgedu_user;
— 查看系统权限
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = ‘FGEDU_USER’;
— 查看用户权限
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = ‘FGEDU_USER’;
— 查看角色权限
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = ‘FGEDU_USER’;
— 回收权限
REVOKE ALL ON fgedu.order FROM fgedu_user;
— 查看系统权限
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = ‘FGEDU_USER’;
2.4 审计与监控配置
审计配置：
- DDL审计：审计所有DDL操作
- DML审计：审计重要的DML操作
- 审计日志：审计日志长期保存
- 审计分析：定期分析审计日志
- 异常告警：异常操作及时告警
# 审计配置
— 启用审计
SP_SET_PARA_VALUE(2, ‘ENABLE_AUDIT’, 1);
— 审计DDL操作
AUDIT DDL;
— 审计重要表的DML操作 更多学习教程公众号风哥教程itpux_com
AUDIT INSERT, UPDATE, DELETE ON fgedu.order;
— 查看审计记录
SELECT * FROM V$AUDITRECORDS WHERE SQLTEXT LIKE ‘%DROP%’;
— 启用审计
SP_SET_PARA_VALUE(2, ‘ENABLE_AUDIT’, 1);
— 审计DDL操作
AUDIT DDL;
— 审计重要表的DML操作 更多学习教程公众号风哥教程itpux_com
AUDIT INSERT, UPDATE, DELETE ON fgedu.order;
— 查看审计记录
SELECT * FROM V$AUDITRECORDS WHERE SQLTEXT LIKE ‘%DROP%’;
3. 生产环境项目实施方案
3.1 误操作恢复流程
步骤1：确认误操作
# 确认误操作
— 查看当前时间
SELECT SYSDATE FROM DUAL;
— 查看误操作时间
SELECT * FROM V$AUDITRECORDS WHERE SQLTEXT LIKE ‘%DROP%’ ORDER BY OPTIME DESC;
— 查看回收站
SELECT * FROM RECYCLEBIN ORDER BY DROPTIME DESC;
— 查看当前时间
SELECT SYSDATE FROM DUAL;
— 查看误操作时间
SELECT * FROM V$AUDITRECORDS WHERE SQLTEXT LIKE ‘%DROP%’ ORDER BY OPTIME DESC;
— 查看回收站
SELECT * FROM RECYCLEBIN ORDER BY DROPTIME DESC;
步骤2：评估影响范围
# 评估影响范围
— 查看受影响的表
SELECT TABLE_NAME, OWNER FROM ALL_TABLES WHERE TABLE_NAME = ‘ORDER’;
— 查看表大小
SELECT SEGMENT_NAME, BYTES/1024/1024 MB FROM USER_SEGMENTS WHERE SEGMENT_NAME = ‘ORDER’;
— 查看表数据量
SELECT COUNT(*) FROM fgedu.order;
— 查看受影响的表
SELECT TABLE_NAME, OWNER FROM ALL_TABLES WHERE TABLE_NAME = ‘ORDER’;
— 查看表大小
SELECT SEGMENT_NAME, BYTES/1024/1024 MB FROM USER_SEGMENTS WHERE SEGMENT_NAME = ‘ORDER’;
— 查看表数据量
SELECT COUNT(*) FROM fgedu.order;
步骤3：选择恢复方案
# 恢复方案选择
— 如果在回收站中，使用闪回恢复 from DB视频:www.itpux.com
FLASHBACK TABLE fgedu.order TO BEFORE DROP;
— 如果不在回收站，使用时间点恢复
— 1. 停止应用
— 2. 恢复备份
— 3. 应用归档日志
— 4. 验证数据
— 5. 启动应用
— 如果在回收站中，使用闪回恢复 from DB视频:www.itpux.com
FLASHBACK TABLE fgedu.order TO BEFORE DROP;
— 如果不在回收站，使用时间点恢复
— 1. 停止应用
— 2. 恢复备份
— 3. 应用归档日志
— 4. 验证数据
— 5. 启动应用
步骤4：执行恢复操作
# 执行恢复操作
— 闪回恢复
FLASHBACK TABLE fgedu.order TO BEFORE DROP;
— 时间点恢复
/dm/bin/dmrman CTLSTMT=”RESTORE DATABASE ‘/dm/data/dm.ini’ FROM ‘/backup/full/full_20240110.bak'”
/dm/bin/dmrman CTLSTMT=”RECOVER DATABASE ‘/dm/data/dm.ini’ FROM ‘/backup/archive’ UNTIL TIME ‘2024-01-10 14:30:00′”
— 闪回恢复
FLASHBACK TABLE fgedu.order TO BEFORE DROP;
— 时间点恢复
/dm/bin/dmrman CTLSTMT=”RESTORE DATABASE ‘/dm/data/dm.ini’ FROM ‘/backup/full/full_20240110.bak'”
/dm/bin/dmrman CTLSTMT=”RECOVER DATABASE ‘/dm/data/dm.ini’ FROM ‘/backup/archive’ UNTIL TIME ‘2024-01-10 14:30:00′”
步骤5：验证恢复结果
# 验证恢复结果
— 检查表是否存在
SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME = ‘ORDER’;
— 检查数据量
SELECT COUNT(*) FROM fgedu.order;
— 检查数据完整性
SELECT * FROM fgedu.order WHERE CREATE_TIME > ‘2024-01-10 14:00:00’;
— 检查表是否存在
SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME = ‘ORDER’;
— 检查数据量
SELECT COUNT(*) FROM fgedu.order;
— 检查数据完整性
SELECT * FROM fgedu.order WHERE CREATE_TIME > ‘2024-01-10 14:00:00’;
3.2 闪回恢复实施
闪回表恢复：
- 从回收站恢复被删除的表
- 恢复表到指定时间点的状态
- 恢复被删除的数据
# 闪回表恢复
— 从回收站恢复表
FLASHBACK TABLE fgedu.order TO BEFORE DROP;
— 恢复到指定时间点
FLASHBACK TABLE fgedu.order TO TIMESTAMP TO_TIMESTAMP(‘2024-01-10 14:30:00’, ‘YYYY-MM-DD HH24:MI:SS’);
— 查看回收站
SELECT OBJECT_NAME, ORIGINAL_NAME, DROPTIME FROM RECYCLEBIN;
— 清空回收站
PURGE RECYCLEBIN;
— 从回收站恢复表
FLASHBACK TABLE fgedu.order TO BEFORE DROP;
— 恢复到指定时间点
FLASHBACK TABLE fgedu.order TO TIMESTAMP TO_TIMESTAMP(‘2024-01-10 14:30:00’, ‘YYYY-MM-DD HH24:MI:SS’);
— 查看回收站
SELECT OBJECT_NAME, ORIGINAL_NAME, DROPTIME FROM RECYCLEBIN;
— 清空回收站
PURGE RECYCLEBIN;
# 闪回查询
— 查询过去某个时间点的数据
SELECT * FROM fgedu.order AS OF TIMESTAMP TO_TIMESTAMP(‘2024-01-10 14:30:00’, ‘YYYY-MM-DD HH24:MI:SS’);
— 查询过去某个SCN的数据
SELECT * FROM fgedu.order AS OF SCN 123456;
— 查询数据的版本历史
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, ORDER_ID, STATUS
FROM fgedu.order VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(‘2024-01-10 14:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND TO_TIMESTAMP(‘2024-01-10 15:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
WHERE ORDER_ID = 1001;
— 查询过去某个时间点的数据
SELECT * FROM fgedu.order AS OF TIMESTAMP TO_TIMESTAMP(‘2024-01-10 14:30:00’, ‘YYYY-MM-DD HH24:MI:SS’);
— 查询过去某个SCN的数据
SELECT * FROM fgedu.order AS OF SCN 123456;
— 查询数据的版本历史
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, ORDER_ID, STATUS
FROM fgedu.order VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(‘2024-01-10 14:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND TO_TIMESTAMP(‘2024-01-10 15:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
WHERE ORDER_ID = 1001;
3.3 时间点恢复实施
时间点恢复步骤：
- 1. 停止数据库服务
- 2. 恢复最近的完整备份
- 3. 应用增量备份
- 4. 应用归档日志到指定时间点
- 5. 打开数据库
# 时间点恢复脚本
#!/bin/bash
# 脚本名称：/dm/scripts/pitr_restore.sh
# 配置参数
DM_HOME=”/dm”
DM_DATA=”/dm/data”
BACKUP_DIR=”/backup/full”
ARCHIVE_DIR=”/backup/archive”
RECOVERY_TIME=”2024-01-10 14:30:00″
# 停止数据库
$DM_HOME/bin/dmserver $DM_DATA/dm.ini stop
# 恢复完整备份
$DM_HOME/bin/dmrman CTLSTMT=”RESTORE DATABASE ‘$DM_DATA/dm.ini’ FROM ‘$BACKUP_DIR/full_20240110.bak'”
# 恢复增量备份
$DM_HOME/bin/dmrman CTLSTMT=”RECOVER DATABASE ‘$DM_DATA/dm.ini’ FROM ‘$BACKUP_DIR/incremental_20240110_1400.bak'”
# 应用归档日志到指定时间点
$DM_HOME/bin/dmrman CTLSTMT=”RECOVER DATABASE ‘$DM_DATA/dm.ini’ FROM ‘$ARCHIVE_DIR’ UNTIL TIME ‘$RECOVERY_TIME'”
# 打开数据库
$DM_HOME/bin/dmserver $DM_DATA/dm.ini mount
$DM_HOME/bin/dmserver $DM_DATA/dm.ini open
echo “PITR recovery completed at $RECOVERY_TIME”
#!/bin/bash
# 脚本名称：/dm/scripts/pitr_restore.sh
# 配置参数
DM_HOME=”/dm”
DM_DATA=”/dm/data”
BACKUP_DIR=”/backup/full”
ARCHIVE_DIR=”/backup/archive”
RECOVERY_TIME=”2024-01-10 14:30:00″
# 停止数据库
$DM_HOME/bin/dmserver $DM_DATA/dm.ini stop
# 恢复完整备份
$DM_HOME/bin/dmrman CTLSTMT=”RESTORE DATABASE ‘$DM_DATA/dm.ini’ FROM ‘$BACKUP_DIR/full_20240110.bak'”
# 恢复增量备份
$DM_HOME/bin/dmrman CTLSTMT=”RECOVER DATABASE ‘$DM_DATA/dm.ini’ FROM ‘$BACKUP_DIR/incremental_20240110_1400.bak'”
# 应用归档日志到指定时间点
$DM_HOME/bin/dmrman CTLSTMT=”RECOVER DATABASE ‘$DM_DATA/dm.ini’ FROM ‘$ARCHIVE_DIR’ UNTIL TIME ‘$RECOVERY_TIME'”
# 打开数据库
$DM_HOME/bin/dmserver $DM_DATA/dm.ini mount
$DM_HOME/bin/dmserver $DM_DATA/dm.ini open
echo “PITR recovery completed at $RECOVERY_TIME”
# 时间点恢复输出示例
# RESTORE DATABASE ‘/dm/data/dm.ini’ FROM ‘/backup/full/full_20240110.bak’
# restore database ‘/dm/data/dm.ini’ from ‘/backup/full/full_20240110.bak’
# restore file: /backup/full/full_20240110.bak
# restore size: 2.5GB
# restore time: 900s
# RECOVER DATABASE ‘/dm/data/dm.ini’ FROM ‘/backup/archive’ UNTIL TIME ‘2024-01-10 14:30:00’
# recover database ‘/dm/data/dm.ini’ from ‘/backup/archive’ until time ‘2024-01-10 14:30:00’
# apply archive log: /backup/archive/arch_20240110_1400.log
# apply archive log: /backup/archive/arch_20240110_1410.log
# apply archive log: /backup/archive/arch_20240110_1420.log
# apply archive log: /backup/archive/arch_20240110_1430.log
# recovery completed at 2024-01-10 14:30:00
# RESTORE DATABASE ‘/dm/data/dm.ini’ FROM ‘/backup/full/full_20240110.bak’
# restore database ‘/dm/data/dm.ini’ from ‘/backup/full/full_20240110.bak’
# restore file: /backup/full/full_20240110.bak
# restore size: 2.5GB
# restore time: 900s
# RECOVER DATABASE ‘/dm/data/dm.ini’ FROM ‘/backup/archive’ UNTIL TIME ‘2024-01-10 14:30:00’
# recover database ‘/dm/data/dm.ini’ from ‘/backup/archive’ until time ‘2024-01-10 14:30:00’
# apply archive log: /backup/archive/arch_20240110_1400.log
# apply archive log: /backup/archive/arch_20240110_1410.log
# apply archive log: /backup/archive/arch_20240110_1420.log
# apply archive log: /backup/archive/arch_20240110_1430.log
# recovery completed at 2024-01-10 14:30:00
3.4 数据泵恢复实施
数据泵恢复：
- 从导出文件恢复数据
- 支持表级、用户级、全库级恢复
- 支持增量恢复
# 数据泵恢复
— 恢复表
$ /dm/bin/dimp USERID=SYSDBA/SYSDBA FILE=/backup/dmp/fgedu_order_20240110.dmp LOG=/backup/log/fgedu_order_20240110.log TABLES=fgedu.order
— 恢复用户
$ /dm/bin/dimp USERID=SYSDBA/SYSDBA FILE=/backup/dmp/fgedu_user_20240110.dmp LOG=/backup/log/fgedu_user_20240110.log SCHEMAS=fgedu
— 恢复全库
$ /dm/bin/dimp USERID=SYSDBA/SYSDBA FILE=/backup/dmp/fgedu_full_20240110.dmp LOG=/backup/log/fgedu_full_20240110.log FULL=Y
— 恢复到指定表空间
$ /dm/bin/dimp USERID=SYSDBA/SYSDBA FILE=/backup/dmp/fgedu_order_20240110.dmp LOG=/backup/log/fgedu_order_20240110.log TABLES=fgedu.order REMAP_TABLESPACE=USERS:FGEDU_TS
— 恢复表
$ /dm/bin/dimp USERID=SYSDBA/SYSDBA FILE=/backup/dmp/fgedu_order_20240110.dmp LOG=/backup/log/fgedu_order_20240110.log TABLES=fgedu.order
— 恢复用户
$ /dm/bin/dimp USERID=SYSDBA/SYSDBA FILE=/backup/dmp/fgedu_user_20240110.dmp LOG=/backup/log/fgedu_user_20240110.log SCHEMAS=fgedu
— 恢复全库
$ /dm/bin/dimp USERID=SYSDBA/SYSDBA FILE=/backup/dmp/fgedu_full_20240110.dmp LOG=/backup/log/fgedu_full_20240110.log FULL=Y
— 恢复到指定表空间
$ /dm/bin/dimp USERID=SYSDBA/SYSDBA FILE=/backup/dmp/fgedu_order_20240110.dmp LOG=/backup/log/fgedu_order_20240110.log TABLES=fgedu.order REMAP_TABLESPACE=USERS:FGEDU_TS
# 数据泵恢复输出示例
# Import: Release 8.1.2.192 – Production on Thu Jan 10 15:00:00 2024
# Copyright (c) 2000, 2024, Dameng. All rights reserved.
#
# Connected to: DM Database Server 8.1.2.192
# Starting “SYSDBA”.”SYS_IMPORT_TABLE_01″: SYSDBA/******** FILE=/backup/dmp/fgedu_order_20240110.dmp LOG=/backup/log/fgedu_order_20240110.log TABLES=fgedu.order
#
# Processing object type TABLE_EXPORT/TABLE/TABLE
# Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
# . . imported “FGEDU”.”ORDER” 1000000 rows
# Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
# Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
# Processing object type TABLE_EXPORT/TABLE/COMMENT/COMMENT
#
# Master table “SYSDBA”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
# ******************************************************************************
# Import job “SYSDBA”.”SYS_IMPORT_TABLE_01″ successfully completed at Thu Jan 10 15:05:00 2024 elapsed 0 00:05:00
# Import: Release 8.1.2.192 – Production on Thu Jan 10 15:00:00 2024
# Copyright (c) 2000, 2024, Dameng. All rights reserved.
#
# Connected to: DM Database Server 8.1.2.192
# Starting “SYSDBA”.”SYS_IMPORT_TABLE_01″: SYSDBA/******** FILE=/backup/dmp/fgedu_order_20240110.dmp LOG=/backup/log/fgedu_order_20240110.log TABLES=fgedu.order
#
# Processing object type TABLE_EXPORT/TABLE/TABLE
# Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
# . . imported “FGEDU”.”ORDER” 1000000 rows
# Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
# Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
# Processing object type TABLE_EXPORT/TABLE/COMMENT/COMMENT
#
# Master table “SYSDBA”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
# ******************************************************************************
# Import job “SYSDBA”.”SYS_IMPORT_TABLE_01″ successfully completed at Thu Jan 10 15:05:00 2024 elapsed 0 00:05:00
3.5 演练方案设计
演练方案：
- 演练目标：验证误操作恢复流程的有效性
- 演练场景：模拟各种误操作场景
- 演练频率：每季度一次
- 演练环境：使用测试环境演练
- 演练记录：记录演练过程和结果
# 演练方案
# 演练场景1：误删表恢复
# 1. 创建测试表
CREATE TABLE fgedu.test_order AS SELECT * FROM fgedu.order WHERE ROWNUM <= 10000; # 2. 删除测试表 DROP TABLE fgedu.test_order; # 3. 从回收站恢复 FLASHBACK TABLE fgedu.test_order TO BEFORE DROP; # 4. 验证数据 SELECT COUNT(*) FROM fgedu.test_order; # 5. 清理测试表 DROP TABLE fgedu.test_order PURGE;
# 演练场景1：误删表恢复
# 1. 创建测试表
CREATE TABLE fgedu.test_order AS SELECT * FROM fgedu.order WHERE ROWNUM <= 10000; # 2. 删除测试表 DROP TABLE fgedu.test_order; # 3. 从回收站恢复 FLASHBACK TABLE fgedu.test_order TO BEFORE DROP; # 4. 验证数据 SELECT COUNT(*) FROM fgedu.test_order; # 5. 清理测试表 DROP TABLE fgedu.test_order PURGE;
# 演练场景2：误删数据恢复
# 1. 创建测试表
CREATE TABLE fgedu.test_order AS SELECT * FROM fgedu.order WHERE ROWNUM <= 10000; # 2. 记录数据量 SELECT COUNT(*) FROM fgedu.test_order; # 3. 删除部分数据 DELETE FROM fgedu.test_order WHERE ORDER_ID <= 5000; COMMIT; # 4. 闪回查询 SELECT * FROM fgedu.test_order AS OF TIMESTAMP TO_TIMESTAMP('2024-01-10 15:00:00', 'YYYY-MM-DD HH24:MI:SS'); # 5. 插入恢复的数据 INSERT INTO fgedu.test_order SELECT * FROM fgedu.test_order AS OF TIMESTAMP TO_TIMESTAMP('2024-01-10 15:00:00', 'YYYY-MM-DD HH24:MI:SS') WHERE ORDER_ID <= 5000; COMMIT; # 6. 验证数据 SELECT COUNT(*) FROM fgedu.test_order; # 7. 清理测试表 DROP TABLE fgedu.test_order PURGE;
# 1. 创建测试表
CREATE TABLE fgedu.test_order AS SELECT * FROM fgedu.order WHERE ROWNUM <= 10000; # 2. 记录数据量 SELECT COUNT(*) FROM fgedu.test_order; # 3. 删除部分数据 DELETE FROM fgedu.test_order WHERE ORDER_ID <= 5000; COMMIT; # 4. 闪回查询 SELECT * FROM fgedu.test_order AS OF TIMESTAMP TO_TIMESTAMP('2024-01-10 15:00:00', 'YYYY-MM-DD HH24:MI:SS'); # 5. 插入恢复的数据 INSERT INTO fgedu.test_order SELECT * FROM fgedu.test_order AS OF TIMESTAMP TO_TIMESTAMP('2024-01-10 15:00:00', 'YYYY-MM-DD HH24:MI:SS') WHERE ORDER_ID <= 5000; COMMIT; # 6. 验证数据 SELECT COUNT(*) FROM fgedu.test_order; # 7. 清理测试表 DROP TABLE fgedu.test_order PURGE;
4. 生产案例与实战讲解
4.1 案例一：误删表恢复实战
案例背景：
- 时间：2024-01-10 14:30
- 操作：开发人员误执行DROP TABLE fgedu.order
- 影响：订单表被删除，业务中断
- 数据量：1000万行，约5GB
步骤1：确认误操作
# 查看审计日志
SELECT * FROM V$AUDITRECORDS WHERE SQLTEXT LIKE ‘%DROP TABLE fgedu.order%’ ORDER BY OPTIME DESC;
# 输出结果
# USER_NAME SQLTEXT OPTIME
# ———— ————————– ——————-
# FGEDU_DEV DROP TABLE fgedu.order 2024-01-10 14:30:00
SELECT * FROM V$AUDITRECORDS WHERE SQLTEXT LIKE ‘%DROP TABLE fgedu.order%’ ORDER BY OPTIME DESC;
# 输出结果
# USER_NAME SQLTEXT OPTIME
# ———— ————————– ——————-
# FGEDU_DEV DROP TABLE fgedu.order 2024-01-10 14:30:00
步骤2：检查回收站
# 查看回收站
SELECT OBJECT_NAME, ORIGINAL_NAME, DROPTIME, TYPE FROM RECYCLEBIN ORDER BY DROPTIME DESC;
# 输出结果
# OBJECT_NAME ORIGINAL_NAME DROPTIME TYPE
# ——————– —————- ———————— ——–
# BIN
$XXXXX==0 ORDER 2024-01-10 14:30:00 TABLE
SELECT OBJECT_NAME, ORIGINAL_NAME, DROPTIME, TYPE FROM RECYCLEBIN ORDER BY DROPTIME DESC;
# 输出结果
# OBJECT_NAME ORIGINAL_NAME DROPTIME TYPE
# ——————– —————- ———————— ——–
# BIN
$XXXXX==0 ORDER 2024-01-10 14:30:00 TABLE
步骤3：从回收站恢复
# 从回收站恢复表
FLASHBACK TABLE fgedu.order TO BEFORE DROP;
# 输出结果
# FLASHBACK TABLE fgedu.order TO BEFORE DROP
# Flashback completed.
FLASHBACK TABLE fgedu.order TO BEFORE DROP;
# 输出结果
# FLASHBACK TABLE fgedu.order TO BEFORE DROP
# Flashback completed.
步骤4：验证数据
# 验证表是否存在
SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME = ‘ORDER’;
# 输出结果
# TABLE_NAME
# ———-
# ORDER
# 验证数据量
SELECT COUNT(*) FROM fgedu.order;
# 输出结果
# COUNT(*)
# ———-
# 10000000
# 验证数据完整性
SELECT * FROM fgedu.order WHERE CREATE_TIME > ‘2024-01-10 14:00:00’;
# 输出结果
# ORDER_ID ORDER_NO CUSTOMER_ID STATUS CREATE_TIME
# ——— ———– ————- ——- ——————-
# 9999991 ORD20240110 10001 1 2024-01-10 14:25:00
# 9999992 ORD20240110 10002 1 2024-01-10 14:26:00
# 9999993 ORD20240110 10003 1 2024-01-10 14:27:00
# 9999994 ORD20240110 10004 1 2024-01-10 14:28:00
# 9999995 ORD20240110 10005 1 2024-01-10 14:29:00
SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME = ‘ORDER’;
# 输出结果
# TABLE_NAME
# ———-
# ORDER
# 验证数据量
SELECT COUNT(*) FROM fgedu.order;
# 输出结果
# COUNT(*)
# ———-
# 10000000
# 验证数据完整性
SELECT * FROM fgedu.order WHERE CREATE_TIME > ‘2024-01-10 14:00:00’;
# 输出结果
# ORDER_ID ORDER_NO CUSTOMER_ID STATUS CREATE_TIME
# ——— ———– ————- ——- ——————-
# 9999991 ORD20240110 10001 1 2024-01-10 14:25:00
# 9999992 ORD20240110 10002 1 2024-01-10 14:26:00
# 9999993 ORD20240110 10003 1 2024-01-10 14:27:00
# 9999994 ORD20240110 10004 1 2024-01-10 14:28:00
# 9999995 ORD20240110 10005 1 2024-01-10 14:29:00
步骤5：业务验证
# 业务验证
— 查询最新订单
SELECT * FROM fgedu.order ORDER BY ORDER_ID DESC LIMIT 10;
— 查询订单状态
SELECT STATUS, COUNT(*) FROM fgedu.order GROUP BY STATUS;
— 查询订单金额
SELECT SUM(AMOUNT) FROM fgedu.order WHERE CREATE_TIME > ‘2024-01-10 14:00:00’;
— 查询最新订单
SELECT * FROM fgedu.order ORDER BY ORDER_ID DESC LIMIT 10;
— 查询订单状态
SELECT STATUS, COUNT(*) FROM fgedu.order GROUP BY STATUS;
— 查询订单金额
SELECT SUM(AMOUNT) FROM fgedu.order WHERE CREATE_TIME > ‘2024-01-10 14:00:00’;
案例总结：
- 恢复时间：5分钟
- 数据完整性：100%
- 业务影响：最小化
- 经验教训：启用回收站，定期清理回收站
4.2 案例二：误删数据恢复实战
案例背景：
- 时间：2024-01-10 15:00
- 操作：开发人员误执行DELETE FROM fgedu.order WHERE ORDER_ID <= 100000
- 影响：10万条订单数据被删除
- 数据量：1000万行，删除10万行
步骤1：确认误操作
# 查看审计日志
SELECT * FROM V$AUDITRECORDS WHERE SQLTEXT LIKE ‘%DELETE FROM fgedu.order%’ ORDER BY OPTIME DESC;
# 输出结果
# USER_NAME SQLTEXT OPTIME
# ———— ————————– ——————-
# FGEDU_DEV DELETE FROM fgedu.order 2024-01-10 15:00:00
# WHERE ORDER_ID <= 100000
SELECT * FROM V$AUDITRECORDS WHERE SQLTEXT LIKE ‘%DELETE FROM fgedu.order%’ ORDER BY OPTIME DESC;
# 输出结果
# USER_NAME SQLTEXT OPTIME
# ———— ————————– ——————-
# FGEDU_DEV DELETE FROM fgedu.order 2024-01-10 15:00:00
# WHERE ORDER_ID <= 100000
步骤2：闪回查询
# 闪回查询删除前的数据
SELECT COUNT(*) FROM fgedu.order AS OF TIMESTAMP TO_TIMESTAMP(‘2024-01-10 14:59:00’, ‘YYYY-MM-DD HH24:MI:SS’) WHERE ORDER_ID <= 100000; # 输出结果 # COUNT(*) # ---------- # 100000 # 查询当前数据量 SELECT COUNT(*) FROM fgedu.order WHERE ORDER_ID <= 100000; # 输出结果 # COUNT(*) # ---------- # 0
SELECT COUNT(*) FROM fgedu.order AS OF TIMESTAMP TO_TIMESTAMP(‘2024-01-10 14:59:00’, ‘YYYY-MM-DD HH24:MI:SS’) WHERE ORDER_ID <= 100000; # 输出结果 # COUNT(*) # ---------- # 100000 # 查询当前数据量 SELECT COUNT(*) FROM fgedu.order WHERE ORDER_ID <= 100000; # 输出结果 # COUNT(*) # ---------- # 0
步骤3：闪回版本查询
# 查询数据版本历史
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, COUNT(*)
FROM fgedu.order VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(‘2024-01-10 14:50:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND TO_TIMESTAMP(‘2024-01-10 15:10:00’, ‘YYYY-MM-DD HH24:MI:SS’)
WHERE ORDER_ID <= 100000 GROUP BY VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION; # 输出结果 # VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_OPERATION COUNT(*) # --------------------- --------------------- -------------------- ---------- # 2024-01-10 14:50:00 2024-01-10 15:00:00 I 100000 # 2024-01-10 15:00:00 NULL D 100000
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, COUNT(*)
FROM fgedu.order VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(‘2024-01-10 14:50:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND TO_TIMESTAMP(‘2024-01-10 15:10:00’, ‘YYYY-MM-DD HH24:MI:SS’)
WHERE ORDER_ID <= 100000 GROUP BY VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION; # 输出结果 # VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_OPERATION COUNT(*) # --------------------- --------------------- -------------------- ---------- # 2024-01-10 14:50:00 2024-01-10 15:00:00 I 100000 # 2024-01-10 15:00:00 NULL D 100000
步骤4：插入恢复数据
# 插入恢复的数据
INSERT INTO fgedu.order
SELECT * FROM fgedu.order AS OF TIMESTAMP TO_TIMESTAMP(‘2024-01-10 14:59:00’, ‘YYYY-MM-DD HH24:MI:SS’)
WHERE ORDER_ID <= 100000; # 输出结果 # 100000 rows inserted. COMMIT; # 输出结果 # Commit completed.
INSERT INTO fgedu.order
SELECT * FROM fgedu.order AS OF TIMESTAMP TO_TIMESTAMP(‘2024-01-10 14:59:00’, ‘YYYY-MM-DD HH24:MI:SS’)
WHERE ORDER_ID <= 100000; # 输出结果 # 100000 rows inserted. COMMIT; # 输出结果 # Commit completed.
步骤5：验证数据
# 验证数据量
SELECT COUNT(*) FROM fgedu.order WHERE ORDER_ID <= 100000; # 输出结果 # COUNT(*) # ---------- # 100000 # 验证数据完整性 SELECT * FROM fgedu.order WHERE ORDER_ID <= 10; # 输出结果 # ORDER_ID ORDER_NO CUSTOMER_ID STATUS CREATE_TIME # --------- ----------- ------------- ------- ------------------- # 1 ORD20240101 10001 1 2024-01-01 10:00:00 # 2 ORD20240101 10002 1 2024-01-01 10:05:00 # 3 ORD20240101 10003 1 2024-01-01 10:10:00 # 4 ORD20240101 10004 1 2024-01-01 10:15:00 # 5 ORD20240101 10005 1 2024-01-01 10:20:00 # 6 ORD20240101 10006 1 2024-01-01 10:25:00 # 7 ORD20240101 10007 1 2024-01-01 10:30:00 # 8 ORD20240101 10008 1 2024-01-01 10:35:00 # 9 ORD20240101 10009 1 2024-01-01 10:40:00 # 10 ORD20240101 10010 1 2024-01-01 10:45:00
SELECT COUNT(*) FROM fgedu.order WHERE ORDER_ID <= 100000; # 输出结果 # COUNT(*) # ---------- # 100000 # 验证数据完整性 SELECT * FROM fgedu.order WHERE ORDER_ID <= 10; # 输出结果 # ORDER_ID ORDER_NO CUSTOMER_ID STATUS CREATE_TIME # --------- ----------- ------------- ------- ------------------- # 1 ORD20240101 10001 1 2024-01-01 10:00:00 # 2 ORD20240101 10002 1 2024-01-01 10:05:00 # 3 ORD20240101 10003 1 2024-01-01 10:10:00 # 4 ORD20240101 10004 1 2024-01-01 10:15:00 # 5 ORD20240101 10005 1 2024-01-01 10:20:00 # 6 ORD20240101 10006 1 2024-01-01 10:25:00 # 7 ORD20240101 10007 1 2024-01-01 10:30:00 # 8 ORD20240101 10008 1 2024-01-01 10:35:00 # 9 ORD20240101 10009 1 2024-01-01 10:40:00 # 10 ORD20240101 10010 1 2024-01-01 10:45:00
案例总结：
- 恢复时间：10分钟
- 数据完整性：100%
- 业务影响：最小化
- 经验教训：设置合理的UNDO_RETENTION参数
4.3 案例三：误更新数据恢复实战
案例背景：
- 时间：2024-01-10 16:00
- 操作：开发人员误执行UPDATE fgedu.order SET STATUS = 0 WHERE ORDER_ID <= 100000
- 影响：10万条订单状态被错误更新
- 数据量：1000万行，更新10万行
步骤1：确认误操作
# 查看审计日志
SELECT * FROM V$AUDITRECORDS WHERE SQLTEXT LIKE ‘%UPDATE fgedu.order%’ ORDER BY OPTIME DESC;
# 输出结果
# USER_NAME SQLTEXT OPTIME
# ———— ————————– ——————-
# FGEDU_DEV UPDATE fgedu.order 2024-01-10 16:00:00
# SET STATUS = 0
# WHERE ORDER_ID <= 100000
SELECT * FROM V$AUDITRECORDS WHERE SQLTEXT LIKE ‘%UPDATE fgedu.order%’ ORDER BY OPTIME DESC;
# 输出结果
# USER_NAME SQLTEXT OPTIME
# ———— ————————– ——————-
# FGEDU_DEV UPDATE fgedu.order 2024-01-10 16:00:00
# SET STATUS = 0
# WHERE ORDER_ID <= 100000
步骤2：闪回查询
# 闪回查询更新前的数据
SELECT STATUS, COUNT(*) FROM fgedu.order AS OF TIMESTAMP TO_TIMESTAMP(‘2024-01-10 15:59:00’, ‘YYYY-MM-DD HH24:MI:SS’) WHERE ORDER_ID <= 100000 GROUP BY STATUS; # 输出结果 # STATUS COUNT(*) # ------- ---------- # 1 95000 # 2 5000 # 查询当前状态 SELECT STATUS, COUNT(*) FROM fgedu.order WHERE ORDER_ID <= 100000 GROUP BY STATUS; # 输出结果 # STATUS COUNT(*) # ------- ---------- # 0 100000
SELECT STATUS, COUNT(*) FROM fgedu.order AS OF TIMESTAMP TO_TIMESTAMP(‘2024-01-10 15:59:00’, ‘YYYY-MM-DD HH24:MI:SS’) WHERE ORDER_ID <= 100000 GROUP BY STATUS; # 输出结果 # STATUS COUNT(*) # ------- ---------- # 1 95000 # 2 5000 # 查询当前状态 SELECT STATUS, COUNT(*) FROM fgedu.order WHERE ORDER_ID <= 100000 GROUP BY STATUS; # 输出结果 # STATUS COUNT(*) # ------- ---------- # 0 100000
步骤3：闪回版本查询
# 查询数据版本历史
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, STATUS, COUNT(*)
FROM fgedu.order VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(‘2024-01-10 15:50:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND TO_TIMESTAMP(‘2024-01-10 16:10:00’, ‘YYYY-MM-DD HH24:MI:SS’)
WHERE ORDER_ID <= 100000 GROUP BY VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, STATUS; # 输出结果 # VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_OPERATION STATUS COUNT(*) # --------------------- --------------------- -------------------- ------- ---------- # 2024-01-10 15:50:00 2024-01-10 16:00:00 U 1 95000 # 2024-01-10 15:50:00 2024-01-10 16:00:00 U 2 5000 # 2024-01-10 16:00:00 NULL U 0 100000
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, STATUS, COUNT(*)
FROM fgedu.order VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(‘2024-01-10 15:50:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND TO_TIMESTAMP(‘2024-01-10 16:10:00’, ‘YYYY-MM-DD HH24:MI:SS’)
WHERE ORDER_ID <= 100000 GROUP BY VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, STATUS; # 输出结果 # VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_OPERATION STATUS COUNT(*) # --------------------- --------------------- -------------------- ------- ---------- # 2024-01-10 15:50:00 2024-01-10 16:00:00 U 1 95000 # 2024-01-10 15:50:00 2024-01-10 16:00:00 U 2 5000 # 2024-01-10 16:00:00 NULL U 0 100000
步骤4：创建临时表
# 创建临时表保存更新前的数据
CREATE TABLE fgedu.order_backup AS
SELECT * FROM fgedu.order AS OF TIMESTAMP TO_TIMESTAMP(‘2024-01-10 15:59:00’, ‘YYYY-MM-DD HH24:MI:SS’)
WHERE ORDER_ID <= 100000; # 输出结果 # Table created.
CREATE TABLE fgedu.order_backup AS
SELECT * FROM fgedu.order AS OF TIMESTAMP TO_TIMESTAMP(‘2024-01-10 15:59:00’, ‘YYYY-MM-DD HH24:MI:SS’)
WHERE ORDER_ID <= 100000; # 输出结果 # Table created.
步骤5：恢复数据
# 恢复数据
UPDATE fgedu.order o
SET STATUS = (SELECT STATUS FROM fgedu.order_backup b WHERE b.ORDER_ID = o.ORDER_ID)
WHERE ORDER_ID <= 100000; # 输出结果 # 100000 rows updated. COMMIT; # 输出结果 # Commit completed.
UPDATE fgedu.order o
SET STATUS = (SELECT STATUS FROM fgedu.order_backup b WHERE b.ORDER_ID = o.ORDER_ID)
WHERE ORDER_ID <= 100000; # 输出结果 # 100000 rows updated. COMMIT; # 输出结果 # Commit completed.
步骤6：验证数据
# 验证数据
SELECT STATUS, COUNT(*) FROM fgedu.order WHERE ORDER_ID <= 100000 GROUP BY STATUS; # 输出结果 # STATUS COUNT(*) # ------- ---------- # 1 95000 # 2 5000 # 清理临时表 DROP TABLE fgedu.order_backup PURGE;
SELECT STATUS, COUNT(*) FROM fgedu.order WHERE ORDER_ID <= 100000 GROUP BY STATUS; # 输出结果 # STATUS COUNT(*) # ------- ---------- # 1 95000 # 2 5000 # 清理临时表 DROP TABLE fgedu.order_backup PURGE;
案例总结：
- 恢复时间：15分钟
- 数据完整性：100%
- 业务影响：最小化
- 经验教训：重要更新操作前先备份数据
4.4 案例四：误删除表空间恢复实战
案例背景：
- 时间：2024-01-10 17:00
- 操作：开发人员误执行DROP TABLESPACE fgedu_ts INCLUDING CONTENTS
- 影响：表空间及其所有对象被删除
- 数据量：50GB，包含10个表
步骤1：确认误操作
# 查看审计日志
SELECT * FROM V$AUDITRECORDS WHERE SQLTEXT LIKE ‘%DROP TABLESPACE%’ ORDER BY OPTIME DESC;
# 输出结果
# USER_NAME SQLTEXT OPTIME
# ———— ————————– ——————-
# FGEDU_DEV DROP TABLESPACE fgedu_ts 2024-01-10 17:00:00
# INCLUDING CONTENTS
SELECT * FROM V$AUDITRECORDS WHERE SQLTEXT LIKE ‘%DROP TABLESPACE%’ ORDER BY OPTIME DESC;
# 输出结果
# USER_NAME SQLTEXT OPTIME
# ———— ————————– ——————-
# FGEDU_DEV DROP TABLESPACE fgedu_ts 2024-01-10 17:00:00
# INCLUDING CONTENTS
步骤2：检查备份
# 查看可用备份
$ ls -lh /backup/full/
# 输出结果
# total 50G
# -rw-r–r– 1 dm dm 50G Jan 10 12:00 full_20240110.bak
# 查看归档日志
$ ls -lh /backup/archive/
# 输出结果
# total 5G
# -rw-r–r– 1 dm dm 500M Jan 10 12:00 arch_20240110_1200.log
# -rw-r–r– 1 dm dm 500M Jan 10 13:00 arch_20240110_1300.log
# -rw-r–r– 1 dm dm 500M Jan 10 14:00 arch_20240110_1400.log
# -rw-r–r– 1 dm dm 500M Jan 10 15:00 arch_20240110_1500.log
# -rw-r–r– 1 dm dm 500M Jan 10 16:00 arch_20240110_1600.log
# -rw-r–r– 1 dm dm 500M Jan 10 17:00 arch_20240110_1700.log
$ ls -lh /backup/full/
# 输出结果
# total 50G
# -rw-r–r– 1 dm dm 50G Jan 10 12:00 full_20240110.bak
# 查看归档日志
$ ls -lh /backup/archive/
# 输出结果
# total 5G
# -rw-r–r– 1 dm dm 500M Jan 10 12:00 arch_20240110_1200.log
# -rw-r–r– 1 dm dm 500M Jan 10 13:00 arch_20240110_1300.log
# -rw-r–r– 1 dm dm 500M Jan 10 14:00 arch_20240110_1400.log
# -rw-r–r– 1 dm dm 500M Jan 10 15:00 arch_20240110_1500.log
# -rw-r–r– 1 dm dm 500M Jan 10 16:00 arch_20240110_1600.log
# -rw-r–r– 1 dm dm 500M Jan 10 17:00 arch_20240110_1700.log
步骤3：停止数据库
# 停止数据库
$ /dm/bin/dmserver /dm/data/dm.ini stop
# 输出结果
# DM server stopped successfully.
$ /dm/bin/dmserver /dm/data/dm.ini stop
# 输出结果
# DM server stopped successfully.
步骤4：恢复备份
# 恢复完整备份
$ /dm/bin/dmrman CTLSTMT=”RESTORE DATABASE ‘/dm/data/dm.ini’ FROM ‘/backup/full/full_20240110.bak'”
# 输出结果
# restore database ‘/dm/data/dm.ini’ from ‘/backup/full/full_20240110.bak’
# restore file: /backup/full/full_20240110.bak
# restore size: 50GB
# restore time: 1800s
$ /dm/bin/dmrman CTLSTMT=”RESTORE DATABASE ‘/dm/data/dm.ini’ FROM ‘/backup/full/full_20240110.bak'”
# 输出结果
# restore database ‘/dm/data/dm.ini’ from ‘/backup/full/full_20240110.bak’
# restore file: /backup/full/full_20240110.bak
# restore size: 50GB
# restore time: 1800s
步骤5：应用归档日志
# 应用归档日志到指定时间点
$ /dm/bin/dmrman CTLSTMT=”RECOVER DATABASE ‘/dm/data/dm.ini’ FROM ‘/backup/archive’ UNTIL TIME ‘2024-01-10 16:59:00′”
# 输出结果
# recover database ‘/dm/data/dm.ini’ from ‘/backup/archive’ until time ‘2024-01-10 16:59:00’
# apply archive log: /backup/archive/arch_20240110_1200.log
# apply archive log: /backup/archive/arch_20240110_1300.log
# apply archive log: /backup/archive/arch_20240110_1400.log
# apply archive log: /backup/archive/arch_20240110_1500.log
# apply archive log: /backup/archive/arch_20240110_1600.log
# recovery completed at 2024-01-10 16:59:00
$ /dm/bin/dmrman CTLSTMT=”RECOVER DATABASE ‘/dm/data/dm.ini’ FROM ‘/backup/archive’ UNTIL TIME ‘2024-01-10 16:59:00′”
# 输出结果
# recover database ‘/dm/data/dm.ini’ from ‘/backup/archive’ until time ‘2024-01-10 16:59:00’
# apply archive log: /backup/archive/arch_20240110_1200.log
# apply archive log: /backup/archive/arch_20240110_1300.log
# apply archive log: /backup/archive/arch_20240110_1400.log
# apply archive log: /backup/archive/arch_20240110_1500.log
# apply archive log: /backup/archive/arch_20240110_1600.log
# recovery completed at 2024-01-10 16:59:00
步骤6：打开数据库
# 打开数据库
$ /dm/bin/dmserver /dm/data/dm.ini mount
$ /dm/bin/dmserver /dm/data/dm.ini open
# 输出结果
# DM server started successfully.
$ /dm/bin/dmserver /dm/data/dm.ini mount
$ /dm/bin/dmserver /dm/data/dm.ini open
# 输出结果
# DM server started successfully.
步骤7：验证数据
# 验证表空间是否存在
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = ‘FGEDU_TS’;
# 输出结果
# TABLESPACE_NAME
# —————-
# FGEDU_TS
# 验证表是否存在
SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLESPACE_NAME = ‘FGEDU_TS’;
# 输出结果
# TABLE_NAME
# ———-
# ORDER
# CUSTOMER
# PRODUCT
# ORDER_ITEM
# …
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = ‘FGEDU_TS’;
# 输出结果
# TABLESPACE_NAME
# —————-
# FGEDU_TS
# 验证表是否存在
SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLESPACE_NAME = ‘FGEDU_TS’;
# 输出结果
# TABLE_NAME
# ———-
# ORDER
# CUSTOMER
# PRODUCT
# ORDER_ITEM
# …
案例总结：
- 恢复时间：60分钟
- 数据完整性：100%
- 业务影响：较大
- 经验教训：重要表空间删除前需要审批
5. 风哥经验总结与分享
5.1 最佳实践总结
最佳实践：
- 启用回收站：启用回收站功能，快速恢复误删表
- 设置UNDO_RETENTION：设置合理的UNDO_RETENTION参数，延长闪回查询时间窗口
- 定期备份：定期进行全量备份和增量备份
- 归档日志：启用归档日志，保留足够长的归档日志
- 权限控制：严格控制DROP、DELETE、UPDATE权限
- 操作审计：启用审计功能，记录所有重要操作
- 演练测试：定期进行误操作恢复演练
# 最佳实践配置
— 启用回收站
SP_SET_PARA_VALUE(2, ‘ENABLE_RECYCLEBIN’, 1);
— 设置UNDO_RETENTION
SP_SET_PARA_VALUE(2, ‘UNDO_RETENTION’, 3600);
— 启用审计
SP_SET_PARA_VALUE(2, ‘ENABLE_AUDIT’, 1);
— 启用归档
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE ADD ARCHIVELOG ‘DEST=/dm/arch, TYPE=LOCAL, FILE_SIZE=1024, SPACE_LIMIT=10240’;
— 启用回收站
SP_SET_PARA_VALUE(2, ‘ENABLE_RECYCLEBIN’, 1);
— 设置UNDO_RETENTION
SP_SET_PARA_VALUE(2, ‘UNDO_RETENTION’, 3600);
— 启用审计
SP_SET_PARA_VALUE(2, ‘ENABLE_AUDIT’, 1);
— 启用归档
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE ADD ARCHIVELOG ‘DEST=/dm/arch, TYPE=LOCAL, FILE_SIZE=1024, SPACE_LIMIT=10240’;
5.2 常见错误与避免
常见错误：
- 错误1：未启用回收站，误删表无法快速恢复
- 错误2：UNDO_RETENTION设置过小，闪回查询时间窗口不足
- 错误3：未启用归档日志，无法进行时间点恢复
- 错误4：备份文件损坏，恢复失败
- 错误5：归档日志不完整，无法恢复到指定时间点
# 避免错误的措施
— 检查回收站状态
SELECT * FROM V$PARAMETER WHERE NAME = ‘ENABLE_RECYCLEBIN’;
— 检查UNDO_RETENTION
SELECT * FROM V$PARAMETER WHERE NAME = ‘UNDO_RETENTION’;
— 检查归档模式
SELECT ARCH_MODE FROM V$DATABASE;
— 验证备份文件
$ /dm/bin/dmrman CTLSTMT=”VALIDATE BACKUPSET ‘/backup/full/full_20240110.bak'”
— 检查归档日志完整性
$ /dm/bin/dmrman CTLSTMT=”CHECK ARCHIVELOG ‘/backup/archive'”
— 检查回收站状态
SELECT * FROM V$PARAMETER WHERE NAME = ‘ENABLE_RECYCLEBIN’;
— 检查UNDO_RETENTION
SELECT * FROM V$PARAMETER WHERE NAME = ‘UNDO_RETENTION’;
— 检查归档模式
SELECT ARCH_MODE FROM V$DATABASE;
— 验证备份文件
$ /dm/bin/dmrman CTLSTMT=”VALIDATE BACKUPSET ‘/backup/full/full_20240110.bak'”
— 检查归档日志完整性
$ /dm/bin/dmrman CTLSTMT=”CHECK ARCHIVELOG ‘/backup/archive'”
5.3 演练经验分享
演练经验：
- 演练频率：每季度一次演练
- 演练环境：使用测试环境演练
- 演练场景：模拟各种误操作场景
- 演练记录：记录演练过程和结果
- 演练总结：总结演练经验，改进恢复流程
# 演练检查清单
# [ ] 误删表恢复演练
# [ ] 误删数据恢复演练
# [ ] 误更新数据恢复演练
# [ ] 误删除表空间恢复演练
# [ ] 时间点恢复演练
# [ ] 数据泵恢复演练
# [ ] 恢复流程验证
# [ ] 恢复时间验证
# [ ] 数据完整性验证
# [ ] 业务影响评估
# [ ] 误删表恢复演练
# [ ] 误删数据恢复演练
# [ ] 误更新数据恢复演练
# [ ] 误删除表空间恢复演练
# [ ] 时间点恢复演练
# [ ] 数据泵恢复演练
# [ ] 恢复流程验证
# [ ] 恢复时间验证
# [ ] 数据完整性验证
# [ ] 业务影响评估
5.4 应急响应建议
应急响应：
- 快速响应：发现误操作后立即响应
- 停止操作：立即停止相关操作，防止数据进一步损坏
- 评估影响：快速评估误操作的影响范围
- 选择方案：根据误操作类型选择合适的恢复方案
- 执行恢复：快速执行恢复操作
- 验证数据：验证恢复的数据完整性
- 业务恢复：尽快恢复业务
- 总结经验：总结误操作原因，改进预防措施
# 应急响应流程
# 1. 发现误操作
# – 时间：记录误操作发生时间
# – 操作：记录误操作的具体操作
# – 影响：评估误操作的影响范围
# 2. 停止操作
# – 停止相关应用
# – 停止相关数据库操作
# – 保存当前状态
# 3. 选择恢复方案
# – 闪回恢复：如果适用，优先使用闪回恢复
# – 时间点恢复：如果闪回不适用，使用时间点恢复
# – 数据泵恢复：如果有逻辑备份，使用数据泵恢复
# 4. 执行恢复
# – 按照恢复流程执行恢复操作
# – 记录恢复过程
# – 监控恢复进度
# 5. 验证数据
# – 验证数据完整性
# – 验证数据一致性
# – 验证业务功能
# 6. 恢复业务
# – 启动相关应用
# – 监控业务运行
# – 通知相关人员
# 7. 总结经验
# – 分析误操作原因
# – 改进预防措施
# – 更新恢复流程
# 1. 发现误操作
# – 时间：记录误操作发生时间
# – 操作：记录误操作的具体操作
# – 影响：评估误操作的影响范围
# 2. 停止操作
# – 停止相关应用
# – 停止相关数据库操作
# – 保存当前状态
# 3. 选择恢复方案
# – 闪回恢复：如果适用，优先使用闪回恢复
# – 时间点恢复：如果闪回不适用，使用时间点恢复
# – 数据泵恢复：如果有逻辑备份，使用数据泵恢复
# 4. 执行恢复
# – 按照恢复流程执行恢复操作
# – 记录恢复过程
# – 监控恢复进度
# 5. 验证数据
# – 验证数据完整性
# – 验证数据一致性
# – 验证业务功能
# 6. 恢复业务
# – 启动相关应用
# – 监控业务运行
# – 通知相关人员
# 7. 总结经验
# – 分析误操作原因
# – 改进预防措施
# – 更新恢复流程
风哥总结：
- 误操作恢复是DBA必备技能
- 预防误操作比恢复更重要
- 定期演练是提高恢复能力的关键
- 完善的备份策略是恢复的基础
- 快速响应可以最小化业务影响
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息