RMAN恢复某张表的方法

一、Oracle 11g有备份的情况下，恢复某张表

环境模拟：删除 test 用户下 t1 表中的数据。

1.查看表的相关信息

ACCEPT SCHEMA PROMPT 'Table Owner: '
ACCEPT TABNAME PROMPT 'Table Name: '

set verify off
set feedback off
set lines 100
set pages 999
with temp as  (
select tablespace_name from dba_tables where owner=UPPER('&SCHEMA') and table_name=UPPER('&TABNAME') union all
select tablespace_name from dba_TAB_PARTITIONS where TABLE_OWNER=UPPER('&SCHEMA') and table_name=UPPER('&TABNAME') union all
select tablespace_name from dba_indexes where owner=UPPER('&SCHEMA') and table_name=UPPER('&TABNAME') union all
select tablespace_name from dba_lobs where owner=UPPER('&SCHEMA') and table_name=UPPER('&TABNAME'))
select distinct tablespace_name "tbs info " from temp union all
select ' ' as "tbs info" from dual union all
select 'file_id   |   file_name          |     tablespace' as "tbs info" from dual union all
select '------------------------------------------------------------' as "tbs info" from dual union all
select ''||file_id||'   '||file_name||'   '||tablespace_name||''  "tbs_info " from dba_data_files where
tablespace_name in (select distinct tablespace_name "tbs info " from temp);

prompt            
prompt          
prompt '2.table size'
SELECT
 (SELECT SUM(S.BYTES/1024/1024) 
  FROM DBA_SEGMENTS S
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (S.SEGMENT_NAME = UPPER('&TABNAME'))) +
 (SELECT nvl(SUM(S.BYTES/1024/1024),0)                                                                                           
  FROM DBA_SEGMENTS S, DBA_LOBS L
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) +
 (SELECT  nvl(SUM(S.BYTES/1024/1024),0)                                                                                       
  FROM DBA_SEGMENTS S, DBA_INDEXES I
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA')))
  "TOTAL TABLE SIZE(MB)"
FROM DUAL;

prompt
prompt
prompt '3.table info'
col object_name for a20
col object_type for a20
col status for a10
col created for a15
select object_name,object_type,status,created from dba_objects where owner=UPPER('&SCHEMA') and object_name=UPPER('&TABNAME');

prompt
prompt
prompt '4.columns info'
col column_name for a20
col data_type for a20
select column_name,data_type from dba_tab_columns where owner=UPPER('&SCHEMA') and table_name=UPPER('&TABNAME');

2.恢复相关的表空间

2.1.恢复参数文件，编辑修改

RMAN> restore spfile to pfile '/CS_ORCL/pfile.txt' from '/CS_ORCL/orcl_1n03p1v4_1_1';

2.2.创建所需路径，恢复控制文件

mkdir -p /oradata/orcl
restore controlfile from '/CS_ORCL/orcl_1n03p1v4_1_1';

2.3.启动到mount，恢复表空间

alter database mount;
catalog start with '/CS_ORCL/';

run {
allocate channel d1 type disk;
allocate channel d2 type disk;
set newname for datafile 1 to '/CS_ORCL/system01.dbf';
set newname for datafile 2 to '/CS_ORCL/sysaux01.dbf';
set newname for datafile 3 to '/CS_ORCL/undotbs01.dbf';
set newname for datafile 4 to '/CS_ORCL/users01.dbf';
set newname for datafile 7 to '/CS_ORCL/test_c.dbf';
restore tablespace SYSTEM,SYSAUX,USERS,UNDOTBS1,TEST_C;
switch datafile all;
release channel d1;
release channel d2;
}

3.需要使用 recover database skip tablespace 的方式跳过不必要的表空间进行恢复，并打开数据库

SQL> select name from v$tablespace where name not in ('SYSTEM','SYSAUX','USERS','UNDOTBS1','TEST_C');

RMAN> recover database skip tablespace P1,TEST_B,AUDIT_TBS,P2,BIG1,BIG2,TEST,FQCS1,FQCS2,TEMP;

SQL> alter database open resetlogs;

4.导出t1表

expdp sys/1 directory=dir2 dumpfile=t1.dmp tables=test.t1 logfile=e1.log

5.导入到正式环境

impdp sys/1 directory=dir dumpfile=t1.dmp table_exists_action=append logfile=i1.log

至此表恢复工作完成。

二、Oracle 12c有备份的情况下，恢复某张表

环境模拟：删除 orclpdb 中 test 用户下 t2 表中的数据。

恢复表的限制：
（1）SYS用户表或分区无法恢复。
（2）存储于SYSAUX和SYSTEM表空间下的表和分区无法恢复。
（3）当REMAP选项用来恢复的表包含NOT NULL约束时，恢复此表是不可行的。
（4）在备库上的表和分区表不能恢复。

恢复表的方法：
（2）SCN
（3）时间戳
（4）Sequence number（日志序列号）

1.恢复被删除的表

recover table TEST.T2 of pluggable database orclpdb until time "to_date('2021-07-15 17:33:00','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/u01/recover_table' datapump destination '/home/oracle/impdp' dump file 'test.t2.dmp' notableimport;

2.导入表

[oracle@12c impdp]$ ll
-rw-r----- 1 oracle oinstall 188416 Jul 16 10:25 test.t2.dmp

创建导入目录，并授权：
create directory impdp as '/home/oracle/expdp/';
grant read,write on directory impdp to test;
impdp test/admin@orclpdb directory=impdp dumpfile=test.t2.dmp table_exists_action=append logfile=t2.log

3.recover用法补充

--将恢复的表重命名
recover table TEST.T2 of pluggable database orclpdb until scn 1664487 auxiliary destination '/u01/recover_table' remap table TEST.T2:TEST.T2BAK;

recover 详细语法使用请点此查看✔✔✔🐇。