Oracle 11g数据库之闪回与回收站

文章由LinuxBoy分享于2019-03-30 03:03:59热评（654）

Oracle 11g数据库之闪回与回收站

闪回技术通常用于快速简单恢复数据库中出现的认为误操作等逻辑错误，从闪回的方式可以分为基于数据库闪回、表闪回、事务闪回，根据闪回对数据的影响程度又可以分为闪回恢复，闪回查询。闪回恢复将修改数据，闪回点之后的数据将全部丢失。而闪回查询则可以查询数据被DML的不同版本，也可以在此基础之上确定是否进行恢复。

1.查看闪回是否启用
SQL> select log_mode,open_mode,flashback_on from v$database;
LOG_MODE OPEN_MODE FLASHBACK_ON
------------ -------------------- ------------------
NOARCHIVELOG READ WRITE NO --NO表示闪回没有开启
SQL>

2.查看闪回目录和闪回的大小
SQL> show parameter db_recovery;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/Oracle/app/oracle/flash_
recovery_area
db_recovery_file_dest_size big integer 3852M
SQL>

3.查看闪回的生存周期
SQL> show parameter db_flashback; --默认是分钟
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL>

4.开启闪回
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1: --出现错误,是因为数据库是OPEN状态，必须修改为挂载状态
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
SQL> select status from v$instance; --查看是OPEN状态
STATUS
------------
OPEN
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 661209088 bytes
Fixed Size 1338560 bytes
Variable Size 478151488 bytes
Database Buffers 176160768 bytes
Redo Buffers 5558272 bytes
Database mounted.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database archivelog; --必须启动归档才可以使用闪回
Database altered.
SQL> alter database flashback on; --启动闪回成功
Database altered.
SQL> select * from v$flashback_database_log; --查看闪回日志
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
1361192 14-FEB-14 1440 8192000 589824
SQL> select * from v$flashback_database_stat; --查看闪回状态
BEGIN_TIM END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
14-FEB-14 14-FEB-14 16384 0 0 0
SQL> ho ls -hlt $ORACLE_BASE/flash_recovery_area/ORCL/flashback --查看闪回的日志
total 7.9M
-rw-r----- 1 oracle oinstall 7.9M Feb 14 11:56 o1_mf_9hv4zfp5_.flb
SQL>

5.基于scn来闪回数据
SQL> select * from t1;
no rows selected
SQL> insert into t1 values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
A
----------
2
SQL> select sysdate,timestamp_to_scn(sysdate) from dual;
SYSDATE TIMESTAMP_TO_SCN(SYSDATE)
--------- -------------------------
14-FEB-14 1385810
SQL> delete from t1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from t1;
no rows selected
SQL> flashback table t1 to scn 1385810; --闪回到scn为1385810
Flashback complete.
SQL> select * from t1; --数据找回来了
A
----------
2
SQL>

6.基于闪存点来闪回数据
SQL> select * from t1;
A
----------
2
SQL> create restore point abc; --创建闪回保存点
Restore point created.
SQL> insert into t1 values(3);
1 row created.
SQL> select * from t1;
A
----------
2
3
SQL> flashback table t1 to restore point abc; --还原到abc保存点
Flashback complete.
SQL> select * from t1;
A
----------
2
SQL>

7.闪回表删除(只能针对普通用户)
SQL> drop table t1; --删除表
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
Q BIN$8la0lsFgu07gQAICcgITjQ==$0 TABLE 2014-02-14:13:03:00
T1 BIN$8lkqPmU58tXgQAICcgIYKw==$0 TABLE 2014-02-14:14:46:26
TONG BIN$8laD8n7xgCzgQAICcgIRvg==$0 TABLE 2014-02-14:12:48:21
TONG BIN$8NsWmNwAEmLgQAICcgIUNA==$0 TABLE 2014-01-26:14:56:19
SQL> select * from t1; --表不存在
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> flashback table t1 to before drop; --恢复t1表和表中的数据(flashback table t1 to before drop rename to t2 --恢复重命名表)
Flashback complete.
SQL> select * from t1;
A
----------
2
SQL>

推荐文章：

Oracle 11g数据库之闪回与回收站