With the help of Stack Overflow, I've been able to export a dump file of my database from my local machine. The command I used is as follows:
host expdp tkcsowner/tkcsowner@xe version=10.2 schemas=tkcsowner dumpfile=tnrg.dmp logfile=tnrg.log
Now, my local machine has the OS Windows 7, 32-bit. Hardly a server. It's got Oracle 11g. I want to transfer it to another machine, the test server, running Linux. It has Oracle 10g.
I am in no way a Linux / Unix expert, but I do have some instructions left for me by the previous person who handled such.
First, I change privileges to root user via 'su -' - No problems there.
Log in as 'sqlplus /nolog', and then 'connect sys/sys@xe as dba' - No problems there, either.
I created a logical dump directory (not sure if this step is needed, but I did it anyway):
create or replace directory dumpdir as 'usr/lib/oracle/xe/app/oracle/admin/XE/dpdump';
Done, no problems.
So I take it TNRG.dmp and tnrg.log should be inside that directory. Unfortunately, it could not be copied, for some reason. Access denied. I figured I should log out, log in as root, and copy the stuff from there. It worked, but just to be safe, I logged out of the root, logged back in as my normal user, and did everything above again. D'oh.
Finally, with all the stuff in place, now comes the time to import the .dmp and .log. Huzzah!
impdp tkcsowner/tkcsowner@xe schemas=tkcsowner dumpfile=TNRG.dmp logfile=tnrg.log
Lo and behold, it asks for a username and password. Is it because tkcsowners does not exist on the 10g database? Anyway, I put in 'system' for both. It continued, but warning bells already set off in my head.
Suddenly:
- Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
- ORA-39002: invalid operation
- ORA-39070: unable to open the log file.
- ORA-29283: invalid file operation
- ORA-06512: at "SYS.UTL_FILE", line 475
- ORA-29283: invalid file operation
At which point, I'm not sure how to proceed. I went into the directory via the command line, and ls -l'ed the contents, showing that both the .dmp and .log have three rwx's, for root. What I have yet to try was to run the entire operation while logged in as root, but I'm not sure how that would change anything.
The directory that your
dumpdir
database directory object points to needs to be a valid existing directory - at least by the time you use it, it won't check or complain when you create the object - and it needs to be readable and writable by the user that Oracle is running under, which is usuallyoracle
.Your initial directory creation had
'usr/lib/oracle/...
rather than'/usr/lib/oracle/...
, but even with that corrected the directory might not be usable by theoracle
account. Since you created the directory asroot
, it is probably still owned byroot:root
and with permissions 700 (if you dols -ld /usr/lib/oracle/xe/app/oracle/admin/XE/dpdump
that will show asdrwx------
).You need to change that to be owned by Oracle, using the correct owner and group - that's probably
oracle:dba
ororacle:oinstall
, but check the owner of theXE
directory. And then change the ownership of the directory and the files you copied into it:and set the directory permissions to a suitable level; if you don't want anyone else to create or modify files, but you don't mind them seeing what's there, then something like:
If you want to be able to copy your
.dmp
file in as yourself (notroot
ororacle
) and you aren't in thedba
group then make it 777. You said the files you copied are 777, which is a little odd as they aren't executable, and could currently be removed by anyone; again to make them just readable:You don't need the export log from the other system though, just the dump file itself. The
logfile
parameter forimpdp
will create a log of the import process; since you used the same file name it will overwrite the export log you copied across. THat probably doesn't matter since you still have the original, but something to watch for in the future. It does mean the existing log file has to be writable byoracle
though.You also need to make sure the Oracle owner has appropriate access to the whole directory tree, but it seems likely that they already own
XE
so I don't think that's an issue here. You shouldn't really need to do any of this asroot
. If you don't have theoracle
password you cansu
to the account fromroot
anyway, which remove the need to manually change ownership later.