OraFAQ Forum: Windows » create an oracle dump file

Home » Infrastructure » Windows » create an oracle dump file (Oracle Enterprise 11g, Windows 2008 R2)

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

create an oracle dump file [message #628898]

Sun, 30 November 2014 07:39

chillkroetle
Messages: 21
Registered: October 2014
Location: germany

Junior Member

Hi everybody,

i'd like to do an oracle dump export. With the exp-command the export shows me the failure EXP-00104: datatype (BINARY_DOUBLE) is not supported. So i found out that i should use the expdb command. For this case i generate the syntax expdp.exe elodb/<password>@<instancename> schemas=ELOAM directory=D:\ELO_Backup dumpfile=ELOAM.dmp logfile=ELOAM.log for a full export.

after the run from this syntax it shows me the error:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name D:\ELO_Backup is invalid

so is it correct that i have to do the following?

1. Login via sqlplus with my elodb account
2. Execute the following syntax:
2.1. grant create any directory to elodb;
2.2. create directory my_data_pump_directory as 'D:\ELO_BACKUP';

Report message to a moderator

Re: create an oracle dump file [message #628899 is a reply to message #628898]

Sun, 30 November 2014 08:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

DIRECTORY= is an Oracle Directory Object; no an OS directoty

http://docs.oracle.com/database/121/SUTIL/dp_export.htm#SUTIL840

scroll down to "DIRECTORY" & RTFM

Report message to a moderator

Re: create an oracle dump file [message #628907 is a reply to message #628899]

Sun, 30 November 2014 12:59

chillkroetle
Messages: 21
Registered: October 2014
Location: germany

Junior Member

Hi BlackSwan,

i read the ****ing manual. So i would do the following steps. Are they right?

1. Mapping my new directory D:\ELO_Backup to Oracle with the sys as sysdba User via SQLPlus
1.1 CREATE DIRECTORY dpump_dir1 AS 'D:\ELO_Backup';
2. Give the Import / Export User elodb permissions to write the dump-files to this directory over the sys as sysdba User via SQLPlus:
2.1 GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO elodb;
3. Export the Schema-User ELOAM with all the Tables and their contents to a dump-file with the User elodb:
3.1 expdp.exe elodb/<password>@<instancename> schemas=ELOAM directory=dpump_dir1 dumpfile=ELOAM.dmp logfile=ELOAM.log

am i right now?

Report message to a moderator

Re: create an oracle dump file [message #628909 is a reply to message #628907]

Sun, 30 November 2014 13:04

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

Senior Member
Account Moderator

Why don't you try?
If you did it you'd surely know the answer now.
More, even if you say yes it is OK, you have to do it to verify, haven't you?

Quote:

Give [...] over the sys as sysdba User via SQLPlus

This is wrong.
Read Read SYS is special.

Report message to a moderator

Re: create an oracle dump file [message #628910 is a reply to message #628909]

Sun, 30 November 2014 13:21

chillkroetle
Messages: 21
Registered: October 2014
Location: germany

Junior Member

Hi michel cadot,

because i'm still an oracle noob and a little bit scary.

Yes in the manual i read that the sys is special but only for doing the export isn't it? i only want to give my user elodb the permissions over the sys user. is this wrong? the user elodb has already the dba role.

Report message to a moderator

Re: create an oracle dump file [message #628911 is a reply to message #628910]

Sun, 30 November 2014 13:23

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

Senior Member
Account Moderator

Quote:

i read that the sys is special but only for doing the export isn't it?

Read the link I posted, it will safe your life.

Report message to a moderator

Re: create an oracle dump file [message #628912 is a reply to message #628911]

Sun, 30 November 2014 13:36

chillkroetle
Messages: 21
Registered: October 2014
Location: germany

Junior Member

hi Michel,

i read your link. Good to know and thanks a lot for that Smile

Smile

So do i understand that correctly that with the role dba my elodb account can do the steps above?

Report message to a moderator

Re: create an oracle dump file [message #628913 is a reply to message #628912]

Sun, 30 November 2014 13:57

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

Senior Member
Account Moderator

Yes, you have to use an account with role DBA (or,more precisely, with the CREATE ANY DIRECTORY privilege) to grant the privilege.

Report message to a moderator

Re: create an oracle dump file [message #628914 is a reply to message #628913]

Sun, 30 November 2014 14:07

chillkroetle
Messages: 21
Registered: October 2014
Location: germany

Junior Member

Hi Michel,

yes with my elodb i could do an successfull export know. Thanks a lot and sorry for my worriedness...

Report message to a moderator

Re: create an oracle dump file [message #628915 is a reply to message #628914]

Sun, 30 November 2014 15:14

chillkroetle
Messages: 21
Registered: October 2014
Location: germany

Junior Member

Hi Michel,

before i forget it. At the next step i would generate a test environment in which i would do an import. Is the expdb syntax above correct so when i have a test environment with the same oracle version and manual generated schema user eloam and its tablespaces that i can complete restore it with all its data?

Report message to a moderator

Re: create an oracle dump file [message #628916 is a reply to message #628915]

Sun, 30 November 2014 15:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

impdp help=yes

REMAP_SCHEMA
Objects from one schema are loaded into another schema.

You could use option above to load the schema export as new schema name into the existing database.

Report message to a moderator

Re: create an oracle dump file [message #628917 is a reply to message #628916]

Sun, 30 November 2014 16:02

chillkroetle
Messages: 21
Registered: October 2014
Location: germany

Junior Member

Hi BlackSwan,

ah okay thanks for that. One thing i don't understand at all. If my expdb syntax above is correct and all data is exported for which situation do i need the full mode? Does that mean it exports my whole database so i don't need the option "schemas="?

Report message to a moderator

Re: create an oracle dump file [message #628918 is a reply to message #628917]

Sun, 30 November 2014 16:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

chillkroetle wrote on Sun, 30 November 2014 14:02

Hi BlackSwan,

ah okay thanks for that. One thing i don't understand at all. If my expdb syntax above is correct and all data is exported for which situation do i need the full mode? Does that mean it exports my whole database so i don't need the option "schemas="?

This question make it OBVIOUS that you did not actually read the URL I previously provided you;
since it goes into detail about the various export/import modes.

Why should we post here details that have already been documented?

Report message to a moderator

Re: create an oracle dump file [message #629007 is a reply to message #628915]

Tue, 02 December 2014 02:59

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

As Michel said: you should not use SYS. To give you an idea of how dangerous it is, if you did an import (impdp) using SYS, you could overwrite your data dictionary (objects owned by SYS) which would render your database useless.

Report message to a moderator

Re: create an oracle dump file [message #629008 is a reply to message #629007]

Tue, 02 December 2014 03:20

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Are you sure about that (SYS being overwritten by imported data)?

I don't do that regularly; as a developer, I occasionally export a schema or a few tables. However, I *think* that whatever you do - for example, perform full export using SYS credentials and then perform full import using SYS credentials on another database - you won't overwrite SYS. It just wouldn't make sense.

I suppose DBAs here know exactly so ... hopefully, someone will explain it.

Report message to a moderator

Re: create an oracle dump file [message #629010 is a reply to message #629008]

Tue, 02 December 2014 03:46

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Well, I could work on a system all day as root and not accidentally overwrite data or restart the server. However, if I don't do it as root, I definitely can't Smile

Smile

There's a note on Metalink about SYSDBA and Datapump: 277237.1

Report message to a moderator

Re: create an oracle dump file [message #629012 is a reply to message #629010]

Tue, 02 December 2014 03:53

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

SYS export/import overwrites data dictionary[ 1 vote ]
1.	Yes	0 / 0%
2.	No	0 / 0%
3.	Maybe (i.e. it depends)	1 / 100%

No objections to your last message (#629010). However, it still doesn't explain whether you can or can not overwrite data dictionary using export/import. You said YES, I said NO. What is the truth?

[Updated poll after seeing John's message]

[Updated on: Tue, 02 December 2014 03:55]

Report message to a moderator

Re: create an oracle dump file [message #629013 is a reply to message #629008]

Tue, 02 December 2014 03:53

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

Senior Member

Actually, this depends on release. Earlier than 12.x, a full export wouldn't export objects owner by SYS, but in 12.x you can - if you use the rather oddly named transportable=always switch. That will include user-created objects in SYS and related schemas, but not system created objects such as the data dictionary tables. I think the distinction is made depending on the value of the oracle_maintained column in dba_objects.

Report message to a moderator

Re: create an oracle dump file [message #629014 is a reply to message #629012]

Tue, 02 December 2014 03:59

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

Senior Member
Account Moderator

http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL826

Quote:

Several system schemas cannot be exported because they are not user schemas; they contain Oracle-managed data and metadata. Examples of system schemas that are not exported include SYS, ORDSYS, and MDSYS.

Report message to a moderator

Re: create an oracle dump file [message #629015 is a reply to message #629014]

Tue, 02 December 2014 04:07

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Same in 12.1, apparently. Apologies.

http://docs.oracle.com/database/121/SUTIL/dp_export.htm#SUTIL826

Report message to a moderator

Previous Topic:	OO4O-Query on text file
Next Topic:	OracleDBConsole<Instance> don't start after install a second database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 13:01:49 CST 2025