Oracle 数据库高级特性深度解析快照与物化视图管理器完全指南10. 什么是 Oracle 的快照管理器10.1 快照与快照管理器概述在 Oracle 数据库中快照(Snapshot)是物化视图(Materialized View)的早期名称。快照管理器是 Oracle 提供的一套用于创建、管理和维护快照(物化视图)的管理工具和机制它包含了快照的创建、刷新策略配置、日志管理和冲突解决等功能。从 Oracle 8i 开始Oracle 逐渐将快照概念统一到物化视图名称下。快照管理器核心体系快照管理器体系快照管理器快照定义刷新管理日志管理复制管理主表定义查询定义刷新模式存储属性刷新组管理定时调度状态监控冲突处理快照日志Snapshot Log清理策略空间管理主站点配置快照站点配置刷新模板部署管理快照(物化视图)命名演变| 时期 | 称呼 | 说明 ||------|------|------|| Oracle 7 | Snapshot(快照) | 最初的名称 || Oracle 8i | Snapshot/Materialized View | 两种名称共存 || Oracle 9i | Materialized View(物化视图) | 统一使用新名称 || 兼容性 | 所有版本 | SNAPSHOT关键字仍可识别 |10.2 快照架构与工作原理10.2.1 快照复制架构网络快照站点SnapshotSite主站点MasterSite主表Master Table快照日志MLOG$_TABLE内部触发器快照/物化视图Snapshot刷新进程数据库链接Database Link10.2.2 快照日志内部结构-- 查看快照日志结构 -- Oracle自动创建的快照日志表 -- 表名格式: MLOG$_基表名 -- 查看快照日志 SELECT master, log_table, log_owner, rowids, primary_key, object_id, filter_columns, current_snapshots FROM dba_snapshot_logs WHERE log_owner SCOTT; -- 查看快照日志中的变更记录 SELECT snaptime$$, dmltype$$, old_new$$, change_vector$$ FROM mlog$_emp;10.3 快照(物化视图)创建实战10.3.1 基础快照创建-- 创建快照日志快速刷新基础 -- 使用主键方式 CREATE SNAPSHOT LOG ON employees WITH PRIMARY KEY, ROWID INCLUDING NEW VALUES; -- 使用ROWID方式 CREATE SNAPSHOT LOG ON departments WITH ROWID INCLUDING NEW VALUES; -- 创建简单快照 -- 完整表快照 CREATE SNAPSHOT emp_snap BUILD IMMEDIATE REFRESH FAST ON DEMAND AS SELECT * FROM employees; -- 带条件的快照子集快照 CREATE SNAPSHOT dept30_emp_snap BUILD IMMEDIATE REFRESH FAST ON DEMAND AS SELECT * FROM employees WHERE department_id 30; -- 创建聚合物化视图使用SNAPSHOT语法 CREATE SNAPSHOT dept_salary_snap BUILD IMMEDIATE REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT department_id, COUNT(*) AS emp_count, SUM(salary) AS total_salary, AVG(salary) AS avg_salary, MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees GROUP BY department_id;10.3.2 多主站点快照配置-- 主站点配置 -- 1. 创建复制管理员 CREATE USER repadmin IDENTIFIED BY RepAdmin123! DEFAULT TABLESPACE users QUOTA UNLIMITED ON users; GRANT CONNECT, RESOURCE TO repadmin; GRANT CREATE SNAPSHOT, CREATE ANY SNAPSHOT TO repadmin; GRANT CREATE DATABASE LINK TO repadmin; GRANT CREATE SESSION, SELECT ANY TABLE TO repadmin; -- 2. 为要复制的表创建快照日志 CREATE SNAPSHOT LOG ON repadmin.products TABLESPACE users WITH PRIMARY KEY INCLUDING NEW VALUES; CREATE SNAPSHOT LOG ON repadmin.orders TABLESPACE users WITH PRIMARY KEY INCLUDING NEW VALUES; -- 快照站点配置 -- 1. 创建数据库链接到主站点 CREATE DATABASE LINK master_db CONNECT TO repadmin IDENTIFIED BY RepAdmin123! USING (DESCRIPTION (ADDRESS(PROTOCOLTCP)(HOSTmaster_host)(PORT1521)) (CONNECT_DATA(SERVICE_NAMEORCL)) ); -- 2. 创建快照 CREATE SNAPSHOT products_snap BUILD IMMEDIATE REFRESH FAST START WITH SYSDATE NEXT SYSDATE 1/24 -- 每小时刷新 AS SELECT * FROM repadmin.productsmaster_db; CREATE SNAPSHOT orders_snap BUILD IMMEDIATE REFRESH FAST START WITH SYSDATE NEXT SYSDATE 1/12 -- 每5分钟刷新 AS SELECT * FROM repadmin.ordersmaster_db;10.4 刷新组管理器详解10.4.1 DBMS_REFRESH 包操作-- 创建刷新组 BEGIN DBMS_REFRESH.MAKE( name daily_refresh_group, list , next_date TRUNC(SYSDATE) 2/24, -- 凌晨2点 interval SYSDATE 1, -- 每天一次 implicit_destroy FALSE, lax FALSE, job 0, rollback_seg NULL, push_deferred_rpc TRUE, refresh_after_errors FALSE, purge_option NULL, parallelism NULL, heap_size NULL ); END; / -- 向刷新组添加快照 BEGIN DBMS_REFRESH.ADD( name daily_refresh_group, list emp_snap, dept_salary_snap, products_snap ); END; / -- 从刷新组移除快照 BEGIN DBMS_REFRESH.SUBTRACT( name daily_refresh_group, list products_snap ); END; / -- 修改刷新组属性 BEGIN DBMS_REFRESH.CHANGE( name daily_refresh_group, next_date TRUNC(SYSDATE) 3/24, interval SYSDATE 1/24, implicit_destroy FALSE, rollback_seg NULL, push_deferred_rpc TRUE, refresh_after_errors FALSE, purge_option 1, parallelism 4, heap_size NULL ); END; / -- 手动执行刷新组 BEGIN DBMS_REFRESH.REFRESH(daily_refresh_group); END; / -- 删除刷新组 BEGIN DBMS_REFRESH.DESTROY(daily_refresh_group); END; /10.4.2 刷新组监控-- 查看所有刷新组 SELECT rname, job, next_date, interval, broken, owner FROM user_refresh; -- 查看刷新组成员 SELECT rname, job, owner, name AS snapshot_name FROM user_refresh_children; -- 创建刷新组状态视图 CREATE OR REPLACE VIEW v_refresh_group_status AS SELECT r.rname AS group_name, r.next_date, r.interval, r.broken, COUNT(c.name) AS snapshot_count, LISTAGG(c.name, , ) WITHIN GROUP (ORDER BY c.name) AS snapshots FROM user_refresh r LEFT JOIN user_refresh_children c ON r.rname c.rname AND r.owner c.owner GROUP BY r.rname, r.next_date, r.interval, r.broken; -- 查询刷新组状态 SELECT * FROM v_refresh_group_status;10.5 快照日志管理10.5.1 快照日志空间管理-- 查看快照日志信息 SELECT log_owner, master, log_table, log_trigger, rowids, primary_key, current_snapshots FROM dba_snapshot_logs WHERE log_owner SCOTT; -- 查看快照日志大小 SELECT segment_name, segment_type, ROUND(bytes/1024/1024, 2) AS size_mb, tablespace_name FROM dba_segments WHERE segment_name LIKE MLOG$% ORDER BY size_mb DESC; -- 清理快照日志 -- 清除指定快照的日志条目 BEGIN DBMS_SNAPSHOT.PURGE_LOG( master scott.employees, num 10000, -- 要保留的记录数 flag FEW -- FEW保留较新的MANY保留较旧的 ); END; / -- 清除所有已注册快照的日志 BEGIN DBMS_SNAPSHOT.PURGE_LOG( master scott.employees, num 1, flag ALL ); END; /10.5.2 快照日志清理策略-- 创建自动清理任务 CREATE OR REPLACE PROCEDURE purge_snapshot_logs IS v_retention_days NUMBER : 7; BEGIN FOR rec IN ( SELECT log_owner, master FROM dba_snapshot_logs WHERE log_owner SCOTT ) LOOP DBMS_SNAPSHOT.PURGE_LOG( master rec.log_owner || . || rec.master, num 1, flag ALL, retention SYSDATE - v_retention_days ); DBMS_OUTPUT.PUT_LINE(Purged log for: || rec.master); END LOOP; END purge_snapshot_logs; / -- 创建定时清理任务 BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name purge_snapshot_logs_job, job_type PLSQL_BLOCK, job_action BEGIN purge_snapshot_logs; END;, start_date SYSTIMESTAMP, repeat_interval FREQDAILY; BYHOUR3; BYMINUTE0, enabled TRUE, comments Daily purge of old snapshot logs ); END; /10.6 快照高级特性10.6.1 可更新快照-- 创建可更新快照 -- 可更新快照允许在快照端进行DML变化会传播回主表 -- 主站点创建可更新的快照日志 CREATE SNAPSHOT LOG ON products WITH PRIMARY KEY, ROWID INCLUDING NEW VALUES FOR UPDATE; -- 快照站点创建可更新快照 CREATE SNAPSHOT products_updatable_snap BUILD IMMEDIATE REFRESH FAST START WITH SYSDATE NEXT SYSDATE 1/24 FOR UPDATE AS SELECT * FROM productsmaster_db; -- 在快照端进行DML操作 UPDATE products_updatable_snap SET price price * 1.1 WHERE category Electronics; COMMIT; -- 推送更改回主站点 BEGIN DBMS_DEFER_SYS.PUSH( destination master_db, stop_on_error FALSE, delay_seconds 0, parallelism 1 ); END; /10.6.2 快照模板管理-- 创建快照模板 -- 部署模板用于在多个站点快速部署标准快照集 BEGIN -- 1. 创建部署模板 DBMS_REPCAT.CREATE_TEMPLATE_OBJECT( refresh_template_name sales_reporting_template, object_name products_snap, object_type SNAPSHOT, ddl_text CREATE SNAPSHOT %%SITE%%_products_snap BUILD IMMEDIATE REFRESH FAST START WITH SYSDATE NEXT SYSDATE 1/24 AS SELECT * FROM products%%MASTER%% ); -- 2. 添加参数定义 DBMS_REPCAT.ALTER_TEMPLATE_PARAMETER( refresh_template_name sales_reporting_template, parameter_name SITE, default_value report, prompt_string Enter site name ); -- 3. 实例化模板到目标站点 DBMS_REPCAT.INSTANTIATE_TEMPLATE( refresh_template_name sales_reporting_template, site_name report_site_1, parameter_values SITEreport1, MASTERmaster_db, use_default_gowner TRUE, copy_rows TRUE ); END; /10.7 快照监控与故障排除10.7.1 综合监控视图-- 创建快照健康检查视图 CREATE OR REPLACE VIEW v_snapshot_health_check AS WITH snapshot_info AS ( SELECT m.mview_name AS snapshot_name, m.last_refresh_type, m.last_refresh_date, m.staleness, m.compile_state, ROUND((SYSDATE - m.last_refresh_date) * 24 * 60, 0) AS minutes_since_refresh, m.mview_id FROM user_mviews m ), refresh_group_info AS ( SELECT c.name AS snapshot_name, r.rname AS group_name, r.next_date, r.interval AS refresh_interval, r.broken FROM user_refresh r JOIN user_refresh_children c ON r.rname c.rname ), log_info AS ( SELECT master AS table_name, log_table, current_snapshots FROM dba_snapshot_logs WHERE log_owner USER ) SELECT si.snapshot_name, si.last_refresh_type, si.last_refresh_date, si.staleness, si.compile_state, si.minutes_since_refresh, rgi.group_name, rgi.next_date, rgi.broken AS group_broken, CASE WHEN si.minutes_since_refresh 1440 THEN CRITICAL: 24h WHEN si.minutes_since_refresh 120 THEN WARNING: 2h WHEN si.staleness STALE THEN NEEDS_REFRESH ELSE OK END AS health_status FROM snapshot_info si LEFT JOIN refresh_group_info rgi ON si.snapshot_name rgi.snapshot_name ORDER BY si.last_refresh_date DESC; -- 查询健康报告 SELECT * FROM v_snapshot_health_check;10.7.2 常见问题解决方案| 问题 | 症状 | 解决方案 ||------|------|----------|| 快照日志过大 | MLOG$表占用大量空间 | 执行PURGE_LOG清理过期记录 || 快速刷新失败 | ORA-12034错误 | 重建快照日志检查主键约束 || 刷新组挂起 | 刷新作业损坏 | DBMS_REFRESH.DESTROY后重建 || 快照陈旧 | STALE状态持续 | 检查网络连接和数据库链接 || 复制冲突 | ORA-23305错误 | 配置冲突解决方法和通知 |10.7.3 快照故障恢复脚本-- 快照修复存储过程 CREATE OR REPLACE PROCEDURE repair_stale_snapshot( p_snapshot_name VARCHAR2 ) IS v_staleness VARCHAR2(20); BEGIN -- 检查快照状态 SELECT staleness INTO v_staleness FROM user_mviews WHERE mview_name UPPER(p_snapshot_name); DBMS_OUTPUT.PUT_LINE(Snapshot: || p_snapshot_name); DBMS_OUTPUT.PUT_LINE(Current status: || v_staleness); IF v_staleness STALE THEN -- 尝试完全刷新 BEGIN DBMS_SNAPSHOT.REFRESH( p_snapshot_name, C -- Complete refresh ); DBMS_OUTPUT.PUT_LINE(Complete refresh completed successfully); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(Complete refresh failed: || SQLERRM); -- 尝试重建快照 BEGIN EXECUTE IMMEDIATE DROP SNAPSHOT || p_snapshot_name; DBMS_OUTPUT.PUT_LINE(Old snapshot dropped); -- 需要从原定义重建此处为示例 DBMS_OUTPUT.PUT_LINE(Please recreate snapshot from original definition); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(Drop failed: || SQLERRM); END; END; ELSE DBMS_OUTPUT.PUT_LINE(Snapshot is || v_staleness || , no repair needed); END IF; END repair_stale_snapshot; / -- 执行修复 BEGIN repair_stale_snapshot(EMP_SNAP); END; /10.8 快照与物化视图的迁移对照-- 快照语法与物化视图语法对照 -- 创建快照旧语法 CREATE SNAPSHOT emp_snap_old BUILD IMMEDIATE REFRESH FAST ON DEMAND AS SELECT * FROM employees; -- 创建物化视图新语法推荐使用 CREATE MATERIALIZED VIEW emp_mv_new BUILD IMMEDIATE REFRESH FAST ON DEMAND AS SELECT * FROM employees; -- 查询系统表对照 -- 旧视图 SELECT * FROM user_snapshots; SELECT * FROM user_snapshot_logs; SELECT * FROM user_refresh; -- 新视图包含更完整信息 SELECT * FROM user_mviews; SELECT * FROM user_mview_logs; SELECT * FROM user_refresh;---通过本文的全面讲解我们深入掌握了 Oracle 快照管理器的各项功能。虽然现代 Oracle 版本更倾向于使用物化视图这一术语但理解快照的概念和历史对于维护旧系统、理解复制架构仍然至关重要。快照管理器提供了从快照创建、刷新组管理到日志清理、故障恢复的完整工具集是构建分布式数据库环境的重要组件。
Oracle 数据库高级特性深度解析快照与物化视图管理器完全指南10. 什么是 Oracle 的快照管理器10.1 快照与快照管理器概述在 Oracle 数据库中快照(Snapshot)是物化视图(Materialized View)的早期名称。快照管理器是 Oracle 提供的一套用于创建、管理和维护快照(物化视图)的管理工具和机制它包含了快照的创建、刷新策略配置、日志管理和冲突解决等功能。从 Oracle 8i 开始Oracle 逐渐将快照概念统一到物化视图名称下。快照管理器核心体系快照管理器体系快照管理器快照定义刷新管理日志管理复制管理主表定义查询定义刷新模式存储属性刷新组管理定时调度状态监控冲突处理快照日志Snapshot Log清理策略空间管理主站点配置快照站点配置刷新模板部署管理快照(物化视图)命名演变| 时期 | 称呼 | 说明 ||------|------|------|| Oracle 7 | Snapshot(快照) | 最初的名称 || Oracle 8i | Snapshot/Materialized View | 两种名称共存 || Oracle 9i | Materialized View(物化视图) | 统一使用新名称 || 兼容性 | 所有版本 | SNAPSHOT关键字仍可识别 |10.2 快照架构与工作原理10.2.1 快照复制架构网络快照站点SnapshotSite主站点MasterSite主表Master Table快照日志MLOG$_TABLE内部触发器快照/物化视图Snapshot刷新进程数据库链接Database Link10.2.2 快照日志内部结构-- 查看快照日志结构 -- Oracle自动创建的快照日志表 -- 表名格式: MLOG$_基表名 -- 查看快照日志 SELECT master, log_table, log_owner, rowids, primary_key, object_id, filter_columns, current_snapshots FROM dba_snapshot_logs WHERE log_owner SCOTT; -- 查看快照日志中的变更记录 SELECT snaptime$$, dmltype$$, old_new$$, change_vector$$ FROM mlog$_emp;10.3 快照(物化视图)创建实战10.3.1 基础快照创建-- 创建快照日志快速刷新基础 -- 使用主键方式 CREATE SNAPSHOT LOG ON employees WITH PRIMARY KEY, ROWID INCLUDING NEW VALUES; -- 使用ROWID方式 CREATE SNAPSHOT LOG ON departments WITH ROWID INCLUDING NEW VALUES; -- 创建简单快照 -- 完整表快照 CREATE SNAPSHOT emp_snap BUILD IMMEDIATE REFRESH FAST ON DEMAND AS SELECT * FROM employees; -- 带条件的快照子集快照 CREATE SNAPSHOT dept30_emp_snap BUILD IMMEDIATE REFRESH FAST ON DEMAND AS SELECT * FROM employees WHERE department_id 30; -- 创建聚合物化视图使用SNAPSHOT语法 CREATE SNAPSHOT dept_salary_snap BUILD IMMEDIATE REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT department_id, COUNT(*) AS emp_count, SUM(salary) AS total_salary, AVG(salary) AS avg_salary, MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees GROUP BY department_id;10.3.2 多主站点快照配置-- 主站点配置 -- 1. 创建复制管理员 CREATE USER repadmin IDENTIFIED BY RepAdmin123! DEFAULT TABLESPACE users QUOTA UNLIMITED ON users; GRANT CONNECT, RESOURCE TO repadmin; GRANT CREATE SNAPSHOT, CREATE ANY SNAPSHOT TO repadmin; GRANT CREATE DATABASE LINK TO repadmin; GRANT CREATE SESSION, SELECT ANY TABLE TO repadmin; -- 2. 为要复制的表创建快照日志 CREATE SNAPSHOT LOG ON repadmin.products TABLESPACE users WITH PRIMARY KEY INCLUDING NEW VALUES; CREATE SNAPSHOT LOG ON repadmin.orders TABLESPACE users WITH PRIMARY KEY INCLUDING NEW VALUES; -- 快照站点配置 -- 1. 创建数据库链接到主站点 CREATE DATABASE LINK master_db CONNECT TO repadmin IDENTIFIED BY RepAdmin123! USING (DESCRIPTION (ADDRESS(PROTOCOLTCP)(HOSTmaster_host)(PORT1521)) (CONNECT_DATA(SERVICE_NAMEORCL)) ); -- 2. 创建快照 CREATE SNAPSHOT products_snap BUILD IMMEDIATE REFRESH FAST START WITH SYSDATE NEXT SYSDATE 1/24 -- 每小时刷新 AS SELECT * FROM repadmin.productsmaster_db; CREATE SNAPSHOT orders_snap BUILD IMMEDIATE REFRESH FAST START WITH SYSDATE NEXT SYSDATE 1/12 -- 每5分钟刷新 AS SELECT * FROM repadmin.ordersmaster_db;10.4 刷新组管理器详解10.4.1 DBMS_REFRESH 包操作-- 创建刷新组 BEGIN DBMS_REFRESH.MAKE( name daily_refresh_group, list , next_date TRUNC(SYSDATE) 2/24, -- 凌晨2点 interval SYSDATE 1, -- 每天一次 implicit_destroy FALSE, lax FALSE, job 0, rollback_seg NULL, push_deferred_rpc TRUE, refresh_after_errors FALSE, purge_option NULL, parallelism NULL, heap_size NULL ); END; / -- 向刷新组添加快照 BEGIN DBMS_REFRESH.ADD( name daily_refresh_group, list emp_snap, dept_salary_snap, products_snap ); END; / -- 从刷新组移除快照 BEGIN DBMS_REFRESH.SUBTRACT( name daily_refresh_group, list products_snap ); END; / -- 修改刷新组属性 BEGIN DBMS_REFRESH.CHANGE( name daily_refresh_group, next_date TRUNC(SYSDATE) 3/24, interval SYSDATE 1/24, implicit_destroy FALSE, rollback_seg NULL, push_deferred_rpc TRUE, refresh_after_errors FALSE, purge_option 1, parallelism 4, heap_size NULL ); END; / -- 手动执行刷新组 BEGIN DBMS_REFRESH.REFRESH(daily_refresh_group); END; / -- 删除刷新组 BEGIN DBMS_REFRESH.DESTROY(daily_refresh_group); END; /10.4.2 刷新组监控-- 查看所有刷新组 SELECT rname, job, next_date, interval, broken, owner FROM user_refresh; -- 查看刷新组成员 SELECT rname, job, owner, name AS snapshot_name FROM user_refresh_children; -- 创建刷新组状态视图 CREATE OR REPLACE VIEW v_refresh_group_status AS SELECT r.rname AS group_name, r.next_date, r.interval, r.broken, COUNT(c.name) AS snapshot_count, LISTAGG(c.name, , ) WITHIN GROUP (ORDER BY c.name) AS snapshots FROM user_refresh r LEFT JOIN user_refresh_children c ON r.rname c.rname AND r.owner c.owner GROUP BY r.rname, r.next_date, r.interval, r.broken; -- 查询刷新组状态 SELECT * FROM v_refresh_group_status;10.5 快照日志管理10.5.1 快照日志空间管理-- 查看快照日志信息 SELECT log_owner, master, log_table, log_trigger, rowids, primary_key, current_snapshots FROM dba_snapshot_logs WHERE log_owner SCOTT; -- 查看快照日志大小 SELECT segment_name, segment_type, ROUND(bytes/1024/1024, 2) AS size_mb, tablespace_name FROM dba_segments WHERE segment_name LIKE MLOG$% ORDER BY size_mb DESC; -- 清理快照日志 -- 清除指定快照的日志条目 BEGIN DBMS_SNAPSHOT.PURGE_LOG( master scott.employees, num 10000, -- 要保留的记录数 flag FEW -- FEW保留较新的MANY保留较旧的 ); END; / -- 清除所有已注册快照的日志 BEGIN DBMS_SNAPSHOT.PURGE_LOG( master scott.employees, num 1, flag ALL ); END; /10.5.2 快照日志清理策略-- 创建自动清理任务 CREATE OR REPLACE PROCEDURE purge_snapshot_logs IS v_retention_days NUMBER : 7; BEGIN FOR rec IN ( SELECT log_owner, master FROM dba_snapshot_logs WHERE log_owner SCOTT ) LOOP DBMS_SNAPSHOT.PURGE_LOG( master rec.log_owner || . || rec.master, num 1, flag ALL, retention SYSDATE - v_retention_days ); DBMS_OUTPUT.PUT_LINE(Purged log for: || rec.master); END LOOP; END purge_snapshot_logs; / -- 创建定时清理任务 BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name purge_snapshot_logs_job, job_type PLSQL_BLOCK, job_action BEGIN purge_snapshot_logs; END;, start_date SYSTIMESTAMP, repeat_interval FREQDAILY; BYHOUR3; BYMINUTE0, enabled TRUE, comments Daily purge of old snapshot logs ); END; /10.6 快照高级特性10.6.1 可更新快照-- 创建可更新快照 -- 可更新快照允许在快照端进行DML变化会传播回主表 -- 主站点创建可更新的快照日志 CREATE SNAPSHOT LOG ON products WITH PRIMARY KEY, ROWID INCLUDING NEW VALUES FOR UPDATE; -- 快照站点创建可更新快照 CREATE SNAPSHOT products_updatable_snap BUILD IMMEDIATE REFRESH FAST START WITH SYSDATE NEXT SYSDATE 1/24 FOR UPDATE AS SELECT * FROM productsmaster_db; -- 在快照端进行DML操作 UPDATE products_updatable_snap SET price price * 1.1 WHERE category Electronics; COMMIT; -- 推送更改回主站点 BEGIN DBMS_DEFER_SYS.PUSH( destination master_db, stop_on_error FALSE, delay_seconds 0, parallelism 1 ); END; /10.6.2 快照模板管理-- 创建快照模板 -- 部署模板用于在多个站点快速部署标准快照集 BEGIN -- 1. 创建部署模板 DBMS_REPCAT.CREATE_TEMPLATE_OBJECT( refresh_template_name sales_reporting_template, object_name products_snap, object_type SNAPSHOT, ddl_text CREATE SNAPSHOT %%SITE%%_products_snap BUILD IMMEDIATE REFRESH FAST START WITH SYSDATE NEXT SYSDATE 1/24 AS SELECT * FROM products%%MASTER%% ); -- 2. 添加参数定义 DBMS_REPCAT.ALTER_TEMPLATE_PARAMETER( refresh_template_name sales_reporting_template, parameter_name SITE, default_value report, prompt_string Enter site name ); -- 3. 实例化模板到目标站点 DBMS_REPCAT.INSTANTIATE_TEMPLATE( refresh_template_name sales_reporting_template, site_name report_site_1, parameter_values SITEreport1, MASTERmaster_db, use_default_gowner TRUE, copy_rows TRUE ); END; /10.7 快照监控与故障排除10.7.1 综合监控视图-- 创建快照健康检查视图 CREATE OR REPLACE VIEW v_snapshot_health_check AS WITH snapshot_info AS ( SELECT m.mview_name AS snapshot_name, m.last_refresh_type, m.last_refresh_date, m.staleness, m.compile_state, ROUND((SYSDATE - m.last_refresh_date) * 24 * 60, 0) AS minutes_since_refresh, m.mview_id FROM user_mviews m ), refresh_group_info AS ( SELECT c.name AS snapshot_name, r.rname AS group_name, r.next_date, r.interval AS refresh_interval, r.broken FROM user_refresh r JOIN user_refresh_children c ON r.rname c.rname ), log_info AS ( SELECT master AS table_name, log_table, current_snapshots FROM dba_snapshot_logs WHERE log_owner USER ) SELECT si.snapshot_name, si.last_refresh_type, si.last_refresh_date, si.staleness, si.compile_state, si.minutes_since_refresh, rgi.group_name, rgi.next_date, rgi.broken AS group_broken, CASE WHEN si.minutes_since_refresh 1440 THEN CRITICAL: 24h WHEN si.minutes_since_refresh 120 THEN WARNING: 2h WHEN si.staleness STALE THEN NEEDS_REFRESH ELSE OK END AS health_status FROM snapshot_info si LEFT JOIN refresh_group_info rgi ON si.snapshot_name rgi.snapshot_name ORDER BY si.last_refresh_date DESC; -- 查询健康报告 SELECT * FROM v_snapshot_health_check;10.7.2 常见问题解决方案| 问题 | 症状 | 解决方案 ||------|------|----------|| 快照日志过大 | MLOG$表占用大量空间 | 执行PURGE_LOG清理过期记录 || 快速刷新失败 | ORA-12034错误 | 重建快照日志检查主键约束 || 刷新组挂起 | 刷新作业损坏 | DBMS_REFRESH.DESTROY后重建 || 快照陈旧 | STALE状态持续 | 检查网络连接和数据库链接 || 复制冲突 | ORA-23305错误 | 配置冲突解决方法和通知 |10.7.3 快照故障恢复脚本-- 快照修复存储过程 CREATE OR REPLACE PROCEDURE repair_stale_snapshot( p_snapshot_name VARCHAR2 ) IS v_staleness VARCHAR2(20); BEGIN -- 检查快照状态 SELECT staleness INTO v_staleness FROM user_mviews WHERE mview_name UPPER(p_snapshot_name); DBMS_OUTPUT.PUT_LINE(Snapshot: || p_snapshot_name); DBMS_OUTPUT.PUT_LINE(Current status: || v_staleness); IF v_staleness STALE THEN -- 尝试完全刷新 BEGIN DBMS_SNAPSHOT.REFRESH( p_snapshot_name, C -- Complete refresh ); DBMS_OUTPUT.PUT_LINE(Complete refresh completed successfully); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(Complete refresh failed: || SQLERRM); -- 尝试重建快照 BEGIN EXECUTE IMMEDIATE DROP SNAPSHOT || p_snapshot_name; DBMS_OUTPUT.PUT_LINE(Old snapshot dropped); -- 需要从原定义重建此处为示例 DBMS_OUTPUT.PUT_LINE(Please recreate snapshot from original definition); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(Drop failed: || SQLERRM); END; END; ELSE DBMS_OUTPUT.PUT_LINE(Snapshot is || v_staleness || , no repair needed); END IF; END repair_stale_snapshot; / -- 执行修复 BEGIN repair_stale_snapshot(EMP_SNAP); END; /10.8 快照与物化视图的迁移对照-- 快照语法与物化视图语法对照 -- 创建快照旧语法 CREATE SNAPSHOT emp_snap_old BUILD IMMEDIATE REFRESH FAST ON DEMAND AS SELECT * FROM employees; -- 创建物化视图新语法推荐使用 CREATE MATERIALIZED VIEW emp_mv_new BUILD IMMEDIATE REFRESH FAST ON DEMAND AS SELECT * FROM employees; -- 查询系统表对照 -- 旧视图 SELECT * FROM user_snapshots; SELECT * FROM user_snapshot_logs; SELECT * FROM user_refresh; -- 新视图包含更完整信息 SELECT * FROM user_mviews; SELECT * FROM user_mview_logs; SELECT * FROM user_refresh;---通过本文的全面讲解我们深入掌握了 Oracle 快照管理器的各项功能。虽然现代 Oracle 版本更倾向于使用物化视图这一术语但理解快照的概念和历史对于维护旧系统、理解复制架构仍然至关重要。快照管理器提供了从快照创建、刷新组管理到日志清理、故障恢复的完整工具集是构建分布式数据库环境的重要组件。
