Solved

fast recovery of an oracle schema

Posted on 2011-03-14
9
508 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
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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle 11g 23 81
how to trim oracle sql sentence in unix 17 53
AWS RDS Backups? 3 40
constraint check 2 40
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Read about achieving the basic levels of HRIS security in the workplace.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

770 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