Submit Feedback
Legacy Knowledge Base
Published Sep. 10, 2025

Setting Up Liferay Portal Using Oracle Databases

Written By

Justin Choi

How To articles are not official guidelines or officially supported documentation. They are community-contributed content and may not always reflect the latest updates to Liferay DXP. We welcome your feedback to improve How To articles!

While we make every effort to ensure this Knowledge Base is accurate, it may not always reflect the most recent updates or official guidelines.We appreciate your understanding and encourage you to reach out with any feedback or concerns.

Legacy Article

You are viewing an article from our legacy "FastTrack" publication program, made available for informational purposes. Articles in this program were published without a requirement for independent editing or verification and are provided"as is" without guarantee.

Before using any information from this article, independently verify its suitability for your situation and project.

Liferay Support does not recommend or endorse specific third-party products over others. Liferay is not responsible for any instructions herein or referenced regarding these products. Any implementation of these principles is the responsibility of the subscriber. 

This article is a guide to setting up the Oracle database, connecting it to Liferay, and offers further guidelines for advanced configurations.

Resolution

Setting up a database

  1. Click Start Menu->Oracle - OraDb11g_home1->Application Development->SQL Plus
  2. If there is no prompt for a username/password the SQL command line, type "connect"
  3. Enter the username and password for the administrator when installing Oracle (e.g. user name: system, password: password.)
  4. In the command line, type "create user (name) identified by (password);". (Oracle uses different terminology than other popular databases. In Oracle, each "user" with its corresponding password represents a database.)
  5. In the command line, type "grant all privileges to (name);". This will grant access to the database connection.

Installing Liferay

  1. Accessing the Oracle database from the application server requires a special jar that is not normally available with Liferay. In the installed Oracle instance, navigate to C:\app\Administrator\product\11.2.0\dbhome_1\jdbc\lib. Copy the ojdbc5.jar for JDK5 or the ojdbc6.jar for JDK6.
  2. Navigate to the location of the other dependencies, that is, hsql.jar, portal-service.jar, and the portlet.jar. Paste the ojdbc.jar here.

Connecting the database to Liferay

  1. Create the portal-ext.properties
  2. Place the following text inside if using Oracle 10g Express Edition.

    jdbc.default.driverClassName=oracle.jdbc.driver.OracleDriver 
    jdbc.default.url=jdbc:oracle:thin:@localhost:1521:xe 
    jdbc.default.username=(name)
    jdbc.default.password=(password)

  3. Place the following text inside if using Oracle 11g and Oracle 12c Enterprise Edition.

    jdbc.default.driverClassName=oracle.jdbc.driver.OracleDriver 
    jdbc.default.url=jdbc:oracle:thin:@localhost:1521:orcl 
    jdbc.default.username=(name)
    jdbc.default.password=(password)

  4. "(name)" and "(password)": to correspond to the user we created above.
  5. Change localhost to an IP address if the database is located on a different machine than Liferay.

Advanced Configuration and Tuning Options

What are the absolutely necessary elements of establishing such an installation?

1) An existing or empty Oracle database.

If there is an existing database, do not forget to check the block size of the database, which MUST be created with at least 8K block-size and AL32UTF8 character set. Please note neither the block-size nor the character set can be changed (easily) afterwards.

select value from v$parameter where name = 'db_block_size';

Or if the database has been created with smaller block size (e.g. 4K), users can still search for those table spaces which have been created with a large enough block-size or users can a new one. However it is important to emphasize that maintaining Oracle databases which have table spaces with different block-size instead having everything created with 8K needs quite advanced DBA knowledge.

select tablespace_name from dba_tablespaces where block_size >= 8192;

2) An empty schema for Liferay.

Users can use the following SQL*Plus script to create empty schemas during supporting subscribers on Oracle. It has only one parameter: the schema name.

 

-- create_schema.sql --
set define on
set echo on

-- Create user
create user &1 identified by &1
  default tablespace lportal
  temporary tablespace temp;

-- Give necessary privileges
grant resource to &1 ; grant connect to &1;

-- Default tablespace, you can pick your own if you have created one.
alter user &1 quota unlimited on lportal;

set echo off

One can execute it in the following way:

$ sqlplus / as sysdba
SQL> @create_schema.sql <Liferay's schema name>

3) Permissions (CONNECT, RESOURCE) for the schema user of Liferay.

 

Done by the script above.

4) Configuring the portal-ext.properties

Put the following snippet of code into your portal-ext.properties.

jdbc.default.driverClassName=oracle.jdbc.driver.OracleDriver

# For using SID
jdbc.default.url=jdbc:oracle:thin:@<HOST>:<PORT>:<SID>
# For using Service name
jdbc.default.url=jdbc:oracle:thin:@//<HOST>:<PORT>/<SERVICE>

jdbc.default.username=<Liferay's schema name>
jdbc.default.password=<Password for Liferay's schema>

Oracle has two JDBC url format pattern:

  • Connecting through a SID (aka. System Identifier) for backward compatibility.
  • Connecting through a Service name from Oracle 8i and onwards.

It is worth mentioning that there is a slight difference between a SID and a Service name:

  • The SID identifies a single database instance with a unique name within a certain host. For Oracle Express Edition the SID is "XE" and for Enterprise Edition is "ORCL".
  • A Service name is an alias for an instance (or instances in case of a clustered database) and it allows DBAs to separate physical database architecture from a logical connection name.

5) Creating objects for Liferay

Liferay creates database tables, indices automatically on the first startup, or they can be also created by using the create-oracle.sql script in advance.

 

 

Advanced deployment options

Large enterprises sometimes must conform to stringent security requirements and they tend to have sophisticated storage appliances used for databases.

This section demonstrates how Liferay can be used without having to put the repository's schema user directly into portal-ext.properties and how to separate the storage of tables, indices, and LOBs.

Notation used in this section is the following:

Liferay's default tablespace LPORTAL_DATA
Liferay's tablespace for indices LPORTAL_IDX
Liferay's tablespace for LOBs LPORTAL_LOB
Liferay's temp tablespace LPORTAL_TEMP

Liferay's schema user LPORTAL_REPOS
Liferay's proxy user LPORTAL_PROXY

1) Storage and tablespaces.

-- LPORTAL_DATA will contain table data
create tablespace lportal_data
  datafile '/path/to/yourdb/lportal_data_01.dbf'
  size 64m autoextend on next 32m maxsize 4096m;

-- LPORTAL_IDX will contain index data
create tablespace lportal_idx
  datafile '/path/to/yourdb/lportal_idx_01.dbf'
  size 64m autoextend on next 32m maxsize 4096m;

-- LPORTAL_LOB will contain data of LOBs (BLOB/CLOB)
create tablespace lportal_lob
  datafile '/path/to/yourdb/lportal_lob_01.dbf'
  size 64m autoextend on next 32m maxsize 4096m;

-- LPORTAL_TEMP will be used for temporary storage needed for on-disk sort
operations
create tablespace lportal_temp
  tempfile '/path/to/yourdb/lportal_temp_01.dbf'
  size 64m autoextend on next 32m maxsize 4096m;

Exact recommended size for tablespaces cannot be given because every deployment is different and might be used in various scenarios. Here are a few best practices:

  • Do not start with too large initial size, becuase it can waste space.
  • Do not allow individual data files grow large (beyond 8-10Gb) - it is impractical from a maintenance point of view. Rather add multiple data files (with 8-10Gb maximal size each) instead.
  • Use a single block-size (8K) for the whole DB to avoid buffer pool management pitfalls for non-standard tablespaces (whose block size is different from DB_BLOCK_SIZE).

2) Create schemas

LPORTAL_REPOS will be used for storing Liferay's objects; however it is going to be locked for security reason. The application server will be connecting through a barely technical user (LPORTAL_PROXY) which has privileges for performing SELECT and the regular DML operations on Liferay's objects but it will not be able to ALTER/DROP the objects.

-- User LPORTAL_PROXY
create user LPORTAL_PROXY
  identified by <yourpass>
  default tablespace LPORTAL_DATA
  temporary tablespace LPORTAL_TEMP;

grant create session to LPORTAL_PROXY;

-- User LPORTAL_REPOS
create user LPORTAL_REPOS
  identified by <yourpass>
  account lock
  default tablespace LPORTAL_DATA
  temporary tablespace LPORTAL_TEMP;

alter user LPORTAL_REPOS quota unlimited on LPORTAL_DATA;
alter user LPORTAL_REPOS quota unlimited on LPORTAL_IDX;
alter user LPORTAL_REPOS quota unlimited on LPORTAL_LOB;

grant connect, resource to LPORTAL_REPOS;

3) Create objects underneath LPORTAL_REPOS

From the SQL script (e.g. liferay-portal-sql-6.1.10-ee-ga1/create/create-oracle.sql)  copy the schema creator script to a convenient location and modify it as described below. As the desired repository owner (LPORTAL_REPOS) already exists, the following block at the beginning is no longer necessary.

drop user &1 cascade;
create user &1 identified by &2;
grant connect,resource to &1;
connect &1/&2;

Connect to the target DB as SYSDBA and create the objects by executing the script.

SQL> connect / as sysdba
SQL> alter session set current_schema = LPORTAL_REPOS;
SQL> @create-oracle.sql

 

4) Creating synonyms under LPORTAL_PROXY for the objects of LPORTAL_REPOS

Now LPORTAL_REPOS has all the objects which needed to run Liferay successfully but for security reasons LPORTAL_PROXY will be used.

To make this scenario possible LPORTAL_PROXY will have synonyms for the tables of LPORTAL_REPOS and the necessary privileges (SELECT/INSERT/UPDATE/DELETE) will be granted.

set serveroutput on

declare

  v_owner varchar2(50) := 'LPORTAL_REPOS';
  v_proxy varchar2(50) := 'LPORTAL_PROXY';

  procedure exec(p_sql in varchar2) is
  begin
    dbms_output.put(p_sql || ' ... ');
    execute immediate p_sql;
    dbms_output.put_line('OK');
  exception
    when others then
      dbms_output.put_line(sqlerrm);
  end;

begin

  dbms_output.enable(1024*1024);

  for tab in
    (select table_name from dba_tables where owner = v_owner)
  loop
    -- Create synonym
    exec('create or replace synonym ' ||
      v_proxy || '.' || tab.table_name || ' for ' ||
      v_owner || '.' || tab.table_name);
    -- Grant
    exec('grant select, insert, update, delete on ' ||
      v_owner || '.' || tab.table_name || ' to ' || v_proxy);
  end loop;
end;
/

5) Moving objects to the right tablespace.

From a maintenance point of view it is beneficial to have multiple tablespaces for different kind of objects. Storing indices, tables and LOBs separately is a common best practice. For high throughput DB applications it is also common to establish a data heatmap (how hot a certain dataset is) and put tables onto different tablespaces based on their temperature: frequently accessed tables on fast SSD or slowly changing and less popular data on some kind of compressed media.

set serveroutput on

declare

  v_owner     varchar2(50) := 'LPORTAL_REPOS';

  v_data_tbs  varchar2(50) := 'LPORTAL_DATA';
  v_index_tbs varchar2(50) := 'LPORTAL_IDX';
  v_lob_tbs   varchar2(50) := 'LPORTAL_LOB';

  procedure exec(p_sql in varchar2) is
  begin
    dbms_output.put(p_sql || ' ... ');
    execute immediate p_sql;
    dbms_output.put_line('OK');
  exception
    when others then
      dbms_output.put_line(sqlerrm);
  end;

begin

  dbms_output.enable(1024*1024);

  -- Move tables
  for tab in
    (select table_name from dba_tables where owner = v_owner)
  loop
    exec('alter table ' || v_owner || '.' || tab.table_name ||
      ' move tablespace ' || v_data_tbs);
  end loop;

  -- Move indices
  for idx in
    (select index_name from dba_indexes where owner = v_owner and index_type = 'NORMAL')
  loop
    exec('alter index ' || v_owner || '.' || idx.index_name ||
      ' rebuild tablespace ' || v_index_tbs);
  end loop;

  -- Move LOBs
  for lob in
    (select table_name, column_name from dba_lobs where owner = v_owner)
  loop
    exec('alter table ' || v_owner || '.' || lob.table_name ||
      ' move lob(' || lob.column_name || ')' ||
      ' store as (tablespace ' || v_lob_tbs || ')');
  end loop;

end;

6) Now everything is on its place, change the user name in portal-ext.properties.

jdbc.default.username=LPORTAL_PROXY

Additional Information

http://www.oracle.com/pls/db111/portal.portal_db?selected=11

  • Use at least 8K block-size for the DB.
  • Do not set the NLS_LENGTH_SEMANTICS initialization parameter to CHAR because some of the indices will not be able to be created.
Did this article resolve your issue ?
Legacy Knowledge Base
Did this article resolve your issue ?