Archive for the ‘Export/Import’ tag
Today I was running export of an Oracle 126.96.36.199 database. The export completed but with an ORA-600 error:
EXP-00008: ORACLE error 600 encountered ORA-00600: internal error code, arguments: [xsoptloc2], , , , , , ,  ORA-06512: in "SYS.DBMS_AW", line 347 ORA-06512: in "SYS.DBMS_AW", line 470 ORA-06512: in "SYS.DBMS_AW_EXP", line 270 ORA-06512: in line 1 EXP-00083: The previous problem occurred when calling SYS.DBMS_AW_EXP.schema_info_exp
I googled a bit and found that the problem is with applying some patchset. Then metalink confirmed the same. Somebody tried applying a patch to upgrade it to 188.8.131.52 but didn’t perform all the steps (missed post installation steps, to be precise). Metalink Note 300849.1 covers the issue and also gives the solution. In nutshell startup the database with startup migrate and run catpatch.sql.
Well, a simple method to import(export) directly to(from) compressed files using pipes. Its for Unix based systems only, as I am not aware of any pipe type functionality in Windows. The biggest advantage is that you can save lots of space as uncompressing a file makes it almost 5 times or more. (Suppose you are uncompressing a file of 20 GB, it will make 100 GB) As a newbie I faced this problem, so thought about writing a post.
Lets talk about export first. The method used is that create a pipe, write to a pipe(ie the file in exp command is the pipe we created), side by side read the contents of pipe, compress (in the background) and redirect to a file. Here is the script that achieves this:
export ORACLE_SID=MYDB rm -f ?/myexport.pipe mkfifo ?/myexport.pipe cat ?/myexport.pipe |compress > ?/myexport.dmp.Z &amp; sleep 5 exp file=?/myexport.pipe full=Y log=myexport.log
Same way for import, we create a pipe, zcat from the dmp.Z file, redirect it to the pipe and then read from pipe:
export ORACLE_SID=MYDB rm -f ?/myimport.pipe mkfifo ?/myimport.pipe zcat ?/myexport.dmp.Z > ?/myimport.pipe &amp; sleep 5 imp file=myimport.pipe full=Y show=Y log=?/myimport.log
In case there is any issue with the script, do let me know
Update: If you are on Wintel, you can directly use a compressed folder as an export target. No need to create a pipe as the file system will automatically do it for you. ( Thanks Noons for the tip)
If you are using gunzip:
export ORACLE_SID=MYDB rm -f exp.pipe mknod exp.pipe gzip < exp.pipe > T1.dmp.gz &amp; exp file=exp.pipe full=Y log=myexport.log
export ORACLE_SID=MYDB rm -f imp.pipe mknod imp.pipe gzip < T1.dmp.gz > imp.pipe &amp; imp file=imp.pipe full=Y log=myimport.log
Update: I came across an article that discusses few ways to achieve the same on Windows. Check it here.