quarta-feira, 22 de outubro de 2014

Install Zabbix on Centos-6.4

1. Introduction


This post shows how to install zabbix into CentOs 6.4

2. Step by Step


Step #1 - Update SO packages


# mkdir /tmp/install
# cd /tmp/install/
# wget http://epel.gtdinternet.com/6/i386/epel-release-6-7.noarch.rpm
# rpm -ivh epel-release-6-7.noarch.rpm
# yum -y update

Step #2 - Install Packages dependencies


# yum -y install httpd php php-common php-mysql php-gd php-bcmath php-mbstring php-xml mysql mysql-server mysql-devel net-snmp net-snmp-devel net-snmp-utils net-snmp-libs gcc gcc-devel gcc-devel curl curl-devel

Step #3 - Download Zabbix binnaries


# mkdir /tmp/install
# cd /tmp/install/
# wget http://sourceforge.net/projects/zabbix/files/ZABBIX%20Latest%20Stable/2.4.1/zabbix-2.4.1.tar.gz

Step #4 - Create Zabbix User


# adduser zabbix -s /bin/false

Step #5 - Extract Zabbix source


# tar xzvf zabbix-*.tar.gz
# cd zabbix-*

Step #6 - Start MySQL and create Zabbix database


# service mysqld start
# chkconfig mysqld on
# mysql -u root
mysql> create database zabbix character set utf8;
mysql> grant all privileges on zabbix.* to 'zabbix'@'localhost' identified by 'zabbix';
mysql> exit

# mysql -u root zabbix < database/mysql/schema.sql

# mysql -u root zabbix < database/mysql/images.sql
# mysql -u root zabbix < database/mysql/data.sql

Step #7 - Configure,  Compile and Make


# ./configure --enable-server --with-mysql --with-net-snmp --with-libcurl --enable-agent 

# make install


Step #8 - Configure Zabbix Server - /etc/zabbix


vi /usr/local/etc/zabbix_server.conf 
  :
DBHost=localhost
DBName=zabbix
DBUser=zabbix
  :
LogFileSize=10
  :

Step #9 - Configure Zabbix Agent - /usr/local/etc/zabbix_agentd.conf


vi /usr/local/etc/zabbix_agentd.conf
  :
Server=127.0.0.1
  :

Step #10 - Disable SELinux configuration


vim /etc/selinux/config
  :
SELINUX=disabled
  :



Step #11 - Disable IPTables or Configure IPTables for zabbix


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 Zabbix and HTTP ports ( 80, 10050, 10051) do this:
iptables -I INPUT 1 -p tcp --dport 80    -j ACCEPT
iptables -I INPUT 2 -p tcp --dport 10050 -j ACCEPT
iptables -I INPUT 3 -p tcp --dport 10051 -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 #12 - Start Zabbix Service, check process 

# echo
# echo 'Starting Zabbix (server and agent) ...'
# echo
# zabbix_server
# zabbix_agentd
# ps aux | grep zabbix
  :
zabbix   26603  0.0  0.2 131636  2484 ?        S    11:08   0:00 zabbix_server
zabbix   26607  0.0  0.1 131636  1960 ?        S    11:08   0:00 zabbix_server: configuration syncer [synced configuration in 0.008139 sec, idle 60 sec]
zabbix   26608  0.0  0.1 131636  1556 ?        S    11:08   0:00 zabbix_server: db watchdog [synced alerts config in 0.001322 sec, idle 60 sec]
zabbix   26609  0.0  0.3 229520  3208 ?        S    11:08   0:00 zabbix_server: poller #1 [got 0 values in 0.000006 sec, idle 5 sec]
  :
#

echo
echo 'Adding Zabbix (server and agent) start running during boot ...'
echo
# vi /etc/rc.d/rc.local
  :
/usr/local/sbin/zabbix_server
/usr/local/sbin/zabbix_agentd

Step #13 - Install and configure Zabbix Web Interface (php)

# cp -r frontends/php/* /var/www/html/zabbix/
chown -R apache:apache /var/www/html/zabbix/
# vi /etc/php.ini
  :
max_execution_time = 300
max_input_time = 300
date.timezone = "America/Sao_Paulo"
post_max_size = 16M
  :
# echo
# echo 'Restarting HTTP Server ...'
#

# service httpd restart


Step #14 - Complete Web Installation in your browser

  • http://<zabbix-server-ip-address>/zabbix
  • Press "next" to see "2. Check of pre-requisites"
  • Press "next" to see "3. Configure DB Connection"
  • Configure Database Name "zabbix", User "zabbix" and Password "zabbix"
  • Press "Test" to Test connection and wait for message "Ok"
  • Press "Next" to "4. Zabbix server details"
  • Configure Name "Zabbix Server" 
  • Press "Next" to "5. Pre-Installation summary"
  • Press "Next" to "6. Install". If you get an error message like "Configuration file "/var/www/html/zabbix/conf/zabbix.conf.php" create fail. Check permission and ownership on path "/var/www/html/zabbix/". Change ownership to "apache" and change permission to read, write and execute
  • Press "Finish" to complete installation
  • Sign in using username "admin" and password "zabbix"

Step #15 - Enabling Zabbix Server self monitoring


  • On Zabbix Menu :: "Configuration >> Host": click on hiperlink "not monitored" to change monitoring status. Confirm enable operation


3. References




quarta-feira, 15 de outubro de 2014

terça-feira, 9 de setembro de 2014

Installing NodeJS, PhoneGap, Ant, Android SDK on Windows 8

1. Introduction


This post gathers information about and show steps for installation of phonegap


2. Step-by-Step



  • Step#1: Install  NodeJS


- http://nodejs.org/
- Execute Installer of NodeJS



  • Step#2: Install PhoneGap


// - Windows >> Search :: Search for "node.js command prompt" and execute
- C:\> npm install -g phonegap
- C:\> npm install -g cordova
- Unzip PhoneGap into C:\Program Files\
+ Set Windows Environment Variables
  - PATH=%PATH%;C:\Program Files\phonegap-2.9.1\lib\android\bin


  • Step#3: Install Apache Ant for Windows


- Download binary from http://ant.apache.org/bindownload.cgi
- Execute installer
+ Set Windows Environment Variables
  - ANT_HOME=C:\PROGRA~1\APACHE~1.4
  - PATH=%PATH%;%ANT_HOME%\bin


  • Step#4: Install JAVA JDK


+ Set Windows Environment Variables
  - JAVA_HOME=C:\Program Files\Java\jdk1.7.0_60
  - PATH=%PATH%;%JAVA_HOME%\bin


  • Step#5: Install ADK (Android Development Kit) and Adding SDK Packages


- Download http://developer.android.com/sdk/index.html
+ Set Windows Environment Variables
  - ANDROID_HOME=C:\Program Files\adt-bundle-windows-x86_64\sdk
  - PATH=%PATH%;%ANDROID_HOME%\platform-tools;C:\Program Files\adt-bundle-windows-x86_64\sdk\tools

+ Adding SDK Packages
  - Read http://developer.android.com/sdk/installing/adding-packages.html
  - Run %ANDROID_HOME%\eclipse\eclipse or %ANDROID_HOME%\SDK Manager
  + In Eclipse or Android Studio, click SDK Manager
    + Get the latest SDK tools
      - [X] Android SDK Tools
      - [X] Android SDK Platform-tools
      - [X] Android SDK Build-tools (highest version)
      - [X] SDK Platform
      - [X] ARM EABI v7a System Image
    + Get the support library for additional APIs
      - [X] Android Support Repository
      - [X] Android Support Library
    + Get Google Play services for even more APIs
      - [X] Google Repository
      - [X] Google Play services
      + Install the packages



  • Step#6: To solve "The Import android.support.v7 cannot be resolved" do

Click add external jars "%ANDROID_HOME%/sdk/extras/android/support/v7/appcompat/libs/android-support-v7-appcompat.jar" to Java Build Path



  • Step#7: Run Eclipse ditributed with Install ADK - Android SDK for Windows

a)

C:\..\> cd "%HOMEPATH%\Documents\Evaluation\PhoneGap"
C:\..\>REM create <sub-directory> <package-name> <app-name>
C:\..\> create hello br.com.josemar.hello HelloWorld

b)

- C:\> C:\Program Files\adt-bundle-windows-x86_64-20140702\eclipse\eclipse
- Enter new path for ADK projects Ex: %HOMEPATH%\workspace-adt-bundle-windows
+ Create New Project:
  + Eclipse ADK :: File >> New >> Project
    - Wizard :: Android Project from Existing Code
      - Import Projects :: [Browse...] Root Directory: C:\Users\Josemarsilva\Documents\Evaluation\Phone Gap\projeto



3. References




How To Add Disk to Existing Linux VM on Hyper-v and Create New Volume Group

1. Introduction

This post shows screens captured step-by-step to add new disk to existing Linux VM on Hyper-v, create a new volume called "/u01" to oracle.


2. Step-by-Step

This step-by-step is divided into 3 parts:

  • Part-I: Configure Hyper-v virtual disk and attach to Linux VM
  • Part-II: Configure additional disk as new Logical Volume (LVM)
  • Part-III: Configure additional disk as new mount point

In this case we suppose that we need to add a new volume group or mount point /u01 for Oracle Database Installer.

2.1. Part-I: Configure Hyper-v virtual disk and attach to Linux VM

  • Edit Hyper-v VM

  • Configure VM Disk as follows:








  • Start Hyper-v VM:
  • Loggin on Linux VM and find out device added:

  • Create new disk partition for new device added using FDISK:






2.2. Part-II: Configure additional disk as new Logical Volume (LVM)

  • Create Physical Volume for disk partition /dev/sdb2
  • Create Volume Group for disk partition /dev/sdb2
  • Create Logical Volume:
  • Create and format FileSystem for Logical Volume
  • Create mount point "/u01" and mount disk manually (only testing)
  • Identify blockid to configure fstab to automatically mount point "/u01" on next boot:




2.3. Part-III: Configure additional disk as new mount point












segunda-feira, 8 de setembro de 2014

Oracle Data Pump Export / Import Examples

1. Introduction

This post show step-by-step how to configure DataPump Directory, how to Export and how to Import using.

1.1. Pre-requisites

Oracle Data Pump tool requires a directory configured on database. Example


2. Examples

2.1. Create Directory for DataPump Export/Import and adjust permission


SQL> CREATE DIRECTORY DPUMP_BACKUP AS '/u01/backup/dpump';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY DPUMP_BACKUP TO SYSTEM, SYS;

Grant succeeded.


2.2. Create Physical DataPump Directory

# mkdir /u01/backup
# mkdir /u01/backup/dpump
# chown -R oracle:dba /u01/backup/


2.3. Example #1: Export DataPump Full Database mode

$ expdp "'/ AS SYSDBA'" DIRECTORY=DPUMP_BACKUP DUMPFILE=expdp_FULL.dmp LOGFILE=expdp_FULL.log FULL=Y REUSE_DUMPFILES=Y DATA_OPTIONS=XML_CLOBS

Export: Release 11.2.0.2.0 - Production on Fri Sep 5 23:27:57 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" DIRECTORY=DPUMP_BACKUP DUMPFILE=expdp_FULL.dmp LOGFILE=expdp_FULL.log FULL=Y REUSE_DUMPFILES=Y DATA_OPTIONS=XML_CLOBS
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 164.3 MB
Processing object type DATABASE_EXPORT/TABLESPACE
      :
      :
      :
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /u01/backup/dpump/expdp_FULL.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 23:29:28



2.4. Example #2: Export DataPump some Schemas Database mode

expdp "'/ AS SYSDBA'" DIRECTORY=DPUMP_BACKUP DUMPFILE=expdp_FULL.dmp LOGFILE=expdp_FULL.log SCHEMAS=RM,SYSDBA,RM_INTEG,MANAGER,MANAGER_WEBAPP,INBI,INBI2,CONFLUENCE,CONFLUENCE_GTW,TRACKPLUS,FIRESCRUM,PYTHON_APP,SURVEY_APP,PRJBUILDER,PROJECTBUILDER,PBOWNER REUSE_DUMPFILES=Y DATA_OPTIONS=XML_CLOBS
Export: Release 11.2.0.3.0 - Production on Thu Sep 18 14:14:58 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" DIRECTORY=DPUMP_BACKUP DUMPFILE=expdp_full.dmp LOGFILE=expdp_full.log SCHEMAS=RM,SYSDBA,RM_INTEG,MANAGER,MANAGER_WEBAPP,INBI,INBI2,CONFLUENCE,CONFLUENCE_GTW,TRACKPLUS,ALEXJUNQ,FIRESCRUM,PYTHON_APP,SURVEY_APP,PRJBUILDER,PROJECTBUILDER,PBOWNER REUSE_DUMPFILES=Y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12.51 GB

Processing object type SCHEMA_EXPORT/USER
      :
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/backup/dpump/expdp_full.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:32:40


3. References




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