10.2.0.5环境dg测试logminer挖掘日志分析
10.2.0.5环境dg测试logminer挖掘日志分析
起因：客户需求，数据库正常每天总的日志切换是20以内，有一天日志切换总数，达到30，客户建议使用Logminer进行日志挖掘分析，到底什么应用导致的问题。
说明：使用logminer进行日志挖掘，只能挖掘出什么对象表，dml操作多少次，而无法明确得到什么应用占用的日志最多。只能提供很单项的东西，某个表dml操作次数，非常多。需要客户结合实际应用，去判断，到底什么应用导致的问题。
环境说明：数据库版本10.2.0.5 双节点rac, 有一套dg 10.2.0.5,单实例。
测试说明：
1.dg 切换快照模式
2.使用logminer进行挖掘
3.dg切换还原为standby 备库模式
一、dg切换快照模式
alter database recover managed standby database cancel; ALTER SYSTEM SET db_recovery_file_dest_size=1024G SCOPE=BOTH; ALTER SYSTEM SET db_recovery_file_dest='/xxxx/实际情况选择路径' SCOPE=BOTH; alter system set db_flashback_retention_target=9000 scope=both; alter database flashback on; create restore point flashback_dzswrac GUARANTEE flashback database;
#确认grant point已创建完毕之后，在进行dg角色更换
SQL> select * from v$restore_point;
#如下操作，切换备库角色为主库角色
alter database activate standby database; alter database open; alter system set job_queue_processes=0; exit
二、使用logminer进行挖掘
1）选择抓取，日志切换频率最高的时间节点，明确所需要挖掘的日志时间节点
SQL>select to_char(completion_time,'YYYYMMDD HH24'),dest_id,thread#,name from v$archived_Log where dest_id=1 and completion_time between to_date('20190104 14','yyyymmdd hh24') and to_date('20190104 19','yyyymmdd hh24') TO_CHAR(COM DEST_ID THREAD# NAME ----------- ---------- ---------- -------------------- 20190104 16 1 2 20190104 16 1 2 20190104 16 1 1 20190104 16 1 2 20190104 16 1 2 20190104 16 1 2 6 rows selected.
上述查询，得到04日，一个小时，日志切换了6次
COMPLETION_TIME 归档完成的时间
2）查询日志挖掘的文件名称
select to_char(completion_time,'YYYYMMDD HH24:mi'),dest_id,thread#,name,deleted,status
from v$archived_Log
where dest_id=1 and completion_time between to_date(‘20190104 14′,’yyyymmdd hh24’) and to_date(‘20190104 22′,’yyyymmdd hh24’)
order by 1;
TO_CHAR(COMPLE DEST_ID THREAD# NAME DEL S
————– ———- ———- ————————————————– — –
20190104 14:03 1 1 xxxx NO A
20190104 14:03 1 2 xxxx NO A
20190104 14:30 1 2 xxxx NO A
20190104 14:45 1 2 xxxx NO A
20190104 14:49 1 1 xxxx NO A
20190104 14:49 1 2 xxxx NO A
20190104 14:50 1 2 xxxx NO A
–如下忽略
20190104 15:03 1 2 xxxx NO A
3）logminer前提准备
添加存储过程
@?/rdbms/admin/dbmslmd.sql
@?/rdbms/admin/dbmslm.sql
@?/rdbms/admin/dbmslms.sql
@?/rdbms/admin/prvtlm.plb
开启最小补充日志
select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;
SQL> alter database add supplemental log data;
Database altered.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
——– — —
YES NO NO
LOGMNR 分析日志，需要创建一个数据字典，这个数据字典可以存储在外部文件（external file) 或者redo log 中。 如果要存储在外部文件中，数据库参数utl_file_dir 需要配置，并重启数据库。
SQL> alter system set utl_file_dir=’*’ scope=spfile;
SQL> shutdown immediate;
SQL> startup
SQL> show parameter utl_file
NAME TYPE VALUE
———————————— ———– ——————————
utl_file_dir string *
SQL> execute dbms_logmnr_d.build(dictionary_filename=>’dict.ora’,dictionary_location=>’/data/newdg/logminer_ctr_directory/’);
PL/SQL procedure successfully completed.
#或者使用redo log 存储数据字典
#使用redo log 存储数据字典不需要重启数据库，也不需要配置任何参数，这种方式比较灵活.直接可以创建数据字典到redo文件。
#dbms_logmnr_d.build(options => dbms_logmnr_d.STORE_IN_REDO_LOGS);
4）使用logminer 挖掘日志
第一个
exec dbms_logmnr.add_logfile(‘/xxx.xxxdbf’,dbms_logmnr.new);
第二个
exec dbms_logmnr.add_logfile(‘xxx.xxxxdbf’,dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(‘/xxx.xxxdbf’,dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(‘/dxxx14.dbf’,dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(‘/xxx.dxxxbf’,dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(‘xxx814x.dbf’,dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(‘/xxxxxx.dbf’,dbms_logmnr.addfile);
注册到日志文件中
execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
由于记录处于不稳定状态，可以创建一个临时表，存储挖掘记录
create table logmnr_04_14_one_date as select * from v$logmnr_contents;
结束
EXECUTE dbms_logmnr.end_logmnr;
5）SQL查询结果
set line 200
set term off verify off feedback off pagesize 999
set markup html on entmap on spool on preformat off
spool log_20190104_14_15.xls
select username,SEG_OWNER,SEG_NAME,seg_type_name,OPERATION_CODE,count(*)
from logmnr_04_14_one_date group by username,SEG_OWNER,SEG_NAME,seg_type_name,
OPERATION_CODE order by 6 asc;
spool off
三、DG恢复shutdown immediate;
startup mount; flashback database to restore point flashback_dzswrac; alter database convert to physical standby; shutdown immediate; startup mount; alter database recover managed standby database disconnect from session;
--根据日志等信息，mgr确认已使用，且已恢复之后，在删除闪回日志，一定要确认完毕
DROP RESTORE POINT flashback_dzswrac; alter database flashback off; ALTER SYSTEM SET db_recovery_file_dest='' SCOPE=BOTH; exit