[size=large][b][color=brown]工作中对数据库的操作经常有误操作,最近研究学习下了oracle提供的各种闪回功能,总结成笔记[/color][/b][/size]
[color=indigo][b][size=medium]1.闪回删除(表级--drop)[/size]
当误drop某张表时,可以进行闪回删除操作,恢复删除这张表之前的状态[/b][/color]
[color=indigo][b][size=medium]2.闪回表(行级--insert、update、delete)[/size]
(一个时间戳对应一个scn点)
方法一:闪回到scn点处
当对一个表进行了错误的增删改等操作,可以进行闪回表操作,恢复增删改之前的数据状态
--1.查询某一时间点的时间戳,对应的SCN码,(SCN系统改变号)[/b][/color]
[color=indigo][b]--2.要闪回哪张表,对该表开启行移动,如表tbinstext[/b][/color]
[color=indigo][b]--3.执行闪回表操作,恢复表到对应的时间戳状态[/b][/color]
[color=indigo][b]方法二:直接返回到表在某一个时间戳的状态
--1.要闪回哪张表,对该表开启行移动,如表tbinstext[/b][/color]
[color=indigo][b]--2.执行闪回表操作,恢复表到对应的时间戳状态[/b][/color]
[size=medium][b][color=indigo]3.闪回查询[/color][/b][/size]
[color=indigo][b]想要查询某个时间点,某个表中的数据[/b][/color]
[size=medium][color=indigo][b]4.闪回版本查询[/b][/color][/size]
[color=indigo][b]可以查询两个时间点或两个scn之间的所有行的所有版本
方法一:查询最大时间戳和最小时间戳之间的所有版本[/b][/color]
[color=indigo][b]方法二:查询两个时间戳之间的所以版本[/b][/color]
[color=indigo][b]方法三:查询两个scn之间的所有版本[/b][/color]
[size=medium][color=indigo][b]5.闪回事务(事务级--transation)[/b][/color][/size]
[color=indigo][b]flashback_transaction_query视图,存放了所有事务的操作,可以根据事务id进行闪回事务查询,
得到undo_sql,执行undo_sql,来恢复这个事务造成的更改
--1.进行闪回版本查询,得到要闪回的事务id[/b][/color]
[color=indigo][b]--2.通过xid,查询flashback_transaction_query,得到undo_sql[/b][/color]
[color=indigo][b]--3.执行获取到的undo_sql,来恢复错误事务造成的更改[/b][/color]
[size=medium][color=indigo][b]6.闪回数据库(数据库级)[/b][/color][/size]
--1.用sqlplus登录(以管理员权限登录,只有管理员才有权限闪回数据库)
--2.配置闪回数据库(闪回数据库,要在归档模式,独占模式下装载数据库,才能执行闪回)
--确定实例是否为归档模式
数据库日志模式 非归档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 1171
当前的日志序列 1173
--将数据库改为归档模式下运行,设为独占模式启动,并启用闪回功能
--要先关闭数据库,才能配置闪回数据库配置
数据库已经关闭
数据库已经卸载
ORACLE例程已经关闭
--以mount和exclusive模式启动
ORACLE例程已经启动
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 88081064 bytes
Database Buffers 197132288 bytes
数据库装载完毕
--改为归档模式
数据库已经更改
--设置参数DB_FLASHBACK_RETENTION_TARGET为希望的值,改值的单位为分钟
本例设置为3天,1440*3=4320
系统已经更改
--启动闪回数据哭
数据库已更改
--将数据库设置为open状态
数据库已更改
--查看更改后的参数
数据库日志模式 归档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 1171
下一个存档日志序列 1173
当前日志序列 1173
2.闪回数据库
--关闭数据库
--在mount模式下启动数据库
--使用flashback database命令闪回数据
或者
SQL>
--用resetlogs选项打开数据库,因为要恢复到当前数据库之前的一个时刻
[color=indigo][b][size=medium]1.闪回删除(表级--drop)[/size]
当误drop某张表时,可以进行闪回删除操作,恢复删除这张表之前的状态[/b][/color]
flashback table tbinstext to before drop
[color=indigo][b][size=medium]2.闪回表(行级--insert、update、delete)[/size]
(一个时间戳对应一个scn点)
方法一:闪回到scn点处
当对一个表进行了错误的增删改等操作,可以进行闪回表操作,恢复增删改之前的数据状态
--1.查询某一时间点的时间戳,对应的SCN码,(SCN系统改变号)[/b][/color]
select timestamp_to_scn(to_timestamp('2014-05-19 19:46:00','yyyy-mm-dd hh24:mi:ss:ff')) SCN from dual;
[color=indigo][b]--2.要闪回哪张表,对该表开启行移动,如表tbinstext[/b][/color]
alter table tbinstext enable row movement;
[color=indigo][b]--3.执行闪回表操作,恢复表到对应的时间戳状态[/b][/color]
flashback table tbinstext to scn 23405704;
[color=indigo][b]方法二:直接返回到表在某一个时间戳的状态
--1.要闪回哪张表,对该表开启行移动,如表tbinstext[/b][/color]
alter table tbinstext enable row movement;
[color=indigo][b]--2.执行闪回表操作,恢复表到对应的时间戳状态[/b][/color]
flashback table tbinstext to timestamp to_timestamp('2014-05-19 19:46:00','yyyy-mm-dd hh24:mi:ss:ff');
[size=medium][b][color=indigo]3.闪回查询[/color][/b][/size]
[color=indigo][b]想要查询某个时间点,某个表中的数据[/b][/color]
select * from tbinstext as of timestamp to_timestamp('2014-05-24 16:45:00','yyyy-mm-dd hh24:mi:ss')
where deal_serial_no='';
[size=medium][color=indigo][b]4.闪回版本查询[/b][/color][/size]
[color=indigo][b]可以查询两个时间点或两个scn之间的所有行的所有版本
方法一:查询最大时间戳和最小时间戳之间的所有版本[/b][/color]
select deal_serial_no,--表tbinstext中的字段
user_id, --表tbinstext中的字段
versions_operation 操作,
versions_starttime 起始时间,
versions_endtime 结束时间,
versions_xid 事务号
from tbinstext versions between timestamp minvalue and maxvalue
order by 1;
[color=indigo][b]方法二:查询两个时间戳之间的所以版本[/b][/color]
select deal_serial_no,
user_id,
versions_operation 操作,
versions_starttime 起始时间,
versions_endtime 结束时间,
versions_xid 事务号
from tbinstext versions between timestamp to_timestamp('2014-05-25 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
and to_timestamp('2014-05-25 12:12:00', 'yyyy-mm-dd hh24:mi:ss');
[color=indigo][b]方法三:查询两个scn之间的所有版本[/b][/color]
select deal_serial_no,
user_id,
versions_operation 操作,
versions_starttime 起始时间,
versions_endtime 结束时间,
versions_xid 事务号
from tbinstext versions between scn 23746725 and 23747089;
[size=medium][color=indigo][b]5.闪回事务(事务级--transation)[/b][/color][/size]
[color=indigo][b]flashback_transaction_query视图,存放了所有事务的操作,可以根据事务id进行闪回事务查询,
得到undo_sql,执行undo_sql,来恢复这个事务造成的更改
--1.进行闪回版本查询,得到要闪回的事务id[/b][/color]
select deal_serialno, user_id, versions_operation 操作, versions_starttime 起始时间,
versions_endtime 结束时间, versions_xid 事务号
from tbinstext
versions between timestamp minvalue and maxvalue order by 事务号;
[color=indigo][b]--2.通过xid,查询flashback_transaction_query,得到undo_sql[/b][/color]
select operation,undo_sql
from flashback_transaction_query
where xid='03001E0093250000';
[color=indigo][b]--3.执行获取到的undo_sql,来恢复错误事务造成的更改[/b][/color]
[size=medium][color=indigo][b]6.闪回数据库(数据库级)[/b][/color][/size]
--1.用sqlplus登录(以管理员权限登录,只有管理员才有权限闪回数据库)
SQL>sqlplus sys/hs123 as sysdba
--2.配置闪回数据库(闪回数据库,要在归档模式,独占模式下装载数据库,才能执行闪回)
--确定实例是否为归档模式
SQL>archive log list;
数据库日志模式 非归档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 1171
当前的日志序列 1173
--将数据库改为归档模式下运行,设为独占模式启动,并启用闪回功能
--要先关闭数据库,才能配置闪回数据库配置
SQL>shutdown immediate;
数据库已经关闭
数据库已经卸载
ORACLE例程已经关闭
--以mount和exclusive模式启动
SQL>startup mount exclusive;
ORACLE例程已经启动
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 88081064 bytes
Database Buffers 197132288 bytes
数据库装载完毕
--改为归档模式
SQL>alter database archivelog;
数据库已经更改
--设置参数DB_FLASHBACK_RETENTION_TARGET为希望的值,改值的单位为分钟
本例设置为3天,1440*3=4320
SQL>alter system set db_flashback_retention_target=4320 scope=both;
系统已经更改
--启动闪回数据哭
SQL>alter database flashback on;
数据库已更改
--将数据库设置为open状态
SQL>alter database open;
数据库已更改
--查看更改后的参数
SQL>archive log list;
数据库日志模式 归档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 1171
下一个存档日志序列 1173
当前日志序列 1173
2.闪回数据库
--关闭数据库
[code="java"]SQL>shutdown immediate
[/code]
--在mount模式下启动数据库
SQL>startup mount
--使用flashback database命令闪回数据
SQL>flashback database to scn 23752574;
或者
SQL>
flashback database to timestamp to_timestamp('2014-05-25 14:30:00','yyyy-mm-dd hh24:mi:ss');
--用resetlogs选项打开数据库,因为要恢复到当前数据库之前的一个时刻
SQL>alter database open resetlogs;