甲骨人-OCM集训营（QQ群号：）

Size: px

Start display at page:

Download "甲骨人-OCM集训营（QQ群号：）"

俦蟹郑
5 years ago
Views:

1 闪回技术简介 : Oracle 11g 中的闪回技术数据库级别 : Oracle10g 时需要在 mount 状态开启,11g 之后可以在 open 下打开, 但前提是必须开启数据库归档配置方法 : 11G: SQL>show parameter recover SQL>show parameter db_flash SQL>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=2G;

2 SQL>ALTER SYSTEM SET DB_RECOVER_FILE_DEST='homeoracleflash'; SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH; -- 默认是 1440 一天 SQL> ALTER DATABASE FLASHBACK ON; SQL> select name,flashback_on from v$database; 使用 V$FLASHBACK_DATABASE_LOG 视图可监视闪回数据库的保留目标 : ESTIMATED_FLASHBACK_SIZE : 使用先前记录的闪回数据, 可估计快速恢复区中闪回日志需要多少磁盘空间才能满足当前闪回保留目标估计的依据是自实例启动以来的工作量, 或者是等于闪回保留目标的最近时间间隔, 取较小的那个值 FLASHBACK_SIZE : 给出了闪回数据的当前字节大小 ( 以字节为单位 ) OLDEST_FLASHBACK_SCN 和 OLDEST_FLASHBACK_TIME : 显示了数据库可闪回到的近似最低 SCN 和时间 SQL> SELECT estimated_flashback_size,flashback_size FROM V$FLASHBACK_DATABASE_LOG; 使用 V$FLASHBACK_DATABASE_STAT 视图可监视闪回数据库日志中记录闪回数据的开销此视图包含 24 小时的信息, 每一行代表一小时的时间间隔 SQL> SELECT begin_time, end_time, flashback_data, db_data,redo_data, estimated_flashback_size AS EST_FB_SZE FROM V$FLASHBACK_DATABASE_STAT; 应用场景 : 数据库升级前数据库做压力测试前应用举例 : SQL> create table t1 as select * from dba_objects; Table created. 找出当前 scn: 查询下当前 scn 时表里数据的条目 SQL>select current_scn from v$database; SQL>select count(*) from t1; 模拟压力测试插入数据 : SQL> insert into t1 select * from t1; SQL>commit SQL> startup mount force; ORACLE instance started. Total System Global Area bytes

3 Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes Database mounted. SQL> flashback database to scn ; Flashback complete. SQL> alter database open resetlogs; 打开数据库则必须使用 RESETLOGS 创建普通还原点 : SQL>CREATE RESTORE POINT test_point; SQL> FLASHBACK DATABASE TO RESTORE point test_point; 可靠还原点 : CREATE RESTORE POINT test_guarantee GUARANTEE FLASHBACK DATABASE; FLASHBACK DATABASE TO RESTORE point test_guarantee; 基于时间点的闪回 : FLASHBACK DATABASE TO TIMESTAMP(TO_DATE(' :19:00', 'YYYY-MM-DD HH24:MI:SS')); SQL>alter database flashback off; 关闭闪回数据库表级别闪回表 Drop delete 或错误的更新删除表里的记录 Drop 表的恢复 : sys 的对像不进回收站 SQL> show parameter recycle SQL> create tablespace test datafile 'u01apporacleoradataprod4prod4test.dbf' size 10m; Tablespace created.

4 SQL> create user zjk identified by oracle default tablespace test; User created. SQL> grant dba to zjk; Grant succeeded. SQL> conn zjkoracle Connected. SQL> create table TEST1 as select * from dba_objects; Table created. SQL> select sum(bytes) from dba_segments where segment_name='test1'; SUM(BYTES) SQL>select TABLESPACE_NAME,sum(bytes) from dba_free_space group by TABLESPACE_NAME; no rows selected SQL> DROP TABLE TEST1; Table dropped. SQL>select TABLESPACE_NAME,SUM(BYTES) FROM dba_free_space WHERE TABLESPACE_NAME='TEST' GROUP BY TABLESPACE_NAME; TABLESPACE_NAME SUM(BYTES) TEST 9 SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME TEST1 BIN$JAkhk08TMaDgUL4KHGhpXg==$0 TABLE :14:23:08 SQL> flashback table "BIN$JAkhk08TMaDgUL4KHGhpXg==$0" to before drop; Flashback complete. create table test(id number,name varchar2(10)); drop table test;

5 create table test(id number,depart_name varchar2(10)); drop table test; SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME TEST BIN$HwOgJaEC91vgUL4KHGgVGw==$0 TABLE :17:49:33 TEST BIN$HwOgJaEB91vgUL4KHGgVGw==$0 TABLE :17:49:32 SQL> desc "BIN$HwOgJaEC91vgUL4KHGgVGw==$0"; Name Null? Type ID NUMBER DEPART_NAME VARCHAR2(10) SQL> flashback table "BIN$HwOgJaEC91vgUL4KHGgVGw==$0" to before drop rename to testnew; Flashback complete. SQL> desc testnew; delete 表的恢复 : 开发人员 delete 时使用了错误的 where 条件, 前提是知道操作的大概的时间点, 示例 : SQL> create table emp as select EMPLOYEE_ID,COMMISSION_PCT from hr.employees; 查看下操作系统时间 date SQL> select count(*) from emp; COUNT(*) SQL>delete emp where department_id =100 ;( 其实业务人员是想删除部门 id=10 的员工结果误操作 ) SQL> select count(*) from emp; COUNT(*) 使用闪回表的技术来闪回误删除的数据 : SQL>select count(*) from emp AS OF timestamp to_date(' :18:45','yyyy-mm-dd hh24:mi:ss') where department_id=100; SQL> Flashback table emp to timestamp to_date(' :18:45','yyyy-mm-dd hh24:mi:ss'); Flashback table emp to timestamp to_date(' :18:45','yyyy-mm-dd hh24:mi:ss')

6 ERROR at line 1: * ORA-08189: cannot flashback the table because row movement is not enabled SQL> alter table emp enable row movement; Table altered. SQL> Flashback table emp to timestamp to_date(' :18:45','yyyy-mm-dd hh24:mi:ss'); Flashback complete. SQL> select count(*) from emp; COUNT(*) 来表里稳妥做法 : 利用闪回查询查下删除前的状态, 如果想保留下来就把数据插到一张新表里, 然后 insert 回原通过 SQL>select * from flashback_transaction_query where TABLE_NAME= TEST2 ; 查出指定表的 COMMIT 时间的 SCN 号 SQL>select * from emp AS OF scn Create table emp2 as.. SQL>insert into emp select * from emp2; where department_id=100; 对于 truncate 的表, 除了 flashback database 之外, 其它的 flashback 功能可能帮不了你 ) 闪回查询 : 示例 : SQL>create table emp as select * from employees; SQL>select count(*) from emp; SQL>!date 或 select current_scn from v$database; SQL>delete emp; SQL>COMMIT; SQL>select count(*) from emp; SQL>select count(*) from emp AS OF timestamp to_date(' :18:45','yyyy-mm-dd hh24:mi:ss') where department_id=100;

7 select count(*) from emp AS OF scn where department_id=100; 闪回版本查询 : flashback version query 提供了一个审计行改变的功能, 它能找到所有已经提交了的行的记录比如说, 时间 1, 我插入一条记录, 时间 2 我删除了这条记录, 对于时间 3, 当然查询不到这条记录, 但是 flashback version query 能够把时间 1 时间 2 的操作给记录下来, 并详细的查询出来示例 : SQL> connect testtest Connected. SQL> create table t1(id int,name varchar2(10)); Table created. 我们执行一系列如下的操作 : insert into T1 values(1,'a'); commit; update T1 set ID=2; commit; update T1 set ID=3; commit; update T1 set ID=4; 现在, 我们看到该表最后的记录是 SQL> select * from T1; 4 A 如果我们利用 flash table 或者是 flash query, 我们也只能是看到过去的某一个快照而已, 利用 flashback version query, 我们将找到其中的任何变化 : SQL>select versions_starttime, versions_endtime, versions_xid, versions_operation,id,name from t1 versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;

8 当然, 除了分析以上所有的变更之外, 我们可以指定时间段, 如 select ID,NAME from test versions between timestamp to_date(' :34:04','yyyy-mm-dd hh24:mi:ss') and to_date(' :34:50','yyyy-mm-dd hh24:mi:ss'); 还可以使用 SCN 来找出过去的版本值可以从伪列 VERSIONS_STARTSCN 和 VERSIONS_ENDSCN 中获得 SCN 号以下是一个示例 : select versions_starttime, versions_endtime from test versions between scn 1000 and 1001 注意 : 此处显示了对该表所作的所有更改, 甚至包括该行被删除和重新插入的情况 VERSION_OPERATION 列显示对该行执行了什么操作 (InsertUpdateDelete) 所做的这些工作不需要历史表或额外的列 flashback version query 记载的是行的改变, 如果一个操作有多行的改变, 如 delete from tablename, 那么, 在 flashback version query 对应的也是多行上述查询中, 列 versions_starttime versions_endtime versions_xid versions_operation 是伪列, 与 ROWNUM LEVEL 等其他熟悉的伪列相类似其他伪列如 VERSIONS_STARTSCN 和 VERSIONS_ENDSCN 显示了该时刻的系统更改号列 versions_xid 显示了更改该行的事务标识符闪回事务查询 : Oracle 11g 的闪回事务查询就是对过去某段时间内所完成的事务的查询和撤销 10g 数据来源于 undo,11g 来自 undoredoarchivelog 示例 : a 打开数据库补充日志: 如果不打开补充日志根本就没有 undo_sql sql>alter database add supplemental log data; b 打开主键补充日志: sql>alter database add supplemental log data (primary key) columns; 然后用 em 查询, 和查询闪回版本差不多 : 打开 em----availability----->perform Recovery---- 如下选择 Create table test2 as select * from departments; #################################### transaction 1 INSERT INTO hr.test2 VALUES (333,'ERP', NULL, 1700) INSERT INTO hr.test2 VALUES (444,'Support Operations', NULL, 1700)

9 INSERT INTO hr.test2 VALUES (555,'HR Training', NULL, 1700) commit #################################### transaction 2 update hr.test2 set department_id=300 where department_id=333 commit #################################### transaction 3 update hr.test2 set department_name='abc' commit where department_id=300 基于事务 ID 查询 : select * from flashback_transaction_query where xid='0a000200ee010000'; select * from flashback_transaction_query where TABLE_NAME= TEST2 ; desc flashback_transaction_query Name Null? Type XID RAW(8) -- 事务 ID 对应 Versions Query 中的 VERSIONS_XID START_SCN NUMBER -- 事务开始时的 SCN START_TIMESTAMP DATE -- 事务开始时间 COMMIT_SCN NUMBER -- 事务提交时的 SCN, 该列为空的话, 说明事务为活动事务 COMMIT_TIMESTAMP DATE -- 事务提交时间 LOGON_USER VARCHAR2(30) - - 操作用户 UNDO_CHANGE# NUMBER --UNDO SCN OPERATION VARCHAR2(32) -- 执行的操作, 有几个值 :Delete Insert Update B UNKNOWN TABLE_NAME VARCHAR2(256) --DML 操作对象的表名 TABLE_OWNER VARCHAR2(32) -- 表的属主 ROW_ID VARCHAR2(19) --DML 操作记录的行地址 UNDO_SQL VARCHAR2(4000) -- 撤销该操作对应的 SQL 闪回归档 : 从 Oracle Database 11g 开始,Oracle 提供了一个这样的功能 : 闪回数据归档 (Flashback Data Archive) 通过这一功能 Oracle 数据库可以将 UNDO 数据进行归档, 从而提供全面的历史数据查询, 也因此 Oracle 引入一个新的概念 Oracle

10 Total Recall, 也即 Oracle 全面回忆功能闪回数据归档可以和我们一直熟悉的日志归档类比, 日志归档记录的是 Redo 的历史状态, 用于保证恢复的连续性 ; 而闪回归档记录的是 UNDO 的历史状态, 可以用于对数据进行闪回追溯查询 ; 后台进程 LGWR 用于将 Redo 信息写出到日志文件,ARCH 进程负责进行日志归档 ; 在 Oracle 11g 中, 新增的后台进程 FBDA(Flashback Data Archiver Process) 异步收集原始数据并将其写入闪回数据归档, 闪回数据归档由一个或多个表空间 ( 或其中的几部分 ) 组成您可以拥有多个闪回数据归档每个闪回数据归档都具有特定的保留持续时间您应根据保留持续时间的要求创建不同的闪回数据归档示例 : 1 创建一个默认的闪回数据归档, 取名为 FRA1 : SQL> create tablespace fratbs datafile 'u01apporacleoradataprod4prod4fratbs.dbf' size 100m; SQL> create user fr_admin identified by oracle default tablespace fratbs; SQL> grant connect,dba to fr_admin; SQL> grant flashback archive administer to fr_admin; Grant succeeded. SQL> create flashback archive default fra1 tablespace fratbs quota 50m retention 1 year; 2 创建一个较小的 undo 表空间, 并设置当前数据库使用该 undo 表空间 : SQL> create undo tablespace undotbs2 datafile 'homeoracleundotbs2.dbf' size 10m; SQL> alter system set undo_tablespace=undotbs2; SQL>STARTUP FORCE; SQL>select segment_name,tablespace_name from dba_undo_extents; 确定下是否使用新的 undo 表空间或查看所有回滚段信息 : SQL>select segment_name,status,tablespace_name from dba_rollback_segs; 创建一个测试表 : SQL>create table t5 as select * from dba_objects; SQL>SELECT COUNT(*) FROM T5; COUNT(*) SQL>SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN

11 SQL>DELETE T5 WHERE ROWNUM<10000; SQL>COMMIT; SELECT COUNT(*) FROM T5 AS OF SCN ; 看执行计划是数据是从 undo 中查到的如不相信可以进行下面操作 : SQL> alter system set undo_tablespace=undotbs1; SQL> drop tablespace undotbs2 including contents and datafiles; 把 undotbs2 表空间删除 SQL> SELECT COUNT(*) FROM T5 AS OF SCN ; ERROR at line 1: ORA-01466: unable to read data - table definition has changed 证明上面的闪回查询是来自 undo 给表开启闪回归档确定下现有行数, 然后给表打开闪回归档 SQL>SELECT COUNT(*) FROM T5; SQL> alter table HR.T5 flashback archive FRA1; 创建一个小的 undo 表空间 SQL>create undo tablespace undotbs2 datafile 'u01apporacleoradataprod4prod4undotbs2.dbf' size 10m; SQL> alter system set undo_tablespace=undotbs2; SQL> select current_scn from v$database; -- 记下删除前的数据行执行删除表中数据 SQL>delete t5 WHERE ROWNUM<10000; COMMIT; SQL> alter system set undo_tablespace=undotbs1; SQL> drop tablespace undotbs2 including contents and datafiles; 把 undotbs2 表空间删除 SQL>set autot trace exp SQL> select count(*) from t5 as of scn ; 更狠一点 SQL> truncate table t5; Table truncated. SQL> select count(*) from t5 as of scn ; SQL> alter table t5 rename to t55; SQL> select count(*) from t55 as of scn ; SQL> alter table t55 drop column EDITION_NAME; SQL> select * from t55 where rownum<2 as of scn ; 通过执行计划可以看到, 查询闪回来自 SYS_FBA_TCRV_ 系统表, 该表隶属于闪回归档表空间, 用于记录闪回数据提醒 : 在表上开启闪回归档后, 表的一切操作都会变慢!

12 DDL 支持 : 添加删除重命名和修改列删除和截断分区重命名和截断表常用数据字典 : 闪回功能生成的字典对象有多个, 通过查询 USER_TABLESUSER_OBJECTS 视图可以获得这些对象的详细信息 : SQL> select table_name,tablespace_name from user_tables where table_name like '%FBA%'; SQL> select OWNER_NAME,FLASHBACK_ARCHIVE_NAME,RETENTION_IN_DAYS,STATUS from DBA_FLASHBACK_ARCHIVE; SQL>select TABLE_NAME,OWNER_NAME,FLASHBACK_ARCHIVE_NAME,ARCHIVE_TABLE_NAME,STATUS from DBA_FLASHBACK_ARCHIVE_TABLES; SQL> select * from DBA_FLASHBACK_ARCHIVE_TS 查询闪回数据归档的创建时间 : SELECT FLASHBACK_ARCHIVE_NAME, CREATE_TIME, STATUS FROM DBA_FLASHBACK_ARCHIVE; 列出闪回数据归档使用的表空间 : SELECT * FROM DBA_FLASHBACK_ARCHIVE_TS; 列出特定表的归档表名称 : SELECT ARCHIVE_TABLE_NAME FROM USER_FLASHBACK_ARCHIVE_TABLES WHERE TABLE_NAME = 'EMPLOYEES'; 可以选择增加空间 : ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs3 QUOTA 5G; 可以选择清除数据 : ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' day); 可以选择更改保留时间 : ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR; 可以选择删除闪回数据归档 : DROP FLASHBACK ARCHIVE fla1; 显示有关闪回数据归档的信息 *_FLASHBACK_ARCHIVE 显示闪回数据归档的表空间 *_FLASHBACK_ARCHIVE_TS 显示有关启用了闪回归档的表的信息 *_FLASHBACK_ARCHIVE_TABLES

三. 发现表被删除, 开始着手解决 1. 该表所在表空间离线 ( 确保删除表所在位置不会被重写 ) SQL> alter tablespace raw_odu offline; Tablespace altered. 2. 通过 logmnr, 找出被删除的数据 data _object _id 1

三. 发现表被删除, 开始着手解决 1. 该表所在表空间离线 ( 确保删除表所在位置不会被重写 ) SQL> alter tablespace raw_odu offline; Tablespace altered. 2. 通过 logmnr, 找出被删除的数据 data _object _id 1 使用 odu 恢复被 drop 表过程一. 数据库版本 SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production PL/SQL Release

甲骨人-OCM集训营（QQ群号： ）

甲骨人-OCM集训营（QQ群号：）