第二十一章 闪回技术、备份恢复与优化
<p>[TOC]</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>
<h2>非当前的还原表空间还原</h2>
<ul>
<li>
<p>非关键表空间可以不进行备份，崩溃时重建即可</p>
</li>
<li>示例
<pre><code>select tablespace_name,status,contents from dba_tablespaces where contents='UNDO';
show parameter undo
--存放undo表空间的创建脚本
--/home/oracle/script/unbackscript/undo.sql
create undo tablespace jinlian_undo
datafile '/home/oracle/data/disk5/unbackdatafile/jinlian_undo.dbf'
size 50 M
extent management local;
SQL> @/home/oracle/script/unbackscript/undo.sql
--显示undo表空间与文件路径
select file_id,tablespace_name,file_name,bytes/1024/1024 MB from dba_data_files where tablespace_name like '%UNDO%';
connect sys/oracle
shutdown immediate
--删除/home/oracle/data/disk5/unbackdatafile/jinlian_undo.dbf文件
--相当于非当前还原表空间所对应的数据文件破坏
[oracle@oracle unbackscript]$ rm /home/oracle/data/disk5/unbackdatafile/jinlian_undo.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 587206592 bytes
Database Buffers 239075328 bytes
Redo Buffers 3952640 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9:
alter database datafile 9 offline drop;
alter database open;
--数据字典查出来的信息与物理路径中查出来的不一样
select file_id,tablespace_name,file_name,STATUS from dba_data_files where tablespace_name like '%UNDO%';
select tablespace_name,status,contents from dba_tablespaces where contents='UNDO';
drop tablespace jinlian_undo;
SQL> @/home/oracle/script/unbackscript/undo.sql</code></pre></li>
</ul>
<h2>只读表空间和临时表空间的恢复</h2>
<ul>
<li>
<p>只读表空间的恢复
只读表空间上不会产生重做操作、不用加锁
改表空间为只读后做一次备份就够
将表空间置为只读前、后都需要对控制文件进行备份</p>
</li>
<li>
<p>临时表空间数据文件恢复
临时表空间数据文件丢失不显示数据库的启动
正常打开，丢失临时表空间数据文件事件写入报警文件
临时表空间也可以不做备份</p>
</li>
<li>
<p>可以关机的临时文件恢复</p>
<pre><code>connect sys/oracle
select f.file#,t.ts#,f.name "file",t.name tablespace from v$tempfile f,v$tablespace t where f.ts#=t.ts# ;
--/home/oracle/data/disk1/temp/temp01.dbf
shutdown immediate
rm /home/oracle/data/disk1/temp/temp01.dbf
SELECT * FROM v$parameter where name='background_dump_dest';
cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
startup
[oracle@oracle trace]$ tail -f alert_orcl.log
2021-08-10T21:45:04.285186+08:00
Re-creating tempfile /home/oracle/data/disk1/temp/temp01.dbf
select * from dba_tablespaces where tablespace_name='TEMP' ;</code></pre>
</li>
<li>不可以关机的临时文件恢复
<pre><code>SQL> select f.file#,t.ts#,f.name "file",t.name tablespace from v$tempfile f,v$tablespace t where f.ts#=t.ts# ;
FILE# TS# file TABLESPACE
1 3 /home/oracle/data/disk1/temp/temp01.dbf TEMP
--大规模的排序sql，报错ORA-01565 无法使用临时文件
--恢复办法是创建新的临时数据文件，删除旧的
alter tablespace temp
add tempfile '/home/oracle/data/disk5/unbackdatafile/temp02.dbf'
size 20M;
--文件没坏是无法删除的
alter tablespace temp drop tempfile '/home/oracle/data/disk1/temp/temp01.dbf';
select f.file#,t.ts#,f.name "file",t.name tablespace,bytes/1024/1024 MB from v$tempfile f,v$tablespace t where f.ts#=t.ts# ;</code></pre></li>
</ul>
<h2>索引表空间的恢复</h2>
<ul>
<li>只放索引的表空间也不用备份，采取重建的方式来恢复</li>
</ul>
<pre><code>connect system/system
select tablespace_name,status from dba_tablespaces where tablespace_name like '%PIO%' or tablespace_name like '%JIN%';
--/home/oracle/data/disk2/data/ORCL/datafile/o1_mf_pioneer__jgrgzf2w_.dbf
select tablespace_name,file_id,file_name from dba_data_files where tablespace_name like '%PIO%' or tablespace_name like '%JIN%';
connect pjinlian/wuda
select index_name,table_name,tablespace_name,status from user_indexes;
select index_name,table_name,tablespace_name,status from dba_indexes where owner='PJINLIAN';
--移动索引表空间
alter index pjinlian.PK_EMP rebuild tablespace PIONEER_INDX;
alter index pjinlian.PK_DEPT rebuild tablespace PIONEER_INDX;
--重建索引脚本/home/oracle/script/unbackscript/indx.sql
create tablespace PIONEER_INDX
datafile '/home/oracle/data/disk5/unbackdatafile/PIONEER_INDX01.dbf'
size 100 M
extent management local
uniform size 1 M;
connect pjinlian/wuda
create index PK_EMP
on EMP(empno) tablespace PIONEER_INDX nologging;
create index PK_DEPT
on DEPT(DEPTNO) tablespace PIONEER_INDX nologging;
create index SALES_PROD_ID_IDX
on SALES(PROD_ID) tablespace PIONEER_INDX nologging;
create index SALES_CUST_ID_IDX
on SALES(CUST_ID) tablespace PIONEER_INDX nologging;
create index SALES_CHANNEL_ID_IDX
on SALES(CHANNEL_ID) tablespace PIONEER_INDX nologging;
create index CUSTOMERS_GENDER_IDX
on CUSTOMERS(cust_GENDER) tablespace PIONEER_INDX nologging;
create index CUSTOMERS_CITY_IDX
on CUSTOMERS(cust_CITY) tablespace PIONEER_INDX nologging;
--删除索引表空间的数据文件
connect sys/oracle
shutdown immediate
$ rm /home/oracle/data/disk2/data/ORCL/datafile/o1_mf_pioneer__jgrgzf2w_.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 587206592 bytes
Database Buffers 239075328 bytes
Redo Buffers 3952640 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2:
'/home/oracle/data/disk2/data/ORCL/datafile/o1_mf_pioneer__jgrgzf2w_.dbf'
alter database datafile 2 offline;
alter database open;
select file_id,tablespace_name from dba_data_files ;
select file#,status from v$datafile;
SQL> drop tablespace PIONEER_INDX including contents;
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
--主键强制索引表空间不能删除，先删除约束再删除索引表空间，这样做肯定丢失键了
alter table pjinlian.EMP drop constraint PK_EMP;
alter table pjinlian.EMP drop constraint FK_DEPTNO;
alter table pjinlian.DEPT drop constraint PK_DEPT;
--查看数据字典确认信息
--执行一部分后超过会话限制
SQL> @/home/oracle/script/unbackscript/indx.sql
ORA-02394: exceeded session limit on IO usage, you are being logged off
--同一用户打开窗口过多，导致运行失败
select username,profile from dba_users where username='PJINLIAN';
select * from dba_profiles where profile='PIONEER_PROF' and resource_name='SESSIONS_PER_USER';
--接下来的手动执行算了。
--检查索引的恢复情况</code></pre>
<ul>
<li>移动索引表空间
<pre><code>--分两种情况，一种是移动普通索引，另一种是移动分区索引。
--oracle移动普通索引到其他表空间语法：
alter index 索引名 rebuild tablespace 其他表空间;
--oracle移动分区索引到其他表空间语法：
alter table 表名 move partition 分区名 tablespace 其他表空间;</code></pre></li>
</ul>
<h2>加快数据表空间的恢复</h2>
<ul>
<li>
<p>减少cp数据文件步骤</p>
<pre><code>select file_id,file_name,tablespace_name from dba_data_files where tablespace_name='PIONEER_DATA';
5 /home/oracle/data/disk2/data/ORCL/datafile/o1_mf_pioneer__jg8x40jd_.dbf PIONEER_DATA
alter tablespace PIONEER_DATA begin backup;
host cp /home/oracle/data/disk2/data/ORCL/datafile/o1_mf_pioneer__jg8x40jd_.dbf /home/oracle/TBSbackup/disk2/data/ORCL/datafile/pioneer_data01.dbf
alter tablespace PIONEER_DATA end backup;
select * from v$backup ;
shutdown immediate
rm /home/oracle/data/disk2/data/ORCL/datafile/o1_mf_pioneer__jg8x40jd_.dbf
startup
alter database datafile 5 offline ;
alter database open;
select file#,status from v$datafile where file#=5;
select tablespace_name,status from dba_tablespaces where tablespace_name='PIONEER_DATA';
alter tablespace PIONEER_DATA rename datafile '/home/oracle/data/disk2/data/ORCL/datafile/o1_mf_pioneer__jg8x40jd_.dbf' to '/home/oracle/TBSbackup/disk2/data/ORCL/datafile/pioneer_data01.dbf';
recover datafile 5 ;
alter database datafile 5 online ;
--检查数据文件</code></pre>
</li>
<li>
<p>恢复回数据文件</p>
<pre><code>alter tablespace PIONEER_DATA begin backup;
host cp /home/oracle/TBSbackup/disk2/data/ORCL/datafile/pioneer_data01.dbf /home/oracle/data/disk2/data/ORCL/datafile/pioneer_data01.dbf
alter tablespace PIONEER_DATA end backup;
alter database datafile 5 offline ;
alter tablespace PIONEER_DATA rename datafile '/home/oracle/TBSbackup/disk2/data/ORCL/datafile/pioneer_data01.dbf' to '/home/oracle/data/disk2/data/ORCL/datafile/pioneer_data01.dbf' ;
recover datafile 5 ;
alter database datafile 5 online ;</code></pre>
</li>
<li>写入脚本
<pre><code>alter database datafile 5 offline;
alter database open;
alter tablespace PIONEER_DATA rename datafile '' to '';
recover datafile 5;
alter database datafile 5 online;</code></pre></li>
</ul>
<h2>整个数据库的闪回</h2>
<ul>
<li>
<p>配置快速恢复区</p>
</li>
<li>
<p>使用初始化参数db_flashback_retention_target 设置保留目标，分钟为单位</p>
</li>
<li>
<p>使用以上命令启用闪回数据库
<code>alter database flashback on;</code></p>
</li>
<li>
<p>数据库必须是归档模式，要运行在mount exclusive 模式下才能闪回数据库</p>
</li>
<li>
<p>查看数据库是否启用闪回数据库
<code>select flashback_on from v$database;</code></p>
</li>
<li>闪回数据库的SQL
<pre><code>flashback database to timestamp(sysdate-3/24);
flashback database to scn 23843;
flashback database to restore point bady3_load;
RMAN> flashback database to time="to_date('2021/8/11 20:32:00','yyyy/mm/dd hh24:mi:ss')";
RMAN> flashback database to scn=23843;</code></pre></li>
</ul>
<h2>配置数据库闪回的实例</h2>
<pre><code>select flashback_on from v$database;
--闪回写后台进程(没启用闪回数据库，这个进程是空的)
select pid,username,program from v$process where background='1' and program like '%RVWR%';
shutdown immediate
startup mount
--可闪回时间设置为两天
alter system set db_flashback_retention_target=2880 scope=both ;
alter database flashback on ;
alter database open;
select flashback_on from v$database;
select pid,username,program from v$process where background='1' and program like '%RVWR%';
--关闭闪回数据库
shutdown immediate
startup mount
alter database flashback off;
alter database open;
select flashback_on from v$database;
select pid,username,program from v$process where background='1' and program like '%RVWR%';</code></pre>
<ul>
<li>尝试闪回数据库
<pre><code>create table pjinlian.pdm(cell_name varchar2(20));
insert into pjinlian.pdm(cell_name) values('test');
insert into pjinlian.pdm(cell_name) values('test2');
insert into pjinlian.pdm(cell_name) values('test3');
insert into pjinlian.pdm(cell_name) values('test4');
commit;
select * from pjinlian.pdm;
select * from dba_tables where owner='PJINLIAN';
select current_scn from v$database;
select GROUP#,sequence#,STATUS,FIRST_CHANGE#,to_char(FIRST_TIME,'yyyy/mm/dd:hh24:mi:ss') time from V$log;
alter system switch logfile;
shutdown immediate
startup mount
flashback database to scn 5119116;
alter database open RESETLOGS;
select * from pjinlian.pdm;</code></pre></li>
</ul>