Solved

fast recovery of an oracle schema

Posted on 2011-03-14
9
511 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 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 334 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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 11

Assisted Solution

by:Akenathon
Akenathon earned 334 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 334 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 334 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

696 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