Oracle DBA, 11g IDM, SSO, EBS Tips and Tricks

Tuesday, October 15, 2013

vi search and replace

vi search and replace between line 32 and 56.

:32,56s/guy/gal/g

Find and search text in unix

find . -exec grep -l "entropy" {} \; 2> /dev/null

alias rgrep='find . -type f -print | xargs grep $1'

find . -type f -name post -print | xargs grep DPassword

DELETE FROM PSAPMSGPUBHDR;
DELETE FROM PSAPMSGPUBDATA;
DELETE FROM PSAPMSGPUBCON;
DELETE FROM PSAPMSGSUBCON;
DELETE FROM PSAPMSGPUBERR;
DELETE FROM PSAPMSGPUBERRP;
DELETE FROM PSAPMSGPUBCERR;
DELETE FROM PSAPMSGPUBCERRP;
DELETE FROM PSAPMSGSUBCERR;
DELETE FROM PSAPMSGSUBCERRP;
DELETE FROM PSAPMSGPCONDATA;
DELETE FROM PSAPMSGSCONDATA;
DELETE FROM PSIBLOGHDR;
DELETE FROM PSIBLOGDATA;
DELETE FROM PSIBLOGERR;
DELETE FROM PSIBLOGERRP;
DELETE FROM PSAPMSGARCHPH;
DELETE FROM PSAPMSGARCHPD;
DELETE FROM PSAPMSGARCHPC;
DELETE FROM PSAPMSGARCHSC;
DELETE FROM PSAPMSGARCHPT;
DELETE FROM PSAPMSGARCHST;
DELETE FROM PSIBLOGHDRARCH;
DELETE FROM PSIBLOGDATAARCH;

Update sender and receiver node in following tables:
PSIBRTNGSUBDEFN
PSRTNGDFNPARM
PSIBRTNGDEFN

Update PSIBRTNGSUBDEFN set SENDERNODENAME='HRSBX' WHERE SENDERNODENAME='HRPRD';
update PSIBRTNGSUBDEFN set RECEIVERNODENAME='HRSBX' WHERE RECEIVERNODENAME='HRPRD';
update PSIBRTNGSUBDEFN set SENDERNODENAME='FNSBX' WHERE SENDERNODENAME='FNPRD';
update PSIBRTNGSUBDEFN set RECEIVERNODENAME='FNSBX' WHERE RECEIVERNODENAME='FNPRD';

update PSRTNGDFNPARM set SENDERNODENAME='HRSBX' WHERE SENDERNODENAME='HRPRD';
update PSRTNGDFNPARM set RECEIVERNODENAME='HRSBX' WHERE RECEIVERNODENAME='HRPRD';
update PSRTNGDFNPARM set SENDERNODENAME='FNSBX' WHERE SENDERNODENAME='FNPRD';
update PSRTNGDFNPARM set RECEIVERNODENAME='FNSBX' WHERE RECEIVERNODENAME='FNPRD';

update PSIBRTNGDEFN set SENDERNODENAME='HRSBX' WHERE SENDERNODENAME='HRPRD';
update PSIBRTNGDEFN set RECEIVERNODENAME='HRSBX' WHERE RECEIVERNODENAME='HRPRD';
update PSIBRTNGDEFN set SENDERNODENAME='FNSBX' WHERE SENDERNODENAME='FNPRD';
update PSIBRTNGDEFN set RECEIVERNODENAME='FNSBX' WHERE RECEIVERNODENAME='FNPRD';

Update node name:
PSMSGNODEDEFN

update PSMSGNODEDEFN set MSGNODENAME='HRSBX' WHERE MSGNODENAME='HRPRD';
update PSMSGNODEDEFN set MSGNODENAME='FNSBX' WHERE MSGNODENAME='FNPRD';

Update trusted node:
PSTRUSTNODES
update PSTRUSTNODES SET MSGNODENAME='HRSBX' WHERE MSGNODENAME='HRPRD';
update PSTRUSTNODES SET MSGNODENAME='FNSBX' WHERE MSGNODENAME='FNPRD';

Update IBPASSWORD:
update PSMSGNODEDEFN set IBPASSWORD='XYZ'
WHERE MSGNODENAME in ('HRSBX','FNSBX');

Few SQLs

Find blocking sessions
select 'alter system disconnect session '''sid','serial#'''immediate;' from V$SESSION where sid in (select sid from V$LOCK where block=1);

Find current Running SQLs
select sesion.sid,
sesion.username,
sesion.machine,
sesion.program,
sesion.serial#,
0ptimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text from v$sqlarea sqlarea, v$session sesion where sesion.sql_hash_value = sqlarea.hash_value and sesion.sql_address = sqlarea.address and sesion.username is not null

Finding DB Character set
select value$ from sys.props$ where name='NLS_CHARACTERSET';

STATs update
on a the entire schema:

EXEC DBMS_STATS.gather_schema_stats (ownname => 'SYSADM', cascade =>true,estimate_percent => dbms_stats.auto_sample_size);
on a single table:
EXEC DBMS_STATS.gather_table_stats ('SYSADM','PS_TABLE',cascade=>true)

Finding the remaining time for a long running query
select * from (
select opname, target, sofar, totalwork,
units, elapsed_seconds, message,elapsed_seconds*(totalwork-sofar)/sofar as time_rem
from v$session_longops
where sid = and serial# =
order by start_time desc)
where rownum <=1;

Finding RMAN backup status

selectSESSION_KEY, INPUT_TYPE, STATUS,to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,elapsed_seconds/3600 hrsfrom V$RMAN_BACKUP_JOB_DETAILSorder by session_key;

find out the impdp status of a table-how many rows got imported
select sum(bytes)/1048576
from dba_segments
where owner = 'SCOTT'
and segment_type = 'TEMPORARY'
and tablespace_name = TABSPACENAME

SGA usage
select round(sum(bytes)/1024/1024,2) total_sga,
round(sum(decode(name,'free memory',bytes,0))/1024/1024,2) free,
round((sum(decode(name,'free memory',bytes,0))/1024/1024)/(sum(bytes)/1024/1024)*100,2) free_perfromv$sgastat;

select * from ( select POOL, NAME, BYTES, BYTES/1048576 as MBytes from v$sgastat where pool='shared pool' order by BYTES desc ) where rownum <= 25;

Finding details on a peoplesoft process instance from DB

select * from PSPRCSQUE where PRCSINSTANCE=297037;
select * from PS_PRCSRQSTDIST where PRCSINSTANCE=297037;
select RUNSTATUS from PSPRCSRQST where PRCSINSTANCE=297037;

Finding total size of a Oracle DB
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB" from ( select sum(bytes)/1024/1024 data_size from dba_data_files ) a,( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) b,( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) c,( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) d;

Long Running Operations

SELECT s.sid,
s.serial#,
s.machine,
TRUNC(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
TRUNC(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;

Saturday, July 20, 2013

Linux : udev renamed network interface eth0 to eth1 on RHEL6 after clone

In some rare circumstances, your system may boot without network interface and you discover the following message in dmesg :

udev: renamed network interface eth0 to eth1

Since RHEL6, kudzu is no longer available since the kernel and udev take care of hardware changes. So, you need to modify using udev to get back you’re eth0 interface. Edit the following file “/etc/udev/rules.d/70-persistent-net.rules“.

Normally, you should have a double entry (eth0 and eth1) with different MAC address. This often happen when the OS is virtualized. Just delete the whole entry for eth1 and then rename eth1 to eth0 on the following entry and reboot. There is how the udev network interface entry look like :

# PCI device 0×8086:0x100f (e1000)SUBSYSTEM==”net”, ACTION==”add”, DRIVERS==”?*”, ATTR{address}==”00:50:56:34:0f:38″, ATTR{type}==”1″, KERNEL==”eth*”, NAME=”eth0“

Also need to make changes to network HWADDR under /etc/sysconfig/network-scripts/ifcfg-eth0

Monday, March 28, 2011

Oracle vs SQL Server SQLs for date formats

DESCRIPTION	ORACLE / SQL-SERVER SQL
Current system date	SELECT SYSDATE FROM DUAL
	SELECT GETDATE()
Current date as char	SELECT TO_CHAR(SYSDATE, 'MMDDYYYY') FROM DUAL **Change format mask as you want
	SELECT CONVERT(VARCHAR, GETDATE(), 112) **Use format mask as per below table 1 or 101 - US Foramt - Displays as MM/DD/YY or MM/DD/YYYY 2 or 102 - ANSI Foramt - Displays as YY.MM.DD or YYYY.MM.DD 3 or 103 - UK Format - Displays as DD/MM/YY or DD/MM/YYYY 10 or 110 - US Format - Displays as MM-DD-YY or MM-DD-YYYY 11 or 111 - Japan Format - Displays as YY/MM/DD or YYYY/MM/DD 12 or 112 - ISO Format - Displays as YYMMDD or YYYYMMDD
Current time as char	SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL **Change format mask as you want
	SELECT CONVERT(VARCHAR, GETDATE(), 108) **Use format mask as per below table 8 or 108 - Displays as hh:mi:ss(24h) 14 or 114 - Displays as hh:mi:ss:mmm(24h)
Current date-time as char	SELECT TO_CHAR(SYSDATE, 'MMDDYYYY HH24:MI:SS') FROM DUAL **Change format mask as you want
	SELECT CONVERT(VARCHAR, GETDATE(), 113) **Use format mask as per below table 13 or 113 - Displays as dd mon yyyy hh:mi:ss:mmm(24h) 20 or 120 - Displays as yyyy-mm-dd hh:mi:ss(24h) 21 or 121 - Displays as yyyy-mm-dd hh:mi:ss.mmm(24h)
Convert char to date	SELECT TO_DATE('03232011','MMDDYYYY') FROM DUAL
	SELECT CONVERT(DATE, '20110323', 112) --Use mask as per format
Last day of current month	SELECT LAST_DAY(SYSDATE) FROM DUAL
	SELECT DATEADD(S,-1,DATEADD(M, DATEDIFF(M,0,GETDATE())+1,0))
Last day of next month	SELECT LAST_DAY(ADD_MONTHS(SYSDATE, 1)) FROM DUAL
	SELECT DATEADD(S,-1,DATEADD(M, DATEDIFF(M,0,GETDATE())+2,0))
Last day of previous month	SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1)) FROM DUAL
	SELECT DATEADD(S,-1,DATEADD(M, DATEDIFF(M,0,GETDATE()),0))
Add a day	SELECT SYSDATE + 1 FROM DUAL
	SELECT DATEADD(D, 1, GETDATE())
Subtract a day	SELECT SYSDATE - 1 FROM DUAL
	SELECT DATEADD(D, -1, GETDATE())
Add a year	SELECT ADD_MONTHS(SYSDATE, 12) FROM DUAL
	SELECT DATEADD(YY, 1, GETDATE())
Subtract a year	SELECT ADD_MONTHS(SYSDATE, -12) FROM DUAL
	SELECT DATEADD(YY, -1, GETDATE())

Wednesday, March 23, 2011

Starting/Stoping Oracle 11g Identity management stack.

Starting/Stoping Oracle 11g Identity management stack.:

Starting IDM Admin server:

MW_HOME/user_projects/domains/domain_name/startWebLogic.sh

Starting IDM wls_ods1 managed server:
MW_HOME/user_projects/domains/domain_name/startWebLogic.sh wls_ods1

Starting Oracle Internet Directory and Oracle Virtual Directory:
ORACLE_INSTANCE/bin/opmnctl startall

You can verify that the system components have started by:
ORACLE_INSTANCE/bin/opmnctl status -l

Starting IAM Admin server:
MW_HOME/user_projects/domains/domain_name/startWebLogic.sh

Starting OAM, SOA and OIM managed servers:
MW_HOME/user_projects/domains/domain_name/bin/startManagedWebLogic.sh oam_server1
MW_HOME/user_projects/domains/domain_name/bin/startManagedWebLogic.sh soa_server1

Always start SOA managed server, before OIM. Although both independent, but OIM needs some of SOA workflow functionality.

MW_HOME/user_projects/domains/domain_name/bin/startManagedWebLogic.sh oim_server1

Stoping Oracle 11g Identity management stack:

StoppingWebLogic managed component wls_ods1:
MW_HOME/user_projects/domains/DOMAIN_NAME/bin/stopManagedWebLogic.sh wls_ods1

Stopping Oracle Internet Directory and Oracle Virtual Directory:
ORACLE_INSTANCE/bin/opmnctl stopall

Stopping the Oracle WebLogic IDM Admin Server:
MW_HOME/user_projects/domains/DOMAIN_NAME/bin/stopWebLogic.sh

Stopping WebLogic managed component oim_server1,soa_server1 and oam_server1
MW_HOME/user_projects/domains/DOMAIN_NAME/bin/stopManagedWebLogic.sh oim_server1
MW_HOME/user_projects/domains/DOMAIN_NAME/bin/stopManagedWebLogic.sh soa_server1
MW_HOME/user_projects/domains/DOMAIN_NAME/bin/stopManagedWebLogic.sh oam_server1

Stopping Oracle Internet Directory and Oracle Virtual Directory:
ORACLE_INSTANCE/bin/opmnctl stopall

Stopping the Oracle WebLogic IAM Admin Server:
MW_HOME/user_projects/domains/DOMAIN_NAME/bin/stopWebLogic.sh

Tuesday, October 15, 2013

Saturday, July 20, 2013

Linux : udev renamed network interface eth0 to eth1 on RHEL6 after clone

Monday, March 28, 2011

Current system date

SELECT SYSDATE FROM DUAL

SELECT GETDATE()

Current date as char

SELECT TO_CHAR(SYSDATE, 'MMDDYYYY') FROM DUAL

**Change format mask as you want

SELECT CONVERT(VARCHAR, GETDATE(), 112)

**Use format mask as per below table

1 or 101 - US Foramt - Displays as MM/DD/YY or MM/DD/YYYY

2 or 102 - ANSI Foramt - Displays as YY.MM.DD or YYYY.MM.DD

3 or 103 - UK Format - Displays as DD/MM/YY or DD/MM/YYYY

10 or 110 - US Format - Displays as MM-DD-YY or MM-DD-YYYY

11 or 111 - Japan Format - Displays as YY/MM/DD or YYYY/MM/DD

12 or 112 - ISO Format - Displays as YYMMDD or YYYYMMDD

Current time as char

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL

**Change format mask as you want

SELECT CONVERT(VARCHAR, GETDATE(), 108)

**Use format mask as per below table

8 or 108 - Displays as hh:mi:ss(24h)

14 or 114 - Displays as hh:mi:ss:mmm(24h)

Current date-time as char

SELECT TO_CHAR(SYSDATE, 'MMDDYYYY HH24:MI:SS') FROM DUAL

**Change format mask as you want

SELECT CONVERT(VARCHAR, GETDATE(), 113)

**Use format mask as per below table

13 or 113 - Displays as dd mon yyyy hh:mi:ss:mmm(24h)

20 or 120 - Displays as yyyy-mm-dd hh:mi:ss(24h)

21 or 121 - Displays as yyyy-mm-dd hh:mi:ss.mmm(24h)

Convert char to date

SELECT TO_DATE('03232011','MMDDYYYY') FROM DUAL

SELECT CONVERT(DATE, '20110323', 112) --Use mask as per format

Last day of current month

SELECT LAST_DAY(SYSDATE) FROM DUAL

SELECT DATEADD(S,-1,DATEADD(M, DATEDIFF(M,0,GETDATE())+1,0))

Last day of next month

SELECT LAST_DAY(ADD_MONTHS(SYSDATE, 1)) FROM DUAL

SELECT DATEADD(S,-1,DATEADD(M, DATEDIFF(M,0,GETDATE())+2,0))

Last day of previous month

SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1)) FROM DUAL

SELECT DATEADD(S,-1,DATEADD(M, DATEDIFF(M,0,GETDATE()),0))

Add a day

SELECT SYSDATE + 1 FROM DUAL

SELECT DATEADD(D, 1, GETDATE())

Subtract a day

SELECT SYSDATE - 1 FROM DUAL

SELECT DATEADD(D, -1, GETDATE())

Add a year

SELECT ADD_MONTHS(SYSDATE, 12) FROM DUAL

SELECT DATEADD(YY, 1, GETDATE())

Subtract a year

SELECT ADD_MONTHS(SYSDATE, -12) FROM DUAL

SELECT DATEADD(YY, -1, GETDATE())

Wednesday, March 23, 2011