Oracle OCP笔记(31)使用闪回功能

Oracle的闪回功能受到3个数据库结构之一的支持: 撤消数据、闪回恢复区和回收站。 撤消表空间中的撤销数据不仅支持事务回滚，也支持大多数闪回表操作。Flashback Data Archives允许查询先前版本的表行，它在撤销表空间之外的一个或多个表空间中提供一个区域，支持的保留期比撤销表空间还长。闪回日志保存在闪回恢复区中，从而无需执行传统的还原和恢复操作，即可方便地将整个数据库回滚到过去的时间点。每个表空间中的回收站包含一个或多个版本的已删除的表和索引，如果表空间中的空间充裕，用户可以方便地将其还原。 1.从回收站还原已经删除的表 (1).启用回收站功能 alter session set recyclebin=on; -- 启用/禁用回收站(会话级) on/off alter system set recyclebin=on; -- 启用/禁用回收站(系统级) on/off 启用回收站功能后，删除对象后会在回收站视图中显示，回收站中对象名称会被改为系统指定的名称: BIN$unique_id$version unique_id: 全局唯一名称 version: 版本号 删除对象后，对象分配的空闲空间会显示在视图dba_free_space中，但对象仍占用用户空间配额。 (2).查看回收站的对象 select * from dba_recyclebin; select * from user_recyclebin; select * from recyclebin; -- 视图user_recyclebin的同义词 show recyclebin; (3).使用回收站还原表 flashback table "BIN$u/===$0" to before drop; 如果尝试还原删除后已经重新创建的表，需要使用rename to子句为已经还原的表提供一个新名称，否则将出错。 flashback table tablename to before drop rename to oldtablename; 在使用rename选项闪回表时，表会使用原来的名称，但表的从属对象并非如此，如果需要为索引、触发器和约束保留原来的名称，需查询回收站，在还原后闪回并重新命名其他对象。 (4).清空回收站 purge recyclebin; -- 清空当前用户的回收站对象 purge dba_recyclebin; -- 清空所有用户的回收站对象(sysdba权限) purge tablespace users; -- 清空表空间的回收站对象 purge tablespace tbname user username; -- 清空特定用户表空间的回收站对象(drop any table系统权限) (5).删除对象时绕过回收站 drop table ... purge; drop tablespace ... including contents; drop user ... cascade; 2.执行闪回查询 (1).有关闪回查询的参数 使用闪回查询功能，需要正确配置撤销表空间参数: undo_management=auto undo_tablespace=undotbs1 undo_retention=1800 在撤销表空间的大小有限，确保闪回操作或长期运行的查询取得成功，必须在创建表空间后为撤销表空间指定retention guarantee. alter tablespace undotbs1 retention guarantee; (2).使用闪回查询 select * from tablename as of timestamp to_timestamp('2014/11/19 19:22:30','YYYY/MM/DD HH24:MI:SS'); select * from employees as of timestamp systimestamp - interval '60' minute; -- 60分钟前 select * from tablename as of timestamp (systimestamp - 1/1440); -- 1分钟前 (3).使用闪回版本查询 select dbms_flashback.get_system_change_number from dual; -- 查看SCN编号 select * from tablename versions between scn 2710111 and 2710113 where ...; select a.*,versions_startscn,versions_starttime,versions_endscn,versions_endtime,versions_xid,versions_operation from mms.usr_mstr versions between scn 2847161 and 2847403 a 闪回版本查询伪列: ·VERSIONS_START{SCN|TIME} -- SCN或时间戳 ·VERSIONS_END{SCN|TIME} -- SCN或时间戳 ·VERSIONS_XID -- 事务ID ·VERSIONS_OPERATION -- 在行上执行的操作(I=插入,D=删除,U=更新) 使用闪回版本查询的限制，无法使用VERSIONS子句查询如下对象: ·外部表 ·固定表(内部Oracle X$表) ·临时表 ·视图 3.使用闪回事务 (1).使用闪回事务的先决条件 数据库必须为归档模式. 数据库必须启用补充日志. 补充日志: 使用闪回事务查询前，必须启用重做日志流的其他日志记录。重做日志流与LogMiner使用的数据相同，只是接口不同而已。很明显，重做日志流数据是撤销表空间中记录的信息的补充(补充日志)。闪回事务查询既需要增强的重做信息，也需要撤销信息。 v$database列相关的参数 supplemental_log_data_min supplemental_log_data_pk supplemental_log_data_ui supplemental_log_data_fk supplemental_log_data_all select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,supplemental_log_data_fk,supplemental_log_data_all from v$database; -- 启用对DML更改引用的列值和主键值的日志记录 alter database add supplemental log data; alter database add supplemental log data (primary key) columns; -- 授予适当权限给将要使用闪回事务查询的用户 grant execute on dbms_flashback to hr; grant select any transaction to hr; (2).闪回事务查询 与闪回版本查询不同，闪回事务查询不引用DML事务中涉及的表，相反，它查询数据字典视图flashback_transaction_query. 视图flashback_transaction_query的列 ·XID -- 事务ID编号 ·START_SCN -- 事务中第一个DML的SCN ·START_TIMESTAMP -- 事务中第一个DML的时间戳 ·COMMIT_SCN -- 提交事务时的SCN ·COMMIT_TIMESTAMP -- 提交事务时的时间戳 ·logoN_USER -- 拥有事务的用户 ·UNDO_CHANGE# -- 撤销SCN ·OPERATION -- 执行的DML操作: DELETE、INSERT、UPDATE、BEGIN或UNKNOWN ·TABLE_NAME -- DML更改的表 ·TABLE_OWNER -- DML更改的表的所有者 ·ROW_ID -- DML修改的行的ROWID ·UNDO_sql -- 撤销DML操作的sql语句 撤销空间是有限的，因此flashback_transaction_query中只包含一部分事务。这种情况下，对于任何所选事务不再显示在撤销表空间的DML，OPERATION列的值是UNKNOWN. select xid,start_scn,commit_scn,logon_user,operation,table_owner,table_name,undo_sql from flashback_transaction_query where table_name = tablename; select xid,undo_sql from flashback_transaction_query where xid = hextoraw('030002001F060000'); undo_sql即为生成的回退语句。 (3).撤销事务 使用过程dbms_flashback.transaction_backout. 过程的参数: ·NumTxns: 被取消的事务处理的数量。 ·Names: 被取消的事务处理的列表（按名字排序）。 ·Timehint: 如果按名字标识事务处理，则可以提供一个时间提示，如在事务处理开始前的某个时间。 ·Options: 指定某个事务处理及其依赖的事务处理被取消的顺序。 回滚的选项(Options): ·CASCADE -- 回滚所有事务及其从属事务 ·NOCASCADE -- 仅回滚指定的事务 ·NOCASCADE_FORCE -- 回滚指定的事务，忽略从属事务 ·NONCONFLICT_ONLY -- 仅回滚指定事务的非冲突行的更改 dbms_flashback.transaction_backout(numtxns=>1,xids=>sys.xid_array('050004009D0A0000'),options=>dbms_flashback.cascade); commit; -- 撤销事务需要显式提交. 4.执行闪回表操作 (1).闪回表 Oracle的闪回功能将表行的状态还原到过去的一个时间点。它也还原表的索引、触发器和约束，而数据库仍处于联机状态。这提高了数据库的总体可用性。 可以按时间戳或SCN还原表。如果用户错误范围小，仅限于一个或很少的几个表，则闪回表优于其他闪回方法。 为了恢复较大数量的表的状态，闪回数据库的效果可能更好。闪回表不适用于独立数据库，也无法重新构建所有DDL操作，如添加和删除列。 由于闪回数据库使用撤销表空间，与其他恢复方法(如基于RMAN的恢复或闪回数据库)相比，恢复窗口相对较小。 闪回表就地执行，在数据库仍处于联机状态时，回滚对表及所有从属对象(例如索引)执行的更改。如果表还有其他从属对象，可以在flashback table命令中指定多个表。无论在闪回表操作中指定一个表还是多表表，都将此操作视为单个事务。所有更改要么成功，要么回滚。 (2).配置闪回表功能 grant flashback on tablename to username; -- 将表的flashback权限授予用户 grant flashback any table to username; -- 或将flashback any table权限授予用户 alter table tablename enable row movement; -- 开启Row movement (3).使用闪回表 delete from tablename; flashback table tablename1,tablename2 to timestamp to_timestamp('2014-12-08 15:30:01','yyyy-mm-dd hh24:mi:ss'); flashback table tablename to scn 1763103; flashback table tablename to timestamp systimestamp - interval '15' minute; 5.设置和使用闪回数据归档 (1).闪回数据归档(Flashback Data Archive) Flashback Data Archive创建一个或多个仓库区域(其中一个是默认区域)，为仓库中的对象指定默认的保留期，然后为适当的表添加跟踪标记。 Flashback Data Archive的行为与撤销表空间类似，但是Flashback Data Archive仅记录UPDATE和DELETE语句，不记录INSERT语句。另外，对于撤销数据而言，所有对象的保留期限通常为数小时或数天，而Flashback Data Archive中的行可以跨越多年，Flashback Data Archive的关注范围更狭窄，只记录对表行的历史改动。 可以像在闪回查询的SELECT语句中使用AS OF子句一样，访问Flashback Data Archive中的数据。闪回版本查询和闪回事务查询也可以使用Flashback Data Archive中的数据。 dba_flashback_archive_tables -- 启用了闪回归档的表 dba_flashback_archive -- 列出归档 dba_flashback_archive_ts -- 表空间与归档的映射关系 (2).配置归档 <1>Flashback Data Archive的权限 flashback archive administer -- 创建或修改Flashback Data Archive的权限 flashback archive -- 启用对表的跟踪的权限 select * from dba_sys_privs where grantee='USERNAME'; grant flashback archive administer to username; grant flashback archive to username; <2>创建归档 create tablespace fbdata01 datafile '/u01/app/oracle/oradata/sales/fbdat01.dbf' size 500m; create tablespace fbdata02 datafile '/u01/app/oracle/oradata/sales/fbdat02.dbf' size 500m; create tablespace fbdata03 datafile '+data01' size 1g; create flashback archive default fbarcdef tablespace fbdata01 quota 400m retention 2 year; create flashback archive fbarcdata tablespace fbdata03 quota 500m retention 7 year; <3>维护归档 -- 为归档添加表空间 alter flashback archive fbarcdef add tablespace fbdata02 quota 400m; -- 使用Purge清除归档数据 alter flashback archive fbarcdef purge before timestamp to_timestamp('2014-12-12 00:00:00','YYYY-MM-DD HH24:MI:SS'); <4>将表分配到归档中. alter table tablename flashback archive fbarcdef; <5>取消表的归档. alter table tablename no flashback archive; (3).查询Flashback Data Archive 可以像使用撤销表空间中的DML活动时那样，通过在表中使用AS OF子句查询Flashback Data Archive中的表的历史数据。 实际上，对用户而言，查询使用撤销表空间还是Flashback Data Archive是完全透明的。 delete from employees where enployee_id = 169; select * from employees as of timestamp (systimestamp - interval '28' day); select * from employees as of timestamp (systimestamp - interval '2' minute); 6.闪回数据库 Oracle的闪回数据库功能使用Flashback database命令将数据库返回到过去的时间或SCN，从而为执行不完全数据库恢复提供了快捷替代方法。 可以使用闪回数据库快速地将整个数据库还原到过去的时间点，在启用闪回数据库时，会将经过修改的块的前映象作为闪回数据库日志保存在闪回恢复区中，如果出现了逻辑损坏，需要恢复到过去的时间点，闪回数据库日志将还原数据块的前映像，然后使用归档和联机重做日志前滚到期望的闪回时间，由于无需还原数据库的数据文件，与执行传统的还原和恢复操作相比，此过程的执行速度通常快很多。 启用闪回数据库时，会将前映像数据保存在闪回缓冲区中，然后使用恢复写入器(Recovery Write,RVWR)后台进程，将闪回缓冲区中的前映像信息保存在闪回恢复区的闪回数据库日志中。循环地重用闪回恢复区中的日志，将数据库回搠到多久远的时间取决于闪回恢复区中的空间量以及配置的担保还原点。 要启用闪回数据库功能，必须设置数据库为归档模式，必须配置归档到快速恢复区. (1).配置闪回数据库 db_recovery_file_dest -- 闪回恢复区路径 db_recovery_file_dest_size -- 闪回恢复区大小 db_flashback_retention_target -- 恢复窗口目标上限(分钟) --必须配置归档日志到快速恢复区 alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'; alter system set log_archive_dest_3='location=USE_DB_RECOVERY_FILE_DEST mandatory'; alter system set db_recovery_file_dest_size=40G; -- 启用闪回数据库 shutdown immediate; startup mount exclusive; alter database archivelog; alter system set db_flashback_retention_target=2880; alter database flashback on; alter database open; -- 关闭闪回数据库 startup mount exclusive; alter database flashback off; alter database open; (2).执行闪回数据库 可以使用RMAN和sqlPLUS执行闪回数据库 -- 线程(实例)和特定日志序列号的闪回 RMAN> flashback database to sequence=307 thread=2; -- 使用sqlplus执行flashback database命令 flashback [standby] database [database] { to { scn | timestamp } expr | to before { scn | timestamp } expr | to restore point expr } 除担保还原点外，还可以使用to scn或to timestamp子句来设置将整个数据库闪回到的点。可以闪回到关键点之前，例如为多个表生成意外结果的事务。 使用特定表行的ORA_ROWSCN伪列来查看对行的最新更改的SCN:select ora_rowscn,a.* from tablename a; 执行闪回，确认闪回数据是可用的，然后执行闪回，闪回到之前的某一时间，将丢失这一时间后的所有事务。 执行flashback database命令时，Oracle将执行检查，以便确保可以使用所有需要的归档和联机重做日志。如果日志可用，联机数据文件将还原到指定的时间、SCN或担保还原点。 如果归档日志和闪回区域中的联机数据不充足，就需要使用传统数据库恢复方法来恢复数据。例如，可以首先使用文件系统恢复方法，然后前滚数据。 完成闪回后，必须使用resetlogs选项打开数据库，以便获得对数据库的写入权限: shutdown immediate; startup mount exclusive; flashback database to timestamp sysdate - (1/24); flashback database to scn 1790640; -- SCN: select ora_rowscn from tablename; -- select dbms_flashback.get_system_change_number from dual; alter database open resetlogs; -- 打开数据库重设日志 (3).排除表空间闪回 alter tablespace example flashback off; -- 表空间关闭闪回数据库 alter tablespace example flashback on; -- 表空间启用闪回数据库 (4).使用担保还原点 担保还原点与普通还原点类似，执行恢复操作期间，可以将其用作SCN的别名。担保还原点的差别在于，它不会在控制文件中过期，并且必须显式删除。担保还原点适用于闪回数据库操作。如果在启用闪回日志记录时创建担保还原点，将确保在闪回恢复区中保留闪回日志，以便确保数据库可以回滚到创建担保还原点后的任何时间点。 -- 创建担保还原点 create restore point restore_point_name guarantee flashback database; -- 闪回担保还原点 shutdown immediate; startup mount exclusive; flashback database to restore point restore_point_name; alter database open resetlogs; -- 删除担保还原点 drop restore point restore_point_name; 要使用担保还原点的先决条件 COMPATIBLE初始化参数必须为10.2或更高 必须在archivelog模式运行数据库 从第一个担保还原点开始，归档重做日志文件必须可供使用 必须配置闪回恢复区 由于在闪回恢复区中保留第一个担保还原点之后的任何闪回日志，担保还原点可能导致闪回恢复区的空间紧张。 (5).监视闪回数据库 select current_scn,flashback_on from v$database; select * from v$flashback_database_log; select * from v$flashback_database_stat;