RMAN基于表空间的不完全恢复（TSPITR)-mysql教程

RMAN基于表空间的不完全恢复（TSPITR)表空间时间点恢复(TSPITR)：概念通过执行TSPITR可将一个或多个表空间快速恢复到以前的某个时间。执行TSPITR不会影响数据库

RMAN基于表空间的不完全恢复（TSPITR)

RMAN自动执行TSPITR案例：

1、工作环境

12:21:32 SCOTT@ prod>create table emp1 as select * from emp; Table created. Elapsed: 00:00:00.65 14:07:41 SCOTT@ prod>insert into emp1 select * from emp where rownum <5; 4 rows created. Elapsed: 00:00:00.02 14:08:18 SCOTT@ prod>commit; Commit complete. Elapsed: 00:00:00.04 14:08:19 SCOTT@ prod>select count(*) from emp1; COUNT(*) ---------- 18 Elapsed: 00:00:00.02 emp1表数据被误删除： 14:08:20 SCOTT@ prod>truncate table emp1; Table truncated. Elapsed: 00:00:00.32 14:08:30 SCOTT@ prod>insert into emp1 select * from emp where empno=7788; 1 row created. Elapsed: 00:00:00.03 14:08:46 SCOTT@ prod>commit; Commit complete. Elapsed: 00:00:00.06 14:08:48 SCOTT@ prod>select * from emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 Elapsed: 00:00:00.08 14:08:55 SCOTT@ prod>update emp1 set empno=8888; 1 row updated. Elapsed: 00:00:00.02 14:09:06 SCOTT@ prod>commit; Commit complete. Elapsed: 00:00:00.01 14:09:08 SCOTT@ prod>select * from emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 8888 SCOTT ANALYST 7566 19-APR-87 3000 20 Elapsed: 00:00:00.01 14:09:12 SCOTT@ prod>

2、做恢复前的检测

检测tablespace是否自包含： 10:39:16 SCOTT@ prod>CONN /as sysdba Connected. 10:39:25 SYS@ prod>exec DBMS_TTS.TRANSPORT_SET_CHECK('TBS1',TRUE); PL/SQL procedure successfully completed. Elapsed: 00:01:58.45 10:41:43 SYS@ prod>select * from TRANSPORT_SET_VIOLATIONS t; no rows selected 确认所要恢复的数据文件： 14:13:34 SYS@ prod>select * from dba_data_files t where T.TABLESPACE_NAME='TBS1'; FILE_NAME ------------------------------------------------------------------------------------------------------------------------ FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_ ------------ ---------- ----------- ------- /u01/app/oracle/oradata/prod/tbs1.dbf 6 TBS1 10485760 1280 AVAILABLE 6 NO 0 0 0 9437184 1152 ONLINE Elapsed: 00:00:00.02 14:14:42 SYS@ prod> 检查是否包含辅助集SYSTEM UNDO和CONTROLFILE： 14:14:38 SYS@ prod>select file_name name from dba_data_files t where T.TABLESPACE_NAME IN('SYSTEM','UNDOTBS1') 14:14:40 2 union 14:14:41 3 select max(name) from v$controlfile t; NAME ------------------------------------------------------------------------------------------- /u01/app/oracle/oradata/prod/control01.ctl /u01/app/oracle/oradata/prod/system01.dbf /u01/app/oracle/oradata/prod/undotbs01.dbf Elapsed: 00:00:00.03 检查TSP之后可能丢失的对象： 14:14:42 SYS@ prod>select * from TS_PITR_OBJECTS_TO_BE_DROPPED T where T.OWNER='TBS1'; no rows selected Elapsed: 00:00:00.17

3、执行基于RMAN的TSPITR

[root@rh6 prod]# mkdir /home/oracle/prod