I'm not an expert. I'm a Software Developer but got trapped in doing this DBA work.

We have a development environment and we would like to copy the DBF files from the production environment to the development environment to get the latest version of the production database.

I'm wondering if this will work?

  • 1
    No, that won't work. The preferred method is to dump the production schema (=user) and import into the schema (=user) of the dev database server – a_horse_with_no_name Sep 5 '19 at 9:44
  • @a_horse_with_no_name can you please provide resource on how to do this. – Red Virus Sep 5 '19 at 9:46
  • 1
    DataPump Export/Import or the deprecated imp exp tools – a_horse_with_no_name Sep 5 '19 at 9:52

Do you have an existing database on the development env. and you want to refresh it or is it going to be a new database? And if this is a new database, do you have the Oracle software installed on the server?

If the database already exists, then the easiest way to refresh is to export the required schema(s) from the production database and import into the dev. database.

I'm providing a very crude example using datapump, but you should follow and read the links @a_horse_with_no_name provided in the comments above:


expdp system DIRECTORY=your_db_dir DUMPFILE=your_out_file.dmp LOGFILE=your_log_file.log SCHEMAS=schema_a,schema_b CONSISTENT=Y COMPRESSION=ALL

Import (you might need to drop the schemas first or you might need to add the TABLE_EXISTS_ACTION parameter):

impdp system DIRECTORY=your_db_dir DUMPFILE=your_out_file.dmp LOGFILE=your_log_file.log FULL=y

If however you want to refresh the database by copying the DBF files, you have a lot more things to take into consideration. The very high level steps would be:

  • Create pfile from spifile from Prod database
  • Shutdown Production and Development database
  • Copy DBFs, control files and pfile from primary to development
  • Do any necessary changes in the pfile to reflect dev. env.
  • Mount the dev. database using pfile
  • Change DBF directory names if different from Prod using 'alter database rename file'
  • Recover database if needed
  • Open the database and create spfile

As you can see, this also involves downtime on your production env. and is much more complex. I would recommend using this approach only if you know what you are doing or with an experienced DBA. But it would be a good learning experience if you could try this yourself on a separate dev. env. (and if you have enough time and patience to experiment).

| improve this answer | |
  • Oracle is already install in the development environment. What I want to do is refresh the data so that the latest data from the production database can be brought into the development database. Please advise thanks! – Red Virus Sep 5 '19 at 9:38
  • 1
    No. You can't just copy the DBF files. There is much more to a database than that. – a_horse_with_no_name Sep 5 '19 at 9:43
  • 1
    VCarstein your opening line is wrong: "Yes, copying DBF files is one way how you can make a copy of an existing database". I did not downvote but please try to be more precise in future. – sandman Sep 5 '19 at 11:27
  • 1
    @sandman Thanks, edited it – VCarstein Sep 5 '19 at 13:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.