struggeling with replication

From: EdStevens <quetico_man_at_yahoo.com>
Date: 25 Apr 2006 07:36:21 -0700
Message-ID: <1145975781.347436.286570@j33g2000cwa.googlegroups.com>

Am in the midst of trying to relocate 2 databases involved in a replication environment. In the new location, the master site is running Ora 8.1.7.4 EE on Solaris 9. (Yes, yes, I know! ) The MV site is running 10.2 on Win2k3.

We took an export from the current MV site (which is running Ora 8.1.7.something) and performed a schema import (fromuser, touser) to the new db. The only errors on the import were like this:

IMP-00017: following statement failed with ORACLE error 6550: "CREATE SNAPSHOT "REP_HOST_TXLOG" USING ("REP_HOST_TXLOG", <snip>
FROM "HOST_TXLOG"@EPS_LINK.US.ORACLE.COM "HOST_TXLOG""

IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 1, column 9:
PLS-00352: Unable to access another database 'EPS_LINK.US.ORACLE.COM'
ORA-06550: line 1, column 9:
PLS-00201: identifier 'SYS_at_EPS_LINK.US.ORACLE.COM' must be declared
ORA-06550: line 1, column 9:

PL/SQL: Statement ignored

Fair enough. I checked out the definition of the db Link from the original db, recreated it in the new db -- pointing to the 8.1.7 master site. Tested it out with some manual SELECT statments and it looked good.

Then I thought I'd familiarize myself with the CREATE SNAPSHOT statement itself. Searching the SQL Reference manuals for both 10.2 and 8.1.7 at tahiti, I can't find any such statement. (In 8.1.7 I do find a CREATE SNAPSHOT privilege.)

Pressing on, I reconstructed the statement from the import log, and executing that gives me:

SQL> @create_snapshot
FROM "HOST_TXLOG"@EPS_LINK.US.ORACLE.COM "HOST_TXLOG"

ERROR at line 143:
ORA-00942: table or view does not exist

yet ...

SQL> select count(*) FROM "HOST_TXLOG"@EPS_LINK.US.ORACLE.COM "HOST_TXLOG"; COUNT(*)

137243

Hmm...

Setting that aside and taking another tack, I used OEM to extract the DDL for the MV from the original MV site, and ran it against the new DB. There I got:

SQL> @create_mv
99 ;
CREATE MATERIALIZED VIEW "NOBELEPS"."REP_HOST_TXLOG" PCTFREE 10 *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> select owner, object_type from dba_objects 2 where object_name = 'REP_HOST_TXLOG';

OWNER                          OBJECT_TYPE
------------------------------ -------------------
NOBELEPS                       TABLE

OK, going back through the import log, I see where this table was created before getting to the CREATE SNAPSHOT statements that failed.

So, would somone mind taking a few minutes to further my education, and give me some pointer on how I need to proceed to get the replication working .. with a view towards how to do it when we do the real production migration?

Thanks. Received on Tue Apr 25 2006 - 09:36:21 CDT