2. 首页 > 国产数据库教程 > YashanDB教程 > 正文

yashandb教程FG037-YashanDB单表恢复实践

教程整理：风哥教程  |  更新时间：2026-04-09  |  教程分类：YashanDB教程  |  文档学习：29

本文档风哥主要介绍YashanDB单表恢复的相关知识，包括YashanDB单表恢复的概念、类型、优势，以及RMAN单表恢复、数据泵单表恢复、传统单表恢复的具体实现和使用方法，风哥教程参考YashanDB官方文档备份恢复手册内容，适合DBA和开发人员在学习和测试中使用。学习交流加群风哥微信: itpux-com

Part01-基础概念与理论知识

1.1 YashanDB单表恢复概念

YashanDB单表恢复是指只恢复数据库中的单个表，而不是整个数据库。单表恢复可以快速恢复误删除或损坏的表，是数据库恢复的重要组成部分。更多视频教程www.fgedu.net.cn

YashanDB单表恢复的特点：

  • 只恢复单个表，而不是整个数据库
  • 可以快速恢复误删除或损坏的表
  • 减少恢复时间和资源消耗
  • 适合处理表级别的数据丢失
  • 可以在数据库运行状态下进行

1.2 YashanDB单表恢复类型

YashanDB支持以下类型的单表恢复：

# 单表恢复类型
– RMAN单表恢复：使用RMAN工具进行单表恢复
– 数据泵单表恢复：使用数据泵工具进行单表恢复
– 传统单表恢复：使用传统导出导入工具进行单表恢复
– 时间点单表恢复：恢复表到指定的时间点
– 基于备份的单表恢复：使用备份文件进行单表恢复
– 基于归档日志的单表恢复：使用归档日志进行单表恢复

1.3 YashanDB单表恢复的优势

YashanDB单表恢复的优势：

  • 快速恢复：只恢复单个表，减少恢复时间
  • 资源消耗低：减少系统资源消耗
  • 影响范围小：只影响单个表，不影响其他表
  • 灵活性高：可以选择恢复到指定的时间点
  • 操作简单：操作步骤简单，易于执行
风哥提示：单表恢复是数据库恢复的重要组成部分。建议掌握单表恢复的方法，以便在表级数据丢失时能够快速恢复。学习交流加群风哥QQ113257174

Part02-生产环境规划与建议

2.1 YashanDB单表恢复规划

在生产环境中进行YashanDB单表恢复规划时，需要考虑以下因素：

# 单表恢复规划考虑因素
1. 备份策略：确保有可用的备份文件
2. 恢复时间目标：根据业务需求确定恢复时间目标
3. 恢复方法：选择合适的单表恢复方法
4. 测试验证：定期测试单表恢复过程
5. 文档记录：记录单表恢复的步骤和方法
6. 应急响应：建立单表恢复的应急响应流程
7. 权限管理：确保恢复操作有足够的权限
8. 监控告警：监控表级别的数据变化和异常

2.2 YashanDB单表恢复策略

YashanDB单表恢复的策略：

单表恢复策略：

  • 备份策略：定期进行全库备份和增量备份，确保有可用的备份文件
  • 恢复方法选择：根据实际情况选择合适的单表恢复方法
  • 时间点选择：根据业务需求选择合适的恢复时间点
  • 测试验证：定期测试单表恢复过程，确保恢复方法有效
  • 文档记录：记录单表恢复的步骤和方法，便于应急使用
  • 应急响应：建立单表恢复的应急响应流程，确保在数据丢失时能够快速响应

2.3 YashanDB单表恢复最佳实践

YashanDB单表恢复的最佳实践：

  • 定期备份：定期进行全库备份和增量备份，确保有可用的备份文件
  • 测试验证：定期测试单表恢复过程，确保恢复方法有效
  • 文档记录：记录单表恢复的步骤和方法，便于应急使用
  • 权限管理：确保恢复操作有足够的权限
  • 监控告警：监控表级别的数据变化和异常，及时发现问题
  • 应急响应：建立单表恢复的应急响应流程，确保在数据丢失时能够快速响应
  • 恢复验证：恢复后验证表数据的完整性和一致性
风哥提示：单表恢复的策略需要根据实际业务需求进行调整。建议建立完善的单表恢复策略，确保在表级数据丢失时能够快速恢复。更多学习教程公众号风哥教程itpux_com

Part03-生产环境项目实施方案

3.1 YashanDB RMAN单表恢复

3.1.1 YashanDB RMAN单表恢复命令

— 连接到RMAN
RMAN target /

— 恢复表到指定时间点
RECOVER TABLE fgedu.employees UNTIL TIME ‘2026-04-11:10:00:00’ AUXILIARY DESTINATION ‘/yashandb/auxiliary’;

— 恢复表到指定SCN
RECOVER TABLE fgedu.employees UNTIL SCN 1234567890 AUXILIARY DESTINATION ‘/yashandb/auxiliary’;

— 恢复表到指定日志序列
RECOVER TABLE fgedu.employees UNTIL SEQUENCE 100 THREAD 1 AUXILIARY DESTINATION ‘/yashandb/auxiliary’;

— 恢复表并导出到指定位置
RECOVER TABLE fgedu.employees UNTIL TIME ‘2026-04-11:10:00:00’ AUXILIARY DESTINATION ‘/yashandb/auxiliary’ DATAPUMP DESTINATION ‘/yashandb/dump’ DUMP FILE ’employees.dmp’;

3.2 YashanDB数据泵单表恢复

3.2.1 YashanDB数据泵单表恢复命令

— 创建数据泵目录
CREATE DIRECTORY datapump_dir AS ‘/yashandb/backup’;
GRANT READ, WRITE ON DIRECTORY datapump_dir TO fgedu;

— 导出单个表
EXPDP fgedu/fgedu@fgedudb TABLES=fgedu.employees DIRECTORY=datapump_dir DUMPFILE=employees.dmp LOGFILE=employees_exp.log;

— 导入单个表
IMPDP fgedu/fgedu@fgedudb TABLES=fgedu.employees DIRECTORY=datapump_dir DUMPFILE=employees.dmp LOGFILE=employees_imp.log TABLE_EXISTS_ACTION=REPLACE;

— 从全库备份中导出单个表
EXPDP fgedu/fgedu@fgedudb TABLES=fgedu.employees DIRECTORY=datapump_dir DUMPFILE=employees.dmp LOGFILE=employees_exp.log FLASHBACK_TIME=SYSTIMESTAMP;

3.3 YashanDB传统单表恢复

3.3.1 YashanDB传统单表恢复命令

— 导出单个表
EXP fgedu/fgedu@fgedudb TABLES=employees FILE=/yashandb/backup/employees.dmp LOG=/yashandb/backup/employees_exp.log;

— 导入单个表
IMP fgedu/fgedu@fgedudb TABLES=employees FILE=/yashandb/backup/employees.dmp LOG=/yashandb/backup/employees_imp.log IGNORE=Y;

3.4 YashanDB单表恢复验证

3.4.1 YashanDB单表恢复验证方法

— 验证表结构
DESCRIBE fgedu.employees;

— 验证表数据
SELECT COUNT(*) FROM fgedu.employees;

— 验证表数据完整性
SELECT * FROM fgedu.employees WHERE emp_id = 1;

— 验证表约束
SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name = ‘EMPLOYEES’;

— 验证表索引
SELECT index_name, index_type FROM user_indexes WHERE table_name = ‘EMPLOYEES’;

风哥提示：RMAN单表恢复是YashanDB推荐的单表恢复方法，相比传统的导出导入方法，RMAN单表恢复具有更高的性能和更多的功能。建议使用RMAN进行单表恢复。from yashandb视频:www.itpux.com

Part04-生产案例与实战讲解

4.1 YashanDB RMAN单表恢复实战案例

在生产环境中，使用RMAN进行单表恢复：

— 案例：RMAN单表恢复
— 1. 连接到RMAN
RMAN target /

— 2. 模拟表数据丢失
SQL> DELETE FROM fgedu.employees;
SQL> COMMIT;

— 3. 执行单表恢复
RECOVER TABLE fgedu.employees UNTIL TIME ‘2026-04-11:10:00:00’ AUXILIARY DESTINATION ‘/yashandb/auxiliary’;

— 恢复结果
Starting recover at 14-APR-26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=123 device type=DISK
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments:
SYSTEM
UNDOTBS1
Creating automatic instance, with SID=’aux1′

initialization parameters used for automatic instance:
db_name=fgedudb
db_unique_name=aux1
compatible=12.2.0
db_block_size=8192
log_archive_format=%t_%s_%r.dbf
db_files=200

# No auxiliary parameter file used

starting up automatic instance fgedudb

Oracle instance started

Total System Global Area 1610612736 bytes
Fixed Size 2253784 bytes
Variable Size 402653128 bytes
Database Buffers 1191182336 bytes
Redo Buffers 7383040 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until time “to_date(‘2026-04-11:10:00:00′,’yyyy-mm-dd:hh24:mi:ss’)”;
# restore the controlfile
restore controlfile to clone controlfile;
# mount the controlfile
sql “alter database mount clone database”;
# archive current online log
sql “alter system archive log current”;
# restore the database
restore database;
# recover to point in time
recover database;
}
executing Memory Script

executing command: SET until clause

Starting restore at 14-APR-26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=123 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /yashandb/backup/controlfile_20260411_1234567890
channel ORA_DISK_1: piece handle=/yashandb/backup/controlfile_20260411_1234567890 tag=TAG20260411T100500
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output file name=/yashandb/auxiliary/fgedudb/controlfile/o1_mf_%u_.ctl
Finished restore at 14-APR-26

sql statement: alter database mount clone database

sql statement: alter system archive log current

Starting restore at 14-APR-26
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /yashandb/auxiliary/fgedudb/datafile/o1_mf_system_%u_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /yashandb/auxiliary/fgedudb/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /yashandb/auxiliary/fgedudb/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /yashandb/auxiliary/fgedudb/datafile/o1_mf_users_%u_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /yashandb/auxiliary/fgedudb/datafile/o1_mf_fgedutbs_%u_.dbf
channel ORA_DISK_1: reading from backup piece /yashandb/backup/level0_1234567890_2_1
channel ORA_DISK_1: piece handle=/yashandb/backup/level0_1234567890_2_1 tag=TAG20260411T100000
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:00
Finished restore at 14-APR-26

Starting recover at 14-APR-26
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=3
channel ORA_DISK_1: reading from backup piece /yashandb/backup/level0_1234567890_6_1
channel ORA_DISK_1: piece handle=/yashandb/backup/level0_1234567890_6_1 tag=TAG20260411T100000
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
archived log file name=/yashandb/auxiliary/fgedudb/archivelog/1_3_1234567892.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:10
Finished recover at 14-APR-26

contents of Memory Script:
{
# create directory for datapump export
sql “create or replace directory TEMP_DIR as ‘/yashandb/auxiliary'”;
# create directory for datapump import
sql “create or replace directory DATA_PUMP_DIR as ‘/yashandb/auxiliary'”;
# export the table
host ‘expdp userid= directory=TEMP_DIR dumpfile=tabu_1_20260414_123456.dmp logfile=tabu_1_20260414_123456.log tables=fgedu.employees’ ;
# import the table back to the original database
host ‘impdp userid= directory=DATA_PUMP_DIR dumpfile=tabu_1_20260414_123456.dmp logfile=tabi_1_20260414_123456.log tables=fgedu.employees’ ;
}
executing Memory Script

sql statement: create or replace directory TEMP_DIR as ‘/yashandb/auxiliary’

sql statement: create or replace directory DATA_PUMP_DIR as ‘/yashandb/auxiliary’

Export: Release 8.0.0.0.0 – Production on Thu Apr 14 10:00:00 2026

Copyright (c) 2020, 2026, YashanDB. All rights reserved.

Connected to: YashanDB Enterprise Edition Release 8.0.0.0.0 – Production
Starting “SYS”.”SYS_EXPORT_TABLE_01″: /******** DIRECTORY=TEMP_DIR dumpfile=tabu_1_20260414_123456.dmp logfile=tabu_1_20260414_123456.log tables=fgedu.employees
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “FGEDU”.”EMPLOYEES” 5.0 KB 9 rows
Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/yashandb/auxiliary/tabu_1_20260414_123456.dmp
Job “SYS”.”SYS_EXPORT_TABLE_01″ successfully completed at Thu Apr 14 10:02:00 2026 elapsed 0 00:02:00

Import: Release 8.0.0.0.0 – Production on Thu Apr 14 10:03:00 2026

Copyright (c) 2020, 2026, YashanDB. All rights reserved.

Connected to: YashanDB Enterprise Edition Release 8.0.0.0.0 – Production
Master table “SYS”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_TABLE_01″: /******** DIRECTORY=DATA_PUMP_DIR dumpfile=tabu_1_20260414_123456.dmp logfile=tabi_1_20260414_123456.log tables=fgedu.employees
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “FGEDU”.”EMPLOYEES” 5.0 KB 9 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYS”.”SYS_IMPORT_TABLE_01″ successfully completed at Thu Apr 14 10:05:00 2026 elapsed 0 00:02:00

Removing automatic instance
Automatic instance removed
auxiliary instance file /yashandb/auxiliary/fgedudb/datafile/o1_mf_system_%u_.dbf deleted
auxiliary instance file /yashandb/auxiliary/fgedudb/datafile/o1_mf_sysaux_%u_.dbf deleted
auxiliary instance file /yashandb/auxiliary/fgedudb/datafile/o1_mf_undotbs1_%u_.dbf deleted
auxiliary instance file /yashandb/auxiliary/fgedudb/datafile/o1_mf_users_%u_.dbf deleted
auxiliary instance file /yashandb/auxiliary/fgedudb/datafile/o1_mf_fgedutbs_%u_.dbf deleted
auxiliary instance file /yashandb/auxiliary/fgedudb/controlfile/o1_mf_%u_.ctl deleted
auxiliary instance file /yashandb/auxiliary/fgedudb/archivelog/1_3_1234567892.dbf deleted
auxiliary instance file /yashandb/auxiliary/tabu_1_20260414_123456.dmp deleted
auxiliary instance file /yashandb/auxiliary/tabu_1_20260414_123456.log deleted
auxiliary instance file /yashandb/auxiliary/tabi_1_20260414_123456.log deleted

Finished recover at 14-APR-26

— 4. 验证恢复结果
SELECT * FROM fgedu.employees;

EMP_ID EMP_NAME DEPARTMENT SALARY
—— ——– ———- ——
1 张三 技术部 5000
2 李四 销售部 6000
3 王五 技术部 7000
4 赵六 销售部 8000
5 孙七 技术部 9000
6 周八 销售部 10000
7 吴九 技术部 11000
8 郑十 销售部 12000
9 王十一 技术部 13000

4.2 YashanDB数据泵单表恢复实战案例

在生产环境中，使用数据泵进行单表恢复：

— 案例：数据泵单表恢复
— 1. 创建测试表
CREATE TABLE fgedu.departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(100),
manager VARCHAR2(100)
);

INSERT INTO fgedu.departments VALUES (1, ‘技术部’, ‘张经理’);
INSERT INTO fgedu.departments VALUES (2, ‘销售部’, ‘李经理’);
INSERT INTO fgedu.departments VALUES (3, ‘财务部’, ‘王经理’);
COMMIT;

— 2. 导出单个表
EXPDP fgedu/fgedu@fgedudb TABLES=fgedu.departments DIRECTORY=datapump_dir DUMPFILE=departments.dmp LOGFILE=departments_exp.log;

— 导出结果
Export: Release 8.0.0.0.0 – Production on Thu Apr 14 10:10:00 2026

Copyright (c) 2020, 2026, YashanDB. All rights reserved.

Connected to: YashanDB Enterprise Edition Release 8.0.0.0.0 – Production
Starting “FGEDU”.”SYS_EXPORT_TABLE_01″: fgedu/********@fgedudb TABLES=fgedu.departments DIRECTORY=datapump_dir DUMPFILE=departments.dmp LOGFILE=departments_exp.log
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “FGEDU”.”DEPARTMENTS” 5.0 KB 3 rows
Master table “FGEDU”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for FGEDU.SYS_EXPORT_TABLE_01 is:
/yashandb/backup/departments.dmp
Job “FGEDU”.”SYS_EXPORT_TABLE_01″ successfully completed at Thu Apr 14 10:12:00 2026 elapsed 0 00:02:00

— 3. 模拟表数据丢失
DELETE FROM fgedu.departments;
COMMIT;

— 4. 导入单个表
IMPDP fgedu/fgedu@fgedudb TABLES=fgedu.departments DIRECTORY=datapump_dir DUMPFILE=departments.dmp LOGFILE=departments_imp.log TABLE_EXISTS_ACTION=REPLACE;

— 导入结果
Import: Release 8.0.0.0.0 – Production on Thu Apr 14 10:15:00 2026

Copyright (c) 2020, 2026, YashanDB. All rights reserved.

Connected to: YashanDB Enterprise Edition Release 8.0.0.0.0 – Production
Master table “FGEDU”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “FGEDU”.”SYS_IMPORT_TABLE_01″: fgedu/********@fgedudb TABLES=fgedu.departments DIRECTORY=datapump_dir DUMPFILE=departments.dmp LOGFILE=departments_imp.log TABLE_EXISTS_ACTION=REPLACE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “FGEDU”.”DEPARTMENTS” 5.0 KB 3 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “FGEDU”.”SYS_IMPORT_TABLE_01″ successfully completed at Thu Apr 14 10:17:00 2026 elapsed 0 00:02:00

— 5. 验证恢复结果
SELECT * FROM fgedu.departments;

DEPT_ID DEPT_NAME MANAGER
——- ———- ——–
1 技术部 张经理
2 销售部 李经理
3 财务部 王经理

4.3 YashanDB传统单表恢复实战案例

在生产环境中，使用传统导出导入工具进行单表恢复：

— 案例：传统单表恢复
— 1. 导出单个表
EXP fgedu/fgedu@fgedudb TABLES=employees FILE=/yashandb/backup/employees_trad.dmp LOG=/yashandb/backup/employees_trad_exp.log;

— 导出结果
Export: Release 8.0.0.0.0 – Production on Thu Apr 14 10:20:00 2026

Copyright (c) 2020, 2026, YashanDB. All rights reserved.

Connected to: YashanDB Enterprise Edition Release 8.0.0.0.0 – Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path …
. . exporting table EMPLOYEES 9 rows exported
Export terminated successfully without warnings.

— 2. 模拟表数据丢失
DELETE FROM fgedu.employees;
COMMIT;

— 3. 导入单个表
IMP fgedu/fgedu@fgedudb TABLES=employees FILE=/yashandb/backup/employees_trad.dmp LOG=/yashandb/backup/employees_trad_imp.log IGNORE=Y;

— 导入结果
Import: Release 8.0.0.0.0 – Production on Thu Apr 14 10:25:00 2026

Copyright (c) 2020, 2026, YashanDB. All rights reserved.

Connected to: YashanDB Enterprise Edition Release 8.0.0.0.0 – Production
Import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

Import terminated successfully without warnings.

— 4. 验证恢复结果
SELECT * FROM fgedu.employees;

EMP_ID EMP_NAME DEPARTMENT SALARY
—— ——– ———- ——
1 张三 技术部 5000
2 李四 销售部 6000
3 王五 技术部 7000
4 赵六 销售部 8000
5 孙七 技术部 9000
6 周八 销售部 10000
7 吴九 技术部 11000
8 郑十 销售部 12000
9 王十一 技术部 13000

4.4 YashanDB单表时间点恢复实战案例

在生产环境中，使用RMAN进行单表时间点恢复：

— 案例：单表时间点恢复
— 1. 记录当前时间
SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
——————————
2026-04-14 10:30:00.000000 +08:00

— 2. 插入新数据
INSERT INTO fgedu.employees VALUES (10, ‘钱十二’, ‘销售部’, 14000);
INSERT INTO fgedu.employees VALUES (11, ‘孙十三’, ‘技术部’, 15000);
COMMIT;

— 3. 连接到RMAN
RMAN target /

— 4. 执行单表时间点恢复
RECOVER TABLE fgedu.employees UNTIL TIME ‘2026-04-14:10:30:00’ AUXILIARY DESTINATION ‘/yashandb/auxiliary’;

— 恢复结果
Starting recover at 14-APR-26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=123 device type=DISK
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments:
SYSTEM
UNDOTBS1
Creating automatic instance, with SID=’aux1′

initialization parameters used for automatic instance:
db_name=fgedudb
db_unique_name=aux1
compatible=12.2.0
db_block_size=8192
log_archive_format=%t_%s_%r.dbf
db_files=200

# No auxiliary parameter file used

starting up automatic instance fgedudb

Oracle instance started

Total System Global Area 1610612736 bytes
Fixed Size 2253784 bytes
Variable Size 402653128 bytes
Database Buffers 1191182336 bytes
Redo Buffers 7383040 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until time “to_date(‘2026-04-14:10:30:00′,’yyyy-mm-dd:hh24:mi:ss’)”;
# restore the controlfile
restore controlfile to clone controlfile;
# mount the controlfile
sql “alter database mount clone database”;
# archive current online log
sql “alter system archive log current”;
# restore the database
restore database;
# recover to point in time
recover database;
}
executing Memory Script

executing command: SET until clause

Starting restore at 14-APR-26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=123 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /yashandb/backup/controlfile_20260413_1234567898
channel ORA_DISK_1: piece handle=/yashandb/backup/controlfile_20260413_1234567898 tag=TAG20260413T100500
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output file name=/yashandb/auxiliary/fgedudb/controlfile/o1_mf_%u_.ctl
Finished restore at 14-APR-26

sql statement: alter database mount clone database

sql statement: alter system archive log current

Starting restore at 14-APR-26
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /yashandb/auxiliary/fgedudb/datafile/o1_mf_system_%u_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /yashandb/auxiliary/fgedudb/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /yashandb/auxiliary/fgedudb/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /yashandb/auxiliary/fgedudb/datafile/o1_mf_users_%u_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /yashandb/auxiliary/fgedudb/datafile/o1_mf_fgedutbs_%u_.dbf
channel ORA_DISK_1: reading from backup piece /yashandb/backup/cumulative_level1_1234567898_2_1
channel ORA_DISK_1: piece handle=/yashandb/backup/cumulative_level1_1234567898_2_1 tag=TAG20260413T100000
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:00
Finished restore at 14-APR-26

Starting recover at 14-APR-26
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece /yashandb/backup/cumulative_level1_1234567898_3_1
channel ORA_DISK_1: piece handle=/yashandb/backup/cumulative_level1_1234567898_3_1 tag=TAG20260413T100000
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
archived log file name=/yashandb/auxiliary/fgedudb/archivelog/1_7_1234567896.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:00:10
Finished recover at 14-APR-26

contents of Memory Script:
{
# create directory for datapump export
sql “create or replace directory TEMP_DIR as ‘/yashandb/auxiliary'”;
# create directory for datapump import
sql “create or replace directory DATA_PUMP_DIR as ‘/yashandb/auxiliary'”;
# export the table
host ‘expdp userid= directory=TEMP_DIR dumpfile=tabu_2_20260414_123456.dmp logfile=tabu_2_20260414_123456.log tables=fgedu.employees’ ;
# import the table back to the original database
host ‘impdp userid= directory=DATA_PUMP_DIR dumpfile=tabu_2_20260414_123456.dmp logfile=tabi_2_20260414_123456.log tables=fgedu.employees’ ;
}
executing Memory Script

sql statement: create or replace directory TEMP_DIR as ‘/yashandb/auxiliary’

sql statement: create or replace directory DATA_PUMP_DIR as ‘/yashandb/auxiliary’

Export: Release 8.0.0.0.0 – Production on Thu Apr 14 10:35:00 2026

Copyright (c) 2020, 2026, YashanDB. All rights reserved.

Connected to: YashanDB Enterprise Edition Release 8.0.0.0.0 – Production
Starting “SYS”.”SYS_EXPORT_TABLE_01″: /******** DIRECTORY=TEMP_DIR dumpfile=tabu_2_20260414_123456.dmp logfile=tabu_2_20260414_123456.log tables=fgedu.employees
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “FGEDU”.”EMPLOYEES” 5.0 KB 9 rows
Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/yashandb/auxiliary/tabu_2_20260414_123456.dmp
Job “SYS”.”SYS_EXPORT_TABLE_01″ successfully completed at Thu Apr 14 10:37:00 2026 elapsed 0 00:02:00

Import: Release 8.0.0.0.0 – Production on Thu Apr 14 10:38:00 2026

Copyright (c) 2020, 2026, YashanDB. All rights reserved.

Connected to: YashanDB Enterprise Edition Release 8.0.0.0.0 – Production
Master table “SYS”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_TABLE_01″: /******** DIRECTORY=DATA_PUMP_DIR dumpfile=tabu_2_20260414_123456.dmp logfile=tabi_2_20260414_123456.log tables=fgedu.employees
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “FGEDU”.”EMPLOYEES” 5.0 KB 9 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYS”.”SYS_IMPORT_TABLE_01″ successfully completed at Thu Apr 14 10:40:00 2026 elapsed 0 00:02:00

Removing automatic instance
Automatic instance removed
auxiliary instance file /yashandb/auxiliary/fgedudb/datafile/o1_mf_system_%u_.dbf deleted
auxiliary instance file /yashandb/auxiliary/fgedudb/datafile/o1_mf_sysaux_%u_.dbf deleted
auxiliary instance file /yashandb/auxiliary/fgedudb/datafile/o1_mf_undotbs1_%u_.dbf deleted
auxiliary instance file /yashandb/auxiliary/fgedudb/datafile/o1_mf_users_%u_.dbf deleted
auxiliary instance file /yashandb/auxiliary/fgedudb/datafile/o1_mf_fgedutbs_%u_.dbf deleted
auxiliary instance file /yashandb/auxiliary/fgedudb/controlfile/o1_mf_%u_.ctl deleted
auxiliary instance file /yashandb/auxiliary/fgedudb/archivelog/1_7_1234567896.dbf deleted
auxiliary instance file /yashandb/auxiliary/tabu_2_20260414_123456.dmp deleted
auxiliary instance file /yashandb/auxiliary/tabu_2_20260414_123456.log deleted
auxiliary instance file /yashandb/auxiliary/tabi_2_20260414_123456.log deleted

Finished recover at 14-APR-26

— 5. 验证恢复结果
SELECT * FROM fgedu.employees;

EMP_ID EMP_NAME DEPARTMENT SALARY
—— ——– ———- ——
1 张三 技术部 5000
2 李四 销售部 6000
3 王五 技术部 7000
4 赵六 销售部 8000
5 孙七 技术部 9000
6 周八 销售部 10000
7 吴九 技术部 11000
8 郑十 销售部 12000
9 王十一 技术部 13000

生产环境建议：在生产环境中，单表恢复是数据库恢复的重要组成部分。建议掌握单表恢复的方法，以便在表级数据丢失时能够快速恢复。更多视频教程www.fgedu.net.cn

Part05-风哥经验总结与分享

5.1 YashanDB单表恢复使用经验

YashanDB单表恢复使用经验总结：

  • 选择合适的恢复方法：根据实际情况选择合适的单表恢复方法
  • 定期备份：定期进行全库备份和增量备份，确保有可用的备份文件
  • 测试验证：定期测试单表恢复过程，确保恢复方法有效
  • 文档记录：记录单表恢复的步骤和方法，便于应急使用
  • 权限管理：确保恢复操作有足够的权限
  • 监控告警：监控表级别的数据变化和异常，及时发现问题
  • 恢复验证：恢复后验证表数据的完整性和一致性

5.2 YashanDB单表恢复常见问题

# 常见问题及解决方法

## 1. 单表恢复失败
– 症状：单表恢复过程中出现错误
– 原因：备份文件不存在，权限不足，表结构不匹配，空间不足
– 解决：确保备份文件存在，确保用户有足够的权限，确保表结构匹配，确保有足够的空间

## 2. 恢复后表数据不一致
– 症状：恢复后表数据与预期不一致
– 原因：恢复时间点选择错误，备份文件损坏，恢复过程中断
– 解决：选择正确的恢复时间点，确保备份文件完整，确保恢复过程不中断

## 3. 恢复后表约束丢失
– 症状：恢复后表约束丢失
– 原因：备份文件不包含约束信息，恢复过程中约束未被恢复
– 解决：确保备份文件包含约束信息，使用包含约束的恢复方法

## 4. 恢复后表索引丢失
– 症状：恢复后表索引丢失
– 原因：备份文件不包含索引信息，恢复过程中索引未被恢复
– 解决：确保备份文件包含索引信息，使用包含索引的恢复方法

## 5. 恢复时间过长
– 症状：单表恢复时间过长
– 原因：表数据量大，系统资源不足，I/O性能差
– 解决：优化系统资源，提高I/O性能，在系统负载低时进行恢复

5.3 YashanDB单表恢复使用建议

YashanDB单表恢复使用建议：

  • 制定恢复策略：根据业务需求制定合理的单表恢复策略
  • 定期备份：定期进行全库备份和增量备份，确保有可用的备份文件
  • 测试验证：定期测试单表恢复过程，确保恢复方法有效
  • 文档记录：记录单表恢复的步骤和方法，便于应急使用
  • 权限管理：确保恢复操作有足够的权限
  • 监控告警：监控表级别的数据变化和异常，及时发现问题
  • 恢复验证：恢复后验证表数据的完整性和一致性
  • 应急响应：建立单表恢复的应急响应流程，确保在数据丢失时能够快速响应
  • 持续改进：定期review单表恢复策略，优化恢复过程
风哥提示：单表恢复是数据库恢复的重要组成部分。建议掌握单表恢复的方法，以便在表级数据丢失时能够快速恢复。学习交流加群风哥微信: itpux-com

持续改进：单表恢复的策略和方法需要根据实际情况不断调整和改进。建议定期review单表恢复策略，优化恢复过程，以确保在表级数据丢失时能够快速恢复。更多学习教程公众号风哥教程itpux_com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息