Alfresco Enterprise Installation with Oracle Database

The below article is my experience with installing alfresco enterprise edition 3.3.1 with Oracle Database. The installation was not straight forward and lacking good documentation with Oracle configuration. The first impression about the installer is that it is not enterprise friendly. By default the installation supports only MySQL or Postgress configuration. The preferred database is MySQL. More over the installation wizard is totally confusing. The alfresco documentation says that it supports Oracle but in the wizard, you will get only two options. Either select in-built MySQL or configure an existing MySQL server!. What will you do if you want to select other database. I have struggled to figure out and have to do installation experiments to make it work with Oracle database. The below article is the step I have followed to install and configure alfresco enterprise with Oracle database.


Installation Overview
I have followed the below high level steps to install and configure Alfresco with Oracle database on a windows 2003 Server with 1 GB RAM.
1. Create a New Schema for Alfresco in Oracle Database.
2. Install Alfresco
3. Configure Alfresco
4. Start the Alfresco Server

Create New Schema
Follow the below steps to create a schema in Oracle Database.
1. Login to Oracle Database as SYS DBA
2. Run below command to create user "alfresco" [You can choose any name you may wish]
3. create user alfresco identified by alfresco
4. Type below command to grant all privileges
5. grant all privileges to alfresco
6. commit and exit the database.
7. Login to the database and verify the new username and password is working fine.

Install Alfresco Enterprise
1. Run the installer alfresco-enterprise-3.3.1-win-installer.1-win-installer.exe
2. The installatin wizard open.
3. Click next
4. From the components screen, uncheck the components you don't need. I have unchecked SharePoint, Records Management and Quickr. You can choose all of them if you wish.
5. Click Next
6. Select the installation folder of your choice. I have selected "d:\installed\Alfresco"
7. Click Next
8. From the database Option, select the radio button option "I wish to use an existing MySQL Database". [Believe me this is the option to choose Oracle Database].
9. Select an Administrator password.
10. Start the installation.
11. After installation completes, close the wizard to exit.

Configure Alfresco with Oracle
The alfresco documentation for this is totally cloud enabled so you don't see anything other than clouds. Just kidding !.

Follow the below steps for configuration.
1. Stop the alfresco tomcat server if it is already running.
2. Open the file "c:\installed\Alfresco\tomcat\shared\classes\alfresco-global.properties" in a file editor. Note that c:\installed\Alfresco is my particular installation location. You can substitute your install folder to locate the file.
3. Update the below database configuration properties in the file.

db.name=ALFRESCO
db.username=alfresco
db.password=alfresco
db.host=localhost
db.port=1521
db.driver=oracle.jdbc.OracleDriver
db.url=jdbc:oracle:thin:@localhost:1521:ALFRESCO
db.pool.validate.query=SELECT 1 FROM DUAL
hibernate.dialect=org.alfresco.repo.domain.hibernate.dialect.AlfrescoOracle9Dialect 

Note that the values marked in red above are specific to my environment. You may have to change it to point to your database parameters. The db.name parameter represents the Database SID.

Important Note: Do not change the hibernate.dialect parameter value other than from above value. There are quite a few options available out there.

4. Modify the following properties to configure outgoing mail server [Without outgoing mail server,  adding new users to alfresco will take more time and configuration effort].

mail.host=yoursmtphost
mail.port=25
mail.from.default=admin@alfresco.com
mail.smtp.auth=false
mail.protocol=smtp
mail.encoding=UTF-8

Note that the values marked in red above are specific to my environment. You may have to change it to point to your database parameters

5. Save the file and exit.
6. Copy the ojdbc14.jar to d:\installed\Alfresco\tomcat\lib folder. Note that ojdbc14.jar is the oracle driver file for 10g database. You may choose the JAR file corresponding to your version of Oracle database.

Start the Server
Start the server from Windows service and you can access the site from http://yourserver:8080/share and the alfresco explorer from http://yourserver:8080/alfresco

Issue Log
Below are some of the Issues I have faced during the installation and the remedy I have done. There could be better solutions for the problem. You can tell me later!.

1. I am seeing "Unable to update Schema" error in the log file during startup. How can I remove this error?

Answer: To avoid this error Note that you should never add the below property in the alfresco-global.properties.
hibernate.default_schema=alfresco
This is not required for single instance database scenario. If you add the above property then the above error may come.
Once this error is occured, there was no way to remedy the database. I have to drop the schema and recreate it.

The above remedy applies to below mentioned errors as well.

a) "name is already used by an existing object"
b) "Schema auto-update failed"

2. The Log file displays lot of errors that says "unable to create beans"

Answer: Check the Hibernate configuration and repository configurations are done properly as described in the sections above