Archive
TNSPing & SQLPlus just hang without errors
Usually, when you connect to Oracle, you get errors that give you some feedback on what is happening.
Today, I got an issue where when trying to connect to SQLplus or even running a tnsping command was hanging. Not getting any error to start the troubleshooting. The issue was definitely some sort of connectivity but not able to point it out
In our case, we use “nameserver” in addition to tnsnames.ora. Our sqlnet.ora file looks like this:
NAMES.DIRECTORY_PATH= (ONAMES,TNSNAMES)
I needed to trace my “tnsping” command to see where it is getting hung.
To troubleshoot the issue with tnsping hanging, all you need to do is add these settings in sqlnet.ora to trace tnsping
TNSPING.TRACE_LEVEL = ADMIN
TNSPING.TRACE_DIRECTORY =/d01/abc/product/8.0.6/network/admin
My being a linux box and hence the path. You may need to modify according to your OS and directory
I ran the “tnsping” to the same Oracle SID again, a trace file “tnsping.trc” got generated in the path defined in the above “TNSPING.TRACE_DIRECTORY” variable.
Careful review of the trace file revealed that he connection was having an issue with the “name server” defined in my sqlnet.ora file.
I asked the Oracle DBA to confirm if the “name server” is started and she confirmed that it is not. Once she started the “name server”, tnsping command went successfully and I was able to connect to SQLplus.
Hope this helps you in some way.
Happy learning!!!
-Anand M
Decrypt weblogic admin password
Pls follow below steps to decrypt Weblogic admin password
Step 1:- Create a file called – decryptPass.py and udpate the file with below cotents
import os import weblogic.security.internal.SerializedSystemIni import weblogic.security.internal.encryption.ClearOrEncryptedService def decrypt(domainHomeName, encryptedPwd): domainHomeAbsolutePath = os.path.abspath(domainHomeName) encryptionService = weblogic.security.internal.SerializedSystemIni.getEncryptionService(domainHomeAbsolutePath) ces = weblogic.security.internal.encryption.ClearOrEncryptedService(encryptionService) clear = ces.decrypt(encryptedPwd) print "RESULT:" + clear try: if len(sys.argv) == 3: decrypt(sys.argv[1], sys.argv[2]) else: print "INVALID ARGUMENTS" print " Usage: java weblogic.WLST decryptPassword.py DOMAIN_HOME ENCRYPTED_PASSWORD" print " Example:" print " java weblogic.WLST decryptPassword.py D:/Oracle/Middleware/user_projects/domains/base_domain {AES}819R5h3JUS9fAcPmF58p9Wb3syTJxFl0t8NInD/ykkE=" except: print "Unexpected error: ", sys.exc_info()[0] dumpStack() raise
Step 2:- Set Domain environment variable
cd $FMW_HOME/user_projects/domains/<domain_name>
. setDomainEnv.sh
Once it is properly set, do echo $DOMAIN_HOME and you will find it getting properly displayed
Step 3:- Get encrypted password value from boot.properties file
$ grep password $DOMAIN_HOME/servers/AdminServer/security/boot.properties | sed -e "s/^password=\(.*\)/\1/" {AES}udb6nZLDw24HiRRrZkojuoiLNiu/MfAIZpcU=
Step 4:- Decrypt the encrypted password obtained in Step 3 (Run the command from the location where the decryptPass.py is kept)
java weblogic.WLST decryptPass.py $DOMAIN_HOME {AES}udb6nZLDw24HiRRrZkojuoiLNiu/MfAIZpcU= Initializing WebLogic Scripting Tool (WLST) ... Welcome to WebLogic Server Administration Scripting Shell Type help() for help on available commands RESULT:r41cOWqpc
Hope this helps. Happy reading!
-Anand M
PGP key generation using gpg 1.4.5 on Linux
Step 1 – Confirm GPG version
$gpg -help gpg (GnuPG) 1.4.5 Copyright (C) 2006 Free Software Foundation, Inc. This program comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to redistribute it under certain conditions. See the file COPYING for details.
Step 2 – Start generating gpg key
$ gpg --gen-key gpg (GnuPG) 1.4.5; Copyright (C) 2006 Free Software Foundation, Inc. This program comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to redistribute it under certain conditions. See the file COPYING for details. Please select what kind of key you want: (1) DSA and Elgamal (default) (2) DSA (sign only) (5) RSA (sign only) Your selection? 5 RSA keys may be between 1024 and 4096 bits long. What keysize do you want? (2048) Requested keysize is 2048 bits Please specify how long the key should be valid. 0 = key does not expire <n> = key expires in n days <n>w = key expires in n weeks <n>m = key expires in n months <n>y = key expires in n years Key is valid for? (0) Key does not expire at all Is this correct? (y/N) y You need a user ID to identify your key; the software constructs the user ID from the Real Name, Comment and Email Address in this form: "Heinrich Heine (Der Dichter) <heinrichh@duesseldorf.de>" Real name: <User ID for which the key is being generated> Email address: <Valid mail ID> Comment: You selected this USER-ID: "<User ID provided earlier> <Mail ID>" Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O You need a Passphrase to protect your secret key. You don't want a passphrase - this is probably a *bad* idea! I will do it anyway. You can change your passphrase at any time, using this program with the option "--edit-key". We need to generate a lot of random bytes. It is a good idea to perform some other action (type on the keyboard, move the mouse, utilize the disks) during the prime generation; this gives the random number generator a better chance to gain enough entropy. Not enough random bytes available. Please do some other work to give the OS a chance to collect more entropy! (Need 276 more bytes) ....+++++ ..+++++ gpg: key 193EAC92 marked as ultimately trusted public and secret key created and signed. gpg: checking the trustdb gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model gpg: depth: 0 valid: 1 signed: 0 trust: 0-, 0q, 0n, 0m, 0f, 1u pub 2048R/193EAC92 2016-11-15 Key fingerprint = F7B1 F82D 8DA3 850B 5F8A 5905 B93D 5AF3 193E AC92 uid <User ID provided earlier> <Mail ID> Note that this key cannot be used for encryption. You may want to use the command "--edit-key" to generate a subkey for this purpose.
Step 3 – List your keys
$ gpg -k /home/.gnupg/pubring.gpg ----------------------------------------- pub 2048R/193EAC92 2016-11-15 uid <User ID provided earlier> <Mail ID>
Step 4 – Export the public key in ASCII format
$ gpg --armor --output <User ID>-pub.asc --export '<User ID>' $ $ ls -ltr -rw-rw-r-- 1 xxx xxxyy 979 Nov 15 09:28 -pub.asc $ cat <User ID>-pub.asc -----BEGIN PGP PUBLIC KEY BLOCK----- Version: GnuPG v1.4.5 (GNU/Linux) mQENBFgrKJYBCADMiFYMrVbhl7HH/WLPqPN5SSSaxv5go92m9LjcLysU0Vd9+JTi hck+8zy3wutY4Q6QK61Zlx72J/Va+4hcv8tMKaJjWfhEGbXV54yKNjNoSM20BtuO AC5+Z4/2UWA3xh3S1JPRRiV3KDO0MTM2JdhH9sufkhpBsLdJd+jFq+X8o633hqAk xK75ihNYyof0Pi8VqGJdmaEwgzy1/bXIYeH8wbivC7yx6Kg84oF5Znii50tP6Grq JygEcnyVjrfS6frsnO4uIBQEuFFoqLGHBohWKQqwkRU6Sd6KTIFkdN6E/SE1XelF EDMURzDczxsQYaNH1A5QRLNxAC2Zm+uCt76bABEBAAG0NHN2Y19Xb3JrZGF5X3Nm dHAgPGFuYW5kLm1hbmRpbHdhci1zY0BhaXJsaXF1aWRlLmNvbT6JATYEEwECACAF AlgrKJYCGwMGCwkIBwMCBBUCCAMEFgIDAQIeAQIXgAAKCRC5PVrzGT6skkMmB/4m SxJJ/cgPBcMArpUpsargl/g4SOVuH1RKIBi+Sjqt1n7ioW3zfceUxEz0u3t76KaN 4+ndKrCxxCokL8xOu0Qgq1tQtdl5MiONwJ7hvxa459U7qjDE9joj/4WcnxZVIYhj 5+ZPxdR+dfiFmJDyIkln1wUnB8RmAov/hzNCvx50nmLJUIVM6C+e3VWyDqDx66Uh vHUPpLY+wk/d+qyHeldk4nqa5z+A6Oh3ZB1qcc9h9mEd3tDHQLZiaiqXst0pFQB+ 3X9QiSektAXrKSIuOf7Bky4DjDfpoCWuSDDpTaF1IIUTv3YrosbyRamAJ3fAu39x ltW8BKpKP3nJWKmWmw7i =EkgV -----END PGP PUBLIC KEY BLOCK----- $
How to copy PGP public and private key from one machine (Linux here) to other Linux.
Step# 1: Export private secret key on the source machine
$ gpg --export-secret-keys -a <key_ID> > myfilename_private_key.asc
key_ID – when you list the key using “gpg -k”
pub 4096R/AD761536 2017-03-29
uid <User_Name> <username@domain.com>
sub 4096R/B045ADCF 2017-03-29
AD761536 – this is the key_ID
Step# 2: Export public secret key
$ gpg --export -a <key_ID> > myfilename_public_key.asc
Step # 3: SCP these 2 files to the target server – where you want to copy the PGP keys (in my case it is other Linux server)
Step # 4: Import the private and public key copied in Step# 3 on the target machine.
$ gpg --import myfilename_private_key.asc gpg: key ADC61536: secret key imported gpg: key ADC61536: public key "<User_name> <username@domain.com>" imported gpg: Total number processed: 1 gpg: imported: 1 (RSA: 1) gpg: secret keys read: 1 gpg: secret keys imported: 1 $ gpg --import myfilename_public_key.asc <-- this is for public key gpg: key ADC61536: "<User_name> <username@domain.com>" not changed gpg: Total number processed: 1 gpg: unchanged: 1
Step # 5: Change the trust level
Now you need to change the “trust” level of the private key thus imported to new server
$ gpg --edit-key username@domain.com gpg (GnuPG) 1.4.18; Copyright (C) 2014 Free Software Foundation, Inc. This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. Secret key is available. pub 4096R/ADC61536 created: 2017-03-29 expires: never usage: SC trust: unknown validity: unknown sub 4096R/B044ADFF created: 2017-03-29 expires: never usage: E [ unknown] (1). <User_name> <username@domain.com> gpg> trust pub 4096R/ADC61536 created: 2017-03-29 expires: never usage: SC trust: unknown validity: unknown sub 4096R/B033456FF created: 2017-03-29 expires: never usage: E [ unknown] (1). <User_name> <username@domain.com> Please decide how far you trust this user to correctly verify other users' keys (by looking at passports, checking fingerprints from different sources, etc.) 1 = I don't know or won't say 2 = I do NOT trust 3 = I trust marginally 4 = I trust fully 5 = I trust ultimately m = back to the main menu Your decision? 5 Do you really want to set this key to ultimate trust? (y/N) y pub 4096R/ADC61536 created: 2017-03-29 expires: never usage: SC trust: ultimate validity: unknown sub 4096R/B033456FF created: 2017-03-29 expires: never usage: E [ unknown] (1). <User_name> <username@domain.com> Please note that the shown key validity is not necessarily correct unless you restart the program. gpg> quit
Now if you list the keys using “gpg -k”, you will find the PGP key ID and associated details are exactly the same as that of the source server.
Happy reading.
-Anand M
Collection of useful script for Oracle Apps DBA
Below are some of the most useful script for any Oracle APPS DBA. These are handy for day to day monitoring and troubleshooting activity.
Hope this helps.
Script to monitor Tablespace Growth
- This probes the dba_hist_tbspc_space_usage table and gets the data as old as the retention time of AWR snap
- Starting Oracle 10G, Oracle records tablespaces usage (allocated, used etc.) in AWR which can be retrieved by querying the data dictionary view dba_hist_tbspc_space_usage
- This script is based on AWR
- If your AWR retention period is 7 days, this script can only tell the growth history of last 7 days and predict based on last 7 days growth
with a as ( select name,ts#,block_size from v$tablespace,dba_tablespaces where name = tablespace_name ), c as ( select a.name,min(snap_id) Begin_snap_ID, max(snap_id) End_Snap_ID, min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))) begin_time, max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))) End_time from dba_hist_tbspc_space_usage,a where tablespace_id= a.ts# group by a.name ), d as ( select a.name, round((dh.tablespace_size* A.BLOCK_SIZE)/1024/1024,2) begin_allocated_space, round((dh.tablespace_usedsize * A.BLOCK_SIZE)/1024/1024,2) begin_Used_space from dba_hist_tbspc_space_usage dh,c,a --,b where dh.snap_id = c.Begin_snap_ID and a.ts# = dh.tablespace_id and a.name = c.name ), e as ( select a.name, round((tablespace_size*a.block_size)/1024/1024,2) End_allocated_space, round((tablespace_usedsize*a.block_size)/1024/1024,2) End_Used_space from dba_hist_tbspc_space_usage,c ,a where snap_id = c.End_Snap_ID and a.ts# = dba_hist_tbspc_space_usage.tablespace_id and a.name = c.name ) select e.name,to_char(c.begin_time,'DD-MON-YYYY') Begin_time,d.begin_allocated_space "Begin_allocated_space(MB)", d.begin_Used_space "Begin_Used_space(MB)", to_char(c.End_time,'DD-MON-YYYY') End_Time, e.End_allocated_space "End_allocated_space(MB)", e.End_Used_space "End_Used_space(MB)", (e.End_Used_space - d.begin_Used_space)"Total Growth(MB)", (c.End_time - c.begin_time)"No.of days", round(((e.End_Used_space - d.begin_Used_space)/(c.End_time - c.begin_time))*30,2) "Growth(MB)_in_next30_days", round(((e.End_Used_space - d.begin_Used_space)/(c.End_time - c.begin_time))*60,2) "Growth(MB)_in_next60_days", round(((e.End_Used_space - d.begin_Used_space)/(c.End_time - c.begin_time))*90,2) "Growth(MB)_in_next90_days" from e,d,c where e.name = d.name and e.name = c.name and (e.End_Used_space - d.begin_Used_space) > 0 order by 1
Script to monitor Tablespace Usage
select a.name,a.gbytes "Allocated", a.MAX_SPACE_GB " Max Space(GB)", a.used_GB, a.Free_GB, a.pct_used_1 "%age Used" from ( SELECT NVL(b.tablespace_name, NVL(a.tablespace_name, 'UNKOWN')) name, Gbytes_alloc Gbytes, Gbytes_alloc - NVL(Gbytes_free, 0) used_GB, MAX_SPACE_GB, NVL(Gbytes_free, 0) free_GB, ROUND(((Gbytes_alloc - NVL(Gbytes_free, 0)) / Gbytes_alloc) * 100, 2) pct_used, ROUND(((Gbytes_alloc - NVL(Gbytes_free, 0)) / MAX_SPACE_GB) * 100, 2) pct_used_1, NVL(largest_GB, 0) "largest(GB)" FROM (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) Gbytes_free, ROUND(MAX(bytes) / 1024 / 1024 / 1024, 2) largest_GB, tablespace_name FROM sys.dba_free_space GROUP BY tablespace_name) a, (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) Gbytes_alloc, tablespace_name FROM sys.dba_data_files GROUP BY tablespace_name) b, (select b.tablespace_name,sum(greatest(b.bytes / (1024 * 1024 * 1024), b.maxbytes / (1024 * 1024 * 1024))) "MAX_SPACE_GB" from dba_data_files b group by b.tablespace_name)c WHERE a.tablespace_name(+) = b.tablespace_name and b.tablespace_name = c.tablespace_name ORDER BY 6 desc )a order by 6 desc
Script to monitor major database wait events
set echo off set pages 1000 set lines 120 col inst format 9999 col sid format 9999 col event format a29 trunc col program format a20 trunc col module format a20 col username format A11 col secs format 99999 select w.inst_id inst, w.sid, w.event,s.module,s.username,w.p1,w.p2,w.p3,w.seconds_in_wait Secs from gv$session_wait w, gv$session s where w.inst_id = s.inst_id and w.sid=s.sid and w.state='WAITING' and w.event not in ('pmon timer', 'smon timer', 'rdbms ipc message', 'pipe get', 'SQL*Net message from client', 'SQL*Net message to client', 'SQL*Net break/reset to client', 'SQL*Net more data from client', 'wakeup time manager', 'slave wait', 'SQL*Net more data to client') and (w.event not like '%slave wait' and w.event not like 'EMON slave idle wait%' and w.event not like 'Streams AQ: waiting for%' and w.event not like 'Space Manager: slave idle wai%' and w.event not like 'Streams AQ: emn coordinator%' and w.event not like 'VKRM%' and w.event not like 'Streams AQ%') group by w.inst_id, w.sid,w.event,s.module,s.username,w.p1,w.p2,w.p3,w.seconds_in_wait order by 1,3;
Query (dbcheck.sql) to check how database is performing at a given point of time
set echo off set feedback off set verify off set lines 500 set pages 1000 column event format a30 column module format a35 column sql_id format a15 SELECT event, module, sql_id, COUNT(*) FROM v$session WHERE event NOT IN ('SQL*Net message from client', 'Streams AQ: waiting for time management or cleanup tasks', 'Streams AQ: qmn slave idle wait', 'Streams AQ: qmn coordinator idle wait', 'Streams AQ: emn coordinator idle wait', 'DIAG idle wait', 'SQL*Net message to client', 'pmon timer', 'smon timer', 'VKTM Logical Idle Wait', 'JOX Jit Process Sleep', 'PL/SQL lock timer', 'Streams AQ: waiting for messages in the queue', 'EMON slave idle wait', 'rdbms ipc message', 'PX Deq: Execution Msg', 'Streams AQ: waiting FOR messages IN the queue', 'rdbms ipc MESSAGE', 'Space Manager: slave idle wait', 'pipe get', 'PL/SQL LOCK timer', 'SQL*Net more data to client', 'SQL*Net break/reset to client') GROUP BY event, module, sql_id ORDER BY COUNT(*) DESC / set feedback on set echo on set verify on
Query to know the specific user session detail
- Input – Session ID
select ses.action, ses.status, ses.module, substr(ses.program, 1, instr(ses.program, ' ') - 1) PROGRAM, proc.spid, ses.process, ses.sid, ses.serial#, to_char(ses.logon_time, 'DD-MON-RR HH24:MI:SS') CONNECT_TIME from v$session ses, v$process proc where ses.paddr = proc.addr and ses.sid = &sid
Script to look for all the concurrent jobs currently running in the database.
select q.concurrent_queue_name qname, f.user_name, a.request_id "Req Id", decode(a.parent_request_id, -1, NULL, a.parent_request_id) "Parent", a.concurrent_program_id "Prg Id", a.phase_code, a.status_code, (nvl(a.actual_completion_date, sysdate) - a.actual_start_date) * 1440 "Time", c.concurrent_program_name || ' - ' || a.program "Program" from APPS.fnd_conc_req_summary_v a, APPLSYS.fnd_concurrent_processes b, applsys.fnd_concurrent_queues q, APPLSYS.fnd_concurrent_programs_tl c2, APPLSYS.fnd_concurrent_programs c, APPLSYS.fnd_user f where a.controlling_manager = b.concurrent_process_id and a.concurrent_program_id = c.concurrent_program_id and a.program_application_id = c.application_id and c2.concurrent_program_id = c.concurrent_program_id and c2.application_id = c.application_id and a.phase_code in ('I', 'P', 'R', 'T') and a.requested_by = f.user_id and b.queue_application_id = q.application_id and b.concurrent_queue_id = q.concurrent_queue_id and c2.language = 'US' and a.hold_flag = 'N' order by 1, 3;
Query to look for all the concurrent jobs currently running in the database in a specific manager
- Input – Queue name
set echo off set heading on set lines 1000 set pagesize 1000 col spid form a6 head SPID col program form A60 trunc col time form 99999.99 head Elapsed col "Req Id" form 9999999999 col "Parent" form a9 col "Prg Id" form 9999999col qname head "Manager" format a20 trunc col user_name form A12 head User trunc set recsep off select q.concurrent_queue_name qname, f.user_name, a.request_id "Req Id", decode(a.parent_request_id, -1, NULL, a.parent_request_id) "Parent", a.concurrent_program_id "Prg Id", a.phase_code, a.status_code, (nvl(a.actual_completion_date, sysdate) - a.actual_start_date) * 1440 "Time", c.concurrent_program_name || ' - ' || a.program "Program" from APPS.fnd_conc_req_summary_v a, APPLSYS.fnd_concurrent_processes b, applsys.fnd_concurrent_queues q, APPLSYS.fnd_concurrent_programs_tl c2, APPLSYS.fnd_concurrent_programs c, APPLSYS.fnd_user f where a.controlling_manager = b.concurrent_process_id and a.concurrent_program_id = c.concurrent_program_id and a.program_application_id = c.application_id and c2.concurrent_program_id = c.concurrent_program_id and c2.application_id = c.application_id and a.phase_code in ('I', 'P', 'R', 'T') and a.requested_by = f.user_id and b.queue_application_id = q.application_id and b.concurrent_queue_id = q.concurrent_queue_id and c2.language = 'US' and a.hold_flag = 'N' and q.concurrent_queue_name = '&qeueue_name' order by 1, 3 set echo on
Query to find the concurrent program ID for any concurrent program(findprog.sql)
- Input – Concurrent Program (Wild character will also do)
set echo off set line 132 set feed off set define on set serveroutput on set timing off set pagesize 1000 set heading off undefine Concurrent_Program_Name1 accept Concurrent_Program_Name1 prompt 'Concurrent_Program_Name: ' exec dbms_output.put_line('*************************************'); exec dbms_output.put_line('Displaying program details...'); exec dbms_output.put_line('*************************************'); select '--------------------------------------------------------'||chr(10) from dual union select 'Prog Name: ' || fcpt.user_concurrent_program_name || chr(10) || chr(9) || 'Conc Prog Id: ' || fcpt.concurrent_program_id || chr(10) || chr(9) || 'Short Name: ' || fcp.concurrent_program_name || chr(10) || chr(9) || 'Application: ' || fat.application_name || chr(10) || '--------------------------------------------------------' Details from apps.fnd_concurrent_programs_tl fcpt, apps.fnd_concurrent_programs fcp, apps.fnd_application_tl fat where upper(fcpt.user_concurrent_program_name) like upper('&Concurrent_Program_Name1') and fcpt.concurrent_program_id = fcp.concurrent_program_id and fcpt.application_id = fcp.application_id and fcpt.application_id = fat.application_id order by 1 / set timing on set heading on
Query to find the history run statistics of any specific program
- Input – Concurrent Program ID
set echo off clear column set lines 500 set feedback off set verify off accept program_id prompt 'Enter Conc Prog ID :' column request_id format 999999999 column username format a15 column name format a40 column argument_text format a30 column actual_start_date format a10 column USER_CONCURRENT_PROGRAM_NAME format a30 select r.request_id Request, f.user_name UserName, r.status_code, r.phase_code, to_char(r.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') Run_date, round((r.actual_completion_date - r.actual_start_date) * 1440, 4) Elapsed, r.argument_text "Program Parameters" from apps.fnd_concurrent_requests r, apps.fnd_user f where r.concurrent_program_id = &&program_id and r.requested_by = f.user_id order by r.actual_completion_date desc / select r.concurrent_program_id Id, p.user_concurrent_program_name Name, trunc(r.actual_start_date) Start_date, round(avg((r.actual_completion_date - r.actual_start_date) * 1440), 2) "Avg Elapsed Time (min)", count(*) from apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs_tl p where r.concurrent_program_id = p.concurrent_program_id and r.program_application_id = p.application_id and r.concurrent_program_id = &&program_id and p.language = 'US' group by r.concurrent_program_id, p.user_concurrent_program_name, trunc(r.actual_start_date) / select concurrent_program_id, user_concurrent_program_name, count(*), avg(round((actual_completion_date - actual_start_date) * 1440, 2)) as "Avg_Time", max(round((actual_completion_date - actual_start_date) * 1440, 2)) as "Max_Time", min(round((actual_completion_date - actual_start_date) * 1440, 2)) as "Min_Time" from (select fr.concurrent_program_id, fc.user_concurrent_program_name, actual_start_date, actual_completion_date from apps.fnd_concurrent_requests fr, apps.fnd_concurrent_programs_tl fc, apps.fnd_user fu where fr.concurrent_program_id = fc.concurrent_program_id and fu.user_id = fr.requested_by and fr.concurrent_program_id = &&program_id and fc.language = 'US' and fr.status_code = 'C' and fr.phase_code = 'C') group by concurrent_program_id, user_concurrent_program_name / prompt ++++++++++++++++++++++++++++++++++ END prompt
Query to find job statistics submitted by a particular user.
It is useful to run during the month end to monitor the job statistics for a particular user.
It can be customized for (Input)
- User
- Request Date or Completion Date.
- Specific concurrent Program.
select r.request_id Request, fcpt.USER_CONCURRENT_PROGRAM_NAME, f.user_name UserNam, DECODE(r.phase_code, 'C', 'Completed', 'I', 'Inactive', 'P', 'Pending', 'R', 'Running', r.phase_code) PHASE, DECODE(r.status_code, 'A', 'Waiting', 'B', 'Resuming', 'C', 'Normal', 'D', 'Cancelled', 'E', 'Errored', 'F', 'Scheduled', 'G', 'Warning', 'H', 'On Hold', 'I', 'Normal', 'M', 'No Manager', 'Q', 'Standby', 'R', 'Normal', 'S', 'Suspended', 'T', 'Terminating', 'U', 'Disabled', 'W', 'Paused', 'X', 'Terminated', 'Z', 'Waiting', r.status_code) STATUS, to_char(r.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') Run_date, to_char(r.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') Comletion_date, round((nvl(r.actual_completion_date, sysdate) - r.actual_start_date) * 1440, 2) Elapsed, r.argument_text "Program Parameters" from apps.fnd_concurrent_requests r, apps.fnd_user f, apps.fnd_concurrent_programs_tl fcpt where r.requested_by = f.user_id and TO_DATE(r.request_date) >= to_date('08-NOV-2016 10:00:00', 'DD-MON-YYYY HH24:MI:SS') and f.user_name = '&User_Name' and fcpt.CONCURRENT_PROGRAM_ID = r.CONCURRENT_PROGRAM_ID and fcpt.language = 'US' and upper(fcpt.USER_CONCURRENT_PROGRAM_NAME) like upper('%&Prog_name%') AND r.phase_code = 'C' -- Can be commented AND r.status_code = 'C' -- Can be commented as per need order by r.actual_completion_date desc
Query to find the profile option and their values defined.
Query can be customized to check for any specific profile option name
- Input – Profile Option name(Wild character will also do)
select distinct t.user_profile_option_name "Profile Option Name", decode(v.level_id, 10001,'Site Level', 10002,'Application Level --> ' ||application_name , 10003,'Responsibility level-->'||responsibility_name, 10004,'User Level-->' ||u.user_name, 'XXX') "Profile Option Level", profile_option_value "Value" from apps.fnd_profile_options o, apps.fnd_profile_option_values v, apps.fnd_profile_options_tl t, apps.fnd_responsibility_tl r, apps.fnd_application_tl a,apps.fnd_user u where o.profile_option_id = v.profile_option_id and o.application_id = v.application_id and start_date_active <= SYSDATE and nvl(end_date_active,SYSDATE) >= SYSDATE and o.profile_option_name = t.profile_option_name and a.application_id(+) = decode(level_id,10002,level_value,null) and r.responsibility_id(+)= decode(level_id,10003,level_value,null) and u.user_id(+) = decode(level_id,10004,level_value,null) and upper(t.user_profile_option_name) like upper('%&Profile_name%') and t.language = 'US' order by 2, t.user_profile_option_name, decode(v.level_id, 10001,'Site Level', 10002,'Application Level --> ' ||application_name , 10003,'Responsibility level-->'||responsibility_name, 10004,'User Level-->' ||u.user_name, 'XXX')
Query to know how much datafiles within a specific tablespace can be resized. This query comes very handy when you want to check how much a datafile within a TBS can be resized.
- Input – Tablespace ID (can be found from v$tablespace) and Tablespace Name
set linesize 1000 pagesize 0 feedback off trimspool on with hwm as ( -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents ) -- 403 is the TS # for APPS_TS_TX_DATA select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue WHERE ktfbuesegtsn = 388 group by ktfbuefno,ktfbuesegtsn ), hwmts as ( -- join ts# with tablespace_name select name tablespace_name,relative_fno,hwm_blocks from hwm join v$tablespace using(ts#)where name = 'APPS_TS_TX_DATA' ), hwmdf as ( -- join with datafiles, put 5M minimum for datafiles with no extents select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes from hwmts right join dba_data_files using(tablespace_name,relative_fno) where tablespace_name = 'APPS_TS_TX_DATA' ) select case when autoextensible='YES' and maxbytes>=bytes then -- we generate resize statements only if autoextensible can grow back to current size '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999) ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ ' ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;' else -- generate only a comment when autoextensible is off '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999) ||'M from '||to_char(ceil(bytes/1024/1024),999999) ||'M after setting autoextensible maxsize higher than current size for file ' || file_name||' */' end SQL from hwmdf where bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed order by bytes-hwm_bytes desc;
Happy Reading!
-Anand M
Oracle Apps R12.2.2 Log file location and Environment Variables
I have compiled the name and location of all the log files in Oracle EBS 12.2.2. As a DBA, I find it quite handy when you need to do some troubleshooting with a definite timeline.
Oracle Apps R12.2.2 Log file location
Also below is the frequently used and very useful environment variables that comes handy often.
$ echo $FILE_EDITION
<shows which file edition you are using, run or patch>
$ echo $RUN_BASE
<shows an absolute path to run file system>
$ echo $PATCH_BASE
<shows an absolute path to patch file system>
$ echo $NE_BASE
<shows an absolute path to non-edition file system>
$ echo $APPL_TOP_NE
<non-editioned appl_top path. Equivalent to $NE_BASE/EBSapps/appl>
$ echo $LOG_HOME
<Application Instance Specific Log Directory>
$ echo $ADOP_LOG_HOME
<Online patching Specific Log Directory. Equivalent to $NE_BASE/EBSapps/log/adop>
$ echo $IAS_ORACLE_HOME
<FMW Web Tier Home Directory>
echo $FMW_HOME
<FMW home>
$ echo $ORACLE_HOME
< 10.1.2 ORACLE_HOME>
$ echo $CONTEXT_FILE
<Source for information populating template files (autoconfig)>
$ echo $EBS_DOMAIN_HOME
<WLS Deployment of Oracle E-Business Suite 12.2 Domain (instance specific)>
$ echo $ADMIN_SCRIPTS_HOME
<Shell scripts to control processes associated to the Applications Instance>
$ echo $EBS_ORACLE_HOME
<Oracle E-Business Suite 12.2 FMW Deployment directory>
$ echo $RW
<10.1.2 reports directory>
$ echo $HOSTNAME
<hostname without domain name>
$ echo $APPS_VERSION
<to get the EBS version>
And the most important part is for setting up the right environment don’t directly hard code the RUN environment .env file in the .profile of OS user as online patching switches the filesystem from RUN to PATCH and vice versa and it can really create confusion.
Instead use EBSapps.env environment file (created under BASE directory) with ‘RUN’ as argument. It will automatically find which file system (fs1 or fs2) is currently RUN file system and lay out the correct environment.
For example, in our case base directory is ‘/<TWO_TASK>/applmgr’
. /<TWO_TASK>/applmgr/EBSapps.env RUN
E-Business Suite Environment Information
—————————————-
RUN File System : /<TWO_TASK>/applmgr/fs2/EBSapps/appl
PATCH File System : /<TWO_TASK>/applmgr/fs1/EBSapps/appl
Non-Editioned File System : /<TWO_TASK>/applmgr/fs_ne
DB Host: xyz008.abc.com Service/SID: <TWO_TASK>
Sourcing the RUN File System …
Hope this helps. Happy learning!!!
-Anand M
Space reclaim using complete database export import
We had a non-prod database which was having more than 50% of space as free. Objective was to reclaim the space at OS level and release the space back to storage.
This is an Oracle E-Biz environment.
Ebiz Version – 12.2.2
Oracle DB version – 11.2.0.3
OS – 64 bit Oracle Linux
Tried to resize the datafile as much as possible but could not reclaim enough space. Hence thought of doing the full database re-org using export and import.
This document demonstrates step by step procedure with screenshots to do full database reorg using export/import
Full Database reorg using export-import
Post export/import, I was able to reclaim around 4 TB (65% reduction) of space.
Pls see the result below.
Hope this helps. Happy learning!!!
-Anand M