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
