使用 recover table 从备份中恢复单个表或者多个表

原创已于 2025-09-12 15:01:39 修改 · 293 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #java #服务器

于 2025-07-14 17:19:26 首次发布

从某一次的RMAN备份中恢复某个表：直接从备份中提取表
RMAN> recover table scott.emp until time '2025-08-16 08：55：58'
auxiliary destination '/res/'
datapump destination '/res/'
dump file 'emp.dmp'
notableimport;

PDB环境：
RMAN> RECOVER TABLE hyj.students OF PLUGGABLE DATABASE orclpdb UNTIL TIME "to_date('2022-01-25 17:07:00','yyyy-mm-dd hh24:mi:ss')"
AUXILIARY DESTINATION '/backup/tablereovery/'
REMAP TABLE 'HYJ'.'STUDENTS':'STUDENTS_RESTORE';

RMAN恢复分区表：
RMAN> RECOVER TABLE hyj.students_p:p2 OF PLUGGABLE DATABASE orclpdb
UNTIL TIME "to_date('2022-01-25 17:07:00','yyyy-mm-dd hh24:mi:ss')"
AUXILIARY DESTINATION '/backup/tablereovery/'
DATAPUMP DESTINATION '/backup/dumpfile/'
REMAP TABLE 'HYJ'.'STUDENTS_P':'P2':'P2_RESTORE'
DUMP FILE 'students_p_p2.dmp' ;

RMAN>RECOVER TABLE username.tablename UNTIL TIME 'TIMESTAMP…'
AUXILIARY DESTINATION '/u01/tablerecovery'
DATAPUMP DESTINATION '/u01/dumpfile'
DUMP FILE 'tablename.dmp'
NOTABLEIMPORT -- 此选项避免自动导入表
REMAP TABLE 'username.tablename': 'username.new_table_name'; --此选项可以对表重命名

恢复多张表到多个用户多个表空间：
RECOVER TABLE owner1.table1,owner1.table2 OF PLUGGABLE DATABASE HR_PDB
UNTIL TIME 'SYSDATE-4'
AUXILIARY DESTINATION '/tmp/backups/'
REMAP TABLE owner1.table1:owner2.table1_1, owner1.table2:owner2.table2_1 --重命名多张表，使用','分开
REMAP TABLESPACE 'tablespace1':'tablespace2'; --更改表空间