[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

Import schema as new name

I have an Oracle 10g database on Windows running in no-archive log mode.  I am being asked to restore a schema as a different schema name from Monday (1/4/2010).  The only way I can think of to do this is to restore the database to a point in time, export the schema, restore the database to its previous state then import the schema.  I am not even sure if it is possible to restore the schema as a new name.  Please give me the syntax for any commands, this is new to me!  Thanks!
0
PsychoDazey
Asked:
PsychoDazey
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
johnsoneSenior Oracle DBACommented:
I would restore the backup from 1/4 on a different machine.  If you can identify the tablespaces involved, you should only have to restore those tablespaces and can offline drop any others.  This could save on space needed for the restore.  As this is a temporary database there is no need for everything.

Then you can export the user.  On the import, you specify the FROMUSER and TOUSER parameters to change the owner.
0
 
PsychoDazeyAuthor Commented:
Thanks johnsone;
What is the syntax (using RMAN) to only restore the tablespaces I need?
0
 
x77Commented:
Use export / inport utility.
You can export  an schema (owner), then import (IMP)  with parameters Fromuser=OldSchema ToUser=NewSchema
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

 
raju1105Commented:
Hi,

If you take daily export backups then your job is simply done. Restore the backup to the new machine and import only the required schema after you create a new database on the sever.

imp system/manager fromuser=abc touser=xyz file=abc.dmp log=abc.dmp statistics=n ignore=y
0
 
johnsoneSenior Oracle DBACommented:
You would have to determine which file numbers were involved in the tablespace and restore only those files.  You may have to do the recovery yourself as I'm not sure that rman can handle that type of recovery itself.

I don't have access to a server I can get you a script from at the moment.  I'll see if I can make up a sample.
0
 
sventhanCommented:
Have you thought about remap schema?
impdp system/tiger REMAP_SCHEMA=SCOTT:JKOOPMANN DIRECTORY=datapump DUMPFILE=dept.dmp LOGFILE=dept.log
http://www.orafaq.com/node/74 
0
 
PsychoDazeyAuthor Commented:
Thanks to all for your input.  I awarded johnsone partial points because he was the first to mention the fromuser/touser.  But I also read the article from sventhan and ended up using parts from both.  Thanks again!
0
 
sventhanCommented:
glad we could help.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now