Problem solve
Get help with specific problems with your technologies, process and projects.
Recovering lost table and data before TSPITR
Before the new way of TSPITR (8i, 9i), how did one recover a lost table and its data?
I'm attempting a 9iR2(9.2.0.3) user-managed TSPITR test. After creating auxiliary database, the export of tablespace fails with ORA-1552. Using the v$datafile, you see: C:ORADATAAUXDBUNDOTBS01.DBF ONLINE. Then using: select segment_name, tablespace_name, status from dba_rollback_segs, _SYSSMU1$ UNDOTBS1 OFFLINE. The SYSTEM LMT is online, but the instance undo segments are offline. As of now, user-managed TSPITR by exporting tablespaces is not a viable option - Metalink bug 2621039. Before the new way of TSPITR (8i,9i), how did one recover a lost table and its data?
Before TSPITR, one used Oracle exports to recover a lost table and its data. Every night, the DBA's scripts would export the entire database. To restore a lost table, one just imported from the nightly export. Unfortunately, one could not then roll forward from the time the export was taken to recover any transactions since the export up until right before the table was dropped. This shortcoming led to the introduction of TSPITR in recent Oracle releases.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our PL/SQL, database administration and data warehousing gurus are waiting to answer your toughest questions.
This was last published in July 2003
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments