閃回數據庫

• Flashbacking a database means going back to a previous database state.閃回數據庫到之前數據庫的狀態
• The Flashback Database feature provides a way to quickly revert entire Oracle database to the state it was in at a past point in time. 閃回數據庫特性提供了一種快速恢復數據庫到過去一個時間點的方法 后臺進程RVWR把數據塊的前映像寫入到閃回日志中
• A new background process RVWR introduced which is responsible for writing flashback logs which stores pre-image(s) of data blocks 后臺進程RVWR把數據塊的前映像寫入到閃回日志中
• One can use Flashback Database to back out changes that:
  • Have resulted in logical data corruptions.
  • Are a result of user error.
• This feature is not applicable for recovering the database in case of media failure. 不適用于介質恢復

配置閃回數據庫

Prerequisite

a) Database must be in archivelog mode.
b) Last clean shutdown.

Steps

• 1. Configure the following parameters in parameter file(init.ora) or spfile

參數	屬性	解釋
DB_RECOVERY_FILE_DEST	dynamically modifiable	Physical location where RVWR background process writes flashback logs
DB_RECOVERY_FILE_DEST_SIZE	dynamically modifiable	Maximum size flashback logs can occupy in
DB_RECOVERY_FILE_DESTDB_FLASHBACK_RETENTION_TARGET	dynamically modifiable	Upper limit in minutes on how far back one can flashback the database

• Example:
  1. \**db_recovery_file_dest參數為0時，不能設定flash_recovery_area參數*\
  2. SQL> alter system set db_recovery_file_dest_size=2147483648 scope=spfile;
  3. SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' scope=spfile;
  4. SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=2880; (2 days) |
• 2. Turn flashback on:
  1. SQL> Startup mount exclusive;
  2. SQL> alter database archivelog;
  3. SQL> Alter database flashback on;
  4. SQL> Alter database open; |
• 3. Check status
  1. SQL> SELECT flashback_on, log_mode FROM gv$database;
  2. ????SQL>?SELECT?estimated_flashback_size?FROM?gv$flashback_database_log;
  3. $ ps -eaf | grep rvwr |

禁用Flashback Database

1. Alter database flashback on;

Flashback Database Using SQL or RMAN Commands

1.使用SQLPLUS

• Use an SCN or a time stamp in the SQL version
• Example: Flash back the database to a day before using SQL
  1. SQL> shutdown immediate;
  2. SQL> startup mount exclusive;
  3. SQL> flashback database to timestamp(sysdate-1);
  4. SQL> alter database open resetlogs;

2.使用 RMAN

• Using RMAN, you can flash back to a time stamp, SCN, or log sequence number (SEQUENCE) and thread number (THREAD).
• Example:
  1. RMAN> FLASHBACK DATABASE TO TIME = TO_DATE('2002-12-10 16:00:00','YYYY-MM-DD HH24:MI:SS');
  2. RMAN> FLASHBACK DATABASE TO SCN=23565;
  3. RMAN> FLASHBACK DATABASE TO SEQUENCE=223 THREAD=1;

視圖

• **VFLASHBACKDATABASELOG???monitortheestimatedandactualsizeoftheflashbacklogsintheflashrecovery?Checkflashrecoveryareadiskquota:‘‘‘SQL>selectretentiontarget,flashbacksize,estimatedflashbacksizeFROMVFLASHBACK_DATABASE_LOG;
  1. - Determine the current flashback window:
  SQL> SELECT oldest_flashback_scn,oldest_flashback_time FROM VFLASHBACKDATABASELOG;‘‘‘???VFLASHBACK_DATABASE_STAT -** monitors the overhead of logging flashback data in the flashback logs. It contains at most 24 rows, with one row for each of the last 24 hours.
  • The flashback generation for the last hour:
    1. SQL> select to_char(end_time,'yyyy-mm-dd hh:miAM') end_timestamp, flashback_data, db_data, redo_data from v$flashback_database_stat where rownum=1;

從Flashback Database中排除表空間

1. SQL> ALTER TABLESPACE <ts_name> FLASHBACK {ON|OFF}
2. SQL> SELECT name, flashback_on 2 FROM v$tablespace;

Note

• Take the tablespace offline before you perform the database flashback operation.
• After performing Flashback Database, drop the tablespace or recover the offline files with traditional point-in-time recovery.

Flash back a RESETLOGS operation

可以閃回到resetlogs之前的一個時間點

1. SQL> FLASHBACK DATABASE TO BEFORE RESETLOGS;

Limitations

• You cannot use Flashback Database in the following situations:

• The control file has been restored or re-created.

• A tablespace has been dropped.
• A data file has been shrunk
• 需要開啟FRA特性

---

閃回數據庫的一些命令:

1. \**閃回到指定的日期**\
2. FLASHBACK DATABASE TO TIMESTAMP(to_data('2015.07.11 20:03:00','YYYY.MM.DD HH24:MI:SS'));
4. \**閃回到5分鐘之前**\
5. FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
6. FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
8. \**回退到過去的某個SCN點**\
9. FLASHBACK DATABASE TO SCN my_scn;
10. FLASHBACK DATABASE TO BEFORE SCN my_scn;
12. \**回退到某個Log Sequence號*\
13. flashback database to sequence=223 thread=1;
15. \**回退完成之后以resetlogs的方式打開數據庫*\
16. alter database open resetlogs;

---

1. \**也可以創建一個恢復點*\
2. CREATE RESTORE POINT before_changes;
3. FLASHBACK DATABASE TO RESTORE POINT before_changes;

---

來自為知筆記(Wiz)