Client lost a table - Db from 1 tera I have a good coldbackup from 5-13 -No archivelog db

LindaC
LindaC used Ask the Experts™
on
Hi experts.

Client lost a table - Db from 1 tera I have a good coldbackup from 5-13 -No archivelog db

No export for that particular table.

Can I recover that table from a coldbackup?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Oracle DBA
Commented:
The short answer is yes.

I writing this from memory and it has been a long time since I did this.  You need a separate machine.  Restore the SYSTEM, UNDO and data tablespace that contains the table.  You can bring up the database at that point (you may have to OFFLINE DROP the unused tablespaces).  From there, use exp or expdp to export the table, then you can import back to where it needs to go.

Author

Commented:
Wow johnsone, if this comes ok, you will be the new and greatest super-hero.

Can it be in another database inside the same server?
Adding a san disk for the recovery of those dbf's from NEtbackup?
Most Valuable Expert 2011
Top Expert 2012
Commented:
If you've got the space for two copies of the db, then yes, recover the old backup to a new database.  Export/import as mentioned above (no expdp since its 8i) and you should be good to go
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

johnsoneSenior Oracle DBA

Commented:
I prefer a different server so mistakes cannot be make affecting the real database.  However, it can be done on the same server.  On the same server, shutting down the original instance is the safest way to go.  Be sure to use a different SID, as you cannot have 2 SIDs with the same name on the same server.

I'm not 100% sure same server with both databases up will work.  I haven't done that in a long time.  I thought that the lock file has the database name and not the SID, in which case you couldn't bring up 2 databases with the same name, even if they have different SIDs.  If you have an issue with the lock file, you would need to create a new ORACLE_HOME and put the restored database in that home.  That should get around the lock file problem.
Greg CloughSenior Oracle DBA
Commented:
What version of Oracle are you running?  I only ask as if you're on 10g or later and haven't yet purged the recyclebin then it may be as simple as:

flashback table MY_MISSING_TABLE to before drop;

Open in new window

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm#i1011362

Author

Commented:
Thank you!

Author

Commented:
Database version 9.2

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial