Solved

Oracle replication error

Posted on 2002-03-14
8
607 Views
Last Modified: 2012-06-27
I am supporting a program that uses oracle replication via Repsvr8.exe
(ocx control), and am getting error during the replication process.
Our IT dept. does not support Oracle Lite so I am basically left to
try and figure out the problem myself.
The replication replicates 6 tables from our server database to the
local databases on the client/user's computer.
error I recieve is;
ORACLE ERROR - #26522
repapi-26522: plsql rpc refresh context get failed
REPAPI-26522: rpc execution error
ORA-06550: line 2, column 10:
PLS-00201: identifier 'SYS.DBMS_SNAP_REPAPI'must be declared
ORA-06550: line 1, column 10:
PL/SQL: statement ignored
ORA-06550: LINE 1, COLUMN 10:
pls-00201: identifier 'SYS.DBMS_SNAP_REPAPI'must be declared
...
The last two lines are identical to the two lines under the other
PLS-00201 error line.
Any help would be greatly appreciated. I am learning on the fly and
have been having quite a bit of problems with this error.
Thanks
Darrel
darrel.seymour@fnc.fujitsu.com
0
Comment
Question by:darrel
  • 3
  • 3
  • 2
8 Comments
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
darrel,
   You're getting an error finding the supplied PL/SQL packages for the replication.  Unless someone else has done exactly the same thing (I haven't) we'll all need some additional info:

1) Has this EVER worked before?  If so, what changed?
2) What is "the replication process" - what steps do you perform to do the replication?
3) What is the database version of the server database?
4) How did you create the logon user on the server database (what role/privileges)?

Good luck!

P.S.  You might want to read the docs at the location below (it requires registration, but it's free):

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76959/repnew.htm#3762
0
 
LVL 7

Expert Comment

by:waynezhu
Comment Utility
On master site:

First  make sure the package installed and valid, for example,
    select status from dba_objects where object_name='DBMS_SNAP_REPAPI';

Second check if the user you are using has sufficient privilidges. If not,  grant execute any procedure
(or simply the DBA privilidge) to the user.
0
 

Author Comment

by:darrel
Comment Utility
I apologize for not giving complete info, I knew better!
We are using Oracle 8.0.5 and oracle lite 4.0.1.0.0
The replication did work at one time, but sometime last year it quit and nobody knows exactly when. I suspect it was when our IT dept. rebuilt the server database when one of our admins deleted 200+ records.
We user a DSN to connect to the database and don't have any roles or priviledges, not a very secure system. I have DBA access to our test database, but am not a DBA. I've been up to my eye-balls in this because I'm not really qualified, but I'm doing the best I can...

The replication code is as follows;
        For Each varTable In mMasterTable
            frmProjIndex.stsMessage.SimpleText = LoadResData(515, kResTypeString) & varTable & "..."
            frmProjIndex.stsMessage.Refresh
            Call mObjReplication.SnapshotGroupReset
            DoEvents
            Call mObjReplication.SnapshotGroupAdd(mStrLocalDBUser, varTable, kRepOptimumRefresh)
            DoEvents
            If False = mObjReplication.SnapshotGroupRefresh(kRepContinueOnError) Then
                'Show error: Could not refresh snapshot for master data
                'This will show if there is an error and kRepStopOnError is used above instead of kRepContinueOnError
                Call ShowOracleError(512)
                Exit Function
            End If
            DoEvents
        Next varTable

0
 
LVL 22

Accepted Solution

by:
DrSQL earned 300 total points
Comment Utility
darrel,

   Run wayne's query.  Then, here are two links (to the same 8.0.5 document) that you need to read:

Preparing for snapshot replication:
http://otn.oracle.com/doc/server.804/a58245/ch4.htm#7475

and Troubleshooting Snapshots
http://otn.oracle.com/doc/server.804/a58245/ch6.htm#7231

Which contains this:

Unable to Generate Replication Support for a Table

When you generate replication support for a table, Oracle activates an internal trigger at the local site. If the table will be propagating changes asynchronously, this trigger uses the DBMS_DEFER package to build the calls that are placed in the local deferred transaction queue. EXECUTE privileges for most of the packages involved with advanced replication, such as DBMS_REPCAT and DBMS_DEFER, need to be granted to replication administrators and users that own replicated objects. The Replication Manager setup wizard and the DBMS_REPCAT_ADMIN package performs the grants needed by the replication administrators for many typical replication scenarios. When the owner of a replicated object is not a replication administrator, however, you must explicitly grant EXECUTE privilege on DBMS_DEFER to the object owner.

Problems with Replicated Procedures or Triggers

If you discover an unexpected unresolved conflict, and you were mixing procedural and row-level replication on a table, carefully review the procedure to ensure that the replicated procedure did not cause the conflict. Ensure that ordering conflicts between procedural and row-level updates are not possible. Check if the replicated procedure locks the table in EXCLUSIVE mode before performing updates (or uses some other mechanism of avoiding conflicts with row-level updates). Check that row-level replication is disabled at the start of the replicated procedure and re-enabled at the end. Ensure that row-level replication is re-enabled even if exceptions occur when the procedure executes. In addition, check to be sure that the replicated procedure executed at all master sites. You should perform similar checks on any replicated triggers that you have defined on replicated tables.

Good luck!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:darrel
Comment Utility
Thanks so much DrSQL. I've been pulling my hair out due to little or no support from or IT dept., and the fact that I'm way over my head. If I can get our test DB replication going I can go to my boss and have him request the changes to production.
I've got VB experience, and some database, but far from a DBA.
Thanks for the help
Darrel
0
 

Author Comment

by:darrel
Comment Utility
Waynezhu, I apologize I must have skipped over your comment. I'll post points under "Question for Waynezhu" Do I'm going to show my ignorance about the subject, but in the Query do I type in query exactly as you have it typed? Or does dba_objects need to be replaced with something? When I type it as is I get an error on the dba_objects of "table or view does not exist".
Thanks much
Darrel
0
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
darrel,
   Thanks.  The error you're getting with wayne's query is not good.  DBA_OBJECTS should be available to your account if it has DBA privileges.  If you can't see it, then you probably don't have DBA priv on that account.  Either switch to an account that has DBA privs, or have your DBA grant you DBA:

grant dba to <your test account>;


Good luck!
0
 
LVL 7

Expert Comment

by:waynezhu
Comment Utility
As inferred in my previous input, the problem is at the master site not at your snapshot site.
Any one who has DBA privilidge can run the query (for non-DBA user, ALL_OBJECTS may
contains the similar information) and fix the problem.
Thanks,
Wayne
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now