Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


ORacle 9i  table backup

Posted on 2011-02-25
Medium Priority
Last Modified: 2012-06-21
How can I a backup tables which are big, that is more than 125 GB?
I tried using the exp utility without any success.
Can somebody give me steps to do this and the imp steps too..
Note: The export has to be from a 9i database and import will be done in 11g database.

Question by:D-pk
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34981308
Why did exp fail?  export file to large?

What OS?

Author Comment

ID: 34981343
These were the errors I got..

EXP-00002: error in writing to export file
EXP-00002: error in writing to export file
EXP-00000: Export terminated unsuccessfully

I am able to do smaller tables, but the tables which 125GB, it gives me the above errors.
OS is Linux. (I gave uname -a, if that is correct)

LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1336 total points
ID: 34981417
You need to set up a large file filesystem:

Create a named pipe, export using it then split the output:

try the following (I don't have access to fully test this):

mkfifo pipe

then: exp file=pipe tables=(largetable)

from another window:
split –bytes=1000m pipe myExpPiece
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1336 total points
ID: 34981430
Before you do all that:  Check the obvious and see if you have enough free space in the filesystem...

Accepted Solution

D-pk earned 0 total points
ID: 34981575
what if I export the dump in to my local PC (Windows XP), does the windows system has that restriction?

exp USERID=usr/pwd TABLES=big_table_125gb  FILE=C:\TABLES_DUMPS\big_table_125gb.dmp

I tried this but its still trying to create it in the unix server itself...
Lets say if I try in the command prompt..
For ex:
exp USERID=usrname/pwd@ TABLES=big_table_125gb FILE=C:\TABLES_DUMPS\big_table_125gb.dmp
it gives me errors like:

EXP-00056: ORACLE error 12514 encountered
ORA-12514: TNS:listener does not currently know of service requested in connect
EXP-00000: Export terminated unsuccessfully

Do I have to give any port numbers or any other parameters to work?

LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34981601
>>does the windows system has that restriction?

It shouldn't but you have network lag time built into the time.

>>ORA-12514: TNS:listener does not currently know of service requested in connect

You need to use a database alias from your tnsnames.ora file.  If you don't know how to configure your client, it will need to be a separate question.

Assisted Solution

gopisera earned 664 total points
ID: 34985907
Adding to above

Add the tns entry  in your oracle home of windows and then try using with that tns entry

<addressname> =
     (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
   (SERVICE_NAME = <sid>)

exp USERID=usr/pwd@<addressname>   TABLES=big_table_125gb  FILE=C:\TABLES_DUMPS\big_table_125gb.dmp

Make sure that the exp  profile is set to 11.  Since,  You are importing into a 11g database.  :)


Author Closing Comment

ID: 35042618

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question