一、闪回恢复
SQL> select * from t;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES WZ
60 DBA ZW
SQL> delete from t;
4 rows deleted.
SQL> commit;
Commit complete.
SQL> create table t_r as select * from t as of timestamp timestamp'2023-06-09 11:30:00';
Table created.
SQL> select * from t_r;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES WZ
60 DBA ZW
二、闪出数据库
1、准备
需要设置删除区和大小并开启闪回
SQL> alter system set db_recovery_file_dest_size=4g;
System altered.
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';
System altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------
NO
SQL> alter database flashback on;
Database altered.
SQL> show parameter db_reco
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_recovery_file_dest string
/u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer
4G
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------
YES
2、删除
SQL> select * from t;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES WZ
60 DBA ZW
SQL> delete from t;
4 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from t;
no rows selected
3、闪回数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 26594104 bytes
Variable Size 620756992 bytes
Database Buffers 1761607680 bytes
Redo Buffers 6959104 bytes
Database mounted.
SQL> flashback database to timestamp timestamp'2023-06-09 11:40:00';
SQL> alter database open resetlogs;
Database altered.
SQL> select * from t;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES WZ
60 DBA ZW
三、闪回表
1、环境
SQL> select * from t;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES WZ
60 DBA ZW
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2023-06-09 11:47:29
SQL> delete from t;
4 rows deleted.
SQL> commit;
Commit complete.
2、闪回表
SQL> flashback table t to timestamp timestamp'2023-06-09 11:47:29
2 ';
flashback table t to timestamp timestamp'2023-06-09 11:47:29
*
ERROR at line 1:
ORA-01882: timezone region not found
SQL> flashback table t to timestamp timestamp'2023-06-09 11:47:29';
flashback table t to timestamp timestamp'2023-06-09 11:47:29'
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SQL> alter table t enable rowmovement;
alter table t enable rowmovement
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> alter table t enable row movement;
Table altered.
SQL> flashback table t to timestamp timestamp'2023-06-09 11:47:29';
Flashback complete.
SQL> select * from t;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES WZ
60 DBA ZW
四、闪回drop
1、准备
SQL> show parameter recyclebin
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
recyclebin string
OFF
SQL> alter system set recyclebin=on;
alter system set recyclebin=on
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
SQL> alter system set recyclebin=on scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 26594104 bytes
Variable Size 620756992 bytes
Database Buffers 1761607680 bytes
Redo Buffers 6959104 bytes
Database mounted.
Database opened.
SQL>
2、查询回收站
SQL> select * from dba_recyclebin;
OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UN CAN_PU RELATED BASE_OBJECT PURGE_OBJECT SPACE
------------------------------ ---------------------------------------- ---------------------------------------- ------------------ -------------------------------------------------- ---------------------------------------- -------------------------------------- -------------------------------------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------ ---------- ----------- ------------ ----------
CY BIN$/a2pHmCjDSTgU8ncEKwO5w==$0 T DROP TABLE USERS 2023-06-09:14:43:33 2023-06-09:14:43:37 12452461 YES YES 78001 78001 78001 8
3、方法一
SQL> create table t1 as select * from "BIN$/a2pHmCjDSTgU8ncEKwO5w==$0";
Table created.
SQL> select * from t1;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES WZ
60 DBA ZW
4、方法二
SQL> flashback table "BIN$/a2pHmCjDSTgU8ncEKwO5w==$0" to before drop;
Flashback complete.
SQL> select * from t;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES WZ
60 DBA ZW
5、方法三
SQL> flashback table t to before drop rename to t3;
Flashback complete.
SQL> select * from t3;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES WZ
60 DBA ZW
FLASHBACK TABLE
[ schema. ] table
[, [ schema. ] table ]...
TO { { { SCN | TIMESTAMP } expr
| RESTORE POINT restore_point
} [ { ENABLE | DISABLE } TRIGGERS ]
| BEFORE DROP [ RENAME TO table ]
} ;
五、闪回事务
1、准备
SQL> delete from t3 where dname='DBA';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from t3;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES WZ
2、闪回事务
SQL> col VERSIONS_STARTTIME for a35
SQL> col VERSIONS_ENDTIME for a35
SQL> col VERSIONS_XID for a30
SQL> set lines 200
SQL>
SQL> select versions_starttime,versions_endtime, versions_xid, versions_operation, t3.* from t3 versions between timestamp minvalue and maxvalue order by versions_starttime;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VE DEPTNO DNAME LOC
----------------------------------- ----------------------------------- ------------------------------ -- ---------- ---------------------------- --------------------------
09-JUN-23 02.52.08 PM 1C000900030B0000 D 60 DBA ZW
09-JUN-23 02.52.08 PM 60 DBA ZW
30 SALES WZ
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
SQL> col undo_sql for a65
SQL> select xid,undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('&xid');
Enter value for xid: 1C000900030B0000
old 1: select xid,undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('&xid')
new 1: select xid,undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('1C000900030B0000')
XID UNDO_SQL
---------------- -----------------------------------------------------------------
1C000900030B0000
1C000900030B0000
undo_sql为空，需要开启附加日志
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> select * from t3;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES WZ
SQL> delete from t3 where deptno=30;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from t3;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
SQL> select versions_starttime,versions_endtime, versions_xid, versions_operation, t3.* from t3 versions between timestamp minvalue and maxvalue order by versions_starttime;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VE DEPTNO DNAME LOC
----------------------------------- ----------------------------------- ------------------------------ -- ---------- ---------------------------- --------------------------
09-JUN-23 02.52.08 PM 1C000900030B0000 D 60 DBA ZW
09-JUN-23 03.01.54 PM 17000C00EF0A0000 D 30 SALES WZ
09-JUN-23 03.01.54 PM 30 SALES WZ
20 RESEARCH DALLAS
09-JUN-23 02.52.08 PM 60 DBA ZW
10 ACCOUNTING NEW YORK
SQL> select xid,undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('&xid');
Enter value for xid: 17000C00EF0A0000
old 1: select xid,undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('&xid')
new 1: select xid,undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('17000C00EF0A0000')
XID UNDO_SQL
---------------- -----------------------------------------------------------------
17000C00EF0A0000 insert into "CY"."T3"("DEPTNO","DNAME","LOC") values ('30','SALES
','WZ');
3、恢复
SQL> insert into "CY"."T3"("DEPTNO","DNAME","LOC") values ('30','SALES','WZ');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t3;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES WZ
六、BBED恢复
1、环境
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid),t.* from t;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DEPTNO DNAME LOC
------------------------------------ ------------------------------------ ---------- ---------------------------- --------------------------
7 5083 50 BOSS A
7 5083 10 ACCOUNTING NEW YORK
7 5083 20 RESEARCH DALLAS
7 5083 30 SALES WZ
7 5083 60 BOSS zw
SQL> delete from t where dname='BOSS';
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from t;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES WZ
2、检查
BBED> set dba 7,5083
DBA 0x01c013db (29365211 7,5083)
BBED> map /v
File: /u01/app/oracle/oradata/ORCL/users01.dbf (7)
Block: 5083 Dba:0x01c013db
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub2 wrp2_kcbh @2
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[3], 72 bytes @44
struct kdbh, 14 bytes @124
ub1 kdbhflag @124
sb1 kdbhntab @125
sb2 kdbhnrow @126
sb2 kdbhfrre @128
sb2 kdbhfsbo @130
sb2 kdbhfseo @132
sb2 kdbhavsp @134
sb2 kdbhtosp @136
struct kdbt[1], 4 bytes @138
sb2 kdbtoffs @138
sb2 kdbtnrow @140
sb2 kdbr[5] @142
ub1 freespace[7946] @152
ub1 rowdata[90] @8098
ub4 tailchk @8188
BBED> p kdbr
sb2 kdbr[0] @142 8051
sb2 kdbr[1] @144 8025
sb2 kdbr[2] @146 8003
sb2 kdbr[3] @148 7988
sb2 kdbr[4] @150 7974
BBED> p *kdbr[0]
rowdata[77]
-----------
ub1 rowdata[77] @8175 0x3c
BBED> p *kdbr[1]
rowdata[51]
-----------
ub1 rowdata[51] @8149 0x2c
BBED> p * kdbr[2]
rowdata[29]
-----------
ub1 rowdata[29] @8127 0x2c
BBED> p *kdbr[3]
rowdata[14]
-----------
ub1 rowdata[14] @8112 0x2c
BBED> p * kdbr[4]
rowdata[0]
----------
ub1 rowdata[0] @8098 0x3c
3、恢复
BBED> modify /x 2c offset 8175
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle/oradata/ORCL/users01.dbf (7)
Block: 5083 Offsets: 8175 to 8191 Dba:0x01c013db
------------------------------------------------------------------------
2c020302 c1330442 4f535301 4102063b b7
<32 bytes per line>
BBED> modify /x 2c offset 8098
File: /u01/app/oracle/oradata/ORCL/users01.dbf (7)
Block: 5083 Offsets: 8098 to 8191 Dba:0x01c013db
------------------------------------------------------------------------
2c020302 c13d0442 4f535302 7a772c00 0302c11f 0553414c 45530257 5a2c0003
02c11508 52455345 41524348 0644414c 4c41532c 000302c1 0b0a4143 434f554e
54494e47 084e4557 20594f52 4b2c0203 02c13304 424f5353 01410206 3bb7
<32 bytes per line>
BBED> p *kdbr[0]
rowdata[77]
-----------
ub1 rowdata[77] @8175 0x2c
BBED> p *kdbr[4]
rowdata[0]
----------
ub1 rowdata[0] @8098 0x2c
BBED> sum apply
Check value for File 7, Block 5083:
current = 0x7dac, required = 0x7dac
SQL> select * from t;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
50 BOSS A
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES WZ
60 BOSS zw
最后修改时间：2023-06-12 09:35:35
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
