sexta-feira, 5 de setembro de 2014

Install Oracle Express Edition 11g on Oracle Linux 6.x - Step by Step

1. Introduction

This post show how to install Oracle Express 11g on Oracle Linux 6.x


2. Step-by-Step

This step-by-step is divided into 3 parts:
  • Part I - Download Oracle Express binnary
  • Part II - Check network configurations
  • Part III - Install binnary files downloaded
  • Part IV - Configure Oracle Database
  • Part V - Advanced configuration and customization

2.1. Part I - Download Oracle Express binnary




2.2. Part II -Check network configurations

To avoid problems during execution I recomend you to check your network configurations before executing install script.

a) Step #1: Hostname and DomainName must be resolved

  • Oracle uses HOSTNAME into configurations, so HOSTNAME must be accessible for ping (if not correct it!)

# cat /etc/sysconfig/network | grep 'HOSTNAME' | awk -F"=" '{ print $2 }' | xargs ping $1

If output of this command is "ping unknown host ..." Then you will get errors continuing !!! Go back and configure network parameters until your hostname respond ping from this command:


  • You can get hostname by command "hostname" or "cat /proc/sys/kernel/hostname"
  • You configure hostname editing file "vim /etc/sysconfig/network". This needs reboot after configuration
  • You can edit /etc/hosts and add exactly your "hostname" on entry for 127.0.0.1
# hostname
oracle-linux64.localdomain
# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain oracle-linux64 oracle-linux64.localdomain


2.3. Part III - Install binnary files downloaded


  • Step#1: Unzip Oracle XE installer 


# cd /root
# unzip oracle-xe-11.2.0-1.0.x86_64.rpm



  • Step#2: Install and RPM packagres 


# cd ./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

  • Step#3: Checking installation

# du /u01 --max-depth 5
584248  /u01/app/oracle/product/11.2.0/xe
584248  /u01/app/oracle/product/11.2.0
584248  /u01/app/oracle/product
584248  /u01/app/oracle
584248  /u01/app
584248  /u01
# cd /u01/app/oracle/product/11.2.0/xe/config/log
# ls -la
drwxr-xr-x.   3 root root 4096 Sep  5 11:51 .

dr-xr-xr-x.  26 root root 4096 Sep  5 11:51 .


  • Step#4: Execute post install script. Accept all default configurations (HTTP Port, Listener Port). Enter a valid password for SYS and SYSTEM. Enter default 'y'es to automatically starts. And WAIT +/- 5 minutes

# /etc/init.d/oracle-xe configure
   :
A valid HTTP port for the Oracle Application Express [8080]
A valid port for the Oracle database listener [1521]
A password for the SYS and SYSTEM administrative user accounts
Confirm password for SYS and SYSTEM administrative user accounts
Whether you want the database to start automatically when the computer starts (y/n) [y]
   :
Starting Oracle Net Listener...Done
Configuring database...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.
#


  • Step#5: Check database process are up and running after install
# ps -ef | grep 'oracle'
oracle   17257     1  0 12:14 ?        00:00:00 /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr LISTENER -inherit
oracle   26612     1  0 12:20 ?        00:00:00 xe_pmon_XE
oracle   26614     1  0 12:20 ?        00:00:00 xe_psp0_XE
oracle   26616     1  0 12:20 ?        00:00:00 xe_vktm_XE
oracle   26620     1  0 12:20 ?        00:00:00 xe_gen0_XE
oracle   26622     1  0 12:20 ?        00:00:00 xe_diag_XE
oracle   26624     1  0 12:20 ?        00:00:00 xe_dbrm_XE
oracle   26626     1  0 12:20 ?        00:00:00 xe_dia0_XE
oracle   26628     1  0 12:20 ?        00:00:00 xe_mman_XE
oracle   26630     1  0 12:20 ?        00:00:00 xe_dbw0_XE
oracle   26632     1  0 12:20 ?        00:00:00 xe_lgwr_XE
oracle   26634     1  0 12:20 ?        00:00:00 xe_ckpt_XE
oracle   26636     1  0 12:20 ?        00:00:00 xe_smon_XE
oracle   26638     1  0 12:20 ?        00:00:00 xe_reco_XE
oracle   26640     1  0 12:20 ?        00:00:00 xe_mmon_XE
oracle   26642     1  0 12:20 ?        00:00:00 xe_mmnl_XE
oracle   26644     1  0 12:20 ?        00:00:00 xe_d000_XE
oracle   26646     1  0 12:20 ?        00:00:00 xe_s000_XE
oracle   26648     1  0 12:20 ?        00:00:00 xe_s001_XE
oracle   26650     1  0 12:20 ?        00:00:00 xe_s002_XE
oracle   26652     1  0 12:20 ?        00:00:00 xe_s003_XE
oracle   26733     1  0 12:20 ?        00:00:01 xe_vkrm_XE
oracle   26735     1  0 12:20 ?        00:00:00 xe_qmnc_XE
oracle   26749     1  0 12:20 ?        00:00:00 xe_cjq0_XE
oracle   26783     1  0 12:20 ?        00:00:00 xe_q000_XE
oracle   26785     1  0 12:20 ?        00:00:00 xe_q001_XE
root     26840 26816  0 12:28 pts/0    00:00:00 grep oracle

  • Step#6: Reboot and check if Oracle XE instance started up automatically
# /etc/init.d/oracle-xe stop
Shutting down Oracle Database 11g Express Edition instance.
Stopping Oracle Net Listener.

# reboot

login as: root
root@10.3.0.9's password:
Last login: Fri Sep  5 12:25:19 2014 from 10.3.0.67
#
# ps -ef | grep 'oracle'
oracle    1500     1  0 12:38 ?        00:00:00 /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr LISTENER -inherit
oracle    1564     1  0 12:39 ?        00:00:00 xe_pmon_XE
   :        :      :  :   :                :        :


2.4. Part IV - Configure Oracle Database

  • Step#1: Setting the Oracle Database XE Environment Variables (oracle_env.sh )

login as: root
root@10.3.0.9's password:
Last login: Fri Sep  5 14:14:33 2014 from 10.3.0.67
# . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
# sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 5 14:23:52 2014

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

Enter user-name: SYS/******** AS SYSDBA

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

SQL> 


  • Step#2: Configure manually NLS_LANGUAGE on oracle_env.sh 

# cd /u01/app/oracle/product/11.2.0/xe/bin
# cp oracle_env.sh oracle_env.sh.bkp.2014-09-05
# vim oracle_env.sh


export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

export ORACLE_SID=XE

#export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15

export PATH=$ORACLE_HOME/bin:$PATH

echo ""

echo "Oracle Environment Variables Set ..."

echo ""

echo "ORACLE_HOME=$ORACLE_HOME"

echo "ORACLE_SID=$ORACLE_SID"

echo "NLS_LANG=$NLS_LANG"

echo "PATH=$PATH"

echo ""



  • Step#3: Configure 'oracle' .bash_profile to automatically set environment variables 

login as: root

root@10.3.0.9's password:

Last login: Fri Sep  5 14:14:33 2014 from 10.3.0.67


# su - oracle

# vim ~/.bash_profile
# .bash_profile

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






  • Step#4: Making Oracle Database XE Available to Remote Clients

a) Enable Oracle XE to listen for remote clients


# sqlplus '/ as sysdba'
SQL> 
SQL> exec dbms_xdb.setListenerLocalAccess(false)

PL/SQL procedure successfully completed.

SQL> commit;
SQL> 


b) Check if Iptables/Firewall accept remote Oracle Connections

# iptables -nvL --line-numbers
Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
num   pkts bytes target     prot opt in     out     source               destination
1      588 39946 ACCEPT     all  --  *      *       0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED
2        0     0 ACCEPT     icmp --  *      *       0.0.0.0/0            0.0.0.0/0
3       56  3922 ACCEPT     all  --  lo     *       0.0.0.0/0            0.0.0.0/0
4        0     0 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22
5      355 51380 REJECT     all  --  *      *       0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
num   pkts bytes target     prot opt in     out     source               destination
1        0     0 REJECT     all  --  *      *       0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT 462 packets, 92468 bytes)
num   pkts bytes target     prot opt in     out     source               destination
[root@oracle-linux64 ~]#

As we can see, there is a INPUT rule #1 that accept all sources/destination but only in some states. And ther is a INPUT rule #5 that reject everything. So, we have to disable firewall or add iptable rule to accept Oracle port 1521.

  • Option #1: To disable Iptable do this:
# service iptables stop
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Unloading modules:                               [  OK  ]
# chkconfig iptables off

  • Option #2: To keep Iptables running and add a new rule to accept Oracle Connection, HTTP 8080 do this:
# iptables -I INPUT 1 -p tcp --dport 1521 -j ACCEPT
# iptables -I INPUT 2 -p tcp --dport 8080 -j ACCEPT
# service iptables save
iptables: Saving firewall rules to /etc/sysconfig/iptables:[  OK  ]
# service iptables restart

iptables: Flushing firewall rules:                         [  OK  ]

iptables: Setting chains to policy ACCEPT: filter          [  OK  ]

iptables: Unloading modules:                               [  OK  ]

iptables: Applying firewall rules:                         [  OK  ]



  • Step#4: Enable Oracle Application Express 

SQL>
SQL> EXEC DBMS_XDB.SETHTTPPORT(8080);
SQL> COMMIT;
SQL> SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;
                      
DBMS_XDB.GETHTTPPORT()
----------------------
                  8080
                      
SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
SQL>



    2.5. Part V - Advanced configuration and customization of Oracle XE

    I've added this part because when I tried to restore my database, I'd problems with Character Set. So I decided to add this advanced configuration topic.


    2.5.1. How to force Oracle XE to "reset" configuration and reconfigure


    It is an Oracle XE script premisse that the file "/etc/sysconfig/oracle-xe" exists then database is configured. So, let's  use a little cheat trick to hide file from scripts and reset configurations.

    • Step#1: Stop Service 

    # echo "Stop service ..."
    # /etc/init.d/oracle-xe stop
    #
    # echo "Try to configure, but it's already configured :-( ..."
    # /etc/init.d/oracle-xe configure
    Oracle Database 11g Express Edition is already configured

    # echo "Force reconfigure ..."
    # mv /etc/sysconfig/oracle-xe /etc/sysconfig/oracle-xe.bkp.2014-09-11
    # /etc/init.d/oracle-xe configure


    • Step#2: How to customize configurations script

    There are majors scripts used during installation:
    • /etc/init.d/oracle-xe: Here you can change configurations like ORACLE_SID=XE
    • $ORACLE_HOME/config/scripts/XE.sh: creates "/u01/app/oracle/admin/XE/' directory structures. Here you can change path.
    • $ORACLE_HOME/config/scripts/XE.sql: Call "CloneRmanRestore.sql", rmanRestoreDatafiles.sql", "cloneDBCreation.sql", "postScripts.sql" and "postDBCreation.sql"
    • $ORACLE_HOME/config/scripts/init.ora: Default Pfile used during database clone

    Oracle XE installation scripts use template database restored by RMAN. This backup brings some schema ( 'HR', 'CTXSYS', 'OUTLN', 'MDSYS', 'FLOWS_FILES') used as demo. Owners users comes locked by default.


    2.5.2. How to create Oracle XE database manually


    • Step#1: Stop Service

    #
    # echo "Logged as 'root' shutdown database (if running) ..."
    # /etc/init.d/oracle-xe stop
    Shutting down Oracle Database 11g Express Edition instance.
    Stopping Oracle Net Listener.


    • Step#2: Backup 'init.ora' and 'old datafiles' from existing database (if exists )

    # echo "Login or sudo as 'oracle' ..."
    # su - oracle
    $
    $ echo "Backup init.ora and old datafiles before custom changes ..."
    $ ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
    $ cp $ORACLE_HOME/config/scripts/init.ora $ORACLE_HOME/config/scripts/init.ora.bkp.2014-09-12
    $ mv /u01/app/oracle/oradata/XE /u01/app/oracle/oradata/XE.bkp.2014-09-12
    mkdir /u01/app/oracle/oradata/XE


    • Step#3: Customize 'init.ora'

    $
    $ echo "Customize init.ora (if necessary) ..."
    $ vim $ORACLE_HOME/config/scripts/init.ora
      :
      :
    ###########################################
    # Database Identification
    ###########################################
    db_name=XE
      :
    #memory_target=414187520
    memory_target=780824576
      :


    • Step#4: Create a new database instance

    $
    $ echo "Setting Oracle Environment variables ..."
    $ source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
    $
    $ echo "Run SqlPlus but do *not* connect because database will be recreated ..."
    $
    $ sqlplus /nolog

    SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 12 16:24:21 2014

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

    SQL> CONN / AS SYSDBA
    Connected to an idle instance.
    SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/product/11.2.0/xe/config/scripts/init.ora';
    ORACLE instance started.

    Total System Global Area  780824576 bytes
    Fixed Size                  2230568 bytes
    Variable Size             461375192 bytes
    Database Buffers          314572800 bytes
    Redo Buffers                2646016 bytes
    SQL>
    SQL> SPOOL /u01/app/oracle/product/11.2.0/xe/config/log/CreateManuallyDB.log
    SQL>
    SQL> CREATE DATABASE XE
    USER SYS IDENTIFIED BY oracle123
    USER SYSTEM IDENTIFIED BY oracle123
    LOGFILE GROUP 1 ('/u01/app/oracle/oradata/XE/redo01.log') SIZE 100M,
            GROUP 2 ('/u01/app/oracle/oradata/XE/redo02.log') SIZE 100M,
            GROUP 3 ('/u01/app/oracle/oradata/XE/redo03.log') SIZE 100M
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXLOGHISTORY 1
    MAXDATAFILES 100
    CHARACTER SET WE8ISO8859P15
    NATIONAL CHARACTER SET AL16UTF16
    EXTENT MANAGEMENT LOCAL
    DATAFILE        '/u01/app/oracle/oradata/XE/system01.dbf' SIZE 1G REUSE
    SYSAUX DATAFILE '/u01/app/oracle/oradata/XE/sysaux01.dbf' SIZE 325M REUSE
    DEFAULT TABLESPACE users
       DATAFILE '/u01/app/oracle/oradata/XE/users01.dbf'
       SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
    DEFAULT TEMPORARY TABLESPACE tempts1
       TEMPFILE '/u01/app/oracle/oradata/XE/temp01.dbf'
       SIZE 500M REUSE
    UNDO TABLESPACE UNDOTBS1
       DATAFILE '/u01/app/oracle/oradata/XE/undotbs01.dbf'
       SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

    Database created.


    • Step#5: Run scripts to build data dictionary views

    SQL> CONN / AS SYSDBA
    SQL> @?/rdbms/admin/catalog.sql
    SQL> @?/rdbms/admin/catproc.sql
    SQL> 
    SQL> CONN SYSTEM/*******
    SQL> 
    SQL> @?/sqlplus/admin/pupbld.sql



    • Step#6: Restart database to check everything

    SQL> CONN / AS SYSDBA
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

    SQL> spool off
    #
    # exit # exit from 'oracle' to 'root'
    #
    # /etc/init.d/oracle-xe stop
    # /etc/init.d/oracle-xe start




    2.5.3. How to solve "ORA-39213: Metadata processing is not available" and "ORA-31609: error loading file "kualter.xsl" from file system directory ... SYS.DBMS_METADATA_UTIL"



    • Step#1: Read technical post
    https://community.oracle.com/message/9911695


    • Step#2: Copy files from another Oracle Standard or Oracle Enterprise instalation in same SO version 


    $ tar -cvf /tmp/xsl.tar  $ORACLE_HOME/rdbms/xml/xsl





    • Step#3: Paste files from another Oracle Standard or Oracle Enterprise instalation in same SO version 

    cd $ORACLE_HOME/rdbms/xml
    mkdir xsl
    tar -xvf  /tmp/xsl.tar ./xsl

    • Step#4: Paste files from another Oracle Standard or Oracle Enterprise instalation in same SO version 

    $ sqlplus "'/ as sysdba'"
    SQL> execute sys.dbms_metadata_util.load_stylesheets;

    PL/SQL procedure successfully completed.

    SQL>


    3. References



    Nenhum comentário:

    Postar um comentário