对于客户的问题故障进行总结
1） 问题现象
归档日志，每小时切换最低60于次，每天产生归档日志720g,由于归档日志过多，定时清理归档不及时，导致Arch磁盘组空间极易消耗殆尽，导致业务无法操作，业务连续性收到影响。
2）短期处理
手工处理，临时清理一天前归档，保障业务连续性
RMAN>DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';
3)问题定位
使用Logminer工具进行挖掘，在2019年03月05日晚上9点，与2019年03月06日早上7点，日志挖掘top3输出结果相同。
对于数据库来说，不同时间段，业务连续性的对同一个表insert,delete，JOB定时调用可能实现。 USERNAME OWNER NAME TYPE_NAME OPERATION COUNT BJ_SELECTION BJ_SELECTION YD_BARGAIN_PRICE TABLE INSERT 1481521 BJ_SELECTION BJ_SELECTION YD_BARGAIN_PRICE TABLE DELETE 1481521 BJ_SELECTION INTERNAL 4444594
JOB视图查询 SQL> select job,log_user,last_date,next_date,broken,interval,what from dba_jobs where WHAT like '%YD_BARGAIN_PRICE%'; JOB LOG_USER LAST_DATE NEXT_DATE B INTERVAL WHAT ---------- ----------------------------- ----------------------------- ------------------- 1365 BJ_SELECTION 2019-03-06 09:29:53 2019-03-06 09:29:59 N SYSDATE + NUMTODSINTERVAL(2,'SECOND') dbms_refresh.refresh('"BJ_SELECTION"."YD_BARGAIN_PRICE"'); 1366 BJ_TEST_SELECTION 2019-03-06 09:30:02 2019-03-06 09:30:04 N SYSDATE + NUMTODSINTERVAL(2,'SECOND') dbms_refresh.refresh('"BJ_TEST_SELECTION"."YD_BARGAIN_PRICE"'); JOB间隔2s，执行物化视图刷新。
通过SQL视图查询，发现以下两个物化视图，确实间隔3s刷新 SQL> select owner,MVIEW_NAME,UPDATABLE,UPDATE_LOG,MASTER_LINK,REWRITE_ENABLED,REWRITE_CAPABILITY,REFRESH_MODE,REFRESH_METHOD,FAST_REFRESHABLE
,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from dba_mviews where owner in ('BJ_TEST_SELECTION','BJ_SELECTION') and MVIEW_NAME ='YD_BARGAIN_PRICE' OWNER MVIEW_NAME U UP MAST R REWRITE_C REFRES REFRESH_ FAS LAST_REF LAST_REFRESH_DATE -------------------- ---------------- - -- ---- - --------- ------ -------- --- -------- ------------------- BJ_SELECTION YD_BARGAIN_PRICE N N GENERAL DEMAND FORCE NO COMPLETE 2019-03-06 10:22:18 BJ_TEST_SELECTION YD_BARGAIN_PRICE N N GENERAL DEMAND FORCE NO COMPLETE 2019-03-06 10:22:28 SQL> select owner,MVIEW_NAME,UPDATABLE,UPDATE_LOG,MASTER_LINK,REWRITE_ENABLED,REWRITE_CAPABILITY,REFRESH_MODE,REFRESH_METHOD,
FAST_REFRESHABLE,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from dba_mviews where owner in ('BJ_TEST_SELECTION','BJ_SELECTION')
and MVIEW_NAME ='YD_BARGAIN_PRICE' OWNER MVIEW_NAME U UP MAST R REWRITE_C REFRES REFRESH_ FAS LAST_REF LAST_REFRESH_DATE -------------------- ---------------- - -- ---- - --------- ------ -------- --- -------- ------------------- BJ_SELECTION YD_BARGAIN_PRICE N N GENERAL DEMAND FORCE NO COMPLETE 2019-03-06 10:22:26 BJ_TEST_SELECTION YD_BARGAIN_PRICE N N GENERAL DEMAND FORCE NO COMPLETE 2019-03-06 10:22:33 查询发现，对象的创建时间与归档日志量开始激增时间点匹配 select owner,object_name,object_type,status,created from dba_objects where object_name='YD_BARGAIN_PRICE' and owner='BJ_SELECTION'; OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED --------------- -------------------- ------------------- ------- ------------------- BJ_SELECTION YD_BARGAIN_PRICE TABLE VALID 2019-03-01 13:03:29 BJ_SELECTION YD_BARGAIN_PRICE MATERIALIZED VIEW VALID 2019-03-01 13:03:30
GET_DDL物化视图创建语法 SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','YD_BARGAIN_PRICE','BJ_SELECTION') ddl_text from dual; DDL_TEXT -------------------------------------------------------------------------------- 分析函数将无法使用快速增量刷新 CREATE MATERIALIZED VIEW "BJ_SELECTION"."YD_BARGAIN_PRICE" ("ID", "PROJECT_ID", "HOS_ID", "PRODUCT_ID", "PRICE", "UPD ATE_TIME", "BID_DATE") ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "BJ_TEST_SELECTION" BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + NUMTODSINTERVAL(2,'SECOND') USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE AS select r.ID,r.PROJECT_ID,r.HOS_ID,r.PRODUCT_ID , r.PRICE,r.UPDATE_TIME,r.BID_DATE from ( SELECT ID, PRODUCT_ID, ORG_PRICE AS PRICE, PROJECT_ID, HOS_ID, UPDATE_TIME, BID_DATE, row_number() over(partition by PROJECT_ID, HOS_ID , PRODUCT_ID order by UPDATE_TIME desc) rid FROM YD_BARGAIN_ITEM WHERE HOS_FLAG = '3' ) r where r.rid='1'; SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','YD_BARGAIN_PRICE','BJ_TEST_SELECTION') ddl_text from dual; CREATE MATERIALIZED VIEW "BJ_TEST_SELECTION"."YD_BARGAIN_PRICE"•••••• 发现信息： 物化视图涉及的对象是当前用户下同一个对象 查询物化视图涉及的基表 SQL> select owner,object_name,object_type,status,created from dba_objects where owner='BJ_SELECTION' and object_name='YD_BARGAIN_ITEM'; OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED --------------- -------------------- ------------------- ------- ------------------- BJ_SELECTION YD_BARGAIN_ITEM TABLE VALID 2018-12-22 13:59:12 1.两个物化视图，间隔2s刷新导致的日志量激增 2.物化视图底层涉及的基表是相同的
4）问题解决
在于业务人员沟通需求后，建议业务SQL调整为直接访问基表，无需物化视图中间环节处理。在业务SQL修改后，确认业务SQL的执行效率能满足需要，删除停用原物化视图，问题得到最终解决。
5）对比
2019年03月06日，中午12点停用两个物化视图刷新后，归档日志切换恢复正常。
|
Redo日志大小对比 |
20190303 13:00-14:00 |
20190306 13:00-14:00 |
|
Redo size: |
5597015.3（异常时） |
9370.6（恢复后） |