How To Export/Import Via A Named Pipe

BACKGROUND & OVERVIEW

The following guide explains how to perform an export/import via a named pipe.

ASSUMPTIONS & PRE_REQUISITES

The following information is used for the puposes of the example shown in the Step-By-Step Guide.

  • The tasks described are performed by a qualified DBA.
  • Both databases are open and functioning and the database users exist.
  • SQL*Net connectivity between the two databases is available and working.
  • All users have the appropriate privileges to perform the tasks described.
  • The tasks are performed on a Unix based environment.
  • The tables to be exported and imported are mytab1 and mytab2.
  • The tables to be exported reside on host myhost1 in database MYSID1 under schema myuser1.
  • The tables to be imported reside on host myhost2 in database MYSID2 under schema myuser2.

STEP-BY-STEP GUIDE

1. On myhost1 create the export parfile - myexport1.par.

Example

userid=myuser1/mypass1@MYSID1
tables=mytab1,mytab2
log=myexport1.log
file=myexport1.pip
compress=n
rows=y

2. On myhost1 create the import parfile - myimport2.par.

Example

userid=myuser2/mypass2@MYSID2
fromuser=myuser1
touser=myuser2
tables=mytab1,mytab2
file=myexport1.pip
log=myimport2.log

3. On myhost1 userid create a named pipe.

  • mknod myexport1.pip p

4. On myhost1 start a nohup background import.

  • nohup imp parfile=myimport2.par &

5. On myhost1 run the export.

  • exp parfile=myexport2.par

6. On myhost1 check the myexport1.log and myimport2.log files to confirm the transfer has completed successfully.

WORKED EXAMPLE

CREATE THE TEST TABLES IN MYSID1

oracle>  sqlplus myuser1/mypass1@MYSID1

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Oct 6 14:02:57 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> create table mytab1 as select * from dba_tables;

Table created.

SQL> create table mytab2 as select * from dba_tables;

Table created.

SQL> select count(*) from mytab1;

  COUNT(*)
----------
      1395

SQL> select count(*) from mytab2;

  COUNT(*)
----------
      1395

SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

CREATE THE NAMED PIPE AND BEGIN THE IMPORT

oracle> cat myimport2.par

userid=myuser2/mypass2@MYSID2
fromuser=myuser1
touser=myuser2
tables=mytab1,mytab2
file=myexport1.pip
log=myimport2.log

oracle> mknod myexport1.pip p

oracle> nohup imp parfile= myimport2.par &
[1]     23204
oracle> Sending output to nohup.out

BEGIN THE EXPORT

oracle> cat myexport1.par

userid=myuser1/mypass1@MYSID1
tables=mytab1,mytab2
log=myexport1.log
file=myexport1.pip
compress=n
rows=y

oracle> exp parfile= myexport1.par

Export: Release 9.2.0.6.0 - Production on Thu Oct 6 14:01:08 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           MYTAB1       1395 rows exported
. . exporting table                           MYTAB2       1395 rows exported
Export terminated successfully without warnings.
oracle>
[1] +  Done                    nohup imp parfile= myimport2.par &

CHECK THE LOG FILES

oracle> cat myexport1.log

Export: Release 9.2.0.6.0 - Production on Thu Oct 6 14:01:08 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           MYTAB1       1395 rows exported
. . exporting table                           MYTAB2       1395 rows exported
Export terminated successfully without warnings.

oracle> cat myimport2.log

Import: Release 9.2.0.6.0 - Production on Thu Oct 6 14:00:53 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by MYUSER1, not by you

import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing MYUSER1's objects into MYUSEr2
. . importing table                         "MYTAB1"       1395 rows imported
. . importing table                         "MYTAB2"       1395 rows imported
Import terminated successfully without warnings.

CHECK THE DATA EXISTS IN MYSID2

oracle> sqlplus myuser2/mypass2@MYSID2

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Oct 6 14:01:39 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> select count(*) from mytab1;

  COUNT(*)
----------
      1395

SQL> select count(*) from mytab2;

  COUNT(*)
----------
      1395

SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
© copyright 2001-2014 ABCdba.com | all rights reserved