首页 > 代码库 > oracle-闪回技术2

oracle-闪回技术2

 

闪回版本查询,用到了附加日志

闪回事务查询

 

http://blog.csdn.net/laoshangxyc/article/details/12405459  这个博客的备份与恢复可以参考

###################################

http://blog.itpub.net/18922393/viewspace-697625/

 

附加日志(supplemental log)可以指示数据库在日志中添加额外信息到日志流中,
以支持基于日志的工具,如逻辑standby、streams、GoldenGate、LogMiner。
可以在数据库和表上设置。

#################################

1.数据库级设置,分两类:
1.1最小附加日志(minimal supplemental logging):
DATA选项启用最小附加日志。启用最小日志可以确保LogMiner
(或其他任何基于LogMiner的产品)可以支持行链接、簇表、索引组织表等。
语法如下:
alter database {add|drop} supplemental log data;

1.2标识键日志(identification key logging):
DATA(all,primary key,unique,foreign key) columns选项启用最小日志及列数据日志。
在源库日志为变化来源同步其他数据库的情况下,比如逻辑备用数据库,
受影响的数据行必须以列数据标识(而不是rowid),必须启用此种附加日志。
语法如下:
alter database {add|drop} supplemental log {data(all,primary key,unique,foreign key) columns};

1.3缺省情况下,Oracle不启用以上任何附加日志。当使用ALL,PRIMARY,UNIQUE或FOREIGN附加日志时最小补全日志默认开启(即检查结果为IMPLICIT)。
在删除所有导致IMPLICIT最小化附加日志的附加日志后,最小化附加日志变为NO。

###############################

2,表级附加日志设置,分两类
2.1,可以通过以下语句设置命名日志组:

语法:
alter table table_name
add supplemental log group group_a(column_a [no log],column_b,...) [always];

NO LOG选项用于指定在日志中排除哪些列。在命名日志组中,至少存在一个无”NO LOG“的定长列。
比如,对LONG列使用 no log选项,可以在更改LONG列时,记录其他列的内容(LONG列本身不能存在日志里)。

ALWAYS选项, 在更新时,日志组中的所有列都会记录在日志中。这就是所谓的”无条件“日志组,有时也叫”always log group“。
如果不指定该选项,只有在日志组中的任何列被修改时,所有列才会出现在日志中。
这就是所谓的”有条件“日志组。

说明:同一列可以在多个日志组中存在,但日志中只记录一次;
同一列在“无条件”与“有条件”日志组中存在时,该列将“无条件”记录。

2.2,可以通过以下语句设置所有列或主键/外键/唯一键组合日志组:

语法:
alter table table_name
add supplemental log data(all,primary key,unique,foreign key) columns;

Oracle将生成无条件或有条件日志组。对于无条件日志组,日志中将记录该日志组中的所有列;
对于有条件日志组,只有日志组中的列有变化时,才会记录日志组中的所有列。

如果指定“ALL”列,日志中将包含所有最大大小固定长度的列。这种日志是系统创建的无条件日志组。
如果指定“PRIMARY KEY”列,只要有更新,组成主键的所有列都会记录在日志中。这种日志是系统创建的无条件日志组。Oracle使用如下顺序确定附加记录哪些列:
*组成主键的列(主键有效,或rely且非DISABLED or INITIALLY DEFERRED状态)
*最小的、至少有一个非空列的唯一索引
*记录所有标量列
如果指定“UNIQUE”列,如果任何组成唯一键或位图索引的列被修改,组成该唯一键或位图索引的其他列都会记录在日志中。这种日志是系统创建的有条件日志组。
如果指定“FOREIGN KEY”列,如果任何组成外键的列被修改,组成该外键的其他列都会记录在日志中。这种日志是系统创建的有条件日志组。


2.3表级测试
drop table test;
create table test(x int,y int);

--增加附加日志
alter table test
add supplemental log data(all,primary key,unique,foreign key) columns;

select * from dba_log_groups;
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
SYS SYS_C0037555 TEST ALL COLUMN LOGGING ALWAYS GENERATED NAME
SYS SYS_C0037556 TEST PRIMARY KEY LOGGING ALWAYS GENERATED NAME
SYS SYS_C0037557 TEST UNIQUE KEY LOGGING CONDITIONAL GENERATED NAME
SYS SYS_C0037558 TEST FOREIGN KEY LOGGING CONDITIONAL GENERATED NAME

select * from dba_log_group_columns;
no row selected

--删除附加日志
alter table test
drop supplemental log data(all,primary key,unique,foreign key) columns;

select * from dba_log_groups;
no row selected

select * from dba_log_group_columns;
no row selected

--增加命名附加日志
alter table test
add supplemental log group group_a(x,y);

select * from dba_log_groups;
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
SYS GROUP_A TEST USER LOG GROUP CONDITIONAL USER NAME

select * from dba_log_group_columns;
OWNER LOG_GROUP_NAME TABLE_NAME COLUMN_NAME POSITION LOGGING_PROPERTY
SYS GROUP_A TEST X 1 LOG
SYS GROUP_A TEST Y 2 LOG

--删除命名附加日志
alter table test
drop supplemental log group group_a;

###############################

语句汇总

alter database add supplemental log data;
alter database add supplemental log data (all) columns;
alter database add supplemental log data (primary key) columns;
alter database add supplemental log data (foreign key) columns;
alter database add supplemental log data (unique) columns;
alter database add supplemental log data for procedural replication;

丢弃时有先后顺序的,最后才能丢弃log data,其它没有次序。
alter database drop supplemental log data (all) columns;
alter database drop supplemental log data (primary key) columns;
alter database drop supplemental log data (foreign key) columns;
alter database drop supplemental log data (unique) columns;
alter database drop supplemental log data for procedural replication;
alter database drop supplemental log data;

查询开关是否打开
select Supplemental_Log_Data_Min min1,Supplemental_Log_Data_All all1,
Supplemental_Log_Data_Pk pk,Supplemental_Log_Data_Fk fk,
Supplemental_Log_Data_Ui ui,Supplemental_Log_Data_Pl pl
from v$database;

 

###################################

 

日志输出

tail -f /u01/app/oracle/diag/rdbms/kyc/kyc/trace/alert_kk.log

Fri Apr 28 18:05:54 2017
alter database add supplemental log data
SUPLOG: Previous supplemental logging attributes at scn = 996039
SUPLOG:  minimal = OFF, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
SUPLOG:  procedural replication = OFF
SUPLOG: New supplemental logging attributes at scn = 996039
SUPLOG:  minimal = ON, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
SUPLOG:  procedural replication = OFF
Completed: alter database add supplemental log data
alter database add supplemental log data (primary key) columns
SUPLOG: Previous supplemental logging attributes at scn = 996049
SUPLOG:  minimal = ON, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
SUPLOG:  procedural replication = OFF
SUPLOG: New supplemental logging attributes at scn = 996049
SUPLOG:  minimal = ON, primary key = ON
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
SUPLOG:  procedural replication = OFF
Completed: alter database add supplemental log data (primary key) columns

 

######################################

Fri Apr 28 15:13:50 2017
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 119196142
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Fri Apr 28 15:14:09 2017
alter database archivelog
Completed: alter database archivelog
Fri Apr 28 15:16:59 2017
alter database flashback on
Starting background process RVWR
Fri Apr 28 15:16:59 2017
RVWR started with pid=22, OS id=3403
Allocated 8388608 bytes in shared pool for flashback generation buffer
Flashback Database Enabled at SCN 991029
Completed: alter database flashback on

#################################

 

http://blog.csdn.net/laoshangxyc/article/details/12405459

 

Flashback Version Query 闪回版本查询
使用Flashback Version Query 返回在指定时间间隔或SCN间隔内的所有版本,一次commit命令就会创建一个版本。
语法如下:
SELECT .....FROM tablename VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}
--start,end可以是时间也可以是scn


--可以看出,一次commit是一个版本,当前版本的versions_endscn和versions_endtime值为空,旧版本则有值。
Flashback Transaction Query闪回事务查询
Flashback Transaction Query实际上是查询的数据字典flashback_transaction_query。可以根据flashback_transaction_query 的undo_sql列值返回数据以前版本。


--使用闪回事务查询前,必须启用重做日志流的其他日志记录,重做日志流与Log Miner使用的数据相同,只是接口不同。

select Supplemental_Log_Data_Min min1,Supplemental_Log_Data_All all1,
Supplemental_Log_Data_Pk pk,Supplemental_Log_Data_Fk fk,
Supplemental_Log_Data_Ui ui,Supplemental_Log_Data_Pl pl
from v$database;

--开启日志
alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;

--更改值
select * from e1;
update e1 set name=‘uuu‘ where name=‘ni‘;
commit;
update e1 set name=‘ooo‘ where name=‘uuu‘;
commit;

--查询事务id,下面全是版本查询伪列

Flashback Version Query伪列说明
versions_start{scn|time}  版本开始的scn或时间戳
versions_end{scn|time}  版本结束scn或时间戳,如果有值表明此行后面被更改过是旧版本,如果为null,则说明行版本是当前版本或行被删除(即versions_operation值为D)。
versions_xid 创建行版本的事务ID
versions_operation  在行上执行的操作(I=插入,D=删除,U=更新)


select versions_startscn,versions_starttime,
versions_endscn,versions_endtime,
versions_xid,versions_operation
from e1 versions between timestamp
to_timestamp(‘2017-04-28 18:09:58‘,‘YYYY-MM-DD HH24:MI:SS‘)
and
to_timestamp(‘2017-04-28 18:14:58‘,‘YYYY-MM-DD HH24:MI:SS‘);


--模糊查询
select * from flashback_transaction_query
where logon_user!=‘SYS‘ and table_name not like ‘%$‘;
--查询undo sql
select start_scn,commit_scn,logon_user,operation,table_name,undo_sql
from flashback_transaction_query
where xid=hextoraw(‘09000F0098030000‘);
--返回上一个版本

 

################################

 

oracle-闪回技术2