Problem solve Get help with specific problems with your technologies, process and projects.

Moving datafiles from production to development by cold backup and renaming

You cannot add a datafile to a database and have the data in that datafile be available to your applications.

I have two databases, one production and one development, that have different content and different file systems. However, I want to take a tablespace named 'PRODDTA' from the production database and bring it online in the development database by overwriting a current tablespace named 'TESTDTA'. We were thinking we could take a cold backup of data files from the production box for the 'PRODDTA' tablespace (around 65 GB of data) and place them on the development box, rename the files and SOMEHOW tell the development database to recognize these files as the files we want it to use for the 'TESTDTA' tablespace. Is there a way to get the development database to recognize these unknown files that were never a part of its system? Can we edit a control file backup that has been backed up to trace and simply replace the control file? Has anyone attempted this? The reason we are not considering an export at this time is due to the extraordinary size of the data.
You cannot add a datafile to a database and have the data in that datafile be available to your applications. The closest you can do is to use Oracle's Transportable Tablespace (TTS) feature. With TTS, you copy READ ONLY datafiles from a source database and also create a metadata file describing the contents of the TTS. You can plug the tablespace into the destination database. Oracle TTS is the method that most matches your requirements. Oracle TTS is first available in Oracle 9i. There are two restrictions with this method of moving data. First, both platforms must be the same OS. Second, you cannot rename the tablespace. Oracle 10g removes the first restriction. Oracle 10g also makes available a command to rename tablespaces.

Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.