• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3846
  • Last Modified:

can't sucessfully run Oracle import

was given an export file from another location.  At first I thought it was a data pump export file until I tried to import it.  Turns out it is an export utility dump file.  So I ran the import utility using imp joeller/xxxxxx   FILE=C:\Ora11G\admin\orcl\adump\arcgis030410.dmp full=yes  (The comments in the dump file say it is a full database export.)  However, upon running a large number of statements are generated on the command line and I am only able to view the last few hundred.  However those last few hundred keep reporting failure due to the non-existence of users.  However I know for a fact at least one of the new users is created and the others I need are already in the database.  I am baffled by this behavior.  Assistence would be greatly appreciated.  These 185 points are all I got until they finish PAQing another question.
0
Edward Joell
Asked:
Edward Joell
  • 52
  • 34
  • 4
  • +1
1 Solution
 
slightwv (䄆 Netminder) Commented:
You can capture ALL the output by adding: LOG=myouyput.txt

Do that and see what error starts the while thing.  There are times when imp gets confused and errors cascade.

After you've logged everything, if you still can't sole it and you can, please post the first few errors from the log file.
0
 
schwertnerCommented:
If the export is FULL then possibly you also do FULL import.
In this case Import will try to create the tablespaces of the original
DB. If it is not possible it will be also unable to create the users
and this will be the sad end of the import.
So you have at least to ensure the same directory structure to allow
the Import to create the .dbf files.
0
 
k3paCommented:
Which utility are you using to run the imp command? If you're using the windows terminal (cmd), you can change the buffer size to a higher value to allow reading the whole output. (cmd -> left click in title bar -> properties -> set Buffer Size to 200000).

You may run the full import again and check the output. Directory structure and permissions is the most probably cause.

Tip: use "show=t" within the imp command to get DDL information. It's a little bit unclear, but you can get usefull information.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
schwertnerCommented:
show=y
0
 
Edward JoellAuthor Commented:
slightwv: I added Log parameter to my import statement and got the following. See code window.
Schwertner as you can see from my comments in the code window I can't create the same directory structure.
k3pa: The maximum number I can set the buffer to is 999.
0
 
Edward JoellAuthor Commented:
I don't understand why my code window did not attach so I am pasting the feed back here.

Here are the errors from the import.
First
IMP-00017: following statement failed with ORACLE error 29339:
 "CREATE TABLESPACE "SYSAUX" BLOCKSIZE 16384 DATAFILE  'D:\ORA11G\ADMINISTRAT"
 "OR\ORADATA\ARCGIS\SYSAUX01.DBF' SIZE 1449M       AUTOEXTEND ON NEXT 1048576"
 "0  MAXSIZE 65535M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  "
 "SEGMENT SPACE MANAGEMENT AUTO"
which makes sense because I don't have a D drive and my Oracle home is not located there.

Then
IMP-00003: ORACLE error 29339 encountered
ORA-29339: tablespace block size 16384 does not match configured block sizes

Which I assume is related the to above though why is it talking about block sizes when the path doesn't even exist is beyond me.
It proceeds to do the same for the UNDO, Temp, users, and all the custom table spaces SDE, ERFO, etc.

Then it throws an error
IMP-00015: following statement failed because the object already exists:
 "CREATE PROFILE "WKSYS_PROF" LIMIT COMPOSITE_LIMIT DEFAULT SESSIONS_PER_USER"
 " DEFAULT CPU_PER_SESSION DEFAULT CPU_PER_CALL DEFAULT LOGICAL_READS_PER_SES"
 "SION DEFAULT LOGICAL_READS_PER_CALL DEFAULT IDLE_TIME DEFAULT CONNECT_TIME "
 "DEFAULT PRIVATE_SGA DEFAULT FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_T"
 "IME DEFAULT PASSWORD_REUSE_TIME DEFAULT PASSWORD_REUSE_MAX DEFAULT PASSWORD"
 "_LOCK_TIME DEFAULT PASSWORD_GRACE_TIME DEFAULT"

I presume that this is one of the default users created at the time of an oracle install.  If so then why is it even trying to create it.  I am trying to do an import.  I would assume that a database already exists  when you are doing an import.  Is this utility trying to create a brand new instance?  or is it only good on a brand new instance?

It goes on to repeat this for all the built-in database users.

Then we get
 "CREATE USER "ZEKIAH" IDENTIFIED BY VALUES 'C3E3F0AAC8BDC49D' DEFAULT TABLES"
 "PACE "SDE" TEMPORARY TABLESPACE "TEMP""
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'SDE' does not exist
Which is true because it was never created.  See above

Then a couple of users already exists error because this is not the first attempt to run the import script.  Then more users failing to be created because the database does not exist.

Then I get
IMP-00015: following statement failed because the object already exists:
 "CREATE ROLE "DATAPUMP_IMP_FULL_DATABASE""
which makes sense and then

IMP-00015: following statement failed because the object already exists:
 "REVOKE "DATAPUMP_IMP_FULL_DATABASE" FROM SYSTEM"

Which doesn't.  How can a revoke fail because the object already exists?  So it does that for a buch of more built-in users and roles.
Then a bunch of create roles fail to be granted to users that failed to be created.
Then a bunch of grant permissions faile to be created for users that failed to be created.

At this point the import fails.  It has not tried to create any tables. or stored procedures or packages.

After this I ran the script with show=y.  However this time the log file showed all the DDL but did not show any of the errors?
0
 
Edward JoellAuthor Commented:
Great.  Now this run not only proceeded to change my sys password, but it also delete my other sysdba user.  I now have no way to get into the database.
0
 
slightwv (䄆 Netminder) Commented:
This is why I don't like doing FULL imports from a FULL export.  There are to many issues.

You can either pre-create the tablespaces (I suggest this one) or possibly configure the 16K block size parameter: db_16k_cache_size (I've never messed with these but it appears to fix the import errors).

Check out:
http://forums.oracle.com/forums/thread.jspa?threadID=610456
http://www.orafaq.com/forum/t/26694/2/

You can use the INDEX_FILE to capture all the creates and change what you need.

>>I now have no way to get into the database

You always have a way:  connect / as sysdba
0
 
Edward JoellAuthor Commented:
'connect' is not recognized as an internal or external command,
operable program or batch file.
0
 
Edward JoellAuthor Commented:
C:\Documents and Settings\Edward Joell> connect / as sysdba
'connect' is not recognized as an internal or external command,
operable program or batch file.

C:\Documents and Settings\Edward Joell>connect / as sysdba
'connect' is not recognized as an internal or external command,
operable program or batch file.

C:\Documents and Settings\Edward Joell> / as sysdba
'/' is not recognized as an internal or external command,
operable program or batch file.

C:\Documents and Settings\Edward Joell>
0
 
slightwv (䄆 Netminder) Commented:
'connect' is a sql*plus command.

C:\Documents and Settings\Edward Joell> sqlplus /nolog
SQL> connect / as sysdba
0
 
Edward JoellAuthor Commented:
And the hell of it is I didn't want a full database backup.  He was supposed to do a backup of two schemas.
0
 
slightwv (䄆 Netminder) Commented:
That's another reason I try to avoid FULL imports.  Just in case things like this happen.  :)

If you knew you only wanted two schemas you should have just imported those.  That would also get you around the create tablespace errors.

FULL imports from a FULL export has never really worked right.  It almost always causes problems with SYSTEM stuff no matter how careful you are.
0
 
Edward JoellAuthor Commented:
Well now I tried.  i was using the parameter schemas=sde,gisadmin then found out that did not exists with import utility command.  so my idea was to import the full database onto my machine and then export using the datapump the two schemas I needed.  So there is a way to just import needed schemas from the full DB export?
0
 
slightwv (䄆 Netminder) Commented:
Why not just import the two from the FULL export?  I'm unclear why you want the intermediate step.


original imp uses FROMUSER:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch02.htm

To see the list of available options:  imp help=y
0
 
Edward JoellAuthor Commented:
As stated I was aware of the schemas option of data pump and when that did not work with import utility then I was under the impression it was not possible to just import schemas  using import utlity.  have created tablespace sde, now getting ready to try to import those two schemas.  Is it going to change my sys password again, since I am getting only these two schemas?
0
 
slightwv (䄆 Netminder) Commented:
If you use FROMUSER to import and not FULL=Y then you should be OK.
0
 
Edward JoellAuthor Commented:
imp joeller/kelsey   FILE=C:\Ora11G\admin\orcl\adump\arcgis030410.dmp LOG=C:\Ora11G\admin\orcl\adump\output.txt fromuser=sde,gisadmin

"ALTER SESSION SET CURRENT_SCHEMA= "SDE""
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
IMP-00000: Import terminated unsuccessfully

Well of course it doesn't exist that is what the import is supposed to do.
0
 
slightwv (䄆 Netminder) Commented:
>>Well of course it doesn't exist that is what the import is supposed to do.

Not necessarily. Only a FULL import will create tablespaces/users/roles/etc...

A SCHEMA level import needs the schema/tablespaces to already exist.
0
 
Edward JoellAuthor Commented:
Found this from other forum.  You mean I not only have to precreate the tablespace but the user then assign quotas?  Then what is the use of the dump file.  I could have done the  same thing by using SQL developer and having it do create DDL in a third of the time this has taken even with having to re-order every SQL statement because of dependacies. Geez

This is, apparently, a user-level dump of data and strutures, and, as
such, needs the user account created before running the imp command.
This means, among other things, that you'll likely get some other
errors from the import process if tablespaces don't exist, if this new
user has no quota on the required tablespaces (required meaning that
these tables are expecting to have this particular tablespace as their
'home'), if you create this user and leave the default tablespace
assignments as 'SYSTEM' and load up that tablespace with user objects
not designed to reside there ...
0
 
slightwv (䄆 Netminder) Commented:
If you think about this it makes sense.  Users/Quotas/Tablespaces are all owned by SYS.  If you do not import SYS objects like USERS wouldn't be imported.

>> I could have done the  same thing by using SQL developer and having it do create DDL in a third of the time this has taken even with having to re-order every SQL statement because of dependacies

Can't speak to SQL Developer never used it.  I thought the dependency 'feature' was fixed in later releases.  exp used to export everything in alpha order which means imp imports in alpha order.  Maybe it was how the export was done.

Times like these are where being old-school comes in handy.  I have .SQL scripts to build my schemas.  I need to recreate one, no big deal.  Just run the script then import the data.

>>Then what is the use of the dump file

transporting non-SYS objects around.  I use it for my test system baseline.  ONce I build it I exported the schema tables.  Like I said above:  run a couple of SQL scripts, import my test data and I'm good to go.
0
 
Edward JoellAuthor Commented:
I am hoping I am faking out the system.  I created tablespaces for the two schemas I need.  Now I am running the full backup.  All the other users will fail to be created because their tablespaces do not exist.  That should get me only the data I need.   I hope.  Of course I am going to have to go back into sqlplus to restore my passwords.   Once these things The release I got of SQL Developer was  the latest a/o November 1, 2009.  If there has been a later release since then I am unaware of it.  It appears to be working.  Tell are there any traps I need to be aware of using the Datapump to export these two schemas?
0
 
Edward JoellAuthor Commented:
BTW I am increasing the point value on this question to 250 since the PAQ process on the other question has been completed.  Everything you have said has been right on target.  I am hoping that this will resolve the issue but I need to wait and see that everything works.
0
 
Edward JoellAuthor Commented:
Try that again.
0
 
schwertnerCommented:
You have to precreate the tablespaces somewhere (the best choice is by the other .dbf files).
So you will overcome the problem with the tablespaces.
Even it signals "Oracle can not create the tablespace" in fact it exists and you have calmly to ignore the
message about the tablespaces.
More interesting will be the other messages.
To be prepared for the user problems try to drop from the target DB (hey, be cautios!!! not on the database where
you run Export !!! This will be suicide!!!!)
SQL>  drop user my_user cascade;

But (be cautios again!!!) - do this not for all users, but only for that two application users
that you in fact try to import.

Be be very careful if you have access to the original (source) database.
it seems you are not experienced enough and can easily damage it.
0
 
slightwv (䄆 Netminder) Commented:
Wow... go to a quick meeting and you've been busy!  I hope I don't miss anything.

>>All the other users will fail to be created because their tablespaces do not exist.

Why do you still want to do a full import?  Even if this is just for the quotas on the specific users, when you do a schema datapump export, you'll still lose them.  Is it really worth all the trouble?

Now that you have the tablespaces, just issue a simple create user for the users and import the two schemas.

>>If there has been a later release since then I am unaware of it

I don't use it so I don't keep up on the releases.  Looks like the latest was released this month:
http://www.oracle.com/technology/software/products/sql/index.html

>>I am increasing the point value on this question to 250

Thanks but not necessary.  The points are nice but not the main reason we do this.
0
 
schwertnerCommented:
Schema export has its disadvantages. It doesn't export the grants given to the schema. Full exports exports all grants.

I run into following scenario:

1. I did schema level export (Data Pump)
2. I did full import of the above export file.
3. 68 objects were invalid
4. The reason for this was that the schema level export doesn't export the grant

GRANT execute DBMS_CRYPTO TO schema_exported;

5. It took me a lot of time to investigate the case and to recognize exactly which grant is missing. I had to read procedures and functions working with DBMS_CRYPTO, to try to compile them and to analyze the errors I saw  .... Waste of time and big pressure
0
 
slightwv (䄆 Netminder) Commented:
>> It doesn't export the grants given to the schema

Good point.  I guess I'm used to my create scripts before imports.
0
 
Edward JoellAuthor Commented:
There are 31 set of permissions and roles that have to be granted to each user.  If any one of them is left out then ArcSDE will not work.  I would rather have the user be duplicated from the previous database.  Since I can't do that then I will have to grant them every possible role and every possible permission, using OEM.

Export pump completed with two errors see below.  LazyDBA says run catexp.sql (which I was pretty sure I had) but that didn't work.  I keep finding reference to "Note 258618.1 - How To Install and Uninstall Expression Filter Feature or EXFSYS schema"  which I have not be able to successfully google.  Then there are other references that say run catexf. Which when I tried it errored out because exfsys user was already created.  when I commented out the line @@exfsys, then it ran but I am still getting the two errors when I datapump.

ORA-04063: package body "EXFSYS.DBMS_EXPFIL_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "EXFSYS.DBMS_EXPFIL_EXP"
ORA-06512: at "EXFSYS.DBMS_EXPFIL_DEPASEXP", line 61
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5980

 C:\ORA11G\ADMIN\ORCL\DPDUMP\ARCGIS030410.DMP
Job "JOELLER"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at 14:08:41
0
 
slightwv (䄆 Netminder) Commented:
>> I will have to grant them every possible role and every possible permission, using OEM.

Maybe not. I can think of two possible options:
1: use SHOW=Y parameter to capture the SQL. Pull out what you need and run it.

2: try the 'FULL' import with INDEXEX=N ROWS=N

I'm still thinking you'll need to capture the grants anyway when you do a schema level expdp.

>>"Note 258618.1

This is an Oracle Support note. If you ever find it with Google, the site that has it posted has some explaining to do.

I thought you were wanting a schema level expdp. If so, I'm not sure why it was trying to export EXSYS. I would have to Google that schema myself.

I'm afraid I probably won't be that much help with datapump. I've used it a couple of times but am far from a true expert.


0
 
Edward JoellAuthor Commented:
I am not trying to export exfsys.  
the code expdp joeller/xxxxxx dumpfile=arcgis030410.dmp DIRECTORY=exp_dir DUMPFILE=EXPSDE.dmp SCHEMAS=SDE,GISADMIN LOGFILE=EXPOUTPUT.TXT threw the two errors mentioned above.  All sites say it is from not running either catexp.sql or catexf.sql  they then reference Metalink Note 258618.1 - "How To Install and Uninstall Expression Filter Feature or EXFSYS schema".  So why reference it if it is not available to anyone?  Why tell me to run a catexp.sql or catexf.sql when the one can't run and the other does not address the errors?  I am going to try to import the created export file anyway.  And if that doesn't work I am going to send the backup back to the customer to tell him to do it right or he can fly out the 3000 miles out here to do it his own damn self, or we will require that he purchase a SQL Server license and refuse to support his Oracle mess.

Since OEM would not let me assign all of the roles and priviledges to the two users I had to create, I ended up assigning each of the 31 permissions and 5 roles manually.
0
 
Edward JoellAuthor Commented:
And to make my day finally oh so good, the server I am trying to import this  to will not let me log on in the command line citing either TNS: protocol Adpater error or TNS could not resolve the connector identifier specified (which is not only the service name but also the service listed in TNSNames.ora)  OEM datapump page keeps asking for HOST's credentials for which it will not accept the user created to do the import, nor will it accept sys.
0
 
slightwv (䄆 Netminder) Commented:
>>I am not trying to export exfsys.  

I've not messed with datapump that much.  It might be some behind the scenes code that is confused.  FULL system imports can do this (I know that doesn't make you feel any better).

The script that resets all the SYSTEM stuff is catalog.sql.  You might want to try that one as well.

SQL> @?/rdbms/admin/catalog

>>So why reference it if it is not available to anyone?

I post links to Oracle Support (formally Metalink) all the time.  Most people using Oracle have a support contract with Oracle and have access to these documents.

>>Why tell me to run a catexp.sql or catexf.sql when the one can't run and the other does not address the errors?

Maybe that fixed a problem for someone before.  What works in the past does not always work in the future.

>> And if that doesn't work I am going to send the backup back to the customer to tell him to do it right

Once you created the tablespaces and roles/grants there should be no reason you can't import just the two schemas you want from their provided export.
0
 
slightwv (䄆 Netminder) Commented:
>>And to make my day finally oh so good

Datapump stuff?  I probably can't help all that much but I can try if you can't get if figured out.


I realize it's a little late to ask this but:  What is the end game here?

Are you just wanting to load some data to help support a client/customer/??? or do you need the EXACT environment?

If you just need a pseudo test envrioment:  Create the schema owner then grant DBA to them.

This should give that user pretty much anything they need.  The downside is:  It gives the user EVERYTHING.
0
 
Edward JoellAuthor Commented:
We need to emulate the customer's database on the development server so that we can run tests of the updates of the app we are created to verify it works.  Already ahead of you and created the users and gave them dba rights as well as the thirty one other required rights.  (SDE user is normallly created by ESRI's ArcSDE application and it is very persnickedty above the permissions that have been set when you use the Oracle version of it. This is not the case in SQL Server.)   I can't run the data pump.  it keeps asking for Host credentials I thought at first they were talking about Oracle users but it would not accept them.  Then it became apparent (not through any assistance from Oracle documentation,) that they were asking for OS user.  But when I provided the OS user with admin rights, it still replied invalid user/password.   Meanwhile the easy way which would be using the command line I am not being permitted to log into the Oracle instance, with a "TNS could not resolve the connector identifier specified" error constantly being returned.  All this so that the DOT could save a few bucks.  I bet the cost in my time has more than exceeded any savings they may have made by using Oracle.
0
 
Edward JoellAuthor Commented:
I should be able to log in on the command line using sys@arcgis/password as sysdba, or joeller@arcgis/password.  Each returns the same error.  logging in as sys as dba returns TNS: Protocol Adapter error which is correct because the default instance of orcale is not running.  arcgis is the name given to this instance of oracle when it was installed and it is listed the service name in TNSNames.ora.
0
 
slightwv (䄆 Netminder) Commented:
>>Already ahead of you and created the users and gave them dba rights as well as the thirty one other required rights

The other 31 rights are likely not needed.  DBA gives you pretty much everything.  This might not work depending on the level of testing you do.  If it's basic DML then you'll probably be fine.

>>I can't run the data pump...

I assume from the GUI:  I never use the GUI (well, almost never).  Can't help with that.

>>Meanwhile the easy way which would be using the command line...

you said the tnsnames.ora file is set up.  From a command line do:  tnsping ORCL
where ORCL is the entry inthe tnsnames file you are trying to get to.

>> I bet the cost in my time has more than exceeded any savings they may have made by using Oracle.

Oracle is just as confusing to SQL Server folks as SQL Server is to Oracle folks.  Everytime I talk to SQL Server folks I'm glad I'm on Oracle.
0
 
Edward JoellAuthor Commented:
If you can complete this last step I will be eternally grateful and will name my first born slightwv.
0
 
slightwv (䄆 Netminder) Commented:
Caught me typing...

from a command prompt: tnsping arcgis



Now that I clicked submit on the previous post and had a second to think about it:  Why are you still trying to use datapump?
0
 
slightwv (䄆 Netminder) Commented:
*shudders*
>>I will be eternally grateful and will name my first born slightwv

Thanks but...  the points will suffice!!!
0
 
Edward JoellAuthor Commented:
OK tnsping successfully hits arcgis
0
 
slightwv (䄆 Netminder) Commented:
from same command prompt:

sqlplus joeller@arcgis/password


P.S.

I'll be heading for home in a couple minutes.  I'll try to keep checking in on you but my 'testing' ability is very limited at home.  You'll be relying on 'memory' for the most part.

To give me something to read once I get there and crack a beer:
I'm still dying to know:  Why are you still trying to use datapump?
0
 
Edward JoellAuthor Commented:
C:\Documents and Settings\ejoell.ZEKIAHSERVDEV01.001>sqlplus joeller@arcgis/xxxxxx

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 30 16:31:10 2010

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
0
 
Edward JoellAuthor Commented:
According to Oracle you can't use the imp utility if your database uses the datatypes float and some others.  Our schema does.
0
 
Edward JoellAuthor Commented:
Also because 1. because the files are already created.  and 2.  because that is the only thing you can use from OEM.  because I can't get to the command line.
0
 
Edward JoellAuthor Commented:
Ok I guess that you've have headed out.  I will be leaving in the next few minutes and headed home to go to bed sick so I don't know when I will be back to this.  I was hoping to get this done so I wouldn't have to worry about it while I am out but that does not look to be happening.  I am going to try the ExportDDL in SQL Developer and then let that take care of this crap.
0
 
Edward JoellAuthor Commented:
Finally got OEM data pump to run only to be presented with this error

Import Submit Failed

 Errors: ORA-31627: API call succeeded but more information is available ORA-31655: no data or metadata objects selected for job ORA-39154: Objects from foreign schemas have been removed from import Exception : ORA-31627: API call succeeded but more information is available ORA-06512: at "SYS.DBMS_DATAPUMP", line 5067 ORA-06512: at line 2

I am going home too.
 
0
 
slightwv (䄆 Netminder) Commented:
Sorry I missed you.  For when you get back to this:

Do you have multiple ORACLE_HOME's?  If tnsping worked and sqlplus doesn't it almost has to be different HOMEs.  YOu will need to configure the one used by the impdp with the correct entry in tnsnames.ora.

>>you can't use the imp utility if your database uses the datatypes float and some others

If exp can export it, imp had better be able to import it.

>>Also because 1. because the files are already created.  

I don't think impdp can read a file created with the original exp utility.  I thought it was.  WAY back up top in the original post you were trying imp, not datapump.

I'm so confused now.

What I suggest:  Start over from the beginning.

Create a brand new instance.  
Create the necessary tablespaces.  
Create the two schema owners, grant them DBA.  
Use the original imp and specify the two schemas using FROMUSER
0
 
Edward JoellAuthor Commented:
Can't create new instance because other schemas in database need to remain intact.  I meant I had already run the expdp from the database on myh local machine which I used to run the import utility on (remember three step process.)  two schema owners have dba rights and other 31 permissions.  can't run imp utlitily on development server because I can't get to command line.  Yes there are mutilple Oracle_homes because there are at least three Oracle instances on this machine.  The only instance I need is the one with the service "arcgis".  This connects fine from sqldeveloper both on the server and on my client machine.  I can also connect using sqlplus on the command line on my client machine.  but I can't run imp or impdp from client command line or server command line.  I would like to try using imp utility on server but I cannot get it to run because of lack of commandline access. Tried to get latest version of SQL developer so I could use ExportDDL without having to re-write the entire script due to dependacies only to have Oracle report page does not exist.  I am too sick to continue anymore today.  I am going home.
0
 
slightwv (䄆 Netminder) Commented:
>>Can't create new instance because other schemas in database need to remain intact.

Oh well.  That would be the easiest.  It would also clea up and 'features' the full import caused.

>> can't run imp utlitily on development server because I can't get to command line.

You just need to add arcgis to the tnsnames.ora file that houses the imp utility.  There may be multiple HOMEs that have imp installed so you might need to just change the PATH (or fully qualify the imp command to the proper HOME).

>>(remember three step process.)

I remember it.  Just don't understand the reason behind it.  If the end-game is taking just importing the tables/indexes/data from two schemas out of a FULL export, I don't get the intermediate imp/expdp/impdp.

>>Tried to get latest version of SQL developer so I could use ExportDDL

You could have the client execute (you'll probably hate me for just thinking of this now):

dbms_metadata.get_ddl has a schema_export feature to generate...guess what: the DDL for the schema.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/metadata_api.htm

>>I am going home.

Enjoy (as best you can.  I bet this will bother you a little...)
0
 
Edward JoellAuthor Commented:
got an abandoned question notice.  I have been out sick,  The question is not abandonedd I haven't been able tpo work it.
0
 
Edward JoellAuthor Commented:
You just need to add arcgis to the tnsnames.ora file that houses the imp utility.  There may be multiple HOMEs that have imp installed so you might need to just change the PATH (or fully qualify the imp command to the proper HOME).

OK arcgis has been in tnsnames.ora since instnace was created.  So the question is to the tnsnames.ora that houses the imp utlity.  How can I tell which tnsNames.ora does that?  

There may be multiple HOMEs that have imp installed  Almost certainly since the default install includes it and no one here knows any other way to install.  

fully qualify the imp command to the proper HOME how cd to correct directory or by including it as part of command?  Do you think this will resolve the issue of no oracle statement on command line functions?

I used existing sql developer tool to create export ddl script.  then re-arranged created script to deal with uncreated dependacies.  Then ran scripts for first schema from sql  thiere were types created in that schema.  The DDL in the second schema included drop table commands and some create table commands using those types.  the drop table command failed with the error "unknown command"  the create table command with type created in the other schema failed with "invalid datatype"  However the type has been verified to in fact exist.

0
 
Edward JoellAuthor Commented:
Gotta run doctor's appointment.  I will have to get back to this tonight hopefully with better internet connectiviity.
0
 
slightwv (䄆 Netminder) Commented:
>> How can I tell which tnsNames.ora does that?  

Check your PATH environment variable.  the first imp.exe it finds, it will run.

>>Almost certainly since the default install includes it and no one here knows any other way to install.  

Choose custom and don't check the utilities.

>>fully qualify the imp command to the proper HOME how cd to correct directory or by including it as part of command?

Give it a try.  You can manually try setting ORACLE_SID, ORACLE_HOME and PATH.  

For grins, set TNS_ADMIN equal to the path where the 'correct' tnsnames.ora file is.

>> the create table command with type created in the other schema failed with "invalid datatype"  However the type has been verified to in fact exist.

Since you are crossing across schemas, did schemaA grant access to the types for schemaB?
0
 
Edward JoellAuthor Commented:
No you miss the point.  The only person here with any real kind of Oracle knowledge, (me) does not install the Oracle on these machines.  So when they get installed they select the default installation.  (Which may mean some needed things get left out and definitely not needed things get included.)  Infact there are issues with the instances on the subject machine because they have two versions of Oracle and three instances, but two are listening on the same port.   However the issue is still running anything from the command line, because Oracle would not authenticate any login in attempt from there using the arcgis service name.  So I am going to try to navigate to the bin directory of the proper Oracle Home and do an import from their.  

Correct me  if I am wrong but didn't you say that if you run the imp utility against that full dump file that using the foruser paramter, that you would only get the tables and such but not the actual data?  I will wait for a few minutes while getting set up to try it, but if I don't hear anything I will give it a go.   I keep getting errors running the DDL created by SQL developer because it uses a "Type" in one of the most important tables, that has been created by the DDL in the other schema.  Even though the call to the Type is fully qualified, I am still getting invalid datatype error.  Yet the import utlity on my local machine was able to create this table just fine.  go figure
0
 
slightwv (䄆 Netminder) Commented:
>>because Oracle would not authenticate any login in attempt from there using the arcgis service name

This is still likely a tnsnames/ORACLE_HOME issue.  You need to make sure you are using the right tools and right oracle homes.

The TNS_ADMIN enviornment variable is a special one that points to a specific tnsnames.ora file.  I suggest you try using that.


>>, that you would only get the tables and such but not the actual data?

You will get data unless you specify ROWS=N

>> Even though the call to the Type is fully qualified,

You need to explicitly grant execute on the type to the user:

connect as type_owner and execute:  grant execute on mytype to newuser;
0
 
Edward JoellAuthor Commented:
Yoiu need to grant execute rights on the type to the new user.  Did so.

  CREATE TABLE "GISADMIN"."COCOUNTIES"
   (      "NAME" VARCHAR2(20),
      "STATE_NAME" VARCHAR2(25),
      "STATE_FIPS" VARCHAR2(2),
      "CNTY_FIPS" VARCHAR2(3),
      "FIPS" VARCHAR2(5),
      "AREA" NUMBER(22,4),
      "POP1990" NUMBER(38,0),
      "POP1999" NUMBER(38,0),
      "POP90_SQMI" NUMBER(6,0),
      "HOUSEHOLDS" NUMBER(8,0),
      "MALES" NUMBER(8,0),
      "FEMALES" NUMBER(8,0),
      "WHITE" NUMBER(8,0),
      "BLACK" NUMBER(8,0),
      "AMERI_ES" NUMBER(6,0),
      "ASIAN_PI" NUMBER(7,0),
      "OTHER" NUMBER(8,0),
      "HISPANIC" NUMBER(8,0),
      "AGE_UNDER5" NUMBER(7,0),
      "AGE_5_17" NUMBER(8,0),
      "AGE_18_29" NUMBER(8,0),
      "AGE_30_49" NUMBER(8,0),
      "AGE_50_64" NUMBER(8,0),
      "AGE_65_UP" NUMBER(7,0),
      "NEVERMARRY" NUMBER(8,0),
      "MARRIED" NUMBER(8,0),
      "SEPARATED" NUMBER(7,0),
      "WIDOWED" NUMBER(7,0),
      "DIVORCED" NUMBER(7,0),
      "HSEHLD_1_M" NUMBER(7,0),
      "HSEHLD_1_F" NUMBER(7,0),
      "MARHH_CHD" NUMBER(7,0),
      "MARHH_NO_C" NUMBER(7,0),
      "MHH_CHILD" NUMBER(6,0),
      "FHH_CHILD" NUMBER(7,0),
      "HSE_UNITS" NUMBER(8,0),
      "VACANT" NUMBER(7,0),
      "OWNER_OCC" NUMBER(8,0),
      "RENTER_OCC" NUMBER(8,0),
      "MEDIAN_VAL" NUMBER(7,0),
      "MEDIANRENT" NUMBER(4,0),
      "UNITS_1DET" NUMBER(8,0),
      "UNITS_1ATT" NUMBER(7,0),
      "UNITS2" NUMBER(7,0),
      "UNITS3_9" NUMBER(7,0),
      "UNITS10_49" NUMBER(7,0),
      "UNITS50_UP" NUMBER(7,0),
      "MOBILEHOME" NUMBER(6,0),
      "NO_FARMS87" NUMBER(5,0),
      "AVG_SIZE87" NUMBER(6,0),
      "CROP_ACR87" NUMBER(8,0),
      "AVG_SALE87" NUMBER(7,0),
      "SHAPE" "SDE"."ST_GEOMETRY" ,
      "OBJECTID" NUMBER(38,0) NOT NULL ENABLE
   )
Error at Command Line:2,188 Column:15
Error report:
SQL Error: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"
*Cause:    
*Action:
0
 
slightwv (䄆 Netminder) Commented:
If you imported the SDE schema and have properly granted execute on ST_GEOMETRY I'm not sure what would cause this.

This is the closest I can find but you are qualifying the type:
http://resources.arcgis.com/content/kbase?fa=articleShow&d=34004

Even though this isn't the issue, I thought you might find it useful:
http://resources.arcgis.com/content/kbase?fa=articleShow&d=34328

Just for grins log in to sqlplus as GISADMIN and try:
SQL> desc sde.st_geometry

The log in as SDE and try:
SQL> desc st_geometry
0
 
Edward JoellAuthor Commented:
OK.  Right now I am copying the exp dump file to the server so I can try the imp utility.  It tested it already but with the wrong file.  Seems to want to work.  login did not fail.  But I am copying a 411 mb file across Remote Desktop connection via the internet. so it is taking a while. (and freezing up my machine.)
0
 
slightwv (䄆 Netminder) Commented:
>> so it is taking a while

no doubt.  I'm thinking ftp should be an option and probably faster.
0
 
Edward JoellAuthor Commented:
I don't know anything about ftp but I would be surprised if it were enabled on  that machine considering all of the problems we have had with people breaking into it.
0
 
slightwv (䄆 Netminder) Commented:
>>I don't know anything about ftp

File Transfer Protocol.  It was created to transfer files from serverA to serverB over a network back in the day before GUIs and when programmers had to write efficient code.  It still tends to out-perform other file copy methods.

>> would be surprised if it were enabled ...
good point.
0
 
Edward JoellAuthor Commented:
Yes I knew what  it is I just don't know how to use it.
0
 
slightwv (䄆 Netminder) Commented:
oops... sorry.

it's pretty straight forward.

if you can squeek out a few cpu/disk cycles while the copy is going on, go ahead and see if the server responds:

c:\ ftp
ftp> open <ipaddress of server>

If should either say someting like 'connection refused' of prompt you for a username.
0
 
Edward JoellAuthor Commented:
Now I get  with imp utility
Export file created by EXPORT:V11.01.00 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully

HeLLOOO I am  a dba.
I think Oracle just makes up these error messages as it goes along.
0
 
slightwv (䄆 Netminder) Commented:
what username did you specify on the imp command?  Verify that user has been granted the DBA role in the database:

select grantee from dba_role_privs where granted_role='DBA';
0
 
Edward JoellAuthor Commented:
did that did that user is me dba role granted when created.  just checked. still there.
0
 
Edward JoellAuthor Commented:
GRANTEE
------------------------------
SYS
SYSMAN
SDE
JOELLER
SYSTEM
0
 
Edward JoellAuthor Commented:
Gandolf989 on dbasupport.com/forums said run import as system.  So I did that.  It ran generating bunches and bunches of errors.
0
 
Edward JoellAuthor Commented:
get same error that were generated using Export DDL

IMP-00060: Warning: Skipping table "GISADMIN"."BRIDGES" because object type "SDE"."ST_GEOMETRY"  does not exist or has different identifier

Still unable to use that type.
0
 
Edward JoellAuthor Commented:
gave execute priviviledges on all of those sde types to gisadmin.  gave execute privileges on all of those sde stype to System.  made sure gisadmin had dba rights.  Ran script to create public synonyms.  Don't know what else I can do.
0
 
Edward JoellAuthor Commented:
OK I am going to drop both schemas and start again from scratch using only the import utility.
0
 
Edward JoellAuthor Commented:
Everything was working until 5 minutes into the import got an error SQL Statement exceeded bufferlength.  Then all kinds of errors got thrown up.  What buffer length? how can I change that?
0
 
slightwv (䄆 Netminder) Commented:
wow... a lot has happened since I was away.  Got called to a meeting.

importing from system is fine as long as you use fromuser and touser.  Without touser you will import SDEs objects into SYSTEM's schema.

imp has a buffer parameter but I believe that is only for a commit point.  What was the exact error number and message?
0
 
slightwv (䄆 Netminder) Commented:
also:  instead of trying both schemas at the same time, try them seperate (to try and fix the type error).

Do SDE first then GISADMIN.
0
 
Edward JoellAuthor Commented:
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  'This table defines the category names available for a metric class.'
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  'This table provides information about what category is applicable
   for a given metric/policy guid and metric class. The same table is
   used to define mappings for both metrics and policies.'
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  'This table contains details of the collections set up for a metric.'
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  'The table stores the target-metric to collection task association.'

Appears to be addressing table comments

Did not use touser but objects were created in proper schema.  Will drop both users and recreate both users then try to create one at a time using both fromuser touser  Will have to do that tonight since this is a overhead tasking and chargeable tasking comes first.
0
 
Edward JoellAuthor Commented:
Will let you know tomorrow how that comes out as this is an overhead tasking and chargeable tasking has priority.
0
 
Edward JoellAuthor Commented:
I just don't get it this import woked just fine on my Oracle local instance.  So why all the errors now check out the log file.
output.txt
0
 
slightwv (䄆 Netminder) Commented:
The first errors do appear to just be the comments.  If you really want them, from the description of BUFFER in the docs, it shouldn't help but it appears it might:

http://www.dbasupport.com/forums/archive/index.php/t-22907.html

>>ASSOCIATE STATISTICS WITH  ..
This has to do with the Oracle SQL optimizer.  If you really want to know, I can explain it a little better.

I'm not sure why ESRI would want to do this.  Maybe back in the older versions when the Optimizer was crap.  I suggest just ignoring these errors and if you have access to the ArcSDE folks, strongly encourage them to reevaluate that practice.

>>ORA-30512: cannot modify ..

The ArcGIS folks know about this:
http://resources.arcgis.com/content/kbase?fa=articleShow&d=36795

0
 
Edward JoellAuthor Commented:
OK. Tonight or today if I am allowed to I will try to run import for the gisadmin schema.  if that works well great.  250 points is not going to be enough for this effort. I will have to increase it.  Do you know how often points are refreshed? (Since I saw that you were part of the admin staff).
0
 
slightwv (䄆 Netminder) Commented:
>>250 points is not going to be enough for this effort.

Don't worry about the points.  I do this for the challenge and to help others.

I would like to think I would have done the same if this was a 50 point question.

I don't know how often points are updated.  I would hope nightly.

FYI in case ou weren't aware of it:  You can earn a premium membership which provides you unlimited points.  It really isn't that difficult to earn/maintain.  Let me know if you would like more information about this.
0
 
Edward JoellAuthor Commented:
As I read this, the only thing other than the comments that failed to be created were indexes on the "shape" column, which I believe to be spatial indexes.  Is that how you read it/
outputgisad.txt
0
 
slightwv (䄆 Netminder) Commented:
I'm not a SPATIAL person.  Oracle throws around the term 'index' for a lot of things.  This doesn't appear to be a normal BTree type index.  Possibly a SPATIAL index.

Hopefully you know what SDE.st_type_export.validate_spref does and if it matters they didn't run.  If I had to guess from the associated text: Some point/row/??? cannot be validated.  To me that might sound like a bad thing.


This is for an older verion of Oracle and slightly different IMP call but seems relevant.  Maybe it will make sense to you (as a GIS/SDE person):

http://resources.arcgis.com/content/kbase?fa=articleShow&d=22939

Everything I can find on "ORACLE error 29855 " points to a CREATE INDEX statement for a spatial index.  I can't find anything for validate_spref
0
 
Edward JoellAuthor Commented:
looks like it coms down to the error reported in here.  
http://resources.arcgis.com/content/kbase?fa=articleShow&d=34334

If so I should be able to get that since it is more in my area of expertise.  Seems a rather labor intensive means of completing an import.  (Funny when we moved those spatial indexes out to the production server all we had to do was run the create Index code generated by exportdll.  go figure.
0
 
slightwv (䄆 Netminder) Commented:
>>Seems a rather labor intensive means of completing an import

I agree.  Try to not place all the fault on Oracle.  It really is a pretty good database product (even though I'll never be able to convince you of this after this question).

Even though Oracle does things that seem strange to SQL Server folk and probably is partially to blame for all this, I'm sure ArcGIS has it's reasons to do what they did but I'm betting it's because of old Oracle 'features' and they had to kludge some stuff together and they never went back to readdress it since it appears to still work.

After all, their support site seemed to already be aware of most of the problems.
0
 
Edward JoellAuthor Commented:
Oh no ESRI is not off the hook by any means.  However I think the biggest issue is the whole "database = server instance" structure.  The only means of associating structure with function is by schema's which are intimately attached to the users.  Whereas in db2, PostgresSQL, and SQL Server to name a few, a server instances has multiple databases that can be Restored from backups without touching the other databases on the server. In SQL Server this would have taken two files, and four statements 2 of four lines and 2 of one line and 10 minutes to carry out this task. And it could all be done from a SQL Worksheet.
0
 
slightwv (䄆 Netminder) Commented:
The terminologies are pretty different but I still think the basics are the same.  I'm not all that familiar with the underlying architecture of SQL Server but understand the concepts of databases and schemas.

There might have been easier ways to have done this in Oracle other than exp/imp that might be more similar to your 4 step process in SQL Server.  You were just dealt the hand you had to play.

Granted, I've had 'simple' issues with the way exp/imp were written as well.  Typically never this bad.  I have a strong feeling these issues are the reason Oracle decided to deprecate them and create datapump.
0
 
Edward JoellAuthor Commented:
Slightwv stayed with the question through thick and thin through repeated setbacks due to the Oracle setup and the specifics of ESRI's arcSDE, to enable the import to be carried out successfully except for the ArcSDE spatial indexs then pointed the way to resolve the issues that prevented that import so they could be imported manually.  A very positive learning experience.
0
 
Edward JoellAuthor Commented:
Done.  Thank you SlightWV.
0
 
slightwv (䄆 Netminder) Commented:
Glad to help.
0
 
Edward JoellAuthor Commented:
I manaully created the create Index statements following the process in validatespref and using the parameters passed in then ran each one manually to create the missing indexes.  And "that's all folks."
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 52
  • 34
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now