Move the Presto and MashZone Repositories to Oracle

Presto Administration : Getting Started with the Presto Server : Move the Presto and MashZone Repositories to a Robust Database Solution : Move the Presto and MashZone Repositories to Oracle

1. If you are using your LDAP Directory as the Presto User Repository, make sure that at least one user in your LDAP Directory has administrator privileges for Presto before you move the Presto Repository. See Grant User Access to Presto with Built-in Groups for instructions.

When the Presto User Repository is your LDAP Directory, the default administrator account (Administrator user) is disabled.

2. If you are hosting the Presto Repository or MashZone Repository in a new database, create the database following Oracle documentation.

Make sure this database is supported by Presto or MashZone. See Presto and MashZone Database Compatibility for details.

If you want Presto to support international characters in meta-data for artifacts, set the character encoding to AL32UTF8 when you create the database. See documentation for your database for specific instructions.

It is a best practice to require passwords for every database account that can access the Presto Repository.

3. Start the database(s) that will become host to the Presto Repository and/or the MashZone Repository, if they are not already up.

4. Using the SQL tool for the database that will be host, add Presto Repository tables with the scripts shown below from the corresponding folder in presto-install/prestorepository/oracledb:

createDBTables.txt for MetaData and the default User Repository

createSnapsTables.sql for Snapshots

createSchedulerTables.sql for Scheduler

Note:

This folder contains other scripts to drop the corresponding Presto Repository tables.

5. Replace the JAR for the Presto Repository:

a. Remove the web-apps-home/presto/WEB-INF/lib/jackbe-presto-rds-postgresql-derby.jar JAR file for each Presto Server that uses this Presto Repository. You can delete this JAR or simply move it to a folder that is not in the classpath for the application server that hosts Presto.

b. Copy this JAR file:

presto-install/prestorepository/jackbe-presto-rds-oracle-mysql-mssql.jar

To the web-apps-home/presto/WEB-INF/lib folder.

6. Copy the JAR file for the JDBC driver for your database to the following folder for each Presto Server that uses this Presto Repository or MashZone Server that uses this MashZone Repository:

presto-install/apache-tomee-jaxrs/lib

7. Add the Quartz-Oracle JAR to the Presto Server:

a. Download the Quartz 1.7.3 installation ZIP from http://www.quartz-scheduler.org/download/download-catalog.html.

b. Extract the Quartz 1.7.3 installation ZIP.

c. Copy the quartz-oracle-1.7.3.jar file to the presto-install/apache-tomee-jaxrs/lib folder.

d. If this is a clustered environment, copy this JAR file to each Presto Server in the cluster.

8. Update snapshot scheduler configuration for the Presto Server:

a. In the text editor of your choice, open the applicationContext-scheduler.xml file in the webapps-home/presto/WEB-INF/classes/ folder for the Presto Server.

b. Find the org.springframework.scheduling.quartz.SchedulerFactoryBean bean.

c. Update the org.quartz.jobStore.driverDelegateClass property to the org.quartz.impl.jdbcjobstore.oracle.OracleDelegate delegate.

The configuration would now look like:

...
<bean id="scheduler"

>
<property name="applicationContextSchedulerContextKey">
<value>applicationContext</value>
</property>
<property name="quartzProperties">
<props>
<prop key="org.quartz.scheduler.instanceId">AUTO</prop>
<prop key="org.quartz.jobStore.class"> org.quartz.impl.jdbcjobstore.JobStoreTX</prop>
<prop key="org.quartz.jobStore.tablePrefix">QRTZ_</prop>
<prop key="org.quartz.jobStore.driverDelegateClass"> org.quartz.impl.jdbcjobstore.oracle.OracleDelegate</prop>
<prop key="org.quartz.jobStore.dataSource">schedulerDS</prop>
...
</props>
</property>
</bean>
...

d. Save this change.

e. If this is a clustered environment, copy the updated applicationContext-scheduler.xml configuration file to each Presto Server in the cluster.

9. Open the presto-install/apache-tomee-jaxrs/conf/tomee.xml configuration file in the text editor of your choice.

10. For the Presto Repository, edit the <Resource> element with an ID of PrestoRepository and:

a. Update the JDBC driver, URL and credential properties:

<Resource id="PrestoRepository" type="DataSource">
JdbcDriver oracle.jdbc.OracleDriver
JdbcUrl jdbc:oracle:drivertype:@host-name:port:dbname
UserName username
Password password
JtaManaged = false
</Resource>

The JTA managed property must be false.

b. If needed, update optional properties. See TomEE Datasource Properties for a complete list of optional properties and information on defaults.

Some common properties you may need to set include:

validationQuery = select 1 from dual

Common tuning properties for connections pools. See Tuning the Presto or MashZone Repository Connection Pool.

11. For the MashZone Repository, edit the <Resource> element with an ID of amzDatabase and:

a. For Oracle 11g, update the following properties:

<Resource id="amzDatabase" type="DataSource">
JdbcDriver oracle.jdbc.OracleDriver
JdbcUrl jdbc:oracle:thin//:host-name:port/sid
UserName username
Password password
defaultAutoCommit = true
rollbackOnReturn = true
removeAbandoned = true
logAbandoned = true
removedAbandonedTimeout = 5
maxActive = 200
maxIdle = 20
maxOpenPreparedStatements = 0
maxWaitTime = 5000
</Resource>

b. For Oracle 12c, update the following properties:

<Resource id="amzDatabase" type="DataSource">
JdbcDriver oracle.jdbc.OracleDriver
#Oracle 12 url
JdbcUrl jdbc:oracle:drivertype:@host-name:port:dbname
UserName username
Password password
defaultAutoCommit = true
rollbackOnReturn = true
removeAbandoned = true
logAbandoned = true
removedAbandonedTimeout = 5
maxActive = 100
maxIdle = 20
maxOpenPreparedStatements = 0
maxWaitTime = 5000
</Resource>

12. Save your changes to this file.

If the Presto Server does not start up successfully, see Troubleshooting Connections to the Presto Repository for suggestions.

13. Open the rdsJDBC.properties configuration file, from the web-apps-home/presto/WEB-INF/classes folder, in the text editor of your choice and:

a. Comment out the property definitions for the Derby database.

b. Uncomment the properties for Oracle and update these properties to match the JNDI configuration your previously updated for TomEE.

c. Save your changes to this file.

You must keep JDBC and JNDI configuration synchronized for the Presto Repository. The application server is using JNDI to connect to the Presto Repository, but some components still use JDBC information.

14. Restart the Presto Server to apply these changes. This also restarts the MashZone Server.

If the Presto Server wedoes not start up successfully, see Troubleshooting Connections to the Presto Repository for suggestions.

15. Update connection information for the Snapshots Repository:

a. Open Presto Hub and login.

b. Add a JDBC driver for the new database that should host the Snapshots Repository. See Add or Manage JDBC Drivers for instructions on adding JDBC drivers.

c. Expand the JDBC Configuration menu, if needed, and select Datasources.

d. Select SnapshotDatasource and click

Edit.

e. Update configuration to point to the new database. See Add a Data Source for information on specific configuration properties.

f. Click Save.

16. Restart the Presto Server to apply these changes.

17. Load macros required for the Snapshot feature in Presto:

a. Open a command or terminal window and move to the presto-install/presto-cli/bin folder.

b. Enter the appropriate command, shown below, for your operating system:

For Windows	For Linux, OS/X or UNIX
publish-global-macros.bat -u Administrator -p manage -url http://app-server:port/presto/edge/api	./publish-global-macros -u Administrator -p manage -url http://app-server:port/presto/edge/api

Contact Support | Community | Feedback