Archive

Archive for the ‘Advanced Replication’ Category

高级复制–ORA-01422

March 8th, 2009 No comments
2008-07-31 17:28

论坛上看到有人说高级复制遭遇:

ORA-1422, 00000, “exact fetch returns more than requested number of rows”
// *Cause: The number specified in exact fetch is less than the rows returned.
// *Action: Rewrite the query or change number of rows requested

其实这个错误最好的找出问题的方法就是做trace,从而能找到引起这个问题的具体原因。

当然了,也有一些经验值:
转自metalink

Subject: ORA-1422 WHEN DEFERRED TRANSACTIONS PUSHED TO THE REMOTE SITE
  Doc ID: Note:1039878.6 Type: PROBLEM
  Last Revision Date: 13-MAY-2002 Status: PUBLISHED


PROBLEM DESCRIPTION:
=====================

You have a replication environment and receive the following error:

ORA-1422, 00000, "exact fetch returns more than requested number of rows"
// *Cause: The number specified in exact fetch is less than the rows returned.
// *Action: Rewrite the query or change number of rows requested

PROBLEM EXPLANATION:
====================

There may be 2 entries in the SYS.DUAL table.
See Note 1067733.6 to resolve this issue.

--OR--

Your replicated tables may not have a primary key or
alternate primary key

SEARCH WORDS:
=============
ORA-1422 replication dbms_defer_sys deftran deferror ORA-01422

SOLUTION DESCRIPTION:
======================
Because two rows may have different row IDs at different replication sites,
Oracle uses the primary key for a table to determine which rows to
compare.  If you do not want to use the primary key for a table or if the
table does not have a primary key, you must use DBMS_REPCAT.SET_COLUMNS
procedure to define an alternate primary key.

SQL> describe sys.dbms_repcat.set_columns
PROCEDURE sys.dbms_repcat.set_columns
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SNAME                          VARCHAR2                IN
 ONAME                          VARCHAR2                IN
 COLUMN_LIST                    VARCHAR2                IN

Because this key is used to determine matching rows at different sites,
the columns that you specify in the COLUMN_LIST must result in a unique
identifier for the row.  The procedure SET_COLUMNS will NOT enforce
uniqueness.

高级复制环境 ORA-23402: 由于延迟事务处理引起的冲突,刷新被终止

March 6th, 2009 No comments

高级复制环境
ORA-23402: 由于延迟事务处理引起的冲突,刷新被终止

 
SQL>
SQL> SELECT DEFERRED_TRAN_ID,
  2         ORIGIN_TRAN_DB,
  3         DESTINATION,
  4         TO_CHAR(START_TIME, ‘DD-Mon-YYYY hh24:mi:ss’) TIME_OF_ERROR,
  5         ERROR_NUMBER
  6  FROM DEFERROR ORDER BY START_TIME;
 
DEFERRED_TRAN_ID       ORIGIN_TRAN_DB                          DESTINATION                             TIME_OF_ERROR           ERROR_NUMBER
———————- ————————————— ————————————— ———————– ————
6.36.2227              CSLGS.REGRESS.RDBMS.DEV.US.ORACLE.COM   CSLNDC.REGRESS.RDBMS.DEV.US.ORACLE.COM  06-3月 -2009 00:16:27             -1
 
SQL>
SQL>
SQL>
SQL>
SQL> select owner,object_name,object_id,data_object_id,object_type from dba_objects
  2  where object_name=’DEF$_AQCALL’;
 
OWNER        OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
———— ————— ———- ————– ———–
SYS          DEF$_AQCALL           4929                SYNONYM
SYSTEM       DEF$_AQCALL           4874           4874 TABLE
SYSTEM       DEF$_AQCALL           4884                QUEUE
 

SQL>
SQL> BEGIN
  2   DBMS_DEFER_SYS.DELETE_ERROR(
  3     deferred_tran_id=>’6.36.2227′,
  4     destination=>’CSLNDC.REGRESS.RDBMS.DEV.US.ORACLE.COM’);
  5  END;
  6  /
 
PL/SQL procedure successfully completed
 
SQL> commit;
 
Commit complete
 
SQL>
SQL> SELECT DEFERRED_TRAN_ID,
  2         ORIGIN_TRAN_DB,
  3         DESTINATION,
  4         TO_CHAR(START_TIME, ‘DD-Mon-YYYY hh24:mi:ss’) TIME_OF_ERROR,
  5         ERROR_NUMBER
  6  FROM DEFERROR ORDER BY START_TIME;
 
DEFERRED_TRAN_ID       ORIGIN_TRAN_DB   DESTINATION   TIME_OF_ERROR    ERROR_NUMBER
———————- —————- ————- —————- ————

oracle高级复制配置手记

March 5th, 2009 No comments

一、准备工作

1.1 检查初始化参数

确认gloal_names是否为true,job_queue_process大于0。分别在主站点和物化视图站点执行下面两条sqlplus 命令,检查数据库初始化参数是否符合要求。

show parameter global_names
show parameter job

如果初始化参数设置的不满足要求,可以通过下列语句动态修改。

alter system set global_names = true;
alter system set job_queue_processes = 20;

1.2 检查全局数据库名称

两个数据库的db_domain 名称应该相同,只有db_name 不同。通过下列语句检查主站点和物化视图站点的全局数据库名:

select * from global_name;

GLOBAL_NAME
——————————————————————————–
RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM

如果全局数据库名设置不符合规范,可以通过如下语句动态修改。

alter database rename global_name to  RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM;     (主站点)
alter database rename global_name to  TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM;      (物化视图站点)

1.3 修改tnsnames.ora 文件

主站点和物化视图站点的tnsnames.ora参数文件中都添加下列内容,注意需要按照实际情况修改HOST的ip地址,以及SERVICE_NAME的值,PORT一般都默认为1521。

RANDY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19.168.0.66)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = randy)
    )
  )

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19.168.0.68)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )

二、主体站点配置

2.1 建立复制管理用户并授权

以sys或者system用户登陆到主体站点
conn sys/test as sysdba;

–建立复制管理员,在实际应用中,需要修改repadmin帐号的密码,以确保安全。
CREATE USER repadmin IDENTIFIED BY repadmin;

–授权
BEGIN
  DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (username => ‘repadmin’);
END;
/

GRANT COMMENT ANY TABLE TO repadmin;
GRANT LOCK ANY TABLE TO repadmin;
GRANT SELECT ANY DICTIONARY TO repadmin;

2.2 注册传播者

这里使用了管理用户repadmin,也可以分别建立用户。

BEGIN
  DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => ‘repadmin’);
END;
/

2.3 注册接收者

这里使用了管理用户repadmin,也可以分别建立用户。

BEGIN
  DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
                    username => ‘repadmin’,
                    privilege_type => ‘receiver’,
                    list_of_gnames => NULL);
END;
/

2.4 建立物化视图站点复制管理员的代理用户

–出于简单考虑,这里也使用repadmin 用户
BEGIN
  DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
                    username => ‘repadmin’,
                    privilege_type => ‘proxy_snapadmin’,
                    list_of_gnames => NULL);
END;
/

–授权
–对于repadmin 而言,不需要create session 权限
–但是这里如果新建用户的话,create session 权限则是必须的
GRANT CREATE SESSION TO repadmin;
GRANT SELECT ANY TABLE TO repadmin;

2.5 设置清除延迟序列的job

–以复制管理员身份登陆到主站点
CONNECT repadmin/repadmin;

BEGIN
  DBMS_DEFER_SYS.SCHEDULE_PURGE (
                 next_date => SYSDATE,
                 interval => ‘SYSDATE + 1/24′,
                 delay_seconds => 0);
END;
/
Commit;

2.6 对复制用户授权

conn sys/test as sysdba

create user rbt
identified by rbt
default tablespace users
temporary tablespace temp;

GRANT CREATE SESSION,CREATE TABLE,CREATE PROCEDURE,CREATE SEQUENCE,CREATE TRIGGER,
      CREATE VIEW,CREATE SYNONYM,ALTER SESSION,CREATE MATERIALIZED VIEW,
      ALTER ANY MATERIALIZED VIEW,CREATE DATABASE LINK,select any dictionary
TO rbt;

2.7 建立复制测试表

conn sys/test as sysdba

create table rbt.jobs
as
select * from hr.jobs;

alter table rbt.jobs
add constraint pk_jobs primary key(job_id);

2.8 在主体站点上建立主体组,将复制对象增加到复制组中

conn repadmin/repadmin

BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPGROUP (
              gname => ‘RBTSYN’);
END;
/
Commit;

BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
              gname => ‘RBTSYN’,
              type => ‘TABLE’,
              oname => ‘JOBS’,
              sname => ‘RBT’,
              use_existing_object => TRUE,
              copy_rows => FALSE);
END;
/

BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
              gname => ‘RBTSYN’,
              type => ‘INDEX’,
              oname => ‘PK_JOBS’,
              sname => ‘rbt’,
              use_existing_object => TRUE,
              copy_rows => FALSE);
END;
/

–添加的时候如果一次没有成功,再次添加可能会报ORA-23308或ORA-23309的错误
–使用DBMS_REPCAT.DROP_MASTER_REPOBJECT删除对象后重新添加

2.9 在主体站点上生成复制支持

BEGIN
  DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
              sname => ‘RBT’,
              oname => ‘JOBS’,
              type => ‘TABLE’,
              min_communication => TRUE);
END;
/

2.10 在主体站点上开始复制

BEGIN
  DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
              gname =>’RBTSYN’);
END;
/

2.11 主体站点上面建立物化视图日志表

conn rbt/rbt

create materialized view log on jobs;

3. 物化视图站点配置

–连接到物化视图站点
conn sys/test as sysdba;

3.1 建立物化视图管理员并授权

CREATE USER mvadmin IDENTIFIED BY mvadmin;

BEGIN
  DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
                    username => ‘mvadmin’);
END;
/
GRANT COMMENT ANY TABLE TO mvadmin;
GRANT LOCK ANY TABLE TO mvadmin;
GRANT SELECT ANY DICTIONARY TO mvadmin;

3.2 注册传播者,并授权

–这里使用mvadmin 用户,也可以建立单独的用户
BEGIN
  DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => ‘mvadmin’);
END;
/

–建立刷新者,并授权,这里使用mvadmin 用户刷新物化视图
–对于mvadmin 而言,不需要create session 权限
–但是这里如果新建用户的话,create session 权限则是必须的
GRANT CREATE SESSION TO mvadmin;
GRANT ALTER ANY MATERIALIZED VIEW TO mvadmin;

3.3 注册接收者,并授权

–注册接受者
BEGIN
  DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
                    username => ‘mvadmin’,
                    privilege_type => ‘receiver’,
                    list_of_gnames => NULL);
END;
/                         

3.4 建立到主站点公用的数据库链接

–通过sys用户,建立公用的数据库链接,注意在正式使用的时候,需要按照需要修改数据–库链接的名字和主站点上面的别名
CREATE PUBLIC DATABASE LINK RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM USING ‘randy’;

3.5 从物化视图站点建立到主站点上代理物化视图管理员的数据库链

conn mvadmin/mvadmin

create database link RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM
connect to repadmin identified by repadmin;

3.6 在物化视图站点设置清除延迟序列的job

BEGIN
  DBMS_DEFER_SYS.SCHEDULE_PURGE (
                 next_date => SYSDATE,
                 interval => ‘SYSDATE + 1/24′,
                 delay_seconds => 0,
                 rollback_segment => ”);
END;
/

3.7 在物化视图站点上设置将修改推入到主站点的job

BEGIN
  DBMS_DEFER_SYS.SCHEDULE_PUSH (
                 destination => ‘RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM’,
                 interval => ‘sysdate + 10/(60*60*24)’,
                 next_date => SYSDATE,
                 stop_on_error => FALSE,
                 delay_seconds => 0,
                 parallelism => 0);
END;
/

3.8 建立复制用户并对其授权

create user rbt identified by rbt;
alter user rbt temporary tablespace temp;

GRANT CREATE SESSION,CREATE TABLE,CREATE PROCEDURE,CREATE SEQUENCE,
      CREATE TRIGGER,CREATE VIEW,CREATE SYNONYM,ALTER SESSION,CREATE MATERIALIZED VIEW,
      ALTER ANY MATERIALIZED VIEW,CREATE DATABASE LINK,select any dictionary
TO rbt;

3.9 在目标站点上面建立复制用户到主站点代理刷新者的数据库链

conn rbt/rbt

CREATE DATABASE LINK RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM
CONNECT TO repadmin IDENTIFIED BY repadmin;

3.10 在物化视图管理帐号上建立物化视图组

–以物化视图管理员身份登陆物化视图站点
CONNECT mvadmin/mvadmin

–物化视图组必须和复制站点上的复制组名称相同
BEGIN
  DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
              gname => ‘RBTSYN’,
              master => ‘RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM’,
              propagation_mode => ‘ASYNCHRONOUS’);
END;
/

–创建刷新组,从物化视图上面刷新数据到本地表
–对于只包含只读物化视图的站点,不需要此步骤
BEGIN
  DBMS_REFRESH.MAKE (
               name => ‘mvadmin.rbt_refresh’,
               list => ”,
               next_date => SYSDATE,
               interval => ‘sysdate + 10/(60*60*24)’,
               implicit_destroy => FALSE,
               rollback_seg => ”,
               push_deferred_rpc => TRUE,
               refresh_after_errors => FALSE);
END;
/

–创建物化视图

create table rbt.jobs
as
select * FROM rbt.jobs@RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM
where 1=2;

CREATE MATERIALIZED VIEW RBT.JOBS
ON PREBUILT TABLE
REFRESH FAST
NEXT sysdate + 5/1440
AS
SELECT * FROM rbt.jobs@RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM;

BEGIN
   DBMS_REFRESH.ADD(
     name => ‘”MVADMIN”.”RBT_REFRESH”‘,
     list => ‘”RBT”.”JOBS”‘,
     lax => TRUE);
END;

–将物化视图添加到刷新组
BEGIN
   DBMS_REFRESH.ADD(
     name => ‘”MVADMIN”.”RBT_REFRESH”‘,
     list => ‘”RBT”.”JOBS”‘,
     lax => TRUE);
END;

–将物化视图添加到物化视图组
–对于只读物化视图,此步骤可以省略
BEGIN
   DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
      gname => ‘”RBTSYN”‘,
      sname => ‘”RBT”‘,
      oname => ‘”JOBS”‘,
      type => ‘SNAPSHOT’,
      min_communication => FALSE);
END;

BEGIN
   DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
      gname => ‘”RBTSYN”‘,
      sname => ‘”RBT”‘,
      oname => ‘”PK_JOBS”‘,
      type => ‘INDEX’);
END;

–至此,物化视图的配置就全部完成了。

四、生成测试数据,并验证配置

–在主站点执行数据修改
begin
  for i in 1..20
  loop
    insert into jobs values(i,i,i,i);
  end loop;
  commit;
end;
/

–在复制站点验证结果

conn rbt/rbt

select * from jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
———- ———————————– ———- ———-
1          1                                            1          1
10         10                                          10         10
……
19         19                                          19         19
20         20                                          20         20

5 注意事项

5.1 复制对象的限制

序列不能复制;
主键索引和其他索引,均需要添加到复制对象里面,不能自动复制;
ddl语句,不会自动复制。

5.2 如何使用序列同步

只能对所有的序列,建立一个job,扫描对应表的最大id值,并定时执行,或者在需要使用的时候,手工执行序列同步job。

Oracle的实体化视图管理

March 5th, 2009 No comments

 

1. 实体化视图概念
  实体化视图管理是用于汇总,预计算,复制或分发数据的对象, 在大型的数据库中使用它可以提高涉及到的SUM,COUNT,AVG,MIN,MAX等的表的查询的速度,只要在实体化视图管理上创建了统计,查询优化器将自动的使用实体化视图管理,这特性被称为QUERY REWRITE(查询重写).与普通的视图不同的是实体化视图管理存储数据,占据数据库的物理空间的.
  创建实体化视图管理的用户的权限必须有: CREATE MATERIALZED VIEW,CREATE TABLE,CREATE VIEW,SELECT等,如果在其他的模式中创建的话要在表上有CREATE ANY MATERIALIZED VIEW和SELECT权限.
  要查询重引用别的模式中的实体化视图管理的话,还要有引用的表的GLOBAL QUERY REWRITE OR QUERY REWRITE权限.
  如果计划使用实体化视图管理的话,要修改参数文件中加: QUERY_REWRITE_ENABLE=TRUE
  2. 创建实体化视图注意事项:
  创建之后,是否要填写数据
  多长时间刷新一次
  使用那种刷新类型: COMPLE(完全),FAST(快速),FORCE(强制),NEVER(从不)

  3. 创建实体化视图
  CREATE MATERIALIZED VIEW TEST3
  PCTFREE 0 TABLESPACE MTEST
  STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
  BUILD DEFERRED
  REFRESH FAST ON COMMIT
  ENABLE QUERY REWRITE
  AS SELECT EMP_NO,SUM(QTY_PSC) AS QTY_PSC FROM BSEMPMS GROUP BY EMP_NO;

  4. 删除实体化视图
  DROP MATERIALIZED VIEW TEST3;

  5. 管理和评估实体化视图的工具
  要运行DBMSSNAP.SQL和DBMSSUM.SQL为实体化视图创建包DBMS_MVIEW和DBMS_OLAP
  DBMS_MVIEW用于执行管理活动的.
  DBMS_OLAP确定实体化视图是否能提高查询的性能.

  6. 刷新实体化视图的方法
  EXECUTE DBMS_MVIEW.REFRESH(‘TEST3′,’C');
  其中TEST3为实体化视图名称,’C'为刷新类型中的一种.
  刷新类型有: C-完全刷新
 

 F-快速刷新和强制刷新
  EXECUTE DBMS_MVIEW.REFRESH_ALL
  不接受任何参数,全部刷新.

  7. 实体化视图间的完整性
  在实体化视图互相嵌套的时候会用不同的实体化视图的刷新的方式和时间的不同而引起的数据的完整性的问题,对于这种情况,建议把相关联的实体化视图放在同一个刷新组中,协调进行刷新.
  DBMS_REFRESH软件包中提供MAKE过程来创建一个刷新组.
  例如:
  EXECUTE DBMS_REFRESH.MAKE
  (NAME=’TEST_GROUP’,
  LIST=’LOC_EMP,LOC_DPT’,
  NEXT_DATE=SYSDATE,
  INTERVAL=’SYSDATE+7′);
  上面创建一个实体化视图刷新组TEST_GROUP,刷新周期为7天一次的.
  两个实体化视图LOC_EMP和LOC_DPT通过一个参数来传递给过程的.

  8. 向刷新组中增加刷新的实体化视图
  DBMS_REFRESH.ADD
  (NAME IN VARCHAR2,
  LIST IN VARCHAR2,|
  TAB IN DBMS_UTILITY.UNCLARRAY,
  LAX IN BOOLEAN:=FALSE);
 

 9. 向刷新组中删除刷新的实体化视图
  DBMS_REFRESH.SUBTRACT
  (NAME IN VARCHAR2,
  LIST IN VARCHAR2,|
  TAB IN DBMS_UTILITY.UNCLARRAY,
  LAX IN BOOLEAN:=FALSE);

  10. 手工删除一个刷新组
  EXECUTE DBMS_REFRESH.REFRESH(‘TEST_GROUP’);
  也可以
  EXECUTE DBMS_REFRESH.DESTROY(NAME=’TEST_GROUP’);

  11. 实体化视图的日志管理
  实体化视图日志是一个表,保持对实体化视图操作的历史记录.
  要创建实体化视图日志必须能在表上创建AFTER ROW触发器,必须有CREATE TRIGGER,CREATE TABLE权限
  例如:
  CREATE MATERIALIZED VIEW LOG ON BSEMPMS
  TABLESPACE DATA_TEST
  STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0)
  PCTFREE 5 PCTUSED 90;
  实体化视图日志的PCTFREE应该很小,PCTUSED应该很大的.
  通过ALTER MATERIALIZED VIEW LOG命令可以修改实体化视图日志参数.
  如:
  ALTER MATERIALIZED VIEW LOG BSEMPMS PCTFREE 1;
  要撤销实体化视图日志,可以用DROP MATERIALIZED VIEW LOG命令
  如:
  DROP MATERIALIZED VIEW LOG ON BSEMPMS;
  

  12. 清除实体化视图日志
  要减少实体化视图日志使用的空间,可用DBMS_MVIEW软件包中的PURGE_LOG过程,PURGE_LOG有三个参数:
  主表名称,NUM变量,DELETE标志
  NUM指最近最少刷新的实体化视图数量
  例如:
  EXECUTE DBMS_MVIEW.PURGE_LOG
  (MATER=’BSEMPMS’,
  NUM=1,
  FLAG=’DELETE’);
  BSEMPMS表的实体化视图将清除实体化视图最近最少使用的条目.
  要在截断主表的时候而不丢失实体化视图日志条目,可以用命令
  TRUNCATE TABLE BSEMPMS PRESERVE MATERIALIZED VIEW LOG;
 

 13. 也可以用OEM来创建和管理实体化视图
  

高级复制下如何强制删除复制组

March 4th, 2009 1 comment

  
   在高级复制环境下,有时候由于一些故障,可能导致复制组删除时出现错误。

$ sqlplus repadmin/repadmin
SQL*Plus: Release 10.1.0.2.0 – Production on Tue Sep 19 10:34:30 2006

Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set linesize 120
SQL> col sname for a10
SQL> col gname for a10
SQL> col schema_comment for a1
SQL> col fname for a10
SQL> col owner for a10
SQL> select * from dba_repgroup;

SNAME M STATUS S GNAME FNAME R OWNER
———- – ——— – ———- ———- – ———-
REP_TEST Y NORMAL REP_TEST N PUBLIC

SQL> exec dbms_repcat.drop_master_repgroup(gname=>’rep_test’,all_sites => true);
BEGIN dbms_repcat.drop_master_repgroup(gname=>’rep_test’,all_sites => true); END;

*
ERROR at line 1:
ORA-23353: deferred RPC queue has entries for object group “PUBLIC”.”REP_TEST”
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.DBMS_REPCAT_MAS”, line 1190
ORA-06512: at “SYS.DBMS_REPCAT_MAS”, line 2666
ORA-06512: at “SYS.DBMS_REPCAT”, line 635
ORA-06512: at line 1
这表明此时,在deftran中还有记录,而可能在deftrandest中已经没有需要push的事务

SQL> select count(*) from deftran;
COUNT(*)
———-
3

SQL> select count(*) from deftrandest;

COUNT(*)
———-
0
正常情况下可以通过dbms_defer_sys.purge过程去清除队列。
在异常情况下,我们可以选择直接截断(truncate),强制删除复制组:
SQL> truncate table system.def$_aqcall;
 

Table truncated.

SQL> exec dbms_repcat.drop_master_repgroup(gname=>’rep_test’,all_sites => true);

PL/SQL procedure successfully completed.
此时可以进行其他操作,如恢复复制组等。

使用Oracle数据库高级复制中最简单的功能

March 4th, 2009 No comments

 

  前言

  日益增长的分布式应用需求要求实现更好分布式的软件环境,不断推动着分布式技术的进步。Oracle数据复制是实现分布式数据环境的一种技术,通过在不同的物理站点拷贝数据来建立分布式数据环境。它与分布式数据库不同,在分布式数据库中,虽然每个数据对象也对所有的站点可用,但是特定的数据对象只存在于一个特定的站点中。而数据复制实现所有的站点都有相同数据对象的可用拷贝。

  在一个典型的分布式商业应用中经常需要把个地区的数据备份到总部的数据库中,一方面可以作为一种备份方式,另一方面也方便总部应用中的综合统计。这是Oracle数据复制中的简单应用,本文将以这样一个例子,讲述如何实现Oracle数据复制。

  实际情况是,A公司总部在北京,有三个营业部分别位于上海(ORACLE.SHANGHAI.COM)、杭州(ORACLE.HANGZHOU.COM)和武汉(ORACLE.

  WUHAN.COM)。三个营业部的软件系统相同,数据库结构也相同。现在需要把三个营业部的数据全部备份到总部的数据库中。

  准备工作

  在进行复制之前需要准备的东西很多,当然最基础就是网络必须畅通,之后需要收集一些复制环境的基本信息:

  1. 需要复制的数据库站点的数量

  2. 每个站点的Oracle版本号

  3. 每个需要复制的数据库的大小

  4. 每个数据库所使用的字符集

  5. 每个需要复制的数据所用的方案名

  收集完环境信息,可以开始建立总部的集中数据库,集中数据库要求版本高于所有主战点的版本,最好所有的数据库都是用相同的字符集。建好库后为每个主站点的备份数据分别建一个表空间,表空间大于需要复制的数据量,至于预留以后的发展空间视实际情况而定。

  为每个主站点的对应复制数据建立方案,如果各个主站点所使用的方案名不同,在集中数据库站点分别建立名称相同的对应方案。否则为各主站点的复制数据分别建立相应的方案名。实际情况是后者,各营业部的数据库都是用Oracle的方案名,这里我们建立三个对应方

  案:SHORACL、HZORACL 和WHORACL。所有数据库的版本都是9i。

  基本概念

  复制之前先解释一下复制中的几个概念:

  1.主站点(Mater Site):在复制过程中提供数据源的站点。如上图中的上海数据库站点。

  2.实体化视图站点(Materialized View Site):实体化视图复制中的目标站点。如上图中的北京数据库站点。

  3.多主体站点复制(Multimaster Replication):复制环境中的站点都是主站点,对复制的数据库对象有相同的管理权限。

  4.实体化视图复制(Materialized View Replication): 一个主体站点提供源复制对象,一个实体化视图站点拷贝主站点数据。

  5.实体化视图(Materialized View):在实体化视图站点为每个复制表或者视图建立一个对应的表保存相应的数据,该表只能通过Oracle的复制机制进行增删改数据的操作。

  6. 快速刷新、完全刷新和强制刷新:复制过程中的三种刷新方式。快速刷新只复制源数据对象的改变部分;完全刷新每次都拷贝一遍源数据对象;强制刷新是数据库的一个折衷方案,如果快速刷新失败则使用完全刷新。

  7. 主体组(Master Group):主体站点中被复制的源数据对象的集合。

  8. 实体化视图组(Materialized View Site):实体化视图站点中复制对象的集合。

  9. 实体化视图日志(Materialized View Log):实体化视图复制中使用快速刷新时记录主体源数据对象操作日志的表。

  同步复制和异步复制就不解释了,本例采用每天一次的异步复制。

  进行复制

  配置好本地服务名分别为:上海站点:SH,杭州站点:HZ,武汉站点:WH,北京站点:BJ,进入没有登录的sqlplus,让我们开始复制!

  一.设置主站点

  这里以上海主站点设置为例。

  1.连接主站点,创建复制管理员并授予相应的权限,复制管理员是管理整个复制环境并创建复制对象的用户。只有数据管理员可以建立主体组和实体化视图组。

  connect system/passwd@SH

  create user repadmin identified by repadmin;

  begin

  dbms_repcat_admin.grant_admin_any_schema(

  username=>’repadmin’);

  end;

  /

  grant comment any table to REPADMIN;

  grant lock any table to REPADMIN;

  后面的两个grant语句使复制管理员可以为任何表建立实体化视图日志。如果想改用户可以使用视图管理器,还需要下面的命令:

  grant select any dictionary to REPADMIN;

  2.注册传播方,传播方会将主体站点的延迟事务队列推入其他主体站点或者实体化视图站点。

  begin

  dbms_defer_sys.reGISter_purpagator(username=>’repadmin’);

  end;

  3.调度清除作业,该作业会定时清除延迟事务队列并用传播方将延迟事务推入其他主体站点或者实体化视图站点。先更换用户:

  disconnect;

  connect repadmin/repadmin@SH;

  begin

  dbms_defer_sys.schedule_purge(

  next_date=>sysdate,interval=>’sysdate + 1’,delay_seconds=>0);

  end;

  next_date:下一次执行日期,sysdate表示立即。

  interval:间隔时段,sysdate + 1表示间隔一天,sysdate+ 1/24表示间隔一小时

  delay_seconds:当延迟队列没有延迟事件时停止被次清除操作的延迟时间。

  4.为实体化视图站点建立复制代理。创建复制代理用户并授予视图接受方权限。复制代理是复制接收方连接主体站点的用户

  disconnect;

  connect system/passwd@SH;

  create user proxy_bjoracle identified by proxy_bjoracle;

  begin

  dbms_repcat_admin.register_user_repgroup(

  user_name=>’proxy_bjoracle,

  privilege_type => ’proxy_snapadmin’,list_of_gnames => NULL);

  end;

  /

  grant select_catalog_role to proxy_bjoracle;

  5. 创建主体组。

  disconnect;

  connect repadmin/repadmin@SH;

  begin

  dbms_repcat.create_master_repgroup(gname=>’sh_rep’);

  end;

  /

  6. 向主体组中添加复制对象

  a) 添加表:

  begin

  dbms_repcat.create_master_repobject(

  gname=>’sh_rep’,

  type=>’TABLE’,

  oname=>’ CREDIT_CARD’

  sname=>’SHORACL’

  use_existing_object=>TRUE,

  copy_rows=>TRUE);

  end;

  b) 添加索引

  begin

  dbms_repcat.create_master_repobject(

  gname=>’sh_rep’,

  type=>’INDEX’,

  oname=>’ INDEX_CREDIT_CARD’

  sname=>’SHORACL’

  use_existing_object=>TRUE,

  copy_rows=>FALSE);

  end;

  /

  7. 如果添加的表没有主键需要设置可以代替主键的列或者列的集合

  begin

  dbms_repcat.set_columns(

  sname => ’SHORACL’,

  oname => ’ CREDIT_CARD ’,

  column_list => ’ CREDIT_CARD_ID’);

  end;

  /

  8. 在主体组中的数据对象可以被复制之前,必须为他们生成复制支持。该方法为复制创建必要的触发器、包或者存储过程:

  begin

  dbms_repcat.generate_replication_support(

  sname=>’SHORACL’,

  oname=>’ CREDIT_CARD’,

  type=>’TABLE’,

  min_communication=>TRUE);

  end;

  /

  9. 为快速刷新创建实体化视图日志:

  create materialized view log on SHORACL. CREDIT_CARD;

  如果是没有主键的表示用一下语句:

  create materialized view log on SHORACL. CREDIT_CARD with

  rowid excluding new values;

  10.启动复制:

  begin

  dbms_repcat.resume_master_activity(

  name=>’sh_rep’);

  end;

  /

  二.设置实体化视图站点

  1.创建复制管理员并授予相应的权限:

  disconnect;

  connect system/passwd@BJ;

  create user mvadmin identified by

  mvadmin;

  begin

  dbms_repcat_admin.

  grant_admin_any_schema(username=> ’mvadmin’);

  end;

  /

  grant comment any table to mvadmin;

  grant lock any table to mvadmin;

  grant select any dictionary to mvadmin;

  2.注册传播方:

  begin

  dbms_defer_sys.register_propagator(

  username => ’mvadmin’);

  end;

  /

  3.公共数据库连接。需要每个复制需要创建三个数据库连接。公共数据库连接指定数据库的全局名称:

  create public database link ORACLSH using ’oracle.shanghai.

  com’;

  Using子句后跟的是全局数据库名或者是连接字符串。

  create public database link ORACLSH using ’(description=

  (address=(protocol=tcp)(host=127.0.0.1)(port=1521))

  (connect_data=(service_name=oracl)))’

  4.建立清除延迟事务队列调度作业:

  disconnect;

  connect mvadmin/mvadmin@BJ;

  begin

  dbms_defer_sys.schedule_purge(

  next_date => sysdate,

  interval => ’/*1:hr*/ sysdate + 1’,

  delay_seconds => 0,

  rollback_segment => ’’);

  end;

  5.建立复制管理员mvadmin的数据库连接:

  create database link ORACLSH connect to proxy_bjoracle

  identified by proxy_bjoralce

  Connect to … Identified by …子句指明用什么用户连接远程数据库

  6.建立复制调度数据库连接作业:

  begin

  dbms_defer_sys.schedule_push(

  destination => ’ora92zjk’,interval => ’/*1:hr*/ sysdate + 1’,

  next_date => sysdate,stop_on_error => false,

  delay_seconds => 0,parallelism => 0);

  end;

  /

  7.授予SHORACL用户(对应SHORACL方案)

  相应的权限建立实体化视图:

  disconnect;

  connect system/passwd@BJ;

  grant alter session to crm;

  grant create cluster to crm;

  grant create database link to crm;

  grant create sequence to crm;

  grant create session to crm;

  grant create synonym to crm;

  grant create table to crm;

  grant create view to crm;

  grant create procedure to crm;

  grant create trigger to crm;

  grant unlimited tablespace to crm;

  grant create type to crm;

  grant create any snapshot to crm;

  grant alter any snapshot to crm;

  8.建立复制方案的数据库连接:

  disconnect;

  connect SHORACL/SHORACL@BJ;

  create database link ORACLSH connect to ORACL identified

  by ORACL;

  复制方案的数据库连接和复制管理员的数据库连接要和system用户间里的对应公共数据库连接使用相同的名字,在调度连接时将使用公共数据库连接中指定的数据库全局名或者连接字符串。

  9.建立实体化视图:

  disconnect;

  connect mvadmin/mvadmin@BJ;

  create materialized view SHORACL.CREDIT_CARD refresh fast

  wit h pr imar y key as sele ct * from ORA CL.

  CREDIT_CARD@ORACLSH;

  @后面是数据库连接名。如果该表没有主键则使用rowid来刷新

  create materialized view SHORACL. CREDIT_CARD refresh

  fast with rowid as select * from ORACL. CREDIT_CARD@ORACLSH;

  10.为多个视图建立刷新组:

  begin

  dbms_refresh.make (

  name => ’mvadmin.sh_refresh’,list => ’’,

  next_date => sysdate,interval => ’sysdate + 1’,

  implicit_destroy => false,rollback_seg => ’’,

  push_deferred_rpc => true,refresh_after_errors => false);

  end;

  11.向刷新组中添加复制对象:

  begin

  dbms_refresh.add (name => ’mvadmin.sh_refresh’,list => ’SHORACL.CREDIT_CARD ’,lax => true);

  end;

  /

  三.检查复制进程

  1.查看sys.dba_jobs视图是否生成了足够的作业。

  经过以上的步骤应该有三个作业分别是清除作业、调度作业和刷新作业,查看视图的what字段是否有下面的内容:

  a) declare rc binary_integer; begin rc := sys.dbms_defer_sys.

  purge( delay_seconds=>0); end;

  b) declare rc binary_integer; begin rc := sys.dbms_defer_sys.

  push(destination=>’ORACLSH’, stop_on_error=>FALSE,

  delay_seconds=>0, parallelism=>0); end;

  c) dbms_refresh.refresh(’”MVADMIN”.”SH_REFRESH”’);

  如果排除其它系统作业本例中杭州和武汉的数据库复制建立之后将会有7个作业(如果为每个复制分别建立刷新组的话),清除作业始终只有一个。每个复制对应一个调度作业,每个刷新组对应一个刷新作业。

  2.查看job_queue_processes参数,确保该参数不为零(数据库的默认值是零),如果该参数为零,除非每次手工执行刷新,否则系统不会自动刷新复制数据。

  3.确保复制执行之后,观察sys.dba_jobs视图的failures字段。如果复制在刷新过程中除错,Oracle会自动在1分钟之后再次尝试刷新,失败之后再在2分钟、4分钟、8分钟..之后尝试刷新,直到失败次数达到16次或者间隔时间超过作业设置的间隔时间,该作业将被标记为中断,Oracle不再执行该作业。要重新执行改作业使用dbms_job包的run过程:

  begin

  dbms_job.run(job_no);

  end;

  /

  job_no 是sys.dba_jobs 的Job字段的值,作业号。

  在重新执行因出错而中断的作业前,需要手工找到出错点,并更正。

  总结

  本文只是使用了Oracle高级复制中最简单的功能,Oracle的高级复制还提供可更新视图和复杂的只读实体化视图复制,当然并不是我们都要去用高级复杂强大的功能,在具体应用的时候,还要根据系统功能和性能需求,选择适当的复制技术。

MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring

March 4th, 2009 1 comment

 

主题: MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring
  文档 ID: 258252.1 类型: BULLETIN
  Modified Date: 28-JAN-2009 状态: PUBLISHED

PURPOSE
-------

The purpose of this article is to provide an understanding of the locking and
performance issue related to materialized view refreshes.  How a refresh
process can impact and be impacted by other refresh processes and user
processes is also discussed.  This article also provides some basic queries
that can be used to monitor materialized view refreshes.

SCOPE & APPLICATION
-------------------

This article is intended for DBA's and support analysts who are familiar with
distributed materialized view concepts.  Note 258021.1 How to monitor the
progress of a materialized view refresh can be reviewed along with this article
to gain a full understanding of the materialized view refresh process.

The information in this article is specific to distributed materialized views.
While much of the information is applicable to local materialized views, there
may be differences in locking and performance expectations for local
materialized views.  For information specific to local materialized view
refreshes, please see the Oracle Data Warehousing Manual.

From this point on, the term 'mview' will be used in place of 'materialized
view'.

'Materialized view' and 'Snapshot' are synonymous as of 8i.

CONTENTS
========

1. Refresh Locking
   1.1 Locks Taken out by a Refresh Operation
   1.2 Refresh Lock Duration
2. Refresh Performance
   2.1 SQL Tuning for Refreshes
       2.1.1 Tune the Mview Defining Query
       2.1.2 Indexes and Refreshes
   2.2 Avoid Locking Issues during Refreshes
   2.3 System Performance Impact on Refreshes
       2.3.1 Network Performance
       2.3.2 I/O Performance
   2.4 Rollback Segments (RBSs) and Refreshes
   2.5 Mview log Maintenance
       2.5.1 Modifications to the Mview Log
       2.5.2 Orphaned Entries in the Mview Log
   2.6 Updateable Refresh Optimization
3. Monitoring Refreshes

1. Refresh Locking
==================

When an mview refresh operation takes place, the following happens:

   1. SYS.SNAP$ and SYS.MLOG$ are updated to show the time of refresh.

   2. If a complete refresh on only one mview is performed using the
      DBMS_MVIEW.REFRESH() api, the mview base table is truncated.

      If the refresh is fast, or involves multiple mviews, or is being
      performed on a refresh group, rows in the mview base table(s) are deleted.

   3. All rows selected from the master table are inserted into the snapshot
      base table.

   4. SYS.SLOG$ is updated with the time of refresh.

   In the case of a fast refresh, an additional step is performed:

   5. Rows which are no longer needed for a refresh by any mview are
      deleted from the mview log - <schema>.MLOG$_<table>.

   Note: For more information on refresh steps, please see Note 258021.1
         How to monitor the progress of a materialized view refresh

The Oracle read consistent mechanism is used to select a consistent set of
rows from the MASTER table, therefore it is NOT necessary to take out any
locks against the MASTER table.

   NOTE: In Oracle 8i locks on the master table were added to support query
         rewrite functionality on mviews. This has resulted in deadlocks
         during fast refreshes and is addressed in Bug 1376209.

         Should you experience this during a fast refresh and you are NOT
         using query rewrite, you can set an event to circumvent this.
         Please contact Oracle support for more information on this event.

1.1 Locks Taken out by a Refresh Operation
------------------------------------------
To facilitate the dml activity required by a refresh, locks on the relevant
rows in the following tables will be required:

   At the master site:
      Associated rows in
         SYS.MLOG$
         SYS.SLOG$
      for the snapshot being refreshed are locked.

      for fast refresh:
         <schema>.MLOG$_<table> (base table for the mview log)

         In 8i+ to support query rewrite feature if the above mentioned
         event is not set:
            Master table (shared, then exclusive locks are taken at different
                          points during the refresh)

   At the Snapshot site:
          SYS.SNAP$
          SYS.SNAP_REFTIME$
          The base table for the mview (exclusive table lock is taken)

             In pre 8.1 compatible instances, the base table name is
                    <schema>.SNAP$_<mview_NAME>.
             In 8.1+ compatible instances, the base table name is
                    <schema>.<mview_name>

      If an updateable mview:
         <schema>.USLOG$_<mview_NAME> (exclusive table lock is taken)

      If a refresh group is used:
         SYS.RGROUP$

Locking the base table of read-only mviews should not have any impact on the
users as the mviews will not have DML performed against them.  However, with
updateable mviews, it is necessary to temporarily prevent user changes during a
refresh.  The mview base table and the updateable mview log will be locked
exclusively during a refresh to accomplish this.  It is possible to see a
temporary hang on mview updates while the updateable mview is being refreshed.

As a result of the refresh locking behavior, user applications may encounter
waits and possibly time-outs if they are accessing mviews that are being
refreshed.  The reverse is also true; mview refreshes may encounter waits and
time-outs if a user application holds locks that the refresh operation needs.

1.2 Refresh Lock Duration
-------------------------
The locks taken out by the refresh are held for the duration of the refresh
transaction.

The refresh transaction duration is dictated by the atomic_refresh parameter
in the dbms_mview.refresh() api.  The default value for atomic_refresh is TRUE.

If atomic_refresh is set to TRUE, all of the mviews listed in the list/tab
parameter will be refreshed in one single transaction.  The larger the number
of mviews in the list, and the larger the amount of data that must be
transferred, will directly impact the length of time the refresh holds the
locks.  This could cause other refreshes to wait, and possibly time-out if they
must access the same rows.  This can be the case when multiple mviews mastered
by the same table are refreshed concurrently.

If atomic_refresh is set to FALSE, each mview listed in the list/tab parameter
will be refreshed in a single transaction.  Thus the locks will be released
after each mview is refreshed.  If another session takes the lock before the
next mview in the refresh list begins refreshing, then that refresh must wait
for the session to release the lock.  This could result in slower than expected
performance or possibly a time-out.

    Note: For more information on atomic_refresh, please see the Oracle
          Replication API manual.

It should be noted that using refresh groups does not allow for atomic_refresh
to be set to FALSE as dbms_refresh.refresh and dbms_refresh.make do not supply
the atomic_refresh parameter.  Therefore, all members of the refresh group will
be refreshed in a single transaction.

2. Refresh Performance
======================
The performance of a refresh operation is affected by a number of factors.
The most common being SQL tuning, locking, system performance, hardware,
and the amount of data being moved as part of the refresh.  Each situation
will vary as they depend on the factors listed.  Below are the considerations
a DBA should be aware of when attempting to improve refresh performance.

2.1 SQL Tuning for Refreshes
----------------------------
The Refresh operation is comprised of a series of selects, updates, inserts
and deletes on various tables.  The performance of these dml operations can
be improved with the same SQL tuning methods used for user dml operations.
The following explains what you can do at different levels to help optimize
refresh dml.

2.1.1 Tune the Mview Defining Query
-----------------------------------
The refresh of an mview will never be run faster than it's defining query runs
on the master site.  In fact, it can be expected to run slightly slower due to
additional operations that must take place along with running the defining
query, and the additional transfer of data required.

The first step to optimizing refresh performance is to tune the defining query
for a distributed environment. Information on the methods used to do this can
be found in the Oracle Tuning Manual under 'Tuning Distributed Queries'.

2.1.2 Indexes and Refreshes
---------------------------
One tool used to improve SQL performance is the INDEX. Users should be aware of
the positive and negative impacts of indexes on refresh performance.

MVIEW Indexes
-------------
When mviews are created, certain indexes are also created at the mview site to
assist in data retrieval for refresh operations.  Users can add additional
indexes to the mview base table to further optimized data retrieval from the
mivew.  However these indexes can have a negative impact on refresh performance.
As mentioned in section 1, the refresh will remove and replace rows in the
mview base table during refresh, and thus must also maintain any indexes on the
mview base table.  More indexes require more work for the refresh, which
requires more time. Additionally, restrictive indexes (unique, constraint) can
interfere with the refresh operation and keep it from completing successfully;
especially if those restrictions are not enforced on the master data.

MVIEW LOG Indexes
-----------------
The creation of an mview log does not include the creation of indexes on the
mview log base table.  This means that every fast refresh that accesses the
mview log must do a full table scan.  This can be costly to performance if
the mview log is large.  It has proven beneficial for users to create indexes
on the mview log base table to improve the dml operations performed on it by
the fast refresh operation.  Adding an index to the pk column in mlog$_<table>,
and an index on snaptime$$ in mlog$_<table> will avoid full table scans on
mlog$_<table> during fast refresh.  The impact of these indexes is positive for
the fast refresh option, but may have a negative performance impact on heavy
loads to the master table as the indexes must be updated for every row updated
in the master table, and subsequently, the mview log base table.  However, the
gains can significantly out-weigh the loses in most cases.

2.1.3 MVIEW Log Storage Parameters
----------------------------------

If the MVIEW will be refreshed via complete refresh then create it using
PCTFREE set to 0 and PCTUSED set to 99.

Avoid using CLOB columns to store chars less than 4K, which can be represented
using VARCHAR2. During the refresh, for each LOB row there is one network
roundtrip. For the case where LOBs are not involved more than one record is
transmitted in one network package, depending on the rowsize.

Set the PCTFREE parameter to 0 for a log on a master table that is used by only
one materialized view. Set the PCTFREE parameter higher for a log on a master
table that has multiple materialized views, to prevent row chaining within the
log table. Remember, the log is updated each time there is a change to a master
table which will be used during the next refresh operation. This update
activity requires some PCTFREE block space.

In addition, it may improve I/O performance to store the materialized view log
in a different tablespace from the master table, on a different disk drive
accessed by a different I/O channel.

2.2 Avoid Locking Issues during Refreshes
-----------------------------------------
The impact of multiple concurrent mview refreshes on the same master table
can significantly impact the performance of refreshes due to the locking
issues discussed in section 1.  You can avoid slow refresh performance or
time-outs due to locking issues by staggering refreshes to the same master
site, and/or the same master tables.

Additionally, avoid bulk updates to the master tables while a refresh is being
performed on mviews mastered by those tables.  If a bulk update is started
while a refresh is active, the refresh must retrieve the undo generated by the
update in order to maintain transactional consistency.  This can significantly
increase the work done by the refresh which in turn increases the time needed.
It could also result in a failed refresh due to ora-1555.

If a refresh is in progress, users can expect to see a slow down or
interruption in data retrieval/changes on mviews.  Data is deleted or truncated
from the  mview base table during refresh depending on the conditions covered
in step 2 of the refresh operation in section 1, and the mview is locked.  If
the data has not been truncated, selects can be performed on the mview during
refresh, but will result in the 'pre refresh' data as the row changes are
rolled back to the values just prior to the refresh.  This rollback can add
time to the retrieval performance. If the data in the mview was truncated, no
rows will be returned from the retrieval. User changes to updateable mviews
will be delayed if made while the mview is being refreshed.  This is due to
the exclusive lock on the mview base table.

2.3 System Performance Impact on Refreshes
------------------------------------------
The refresh operation will only be as fast as the system allows.

2.3.1 Network Performance
-------------------------
The speed and accessibility of the network connection used by the dblink to
transfer data has a direct impact on refresh performance.  If the network
is slow, the data transfer is slow, and thus the refresh is slow.

To avoid refresh performance problems due to network issues, make sure that
your network connection is tuned for optimum throughput and performance.
You can work with your network administrator to achieve this.

Busy networks can also have a negative impact refresh performance.  To avoid
this, consider refreshing large mviews during low traffic times. Additionally
stagger the interval of refresh groups to the same master site to reduce the
contention of multiple refresh operations for network resources.

2.3.2 I/O Performance
---------------------
If multiple refreshes are running concurrently and must read/write to the same
disk, I/O performance can have an impact on refresh performance.  While this
is not the largest contributing factor to poor refresh performance, it is worth
consideration in heavy data change environments.  If possible, consider disk
stripping with mviews belonging to different refresh groups on the mview site,
and the mview logs at the master site.  For more information on optimizing I/O,
please see the Oracle Performance Tuning Manual.

In many cases, there may be nothing that can be done to improve I/O due to
hardware and o/s limitations.  If this is the case, you can improve refresh
performance by staggering the interval of refresh groups with mviews that
reside on the same disk, so that only one refresh operation is utilizing I/O
at a given time.

2.4 Rollback Segments (RBSs) and Refreshes
------------------------------------------
Due to all the dml involved with a refresh operation, a significant amount
of undo is generated by a refresh.  To avoid performance issues and errors
during a refresh, consider the following:

  1) Make sure RBSs are large enough to handle the refresh

      Rule of thumb:
        Master site: RBS 5.5 times the size of the mview log
        Mview site:  RBS 2.5 times the size of the mview

  2) Avoid using 'optimal' on the master site RBSs as this could result in
     ora-1555 on long running refreshes.

     If optimal must be used:
       a) Stagger user updates and refreshes on the same master tables to
          avoid the 'optimal' shrinking the user update RBS before the
          refresh can access it.
       b) Reduce the number of mviews being refreshed in a refresh operation
          to reduce the time needed to complete the refresh.
       c) Reduce the interval of the refresh operation for fast refreshes to
          reduce the amount of data needed to be retrieved and time needed
          to complete each refresh. You should not reduce the interval lower
          than the time it takes for the refresh to complete.  Also keep in
          mind intervals of other refresh groups for staggering purposes.

  Note:  For more information on ORA-1555 please see
         Note 18954.1OERR:  ORA 1555  "snapshot too old (rollback segment
                              too small)"

2.5 Mview log Maintenance
-------------------------
The 'health' of the mview log for the master table can have an impact on
refreshes.

2.5.1 Modifications to the Mview Log
------------------------------------
If the mview log has been created, recreated, or altered since the last refresh
or creation of an mview that would use it for a fast refresh, it can not be
used until that mview has performed a complete refresh.  This is to ensure that
the next refresh will pick up all changes made to the master table since the
mview last refreshed.  If the mview log has changed since that last refresh,
we can not guarantee that it contains all the required changes.  Therefore, a
complete refresh is necessary.  For an illustration of this concept, please
see Note 216279.1 Effect of "alter snapshot log" on fast refresh capability.  The
DBMS_MVIEW api package provides procedures that will allow maintenance the
mview log and still maintain 'fast refreshability'. For more information on
the DBMS_MVIEW package, please see the Oracle Replication API Manual, and the
Oracle Supplied PL/SQL Packages Manual.

2.5.2 Orphaned Entries in the Mview Log
---------------------------------------
Another 'health' consideration for mview logs is the actual rows it contains.
A healthy mview log will only contain rows that are needed by mviews that
still need to pick up those changes on their next refresh. If a row is not
needed, it is purged from the mview log to reduce the amount of data.
However, it is possible for the mview log to believe that rows are still
needed by an mview, when that mview no longer exists.  This is because the
mview was not properly unregistered from the master site when it was dropped
or lost.  The result is that the mview log continues to collect data but does
not delete it, so each subsequent refresh must traverse more and more data.
This will eventually impact the performance of the refresh operations.  To
fix this, the lost or 'orphaned' mview must be unregistered at the master
site manually.  For information on how to do this, please see Note 1031924.6
SNAPSHOT LOGS GROWS DUE TO MISSING/INVALID SNAPSHOT

2.6 Updateable Refresh Optimization
-----------------------------------
Because updateable mviews are locked during refresh, it is desirable to reduce
the refresh time required as much as possible.  When an updateable mview is
refreshed, it must first push it's data changes to the master site, then pull
the master table data down.  This is actually done in two phases, the push and
pull phase (for more information on these phases, please see Note 258021.1
How to monitor the progress of a materialized view refresh).  If there are a
number of changes at the mview site, this can take a considerable amount of
time.  To reduce the amount of work that the refresh must complete, you can
schedule a separate job  to send the changes to the master site without a
refresh.  This job would be the advanced replication 'push', the same used for
master-to-master replication. This push will send the changes to the master
site without locking the updateable mview that generated them.  Then, when a
refresh is started, the push phase of the refresh will be considerably reduced
as most of the changes have already been pushed to the master table.  This in
turn reduces the amount of time needed to perform the refresh, and the amount
of time the updateable mview is locked.  The pull phase is not affected by the
extra push job.  It will still need to pull down all changes made by it's own
push and those made by the additional push job, since the last refresh.

To schedule an additional push job for updateable mviews, use the
DBMS_DEFER_SYS.SCHEDULE_PUSH() api.  For more information on this api, please
see the Oracle Replication API Manual.

Another consideration for updateable mviews is to refresh each in it's
own refresh group.  If the mview is the only member of the group, the refresh
transaction will be completed when the mview has finished it's refresh.  This
is due to the transaction duration of refresh groups.  The more members, the
longer the refresh transaction, the longer the mview base tables are locked.

3. Monitoring Refreshes
=======================
The following queries can be used to monitor mview refreshes.

When an mview was last successfully refreshed
-----------------------------------------------
The following query will indicate when an mview was last successfully
refreshed and what type of refresh was done.

   column last_refresh_type format a18
   column owner format a7
   column mview_name format a12
   select owner, mview_name, last_refresh_type, last_refresh_date
   from dba_mviews;

  OWNER   MVIEW_NAME   LAST_REFRESH_TYPE  LAST_REFRESH_DATE
  ------- ------------ ------------------ ------------------
  SCOTT   CREF_DEPT    COMPLETE           24-JAN-03 22:17:25
  SCOTT   MYDEPT       FAST               23-JAN-03 15:44:24
  SCOTT   MY_EMP       FAST               27-JAN-03 21:40:13
  SCOTT   UPSNP_DEPT   COMPLETE           24-JAN-03 14:35:37

Status of a Refresh Group Using the Job Queue
---------------------------------------------
The following query shows all the jobs scheduled in the job queue to perform
refreshes.  It shows when the last refresh was run, the total amount of time
spent by the system running the job since the instance started, if the job is
broken or has failures, and when the next refresh will run.

   column broken format a6
   alter session set nls_date_format='DD-MON-YY hh24:MI:SS';

   select job, last_date last_refresh,
   next_date next_refresh, total_time,
   broken, failures, what
   from dba_jobs
   where what like '%dbms_refresh%';

    JOB LAST_REFRESH       NEXT_REFRESH       TOTAL_TIME BROKEN   FAILURES
  ----- ------------------ ------------------ ---------- ------ ----------
  WHAT
  ------------------------------------------------------------------------
      1 05-FEB-03 16:37:57 05-FEB-03 16:47:57          2 N               0
  dbms_refresh.refresh('"SYS"."REFRESHG1"');

Note: TOTAL_TIME - you can determine how long (in seconds) a refresh job takes
      to run by querying total_time for the job before and after the job runs,
      and calculating the difference.  This is helpful in determining what
      interval to use to stager refreshes.

Refreshes Currently Running
--------------------------------
The following query shows all refresh jobs that are currently running, when
they started, and if any have failed.

   select r.job, r.this_date, r.failures
   from all_jobs_running r, all_jobs j
   where j.job = r.job
   and j.what like '%dbms_refresh%';

        JOB  THIS_DATE            FAILURES
  ---------- ------------------ ----------
           1 05-FEB-03 16:37:57          0

If an mview belongs to a refresh group, you can also use the ALL_REFRESH
and ALL_REFRESH_CHILDREN to obtain information about the mview refresh

   select r.rowner, r.rname, r.job, c.name,
   r.next_date next_refresh, r.broken
   from all_refresh r, all_refresh_children c
   where r.job = c.job;

  ROWNER     RNAME             JOB NAME       NEXT_REFRESH       BROKEN
  ---------- --------------- ----- ---------- ------------------ ------
  SYS        REFRESHG1           1 MYDEPT     05-FEB-03 16:47:57 N

Last refresh times can also be seen at the master site.  To do this, please
see  Note 114743.1 How to Check the Last Refresh Time of Snapshots from
                     the Master Site

New 9i Refresh Views and Procedure
----------------------------------
9i introduces two new views that can be used to monitor refreshes:
   V$REPLPROP - monitor the push phase of an updateable refresh
                and any scheduled push jobs

   V$MVREFRESH - monitor the pull phase of an updateable refresh or
                 a read-only refresh

9i also introduces the DBMS_MVIEW.EXPLAIN_MVIEW procedure to determine
refresh capabilities (fast vs complete) for an mview.  For more information
on this procedure, please see the Oracle 9i Replication API Manual, and the
Oracle 9i Supplied PL/SQL Packages Manual

RELATED DOCUMENTS
-----------------

For more Information on monitoring refreshes, please reference
Note 258021.1 How to monitor the progress of a materialized view
                refresh

The test case information used for this bulletin can be found in
Note 258258.1 Monitoring Locks During Materialized View Refreshes

Additional References
========================
Oracle Replication Manual
Oracle Replication API Manual
Oracle Tuning Manual

最简单的创建复制环境中只读实体化视图站点的方法

March 4th, 2009 No comments

目的:创建一个实体化视图站点,定时刷新,获取主站点中指定表的变化,这个实体化站点可以作为查询服务器使用

步骤:

1。主站点上创建实体化视图日志表

CREATE MATERIALIZED VIEW LOG ON kamus.account2004;

2。实体化视图站点上创建公用数据库链接

conn system/password

CREATE PUBLIC DATABASE LINK orcl using ‘ORCL’;

3。实体化视图站点上创建刷新组,本例中3分钟刷新一次

conn system/password

BEGIN
   DBMS_REFRESH.MAKE (
      name => ‘kamus.test_repg’,
      list => ”,
      next_date => SYSDATE,
      interval => ‘SYSDATE + 3/(24*60)’,
      implicit_destroy => FALSE,
      rollback_seg => ”,
      push_deferred_rpc => TRUE,
      refresh_after_errors => FALSE);
END;
/
COMMIT;
注意:此处必须commit,否则创建的刷新组并没有真正生效。

4。实体化视图站点上创建用户私有数据库链接

conn kamus/password

CREATE DATABASE LINK orcl CONNECT TO kamus IDENTIFIED BY password;

5。实体化视图站点上创建实体化视图

conn kamus/password

CREATE MATERIALIZED VIEW KAMUS.ACCOUNT2004 REFRESH FAST WITH PRIMARY KEY  AS SELECT * FROM   KAMUS.ACCOUNT2004@orcl;

6。实体化视图站点上将创建的视图加入刷新组

conn kamus/password

exec DBMS_REFRESH.ADD(name => ‘kamus.TEST_REPG’, list => ‘kamus.ACCOUNT2004′, lax => TRUE);

7。测试,在主站点中更新ACCOUNT2004表,过3分钟检查实体化试图站点中的视图,发现更新已经复制成功。

以上为建立只读实体化视图站点的最简单方法:

不需要创建其它的任何用户,比如复制环境中需要的传播者,刷新者,接收者等等

不需要创建任何复制组

不需要生成任何复制对象,不需要生成任何对象的复制支持

创建实体化视图的几个注意点

March 4th, 2009 No comments

1.如果要创建基表是其它用户表的实体化视图,那么需要给实体化视图的owner赋予以下权限:
grant CREATE ANY MATERIALIZED VIEW to username;
grant SELECT ANY TABLE to username;
如果要创建refresh on commit的视图,那么还需要下面这个权限:
grant ON COMMIT REFRESH to username;

2.创建refresh on commit的语法如下,此类实体化视图在基表的事务commit之后,就会立刻刷新
CREATE MATERIALIZED VIEW MV_T1
REFRESH FAST ON COMMIT WITH PRIMARY KEY AS SELECT * FROM kamus.t1;

3.如果不指定on commit,那么默认是on demand,只有手工调用DBMS_MVIEW包中的刷新过程,实体化视图才会被刷新

4.指定了start with … next …选项之后,第一次创建会有作一次完整刷新,然后在指定的时间间隔之后会定时刷新,本例中刷新间隔为1分钟。
语法如下:
CREATE MATERIALIZED VIEW MV_T1
REFRESH FAST START WITH SYSDATE NEXT sysdate+1/24/60 WITH PRIMARY KEY AS SELECT * FROM kamus.t1;
检查USER_REFRESH视图和USER_JOBS视图,我们可以发现start with… next …语法也就是Oracle自动创建了一个刷新组,这个刷新组的名称跟实体化视图名称相同,并且IMPLICIT_DESTROY属性为Y,表示只要该组中的实体化视图删除该组也自动被删除。同时,创建了一个JOB,JOB中的waht属性是dbms_refresh.refresh(‘”SCOTT”.”MV_T1″‘);
自然,由于自动刷新是通过JOB完成的,那么初始化参数job_queue_processes必须大于0,这样JOB才会正常运行。

Troubleshooting Basics for Advanced Replication

March 4th, 2009 No comments

 

主题: Troubleshooting Basics for Advanced Replication
  文档 ID: 122039.1 类型: TROUBLESHOOTING
  Modified Date: 22-SEP-2008 状态: PUBLISHED

PURPOSE
-------

The purpose of this article is to provide basic steps for troubleshooting
Advanced Replication setup, configuration and operation. Replication DBA's
can use this article as a starting point for diagnosing and resolving the most
common problems encountered in replicated environments. Additional notes are
referenced through out this article that address specific issues or provide
additional information on a particular component used by Advanced Replication.

SCOPE & APPLICATION
-------------------

To be used by Oracle support analysts and replication DBA's to understand and
employ basic troubleshooting techniques for Advanced Replication configuration
and components.

Troubleshooting Basics for Advanced Replication
===============================================

Article Contents
----------------

1. TROUBLESHOOTING SETUP/CONFIGURATION

  1.1 Packages
  1.2 Users and privileges
  1.3 Database links

2. TROUBLESHOOTING OPERATION
2.1 Deferred Transactions

  2.1.1 Deferred transactions Not being Created for Asynchronous Propagation
  2.1.2 Deferred transactions created but not propagated
  2.1.3 Deferred transactions propagated but not applied to receiving site
  2.1.4 Deferred transactions propagated and no longer in the Deftran
        view but def$_aqcall table at local site still has entries.

2.2 Administrative Requests
  2.2.1 Admin requests generated but not propagated
  2.2.2 Admin requests propagated but not applied at receiving site

2.3 Master Repgroup State
  2.3.1 Stuck in 'Quiescing' mode when suspend_master_activity command issued
  2.3.2 Group still quiesced after resume_master_activity command issued

2.4 Automated job not running

2.5 Snapshot refreshes
  2.5.1 Snapshot refresh looses snapshot changes
  2.5.2 Snapshot can not fast refresh

3. ISSUES WITH REPLICATION MANAGER SETUP WIZARD

-------------------------------------------------------------------------------

Sections:

1. TROUBLESHOOTING SETUP/CONFIGURATION
======================================

A large majority of the problems encountered by replication users are due to
incorrect setup and configuration of the replicated environment. The following
components must be correctly in place before replication can work properly.

1.1 Packages
------------

CATREP.SQL is the script that creates the necessary data dictionary entries
and API packages that enable the Advanced Replication feature. From Oracle9
onwards this script is automatically run from CATPROC.SQL.

The packages created by CATREP.SQL often have dependencies on objects created
by CATALOG.SQL and CATPROC.SQL, so it important that these have been run with
out error before CATREP.SQL is run.

Some things to check to make sure CATREP.SQL runs successfully:

1) Ensure CATALOG.SQL and CATPROC.SQL have been successfully run on the
   database as SYS (never SYSTEM) from svrmgrl or from Oracle9 onwards
   sqlplus.

2) Make sure all objects in the SYS and SYSTEM schema are valid - see
   Note 73995.1 for more information on troubleshooting invalid objects
   in the database.

3) CATREP.SQL tends to require a significant amount of rollback space so make
   sure a large rollback segment is available and set transaction to that
   rollback segment.

4) Prior to Oracle9 there were problems running CATREP.SQL from sqlplus (see
   Bug 895928 and Note 116118.1 - there are still issues with this in
   8.1.6). So make sure CATREP.SQL is run as SYS (never SYSTEM) from svrmgrl
   (up to release 8.1.7, then in sqlplus from Oracle9 onwards). Make sure the
   output is spooled to a file, so the log file can be used for troubleshooting
   if needed.

If you have migrated or upgraded the database from a previous version, refer
to Note 91900.1 and Note 118416.1 for troubleshooting invalid packages
after migration.

1.2 Users and privileges
------------------------

Special, privileged users are needed with replication to ensure cohesive and
controlled administration of replicated objects, data and the environment.

  Master site

      Replication Administrator - usually REPADMIN (administers the replicated
      objects and environment). Required privileges granted with:

          dbms_repcat_admin.grant_admin_any_schema() - Global Repadmin
          dbms_repcat_admin.grant_admin_schema() - Schema only Repadmin

      Propagator - usually REPADMIN (pushes DML changes to other sites)
      Required privileges granted with:

          dbms_defer_sys.register_propagator() - Global

          Note:  You can only have one propagator registered for a database.
          Also grant: Comment any table, Lock any table

      Receiver - defaults to propagator, usually REPADMIN (receives DML
      changes from other sites). Required privileges granted with (if not
      propagator):

          dbms_repcat_admin.register_user_repgroup() - Repgroup level Receiver

          Note: use privilege_type => 'RECEIVER'

  Updateable Snapshot Site

      Snapshot Administrator - usually SNAPADMIN or REPADMIN (administers the
      replicated objects and environment). Required privileges granted with:

          dbms_repcat_admin.grant_admin_any_schema() - Global Admin
          dbms_repcat_admin.grant_admin_schema() - Schema only Admin

      Propagator - usually SNAPADMIN or REPADMIN (pushes DML changes to master
      sites). Required privileges granted with:

          dbms_defer_sys.register_propagator() - Global

          Note:  You can only have one propagator registered for a database.
          Also grant: Comment any table, Lock any table

      Refresher - defaults to propagator, usually SNAPADMIN or REPADMIN (pulls
      data from master sites on refresh). Required privileges if not
      propagator:

          Create Session
          Alter Any Snapshot
          Comment any table
          Lock any table

See setup note for user creation and required privileges in:

       Oracle 8.1 onwards - Note 117434.1

1.3 Database links
------------------

Replication relies solely on database links to transfer data from one site to
another.  If database links are not created correctly or are malfunctioning,
propagation will not take place.

Common things to look for when troubleshooting database link connection:

1) Make sure that the remote database to which the link attaches is started
   and available through the network.

2) Make sure that each replication user has a private database link with the
   same name as a public database link pointing to the remote database.

   See the following setup note for the required database link configuration:

       Oracle 8.1 onwards - Note 117434.1

3) Make sure the database links are created and work properly.

   See Note 121716.1 for troubleshooting database links in a
   replicated environment.

2. TROUBLESHOOTING OPERATION
============================

There are many mechanisms and components used throughout the propagation
operation of replication.  It is essential to understand the flow of
propagation.  Knowing 'how' a component does 'what', and 'when' it should do
it, will make troubleshooting the operations of a replicated environment more
manageable.

2.1 Deferred Transactions
-------------------------

2.1.1 Deferred transactions not being Created for Asynchronous Propagation
--------------------------------------------------------------------------

When changes are made to data in a replicated table, Oracle uses an internal
trigger ($RT) to create the deftran entry. The transaction must be successfully
committed at the local site before an entry is placed in deftran for other
master sites.

Check the following:

1) Make sure a 'commit' has been issued from the session making the change
   $RT is an 'on commit trigger' and will not fire until the change has been
   successfully committed to the table.

2) Make sure the 'commit' was successful. If the 'commit' for the transaction
   was not successful, $RT will not generate an entry in deftran. This avoids
   propagating a 'bad' transaction.

3) Check if there are any other master site members for the regroup to which
   the updated table belongs.

   When the $RT fires, it will check to see if there are other master sites
   to which the data change needs to be propagated. If no other master sites
   belong to the group, the $RT will not generate an entry in deftran.

   To check this run the following query as REPADMIN:

       select * from dba_repsites where gname = <repgroup>;

       gname = repgroup to which the updated table belongs

   If only one record is returned for the gname, there are no other master
   sites designated for the repgroup and thus, no need to generate an entry
   for deftran.

4) If the table update has been successfully committed and there are other
   master sites designated for the group, it is likely that the $RT trigger
   has become corrupted or destroyed. You will need to regenerate replication
   support for the object.

2.1.2 Deferred transactions created but not propagated
------------------------------------------------------

There are transactions in the deftrandest view but they are not being
propagated. The component that is responsible for send pending transactions
to their remote destinations is the 'push' job.

In Oracle 7.3 the 'push' operation was carried out by dbms_defer_sys.execute.
This 'push' could be automated with the dbms_defer_sys.schedule_execute
procedure. In Oracle 8.0+, the calls are to dbms_defer_sys.push (function),
and dbms_defer_sys.schedule_push (procedure), respectively.

When the push is called, all pending transactions in the deferred transaction
queue for the specified destination are sent. Once the existing deferred
transactions are successfully sent, the procedure is finished. Transactions
generated after the procedure is called will remain in the deferred
transaction queue until the procedure is called again for that destination.

The schedule_push procedure creates an automated job in the job queue
(dba_jobs) that will make a call to the dbms_defer_sys.execute/push. If
transactions are not being propagated, it could be due to problems with the
'push' process itself, or the automated job process. To determine where the
problem lies, you will want to separate the two processes and troubleshoot
them individually. First, make sure that the automated job is configured
properly. If so, then try running the push manually by calling the
dbms_defer_sys.execute/push API directly.

Please see Note 1035874.6 section 3 for troubleshooting the push job.

2.1.3 Deferred transactions propagated but not applied to receiving site
------------------------------------------------------------------------

The deferred transactions are propagating, however you do not see the changes
at the remote site after propagation. More than likely, the transaction failed
on the remote site. Check the deferror view at the remote site, taking note of
the error number and message for the deferror entry. You can also view failed
transactions with Replication Manager.

If needed, you can determine the row values and row operation for each call
by using show_call scripts that can be found on metalink:

    Note 2063747.4    V7.3 PRINTING DEFCALL FOR A SINGLE TRANSACTION
    Note 2065172.102  V8.0.X PRINTING A SINGLE CALL FOR A TRANSACTION
    Note 2103883.6    V8.0.X PRINTING DEFCALL FOR A SINGLE TRANSACTION
    (Notes Note 2065172.102 and Note 2103883.6 can also be used for 8i)

Or, you can use Replication Manger to display the information by drilling down
through transactions and calls in the 'Local Errors' folder.

This information can be used to identify the row at the local master site for
manual correction.

Please see Note 1035874.6 section 4 for more information on troubleshooting
failed deferred transactions.

2.1.4 Deferred transactions propagated and no longer in the Deftran view
      but def$_aqcall table at local site still has entries.
------------------------------------------------------------------------

In Oracle 7.3, the dbms_defer_sys.execute() package will automatically remove
the deferred transaction entry from the local def$_call when it has been
successfully sent to it's remote site. In Oracle 8.0+, this 'send and purge'
operation has been separated. Oracle 8.0+ replication relies on the 'purge'
job to clean propagated transactions from the local def$_aqcall table once
they have been successfully pushed to their remote site.

As with the 'push' job, the 'purge' job can be run manually with the function
dbms_defer_sys.purge(), or automated with the dbms_defer_sys.schedule.purge()
procedure. The automated job calls the dbms_defer_sys.purge() package. There
are two types of purges that can be done; a lazy purge (default), and a precise
purge.

The lazy purge will purge transactions with a cscn lower than the local low
water mark for propagated transactions (this is calculated based on the minimum
last_delivered in the local def$_destination). This low water mark can be lower
than some cscn numbers of some previously pushed transactions, so they will not
be purged immediately. This can happen if not all the push jobs have run and
still have active transactions.  In this case, the transactions will remain in
the def$_aqcall until the low water mark rises above the cscn for the
transaction.

A precise purge will purge transactions with a cscn lower than the low water
mark for propagated transactions to it's specific destination.  This means
that the purge will query the last_delivered for each dblink destination. All
transactions that have been pushed from the local site to that destination will
usually fall below the low water mark and be purged.

Users should NEVER manually remove entries from def$_aqcall unless directed by
an Oracle Support analyst.

Please see Note 1035874.6 section 4 for more information on troubleshooting
the purge operation.

2.2 Administrative Requests
---------------------------

2.2.1 Admin requests generated but not propagated
-------------------------------------------------

When you use the dbms_repcat package to administer a master repgroup or
repobject, you will see administrative requests generated in the dba_repcatlog
view, which is commonly referred to as the admin request queue.

When the administrative request (admin request) is first generated at the
master definition site, it will be in a 'READY' status. Execution of requests
on the queue depend on an automated job to apply them at the current site and
to propagate them to other master sites if needed. Most calls to dbms_repcat
at the master definition site will automatically run the local job when the
first set of admin requests generated by the call enter the queue, so you will
see some or all of the calls in the 'AWAITING CALLBACK' status almost
immediately at the master definition site. There are interdependencies between
some admin requests, so if one request errors, dependent requests will not run
until the error has been resolved.

To troubleshoot, please refer to the "Admin requests generated but not
propagated" section of Note 180014.1

2.2.2 Admin requests propagated but not applied at receiving site
-----------------------------------------------------------------

When an admin request is propagated to a remote master site, it is placed
in that master site's dba_repcatlog with a 'READY' status, and must wait for
the do_defer_repcat_admin job for the associated repgroup on that site to
run before it can be applied. When the job runs, the admin request status is
changed to 'DO CALLBACK' and remains so until the admin request has been
completed.

If the admin request is applied successfully, it will be removed from the
dba_repcatlog on both the master definition site and the master site. If the
admin request fails, it will be removed from the master site and marked with
status 'ERROR' at the master definition site.

Special note: If you loose the connection between the master definition site
and the master site, for what ever reason, while an admin request is in
AWAITING CALLBACK at the master definition site, the admin request becomes
corrupted and must be manually removed from both the master definition site
and the master site.

To troubleshoot, please refer to the "Admin requests propagated but not
applied at receiving site" section of Note 180014.1

2.3 Master Repgroup State
-------------------------

2.3.1 Stuck in 'Quiescing' mode when suspend_master_activity command issued
---------------------------------------------------------------------------

A master replication group, or repgroup, can be in one of three states:

    Normal, Quiescing or Quiesced

When you suspend activity of a repgroup, Oracle will attempt to push all
waiting transactions in deftran to remote master sites. While this is running
the repgroup is in a Quiescing state. Once deftran has been cleared, the
repgroup then becomes quiesced. A repgroup cannot become quiesced until all
defered transactions have been pushed. If there are any problems pushing the
transactions, the repgroup will remain 'stuck' in quiescing mode.

To recover from this : refer to the troubleshooting guide Note 180014.1
(Master repgroup is stuck in 'QUIESCING' mode and does not go to quiesced).

2.3.2 Group still quiesced after resume_master_activity command issued
----------------------------------------------------------------------

A master repgroup cannot resume activity until all admin requests for the
group have been successfully applied at all master sites for the repgroup.

If you have issued the resume_master_activity command but the group is still
quiesced, there may be admin requests in the admin queue that need to be run
or that have failed with an error. To check this :

    select id, request, status, master from dba_repcatlog;

If rows are returned, run the associated do_defer_recat_admin jobs at all
master sites for the repgroup until all admin requests have been applied.  The
last admin request that you should see applied is the resume master activity
request.

If you have admin requests that have failed you will need to resolve the
errors, regenerate the admin request, and delete the requests with errors from
the admin request queue (see 2.2 Admin Requests). For additional information
please refer to Note 180014.1

2.4 Automated job not running
-----------------------------

Oracle Replication uses the job queue to automate the propagation and purging
of deferred transactions, the propagation and application of administrative
requests, and snapshot refreshes. If the jobs are not configured properly,
they will not run as expected and their associated tasks will not be completed.

To troubleshoot a job, address the following:

1) If this is a new database. Bounce the database at least once. You may
   need to drop the job and reschedule it. This is one of those
   idiosyncrasies of new databases and automated jobs.
2) Make sure the package the job is calling is valid (see section 1.1 Packages)
3) Make sure the log_user of the job has privileges to do what the job is doing
   (see section 1.2 Users and privileges).
4) Make sure all database links required by the job are available and working
   (see section 1.3 Database links).
5) Make sure the job is not broken or does not have failures.

   Issue the following command:

       select job, what, next_date, interval, log_user, broken, failures
       from dba_jobs;

       job       - job identifier number
       what      - procedure the job will run
       next_date - the next time the job will run
       interval  - how often the job should run
       log_user  - owner of the job; should be user with proper
                   privilages, usually repadmin user or propagator
       broken    - if true, job is broken and will not run automatically
       failures  - number of times a job has attempted to run and failed

If a job has failed 16 times, it will mark itself as broken and requires user
intervention to run again. If a job has failed, it will generate a trace file
snpxxx.trc in the background_dump_dest directory. The trace file will show the
cause of the failure. Resolve the problem, then run the job manually with
dbms_job.run(<job#>).  Also see Note 103349.1

If the next_date of the job is in the past, try running the job manually.

If the job runs fine check your job queue parameters:

    job_queue_processes = (number of jobs in dba_jobs that may run
                           concurrently) + 1

    job_queue_interval  = less than the interval for the job itself
                          (keep in mind that jobs may not run exactly on
                           time.  The job could actually run as late as
                           the next_date + job_queue_interval)

If the job runs fine manually, make sure the next_date increments by the
interval. Monitor the job queue to see if the job runs as scheduled. If the
job still does not run automatically, contact Oracle Support.

2.5 Snapshot refreshes
----------------------

2.5.1 Snapshot refresh loses snapshot changes
---------------------------------------------

The base concept of a snapshot is to contain an exact copy of the data in it's
associated master table at the time of refresh.  If changes are made at an
updateable snapshot site, but are lost when the snapshot is refreshed, the
changes either did not take place at the master site or were overwritten by
later changes.  If your snapshot changes are lost after a successful refresh,
check the following:

1) Make sure the changes are being recorded in the deftran at the snapshot
   site.

2) Make sure the refresh job is pushing the changes as part of the job:

   The dbms_snapshot.refresh package parameter 'push_deferred_rpc' defaults
   to true. This will cause the refresh job to push the entries in deftran
   before it refreshes the snapshot. If this parameter is set to false, the
   transactions will not be pushed and thus not applied at the master site.
   When the snapshot refreshes, the changes will appear to be 'lost'. However,
   when the transactions are pushed, usually by a separate push job, the
   changes will then be visible at the master site and pulled to the snapshot
   site on the next refresh.

3) Make sure the deferred transactions are propagated properly (see section
   2.1 Deferred Transactions).

4) Make sure deferred transactions did not error out at the master site
   (see section 2.1.3 of 2.1 Deferred Transactions).

5) If conflict resolution has been implemented at the master site, make sure
   you are familiar with the results of the resolution methods. This could
   be the cause of the difference between what you are seeing and what you
   expect to see:

   An example of this would be the EARLIEST TIMESTAMP resolution method which
   will discard changes with a newer timestamp if a conflict is detected.

6) If more than one site can update the same record that the snapshot site
   can update, the snapshot update may be replaced with a newer update when
   refreshed.

2.5.2 Snapshot can not fast refresh
-----------------------------------

There are a number of mechanisms in place at both the snapshot site and the
master site to facilitate a fast refresh.  The main reasons a snapshot cannot
be refreshed are because it is either too complex or the snapshot log on the
master site is not accessible or out of date.

See Note 179469.1 DIAGNOSING THE ORA-12004 for fast refresh problems and
resolutions.

See Note 35217.1 WHEN SNAPSHOT LOGS GET PURGED for discussion on how snapshot
logs on the master site are used for fast refreshes.

Special Note:  In Oracle 8i, the term 'materialized view' replaces the term
snapshot. In Oracle 8i, materialized views can reside on the same site as
it's master table. This is NOT regarded as a replicated environment, but
rather a data warehousing environment.  There are special conditions that
apply only to data warehousing materialized view fast refreshes and not to
replicated materialized view fast refreshes.  The easiest way to determine
what you can and can not do with fast refreshes is look at where the master
table for the snapshot resides.  If the master table resides on a remote site
(you have to go over a database link), your fast refresh is governed by the
rules laid forth in the Replication manual.  If the master table resides on
the same site, your fast refresh is governed by the rules laid forth in the
Tuning manual.

3. ISSUES WITH REPLICATION MANAGER SETUP WIZARD:
================================================

Replication Manager Setup Wizard connects as user SYSTEM to create replication
users and grant privileges.  SYSTEM does not have privileges to grant execute
on the specific packages used internally by the replication users.
Irregularities have been encountered when using the setup wizard to create a
replicated environment.  It is suggested that you create the replication users
and grant privileges via command line connecting as SYS as SYSDBA.

Replication setup wizard assumes that the global_name and tnsalias for a
database are identical when creating the public database link.  This is often
not the case, and you will get an ora-12154 when attempting to access the
database link. It is suggested that you create the replication users database
links via command line.

See the following setup note for Multi-Master replication environment setup
and configuration :

    Oracle 8.1 onwards - Note 117434.1

See the following setup note for Single-Master replication environment setup
and configuration :

    Oracle 8.1 onwards - Note 112075.1

RELATED DOCUMENTS
-----------------

Oracle Server Replication Manual
_______________________________________________________________________________
                                                        Oracle Support Services

.
 

Videos, Slideshows and Podcasts by Cincopa Wordpress Plugin