Flashback database 闪库
从oracle10g开始提供了flashback database。
Oracle为了实现这一功能,另外创建了一组日志,就是flashback logs(前滚日志)
日志记录数据库的前滚操作,并不是所有的改变都需要记录,
只记录一系列的前滚快照点,也就是一些时间点上块的前映像,
根据这些快照点,加上归档日志与联机日志,就可以恢复任何时间点上的记录了
使用flashback database,数据库必须处于归档状态
执行闪库必须在mount状态
闪回数据存储区
闪回需要产生闪回日志 以及改前数据映像
这些默认都是放到闪回恢复区的 这个区域的大小,位置,保持时间都由参数来决定
参数db_recover_file_dest 定义路径位置
参数db_recover_file_dest_size 定义区域大小 默认2G
参数db_flashback_retention_target定义保持力 默认1440分 就24小时
开启闪库
1.开启归档
2.闪回区位置设置正确路径和大小
db_recover_file_dest
db_recover_file_dest_size
3.激活数据库闪回 alter database flashback on
------------------- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=7G scope=both;--------------------更改闪回区的大小
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arc1/
Oldest online log sequence 52
Next log sequence to archive 54
Current log sequence 54
SQL>
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 2G
SQL>
SQL> alter system set db_recovery_file_dest='/u01/oracle/flash_recovery_area';
System altered.
SQL>
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G
SQL>
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter database FLASHBACK ON
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 75499088 bytes
Database Buffers 205520896 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL>
SQL> alter database FLASHBACK ON;
Database altered.
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
YES
SQL>
SQL> create table trun_test tablespace users as select * from scott.emp;
Table created.
SQL> create table del_test tablespace users as select * from scott.emp;
Table created.
SQL> select count(*) from scott.emp;
COUNT(*)
----------
14
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
925204
SQL>
SQL> truncate table trun_test;
Table truncated.
SQL> delete del_test;
14 rows deleted.
SQL> commit;
Commit complete.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 79693392 bytes
Database Buffers 201326592 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> flashback database to scn 925204;
Flashback complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
注意----->resetlogs 仅在不完全数据库恢复后有效
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from trun_test;
COUNT(*)
----------
14
SQL> select count(*) from del_test;
COUNT(*)
----------
14
SQL>
恢复到1天以前
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1;
(前提flashback log 数据保留的时间内
恢复到自定义的存储点
存储点创建方法 SQL> create restore point abc;
查看 SQL> SELECT * FROM V$RESTORE_POINT;
删除 SQL> drop restore point abc;
SQL> flashback database to restore point abc;
Flashback complete.
SQL>
从oracle10g开始提供了flashback database。
Oracle为了实现这一功能,另外创建了一组日志,就是flashback logs(前滚日志)
日志记录数据库的前滚操作,并不是所有的改变都需要记录,
只记录一系列的前滚快照点,也就是一些时间点上块的前映像,
根据这些快照点,加上归档日志与联机日志,就可以恢复任何时间点上的记录了
使用flashback database,数据库必须处于归档状态
执行闪库必须在mount状态
闪回数据存储区
闪回需要产生闪回日志 以及改前数据映像
这些默认都是放到闪回恢复区的 这个区域的大小,位置,保持时间都由参数来决定
参数db_recover_file_dest 定义路径位置
参数db_recover_file_dest_size 定义区域大小 默认2G
参数db_flashback_retention_target定义保持力 默认1440分 就24小时
开启闪库
1.开启归档
2.闪回区位置设置正确路径和大小
db_recover_file_dest
db_recover_file_dest_size
3.激活数据库闪回 alter database flashback on
------------------- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=7G scope=both;--------------------更改闪回区的大小
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arc1/
Oldest online log sequence 52
Next log sequence to archive 54
Current log sequence 54
SQL>
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 2G
SQL>
SQL> alter system set db_recovery_file_dest='/u01/oracle/flash_recovery_area';
System altered.
SQL>
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G
SQL>
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter database FLASHBACK ON
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 75499088 bytes
Database Buffers 205520896 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL>
SQL> alter database FLASHBACK ON;
Database altered.
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
YES
SQL>
SQL> create table trun_test tablespace users as select * from scott.emp;
Table created.
SQL> create table del_test tablespace users as select * from scott.emp;
Table created.
SQL> select count(*) from scott.emp;
COUNT(*)
----------
14
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
925204
SQL>
SQL> truncate table trun_test;
Table truncated.
SQL> delete del_test;
14 rows deleted.
SQL> commit;
Commit complete.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 79693392 bytes
Database Buffers 201326592 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> flashback database to scn 925204;
Flashback complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
注意----->resetlogs 仅在不完全数据库恢复后有效
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from trun_test;
COUNT(*)
----------
14
SQL> select count(*) from del_test;
COUNT(*)
----------
14
SQL>
恢复到1天以前
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1;
(前提flashback log 数据保留的时间内
设置flashback log 数据保留的时间: SQL>alter system set db_flashback_retention_target=1440 scope=both;
如果你希望flashback database 能够恢复的最早的时间点。默认值是1440,单位是minute,即24 小时,需要注意的是该参数虽然未直接指定flash recovery area大小,但却受其制约,举个例子假如数据库每天有10%左右的数据变动的话,如果该初始化参数值设置为1440,则flash recovery area 的大小至少要是当前数据库实际容量的10%,如果该初始化参数设置为2880,则flash recovery area 的大小就至少是数据库所占容量的20%。)
恢复到自定义的存储点
存储点创建方法 SQL> create restore point abc;
查看 SQL> SELECT * FROM V$RESTORE_POINT;
删除 SQL> drop restore point abc;
SQL> flashback database to restore point abc;
Flashback complete.
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28602568/viewspace-758922/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28602568/viewspace-758922/