?
Solved

fast recovery of an oracle schema

Posted on 2011-03-14
9
Medium Priority
?
514 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 664 total points
ID: 35132333
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 1336 total points
ID: 35132340
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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 664 total points
ID: 35132389
"You really can't recover a tablespace."

I guess I stand corrected.  I should stop saying "can't".  I'm often corrected...
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 11

Assisted Solution

by:Akenathon
Akenathon earned 1336 total points
ID: 35132524
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
 
LVL 11

Accepted Solution

by:
Akenathon earned 1336 total points
ID: 35132971
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
ID: 35144708
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 1336 total points
ID: 35149616
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
ID: 35174227
Thanks very much.
0
 

Author Comment

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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

862 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