Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2009
    Posts
    3

    Question Unanswered: map data tables and copy data between two schemas

    Hi All,

    I am new to DB related development, I am currently asked to come up with a solution to write a script or use a tool to map tables between two schemas and copy data from one DB to another
    A simple scenario would be, we have our production DB with the current schema, during each release we might change the structure of development DB or just change names of some columns in some tables.
    the requirement is to have a script or a tool which can map entries of production and copy them to the new structure of development DB

    can anyone help me in this regard as I am totally blank on how to proceed with this.

    Thanks & Regards,
    Prajwal Boloor

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    In some/many/most places running Oracle based applications in a production environment utilize some sort of Source Code Control System to manage the production environment; including schema (DDL) changes

    In some/many/most companies application changes (including DDL) are made
    & tested in a development environment PRIOR to be released into Production.

    From my perspective the posted "requirements" make little to no sense.
    The schema "changes" should already exist in development DB
    & you should have the necessary DDL to make the requisite schema changes.

    Ready, Fire, Aim!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    I would argue that you need an instance which is a nightly copy of PROD, in it's entirety. If possible, something like an Export/Import process.

    We're looking into the same thing, so that we can investigate production problems in a non-PROD environment which reflects pre-production data.

    I'm not sure if others are able to troubleshoot problems in a different manner, but some of our nightly batch jobs are complicated, and due to changes (like you're mentioning) we can't just move data from PROD into DEV for troublshooting.

    --=Chuck

  4. #4
    Join Date
    Jan 2009
    Posts
    3
    may be I can shed some more light on the requirement.
    we are currently migrating an existing application into a new application.
    so we have a production DB running now.

    once we start developing, with every release the client should be able to readily migrate his production data into the new DB and start using the new application and test it at his production-simulation environment.

    usually the migration of data from existing production to new schema would happen when we give the final release to the client, but we have proposed that with every release the client can migrate his data from production onto the new schema and use the new application if required.

    so now i am supposed to come up with a solution which involves a tool or a migration script which would map the tables of production schema (structure won't change) and our development schema (structure will change) & copy the data. so when ever we change our dev schema, we modify the current script and run it to copy the data again.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    After reading your response my head is spinning.
    Now I am not sure which comes first, development or production.
    You can always use MINUS to determine differences between two schemas;
    but I am still not sure which schema should be considered "baseline".
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jan 2009
    Posts
    3
    1. hey u have an application on production
    2. the technology is out dated
    3. you develop new app using new technology new DB (slightly different from the current DB)
    4. when u want to migrate app to new one, u want to retain ur current production data, so u would want to map and copy them on to new DB
    5. now u use the new app, new DB, and continue with current prod data...

    so how to map the two DB and copy data ???

    I cant be more precise ..

    any help??

  7. #7
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    I am afraid I won't be of much help answering your question. I am going to ask you a question. Are you sure this is the best process to roll out appliction upgrades? If a customer has a large amount of data, moving it from the 'old' database to the 'new' database is time consuming and painful.

    To be honest, I absolutely hate when vendors use this model for application upgrades. Why not just provide SQL scripts to modify the existing database structures? It's much simpler, doesn't require twice the disk space, and requires a smaller application outage for the upgrade. I am sure your customers would prefer the upgrade-in-place model better than the create a new database and move all the data over model. Especially if you have a normal amount of application upgrades.

    Just my 2 cents.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How is it that you can start with the old schema, modify the old schema into the new/current state & not have DLL to do so?
    The same DDL used to create the new schema or modify the old schema, can be used on the customers DB.

    >so how to map the two DB and copy data ???
    With much difficulty.

    This whole approach will only succeed by taking the whole application "offline" while making the changes
    & transferring the data between the 2 DBs.
    This approach would be totally unworkable with a 24x7 application.
    I can only hope I never have to be involved with such an application.
    Last edited by anacedent; 01-09-09 at 16:15.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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