Question about Oracle database dump and username and passwords

[Lawrence Lui]

Hello,
We are trying to stand up a transmart instance locally and seem to be
getting stuck connecting it to the oracle database. Does anybody know
the default usernames/passwords for each of the databases in the dump,
or are they the default ones configured in the xml configuration
files? Thanks.

Larry

[Kees van Bochove]

Hi Larry,

Have you tried biomart_user/biomart_user?

Met vriendelijke groet / Kind regards,

Kees van Bochove

Taskforce leader metabolomics and study capture
Netherlands Bioinformatics Centre - BioAssist

www.thehyve.nl
E ke...@thehyve.nl
T +31(0)30 700 9713
M +31(0)6 10 83 53 64
Skype keesboston

2012/5/31 Lawrence Lui <lawren...@gmail.com>

[Terry]

In the default database each schema X has the username/password combination X/X

[Lawrence Lui]

Thanks for the information.  When I try to start JBOSS I get this error, even thou the oracle import looked successful:

[ConnectionFactoryBindingService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=DefaultDS' to JNDI name 'java:DefaultDS'
16:07:57,102 WARN  [ServiceController] Problem starting service jboss:service=KeyGeneratorFactory,type=HiLo
java.sql.SQLException: ORA-00942: table or view does not exist
\

Also when trying to create the SOLR index thru the dataimport handler, I get this error:
SEVERE: Exception while processing: id document : SolrInputDocument[{}]:org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to execute query: select SAMPLE_ID,TRIAL_NAME,DISEASE,TISSUE_TYPE,DATA_TYPES,BIOBANK,SOURCE_ORGANISM,SAMPLE_TREATMENT,SUBJECT_TREATMENT from i2b2DemoData.sample_categories Processing Document # 1
    at org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:72)
    at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:253)
    at org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:210)
    at org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:39)
    at org.apache.solr.handler.dataimport.SqlEntityProcessor.initQuery(SqlEntityProcessor.java:59)
    at org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEntityProcessor.java:73)
    at org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(EntityProcessorWrapper.java:238)
    at org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:591)
    at org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:267)
    at org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:186)
    at org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:353)
    at org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:411)
    at org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:392)
Caused by: java.sql.SQLException: ORA-00904: "SUBJECT_TREATMENT": invalid identifier

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
    at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:790)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1037)
    at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:830)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1687)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1653)
    at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:246)
    ... 11 more


Any thoughts or ideas would be appreciated, thanks.

Larry

[Lawrence Lui]

Just an update. I saw the documentation and the sql file for the SOLR
index, so that problem is resolved. I still have a problem starting
up the JBOSS server with this error:

--- MBeans waiting for other MBeans ---
ObjectName: jboss:service=KeyGeneratorFactory,type=HiLo
State: FAILED
Reason: java.sql.SQLException: ORA-00942: table or view does not
exist

I Depend On:
jboss:service=TransactionManager
jboss.jca:service=DataSourceBinding,name=DefaultDS

--- MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM ---
ObjectName: jboss:service=KeyGeneratorFactory,type=HiLo
State: FAILED
Reason: java.sql.SQLException: ORA-00942: table or view does not
exist

I Depend On:
jboss:service=TransactionManager
jboss.jca:service=DataSourceBinding,name=DefaultDS



Is there another sql file that I have to ingest to get this portion
functioning? Thanks in advance.

[Tim te Beek]

How did you do your dump import? If you used the command at http://transmartproject.org/wiki/display/TSMTGPL/Installation-GPL+1.0_RC1 you migt now have gotten all the schema's:

sqlldr <username/password> dumpfile=<dumpfile name> logfile=<logfile name> schemas= i2b2hive, i2b2metdata, i2b2sampledata, i2b2demodata, i2b2workdata, i2b2workdata,biomart,biomart_user,deapp,searchapp,tm_cz,tm_lz,tm_wz 

This would explain the message you're receiving.

Instead, we found this to work in our case:

• wget https://github.com/downloads/transmart/transmartApp-DB/transmart_GPL1_for_Oracle_10.2.0.3.dmp.zip

• unzip transmart_GPL1_for_Oracle_10.2.0.3.dmp.zip

• mv   transmart_GPL1_for_Oracle_10.2.0.3.dmp /u01/app/oracle/admin/XE/dpdump/transmart.dmp

• chown oracle:dba /u01/app/oracle/admin/XE/dpdump/transmart.dmp

• impdp SYSTEM dumpfile=transmart.dmp logfile=transmart3.log full=y

[Lawrence Lui]

Thanks, this solved the issue!

On Jun 1, 1:24 pm, Tim te Beek <tim.te.b...@nbic.nl> wrote:
> How did you do your dump import? If you used the command athttp://transmartproject.org/wiki/display/TSMTGPL/Installation-GPL+1.0...you

> migt now have gotten all the schema's:
> sqlldr <username/password> dumpfile=<dumpfile name> logfile=<logfile name>
> schemas= i2b2hive, i2b2metdata, i2b2sampledata, i2b2demodata, i2b2workdata,
> i2b2workdata,biomart,biomart_user,deapp,searchapp,tm_cz,tm_lz,tm_wz
> This would explain the message you're receiving.
>
> Instead, we found this to work in our case:
>

>    - wget
>    https://github.com/downloads/transmart/transmartApp-DB/transmart_GPL1...
>
>    - unzip transmart_GPL1_for_Oracle_10.2.0.3.dmp.zip
>
>    - mv   transmart_GPL1_for_Oracle_10.2.0.3.dmp
>    /u01/app/oracle/admin/XE/dpdump/transmart.dmp
>
>    - chown oracle:dba /u01/app/oracle/admin/XE/dpdump/transmart.dmp
>
>    - impdp SYSTEM dumpfile=transmart.dmp logfile=transmart3.log full=y

[Tim te Beek]

Glad I could help! Feel free to update the wiki to fix this for the next person to come along :)

[John Boles]

Tim,

       Thanks for the update in the instructions and the wiki has been updated accordingly.  Much appreciated.

Thanks,

John