Oracle 10g FLASHBACK DATABASE 實驗記錄

...[@more@]

[TEST:oradev] /devTESTarch1/flash_back/TEST/onlinelog> as sysdba" <

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Jan 5 16:28:55 2007

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system set db_recovery_file_dest='/devTESTarch1/flash_back' scope=bo
th;

System altered.

SQL> alter system set db_recovery_file_dest_size=5G scope=both;

System altered.

SQL> show parameter recover

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /devTESTarch1/flash_back
db_recovery_file_dest_size big integer 5G
recovery_parallelism integer 0
SQL> show parameter db_flashback

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /devTESTarch1/arch
Oldest online log sequence 268
Next log sequence to archive 270
Current log sequence 270
SQL> select count(*) from v$session;

COUNT(*)
----------
24

SQL> shutdown immeidate
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 3758096384 bytes
Fixed Size 2075728 bytes
Variable Size 754975664 bytes
Database Buffers 2986344448 bytes
Redo Buffers 14700544 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> select FLASHBACK_ON from v$database;

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

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /devTESTarch1/arch
Oldest online log sequence 268
Next log sequence to archive 270
Current log sequence 270
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
2 FROM V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
3609746 2007-01-05 16:32:36

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from t1;

Table created.

SQL> create table t3 as select * from t1;

Table created.

SQL> set time on;
16:35:10 SQL> select sysdate from dual;

SYSDATE
-------------------
2007-01-05 16:35:19

16:35:19 SQL> select sysdate from dual;

SYSDATE
-------------------
2007-01-05 16:35:48

16:35:48 SQL> truncate table t2;

Table truncated.

16:36:03 SQL> drop table t3;

Table dropped.

16:36:11 SQL> create table t4 as select * from t1;
Table created.

16:36:29 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
16:37:34 SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area 3758096384 bytes
Fixed Size 2075728 bytes
Variable Size 754975664 bytes
Database Buffers 2986344448 bytes
Redo Buffers 14700544 bytes
Database mounted.
16:38:09 SQL> FLASHBACK DATABASE TO timestamp(to_date('2007-01-05 16:35:48','yyy
y-mm-dd hh24:mi:ss'));

Flashback complete.

16:38:16 SQL> alter database open resetlogs;

Database altered.

16:38:57 SQL> select count(*) from t1;

COUNT(*)
----------
50291

16:39:21 SQL> select count(*) from t2;

COUNT(*)
----------
50291
16:39:31 SQL> select count(*) from t3;

COUNT(*)
----------
50291

16:39:42 SQL> select count(*) from t4;
select count(*) from t4
*
ERROR at line 1:
ORA-00942: table or view does not exist

16:39:45 SQL>