FLASHBACK DATABASE (1)开启数据库闪回及闪回误删的SCHEMA

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

Total System Global Area  167772160 bytes                                      
Fixed Size                  1247900 bytes                                      
Variable Size             104858980 bytes                                      
Database Buffers           58720256 bytes                                      
Redo Buffers                2945024 bytes                                      
数据库装载完毕。
SQL> select flashback_on from v$database;

FLASHBACK_ON                                                                   
------------------                                                             
NO                                                                             

SQL> alter database flashback on ;

数据库已更改。

SQL> select log_mode from v$database;

LOG_MODE                                                                       
------------                                                                   
ARCHIVELOG                                                                     

SQL> select archiver from v$instance;

ARCHIVE                                                                        
-------                                                                        
STARTED                                                                        

SQL> alter database open
  2  ;

数据库已更改。

SQL> select flashback_on from v$database;

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

SQL> set time on                                                 

12:05:27 SQL> set linesize 300
12:05:35 SQL> select *from V$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBA RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE                                                                                                                                                                                                               
-------------------- -------------- ---------------- -------------- ------------------------                                                                                                                                                                                                               
             2870987 29-12月-11                 1440        8192000                        0                                                                                                                                                                                                               

12:05:37 SQL> set linesize 500
12:05:45 SQL> select *from V$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBA RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE                                                                                                                                                                                                                                                                                                                                                                                                                       
-------------------- -------------- ---------------- -------------- ------------------------                                                                                                                                                                                                                                                                                                                                                                                                                       
             2870987 29-12月-11                 1440        8192000                        0                                                                                                                                                                                                                                                                                                                                                                                                                       

12:05:47 SQL> select * from v$flashback_database_stat;

BEGIN_TIME     END_TIME       FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE                                                                                                                                                                                                                                                                                                                                                                                                                        
-------------- -------------- -------------- ---------- ---------- ------------------------                                                                                                                                                                                                                                                                                                                                                                                                                        
29-12月-11     29-12月-11            4104192     761856    3570688                        0                                                                                                                                                                                                                                                                                                                                                                                                                        

12:06:05 SQL> select * from V$sgastat where name like 'flashback%';

POOL         NAME                            BYTES                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
------------ -------------------------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
shared pool  flashback generation buff     3981204                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

12:06:31 SQL> select object_name from dba_objects where owner='TINA';

OBJECT_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
--------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                   
CREATE_DATE_LOG_ROW                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

12:17:02 SQL>
12:17:40 SQL> drop user tina cascade;

用户已删除。

12:18:01 SQL> select object_name from dba_objects where owner='TINA';

未选定行

12:18:09 SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
12:19:12 SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE 例程已经启动。

Total System Global Area  167772160 bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
Fixed Size                  1247900 bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
Variable Size             109053284 bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
Database Buffers           54525952 bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
Redo Buffers                2945024 bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
数据库装载完毕。
12:20:51 SQL> flashback database to timestamp to_timestamp('2011-12-29 12:17:40','yyyy-mm-dd hh24:mi:ss');

闪回完成。

12:21:57 SQL> alter database open read only;

数据库已更改。

12:22:08 SQL> select object_name from dba_objects where owner='TINA';

OBJECT_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
--------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                   
CREATE_DATE_LOG_ROW                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

12:22:39 SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
12:23:25 SQL> ;
  1* select object_name from dba_objects where owner='TINA'
12:23:25 SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE 例程已经启动。

Total System Global Area  167772160 bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
Fixed Size                  1247900 bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
Variable Size             109053284 bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
Database Buffers           54525952 bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
Redo Buffers                2945024 bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
数据库装载完毕。
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项

12:23:48 SQL> alter database open resetlogs;

数据库已更改。

12:25:40 SQL> select object_name from dba_objects where owner='TINA';

OBJECT_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
--------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                   
CREATE_DATE_LOG_ROW