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 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

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 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

Execute Windows Commands from Linux using winexe. Connect from Linux to Windows without SSH

Winexe ‚Äď is a software on Linux which uses RPC to run remote commands and might even come with your distribution’s packages. It depends on Samba for running rpc commands

Edit: even if your distro does not have it precompiled and you do not want to compile yourself, you should check out the Build Service – it is likely to have the binary version you would need for your system.

First Install samba-common on your Linux machine before beginning winexe

Use below article to check WinEXE installation

Using winexe on CentOS 7 to execute commands on remote Windows 7 machines

Winexe is a GNU/Linux based application that allows users to execute commands remotely on WindowsNT/2000/XP/2003/Vista/7/8 systems. It installs a service on the remote system, executes the command and uninstalls the service. Winexe allows execution of most of the windows shell commands.

How to install:
You can download the source package from here [Current version is winexe-1.00.tar.gz]

  1. tar -xvf winexe-1.00.tar.gz
  2. cd winexe-1.00/source4/
  3. ./autogen.sh
  4. ./configure
  5. make basics bin/winexe
  6. make ‚ÄúCPP=gcc -E -ffreestanding‚ÄĚ basics bin/winexe (For X64 bit)

this will create a [ winexe ] binary file in the bin folder. You can use that binary to execute the windows commands from Linux.

or else there are some compiled version of binary itself available for download. You can download and use it from here.

The above version of winexe works only till version Windows Server 2012. For Windows Server 2012R2 requires below method to make winexe work with latest version of windows. Below method will get the code for samba and winexe from the github repository and we will have to build it for it to work.

Alternate Method (Best Method to Work with Latest Version of Windows 10 and Windows Server 2012R2)

http://rand0m.org/2015/08/05/winexe-1-1-centos-6-and-windows-2012-r2/

  1. Install Samba on Linux

yum install samba-common

2.      fixup a bunch of samba dependencies

yum install gcc perl mingw-binutils-generic mingw-filesystem-base mingw32-binutils mingw32-cpp mingw32-crt mingw32-filesystem mingw32-gcc mingw32-headers mingw64-binutils mingw64-cpp mingw64-crt mingw64-filesystem mingw64-gcc mingw64-headers libcom_err-devel popt-devel zlib-devel zlib-static glibc-devel glibc-static python-devel

yum install git gnutls-devel libacl1-dev libacl-devel libldap2-dev openldap-devel

  1. As per the docs, remove libbsd-devel if installed

yum remove libbsd-devel

  1. Clone the git repos. samba is huge, like 280MB

cd /usr/src

git clone git://git.code.sf.net/p/winexe/winexe-waf winexe-winexe-wafgit clone git://git.samba.org/samba.git samba

  1. per winexe bug 64, samba needs to be reverted to a6bda1f2bc85779feb9680bc74821da5ccd401c5

cd /usr/src/samba

git reset –hard a6bda1f2bc85779feb9680bc74821da5ccd401c5

  1. Fixup the build deps

cd /usr/src/winexe-winexe-waf/source

vi wscript_build

# modify ‘wscript_build’, and at the very end …stlib=’smb_static bsd z resolv rt’lib=’dl gnutls’

7.     Build it! his does a huge configure, then also compiles samba, which takes a while. 

./waf –samba-dir=../../samba configure build

8.     Executable should be  /usr/src/winexe-winexe-waf/source/build/winexe-static

cd /usr/src/winexe-winexe-waf/source/build/

cat </dev/null | ./winexe-static -U ‘domain\admin%$PWD’ //iihoserver01 “ipconfig -all”

cat </dev/null | ./winexe-static -U ‘domain\admin%$PWD’ //iihoserver01 “ipconfig -all” -d99

Sample on How to use it to Check Service on Linux :

export CHECK_COMMAND=’sc query “Notification Service”‘

export STOP_COMMAND=’sc stop “Notification Service”‘

export START_COMMAND=’sc start “Notification Service”‘

## Check Service Status ##

cat </dev/null | ./winexe-static -U ‘domain\admin%$PWD’ //iihoserver01 –interactive=0 “$CHECK_COMMAND”

## Check with Debug ##

cat </dev/null | ./winexe-static -U ‘domain\admin%$PWD’ //iihoserver01 –interactive=0 “$CHECK_COMMAND” -d99

## Run powershell script or command on windows then like below ##

winexe -U administrator%$PWD //$IP “powershell -Command & {(c:\User\administrator\powershell.ps1)}”

 

## Stop and Start Service ##

export CHECK_COMMAND=’sc query “Browser”‘

export STOP_COMMAND=’sc stop “Browser “‘

export START_COMMAND=’sc start “Browser “‘

cat </dev/null | ./winexe-static -U ‘domain\admin%$PWD’ //iihoserver01 –interactive=0 “$CHECK_COMMAND”

cat </dev/null | ./winexe-static –U ‘domain\admin%$PWD’ //iihoserver01 –interactive=0 “$STOP_COMMAND”

cat </dev/null | ./winexe-static -U ‘domain\admin%$PWD’ //iihoserver01 –interactive=0 “$START_COMMAND”

 

 

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)

ORA-29548 release of java system classes in the database (12.2.0.2.160419.1.6 ) does not match that of oracle executable (12.1.0.2.170418 1.6)

This error is mostly due to a mismatch in the Java system classes. It happened to us when we cloned a 12c Database which was on a lower home version to a 12c home on a higher version. It requires data patch to be run and rollback the old patch and apply the new JVM patch.

 

If it is a RAC database, put it in single instance mode by disabling cluster_database parameter and mount the database in upgrade exclusive mode.

 

SQL> show parameter cluster

 

NAME     TYPE VALUE
———————————— ———– ——————————
cluster_database     boolean TRUE
cluster_database_instances     integer 2
cluster_interconnects     string
SQL>

SQL>

SQL> alter system set cluster_database=false scope=spfile sid=’*’;

 

 

System altered.

 

SQL>

SQL>

SQL> shutdown immediate;

 

Database closed.

 

Database dismounted.

 

ORACLE instance shut down.

 

SQL>

SQL>

SQL> startup upgrade exclusive;

 

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

 

ORACLE instance started.

 

Total System Global Area 3.4360E+10 bytes
Fixed Size    5304248 bytes
Variable Size 1.8119E+10 bytes
Database Buffers 1.6173E+10 bytes
Redo Buffers   61804544 bytes

 

Database mounted.

 

Database opened.
SQL>

SQL>

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
nessy1::12c–> cd /u03/app/oracle/product/12.1.0.2/dbhome_1/OPatch

nessy1::12c–> ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 Production on Sat Aug 12 14:47:54 2017Copyright (c) 2012, 2017, Oracle.  All rights reserved.
Log file for this invocation: /u03/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_125122_2017_08_12_14_47_54/sqlpatch_invocation.log
Connecting to database…OKBootstrapping registry and package to current versions…doneDetermining current state…done
Current state of SQL patches:Patch 21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY):  Installed in the SQL registry onlyPatch 22674709 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)):  Installed in the SQL registry onlyPatch 25437695 (Database PSU 12.1.0.2.170418, Oracle JavaVM Component (APR2017)):  Installed in the binary registry onlyBundle series DBBP:  ID 170418 in the binary registry and ID 160419 in the SQL registry
Adding patches to installation queue and performing prereq checks…Installation queue:¬† The following patches will be rolled back:¬† ¬† 22674709 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016))¬† ¬† 21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY)¬† The following patches will be applied:¬† ¬† 25437695 (Database PSU 12.1.0.2.170418, Oracle JavaVM Component (APR2017))¬† ¬† 25397136 (DATABASE BUNDLE PATCH 12.1.0.2.170418)
Installing patches…Patch installation complete. ¬†Total patches installed: 4
Validating logfiles…Patch 22674709 rollback: SUCCESS¬† logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/22674709/20057886/22674709_rollback_NESSY_2017Aug12_14_48_20.log (no errors)Patch 21171382 rollback: SUCCESS¬† logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/21171382/19741104/21171382_rollback_NESSY_2017Aug12_14_49_09.log (no errors)Patch 25437695 apply: SUCCESS¬† logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/25437695/21057393/25437695_apply_NESSY_2017Aug12_14_49_17.log (no errors)Patch 25397136 apply: SUCCESS¬† logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/25397136/21145057/25397136_apply_NESSY_2017Aug12_14_49_17.log (no errors)SQL Patching tool complete on Sat Aug 12 14:49:54 2017nessy1::12c–>nessy1::12c–>nessy1::12c–>sqld

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 12 14:50:05 2017

 

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

 

 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

 

SQL>

SQL>

SQL> shutdown immediate;

 

Database closed.
Database dismounted.

 

ORACLE instance shut down.

 

SQL>

SQL>

SQL> startup;

 

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

 

ORACLE instance started.

 

Total System Global Area 3.4360E+10 bytes
Fixed Size    5304248 bytes
Variable Size 1.8119E+10 bytes
Database Buffers 1.6173E+10 bytes
Redo Buffers   61804544 bytes

 

Database mounted.

 

Database opened.
SQL>

SQL>

SQL> set lines 9990
SQL>

SQL>

 

SQLselect * from dba_registry;

 

 

COMP_ID       COMP_NAME       VERSION      STATUS  MODIFIED NAMESPACE       CONTROL SCHEMA PROCEDURE       STARTUP PARENT_ID       OTHER_SCHEMAS
—————————— ————————————————————————————————————————————————————————————————————————————————————— —————————— ———– —————————– —————————— ——————————————————————————————————————————– ——————————————————————————————————————————– ————————————————————- ——– —————————— —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-
APEX       Oracle Application Express       4.2.5.00.08      VALID  02-JUL-2016 16:08:09 SERVER       SYS APEX_040200 VALIDATE_APEX       FLOWS_FILES
OLS       Oracle Label Security       12.1.0.2.0      VALID  02-JUL-2016 16:08:08 SERVER       SYS LBACSYS VALIDATE_OLS
DV       Oracle Database Vault       12.1.0.2.0      VALID  02-JUL-2016 16:08:09 SERVER       SYS DVSYS VALIDATE_DV       DVF
ORDIM       Oracle Multimedia       12.1.0.2.0      VALID  02-JUL-2016 16:08:05 SERVER       SYS ORDSYS VALIDATE_ORDIM       MDSYS,ORDDATA,ORDPLUGINS,SI_INFORMTN_SCHEMA
CONTEXT       Oracle Text       12.1.0.2.0      VALID  02-JUL-2016 16:08:05 SERVER       SYS CTXSYS VALIDATE_CONTEXT
OWM       Oracle Workspace Manager       12.1.0.2.0      VALID  02-JUL-2016 16:08:01 SERVER       SYS WMSYS VALIDATE_OWM
SDO       Spatial       12.1.0.2.0      VALID  02-JUL-2016 16:08:07 SERVER       SYS MDSYS VALIDATE_SDO       MDDATA,MDSYS,SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN_USR
XDB       Oracle XML Database       12.1.0.2.0      VALID  02-JUL-2016 16:07:57 SERVER       SYS XDB DBMS_REGXDB.VALIDATEXDB       ANONYMOUS,XS$NULL
CATALOG       Oracle Database Catalog Views       12.1.0.2.0      VALID  02-JUL-2016 16:07:55 SERVER       SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC       Oracle Database Packages and Types       12.1.0.2.0      VALID  02-JUL-2016 16:07:55 SERVER       SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATPROC       APPQOSSYS,DBSNMP,DIP,GSMADMIN_INTERNAL,ORACLE_OCM,OUTLN,SYSTEM
JAVAVM       JServer JAVA Virtual Machine       12.1.0.2.0      VALID  12-AUG-2017 14:49:17 SERVER       SYS SYS INITJVMAUX.VALIDATE_JAVAVM

 

COMP_ID       COMP_NAME       VERSION      STATUS  MODIFIED NAMESPACE       CONTROL SCHEMA PROCEDURE       STARTUP PARENT_ID       OTHER_SCHEMAS
—————————— ————————————————————————————————————————————————————————————————————————————————————— —————————— ———– —————————– —————————— ——————————————————————————————————————————– ——————————————————————————————————————————– ————————————————————- ——– —————————— —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-
XML       Oracle XDK       12.1.0.2.0      VALID  02-JUL-2016 16:08:04 SERVER       SYS SYS XMLVALIDATE
CATJAVA       Oracle Database Java Packages       12.1.0.2.0      VALID  02-JUL-2016 16:08:04 SERVER       SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATJAVA
APS       OLAP Analytic Workspace       12.1.0.2.0      VALID  02-JUL-2016 16:08:06 SERVER       SYS SYS APS_VALIDATE
XOQ       Oracle OLAP API       12.1.0.2.0      VALID  02-JUL-2016 16:08:07 SERVER       SYS SYS XOQ_VALIDATE
RAC       Oracle Real Application Clusters       12.1.0.2.0      VALID  02-JUL-2016 16:08:09 SERVER       SYS SYS DBMS_CLUSTDB.VALIDATE

 

16 rows selected.

 

SQL>

SQL>

SQL>
SQL>

SQL>

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

Shell Script to Add Date to Filenames

Below Shell Script will append Date to all FileNames in Directory with extension .TXT. Also it will create a folder with the Date and move the files to it.

#!/bin/bash
FILE=”$(date +%Y%m%d)”
cd /bi_dataload
mkdir -p /bi_dataload/BI_$FILE
for f in *.TXT
do
mv -n “$f” /bi_dataload/BI_$FILE/”${f%%.*}”_”$FILE”.TXT
done

SYSAUX tablespace growing rapidly in Oracle, 12c Unified Audit Trail AUDSYS

The Unified Auditing in Oracle 12c can Grow the SYSAUX table to a large size. In our case it grew to about 100GB and We had to take action to make sure it did not grow any larger. First we query to identify what is residing inside the SYSAUX tablespace. In our case it was the AUDSYS schema which was taking the most space. All arrows pointed to Unified Auditing’s way

— Query to Check the SYSAUX Tablespace Occupants —

COLUMN “Item” FORMAT A25
COLUMN “Space Used (GB)” FORMAT 999.99
COLUMN “Schema” FORMAT A25
COLUMN “Move Procedure” FORMAT A40

SELECT occupant_name “Item”,
space_usage_kbytes/1048576 “Space Used (GB)”,
schema_name “Schema”,
move_procedure “Move Procedure”
FROM v$sysaux_occupants
ORDER BY 2
/
— Query to Check the SYSAUX Tablespace Largest Segments–

col owner for a6
col segment_name for a50
select * from
(select owner,segment_name||’~’||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = ‘SYSAUX’ ORDER BY BLOCKS desc) where rownum < 11;
— Clean Up the Unified Audit Trail —

1. First set the Date, starting from where you need to have the Unified Auditing Records

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
last_archive_time => TO_TIMESTAMP(’15-SEP-2016 00:00:10.0′,’DD-MON-RRRR HH24:MI:SS.FF’),
rac_instance_number => 2);
END;
/
2. Since Unified Auditing caches the audit trail in memory to implement a ‘lazy write’ feature that helps performance, some of the records eligible for deletion may still linger in the cache, to also first flush this cache

BEGIN
DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
END;
/

3. Execute the Purge Procedure

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE);
END;
/

Useful Links:

https://docs.oracle.com/database/121/DBSEG/audit_admin.htm#DBSEG474

How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)

 

Check ASM Diskgroup Space and Directory Size

The script below can be used to check the Disk Group Space Free and also Check Directory sizes for each Disk Group

The script is written by somebody at Pythian but i cannot re-collect the original link to the blog.

eg:
./asmcmd_du.sh

DiskGroup Total_MB Free_MB % Free
——— ——– ——- ——
DATAC1 15962112 11215880 70
DBFS_DG 415296 403740 97
RECOC1 3996000 3460272 86

./asmcmd_du.sh DATAC1

DiskGroup Total_MB Free_MB % Free
——— ——– ——- ——
DATAC1 15962112 10155732 63

DATAC1 subdirectories size

Subdir Used MB Mirror MB
—— ——- ———
CARATST/ 55646 111356
ECCTST/ 174912 349856
—— ——- ———
Total 2799978 5600788

Script Below :

[code language=”bash”]
#!/bin/bash
# Shadab Mohammad — 2016
#
# – If no parameter specified, show a du of each DiskGroup
# – If a parameter, print a du of each subdirectory
#

D=$1

#
# Colored thresholds (Red, Yellow, Green)
#
CRITICAL=90
WARNING=75

#
# Set the ASM env
#
ORACLE_SID=`ps -ef | grep pmon | grep asm | awk ‘{print $NF}’ | sed s’/asm_pmon_//’ | egrep "^[+]"`
export ORAENV_ASK=NO
. oraenv > /dev/null 2>&1

#
# A quick list of what is running on the server
#
ps -ef | grep pmon | grep -v grep | awk ‘{print $NF}’ | sed s’/.*_pmon_//’ | egrep "^([+]|[Aa-Zz])" | sort | awk -v H="`hostname -s`" ” | sed s’/, $//’

#
# Manage parameters
#
if [[ -z $D ]]
then # No directory provided, will check all the DG
DG=`asmcmd lsdg | grep -v State | awk ‘{print $NF}’ | sed s’/\///’`
SUBDIR="No" # Do not show the subdirectories details if no directory is specified
else
DG=`echo $D | sed s’/\/.*$//g’`
fi

#
# A header
#
printf "\n%25s%16s%16s%14s" "DiskGroup" "Total_MB" "Free_MB" "% Free"
printf "\n%25s%16s%16s%14s\n" "———" "——–" "——-" "——"

#
# Show DG info
#
for X in ${DG}
do
asmcmd lsdg ${X} | tail -1 |\
awk -v DG="$X" -v W="$WARNING" -v C="$CRITICAL" ‘\
BEGIN \
{COLOR_BEGIN = "\033[1;" ;
COLOR_END = "\033[m" ;
RED = COLOR_BEGIN"31m" ;
GREEN = COLOR_BEGIN"32m" ;
YELLOW = COLOR_BEGIN"33m" ;
COLOR = GREEN ;
}
{ FREE = sprintf("%12d", $8/$7*100) ;
if ((100-FREE) > W) {COLOR=YELLOW ;}
if ((100-FREE) > C) {COLOR=RED ;}
printf("%25s%16s%16s%s\n", DG, $7, $8, COLOR FREE COLOR_END) ; }’
done
printf "\n"

#
# Subdirs info
#
if [ -z ${SUBDIR} ]
then
(for DIR in `asmcmd ls ${D}`
do
echo ${DIR} `asmcmd du ${D}/${DIR} | tail -1`
done) | awk -v D="$D" ‘ BEGIN { printf("\n\t\t%40s\n\n", D " subdirectories size") ;
printf("%25s%16s%16s\n", "Subdir", "Used MB", "Mirror MB") ;
printf("%25s%16s%16s\n", "——", "——-", "———") ;}
{
printf("%25s%16s%16s\n", $1, $2, $3) ;
use += $2 ;
mir += $3 ;
}
END { printf("\n\n%25s%16s%16s\n", "——", "——-", "———") ;
printf("%25s%16s%16s\n\n", "Total", use, mir) ;} ‘
fi

#************************************************************************#
#* E N D O F S O U R C E *#
#************************************************************************#
[/code]