You must also copy the datafile for the SYSTEM tablespace. If you just have the datafile for the USERS tablespace, then you will not be able to copy this file to another server and get it up and running in an Oracle database. The reason is that internally in that datafile, tables are only known by their object number. Columns in a table are only known by their column number. The Data Dictionary in the SYSTEM tablespace (specifically SYS.OBJ$ and SYS.COL$) map these object numbers to the object name, and the column number to the column name. Re-creating the control file will not re-create the SYSTEM tablespace.
Oracle's Transportable Tablespaces does let you copy a single datafile to a new database. But with TTS, you must also create a metadata dump file which describes the contents of that datafile. When you plug that datafile into the database, it updates the Data Dictionary with the required information.
As you noted, I stated "You cannot just copy a datafile . . ." This means you cannot copy one datafile to another server and add it to an existing Oracle database (unless you use TTS). You can, however copy the SYSTEM tablespace's datafile, and other tablespaces' datafiles, and get access to the data. You stated that you copy "the datafiles," which I assume includes the SYSTEM tablespace. In the question I answered, if I remember it correctly, the user did not have the SYSTEM tablespace's datafile.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.