ORACLE DBA

Troubleshooting - APEX login page is not coming after APEX fresh installation in EBS  APEX login page is not coming after fresh installation/upgrade of APEX 5.0.3 in R12 EBS database using embedded PL/SQL Gateway. Verify shared_servers parameter and make sure that it should not set to "0" Change the parameter value to non-zero value and retest the issue. SQL> alter system set shared_servers=1; System altered. SQL>

 Query to find Concurrent Program Name using concurrent request ID. SELECT PROGRAM FROM FND_CONC_REQ_SUMMARY_V WHERE REQUEST_ID = ‘Request ID;

Query to flush the running sql ## oracle Database ## Some times we have to flush the running sql due to bad sql plan. New sql plan can be picked if we can flush the running sql. Make sure the running sql has bad sql plan before flushing it. Find the sql_id using below query. select sid,serial#,sql_id from v$session where sid=438; Flush the running sql from shared pool using below steps. Step A:  select address, hash_value from v$sqlarea where sql_id like 'gr7q58thdjntu'; Step B: exec dbms_shared_pool.purge('00000000qaqla8, 2358767','C');

Fetch session details Session details associated with Oracle SID  set head off set verify off set echo off set pages 1500 set linesize 100 set lines 120 prompt prompt Details of SID / SPID / Client PID prompt ================================== select /*+ CHOOSE*/ 'Session  Id.............................................: '||s.sid, 'Serial Num..............................................: '||s.serial#, 'User Name ..............................................: '||s.username, 'Session Status .........................................: '||s.status, 'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client, 'Server Process ID ......................................: '||p.spid Server, 'Sql_Address ............................................: '||s.sql_address, 'Sql_hash_value .........................................: '||s.sql_hash_value, 'Schema Name ..... .......................

 query to find the SQLTEXT in datbase oracle Find sqltext through sqladdress select sql_address from gv$session where sid=36; select sql_text from v$sqltext where ADDRESS='D00000034GG11BH1' order by PIECE; To find sqltext for different sql hashvalues: select hash_value,sql_text from v$sql where hash_value in (3417378547,6564768875,8753841712, 1353497310,7874027456);

query application user end data in oracle applications 11i and R12 here we can not delete the user in oracle apps R12.  instead of we can end date the user.  we can use below query to find the end date of a user. select user_name,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY, START_DATE,END_DATE from fnd_user where user_name='SRIN500';

gather stats for a single table in oracle apps R12 connect to apps user and trigger below query begin  FND_STATS.GATHER_TABLE_STATS(ownname=>'&owner', tabname =>'&table_name', percent=> 80, degree=> 80, cascade=>TRUE, granularity=>'ALL');  end;   /

  SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name  FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup  WHERE concurrent_queue_name = 'WFALSNRSVC'  AND fcq.concurrent_queue_id = fcp.concurrent_queue_id  AND fcq.application_id = fcp.queue_application_id  AND flkup.lookup_code=fcp.process_status_code   AND lookup_type ='CP_PROCESS_STATUS_CODE'   AND meaning='Active';

 select owner,object_name,timestamp, to_char(created,'hh24:mi:ss'), to_char(last_ddl_time,'hh24:mi:ss'),status from dba_objects  where status = 'INVALID'  and owner = 'SYS'  order by timestamp;

  set pagesize 200; set linesize 160; column adop_session_id format 999999999999; column bug_number format a15; column status format a15; column applied_file_system_base format a23; column patch_file_system_base format a23; column adpatch_options format a15; column node_name format a15; column end_date format a15; column clone_status format a15; select ADOP_SESSION_ID, BUG_NUMBER, STATUS, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYSTEM_BASE, ADPATCH_OPTIONS, NODE_NAME, END_DATE, CLONE_STATUS from ad_adop_session_patches order by end_date desc;

  set lines 200 pages 200 select s.sid, t.name, s.value from v$sesstat s, v$statname t where s.statistic#=t.statistic# and t.name='undo change vector size' order by s.value desc;

 Query to find concurrent request ID using SID in R12 SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s WHERE s.sid in ('&SID') AND s.paddr = c.addr AND a.oracle_process_id = c.spid AND a.phase_code = UPPER ('R');

How to find oracle database monthly growth set pagesize 50000 tti "Database growth per month for last year" select to_char(creation_time, 'RRRR Month') "Month",        sum(bytes)/1024/1024/1024 "Growth in GB"   from sys.v_$datafile where creation_time > SYSDATE-365 group by to_char(creation_time, 'RRRR Month') /

 find oracle temp tablespace usage in DB - Oracle ## SET PAUSE ON SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300   SELECT     A.tablespace_name tablespace,     D.mb_total,    SUM (A.used_blocks * D.block_size) /1024/1024/1024 gb_used,    D.mb_total - SUM (A.used_blocks * D.block_size) /1024/1024/1024 gb_free FROM     v$sort_segment A, ( SELECT     B.name,     C.block_size,     SUM (C.bytes) /1024/1024/1024 mb_total FROM     v$tablespace B,     v$tempfile C WHERE     B.ts#= C.ts# GROUP BY     B.name,     C.block_size) D WHERE     A.tablespace_name = D.name GROUP by     A.tablespace_name,     D.mb_total /

 find SID using SPID in oracle database   col sid format 9999999 col username format a25 col osuser format a16 select b.spid,a.sid, a.serial#,a.username, a.osuser from v$session a, v$process b where a.paddr= b.addr and b.spid='&spid' order by b.spid;

 fetch execution plan using sql_id  set lines 500 SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id',0)); example SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('<D345G5543de',0));

 identify user is already existed in OID or not   login to OID app server. trigger below command. ebsadmin@yebolisetty-apps.gen.local $ ldapsearch -p 3060 -h <OID SERVER IP> -D "cn=orcladmin" -w <orcladmin password> -b "" -s sub "uid=User Name"

  select b.USER_CONCURRENT_QUEUE_NAME from apps.fnd_concurrent_processes a, fnd_concurrent_queues_vl b, fnd_concurrent_requests c  where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID  and a.CONCURRENT_PROCESS_ID = c.controlling_manager  and c.request_id = 674392731;

 identify opp log file using concurrent (CM Req ID) request identify SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp WHERE fcpp.processor_id = fcp.concurrent_process_id AND fcpp.action_type = 6 AND fcpp.concurrent_request_id = &&request_id;

 Steps to terminate concurrent (CM) request from server InGeneral, we terminate the requests from sysadmin responsibilities. in some cases, we may recieve this type of requests from apps team to clear it from server. connect to apps user and then trigger below update stamement along with commit. update apps.fnd_concurrent_requests set phase_code='C', status_code='X' where request_id='&REQUEST_ID'; commit;

 Day To Day Responsibiliteis related oracle dba scripts These scripts are valid for Single & rac Database & exadata. A => status to check Oracle instance is running or not. [oracle@yesbolisetty01 ~]$ ps -ef | grep smon or [oracle@yesbolisetty01 ~]$ ps -ef | grep pmon Check all Instance of any database as follows. [oracle@yesbolisetty01 ~]$ srvctl status database -d <DB_NAME> [oracle@yesbolisetty01 ~]$ ps -ef | grep smon oracle 140550 1 0 2019 ? 00:01:49 asm_smon_+ASM1 root 147726 1 1 2019 ? 1-04:06:08 /u01/app/12.1.0.2/grid/bin/osysmond.bin oracle 156342 1 0 2019 ? 00:13:57 ora_smon_testdb oracle 248609 246524 0 12:02 pts/0 00:00:00 grep smon [oracle@yesbolisetty01 ~]$ [oracle@yesbolisetty01 ~]$ srvctl status database -d testdb Instance testdb1 is running on node yesbolisetty01 Instance testdb2 is running on node yesbolisetty02 [oracle@yesbolisetty01 ~]$ B => status to check Local /SCAN listeners are running or not [oracle@yesbolisetty01 ~]$ lsnrctl status [orac...

Trick to convert viceversa ð   SCN to TIMESTAMP ð   TIMESTAMP to SCN   Here is the process on how to convert SCN to TIMESTAMP and also TIMESTAMP to SCN in oracle database servers In real time scenarios   we may get the requirement. These are the seeded functions to convert SCN to Timestmp SCN_TO_TIMESTAMP TIMESTAMP_TO_SCN   Convert SCN to Timestamp:   SQL> select current_scn from v$database; CURRENT_SCN ----------------------    2835496 SQL> select scn_to_timestamp(2835496) from dual; SCN_TO_TIMESTAMP(2835496) --------------------------------------------------------------------------- 28-FEB-21 11.15.09.000000000 AM     Convert TIMESTAMP to SCN:   SQL> select timestamp_to_scn(to_timestamp(‘28-FEB-21 11.15.09.000000000’,'dd-mm-yyyy hh24:mi:ss')) SCN from dual;        SCN ----------------    2835496