一、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 详细语法使用请点此查看✔✔✔。
好文链接
您阅读本篇文章共花了：
发表评论