flashback系列文章三(flashback database)
需要首先開啟flashback_on,把資料庫啟動到mount狀態
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> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> alter database open;
Database altered.
類似於redo log,在啟用flashback database之後oracle會啟用rvwr程式,把undo寫入到閃回日誌檔案中
[oracle@rac1 ~]$ ps -ef|grep rvwr|grep -v grep
oracle 7421 1 0 16:28 ? 00:00:00 ora_rvwr_dcits
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 0
[oracle@rac1 flashback]$ date
Tue Dec 31 17:58:38 CST 2013
[oracle@rac1 flashback]$ pwd
/u01/app/oracle/flash_recovery_area/DCITS/flashback
[oracle@rac1 flashback]$ ll
total 8020
-rw-r----- 1 oracle oinstall 8200192 Dec 31 17:56 o1_mf_9d501fd4_.flb
可見flashback設定成on之後已經生成了一個flashback日誌了
db_flashback_retention_target引數以分鐘為單位,定義了資料庫能夠閃回的時間上限。
SQL> show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
建立個測試表,記錄下時間和scn,等下用timestamp和scn恢復下資料庫
SQL> conn scott/tiger
Connected.
SQL> create table test(id number);
Table created.
SQL> insert into test values(100);
1 row created.
SQL> insert into test values(200);
1 row created.
SQL> commit;
Commit complete.
SQL> select to_char(sysdate,;yyyy-mm-dd hh24:mi:ss') from dual;
ERROR:
ORA-01756: quoted string not properly terminated
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2013-12-31 22:06:20
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
835057
SQL> drop table test;
Table dropped.
下面我們分別用時間戳和scn進行恢復資料庫
SQL> conn sys/oracle as sysdba
Connected.
SQL> shutdown 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 71304784 bytes
Database Buffers 209715200 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> flashback database to scn 835057;
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> conn scott/tiger
Connected.
SQL> select * from test;
ID
----------
100
200
可以看見刪掉的表被恢復了。
下面類似的透過timestamp來flashback database
SQL> conn sys/oracle as sysdba
Connected.
SQL> shutdown 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 71304784 bytes
Database Buffers 209715200 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('2013-12-31 22:06:20','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> conn scott/tiger
Connected.
SQL> select * from test;
ID
----------
100
200
如果確認恢復完畢了,使用resetlogs重新啟動下資料庫
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 71304784 bytes
Database Buffers 209715200 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26613085/viewspace-1066111/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【Flashback】Flashback Database閃回資料庫功能實驗Database資料庫
- oracle 10g flashback databaseOracle 10gDatabase
- [20210722]ORA-38760與flashback database.txtDatabase
- 【FLASHBACK】Oracle flashback data archive 介紹OracleHive
- Flashback database必須要有之前的archivelog嗎?DatabaseHive
- 在Oracle DG Standby庫上啟用flashback database功能OracleDatabase
- ORACLE 閃回檢視v$flashback_database_log/statOracleDatabase
- 【Flashback】Flashback Drop閃回刪除功能實驗
- Flashback Query(轉)
- 2.6.2 Overview of Flashback PDB in a CDBView
- ORACLE Flashback Query偽列Oracle
- flashback query閃回資料
- Flashback Data Archive原理詳解Hive
- Oracle 備份恢復之 FlashbackOracle
- flashback實現資料快速復原
- 用flashback恢復儲存過程儲存過程
- guarantee restore points-Flashback after RMAN restoreREST
- [20181002]DBMS_FLASHBACK與函式.txt函式
- Oracle閃回技術--Flashback Version QueryOracle
- [20180424]開啟表空flashback on.txt
- Flashback Drop閃回刪除功能實踐
- [20180724]Flashback query和子游標共享.txt
- 基於flashback_scn的expdp匯出
- ORA-55507: Encountered mining error during Flashback Transaction Backout. functiError
- C++ 未初始化記憶體出現 flashbackC++記憶體
- 【FLASHBACK】Oracle閃回及回收站相關語句參考Oracle
- 螢幕錄影機(bb flashback pro 4)pjb v4.1.21
- Oracle 12.2新特性: PDB級閃回資料庫(Flashback PDB)Oracle資料庫
- GeminiDB Cassandra介面新特性FLASHBACK釋出:任意時間點秒級閃回
- 刪使用者刪表空間的操作還能flashback回來嗎?
- TiKV 原始碼解析系列文章(三)Prometheus(上)原始碼Prometheus
- Grafana 系列文章(三):Tempo-使用 HTTP 推送 SpansGrafanaHTTP
- 推薦系統FM系列文章(三)-- NFM模型模型
- MaterialDesign系列文章(三)過渡動畫的實現動畫
- Git 系列文章Git
- 高可用系列文章之三 - NGINX 高可用實施方案Nginx
- openGauss資料庫原始碼解析系列文章——openGauss簡介(三)資料庫原始碼
- 徹底搞懂 etcd 系列文章(三):etcd 叢集運維部署運維
- Grafana 系列文章(十五):ExemplarsGrafana