--打开SQL*Plus时间显示

scott@TESTDB11>set time on

15:34:04 scott@TESTDB11>select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      ......

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

--删除所有数据，被删除的大概时间是15:34:26

15:34:26 scott@TESTDB11>delete from emp;

14 rows deleted.

--提交

15:35:13 scott@TESTDB11>commit;

Commit complete.

15:35:16 scott@TESTDB11>select * from emp;

no rows selected

--做3次日志切换，目的就是让记录刚才改变的日志被归档出来

sys@TESTDB11>alter system switch logfile;

System altered.

--查看归档出来的日志，确定需要挖掘的日志序号为8

sys@TESTDB11>col name for a40

sys@TESTDB11>alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Session altered.

sys@TESTDB11>select sequence#, name, first_time, next_time from v$archived_log;

 SEQUENCE# NAME                                     FIRST_TIME          NEXT_TIME

---------- ---------------------------------------- ------------------- -------------------

         1 /archive2/1_1_823247647.dbf              2013-08-12 07:54:07 2013-08-12 08:24:28

         1 /archive1/1_1_823247647.dbf              2013-08-12 07:54:07 2013-08-12 08:24:28      

...

         7 /archive2/1_7_823247647.dbf              2013-08-12 08:27:00 2013-08-12 08:27:01

         8 /archive1/1_8_823247647.dbf              2013-08-12 08:27:01 2013-08-12 15:40:42

         8 /archive2/1_8_823247647.dbf              2013-08-12 08:27:01 2013-08-12 15:40:42

130 rows selected.

-- 进行挖掘，根据前面的时间确定操作的SCN和时间为: 2703654 2013-08-12 15:35:13

sys@TESTDB11>exec dbms_logmnr.add_logfile(logfilename => '/archive1/1_8_823247647.dbf', options => dbms_logmnr.new);

PL/SQL procedure successfully completed.

sys@TESTDB11>exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

sys@TESTDB11>col sql_redo for a50

sys@TESTDB11>select scn, timestamp, sql_redo from v$logmnr_contents where seg_owner='SCOTT' and seg_name = 'EMP';

       SCN TIMESTAMP           SQL_REDO

---------- ------------------- --------------------------------------------------

   2683065 2013-08-12 08:46:52 insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","M

                               GR","HIREDATE","SAL","COMM","DEPTNO") values ('793

                               4','MILLER','CLERK','7782',TO_DATE('1982-01-23 00:

                               00:00', 'yyyy-mm-dd hh24:mi:ss'),'1300',NULL,'10')

                               ;

   2703654 2013-08-12 15:35:13 delete from "SCOTT"."EMP" where "EMPNO" = '7369' a

       SCN TIMESTAMP           SQL_REDO

---------- ------------------- --------------------------------------------------

                               nd "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

                               = 'AAAU4PAAEAAAACTAAA';

   .......省略

   2703654 2013-08-12 15:35:13 delete from "SCOTT"."EMP" where "EMPNO" = '7934' a

                               nd "ENAME" = 'MILLER' and "JOB" = 'CLERK' and "MGR

                               " = '7782' and "HIREDATE" = TO_DATE('1982-01-23 00

                               :00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '130

                               0' and "COMM" IS NULL and "DEPTNO" = '10' and ROWI

                               D = 'AAAU4PAAEAAAACTAAN';

30 rows selected.

-- 查看当前数据库检查点号, 上面确定的那个检查点2703654，肯定要比这个小。

sys@TESTDB11>select name, checkpoint_change# from v$datafile_header;

NAME                                     CHECKPOINT_CHANGE#

---------------------------------------- ------------------

/oradata/TestDB11/system01.dbf                      2703910

/oradata/TestDB11/sysaux01.dbf                      2703910

/oradata/TestDB11/undotbs01.dbf                     2703910

/oradata/TestDB11/users01.dbf                       2703910

/oradata/TestDB11/example01.dbf                     2703910

/oradata/TestDB11/newundotbs01.dbf                  2703910

6 rows selected.

--下面要进行不完全恢复

sys@TESTDB11>shutdown mount force;

SP2-0717: illegal SHUTDOWN option

sys@TESTDB11>startup mount force;

ORACLE instance started.

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             641730040 bytes

Database Buffers          209715200 bytes

Redo Buffers                2306048 bytes

Database mounted.

--还原

sys@TESTDB11>!cp /pooldisk02/backup01/inconsistent/*.dbf /oradata/TestDB11

--人工计算一下恢复到检查点，确定恢复会在哪个日志停下来（其实不需要人工计算,Oralce会自己计算)

sys@TESTDB11>col name for a40

sys@TESTDB11>select sequence#, name, first_change#, next_change# from v$archived_log;

 SEQUENCE# NAME                                     FIRST_CHANGE# NEXT_CHANGE#

---------- ---------------------------------------- ------------- ------------

         1 /archive1/1_1_823247647.dbf                    2679129      2681425

         ....省略

         7 /archive2/1_7_823247647.dbf                    2681524      2681527

         8 /archive1/1_8_823247647.dbf                    2681527      2703910

         8 /archive2/1_8_823247647.dbf                    2681527      2703910

--恢复

sys@TESTDB11>recover database until change 2703654;

Media recovery complete.

sys@TESTDB11>select sequence#, group#, status from v$log;

 SEQUENCE#     GROUP# STATUS

---------- ---------- ----------------

         7          1 INACTIVE

         9          3 CURRENT

         8          2 INACTIVE

--此时查看数据文件中头中的检查点号，为我们指定恢到的SCN号而且一致，可以开库

sys@TESTDB11>col name for a50

sys@TESTDB11>select name, checkpoint_change# from v$datafile_header;

NAME                                               CHECKPOINT_CHANGE#

-------------------------------------------------- ------------------

/oradata/TestDB11/system01.dbf                                2703654

/oradata/TestDB11/sysaux01.dbf                                2703654

/oradata/TestDB11/undotbs01.dbf                               2703654

/oradata/TestDB11/users01.dbf                                 2703654

/oradata/TestDB11/example01.dbf                               2703654

/oradata/TestDB11/newundotbs01.dbf                            2703654

6 rows selected.   

sys@TESTDB11>alter database open resetlogs;

Database altered.

--日志已经重置了

sys@TESTDB11>select sequence#, group#, status from v$log;

 SEQUENCE#     GROUP# STATUS

---------- ---------- ----------------

         1          1 CURRENT

         0          2 UNUSED

         0          3 UNUSED

--检查数据,都被恢复回来了（即解决了误操作的问题）

16:20:30 scott@TESTDB11>select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

.......

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

--删除无用的归档

[oracle@S1011:/archive1]$ rm *

[oracle@S1011:/archive2]$ rm *

--resetlogs之后，要做一个整个数据库备份（调用我们之前做好的脚本backup02.sql)

sys@TESTDB11>@backup_script/backup02.sql