Solved

fast recovery of an oracle schema

Posted on 2011-03-14
9
506 Views
Last Modified: 2012-05-11
Hi,

i have a question. An application works as having an oracle schema e.g. "abcd" and it has all objects inside tablespace "abcd". Now normal procedure to backup is to take export of that schema obviously which takes some time as data is growing. An in case we have to recover schema by importing from that export file it takes many hours.

is there any quick solution to this problem?
can only tablespace backup /restore can help in this scenario?
0
Comment
Question by:baberamin
  • 4
  • 3
  • 2
9 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 total points
Comment Utility
You can do a litle with just a tablespace but I don't think it will do what you want.

You really can't recover a tablespace.  You recover a database and a tablespace is part of it.

How big is the database in addition to that tablespace?  IS there a reason you aren't using RMAN to do the backups?

With block change tracking and rolling incrementals, it's not that big of a deal to do backups.
0
 
LVL 11

Assisted Solution

by:Akenathon
Akenathon earned 334 total points
Comment Utility
You can backup the tablespace, look into TRANSPORTABLE TABLESPACES in the documentation, and then tablespace POINT IN TIME RECOVERY (TSPITR) in the docs. For instance, begin here for 11g:

http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmtspit.htm
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 total points
Comment Utility
"You really can't recover a tablespace."

I guess I stand corrected.  I should stop saying "can't".  I'm often corrected...
0
 
LVL 11

Assisted Solution

by:Akenathon
Akenathon earned 334 total points
Comment Utility
Oh, I really hadn't read your post until submitting mine... but you are right, with Oracle you can do everything... for a price! He can have incremental backups, incrementally updated backups, incremental/cummulative exports, or even a phisical (or logical) data guard if he wants REALLY fast recovery. He just needs to provide more information, we don't even know the version number...
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 11

Accepted Solution

by:
Akenathon earned 334 total points
Comment Utility
Quick question: you are using datapump (expdp/impdp), right? If your version has them, they are much faster than exp/imp, even with direct=Y. Migrate to datapump if you've not already done so and you'll never look back when it comes to performance. You'll only miss OS pipes to compress the dump files on the fly :-)
0
 

Author Comment

by:baberamin
Comment Utility
Hi,

Oracle version is 10g.
Looks like RMAN Point in time recovery is a solution.
Actually things should have quite simple if Database server was having only database for 1 application but thats not true in reality, the database server is having data for different applications.

The application about which I am concerned its having a schema 'abcd' with tablespace 'abcd'. So in case I need to recover to 2 hours before I can use RMAN to recover.
Correct me if I am wrong? and is it same as I'm recovering schema 'abcd' using IMP?

I'm using exp/imp and as advised I'll try to use expdp/impdb.
Can you tell exp/imp with direct=Y makes difference? and how much? any side effect?
0
 
LVL 11

Assisted Solution

by:Akenathon
Akenathon earned 334 total points
Comment Utility
Let's try to cover everything:

- RMAN will let you do much more than imp or impdp... you can recover to whenever you want with RMAN, instead of to the time of the exp or expdp. However, it's NOT the same as recovering a schema, RMAN does not know about those. It's physically recovering a tablespace, when it finishes you'll have every object there and nothing else, regardless of which schema they belong to. If you have abcd's objects elsewhere, they will NOT be recovered, for instance stored procedures belonging to abcd will NOT be included in an RMAN's TSPITR (they are stored in the SYSTEM tablespace)
- direct=Y works ONLY with exp. It bypasses the buffer cache, so it's much faster. How much? That's for you to try and tell me in YOUR scenario, Oracle never publishes benchmarks for a reason: they are too portable to apply to everyone's setup. No side effects
- If you frequently need to go back X hours, have you looked into flashback features? Google for "flashback table" and see if you like it
0
 

Author Comment

by:baberamin
Comment Utility
Thanks very much.
0
 

Author Comment

by:baberamin
Comment Utility
Thanks to all of you.
Oracle data pump is really great and saves lot of time.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now