Logminer DDL变更场景测试

一、Logminer字典解析测试

表结构变更与Logminer数据字典测试
测试目的：
1、验证表结构的变更是否会修改数据字典
2、某张表结构的变更是否只影响其本身的数据字典

1、开启最小附加日志

alter database add supplemental log data;

select SUPPLEMENTAL_LOG_DATA_ALL log_ALL,SUPPLEMENTAL_LOG_DATA_FK FK,SUPPLEMENTAL_LOG_DATA_MIN log_MIN,SUPPLEMENTAL_LOG_DATA_PK PK,SUPPLEMENTAL_LOG_DATA_UI UI from v$database;


ALL       FK        MIN                      PK     UI
--------- --------- ------------------------ --------- ---------
NO        NO        YES                       NO        NO

2、创建测试表EMP

create table emp(id number,name varchar2(20));

插入测试数据
insert into emp values(1,'a');
commit;

3、其他表TEST4插入测试数据

select * from test4;

no rows selected

insert into test4 values(1,'ok');
commit;

select * from test4;

        ID NAME
---------- ----------
         1 ok

4、修改表emp表结构，增加一数据列

alter table emp add sex varchar2(20);
insert into emp values(2,'b','man');
commit;

5、切换日志

alter system switch logfile;

6、解析日志

exec dbms_logmnr.add_logfile('/u01/arch/1_4_995726293.dbf',options=>dbms_logmnr.new);

 exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

7、提取解析日志

 create table log_new2 as select * from v$logmnr_contents;


 select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo from log_new2 where username='ADMIN' and table_name in ('EMP','TEST4')


exec dbms_logmnr.end_logmnr();

测试结论：
某张表结构发生变更之后，数据字典会被修改，使用logminer解析其他表的DML解析，无影响

##表删除测试
测试表被删除之后，前后DDL以及DML日志解析情况

1、创建表test

SQL> create table sales(id number,name varchar2(20));
08:57:31

2、插入一行测试数据

SQL> insert into sales values(1,'no1');
SQL> commit;
08:58:14

3、插入另外一张表测试数据

SQL> insert into new values(2,'no2');
SQL> commit;
08:58:34 

4、删除测试表

SQL> drop table sales;
08:58:47

5、重新创建测试表

SQL> create table sales(id number,name varchar2(20));
08:58:57

6、重新插入测试表

SQL> insert into sales values(3,'no3');
SQL> commit;
08:59:16

7、分析归档日志

SQL> select timestamp,scn,sql_redo from v$logmnr_contents where username='ADMIN' 
and table_name in ('SALES','NEW');

TIMESTAMP         SCN SQL_REDO
---------- ---------- ------------------------------------------------------------
2018/12/25    1774575 create table sales(id number,name varchar2(20));
 08:57:31

2018/12/25    1774615 insert into "ADMIN"."NEW"("ID","NAME") values ('2','no2');
 08:58:34

2018/12/25    1774635 ALTER TABLE "ADMIN"."SALES" RENAME TO "BIN$fc7HL0kxNTfgU4fOq
 08:58:47             MBvjw==$0" ;

2018/12/25    1774638 drop table sales AS "BIN$fc7HL0kxNTfgU4fOqMBvjw==$0" ;
 08:58:47

2018/12/25    1774655 create table sales(id number,name varchar2(20));
 08:58:57

2018/12/25    1774673 insert into "ADMIN"."SALES"("ID","NAME") values ('3','no3');
 08:59:16


6 rows selected.

测试结果：
测试表做drop table操作之后，logminer查询不到其被删除之前的DML记录，不对其他表的DML解析有影响

使用旧数据字典测试
检验Logminer使用老的数据字典，在表结构发生变化之后的解析情况

1、创建测试表test2

SQL> create table test2 (id number,name varchar2(20));

Table created.
--09:37:04 

SQL> insert into test2 values(1,'a');

1 row created.

SQL> commit;

Commit complete.

--09:37:23

2、logminer提取数据字典到归档日志

exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_redo_logs);

3、增加一列，表结构改变

 SQL> alter table test2 add sex varchar2(20);

Table altered.
--09:58:44

 SQL> insert into test2 values(2,'tom','man');
SQL> commit;
10:08:50

4、分别使用2种类型方法解析日志
使用当前数据字典

exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

使用归档的数据字典

exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_redo_logs);

5、测试结果
结果1
online catalog
使用当前数据字典可以解析表test2增加一列之后插入数据的DML，由于数据字典已被修改，之前的DML无法成功解析。

结果2
redo dict
使用之前生成的数据字典归档日志，可以完整解析表TEST2表结构变更之前的DML语句.

因为是使用的旧的数据字典，所以表test2增加一列之后，新插入的数据的DML无法解析

二、RMAN不完全恢复

测试目的：
验证是否可以通过RMAN实现不完全恢复，将数据恢复到表结构变更之前的状态。

1、查询是否开启附加日志

select SUPPLEMENTAL_LOG_DATA_ALL log_ALL,SUPPLEMENTAL_LOG_DATA_FK FK,SUPPLEMENTAL_LOG_DATA_MIN log_MIN,SUPPLEMENTAL_LOG_DATA_PK PK,SUPPLEMENTAL_LOG_DATA_UI UI from v$database;


log_ALL       FK        log_MIN                      PK     UI
--------- --------- ------------------------ --------- ---------
NO        NO        NO                       NO        NO

2、做RMAN全备
T0时间点

    RMAN> backup database format '/u01/rman/full_%d_%U.bak';

    Finished backup at 2018/12/24 14:02:31

3、创建表emp

	create table emp(id number,name varchar2(20));
    
    14:02:52  创建表emp完毕

4、插入emp第一条数据

    insert into emp values(1,'a');
    commit;

    14:03:04 第一条数据插入完毕

5、插入test4表一条数据

    select * from test4;
   no rows selected



    insert into test4 values(1,'ok');
    commit;
     
    14:03:21 插入完毕

6、表emp增加1列以及插入emp第二条数据

T1时间点发生表结构变化

   alter table emp add sex varchar2(20);
   14:03:39 表结构发生变化




    insert into emp values(2,'b','man');
    commit;
    
    14:03:58 第二条数据插入完毕

7、查询两张表数据

       select * from emp;
        ID NAME       SEX
---------- ---------- ----------
         1 a
         2 b          man



       select * from test4;
        ID NAME
---------- ----------
         1 ok

8、切换日志

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

9、假设emp表被认为表结构需要恢复原状，需要将表恢复增加列之前

RMAN不完全恢复到14:03:39之前（表emp增加列的时间点T1）

rman target /

startup force mount;

run{
set until time "to_date('2018-12-24 14:03:38','yyyy-mm-dd hh24:mi:ss')";
restore database;
recover database;
}

    
Finished recover at 2018/12/24 12:54:42

RMAN> alter database open resetlogs;

9、查看数据

SQL> conn admin/admin123
Connected.


SQL> select * from emp;

        ID NAME
---------- ----------
         1 a


SQL> select * from test4;

        ID NAME
---------- ----------
         1 ok

结果：未打开附加日志，rman也可以恢复数据到修改表结构T1时间点之前，T0到T1这段时间的数据完好。

三、测试总结

1、一旦某张表发生过DDL的变更，数据字典会被修改。
2、通过几个场景的测试发现RMAN在做recover的时候不会被数据字典的修改所影响，可以通过基于时间点的不完全恢复数据。

四、参考文献

MOS文献
追加日志是否开启与Logminer测试结果
Effect of Supplemental Logging on LogMiner with Example (文档 ID 750198.1)