SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 4月 22 10:14:27 2008

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
已连接。
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 UNDOTBS1                       YES NO  YES
         2 SYSAUX                         YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           NO  NO  YES
         6 EXAMPLE                        YES NO  YES
         7 TEST                           YES NO  YES

已选择7行。

SQL> select table_name,tablespace_name from dba_tables where table_name in ('EMP','EMP2');

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMP2                           TEST
EMP                            USERS

SQL> alter tablespace test flashback off;

表空间已更改。

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

  COUNT(*)
----------
        15

SQL> delete from scott.emp where job='DBA';

已删除 1 行。

SQL> commit;

提交完成。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     867262

删除1行之前的SCN号,这个时候EMP表有14行,EMP2没有改变.

SQL> insert into scott.emp values(9900,'Matt','AUDIT',null,sysdate,8000,null,40);

已创建 1 行。

SQL> insert into scott.emp values(7899,'Gay','SA',null,sysdate-20,7000,null,30);

已创建 1 行。

SQL> commit;

提交完成。

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

  COUNT(*)
----------
    438769

SQL> delete from scott.emp2 where rownum<28770;

已删除28769行。

SQL> commit;

提交完成。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     867588

变化之后的SCN,这个时候EMP和EMP2都发生了变化,这个时候我们要闪回数据库到之前那个SCN,也就是EMP表有14行,
但是又希望EMP2表能保持现在的数据.

SQL>

SQL> alter tablespace test offline;

表空间已更改。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。

SQL> flashback database to scn 867262;
flashback database to scn 867262
*
第 1 行出现错误:
ORA-38795: 警告: FLASHBACK 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01245: RESETLOGS 完成时脱机文件 6 将丢失
ORA-01110: 数据文件 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DEVDB\TEST.DBF'

这时alert_devdb.log的内容:

Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Tue Apr 22 10:31:27 2008
flashback database to scn 867262
Tue Apr 22 10:31:28 2008
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Tue Apr 22 10:31:31 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 31 Reading mem 0
  Mem# 0: +DISK_GROUP1/devdb/onlinelog/group_1.258.651578121
  Mem# 1: +DISK_GROUP2/devdb/onlinelog/group_1.317.651578125
Tue Apr 22 10:31:33 2008
Incomplete Recovery applied until change 867266
Flashback Media Recovery Complete

SQL> alter database open read only;
alter database open read only
*
第 1 行出现错误:
ORA-16004: 备份数据库需要恢复
ORA-01245: RESETLOGS 完成时脱机文件 6 将丢失
ORA-01110: 数据文件 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DEVDB\TEST.DBF'

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

     FILE# NAME                                                         STATUS  CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------------------------------------------------ ------- ------------------ ------------
         1 +DISK_GROUP1/devdb/datafile/system.273.651578001             SYSTEM              867266
         2 +DISK_GROUP1/devdb/datafile/undotbs1.268.651578003           RECOVER             867266
         3 +DISK_GROUP1/devdb/datafile/sysaux.278.651578001             RECOVER             867266
         4 +DISK_GROUP1/devdb/datafile/users.259.651578003              RECOVER             867266
         5 +DISK_GROUP1/devdb/datafile/example.290.651578169            RECOVER             867266
         6 D:\ORACLE\PRODUCT\10.2.0\ORADATA\DEVDB\TEST.DBF              OFFLINE             867140

已选择6行。

SQL> select file#,status,recover,CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  REC CHECKPOINT_CHANGE#
---------- ------- --- ------------------
         1 ONLINE                  867266
         2 ONLINE                  867266
         3 ONLINE                  867266
         4 ONLINE                  867266
         5 ONLINE                  867266
         6 OFFLINE                 867663

已选择6行。

SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01245: RESETLOGS 完成时脱机文件 6 将丢失
ORA-01110: 数据文件 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DEVDB\TEST.DBF'

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
第 1 行出现错误:
ORA-01610: 使用 BACKUP CONTROLFILE 选项的恢复必须已完成

SQL> recover datafile 6;
ORA-00283: 恢复会话因错误而取消
ORA-38798: 无法执行部分数据库恢复
ORA-38797: 在闪回数据库后, 需要执行完全数据库恢复

SQL> recover database;
完成介质恢复。

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

     FILE# NAME                                                         STATUS  CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------------------------------------------------ ------- ------------------ ------------
         1 +DISK_GROUP1/devdb/datafile/system.273.651578001             SYSTEM              868012       868012
         2 +DISK_GROUP1/devdb/datafile/undotbs1.268.651578003           ONLINE              868012       868012
         3 +DISK_GROUP1/devdb/datafile/sysaux.278.651578001             ONLINE              868012       868012
         4 +DISK_GROUP1/devdb/datafile/users.259.651578003              ONLINE              868012       868012
         5 +DISK_GROUP1/devdb/datafile/example.290.651578169            ONLINE              868012       868012
         6 D:\ORACLE\PRODUCT\10.2.0\ORADATA\DEVDB\TEST.DBF              OFFLINE             867663

已选择6行。

SQL> select file#,status,recover,CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  REC CHECKPOINT_CHANGE#
---------- ------- --- ------------------
         1 ONLINE                  868012
         2 ONLINE                  868012
         3 ONLINE                  868012
         4 ONLINE                  868012
         5 ONLINE                  868012
         6 OFFLINE                 867663

已选择6行。

SQL>

SQL> alter database open;

数据库已更改。

SQL> alter tablespace test online;

表空间已更改。

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

  COUNT(*)
----------
    410000

EMP2表还是最新的结果.

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

  COUNT(*)
----------
        16

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      9900 Matt       AUDIT                2008-04-22 10:25:13       8000                    40
      7899 Gay        SA                   2008-04-02 10:25:50       7000                    30

已选择16行。

SQL>
emp表也是最新的结果,被恢复回来了.

SQL> alter tablespace test flashback on;
alter tablespace test flashback on
*
第 1 行出现错误:
ORA-01126: 数据库必须已装载到此实例并且不在任何实例中打开

SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  612368384 bytes
Fixed Size                  1298184 bytes
Variable Size             209715448 bytes
Database Buffers          398458880 bytes
Redo Buffers                2895872 bytes
数据库装载完毕。
SQL> select * from v$tablespace;

       TS# NAME                                                         INC BIG FLA ENC
---------- ------------------------------------------------------------ --- --- --- ---
         0 SYSTEM                                                       YES NO  YES
         1 UNDOTBS1                                                     YES NO  YES
         2 SYSAUX                                                       YES NO  YES
         4 USERS                                                        YES NO  YES
         3 TEMP                                                         NO  NO  YES
         6 EXAMPLE                                                      YES NO  YES
         7 TEST                                                         YES NO  NO

已选择7行。

SQL> alter tablespace test flashback on;

表空间已更改。

SQL>

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

  COUNT(*)
----------
    410000

SQL> delete from scott.emp2 where rownum<20000;

已删除19999行。

SQL> commit;

提交完成。

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

  COUNT(*)
----------
    390001

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     868763

SQL> select  * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
              866025 2008-04-01 21:37:25             1440       24576000                        0

SQL> select * from v$flashback_database_stat;

BEGIN_TIME          END_TIME            FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------- ------------------- -------------- ---------- ---------- ------------------------
2008-04-22 10:59:36 2008-04-22 11:03:31         557056      49152      66560                        0

SQL>select * from v$recovery_file_dest;

NAME            SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------- ----------- ---------- ----------------- ---------------
+DISK_GROUP2     4294967296  635437056          44040192              13

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  612368384 bytes
Fixed Size                  1298184 bytes
Variable Size             209715448 bytes
Database Buffers          398458880 bytes
Redo Buffers                2895872 bytes
数据库装载完毕。

SQL> flashback database to timestamp to_timestamp('2008-04-22 11:00:00','yyyy-mm-dd hh24:mi:ss')
  2  ;
flashback database to timestamp to_timestamp('2008-04-22 11:00:00','yyyy-mm-dd hh24:mi:ss')
*
第 1 行出现错误:
ORA-38753: 无法闪回数据文件 6; 没有闪回日志数据。
ORA-01110: 数据文件 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DEVDB\TEST.DBF'

SQL> alter system archive log current;

系统已更改。

SQL> delete from scott.emp2 where rownum<20001;

已删除20000行。

SQL> commit;

提交完成。

SQL> l
  1* commit
SQL> delete from scott.emp2 where rownum<20001;

已删除20000行。

SQL> commit;

提交完成。

SQL>
SQL> delete from scott.emp2 where rownum<20001;

已删除20000行。

SQL>
SQL> commit;

提交完成。

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

  COUNT(*)
----------
    330001

SQL>
SQL>
SQL>
SQL> commit;

提交完成。

SQL> alter system checkpoint;

系统已更改。

SQL> flashback database to timestamp to_timestamp('2008-04-22 11:15:00','yyyy-mm-dd hh24:mi:ss');

闪回完成。

SQL> alter database open read only;

数据库已更改。

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

  COUNT(*)
----------
    390001

可以通过READ ONLY来看闪回到哪里.