Flasback Database 的配置與演示
設定必要條件:
資料庫處於歸檔狀態:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 71305460 bytes
Database Buffers 92274688 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter system set log_archive_dest_1='location=/u01/orcl_arch';
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/orcl_arch
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
啟動Flashback Database
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
設定FRA(Flash Recover area)
SQL> show parameter db_recover;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
SQL> !mkdir /u01/FRA
SQL> alter system set db_recovery_file_dest='/u01/FRA';
System altered.
SQL> alter system set db_recovery_file_dest_size=5G;
System altered.
SQL> show parameter db_recover;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/FRA
db_recovery_file_dest_size big integer 5G
DB_FLASHBACK_RETENTION_TARGET用來設定控制Flashback logs的保留時間
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
Oracle還建議在閃回資料庫中啟用Force Logging模式
注:該模式會強制所有日誌寫入重做日誌,否則容易造成部分資料無法恢復
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
Flashback Database 演練:
SQL> conn scott/oracle
Connected.
SQL> create table emp_test as select * from emp;
Table created.
測試前先檢視一下SCN
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
521620
刪除一個表做下測試:
SQL> drop table emp_test purge;
Table dropped.
連線到SYS使用者開始閃回資料庫恢復:
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 71305460 bytes
Database Buffers 92274688 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> flashback database to scn 521620;
Flashback complete.
閃回資料庫需要重做日誌模式開啟資料庫,如果想要指定SCN之後的資料不丟失,可以Read Only開啟資料庫,將誤操作的資料匯出,再將資料庫進行恢復到未執行Flashback Database前的狀態,將資料邏輯匯入即可。
SQL> alter database open resetlogs;
Database altered.
SQL> conn scott/oracle
Connected.
SQL> select tname from tab;
TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
EMP_TEST
DEPT_TEST
6 rows selected.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29320885/viewspace-1124968/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 演示:配置安全的shell屬性
- Oracle 資料庫重放(Database Replay)功能演示Oracle資料庫Database
- 配置Flashback DatabaseDatabase
- PySpark DataFrame教程與演示Spark
- Logical Standby Database的配置步驟.Database
- 【差異】LENGTH與VSIZE的區別演示
- oracle 11g Oracle Database Vault 的配置方法OracleDatabase
- 【Database】可選的資料來源配置項Database
- Data Guard - Snapshot Standby Database配置Database
- recover database using backup controlfile與 recover database 的區別Database
- demo演示如何寫一個無配置格式統一的日誌
- mysql下載與安裝 視訊演示MySql
- ORACLE10G DG配置下Physical Standby Database的管理OracleDatabase
- go演示工具 - go 社群是如何做演示的?Go
- Oracle Database 12.2本機配置ADGOracleDatabase
- ORACLE10g DataGuard 配置Physical Standby DatabaseOracleDatabase
- 演示PPT文件時常出現的問題與解決
- 遺留程式碼處理技巧與案例演示
- Emacs 系列(五):Org 模式之文件與演示稿Mac模式
- jive配置中的database問題?!請幫忙阿謝謝!Database
- 演示:思科IPS感測器的命令列初始配置(支援圖型化管理)命令列
- alter database drop datafile 與 drop tablespace file 的區別Database
- ALTER DATABASE 與 ALTER TABLESPACE OFFLINE的區別Database
- Flashback Database特性常見問題的問與答Database
- 詳解OpenCV For Java環境搭建與功能演示OpenCVJava
- Log4j使用總結與例項演示
- Autotrace的配置與分析
- ASM的配置與管理ASM
- alter database offline 與 alter database offline drop效果比對Database
- Cobalt Strike 之團隊伺服器的搭建與DNS通訊演示伺服器DNS
- NEO外掛錢包方案演示——安全與便捷的藝術統一
- MySQL鎖之三:MySQL的共享鎖與排它鎖編碼演示MySql
- WAS與IHS整合的安裝與配置
- redis的mq功能演示RedisMQ
- .NET開發框架(一)-框架介紹與視訊演示框架
- 專注視覺思維與演示設計-孫小小視覺
- C++中dynamic_cast與static_cast淺析與例項演示C++AST
- Ansible的原理與配置