OCP读书笔记(11)

一总

　　闪回归档
　　1. 什么是闪回数据归档？
　　闪回归档是用来保存一个或多个表的历史数据的新数据库对象，以及该数据的存储保留和清除策略。归档只是保存数据库中一个或多个表的所有事务处理的变化的一个或多个表空间，数据库将缓冲区高速缓存中的所有原始数据作为撤销数据写入撤销表空间，一个称为Flashback Data Archiver Process(FBDA)的新后台进程把原始数据收集并写入一个闪回数据归档中，因而创建了一个所有表数据的历史，为了启用闪回归档必须创建一个带有flashback data archive子句的表，或者使用alter table语句启用已有表的归档
　　2. 创建闪回数据归档
普通用户创建闪回数据归档,必须要有dba角色或系统权限flashback archive administer
　　使用create flashback语句可以创建一个闪回数据归档,但必须首先创建闪回数据归档的表空间,在创建新的闪回数据归档时可以指定以下内容：
　　1.闪回数据归档名
2.闪回数据归档是否为数据库的默认归档
3.早期创建的表空间的名字
4.该闪回数据归档在表空间中的份额
5.闪回数据归档的保留期
　　两个关键参数是quota和retention,前者确定闪回数据归档在表空间中可以使用的空间量,后者确定历史数据将在归档中保留多长时间后被数据库自动清除(也可以手动清除历史数据)
　　在表空间flash_tbs1中创建闪回数据归档：



create flashback archive flash1
tablespace flash_tbs1
retention 4 year;
　　retention 4 year：表示闪回数据归档flash1将保留数据长达4年，在create flashback data archive语句中没有quota字句，这意味着归档可以占用表空间flash_tbs1中的全部可用空间，通过指定quota字句的值，可以限定闪回数据归档占用表空间的空间量，如：



create flashback archive flash1
tablespace flash_tbs1
quota 200m
retention 4 year
　　删除闪回数据归档：



drop flashback archive flash1;
　　3. 更改闪回数据归档
　　可以使用alter flashback命令改变诸如保留期长度、将一个闪回数据归档为数据库默认的闪回数据归档，增加闪回数据归档的空间或清除数据等内容。以下几个例子给出了使用alter flashback命令管理闪回数据归档的几种方法：



alter flashback archive flash1 set default
alter flashback archive flash1 add tablespace flash_tbs1
alter flashback archive flash1 modify tablespace flash_tbs1 quota 10g;
alter flashback archive flash1 modify retention 2 year;
　　以下两个命令说明如何给闪回数据归档添加表空间和删除表空间：



alter flashback archive flash1 add tablespace flash_tbs2;
alter flashback archive flash1 remove tablespace flash_tbs2;
　　清除闪回数据归档中数据：



alter flashback archive flash1 purge all;
alter flashback archive flash1 purge before timestamp(systimestamp-interval '2' day);
alter flashback archive flash1 purge before scn 123456;
　　注意：数据库在保留期超过一天后将自动清除归档数据
　　4. 启用和禁用闪回数据归档
　　默认数据库的每个表都禁用闪回归档，在数据库中创建了一个或多个数据归档之后，用flashback archive子句创建的任何表或用flashback archive子句更改的任何表将自动启用闪回归档
　　如果一开始就启用表的闪回日志,可更改create table语句使其包含flashback archive子句,如：



create table test1(
　　name varchar2(30),
　　address varchar2(50)
)
flashback archive flash1;
　　表创建以后开启闪回归档：



sqlplus / as sysdba
grant flashback archive on flash1 to scott;
conn scott/tiger
alter table emp flashback archive flash1;
select * from dba_flashback_archive_tables;
　　关闭闪回归档：



alter table emp no flashback archive
　　5. 监视闪回数据归档



查看哪些表已经启用了闪回数据归档
select * from dba_flashback_archive_tables;
查看数据库中所有的闪回数据归档：
select flashback_archive_name,
retention_in_days
from dba_flashback_archive;
查询有关闪回数据归档所使用的表空间的信息：
select flashback_archive_name,
tablespace_name,
quota_in_mb
from dba_flashback_archive_ts;
　　6. 实验：



--为闪回数据归档创建所需要的表空间：
conn /as sysdba
create tablespace fb_tbs datafile '/u01/app/oracle/oradata/orcl/fb_tbs01.dbf' size 200M;
--创建闪回数据归档：
create flashback archive flash1 tablespace fb_tbs quota 50M retention 2 year;
--将flash1的使用权授予scott：
grant flashback archive administer to scott;
grant flashback archive on flash1 to scott;
--创建t1表,让其使用闪回数据归档：
conn scott/tiger
create table d as select * from dept;
alter table d flashback archive flash1;
--查看闪回归档中有哪些表：
conn /as sysdba
select * from dba_flashback_archive_tables;
　　SYS_FBA_HIST_74607将存储d表的历史数据：



conn scott/tiger
select * from tab;--没有表SYS_FBA_HIST_74599
delete d;
commit;
select * from d;
select * from d as of timestamp(systimestamp-interval '30' minute);--通过闪回归档查询30分钟之前的数据
select * from tab;
select * from SYS_FBA_HIST_74607;
--查看SYS_FBA_HIST_74607段属于哪个表空间：
select tablespace_name from user_segments where segment_name='SYS_FBA_HIST_74607';
　　ps -ef | grep fbda
　　fbda进程将d表修改前的数据从undo表空间写入SYS_FBA_HIST_74607表，此数据如果不删除将存储2年
　　手动删除d表的历史数据：



alter flashback archive flash1 purge all;
select * from SYS_FBA_HIST_74607;
　　重新设置undo表空间



conn /as sysdba
grant select on dba_objects to scott;
grant select on v_$transaction to scott;
grant select on v_$session to scott;
grant select on v_$mystat to scott;
grant select on v_$database to scott;
create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/undotbs2.dbf' size 30M;
show parameter undo_tablespace
alter system set undo_tablespace=undotbs2;
　　查看没有使用归档的数据



conn scott/tiger
create table d as select * from dept;
select current_scn from v$database;
select * from d;
delete d;
commit;
select * from d;
select * from d as of scn 967811;
　　删除undo表空间



conn /as sysdba
alter system set undo_tablespace=undotbs1;
alter tablespace undotbs2 offline;
alter tablespace undotbs2 online;
drop tablespace undotbs2 including contents and datafiles;
alter system flush buffer_cache;
alter system flush shared_pool;
　　重新查看表中的数据



conn scott/tiger
select * from d as of scn 967811; --snapshot too old
　　回收站
　　1. 闪回删除表依赖于回收站
　　SQL>show parameter recyclebin
　　2. 如何开启闪回删除表：
　　SQL>alter system set recyclebin=on;
　　3.如何执行闪回删除表
　　使用原来的名进行闪回：



SQL>conn scott/tiger
SQL>create table t as select * from dept;
SQL>drop table t;
SQL>show recyclebin
SQL>flashback table t to before drop;
SQL>desc t
　　使用回收站中的名进行闪回：



SQL>drop table t ;
SQL>show recyclebin
SQL>flashback table "BIN$3z04gzCHqIngQKjAZAAkxg==$0" to before drop;
SQL>desc t
　　4.drop表的本质：
　　如果不加purge，实际上将表改了个名放到了回收站，也就是说，表逻辑上被删除了，物理上并没有被删除（此表占用的物理空间并没有释放）



SQL>drop table t ;
SQL>purge recyclebin;
SQL>create table t as select * from dept;
SQL>drop table t ;
SQL>show recyclebin
SQL>select * from t;--报错，逻辑上删除

SQL>select * from "BIN$3z1cRuOpwurgQKjAZAAlqg==$0"; --物理上并没有删除
　　如果表空间空间不足，回收站中的数据可能被覆盖：



conn scott/tiger
create table t as select * from dept;
conn /as sysdba
select FILE_ID, BLOCK_ID, EXTENT_ID from dba_extents where OWNER='SCOTT' and SEGMENT_NAME='T'--系统为t表分配了一个区

conn /as sysdba
select FILE_ID, BLOCK_ID, BLOCKS from dba_free_space where tablespace_name='USERS' --users表空间中的空闲空间只有一个区

conn scott/tiger
drop table t ;
select FILE_ID,BLOCK_ID,BLOCKS from dba_free_space where tablespace_name='USERS' --t表被删除后，多了一个区
　　说明档空间不足的时候，oracle会覆盖回收站中的数据
　　5.回收站遵循先进后出：



SQL>create table t as select * from emp;
SQL>drop table t;
SQL>create table t as select * from dept;
SQL>drop table t;
SQL>show recyclebin
SQL>flashback table t to before drop;
SQL>select * from t;
　　发现最后进入的回收站的表最先闪回
　　指定回收站中的名称进行指定表的闪回（想闪回谁就闪回谁）



SQL>drop table t ;
SQL>show recyclebin
SQL>flashback table "BIN$3z1++LPVDbLgQKjAZAAmgA==$0" to before drop;
SQL>select * from t;
　　6.闪回的同时改名：



SQL>flashback table t to before drop; --error

SQL>flashback table t to before drop rename to tt;
　　7.关于表的依赖对象的闪回：
　　1. 表上的索引：



SQL>create table t as select * from emp;
SQL>create index ind_t on t(empno);
SQL>drop table t;
SQL>show recyclebin
SQL>select original_name, object_name, type
　　from user_recyclebin;
　　使用 show recyclebin看不出有索引，要使用user_recyclebin
发现回收站中有索引，也就是说，删表的时候，索引也会被一起放入回收站



SQL>flashback table t to before drop;
SQL>select original_name, object_name, type
　　from user_recyclebin;
SQL>select index_name
　　from user_indexes
　　where table_name='T';
SQL>alter index "BIN$3z1++LPYDbLgQKjAZAAmgA==$0" rename to ind_t;
　　闪回之后回收站中没数据，说明索引也被闪回了
发现索引已经恢复，但是名称没有改变，仍然是回收站名，应该对其改名
　　2.表上的约束



SQL>create table t1 as select * from emp;
SQL>alter table t1 modify(empno not null);
SQL> select CONSTRAINT_NAME from user_constraints where table_name='T1';
SQL> drop table t1;
SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE from user_recyclebin;
SQL> flashback table t1 to before drop;
SQL> select CONSTRAINT_NAME from user_constraints where table_name='T1';
SQL>alter table t1 rename constraint "BIN$3z1++LPaDbLgQKjAZAAmgA==$0" to SYS_C005403;
SQL> select CONSTRAINT_NAME from user_constraints where table_name='T1';
　　8.回收站空间的回收
　　1）自动回收
　　如果数据文件的自动扩展没有打开：



SQL> create tablespace t2 datafile '/u01/app/oracle/oradata/orcl/t202.dbf' size 1M;
SQL> select autoextensible from dba_data_files where tablespace_name = 'T2';
SQL> create user test default tablespace t2 identified by a;
SQL> grant connect,resource to test;
SQL> conn scott/tiger
SQL> grant select on emp to test;
SQL> conn test/a
SQL> create table t1 as select * from scott.emp;
SQL> drop table t1;
SQL> show recyclebin
SQL> create table t2 as select * from scott.emp;
SQL> show recyclebin
SQL> insert into t2 select * from t2;
SQL> /
　　空间不足，查看回收站，发现回收站中的空间被自动释放
SQL> show recyclebin
　　自动扩展打开：



SQL> create tablespace t2 datafile '/u01/app/oracle/oradata/orcl/t2.dbf' size 1M autoextend on;
SQL> alter user test default tablespace t2;
SQL> conn test/a
SQL> create table t1 as select * from scott.emp;
SQL> drop table t1;
SQL> show recyclebin;
SQL> create table t2 as select * from scott.emp;
SQL> insert into t2 select * from t2;
/
SQL> show recyclebi
　　此时空间不足，仍然是先释放回收站，然后再扩展：
　　2）手动回收
　　使用purge命令进行空间回收



SQL> create table t1 as select * from emp;
SQL> drop table t1 purge;---不往回收站中放，直接删除
清空回收站中的某个对象：
SQL> create table t1 as select * from emp;
SQL> drop table t ;
SQL> show recyclebin
SQL> create table t1 as select * from emp;
SQL> purge table t1;
SQL> show recyclebin
SQL> purge recyclebin;
SQL> show recyclebin
SQL> purge recyclebin; ---清空当前用户下回收站中所有的对象：
SQL> select object_name, original_name, type from user_recyclebin;
清空某个表空间下的回收站中的数据：
SQL> conn /as sysdba
SQL> select owner, object_name, original_name, type, ts_name from dba_recyclebin;
SQL> purge tablespace users;
SQL> select owner, object_name, original_name, type, ts_name from dba_recyclebin;
发现users表空下的数据没了
清空表空间中某个用户的数据：
SQL>purge tablespace t2 user scott;
SQL> select owner, object_name, original_name, type, ts_name from dba_recyclebin;
清空数据中所有回收站中的数据：
SQL> purge dba_recyclebin;
　　闪回对DDL的支持
　　在早期版本中闪回归档有很多限制，包括增加、修改、重命名、删除表的列、truncate表、修改表的约束、以及修改分区表的分区规范，在Oracle 11g R2中，这些限制全部没有了，Oracle 11g R2提供了新的dbms_flashback_archive包，存储过程disassociate_fba将会把基础表从FBDA中分离出来，一旦请求的改变完成，存储过程reassociate_fba会被用来重新关联修改的表和基础表
　　实验：



conn /as sysdba
create tablespace tbs_flash datafile '/u01/app/oracle/oradata/orcl/tbs01.dbf' size 100M;
create flashback archive flash1 tablespace tbs_flash retention 2 year;
grant flashback archive on flash1 to scott;
conn scott/tiger
create table emp_test as select * from emp;
alter table emp_test flashback archive flash1;
delete emp_test;
commit;
select * from emp_test as of timestamp(systimestamp-interval '5' minute);
select * from tab;
　　正常情况下，用户不能对历史表进行任何修改：



conn scott/tiger
delete from sys_fba_hist_76866;
alter table sys_fba_hist_76866 drop column comm;
使历史表与基表分离：
conn /as sysdba
exec dbms_flashback_archive.disassociate_fba('scott','emp_test');
现在用户可以对基表的表结构进行修改：
alter table scott.emp_test rename column sal to salary;
用户也可以修改历史表中的数据：
delete from scott.sys_fba_hist_76866 where empno=7788;
如果此时用户重新结合基表与历史表，将会失败，因为基表的表结构发生了改变：
exec dbms_flashback_archive.reassociate_fba('scott','emp_test');
begin
　　dbms_flashback_archive.reassociate_fba('scott','emp_test');
end;
exec dbms_flashback_archive.disassociate_fba('scott','emp_test');
在历史表上执行与基表相同的DDL：
alter table scott.sys_fba_hist_76866 rename column sal to salary;
重新结合历史表语基表：
exec dbms_flashback_archive.reassociate_fba('scott','emp_test')