David Ghedini

Linux, Java, Oracle, and PostgreSQL


David Ghedini

Sunday Sep 25, 2011

Install Oracle 11g Express (XE) on CentOS

This post will cover basic installation and configuration of Oracle 11g Express Edition (XE) on CentOS.

We will also take a quick look at configuring Application Express (APEX) for 11g XE.

Basic installation is straight forward.

If you just want to get up and running, you can just do steps 1 to 4 below (and 10 and 11 for Apex). The remaining steps (5 to 9) cover basic backup, recovery, and performance configuration.

The full system requirements are here


Your CentOS box should have swap equal to 2xRAM.

On every CentOS installation I have done for XE, I just needed to update/install the packages for libaio, bc, and flex.

[root@ms3 ~]#  yum install libaio bc flex


Step 1: Download and Install Oracle 11g XE rpm



You can download the Oracle XE rpm, oracle-xe-11.2.0-1.0.x86_64.rpm.zip, from the OTN here

Unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip:

[root@ms3 ~]# unzip -q oracle-xe-11.2.0-1.0.x86_64.rpm.zip


This will create the directory Disk1. Change to the Disk1 directory:

[root@ms3 ~]# cd Disk1
[root@ms3 Disk1]# ls
oracle-xe-11.2.0-1.0.x86_64.rpm  response  upgrade


Install the rpm using rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm


[root@ms3 Disk1]# rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracle-xe              ########################################### [100%]
Executing post-install steps...
You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.

[root@ms3 Disk1]#



Step 2: Configure 11g XE Database and Options



When installation completes, run '/etc/init.d/oracle-xe configure' to configure and start the database.

Unless you wish to change the ports, except the defaults and set SYS/SYSTEM password.

[root@ms3 Disk1]# /etc/init.d/oracle-xe configure

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express
Edition.  The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts.  Press  to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:
Confirm the password:

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...Done
Configuring database...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.


The installation created the directory /u01 under which Oracle XE is installed.


Step 3: Set the Environment



To set the required Oracle environment variables, use the oracle_env.sh the script included under cd /u01/app/oracle/product/11.2.0/xe/bin
[root@ms3 Disk1]# cd /u01/app/oracle/product/11.2.0/xe/bin


To set the environment for your current session run '. ./oracle_env.sh':

[root@ms3 bin]# . ./oracle_env.sh


To set the environment permanently for users, add the following to the .bashrc or .bash_profile of the users you want to access the environment:

. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh


You should now be able to access SQL*Plus

[root@ms3 bin]# sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 21 08:17:26 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> connect sys/Password as sysdba
Connected.
SQL>




Step 4: Allow Remote Access to Oracle 11g XE GUI



To allow remote access to Oracle 11g XE GUI (as well as Application Express GUI) issue the following from SQL*Plus

SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

PL/SQL procedure successfully completed.


You should now be able to access the Oracle 11g XE Home Page GUI at:

http://localhost:8080/apex/f?p=4950:1

Replace localhost above with your IP or domain as required.

Log in as SYSTEM using the password you selected in Step 2 above.


Step 5: Move the Flash Recovery Area (Fast Recovery Area)



To protect against disk failure, you should move the Flash Recovery Area to a separate disk.

This is actually now called the Fast Recovery Area, but the existing documentation still refers to it as the Flash Recovery Area

If a separate disk is not in your budget you should, at the very least, move the Flash Recovery Area to a partition other than the Oracle installation directory.

By default, the Fast Recovery Area will be located under /u01/app/oracle/fast_recovery_area

SQL> show parameter DB_RECOVERY_FILE_DEST;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 10G
SQL>
So, to move it elsewhere, first create the new directory

[root@ms3 ~]# mkdir /opt/fra


Change the owner to oracle and the group to dba

[root@ms3 ~]# chown oracle:dba /opt/fra


Now, change the DB_RECOVERY_FILE_DEST to the location you selected above.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/opt/fra';

System altered.

SQL>
To move the files use the movelog.sql script:
SQL> @?/sqlplus/admin/movelogs
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> declare
  2     cursor rlc is
  3        select group# grp, thread# thr, bytes/1024 bytes_k
  4          from v$log
  5        order by 1;
  6     stmt     varchar2(2048);
  7     swtstmt  varchar2(1024) := 'alter system switch logfile';
  8     ckpstmt  varchar2(1024) := 'alter system checkpoint global';
  9  begin
 10     for rlcRec in rlc loop
 11    stmt := 'alter database add logfile thread ' ||
 12                 rlcRec.thr || ' size ' ||
 13                 rlcRec.bytes_k || 'K';
 14        execute immediate stmt;
 15        begin
 16           stmt := 'alter database drop logfile group ' || rlcRec.grp;
 17           execute immediate stmt;
 18        exception
 19           when others then
 20              execute immediate swtstmt;
 21              execute immediate ckpstmt;
 22              execute immediate stmt;
 23        end;
 24        execute immediate swtstmt;
 25     end loop;
 26  end;
 27  /

PL/SQL procedure successfully completed.

SQL>
SQL>


Now, set an appropriate size for the Fast Recovery Area. Use df -h to insure that there is ample space.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G;

System altered.


Verify the new location and size.

SQL> show parameter DB_RECOVERY_FILE_DEST;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /opt/fra
db_recovery_file_dest_size           big integer 20G
SQL>


Step 6: Add Redo Log Members to Groups



You should have at least two Redo Log Groups and each group should have at least two members.

Additionally, the members should be spread across disks (or at least directories)

For whatever reason, only one member is created per group on install.

You can view the redo log files using SQL> SELECT * FROM V$LOGFILE;

Since the default location for the two members is the Flash Recovery Area, the two existing members have been moved to our new FRA.

You should now add an additional member for each group under /u01/app/oracle/oradata/XE

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/XE/log1b.LOG' TO GROUP 1;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/XE/log2b.LOG' TO GROUP 2;

Database altered.

SQL>



Step 7: Set Sessions and Processes Parameters



The default values for parameters and sessions is quite low on the default installation.


SQL> show parameters sessions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     172
shared_server_sessions               integer

SQL> show parameters processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     4
log_archive_max_processes            integer     4
processes                            integer     100



You can increase these parameters.

After each change, you will need to restart the database.

Increase sessions and then bounce database.

SQL> alter system set sessions=250 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233344 bytes
Variable Size             780143616 bytes
Database Buffers          281018368 bytes
Redo Buffers                5541888 bytes
Database mounted.
Database opened.


Verify change to sessions parameter:


SQL> show parameters sessions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     252
shared_server_sessions               integer


Increase processes and restart database

SQL> alter system set processes=200 scope=spfile;

System altered.

SQL>


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233344 bytes
Variable Size             763366400 bytes
Database Buffers          297795584 bytes
Redo Buffers                5541888 bytes
Database mounted.
Database opened.



Verify change to processes parameter:



SQL>  show parameters processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     4
log_archive_max_processes            integer     4
processes                            integer     200
SQL>




Step 8: Enable Archivelog Mode



To enable online or "hot" backups, Archivelog Mode must be enabled.

Additionally, if you do not enable Archivelog Mode and take only offline or "cold" backups, should you need to restore the database you will only be able to restore to the last backup

To enable Archivelog Mode, shutdown the database and then startup mount:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233344 bytes
Variable Size             763366400 bytes
Database Buffers          297795584 bytes
Redo Buffers                5541888 bytes
Database mounted.


Enable Archivelog Mode


SQL> alter database archivelog;

Database altered.



Open the database and verify that Archivelog Mode is enabled

SQL> alter database open;

Database altered.

SQL>

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

SQL>



Step 9: Create Online Backup Script



To create automated backups, you can modify the backup.sh included under /u01/app/oracle/product/11.2.0/xe/config/scripts

Create a directory for your backup script

[root@ms3 ~]# mkdir /opt/ora_backup


Change the owner to oracle and the group to dba

[root@ms3 ~]# chown oracle:dba /opt/ora_backup


Copy the backup.sh script from /u01/app/oracle/product/11.2.0/xe/config/scripts to the directory you created above.

[root@ms3 ~]# cp  /u01/app/oracle/product/11.2.0/xe/config/scripts/backup.sh /opt/ora_backup/backup.sh


Open the backup.sh script in a text editor or vi. The last section will look like this:
else
   echo Backup of the database succeeded.
   echo Log file is at $rman_backup_current.
fi

#Wait for user to press any key
echo -n "Press ENTER key to exit"
read userinp 


Change it to:

else
   echo Backup of the database succeeded.
   echo Log file is at $rman_backup_current.
   mail -s 'Oracle Backup Completed' 'david@davidghedini.com' < /u01/app/oracle/oxe_backup_current.log
fi

#Wait for user to press any key
#echo -n "Press ENTER key to exit"
#read userinp 


The line we added above, mail -s 'Oracle Backup Completed' 'david@davidghedini.com' < /u01/app/oracle/oxe_backup_current.log, will send us an email notification that the backup has completed as well as cat the backup log to the body of the email.

Note that we have also commented out the last two lines of the script (the prompt).

Create a cron job to run the script as user oracle.

You should run it at least once a day. With Archivelog Mode enabled, it is important that backups be taken regularly to prevent the Flash Recovery Area from filling.


Step 10: Oracle 11g XE and Application Express (APEX)



Oracle 11g Express Edition comes with Application Express 4.0.2 already installed.

If you elect to upgrade to the latest version (4.1 as of this writing), you can do so but will loose access to the XE GUI. Not a huge loss, but something to keep in mind.

Although Apex is already installed, you will need to set the Internal Admin password.

To do so, run the apxchpwd.sql located under /u01/app/oracle/product/11.2.0/xe/apex:

Note: pick something simple like Password123! as you will be prompted to change it on first log in anyways.

SQL> @/u01/app/oracle/product/11.2.0/xe/apex/apxchpwd.sql
Enter a value below for the password for the Application Express ADMIN user.


Enter a password for the ADMIN user              []

Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.


Commit complete.

SQL>


You can access the Application Express GUI at:

http://localhost:8080/apex/f?p=4550:1

Replace localhost above with your IP or domain as required.

Workspace: Internal
User Name: admin
Password: (whatever you selected above).

Alternatively, you can access via

http://localhost:8080/apex/f?p=4550:10 or http://localhost:8080/apex/apex_admin

Again, replace localhost above with your IP or domain as required.


Step 11: Oracle 11g XE: Configure EPG or Apex Listener



Unless you have a license for Oracle HTTP Server (OHS), your options are the embedded PLSQL Gateway (EPG) or Apex Listener.

The Application Express that comes installed with Oracle 11g XE is configured using the EPG.

While the EPG is simpler than Apex Listener, it can be painfully slow as of Apex 3.2.

Apex Listener, while quite fast, adds an extra layer of complexity.

You will need to install an application server to run Apex Listener.

I have run Apex Listener on both Tomcat (unsupported) as well as Oracle GlassFish 3.x (supported) and was not impressed with either.

A lot of people who know far more than I do about APEX (read: 99.9999% of the population) like the Apex Listener.

Apex Listener and it's installation guide can be found here.

The Apex Listener installation guide is well done and simple to follow.

If you need to install Oracle GlassFish or GlassFish CE (basic installation is the same), you can use my GlassFish 3.1 instructions here.

If you want to be an outlaw and use Tomcat, you can use my Tomcat 6 installation guide here. or my Tomcat 7 installation guide here..

Oracle APEX Hosting

Bookmark and Share



Comments:

Hi David, Just wanted to thank you for the great content on your website. Definitely useful in my day-to-day geekiness. Take care, CJ @cjtravis

Posted by CJ Travis on December 05, 2011 at 01:59 PM CST #

Excellent tutorial. Just need to add how to set ORACLE_HOME and ORACLE_SID environment variables. Also check that listener.ora uses XE as default for ORACLE_SID in order to use sqlplus without problems.

Posted by Antonio Sobalvarro on March 09, 2012 at 02:13 PM CST #

Excellent write up.. very well compiled info.. will re-blog on my Blog site !!

Posted by Yohan on September 09, 2012 at 01:51 AM CDT #

Excellent!!
Thank You!
I only found a small error in step 5:
the location of the script movelogs is not in @?/sqlplus/admin/movelogs but @?/rdbms/admin/movelogs Worked great!

Posted by Nicola on October 04, 2012 at 05:37 AM CDT #

Great post!
I'm about to move my Apex installation (currently) 10g XE to a linux server and migrate to 11g XE.
Even considering (and selling) myself as an Oracle expert, this guide helped me a lot and made my 11g XE running on Cent OS in a few minutes.

Posted by Kai on October 20, 2012 at 01:45 PM CDT #

Thanks a lot.

Posted by Vlad on December 25, 2012 at 05:57 AM CST #

Hi David, Thanks for the tutorial. It really helped me out.

Posted by Joey Caparas on February 13, 2013 at 10:21 AM CST #

Thanks a ton. Steps worked seamlessly.

Posted by Ashwani on July 09, 2013 at 01:24 AM CDT #

Very good and detailed content. Helps me lot. Thanks David.

Posted by Niraj Chauhan on July 23, 2013 at 06:01 AM CDT #

I have a problem, please help me. After following your steps when I try to connect: sqlplus sys/password as sysdba I get this error: ORA-01031: insufficient privileges (Using CentOS 6.4) Yours JCV

Posted by Jose Carlos Vieira on October 29, 2013 at 02:21 PM CDT #

Dear David, Really like the detailed content. Got my Oracle installed without too much trouble. Only thing now I am stuck that I have installed this on port 8088 but when i am trying to access it I am not able to connect. It always gives error "Unable to connect". What must have gone wrong can you please help me. Thanks. Regards, Siddhartha

Posted by Siddhartha on November 15, 2013 at 12:32 AM CST #

Many thanks, David.

Posted by Robert on November 29, 2013 at 03:23 PM CST #

Many many Thanks. Very helpful post.

Posted by Sajib Nandi on March 02, 2014 at 08:34 AM CST #

For anyone experiencing the 'insufficient privileges' issue, you need to add your user to the `dba` group: $ sudo usermod -a -G dba {username}

Posted by Allan on March 05, 2014 at 04:48 AM CST #

Great post. You provided me with everything I needed and more. Thanks.

Posted by DaShaun on March 17, 2014 at 02:16 PM CDT #

Simply Awesome!! Thanks a ton!!

Posted by Samad on April 16, 2014 at 03:33 AM CDT #

the Oracle 11g XE version, is free or has cost

Posted by Juan Ramirez on April 19, 2014 at 09:21 PM CDT #

Thanks for these notes David. Very clear, concise and useful.

Posted by Niall Mc Phillips on June 11, 2014 at 08:05 AM CDT #

Thanks David, this really helped me.

Posted by Jefferson Lima on July 05, 2014 at 11:19 PM CDT #

I had a problem to remote connection, I used these commands to solve.
[root@oracle11g ~]# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/
[root@oracle11g ~]# export ORACLE_SID=XE
[root@oracle11g ~]# sqlplus /nolog
SQL> connect sys/PASSWORD@XE as sysdba
SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

Posted by Paulo Henrique Alves on August 13, 2014 at 09:15 AM CDT #

Very good information i just followed it and was through except in stage where i got insufficient previledges when logging in as sys/system.
Jose Carlos Vieira - i figured it out we need to login as "oracle"(after unlocking this user) user and follow the remaining steps.

Posted by Prakash on August 20, 2014 at 10:36 PM CDT #

thanks for this arictle, however I seem to geting stuck after shutting down the database: OK SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. NOK SQL> startup mount ORA-12514: TNS:listener does not currently know of service requested in connect descriptor SQL> startup mount ORA-24324: service handle not initialized ORA-01041: internal error. hostdef extension doesn't exist If I execute " /etc/init.d/oracle-xe restart " all fine again

Posted by PietPatat on September 06, 2014 at 09:10 AM CDT #

Thank you David. It was really helpful to install Oracle 11g XE best way in Cent OS 7.0. I like functional examples like above so much :-)

Posted by Marek FECO on November 13, 2014 at 01:47 PM CST #

How to set a non standart HTTPS port 444 for 11g XE ?

Posted by Vitalijus on December 11, 2014 at 12:48 PM CST #

Hi Vitalijus,

You would put Apache in front of Apex and use reverse proxy.

You can then change the listen port to whatever you would like to.

HTH,
David

Posted by David Ghedini on December 17, 2014 at 04:39 PM CST #

David: Thanks a lot for this vast and useful procedure, it is a great knowledge sharing. Greeting from Mexico, city. BR/

Posted by Julio César Hdez J. on April 07, 2015 at 09:39 AM CDT #

Awesome guide. Thanks a lot for sharing this useful information.

Posted by TechEnthusiast on May 20, 2015 at 07:06 AM CDT #

Amazing guide, thank you very much It would be interesting if you add an advice to deactivate the firewall "systemctl stop firewalld" in order to access to the appex from remote sources

Posted by Ricardo on July 10, 2015 at 02:09 AM CDT #

cannot reload http://127.0.0.1:8080/apex/f?p=4950:1 Error: This webpage is not available

Posted by hafiz on September 22, 2015 at 11:42 PM CDT #

Awesome (y)

Posted by 115.248.130.138 on March 07, 2016 at 03:47 AM CST #

Better try to install net-tools to have netstat application

yum install net-tools

it is used by the script

/etc/init.d/oracle-xe configure

Posted by Emmanuel on July 12, 2016 at 12:02 AM CDT #

Great, things always go as expected

Posted by juan.valentin on December 12, 2016 at 12:38 PM CST #

Good content David cudos

Posted by Sagar on February 02, 2017 at 05:46 AM CST #

I have installed oracle 11 xe version on Suse linux system. What are the steps to be followed to restore a database from a backup (Please note that the backup database is from oracle enterprise edition . This back i must restore in express edition)

Posted by Mohan on April 12, 2017 at 07:11 AM CDT #

How to make new database, please?

Posted by Ananda Bayu on May 11, 2017 at 10:04 PM CDT #

Hi Ananda

Oracle XE is a single instance database. It's not possible to create additional instances.
Thanks,
David

Posted by David Ghedini on May 14, 2017 at 09:34 AM CDT #

Excellent post! I have a question: The installer creates an 'oracle' OS user, do you know what password uses for this user? Is it safe to change it's password?

Posted by Gonzalo on August 02, 2017 at 04:08 PM CDT #

Post a Comment:
  • HTML Syntax: Allowed

Main Menu

Built With

Pages

LinkedIn

Tag Cloud

Hosted by:

Navigation

Visitors

Sponsors

petiole Cloud

Feeds