oracle误删除,oracle误更新等问题闪回解决

有时候,sql执行后会出现问题,导致需要回到执行前的时间点,这时就可以做闪回,可以会了不用,不能到用的时候不会。
首先,闪回有几种形式,闪回查询,闪回表,闪回事务,闪回数据库这4种。


闪回查询


无需配置,但一定程度上依赖UNDO_RETENTION时间与UNDO 表空间,可用适当配置和延长。可以查询到误操作时间点前的数据状态,举例。
建一个测试表tests

 执行一个错误更新,可以看到数据已被更新

update tests set NAMES='66' where moneys<=33

开始通过闪回查询恢复,在不确定准确的时间点的情况下直接执行,注意,两个单引号代表输出一个'

SELECT SQL_ID "SQLid", SQL_TEXT "SQL内容", LAST_LOAD_TIME "SQL时间", PARSING_SCHEMA_NAME "执行用户"
FROM V$SQL
WHERE SQL_TEXT LIKE '%update tests set NAMES=''66'' where moneys<=33%'
ORDER BY LAST_LOAD_TIME DESC;

 闪回查询, 获取误操作表误操作前的那个时间点的状态

SELECT * FROM tests AS OF TIMESTAMP TO_TIMESTAMP('2024-11-07 18:04:54', 'YYYY-MM-DD HH24:MI:SS');


加上where条件找到被更改的数据

SELECT * FROM tests AS OF TIMESTAMP TO_TIMESTAMP('2024-11-07 18:04:54', 'YYYY-MM-DD HH24:MI:SS') where moneys<=33;


这个时候可以还原到临时表或者直接还原回去,相当于只是执行了闪回查询,但还原依旧采取,更新,插入等方式来执行。所以分开看。

表闪回,事务闪回,数据库闪回查看是否可用
查看表闪回是否可用

检查表闪回是否可用


-- 如果 UNDO_RETENTION 设置的时间过短,可能会导致无法进行表闪回操作

SHOW PARAMETER UNDO_RETENTION;


-- 检查 UNDO 表空间大小,状态

SELECT TABLESPACE_NAME, STATUS, RETENTION FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO';

检查事务闪回是否可用


--如果 SUPPLEMENTAL_LOG_DATA_MIN 的值为 YES,则表示补充日志已开启,可以支持事务闪回。如果为 NO,则需要开启补充日志。

SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

检查数据库闪回是否可用


--检查数据库是否处于 ARCHIVELOG 模式

SELECT LOG_MODE FROM V$DATABASE;


-- 检查闪回日志是否开启 如果 FLASHBACK_ON 的值为 YES,表示数据库闪回日志已启用;如果为 NO,则说明未启用闪回数据库功能。

SELECT FLASHBACK_ON FROM V$DATABASE;

启用表闪回,事务闪回,数据库闪回

启用表闪回

ALTER SYSTEM SET UNDO_RETENTION = 3600;  -- 保留 1 小时的 UNDO 信息


-- 表闪回依赖于 UNDO 表空间。数据库必须配置有足够的 UNDO 表空间,并且保留了足够的 UNDO 信息。UNDO_RETENTION 参数需要设置较大的值,以确保在需要的时间点上有足够的 UNDO 数据支持闪回

启用事务闪回


--事务闪回同样依赖UNDO 表空间,所以同样需要配置

ALTER SYSTEM SET UNDO_RETENTION = 3600;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

启用数据库闪回


启用 ARCHIVELOG 模式

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;


启用闪回日志

ALTER DATABASE FLASHBACK ON;


设置闪回保留目标

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 1440; -- 保留24小时的闪回日志单位分钟(可避免占用存储空间过大)

执行闪回操作的信息收集


 找到错误sql执行的sqlID

SELECT SQL_ID "SQLid", SQL_TEXT "SQL内容", LAST_LOAD_TIME "SQL时间", PARSING_SCHEMA_NAME "执行用户"
FROM V$SQL
WHERE SQL_TEXT LIKE '%你的错误SQL语句的关键字%'
ORDER BY LAST_LOAD_TIME DESC;

根据上一步的SQLID获取对应的会话ID

SELECT S.SID "会话ID", S.SERIAL# "会话序列号", S.USERNAME "执行SQL用户", S.STATUS "会话状态", S.MACHINE "执行会话机器名"
FROM V$SESSION S
JOIN V$SQLAREA Q ON S.SQL_ID = Q.SQL_ID
WHERE Q.SQL_ID = '上一步找到的SQL_ID';

 根据会话ID获取事务ID

SELECT T.XIDUSN, T.XIDSLOT, T.XIDSQN, T.TRANSACTION_ID "完整事务ID"
FROM V$TRANSACTION T
JOIN V$SESSION S ON T.SES_ADDR = S.SADDR
WHERE S.SID = 会话SID
AND S.SERIAL# = 会话序列号;

 查看事务的详细信息

SELECT OPERATION "操作类型", UNDO_SQL "撤销SQL", TABLE_NAME "受影响的表", ROW_ID "受影响的行ID", COMMIT_TIMESTAMP "事务提交时间"
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = 'XIDUSN.XIDSLOT.XIDSQN';

执行闪回

闪回事务查询, 获取到 UNDO_SQL 后,可以将其逐条执行,完成对误操作的撤销。

SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '事务ID';

 闪回表,把整个表的全表数据状态,恢复到指定时间点的状态

FLASHBACK TABLE 表名 TO TIMESTAMP TO_TIMESTAMP('2024-11-07 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

执行数据库闪回


执行闪回数据库操作前,需要将数据库关闭并重新启动到 MOUNT 状态

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;


执行闪回数据库操作

FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2024-11-07 10:00:00', 'YYYY-MM-DD HH24:MI:SS');


按系统更改号(SCN)闪回

FLASHBACK DATABASE TO SCN 12345678;


打开数据库并重置日志

ALTER DATABASE OPEN RESETLOGS;

PS:忘了写一些闪回后的检查事项了,这里补充

闪回后检查


索引

命名索引不会影响,但如果索引是系统生成的临时名称,可能会遇到命名上的冲突或恢复过程中的名称不一致的情况,需检查。


约束

主键、唯一约束、检查约束不会受影响,外键约束若配置了级联更新,级联删除也不会影响,若无配置,需检查。闪回表操作不会自动处理与其他表之间的外键关系,所以在涉及外键的表上进行闪回时,可以采用多表联合闪回或依次闪回。如

FLASHBACK TABLE CUSTOMERS, ORDERS TO TIMESTAMP TO_TIMESTAMP('2024-11-07 10:00:00', 'YYYY-MM-DD HH24:MI:SS');


触发器

如果表闪回前触发器被禁用,后面启用了,执行闪回操作后触发器会回到禁用的状态
列的默认值,闪回表会将列的默认值恢复到闪回时间点的状态。如果在闪回时间点之前修改了列的默认值,那么闪回后将恢复为原来的默认值。

就补充这些,后面有想起来的可用继续补充。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

cc灵风

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值