flashback database

- setting

$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Tue May 26 16:09:43 2009

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                  1279240 bytes
Variable Size              79694584 bytes
Database Buffers          104857600 bytes
Redo Buffers                2912256 bytes
Database mounted.

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

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

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASH RETENTION_TARGET FLASHBACK_SIZE
-------------------- ------------ ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
              421926 26-MAY-09                1440        8192000
                       0


SQL> select * from v$flashback_database_stat;

BEGIN_TIME   END_TIME     FLASHBACK_DATA    DB_DATA  REDO_DATA
------------ ------------ -------------- ---------- ----------
ESTIMATED_FLASHBACK_SIZE
------------------------
26-MAY-09    26-MAY-09              8192          0          0
                       0


SQL> alter database open;

Database altered.

- test

SQL> conn scott/tiger
Connected.

SQL> select to_char(sysdate, 'YY-MM-DD HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'
-----------------
09-05-26 16:31:49

SQL> select empno,ename,job,sal,deptno from test_emp;

     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK            800         20
      7499 ALLEN      SALESMAN        1600         30
      7521 WARD       SALESMAN        1250         30
      7566 JONES      MANAGER         2975         20
      7654 MARTIN     SALESMAN        1250         30
      7698 BLAKE      MANAGER         2850         30
      7782 CLARK      MANAGER         2450         10
      7788 SCOTT      ANALYST         3000         20
      7839 KING       PRESIDENT       5000         10
      7844 TURNER     SALESMAN        1500         30
      7876 ADAMS      CLERK           1100         20

     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
      7900 JAMES      CLERK            950         30
      7902 FORD       ANALYST         3000         20
      7934 MILLER     CLERK           1300         10

14 rows selected.

SQL> delete from test_emp where deptno=30;

6 rows deleted.

SQL> commit
  2  ;

Commit complete.

SQL> select empno,ename,job,sal,deptno from test_emp;

     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK            800         20
      7566 JONES      MANAGER         2975         20
      7782 CLARK      MANAGER         2450         10
      7788 SCOTT      ANALYST         3000         20
      7839 KING       PRESIDENT       5000         10
      7876 ADAMS      CLERK           1100         20
      7902 FORD       ANALYST         3000         20
      7934 MILLER     CLERK           1300         10

8 rows selected.

SQL> conn /as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                  1279240 bytes
Variable Size              79694584 bytes
Database Buffers          104857600 bytes
Redo Buffers                2912256 bytes
Database mounted.

SQL> flashback database   
  2  to timestamp(to_date('2009-05-26 16:31:49','YYYY-MM-DD HH24:MI:SS'));

Flashback complete.

SQL> alter database open
  2  ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> conn scott/tiger
Connected.
SQL> select empno,ename,job,sal,deptno from test_emp;

     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK            800         20
      7499 ALLEN      SALESMAN        1600         30
      7521 WARD       SALESMAN        1250         30
      7566 JONES      MANAGER         2975         20
      7654 MARTIN     SALESMAN        1250         30
      7698 BLAKE      MANAGER         2850         30
      7782 CLARK      MANAGER         2450         10
      7788 SCOTT      ANALYST         3000         20
      7839 KING       PRESIDENT       5000         10
      7844 TURNER     SALESMAN        1500         30
      7876 ADAMS      CLERK           1100         20

     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
      7900 JAMES      CLERK            950         30
      7902 FORD       ANALYST         3000         20
      7934 MILLER     CLERK           1300         10

14 rows selected.

SQL>