有时候,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');
触发器
如果表闪回前触发器被禁用,后面启用了,执行闪回操作后触发器会回到禁用的状态
列的默认值,闪回表会将列的默认值恢复到闪回时间点的状态。如果在闪回时间点之前修改了列的默认值,那么闪回后将恢复为原来的默认值。
就补充这些,后面有想起来的可用继续补充。