oracle闪回技术

一、闪回技术配置
1、oracle 处于归档模式
2、用 alter database flashback on 开启闪回

1）配置oracle运行的模式
在sqlplus下以sys用户运行archive log list 命令

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 481
Current log sequence 483
SQL>
database log mode 显示为No Archive Mode 证明数据库不处于归档模式，所以要开启归档模式，但是要开启归档模式，数据库必须要处于mount状态，因此要关闭数据库，重启数据库到mount状态
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 722366464 bytes
Fixed Size 2216864 bytes
Variable Size 511708256 bytes
Database Buffers 205520896 bytes
Redo Buffers 2920448 bytes
Database mounted.
SQL> alter database archivelog ;
Database altered.
现在输入archivelog list命令可以查看到
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 483
Next log sequence to archive 485
Current log sequence 485
SQL>
Database log mode Archive Mode 证明数据库已经处于归档模式
注* alter database archivelog 是在oracle mount状态下使用
2）开启闪回
直接在mount状态执行alter database flashback on; 命令
SQL> alter database flashback on;
Database altered.
修改完之后可以在vdatabase里面查看运行模式和是否开启了闪回SQL>selectLOGMODE,FLASHBACKONfromvdatabase ;
LOG_MODE FLASHBACK_ON

---

ARCHIVELOG YES
SQL>
注*该命令也可以在oracle open状态下执行

二、闪回命令使用
1、闪回数据库
sql: 1、flashback database to timestamp (sysdate-1/24);–闪回一个小时
2、flashback database to scn 53943;
3、flashback database to restore point b4_load;

SQL> conn scott/oracle
Connected.
SQL> select * from t2;
no rows selected
SQL> show user;
USER is “SCOTT”
SQL> drop table t2; ———–册除表
Table dropped.
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate —-关闭数据库
ORACLE instance shut down.
SQL> startup mount;——-开启数据到mount状态
ORACLE instance started.
Total System Global Area 722366464 bytes
Fixed Size 2216864 bytes
Variable Size 511708256 bytes
Database Buffers 205520896 bytes
Redo Buffers 2920448 bytes
Database mounted.
SQL> flashback database to timestamp (sysdate-1/144);—–闪回到十分钟前
Flashback complete.
SQL> alter database open read only ; —–开启数据只读验证数据
Database altered.
SQL> select * from t2;
select * from t2
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn scott/oracle
Connected.
SQL> select * from t2;——-t2表已经恢复
no rows selected
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 722366464 bytes
Fixed Size 2216864 bytes
Variable Size 511708256 bytes
Database Buffers 205520896 bytes
Redo Buffers 2920448 bytes
Database mounted.
SQL> alter database open resetlogs ;——–开启数据库并且重置日志
Database altered.
SQL> conn scott/oracle
Connected.
SQL> select* from t2;
no rows selected
SQL>

注*此例权是一个简单的举例，在实际中不可能因为一个表的数据被删或者表被drop而回滚数据库
不能使用闪回数据库例子
1、 数据库文件被删除
2、当控制文件被标志为脱节
3、 数据库被删除
4 、当数据库data file 被reset之后，只可以闪回reset之后的操作