Infrastructure & DevOps Guide [Oracle, AWS, Azure, Kubernaties]

Wednesday, November 30, 2011

Linux Find Out Which Process Is Listening Upon a Port

Find Out Which Process Is Listening Upon a Port

1. Find process id

[root@node1 ~]# ps -ef| grep tfa
root      28422      1 0 08:37 ?        00:00:00 /bin/sh /etc/init.d/init.tfa run
root     101416      1 92 08:43 ?        00:09:55 /opt/oracle.ahf/jre/bin/java -server -Xms512m -Xmx1024m -Djava.awt.headless=true -Ddisable.checkForUpdate=true -XX:HeapDumpPath=/depot/TFA_DATA/oracle.ahf/data/node1/diag/tfa -XX:ParallelGCThreads=5 oracle.rat.tfa.TFAMain /opt/oracle.ahf/tfa
root     235621 204091 0 08:53 pts/8    00:00:00 grep tfa
[root@node1 ~]# netstat -tulpn | grep 292505

2. command to find port:
netstat -tulpn

e.g
[root@node1 ~]# netstat -tulpn | grep 250110
tcp        0      0 127.0.0.1:34149             0.0.0.0:*                   LISTEN      250110/java
tcp        0      0 10.118.50.98:5000           0.0.0.0:*                   LISTEN      250110/java

Monday, May 23, 2011

Oracle EBS Debug is on

If you have Debug is on at Site level then it will Impact the performance of the Instance, here is the script which will help you to quickly check your EBS Instance if ‘Debug’ is on (all level) of the EBS.

set linesize 160
col USER_PROFILE_OPTION_NAME for a30
col CONTEXT for a30
col VALUE for a30
col NAME for a30

SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.user_profile_option_name like '%Debug%'
AND pov.profile_option_value='Y'
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value;

Tuesday, January 4, 2011

Oracle RMAN Backup and Restore

Convert Gigabits Per Second to Gigabytes Per Hour
http://www.kylesconverter.com/data-bandwidth/gigabits-per-second-to-gigabytes-per-hour

copy backup from ASM to FS and FS to ASM
http://select-star-from.blogspot.com/2013/08/how-to-copy-files-from-asm-to.html
https://dbaclass.com/article/copy-asm-file-from-one-server-to-another-server/

run {
BACKUP AS BACKUPSET SECTION SIZE 100G INCREMENTAL LEVEL 0 DATABASE FILESPERSET 1 TAG 'bkup_weekly_L0' plus ARCHIVELOG;
backup spfile format '/zfssa/proddb/backup1/spfile_%d_%s_%T_dbid%I.rman';
backup current controlfile format '/zfssa/proddb/backup1/Controlfile_%d_%T_dbid%I_s%s_p%p';
}

Tuesday, November 30, 2010

Oracle Database archive Alert Log

Hello DBAs,

Here is the simple script which can be used for clear archive log on every Sunday. I know we should make it smart but this is for easy setup :)

Steps 1: create a shell script to do this operation

#!/usr/bin/sh
# File name : alert_log_archive.sh
LOGFILE=/u01/oracle/12.1.1/diag/rdbms/dev/DEV/trace/alert_DEV.log
DDATE=`date +"%Y%m%d"`
cp $LOGFILE $LOGFILE.$DDATE
cat /dev/null > $LOGFILE
touch $LOGFILE

Steps 2: schedule it in cronjob

30 23 * * 0 sh /home/oracle/alert_log_archive.sh

Please put your suggestion in a comment.

Sunday, May 23, 2010

Oracle Database Archive Log Tables

This script will help you to query the oracle database and list how archive logs are generating.

Script name: archtab.sql

set line 120

set linesize 180

set pagesize 180

SELECT to_date(first_time) DAY,

to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",

to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",

to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",

to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",

to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",

to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",

to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",

to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",

to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",

to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",

to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",

to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",

to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",

to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",

to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",

to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",

to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",

to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",

to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",

to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",

to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",

to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",

to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",

to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"

from

v$log_history

where to_date(first_time) > sysdate - 8

GROUP by

to_char(first_time,'YYYY-MON-DD'), to_date(first_time)

order by to_date(first_time)

Saturday, May 23, 2009

Oracle Database tablespace Query

Check Tablespace size

scriptname : tbsn.sql

set heading on

set linesize 180

set pagesize 100

col TABLESPACE_NAME for a25

col bytes_gb for 99,999,999.999

col FREE_bytes_gb for 99,999,999.999

col ACTUAL_USED_MB for 99,999,999.999

col FILE_USED_PTC for a8

col MAXbytes_gb for 99,999,999.999

col BAL_MAX_EXT_MB for 99,999,999.999

col max_ext_used_pt for a8

col AUTO_EXTEND for a3

set feedback off

select name from v$database;

SELECT a.tablespace_name

, round(a.bytes_gb,3) bytes_gb

, round(b.free_bytes_gb,3) free_bytes_gb

, round((a.bytes_gb - b.free_bytes_gb),3) actual_used_mb

, round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) || '%' file_used_ptc

, round(a.maxbytes_gb,3) maxbytes_gb

, round((a.maxbytes_gb - (a.bytes_gb - b.free_bytes_gb)),3) bal_max_ext_mb

, round(((a.bytes_gb - b.free_bytes_gb)*100/a.maxbytes_gb),3) || '%' max_ext_used_pt

, decode(a.autoext,0,'No','Yes') auto_extend

FROM (SELECT dbf.tablespace_name

, SUM (dbf.bytes)/1024/1024/1024 bytes_gb

, SUM (decode(dbf.maxbytes,0,dbf.bytes,dbf.maxbytes))/1024/1024/1024 maxbytes_gb

, sum (dbf.maxbytes) autoext

FROM dba_data_files dbf

GROUP BY tablespace_name) a

, (SELECT tablespace_name

, SUM (BYTES)/1024/1024/1024 free_bytes_gb

FROM dba_free_space

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

-- and round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) > 85

order by round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) desc ;

set heading off

SELECT a.tablespace_name

, round(a.bytes_gb,3) bytes_gb

, round(b.free_bytes_gb,3) free_bytes_gb

, round((a.bytes_gb - b.free_bytes_gb),3) actual_used_mb

, round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) || '%' file_used_ptc

, round(a.maxbytes_gb,3) maxbytes_gb

, round((a.maxbytes_gb - (a.bytes_gb - b.free_bytes_gb)),3) bal_max_ext_mb

, round(((a.bytes_gb - b.free_bytes_gb)*100/a.maxbytes_gb),3) || '%' max_ext_used_pt

, decode(a.autoext,0,'No','Yes') auto_extend

FROM (SELECT dbf.tablespace_name

, SUM (dbf.bytes)/1024/1024/1024 bytes_gb

, SUM (decode(dbf.maxbytes,0,dbf.bytes,dbf.maxbytes))/1024/1024/1024 maxbytes_gb

, sum (dbf.maxbytes) autoext

FROM dba_temp_files dbf

GROUP BY tablespace_name) a

, (SELECT tablespace_name

, SUM (BYTES)/1024/1024/1024 free_bytes_gb

FROM dba_temp_files

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

-- and round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) > 85

order by round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) desc ;

==> OLD SQL

set linesize 120
set pagesize 100
col TABLESPACE_NAME for a25
col BYTES_MB for 99,999.999
col FREE_BYTES_MB for 99,999.999
col ACTUAL_USED_MB for 99,999.999
col FILE_USED_PTC for a8
col MAXBYTES_MB for 999,999.999
col BAL_MAX_EXT_MB for 999,999.999
col max_ext_used_pt for a8
col AUTO_EXTEND for a3
SELECT a.tablespace_name
, round(a.bytes_mb,3) bytes_mb
, round(b.free_bytes_mb,3) free_bytes_mb
, round((a.bytes_mb - b.free_bytes_mb),3) actual_used_mb
, round(((a.bytes_mb - b.free_bytes_mb)*100/a.bytes_mb),3) || '%' file_used_ptc
, round(a.maxbytes_mb,3) maxbytes_mb
, round((a.maxbytes_mb - (a.bytes_mb - b.free_bytes_mb)),3) bal_max_ext_mb
, round(((a.bytes_mb - b.free_bytes_mb)*100/a.maxbytes_mb),3) || '%' max_ext_used_pt
, decode(a.autoext,0,'No','Yes') Auto_extend
FROM (SELECT dbf.tablespace_name
, SUM (dbf.bytes)/1024/1024 bytes_mb
, SUM (decode(dbf.maxbytes,0,dbf.bytes,dbf.maxbytes))/1024/1024 maxbytes_mb
, sum (dbf.maxbytes) autoext
FROM dba_data_files dbf
GROUP BY tablespace_name) a
, (SELECT tablespace_name
, SUM (BYTES) / 1024 / 1024 free_bytes_mb
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
-- and round(((a.bytes_mb - b.free_bytes_mb)*100/a.bytes_mb),3) > 85
order by round(((a.bytes_mb - b.free_bytes_mb)*100/a.bytes_mb),3) desc ;

Get Datafile in Table Space

set linesize 120
col file_name for a60
select file_name,bytes/1024/1024 bytes,maxbytes/1024/1024 maxbytes,AUTOEXTENSIBLE from dba_data_files where tablespace_name = '&TB';

Temporary Tablespace

set linesize 120

col tablespace_name for a10

col file_name for a60

select tablespace_name,file_name,bytes/1024/1024 bytes,maxbytes/1024/1024 maxbytes,AUTOEXTENSIBLE from dba_temp_files order by 1;

Add datafile

alter tablespace APPS_UNDOTS1 add datafile '/db02/PROD/proddata/APPS_UNDOTS2.dbf' SIZE 1024M autoextend on NEXT 16M MAXSIZE 4096M;

Resize tablespace

ALTER DATABASE DATAFILE '/u02/oracle/PROD/db/apps_st/data/sysaux05.dbf' AUTOEXTEND ON NEXT 16M MAXSIZE 4096M;