Re: struggeling with replication

EdStevens wrote:
> 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.
>

Snapshot is the old (7-ish) term for materialized views. It's kept for backward compatibility, but confuses people when referring to flashback, or RMAN.

On 8i, lots of things were broken with snapshot replication; exp/imp being one of them (refresh on commit being another, and updateable vertically and horizontally partitioned snapshots/MVs were a nightmare to set up).
I'd go for the create statements (as you partially did already), which brings another issue: 10g connectivity to 8i. May cause you trouble, although a 10g client should work, MVs (and reffresh commands) may be troublesome.

Unless you have a slow network, an unreasonable high number of records, or anything else, forcing you to use exp/imp, I'd get the original MV creation script(s), and run these.
If not availble, lost in time, or left the building - extract them from the export.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...