Home » Infrastructure » Windows » create an oracle dump file (Oracle Enterprise 11g, Windows 2008 R2)
create an oracle dump file [message #628898] Sun, 30 November 2014 07:39 Go to next message
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';

Re: create an oracle dump file [message #628899 is a reply to message #628898] Sun, 30 November 2014 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 26756
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
Re: create an oracle dump file [message #628907 is a reply to message #628899] Sun, 30 November 2014 12:59 Go to previous messageGo to next message
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?
Re: create an oracle dump file [message #628909 is a reply to message #628907] Sun, 30 November 2014 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: create an oracle dump file [message #628910 is a reply to message #628909] Sun, 30 November 2014 13:21 Go to previous messageGo to next message
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.
Re: create an oracle dump file [message #628911 is a reply to message #628910] Sun, 30 November 2014 13:23 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: create an oracle dump file [message #628912 is a reply to message #628911] Sun, 30 November 2014 13:36 Go to previous messageGo to next message
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 So do i understand that correctly that with the role dba my elodb account can do the steps above?
Re: create an oracle dump file [message #628913 is a reply to message #628912] Sun, 30 November 2014 13:57 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
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.
Re: create an oracle dump file [message #628914 is a reply to message #628913] Sun, 30 November 2014 14:07 Go to previous messageGo to next message
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...
Re: create an oracle dump file [message #628915 is a reply to message #628914] Sun, 30 November 2014 15:14 Go to previous messageGo to next message
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?
Re: create an oracle dump file [message #628916 is a reply to message #628915] Sun, 30 November 2014 15:22 Go to previous messageGo to next message
BlackSwan
Messages: 26756
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.
Re: create an oracle dump file [message #628917 is a reply to message #628916] Sun, 30 November 2014 16:02 Go to previous messageGo to next message
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="?
Re: create an oracle dump file [message #628918 is a reply to message #628917] Sun, 30 November 2014 16:12 Go to previous messageGo to next message
BlackSwan
Messages: 26756
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?
Re: create an oracle dump file [message #629007 is a reply to message #628915] Tue, 02 December 2014 02:59 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, 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.
Re: create an oracle dump file [message #629008 is a reply to message #629007] Tue, 02 December 2014 03:20 Go to previous messageGo to next message
Littlefoot
Messages: 21623
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.
Re: create an oracle dump file [message #629010 is a reply to message #629008] Tue, 02 December 2014 03:46 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, 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

There's a note on Metalink about SYSDBA and Datapump: 277237.1
Re: create an oracle dump file [message #629012 is a reply to message #629010] Tue, 02 December 2014 03:53 Go to previous messageGo to next message
Littlefoot
Messages: 21623
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 Go to previous messageGo to next message
John Watson
Messages: 8427
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.
Re: create an oracle dump file [message #629014 is a reply to message #629012] Tue, 02 December 2014 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: create an oracle dump file [message #629015 is a reply to message #629014] Tue, 02 December 2014 04:07 Go to previous message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Same in 12.1, apparently. Apologies.

http://docs.oracle.com/database/121/SUTIL/dp_export.htm#SUTIL826
Previous Topic: OO4O-Query on text file
Next Topic: OracleDBConsole<Instance> don't start after install a second database
Goto Forum:
  


Current Time: Sun Nov 29 19:20:09 CST 2020