Oracle - Oracle - Alfresco - Alfresco Sync Service - Alfresco/Alfresco-Sync-Service/3.0/Alfresco-Sync-Service/Alfresco-Sync-Service/Install/Install-and-configure-databases/Oracle - 3.0

Alfresco recommends that you use a separate Oracle instance for the Sync Service.

The Sync Service isn't packaged with an Oracle driver, so it'll need to be downloaded separately and cited in the start-up. See step Starting the Sync Service.

1. Download the Oracle database connector ojdbc7.jar from the Oracle JDBC Driver download page.

2. Copy the JAR file into the same directory as the Sync Service JAR.

   The JDBC driver for Oracle is in the JAR file: ojdbc7.jar.

   However, if you see the following error, then add the Doracle.jdbc.thinLogonCapability=o3 parameter to JAVA_OPTS:

   java.sql.SQLException: OAUTH marshaling failure
   

3. The Oracle database must be created with the AL32UTF8 character set. Check the current character set by executing:

   SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
   

   Have a look at this quick tutorial to alter the character set: Change Oracle Database Character Set : NLS_CHARACTERSET.

4. Increase the maximum connections setting in the Oracle configuration file. The property processes specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. This effectively determines the maximum number of concurrent users in the system.

   alter system set processes=450 scope=spfile
   

   The value of the db.pool.max property in the  config.yml file must be less than processes.

   If there are multiple Sync Service instances forming a cluster hidden behind a load balancer, processes should be greater than or equal to the sum of all db.pool.max from all config.yml files.

   For example, if processes=450, and there are 3 Sync Service instances, then the correct setting in config.yml is db.pool.max : 150.

5. Create a user with the username given by the sync property, sql.db.username, with password given by the sync property, sql.db.password:

   CREATE USER alfresco IDENTIFIED BY admin;
   

6. Grant the alfresco user Connect and Resource privileges in Oracle.

   1. Grant the user write permissions on all tables and sequences:

      GRANT CONNECT, RESOURCE TO alfresco;
      

   2. Configure the privileges by using one of the following commands:

      ALTER USER alfresco QUOTA <QUOTE_M> ON Users;
      

      or

      GRANT UNLIMITED TABLESPACE TO alfresco
      

      Note

      If the privileges on tablespace "USERS" aren't set correctly, you may see the following error:

      ORA-01950: no privileges on tablespace 'USERS'
      

7. Ensure sql.db.driver and sql.db.url are correctly updated in the config.yml file, for example:

   sql:
       db:
           driver: oracle.jdbc.OracleDriver
           url: jdbc:oracle:thin:@//localhost:1521/xe