RMAN不完全恢复实验

实验前准备
1 删除备份和归档
oracle@TestDB:/home/oracle> rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Oct 30 00:42:12 2017

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: TESTDB12 (DBID=2811829300)

RMAN> delete noprompt backup;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name

---

5 5 1 1 AVAILABLE DISK /home/oracle/rman/TESTDB12_5_1_1030.bak
6 6 1 1 AVAILABLE DISK /home/oracle/rman/TESTDB12_6_1_1030.bak
7 7 1 1 AVAILABLE DISK /home/oracle/rman/control.bak
8 8 1 1 AVAILABLE DISK /home/oracle/rman/arch.bak
deleted backup piece
backup piece handle=/home/oracle/rman/TESTDB12_5_1_1030.bak RECID=5 STAMP=958690856
deleted backup piece
backup piece handle=/home/oracle/rman/TESTDB12_6_1_1030.bak RECID=6 STAMP=958691075
deleted backup piece
backup piece handle=/home/oracle/rman/control.bak RECID=7 STAMP=958691246
deleted backup piece
backup piece handle=/home/oracle/rman/arch.bak RECID=8 STAMP=958691305
Deleted 4 objects

RMAN> delete noprompt copy;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
specification does not match any datafile copy in the repository
List of Control File Copies

Key S Completion Time Ckp SCN Ckp Time

---

2 A 27-OCT-17 1187790 27-OCT-17
Name: /home/oracle/control.bk
Tag: TAG20171027T022711

List of Archived Log Copies for database with db_unique_name TESTDB12

Key Thrd Seq S Low Time

---

6 1 13 A 27-OCT-17
Name: /u01/app/oracle/rman/1_13_820054583.dbf

7 1 14 A 27-OCT-17
Name: /u01/app/oracle/rman/1_14_820054583.dbf

8 1 15 A 27-OCT-17
Name: /u01/app/oracle/rman/1_15_820054583.dbf

9 1 1 A 27-OCT-17
Name: /u01/app/oracle/rman/1_1_958447651.dbf

10 1 2 A 27-OCT-17
Name: /u01/app/oracle/rman/1_2_958447651.dbf

11 1 3 A 27-OCT-17
Name: /u01/app/oracle/rman/1_3_958447651.dbf

deleted control file copy
control file copy file name=/home/oracle/control.bk RECID=2 STAMP=958444031
deleted archived log
archived log file name=/u01/app/oracle/rman/1_13_820054583.dbf RECID=6 STAMP=958447651
deleted archived log
archived log file name=/u01/app/oracle/rman/1_14_820054583.dbf RECID=7 STAMP=958447651
deleted archived log
archived log file name=/u01/app/oracle/rman/1_15_820054583.dbf RECID=8 STAMP=958447653
deleted archived log
archived log file name=/u01/app/oracle/rman/1_1_958447651.dbf RECID=9 STAMP=958448584
deleted archived log
archived log file name=/u01/app/oracle/rman/1_2_958447651.dbf RECID=10 STAMP=958449369
deleted archived log
archived log file name=/u01/app/oracle/rman/1_3_958447651.dbf RECID=11 STAMP=958691304
Deleted 7 objects

2 创建测试表
idle>conn test/oracle
Connected.
test@TESTDB12>create table test1 tablespace test as select * from scott.emp;

Table created.

test@TESTDB12>create table test2 tablespace test as select * from scott.emp;

Table created.

test@TESTDB12>create table test3 tablespace test as select * from scott.emp;

Table created.

test@TESTDB12>create table test4 tablespace test as select * from scott.emp;

Table created.

test@TESTDB12>
3 开闪回
sys@TESTDB12>alter system set db_recovery_file_dest_size=4G scope=spfile;

System altered.

sys@TESTDB12>alter system set db_recovery_file_dest=‘/home/oracle/flash’ scope=spfile;

System altered.

sys@TESTDB12>alter system set recyclebin=on scope=spfile;

System altered.

sys@TESTDB12>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TESTDB12>startup mount;
ORACLE instance started.

Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 528485720 bytes
Database Buffers 301989888 bytes
Redo Buffers 6574080 bytes
Database mounted.
sys@TESTDB12>alter database flashback on; --开启闪回 这里已经开启
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38713: Flashback Database logging is already turned on.

sys@TESTDB12>alter database open;

Database altered.

sys@TESTDB12>select name ,flashback_on from v$database;

NAME FLASHBACK_ON

---

TESTDB12 YES

4 备份数据库
oracle@TestDB:/home/oracle> rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Oct 30 00:56:16 2017

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: TESTDB12 (DBID=2811829300)

RMAN> backup database format ‘/home/oracle/rman/%d_%s_%p_hot.bak’; --备份数据文件

Starting backup at 30-OCT-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/TestDB12/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/TestDB12/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/TestDB12/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/TestDB12/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/TestDB12/test1.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/TestDB12/users01.dbf
channel ORA_DISK_1: starting piece 1 at 30-OCT-17
channel ORA_DISK_1: finished piece 1 at 30-OCT-17
piece handle=/home/oracle/rman/TESTDB12_10_1_hot.bak tag=TAG20171030T005619 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 30-OCT-17
channel ORA_DISK_1: finished piece 1 at 30-OCT-17
piece handle=/home/oracle/rman/TESTDB12_11_1_hot.bak tag=TAG20171030T005619 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-OCT-17

RMAN> backup current controlfile format ‘/home/oracle/rman/control.bak’; --备份控制文件

Starting backup at 30-OCT-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 30-OCT-17
channel ORA_DISK_1: finished piece 1 at 30-OCT-17
piece handle=/home/oracle/rman/control.bak tag=TAG20171030T005910 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-OCT-17

RMAN> backup archivelog all format ‘/home/oracle/arch.bak’; --备份归档

Starting backup at 30-OCT-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=12 STAMP=958697991
channel ORA_DISK_1: starting piece 1 at 30-OCT-17
channel ORA_DISK_1: finished piece 1 at 30-OCT-17
piece handle=/home/oracle/arch.bak tag=TAG20171030T005951 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-OCT-17

RMAN>

5 激活log miner

SQL> alter database add supplemental log data;

实验1 基于时间点的不完全恢复

oracle@TestDB:/home/oracle> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 30 01:01:28 2017

Copyright © 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sys@TESTDB12>conn test/oracle
Connected.
test@TESTDB12>delete from test1;

14 rows deleted.

test@TESTDB12>commit; --误操作并提交

Commit complete.

test@TESTDB12>insert into test1 select * from scott.emp where rownum<=3;

3 rows created.

test@TESTDB12>commit; --误操作后插入了数据

Commit complete.

test@TESTDB12>select count(*) from test1;

COUNT(*)

     3

test@TESTDB12>

sys@TESTDB12>set line 300;
sys@TESTDB12>select * from v$log; --查看日志当前日志为2

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

---

     1          1          4   52428800        512          1 YES ACTIVE                 1222025 29-OCT-17      1235864 30-OCT-17
     2          1          5   52428800        512          1 NO  CURRENT                1235864 30-OCT-17   2.8147E+14
     3          1          3   52428800        512          1 YES INACTIVE               1211254 27-OCT-17      1222025 29-OCT-17

sys@TESTDB12>col member for a70;
sys@TESTDB12>select * from v$logfile;

GROUP# STATUS  TYPE    MEMBER                                                                 IS_

---

     3         ONLINE  /u01/app/oracle/oradata/TestDB12/redo03.log                            NO
     2         ONLINE  /u01/app/oracle/oradata/TestDB12/redo02.log                            NO
     1         ONLINE  /u01/app/oracle/oradata/TestDB12/redo01.log                            NO

sys@TESTDB12>col name for a70
sys@TESTDB12>select name from v$archived_log; --当前归档为1_4_958447651.dbf

NAME

/u01/app/oracle/oradata/TestDB12/redo01.log
/u01/app/oracle/oradata/TestDB12/redo02.log
/u01/app/oracle/oradata/TestDB12/redo03.log

/u01/app/oracle/rman/1_4_958447651.dbf

12 rows selected.

sys@TESTDB12>

---

编写脚本
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => ‘/u01/app/oracle/oradata/TestDB12/redo02.log’, -
OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => ‘/u01/app/oracle/rman/1_4_958447651.dbf’, -
OPTIONS => DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -
DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

EXECUTE DBMS_LOGMNR.END_LOGMNR;

sys@TESTDB12>col username for a10
sys@TESTDB12>
sys@TESTDB12>col sql_redo for a50
sys@TESTDB12>select username,scn,timestamp,sql_redo from v$logmn{r}_{c}ontentswherese{g}_{n}ame{=}^{\prime }test{1}^{\prime }orderbyscn;selectusername,scn,timestamp,sq{l}_{r}edofromv$logmnr_contents where seg_name=‘test1’ order by scn
*
ERROR at line 1:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents

sys@TESTDB12>EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

LOGFILENAME => ‘/u01/app/oracle/oradata/TestDB12/redo02.log’, -
OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed. --添加当前日志文件

sys@TESTDB12>EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

LOGFILENAME => ‘/u01/app/oracle/rman/1_4_958447651.dbf’, -
OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed. --添加最后一次归档日志

sys@TESTDB12>EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -

DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed. --开启日志挖掘

sys@TESTDB12>select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name=‘test1’ order by scn;

no rows selected

sys@TESTDB12>select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name=‘TEST1’ order by scn;

USERNAME SCN TIMESTAMP SQL_REDO

---

TEST 1223964 2017-10-29 23:29:56 truncate table test1;
TEST 1224073 2017-10-29 23:30:32 ALTER TABLE “TEST”.“TEST1” RENAME TO “BIN$XLxCyWpL
ZNPgUwEAAH8kaw==$0” ;

TEST 1224076 2017-10-29 23:30:33 drop table test1 AS “BIN$XLxCyWpLZNPgUwEAAH8kaw==$
0” ;

TEST 1224194 2017-10-29 23:31:49 create table test1 tablespace test as select * fro
m scott.emp;

SYS 1224776 2017-10-29 23:35:42 ALTER TABLE “TEST”.“TEST1” RENAME TO “BIN$XLxVPFdW
ZSvgUwEAAH+b1g==$0” ;

SYS 1224778 2017-10-29 23:35:42 drop table test.test1 AS “BIN$XLxVPFdWZSvgUwEAAH+b
1g==$0” ;

TEST 1224809 2017-10-29 23:36:04 create table test1 tablespace test as select * fro
m scott.emp;

SYS 1234481 2017-10-30 00:45:51 ALTER TABLE “TEST”.“TEST1” RENAME TO “BIN$XL1QJUgd
aDfgUwEAAH90tw==$0” ;

SYS 1234484 2017-10-30 00:45:51 drop table test.test1 AS “BIN$XL1QJUgdaDfgUwEAAH90
tw==$0” ;

TEST 1234705 2017-10-30 00:49:17 create table test1 tablespace test as select * fro
m scott.emp;

TEST 1234716 2017-10-30 00:49:17 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘78
44’,‘TURNER’,‘SALESMAN’,‘7698’,TO_DATE(‘1981-09-08
00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘1500’,‘0’,‘3
0’);

TEST 1234716 2017-10-30 00:49:17 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘78
39’,‘KING’,‘PRESIDENT’,NULL,TO_DATE(‘1981-11-17 00
:00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘5000’,NULL,‘10’
);

TEST 1234716 2017-10-30 00:49:17 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘77
88’,‘SCOTT’,‘ANALYST’,‘7566’,TO_DATE(‘1987-04-19 0
0:00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘3000’,NULL,'20
');

TEST 1234716 2017-10-30 00:49:17 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘77
82’,‘CLARK’,‘MANAGER’,‘7839’,TO_DATE(‘1981-06-09 0
0:00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘2450’,NULL,'10
');

TEST 1234716 2017-10-30 00:49:17 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘76
98’,‘BLAKE’,‘MANAGER’,‘7839’,TO_DATE(‘1981-05-01 0
0:00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘2850’,NULL,'30
');

TEST 1234716 2017-10-30 00:49:17 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘76
54’,‘MARTIN’,‘SALESMAN’,‘7698’,TO_DATE(‘1981-09-28
00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘1250’,‘1400’
,‘30’);

TEST 1234716 2017-10-30 00:49:17 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘75
66’,‘JONES’,‘MANAGER’,‘7839’,TO_DATE(‘1981-04-02 0
0:00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘2975’,NULL,'20
');

TEST 1234716 2017-10-30 00:49:17 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘75
21’,‘WARD’,‘SALESMAN’,‘7698’,TO_DATE(‘1981-02-22 0
0:00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘1250’,‘500’,‘3
0’);

TEST 1234716 2017-10-30 00:49:17 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘74
99’,‘ALLEN’,‘SALESMAN’,‘7698’,TO_DATE(‘1981-02-20
00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘1600’,‘300’,’
30’);

TEST 1234716 2017-10-30 00:49:17 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘73
69’,‘SMITH’,‘CLERK’,‘7902’,TO_DATE(‘1980-12-17 00:
00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘800’,NULL,‘20’);

TEST 1234716 2017-10-30 00:49:17 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘78
76’,‘ADAMS’,‘CLERK’,‘7788’,TO_DATE(‘1987-05-23 00:
00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘1100’,NULL,‘20’)
;

TEST 1234716 2017-10-30 00:49:17 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘79
34’,‘MILLER’,‘CLERK’,‘7782’,TO_DATE(‘1982-01-23 00
:00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘1300’,NULL,‘10’

USERNAME SCN TIMESTAMP SQL_REDO

---

                                      );

TEST 1234716 2017-10-30 00:49:17 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘79
02’,‘FORD’,‘ANALYST’,‘7566’,TO_DATE(‘1981-12-03 00
:00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘3000’,NULL,‘20’
);

TEST 1234716 2017-10-30 00:49:17 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘79
00’,‘JAMES’,‘CLERK’,‘7698’,TO_DATE(‘1981-12-03 00:
00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘950’,NULL,‘30’);

TEST 1236025 2017-10-30 01:01:57 delete from “TEST”.“TEST1” where “EMPNO” = ‘7369’
and “ENAME” = ‘SMITH’ and “JOB” = ‘CLERK’ and "MGR
" = ‘7902’ and “HIREDATE” = TO_DATE(‘1980-12-17 00
:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “SAL” = '800
’ and “COMM” IS NULL and “DEPTNO” = ‘20’ and ROWID
= ‘AAASxmAAGAAAAF7AAA’;

TEST 1236025 2017-10-30 01:01:57 delete from “TEST”.“TEST1” where “EMPNO” = ‘7499’
and “ENAME” = ‘ALLEN’ and “JOB” = ‘SALESMAN’ and "
MGR" = ‘7698’ and “HIREDATE” = TO_DATE(‘1981-02-20
00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “SAL” = ’
1600’ and “COMM” = ‘300’ and “DEPTNO” = ‘30’ and R
OWID = ‘AAASxmAAGAAAAF7AAB’;

TEST 1236025 2017-10-30 01:01:57 delete from “TEST”.“TEST1” where “EMPNO” = ‘7934’
and “ENAME” = ‘MILLER’ and “JOB” = ‘CLERK’ and “MG
R” = ‘7782’ and “HIREDATE” = TO_DATE(‘1982-01-23 0
0:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “SAL” = ‘13
00’ and “COMM” IS NULL and “DEPTNO” = ‘10’ and ROW
ID = ‘AAASxmAAGAAAAF7AAN’;

TEST 1236025 2017-10-30 01:01:57 delete from “TEST”.“TEST1” where “EMPNO” = ‘7902’
and “ENAME” = ‘FORD’ and “JOB” = ‘ANALYST’ and “MG
R” = ‘7566’ and “HIREDATE” = TO_DATE(‘1981-12-03 0
0:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “SAL” = ‘30
00’ and “COMM” IS NULL and “DEPTNO” = ‘20’ and ROW
ID = ‘AAASxmAAGAAAAF7AAM’;

TEST 1236025 2017-10-30 01:01:57 delete from “TEST”.“TEST1” where “EMPNO” = ‘7521’
and “ENAME” = ‘WARD’ and “JOB” = ‘SALESMAN’ and “M
GR” = ‘7698’ and “HIREDATE” = TO_DATE(‘1981-02-22
00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “SAL” = ‘1
250’ and “COMM” = ‘500’ and “DEPTNO” = ‘30’ and RO
WID = ‘AAASxmAAGAAAAF7AAC’;

TEST 1236025 2017-10-30 01:01:57 delete from “TEST”.“TEST1” where “EMPNO” = ‘7566’
and “ENAME” = ‘JONES’ and “JOB” = ‘MANAGER’ and “M
GR” = ‘7839’ and “HIREDATE” = TO_DATE(‘1981-04-02
00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “SAL” = ‘2
975’ and “COMM” IS NULL and “DEPTNO” = ‘20’ and RO
WID = ‘AAASxmAAGAAAAF7AAD’;

TEST 1236025 2017-10-30 01:01:57 delete from “TEST”.“TEST1” where “EMPNO” = ‘7654’
and “ENAME” = ‘MARTIN’ and “JOB” = ‘SALESMAN’ and
“MGR” = ‘7698’ and “HIREDATE” = TO_DATE(‘1981-09-2
8 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “SAL” =
‘1250’ and “COMM” = ‘1400’ and “DEPTNO” = ‘30’ and
ROWID = ‘AAASxmAAGAAAAF7AAE’;

TEST 1236025 2017-10-30 01:01:57 delete from “TEST”.“TEST1” where “EMPNO” = ‘7698’
and “ENAME” = ‘BLAKE’ and “JOB” = ‘MANAGER’ and “M
GR” = ‘7839’ and “HIREDATE” = TO_DATE(‘1981-05-01
00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “SAL” = ‘2
850’ and “COMM” IS NULL and “DEPTNO” = ‘30’ and RO
WID = ‘AAASxmAAGAAAAF7AAF’;

TEST 1236025 2017-10-30 01:01:57 delete from “TEST”.“TEST1” where “EMPNO” = ‘7782’
and “ENAME” = ‘CLARK’ and “JOB” = ‘MANAGER’ and “M
GR” = ‘7839’ and “HIREDATE” = TO_DATE(‘1981-06-09
00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “SAL” = ‘2
450’ and “COMM” IS NULL and “DEPTNO” = ‘10’ and RO
WID = ‘AAASxmAAGAAAAF7AAG’;

TEST 1236025 2017-10-30 01:01:57 delete from “TEST”.“TEST1” where “EMPNO” = ‘7788’
and “ENAME” = ‘SCOTT’ and “JOB” = ‘ANALYST’ and “M
GR” = ‘7566’ and “HIREDATE” = TO_DATE(‘1987-04-19
00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “SAL” = ‘3
000’ and “COMM” IS NULL and “DEPTNO” = ‘20’ and RO
WID = ‘AAASxmAAGAAAAF7AAH’;

TEST 1236025 2017-10-30 01:01:57 delete from “TEST”.“TEST1” where “EMPNO” = ‘7839’
and “ENAME” = ‘KING’ and “JOB” = ‘PRESIDENT’ and "
MGR" IS NULL and “HIREDATE” = TO_DATE(‘1981-11-17
00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “SAL” = ‘5
000’ and “COMM” IS NULL and “DEPTNO” = ‘10’ and RO
WID = ‘AAASxmAAGAAAAF7AAI’;

TEST 1236025 2017-10-30 01:01:57 delete from “TEST”.“TEST1” where “EMPNO” = ‘7844’
and “ENAME” = ‘TURNER’ and “JOB” = ‘SALESMAN’ and
“MGR” = ‘7698’ and “HIREDATE” = TO_DATE(‘1981-09-0
8 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “SAL” =
‘1500’ and “COMM” = ‘0’ and “DEPTNO” = ‘30’ and RO
WID = ‘AAASxmAAGAAAAF7AAJ’;

USERNAME SCN TIMESTAMP SQL_REDO

---

TEST 1236025 2017-10-30 01:01:57 delete from “TEST”.“TEST1” where “EMPNO” = ‘7876’
and “ENAME” = ‘ADAMS’ and “JOB” = ‘CLERK’ and "MGR
" = ‘7788’ and “HIREDATE” = TO_DATE(‘1987-05-23 00
:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “SAL” = ‘110
0’ and “COMM” IS NULL and “DEPTNO” = ‘20’ and ROWI
D = ‘AAASxmAAGAAAAF7AAK’;

TEST 1236025 2017-10-30 01:01:57 delete from “TEST”.“TEST1” where “EMPNO” = ‘7900’
and “ENAME” = ‘JAMES’ and “JOB” = ‘CLERK’ and "MGR
" = ‘7698’ and “HIREDATE” = TO_DATE(‘1981-12-03 00
:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “SAL” = '950
’ and “COMM” IS NULL and “DEPTNO” = ‘30’ and ROWID
= ‘AAASxmAAGAAAAF7AAL’;

TEST 1236036 2017-10-30 01:02:27 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘74
99’,‘ALLEN’,‘SALESMAN’,‘7698’,TO_DATE(‘1981-02-20
00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘1600’,‘300’,’
30’);

TEST 1236036 2017-10-30 01:02:27 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘73
69’,‘SMITH’,‘CLERK’,‘7902’,TO_DATE(‘1980-12-17 00:
00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘800’,NULL,‘20’);

TEST 1236036 2017-10-30 01:02:27 insert into “TEST”.“TEST1”(“EMPNO”,“ENAME”,“JOB”,"
MGR",“HIREDATE”,“SAL”,“COMM”,“DEPTNO”) values (‘75
21’,‘WARD’,‘SALESMAN’,‘7698’,TO_DATE(‘1981-02-22 0
0:00:00’, ‘yyyy-mm-dd hh24:mi:ss’),‘1250’,‘500’,‘3
0’);

41 rows selected. --确认误操作的时间为 2017-10-30 01:01:57

sys@TESTDB12>

sys@TESTDB12>select file#,name from v$datafile;

 FILE# NAME

---

     1 /u01/app/oracle/oradata/TestDB12/system01.dbf
     2 /u01/app/oracle/oradata/TestDB12/sysaux01.dbf
     3 /u01/app/oracle/oradata/TestDB12/undotbs01.dbf
     4 /u01/app/oracle/oradata/TestDB12/users01.dbf
     5 /u01/app/oracle/oradata/TestDB12/example01.dbf
     6 /u01/app/oracle/oradata/TestDB12/test1.dbf

6 rows selected.
sys@TESTDB12>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TESTDB12>startup mount;
ORACLE instance started.

Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 528485720 bytes
Database Buffers 301989888 bytes
Redo Buffers 6574080 bytes
Database mounted.
sys@TESTDB12>

---

sys@TESTDB12>alter system set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’ scope=spfile;

System altered.

sys@TESTDB12>shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
sys@TESTDB12>startup mount;
ORACLE instance started.

Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 528485720 bytes
Database Buffers 301989888 bytes
Redo Buffers 6574080 bytes
Database mounted.
sys@TESTDB12>
------------------------------------------------------- 如果报时间格式错误执行这一步
oracle@TestDB:/home/oracle> rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Oct 30 01:18:55 2017

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: TESTDB12 (DBID=2811829300, not open)

RMAN> run{
2> set until time “to_date(‘2017-10-30 01:01:57’,‘yyyy-mm-dd hh24:mi:ss’)”;
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> restore database;
6> recover database;
7> alter database open resetlogs;
8> }

executing command: SET until clause

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=135 device type=DISK

allocated channel: c2
channel c2: SID=11 device type=DISK

Starting restore at 30-OCT-17
flashing back control file to SCN 1235833

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/TestDB12/system01.dbf
channel c1: restoring datafile 00002 to /u01/app/oracle/oradata/TestDB12/sysaux01.dbf
channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/TestDB12/undotbs01.dbf
channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/TestDB12/users01.dbf
channel c1: restoring datafile 00005 to /u01/app/oracle/oradata/TestDB12/example01.dbf
channel c1: restoring datafile 00006 to /u01/app/oracle/oradata/TestDB12/test1.dbf
channel c1: reading from backup piece /home/oracle/rman/TESTDB12_10_1_hot.bak
channel c1: piece handle=/home/oracle/rman/TESTDB12_10_1_hot.bak tag=TAG20171030T005619
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:01:55
Finished restore at 30-OCT-17

Starting recover at 30-OCT-17

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 30-OCT-17

database opened
released channel: c1
released channel: c2

RMAN>
test@TESTDB12>select count(*) from test1;

COUNT(*)

    14

test@TESTDB12>

---

基于scn号的恢复和上述一样，只不过将恢复脚本改为
run{
set until scn 500582;
allocate channel c1 type disk;
allocate channel c2 type disk;
restore database;
recover database;
alter database open resetlogs;
}
即可。