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

x
?
Solved

ORacle 9i  table backup

Posted on 2011-02-25
8
Medium Priority
?
667 Views
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.

Thanks@
0
Comment
Question by:D-pk
[X]
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
8 Comments
 
LVL 77

Expert Comment

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

What OS?
0
 

Author Comment

by:D-pk
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)

Thanks@
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1336 total points
ID: 34981417
You need to set up a large file filesystem:
http://en.wikipedia.org/wiki/Large_file_support

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

http://www.linuxjournal.com/article/2156
http://linux.about.com/library/cmd/blcmdl1_split.htm

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
0
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...
0
 

Accepted Solution

by:
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@198.2.34.122 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
descriptor
EXP-00000: Export terminated unsuccessfully

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

Thanks@
0
 
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
descriptor

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.
0
 
LVL 3

Assisted Solution

by:gopisera
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> =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
   )
 (CONNECT_DATA =
   (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.  :)


0
 

Author Closing Comment

by:D-pk
ID: 35042618
Thanks
0

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