Configuring EPiServer CMS6 with an Oracle Database

Scope

This document explains how to configure an EPiServer CMS 6 site with Oracle database and some preliminary steps that should be performed.
This document DOES NOT explain how to:

• Install Oracle database server or client
• Install EPiServer CMS
• Install or configure IIS

Prerequisites

All prerequisites are valid for an EPiServer CMS 6 installation (see system requirements); there are some further prerequisites for configuration with an Oracle database - which are:

1. Oracle Data Provider for .NET  (11g)
   Note: Is downloadable form Oracle in one package “Oracle 11g ODAC” must be installed on the same machine as EPiServer.
2. Oracle ASP.NET Providers (11g)
   Note: Is downloadable form Oracle in one package “Oracle 11g ODAC” which must be installed on the same machine as EPiServer.
3. Oracle Server (any of following):
   a. Oracle 9i 32 bit (Windows server)
   b. Oracle 10g R2 32/64 bit (Windows server)
   c. Oracle 11g 32/64 bit (Windows server)
   Note: The Oracle Servers listed above can be running on a separate server.
   
   Please Note: Oracle Data Provider for .NET referred within this document is the 32-bit flavor. For ODAC 11gx64, download Oracle Database Client 11.1.0.6 for Windows x64 or 11.1.0.7 for Windows Server 2008x64.

Installation Instructions

Since EPiServer Deployment Center for EPiServer CMS 6 does not currently have an installation option for installing a site with an Oracle database the first step you need to complete is to install a site without a database. The following instructions assume that you have already installed an EPiServer CMS site called EPiServerSample1 from the Deployment Center - using the option "Install site without database".

The changes required for configuration to an Oracle database will be performed on this site.

<EPiServer HOME> in these instructions is the path EPiServer CMS 6 is typically installed on: “C:\Program Files\EPiServer\” or “C:\Program Files (x86)\EPiServer\” on 64-bit machines.

<ORACLE HOME> is a directory where you installed your oracle client components, for example “C:\OracleClient\”.

Instructions

1. Download and Install EPiServer CMS 6.
2. Open the SQL Plus Oracle command line client and log in as “system” user.
   NOTE: the password of the system user is set during Oracle Server installation and should be known by DBA.
3. In SQLPlus create a tablespace by executing the following line:
   CREATE TABLESPACE EPISERVER_DATA
LOGGING DATAFILE 'C:/oraclexe/oradata/epdev/EP_DATA.dbf'
SIZE 500M
EXTENT MANAGEMENT LOCAL;
   NOTE: The path is relative to the machine Oracle is installed on and can be changed, if the path is changed remember that Oracle needs to have write permission on it. “EP_DATA.dbf” will be created by Oracle and its name can be changed. Also note that tablespace creation is an example which can be changed with the DBA approval.
4. In SQLPlus copy and paste the following lines of code (Note - you can change the “EPISERVER”  username to one of your preference):
   CREATE USER EPISERVER IDENTIFIED BY EPISERVER
DEFAULT TABLESPACE EPISERVER_DATA
TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE TO EPISERVER;

GRANT CONNECT, RESOURCE TO EPISERVER;
GRANT CHANGE NOTIFICATION TO EPISERVER;
GRANT CREATE JOB TO EPISERVER;
GRANT CREATE PUBLIC SYNONYM TO EPISERVER;
GRANT CREATE PROCEDURE TO EPISERVER;
GRANT CREATE ROLE TO EPISERVER;
GRANT CREATE SESSION TO EPISERVER;
GRANT CREATE TABLE TO EPISERVER;
GRANT CREATE VIEW TO EPISERVER;
GRANT CREATE VIEW TO EPISERVER;
GRANT DROP PUBLIC SYNONYM TO EPISERVER;
GRANT UNLIMITED TABLESPACE TO EPISERVER;
5. In SQLPlus, connect as the newly created user ("EPISERVER" in this example) and execute the following Oracle scripts as below:
   a.@<EPiServer HOME> \Framework\6.<subversion>\Database\Oracle\sql_empty_db.sql
   b..@<EPiServer HOME> \Framework\6.<subversion>\Database\Oracle\sql_proc.sql
   c. @<EPiServer HOME> \CMS\6.<subversion>\Database\Oracle\sql_empty_db.sql
   d. @<EPiServer HOME> \CMS\6.<subversion>\Database\Oracle\sql_proc.sql
   e. @<ORACLE HOME>\product\11.1.0\client_1\ASP.NET\InstallAllOracleASPNETProviders.sql
6. +Open <ORACLE HOME>\product\11.1.0\client_1\Network\Admin folder edit by using text editor the file “tnsnames.ora”. If the file is not present, open your text editor and create new a file with the name “tnsnames.ora” (note that this file name is mandatory).
   Copy and paste the following lines of code in tnsnames.ora file and then save the file:
   EPISERVERORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname or IP>)(PORT = <port>))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <database service name>)
    )
  )
   NOTE: 1. Substitute <hostname or IP>, <port> and <database service name> with appropriate names.
               2. This is an example of the Oracleclient configuration according with the tnsnames.ora found under the Oracle db server. 
7. Open the connectionStrings.config file of your EPiServerSample1 site  and update the following line to the new user created if not existing:
   <add name="EPiServerDB_oracle" connectionString="Data Source=EPISERVERORACLE;User Id=EPISERVER;Password=EPISERVER;" providerName="Oracle.DataAccess.Client"/>
NOTE: the connectionString attribute contains Data Source  section that is identical to the data source alias for Oracle which is inserted in tnsnames.ora file
8. Modify the episerver.config file of your EPiServerSample1 site as below:
   
   In siteSettings node add if not existing following attribute:
   connectionStringName="EPiServerDB_oracle"
   NOTE: the value has to correspond to a name of the connectionString inserted in previous step  that contains providerName=”Oracle.DataAccess.Client”
9. Modify the Web.config file of your EPiServerSample1:
   
   a. Replace the entire profile section with the one below:
   <profile enabled="true" defaultProvider="OracleProfileProvider" automaticSaveEnabled="true">
      <properties>
        <add name="Address" type="System.String" provider="OracleProfileProvider" />
        <add name="ZipCode" type="System.String" provider="OracleProfileProvider" />
        <add name="Locality" type="System.String" provider="OracleProfileProvider" />
        <add name="Email" type="System.String" provider="OracleProfileProvider" />
        <add name="FirstName" type="System.String" provider="OracleProfileProvider" />
        <add name="LastName" type="System.String" provider="OracleProfileProvider" />
        <add name="Language" type="System.String" provider="OracleProfileProvider" />
        <add name="Country" type="System.String" provider="OracleProfileProvider" />
        <add name="Company" type="System.String" provider="OracleProfileProvider" />
        <add name="Title" type="System.String" provider="OracleProfileProvider" />
        <add name="SubscriptionInfo" type="EPiServer.Personalization.SubscriptionInfo, EPiServer" provider="OracleProfileProvider" />
        <add name="CustomExplorerTreePanel" type="System.String" provider="OracleProfileProvider" />
        <add name="FileManagerFavourites" type="System.Collections.Generic.List`1[System.String]" provider="OracleProfileProvider" />
        <add name="EditTreeSettings" type="EPiServer.Personalization.GuiSettings, EPiServer" provider="OracleProfileProvider" />
        <add name="ClientToolsActivationKey" type="System.String" provider="OracleProfileProvider" />
        <add name="FrameworkName" type="System.String" provider="OracleProfileProvider" />       
      </properties>
      <providers>
        <clear />
        <add name="OracleProfileProvider" type="Oracle.Web.Profile.OracleProfileProvider, Oracle.Web, Version=2.111.6.20, Culture=neutral, PublicKeyToken=89b483f429c47342" connectionStringName="EPiServerDB_oracle" applicationName="EPiServerSample1" />
     </providers>
</profile>
   b. Replace the entire roleManager section with the one below:<roleManager enabled="true" defaultProvider="MultiplexingRoleProvider" cacheRolesInCookie="true">
      <providers>
        <clear />
        <add  name="MultiplexingRoleProvider" provider1="OracleRoleProvider" provider2="WindowsRoleProvider" providerMap1="OracleMembershipProvider" providerMap2="WindowsMembershipProvider" type="EPiServer.Security.MultiplexingRoleProvider, EPiServer" />
        <add name="WindowsRoleProvider" applicationName=" EPiServerSample1" type="EPiServer.Security.WindowsRoleProvider, EPiServer" />
        <add name="SqlServerRoleProvider" connectionStringName="EPiServerDB" applicationName=" EPiServerSample1" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
        <add name="OracleRoleProvider" type="Oracle.Web.Security.OracleRoleProvider, Oracle.Web, Version=2.111.6.20, Culture=neutral, PublicKeyToken=89b483f429c47342" connectionStringName="EPiServerDB_oracle" applicationName="EPiServerSample1"/>
      </providers>
    </roleManager>
   c. Replace the entire membership section with the one below:
   <membership enabled="true" defaultProvider="MultiplexingMembershipProvider" userIsOnlineTimeWindow="10">
      <providers>
        <clear />
        <add provider1="OracleMembershipProvider" provider2="WindowsMembershipProvider"
          name="MultiplexingMembershipProvider" type="EPiServer.Security.MultiplexingMembershipProvider, EPiServer" />
        <add name="WindowsMembershipProvider" type="EPiServer.Security.WindowsMembershipProvider, EPiServer" deletePrefix="BUILTIN\" searchByEmail="true" />
        <add name="SqlServerMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="EPiServerDB" requiresQuestionAndAnswer="false" applicationName=" EPiServerSample1" requiresUniqueEmail="true" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
        <add name="OracleMembershipProvider" type="Oracle.Web.Security.OracleMembershipProvider, Oracle.Web, Version=2.111.6.20, Culture=neutral, PublicKeyToken=89b483f429c47342" connectionStringName="EPiServerDB_oracle" applicationName=" EPiServerSample1" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
      </providers>
    </membership>
   d. Replace the line under the <episerver.dataStore> defaultProvider section by:
    
   <dataStore defaultProvider="EPiServerOracleDataStoreProvider">

   e. Comment out the lines related to sqlServer within the <workflowRuntime> section, as below:
   
   <!--<add type="System.Workflow.Runtime.Hosting.SharedConnectionWorkflowCommitWorkBatchService, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />-->
<!--<add type="System.Workflow.Runtime.Hosting.SqlWorkflowPersistenceService, System.Workflow.Runtime, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" UnloadOnIdle="true"/>-->

   
   f. If you wish to enable workflow functionality perform the following steps (i,ii,iii), if you do not require workflow functionality you can skip this step.
   
        i. Ensure that the following lines are included in the <workflowRuntime> section:
   
   <add name="oracle" providerName="Oracle.DataAccess.Client" type="WFTools.Services.Persistence.Ado.AdoPersistenceService, EPiServer.WFTools.Services,Culture=neutral" UnloadOnIdle="true" ConnectionStringName="EPiServerDB_oracle"/>
      <add type="WFTools.Services.Batching.Ado.AdoWorkBatchService, EPiServer.WFTools.Services" ConnectionStringName="EPiServerDB_oracle"/>   
    
        ii. Include the following configuration node:<wftools.services.common.ado>
    <valueReaders>
    <add providerName="Oracle.DataAccess.Client" type="WFTools.Services.Odp.OdpValueReader,EPiServer.WFTools.Services.Odp" />
    </valueReaders>
<resourceProviders>
      <add providerName="Oracle.DataAccess.Client" type="WFTools.Services.Odp.OdpResourceProvider,EPiServer.WFTools.Services.Odp" />
    </resourceProviders>
  </wftools.services.common.ado>
  <wftools.services.persistence.ado>
    <nameResolvers>
      <add providerName="Oracle.DataAccess.Client" type="WFTools.Services.Odp.Persistence.OdpPersistenceNameResolver,EPiServer.WFTools.Services.Odp" />
    </nameResolvers>
  </wftools.services.persistence.ado>

                  iii. Place the EPiserver.Wftools.*.dlls, found under:   
              <EPiServer HOME>\CMS\6.<subversion>\Database\Oracle\bin\   
               into the folder:   
               <MyEPiServerSite>\bin\   
            
            
         10.  If you wish, now you can Install Public Templates from Deployment Center.

The configuration of EPiServer CMS 6 Web site with the Oracle database installation should now be complete.