search

qwazer / oracle-ddl2svn

Automatically exported from code.google.com/p/oracle-ddl2svn
16 stars 3 forks source link

ORA-31603: object "XXXXX" of type DB_LINK not found in schema "PUBLIC" #40

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 

org.springframework.jdbc.UncategorizedSQLException: ConnectionCallback; 
uncategorized SQLException for SQL []; SQL state
 [99999]; error code [31603]; ORA-31603: object "XXXXX" of type DB_LINK not found in schema "PUBLIC"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 628
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1
; nested exception is java.sql.SQLException: ORA-31603: object "XXXXX" of type 
DB_LINK not found in sche
ma "PUBLIC"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 628
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

Not sure, but you probably need to map to "DATABASE LINK" (at least that's how 
it's listed in the _OBJECTS views.

Related, is there a way to exclude database links? As this depends on 
privileges on DBA_DB_LINKS view which the user may not have...

scheme2ddl version 2.0.1

Original issue reported on code.google.com by markd...@gmail.com on 13 Dec 2012 at 12:11

GoogleCodeExporter commented 9 years ago 
Never mind, I guess, it works after granting select_catalog_role.

Original comment by markd...@gmail.com on 13 Dec 2012 at 9:29

GoogleCodeExporter commented 9 years ago 
Processing of public db links may be controlled in advanced config:

http://code.google.com/p/scheme2ddl/wiki/Configuration

<bean id="reader"
...
    <property name="processPublicDbLinks" value="true"/>
    <property name="processDmbsJobs" value="true"/>

Original comment by resh...@gmail.com on 14 Dec 2012 at 6:13

GoogleCodeExporter commented 9 years ago 
[...]
Encountered an error executing the step
org.springframework.jdbc.UncategorizedSQLException: ConnectionCallback; 
uncategorized SQLException for SQL []; SQL state [99999]; error code [31603];
ORA-31603: object "/25da6314_CommonServicesjar" of type JAVA_RESOURCE not found 
in schema "ERRU"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1
; nested exception is java.sql.SQLException: ORA-31603: object 
"/25da6314_CommonServicesjar" of type JAVA_RESOURCE not found in schema "ERRU"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1

This is another error after disabling processPublicDbLinks and processDmbsJobs. 
More tables were processed after disabling those 2 params, but at a certain 
point it gave me this error. I have Oracle Database 11g Release 11.2.0.1.0 - 
Production and scheme2ddl version 2.0.1.

Original comment by iulian.g...@gmail.com on 10 Apr 2013 at 1:58

GoogleCodeExporter commented 9 years ago 
Quick workaround: Add JAVA_RESOURCE to exclude section of advanced config.
http://code.google.com/p/scheme2ddl/wiki/Configuration
 <util:map id="excludes">
  <entry key="JAVA_RESOURCE">
            <set>
                <value>*CommonServicesjar</value>
            </set>
        </entry>

Original comment by resh...@gmail.com on 10 Apr 2013 at 6:10

GoogleCodeExporter commented 9 years ago 
Thank you for help!

It worked with "JAVA RESOURCE" and not with "JAVA_RESOURCE". I listed the 
object types in DB and the text is "JAVA RESOURCE". I don't know why in the 
backtrace of jar it shows "JAVA_RESOURCE".

Another problem:

I want to export multiple schemas using the SYS as SYDBA for login.

Here I get the error:

Exception in thread "main" 
org.springframework.beans.factory.BeanCreationException: Error creating bean 
with name 'step1': Cannot resolve reference to
 bean 'reader' while setting bean property 'itemReader'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating b
ean with name 'reader' defined in file [C:\Users\Iulian 
Ghinea\Downloads\scheme2ddl\cfg.txt]: Error setting property values; nested 
exception is org.s
pringframework.beans.NotWritablePropertyException: Invalid property 
'processSchemas' of bean class [com.googlecode.scheme2ddl.UserObjectReader]: 
Bean
property 'processSchemas' is not writable or has an invalid setter method. Does 
the parameter type of the setter match the return type of the getter?
        at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:275)
        at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:104)

============

My config looks like:

[.....]
    <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource">
        <property name="URL" value="jdbc:oracle:thin:@192.168.0.1:1521:orcl"/>
        <!-- You can choose a DBA user. For example: value="sys as sysdba" -->
        <property name="user" value="sys as sysdba"/>
    <property name="password" value="**********"/>

        <property name="connectionCachingEnabled" value="true"/>
    </bean>

    <bean id="reader" class="com.googlecode.scheme2ddl.UserObjectReader">
        <!-- a comma separated list of schemas for processing. Workes only for DBA users -->
        <!-- property name="processSchemas" value="SCOTT"/ -->
        <property name="processSchemas" value="TEST_SCHEMA"/>
        <property name="userObjectDao" ref="userObjectDao"/>
        <property name="processPublicDbLinks" value="false"/>
        <property name="processDmbsJobs" value="false"/>
    </bean>
[...........]

What is wrong here ?

Thank you!

Original comment by iulian.g...@gmail.com on 11 Apr 2013 at 9:05

GoogleCodeExporter commented 9 years ago 
For export multiplay schemas try SNAPSHOT version of scheme2ddl from 
http://scheme2ddl.googlecode.com/svn/m2/snapshots/com/googlecode/scheme2ddl/2.0.
2-SNAPSHOT/scheme2ddl-2.0.2-20121220.135058-2.jar 
This version with patch https://code.google.com/p/scheme2ddl/issues/detail?id=1 
not released yet

Original comment by resh...@gmail.com on 11 Apr 2013 at 12:34

GoogleCodeExporter commented 9 years ago 
Indeed, it is working with 2.0.2, thank you!

Original comment by iulian.g...@gmail.com on 11 Apr 2013 at 4:22

GoogleCodeExporter commented 9 years ago 
Hello,
I have a problem with a schema export. I gives me the error:

Encountered an error executing the step
org.springframework.jdbc.UncategorizedSQLException: ConnectionCallback; 
uncategorized SQLException for SQL []; SQL state [99999]; error code [31600];
ORA-31600: invalid input value LOB for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 3130
ORA-06512: at "SYS.DBMS_METADATA", line 3185
ORA-06512: at "SYS.DBMS_METADATA", line 4863
ORA-06512: at line 1
; nested exception is java.sql.SQLException: ORA-31600: invalid input value LOB 
for parameter OBJECT_TYPE in function GET_DDL

Version of scheme2ddl is 2.0.2, the same server as before when it was a 
successfull export bot on another schema. I used sys as sysdba for connect and 
a single schema in the schema list.

Previously the message was: Saved [...] ....   (a successfull save). But it 
doesn't say what is the current object that the will get the ddl from, in order 
to see what is the object with the problem when it creashes....

The log should be like:

Saving [...] ....       [done]

and [done] should be added after a sucessful save. In case it crashes, on the 
screen will remain the last object it was working on.

Do you have any ideea from this log what is the problem ?

Best regards!

Original comment by iulian.g...@gmail.com on 15 Apr 2013 at 4:34

GoogleCodeExporter commented 9 years ago 
Hello!
Googe code hosting in read-only mode, so I send answer by direct email.

Try configure excludes map as

    <util:map id="excludes">
       ...
        <entry key="LOB">
            <set>
                <value>*</value>
            </set>
        </entry>
      ...
   </util:map>

Also look at discussion at
http://code.google.com/p/oracle-ddl2svn/issues/detail?id=39

BR, Anton

Original comment by resh...@gmail.com on 16 Apr 2013 at 5:50

GoogleCodeExporter commented 9 years ago 
also created issue 41

Original comment by resh...@gmail.com on 16 Apr 2013 at 5:56

GoogleCodeExporter commented 9 years ago 
fixed in release 2.1

Original comment by resh...@gmail.com on 30 Apr 2013 at 10:04

GoogleCodeExporter commented 9 years ago 
The 2.1 version does not include the processSchemas for mutiple schemas export 
as it is the case with unofficial version 2.0.1 ?

Original comment by iulian.g...@gmail.com on 30 Apr 2013 at 11:14

GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 

Hello!

I'm getting this error with 2.1:

Start getting of user object list for processing
Start getting of user object list for processing
Start getting of user object list for processing
Start getting of user object list for processing
Start getting of user object list for processing
Encountered an error executing the step
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL 
grammar [select t.object_name, t.object_type   from user_objects t  where
t.generated = 'N'    and not exists (select 1           from user_nested_tables 
unt         where t.object_name = unt.table_name) UNION ALL  select rn
ame, 'REFRESH GROUP', NULL  from user_refresh a ]; nested exception is 
java.sql.SQLException: ORA-01789: query block has incorrect number of result co
lumns

        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
[....]

It seems there is an UNION between a sql with 2 columns and a sql with 3 
columns so in cannot union them...

Original comment by iulian.g...@gmail.com on 30 Apr 2013 at 11:26

GoogleCodeExporter commented 9 years ago 
fixed in 2.1.2

Original comment by resh...@gmail.com on 30 Apr 2013 at 2:50

GoogleCodeExporter commented 9 years ago 
iulian.ghinea, you was asking about multi schema support in this thread.
Version 2.2 with multi schema support is released now.

Original comment by resh...@gmail.com on 7 May 2013 at 8:12

GoogleCodeExporter commented 9 years ago 
hello,

thanks for update.

I tried the 2.2.1 version and it is working for some shemas, and for some it 
does not:

for example I have 3 shemas: S1,S2,S3 with similar content type (but different 
number of objects). 
All 3 schemas are set for export in the config file.

The program exports 0 objects for S1 and S2 and all objects for S3. Doesn't 
matter if S3 is first or last. Always export ok S3 but find 0 ojbects for S1 
and S2.

For example, S1 has:

OBJECT_TYPE   OBJECT_COUNT
SEQUENCE    9
PROCEDURE   10
JAVA RESOURCE   1
TRIGGER 3
TABLE   23
INDEX   16
FUNCTION    2
VIEW    4
TYPE    1

And S2 has:
OBJECT_TYPE   OBJECT_COUNT
SEQUENCE    17
PROCEDURE   17
TRIGGER 11
TABLE   28
INDEX   50
VIEW    20
FUNCTION    5

And S3 has:
OBJECT_TYPE   OBJECT_COUNT
SEQUENCE    36
PROCEDURE   43
PACKAGE 1
PACKAGE BODY    1
TYPE BODY   3
TRIGGER 111
INDEX   142
TABLE   73
VIEW    58
FUNCTION    10
TYPE    5

Output, for S3,S1,S2 in this order, is something like:

Will try to process schema list [S3, S1, S2]
Start getting of user object list in schema S3 for processing
Found 900 items for processing in schema S3
[...]
//here it is exporting ok the objects of S3
[...]
Written 364 ddls with user objects from total 900 in schema S3
Skip processing 536 user objects from total 900 in schema S3
scheme2ddl of schema S3 completed in 15 seconds
Start getting of user object list in schema S1 for processing
Found 0 items for processing in schema S1
Written 0 ddls with user objects from total 0 in schema S1
Skip processing 0 user objects from total 0 in schema S1
scheme2ddl of schema S1 completed in 0 seconds
Start getting of user object list in schema S2 for processing
Found 0 items for processing in schema S2
Written 0 ddls with user objects from total 0 in schema S2
Skip processing 0 user objects from total 0 in schema S2
scheme2ddl of schema S2 completed in 0 seconds
Processing schema list [S3, S1, S2] completed

The name of S1 S2 and S3 are ok written in the config, and they exists in the 
DB...

Any ideea ?
( Oracle Database 11g Release 11.2.0.1.0 - Production )

Original comment by iulian.g...@gmail.com on 8 May 2013 at 8:29

GoogleCodeExporter commented 9 years ago 
I mention that the user used for connectig to DB is sys (as sysdba).

Original comment by iulian.g...@gmail.com on 8 May 2013 at 8:31

GoogleCodeExporter commented 9 years ago 
iulian.ghinea,
Are you use custom xml config?
Compatability with old configs (2.0.x, 2.1.x) with multi schema processing not 
preserved. I'll update release notes
Try to add scope='step' to bean 'reader'.

  <bean id="reader" class="com.googlecode.scheme2ddl.UserObjectReader" scope="step">
        <property name="processPublicDbLinks" value="true"/>
        <property name="processDmbsJobs" value="true"/>
    </bean>

Original comment by resh...@gmail.com on 9 May 2013 at 5:00

GoogleCodeExporter commented 9 years ago 

also add autowire="autodetect" and        
 <property name="schemaName" value="#{jobParameters['schemaName']}"/>

    <bean id="reader" class="com.googlecode.scheme2ddl.UserObjectReader" scope="step" autowire="autodetect">
        <property name="processPublicDbLinks" value="true"/>
        <property name="processDmbsJobs" value="true"/>
        <property name="schemaName" value="#{jobParameters['schemaName']}"/>
    </bean>

sample: 
http://code.google.com/p/scheme2ddl/source/browse/trunk/src/main/resources/schem
e2ddl.config.xml?spec=svn149&r=126

xml config without this additional configuration not released yet
http://code.google.com/p/scheme2ddl/source/detail?r=149

 29

        </bean>

Original comment by resh...@gmail.com on 9 May 2013 at 5:06

GoogleCodeExporter commented 9 years ago 
I already have 
<bean id="reader" class="com.googlecode.scheme2ddl.UserObjectReader" 
scope="step" autowire="autodetect">

I used the config used as example in 2.2.1 and just changed the user/pwd and 
the entries with schemas:

    <util:list id="schemaList">
        <value>S3</value>
        <value>S1</value>
        <value>S2</value>
    </util:list>

Original comment by iulian.g...@gmail.com on 9 May 2013 at 5:57

GoogleCodeExporter commented 9 years ago 
I already have: <property name="schemaName" 
value="#{jobParameters['schemaName']}"/>

Original comment by iulian.g...@gmail.com on 9 May 2013 at 5:59

GoogleCodeExporter commented 9 years ago 
Maybe it's bug in parsing CLI parameters.
I'm out of development environment to reproduce it now, please, try to run 
scheme2ddl with -s S1,S2,S3

Original comment by resh...@gmail.com on 9 May 2013 at 6:38

GoogleCodeExporter commented 9 years ago 
I tried with -S "S1,S2,S3" and I have the same result.
It first tried to export S1,S2 but with 0 objects and S3 with all objects. 
(I noticed that the comand parameter "-s" takes precedence over schema list 
from config file)

Original comment by iulian.g...@gmail.com on 9 May 2013 at 10:09

GoogleCodeExporter commented 9 years ago 
Look at query used for find list of oracle objects for processing
http://code.google.com/p/scheme2ddl/source/browse/trunk/src/main/java/com/google
code/scheme2ddl/dao/UserObjectDaoImpl.java#36

run this query aganist db directly as sysdba for schemes S1, S2
is it return non empty result?

Original comment by resh...@gmail.com on 9 May 2013 at 11:27

GoogleCodeExporter commented 9 years ago 
no, the result is not null.

It selected (grouped by type):

SEQUENCE    9
PROCEDURE   10
DATABASE LINK   1
JAVA RESOURCE   1
TRIGGER 3
VIEW    4
INDEX   16
TABLE   23
FUNCTION    2
TYPE    1
CONSTRAINT  46

The result was running the select:

select object_type, count(*) cnt from ( 
select t.object_name, t.object_type  
                      from dba_objects t  
                     where t.generated = 'N'  
                       and t.owner = 'S1'  
                       and not exists (select 1  
                              from user_nested_tables unt 
                             where t.object_name = unt.table_name) 
                     UNION ALL  
                     select rname as object_name, 'REFRESH_GROUP' as object_type  
                     from dba_refresh a  
                     where a.rowner = 'S1'  
                     UNION ALL  
                     select constraint_name as object_name, 'CONSTRAINT' as object_type 
                     from all_constraints  
                     where constraint_type != 'R' and owner = 'S1' 
                     UNION ALL  
                     select constraint_name as object_name, 'REF_CONSTRAINT' as object_type 
                     from USER_CONSTRAINTS  
                     where CONSTRAINT_TYPE = 'R' and OWNER = 'S1'
)
group by object_type;

For S2 the results are:

SEQUENCE    17
DATABASE LINK   7
PROCEDURE   17
TRIGGER 11
VIEW    20
TABLE   28
INDEX   50
FUNCTION    5
CONSTRAINT  87

Original comment by iulian.g...@gmail.com on 9 May 2013 at 1:38

GoogleCodeExporter commented 9 years ago 
Do you have the same issue, if you try export only *one* scheme at once and use 
connection as sysdba?

Provide output log when run with -s S1

Original comment by resh...@gmail.com on 9 May 2013 at 6:53

GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
I used -s "S1" and the output is:

Will try to process schema  [S1]
Start getting of user object list in schema S1 for processing
Found 0 items for processing in schema S1
Written 0 ddls with user objects from total 0 in schema S1
Skip processing 0 user objects from total 0 in schema S1
scheme2ddl of schema S1completed in 0 seconds
Processing schema  [S1] completed

Original comment by iulian.g...@gmail.com on 13 May 2013 at 10:59