Flashback database tested in Oracle 10g

************** Flashback database *********************

Scenario 1:

14:45:16 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

14:45:38 SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1248552 bytes
Variable Size 104858328 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
Database mounted.

14:50:40 SQL> alter database flashback on;
Database altered.

14:50:48 SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
14:51:02 SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1248552 bytes
Variable Size 104858328 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.

14:51:14 SQL> select * from v$flashback_database_logfile;

NAME
-------------------------------------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\FLASHBACK\O1_MF_55XW7J6N_.FLB
SQL> set time on

13:34:09 SQL> create user xx identified by xx;
User created.
13:35:16 SQL> grant connect,resource to xx;
Grant succeeded.

13:35:23 SQL> select username from dba_users;
USERNAME
------------------------------
DBSNMP
SYSMAN
NV
XX
OUTLN
MDSYS
IX
.
.
HR
29 rows selected.

13:35:35 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

13:36:26 SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1248552 bytes
Variable Size 113246936 bytes
Database Buffers 163577856 bytes
Redo Buffers 7139328 bytes
Database mounted.

13:36:50 SQL> flashback database to timestamp to_timestamp('17/07/09:11:36:09','dd/mm/yy:hh:mi:ss');
Flashback complete.

13:38:10 SQL> alter database open resetlogs;
Database altered.

13:38:46 SQL> select username from dba_users;

USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
NV
OUTLN
MDSYS
ORDSYS
EXFSYS
DMSYS
WMSYS
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS
SCOTT
TSMSYS
BI
PM
MDDATA
IX
SH
DIP
OE
HR
28 rows selected.

Scenario 2:

******************************* Adding tablespace and creating tables ******************

16:15:26 SQL> alter table c add (name varchar(10));
Table altered.

16:15:47 SQL> desc c
Name
-------------------------------------------------------------------------------------------------------------NO
NAME

16:15:52 SQL> select * from c;
NO NAME
---------- ----------
12
23
44
45

16:15:58 SQL> commit;
Commit complete.

16:16:02 SQL> select * from c as of timestamp to_timestamp('20/07/2009:04:00:00','dd/mm/yyyy:hh:mi:ss');
NO NAME
---------- ----------
12
23
44

16:17:41 SQL> alter table c enable row movement;
Table altered.

16:17:53 SQL> flashback table c to timestamp to_timestamp('20/07/2009:04:00:00','dd/mm/yyyy:hh:mi:ss');
Flashback complete.

16:17:56 SQL> select * from c;
NO NAME
---------- ----------
12
23
44

16:21:43 SQL> create tablespace flashback datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\flashback.dbf' size 2M;
Tablespace created.

16:22:43 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 FLASHBACK YES NO YES
7 rows selected.

16:22:48 SQL> select name from v$datafile;

NAME
-------------------------------------------------------------------------------------------------------------D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\FLASHBACK.DBF
6 rows selected.

16:24:49 SQL> conn nv/database
Connected.

16:24:57 SQL> create table test_flash(no number);
Table created.

16:25:05 SQL> conn / as sysdba
Connected.

16:25:09 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

16:25:45 SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1248552 bytes
Variable Size 104858328 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
Database mounted.

16:25:54 SQL> flashback database to timestamp to_timestamp('20/07/2009:04:00:00','dd/mm/yyyy:hh:mi:ss');
Flashback complete.

16:27:17 SQL> alter database open resetlogs;
Database altered.

16:27:39 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
6 rows selected.

16:27:45 SQL> select name from v$datafile;
NAME
-------------------------------------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF

16:28:00 SQL> conn nv/database
Connected.

16:28:31 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE
T TABLE
B TABLE
SYS_TEMP_FBT TABLE
C TABLE

16:28:34 SQL> desc c
Name
-------------------------------------------------------------------------------------------------------------
NO

16:28:37 SQL> desc a
Name
-------------------------------------------------------------------------------------------------------------NO

16:28:41 SQL> select * from c;
NO
----------
12
23
44

Scenario 3:
***************************** Droping tablespace *********************

15:54:52 SQL> create tablespace n datafile 'D:\oracle\product\10.2.0\oradata\orcl\n.dbf' size 5M;
Tablespace created.

15:55:18 SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
899730

15:57:14 SQL>
15:57:16 SQL> drop tablespace n including contents and datafiles;
Tablespace dropped.

15:57:34 SQL> conn / as sysdba
Connected.

15:58:36 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

15:59:20 SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1248552 bytes
Variable Size 79692504 bytes
Database Buffers 197132288 bytes
Redo Buffers 7139328 bytes
Database mounted.

15:59:26 SQL> flashback database to timestamp to_timestamp('22/07/2009:15:55:39','dd/mm/yyyy:hh24:mi:ss');
flashback database to timestamp to_timestamp('22/07/2009:15:55:39','dd/mm/yyyy:hh24:mi:ss')
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 6 will be lost if RESETLOGS is done
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00006'


16:05:41 SQL> flashback database to timestamp to_timestamp('22/07/2009:15:57:36','dd/mm/yyyy:hh24:mi:ss');
Flashback complete.

16:06:04 SQL> alter database open resetlogs;
Database altered.

16:06:31 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 52428800 1 YES UNUSED 0
2 1 1 52428800 1 NO CURRENT 899831 22-JUL-09
3 1 0 52428800 1 YES UNUSED 0

16:06:50 SQL> show user
USER is "SYS"

16:28:03 SQL> conn nv/database
Connected.

16:28:08 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE
T TABLE
B TABLE
SYS_TEMP_FBT TABLE
C TABLE

16:28:12 SQL> select * from c;
NO
----------
1
23
44
2
23333333

******************************* end ***********************