Configuring EPiServer CMS 5 R2 with an Oracle Database

Scope

This document explains how to configure an EPiServer CMS 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 5 R2 SP2 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” must be installed on the same machine as EPiServer.
3. Microsoft  Oracle .NET Data Provider
4. 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: Currently Oracle Data Provider for .NET exists only in 32-bit flavor which means that EPiServer CMS can be only installed on 32-bit server
   
   However, there are no known problems when Oracle (server components) is installed on a separate server and runs as 64-bit process.

Installation Instructions

Since EPiServer Deployment Center for EPiServer CMS 5 R2 SP2 does not currently have an installation option for installing a site with an Oracle database. The following instructions assume that you have already installed an EPiServer CMS site (with the Public Templates enabled for SQL Server) called EPiServerSample1. 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 5 R2 is installed on, typically: “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 5 R2 SP2.
2. Open the SQL Plus Oracle command line client and login 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 50M
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.
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 copy each of the following lines followed by entering as below:
   a. @<EPiServer HOME> \CMS\5.2.375.7\Database\Oracle\sql_empty_db.sql
   b. @<EPiServer HOME> \CMS\5.2.375.7\Database\Oracle\sql_proc.sql
   c. @<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 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: substitute <hostname or IP>, <port> and <database service name> with appropriate names.
   
7. Open the connectionStrings.config file of your EPiServerSample1 site  and add the following two lines if not existing:
   <add name="EPiServerDB_oracle" connectionString="Data Source=EPISERVERORACLE;User Id=EPISERVER;Password=EPISERVER;" providerName="System.Data.OracleClient" />
<add name="OraAspNetConString" connectionString="User Id= EPISERVER;Password= EPISERVER;Data Source= EPISERVERORACLE;" 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 web.config file of your EPiServerSample1 site as below:
   a. 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=”System.Data.OracleClient”
   b. 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="SqlProfile" type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="EPiServerDB_oracle" applicationName="EPiServerSample" />
        <add name="OracleProfileProvider" type="Oracle.Web.Profile.OracleProfileProvider, Oracle.Web, Version=2.111.6.20, Culture=neutral, PublicKeyToken=89b483f429c47342" connectionStringName="OraAspNetConString" applicationName="EPiServerSample1" />
     </providers>
</profile>
   c. Replace the entire roleManager section with the on 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="OraAspNetConString" applicationName="EPiServerSample1"/>
      </providers>
    </roleManager>
   d.Replace the entire membership section with the one below:
   <membership 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="OraAspNetConString" applicationName=" EPiServerSample1" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
      </providers>
    </membership>
   e. If you wish to enable workflow functionality perform the following steps, if you do not require workflow functionality you can skip this step.  Add the lines below to the workflowRuntime section:
   <add name="oracle" providerName="Oracle.DataAccess.Client" type="WFTools.Services.Persistence.Ado.AdoPersistenceService, EPiServer.WFTools.Services,Culture=neutral" UnloadOnIdle="true" ConnectionString="Data Source= EPISERVERORACLE;User Id=EPISERVER;Password=EPISERVER;"/>
      <add type="WFTools.Services.Batching.Ado.AdoWorkBatchService, EPiServer.WFTools.Services" ConnectionString="Data Source=EPISERVERORACLE;User Id=EPISERVER;Password=EPISERVER;"/>
   f. Add the sections below to the configuration node: <wftools.services.common.ado>
    <valueReaders>
      <add providerName="System.Data.OracleClient" type="WFTools.Services.Common.Ado.Oracle.OracleValueReader,EPiServer.WFTools.Services" />
      <add providerName="Oracle.DataAccess.Client" type="WFTools.Services.Odp.OdpValueReader,EPiServer.WFTools.Services.Odp" />
    </valueReaders>
<resourceProviders>
      <add providerName="System.Data.OracleClient" type="WFTools.Services.Common.Ado.Oracle.OracleResourceProvider,EPiServer.WFTools.Services" />
      <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="System.Data.OracleClient" type="WFTools.Services.Persistence.Ado.Oracle.OraclePersistenceNameResolver,EPiServer.WFTools.Services" />
      <add providerName="Oracle.DataAccess.Client" type="WFTools.Services.Odp.Persistence.OdpPersistenceNameResolver,EPiServer.WFTools.Services.Odp" />
    </nameResolvers>
  </wftools.services.persistence.ado>

g. Place the Episerver.Wftools.*.dll, located found under:
  @<EPiServer HOME> \CMS\5.2.375.7 \Database\Oracle\bin\ , 
to the folder:
@<MyEPiServerSite>\bin\.

The configuration of EPiServer CMS 5 R2 SP2 Web site with the Oracle database installation should be complete.