OraFAQ Forum: Server Administration » Duplicate database using dpexp and dpimp

Home » RDBMS Server » Server Administration » Duplicate database using dpexp and dpimp (Oracle 10.2.0.4, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Duplicate database using dpexp and dpimp [message #573887]

Thu, 03 January 2013 19:31

learnit
Messages: 5
Registered: January 2013
Location: USA

Junior Member

I have Oracle 10.2.0.4 database on HP-UX. I want to move this to same version of database on windows on different server.I want everything to be same on both databases on different servers(names of databases can change)

I installed oracle on windows box and created a database 10.2.0.4. Few questions

1) What is the best method to recreate complete database on another machine.In my case move 10.2.0.4 database on HP-UX to 10.2.0.4 database on windows. Is there a better method other than dpexp/dpimp?

2) When I created database 10.2.0.4 on windows it created sys, system and other default users. If I get a full export of 10.2.0.4 database on HP-UX it also contains sys, system and other default users. My question is, will there be any issue when I import full mode into 10.2.0.4 database on windows?

3) If I do with dpexp/dpimp what are the best parameters to be used?

Thanks

Report message to a moderator

Re: Duplicate database using dpexp and dpimp [message #573893 is a reply to message #573887]

Thu, 03 January 2013 20:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

1) no better method

2) no issues

3) expdp help=yes & impdp help=yes

Report message to a moderator

Re: Duplicate database using dpexp and dpimp [message #573900 is a reply to message #573887]

Fri, 04 January 2013 00:57

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1) RMAN backup/convert/restore
2) SYS is never exported but I'd only export custom users not full
3) export only your users

Regards
Michel

Report message to a moderator

Re: Duplicate database using dpexp and dpimp [message #573902 is a reply to message #573887]

Fri, 04 January 2013 01:53

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

The transportable database facility might be the way to go, http://docs.oracle.com/cd/B19306_01/backup.102/b14191/dbxptrn.htm#CHDCFFDI

Report message to a moderator

Re: Duplicate database using dpexp and dpimp [message #573915 is a reply to message #573902]

Fri, 04 January 2013 04:34

learnit
Messages: 5
Registered: January 2013
Location: USA

Junior Member

Thanks Black,Michel,John for answers.

1) RMAN backup/convert/restore. I understand backup and restore. Not sure what is convert and why needed here.

2) When I dpexport what user(sys,system,another user) account is recommended? I agree that user need full_database_export privileges.

3) What user is recommended again while dpimport?

4) Can I safely ignore users created by default during database creation for dpexport full?

Thanks

Report message to a moderator

Re: Duplicate database using dpexp and dpimp [message #573918 is a reply to message #573915]

Fri, 04 January 2013 04:36

learnit
Messages: 5
Registered: January 2013
Location: USA

Junior Member

Sorry. I might have hit submit button quickly. Please ignore my question 1). Being on different platform if endian format is different we have to convert.

Report message to a moderator

Re: Duplicate database using dpexp and dpimp [message #573923 is a reply to message #573918]

Fri, 04 January 2013 05:39

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

2) Yes.
3) Because it requires you analyze your database and explicitly export/import what is needed for your application(s). It is a cleaner and safer way to do it than to blindly export/import full database and then you are sure you do not import what you don't want. More work but, once again, safer and cleaner.
4) See 3: analysis step.

Regards
Michel

Report message to a moderator

Re: Duplicate database using dpexp and dpimp [message #574196 is a reply to message #573923]

Mon, 07 January 2013 19:40

learnit
Messages: 5
Registered: January 2013
Location: USA

Junior Member

Michel,

Sorry.I am not clear on your answers. Here are my questions

2) When I dpexport what user(sys,system,another user) account is recommended? I agree that user need full_database_export privileges. Do you recommend sys or system account for full export?

3) What user is recommended again while dpimport? Do you recommend sys or system account for full export?

4) Can I safely ignore users created by default during database creation for dpexport full? My understanding from your last post was to select schemas to export rather than exporting everything. Correct me if I am wrong.

Thanks

Report message to a moderator

Re: Duplicate database using dpexp and dpimp [message #574197 is a reply to message #574196]

Mon, 07 January 2013 19:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

are you unwilling or incapable to Read The Fine Manual yourself; regarding SYS & SYSTEM users?

http://docs.oracle.com/cd/E11882_01/server.112/e10897/users_secure.htm#ADMQS12003

SYS user should only be used to apply patches & version upgrades

Report message to a moderator

Re: Duplicate database using dpexp and dpimp [message #574218 is a reply to message #574196]

Tue, 08 January 2013 01:30

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

4) Yes.

The rest was already answered: export YOUR users and only YOUR users.

Regards
Michel

Report message to a moderator

Re: Duplicate database using dpexp and dpimp [message #574911 is a reply to message #573887]

Wed, 16 January 2013 13:31

alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California

Senior Member

I use the following script to build the expdp commands. If an expdp command fails or an impdp command fails, I fall back on the old exp/imp commands. the expdp and impdp are way faster. I usually do NOT run in parallel in 10.2.0.4 because I get more failures.

ENWEBP1P > @cr8_expdp_by_SCHEMAS_ONE_AT_A_TIME.sql
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8  -- UNIX
NLS_LANG=AMERICAN_AMERICA.AL32UTF8  -- MSDOS
grant create any directory to ENWEBP1P;
create or replace directory MY_DIR as '/content_services/alan';
expdp  system/manager schemas=ALFC directory=MY_DIR dumpfile=ALFC.dmp logfile=expdpALFC.log
expdp  system/manager schemas=BATCH_NFL_USER directory=MY_DIR dumpfile=BATCH_NFL_USER.dmp logfile=expdpBATCH_NFL_USER.log
expdp  system/manager schemas=CONTENT directory=MY_DIR dumpfile=CONTENT.dmp logfile=expdpCONTENT.log
expdp  system/manager schemas=DBLINK_USER directory=MY_DIR dumpfile=DBLINK_USER.dmp logfile=expdpDBLINK_USER.log
expdp  system/manager schemas=ENWEBP1P directory=MY_DIR dumpfile=ENWEBP1P.dmp logfile=expdpENWEBP1P.log
expdp  system/manager schemas=ENWEBP2P directory=MY_DIR dumpfile=ENWEBP2P.dmp logfile=expdpENWEBP2P.log
expdp  system/manager schemas=ENWEBP3P directory=MY_DIR dumpfile=ENWEBP3P.dmp logfile=expdpENWEBP3P.log
expdp  system/manager schemas=ENWEBP4P directory=MY_DIR dumpfile=ENWEBP4P.dmp logfile=expdpENWEBP4P.log
expdp  system/manager schemas=EXTERNAL_CONSOLE_OWNER directory=MY_DIR dumpfile=EXTERNAL_CONSOLE_OWNER.dmp logfile=expdpEXTERNAL_CONSOLE_OWNER.log
expdp  system/manager schemas=EXTERNAL_CONSOLE_USER directory=MY_DIR dumpfile=EXTERNAL_CONSOLE_USER.dmp logfile=expdpEXTERNAL_CONSOLE_USER.log
expdp  system/manager schemas=FEEDS directory=MY_DIR dumpfile=FEEDS.dmp logfile=expdpFEEDS.log
expdp  system/manager schemas=FEEDS_USER directory=MY_DIR dumpfile=FEEDS_USER.dmp logfile=expdpFEEDS_USER.log
expdp  system/manager schemas=GAMECENTER directory=MY_DIR dumpfile=GAMECENTER.dmp logfile=expdpGAMECENTER.log
expdp  system/manager schemas=LEAGUE directory=MY_DIR dumpfile=LEAGUE.dmp logfile=expdpLEAGUE.log
expdp  system/manager schemas=MGMT_READ_ONLY directory=MY_DIR dumpfile=MGMT_READ_ONLY.dmp logfile=expdpMGMT_READ_ONLY.log
expdp  system/manager schemas=MONITOR directory=MY_DIR dumpfile=MONITOR.dmp logfile=expdpMONITOR.log
expdp  system/manager schemas=NFL_READ directory=MY_DIR dumpfile=NFL_READ.dmp logfile=expdpNFL_READ.log
expdp  system/manager schemas=NWEBP_RO directory=MY_DIR dumpfile=NWEBP_RO.dmp logfile=expdpNWEBP_RO.log
expdp  system/manager schemas=POLLS directory=MY_DIR dumpfile=POLLS.dmp logfile=expdpPOLLS.log
expdp  system/manager schemas=PROFILE directory=MY_DIR dumpfile=PROFILE.dmp logfile=expdpPROFILE.log
expdp  system/manager schemas=SITE directory=MY_DIR dumpfile=SITE.dmp logfile=expdpSITE.log
expdp  system/manager schemas=SITE_DEV directory=MY_DIR dumpfile=SITE_DEV.dmp logfile=expdpSITE_DEV.log
expdp  system/manager schemas=SITE_QA directory=MY_DIR dumpfile=SITE_QA.dmp logfile=expdpSITE_QA.log
expdp  system/manager schemas=SITE_USER directory=MY_DIR dumpfile=SITE_USER.dmp logfile=expdpSITE_USER.log
ENWEBP1P > list
  1  select 'expdp  system/manager schemas='||username||
  2  ' directory=MY_DIR dumpfile='||username||'.dmp logfile=expdp'
  3  ||username||'.log'
  4  from dba_users
  5  where username not in (
  6  'SYSTEM','SYS','OUTLN','DIP','TSMSYS','DBSNMP','WMSYS','XDB','EXFSYS','ORDPLUGINS','ORDSYS','SI_INFORMTN_SCHEMA','MDSYS',
  7  'CTXSYS','RMAN','SMODBUSER','ENWEBP2','ENWEBP1','ENWEBP3','ORACLE_OCM','YSUN','DBO','ACTIVEMQ',
  8  'FLOWS_FILES','APEX_030200','OWBSYS_AUDIT','ANONYMOUS','OWBSYS','SYSMAN','APEX_PUBLIC_USER','SCOTT',
  9  'APPQOSSYS','DMSYS','MDDATA','MGMT_VIEW','OLAPSYS','ORDDATA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','IPSOFT')
 10* order by username

Report message to a moderator

Re: Duplicate database using dpexp and dpimp [message #575153 is a reply to message #574911]

Sun, 20 January 2013 06:12

learnit
Messages: 5
Registered: January 2013
Location: USA

Junior Member

Thanks everyone for reply.

Report message to a moderator

Previous Topic:	temp tables - readonly user
Next Topic:	migration of database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 00:51:40 CST 2024