Flashback Database的使用

Flashback Database是Oracle 10g新增的特性,默认情况下Flashback Database是不可用的.他通过Oracle10g新的文件flasback database log来实现,flashback database log的存放位置由db_recovery_file_dest决定.

1.启用Flashback Database

关闭数据库

shutdown immediate

启动数据库到mount状态

startup mount

设置db_flashback_rentention_target参数为你想要的值

alter system set db_flashback_retention_target=1440;(1440为默认值时间单位为分钟)

启用flashback databse

alter database flashback on;(数据库必须运行在archivelog模式下,否则你将得到错误信息)

2.下面我们做一测试来说明Flashback Database的使用

可以通过下面的方式 使用flashback database

flashback database to scn …

flashback databasae to timestamp …

设置会话日期格式

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

通过下的statement 可以确定能恢复到SCN或time的最前时间.

SQL> select oldest_flashback_scn , oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI

-------------------- -------------------

          1.4941E+10 2008-06-24 08:35:43

确定oak用户拥有的表

SQL> select table_name from dba_tables where wner='OAK';

TABLE_NAME

------------------------------

TEST1

TEST2

TEST3

查看sysdate

SQL> select sysdate from dual;

SYSDATE

-------------------

2008-06-24 09:21:27

删除schema oak

SQL> drop user oak cascade;

User dropped.

Flashback Database

你可以在RMAN或sqlplus下Flashback Database

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              88082676 bytes

Database Buffers           75497472 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL>  flashback database to timestamp to_timestamp('2008-06-24 09:21:27','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

通过alter database open read only查看flashback database是否是有想要的状态,若是就就可打开了

SQL> alter database open read only;

Database altered.

SQL> select table_name from dba_tables where wner='OAK';

TABLE_NAME

------------------------------

TEST1

TEST2

TEST3

我们可看出数据库已经恢复.

SQL> startup force mount;

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              88082676 bytes

Database Buffers           75497472 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

3.与Flashback Database有关的视图

v$database:可以确认flashback database是否启用.

SQL>select flashback_on from v$database;

FLASHBACK_ON

------------------

YES

v$flashback_database_log

v$flashback_database_stat