LogMiner日志挖掘分析管理

一、Logminer 相关概念

1. logminer 的作用

        Oracle LogMiner 是 Oracle 公司从产品 8i 以后提供的一个实际非常有用的分析工具，使用该工具可以轻松获得 Oracle 在线/归档日志文件中的具体内容，特别是该工具可以分析出所有对于数据库操作的DML和DDL语句。该工具特别适用于调试、审计或者回退某个特定的事务。

        LogMiner 分析工具实际上是由一组 PL/SQL 包和一些动态视图组成，它作为 Oracle数据库的一部分来发布是一个完全免费的工具。但该工具和其他 Oracle 内建工具相比使用起来显得有些复杂，主要原因是该工具没有提供任何的图形用户界面（GUI）。

		我们知道日志文件中存放着所有进行数据库恢复的数据，记录了针对数据库结构的每一个变化，也就是对数据库操作的所有 DML 语句进行记录。logminer 工具即可以用来分析在线，也可以用来分析离线日志文件，即可以分析本身自己数据库的重作日志文件，也可以用来分析其他数据库的重作日志文件。

--**总的说来，logminer 工具的主要用途有：** --

1．跟踪数据库的变化：可以离线的跟踪数据库的变化，而不会影响在线系统的性能。
2．回退数据库的变化：回退特定的变化数据，减少 point-in-time recovery 的执行。
3．优化和扩容计划：可通过分析日志文件中的数据以分析数据增长模式。

2. LogMiner 不支持的数据类型和表存储属性

bfile；

 抽象数据类型；

 集合类型（嵌套表和 varray）；

 参照对象；

 xmltype；

 具有 LOB 列的索引组织表；

 使用 compress 特征的表。

 除以上类型外，基本上都是支持的。

3. LogMiner 的配置要求

 (1)源数据库和分析数据库必须运行在相同硬件平台上；
 (2)分析数据库可以是独立数据库或源数据库；
 (3)分析数据库的版本不能低于源数据库的版本；
 (4)分析数据库与源数据库必须具有相同的字符集；

4. LogMiner 使用注意事项

 (1)LogMiner 字典必须在源数据库中生成
 (2)当分析多个重做日志和归档日志时，它们必须是同一个源数据库的重做日志和归档日志
 (3)当分析多个重做日志和归档日志时，它们必须具有相同的 resetlogs scn
 (4)当分析的重做日志和归档日志必须在 Oracle8.0 版本以上

5. 什么是补充日志，补充日志的作用是什么？

重做日志用于实现例程恢复和介质恢复，这些操作所需要的数据被自动记录在重做
日志中。但是，重做应用可能还需要记载其他列信息到重做日志中，记录其他列的日
志过程被称为补充日志
 默认情况下，Oracle 数据库没有提供任何补充日志，从而导致默认情况下
LogMiner 无法支持以下特征：
 （1）索引簇、链行和迁移行；
 （2）直接路径插入；
 （3）摘取 LogMiner 字典到重做日志；
 （4）跟踪 DDL；
 （5）生成键列的 SQL_REDO 和 SQL_UNDO信息；
 （6）LONG 和 LOB 数据类型。

6.激活数据库补充日志

因此，为了充分利用 LogMiner 提供的特征，必须激活补充日志。
 
注意：激活不用重启数据库，数据库联机即可.

在做之前，检查下 suppplemental logging：

SYS@orcl > select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
NO

SYS@orcl > alter database add supplemental log data;

Database altered.

SYS@orcl > select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
YES
///如果是 YES 或者 IMPLICIT 则表明已经生效了，否则需要启动：

（## 删除 supplemental log （如有要想删除，执行这条命令）
alter database drop supplemental log data;）

supplemental logging（扩充日志）：在通常情况下，redo log 只记录的进行恢复所必需的信息，但是这些信息对于我们使用 redo log 进行一些其他应用时是不够的。

例如在 redo log 中使用 rowid 唯一标识一行而不是通过 Primary key，如果我们在另外的数据库分析这些日志并想重新执行某些 dml 时就可能会有问题，因为不同的数据库其 rowid 代表的内容是不同的。在这时候就需要一些额外的信息（columns）加入 redo log，这就是 supplemental logging。

supplemental logging分为两个级别database_level and table_level，默认不开启，建议开启数据库级别。

二、Logminer 使用详解

LogMiner 两种使用类型，一种是使用源数据库的数据字典分析 DML 操作，另一种是提取 LogMiner 数据字典到字典文件分析 DDL 操作。

1.安装 logminer

$ORACLE_HOME/rdbms/admin/dbmslm.sql --- DBMS_LOGMNR---》创建字典包，是用来分析日志的
$ORACLE_HOME/rdbms/admin/dbmslmd.sql --- DBMS_LOGMNR_D---》创建另一个字典包，
$ORACLE_HOME/rdbms/admin/dbmslms.sql --	建议这个也最好安装上去，这个是做logminer会话管理的，里面有一些会话视图

## 进行创建 （不管之前是否创建都进行创建一次，不影响的）
SYS@orcl > @$ORACLE_HOME/rdbms/admin/dbmslm.sql

Package created.


Grant succeeded.


Synonym created.

SYS@orcl > 

SYS@orcl > @$ORACLE_HOME/rdbms/admin/dbmslmd.sql

Package created.


Synonym created.

SYS@orcl >

--过程
dbms_logmnr_d.build 创建一个数据字典文件
dbms_logmnr.add_logfile  添加日志文件
dbms_logmnr.start_logmnr 根据上面的字典和文件来启动 logmnr ,进行分析
dbms_logmnr.end_logmnr  结束分析

--视图
select * from v$logmnr_dictionary;  决定对象名称id 的数据字典信息
select * from v$logmnr_logs;  logmnr 启动的时候显示要分析的日志列表
select * from v$logmnr_contents;  logmnr 启动以后可以使用该视图（分析的sql 结果在这个里面）

2.使用源数据库的数据字典（Online catalog)来分析 DML 操作

## 01.开启补充日志
SYS@orcl > select SUPPLEMENTAL_LOG_DATA_MIN from v$database; 

SUPPLEME
--------
YES

如果没有打开就执行这条语句：
alter database add supplemental log data;


## 02.建立日志分析列表
execute dbms_logmnr.add_logfile(logfilename=>'日志文件',options=>dbms_logmnr.new)

-- 继续添加
execute dbms_logmnr.add_logfile(logfilename=>'日 志文件',options=>dbms_logmnr.addfile)
execute dbms_logmnr.add_logfile(logfilename=>'日志文件'，options=>dbms_logmnr.addfile)

这样写也可以//execute dbms_logmnr.add_logfile('日志文件'，dbms_logmnr.addfile)

-- 移除命令
execute dbms_logmnr.remove_logfile(logfilename=>'日志文件')

## 03.启动分析
execute dbms_logmnr.start_logmnr(Options => dbms_logmnr.dict_from_online_catalog)

分析某个scn 到某个scn:
select dbms_flashback.get_system_change_number from dual;(获得当前改变号scn)
execute dbms_logmnr.start_logmnr(Options =>dbms_logmnr.dict_from_online_catalog,startscn=>123,endScn => 124);


//exec dbms_logmnr.start_logmnr(Options => dbms_logmnr.dict_from_online_catalog,
 starttime => to_date('2016-08-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),
 endtime => to_date('2016-08-15 01:00:00','YYYY-MM-DD HH24:MI:SS');
 
 ## 04.查看日志分析结果
 select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents;
 
 ## 05.结束分析
dbms_logmnr.end_logmnr;

3. 使用 LogMiner 字典到字典文件来分析 DDL 操作

## 01.提取 logminer 字典
--设置一个字典文件路径：
show parameter utl_file_dir --需要重启 DB
alter system set utl_file_dir='/data' scope=spfile;
--创建一个数据字典文件
exec dbms_logmnr_d.build('dict.ora','/data');

## 02.建立日志分析列表
execute dbms_logmnr.add_logfile(logfilename=>'日志文件'，options=>dbms_logmnr.new)

--继续添加
execute dbms_logmnr.add_logfile(logfilename=>'日志文件'，options=>dbms_logmnr.addfile)
execute dbms_logmnr.add_logfile(logfilename=>'日志文件'，options=>dbms_logmnr.addfile)
或者
//execute dbms_logmnr.add_logfile('日志文件'，dbms_logmnr.addfile)

--移除
execute dbms_logmnr.remove_logfile(logfilename=>'日志文件')

## 03.启动分析
exec dbms_logmnr.start_logmnr(DictFileName => '/data/dict.ora');---无条件分析

分析某个scn 到某个scn:
select dbms_flashback.get_system_change_number from dual;(获得当前改变号scn)
//exec dbms_logmnr.start_logmnr(DictFileName => '/data/dict.ora',startscn=>123,endScn => 124); --有条件分析

//exec dbms_logmnr.start_logmnr(DictFileName => '/data/dict.ora',
 starttime => to_date('2016-08-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),
 endtime => to_date('2016-08-15 01:00:00','YYYY-MM-DD HH24:MI:SS');
 
--有条件分析：
scn: startscn,endScn
time: starttime,endtime

## 04.查看日志分析结果
select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents;

## 05.结束分析
dbms_logmnr.end_logmnr;

// 一般用在线分析，这样不需要重启数据库，避免出现不变要的事情

4.使用 LogMiner 进行日志分析

exec dbms_logmnr.start_logmnr(DictFileName => '/oracle/dict.ora');---无条件分析

//exec dbms_logmnr.start_logmnr(DictFileName => '/oracle/dict.ora',startscn=>123,endScn => 124); --有条件分析

//exec dbms_logmnr.start_logmnr(DictFileName => '/oracle/dict.ora',
 starttime => 
 to_date('2016-08-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),
 endtime => 
 to_date('2016-08-15 01:00:00','YYYY-MM-DD HH24:MI:SS');
 
 --有条件分析：
scn: startscn,endScn
time: starttime,endtime

观察分析结果：（v$logmnr_contents)
动态性能视图 v$logmnr_contents 包含LonMiner 分析得到所有的信息。
select sql_redo from v$logmnr_contents;

如果我们仅仅想知道某个用户对于那张表的操作，可以通过下面的SQL查询得到，该查询可以得到用户LOGMINER对表EMP所做的一切工作。
## 查看 logminer 分析结果
SQL>select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents;

SQL> desc v$logmnr_contents;
名称 类型
----------------------------------------- ----------------------------
TIMESTAMP DATE 	//SQL 执行时间
COMMIT_TIMESTAMP DATE 	//事务提交时间
SEG_OWNER VARCHAR2(32) 	//被修改对象创建者
SEG_NAME VARCHAR2(256) 	//被修改对象的名字，如表名
SEG_TYPE NUMBER 	//被修改对象类型
SEG_TYPE_NAME VARCHAR2(32) 	//被修改对象类型名
TABLE_SPACE VARCHAR2(32) 	//被修改对象所属表空间
ROW_ID VARCHAR2(19) 	//被修改行的 ROWID，如果
SESSION# NUMBER 	//执行修改的 SESSION 号
SERIAL# NUMBER 	//执行修改的 SESSION 序号
USERNAME VARCHAR2(30) 	//执行事务的用户名
SESSION_INFO VARCHAR2(4000) 	//执行修改的 SESSION 信 息 , 例如： login_username= client_info= 
OS_username=SYSTEM Machine_name=ZFMISERVER OS_terminal=ZFMISERVER OS_process_id=1812 OS_program name=ORACLE.EXE

TX_NAME VARCHAR2(256) 	//执行的事务名，当该事务被命名时
ROLLBACK NUMBER 	//回滚标记

OPERATION VARCHAR2(32) 	//操作类型
 INSERT
 UPDATE
 DELETE
 DDL
 START
 COMMIT
 ROLLBACK
 LOB_WRITE
 LOB_TRIM
 LOB_ERASE
 SELECT_FOR_UPDATE
 SEL_LOB_LOCATOR
 MISSING_SCN
 INTERNAL
 
 UNSUPPORTED OPERATION_CODE NUMBER 	//操作类型代码
 0 = INTERNAL
 1 = INSERT
 2 = DELETE
 3 = UPDATE
 5 = DDL
 6 = START
 7 = COMMIT
 9 = SELECT_LOB_LOCATOR
 10 = LOB_WRITE
 11 = LOB_TRIM
 25 = SELECT_FOR_UPDATE
 28 = LOB_ERASE
 34 = MISSING_SCN
 36 = ROLLBACK
 255 = UNSUPPORTED
 SQL_REDO VARCHAR2(4000) 	//重做日志 SQL
 SQL_UNDO VARCHAR2(4000) 	//相反操作 SQL
 SEQUENCE# NUMBER 	//重做日志的序号

三、分析生产系统表数据丢失的原因

1 进行试验（DDL)

1.1 创建实验环境

由于某生产系统 itpux 用户模式下 itpuxtb 表数据丢失，需要分析丢失的原因，下面是整个恢复步骤:

## 直接执行一下操作

create tablespace itpux01 datafile '/data/app/oracle/oradata/orcl/itpux01.dbf' size 
100m;
create user itpux01 identified by itpux01 default tablespace itpux01;
grant dba to itpux01;
conn itpux01/itpux01
create table itpux01 (id number(10),name varchar2(10));
insert into itpux01 values(1,'itpux01');
insert into itpux01 values(2,'itpux02');
insert into itpux01 values(3,'itpux03');
insert into itpux01 values(4,'itpux04');
insert into itpux01 values(5,'itpux05');
commit;
select * from itpux01.itpux01;
alter system switch logfile;
alter system checkpoint;
drop table itpux01;
conn /as sysdba;
alter system switch logfile;

// 目前这个表创建了，但是目前的结果是又被我删除了

1.2. 安装 logminer

## 安装 logminer
@$ORACLE_HOME/rdbms/admin/dbmslm.sql
@$ORACLE_HOME/rdbms/admin/dbmslmd.sql
@$ORACLE_HOME/rdbms/admin/dbmslms.sql

## 开启补充日志
select SUPPLEMENTAL_LOG_DATA_MIN from v$database; 
alter database add supplemental log data;

1.3. 使用 LogMiner 字典到字典文件来分析 DDL 操作

## 01.提取 logminer 字典
--设置一个字典文件路径：
SQL>show parameter utl_file_dir --需要重启 DB
SQL>alter system set utl_file_dir='/data' scope=spfile;
SQL>shutdown immediate
SQL>startup

--创建一个数据字典文件
SYS@orcl > exec dbms_logmnr_d.build('dict.ora','/data');

PL/SQL procedure successfully completed.

SYS@orcl > 

查看添加结果
[oracle@ogg12c data]$ ls
app  database  dict.ora  dpdata
[oracle@ogg12c data]$ pwd
/data
[oracle@ogg12c data]$ 

## 02.建立日志分析列表
之前进行了两次日志的切换，生成了两个归档
[oracle@ogg12c 2020_06_04]$ ll -rt
total 25584
-rw-r----- 1 oracle oinstall  1617408 Jun  4 09:05 o1_mf_1_81_hfjl6ptt_.arc
-rw-r----- 1 oracle oinstall    20480 Jun  4 09:05 o1_mf_1_82_hfjl6qpq_.arc
[oracle@ogg12c 2020_06_04]$ pwd
/data/app/oracle/fast_recovery_area/ORCL/archivelog/2020_06_04
[oracle@ogg12c 2020_06_04]$ 

-- 建立日志分析列表
SYS@orcl > execute dbms_logmnr.add_logfile(logfilename=>'/data/app/oracle/fast_recovery_area/ORCL/archivelog/2020_06_04/o1_mf_1_81_hfjl6ptt_.arc',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SYS@orcl > execute dbms_logmnr.add_logfile(logfilename=>'/data/app/oracle/fast_recovery_area/ORCL/archivelog/2020_06_04/o1_mf_1_81_hfjl6ptt_.arc',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

## 03.启动分析
SYS@orcl > exec dbms_logmnr.start_logmnr(DictFileName => '/data/dict.ora');

PL/SQL procedure successfully completed.

SYS@orcl > 

## 04.查看日志分析结果
select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents; //最好不要直接查询，用下面的方法创建一张表来进行查询
create table itpux01.logmnr_temp nologging as select * from v$logmnr_contents;
（如果在创建表的时候提示表空间不够大，需要增大表空间
SYS@orcl > select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/data/app/oracle/oradata/orcl/system01.dbf
/data/app/oracle/oradata/orcl/sysaux01.dbf
/data/app/oracle/oradata/orcl/undotbs01.dbf
/data/app/oracle/oradata/orcl/users01.dbf
/data/app/oracle/oradata/orcl/itpux01.dbf

SYS@orcl > 
SYS@orcl > alter database datafile '/data/app/oracle/oradata/orcl/itpux01.dbf' resize 1024m; 然后在进行创建 ）

SYS@orcl > select count(*) from itpux01.logmnr_temp;

  COUNT(*)
----------
      5253
select * from itpux01.logmnr_temp where seg_owner='ITPUX01' and seg_name='ITPUX01'; （通过plsql 查看更加直观）
select username,scn,timestamp,sql_redo,sql_undo from itpux01.logmnr_temp where seg_owner='ITPUX01' and seg_name='ITPUX01';

根据时间段查看：
SYS@orcl > select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual;

TIME
-------------------
2020-06-04 10:53:50

SYS@orcl > 
select username,scn,timestamp,sql_redo,sql_undo from itpux01.logmnr_temp where seg_owner='ITPUX01' and seg_name='ITPUX01' and timestamp >=to_date('2020-06-04 9:00:00','YYYY-MM-DD HH24:MI:SS') and <=to_date('2020-06-04 10:00:00','YYYY-MM-DD HH24:MI:SS') order by 1;


## 05.结束分析
SYS@orcl > exec dbms_logmnr.end_logmnr;  // 分析占用的是pga 内存区，不结束分析占用的就比较大

PL/SQL procedure successfully completed.

SYS@orcl >

2. 使用源数据库的数据字典（Online catalog)来分析 DML 操作

经常遇到开发人员错误删除，修改,插入等误操作,事后又不知道具体操作哪些数据,希望把恢复到某某时间点,在数据库开归档并有备份的情况的下有以下方法
1 闪回数据库(需要开启 flashback)
2 异机数据库不完全恢复(可基于部分表空间) 
3 LOGMINER 日志挖掘(需要设置 supplemental logging)
对 DML 误操作,如果 flashback 没有开启,只有选择后两种方法,如果数据库较大对于第二种方法将比较笨重,需要准备新的机器,安装软件,拷贝备份,执行恢复。在开启supplemental logging(建议数据库开启 supplemental logging 最小模式,对日志量影响较小)，情况下则可以选择 LOGMINER。

2.1准备测试数据

## 准备测试的数据
conn itpux01/itpux01
create table itpux01.itpux02 (id number(10),name varchar2(10));
insert into itpux01.itpux02 values(1,'itpux01');
insert into itpux01.itpux02 values(2,'itpux02');
insert into itpux01.itpux02 values(3,'itpux03');
insert into itpux01.itpux02 values(4,'itpux04');
insert into itpux01.itpux02 values(5,'itpux05');
commit;
conn /as sysdba;

## 执行统计分析
SYS@orcl > exec dbms_stats.gather_table_stats(ownname=>'ITPUX01',tabname => 'ITPUX02');

PL/SQL procedure successfully completed.

SYS@orcl > 

## 切换归档 （从下面的可以看出来，当时的归档是83 ，切换后是84了）
SYS@orcl > archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     78
Next log sequence to archive   83
Current log sequence	       83

SYS@orcl > alter system archive log current;

System altered.

SYS@orcl > archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     79
Next log sequence to archive   84
Current log sequence	       84
SYS@orcl > 

## 要用到的归档
SYS@orcl > select SEQUENCE#,FIRST_TIME,NAME from v$archived_log where SEQUENCE# >82 and SEQUENCE# <84;

 SEQUENCE# FIRST_TIME
---------- -------------------
NAME
--------------------------------------------------------------------------------
	83 2020-06-04 09:05:27
/data/app/oracle/fast_recovery_area/ORCL/archivelog/2020_06_04/o1_mf_1_83_hfk7qh
v8_.arc


SYS@orcl >

2.2 安装 logminer

## 安装 logminer
$ORACLE_HOME/rdbms/admin/dbmslm.sql 
$ORACLE_HOME/rdbms/admin/dbmslmd.sql
$ORACLE_HOME/rdbms/admin/dbmslms.sql

2.3 使用源数据库的数据字典（Online catalog)来分析 DML 操作

## 01.开启补充日志
select SUPPLEMENTAL_LOG_DATA_MIN from v$database; 
alter database add supplemental log data;

## 02.建立日志分析列表

SYS@orcl > execute dbms_logmnr.add_logfile(logfilename=>'/data/app/oracle/fast_recovery_area/ORCL/archivelog/2020_06_04/o1_mf_1_83_hfk7qhv8_.arc',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SYS@orcl > 

## 03.启动分析
SYS@orcl > execute dbms_logmnr.start_logmnr(Options => dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

SYS@orcl > 

##04.查看日志分析结果 （查询得出的结果有创建表和插入数据的操作）
SYS@orcl > select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents;
SYS@orcl > create table itpux01.logmnr_temp2 nologging as select * from v$logmnr_contents;


select * from itpux01.logmnr_temp2 where seg_owner='ITPUX01' and seg_name='ITPUX02' order by 1;

select sql_redo from itpux01.logmnr_temp2 where 
seg_owner='ITPUX01' and seg_name='ITPUX02' and timestamp  >=to_date
('2020-6-4 14:33:15','YYYY-MM-DD hh24:mi:ss') and timestamp <= to_date 
('2020-6-4 15:50:50','YYYY-MM-DD hh24:mi:ss') order by 1;
SQL_REDO
--------------------------------------------------------------------------------
create table itpux01.itpux02 (id number(10),name varchar2(10));
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('1','itpux01');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('2','itpux02');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('3','itpux03');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('4','itpux04');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('5','itpux05');


-- 删除表中的数据
SYS@orcl > delete from itpux01.itpux02;

5 rows deleted.

SYS@orcl > commit;

Commit complete.

SYS@orcl > 

--切换一下日志
SYS@orcl > alter system archive log current;

System altered.

SYS@orcl > 
SYS@orcl > archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     80
Next log sequence to archive   85
Current log sequence	       85
SYS@orcl > 

SYS@orcl >  select SEQUENCE#,FIRST_TIME,NAME from v$archived_log where SEQUENCE# >83 and SEQUENCE# <85;

 SEQUENCE# FIRST_TIME
---------- -------------------
NAME
--------------------------------------------------------------------------------
	84 2020-06-04 15:12:47
/data/app/oracle/fast_recovery_area/ORCL/archivelog/2020_06_04/o1_mf_1_84_hfkb5s
4n_.arc


SYS@orcl > 

--建立分析日志列表
SYS@orcl > execute dbms_logmnr.add_logfile(logfilename=>'/data/app/oracle/fast_recovery_area/ORCL/archivelog/2020_06_04/o1_mf_1_84_hfkb5s4n_.arc',options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SYS@orcl > 

-- 启动分析
SYS@orcl > execute dbms_logmnr.start_logmnr(Options => dbms_logmnr.dict_from_online_catalog);

--查看日志分析结果  （用plsql 工具查看最直观）
SYS@orcl > create table itpux01.logmnr_temp3 nologging as select * from v$logmnr_contents;

select sql_redo from itpux01.logmnr_temp3 where seg_owner='ITPUX01' and seg_name='ITPUX02' order by 1;

create table itpux01.itpux02 (id number(10),name varchar2(10));
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('1','itpux01');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('4','itpux04');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('3','itpux03');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('2','itpux02');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('5','itpux05');
delete from "ITPUX01"."ITPUX02" where "ID" = '1' and "NAME" = 'itpux01' and ROWID = 'AAAVRqAAAAAAAEMAAA';
delete from "ITPUX01"."ITPUX02" where "ID" = '2' and "NAME" = 'itpux02' and ROWID = 'AAAVRqAAAAAAAEMAAB';
delete from "ITPUX01"."ITPUX02" where "ID" = '3' and "NAME" = 'itpux03' and ROWID = 'AAAVRqAAAAAAAEMAAC';
delete from "ITPUX01"."ITPUX02" where "ID" = '4' and "NAME" = 'itpux04' and ROWID = 'AAAVRqAAAAAAAEMAAD';
delete from "ITPUX01"."ITPUX02" where "ID" = '5' and "NAME" = 'itpux05' and ROWID = 'AAAVRqAAAAAAAEMAAE';

-- 进行恢复 
------ 查看恢复的数据

select timestamp,sql_undo  from itpux01.logmnr_temp3 where seg_owner='ITPUX01' and seg_name='ITPUX02' and timestamp >=to_date
('2020-6-4 15:52:50','YYYY-MM-DD hh24:mi:ss') and timestamp <= 
to_date ('2020-6-4 15:52:58','YYYY-MM-DD hh24:mi:ss') order by 1;

（如果记录少直接复制粘贴 执行就可以了，多了就这样提取抽取来。
SYS@orcl > spool itpux02.sql
select sql_undo  from itpux01.logmnr_temp3 where seg_owner='ITPUX01' and seg_name='ITPUX02' and timestamp >=to_date
('2020-6-4 15:52:50','YYYY-MM-DD hh24:mi:ss') and timestamp <= 
  3  to_date ('2020-6-4 15:52:58','YYYY-MM-DD hh24:mi:ss') order by 1;

SQL_UNDO
--------------------------------------------------------------------------------
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('1','itpux01');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('2','itpux02');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('3','itpux03');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('4','itpux04');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('5','itpux05');

SYS@orcl > spool off;
SYS@orcl > 
------

生产的sql 脚本（脚本在当前目录下，你从哪个目录sql进去就保存在哪里）
[oracle@ogg12c ~]$ ls
1  install2020-05-27_16-46-47.log  itpux02.sql  LOG_cat_owb.TXT
[oracle@ogg12c ~]$ pwd
/home/oracle
[oracle@ogg12c ~]$ 

只保留查询出来的结果
[oracle@ogg12c ~]$ vim itpux02.sql 
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('1','itpux01');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('2','itpux02');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('3','itpux03');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('4','itpux04');
insert into "ITPUX01"."ITPUX02"("ID","NAME") values ('5','itpux05');
commit;

-- 执行脚本
SYS@orcl > @itpux02.sql
-- 查看插入的结果
SYS@orcl > select * from itpux01.itpux02;

	ID NAME
---------- ----------
	 1 itpux01
	 2 itpux02
	 3 itpux03
	 4 itpux04
	 5 itpux05

## 05.结束分析
exec dbms_logmnr.end_logmnr;

select timestamp,sql_redo from itpux01.logmnr_temp5 where 
seg_owner='ITPUX02' and operation='DDL' order by 1;

四、RMAN 表空间基于时间点的自动回复

① TSPITR 表空间基于时间点的自动恢复

② logminer 的简单应用

1.TSPITR 的相关知识点归纳

TSPITR 简介：

TSPITR(表空间时间点恢复)用于将一个或多个表空间恢复到过去某个时间点的状态，而其他表空间仍然保持现有状态。

TSPITR 相关的概念和术语：

(1) 恢复集(Recovery Set)。恢复集是指在主数据库上需要执行 TSPITR 的表空间集合。注意，当在恢复集的表空间上执行 TSPITR 时，要求这些表空间必须是自包含的。（自包含：这个用户里面所有的对象都在这个表空间里面，如果夸表空间可能恢复不了了）

(2) 辅助数据库(Auxiliary Database)。辅助数据库是主数据库的一个副本数据库。当执行 TSPITR 时，辅助数据库用于将恢复集表空间恢复到过去时间点。注意，辅助数据库的所有物理文件都是从主数据库备份中取得，并且辅助数据库必须包含 SYSTEM 表空间、UNDO 表空间以及恢复集表空间的备份文件。

(3) 辅助集(Auxiliary Set)。辅助集是指辅助数据库所需要的、除了恢复集表空间文件之外的其他文件集合。当执行 TSPITR 时，辅助数据库除了需要恢复集表空间的备份文件之外，还需要备份控制文件、SYSTEM 表空间的备份文件、UNDO 表空间的备分件。

2. 创建实验环境

2.1 建立表空间，rman 备份

## 创建表空间
SYS@orcl > create tablespace itpux02 datafile '/data/app/oracle/oradata/orcl/itpux02.dbf' size 500m;
SYS@orcl > create user itpux02 identified by itpux02 default tablespace itpux02;
SYS@orcl > grant dba to itpux02;

## 备份数据
[oracle@ogg12c data]$ mkdir backup
[oracle@ogg12c ~]$ rman target /
RMAN> backup database format '/data/backup/fullbak_%d_%U';

## 查看备份结果
RMAN> list backup;

2.2 建立测试表

## 切换归档
SYS@orcl > alter system switch logfile;
SYS@orcl > /
SYS@orcl > /

SYS@orcl > conn itpux02/itpux02
Connected.
ITPUX02@orcl > set time on;
11:44:41 ITPUX02@orcl > 


11:44:41 ITPUX02@orcl > create table itpux021 as select * from itpux01.itpux02;

11:46:09 ITPUX02@orcl > create table itpux022 as select * from itpux01.itpux02;

## 查看结果
11:46:28 ITPUX02@orcl > select * from itpux021;

	ID NAME
---------- ----------
	 1 itpux01
	 2 itpux02
	 3 itpux03
	 4 itpux04
	 5 itpux05

11:47:15 ITPUX02@orcl > select * from itpux022;

	ID NAME
---------- ----------
	 1 itpux01
	 2 itpux02
	 3 itpux03
	 4 itpux04
	 5 itpux05

11:47:19 ITPUX02@orcl > 

## 创建一个索引
11:47:19 ITPUX02@orcl >  create index itpux02.itpux022_idx on itpux02.itpux022(id) tablespace users;

## 继续切换日志
11:52:40 ITPUX02@orcl > alter system switch logfile;
11:52:48 ITPUX02@orcl > /


## 查看当前的日志
11:53:42 ITPUX02@orcl > conn / as sysdba
Connected.
11:54:10 SYS@orcl > archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     88
Next log sequence to archive   93
Current log sequence	       93
11:54:16 SYS@orcl > 

11:54:16 SYS@orcl > set lin 200
11:54:46 SYS@orcl > set pagesize 200
11:54:54 SYS@orcl > select * from v$log;

11:59:47 SYS@orcl > select GROUP#,SEQUENCE#,ARCHIVED,STATUS  from v$log;

 GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
	 1	   91 YES INACTIVE
	 2	   92 YES INACTIVE
	 3	   93 NO  CURRENT
	 4	   88 YES INACTIVE
	 5	   89 YES INACTIVE
	 6	   90 YES INACTIVE

	

12:00:15 SYS@orcl > truncate table itpux02.itpux021;

Table truncated.

12:01:06 SYS@orcl > select * from itpux02.itpux021;

no rows selected

12:01:20 SYS@orcl > col MEMBER format a50;
12:02:58 SYS@orcl > select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER					      IS_
---------- ------- ------- -------------------------------------------------- ---
	 1	   ONLINE  /data/app/oracle/oradata/orcl/redo01.log	      NO
	 2	   ONLINE  /data/app/oracle/oradata/orcl/redo02.log	      NO
	 3	   ONLINE  /data/app/oracle/oradata/orcl/redo03.log	      NO
	 4	   ONLINE  /data/app/oracle/oradata/orcl/redo04.log	      NO
	 5	   ONLINE  /data/app/oracle/oradata/orcl/redo05.log	      NO
	 6	   ONLINE  /data/app/oracle/oradata/orcl/redo06.log	      NO
	 
	-- //从查询结果可以 当前日志文件 93 对应的是 第3 组 ----

## 继续创建表
14:16:52 SYS@orcl > create table itpux02.itpux023 as select * from itpux01.itpux02;
14:17:59 SYS@orcl > create table itpux02.itpux024 as select * from itpux01.itpux02;

2.3 进行分析

## 进行挖掘分析
14:19:22 SYS@orcl > execute dbms_logmnr.add_logfile('/data/app/oracle/oradata/orcl/redo03.log',dbms_logmnr.new);

## 启动分析
14:29:46 SYS@orcl > execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

14:30:45 SYS@orcl > create table itpux01.logmnr_temp7 as select * from v$logmnr_contents;

## 结束分析
14:35:55 SYS@orcl > exec dbms_logmnr.end_logmnr;

2.4 进行查询

查询结果有  truncate  的操作和准确时间（这里查出的时间最准确了）
select * from itpux01.logmnr_temp7 where seg_owner='ITPUX02' order by 1;

2.5 检查表空间自包含

begin 
sys.dbms_tts.transport_set_check ('ITPUX02',true,true);
end; 
/
或者可以写成一行
begin sys.dbms_tts.transport_set_check ('ITPUX02',true,true);end;
/
-- 进行查看  （查看有索引，在表空间user里面了，做表空间点恢复是恢复不了的需要处理掉这个索引才能恢复）
SYS@orcl >  select * from sys.transport_set_violations;

VIOLATIONS
--------------------------------------------------------------------------------
ORA-39907: Index ITPUX02.ITPUX022_IDX in tablespace USERS points to table ITPUX0
2.ITPUX022 in tablespace ITPUX02.

## 处理索引（删除掉索引）
17:10:31 SYS@orcl > drop index ITPUX02.ITPUX022_IDX;

## 再次进行检查（如果没有了就可以进行恢复操作了）
begin sys.dbms_tts.transport_set_check ('ITPUX02',true,true);end;
/

17:11:25 SYS@orcl > select * from sys.transport_set_violations;

no rows selected

17:11:39 SYS@orcl > 

## 检查哪些对象在执行表空间时间点恢复之后会被删除 （truncate 之后）
SYS@orcl > select * from sys.ts_pitr_objects_to_be_dropped where tablespace_name='ITPUX02' and creation_time >= to_date('2020-06-05 12:00:15','YYYY-MM-DD HH24:MI:SS');

OWNER	   NAME       CREATION_TIME	  TABLESPACE_NAME
---------- ---------- ------------------- ---------------
ITPUX02    ITPUX023   2020-06-05 15:17:53 ITPUX02
ITPUX02    ITPUX024   2020-06-05 15:17:59 ITPUX02

## 导出数据
[oracle@ogg12c dpdata]$ exp itpux02/itpux02 file=itpux023-024.dmp tables=itpux023,itpux024 log=itpux023-024.log

[oracle@ogg12c dpdata]$ ls
itpux023-024.dmp  itpux023-024.log

2..6 执行表空间时间点恢复

中间库，辅助实例 目录
[oracle@ogg12c backup]$ pwd
/data/backup
[oracle@ogg12c backup]$ mkdir auxdb01
[oracle@ogg12c backup]$ 

rman target /
recover tablespace itpux02 until time "to_date('2020-06-05 12:00:15','YYYY-MM-DD HH24:MI:SS')" auxiliary destination '/data/auxdb01';
。。。。。。略
sql statement: alter tablespace  ITPUX02 offline
。。。。。。略

//auxiliary destination '/data/auxdb01'; 中间库存放的位置

恢复这个表空间需要之前的备份，先把之前的备份集恢复到/data/auxdb01 目录下，然后在把表拿出来
[oracle@ogg12c ORCL]$ ls
controlfile  datafile  onlinelog
[oracle@ogg12c ORCL]$ pwd
/data/auxdb01/ORCL

SYS@orcl > alter tablespace ITPUX02 online;

## 查看恢复的表数据
SYS@orcl > select * from itpux02.itpux021;

	ID NAME
---------- ----------
	 1 itpux01
	 2 itpux02
	 3 itpux03
	 4 itpux04
	 5 itpux05
	 
	 此时表 itpux023 、itpux024 表的数据不再
	 
## 导入表
[oracle@ogg12c dpdata]$ imp itpux02/itpux02 file=itpux023-024.dmp tables=itpux023,itpux024

## 恢复索引
create index itpux02.itpux022_idx on itpux02.itpux022(id) tablespace users;

五logminer 使用总结

1. 其他注意事项

我们可以利用 logminer 日志分析工具来分析其他数据库实例产生的重做日志文件，而不仅仅用来分析本身安装 logminer 的数据库实例的 redo logs 文件。使用logminer 分析其他数据库实例时，有几点需要注意：

-- 1.logminer 必须使用被分析数据库实例产生的字典文件，而不是安装 logminer 的数据库产生的字典文件，另外必须保证安装logminer 数据库的字符集和被分析数据库的字符集相同。

-- 2. 被分析数据库平台必须和当前 logminer 所在数据库平台一样，也就是说如果我们要分析的文件是由运行在 UNIX 平台上的 Oracle 9i 产生的，那么也必须在一个运行在 UNIX 平台上的 Oracle 实例上运行 logminer，而不能在其他如 Microsoft NT 上
运行 logminer。当然两者的硬件条件不一定要求完全一样。

-- 3.logminer 日志分析工具仅能够分析 Oracle 8 以后的产品，对于 8 以前的产品，该工具也无能为力。

2.结语

logminer 对于数据库管理员（DBA）来讲是个功能非常强大的工具，也是在日常工作中经常要用到的一个工具，借助于该工具，可以得到大量的关于数据库活动的信息。其中一个最重要的用途就是不用全部恢复数据库就可以恢复数据库的某个变化。另外，该工具还可用来监或者审计用户的活动，如你可以利用 logminer 工具察看谁曾经修改了那些数据以及这些数据在修改前的状态。我们也可以借助于该工具分析任何Oracle 8 及其以后版本产生的重作日志文件。另外该工具还有一个非常重要的特点就是可以分析其他数据库的日志文件。总之，该工具对于数据库管理员来讲，是一个非常有效的工具，深刻理解及熟练掌握该工具，对于每一个数据库管理员的实际工作是非常有帮助的。

posted @ 2020-10-27 16:09 LWJ303 阅读(1006) 评论(1) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

LWJ303

凌晨还有谁没睡

LogMiner日志挖掘分析管理

LogMiner日志挖掘分析管理

一、Logminer 相关概念

1. logminer 的作用

2. LogMiner 不支持的数据类型和表存储属性

3. LogMiner 的配置要求

4. LogMiner 使用注意事项

5. 什么是补充日志，补充日志的作用是什么？

6.激活数据库补充日志

二、Logminer 使用详解

1.安装 logminer

2.使用源数据库的数据字典（Online catalog)来分析 DML 操作

3. 使用 LogMiner 字典到字典文件来分析 DDL 操作

4.使用 LogMiner 进行日志分析

三、分析生产系统表数据丢失的原因

1 进行试验（DDL)

1.1 创建实验环境

1.2. 安装 logminer

1.3. 使用 LogMiner 字典到字典文件来分析 DDL 操作

2. 使用源数据库的数据字典（Online catalog)来分析 DML 操作

2.1准备测试数据

2.2 安装 logminer

2.3 使用源数据库的数据字典（Online catalog)来分析 DML 操作

四、RMAN 表空间基于时间点的自动回复

1.TSPITR 的相关知识点归纳

2. 创建实验环境

2.1 建立表空间，rman 备份

2.2 建立测试表

2.3 进行分析

2.4 进行查询

2.5 检查表空间自包含

2..6 执行表空间时间点恢复

五logminer 使用总结

1. 其他注意事项

2.结语

公告

LWJ303

凌晨还有谁没睡

LogMiner日志挖掘分析管理

LogMiner日志挖掘分析管理

一、Logminer 相关概念

1. logminer 的作用

2. LogMiner 不支持的数据类型和表存储属性

3. LogMiner 的配置要求

4. LogMiner 使用注意事项

5. 什么是补充日志，补充日志的作用是什么？

6.激活数据库补充日志

二、Logminer 使用详解

1.安装 logminer

2.使用源数据库的数据字典（Online catalog)来分析 DML 操作

3. 使用 LogMiner 字典到字典文件来分析 DDL 操作

4.使用 LogMiner 进行日志分析

三、分析生产系统表数据丢失的原因

1 进行试验 （DDL)

1.1 创建实验环境

1.2. 安装 logminer

1.3. 使用 LogMiner 字典到字典文件来分析 DDL 操作

2. 使用源数据库的数据字典（Online catalog)来分析 DML 操作

2.1准备测试数据

2.2 安装 logminer

2.3 使用源数据库的数据字典（Online catalog)来分析 DML 操作

四、RMAN 表空间基于时间点的自动回复

1.TSPITR 的相关知识点归纳

2. 创建实验环境

2.1 建立表空间，rman 备份

2.2 建立测试表

2.3 进行分析

2.4 进行查询

2.5 检查表空间自包含

2..6 执行表空间时间点恢复

五logminer 使用总结

1. 其他注意事项

2.结语

公告

1 进行试验（DDL)