第二十一章 闪回技术、备份恢复与优化
<p>[TOC]</p>
<h1>第二十一章 闪回技术、备份恢复与优化</h1>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/b6e30ddb624d1d9d00be4d4422dba2c6" alt="闪回删除和回收站" /></p>
<h2>闪回已经删除的表</h2>
<ul>
<li>
<p>闪回技术说明
删除的表放在回收站，回收站占用表原来的表空间
如果空闲磁盘空间不够就使用回收站的表空间</p>
</li>
<li>
<p>闪回表实例</p>
<pre><code>-- recyclebin=on时启用回收站
SQL> show parameter recyclebin
recyclebin string on
select isses_modifiable, issys_modifiable from v$parameter where name='recyclebin';
--12c需要加deferred才能修改，在新的session中才能生效(修改参数)
alter system set recyclebin = off DEFERRED;
alter system set recyclebin=on deferred;
--scott用户下删除表再闪回表
connect scott/tiger
select * from cat;
show recyclebin --显示回收信息与表名对应关系
--bin开头的是回收站的信息，如下清空回收站
purge table DEPT_DUMP;
purge recyclebin;
col table_name for a20
col table_type for a10
select * from cat;
drop table DEPT_DUMP;
select * from cat;
show recyclebin;
--查看已经删除的表占用的磁盘空间
select * from user_recyclebin;
select * from dba_recyclebin;
select * from DEPT_DUMP;
--闪回回收站的表
flashback table dept_dump to before drop;
--回收站不见了
show recyclebin;
--直接删除表不进入回收站
drop table dept_dump purge;</code></pre>
</li>
<li>闪回技术只能保护非系统表空间中的表
这些表必须是本地管理表空间中
表的位图连接索引、引用完整性约束等对象不受回收站保护</li>
</ul>
<h2>闪回错误的DML操作（回滚段）</h2>
<ul>
<li>
<p>DML操作事务的数据保存在回滚段中用于恢复
undo_retention参数，单位是秒，表示一个事务提交后保存在还原段中的时间</p>
</li>
<li>设置还原表空间的retention属性为guarantee为强制保留
设置retention属性为noguarantee为不强制保留</li>
</ul>
<pre><code>connect system/system
--900秒15分钟保留时间
SQL> show parameter undo_retention
undo_retention integer 900
-- 改为保留两小时
alter system set undo_retention=7200;
-- retention 属性修改
select * from dba_tablespaces where tablespace_name='UNDOTBS1';
connect scott/tiger
update emp_dump set sal = 9999;
--versions 子句，查看版本，提交后能看到两个版本
select versions_xid,empno,ename,sal from emp_dump versions between scn minvalue and maxvalue where empno = 7900;
connect system/system
--数据字典flashback_transaction_query：获取闪回信息
select operation,undo_sql from flashback_transaction_query where xid=hextoraw('0600100042080000');
--oracle12c禁用如下，开启才能看到undosql
alter database add supplemental log data;
--找出sql执行即可还原
update "SCOTT"."EMP_DUMP" set "SAL" = '1300' where ROWID = 'AAAS7aAAHAAAACrAAL';
update "SCOTT"."EMP_DUMP" set "SAL" = '3000' where ROWID = 'AAAS7aAAHAAAACrAAK';
update "SCOTT"."EMP_DUMP" set "SAL" = '950' where ROWID = 'AAAS7aAAHAAAACrAAJ';
update "SCOTT"."EMP_DUMP" set "SAL" = '1500' where ROWID = 'AAAS7aAAHAAAACrAAI';
update "SCOTT"."EMP_DUMP" set "SAL" = '5000' where ROWID = 'AAAS7aAAHAAAACrAAH';
update "SCOTT"."EMP_DUMP" set "SAL" = '2450' where ROWID = 'AAAS7aAAHAAAACrAAG';
update "SCOTT"."EMP_DUMP" set "SAL" = '2850' where ROWID = 'AAAS7aAAHAAAACrAAF';
update "SCOTT"."EMP_DUMP" set "SAL" = '1250' where ROWID = 'AAAS7aAAHAAAACrAAE';
update "SCOTT"."EMP_DUMP" set "SAL" = '2975' where ROWID = 'AAAS7aAAHAAAACrAAD';
update "SCOTT"."EMP_DUMP" set "SAL" = '1250' where ROWID = 'AAAS7aAAHAAAACrAAC';
update "SCOTT"."EMP_DUMP" set "SAL" = '1600' where ROWID = 'AAAS7aAAHAAAACrAAB';
update "SCOTT"."EMP_DUMP" set "SAL" = '800' where ROWID = 'AAAS7aAAHAAAACrAAA';
--一个语句完成恢复
--获取事务提交的SCN号：4977453
select operation,undo_sql,start_scn from flashback_transaction_query where xid=hextoraw('0600100042080000');
SQL> flashback table scott.emp_dump to scn 4977453 ;
ORA-08189: cannot flashback the table because row movement is not enabled
--将表的行移动开启，才能进行表闪回操作
alter table scott.emp_dump enable row movement;
select * from scott.emp_dump;
--根据时间闪回
flashback table scott.emp_dump to timestamp to_timestamp('2021/8/10 00:31:00','yyyy/mm/dd hh24:mi:ss') ;</code></pre>