Next: Digging Deeper
Up: Exporting databases with Data
Previous: The Export
Contents
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: Digging Deeper
Up: Exporting databases with Data
Previous: The Export
Contents
2005-02-10