13 Replies Latest reply: Apr 28, 2014 5:38 AM by spajdy RSS

    11G replication with blob field (ORA-04054)

    G. Krieger

      Hi all,


      I already posted this in 2012, but didn't get a useful answer, so I try again:

       

      I want to replicate a table that contains a blob field from a 11g database to an 11g XE using materialized view replication. I use a subquery to create the MV on the client. I want to use the REFRESH FAST option for the MV. The subquery works with other tables that do not contain a blob item.

       

      When I omit the subquery for the MV, then replication works perfectly. When I use a subquery for the MV I get the following error during a refresh on the client:

       

      ORA-12048: error encountered while refreshing materialized view "MDS_ADMIN"."DRAWIN"

      ORA-04054: database link XEKRIEGE.HMB.HMBNET does not exist

      ORA-02063: preceding line from TMDS.HMB.HMBNET

      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566

      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779

      ORA-06512: at "SYS.DBMS_IREFRESH", line 685

      ORA-06512: at "SYS.DBMS_REFRESH", line 195

      ORA-06512: at line 2

       

      where MDS_ADMIN.DRAWIN is the table/MV that contains the blob field, XEKRIEGE.HMB.HMBNET is the client and TMDS.HMB.HMBNET the master.

       

      Any ideas?

       

      Thanks in advance

       

      Gerald

        • 1. Re: 11G replication with blob field (ORA-04054)
          spajdy

          We need more information about your environment to help you.

          What we need:

          1/ your DB topology - DB and how there are connected by DB links

          2/ DDL statement for you MV snapshot site

          3/ DLL statement for objects on master site your MV is referencing

          • 2. Re: 11G replication with blob field (ORA-04054)
            G. Krieger

            Hi,

            sorry for the delay.

             

            1) Master: Oracle 11g SE; Snapshot: Oracle XE 11g;; DB-Links on Snapshot site: public links for SYS, public and private links for Materialized view admin, propagator, table owner and user schema

            2)

            CREATE SNAPSHOT DRAWIN TABLESPACE MDS_1M_D

            REFRESH FAST

            AS

            SELECT

               SITE_ID, MACHINE_CUST_ID, MACHINE_ID,

               ID, SECTION, SHEET,

               DRAWINGNAME, DRAWING, CREATED_BY,

               DATE_CREATED, MODIFIED_BY, DATE_MODIFIED,

               CONFIDENTIAL

            FROM "MDS_ADMIN".DRAWIN@TMDS.HMB.HMBNET drawin

            where confidential = 0 and

            exists (select 0 from moempcus@TMDS.HMB.HMBNET moempcus where drawin.machine_CUST_ID=moempcus.cust_id and moempcus.hgremp_id = &2);

             

            3)

            CREATE TABLE DRAWIN

            (

              SITE_ID          NUMBER(20)                   NOT NULL,

              MACHINE_CUST_ID  NUMBER(20)                   NOT NULL,

              MACHINE_ID       NUMBER(20)                   NOT NULL,

              ID               NUMBER(20)                   NOT NULL,

              SECTION          VARCHAR2(1 BYTE)             NOT NULL,

              SHEET            NUMBER(2)                    NOT NULL,

              DRAWINGNAME      VARCHAR2(20 BYTE)            NOT NULL,

              DRAWING          BLOB,

              CREATED_BY       VARCHAR2(30 BYTE)            NOT NULL,

              DATE_CREATED     DATE                         NOT NULL,

              MODIFIED_BY      VARCHAR2(30 BYTE),

              DATE_MODIFIED    DATE,

              CONFIDENTIAL     NUMBER(1)                    NOT NULL

            )

             

            CREATE TABLE MOEMPCUS

            (

              HGREMP_ID        NUMBER(20)                   NOT NULL,

              CUST_ID          NUMBER(20)                   NOT NULL,

              SITE_ID          NUMBER(20)                   NOT NULL,

              COUNTR_ISO_CODE  VARCHAR2(3 BYTE)             NOT NULL

            )

             

            Thanks for your efforts.

             

            Gerald

            • 3. Re: 11G replication with blob field (ORA-04054)
              spajdy

              Log to snapshot site under schema that is owner of MV DRAWIN and run a SELECT you use to define MV.

              SELECT
                 SITE_ID, MACHINE_CUST_ID, MACHINE_ID,
                 ID, SECTION, SHEET,
                 DRAWINGNAME, DRAWING, CREATED_BY,
                 DATE_CREATED, MODIFIED_BY, DATE_MODIFIED,
                 CONFIDENTIAL
              FROM "MDS_ADMIN".DRAWIN@TMDS.HMB.HMBNET drawin
              where confidential = 0 and
              exists (select 0 from moempcus@TMDS.HMB.HMBNET moempcus where drawin.machine_CUST_ID=moempcus.cust_id and moempcus.hgremp_id = &2);
              

               

              If this SQL work then problem is probably in DB link definition. When job is runned than background proccess is used and if in DB link definition is not used schema to log to master site then actual user credential are used.

              • 4. Re: 11G replication with blob field (ORA-04054)
                G. Krieger

                This SQL raises "ORA-22992 cannot use LOB locators selected from remote tables", which is expected. But I CAN create the MV, I only cannot replicate it, as long as the subquery is present.

                • 5. Re: 11G replication with blob field (ORA-04054)
                  spajdy

                  So remove column DRAWING and try SQL again.

                  If it will work then try under same session refresh MV using exec dbms_mview.refresh.

                  • 6. Re: 11G replication with blob field (ORA-04054)
                    G. Krieger

                    Well, that works of course. But how do I then get my blob item DRAWING replicated to the client?

                    • 7. Re: 11G replication with blob field (ORA-04054)
                      spajdy

                      If dbms_mview.refresh works than create a job that call this procedure a then check if job is runned successfuly. If not search alert.log or trace file for logged errors.

                      • 8. Re: 11G replication with blob field (ORA-04054)
                        G. Krieger

                        dbms_mview.refresh works when I omit the blob item. When I add the blob item it gives the above mentioned error in the alert.log.

                        • 9. Re: 11G replication with blob field (ORA-04054)
                          spajdy

                          G. Krieger wrote:

                           

                          dbms_mview.refresh works when I omit the blob item. When I add the blob item it gives the above mentioned error in the alert.log.

                          ORA-12048: error encountered while refreshing materialized view "MDS_ADMIN"."DRAWIN"

                          ORA-04054: database link XEKRIEGE.HMB.HMBNET does not exist

                          ORA-02063: preceding line from TMDS.HMB.HMBNET


                          For me this errro say that on master site (DB link TMDS.HMB.HMBNET is pointing to) is missing DB link with name XEKRIEGE.HMB.HMBNET.

                          That's reason why I ask for DLL for master site objects. You suplied DDL statement for table. No view or MV using DB link XEKRIEGE.HMB.HMBNET.


                          I could suggest only create on master site simple table with primary key (number type) and BLOB column. Than on snapshot site create simple MV based on that table a try job that refresh this MV.

                          If this will work then porblem must be somwhere in objects used for you problematic MV. You need to check if you don't miss something.

                          • 10. Re: 11G replication with blob field (ORA-04054)
                            G. Krieger

                            What is DLL statement? I know DDL, DML DCL.....

                             

                            Ok, after some reading and testing I did the following:

                            - rename the snapshot db from XEKRIEGE to XEKRIEGE.HMB.HMBNET

                            - create public database link on master db XEKRIEGE.HMB.HMBNET using the MV owner login

                             

                            This works as long as the client computer is within our LAN. When I refresh from outside I get ORA-121790 TNS Connection timeout. That's clear, the master cannot resolve the client's name used in the db link.

                             

                            Here's the code for the public db link:

                             

                            CREATE PUBLIC DATABASE LINK "XEKRIEGE.HMB.HMBNET"

                            CONNECT TO <MV owner>

                            IDENTIFIED BY <PWD>

                            USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<MV client computer>)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))';

                            • 11. Re: 11G replication with blob field (ORA-04054)
                              spajdy

                              Sorry my type error DDL of course.

                               

                              For connection timeout set in sqlnet.ora

                              SQLNET.EXPIRE_TIME

                              or

                              SQLNET.INBOUND_CONNECT_TIMEOUT

                               

                              see documentation for details.

                               

                              So at least refresh job is now working correctly ?

                              • 12. Re: 11G replication with blob field (ORA-04054)
                                G. Krieger

                                Refresh works as long as the client is in our LAN. When it is outside it will never work because the DB Link will not know the client's IP address, which results into TNS connection timeout.

                                 

                                Thanks anyway.

                                • 13. Re: 11G replication with blob field (ORA-04054)
                                  spajdy

                                  This is a network problem. Ask you network admin guys for help.

                                  If you are connected using VPN then DNS must work correctly. Or there could be some firewall issue.