Importing Snapshots during migration process
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Importing Snapshots during migration process

  1. #1
    Join Date
    Feb 2001
    Location
    Adelaide, Australia
    Posts
    159

    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

  2. #2
    Join Date
    Feb 2001
    Location
    Adelaide, Australia
    Posts
    159
    Does anyone know about the process of snapshot registration during an import "Create snapshot" statement?

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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');"?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  4. #4
    Join Date
    Feb 2001
    Location
    Adelaide, Australia
    Posts
    159
    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

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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?

    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.

    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.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width