Home » RDBMS Server » Server Administration » Duplicate database using dpexp and dpimp (Oracle 10.2.0.4, Windows)
Duplicate database using dpexp and dpimp [message #573887] Thu, 03 January 2013 19:31 Go to next message
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
Re: Duplicate database using dpexp and dpimp [message #573893 is a reply to message #573887] Thu, 03 January 2013 20:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
1) no better method

2) no issues

3) expdp help=yes & impdp help=yes
Re: Duplicate database using dpexp and dpimp [message #573900 is a reply to message #573887] Fri, 04 January 2013 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Duplicate database using dpexp and dpimp [message #573902 is a reply to message #573887] Fri, 04 January 2013 01:53 Go to previous messageGo to next message
John Watson
Messages: 8922
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
Re: Duplicate database using dpexp and dpimp [message #573915 is a reply to message #573902] Fri, 04 January 2013 04:34 Go to previous messageGo to next message
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
Re: Duplicate database using dpexp and dpimp [message #573918 is a reply to message #573915] Fri, 04 January 2013 04:36 Go to previous messageGo to next message
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.
Re: Duplicate database using dpexp and dpimp [message #573923 is a reply to message #573918] Fri, 04 January 2013 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Duplicate database using dpexp and dpimp [message #574196 is a reply to message #573923] Mon, 07 January 2013 19:40 Go to previous messageGo to next message
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
Re: Duplicate database using dpexp and dpimp [message #574197 is a reply to message #574196] Mon, 07 January 2013 19:52 Go to previous messageGo to next message
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
Re: Duplicate database using dpexp and dpimp [message #574218 is a reply to message #574196] Tue, 08 January 2013 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
4) Yes.

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

Regards
Michel
Re: Duplicate database using dpexp and dpimp [message #574911 is a reply to message #573887] Wed, 16 January 2013 13:31 Go to previous messageGo to next message
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
Re: Duplicate database using dpexp and dpimp [message #575153 is a reply to message #574911] Sun, 20 January 2013 06:12 Go to previous message
learnit
Messages: 5
Registered: January 2013
Location: USA
Junior Member
Thanks everyone for reply.
Previous Topic: temp tables - readonly user
Next Topic: migration of database
Goto Forum:
  


Current Time: Thu Mar 28 20:09:14 CDT 2024