Thứ Năm, 1 tháng 4, 2021

Quản trị MView, MView Log trong Oracle Database

Mục đích: MView là giải pháp đồng bộ dữ liệu từ DB Source về DB Dest để khai thác bảng trên DB Dest giống như bảng ở DB Source tăng tốc độ truy cập dữ liệu so với giải pháp dùng view qua dblink trên DB Dest (giải pháp thay thế là GoldenGate nếu cần real time)

Các câu lệnh quản trị như bên dưới:
Check
Các view hay sử dụng:

DBA_MVIEWS                                      ALL_MVIEWS           USER_MVIEWS
 DBA_MVIEW_AGGREGATES            ALL_MVIEW_AGGREGATES USER_MVIEW_AGGREGATES
 DBA_MVIEW_ANALYSIS                   ALL_MVIEW_ANALYSIS   USER_MVIEW_ANALYSIS
 DBA_MVIEW_DETAIL_RELATIONS ALL_MVIEW_DETAIL_RELATIONS USER_MVIEW_DETAIL_RELATIONS
 DBA_MVIEW_JOINS                        ALL_MVIEW_JOINS      USER_MVIEW_JOINS
 DBA_MVIEW_KEYS                         ALL_MVIEW_KEYS       USER_MVIEW_KEYS
 DBA_MVIEW_LOGS                         ALL_MVIEW_LOGS       USER_MVIEW_LOGS 
 DBA_MVIEW_LOG_FILTER_COLS
 DBA_MVIEW_REFRESH_TIMES    ALL_MVIEW_REFRESH_TIMES USER_MVIEW_REFRESH_TIMES

Kiểm tra trạng thái Mview
SELECT MVIEW_NAME,  UNUSABLE, KNOWN_STALE, INVALID 
FROM DBA_MVIEW_ANALYSIS 
WHERE OWNER = 'APPS' 
AND INVALID = 'Y';
MVIEW_NAME                     U K I
------------------------------ - - -
AS_FORECAST_MV                 N Y Y
AS_SUBORDINATE_REPS_MV         N Y Y
AS_GROUP_MV                    N Y Y
BIC_OPPORTUNITIES_MV           N Y Y
CORRECTION_DESC                N N Y
SR_CUST_NAME_ORG               N N Y
ASF_ROLLUP_MGR_MV              N N Y
ASF_SC_BIN_MV                  N N Y
XXE_TT_SZF_FOREIS              N N Y
XXE_TT_CITIES_SNAPSHOT         N N Y
XXE_TT_SZF_FOREIS_ATTR_SNAP    N N Y
MVIEW_NAME                     U K I
------------------------------ - - -
XXE_TT_SZF_PARTIES_DATA        N N Y
MANDATE_BANKS                  N N Y
PAYMENT_SOURCES                N N Y

Mview refresh progress
You can find the refresh progress of a Mview with this script
SELECT  CURRMVOWNER_KNSTMVR || '.' || CURRMVNAME_KNSTMVR 
               "MVIEW BEING REFRESHED",
           DECODE( REFTYPE_KNSTMVR, 1, 'FAST', 2, 'COMPLETE', 'UNKNOWN' ) REFTYPE,
           DECODE(GROUPSTATE_KNSTMVR, 1, 'SETUP', 2, 'INSTANTIATE', 
                                      3, 'WRAPUP', 'UNKNOWN' ) STATE,
           TOTAL_INSERTS_KNSTMVR INSERTS,
           TOTAL_UPDATES_KNSTMVR UPDATES,
           TOTAL_DELETES_KNSTMVR DELETES
   FROM X$KNSTMVR X
   WHERE type_knst=6 AND 
         EXISTS (SELECT 1 FROM v$session s 
                 WHERE s.SID=x.sid_knst AND 
                       s.serial#=x.serial_knst);
SELECT l.SID, 
          DECODE( COUNT(*), 0, 'No purge in progress', 
                               'Purge is in progress' ) State
   FROM v$lock l, DBMS_LOCK_ALLOCATED la
   WHERE l.TYPE='UL' AND 
         l.lmode=6 AND
         l.id1=la.lockid AND
         la.NAME='ORA$DEF$EXE$PurgeCommonLock'
   GROUP BY l.SID;
SELECT l.SID, 
          DECODE( COUNT(*), 0, 'No propagation in progress', 
                               'Propagation in progress' ) State
   FROM v$lock l, DBMS_LOCK_ALLOCATED la
   WHERE l.TYPE='UL' AND 
         l.lmode=4 AND
         l.id1=la.lockid AND
         la.NAME='ORA$DEF$EXE$PushCommonLock'
   GROUP BY l.SID;
SELECT /*+ RULE */ o.owner, o.object_name mview, username, s.SID 
   FROM v$lock l, dba_objects o, v$session s 
   WHERE o.object_id=l.id1 AND 
         l.TYPE='JI' AND 
         l.lmode=6   AND 
         s.SID=l.SID AND 
         o.object_type='TABLE'; 


CREATE MVIEW

-- Cần quyền  CREATE ANY MATERIALIZED VIEW
--1.Tạo trên DB Source: Tạo MView Log
CREATE MATERIALIZED VIEW LOG ON binh_owner.tab1
TABLESPACE users
WITH PRIMARY KEY
INCLUDING NEW VALUES;

select * from DBA_MVIEW_LOGS;

--binh_owner.tab1    MLOG$_tab1    

-- 2.Tạo trên DB Dest
CREATE MATERIALIZED VIEW binh_owner.tab1
BUILD IMMEDIATE 
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM binh_owner.tab1@dblink_name;
 
(gan quyen select Mlog$tab1 cho user dblink tren nguon)
 
Select * from dba_mviews;

--c_owner.tab1

BEGIN
   DBMS_REFRESH.make(
     name                 => 'binh_owner.tab1',
     list                 => '',
     next_date            => SYSDATE,
     interval             => 'SYSDATE + 5/(60*24)',
     implicit_destroy     => FALSE,
     lax                  => FALSE,
     job                  => 0,
     rollback_seg         => NULL,
     push_deferred_rpc    => TRUE,
     refresh_after_errors => TRUE,
     purge_option         => NULL,
     parallelism          => NULL,
     heap_size            => NULL);
END;
/
 
BEGIN
   DBMS_REFRESH.add(
     name => 'tab1',
     list => 'tab1',
     lax  => TRUE);
END;
/
 
--Tao job 5 || 10p dong bo 1 lan
BEGIN
     dbms_refresh.refresh('"BINH_OWNER"."TAB1"');
exception
    WHEN OTHERS THEN                                          
      dbms_output.put_line('DBDest-Loi dong bo DBMS_MVIEW.REFRESH(TAB1):' || SQLErrM,'','');
        commit;             
end;

Refresh
BEGIN
     dbms_mview.refresh('tab1');          
end;

ALTER MVIEW/MVIEW LOG
• Automatic Refresh: Examples  
ALTER MATERIALIZED VIEW sales_by_month_by_state
   REFRESH FAST; 
ALTER MATERIALIZED VIEW sales_by_month_by_state
   REFRESH NEXT SYSDATE+7;
• Primary Key Materialized View:  
ALTER MATERIALIZED VIEW order_data 
   REFRESH WITH PRIMARY KEY; 
Compiling a Materialized View:ALTER MATERIALIZED VIEW order_data COMPILE;

• MView Log
ALTER MATERIALIZED VIEW LOG ON order_items ADD ROWID;
 
ALTER MATERIALIZED VIEW LOG ON employees
   ADD (commission_pct)
   EXCLUDING NEW VALUES


DROP MVIEW/MVIEW LOG
DROP MATERIALIZED VIEW binh_owner.tab1
DROP MATERIALIZED VIEW tab1; 

DROP MATERIALIZED VIEW LOG ON  binh_owner.tab1; 

Troubleshooting
-- Loi MVIEW tab1
--1.Lech du lieu bang
binh_owner.tab1

-- Khong co du lieu trong khi do du lieu nguon co du lieu
select * from v_tab1 where sub_id =123;

-- Check tu  nguon
select * from  tab1@dblink_name where sub_id =123;

--2.Xac dinh MVIEW
-- Check mview log
select * from DBA_MVIEW_LOGS;

--3.Check du lieu
--205020
select count(*) from cus_owner.tab1;

--205020
select count(*) from tab1@dblink_name;

--4.Chay thu job: Chay lau ko ra du liéu
BEGIN
dbms_refresh.refresh('BINH_OWNER.TAB1');
exception
WHEN OTHERS THEN 
dbms_output.put_line('DBDest-Loi dong bo tab1' || SQLErrM,'','');
end;

--5.Fix: 1p37s
exec dbms_snapshot.refresh('BINH_OWNER.TAB1','C');

--6.Kiem tra lai du lieu o buoc 2
=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
hoặc
https://bit.ly/oaz_fp
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,khóa học pl/sql, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle dataguard, oracle goldengate, oracle weblogic, oracle exadata, hoc solaris, hoc linux, hoc aix
By lúc
Labels: ,

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master