Ask the Expert

Moving datafiles from production to development by cold backup and renaming

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.

    Requires Free Membership to View

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.

This was first published in August 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: