首页 文章

从先前版本迁移到Oracle 12c：我是否使用多租户容器数据库（CDB）或非CDB数据库

提问于
浏览 次
0

我目前正在尝试为我们已经开发多年的大型项目添加Oracle 12c支持 . 我们目前正在运行Oracle 10g .

如果我运行我在10g中使用的脚本使用12c创建我们的数据库对象，当我尝试创建用户时，我得到一个ORA-65096错误，which is described here .

如果我使用以下修复程序，我可以使用用于10g的脚本创建用户和我的数据库模式：

alter session set "_ORACLE_SCRIPT"=true;

但是，当应用程序执行时，我收到以下权限错误：

EJB Invocation failed on component CommonDaoEJB for method public abstract java.lang.String com.mycompany.myappserver.dao.remote.CommonDao.getSchemaVersion(java.lang.String): javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: ORA-00942: table or view does not exist

    at org.jboss.as.ejb3.tx.CMTTxInterceptor.handleExceptionInOurTx(CMTTxInterceptor.java:166) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:230) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:304) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:190) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ejb3.remote.EJBRemoteTransactionPropagatingInterceptor.processInvocation(EJBRemoteTransactionPropagatingInterceptor.java:80) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ejb3.security.AuthorizationInterceptor.processInvocation(AuthorizationInterceptor.java:106) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ejb3.security.SecurityContextInterceptor.processInvocation(SecurityContextInterceptor.java:76) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:59) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ejb3.component.interceptors.AdditionalSetupInterceptor.processInvocation(AdditionalSetupInterceptor.java:43) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ee.component.TCCLInterceptor.processInvocation(TCCLInterceptor.java:45) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ee.component.ViewService$View.invoke(ViewService.java:165) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.as.ejb3.remote.protocol.versionone.MethodInvocationMessageHandler.invokeMethod(MethodInvocationMessageHandler.java:302) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.as.ejb3.remote.protocol.versionone.MethodInvocationMessageHandler.access$200(MethodInvocationMessageHandler.java:64) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.as.ejb3.remote.protocol.versionone.MethodInvocationMessageHandler$1.run(MethodInvocationMessageHandler.java:196) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) [rt.jar:1.7.0_51]
    at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_51]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_51]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_51]
    at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]
    at org.jboss.threads.JBossThread.run(JBossThread.java:122)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: ORA-00942: table or view does not exist

    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1361) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:816) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:770) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.jboss.as.jpa.container.AbstractEntityManager.find(AbstractEntityManager.java:193) [jboss-as-jpa-7.1.1.Final.jar:7.1.1.Final]
    at com.mycompany.myappserver.ejb.CommonDaoBean.getSystemContextProperty(CommonDaoBean.java:565) [myappserverejb.jar:]
    at com.mycompany.myappserver.ejb.CommonDaoBean.getSchemaVersion(CommonDaoBean.java:67) [myappserverejb.jar:]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_51]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_51]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_51]
    at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_51]
    at org.jboss.as.ee.component.ManagedReferenceMethodInterceptorFactory$ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptorFactory.java:72) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:36) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47) [jboss-as-jpa-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:53) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:51) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:228) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    ... 30 more
Caused by: org.hibernate.exception.SQLGrammarException: ORA-00942: table or view does not exist

    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at com.sun.proxy.$Proxy100.executeQuery(Unknown Source)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1978) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:829) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.loadEntity(Loader.java:2058) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:82) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:72) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3697) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:439) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:420) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:204) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:251) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:148) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:954) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.internal.SessionImpl.get(SessionImpl.java:870) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.internal.SessionImpl.get(SessionImpl.java:863) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:795) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    ... 53 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91) [ojdbc6-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133) [ojdbc6-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206) [ojdbc6-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455) [ojdbc6-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413) [ojdbc6-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034) [ojdbc6-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194) [ojdbc6-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791) [ojdbc6-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866) [ojdbc6-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186) [ojdbc6-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387) [ojdbc6-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431) [ojdbc6-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491) [ojdbc6-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_51]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_51]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_51]
    at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_51]
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    ... 72 more

我认为这与用户没有与CDB相关联，因此没有对架构中的对象具有权限 .

在我开始尝试解决此问题之前，我需要确定是否需要将用户创建为CDB用户或非CDB用户 . 此外，如果用户是CDB用户，如何创建用户应与之关联的CDB？

一些额外的信息 .

  • 用于创建数据库对象的所有数据库交互都是通过jdbc进行的 .

  • 当JBoss像这样启动时，应用程序通过JBoss 7.1.1-final连接使用hibernate和方言设置为10g作为JVM arg：Dhibernate.dialect = org.hibernate.dialect.Oracle10gDialect

  • 执行以下语句以创建数据库对象：

alter session set \"_ORACLE_SCRIPT\"=true

create tablespace myapp4 datafile 'myapp4.dbf' size 100M reuse
  autoextend on next 500M maxsize 25000M

create user myapp4 identified by myapp4

grant all privileges to myapp4

alter user myapp4 quota unlimited on myapp4

执行这些步骤后，将运行一个脚本，在 myapp4 架构中创建表等 .

oracle oracle12c

1 回答

  • 0

    事实证明，这里的根本问题实际上是由使用不正确的jdbc驱动程序jar引起的 .

    通过添加已安装产品附带的odbc7.jar文件（默认情况下安装在此处\ product \ 12.1.0 \ dbhome_1 \ jdbc \ lib），然后通过将以下jvm参数添加到jboss来设置hibernate方言，可以解决此问题 . 启动脚本：

    -Dhibernate.dialect = org.hibernate.dialect.Oracle10gDialect

    在进行这些更改后，旧版10g应用程序已启动并正在运行，并准备在12c中进行测试 .

    所以这里提到的具体问题的答案是：我没有，我能够让我的应用程序包括用于创建10g架构的脚本在12c“按原样”工作，一旦我正确配置了所有内容 .

    回复于

相关问题