Importing Snapshots during migration process

Printable View

08-15-2003, 03:07 AM
oddster

Importing Snapshots during migration process

Hi all,

Here is a sample 'create snapshot' statement from an export dump:

CREATE SNAPSHOT "TVP219UNITMEASURE" USING ("TVP219UNITMEASURE", (3,
'IVPAP.WORLD', 1, 0, "VPO999", "TVP219UNITMEASURE", '2003-08-01:
18:19:10', 0,
2760, '1950-01-01:12:00:00', '', 0, (2, "CD_COMPANY_SYSTEM",
"CD_COMPANY_SYSTEM", 0, 321, 0, "CD_UNIT_OF_MEASURE",
"CD_UNIT_OF_MEASURE", 0,
321, 0)), 2228321, 4, ('1950-01-01:12:00:00', 372, 0, 0,
'@CISOV_LINK')
REFRESH FAST WITH PRIMARY KEY AS
SELECT t.* FROM VPO999.TVP219UNITMEASURE@CISOV_LINK t

I have had a tar open with Oracle for about a month now in an attempt to understand this process. I am now hoping that all the people who used to work for Oracle and have real hands-on knowledge of this process are now happily working elsewhere, earning more money, and monitoring this forum......

;)

Basically this question came up after the migration we performed of a test system with simple read-only snapshots (8.1.7.2 on Sun Solaris 5.6 to 8.1.7.4 on HPUX 11i) using export/import.
Oracle advised that as long as the db_name, sid and tnsnames was the same in the target database, then the snapshots would import and continue to replicate where they left off.
In actual fact, this was incorrect information, the global_name of the target database was different (it had a .world domain extension) and this meant that upon importing, the snapshots registered themselves as completely new entities on the master site - and we lost data between the time of the export (when replication had been suspended) from the source and the time that these snapshots were created in the target.

We would like to clarify exactly what happens when the snapshot is imported, but it appears that not even the 'Replication Product Manager in US' can answer this question.

If you look at the import statement, 372 corresponds to the snapshot_id on the master site.
Does anyone know what criteria the registration process uses to match this snapshot with the old snapshot that is already registered?

Also, surely the recreation of the snapshots should have used the last_refresh time and picked up all log updates since that timestamp?

Can anyone clarify this for us? If you can its worth a beer or two.

Many thanks in advance,

Andy
08-17-2003, 08:09 AM
oddster

Does anyone know about the process of snapshot registration during an import "Create snapshot" statement?
08-17-2003, 08:22 AM
julian

Quote:

Originally posted by oddster
Does anyone know about the process of snapshot registration during an import "Create snapshot" statement?

I have encountered problems with snapshot replication between Solaris and HP-UX. However, your strategy is not the best possible. There is no need to import MVs. You better just recreate them. I would be curious to see all the information from "SELECT * FROM DBA_JOBS where JOB=" for the MV in question. Did you try to manually restart the refresh with "EXEC DBMS_SNAPSHOT.REFRESH('TVP219UNITMEASURE');"?
08-17-2003, 08:37 PM
oddster

Quote:

Originally posted by julian
However, your strategy is not the best possible. There is no need to import MVs.

How would you migrate a database from 8.1.7.2 on SUN to 8.1.7.4 on HP?
We had to use export/import. This is a very large database and the snapshots are on prebuilt tables, which have extra columns containing information on refresh times needed by the app.

We cannot stop the snapshots being created on full import.

Do you know what the criteria for regitsering a snapshot at the master is? e.g. does it use global name, snapshot id and/or last refresh time to determine waht to refresh and if a new snapshot should be registered in reg_snap$?

Brgds,

Andy
08-18-2003, 02:23 AM
julian

Quote:

Originally posted by oddster
How would you migrate a database from 8.1.7.2 on SUN to 8.1.7.4 on HP?

I would clone the database. Do you know how to do that?

Quote:

We had to use export/import. This is a very large database and the snapshots are on prebuilt tables, which have extra columns containing information on refresh times needed by the app.

Aye, with prebuilt tables you need the dumps, right.

Quote:

Do you know what the criteria for regitsering a snapshot at the master is? e.g. does it use global name, snapshot id and/or last refresh time to determine waht to refresh and if a new snapshot should be registered in reg_snap$?

It depend son how you build the MV log. Usually it is the primary key. You should run select * from DBA_MVIEW_LOGS: to check this.

To see the snapshots registered in the database, run select * from DBA_REGISTERED_SNAPSHOTS; It will tell you the snapshot site and even the query text.