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

Method for refreshing training databases

In an answer you gave to a question regarding restoring datafiles, you stated "You cannot just copy a datafile from one database to another and access the data." That is what is being done in a restore from a manual cold backup. Why didn't you indicate that after restoring the datafiles you can re-create the control file(s) and the new database is up and running? I refresh my training databases this way.

In an answer you gave to a question regarding restoring datafiles, you stated "You cannot just copy a datafile from one database to another and access the data." That is what is being done in a restore from a manual cold backup. Why didn't you indicate that after restoring the datafiles you can re-create the control file(s) and the new database is up and running? I refresh my training databases by shutting down my master database, copying the datafiles to a new directory, starting up the training db in nomount mode, re-creating the control file, opening the database with resetlogs, renaming global_name and adding back the datafiles for my temporary tablespaces. I realize that the master db and training db have the same DBID now, but I don't use RMAN to backup these databases. Is there a flaw in my method of creating my training databases?

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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close