Database , Application and Fusion Technology : 2019

Friday, December 20, 2019

AQ$_FND_CP_GSM_OPP_AQTBL table created huge mess ....

Issue : OPP given us issue and it got stuck .

Root cause identified that table AQ$_FND_CP_GSM_OPP_AQTBL huge size .

Followed below steps:

1. Shutdown application services

2. select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

3. Sys user :

DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => 'APPLSYS.FND_CP_GSM_OPP_AQTBL',
purge_condition => NULL,
purge_options => po);
END;
/

4. select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

5. Run cmclean.sql ( For 12.2 environment use OAM )

6. Startup the services

Our issue got fixed.

Thanks

Thursday, December 19, 2019

Suddenly users got huge old workflow notification mails

Issue : Users complained they got huge number of old notification mails

Action -

Very old items still there in wf_notification_out table.

1) SQL> select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid, decode(wfe.state,0,'0 = Ready',1,'1 = Delayed',2,'2 = Retained',
3,'3 = Exception',to_char(substr(wfe.state,1,12))) State,
count(*)COUNT from applsys.WF_NOTIFICATION_OUT wfe group by wfe.corrid, wfe.state;
2 3

CORRID STATE COUNT
-------------------------------------------------- ------------- ----------
APPS:POAPPRV:EMAIL_PO_PDF 234 3 = Exception 1
APPS:ALR: 0 = Ready 123
APPS:REQAPPRV:PO_REQ_APPROVED_JRAD 3 = Exception 1625
APPS:POAPPRV:PO_PO_HAS_BEEN_REJECTED 3 = Exception 380
APPS:OECHGORD:ISO_CANCEL_MSG 3 = Exception 1
APPS:POAPPRV:PO_PO_APPROVE_PDF_OAF 2 = Retained 59
APPS:CS_MSGS:FYI_MESSAGE 3 = Exception 7
APPS:ASOAPPRV:OA_FYI_TO_REQUESTER 3 = Exception 339
APPS:UMXLHELP:LOGIN_HELP_MSG_PWD 3 = Exception 11
APPS:FNDCMMSG:SIMPLE_REQ_COMPLETION 2 = Retained 6
APPS:ASOAPPRV:OA_REQ_APPR_BY_ALL_APPR 3 = Exception 284

2) Stop Workflow notification mailer services

3) Rebuild the Queue , Prior rebuild Q , please check all Mail status and OPEN status.

$ cd $FND_TOP/patch/115/sql/
$ ls -ltr wfntfqup.sql
-rwxrwxr-x 1 prodappl dba 11862 Mar 25 2010 wfntfqup.sql
$ sqlplus apps/*****

SQL> @wfntfqup.sql apps ****** applsys

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.95

Commit complete.

Elapsed: 00:00:00.01
**** TEMPORARY TABLES / AQs created ****

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.96
**** Messages backed up ****

Commit complete.

Elapsed: 00:00:00.00
**** wfaqback.sql completed, executing SQLs from wfntfqup

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.18
*** invoking wfaqrenq.sql ******
*** Re-enqueing messages

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17

Commit complete.

Elapsed: 00:00:00.00
***** Re-enqueue OF Alerts completed *****

4) After that Q only having new items:

SQL> select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid, decode(wfe.state,0,'0 = Ready',1,'1 = Delayed',2,'2 = Retained',
2 3,'3 = Exception',to_char(substr(wfe.state,1,12))) State,
count(*)COUNT from applsys.wf_notification_out wfe group by wfe.corrid, wfe.state; 3

CORRID STATE COUNT
-------------------------------------------------- ------------- ----------
APPS:ALR: 0 = Ready 6
APPS:WFERROR:DEFAULT_EVENT_ERROR 0 = Ready 2
APPS:OMERROR:OMERROR_MSG 0 = Ready 2
APPS:REQAPPRV:PO_REQ_APPROVE_JRAD 0 = Ready 4

Thanks,

Thursday, December 12, 2019

Install Apache Tomcat on Linux

Install Tomcat on Linux :

1) Downloaded apache-tomcat-9.0.8.tar.gz for Linux server

2) Untar it on some location

[root@stand soft]# tar -xvf apache-tomcat-9.0.8.tar.gz -C /u02/apache/
apache-tomcat-9.0.8/conf/
apache-tomcat-9.0.8/conf/catalina.policy

3) Used tomcat user for this so change the ownership of the directory

chown tomcat:tomcat /u02/apache/apache-tomcat-9.0.8

Also edit bash_profile as

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

Source the environment variable :

. ./bash_profile

4) We want to start it on port 8006

so modified the value as below:

-bash-4.1$ pwd
/u02/apache/apache-tomcat-9.0.8/conf

-bash-4.1$ grep -i 8006 server.xml

<Connector port="8006" protocol="HTTP/1.1"

5) Start tomcat

-bash-4.1$ sh $CATALINA_HOME/bin/startup.sh
Using CATALINA_BASE: /u02/apache/apache-tomcat-9.0.8
Using CATALINA_HOME: /u02/apache/apache-tomcat-9.0.8
Using CATALINA_TMPDIR: /u02/apache/apache-tomcat-9.0.8/temp
Using JRE_HOME: /usr
Using CLASSPATH: /u02/apache/apache-tomcat-9.0.8/bin/bootstrap.jar:/u02/apache/apache-tomcat-9.0.8/bin/tomcat-juli.jar
Tomcat started.

6) Launch the url http://stand.som.com:8006

Thanks,

Linux - Port is being used netstat or lsof

Linux - Port is being used.

Use command

netstat -tuplen

[oracle@stand ~]$ netstat -tuplen

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State User Inode PID/Program name

tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 0 13395 -

tcp 0 0 0.0.0.0:51985 0.0.0.0:* LISTEN 29 13946 -

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 0 15491 -

tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 0 14484 -

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 0 54988 -

tcp 0 0 :::42156 :::* LISTEN 501 19541 3987/ora_d000_prod1

tcp 0 0 :::111 :::* LISTEN 0 13400 -

tcp 0 0 :::8080 :::* LISTEN 501 19814 3858/tnslsnr

tcp 0 0 :::1522 :::* LISTEN 501 19034 3858/tnslsnr

tcp 0 0 :::22 :::* LISTEN 0 15495 -

tcp 0 0 ::1:631 :::* LISTEN 0 14483 -

tcp 0 0 ::ffff:127.0.0.1:8005 :::* LISTEN 91 85049 -

tcp 0 0 :::50471 :::* LISTEN 29 13954 -

tcp 0 0 :::8009 :::* LISTEN 91 84985 -

udp 0 0 0.0.0.0:750 0.0.0.0:* 0 13184 -

udp 0 0 0.0.0.0:111 0.0.0.0:* 0 13390 -

udp 0 0 0.0.0.0:631 0.0.0.0:* 0 14487 -

udp 0 0 192.168.245.154:123 0.0.0.0:* 38 55000 -

[oracle@stand ~]$ lsof -i :8080 | grep LISTEN
tnslsnr 3858 oracle 14u IPv6 19814 0t0 TCP *:webcache (LISTEN)

[oracle@stand ~]$ ps -ef|grep 3858
oracle 3858 1 0 14:39 ? 00:00:01 /u04/app/oracle/11g/bin/tnslsnr LISTENER -inherit
oracle 20110 17768 0 18:44 pts/0 00:00:00 grep 3858

Thanks,

Tomcat Apache issue - Exception in thread "main" java.lang.UnsupportedClassVersionError: org/apache/catalina/startup/Bootstrap : Unsupported major.minor version 52.0

Tomcat / Apache Issue:

Exception in thread "main" java.lang.UnsupportedClassVersionError: org/apache/catalina/startup/Bootstrap : Unsupported major.minor version 52.0

Fix for the issue - We have upgraded JDK to latest version.

-bash-4.1$ sh $CATALINA_HOME/bin/startup.sh
Using CATALINA_BASE: /u02/apache/apache-tomcat-9.0.8
Using CATALINA_HOME: /u02/apache/apache-tomcat-9.0.8
Using CATALINA_TMPDIR: /u02/apache/apache-tomcat-9.0.8/temp
Using JRE_HOME: /usr
Using CLASSPATH: /u02/apache/apache-tomcat-9.0.8/bin/bootstrap.jar:/u02/apache/apache-tomcat-9.0.8/bin/tomcat-juli.jar
Tomcat started.

Thanks,

Install latest Java on Linux machine

Please download latest java on linux machine:

-rw-r--r-- 1 root root 319890464 Apr 12 17:23 jdk-9.0.4_linux-x64_bin.rpm
[root@stand soft]# rpm -ivh jdk-9.0.4_linux-x64_bin.rpm
Preparing... ########################################### [100%]
1:jdk-9.0.4 ########################################### [100%]
Unpacking JAR files...
plugin.jar...
javaws.jar...
deploy.jar...

[root@stand soft]# java -version
java version "9.0.4"
Java(TM) SE Runtime Environment (build 9.0.4+11)
Java HotSpot(TM) 64-Bit Server VM (build 9.0.4+11, mixed mode)

[root@stand soft]# rpm -q --whatprovides java
java-1.6.0-openjdk-1.6.0.0-1.50.1.11.5.el6_3.x86_64
java-1.5.0-gcj-1.5.0.0-29.1.el6.x86_64
jdk-9.0.4-9.0.4-ga.x86_64

Thanks,

Untar a file to another location

Untar a file to another location:

from /u02/soft to untar on /u02/apache

[root@stand soft]# tar -xvf apache-tomcat-9.0.8.tar.gz -C /u02/apache/

Thanks,

Linux OS user is facing issue "This account is currently not available"

Linux OS User issue:

Fix for any OS user facing below issue:

[root@stand ~]# su - tomcat
This account is currently not available.

[root@stand ~]# cat /etc/passwd | grep tomcat
tomcat:x:91:91:Apache Tomcat:/usr/share/tomcat6:/sbin/nologin

Above /sbin/nologin is issue , it means no shell assigned to user:
Check the shell using below command.

[root@stand ~]# cat /etc/shells
/bin/sh
/bin/bash
/sbin/nologin
/bin/tcsh
/bin/csh
/bin/dash
/bin/zsh
/bin/mksh
/bin/ksh

[root@stand ~]# chsh -s /bin/bash tomcat
Changing shell for tomcat.
Shell changed.

[root@stand ~]# su - tomcat

-bash-4.1$

Thanks,

AIX - During adstrtal.sh getting "Database connection could not be established. Either the database is down or the APPS credentials supplied are wrong"

In AIX machine - We have encountered an issue where while starting application services we are getting issue

Database connection could not be established. Either the database is down or the APPS credentials supplied are wrong

We did below things:

1) PING is working fine from APPS to DB and DB to APPS

2) TNSPING is working fine.

3) Able to login sqlplus from application node using /nolog and apps user, so apps user password is not an issue.

4) Did DB and Listener bounce

5) Autoconfig on DB and Apps

6) DBC file also looks OK

In our case issue was with /tmp, someone did something and applmgr user unable to create any file on this location:

$ touch aa

touch: 0652-048 Cannot change the modification time on aa.

Chnage the permission of /tmp to 777 and then user applmgr able to write it.

Application came up fine.

Thanks,

R12.2 Log files location

Below are the path of logfiles in R12.2:

Admin server - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/AdminServer/logs/AdminServer.log

oacore logfile - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1.log

oacore out file - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1.out

oacore diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1-diagnostic.log

oafm logfile - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1.log

oafm outfile - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1.out

oafm diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1-diagnostic.log

form server log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/forms_server1.log

form server access log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/access.log

form server out file - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/forms_server1.out

form server diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/forms_server1-diagnostic.log

forms-c4ws_server1 log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms-c4ws_server1/logs/forms-c4ws_server1.log

forms-c4ws_server1 out file - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms-c4ws_server1/logs/forms-c4ws_server1.out

forms-c4ws_server1 diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms-c4ws_server1/logs/forms-c4ws_server1-diagnostic.log

BR,

Sunday, November 24, 2019

Workflow notification mailer issue - Few notification not going

Issue due to smtp server.
SMTP test was failing , so once SMTP issue resolved notification mailer start coming.

Thanks

Wednesday, November 13, 2019

Few important POSTGRES monitoring command

pg_controldata -- It display control information of a PostgreSQL database cluster

-bash-4.1$ ./pg_controldata /u01/postgres/data
pg_control version number: 1002
Catalog version number: 201707211
Database system identifier: 6754729802804905833
...
..........

Query Monitoring:
=================

postgres=# SELECT * FROM pg_stat_activity;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start |
query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_
type
-------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+----------------------------------+----------------------------------+-----
-----------------------------+----------------------------------+-----------------+---------------------+--------+-------------+--------------+---------------------------------+------------
---------
| | 89107 | | | | | | | 2017-10-13 21:33:17.427986+05:30 | |
| | Activity | AutoVacuumMain | | | | | autovacuum
launcher
| | 89109 | 10 | postgres | | | | | 2017-10-13 21:33:17.430675+05:30 | |
| | Activity | LogicalLauncherMain | | | | | background
worker
13804 | postgres | 89818 | 10 | postgres | | | | -1 | 2017-10-13 21:45:38.093483+05:30 | 2019-11-13 21:45:39.471797+05:30 | 2019
-11-13 21:45:39.471797+05:30 | 2017-10-13 21:45:39.471805+05:30 | | | active | | 571 | SELECT * FROM pg_stat_activity; | client back
end
| | 89105 | | | | | | | 2017-10-13 21:33:17.426106+05:30 | |
| | Activity | BgWriterMain | | | | | background
writer
| | 89104 | | | | | | | 2017-10-13 21:33:17.429943+05:30 | |
| | Activity | CheckpointerMain | | | | | checkpointe
r
| | 89106 | | | | | | | 2017-10-13 21:33:17.427542+05:30 | |
| | Activity | WalWriterMain | | | | | walwriter
(6 rows)

Database Statistics:
====================

postgres=# SELECT * FROM pg_stat_database;
datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp
_bytes | deadlocks | blk_read_time | blk_write_time | stats_reset
-------+-----------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+-----
-------+-----------+---------------+----------------+----------------------------------
13804 | postgres | 1 | 53 | 0 | 208 | 4054 | 26818 | 1639 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 2017-10-13 21:21:18.233825+05:30
16393 | test | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 |
1 | template1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

Table activities:
=================

postgres=# SELECT * FROM pg_stat_all_tables;
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_
mod_since_analyze | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+--------------------+-------------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---
------------------+-------------+-----------------+--------------+------------------+--------------+------------------+---------------+-------------------
2606 | pg_catalog | pg_constraint | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | | | | | 0 | 0 | 0 | 0
13644 | information_schema | sql_implementation_info | 0 | 0 | | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | | | | | 0 | 0 | 0 | 0

Table Block activity:
====================

postgres=# SELECT * FROM pg_statio_all_tables;
relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+--------------------+-------------------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
13646 | pg_toast | pg_toast_13644 | 0 | 0 | 0 | 0 | | | |
3596 | pg_catalog | pg_seclabel | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
2603 | pg_catalog | pg_amproc | 8 | 51 | 6 | 108 | | | |

Lock monitoring:
================

postgres=# SELECT * FROM pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------
relation | 13804 | 11577 | | | | | | | | 3/37 | 90315 | AccessShareLock | t | t
virtualxid | | | | | 3/37 | | | | | 3/37 | 90315 | ExclusiveLock | t | t
(2 rows)

Thanks,

How to start/stop postgres services

How to start/stop postgres services

Services are not running:

-bash-4.1$ ps -ef | egrep "pg|postgres"
root 85717 79705 0 20:35 pts/0 00:00:00 su - postgres
postgres 85718 85717 0 20:35 pts/0 00:00:00 -bash
postgres 85777 85718 1 20:35 pts/0 00:00:00 ps -ef
postgres 85778 85718 0 20:35 pts/0 00:00:00 egrep pg|postgres

Using postgres user try to start using below :

-bash-4.1$ cd /u01/postgres/bin

-bash-4.1$ ./pg_ctl -D /u01/postgres/data -l /u01/postgres/server.log start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.

-bash-4.1$

When searched got below error in logfile:

2017-08-06 20:35:26.620 IST [85781] FATAL: data directory "/u01/postgres/data" has group or world access
2017-08-06 20:35:26.620 IST [85781] DETAIL: Permissions should be u=rwx (0700).

-bash-4.1$ chmod -R 700 /u01/postgres/data

-bash-4.1$ ./pg_ctl -D /u01/postgres/data -l /u01/postgres/server.log start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start.... done
server started

-bash-4.1$ ps -ef | egrep "pg|postgres"
root 85717 79705 0 20:35 pts/0 00:00:00 su - postgres
postgres 85718 85717 0 20:35 pts/0 00:00:00 -bash
postgres 85834 1 0 20:36 pts/0 00:00:00 /u01/postgres/bin/postgres -D /u01/postgres/data
postgres 85835 85834 0 20:36 ? 00:00:00 postgres: logger process
postgres 85837 85834 0 20:36 ? 00:00:00 postgres: checkpointer process
postgres 85838 85834 0 20:36 ? 00:00:00 postgres: writer process
postgres 85839 85834 0 20:36 ? 00:00:00 postgres: wal writer process
postgres 85840 85834 0 20:36 ? 00:00:00 postgres: autovacuum launcher process
postgres 85841 85834 0 20:36 ? 00:00:00 postgres: stats collector process
postgres 85842 85834 0 20:36 ? 00:00:00 postgres: bgworker: logical replication launcher
postgres 85846 85718 0 20:36 pts/0 00:00:00 ps -ef
postgres 85847 85718 0 20:36 pts/0 00:00:00 egrep pg|postgres

-bash-4.1$ ./pg_ctl -D /u01/postgres/data -l /u01/postgres/server.log stop
waiting for server to shut down.... done
server stopped

-bash-4.1$ ps -ef | egrep "pg|postgres"
root 85717 79705 0 20:35 pts/0 00:00:00 su - postgres
postgres 85718 85717 0 20:35 pts/0 00:00:00 -bash
postgres 88181 85718 1 21:17 pts/0 00:00:00 ps -ef

postgres 88182 85718 0 21:17 pts/0 00:00:00 egrep pg|postgres

========================================================

Another way we can do using below:

-bash-4.1$ export PGDATA=/u01/postgres/data

-bash-4.1$ ./pg_ctl stop
waiting for server to shut down.... done
server stopped

-bash-4.1$ ps -ef | egrep "pg|postgres"
root 85717 79705 0 20:35 pts/0 00:00:00 su - postgres
postgres 85718 85717 0 20:35 pts/0 00:00:00 -bash
postgres 89097 85718 1 21:33 pts/0 00:00:00 ps -ef
postgres 89098 85718 0 21:33 pts/0 00:00:00 egrep pg|postgres

-bash-4.1$ ./pg_ctl start
waiting for server to start....2017-11-13 21:33:17.087 IST [89101] LOG: listening on IPv4 address "0.0.0.0", port 5432
2017-11-13 21:33:17.088 IST [89101] LOG: listening on IPv6 address "::", port 5432
2017-11-13 21:33:17.098 IST [89101] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2017-11-13 21:33:17.106 IST [89101] LOG: redirecting log output to logging collector process
2017-11-13 21:33:17.106 IST [89101] HINT: Future log output will appear in directory "log".
done
server started

Thanks,

Oracle to MS Sql Server Heterogeneous DB link

Below are the steps we have to take care to achieve it:

1) Install ODBC driver or Gateway ( we used gateway )
2) Configure Listener and TNSNAMES .ora files
3) $ORACLE_HOME/hs/admin configuration
4) Create DB Link and test it

1) Gateway installation:

2) Configure Listener and TNSNAMES.ora file as below:

Listener.ora

dg4msql =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.58.10)(PORT = 1521))

)

SID_LIST_DG4MSQL =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME = /u01/app/12c/database/dbhome_1)

(ENV = "LD_LIBRARY_PATH=/u01/app/12c/database/dbhome_1/dg4msql/driver/lib:/u01/app/12c/database/dbhome_1/lib")

(SID_NAME = dg4msql)

(PROGRAM = dg4msql)

)

tnsnames.ora file:

DG4MSQL =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.58.10)(PORT=1521))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)

[oracle@prod admin]$ tnsping dg4msql

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 18-NOV-2017 17:40:47

Used parameter files:

/u01/app/12c/database/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.58.10)(PORT=1521)) (CONNECT_DATA=(SID=dg4msql)) (HS=OK))

OK (10 msec)

3) HS/admin configuration:

[oracle@prod admin]$ cat initdg4msql.ora

# This is a customized agent init file that contains the HS parameters

# that are needed for the Database Gateway for Microsoft SQL Server

# HS init parameters

HS_FDS_CONNECT_INFO=[192.168.58.10]:1433//testing

# alternate connect format is hostname/serverinstance/databasename

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

4) Create db link as mentioned :

create public database link db_link_name connect to username_of_sqlserver identified by password_of_sqlserver using 'dg4msql';

select * from dual@db_link_name;

Regards,

Monday, November 4, 2019

Few important day to day command in Postgres

Install Postgres version 10.10 on Linux 64 machine

Download postgres from postgres source website and proceed as below:

[oracle@prod u01]$ cd stage/
[oracle@prod stage]$ ls -ltr
-rw-r--r-- 1 root daemon 128913813 Nov 2 20:13 postgresql-10.10-2-linux-x64.run

RunInstaller should be run from super user

[root@prod stage]# ./postgresql-10.10-2-linux-x64.run
----------------------------------------------------------------------------
Welcome to the PostgreSQL Setup Wizard.

----------------------------------------------------------------------------
Please specify the directory where PostgreSQL will be installed.

Installation Directory [/opt/PostgreSQL/10]: /u01/postgres

----------------------------------------------------------------------------
Select the components you want to install; clear the components you do not want
to install. Click Next when you are ready to continue.

PostgreSQL Server [Y/n] :Y

pgAdmin 4 [Y/n] :Y

Stack Builder [Y/n] :Y

Command Line Tools [Y/n] :Y

Is the selection above correct? [Y/n]: Y

----------------------------------------------------------------------------
Please select a directory under which to store your data.

Data Directory [/u01/postgres/data]:

----------------------------------------------------------------------------
Please provide a password for the database superuser (postgres). A locked Unix
user account (postgres) will be created if not present.

Password :
Retype password :
----------------------------------------------------------------------------
Please select the port number the server should listen on.

Port [5432]:

----------------------------------------------------------------------------
Advanced Options

Select the locale to be used by the new database cluster.

Locale

[1] [Default locale]
[2] aa_DJ
..
....
[718] zu_ZA.utf8
Please choose an option [1] :

----------------------------------------------------------------------------
Pre Installation Summary

The following settings will be used for the installation::

Installation Directory: /u01/postgres
Server Installation Directory: /u01/postgres
Data Directory: /u01/postgres/data
Database Port: 5432
Database Superuser: postgres
Operating System Account: postgres
Database Service: postgresql-10
Command Line Tools Installation Directory: /u01/postgres
pgAdmin4 Installation Directory: /u01/postgres/pgAdmin 4
Stack Builder Installation Directory: /u01/postgres

Press [Enter] to continue:

----------------------------------------------------------------------------
Setup is now ready to begin installing PostgreSQL on your computer.

Do you want to continue? [Y/n]: y

----------------------------------------------------------------------------
Please wait while Setup installs PostgreSQL on your computer.

Installing
0% ______________ 50% ______________ 100%
#########################################

----------------------------------------------------------------------------
Setup has finished installing PostgreSQL on your computer.

Once installer process completed check postgres background processes:

[root@prod stage]# su - postgres
-bash-4.1$ ps -fu postgres
UID PID PPID C STIME TTY TIME CMD
postgres 6527 1 0 20:51 ? 00:00:00 /u01/postgres/bin/postgres -D /u01/postgres/data
postgres 6528 6527 0 20:51 ? 00:00:00 postgres: logger process
postgres 6530 6527 0 20:51 ? 00:00:00 postgres: checkpointer process
postgres 6531 6527 0 20:51 ? 00:00:00 postgres: writer process
postgres 6532 6527 0 20:51 ? 00:00:00 postgres: wal writer process
postgres 6533 6527 0 20:51 ? 00:00:00 postgres: autovacuum launcher process
postgres 6534 6527 0 20:51 ? 00:00:00 postgres: stats collector process
postgres 6535 6527 0 20:51 ? 00:00:00 postgres: bgworker: logical replication launcher
postgres 7530 7529 0 21:00 pts/0 00:00:00 -bash
postgres 7590 7530 0 21:00 pts/0 00:00:00 ps -fu postgres

-bash-4.1$ ps -ef | egrep "pg|postgres"
postgres 6527 1 0 20:51 ? 00:00:00 /u01/postgres/bin/postgres -D /u01/postgres/data
postgres 6528 6527 0 20:51 ? 00:00:00 postgres: logger process
postgres 6530 6527 0 20:51 ? 00:00:00 postgres: checkpointer process
postgres 6531 6527 0 20:51 ? 00:00:00 postgres: writer process
postgres 6532 6527 0 20:51 ? 00:00:00 postgres: wal writer process
postgres 6533 6527 0 20:51 ? 00:00:00 postgres: autovacuum launcher process
postgres 6534 6527 0 20:51 ? 00:00:00 postgres: stats collector process
postgres 6535 6527 0 20:51 ? 00:00:00 postgres: bgworker: logical replication launcher
root 7529 3639 0 21:00 pts/0 00:00:00 su - postgres
postgres 7530 7529 0 21:00 pts/0 00:00:00 -bash
postgres 7662 7530 0 21:01 pts/0 00:00:00 ps -ef
postgres 7663 7530 0 21:01 pts/0 00:00:00 egrep pg|postgres

Regards,

Fragmented Table details

Fragmented Table in Oracle

Below query will display all the highly fragmented table:

SQL> set lines 999
SQL> set pages 999
SQL> col table_name format a30
SQL> select *
from (select table_name,
2 3 round((blocks * 8), 2) "size (kb)",
round((num_rows * avg_row_len / 1024), 2) "actual_data (kb)",
4 5 (round((blocks * 8), 2) -
round((num_rows * avg_row_len / 1024), 2)) "wasted_space (kb)"
from dba_tables
where (round((blocks * 8), 2) >
round((num_rows * avg_row_len / 1024), 2))
order by 4 desc)
WHERE ROWNUM <= 10; 6 7 8 9 10 11

TABLE_NAME size (kb) actual_data (kb) wasted_space (kb)
------------------------------ ---------- ---------------- -----------------
ASO_ORDER_FEEDBACK_T 6130376 1315620.42 4814755.58
XLA_DIAG_SOURCES 26622880 22002509.8 4620370.23

10 rows selected.

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='ASO_ORDER_FEEDBACK_T';

SUM(BYTES)/1024/1024/1024
-------------------------
5.88623047

SQL> select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
2 round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
3 round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
4 5 from all_tables WHERE table_name='ASO_ORDER_FEEDBACK_T';

TABLE_NAME AVG_ROW_LEN TOTAL_SIZE ACTUAL_SIZE FRAGMENTED_SPACE percentage
------------------------------ ----------- ------------------------------------------ ------------------------------------------ ------------------------------------------ ----------
ASO_ORDER_FEEDBACK_T 449 11973.39MB 1284.79Mb 10688.61MB 89.2697056

If above percentage is above 20 percent then we can think of these tables.

All the index details in that table:

SQL> select index_name from dba_indexes where table_name='ASO_ORDER_FEEDBACK_T'

INDEX_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_IL0000081743C00333$$
SYS_IL0000081743C00334$$
SYS_IL0000081743C00335$$
SYS_IL0000081743C00336$$

SYS_IL0000081743C00337$$

We can take many measure to take care this fragmented table.

Regards,

Friday, November 1, 2019

Oracle Webtier 12c ( HTTP Server ) installation and Configure to run Privilege port 80

Oracle Webtier 12c ( HTTP Server ) installation and Configure to run Privilege port 80

Download software form support and unzip the software as below:

[oracle@test u02]$ unzip Oracle_Http_Server_12c_Linux_X86_64bit.zip
Archive: Oracle_Http_Server_12c_Linux_X86_64bit.zip
inflating: fmw_12.2.1.2.0_ohs_linux64.bin

inflating: fmw_12212_readme.htm

Prior running below command set your display setting

[oracle@test u02]$./fmw_12.2.1.2.0_ohs_linux64.bin

You can see above web url opening with default port 7777 we wanted to change it to port 80 .

Few ports are explicitly reserved for system.
If you want to use those port then few other settings needs to be modified.

Shutdown OHS

[oracle@test bin]$ ./stopComponent.sh ohs1

[oracle@test bin]$ cd /u02/ohs_home/user_projects/domains/base_domain/config/fmwconfig/components/OHS/ohs1

[oracle@test ohs1]$ cp httpd.conf httpd.conf_org
[oracle@test ohs1]$ vi httpd.conf

Modify two values as mentioned below:

From :

Listen 192.168.171.20:7777
ServerName http://192.168.171.20:7777

To :

Listen 192.168.171.20:80
ServerName http://192.168.171.20:80

While starting OHS after modified port we are getting below error:

[oracle@test bin]$ ./startComponent.sh ohs1
Starting system Component ohs1 ...

weblogic.nodemanager.NMException: Received error message from Node Manager Server: [Server start command for OHS server 'ohs1' failed due to: [Failed to start the server ohs1
Check log file /u02/ohs_home/user_projects/domains/base_domain_new/system_components/OHS/ohs_nm.log
Check log file /u02/ohs_home/user_projects/domains/base_domain_new/servers/ohs1/logs/ohs1.log]. Please check Node Manager log and/or server 'ohs1' log for detailed information.]. Please check Node Manager log for details.
Error: Error occurred while performing nmStart : Error Starting server ohs1 : Received error message from Node Manager Server: [Server start command for OHS server 'ohs1' failed due to: [Failed to start the server ohs1
Check log file /u02/ohs_home/user_projects/domains/base_domain_new/system_components/OHS/ohs_nm.log
Check log file /u02/ohs_home/user_projects/domains/base_domain_new/servers/ohs1/logs/ohs1.log]. Please check Node Manager log and/or server 'ohs1' log for det
ailed information.]. Please check Node Manager log for details.
Use dumpStack() to view the full stacktrace :

After followed one of Oracle Doc id ohs came up fine:

[oracle@test bin]$ ./startComponent.sh ohs1
....
......
Successfully started server ohs1 ...
Successfully disconnected from Node Manager.

Exiting WebLogic Scripting Tool.

Done

Now URL coming with 80 as mentioned below:

Regards,

Thursday, October 31, 2019

Oracle grant and revoke from user - GRANT and REVOKE

Below example show how grant and revoke work for a user in Oracle:

SQL> select owner from all_objects where object_name like '%CUSTOMER%';

OWNER
--------------------------------------------------------------------------------
MY_WORKSPACE

SQL> conn MY_WORKSPACE/*******
Connected.

SQL> desc customer
Name Null? Type
----------------------------------------- -------- ----------------------------
CUST_ID NUMBER
CUST_NAME VARCHAR2(40)
CUST_SEX VARCHAR2(10)
CUST_ADD VARCHAR2(100)
CUST_MOBILE NUMBER
CUST_JOIN_DATE DATE
CUST_STATUS VARCHAR2(10)

SQL> CREATE VIEW V_CUSTOMER
AS SELECT CUST_ID,CUST_NAME
FROM CUSTOMER; 2 3

View created.

SQL> select * from V_CUSTOMER;

CUST_ID CUST_NAME
---------- ----------------------------------------
2 ram
3 shayam
4 madhu

SQL> create user temp_user identified by welcome1;

User created.

SQL> grant create session to temp_user;

Grant succeeded.

SQL> grant select on MY_WORKSPACE.V_CUSTOMER to temp_user;

Grant succeeded.

SQL> conn temp_user/welcome1
Connected.

SQL> select * from MY_WORKSPACE.V_CUSTOMER;

CUST_ID CUST_NAME
---------- ----------------------------------------
2 ram
3 shayam
4 madhu

3 rows selected.

As sysdba revoke it :

SQL> revoke select on MY_WORKSPACE.V_CUSTOMER from temp_user;

Revoke succeeded.

SQL> conn temp_user/welcome1
Connected.

SQL> select * from MY_WORKSPACE.V_CUSTOMER;
select * from MY_WORKSPACE.V_CUSTOMER
*
ERROR at line 1:
ORA-00942: table or view does not exist

Regards

Using XMING to install software

If VNC Server not configured or any issue with Linux display/desktop then use below
method to install any software from locally using putty.

1) Download Xming , there might be other product but I am using it.

2) Start Xming and it will show you in system tray, just let it run in background.

3) start putty and make connection to server . IMPORTANT Settings now

In putty SSH -->> X11 -->> Enable X11 Forwarding -->> X Display location put it as localhost:0

4) Execute installer and it launcher should come in local machine using XMING

Regards

Wednesday, October 30, 2019

Apex Upgrade - Upgrade Apex from 5.1 to 19.1

Apex Upgrade - Upgrade Apex from 5.1 to 19.1

Upgrade Apex 5.1 to 19.1

Take full database backup before doing apex upgrade

1) Download latest Apex 19.1 in our case we are upgrading apex

2) Unzip it to the location where you wanted to keep it.

3) mv old apex directory as /u01/app/apex to /u01/app/apex_old

4) start upgrade process

cd /u01/app/apex

SQL> @apexins APEX APEX TEMP /i/

output something like below

. Application Express (APEX) Installation.
..........................................
.
...set_appun.sql
... Checking prerequisites (MANUAL)
.
. SYSDBA Privilege
. pass - Connection with SYSDBA privilege.
. Database rolling upgrade
. pass - No rolling upgrade.
. DB components
. pass - APEX: version=5.1.4.00.08
. pass - CATPROC: version=12.1.0.2.0
. pass - XDB: version=12.1.0.2.0
. XDB
. pass - is valid
. PL/SQL Web Toolkit
. pass - version 10.1.2.1.9
. Tablespaces
. pass - found APEX
. pass - found APEX
. pass - found TEMP
. PHASES (1,2,3)...

PL/SQL procedure successfully completed.

# Creating APEX_INSTANCE_ADMIN_USER
#
APEX_INSTANCE_ADMIN_USER already exists - do nothing.
#
# Actions in Phase 1:
#
ok 1 - BEGIN | 0.00
ok 2 - Creating FLOWS_FILES | 0.00
ok 3 - Creating APEX_PUBLIC_USER | 0.00
ok 4 - Creating APEX_GRANTS_FOR_NEW_USERS_ROLE | 0.02
ok 5 - Creating SYS Objects | 0.03
ok 6 - Creating APEX Tables | 0.37
ok 7 - Installing Package Specs (Runtime) | 1.52
ok 8 - Installing Package Bodies (Runtime) | 0.38
ok 9 - Dev Grants | 0.05
ok 10 - Installing Package Specs (Dev) | 0.27
ok 11 - Installing Views (Dev) | 0.05
ok 12 - Installing Package Bodies (Dev) | 0.33
ok 13 - Recompiling APEX_190100 | 1.57
ok 14 - Creating APEX$ objects in APEX_190100 | 0.10
ok 15 - Creating Spatial Support | 0.03
ok 16 - Creating Instance Parameter Defaults | 0.00
ok 17 - Inherit Privileges | 0.02
ok 18 - Creating APEX_INSTANCE_ADMIN_USER | 0.00
ok 1 - 18 actions passed, 0 actions failed | 4.73
timing for: Creating APEX_INSTANCE_ADMIN_USER
Elapsed: 00:00:00.24
timing for: Phase 1 (Installation)
Elapsed: 00:04:52.05
Phase 2 (Upgrade)

timing for: Enabling Phase 2
Elapsed: 00:00:00.03
#
# Upgrade Metadata (1)
#
...reset_state_and_show_invalid.sql

timing for: Upgrade Metadata (1)
Elapsed: 00:01:20.06
#
# Upgrade Metadata (2)
#
-- Upgrading new schema. -------

timing for: Upgrade Metadata (2)
Elapsed: 00:00:00.46

Thank you for installing Oracle Application Express 19.1.0.00.15

Oracle Application Express is installed in the APEX_190100 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex/apex_admin (Oracle REST Data Services)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex (Oracle REST Data Services)

timing for: Phase 3 (Switch)
Elapsed: 00:01:19.64
timing for: Complete Installation
Elapsed: 00:12:31.76

PL/SQL procedure successfully completed.

SQL> @apex_epg_config.sql /u01/app/

PL/SQL procedure successfully completed.

timing for: Load Images
Elapsed: 00:04:00.66

PL/SQL procedure successfully completed.

Commit complete.

Launch apex url if you see any issues before digging more into issue clear cache from browser.

Friday, December 20, 2019

Thursday, December 19, 2019

Thursday, December 12, 2019

Database connection could not be established. Either the database is down or the APPS credentials supplied are wrong

Sunday, November 24, 2019

Wednesday, November 13, 2019

Monday, November 4, 2019

Friday, November 1, 2019

Thursday, October 31, 2019

Wednesday, October 30, 2019

Translate