CUUG官方博客: CUUG官网_CUUG怎么样_CUUG培训就业怎么样_CUUG学Oracle要多少钱

-手工不完全恢复实验-

手工不完全恢复实验

先用logmnr找到错误的时间;关库,为了配合rman 启到mount状态转储所有的数据文件;
恢复到某个时间点或者是某个SCN号;先以read only方式开库查看数据状态,不得以的情况用resetlogs开库

1.基于DML操作所做的不完全恢复)

SQL> alter database add supplemental log data;

Database altered.

SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database;

SUPPLEME SUP SUP
-------- --- ---
YES      NO  NO
SQL> insert into scott.cuug1 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> select count(*) from scott.cuug1;

  COUNT(*)
----------
        28

SQL> insert into scott.cuug1 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from scott.cuug1;

  COUNT(*)
----------
        42

SQL> delete from scott.cuug1;

42 rows deleted.

SQL> insert into scott.cuug1 select * from scott.emp where rownum<=3;

3 rows created.

SQL> commit;

Commit complete.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
FIRST_TIM NEXT_CHANGE# NEXT_TIME
--------- ------------ ---------
         1          1          1  104857600        512          2 YES ACTIVE                  549437
15-APR-16       549652 15-APR-16

         2          1          2  104857600        512          2 NO  CURRENT                 549652
15-APR-16   2.8147E+14

         3          1          0  104857600        512          2 YES UNUSED                       0
                     0
SQL> col member for a50
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /u01/app/oracle/oradata/PROD/disk1/redo01.log      NO
         1         ONLINE  /u01/app/oracle/oradata/PROD/disk2/redo01.log      NO
         2         ONLINE  /u01/app/oracle/oradata/PROD/disk1/redo02.log      NO
         2         ONLINE  /u01/app/oracle/oradata/PROD/disk2/redo02.log      NO
         3         ONLINE  /u01/app/oracle/oradata/PROD/disk1/redo03.log      NO
         3         ONLINE  /u01/app/oracle/oradata/PROD/disk2/redo03.log      NO

6 rows selected.

SQL> select name from v$archived_log where name is not null;

NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/disk1/redo01.log
/u01/app/oracle/oradata/PROD/disk2/redo01.log
/u01/app/oracle/oradata/PROD/disk1/redo02.log
/u01/app/oracle/oradata/PROD/disk2/redo02.log
/u01/app/oracle/oradata/PROD/disk1/redo03.log
/u01/app/oracle/oradata/PROD/disk2/redo03.log
/home/oracle/PROD/1_1_909244172.dbf

7 rows selected.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
>    LOGFILENAME => '/u01/app/oracle/oradata/PROD/disk1/redo02.log', -
>    OPTIon\=> DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL>
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
>    LOGFILENAME => '/home/oracle/PROD/1_1_909244172.dbf', -
>    OPTIon\=> DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL>
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIon\=> -
>    DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

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

Session altered.

SQL> col username for a10
SQL> col sql_redo for a50
SQL> select username,scn,timestamp,sql_redo from (
  2     select a.*,rownum rn
  3     from (select username,scn,timestamp,sql_redo
  4           from v$logmnr_contents where seg_name='CUUG1' order by scn) a
  5    )where rn>65 and rn<80;       

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
SYS            549672 2016-04-15 15:55:03 delete from "SCOTT"."CUUG1" where "EMPNO" = '7844'
                                           and "ENAME" = 'TURNER' and "JOB" = 'SALESMAN' and
                                           "MGR" = '7698' and "HIREDATE" = TO_DATE('1981-09-
                                          08 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" =
                                           '1500' and "COMM" = '0' and "DEPTNO" = '30' and R
                                          OWID = 'AAADNsAAFAAAADbAAz';

SYS            549672 2016-04-15 15:55:03 delete from "SCOTT"."CUUG1" where "EMPNO" = '7876'
                                           and "ENAME" = 'ADAMS' and "JOB" = 'CLERK' and "MG
                                          R" = '7788' and "HIREDATE" = TO_DATE('1987-05-23 0
                                          0:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '11

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
                                          00' and "COMM" IS NULL and "DEPTNO" = '20' and ROW
                                          ID = 'AAADNsAAFAAAADbAA0';

SYS            549672 2016-04-15 15:55:03 delete from "SCOTT"."CUUG1" where "EMPNO" = '7900'
                                           and "ENAME" = 'JAMES' and "JOB" = 'CLERK' and "MG
                                          R" = '7698' and "HIREDATE" = TO_DATE('1981-12-03 0
                                          0:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '95
                                          0' and "COMM" IS NULL and "DEPTNO" = '30' and ROWI
                                          D = 'AAADNsAAFAAAADbAA1';

SYS            549672 2016-04-15 15:55:03 delete from "SCOTT"."CUUG1" where "EMPNO" = '7902'

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
                                           and "ENAME" = 'FORD' and "JOB" = 'ANALYST' and "M
                                          GR" = '7566' and "HIREDATE" = TO_DATE('1981-12-03
                                          00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '3
                                          000' and "COMM" IS NULL and "DEPTNO" = '20' and RO
                                          WID = 'AAADNsAAFAAAADbAA2';

SYS            549672 2016-04-15 15:55:03 delete from "SCOTT"."CUUG1" where "EMPNO" = '7934'
                                           and "ENAME" = 'MILLER' and "JOB" = 'CLERK' and "M
                                          GR" = '7782' and "HIREDATE" = TO_DATE('1982-01-23
                                          00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '1
                                          300' and "COMM" IS NULL and "DEPTNO" = '10' and RO

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
                                          WID = 'AAADNsAAFAAAADbAA3';

SYS            549683 2016-04-15 19:52:39 insert into "SCOTT"."CUUG1"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          369','SMITH','CLERK','7902',TO_DATE('1980-12-17 00
                                          :00:00', 'yyyy-mm-dd hh24:mi:ss'),'800',NULL,'20')
                                          ;

SYS            549683 2016-04-15 19:52:39 insert into "SCOTT"."CUUG1"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          521','WARD','SALESMAN','7698',TO_DATE('1981-02-22

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
                                          00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1250','500','
                                          30');

SYS            549683 2016-04-15  19:52:39insert into "SCOTT"."CUUG1"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          499','ALLEN','SALESMAN','7698',TO_DATE('1981-02-20
                                           00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1600','300',
                                          '30');


8 rows selected.

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size                  1339740 bytes
Variable Size             490737316 bytes
Database Buffers          339738624 bytes
Redo Buffers                5160960 bytes
Database mounted.

SQL> recover database until time '2016-04-16 19:58:03';
Media recovery complete.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery


SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from scott.cuug1;

  COUNT(*)
----------
        42


2.truncate 误操作,找到truncate操作的时间点和SCN号,转储所有的数据文件,恢复 open resetlogs
SQL> alter database add supplemental log data;

Database altered.

SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database;

SUPPLEME SUP SUP
-------- --- ---
YES      NO  NO

SQL> insert into scott.cuug2 select * from scott.emp;

14 rows created.
SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> select count(*) from scott.cuug2;

  COUNT(*)
----------
        28

SQL> insert into scott.cuug2 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from scott.cuug2;

  COUNT(*)
----------
        42

SQL> truncate table scott.cuug2;

Table truncated.

SQL> insert into scott.cuug2 select * from scott.emp where rownum<=3;

3 rows created.

SQL> select count(*) from scott.cuug2;

  COUNT(*)
----------
         3

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
FIRST_TIM NEXT_CHANGE# NEXT_TIME
--------- ------------ ---------
         1          1          1  104857600        512          2 YES INACTIVE                549673
15-APR-16       550123 15-APR-16

         2          1          2  104857600        512          2 YES ACTIVE                  550123
15-APR-16       550222 15-APR-16

         3          1          3  104857600        512          2 NO  CURRENT                 550222
15-APR-16   2.8147E+14


SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /u01/app/oracle/oradata/PROD/disk1/redo01.log      NO
         1         ONLINE  /u01/app/oracle/oradata/PROD/disk2/redo01.log      NO
         2         ONLINE  /u01/app/oracle/oradata/PROD/disk1/redo02.log      NO
         2         ONLINE  /u01/app/oracle/oradata/PROD/disk2/redo02.log      NO
         3         ONLINE  /u01/app/oracle/oradata/PROD/disk1/redo03.log      NO
         3         ONLINE  /u01/app/oracle/oradata/PROD/disk2/redo03.log      NO

6 rows selected.

SQL> col name for a50
SQL> select resetlogs_id,name from v$archived_log where name is not null order by 1,2;

RESETLOGS_ID NAME
------------ --------------------------------------------------
   909225221 /u01/app/oracle/oradata/PROD/disk1/redo01.log
   909225221 /u01/app/oracle/oradata/PROD/disk1/redo02.log
   909225221 /u01/app/oracle/oradata/PROD/disk1/redo03.log
   909225221 /u01/app/oracle/oradata/PROD/disk2/redo01.log
   909225221 /u01/app/oracle/oradata/PROD/disk2/redo02.log
   909225221 /u01/app/oracle/oradata/PROD/disk2/redo03.log
   909244172 /home/oracle/PROD/1_1_909244172.dbf
   909244172 /home/oracle/PROD/1_2_909244172.dbf
   909245210 /home/oracle/PROD/1_1_909245210.dbf
   909245210 /home/oracle/PROD/1_2_909245210.dbf

10 rows selected.

                                           
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
>    LOGFILENAME => '/u01/app/oracle/oradata/PROD/disk1/redo03.log', -
>    OPTIon\=> DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL>
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
>    LOGFILENAME => '/home/oracle/PROD/1_2_909245210.dbf', -
>    OPTIon\=> DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.


SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIon\=> -
>    DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> col username for a10
SQL> col sql_redo for a50
SQL> select username,scn,timestamp,sql_redo from (
  2     select a.*,rownum rn
  3     from (select username,scn,timestamp,sql_redo
  4           from v$logmnr_contents where seg_name='CUUG2' order by scn) a
  5    )where rn>100 and rn<130;

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
UNKNOWN        550228 2016-04-17 10:25:47 insert into "SCOTT"."CUUG2"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          788','SCOTT','ANALYST','7566',TO_DATE('1987-04-19
                                          00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'3000',NULL,'2
                                          0');

UNKNOWN        550228 2016-04-17 10:25:47 insert into "SCOTT"."CUUG2"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          839','KING','PRESIDENT',NULL,TO_DATE('1981-11-17 0
                                          0:00:00', 'yyyy-mm-dd hh24:mi:ss'),'5000',NULL,'10
                                          ');

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------

UNKNOWN        550228 2016-04-17 10:25:47 insert into "SCOTT"."CUUG2"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          934','MILLER','CLERK','7782',TO_DATE('1982-01-23 0
                                          0:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1300',NULL,'10
                                          ');

UNKNOWN        550228 2016-04-17 10:25:47 insert into "SCOTT"."CUUG2"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          902','FORD','ANALYST','7566',TO_DATE('1981-12-03 0
                                          0:00:00', 'yyyy-mm-dd hh24:mi:ss'),'3000',NULL,'20

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
                                          ');

UNKNOWN        550259 2016-04-15 10:25:47 truncate table scott.cuug2;
UNKNOWN        550274 2016-04-15 10:25:47 insert into "SCOTT"."CUUG2"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          369','SMITH','CLERK','7902',TO_DATE('1980-12-17 00
                                          :00:00', 'yyyy-mm-dd hh24:mi:ss'),'800',NULL,'20')
                                          ;

UNKNOWN        550274 2016-04-15 10:25:47 insert into "SCOTT"."CUUG2"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
                                          499','ALLEN','SALESMAN','7698',TO_DATE('1981-02-20
                                           00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1600','300',
                                          '30');

UNKNOWN        550274 2016-04-15 10:25:47 insert into "SCOTT"."CUUG2"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          521','WARD','SALESMAN','7698',TO_DATE('1981-02-22
                                          00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1250','500','
                                          30');


8 rows selected.
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size                  1339740 bytes
Variable Size             490737316 bytes
Database Buffers          339738624 bytes
Redo Buffers                5160960 bytes
Database mounted.
SQL> recover database until change 550259;
ORA-00279: change 549437 generated at 04/15/2016 16:29:09 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_1_909244172.dbf
ORA-00280: change 549437 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 549652 generated at 04/15/2016 16:29:09 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_2_909244172.dbf
ORA-00280: change 549652 for thread 1 is in sequence #2


Log applied.
Media recovery complete.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery


SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from scott.cuug2;

  COUNT(*)
----------
        42

3.破坏了最新的归档,当前的日志文件,还有一个数据文件。
SQL> alter database add supplemental log data;

Database altered.

SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database;

SUPPLEME SUP SUP
-------- --- ---
YES      NO  NO
SQL> insert into scott.cuug3 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> select count(*) from scott.cuug3;

  COUNT(*)
----------
        28

1 row selected.

SQL> insert into scott.cuug3 select * from scott.emp;

14 rows created.

SQL>  commit;

Commit complete.

SQL> select count(*) from scott.cuug3;

  COUNT(*)
----------
        42

1 row selected.

SQL> insert into scott.cuug3 select * from scott.emp;

14 rows created.

SQL> select count(*) from scott.cuug3;

  COUNT(*)
----------
        56

1 row selected.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1          4  104857600        512          2 NO  CURRENT                 551118 15-APR-16         2.8147E+14
         2          1          2  104857600        512          2 YES INACTIVE                550910 15-APR-16             551048 15-APR-16
         3          1          3  104857600        512          2 YES INACTIVE                551048 15-APR-16             551118 15-APR-16

3 rows selected.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /u01/app/oracle/oradata/PROD/disk1/redo01.log      NO
         1         ONLINE  /u01/app/oracle/oradata/PROD/disk2/redo01.log      NO
         2         ONLINE  /u01/app/oracle/oradata/PROD/disk1/redo02.log      NO
         2         ONLINE  /u01/app/oracle/oradata/PROD/disk2/redo02.log      NO
         3         ONLINE  /u01/app/oracle/oradata/PROD/disk1/redo03.log      NO
         3         ONLINE  /u01/app/oracle/oradata/PROD/disk2/redo03.log      NO

6 rows selected.

SQL> select resetlogs_id,name from v$archived_log where name is not null order by 1,2;

RESETLOGS_ID NAME
------------ --------------------------------------------------
   909225221 /u01/app/oracle/oradata/PROD/disk1/redo01.log
   909225221 /u01/app/oracle/oradata/PROD/disk1/redo02.log
   909225221 /u01/app/oracle/oradata/PROD/disk1/redo03.log
   909225221 /u01/app/oracle/oradata/PROD/disk2/redo01.log
   909225221 /u01/app/oracle/oradata/PROD/disk2/redo02.log
   909225221 /u01/app/oracle/oradata/PROD/disk2/redo03.log
   909244172 /home/oracle/PROD/1_1_909244172.dbf
   909244172 /home/oracle/PROD/1_2_909244172.dbf
   909245210 /home/oracle/PROD/1_1_909245210.dbf
   909245210 /home/oracle/PROD/1_2_909245210.dbf
   909245210 /home/oracle/PROD/1_3_909245210.dbf

RESETLOGS_ID NAME
------------ --------------------------------------------------
   909247447 /home/oracle/PROD/1_1_909247447.dbf
   909247447 /home/oracle/PROD/1_2_909247447.dbf
   909247447 /home/oracle/PROD/1_3_909247447.dbf

14 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/PROD/disk3/system01.dbf
/u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
/u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
/u01/app/oracle/oradata/PROD/disk3/users01.dbf
/u01/app/oracle/oradata/PROD/disk4/cuug01.dbf

5 rows selected.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size                  1339740 bytes
Variable Size             490737316 bytes
Database Buffers          339738624 bytes
Redo Buffers                5160960 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/disk4/cuug01.dbf'


SQL> select file#,error from v$recover_file;

     FILE# ERROR
---------- -----------------------------------------------------------------
         1 UNKNOWN ERROR
         2 UNKNOWN ERROR
         3 UNKNOWN ERROR
         4 UNKNOWN ERROR
         5 FILE NOT FOUND

5 rows selected.

SQL> recover database until cancel;
ORA-00279: change 550821 generated at 04/15/2016 16:57:33 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_1_909247447.dbf
ORA-00280: change 550821 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from scott.cuug3;

  COUNT(*)
----------
        14

1 row selected.

5.闪回drop(利用的原理:recyclebin)
SQL> show parameter recover;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size                  1339740 bytes
Variable Size             490737316 bytes
Database Buffers          339738624 bytes
Redo Buffers                5160960 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

以上的操作开闪回五步

SQL> select name,flashback_on from v$database;

NAME                                               FLASHBACK_ON
-------------------------------------------------- ------------------
PROD                                               YES

SQL> select count(*) from scott.cuug5;

  COUNT(*)
----------
        14

SQL> drop table scott.cuug5;

Table dropped.

SQL> conn scott/tiger
Connected.
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
CUUG1            BIN$MHwYAce63qvgUKjAgDgLEQ==$0 TABLE        2016-04-17:09:28:45
CUUG2            BIN$MH1g/qTKC2ngUKjAgDgEwQ==$0 TABLE        2016-04-17:10:20:29
CUUG5            BIN$MI/TPwMN///gUKjAgDgV1w==$0 TABLE        2016-04-17:10:12:46
SQL> select * from "BIN$MI/TPwMN///gUKjAgDgV1w==$0";

     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
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      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.

SQL> flashback table scott.cuug5 to before drop rename to cuug5;

Flashback complete.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$MH1g/qTKC2ngUKjAgDgEwQ==$0 TABLE
BIN$MHwYAce63qvgUKjAgDgLEQ==$0 TABLE
BONUS                          TABLE
CUUG1                          TABLE
CUUG10                         TABLE
CUUG2                          TABLE
CUUG3                          TABLE
CUUG4                          TABLE
CUUG55                         TABLE
CUUG6                          TABLE
CUUG7                          TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
CUUG8                          TABLE
CUUG9                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

16 rows selected.

SQL> rename cuug55 to cuug5
  2  ;

Table renamed.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$MH1g/qTKC2ngUKjAgDgEwQ==$0 TABLE
BIN$MHwYAce63qvgUKjAgDgLEQ==$0 TABLE
BONUS                          TABLE
CUUG1                          TABLE
CUUG10                         TABLE
CUUG2                          TABLE
CUUG3                          TABLE
CUUG4                          TABLE
CUUG5                          TABLE
CUUG6                          TABLE
CUUG7                          TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
CUUG8                          TABLE
CUUG9                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

16 rows selected.

SQL> select * from cuug5;

     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
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      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.
6.闪回数据库、闪回归档用于恢复DDL操作,适用于恢复system表空间
09:42:23 SQL> select count(*) from scott.cuug6;

  COUNT(*)
----------
        14

Elapsed: 00:00:00.00
09:43:05 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
09:43:14 SQL> delete from scott.cuug6;

14 rows deleted.

Elapsed: 00:00:00.00
09:43:28 SQL> insert into scott.cuug6 select * from scott.emp where rownum<=3;

3 rows created.

Elapsed: 00:00:00.00
09:44:01 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
 
09:46:46 SQL> select * from scott.cuug6 as of timestamp to_timestamp('2016-04-17 10:20:00','yyyy-mm-dd hh24:mi:ss');

     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
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      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.

Elapsed: 00:00:00.01
09:56:14 SQL> create table emp_bak as select * from scott.cuug6 as of timestamp to_timestamp('2016-04-16 09:00:00','yyyy-mm-dd hh24:mi:ss');

Table created.

Elapsed: 00:00:01.58
09:57:28 SQL> select * from emp_bak;

     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
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      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.

Elapsed: 00:00:00.01
09:57:46 SQL> truncate table scott.cuug6;

Table truncated.

Elapsed: 00:00:00.10
09:58:11 SQL> insert into scott.cuug6 select * from emp_bak;

14 rows created.

Elapsed: 00:00:00.01
09:58:38 SQL> drop table emp_bak;

Table dropped.

Elapsed: 00:00:00.22
09:58:48 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
09:58:54 SQL> select * from scott.cuug6;

     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
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      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.

Elapsed: 00:00:00.00
09:10:09 SQL>


7.闪回表、闪回查询,依赖undo数据,undo段空间能大些最好

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     556278

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            554301

SQL> select count(*) from scott.cuug7;

  COUNT(*)
----------
        14

SQL> delete from scott.cuug7;

14 rows delete.
SQL> insert into scott.cuug7 select * from scott.emp where rownum<=3;

3 rows created.

SQL> commit;

Commit complete.

SQL> select * from scott.cuug7 as of scn 554301
  2  ;

     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
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      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.

SQL> flashback table scott.cuug7 to scn 554301;
flashback table scott.cuug7 to scn 554301
                      *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

SQL> alter table scott.cuug7 enable row movement;

Table altered.

SQL> flashback table scott.cuug7 to scn 554301;

Flashback complete.

SQL> select * from scott.cuug7;

     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
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      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.

8.闪回数据库,正常关库,启到mount状态,不用转储,因为用的是闪回日志。
9:27:36 SQL> insert into scott.cuug8 select * from scott.emp;

14 rows created.

9:27:57 SQL> commit;

Commit complete.

9:28:00 SQL> alter system  archive log current;

System altered.

9:28:16 SQL> select count(*) from scott.cuug8;

  COUNT(*)
----------
        28

9:28:37 SQL> insert into scott.cuug8 select * from scott.emp;

14 rows created.

9:28:44 SQL> commit;

Commit complete.

9:28:48 SQL> select count(*) from scott.cuug8;

  COUNT(*)
----------
        42

9:28:59 SQL> truncate table scott.cuug8;

Table truncated.

9:30:30 SQL> insert into scott.cuug8 select * from scott.emp where rownum<=3;

3 rows created.

9:31:14 SQL> commit;

Commit complete.

9:31:26 SQL> select count(*) from scott.cuug8;

  COUNT(*)
----------
         3

9:31:45 SQL> alter database add supplemental log data;

Database altered.

9:34:00 SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database;

SUPPLEME SUP SUP
-------- --- ---
YES      NO  NO

9:35:02 SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
FIRST_TIM NEXT_CHANGE# NEXT_TIME
--------- ------------ ---------
         1          1          1  104857600        512          2 YES INACTIVE                551199
16-APR-16       556979 16-APR-16

         2          1          2  104857600        512          2 NO  CURRENT                 556979
16-APR-16   2.8147E+14

         3          1          0  104857600        512          2 YES UNUSED                       0
                     0


9:35:12 SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /u01/app/oracle/oradata/PROD/disk1/redo01.log      NO
         1         ONLINE  /u01/app/oracle/oradata/PROD/disk2/redo01.log      NO
         2         ONLINE  /u01/app/oracle/oradata/PROD/disk1/redo02.log      NO
         2         ONLINE  /u01/app/oracle/oradata/PROD/disk2/redo02.log      NO
         3         ONLINE  /u01/app/oracle/oradata/PROD/disk1/redo03.log      NO
         3         ONLINE  /u01/app/oracle/oradata/PROD/disk2/redo03.log      NO

6 rows selected.

9:35:29 SQL> select resetlogs_id,name from v$archived_log where name is not null order by 1,2;

RESETLOGS_ID NAME
------------ --------------------------------------------------
   909225221 /u01/app/oracle/oradata/PROD/disk1/redo01.log
   909225221 /u01/app/oracle/oradata/PROD/disk1/redo02.log
   909225221 /u01/app/oracle/oradata/PROD/disk1/redo03.log
   909225221 /u01/app/oracle/oradata/PROD/disk2/redo01.log
   909225221 /u01/app/oracle/oradata/PROD/disk2/redo02.log
   909225221 /u01/app/oracle/oradata/PROD/disk2/redo03.log
   909244172 /home/oracle/PROD/1_1_909244172.dbf
   909244172 /home/oracle/PROD/1_2_909244172.dbf
   909245210 /home/oracle/PROD/1_1_909245210.dbf
   909245210 /home/oracle/PROD/1_2_909245210.dbf
   909245210 /home/oracle/PROD/1_3_909245210.dbf

RESETLOGS_ID NAME
------------ --------------------------------------------------
   909247447 /home/oracle/PROD/1_1_909247447.dbf
   909247447 /home/oracle/PROD/1_2_909247447.dbf
   909247447 /home/oracle/PROD/1_3_909247447.dbf
   909306226 /home/oracle/PROD/1_1_909306226.dbf

15 rows selected.

9:36:08 SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
9:39:21 >    LOGFILENAME => '/u01/app/oracle/oradata/PROD/disk1/redo02.log', -
9:39:21 >    OPTIon\=> DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

9:39:22 SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
9:39:32 >    LOGFILENAME => '/home/oracle/PROD/1_1_909306226.dbf', -
9:39:32 >    OPTIon\=> DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

9:39:32 SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIon\=> -
9:39:50 >    DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

9:39:51 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

9:40:56 SQL> col username for a10
9:41:06 SQL> col sql_redo for a50
9:41:06 SQL> select username,scn,timestamp,sql_redo from (
9:41:06   2     select a.*,rownum rn
9:41:06   3     from (select username,scn,timestamp,sql_redo
9:41:06   4           from v$logmnr_contents where seg_name='CUUG1' order by scn) a
9:41:06   5    )where rn>30 and rn<50;

no rows selected

9:41:11 SQL> col username for a10
9:41:28 SQL> col sql_redo for a50
9:41:28 SQL> select username,scn,timestamp,sql_redo from (
9:41:28   2     select a.*,rownum rn
9:41:28   3     from (select username,scn,timestamp,sql_redo
9:41:28   4           from v$logmnr_contents where seg_name='CUUG8' order by scn) a
9:41:28   5    )where rn>30 and rn<50;

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
SYS            557182 2016-04-16 9:28:58 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          902','FORD','ANALYST','7566',TO_DATE('1981-12-03 0
                                          0:00:00', 'yyyy-mm-dd hh24:mi:ss'),'3000',NULL,'20
                                          ');

SYS            557182 2016-04-16 9:28:58 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          934','MILLER','CLERK','7782',TO_DATE('1982-01-23 0
                                          0:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1300',NULL,'10
                                          ');

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------

SYS            557182 2016-04-16 9:28:58 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          900','JAMES','CLERK','7698',TO_DATE('1981-12-03 00
                                          :00:00', 'yyyy-mm-dd hh24:mi:ss'),'950',NULL,'30')
                                          ;

SYS            557182 2016-04-16 9:28:58 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          521','WARD','SALESMAN','7698',TO_DATE('1981-02-22
                                          00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1250','500','

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
                                          30');

SYS            557182 2016-04-16 9:28:58 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          566','JONES','MANAGER','7839',TO_DATE('1981-04-02
                                          00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'2975',NULL,'2
                                          0');

SYS            557182 2016-04-16 9:28:58 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          654','MARTIN','SALESMAN','7698',TO_DATE('1981-09-2

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
                                          8 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1250','1400
                                          ','30');

SYS            557182 2016-04-16 9:28:58 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          698','BLAKE','MANAGER','7839',TO_DATE('1981-05-01
                                          00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'2850',NULL,'3
                                          0');

SYS            557182 2016-04-16 9:28:58 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
                                          782','CLARK','MANAGER','7839',TO_DATE('1981-06-09
                                          00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'2450',NULL,'1
                                          0');

SYS            557182 2016-04-16 9:28:58 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          788','SCOTT','ANALYST','7566',TO_DATE('1987-04-19
                                          00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'3000',NULL,'2
                                          0');

SYS            557182 2016-04-16 9:28:58 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          839','KING','PRESIDENT',NULL,TO_DATE('1981-11-17 0
                                          0:00:00', 'yyyy-mm-dd hh24:mi:ss'),'5000',NULL,'10
                                          ');

SYS            557182 2016-04-16 9:28:58 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          844','TURNER','SALESMAN','7698',TO_DATE('1981-09-0
                                          8 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1500','0','
                                          30');


USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
SYS            557182 2016-04-16 9:28:58 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          876','ADAMS','CLERK','7788',TO_DATE('1987-05-23 00
                                          :00:00', 'yyyy-mm-dd hh24:mi:ss'),'1100',NULL,'20'
                                          );

SYS            557228 2016-04-16 9:30:30 truncate table scott.cuug8;
SYS            557250 2016-04-16 9:31:19 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          369','SMITH','CLERK','7902',TO_DATE('1980-12-17 00
                                          :00:00', 'yyyy-mm-dd hh24:mi:ss'),'800',NULL,'20')

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
                                          ;

SYS            557250 2016-04-16 9:31:19 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          499','ALLEN','SALESMAN','7698',TO_DATE('1981-02-20
                                           00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1600','300',
                                          '30');

SYS            557250 2016-04-16 9:31:19 insert into "SCOTT"."CUUG8"("EMPNO","ENAME","JOB",
                                          "MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7
                                          521','WARD','SALESMAN','7698',TO_DATE('1981-02-22

USERNAME          SCN TIMESTAMP           SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
                                          00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1250','500','
                                          30');


16 rows selected.

9:41:31 SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.

10:42:34 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10:43:48 SQL> startup mount;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size                  1339740 bytes
Variable Size             490737316 bytes
Database Buffers          339738624 bytes
Redo Buffers                5160960 bytes
Database mounted.
9:44:00 SQL> flashback database to timestamp to_timestamp('2016-04-17 9:30:29','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

9:49:07 SQL> alter database open read only;

Database altered.

9:49:29 SQL> select count(*) from scott.cuug8;

  COUNT(*)
----------
        42
9:53:44 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10:53:56 SQL> startup mount
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size                  1339740 bytes
Variable Size             490737316 bytes
Database Buffers          339738624 bytes
Redo Buffers                5160960 bytes
Database mounted.
9:54:07 SQL> alter database open resetlogs;

Database altered.

9:54:43 SQL> select count(*) from scott.cuug8;

  COUNT(*)
----------
        42

9.闪回归档,闪回归档的表空间必须是ASSM的表空间

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
         2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
         3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
         5 /u01/app/oracle/oradata/PROD/disk4/cuug01.dbf

SQL> create tablespace fba datafile '/u01/app/oracle/oradata/PROD/disk5/fba01.dbf' size 50m autoextend on next 10m maxsize 500m;

Tablespace created.

SQL> create flashback archive fba1 tablespace fba retention 1 year;

Flashback archive created.

SQL> grant flashback archive on fba1 to scott;

Grant succeeded.

SQL> alter table scott.emp flashback archive fba1;

Table altered.

SQL> show user;
USER is "SYS"
SQL> truncate table scott.emp;

Table truncated.

SQL> select * from scott.emp as of timestamp to_timestamp('2016-04-17 11:10:00','yyyy-mm-dd hh24:mi:ss');

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

14 rows selected.
SQL> insert into scott.emp select * from scott.emp as of timestamp to_timestamp('2016-04-17 11:10:00','yyyy-mm-dd hh24:mi:ss');

14 rows created.

SQL> commit;

Commit complete.

SQL> conn scott/tiger
Connected.
SQL> col OWNER_NAME for a20
SQL> col FLASHBACK_ARCHIVE_NAME for a20
SQL> col ARCHIVE_TABLE_NAME for a30
SQL> select * from user_flashback_archive_tables;

TABLE_NAME OWNER_NAME           FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME             STATUS
---------- -------------------- -------------------- ------------------------------ --------
EMP        SCOTT                FBA1                 SYS_FBA_HIST_13010             ENABLED
SQL> select EMPNO,ENAME,RID,STARTSCN,ENDSCN,XID,OPERATION from SYS_FBA_HIST_13010;

     EMPNO ENAME      RID                    STARTSCN     ENDSCN XID              O
---------- ---------- -------------------- ---------- ---------- ---------------- -
      7902 FORD       AAADLSAAEAAAACRAAM                  558598
      7369 SMITH      AAADLSAAEAAAACRAAA                  558598
      7839 KING       AAADLSAAEAAAACRAAI                  558598
      7698 BLAKE      AAADLSAAEAAAACRAAF                  558598
      7782 CLARK      AAADLSAAEAAAACRAAG                  558598
      7900 JAMES      AAADLSAAEAAAACRAAL                  558598
      7934 MILLER     AAADLSAAEAAAACRAAN                  558598
      7654 MARTIN     AAADLSAAEAAAACRAAE                  558598
      7521 WARD       AAADLSAAEAAAACRAAC                  558598
      7876 ADAMS      AAADLSAAEAAAACRAAK                  558598
      7566 JONES      AAADLSAAEAAAACRAAD                  558598

     EMPNO ENAME      RID                    STARTSCN     ENDSCN XID              O
---------- ---------- -------------------- ---------- ---------- ---------------- -
      7499 ALLEN      AAADLSAAEAAAACRAAB                  558598
      7844 TURNER     AAADLSAAEAAAACRAAJ                  558598
      7788 SCOTT      AAADLSAAEAAAACRAAH                  558598

14 rows selected.

作者:shaoxinyu_lucky 分类:学员作业 浏览:297 评论:1
留言列表
访客
访客 很有质量的一篇文章  回复
发表评论
来宾的头像

点击关闭
  • 在线客服