在CentOS 6.4下安装Oracle 11gR2(x64)
--------------------------------------------------------------------------------
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