Database Schema

[anand gopalan, modifié il y a 11 années.]

current Dev Enviornment
Liferay6.1.0
Database:Mysql5.5
Database schema name: lportal

Current Production Enviornment

Liferay6.1.0
Database: Oracle11
Database schema name: lportal

My question is,

1. can I use same schema for both local & production?

2. If I connect the local system to production system oracle database and use same schema, our existing production pages may get affect or not.

Please answer.

[David H Nebinger, modifié il y a 11 années.]

You can and should use the same schema between test/production.

[anand gopalan, modifié il y a 11 années.]

David, thanks for quick response.

1. But my worry is if I point into prodcution schema, our production pages will affect or not? Please confirm.

2. Because already lportal schema has lots default tables and values. now If connect from test system to production system, It will not overwrite the any tables?

Please answer. my 2 questions.

[David H Nebinger, modifié il y a 11 années.]

Liferay uses a database and a data directory in the filesystem.

If you point your test system at the production database (without also pointing to the production data directory), you will have some rendering problems, but no errors.

As long as both instances of Liferay are the same version, the test system will not break the prod database.

However, as any good SCM will tell you, you never allow test resources to connect to/access/use production resources.

[anand gopalan, modifié il y a 11 années.]

Thanks David for your reply.

[anand gopalan, modifié il y a 11 années.]

Now, how to switch from one database to aother database.

Currently I am using MySQL5.5 and I want to connect Oracle11.

[László Csontos, modifié il y a 11 années.]

anand gopalan:

Now, how to switch from one database to aother database.

Currently I am using MySQL5.5 and I want to connect Oracle11.

Hi Anand,

I'm using the following snippet of properties to connect to Oracle DBs:

jdbc.default.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.default.url=jdbc:oracle:thin:@//localhost:1521/SERVICE_NAME
jdbc.default.username=xxxxxxx
jdbc.default.password=xxxxxxx

Please do not forget to drop ojdbc6.jar to $CATALINA_HOME/lib/ext.

If you want to migrate your existing data from MySQL -> Oracle, the simplest way to accomplish that is to use the data migration tool (Control Panel -> Server admin -> Data migration). Please see also here uderneath Data Migration.

With best wishes,
Laszlo Csontos

[Hitoshi Ozawa, modifié il y a 11 années.]

My question is,

1. can I use same schema for both local & production?

2. If I connect the local system to production system oracle database and use same schema, our existing production pages may get affect or not.

1. But my worry is if I point into prodcution schema, our production pages will affect or not? Please confirm.

2. Because already lportal schema has lots default tables and values. now If connect from test system to production system, It will not overwrite the any tables?

Since you've said "our existing production pages", I assume that you already have a production server in service and this is not a migration from development to become a production server.

Hope you didn't do this yet. There's another point to consider because you already have a production server in service. If your development properties are different from your production properties, you may get some errors.
Most productions system is set not to cache files so changes can be seen immediately while production system is set to set files to increase performance. Also, if production system may be using LDAP while development system may not be.

Are all the portlets the same? If you have portlet in development which is not in production, information about the portlet will be added to the production database.

[anand gopalan, modifié il y a 11 années.]

As per the guideline I changed from MYSQL to Oracle Database.

Steps I followed
1. changed the properties-ext.properties file

jdbc.default.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.default.url=jdbc:oracle:thin:@ind1.world:1521:xe
jdbc.default.username=XXXX
jdbc.default.password=XXXXX

2. Removed Mysql.jar file
3. placeed ojdbc6.jar in $CATALINA_HOME/lib/ext.


but I still it looking into
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver.

Please let me know any other changes i have to do and FYI I have attached the error.txt

[anand gopalan, modifié il y a 11 années.]

Please help me.

[Krisztián Rostás, modifié il y a 11 années.]

Hi Anand,

Did you make your changes in portal-ext.properties file?

"Steps I followed
1. changed the properties-ext.properties file"

best,
Krisztian

[anand gopalan, modifié il y a 11 années.]

yes Mr.Rostas.
I removed MySQL code and I put Oracle in portal-ext.properties under [liferay\liferay-portal-6.1.0-ce-ga1\tomcat-7.0.23\webapps\ROOT\WEB-INF\classes]

Regards
anand

[anand gopalan, modifié il y a 11 années.]

After I have done below 3 steps and I restart the system.
1.changed the properties-ext.properties file
2. Removed Mysql.jar file
3. placeed ojdbc6.jar


but still tomcat server looking into MySQL Dialect. see the server console,
How come server will look into Oracle database.

Loading jar:file:/C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/lib/portal-impl.jar!/system.properties
09:43:38,712 INFO [EasyConf:122] Refreshed the configuration of all components
09:43:39,015 INFO [ConfigurationLoader:56] Properties for jar:file:/C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/lib/portal-impl.jar!/portal loaded from [file:/C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/classes/portal-developer.properties, file:/C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1/portal-ide.properties, file:/C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1/portal-setup-wizard.properties, file:/C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1/portal-ext.properties, file:/C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/classes/portal-ext.properties, jar:file:/C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/lib/portal-impl.jar!/portal.properties]
Loading jar:file:/C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/lib/portal-impl.jar!/portal.properties
Loading file:/C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/classes/portal-ext.properties
Loading file:/C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1/portal-ext.properties
Loading file:/C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1/portal-setup-wizard.properties
Loading file:/C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1/portal-ide.properties
Loading file:/C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/classes/portal-developer.properties
09:43:43,828 INFO [DialectDetector:80] Determine dialect for MySQL 5
09:43:43,863 INFO [DialectDetector:145] Found dialect org.hibernate.dialect.MySQLDialect
Starting Liferay Portal Community Edition 6.1.0 CE (Paton / Build 6100 / January 6, 2012)
09:44:02,885 INFO [BaseDB:409] Database does not support case sensitive queries
09:44:03,259 INFO [PluginPackageUtil:1096] Reading plugin package for the root context

[Krisztián Rostás, modifié il y a 11 années.]

I suspect that your settings in portal-ext.properties was overwritten by other configuration file (s) which is loaded later, see your log file.
E.g. portal-setup-wizard.properties, which often contains database configuration related parts.
Can you please check that are there any MySQL related attributes inside?

[anand gopalan, modifié il y a 11 années.]

Yes Mr.Rostas,

Below code of Portal-setup-wizard.properties
admin.email.from.name=Test Test
default.admin.first.name=Test
default.admin.last.name=Test
jdbc.default.password=test
liferay.home=C:/Users/agopalan/Downloads/liferay/liferay-portal-6.1.0-ce-ga1
admin.email.from.address=[e-mail]
company.default.name=Liferay
default.admin.email.address=[e-mail]
company.default.locale=en_US
jdbc.default.username=root
jdbc.default.driverClassName=com.mysql.jdbc.Driver
jdbc.default.url=jdbc:mysql://localhost/lportal?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false
setup.wizard.enabled=false
default.admin.screen.name=test

you Mean I want replace with below code(oracle)

#oracle
#
jdbc.default.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.default.url=jdbc:oracle:thin:@ind1.world:1521:xe
jdbc.default.username=XXX
jdbc.default.password=XXXX</b></color>

[Krisztián Rostás, modifié il y a 11 années.]

You can do this, but I think it would be better to remove all jdbc. attributes from here and let portal-ext.properties to do the connection settings with Oracle.

[anand gopalan, modifié il y a 11 années.]

Hello Mr.Rostas,

Small Doubt in below line for oracle database
Oralce:
jdbc.default.url=jdbc:oracle:thin:@ind1.world:1521:xe - Can I give like this?

MySQL:
# jdbc.default.url=jdbc:mysql://localhost/lportal?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false

because In Mysql i specified with localhost and schema name.but i didn't specified like that so how it will identify?

[Gaurav Jain, modifié il y a 11 années.]

anand gopalan:

Hello Mr.Rostas,

Small Doubt in below line for oracle database
Oralce:
jdbc.default.url=jdbc:oracle:thin:@ind1.world:1521:xe - Can I give like this?

MySQL:
# jdbc.default.url=jdbc:mysql://localhost/lportal?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false

because In Mysql i specified with localhost and schema name.but i didn't specified like that so how it will identify?

Its syntax is:
Old syntax
jdbc:oracle:thin:@[HOST][: PORT]:SID
New syntax
jdbc:oracle:thin:@//[HOST][: PORT]/SERVICE


You can find the SID/SERVICE name in your tnsnames.ora file:

XE =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = XE)
   )
 )
ORCL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
   (CONNECT_DATA =
     (SID = ORCL)
   )
 )

Refer to : http://www.orafaq.com/wiki/JDBC

[anand gopalan, modifié il y a 11 années.]

After I add oracle connection code in portal-ext.properties i am getting below error.

I tried to ping tnsping ind1.world but i getting this Message 3511 not found; No message file for product=NETWORK, facility=TNSTNS-03505: Message 3505 not found; No message file for product=NETWORK, facility=TNS

error showing in console. Please let me know how come overcome from this issue

at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5260)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:866)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:842)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:615)
at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:649)
at org.apache.catalina.startup.HostConfig$DeployDescriptor.run(HostConfig.java:1581)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
... 102 more
11:10:55,895 WARN [BasicResourcePool:1841] com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@293b53 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (3). Last acquisition attempt exception:
java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:419)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:536)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:228)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:148)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:375)
at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:422)
at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:678)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:238)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1054)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:308)
... 11 more
Caused by: java.net.UnknownHostException: ind1.world
at java.net.InetAddress.getAllByName0(InetAddress.java:1158)
at java.net.InetAddress.getAllByName(InetAddress.java:1084)
at java.net.InetAddress.getAllByName(InetAddress.java:1020)
at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:171)
at oracle.net.nt.ConnOption.connect(ConnOption.java:123)
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:353)
... 16 more

[Nikhil Nishchal, modifié il y a 11 années.]

Hello Anand,
According to your log its seems wrong host name entry is there.
As per Gaurav's comment connection url should like:

Oralce:
jdbc.default.url=jdbc:oracle:thin:@ind1.world:1521:xe - Can I give like this?

here "ind1.world" is DB host name and "1521" is oracle running instance port no.
Is your hostname is "ind1.world"?
are you providing correct hostname and portno there?

[anand gopalan, modifié il y a 11 années.]

Hello Nikhil,

After I changed into New Syntax like below.i am getting the error. see the attached logs - You can get more information.

jdbc.default.url=jdbc:oracle:thin:@ind1.world:1521/xe.

Is your hostname is "ind1.world"? = Yes
"1521" is oracle running instance port no. - Yes
Logs:
12:06:58,928 WARN [BasicResourcePool:1841] com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@f5e814 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (3). Last acquisition attempt exception:
java.sql.SQLRecoverableException: IO Error: Unknown host specified
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:419)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:536)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:228)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:148)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Caused by: oracle.net.ns.NetException: Unknown host specified
at oracle.net.resolver.HostnameNamingAdapter.resolve(HostnameNamingAdapter.java:191)
at oracle.net.resolver.NameResolver.resolveName(NameResolver.java:133)
at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:404)
at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:678)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:238)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1054)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:308)
... 11 more

[David H Nebinger, modifié il y a 11 années.]

Your error is "Unknown host specified".

This is not a Liferay issue at all.

This is totally an environmental issue. Obviously you don't know how to a) install the oracle driver or b) configure it correctly or c) test the configuration or d) resolve name lookup failures.

Unknown host most likely means d) if you've got the other pieces straight, but at this point you've gone through so many iterations of changes on the database config it's hard to say...

[anand gopalan, modifié il y a 11 années.]

Hello David,
Same configuration I made it in Production box and its successfully running. But in the development box it is not working.
Dev box initially it was MYSQL and now i shifted to Oracle Database.

[David H Nebinger, modifié il y a 11 années.]

So start with the simple case: do a "ping <hostname>" from a dos prompt and see what you get. Replace <hostname> with the host from the jdbc url.

[anand gopalan, modifié il y a 11 années.]

Hello Sir,
tnsping ind1.world.

TNS Ping Utility for 32-bit Windows: Version 11.1.0.7.0 - Production on 29-OCT-2012 16:39:24

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

Message 3511 not found; No message file for product=NETWORK, facility=TNSTNS-03505: Message 3505 not found; No message file for
product=NETWORK, facility=TNS

i try to set the oracle path in the enviornment variable, but it is not success.
ORACLE_HOME=C:\oracle\product\11.1.0\client_1

Anything else i have to do.

[David H Nebinger, modifié il y a 11 années.]

Yes, contact your dba and/or networking group for help setting this up.

[anand gopalan, modifié il y a 11 années.]

Thank you Sir, I will do it.

Regards
Anand

[Nikhil Nishchal, modifié il y a 11 années.]

In place of hostname try it with IP (for localhost)
127.0.0.1
in case if host-entry is not there.

[Gaurav Jain, modifié il y a 11 années.]

anand gopalan:

As per the guideline I changed from MYSQL to Oracle Database.

Steps I followed
1. changed the properties-ext.properties file

jdbc.default.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.default.url=jdbc:oracle:thin:@ind1.world:1521:xe
jdbc.default.username=XXXX
jdbc.default.password=XXXXX

2. Removed Mysql.jar file
3. placeed ojdbc6.jar in $CATALINA_HOME/lib/ext.


but I still it looking into
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver.

Please let me know any other changes i have to do and FYI I have attached the error.txt

Hi Anand,

Just FYI: this change will use a fresh database (I mean if your oracle db is not already containing liferay data, then liferay will populate its tables here)
And will not migrate your mysql data to this oracle db. If you intended for migration, then please follow steps mentioned by László Csontos.


For this particular java.lang.ClassNotFoundException: com.mysql.jdbc.Driver. ; just to confirm have you restarted your server after making these changes in portal-ext?

[anand gopalan, modifié il y a 11 années.]

Hello Jain,

In production box Liferay6.1.0/Oracle and In Dev box Liferay6.1.0/MySql.

Now I want to swithch over in my Dev box Liferay6.1.0/Oracle instead of Mysql.

In Dev MySQL i don't have much data. I don't want data migration, Only I want switch over from MySQL to Oracle thats it.

so can use the step1.step2 & step 3.?
1. changed the properties-ext.properties file

jdbc.default.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.default.url=jdbc:oracle:thin:@ind1.world:1521:xe
jdbc.default.username=XXXX
jdbc.default.password=XXXXX

2. Removed Mysql.jar file
3. placeed ojdbc6.jar in $CATALINA_HOME/lib/ext.

Please let me know is it correct? or i missed anything.

Regards
Anand

[Vitaliy Natarov, modifié il y a 9 années.]

Hello guys!

I have on first server oracle 11g and on second server i want to install liferay 6.2 with oracle ( not mysql). But i have problems.
Please write me how to do it if possible
Many thanks,

Regards,
Vitaliy

[David H Nebinger, modifié il y a 9 années.]

Vitally, please open a new thread as this one is long since dead.

Yes Oracle will work, you just need to install the client jar and connect it up.