Search code examples
oracle-databaseimportexportdatabase-administrationdatabase-restore

Oracle DB - Database backup without any data

I have a situation where I need:

  1. to import the latest structure of my prod database (everything including business logic but without any data) into a lower environment - because my web app refers to the new db structure.
  2. and then import data from a very specific old backup (data only) in this schema - because a certain set of our automated test cases refer to the older data and it's not feasible at this point to rewrite all those test cases. Is it possible? How?

What the team does right now is - Everytime to begin testing in this lower env.- Refresh this database with that specific old backup, say Jan 2021 (structure and data) and then execute all the DDLs that went live in Production after Jan 2021 to align the structure with prod's.. I recently joined team and find it really cumbersome and wondering if above solution I propose is doable by a DBA? It's Oracle Database. Any other way? Thanks for any help you may offer.

Solution

  • The data and data structure must match. You can't import data directly into a table structure that it didn't originate in, so initially the process you describe is the way to go:

    1. Import old table structure and data
    2. Apply all DDL to transform the structure to the new format

    At this point, before you do anything else:

    1. Make another export, with your new table structure and your data in the starting state you desire.
    2. In the future, restore from this export dump and achieve your starting state in a single step.