How can I take a tablespace backup using the export utility? Is it possible in Oracle8i? What is the procedure?
Requires Free Membership to View
Yes, you can take a tablespace backup using the export utility. But be warned, that backups with the export utility will not be able to use archived redo logs. So you can restore only to the point that the export was performed. You will not be able to roll forward to some point after the export was performed like you can with other Oracle backup methods. Keep this point in mind when you are implementing a solid backup and recovery strategy.
To take a tablespace export, you will want to take advantage of Transportable Tablespaces. This capability was introduced in Oracle 8i. And you must have Oracle 8i Enterprise Edition to create the backup of the tablespace. For more information than I am providing here, I suggest that you take a look at the Oracle 8i Concepts Guide and the Oracle 8i Administrator's Guide.
Here are the basic guidelines of taking a backup copy of a tablespace with the export utility:
- Make sure that the tablespace is "self contained". According to the Oracle documentation this means "there are no references from inside the set of tablespaces pointing outside of the tablespaces." You can use the dbms_tts.transport_set_check procedure to verify that the tablespace is self contained.
- Make the tablespace read-only.
ALTER TABLESPACE ts_name READ ONLY; - Invoke the export utility.
EXP TRANSPORT_TABLESPACE=y TABLESPACES=ts_name FILE=expdat.dmp
You must connect as SYSDBA to perform this step! - Use OS commands to copy the datafile(s) that make up the tablespace.
- Put the tablespace back in read write mode.
ALTER TABLESPACE ts_name READ WRITE;
The export utility in this case basically captures the data dictionary information of all objects in the tablespace. The datafiles that you copied contain the data! Restoring from this type of backup is done by "plugging" the transportable tablespace back into the database. The Oracle documention has all the information you'll need to figure that one out!
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Oracle gurus are waiting to answer your toughest questions.
This was first published in June 2001
Join the conversationComment
Share
Comments
Results
Contribute to the conversation