next up previous contents
Next: Digging Deeper Up: Exporting databases with Data Previous: The Export   Contents

The Import

Once you've got your full export your good to go. As a test I've created a clean database instance on my Linux workstation at home and named it "test" just like the database I exported from at the office.

THe first thing we need to do is start up the new clean instance as usual. Once thats done we can copy over the exported dump.

oracle@nexus6 ~$ sqlplus sys/passwd as sysdba;
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Oct 12 01:31:02 2004
Copyright (c) 1982, 2004, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  159383552 bytes
Fixed Size                   777896 bytes
Variable Size             132915544 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.
SQL> quit;
oracle@nexus6 ~$ mkdir /u01/app/oracle/DUMPS
oracle@nexus6 ~$ cd /u01/app/oracle/DUMPS
oracle@nexus6 DUMPS$ scp hostxyz:/export/.../Oracle-Oct11-fullexp.dmp .
oracle@nexus6 DUMPS$ sqlplus sys/passwd as sysdba;
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Oct 12 01:48:48 2004
Copyright (c) 1982, 2004, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create or replace directory dump_dir as '/u01/app/oracle/DUMPS';
Directory created.

SQL> quit

Notice that you need to create a directory refernce. It doesn't matter what directory or referance name you use, but if you don't create the refence you'll get errors from Data Pump.

Moving onwards, lets do the actual import using the impdp command.

oracle@nexus6 DUMPS$ impdp system/passwd FULL=y DIRECTORY=dump_dir  \
> DUMPFILE=Oracle-Oct11-fullexp.dmp LOGFILE=Oracle-Oct11-import.log
Import: Release 10.1.0.2.0 - Production on Tuesday, 12 October, 2004 1:50
Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** FULL=y 
  DIRECTORY=dump_dir DUMPFILE=Oracle-Oct11-fullexp.
dmp LOGFILE=Or
acle-Oct11-import.log
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
.........
Processing object type DATABASE_EXPORT/SCHEMA/DE_POST_SCHEMA_PROCOBJ...
Processing object type DATABASE_EXPORT/SCHEMA/DE_POST_SCHEMA_PROCOBJ...
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 6877 error(s) at 01:54

oracle@nexus6 DUMPS$

Ok, the import completed sucessfully. You'll notice all the errors (6877 of them!) due to duplication. This isn't a problem, we could have imported with "TABLE_EXISTS_ACTION=APPEND" parameter to avoid these.

Finally, lets test the import. Remember, this is a clean instance. All I've done to the database is to create it with dbca, start it, create a directory reference and done the import. I never created the user "ben". So, if the import worked properly I should be able to log in as my old user (ben/passwd) can access the "sys_log_tbl" table that we created in the SQL*Loader chapter.

oracle@nexus6 DUMPS$ echo $ORACLE_SID
test
oracle@nexus6 DUMPS$ sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Oct 12 01:55:59 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL> connect ben/passwd;
Connected.
SQL> select * from sys_log_tbl;
....
TIMESTAMP HOSTNAME
--------- ------------
MESSAGE
--------------------------------------------------------------------------------

05-OCT-04 vixen
pseudo: [ID 129642 kern.info] pseudo-device: devinfo0

05-OCT-04 vixen
genunix: [ID 936769 kern.info] devinfo0 is /pseudo/devinfo@0


903 rows selected.

SQL> quit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
oracle@nexus6 DUMPS$

And it works!


next up previous contents
Next: Digging Deeper Up: Exporting databases with Data Previous: The Export   Contents
2005-02-10