full import questions
jianhui, November 03, 2003 - 11:53 am UTC
Tom,
In case i need to import entire database from A to B so that I can get B updated to the time when export of A was done. And B was a clone of A in the past. No restore and recovery can be considered here since the platform is different and we have the dump file already.
In order to do this, which way will be better?
1) Recreate B from scratch with only SYS, SYSTEM, and do FULL import
2) Drop all non SYS/SYSTEM users then do FULL import
3) In case 1), should I create all tablespaces? I wonder that import is not able to do this since the physical layout of the tablespaces might not be the same between A and B
November 03, 2003 - 5:56 pm UTC
1 and optionally 3 if you like.
if the datafile names will be different, you'll have to precreate the tablespaces yes.
How to exclude a particular table from export dump
Thiru, May 25, 2004 - 1:55 pm UTC
Tom,
I have a table with one column BLOB datatype and has 10000 records. It takes around 2 hrs just for this table to import.
a. is there a faster way of doing?
b. If I have to exclude this table from importing, is there a way out?
Thanks for evrything u do.
May 25, 2004 - 2:56 pm UTC
blobs are imported one row at a time (no array interface)
but in any case -- 2 hours for 10,000 rows sounds extreme unless you are doing some large blobs.
is the blob cache or nocache, log or no log.
one way to "skip" it would be to precreate it and not use ignore=y. imp will fail trying to create it and then skip it.
another way -- if you have to use ignore=y to load into existing tables, would be to make it "non-insertable" -- eg: if the table is named "T", rename T to something else if it exists, and create a new table T that has the wrong columns. imp will fail on it. afterwards, drop t and rename the other table back
Amazing
A reader, May 25, 2004 - 4:52 pm UTC
Tom
I am dazed by the stuff you know. And that too that accurate..
Thanks.
Full import on a new DB
A reader, June 12, 2004 - 2:11 pm UTC
Tom,
I am running into problems doing a full import, would really appreciate your suggestions
I am doing an import into a newly created db
--Exported as follows
/***********Export file start****************************/
# Export beta2
trap "rm temp$$.txt" 0 1 2 15
echo
echo $(date +"%d %b %Y %T") Export
echo
cat << EOF > temp$$.txt
userid="/ as sysdba"
file=( $ORACLE_SID.1.dat
, $ORACLE_SID.2.dat
, $ORACLE_SID.3.dat
, $ORACLE_SID.4.dat
, $ORACLE_SID.5.dat
, $ORACLE_SID.6.dat
)
filesize=3GB
log=$ORACLE_SID.export.log
full=y
consistent=y
direct=y
statistics=none
EOF
$ORACLE_HOME/bin/exp parfile=temp$$.txt
echo
echo $(date +"%d %b %Y %T") The job ended.
/************export file end***************************/
--Imported as follows
/**************import start*************************/
imp system/password@xyz.abc.com parFile=parFile.txt
The file parFile.txt has the following
full=y
file=beta2.1.dat, beta2.2.dat, beta2.3.dat, beta2.4.dat, beta2.5.dat
grants=y
log=error.txt
/***************************************/
I am getting the quite a few errors as listed below
/*************errors start**************************/
.................
.................
"CREATE TABLESPACE "USERS" BLOCKSIZE 8192 DATAFILE '/beta2-02/oradata/beta2"
"/users01.dbf' SIZE 26214400 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767"
"M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING SEGMEN"
"T SPACE MANAGEMENT AUTO"
.................
.................
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "NNS" BLOCKSIZE 8192 DATAFILE '/beta2-02/oradata/beta2/a"
"bs01.dbf' SIZE 5242880 AUTOEXTEND ON NEXT 5242880 MAXSIZE 104857600 "
" EXTENT MANAGEMENT LOCAL UNIFORM SIZE 131072 ONLINE PERMANENT NOLOGGING"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/beta2-02/oradata/beta2/NNS01.dbf'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "APPS" BLOCKSIZE 8192 DATAFILE '/beta2-02/oradata/beta2/"
"apps01.dbf' SIZE 173015040 AUTOEXTEND ON NEXT 5242880 MAXSIZE 209715"
"200 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 131072 ONLINE PERMANENT NOLOGGI"
"NG"
..................
IMP-00015: following statement failed because the object already exists:
"CREATE USER "OLAPSYS" IDENTIFIED BY VALUES '3FB8EF9DB538647C' DEFAULT TABLE"
"SPACE "CWMLITE" TEMPORARY TABLESPACE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK"
..................
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'NETNNS_ADMIN' does not exist
IMP-00017: following statement failed with ORACLE error 959:
"CREATE USER "NNSDEV" IDENTIFIED BY VALUES '72737001C037F27F' DEFAULT TABL"
"ESPACE "NNSDEV" TEMPORARY TABLESPACE "TEMP" PROFILE "NNS_SCHEMA""
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'NNSDEV' does not exist
IMP-00017: following statement failed with ORACLE error 959:
"CREATE USER "ESCAN" IDENTIFIED BY VALUES 'B09D8EFDA7F02C45' DEFAULT TABLESP"
"ACE "ESCAN" TEMPORARY TABLESPACE "TEMP" PROFILE "NNS_SCHEMA""
IMP-00003: ORACLE error 959 encountered
.....................
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT "NNS_AD_HOC" TO "GENERAL""
IMP-00003: ORACLE error 1917 encountered
..........................
/*************errors end**************************/
Other info
Export db version: 9.2.0.5, O/S: Linux
Import db version 9.2.0.5, O/S: Windows Server 2003
Exp dumpfile size: close to 12G
No. of users/schemas exported 30 +, Each schema has its own tablespace
The db being imported is a newly created database. None of the users, tablespaces, roles present in the export db are present in the destination db.
My question
Is there anyway I can avoid creating users, tablespaces, roles etc. on the destination db
and let the import do the creation?
Thank you
Thank you
June 12, 2004 - 2:53 pm UTC
you would have to precreate the tablespaces. you sort of need them to import this full import.
or just do user level exports/imports instead of "full" ones.
(import is TRYING to do the creation already ?!? not sure why you are asking how to let import do that (which it is already doing)
full import of a new db
A reader, June 21, 2004 - 8:38 pm UTC
Tom,
Pre-created the tablespaces and did an import with the parameter file having the following values. Did not get the above tablespace errors
file=beta2.1.dat, beta2.2.dat, beta2.3.dat, beta2.4.dat, beta2.5.dat
grants=y
log=error.txt
ignore=y
fromuser = user1,user2,user3,user4,user5,user6,user7,user8,user9,user10,user11,user12,user13,user14,user15,user16,user17,user18
touser = user1,user2,user3,user4,user5,user6,user7,user8,user9,user10,user11,user12,user13,user14,user15,user16,user17,user18
Doing so did not get the following
triggers, views, synonyms and a few grants
I am not sure what I have missed. Would appreciate your input to get the triggers, views, synonyms and the grants.
Thank you
June 21, 2004 - 9:03 pm UTC
you would have gotten all triggers, views, and PRIVATE synonyms (public are owned by SYS and not imported in user level stuff)
so, for the triggers and views -- give us more info - how were they created, can you reproduce a really small example?
Full Database Import and Sys/System
sachin, July 20, 2004 - 1:26 am UTC
Hi Tom ,
You mention that
" exp and
imp Never exp or imp SYS owned objects -- they are always assumed to exist "
This caused a little confusion to me . I imported a export done from Oracle 9.2.0.5 into Oracle 9.2.0.1 .
In the process , I have 2 doubts
1]
I am getting following error text :
.............
. importing SYS's objects into SYS
IMP-00003: ORACLE error 1925 encountered
ORA-01925: maximum of 30 enabled roles exceeded
. importing SYS's objects into SYS
IMP-00003: ORACLE error 1925 encountered
ORA-01925: maximum of 30 enabled roles exceeded
. importing SYS's objects into SYS
IMP-00003: ORACLE error 1925 encountered
ORA-01925: maximum of 30 enabled roles exceeded
. importing OUTLN's objects into OUTLN
. importing DBSNMP's objects into DBSNMP
. importing ADF's objects into ADF
. importing ENT's objects into ENT
. importing ENT_USER's objects into ENT_USER
. importing ENT_REPORT's objects into ENT_REPORT
. importing ENT_SUPPORT's objects into ENT_SUPPORT
. importing ENT_CONFIG's objects into ENT_CONFIG
. importing ENT_CONFIG_USER's objects into ENT_CONFIG_USER
. importing ENT_CONFIG_REPORT's objects into ENT_CONFIG_REPORT
. importing ENT_CONFIG_SUPPORT's objects into ENT_CONFIG_SUPPORT
. importing SYSTEM's objects into SYSTEM
. . importing table "AQ$_INTERNET_AGENTS" 0 rows imported
. . importing table "AQ$_INTERNET_AGENT_PRIVS" 0 rows imported
IMP-00017: following statement failed with ORACLE error 23327:
"BEGIN SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('LHP2.HBOC.COM','"
"HP9000/800-64.0.8.1.0'); END;"
IMP-00003: ORACLE error 23327 encountered
ORA-23327: imported deferred rpc data does not match GLOBAL NAME and platform of importing db
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_DEFER_IMPORT_INTERNAL", line 30
ORA-06512: at line 1
. . importing table "DEF$_AQCALL" 0 rows imported
IMP-00017: following statement failed with ORACLE error 23327:
"BEGIN SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('LHP2.HBOC.COM','"
"HP9000/800-64.0.8.1.0'); END;"
IMP-00003: ORACLE error 23327 encountered
ORA-23327: imported deferred rpc data does not match GLOBAL NAME and platform of importing db
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_DEFER_IMPORT_INTERNAL", line 30
...............
The text seems to show that It is indeed trying to import
the System and Sys object.
2] When the contents of the export file are listed ,
they reveal that the SYS and SYSTEM password are getting reset .
imp system/password@connect_db file=expdat.dmp
log=textonly.log show=y full=y
The results :
ALTER USER "SYS" IDENTIFIED BY VALUES '735F98961B8D7C08' TEMPORARY TABLESPACE "TEMP"
ALTER USER "SYSTEM" IDENTIFIED BY VALUES 'A37053443D377872' TEMPORARY TABLESPACE "TEMP"
Could you please throw some light on
1. The meaning of message in [1]
2. Why should the SYS and SYSTEM passwords be changed
3. What is the best method to do a full import , on an existing database , which contains data .
Thanks
July 20, 2004 - 8:52 am UTC
1) it was trying to do things as sys but was not allowed for 30 was not sufficient for the number of roles you have configured.
the message means sys has more than 30 default roles and you have max_enabled_roles set too small -- increase it.
2) 1. see above.
2. it is setting them to what they were on the other database, that is the
behaviour of a full import of a full export
3. I would just backup the other database and restore it, short of that,
transport the tablespaces. short of that -- what you are doing, the slow
way (in 10g, answer would change -- datapump is a new way to do it)
Excellent
Sarah-Jane, September 20, 2004 - 11:23 am UTC
This contained the information that I failed to get out of Oracle support personal after two days trying!
Full Database export and import
ravi, February 08, 2005 - 9:28 am UTC
This is really handy. Don't need to search the huge oracle documentation for full exp/imp.
Good piece of work Tom.
FULL EXPORT/IMPORT
Rais Sheikh, February 15, 2005 - 3:25 am UTC
I have to build test database from production.
when i import full it give me following error.
IMP-00017: following statement failed with ORACLE error 3214:
"CREATE TABLESPACE "LHR_LEASE" BLOCKSIZE 8192 DATAFILE 'E:\ORACLE\ORADATA\S"
"PLCDB\LHR_LEASE.ORA' SIZE 2400 EXTENT MANAGEMENT LOCAL AUTOALLOCATE "
" ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 3214 encountered
ORA-03214: File Size specified is smaller than minimum required
how can i change the size.
February 15, 2005 - 3:19 pm UTC
that doesn't look right -- it looks like someone might have been editing the file (blanks after 2400).
is that possible?
in any case, just precreate the tablespace.
full export/import
Rais Sheikh, February 15, 2005 - 11:43 pm UTC
tom i create a tablespace with 2400MB datafile size after that i export full and then i import it on test database it give me following error i did not understand why it change the file size at the time of import, it show 2400 instead of 2400MB.
IMP-00017: following statement failed with ORACLE error 3214:
"CREATE TABLESPACE "LHR_LEASE" BLOCKSIZE 8192 DATAFILE 'E:\ORACLE\ORADATA\S"
"PLCDB\LHR_LEASE.ORA' SIZE 2400 EXTENT MANAGEMENT LOCAL AUTOALLOCATE "
" ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 3214 encountered
ORA-03214: File Size specified is smaller than minimum required
February 16, 2005 - 7:58 am UTC
ops$tkyte@ORA9IR2> create tablespace foo datafile '/tmp/foo.dbf' size 2400m extent management local autoallocate ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
ops$tkyte@ORA9IR2> !exp userid=/ full=y rows=n
Export: Release 9.2.0.5.0 - Production on Wed Feb 16 07:51:57 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
^C
EXP-00008: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
EXP-00000: Export terminated unsuccessfully
ops$tkyte@ORA9IR2> !imp userid=/ full=y show=y
Import: Release 9.2.0.5.0 - Production on Wed Feb 16 07:52:09 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM
"ALTER SESSION SET CURRENT_SCHEMA= "SYSTEM""
....<b>
"CREATE TABLESPACE "FOO" BLOCKSIZE 8192 DATAFILE '/tmp/foo.dbf' SIZE 2400M "
" EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT SPAC"
"E MANAGEMENT AUTO"
</b>
I cannot reproduce, please contact support for assistance (but you can workaround this right now using a create tablespace command of your own prior to importing)
A reader, February 26, 2005 - 10:57 am UTC
Hi Tom.
Trying to user data pump feature in 10g and got following errors
C:\>expdp system/manager dumpfile=emp.dmp logfile=emp.log schemas=scott
Export: Release 10.1.0.2.0 - Production on Saturday, 26 February, 2005 10:54
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid
February 26, 2005 - 1:44 pm UTC
uh huh,
[tkyte@localhost tkyte]$ oerr ora 39087
39087, 00000, "directory name %s is invalid"
// *Cause: A corresponding directory object does not exist.
// *Action: Correct the directory object parameter, or create a corresponding
// directory object with the CREATE DIRECTORY command.
(have you read the data pump documentation in the supplied packages guide? it is very different from the older exp/imp utilities, very similar but architecturally very different)
Phil, February 28, 2005 - 1:04 pm UTC
V Useful and informative thread.
Export/Import
Rahul, March 03, 2005 - 12:37 am UTC
the session was very useful
but on thing i want to ask tom is there any table or view that shows the export and import information like what is exporting or what is importing
or any other way to know plz let me know these.
March 03, 2005 - 7:24 am UTC
not a table, no -- exp/imp is just a client tool, it prints to a screen what it is doing.
export/import to create database oracle
tchouente, June 01, 2005 - 8:37 am UTC
how can i user export full to re_create/create the database oracle
June 01, 2005 - 10:28 am UTC
just exp and imp. import into the same release with an "empty database"
but, I would not suggest it, sort of the slowest way possible (backup and restore for example). it is the least flexible as well.
Import Procedures
A Reader, June 20, 2005 - 9:04 am UTC
Tom,
We importing from 7.3 to 8.1.7 database (full). The tables and data import was successful along with some of the procedures and views. The import hang in there and never finish. We can query the tables of all the owners and use some of procedures. However, some of the objects, like packages, are not imported (the import was incomplete). Is there a way we can just import the other objects (not include the tables and data) because the import takes a long time.
Thank you.
June 20, 2005 - 10:44 am UTC
your choices with imp are:
tables=
full=
fromuser/touser=
there is no real option to skip tables, it's primary design goal was tables.
imp cannot do just procedures/views/etc. it wants to do data.
if you search this site for "getallcode" "getallviews", you'll find sqlplus scripts to extract code/views which may be of use. you would run it on the 7.3 instance to extract the code.
A reader, June 23, 2005 - 11:55 pm UTC
Tom
When should we create tablespaces before doing the import. Actually, the dump file should have even the tablespaces included, why is it that we have to create the tablespaces before doing an import.
Thanks
Otn
June 24, 2005 - 6:32 am UTC
you might do it if you wanted the tablespace to have different files, different characteristics (eg: it WAS a dictionary managed one, you want it to NOW be a locally managed one)
imports to user table space
A reader, June 24, 2005 - 7:15 pm UTC
I have trying to import a dmp file without creating the tablspaces in the first place, and I see that all the tables are getting created in teh USERS table space.
Please help. What can be the possible causes?
June 24, 2005 - 9:52 pm UTC
you aren't allowed to create tablespaces
the tablespaces are not in the dmp file (it was not FULL=Y)
the paths in the dmp file are not legitimate on the target machine..
for starters. I'd guess the 2nd as the most likely?
Tablespaces on partitioned tables fromuser touser
A reader, July 13, 2005 - 6:11 pm UTC
Tom - I am getting an Oracle 959 on what is definitely a fromuser touser import where the user has a clearly defined default tablespace. Is this an issue on partitioned tables? It looks like it is only on the partitioned tables. snippit from log file
"ELISTS 1 FREELIST GROUPS 1) TABLESPACE "STDRS1X" LOGGING, PARTITION "P4" "
"VALUES LESS THAN (400000) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 ST"
"ORAGE(INITIAL 1677721600 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "BUSDELP"
"1D" LOGGING, PARTITION "P5" VALUES LESS THAN (500000) PCTFREE 10 PCTUSED 4"
"0 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 1677721600 FREELISTS 1 FREELIST G"
"ROUPS 1) TABLESPACE "STDRS1X" LOGGING, PARTITION "P6" VALUES LESS THAN (M"
"AXVALUE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 167"
"7721600 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "STDRS1X" LOGGING )"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'STDRS1X' does not exist
IMP-00017: following statement failed with ORACLE error 1931:
July 14, 2005 - 10:09 am UTC
Yes, with multi-segment objects (tables with lobs, partitioned tables, IOT's with overflows for example) the "simple tablespace mapping" doesn't work
imp will take the create and execute it.
IF the create fails because the tablespace does not exist
AND
the create is a single segment object
THEN
imp will drop the tablespace clause and let is use the default tablespace
ELSE
imp will say "multi-segment object, bad idea in general to put it all in
a single tablespace (so they say) so do not drop the tablespace clauses,
fail the statement"
END IF
you'll need to precreate that table and use ignore=y
very informative for the starters like me
sameer shaik, September 28, 2005 - 9:08 am UTC
Tom,
I am a new DBA, I was wondering how to move my current production database which is on Solaris to new environment AIX.
From the above export/import questions/answers , Iam confident that I can do a full export of the current database which is on Solaris and import it into my new database in AIX.
Thank you So much
Full Database export/import
David Prabhakar, November 01, 2005 - 9:19 am UTC
Hi Tom,
I have full db export and I am trying to import that to the newly created instance. I get the following errors. And most of the tables are not getting imported.
C:\tmp\STARLIMS_DMP_ORACLE>imp sys@sldev file=expdat.dmp full=y rows=y ignore=y constraints=n destroy=y log=c:\tmp\starlims_imp_11_01_05.log
IMP-00058: ORACLE error 1017 encountered
ORA-01017: invalid username/password; logon deniedUsername:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by SYSTEM, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses WE8MSWIN1252 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 29339:
"CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 4096 DATAFILE 'C:\ORACLE\ORADA"
"TA\LIMSPRD\UNDOTBS01.DBF' SIZE 2000M REUSE AUTOEXTEND ON NEXT 5242880 MAXS"
"IZE 16383M EXTENT MANAGEMENT LOCAL "
IMP-00003: ORACLE error 29339 encountered
ORA-29339: tablespace block size 4096 does not match configured block sizes
IMP-00017: following statement failed with ORACLE error 29339:
"CREATE TEMPORARY TABLESPACE "TEMP" BLOCKSIZE 4096 TEMPFILE 'C:\ORACLE\ORAD"
"ATA\LIMSPRD\TEMP01.DBF' SIZE 7784M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE"
" 16383M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576"
IMP-00003: ORACLE error 29339 encountered
ORA-29339: tablespace block size 4096 does not match configured block sizes
IMP-00017: following statement failed with ORACLE error 29339:
"CREATE TABLESPACE "DRSYS" BLOCKSIZE 4096 DATAFILE 'C:\ORACLE\ORADATA\LIMSP"
"RD\DRSYS01.DBF' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 1638"
"3M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING SEGME"
"NT SPACE MANAGEMENT AUTO"
ORA-29339: tablespace block size 4096 does not match configured block sizes
IMP-00061: Warning: Object type "SYSTEM"."REPCAT$_OBJECT_NULL_VECTOR" already exists with a different identifier
"CREATE TYPE "REPCAT$_OBJECT_NULL_VECTOR" TIMESTAMP '2002-05-12:16:31:26' OI"
"D 'BA042EA565FE11D6B6CE0002A52299F8' AS OBJECT"
"("
" -- type owner, name, hashcode for the type represented by null_vector"
" type_owner VARCHAR2(30),"
" type_name VARCHAR2(30),"
" type_hashcode RAW(17),"
" -- null_vector for a particular object instance"
" -- ROBJ REVISIT: should only contain the null image, and not version#"
" null_vector RAW(2000)"
")"
IMP-00061: Warning: Object type "WKSYS"."NUMBER_ARR" already exists with a different identifier
"CREATE TYPE "NUMBER_ARR" TIMESTAMP '2002-05-12:17:03:54' OID '2A19E97466041"
"1D6B6CE0002A52299F8' AS"
" TABLE OF NUMBER;"
IMP-00061: Warning: Object type "WKSYS"."DATE_ARR" already exists with a different identifier
"CREATE TYPE "DATE_ARR" TIMESTAMP '2002-05-12:17:03:55' OID '2A19E978660411D"
"6B6CE0002A52299F8' AS"
" TABLE OF DATE;"
IMP-00061: Warning: Object type "ODM"."NUMERIC_DETAILS" already exists with a different identifier
"CREATE TYPE "NUMERIC_DETAILS" TIMESTAMP '2002-05-12:17:06:37' OID '2A19E9C2"
"660411D6B6CE0002A52299F8' wrapped"
"0"
"abcd"
"abcd"
"abcd"
"abcd"
"abcd"
"abcd"
"abcd"
"abcd"
"abcd"
"abcd"
"abcd"
"abcd"
"abcd"
"abcd"
"abcd"
"3"
"d"
"9200000"
"1"
"4"
"0"
"8"
"2 :e:"
"1TYPE:"
"1NUMERIC_DETAILS:"
"1OBJECT:"
"1ATTR_NAME:"
"1VARCHAR2:"
"130:"
"1NUM_QUANTILES:"
"1NUMBER:"
"0"
""
"0"
"0"
"19"
"2"
"0 a0 9d a0 60 a3 a0 51"
"a5 1c b0 81 a3 a0 1c b0"
"81 77 a0 102 a0 c6 1d 17"
"b5"
"19"
"2"
"0 3 5b b f 33 23 27"
"2a 2b 22 3a 4f 43 47 1f"
"56 3f 7 62 64 68 6a 6b"
"74"
"19"
"2"
"0 1 6 19 1 22 2c 35"
"34 2c :2 22 3a :2 48 :2 3a 1 :2 4e"
********************************************
I wonder if there is any object TYPE in the export file. Please let me know tom, how to go about this problem. If you need anymore information, please let me know, I'll be glad to give you that.
Thanks and regards,
David.
Prasenjit Pal, January 31, 2006 - 6:12 pm UTC
ORA-39087: directory name is invalid
Michael, March 09, 2006 - 7:16 pm UTC
I ran into this problem as well, even after reading the documentation. Originally my par file read:
DIRECTORY=/workspace/oracle/dpump
DUMPFILE=exp_tabs_rcas.dmp
LOGFILE=exp_tabs_rcas.log
SCHEMAS=rcas01
INCLUDE=TABLE:"LIKE 'RCAS%'"
On the DIRECTORY line I had specified the actual UNIX path to the directory, not the name specified inside the database. Once I switched this to name used in the "create directory" statement the issue was resolved.
Duplicate from FULL EXP
V, March 23, 2006 - 2:25 pm UTC
Tom,
How would a take a full EXP from database A
and create a identical database on the same server called B? I realize I have to create the B database first.
Can IMP/EXP be used? If so, how do I handle the datafile names?
i.e. /oradata/A will all be in oradata/B
March 23, 2006 - 2:34 pm UTC
yes you can, you would simply pre-create the tablespaces using whatever filenames you wanted.
IMP/EXP Answers
Mark Pennington, April 27, 2006 - 10:16 am UTC
Tom,
I have learned a great deal about Oracle DBA stuff in general from your site. Thank you for taking the time to share your knowledge with all of us. Nicely done.
Very Good
Uma Mahadevan, June 14, 2006 - 10:15 pm UTC
There is something I have been trying to find out about
import from a full export
1.Export is great - successfully terminated with no warnings. But when the import is done to an empty Oracle Shell, (every time I do), finsihes with a message
import terminated succesfully with warnings
And I would have to go into the new db( to which the import was done) and recompile views and packages etc manually.Is there any reason for this/
2. Can I use the utilsp.sql or similar scripts to recompile all non valid objects?
June 15, 2006 - 8:35 am UTC
1) you didn't need to go in and do that, they'll do it all by themselves. Import just "forces" things in so it doesn't have to figure out the order. It creates all views and then creates all "X" and then creates all "Y" and so on - views might depend on packages, packages on views - chicken and egg.
2) you don't need to... but I think you meant utlrp.sql in $ORACLE_HOME/rdbms/admin
Thanks Tom
Uma, June 15, 2006 - 10:21 pm UTC
Great for starters!
It does give a lot of insight into possible options and solutions ..
Sorry I just read your status and instructions,
might have to post my question later, when you are less loaded.
Concept confusion on "Empty database"
Skell, September 25, 2006 - 3:24 am UTC
Hi, TOM
You refered to "Create a empty database" before FULL import data to a new database.
Could you pls tell me what's the "empty database"?
I create the database as below,
===================================
startup nomount pfile=d:\oracle\product\8.0.5\database\initORC0.ora
CREATE DATABASE ORC0
LOGFILE
GROUP 1 ('E:\ORACLE\ORC0\REDO11\REDO11.ORA','E:\ORACLE\ORC0\REDO12\REDO21.ORA') size 409600K reuse,
GROUP 2 ('E:\ORACLE\ORC0\REDO11\REDO12.ORA','E:\ORACLE\ORC0\REDO12\REDO22.ORA') size 409600K reuse,
GROUP 3 ('E:\ORACLE\ORC0\REDO11\REDO13.ORA','E:\ORACLE\ORC0\REDO12\REDO23.ORA') size 409600K reuse
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
DATAFILE 'e:\oracle\ORC0\datafile\system\SYSORC0.ORA' SIZE 910M REUSE
AUTOEXTEND OFF
MAXDATAFILES 1020
MAXINSTANCES 1
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET ZHS16GBK;
ALTER DATABASE DATAFILE 'e:\oracle\ORC0\datafile\system\SYSORC0.ORA' AUTOEXTEND OFF;
CREATE ROLLBACK SEGMENT SYSROL TABLESPACE "SYSTEM" STORAGE (INITIAL 100K NEXT 100K);
ALTER ROLLBACK SEGMENT "SYSROL" ONLINE;
==================================
It is "EMPTY DATABASE"? (ORACLE 8.0.5.)
Should I run the following scripts?
@d:\oracle\product\8.0.5\Rdbms80\admin\catalog.sql;
@d:\oracle\product\8.0.5\Rdbms80\admin\catproc.sql
@d:\oracle\product\8.0.5\Rdbms80\admin\caths.sql
@d:\oracle\product\8.0.5\Rdbms80\admin\catexp.sql
Thanks very much!
Skell
September 25, 2006 - 7:51 am UTC
you want to have the catalog created yes, an empty database has nothing other than "oracle's stuff" in it.
Thanks
Skell, September 25, 2006 - 9:43 pm UTC
Thanks :-)
Full DB export and Tablespace
RK, November 30, 2006 - 10:48 am UTC
If I am performing full database import to a database do
a) I need to pre-create partitioned tablespaces import will take care of creating TS and partitions.
b) During importing tablespace, how it hanles the process if origional datafile was at 'G' drive and the box where database will be imported, drive G does not exist, will it ignore TS creation and proceed further or it will create TS at oracle home ?
November 30, 2006 - 10:51 am UTC
a) it'll create the tablespaces and partitions during the import
b) it'll fail the tablespace creation and many other things will fail as a result likely - so you would best be served by pre-creating that tablespace in the new location so that just the create tablespace fails but everything that needs that tablespace works OK.
Export/Import
RK, November 30, 2006 - 11:00 am UTC
Thanks!
Problem in importing a full Database
Appu, February 26, 2007 - 1:55 am UTC
hi,
I have taken the exp from my production server now i want to import that into our local dev server , but full import is not coming , as i do not have enough space on my machine so i am taking import from a remote pc. In each table huge amount of rows are missing in Dev server.Is imp from a remote machine possible or do i need to take the dump into my machine and the use the imp utility?
Reply
Regrads
February 26, 2007 - 2:38 pm UTC
it is absolutely possible.
imp works over the network, it cares not where the database is - it just connects and inserts.
Export expected downtime.
Ibnhussain, January 29, 2008 - 3:51 am UTC
Hi, Tom
Excellent stuff, It would be more useful if discuss about the expected downtime time for ??Gbs of data.
How much time it takes to export/import 100GB of data for example.
Regards,
Ibnhussain
January 29, 2008 - 7:02 am UTC
it takes no downtime to export.
it takes no downtime to import.
so, no idea what you mean.
it takes somewhere between 1 minute and a lot of days to export/import 100gb of data. "it depends", did you attempt to do it in parallel (eg: I have 100 schemas, each with 1gb of data - I'll export 20 at a time in 5 sets and import 20 at a time in 5 sets - and I'll do it all at the same time - so I'll be importing set 1 as I'm exporting set 2 - that'll take less time probably then "I have 1 schema with 1-100gb table, I'll export, then import it)
Full export
ibnhussain, January 30, 2008 - 7:12 am UTC
Good Clarification, Thanks
Downtime: I mean the users will have to deal with read only tablespaces, but how much time it takes to bring it back to read write.
and dear Tom, Is it good idea to migrate a database from one platform to another using export/import which is more than 250GB. Or Will Cross platform tablespace be good for it?
Regards,
ibnhussain
January 30, 2008 - 10:27 am UTC
... Downtime: I mean the users will have to deal with read only tablespaces, but
how much time it takes to bring it back to read write. ...
why? export doesn't require that.
I would use a cross platform transport, regardless of size, I'd not want to have to dump my database out to files and reload the entire thing, just back it up and restore it (cross platform transport with rman)
Alexander, January 30, 2008 - 11:57 am UTC
The tablespaces must be self contained though correct?
If they are not, our only choice is data pump right?
January 30, 2008 - 2:01 pm UTC
the SET of tablespaces must be - and if you are moving from platform A to platform B, you must be moving all of them so........
be definition they will be self contained.
and you have a "full database transport" in 10g as well - everything including system - like a backup and restore to new platform - if they have the same byte order.
so no, data pump is no the only choice, you can always transport the entire thing as a transport set, and if you have the same byte order, you can do everything - including system.
Alexander, January 30, 2008 - 3:00 pm UTC
Can you explain what you mean by "transport set".
Let's say I do not have the same endian format (I don't think we do, I don't know exactly what version of Sun we have, it's Solaris 5.9 but I don't know where that falls in V$TRANSPORTABLE_PLATFORM).
All the documentation I see says we need the same endian format to use transportable database/datafile/tablespace.
Thanks.
January 30, 2008 - 4:18 pm UTC
"transport set" = set of data to be transported
a transport set is a self contained SET OF TABLESPACES.
in 10g, you have cross platform transport of tablespaces (plural, so you can be self contained) regardless of endianess.
in 10g, you have FULL DATABASE (eg: including SYSTEM - like a backup and restore) if the endianess is the same.
Alexander, January 31, 2008 - 9:00 am UTC
Ok I'm with you, transporting the set of tablespaces sounds promising.
Would you mind outlining the steps one would take to move to another database on a different platform?
Would I run the convert command on the source tablespaces, build the new database on platform x, then backup and restore?
February 04, 2008 - 3:15 pm UTC
Full Export (MINUS) som users
Yogesh Purabiya, October 01, 2008 - 7:32 am UTC
Oracle 8.1.7
Our fullexport size is 12 GB
Out of this, almost 5 GB is of legacy data - read only.
So, we would like to exclude those users / owners from the full export (if possible).
Also, this data are on a separate tablespace.
Is it possible to take full export - but excluding this legacy data - in Oracle 8i ?
October 01, 2008 - 12:08 pm UTC
that feature does not exist, you would have to export the users of interest.
You should not be using export as a backup tool and it sounds like you are. It is not useful as a backup tool - only backups are.
You could use fine grained access control if you wanted, create a policy that returns "1=0" (no rows appear to be in the table) for a given user - then do the export as that user.
Harschil Kaparwan, October 07, 2008 - 1:50 pm UTC
Hi Tom
I have imported data into 'XX' schema.
Now when I query user_tables it does not show anything.
and
select * from <TABLE_NAME>
does not show anything.
But when i query dba_objects where owner='XX' and object_type='TABLE' then it shows the tables imported.
or
when
i select * from XX.<TABLE_NAME>
it show output.
Thanks for your help.
October 08, 2008 - 9:51 pm UTC
you do not say who you are logged in as.
so I will guess
you are not logged in as XX
there is a synonym/view <TABLE_NAME> that the user you are logged in as can see.
select * from <TABLE_NAME> is really resolving to select * from YY.<TABLE_NAME>
and YY has no data.
when you use XX.<TABLE_NAME> you avoid the synonym/view
Harschil Kaparwan, October 09, 2008 - 3:38 am UTC
Hi Tom,
...you do not say who you are logged in as.
Really sorry for not giving the complete details earlier.
Above user XX was created new ( While creating User didnt specify the default tablespace , so XX has default tablespace SYSTEM). Then imported the export dump of user XX from some other database.
a)imp xx/<pwd> file=a.dmp fromuser=xx touser=xx ignore=Y
After import SYSTEM tablespace reaches near 100%.
b) logged in user XX.
c) select * from dba_objects where owner='XX'
is showing the <TABLE_NAME> as one of the object
(table)of XX.
d) Select * from user_tables
now rows selected.
e) select * from <table_name>
table/view does not exist.
f) select * from xx.<table_name>
100 rows selected.
Cheers.
October 09, 2008 - 10:47 am UTC
Nope, this doesn't compute. Do you have the import logs - if not, drop user xx, recreate and do it again, saving the import log and share it with us.
Harschil Kaparwan, October 15, 2008 - 2:52 pm UTC
Many Thanks Tom,
We were checking it with another user and not 'XX'. Sorry , to trouble you for this.
Regards
imp full=y
A reader, November 09, 2008 - 4:28 pm UTC
greetins thomas,
and thanks like always,
can you pelase explain to me what is the difference between:
1) imp scott/tiger full=y file=scott.dmp
and
2) imp scott/tiger file=scott.dmp
scott privileges are connect and resource only.
and exp was done
exp scott/tiger file=scott.dmp
Thanks
November 11, 2008 - 3:32 pm UTC
that was a 'user mode' export dump file
therefore, fromuser will be the user that was in the dmp file and touser will be the logged in user.
if you created a user A with connect, resource and did
imp a/a file=scott.dmp
you would observe:
. importing SCOTT's objects into A
so the full=y is just like a fromuser=<whatever was in file> and touser=USER
full imports vs doing users separately
A reader, February 24, 2010 - 12:20 am UTC
Tom - when doing a full import, there are often errors thrown as the system tables import on top of the system tables. Is this a problem? Is it dangerous? What if in one case you are attempting to move a db to a newer version. How about in the case if you are not? I am always slightly wary of full imports since the dictionary is already there in the first place.
March 01, 2010 - 8:43 am UTC
I've never used a full export/import - way too slow for anything over a couple of megabytes.
But, SYS owned objects are never exported, so what objects are you encountering an error on?
Full Import Question 11g
A reader, March 09, 2010 - 10:30 pm UTC
Tom,
I did a full expdp of 11g database.
I want to do a "full import" of this 11g 11.1.0.7 database into a new database built from scratch using impdp on a different server.
Please tell me what should the impdp run as which user.
Sys is not allowed to run impdp.
Can I use system user to do the import (impdp).
I will also precreate the tablespaces.
What happens to users and objects already existing in this new database for example system user , and all default 11g users will already be present .
Do I have to give some special settings during full impdp.
March 10, 2010 - 8:40 am UTC
you could use system, but just use your own DBA account.
it'll deal with the existing users and tablespaces fine.
Import Export
ganesh, March 24, 2010 - 5:18 am UTC
My Database server capacity is 100Gb. My current database size is 80GB. Everyday i am taking dump (export). It will come around 20Gb. So i am not facing any problem. But today my db size is increased 10GB. So i can't able to export now. I can't able to add a disk right now. How can i resolve this problem.
March 26, 2010 - 10:55 am UTC
buy more disk???
seriously, what would you have me say? Given that a terabyte of storage for something as meaningless as an export costs about $99USD - just buy more disk.
I seriously hope you are not thinking that export is a backup, are you? I'm probably wrong, you are, but my fingers are crossed.
EXPORT is not a backup, EXPORT is definitely not a backup.
11g : Full Exp and Imp
Saurabh, October 14, 2010 - 6:08 am UTC
Tom,
I read complete thread, pls confirm on my below understanding.
1)To recreate database from FULL EXPDP - We should create blank database (with SYS and other default tablespaces,users etc) and run IMPDP with REMAP_TABLESPACE and REMAP_USERS clause with appropriate details. This should work?
2) Export is not backup, so we should take coldbackup(shutdown, copy all Datafiles,controlfiles,p/spfile,redologfile(not sure),passwordfile,archivelog files) Pls let me know if i missed any thing?
3) For hotbackup (copy all Datafiles,controlfiles,p/spfile,redologfile(not sure),passwordfile,archivelog files)Pls let me know if i missed any thing?
Thanks,
October 15, 2010 - 7:47 am UTC
1) well, you do not need remap at all - if you want to recreate, you would simply impdp it - yes.
2) you should take a HOT backup if you ask me, cold backups are for losers. If the data means anything to you - you are in archive log mode and hence hot backups are the only thing to consider doing.
use rman - let it do the work - do not try to do it manually yourself.
Never backup online redo logs - the only thing you could do with them is accidentally restore them over the current online redo logs - making it impossible to perform a full recovery.
3) there is more to it than that - you would backup the stuff but not the onlines - and you would switch through the onlines at the end to make them become archives and back them up as well - so you have a set of files that could be restored consistently in your backups - without requiring anything from disk.
I suggest you read:
http://www.oracle.com/pls/db112/portal.portal_db?selected=4&frame=#backup_and_recovery the backup and recovery stuff and *practice*, *practice*, *practice*
Mostly practice recovery - it'll force you to practice backup. Do not practice just backing up, focus on practicing RECOVERY.
Full database export and import
Aidan McCarthy, May 19, 2011 - 5:35 am UTC
Hi Tom,
We want to migrate a 9i database running on HP PA-RISC to 11g on Solaris 10. I have created an empty 11g database and used export/import but I'm concerned that this may no be best practice. Is it possible that I can use RMAN to convert the endian formats of all the datafiles from HPUX to Solaris and then just move these to the new platform and upgrade the database?
Thanks in advance,
Aidan
May 19, 2011 - 8:48 am UTC
ops$tkyte%ORA11GR2> select * from v$transportable_platform;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little
20 rows selected.
if the endian formats are the same for your two hardware platforms (refer to above view), you can do a full database transport AFTER upgrading your 9i database on hp to 11g. That would be the fastest method. And a lot easier than exp/imp
DB clone
aliyar, May 28, 2011 - 3:33 am UTC
Dear Tom ,
Thanks for your valuable help for DBA world
can you please suggest me for the following
requirement is need to clone prod database to another machine .
production is running in archive log mode
version is : 10.1.0.5.0
totally 12 permanent tablespaces inacluding undo ..
size of the database is around 450 GB.
we are taking rman level0 hotbackup one after another day base
which is best way to clone. cold backup is not possible
1) take manual hotbackup and restore them back to target machine
2 ) take full exp and imp in target machine
3 ) use rman to clone
if rman is best , is that possible to give me some links where i can get rman steps to clone ..
Thanks
May 31, 2011 - 9:39 am UTC
9i to 11g exp/imp
Asim Saeed, October 14, 2011 - 7:07 am UTC
Hi Tom,
I did full export of 9i database and then import it in 11g database but it gives following error on various objects
. importing WKSYS's objects into WKSYS
"ALTER SESSION SET CURRENT_SCHEMA= "WKSYS""
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
IMP-00000: Import terminated unsuccessfully
What I know is "FULL IMP" creates all the users itself, then why is this error? Also i did query dba_users after import and found all the users without any objects.
Thanks,
Asim Saeed
oracle exp and imp from unix to windows
arun, December 08, 2011 - 11:30 pm UTC
Hi Tom,
Thanks in advance.
I have a full database export file from UNIX platform oracle 10.2.0.4 and now what i want to import this into WINDOWS platform oracle 10.2.0.1 for testing purpose.
i want that i can see all the users tablespace and their data as it looks in original unix platform.
can you help me on this.
December 09, 2011 - 4:05 pm UTC
just import it. what more do you need to know?