Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Meysam.

Asked: May 30, 2010 - 2:02 am UTC

Last updated: June 09, 2010 - 7:49 am UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hello
i have a problem in my backup strategy.
i use exp command to backup all schema ,tables ,data and other objects like views ... .
but my problem is in other objects.
i can restore one or some tables with command:
imp user/pass file=exp.dmp fromuser=mySchema tables=table1,table2,... toUser=mySchema log= imp.log
feedback=1000 statistics=none

but there are 2 problems:

1) sometimes my restored table has alot of data that needs a lot of space and i don't want to restore it
in its schema (for my some policies in database)
i use this command:
imp user/pass file=exp.dmp fromuser=mySchema tables=table1 toUser=newSchema log=imp.log
feedback=1000 statistics=none
but again my table moves into new schema and uses main schema tablespace!
i have to move table1 tablespace to new schema that sometimes it's not a good solution because i can't reduce
space of datafiles in main tablespace.
how can i restore table in my selected schema and TABLESPACE?

2) some schemas like APPS has a lot of objects like views,functions,packages and,etc and they don't have tables.
lets think that one or more of my for example views suddenly dropped by user mistakes and i must to restore
them.
i can't restore only these views from all objects as i know and i have to restore all objects in that
schema in another schema then move dropped views in main schema,and this is not possible
because of number of objects in exp file and time and space needed to restore sometimes its not reasonable.
how can i solve this problem and restore some views , packages , functions, procedures ... without restoring
all objects (including tables)?

i hope i could explain my problems clearly.
best regard

and Tom said...

i have a problem in my backup strategy.
i use exp command to backup all schema ,tables ,data and other objects like views ... .

the problem is - that is NOT a backup strategy - export is NOT a backup tool. RMAN is, user managed backups are, export is definitely NOT.


You want to get into RMAN and read up on it. You can restore tablespaces from rman easily, quickly - without a fraction of the work that import would take.

import is not a very flexible tool - you won't be able to do much with it, it is what it is. and it is NOT a backup/recovery tool.


views suddenly dropped by user mistakes....

You should have developed objects like that in source code control - first - the schema containing application objects should have ZERO access to it - so this mistake should *never happen*. Second - even if it did - I'd be looking to source code control to reinstall part of the application - not backups at all.


And import isn't going to do what you ask of it - datapump will have more features, but you get the same answer from me which is "you are doing this wrong, please do proper backups"

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Import

Meysam Khayatan, June 02, 2010 - 12:09 am UTC

thanks for your reply
but i think i couldn't mean correctly
i know rman tools for backup /recovery
but rman work physically and i couldn't restore one table's data from it and i use it for for example my disaster backup.
i mean i have to restore for example 10000000 record from my dump file and its get alot of space
i have data in orginaly table but some data lost and my progreammer should check data and insert lost data into main table.

now please tell me if i'm wrong: can i restore data for one table from rman backup set?
i think export do it! but main question is as you know export although i set default tablespace of new schema another tablespace, still imp command imports data in new table but in previous tablespace.

about second question:
you are right but i made an example only
for a new example our developer change a view and after some time know that it's wrong.
i know they should save their source but maybe sometime forget it.
as a dba now i should restore that view from my export dump file contains for about 2000 view !!
my ideal and best solution is only restore that view ,not all views.
can i do this job?
somebody told me use: imp show=y
is this true?
i test it but i think it makes some syntax error like useing " in the output script.
i know oracle solved these problem in 10g but i must solve these in 9i Vr.

please guide me to solve these
Tom Kyte
June 08, 2010 - 9:34 am UTC

... but rman work physically and i couldn't restore one table's data from it and i use it for for example my disaster backup. ...

TSPITR (tablespace point in time recovery)

and then either accept that entire tablespace OR just export (datapump) out what you need. You do not want to use export.


... now please tell me if i'm wrong: can i restore data for one table from rman backup set? ...


Can I get the data for any table in my database if I have an RMAN backup? Yes, absolutely, 100%, definitely.


... i know they should save their source but maybe sometime forget it. ...

fix that, that is a major problem - bigger than any other problem you have, the biggest problem ever...



¿Target?

alfrefo, June 04, 2010 - 2:00 pm UTC

Your target is
1) Have a backup strategy
OR
2) Have the posibility of recover only one table.

If both, then you must have two different estrategies.

You know what is it flashback ? (db 10 and db 11)
Tom Kyte
June 09, 2010 - 7:49 am UTC

flashback table would work theoretically 5 days into the past, but practically only a few hours as it relies on UNDO being around and no one keeps 5 days of UNDO on disk...

a true backup from an archivelog mode database can be used to recover any bit of data, of any size, at any time, to any point in time.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.