Unlock Apex Admin User

select user_id from APEX_050000.WWV_FLOW_FND_USER where user_name = 'ADMIN' order by last_update_date desc;

update APEX_050000.WWV_FLOW_FND_USER
set web_password = 'pass'
where user_name = 'ADMIN'
and user_id = 1670116652066977;
commit;

alter session set current_schema = apex_050000;

begin
wwv_flow_security.g_security_group_id := 10;
wwv_flow_fnd_user_api.UNLOCK_ACCOUNT('ADMIN');
commit;
end;

OR

sqlplus / as sysdba @apxchpwd.sql

Default Profile - Unlimited Password

SELECT profile FROM dba_users WHERE username='APEX_PUBLIC_USER';

SELECT resource_name,limit FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Restart Failed Adpatch Session

Yesterday i had a problem when my ssh session failed during adpatch session.

How to restart failed adpatch session:

01. Source the env file as applmgr user and run adctrl

02. Select the following options

Opt 4: Tell manager that a worker has failed its job (specify all workers)
Opt 2: Tell worker to restart a failed job (specify all workers)

04. Restart the AD utility (adpatch) that was running when the machine crashed

Glassfish JAVA Memory Settings

To chnage Java Heap Memory in Glassfish4:

/opt/glassfish/glassfish4/glassfish/domains/domain1/config/domain.xml

change <jvm-options>-Xmx512m</jvm-options> to <jvm-options>-Xmx1024m</jvm-options> or more

Restart domain:
asadmin stop-domain
asadmin start-domain

Workflow mailer config&troubleshoot

A Guide For Troubleshooting Workflow Notification Emails - Inbound and Outbound (Doc ID 831982.1)

http://www.smarterp.com/blogs/app-and-database-administration-oracle-application-adminstration/r12-ebs-workflow

 http://onlineappsdba.com/index.php/2007/12/27/workflow-notification-mailer-setup-in-oracle-apps-r1212i/

How To Configure The Workflow Java Mailer Without An IMAP Account (Assumes No Inbound Processing) (Doc ID 268274.1)

https://cyclopsdba.wordpress.com/2012/06/09/workflow-notification-mailer/

For e-mail server use adress in the following format <server_name>:<port>

R12 Clone Corrupted inventory

Problem corrupted inventory + apps account locked during adcfgclone.pl

ERROR INSTALLING IAS PATCH IN EBS R12.1 ENVIRONMENT: COPYRETRY : Retrying copy, due to exception (Doc ID 1050564.1)

R12.0 / R12.1 : How To Create, Update or Rebuild The Central Inventory For Oracle Applications E-Business Suite ? (Doc ID 742477.1)

IAS Upgrade - OPMN Port Information is Unavailable (Doc ID 1545145.1)

R12: Clone fails with error "ORA-28000 The account is locked" (Doc ID 1114244.1)

FNDCPASS Troubleshooting Guide For Login and Changing Applications Passwords [ID 1306938.1] -- 2. Log In Fails With: You Don't Have Permission To Access /pls/.../fnd_icx_launch.launch On This Server

Session timeout R12

Session Timeout:

1. Change profile option ICX: Session Timeout to 60 min from 30, which is default.

2. change s_oc4j_sesstimeout value to 60 again in context file and run adconfig.

3. Bounce the apps services and check.

Autoinvoce Performance Problem R12 12.1.3+ RPC3

##Notes:

Troubleshooting AutoInvoice: Process Stops Working, No Errors in Log File (Doc ID 1294898.1)
AutoInvoice Error: Program exited with status 1 (Doc ID 1238437.1)
Troubleshooting AutoInvoice Performance Issues (Doc ID 1089196.1)
How To Setup AutoInvoice For Optimal Performance (Doc ID 1083467.1)

Setting Up System Profile Options For AutoInvoice [ID 1069294.1]
Autoinvoice Master Program Spawns Multiple Concurrent Autoinvoice Import Program [ID 1311477.1]

 Troubleshooting AutoInvoice: Master Program Issues including Errors, Warnings and When No Data Imports (Doc ID 1094863.1)

Troubleshooting Autoinvoice Import - Execution Report Errors (Request Status = Completed) [ID 1089172.1]


Check how log was AutoInvoice Running:

SELECT request_id,
TO_CHAR(request_date,'DD-MON-YYYY') "Date",
RPAD(user_concurrent_program_name,60) "Program Name",
RPAD(argument_text,20) "Arg",
RPAD(TO_CHAR(a.ACTUAL_START_DATE, 'hh24:mi:ss'),10) "Start",
RPAD(TO_CHAR(a.ACTUAL_COMPLETION_DATE, 'hh24:mi:ss'),10) "End",
RPAD(TO_CHAR( (TRUNC(SYSDATE) + ( a.ACTUAL_COMPLETION_DATE - a.ACTUAL_START_DATE)), 'hh24:mi:ss'),10) "Actual",
c.user_name,
status_code "S" FROM applsys.fnd_concurrent_requests a,
applsys.fnd_concurrent_programs_tl b,
apps.fnd_user c
WHERE b.user_concurrent_program_name like 'Autoinvoice Import Program'
AND a.concurrent_program_id = b.concurrent_program_id
AND a.request_date >= '01-Sep-2015'
AND a.requested_by = c.user_id ORDER BY request_id desc;

Frmweb runaway forms

OAM -> Site Map -> Monitoring tab -> Form Runtime Processes -> View Runaways

Note: 428262.1 - How to identify the form name attached to an f60webmx process

Note: 745711.1 - Forms Process (FRMWEB) Consumes 100% of CPU in Oracle Applications R12

Note: 206681.1 - How to Troubleshoot Spinning / Runaway Web Deployed Forms Runtime Processes?

AHL invalid objects - R12

Invalid objects encountered in R12 database after patch 12.1.3+ 3

AHL_CMP_UTIL_PKG
AHL_COMPLEX_MX_PVT
AHL_COMPLEX_MX_RWSC_PVT
AHL_OSP_RCV_PVT

Solution:

(N) Inventory>Set Up>Flexfields>Key>Segments.

Query for the Stock Locators in the Flexfield Title field.
Check the Freeze Flexfield Definition box.
Click on Compile

Ensure MTL_ITEM_LOCATIONS_KFV compiled successfully by clicking on View > Requests.

(N) Assets>Set Up>Flexfields>Key>Segments.
Query for the Flexfield Title - Location Flexfield
Check the Freeze Flexfield Definition box.
Click on Compile

Ensure FA_LOCATIONS_KFV compiled successfully by clicking on View > Requests.

Remove only directories in current directory

find . -depth -type d -exec rm -rf '{}' \;

R12 change profile option SQL

Check Profile option value

set serveroutput on
set echo on
set timing on
set feedback on
set long 10000
set linesize 120
set pagesize 132
column SHORT_NAME format A30
column NAME format A40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format A60 wrap
spool profile_options.txt
select p.profile_option_name SHORT_NAME, n.user_profile_option_name NAME,
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005, 'Server',
10007, 'SERVRESP', 'UnDef') LEVEL_SET,
decode(to_char(v.level_id), '10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) like upper('%&profile_name%')
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, level_set;

Change profile option value

DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('SITENAME', 'OSLIC' || '-' || sysdate, 'SITE');
IF stat THEN
dbms_output.put_line( 'Stat = TRUE - profile updated' );
ELSE
dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
END IF;
commit;
END;
/

Emulate http request eBS port

telnet 10.0.41.69 8030
Trying 10.0.41.69...
Connected to 10.0.41.69.
Escape character is '^]'.
GET /OA_HTML/OAInfo.jsp

Ping with timestamp

ping <ipadress> -t | while read pong; do echo "$(date): $pong"; done

Dropping a connected user from an Oracle 11g database schema

select sid,serial# from v$session where username = '<your_schema>'
alter system kill session '<sid>,<serial#>'

select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = '<your_schema>'

 ------

SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != 'BACKGROUND';

ALTER SYSTEM KILL SESSION '<put above s.sid here>,<put above s.serial# here>';

-----------

 DECLARE
 lc_username VARCHAR2 (32) := 'your user name here';
BEGIN
FOR ln_cur IN (SELECT sid, serial# FROM v$session WHERE username = lc_username)

LOOP 

    EXECUTE IMMEDIATE ('ALTER SYSTEM KILL SESSION ''' || ln_cur.sid || ',' || ln_cur.serial# || ''' IMMEDIATE'); 

END LOOP; 

END; /

--------------

Disconnect and drop user

DECLARE
  l_cnt integer;
BEGIN
  EXECUTE IMMEDIATE 'alter user <USERNAME> account lock';
  FOR x IN (SELECT *
              FROM v$session
             WHERE username = '<USERNAME>')
  LOOP
    EXECUTE IMMEDIATE 'alter system disconnect session ''' || x.sid || ',' || x.serial# || ''' IMMEDIATE';
  END LOOP;

  -- Wait for as long as it takes for all the sessions to go away
  LOOP
    SELECT COUNT(*)
      INTO l_cnt
      FROM v$session
     WHERE username = '<USERNAME>';
    EXIT WHEN l_cnt = 0;
    dbms_lock.sleep( 2 );
  END LOOP;

  EXECUTE IMMEDIATE 'drop user <USERNAME> cascade';
  EXECUTE IMMEDIATE 'CREATE USER <USERNAME> IDENTIFIED BY <USERNAME> DEFAULT TABLESPACE WCC_OCS TEMPORARY TABLESPACE WCC_OCS_TEMP ACCOUNT UNLOCK';  
  EXECUTE IMMEDIATE 'ALTER USER <USERNAME> QUOTA UNLIMITED ON WCC_OCS';
  EXECUTE IMMEDIATE 'GRANT "DBA", "CONNECT" TO <USERNAME>';
  EXECUTE IMMEDIATE 'ALTER USER <USERNAME> DEFAULT ROLE "DBA","CONNECT"'; 
  EXECUTE IMMEDIATE 'GRANT EXECUTE ON SYS.DBMS_LOCK TO <USERNAME>';
 
END;

CIFS share not mounting at boot

For some reason CIFS share entry in /etc/fstab is not mounting at boot. With mount -a command its mounting fine.

Reason: netfs service was disabled

Solution:

chmod 755 netfs

chkconfig netfs on

Resize Partition and Filesystem

Extend Partition and Filesystem
 
Unmount partition:

umount /<mountpoint>

Delete partition

fdisk /dev/xvdb
Command (m for help): d
Selected partition 1
Command (m for help): p
Command (m for help): w

Create a new (larger) partition

fdisk /dev/xvdb
Command (m for help): n
Command action

   e extended 

   p primary partition (1-4) 

p

Partition number (1-4): 1
Command (m for help): p
Command (m for help): w

Run fsck on your filesystem

e2fsck -f /dev/xvdb1

Resize your filesystem with resize2fs

resize2fs /dev/xvdb1

Re-mount extended partition

mount /<mountpoint>

Reduce a Partition and Filesystem

Unmount partition:

umount /<mountpoint>

Run fsck on your filesystem

e2fsck -f /dev/xvdb1

Resize your filesystem with resize2fs

resize2fs /dev/xvdb1

Delete partition

fdisk /dev/xvdb
Command (m for help): d
Selected partition 1
Command (m for help): p
Command (m for help): w

Create a new (smaller) partition

fdisk /dev/xvdb
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
Command (m for help): p
Command (m for help): w

Re-mount shrinked partition

mount /<mountpoint>

System Hold, Fix Manager before resetting counters

The actual value of the manager is 0.
ERROR
“System Hold, Fix Manager before resetting counters”.
Solution:
1. Stop all middle tier services including the concurrent managers.
Please make sure that no FNDLIBR, FNDSM, or any process is running by ps -ef | grep FNDLIBR
2. Stop the database.
3. Start the database.
4. Go to cd $FND_TOP/bin

adrelink.sh force=y link_debug=y "fnd FNDLIBR"
adrelink.sh force=y link_debug=y "fnd FNDFS"
adrelink.sh force=y link_debug=y "fnd FNDCRM"
adrelink.sh force=y link_debug=y "fnd FNDSM"

5. Run the CMCLEAN.SQL script from the referenced note below + commit!

Session monitoring

Active sessions usage by CPU

select
   ss.username,
   se.SID,
   VALUE/100 cpu_usage_seconds,
   ss.machine
from
   v$session ss,
   v$sesstat se,
   v$statname sn
where
   se.STATISTIC# = sn.STATISTIC#
and
   NAME like '%CPU used by this session%'
and
   se.SID = ss.SID
and
   ss.status='ACTIVE'
and
   ss.username is not null
order by VALUE desc

SELECT
   s.username,
   t.sid,
   s.serial#,
   SUM(VALUE/100) as "cpu usage (seconds)"
FROM
   v$session s,
   v$sesstat t,
   v$statname n
WHERE
   t.STATISTIC# = n.STATISTIC#
AND
   NAME like '%CPU used by this session%'
AND
   t.SID = s.SID
AND
   s.status='ACTIVE'
AND
   s.username is not null
GROUP BY username,t.sid,s.serial#

Top10 Active sessions

/* top10active.sql
shows the top 10 longest-active user sessions
*/
col osuser format a10 trunc
col LastCallET format 99,999
col sid format 9999
col spid formar 999999
col username format a10 trunc
col uprogram format a25 trunc
col machine format a10 trunc
set linesize 132
set verify off
select * from (
select to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.sid, s.status,
floor(last_call_et/60) "LastCallET",
s.username, s.osuser,
p.spid, s.module || ' - ' || s.program uprogram,
s.machine, s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
and s.type = 'USER'
and module is not null
and s.status = 'ACTIVE'
order by 4 desc)
where rownum < 11;

Session/SQL monitoring

SELECT SESION.SID,
       SESION.SERIAL#,
       SESION.USERNAME,
       OPTIMIZER_MODE,
       HASH_VALUE,
       ADDRESS,
       CPU_TIME/1000000000,
       ELAPSED_TIME/1000000000,
       DISK_READS,
       --DIRECT_WRITES,
       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
   AND ROWNUM < 100
   ORDER BY CPU_TIME, DISK_READS DESC,ELAPSED_TIME DESC;

Create Samba Share

## Install and configure samba shared folder ##

# install samba

yum install samba.x86_64

# Create samba user

smbpasswd -a username

# User that was used is from OS

# add shared folder to smb.cfg

vi /etc/samba/smb.conf

[folder]
path = /var/tmp/folder
available = yes
valid users = prodmgr
read only = no
browsable = yes
public = yes
writable = yes

# restart samba

service smb restart

Manipulate DB Links

create public database link mylink connect to remote_username identified by mypassword using 'tns_service_name';

create public database link mylink connect to remote_username identified by mypassword using 'myserver:1521/MYSID';

select * from dba_db_links;

drop database link <db_link>

FRA location/size query

SELECT
NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE,
'999,999,999,999') AS SPACE_AVAILABLE,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;

Copy existing VDI to specific SR

Show the disk you want to copy:

xe vdi-list name-label="VDI Label"
uuid ( RO) : 3f1c8982-78e8-4004-82de-7c8a961200e9
name-label ( RW): redologs
name-description ( RW):
sr-uuid ( RO): 632e5cac-45b6-0707-8d13-9bb47ab4f480
virtual-size ( RO): 10737418240
sharable ( RO): false
read-only ( RO): false

Copy disk to another SR

xe vdi-copy uuid=3f1c8982-78e8-4004-82de-7c8a961200e9 sr-uuid=<other SR UUID>

Reinstall Oracle EM 11g after DB clone

Remove files & folders

emctl stop dbconsole
rm -rf $ORACLE_HOME/<hostname>_<db_unique_name>
rm -rf $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_<hostname>_<db_unique_name>

Remove and reinitialize DB objects

DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/

DROP USER mgmt_view CASCADE;
DROP ROLE mgmt_user;
DROP USER sysman CASCADE;

alter user dbsnmp identified by <new dbsnmp password> ;

Create new configuration

emca -config dbcontrol db -repos create