Oracle的flashback database比起RMAN或者其他的不完全恢复,是简单多了。
对数据库的要求除了和其他不完全恢复一样要求archive log模式外,还需要设置flashback on。
对于恢复truncate table的误操作,目前好像只有回滚整个数据库,没有其他更简单的办法。
[@more@]1. Flashback Database
1. Introduction
Flashback Database is implemented using a new type of log file called the Flashback Database log. The Oracle database server periodically logs before images of data blocks in the Flashback Database logs. The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.
A new RVWR background process writes Flashback Database data to the Flashback Database logs.
For instance “grid”:
$ ps -ef | grep grid
oracle 25124 1 0 16:32:05 ? 0:00 ora_s000_grid
oracle 25116 1 0 16:32:04 ? 0:00 ora_reco_grid
oracle 25169 1 0 16:32:22 ? 0:00 ora_rvwr_grid
oracle 25112 1 0 16:32:04 ? 0:00 ora_ckpt_grid
…………
2. Enabling Flashback Database
1. Make sure the database is in archive mode.
SQL>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
2. Configure the recovery area by setting the two parameters:
- DB_RECOVERY_FILE_DEST
- DB_RECOVERY_FILE_DEST_SIZE
SQL>show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string C:oracleproduct10.1.0flash_recovery_area
db_recovery_file_dest_size big integer 2G
3. Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature:
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE FLASHBACK ON;
4. Set the Flashback Database retention target:
- DB_FLASHBACK_RETENTION_TARGET
5. Determine if Flashback Database is enabled
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------
YES
3. Disabling Flashback Database
SQL> ALTER DATABASE FLASHBACK OFF;
4. Monitoring Flashback Database
- Monitor logging in the Flashback Database logs:
SQL> select begin_time, flashback_data,
2 db_data, redo_data, ESTIMATED_FLASHBACK_SIZE
3 from v$flashback_database_stat;
BEGIN_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
-------------------- -------------- ---------- ---------- ------------------------
Feb 22 2004 01:05:14 147456 2719744 92160 0
Feb 22 2004 00:05:09 3891200 5857280 2537984 252788736
Feb 21 2004 23:05:04 7979008 13615104 3385344 254877696
- Monitor the Flashback Database retention target:
SQL> select *
2 from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE
ESTIMATED_FLASHBACK_SIZE
-------------------- --------------------- ---------------- -------------- -----
-------------------
2.2029E+12 Oct 06 2003 09:44:42 1440 48316416
21774336
- Adjust recovery area disk quota:
SQL> select estimated_flashback_size from v$flashback_database_log;
ESTIMATED_FLASHBACK_SIZE
------------------------
21823488
5. Example
ZHYUH @ orcl>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST1 TABLE
TEST2 TABLE
ZHYUH @ orcl>select * from test1;
COL1
----------
dfa
ZHYUH @ orcl>select * from test2;
COL1
----------
34245
ZHYUH @ orcl>select to_char(sysdate,'mmddyyyy hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'
-----------------
09092004 11:26:21
ZHYUH @ orcl>truncate table test1;
Table truncated.
ZHYUH @ orcl>drop table test2 purge;
Table dropped.
ZHYUH @ orcl>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST1 TABLE
ZHYUH @ orcl>select * from test1;
no rows selected
Now, we try to flashback to timestamp “09092004 11:26:21”
SQL>select OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,'mmddyyyy hh24:mi:ss'), RETENTION_TARGET,FLASHBACK_SIZE,ESTIMATED_FLASHBACK_SIZE from v$flashback_database_log;
OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- ----------------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
654057 09092004 10:41:38 1440 8192000
0
SYS @ orcl>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ orcl>startup mount exclusive;
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
SYS @ orcl>flashback database to timestamp(to_date('09092004 11:26:21','mmddyyyy hh24:mi:ss'));
Flashback complete.
SYS @ orcl>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS @ orcl>alter database open resetlogs;
Database altered.
Check the flashback result:
SYS @ orcl>connect zhyuh/zhyuh
Connected.
ZHYUH @ orcl>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST1 TABLE
TEST2 TABLE
ZHYUH @ orcl>select * from test1;
COL1
----------
dfa
ZHYUH @ orcl>select * from test2;
COL1
----------
34245
Recovery is successful!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-778682/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/207/viewspace-778682/