Install Oracle 18c(18.3) on CentOS 7.5 Silent Mode

Linux Version : CentOS 7.5
Oracle Version : 18c (18.3.0.0)

1. Download the Zip file from Oracle Website and create groups and oracle user(https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle18c-linux-180000-5022980.html ) ##

groupadd oinstall
groupadd dba

useradd oracle -g oinstall -G dba
passwd oracle

Create .bash_profile , in my test server i use 3 different Oracle homes and versions, so the bash profile looks a bit extra funky 😉

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs
export CATALINA_HOME=/home/oracle/apache-tomcat
PATH=/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:/bin/bash:$CATALINA_HOME/bin.
export PATH
EDITOR=vi
export EDITOR=vi

if [ -s “$MAIL” ] # This is at Shell startup. In normal
then echo “$MAILMSG” # operation, the Shell checks
fi # periodically.

echo ” ”
echo ” ”
echo ” ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~”
echo ” Database options”
echo ” ”
echo ” ”
echo ” 1) DATABASE 12cR2″
echo ” ”
echo ” 2) DATABASE 12cR1″
echo ” ”
echo ” 3) DATABASE 18.3″
echo ” ”
echo ” ”
echo ” ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~”
echo ” ”
echo ” ”
echo ” Enter Choice:\c”
read env
if [ $env -eq 1 ] ; then
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=ora12c
PS1=’$PWD:12cR2–>’

else
if [ $env -eq 2 ] ; then
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=moovorix
PS1=’$PWD:12cR1–>’
fi

if [ $env -eq 3 ] ; then
export ORACLE_HOME=/u01/app/oracle/product/18.3.0/dbhome_1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=ora18c
PS1=’$PWD:18c–>’
fi
fi
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/lib
export LIBPATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:/usr/lib:/lib
export PATH=$PATH:$ORACLE_HOME/bin:/u01/app/12.1.0.2/grid/bin:$ORACLE_HOME/OPatch
stty erase ^?
TMOUT=0;TIMEOUT=600;export readonly TMOUT TIMEOUT
set -o vi

echo ”
echo ”
clear
echo
echo “ORACLE_SID=$ORACLE_SID”
echo

alias tsmd=’cd /opt/tivoli/tsm/client/oracle/bin64/’
alias ob=’cd $ORACLE_BASE’
alias oh=’cd $ORACLE_HOME’
alias tns=’cd $ORACLE_HOME/network/admin’
alias ch=’cd $CATALINA_HOME’
alias envo=’env | grep ORACLE’
alias sqld=’rlwrap sqlplus “/as sysdba”‘
alias cpu=”cat /proc/cpuinfo | awk ‘/^processor/{print $3}’ | wc -l”
alias py=’/usr/bin/python3.6′
alias py2=’/usr/bin/python’
export PATH
umask 022

envo

2. Install all the pre-requisites, if your on Oracle Linux there is an RPM you can install : oracle-database-preinstall-18c

Else if you prefer do the pre-requisites manually (Which I do)

Check for missing packages

rpm -q –qf ‘%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n’ binutils \
compat-libcap1 \
compat-libstdc++-33 \
elfutils-libelf \
elfutils-libelf-devel \
gcc \
gcc-c++ \
glibc \
glibc-common \
glibc-devel \
glibc-headers \
ksh \
libaio \
libaio-devel \
libgcc \
libstdc++ \
libstdc++-devel \
make \
libXext \
libXtst \
libX11 \
libXau \
libxcb \
libXi \
sysstat \
unixODBC \
unixODBC-devel

Install missing packages with Yum
eg:
rpm -Uvh compat-libcap1

Update Kernel Parameters

Open sysctl.conf file and add the kernel parameters

vi /etc/sysctl.conf

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

Add Limits for Oracle user

Open file limits.conf and add below parameters

vi /etc/security/limits.conf

–shell limits for users oracle 18c

oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768

3. Silent Installation of 18c Software, the new installer is a zipped Oracle Home which you need to unzip to your Oracle Home directory and installation command just relinks all libraries. The installation is super quick due to this, afer this installation you are never going back to the normal runInstaller again

mkdir -p /u01/app/oracle/product/18.3.0/dbhome_1/

unzip -q LINUX.X64_180000_db_home.zip -d /u01/app/oracle/product/18.3.0/dbhome_1/

cd /u01/app/oracle/product/18.3.0/dbhome_1/

sed -e ‘/\s*#.*$/d’ -e ‘/^\s*$/d’ install/response/db_install.rsp > install/response/db_18c.rsp

cat install/response/db_18c.rsp
## Remove all other parameters and just leave the below ##

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v18.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/18.3.0/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=dba
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba

## If an error “[WARNING] [INS-13001] Oracle Database is not supported on this operating system 18c” pops up; the use parameter ignoreInternalDriverError in the runinstaller ##

./runInstaller -silent -responseFile install/response/db_18c.rsp -ignoreInternalDriverError

## As Root User ##
sh /u01/app/oracle/product/18.3.0/dbhome_1/root.sh

4. Silent Creation of Database using dbca silent mode

dbca -createDatabase -silent -gdbName ora18c -templateName General_Purpose.dbc -sysPassword sys123 -systemPassword sys123 -dbsnmpPassword sys123 -datafileDestination /u01/oradata -storageType FS -memoryPercentage 20 -emConfiguration NONE -sampleSchema false

[FATAL] [DBT-50000] Unable to check for available memory.
[FATAL] [DBT-50001] Unable to check the value of kernel parameter {0}

While creating a on-premise 18c (18.3) database with DBCA in the silent mode If you get above error. Then use the parameter -J-Doracle.assistants.dbca.validate.ConfigurationParams=false ; it can be used both in cli and gui ##

dbca -createDatabase -silent -gdbName ora18c -templateName General_Purpose.dbc -sysPassword sys123 -systemPassword sys123 -dbsnmpPassword sys123 -datafileDestination /u01/oradata -storageType FS -memoryPercentage 20 -emConfiguration NONE -sampleSchema false -J-Doracle.assistants.dbca.validate.ConfigurationParams=false

5. Create Listener for 18c

cd /u01/app/oracle/product/18.3.0/dbhome_1/admin/network

vi listener.ora

SID_LIST_LISTENER_18C =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora18c)
(ORACLE_HOME = /u01/app/oracle/product/18.3.0/dbhome_1)
(SID_NAME = ora18c)
)
)

LISTENER_18C =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 103.42.111.196)(PORT = 1523))
)
)

lsnrctl start LISTENER_18C

Checking from SQL*PLUS

SQL*Plus: Release 18.0.0.0.0 – Production on Mon Aug 13 04:03:10 2018
Version 18.3.0.0.0

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

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.3.0.0.0

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ——————–
ORA18C READ WRITE

Dkam7GRUwAA3XXJ.jpg large

Install Oracle Database 12cR2 Silent Mode on CENT OS 7 & Oracle Linux 7

If you are going to work on Oracle Database in cloud or in environments where X11 client or server is not available. It is always good to learn how to do a silent installation of Oracle. Specially in todays CI/CD devops model where dba’s have to provision DB servers using Ansible or other such tools. The base of doing such automation will require you to install Oracle from cli. In the below article we will do below 3 steps without any graphical user interface like X11, gnome, kde,vnc etc

1. Install Oracle 12.2.0.1 Database Software using a Response File

2. Create a Database with Response File

3. Create a Listener and Register the Database

I did the below setup on a cloud hosted server, running CENT OS 7, which came with bare minimum packages. It is always good to add different repositories which host all the important softwares for Linux

Now lets get started….

1. Add Groups

# groupadd oinstall
# groupadd dba
# groupadd oper
# groupadd backupdba
# groupadd dgdba
# groupadd kmdba
# groupadd asmdba
# groupadd asmoper
# groupadd asmadmin
# groupadd racdba

2. Add Oracle User

# useradd oracle -g oinstall -G dba,oper,backupdba,dgdba,kmdba

# passwd oracle

3. Check for missing packages

rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n' binutils \
compat-libcap1 \
compat-libstdc++-33 \
elfutils-libelf \
elfutils-libelf-devel \
gcc \
gcc-c++ \
glibc \
glibc-common \
glibc-devel \
glibc-headers \
ksh \
libaio \
libaio-devel \
libgcc \
libstdc++ \
libstdc++-devel \
make \
libXext \
libXtst \
libX11 \
libXau \
libxcb \
libXi \
sysstat \
unixODBC \
unixODBC-devel

 

4. Install missing packages with Yum
eg:
# rpm -Uvh compat-libcap1

 

5. Update Kernel Parameters

Open sysctl.conf file and add the kernel parameters

# vi /etc/sysctl.conf

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

 

6. Add Limits for Oracle user

Open file limits.conf and add below parameters

# vi /etc/security/limits.conf

 

–shell limits for users oracle 12gR1

oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768

 

7. Create Bash Profile for Oracle user

 

# mkdir -p /u01/app/oracle/product/12.2.0/dbhome_1

# mkdir -p /u01/app/oracle/product/12.2.0/dbhome_1/network/admin

# mkdir -p /u01/app/oraInventory

# chown oracle:oinstall -R /u01

# vi .bash_profile

 

export TMP=/tmp

export ORACLE_HOSTNAME=easyoradba.com
export ORACLE_UNQNAME=ORA12C
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/dbhome_1
export ORACLE_SID=ORA12C

PATH=/usr/sbin:$PATH:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;

alias ob=’cd $ORACLE_BASE’
alias oh=’cd $ORACLE_HOME’
alias tns=’cd $ORACLE_HOME/network/admin’
alias envo=’env | grep ORACLE’
umask 022
envo

# envo

8. Disable SELINUX and IPTABLES (Not a good thing to do on a production server, only for test !!!)

# vi /etc/selinux/config

set SELINUX=disabled

# service iptables stop
# chkconfig iptables off

 

9. Download Oracle Database binary to home directory and prepare the staging area

# su – oracle

# cd /home/oracle

# unzip Oracle_Database_12CR2_linux_x64_12201.zip

This creates a directory ‘database’ with the installation files in it

 

10. Create Response File and Install Database Software in Silent Mode

Create a copy of the response file and add parameters for installation

# cd /home/oracle/database/response

# ls -ltrh

-rwxrwxr-x 1 oracle oinstall 6.1K Jun 20 2016 netca.rsp
-rwxrwxr-x 1 oracle oinstall 25K Jan 5 2017 dbca.rsp
-rw-rw-r– 1 oracle oinstall 23K Jan 26 2017 db_install.rsp

# cp db_install.rsp ../

# vi db_install.rsp

Now add below values for the parameters as per the directory structure you created

 

oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=dba
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true

Make sure the above parameters are not duplicated if you will copy and paste the above values 🙂

 

# cd /home/oracle/database

# ./runInstaller -silent -responseFile /home/oracle/database/db_install.rsp

if you get error Oracle runInstaller – CreateOUIProcess(): 13 : Permission denied

Then change temporary directory to home directory and run installer again

# mkdir -p $HOME/tmp
# export TMP=$HOME/tmp

Run the installer again

# ./runInstaller -silent -responseFile /home/oracle/database/db_install.rsp

 

11. Run root scripts and complete install

If all goes well you will get below message

Starting Oracle Universal Installer…

Checking Temp space: must be greater than 500 MB. Actual 68933 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3958 MB Passed
Preparing to launch Oracle Universal Installer from /home/oracle/tmp/OraInstall2018-07-05_04-21-40AM. Please wait …[oracle@vps database]$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2018-07-05_04-21-40AM.log
The installation of Oracle Database 12c was successful.
Please check ‘/u01/app/oraInventory/logs/silentInstall2018-07-05_04-21-40AM.log’ for more details.

As a root user, execute the following script(s):
1. /u01/app/oracle/product/12.2.0/dbhome_1/root.sh

 

Run the script by opening another session with root user and setup will complete successfully

Successfully Setup Software.

 

12. Create Database in Silent Mode

find the file dbca.rsp and make a copy of it and update below parameters

# cd $ORACLE_HOME/assistants/dbca/

# cp dbca.rsp $ORACLE_HOME

# vi dbca.rsp

gdbName=ora12c
sid=ora12c
databaseConfigType=SI
createAsContainerDatabase=false
templateName=General_Purpose.dbc
sysPassword=sys123
systemPassword=sys123
emConfiguration=NONE
datafileDestination=/u01/oradata
recoveryAreaDestination=/u01/fra
storageType=FS
characterSet=AL32UTF8
sampleSchema=TRUE
memoryPercentage=40

ora12c is global database name

# dbca -silent -createDatabase -responseFile dbca.rsp

53% complete
54% complete
55% complete
58% complete
Completing Database Creation
59% complete
60% complete
61% complete
64% complete
68% complete
69% complete
Executing Post Configuration Actions
100% complete

 

13. Create Listener and Register Database

# cd $ORACLE_HOME/network/admin

# vi listener.ora

LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

# lsnrctl start

# sqlplus “/as sysdba”

SQL> alter database register;

# lsnrctl status

Services Summary…
Service “ora12c” has 1 instance(s).
Instance “ora12c”, status READY, has 1 handler(s) for this service…
Service “ora12cXDB” has 1 instance(s).
Instance “ora12c”, status READY, has 1 handler(s) for this service…
The command completed successfully

 

 

Documentation#1193 – Unknown system variable ‘lc_messages’

This error you would usually see when you login to phpMyAdmin after an upgrade. This is related to your MySQL version, in my case I had upgraded my phpMyAdmin but the MySQL version was still on 5.1, after upgrading to MySQL 5.6 it solved the issue

You can upgrade MySQL to the latest version by downloading the latest rpm’s or use repository for the upgrade. I prefer to use the mysql repo’s as it is the best source of the latest release.

https://dev.mysql.com/doc/mysql-repo-excerpt/5.6/en/linux-installation-yum-repo.html

 

Generate REST API from mysql database in less than 5 minutes

Representation State Transfer is the new industry standard of building technology neutral API’s. REST API are essentially web services to read or transfer data. REST API’s can be build on top of most relational databases. Mysql being the most popular database around. If you have nodejs and mysql already installed, you can build a REST API for your mysql db in less than 2 minutes 🙂

Before we begin we must have nodejs and mysql running on the server. If you don’t have nodejs, you can check my other article on how to install node.js on Cent OS Linux : https://easyoradba.com/2018/05/08/install-nodejs-on-centos-6-7/

And to quickly install mysql refer to my another of my blog posts 🙂 https://easyoradba.com/2018/05/18/install-mysql-database-along-with-phpadmin-on-centos-6-7/

Once you have mysql and nodejs setup. You can install and spin up  REST API’s doing the below steps

1.Install xmysql using node package manager

$ npm install -g xmysql

2. Create a new user, a new test database and a new table with few records

mysql -u root -p

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘test’@’localhost’ IDENTIFIED BY ‘test123’;

mysql> CREATE DATABASE easyoradba;

mysql> USE easyoradba;

mysql> CREATE TABLE emp ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );

mysql> INSERT INTO emp ( id, name ) VALUES ( null, ‘SHADAB’ );

mysql> select * from emp;
+—-+——–+
| id | name |
+—-+——–+
| 1 | SHADAB |
+—-+——–+
1 row in set (0.00 sec)

mysql> exit;

3. Generate the REST API for db ‘easyoradba’ using xmysql

$ xmysql -h localhost -u test -p test123 -d easyoradba -n 8899 -r 192.168.1.46

Generating REST APIs at the speed of your thought..

– – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

Database : easyoradba
Number of Tables : 1

REST APIs Generated : 19

Xmysql took : 0.3 seconds

– – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

-h hostname of your mysql DB server

-u username of mysql

-p password for mysql user

-d DB name for which API needs to be generated

-n port number to create the REST service

-r your server IP, if left blank it will create with default value localhost

4. Check the REST API service and Call it from Postman

Go to browser and check the ip with defined port number : http://192.168.1.46:8899/

[{“resource”:”emp”,”routes”:[{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/describe”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/count”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/groupby”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/distinct”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/ugroupby”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/chart”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/aggregate”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/findOne”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/autoChart”},{“httpType”:”post”,”routeUrl”:”http://192.168.1.46:8899/api/emp”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp”},{“httpType”:”post”,”routeUrl”:”http://192.168.1.46:8899/api/emp/bulk”},{“httpType”:”delete”,”routeUrl”:”http://192.168.1.46:8899/api/emp/bulk”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/bulk”},{“httpType”:”put”,”routeUrl”:”http://192.168.1.46:8899/api/emp”},{“httpType”:”patch”,”routeUrl”:”http://192.168.1.46:8899/api/emp/:id”},{“httpType”:”delete”,”routeUrl”:”http://192.168.1.46:8899/api/emp/:id”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/:id”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/:id/exists”}]},”http://192.168.1.46:8899/api/tables”,”http://192.168.1.46:8899/api/xjoin”,”http://192.168.1.46:8899/api/dynamic”,”/upload”,”/uploads”,”/download”]

Open Postman and call the URL with GET method to fetch the data

Screen Shot 2018-05-18 at 2.17.30 PM

 

And voila ! There you have it an REST API running on top of a mysql database in less than 5 minutes :)))

 

 

 

Install mysql database along with phpadmin on CENTOS 6 7

Mysql is the most popular open database and is the M in the LAMP stack (others being Linux-Apache-PHP)

If your on CentOS and your linux server is connected to the internet installing mysql is just a few terminal commands

1. Install the LAMP stack via yum

$ yum install httpd mysql-server php php-mysql

2. Configure the LAMP Stack

$ service httpd start

Now you should have a webserver up and running on http://localhost/

3. Start and secure your mysql database by executing below script

$ service mysqld start

$ mysql_secure_installation

Securing your database is very important, specially if your deploying a productions database

4. Install phpmyadmin

Install the EPEL Repo, if your on CentOS 6

$ wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
$ rpm -ivh epel-release-6-8.noarch.rpm

if your on CentOS 7

$ wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
$ rpm -ivh epel-release-latest-7.noarch.rpm

Now install phpmyadmin

$ yum install phpmyadmin

If you want access to phpmyadmin from other computers edit below file and add the host ip’s

$ vi /etc/httpd/conf.d/phpMyAdmin.conf

Require ip <other workstation ip>

Allow from <other workstation ip>

Restart httpd services

$ service httpd restart

You can now access using below URL

http://localhost/phpmyadmin

Enter your root username and password and you now have a nice GUI to manage your mysql instance

Install Oracle Apex 5.1.x, 18.1 on ORDS 3,17.4, 18.1

Oracle Apex is a modern framework to quickly develop and deploy web apps sitting on top of Oracle Database. Apex can be run directly from the database engine itself using a component called Mod PL/SQL. It is one of the easiest way to run Apex, but it is not a production ready setup. In a production scenario your database server should not be internet facing. The ideal design is a reverse proxy server facing the internet and a webserver sitting behind it and the DB server behind a firewall isolated from any public network.

Oracle has a modern gateway to do this, it is called ORDS( previously called Apex Listener). It stands for Oracle Rest Data Services. Not only it can help you host your apex instance in a Java web container like Tomcat, Glassfish, Weblogic but also it gives your developer access to build enterprise scale data access API’s. It can help you build your front-end in the modern JavaScript instead of the traditional programming languages. You can read about it more here : https://blogs.oracle.com/newgendbaccess/why-use-rest-and-ords-to-transform-your-oracle-database-into-a-restful-api-service

architecture big

Now to setup ORDS to host your Apex instance first we need to make sure you have 3 components

1. Oracle APEX itself, though from ORDS 17.1 it is not compulsory to have Apex installed, but i always prefer having it. Download the latest version from here : http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

2. A Servlet web container like Tomcat, Glassfish or Weblogic. I prefer to use Tomcat since it is the easiest to setup, is open source and is quite lightweight. Apache tomcat can be downloaded from here  : https://tomcat.apache.org/download-90.cgi

3. ORDS, this is the most important component in this setup. Oracle has recently changed it’s numbering scheme. So it jumped from ORDS 3 to ORDS 17.4 and ORDS 18.1. It is now done using YY.MM naming convention. It can be downloaded from here : http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html

Let’s  Start with Installing Apache tomcat first on you server

                             1.  INSTALL APACHE TOMCAT

1. Create a user tomcat on the linux server which will be your webserver

useradd tomcat

2. Untar the file in the home directory or create a separate directory. To have a well defined structure i always prefer to have a directory structure like /u01/tomcat

tar xvf apache-tomcat-9.0.8.tar.gz

This will create the directory ‘apache-tomcat-9.0.8’ inside

go to the .bash_profile of tomcat user and add below for the home directory

PATH=$PATH:$HOME/bin:$CATALINA_HOME/bin
CATALINA_HOME=/u01/tomcat/apache-tomcat-9.0.8
export CATALINA_HOME
export PATH

. .bash_profile

3. Start Stop Apache Tomcat 9 snd enable Gui Access

sh $CATALINA_HOME/bin/shutdown.sh

sh $CATALINA_HOME/bin/startup.sh

You can now access the console of tomcat using http://localhost:8080/

To enable access to manager app and manager app gui. Add below to lines to file tomcat-users.xml

vi $CATALINA_HOME/conf/tomcat-users.xml

<role rolename=”manager-gui”/>
<user username=”tomcat” password=”Abc1234$#” roles=”manager-gui”/>
<role rolename=”admin-gui”/>
<user username=”tomcat” password=”Abc1234$#” roles=”admin-gui”/>

Save and restart Tomcat

                        2.   INSTALL ORACLE APEX 5.1.x, 18.1

This installation has to be done on the DB Server. Copy your Apex zip file to Oracle Home Directory. Remove the existing Apex directory and unzip the fil

1.Install Apex Binary and Create Tablespace and Schema

cd $ORACLE_HOME

rm -rf apex/

unzip apex_5_0_1_en.zip

cd $ORACLE_HOME/apex

sqlplus “/as sysdba”

SQL> CREATE TABLESPACE APEX  DATAFILE ‘/u01/oradata/orcl/datafiles/apex01.dbf’ SIZE 1G AUTOEXTEND ON NEXT 10M;

SQL> @apexins.sql APEX APEX TEMP /i/

APEX – Tablespace to hold Apex Schema and its associated files

TEMP –  Temporary Tablespace

/i/ – It is  your image directory

Change your Admin password

SQL> @apxchpwd.sql

Create APEX_LISTENER and  APEX_REST_PUBLIC_USER

SQL> @apex_rest_config.sql

If you want to run the PL/SQL gateway continue to the next step else you can skip this and go to Step 3. directly

SQL> @apex_epg_config.sql /u01/app/oracle/product/12.2.0/dbhome_1

‘/u01/app/oracle/product/12.2.0/dbhome_1’ is the home directory of Oracle Database

SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

Check the HTTP Port,

SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

If this is 0 and you need to to install Apex with ORDS then goto Step 3. else continue and get the port

SQL> EXEC DBMS_XDB.sethttpport(8080);

SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

8080

The URL for Apex application now should be accessible from Web Browser http://localhost:8080/apex/apex_admin

              3. ORACLE REST DATA SERVICES (ORDS) CONFIGURATION & INSTALLATION

After downloading the ORDS to the server, go to the server where you installed Tomcat earlier

1.Unzip the ords zip file

cd /u01

mkdir -p /u01/ords

mkdir -p /u01/ords/conf

unzip ords.18.1.1.95.1251.zip

2. Login to DB Server SQL*PLUS and unlock the Apex  listener usernames and password to ensure ORDS installation goes smoothly

SQL> ALTER USER SYS IDENTIFIED BY abc123 ACCOUNT UNLOCK;

SQL> ALTER USER APEX_LISTENER IDENTIFIED BY abc123 ACCOUNT UNLOCK;
SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY abc123 ACCOUNT UNLOCK;
SQL> ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY abc123 ACCOUNT UNLOCK;

SQL>ALTER USER ORDS_PUBLIC_USER IDENTIFIED BY abc123 ACCOUNT UNLOCK;

If last SQL command fails, it is because there is no ORDS schema yet.

3. Configure ORDS for Database connectivity. Go back to WebServer and there will be a ords.war file in /u01/ords

Edit the /u01/ords/params/ords_params.properties and set the parameters of your DB server and instance. Make sure there is network connectivity between DB Server and Web Server

cd /u01/ords

java -jar ords.war configdir /u01/ords/conf

java -jar ords.war

Don’t select Standalone option in the last, just press 2 and exit. Standlone server is a quick Jetty webserver build in ORDS. But its not the best production setup, hosting on Apache Tomcat is better.

mkdir $CATALINA_HOME/webapps/i/

Copy the image directory from the Apex installation in DB Server to Tomcat image directory.

cp -R $ORACLE_HOME/apex/images/* $CATALINA_HOME/webapps/i/

Copy the ords.war file from /u01/ords to Webapps directory of tomcat

cd /u01/ords
cp ords.war $CATALINA_HOME/webapps/

ORDS should now be accessible on URL http://localhost:8080/ords

Remember to remove the port 8080 configuration from Embedded PL/SQL gateway, if you did Step 2. and are moving to ORDS from PL/SQL Gateway

SQL> EXEC DBMS_XDB.sethttpport(0);

SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

0

And try URL again : http://localhost:8080/ords

Error 1 :

Oracle Rest Data Services 404 Not Found When Running APEX – PL/SQL Gateway Configured

Check APEX_PUBLIC_USER account and unlock it, stop and start Apache tomcat and try again. Or maybe an incorrect APEX_PUBLIC_USER password was entered during ORDS configuration. reconfigure the ords.war using below command:

java -jar ords.war setup

And copy new war file to webapps directory again and access URL again

http://localhost:8080/ords

Reference : Oracle Rest Data Services 404 Not Found When Running APEX – PL/SQL Gateway Configured (Doc ID 2048493.1)

Error 2:

Server isn’t redirecting oracle apex ords on Firefox

This page isn’t working
<hostname> redirected you too many times.

Try clearing the cookies and try again. Most likely close the browser windows and open and again and the error will go away

http://localhost:8080/ords

Reference: Problem Accessing APEX Application Using ORDS 3.0.9.348.07.16 (Doc ID 2280694.1)

Summary :

1.Steps to do when installing only PL/SQL gateway

1 > 2

2. Steps when upgrading from PL/SQL Gateway to ORDS

1 > 3

3. Steps when doing fresh new install of ORDS

1 > 2 > 3

Install NodeJS on Mac OS

To install NodeJS on a Mac the first thing you have to do is download Brew package manager for mac. Though you can install NodeJS via other methods but doing it with Brew is the easiest and least error prone method

1. Install Brew on your mac if you dont have it already. Open the terminal app and paste the below command (make sure you have the password for the user)

ruby -e “$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)”

2. Now the Brew package manager is installed. Open terminal and type below command

brew install node

3. Check Versions of Node and NPM

node -v

npm -v

 

4. Create a Demo Web server and Check. Open terminal and type below

vim demo_server.js

— Paste below Code in the file —

var http = require(‘http’);
http.createServer(function (req, res) {
res.writeHead(200, {‘Content-Type’: ‘text/plain’});
res.end(‘Welcome To EasyOraDBA’);
}).listen(3005, “127.0.0.1”);
console.log(‘Server is running at http://127.0.0.1:3005/’);

 

node demo_server.js

— Now open a Web browser and paste the URL http://127.0.0.1:3005/ And voila you have a NodeJS Web Server running —

 

Install NodeJS on CentOS 6 – 7

Install Latest NodeJS on CentOS 6 or 7
————————————–

1. Add Repo and Install NodeJS

For Stable Release:-

$ yum install -y gcc-c++ make
$ curl -sL https://rpm.nodesource.com/setup_8.x | sudo -E bash –

$ yum install nodejs

 

2. Check the version for Node and NPM

$ node -v

v8.11.1

$ npm -v

5.6.0

 

3. Create a Demo Server

$ vim demo_server.js

add below lines

var http = require(‘http’);
http.createServer(function (req, res) {
res.writeHead(200, {‘Content-Type’: ‘text/plain’});
res.end(‘Welcome To EasyOraDBA’);
}).listen(3005, “127.0.0.1”);
console.log(‘Server is running at http://127.0.0.1:3005/’);

4. Start the Node Server

[root@easyoradba CordovaApps]# node demo_server.js
Server is running at http://127.0.0.1:3005/

Go to your Browser and Paste the URL, your WebServer has been started on port 3005

Compare 2 Servers RPM and Install RPM from Text File List

1. Generate RPM list on Old Server

rpm -qa –queryformat=’%{NAME}\n’ | sort > serverold.txt

2. Generate RPM list on New Server

rpm -qa –queryformat=’%{NAME}\n’ | sort > servernew.txt

3. Copy File to New Server and use diff comand to get differences

diff serverold.txt servernew.txt > diff.txt

4. Get list of packages missing in new Server

cat diff.txt | grep “<” > difference.txt

vi difference.txt

%s,< ,

## save and exit

5. Install missing packages on new server

yum -y install $(cat difference.txt)

Oracle Dataguard Broker 12c with password file in ASM

Configure 12c dataguard broker using the below command onPrimary database server

primeprd – > tnsnames for primary database

primedr-> tnsnames for standby database

/home/oracle::11g–>dgmgrl

DGMGRL> connect sys/********

DGMGRL> create configuration dg_primeprd as primary database is primeprd connect identifier is primeprd;

DGMGRL> add database primedr as connect identifier is primedr maintained as physical;

Configuration “dg_primeprd” created with primary database “primeprd”

DGMGRL> add database primedr as connect identifier is primedr maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.
DGMGRL> remove configuration;
Removed configuration
Set the log_archive_dest_2 settings from both the Primary and Standby databases to be nothing.

alter system set log_archive_dest_2=” scope=both sid=’*’;
Disable then Enable the broker parameter on both the Primary and Standby databases.

— Primary
alter system set dg_broker_start=false scope=both sid=’*’;
alter system set dg_broker_start=true scope=both sid=’*’;

— Standby
alter system set dg_broker_start=false scope=both sid=’*’;
alter system set dg_broker_start=true scope=both sid=’*’;

On the Primary database create the broker configuration for the Primary and Standby database and this time it should work fine with no issues since the log archive destination 2 setting is not set, this is the workaround/solution.

/home/oracle::11g–>dgmgrl

DGMGRL> connect sys/**********

DGMGRL> create configuration dg_primeprd as primary database is primeprd connect identifier is primeprd;

DGMGRL> add database primedr as connect identifier is primedr maintained as physical;

DGMGRL> enable configuration;
If in Broker you get below error Error: ORA-16664: unable to receive the result from a database

DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primeprd – Primary database
primedr – Physical standby database
Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR (status updated 40 seconds ago)

Check in Broker Log File.

Mon Jul 04 14:41:46 2016
Error 1017 received logging on to the standby
————————————————————
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191

Failed to send message to site . Error code is ORA-01017.

Re-create password files in ASM in 12c like below, the correct format is pwd<dbuniquename>

Primary

srvctl config database -d primeprd

orapwd file=’+DATAC1/PRIMEPRD/pwdprimeprd’ entries=10 dbuniquename=primeprd password=*** force=y ignorecase=y
Standby

srvctl config database -d primedr
orapwd file=’+DATAC1/PRIMEDR/pwdprimedr’ entries=10 dbuniquename=primedr password=*** force=y ignorecase=y

DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primeprd – Primary database
primedr – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 18 seconds ago)

— Try Switchover Now —
DGMGRL> switchover to primedr
Performing switchover NOW, please wait…
Error: ORA-16644: apply instance not available

Failed.
Unable to switchover, primary database is still “primeprd”

DGMGRL> edit database primedr set state=’APPLY-ON’ with apply instance=’primedr1′;

DGMGRL> show database verbose primedr

Database – primedr

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 6.00 KByte/s
Active Apply Rate: 755.00 KByte/s
Maximum Apply Rate: 827.00 KByte/s
Real Time Query: OFF
Instance(s):
primedr1 (apply instance)
primedr2
DGMGRL> switchover to primedr
Performing switchover NOW, please wait…
New primary database “primedr” is opening…
Oracle Clusterware is restarting database “primeprd” …
Switchover succeeded, new primary is “primedr”
DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primedr – Primary database
primeprd – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 40 seconds ago)
DGMGRL> edit database primeprd set state=’APPLY-ON’ with apply instance=’primeprd1′;

DGMGRL> show database verbose primeprd

Database – primeprd

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 307.00 KByte/s
Active Apply Rate: 0 Byte/s
Maximum Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
primeprd1 (apply instance)
primeprd2

DGMGRL>
DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primedr – Primary database
primeprd – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 55 seconds ago)

Connect to DR site broker to intiate switchover

DGMGRL> switchover to primeprd ;
Performing switchover NOW, please wait…
Operation requires a connection to instance “primeprd1” on database “primeprd”
Connecting to instance “primeprd1″…
Connected as SYSDBA.
New primary database “primeprd” is opening…
Oracle Clusterware is restarting database “primedr” …
Switchover succeeded, new primary is “primeprd

DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primeprd – Primary database
primedr – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 11 seconds ago