ITPub博客

首页 > Linux操作系统 > Linux操作系统 > flashback database

flashback database

原创 Linux操作系统 作者:cqubityj 时间:2008-01-11 10:03:52 0 删除 编辑

启用flashback database后,数据库中会出现一个新的后台进程RVWR,该进程用于将flashback buffer中的数据写入flashback日志中,作用类似lgwr。
1、配置flashback database:
(1) 关闭数据库并启动数据到mount exclusive状态
shutdown immediate;
startup mount exclusive;
(2) 设置参数
alter system set db_flashback_retention_target=60;
alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';
alter system set db_recovery_size=4G;
(3) 启用flashback database
alter database flashback on;
(4) 打开数据库
alter database open;
(5) 查询动态视图
select flashback_on from v$database;

2、flashback database:
(1) 关闭数据库并启动到mount exclusive状态
shutdown immediate;
startup mount exclusive;
(2) flashback database
flashback database to timestamp/scn...;
(3) open resetlogs
alter database open resetlogs;

3、监控flashback log area:
(1) 监控flashback log空间占用情况
select retention_target, flashback_size,estimated_flashback_size from v$flashback_database_log;
(2) 监控过去24小时内每小时flashback日志、redo日志及data block读写量
select to_char(end_time,'yyyy-mm-dd hh:miAM') end_timestamp,flashback_data, db_data, redo_data from v$flashback_database_stat;
(3) 监控flash recovery area情况
select name, space_limit max_size, space_used used, space_reclaimable obsolete,number_of_files num_files from v$recovery_file_dest;

flashback database测试:
SQL> select flashback_on from v$database;

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

SQL> show parameter instance_n

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      test
instance_number                      integer     0
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  637534208 bytes
Fixed Size                  1262416 bytes
Variable Size             171969712 bytes
Database Buffers          457179136 bytes
Redo Buffers                7122944 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
flashback database功能已经启动.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     165059

SQL> conn user1/abc
Connected.
SQL> create table test as select * from user_objects;

Table created.

SQL> select count(1) from test;

  COUNT(1)
----------
         1

1 row selected.

SQL> conn /as sysdba
Connected.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     165095

1 row selected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area  637534208 bytes
Fixed Size                  1262416 bytes
Variable Size             171969712 bytes
Database Buffers          457179136 bytes
Redo Buffers                7122944 bytes
Database mounted.

SQL> flashback database to scn 165059;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> conn user1/abc
Connected.
SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from tab;

no rows selected
数据库flashback到表test创建之前。

SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area  637534208 bytes
Fixed Size                  1262416 bytes
Variable Size             171969712 bytes
Database Buffers          457179136 bytes
Redo Buffers                7122944 bytes
Database mounted.
SQL> flashback database to scn 165095;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> conn user1/abc
Connected.
SQL> select count(1) from test;

  COUNT(1)
----------
         1

1 row selected.
数据库再次flashback到表创建之后。

确认flashback到需要的时间点之后,重启数据库,以resetlogs方式打开。
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  637534208 bytes
Fixed Size                  1262416 bytes
Variable Size             171969712 bytes
Database Buffers          457179136 bytes
Redo Buffers                7122944 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> conn user1/abc
Connected.
SQL> select count(1) from test;

  COUNT(1)
----------
         1

1 row selected.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/228190/viewspace-134390/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-19

  • 博文量
    133
  • 访问量
    425159