Adding LOB Objects in Repliation

How to Add LOB Objects in Replication site.

If you want add lob objects in replication site. (You must be already done Advanced Replication “Click here “)

That objects must be contains primary key constraints.

In scott@US.edkal

SQL> DESC MASTER

Name Null? Type

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

COMPANY_CODE VARCHAR2(10)

COMPANY_NAME NOT NULL VARCHAR2(100)

COMPANYLOGO BLOB

SHORT_NAME NOT NULL VARCHAR2(10)

PRINT_NAME NOT NULL VARCHAR2(100)

In repadmin@US.edkal

Here you need temporary stop master site activity and add those objects in master site.

SQL> BEGIN

2 DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (

3 gname => 'us_rep'

4 );

5 END;

6 /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

3 gname => 'us_rep',

4 sname => 'SCOTT',

5 oname => 'MASTER',

6 type => 'TABLE',

7 copy_rows => TRUE,

8 use_existing_object => TRUE );

9 END;

10 /

PL/SQL procedure successfully completed.

SQL> ED

Wrote file afiedt.buf

1 BEGIN

2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

3 gname => 'us_rep',

4 sname => 'SCOTT',

5 oname => 'MASTER_PK',

6 type => 'INDEX',

7 copy_rows => TRUE,

8 use_existing_object => TRUE );

9* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> CREATE MATERIALIZED VIEW LOG ON SCOTT.MASTER;

Materialized view log created.

SQL> BEGIN

2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (

3 sname => 'SCOTT',

4 oname => 'MASTER',

5 type => 'TABLE',

6 min_communication => true,

7 generate_80_compatible => FALSE);

8* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY (

3 gname => 'us_rep'

4 );

5 END;

6 /

PL/SQL procedure successfully completed.

Once complete start your master activity.

Create materialized view’s in your replication site.

In scott@mv01.edkal

SQL> CREATE MATERIALIZED VIEW MASTER REFRESH FAST WITH PRIMARY KEY UPDATABLE AS SELECT * FROM SCOTT.MASTER@US.EDKAL;

Materialized view created.

In mviewadmin@mv01.edkal

SQL> BEGIN

DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(

gname => '"US_REP"',

sname => '"SCOTT"',

oname => '"MASTER"',

type => 'SNAPSHOT',

min_communication => TRUE);

END;

PL/SQL procedure successfully completed.

SQL> BEGIN

DBMS_REFRESH.ADD(

name => '"MVIEWADMIN"."US_REP"',

list => '"SCOTT"."MASTER"',

lax => TRUE);

END;

PL/SQL procedure successfully completed.

ORA-01788

Oracle Server - Enterprise Edition - Version: 10.2.0.3
This problem can occur on any platform.
After upgrading to Oracle 10g, started getting an ORA-1788 error for a query that includes the LEVEL pseudo column. This query was running fine on 9i.

Example:

On 9i:

SQL> select level from dual;

LEVEL
----------
0

On 10g:

SQL> select level from dual;
select level from dual
*
ERROR at line 1:
ORA-01788: CONNECT BY clause required in this query block

Solution:

SQL> alter system set "_allow_level_without_connect_by"=true scope=spfile;

or if using a pfile add the line

_allow_level_without_connect_by=true

at the beginning of the pfile.


Both cases will require a restart of the database to take effect.

Replicaton Tuning Tool

The primary tools used to perform the FAST REFRESH analysis were:

1. Oracle provided DBMS_MVIEW.explain_mview procedure
2. MV_CAPABILITIES_TABLE table

The Oracle provided DBMS_MVIEW.explain_mview procedure was used to analyze each of the existing materialized views FAST REFRESH capabilities and write the results to the MV_CAPABILITIES_TABLE:

1. Lists the general capabilities of materialized view or materialized view query
2. Specifies whether each capability is possible (Y or N)
3. If a capability is not possible the reason (why) is written to the msgtxt column of the MV_CAPABILITIES_TABLE

The MV_CAPABILITIES TABLE is created in the owner’s schema by running the following Oracle 9i script:

SQL> @ $ORACLE_HOME/rdbms/admin/utlxmv.sql

Example:

After creating utlxmv.sql script execute the below command.

SQL> exec dbms_mview.explain_mview( 'GENERALISSUEARNO');


SQL> SELECT CAPABILITY_NAME,POSSIBLE,SUBSTR(MSGTXT,1,60) MSGTXT FROM MV_CAPABILITIES_TABLE WHERE CAPABILITY_NAME LIKE '%FAST%';

CAPABILITY_NAME P MSGTXT
------------------------------ - ------------------------------------------------------------
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the mater
REFRESH_FAST_PCT N the materialized view is upgraded from a previous version th

6 rows selected.