Friday 6 June 2014

DBA support cammand

My scripts

Oracle R12 Apps startup script

 scripts name= Apps_startup.sh

. /oratest/TEST/db/tech_st/11.1.0/TEST_test.env
lsnrctl start TEST
. /$ORACLE_HOME/appsutil/scripts/TEST_test/addbctl.sh start
. /appltest/TEST/apps/apps_st/appl/APPSTEST_test.env
. /$INST_TOP/admin/scripts/adstrtal.sh apps/apps

How to find Oracle EBSR12 URL

SQL> select HOME_URL from icx_parameters;

HOME_URL
--------------------------------------------------------------------------------
http://ebs.demo.com/OA_HTML/AppsLogin

SQL> Select PROFILE_OPTION_VALUE
From   FND_PROFILE_OPTION_VALUES
WHERE  PROFILE_OPTION_ID =
       (SELECT PROFILE_OPTION_ID
        FROM FND_PROFILE_OPTIONS
        WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT')
        AND LEVEL_VALUE=0;

PROFILE_OPTION_VALUE
--------------------------------------------------------------------------------
http://ebs.globalerpdemo.com:80

Oracle R12 apps stop script

 scripts name= Apps_startup.sh
. /appltest/TEST/apps/apps_st/appl/APPSTEST_idiora2.env
. /$INST_TOP/admin/scripts/adstpall.sh apps/apps
. /oratest/TEST/db/tech_st/11.1.0/TEST_test.env
lsnrctl stop TEST

. /$ORACLE_HOME/appsutil/scripts/TEST_test/addbctl.sh stop immediate
How to ADD DATA-FILE?

 1. Need to check and verified host-name & Database name.
2. Need to check Mount point size (free space available or not)
3. Log-in with SQL
4. Check Data-file location and name.

 sujeet $ hostname

 sujeet
SQL> select name from V$database;

 NAME
---------
idea

 SQL> !df -g <AIX OS>
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on

/dev/fslv03      204.00     55.73   73%  1159614     9% /u01
/dev/fslv04      204.00     50.03   76%       17     1% /u02
/dev/fslv05      470.00    202.19   57%       61     1% /u03
/dev/fslv06      600.00    154.14   75%       92     1% /u04

 Sujeet $ sqlplus / as sysdba

 Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 SQL> desc dba_data_files;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(257)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)
bash-4.2$ sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 16 06:36:35 2014

 Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 SQL> alter tablespace SYSAUX add datafile '/u03/oracle/idea/sysaux12.dbf' size 20G autoextend on next 500M maxsize 30G;

 Tablespace altered.
SQL> select file_name,TABLESPACE_NAME,BYTES/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024,INCREMENT_BY/1024/1024 from dba_data_files where tablespace_name='SYSAUX';
FILE_NAME                                TABLESPACE_NAME                BYTES/1024/1024 AUT MAXBYTES/1024/1024 INCREMENT_BY/1024/1024
---------------------------------------- ------------------------------ --------------- --- ------------------ -------------------          
/u04/oracle/idea/sysaux08.dbf        SYSAUX                                   30720 YES              30720             .061035156
/u05/oracle/idea/sysaux09.dbf        SYSAUX                                   10240 YES              10240             .061035156
/u04/oracle/idea/sysaux10.dbf        SYSAUX                                   30720 YES              30720             .061035156
/u05/oracle/idea/sysaux11.dbf        SYSAUX                                   20300 YES              30720             .012207031

 FILE_NAME                                TABLESPACE_NAME                BYTES/1024/1024 AUT MAXBYTES/1024/1024 INCREMENT_BY/1024/1024
---------------------------------------- ------------------------------ --------------- --- ------------------ ----------------------
/u03/oracle/idea/sysaux12.dbf        SYSAUX                                   20480 YES              30720             .061035156
12 rows selected.
How to check RAC Services Status, Start & STOP CommandA. Status of the services

[oracle@sujeet ~]$ srvctl status instance -d prod1 -i prod2 

[oracle@sujeet ~]$ srvctl status scan_listener -i 1

[oracle@sujeet ~]$ srvctl status scan -i 1

[oracle@sujeet ~]$ srvctl status listener -n sujeet 

[oracle@sujeet ~]$ srvctl status nodeapps -n sujeet  


[root@sujeet ~]# cd /u01/app/11.2.0.3/grid/bin


[root@sujeet bin]# ./crsctl check crs


B. Stop the services

1.  [oracle@sujeet ~]$ srvctl stop instance -d prod1 -i prod2

2.  [oracle@sujeet ~]$ srvctl stop scan_listener -i 1

3.  [oracle@sujeet~]$ srvctl stop scan -i 1

4.  [oracle@sujeet ~]$ srvctl stop listener -n sujeet
 
5.  [oracle@sujeet ~]$ srvctl stop nodeapps -n sujeet
 
6.      [root@sujeet ~]# cd /u01/app/11.2.0.3/grid/bin
    [root@sujeet bin]#
    [root@sujeet bin]# ./crsctl check crs
    [root@sujeet bin]#
    [root@sujeet bin]# ./crsctl stop crs



Note:- After the rebooting of the server check all the services status as mentioned in part A, services will come up automatically, if the services are not resumed in 10 minutes  start the services as mentioned in part C

C. Starting the services

    [root@sujeet ~]# cd /u01/app/11.2.0.3/grid/bin
    [root@sujeet bin]#
    [root@sujeet bin]# ./crsctl start crs
    [root@sujeet bin]#
    [root@sujeet bin]# ./crsctl check crs

Note:- after starting ‘./crsctl start crs’ wait till all the services comes ‘ONLINE’ then start other services

2. [oracle@sujeet ~]$ srvctl start nodeapps -n sujeet
  
3. [oracle@sujeet ~]$ srvctl start listener -n sujeet
  
4. [oracle@sujeet ~]$ srvctl start scan -i 1
    
5. [oracle@sujeet~]$ srvctl start scan_listener -i 1
     
6. [oracle@sujeet ~]$ srvctl start instance -d prod1 -i prod2
  
   
Check Archive Mode States

 SQL> select dest_name,status, destination from v$archive_dest;

 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oratest/TEST/db/tech_st/11.1.0/dbs/arch
Oldest online log sequence     91
Next log sequence to archive   92
Current log sequence           92


 SQL> show parameters archive log

Check v$managed_standby status

 SQL> select process, status, sequence# from v$managed_standby;

 PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      WRITING           18723
ARCH      CLOSING           18783
ARCH      WRITING           18722
ARCH      WRITING           18724

LNS       WRITING           18784

 How to create INDEX?
SQL> create index SG00200TI5 on UMSDATA.SG00200T (SOURCE_ID,SOURCE_CD) tablespace ums_data;
INDEX CREATED.
How to find INDEX Created or not?
Index name=SG00200TI5
SQL> select count(*) from user_indexes where index_name = ' SG00200TI5';
We can alter the index created by using this statement to start monitoring..

ALTER INDEX yourIndex_idx MONITORING USAGE;

Once, it is altered, you can query the table, v$object_usage for checking the result

SELECT table_name, index_name, monitoring, used FROM v$object_usage;

 How to delete log file last 5 days before?

 If Apps server have space issue at that time DBA release unwanted file and log.

 $ cd $APPLCSF/$APPLLOG

 find . -name '*.out' -mtime +5 | xargs rm -f
find . -name '*.req' -mtime +5 | xargs rm -f
find . -name '*.RTF' -mtime +5 | xargs rm -f

 find . -name '*.xml' -mtime +5 | xargs rm -f
find . -name 'access_log*' -mtime +5 | xargs rm -f

 find . -name 'error_log*' -mtime +5 | xargs rm -f
   

 Check Mount Point space 

 $ df -h <Linux OS>

Check Database size

 SQL> select sum(bytes / (1024*1024*1024)) "DB Size in GB" from dba_data_files;

 DB Size in GB
-------------

   85.7810669
Export Full Database Scripts
script name:-  exp_TEST_idea.sh
#!/bin/bash
#Delete exports older than 3 days
find /u01/db_exports -name '*.dmp' -mtime +3 | xargs rm -f
find /u01/db_exports -name '*.log' -mtime +3 | xargs rm -f
export ORACLE_SID=test_idea
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH:.
fdate=`date +%Y%m%d`
expdp system/manager@TEST_idea directory=TEST_idea_EXP_DIR dumpfile=TEST_idea_$fdate.dmp logfile=TEST_idea_export_$fdate.log full=y

 RMAN Backup Script

 # Identify the backup location
export BackupLocation=/backup/Test

 # Source the environment file, if any
. /home/oratest/TEST_db.env
find /backup/Test/RMAN -name '*.gz' -mtime +15 | xargs rm -f

 # Run the RMAN backup script and spool the output
date
rman target / nocatalog log=$BackupLocation/LogRMAN << EOF
run {

 allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
crosscheck backup;
delete noprompt expired backup;
backup database format '$BackupLocation/Test_`date +%F`_%U_DB.bkp';
backup current controlfile format '$BackupLocation/Test_`date +%F`_ControlFile.bkp';
backup archivelog from time 'sysdate-1' format '$BackupLocation/Test_`date +%F`_%U_ArchiveLog.bkp';
delete noprompt archivelog until time 'sysdate-7';
crosscheck archivelog all;
delete noprompt expired archivelog all;
release channel t1;
release channel t2;
release channel t3;

 }
exit;
EOF

 # Compress the file
date
gzip -r $BackupLocation/Test_`date +%F`*

 # Transfer the file
date
mv $BackupLocation/Test_`date +%F`* $BackupLocation/RMAN
date
echo "Backup Completed Successfully ..."
INSTANCE RELATED QUERIES 

SQL> select instance_name,instance_number,status from v$instance;

 INSTANCE_NAME    INSTANCE_NUMBER STATUS
---------------- --------------- ------------
PROD                           1 OPEN

SQL> select banner from v$version;

 BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> desc fnd_product_groups


SQL> select release_name,last_update_date from fnd_product_groups;

 RELEASE_NAME                                       LAST_UPDA
-------------------------------------------------- ---------
12.1.3                                             15-JUL-12

SQL> select * from v$parameter


SQL> select name from v$parameter where name like '%out%'

How to check how may CPU Running?

CPU NO Count:-
[root@sujeet ~]# cat /proc/cpuinfo | grep "cpu cores" |uniq

cpu cores       : 6
How to check OPEN CURSOR Parameter?

SQL> select sum(a.value) from v$sysstat a,v$statname b
where a.STATISTIC#=b.STATISTIC#
and b.name='opened cursors current';
SUM(A.VALUE)
------------
          76

SQL> show parameter open
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     600
open_links                           integer     4
open_links_per_instance              integer     4
read_only_open_delayed               boolean     FALSE
session_max_open_files               integer     10

How to find RAC all IP Address?

SCAN_IP_INFO:-

[oracle@sujeet ~]$ . idea1.env
[oracle@sujeet ~]$ srvctl config scan

SCAN name: sujeet-scan, Network: 1/10.110.30.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /sujeet-scan/10.110.30.25
SCAN VIP name: scan2, IP: /sujeet-scan/10.110.30.26
SCAN VIP name: scan3, IP: /sujeet-scan/10.110.30.27

VIP Info:-

[oracle@sujeet ~]$ srvctl config nodeapps -a

Network exists: 1/10.110.30.0/255.255.255.0/eth0, type static
VIP exists: /ami-ms-db-a-vip/10.110.30.23/10.110.30.0/255.255.255.0/eth0, hosting node ami-ms-db-a
VIP exists: /ami-ms-db-b-vip/10.110.30.24/10.110.30.0/255.255.255.0/eth0, hosting node ami-ms-db-b

Private IP Info:-

SQL> select INST_ID,IP_ADDRESS from GV$CLUSTER_INTERCONNECTS;
 INST_ID IP_ADDRESS
---------- ----------------
         1 169.254.204.91
         2 169.254.35.170

SQL> select IP_KSXPIA from X$KSXPIA where PUB_KSXPIA= 'N';
IP_KSXPIA
----------------
169.254.204.91

[oracle@sujeet ~]$ nslookup ami-ms-db-a.btutilities.com
Server:         10.110.4.244
Address:        10.110.4.240#53
Name:   sujeet.oracle.com
Address: 10.110.30.22

PATCHING RELATED 


SQL> select * from dba_objects;


SQL> select count(*) from dba_objects where status ='INVALID';


SQL> select * from dba_objects where status='INVALID'


SQL> select count(*) from dba_objects where owner = 'APPS' and status ='INVALID';


SQL> select * from dba_objects where status='INVALID';


SQL> select bug_number,last_update_date from ad_bugs where bug_number='13374062'


SQL> select bug_number,last_update_date from ad_bugs where bug_number='13006289';        

BUG_NUMBER                     LAST_UPDATE_DATE

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

13006289                       05-MAR-14


SQL> select patch_name, last_update_date from ad_applied_patches where patch_name='13374062'


SQL> select release_name,last_update_date,creation_date from fnd_product_groups


SQL> select  bug_number,last_update_date from ad_bugs where trunc(LAST_UPDATE_DATE) >= '13-JUN-2011'


SQL> select  PATCH_NAME,last_update_date from ad_applied_patches where trunc(LAST_UPDATE_DATE) >='08-JUN-2011'


USER ACCOUNT RELATED


SQL> select * from dba_users where username='APPS';


SQL> select username,account_status from dba_users where username='RAC_ACCNT';


SQL> select user_id,user_name,session_number from fnd_user where user_name=''


SQL> select username,password,account_status,lock_date from dba_users where username='APPS'


TABLE SPACES and GRANTS Related 


SQL> select * from dba_tablespaces


SQL> select * from dba_temp_files


SQL> select tablespace_name from dba_tablespaces where tablespace_name='READONLY';


SQL> select username,DEFAULT_TABLESPACE from dba_users where username='ROAPPS'


SQL> revoke  select on  HR.AME_ACTION_TYPES_TL from ROAPPS


DATABASE  LOCKS 


SQL> select s1.username || '@' || s1.machine

|| ' ( SID=' || s1.sid || ' )  is blocking '

|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$session s2

where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0

and l1.id1 = l2.id1

and l2.id2 = l2.id2 ;


no rows selected


SQL> desc dba_locks

 Name                                      Null?    Type

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

 SESSION_ID                                         NUMBER

 LOCK_TYPE                                          VARCHAR2(26)

 MODE_HELD                                          VARCHAR2(40)

 MODE_REQUESTED                                     VARCHAR2(40)

 LOCK_ID1                                           VARCHAR2(40)

 LOCK_ID2                                           VARCHAR2(40)

 LAST_CONVERT                                       NUMBER

 BLOCKING_OTHERS                                    VARCHAR2(40)


SQL> desc v$locked_objects ;


SQL> desc dba_blockers


SQL> select holding_session from dba_blockers


SQL> select session_id,lock_type from dba_locks


SQL> select * from dba_ddl_locks


SQL> select * from v$lock where block<>0;


SQL> select * from v$session where sid =820;


SQL> select * from V$sqltext where  hash_value='3533547372' order by piece


For checking what SQL, CM program is executing


SQL> select * from fnd_concurrent_requests where request_id='639447';


we need to take oracle_process_id  from above , then  SPID is oracle process id from the above query


SQL> select * from v$process where spid='15229';


Take paddr value from the above query


SQL> select * from v$session where paddr='00000000DC48C2D0';


Take SQL_HASH_VALUE from the above query


SQL> select * from V$sqltext where  hash_value='2556871103' order by piece;


COMPILE APPS SCHEMA


SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'APPS');


APP Users Responsibilities


SQL> Select fu.user_id

,fu.user_name

,frt.RESPONSIBILITY_NAME

,fur.start_date Start_date

,fur.end_date End_Date

From fnd_user fu

,apps.FND_USER_RESP_GROUPS_ALL fur

,FND_RESPONSIBILITY_TL frt

Where fu.user_name= 'VIKRAM_MEHTA'

and fu.user_id=fur.user_id

and fur.RESPONSIBILITY_ID=frt.RESPONSIBILITY_ID

                  

GATHER SCHEMA STATISTICS on TABLES 


exec dbms_stats.gather_table_stats(SYS, 'GL_JE_LINES', cascade => TRUE);


exec sys.dbms_stats.gather_table_stats(GL, 'GL.GL_JE_LINES', cascade => TRUE);


execsys.dbms_stats.gather_table_stats('GL','GL_JE_LINES',ESTIMATE_PERCENT=>100,CASCADE=>True);


analyze table gl.gl_je_lines estimate statistics sample 100 percent for table for all indexed columns forall indexes


analyze table gl.gl_balances estimate statistics sample 100 percent for table for all indexed columns forall indexes


select table_name,column_name,num_distinct,last_analyzed,num_buckets from user_tab_cols wheretable_name like ('%GL_JE_LINES%');


select table_name,index_name,num_rows,last_analyzed from user_indexes where table_name like('%GL_JE_LINES%');    

                  

TEMPLATES 


SQL> select * from xdo_lobs where LOB_TYPE like 'TEMPLATE_SOURCE'

and LOB_CODE like 'XXWINCEADR%'             


select * from xdo_lobs where 1=1-- LOB_TYPE like 'TEMPLATE_SOURCE'

and LOB_CODE like 'PO_STANDARD_PO%'


OTHERS 

                 

SQL> select * from dba_ddl_locks

where name like 'XXWIN_HCM%'


SQL> select * from v$lock


SQL> select * from v$session_wait


SQL> select * from fnd_concurrent_requests


SQL> select request_id, request_date,logfile_name from fnd_concurrent_requests where request_id='744042'


SQL> select * from fnd_nodes


To see employee name for the persons column (To add employee to the user)


select * from per_all_people_f  where FULL_NAME like '%vic%'


select * from apps.fnd_lookup_values where lookup_type='XXWIN_SFTP_DETAILS'

              
select * from v$recover_file

To SEE what parameters  assigned for CUSTOM CONCURRENT PROGRAMS


select * from FND_DESCR_FLEX_COL_USAGE_VL

where END_USER_COLUMN_NAME = 'p_debug_flag'


select * from fnd_concurrent_programs where concurrent_program_id=55455


select * from  xxwin_datafile_validation_tab where data_file_name like 'UKJNL%'

Table Space Query

SELECT

    d.status                                            status

  , d.tablespace_name                                   name

  , d.contents                                          type

  , d.extent_management                                 extent_mgt

  , d.segment_space_management                          segment_mgt

  , NVL(a.bytes, 0)                                     ts_size

  , NVL(a.bytes - NVL(f.bytes, 0), 0)                   used

  -- , NVL(f.bytes, 0)                                     free

  , NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used

FROM

    sys.dba_tablespaces d

  , ( select tablespace_name, sum(bytes) bytes

      from dba_data_files

      group by tablespace_name

    ) a

  , ( select tablespace_name, sum(bytes) bytes

      from dba_free_space

      group by tablespace_name

    ) f

WHERE

      d.tablespace_name = a.tablespace_name(+)

  AND d.tablespace_name = f.tablespace_name(+)

  AND NOT (

    d.extent_management like 'LOCAL'

    AND

    d.contents like 'TEMPORARY'

  )

and NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)>70

UNION ALL

SELECT

    d.status                         status

  , d.tablespace_name                name

  , d.contents                       type

  , d.extent_management              extent_mgt

  , d.segment_space_management       segment_mgt

  , NVL(a.bytes, 0)                  ts_size

  , NVL(t.bytes, 0)                  used

  , NVL(t.bytes / a.bytes * 100, 0)  pct_used

FROM

    sys.dba_tablespaces d

  , ( select tablespace_name, sum(bytes) bytes

      from dba_temp_files

      group by tablespace_name

    ) a

  , ( select tablespace_name, sum(bytes_cached) bytes

      from v$temp_extent_pool

      group by tablespace_name

    ) t

WHERE

      d.tablespace_name = a.tablespace_name(+)

  AND d.tablespace_name = t.tablespace_name(+)

  AND d.extent_management like 'LOCAL'

  AND d.contents like 'TEMPORARY'

  AND NVL(t.bytes / a.bytes * 100, 0)>70



select * from dba_temp_files


Checks to be performed while Concurrent program is running.


1)    You need to first get the oracle session id by running the below sql with appropriate request_id.


select * from fnd_concurrent_requests where request_id='656309'


2)    Oracle session id will be the input to the below query to find out the sql_id and SID.


select * from gv$session where AUDSID='2659367'


3)    To find out the latest sql running , you need to input sql_id from the above query into the belowquery.


select * from gv$sqltext where sql_id='3sxmjmxtz9mt8' order by piece


4)    Once the records in the table XXWIN_HCM_EMP_MASTER_STG_TAB  is populated completely ( total records should be 196601) it will start writing into the file. You can get this confirmed by running thequery in step 3 and the output should be below which means it writing to the file.


SELECT * FROM XXWIN_HCM_EMP_MASTER_STG_TAB WHERE EXTRACT_ID = :B

AND REQUEST_ID=:B1


5)    To check the number of records processed, you need to run the below query.


select count(*) from xxwin.XXWIN_HCM_EMP_MASTER_STG_TAB  where status not like  'P'


6)    Keep an eye on the file created in the interface_home outgoing directory. The file size should be gradually increasing.


-rw-r--r-- 1 ortwncti dbtwncti 112573157 Nov 24 16:23 INTHCM1_20111124121142_00001.dat


select count(*) from PO_LINE_LOCATIONS_ALL


select count(*) from po_headers_interface


select count(*) from po_lines_interface


select count(*) from po_distributions_interface



select username,default_tablespace,account_status from dba_users

 where default_tablespace='SYSAUX';


select * from DBA_HIST_WR_CONTROL;


select min(snap_id),MAX(snap_id) from dba_hist_snapshot;


Select * from v$sysaux_occupants;

This is to find the work flow inbound and outbound service names and mailer user names.

select c.component_id, c.component_name, c.component_status,

p.parameter_id, p.parameter_name, v.parameter_value value

from fnd_svc_comp_param_vals_v v,

fnd_svc_comp_params_b p,

fnd_svc_components c

where c.component_type = 'WF_MAILER'

and v.component_id = c.component_id

and v.parameter_id = p.parameter_id

and p.parameter_name in ('OUTBOUND_SERVER', 'INBOUND_SERVER', 'ACCOUNT', 'REPLYTO')

order by c.COMPONENT_ID;


To see the Login url from OS level.

grep -i login $CONTEXT_FILE




Important Quearies to get the SID from PID:

select * from v$session, v$process

where addr=paddr and

v$process.spid  in ('1178','1129')


To Query users:


select user_name,creation_date

from fnd_user 

where end_date is null

and user_name like '%\_%' escape '\'



select trunc(start_time) "Date", count( distinct user_id) "Number of users logged in",

count( distinct user_id)*100/2503 "% of total users"

from fnd_logins

where user_id not in (1162,0)

group by trunc(start_time)


Blocking Sessions Query

check for blocking sessions...

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;


To check lock on PO table’s and AP table’s

select owner,OBJECT_NAME,LOCKED_MODE,SESSION_ID,client_identifier,program,module,action fromv$locked_object,dba_objects,v$session

where dba_objects.owner='AP' and v$locked_object.object_id=dba_objects.object_id

and SESSION_ID=sid;


To Check that the program has to be run with which responsibility.

SELECT responsibility_name
  FROM fnd_responsibility_tl a,
       fnd_responsibility c,             
       fnd_request_group_units d,
       fnd_concurrent_programs_tl b
 WHERE     a.responsibility_id = c.responsibility_id
       AND c.request_group_id = d.request_group_id
       AND b.concurrent_program_id = d.request_unit_id
       AND UPPER (b.USER_CONCURRENT_PROGRAM_NAME) =
              UPPER ('Qtel CI TO IRB Interface Program');

Revoke Exceptional Privileges from a DB User.


SELECT 'revoke insert,update,delete '||owner||'.'||object_name||' from xxwin;' FROM dba_objectsWHERE object_type IN ('TABLE') and owner in ('APPS');

To See responsibilities of a User:

Select fu.user_id

                          ,fu.user_name

                          ,frt.RESPONSIBILITY_NAME

                   From fnd_user fu

                        ,FND_USER_RESP_GROUPS_ALL fur

                        ,FND_RESPONSIBILITY_TL frt

                   Where fu.user_id= 1218

                   and fu.user_id=fur.user_id

                   and fur.RESPONSIBILITY_ID=frt.RESPONSIBILITY_ID


To Find out the DB patch list:

$ cd $ORACLE_HOME/OPatch

$ optach lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc >> dbpatchlist.txt

$ vi patch.list

To find out size of the Database:

select sum(bytes / (1024*1024*1024)) "DB Size in GB" from dba_data_files;


Lock on Table:

select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from 
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a, 
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id


TO FIND OUT CONCURRENT REQUESTS CURRENTLY RUNNING:

set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id
"PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
                        c.program,d.spid from v$session c, v$process d
                        where c.paddr=d.addr) b,
                        apps.fnd_concurrent_programs_tl e,
                        apps.fnd_user f,
                        v$session_wait g
                        where a.oracle_process_id=b.spid
                        and a.concurrent_program_id=e.concurrent_program_id
                        and e.language='US'
                        and a.requested_by=f.user_id
                        and b.sid=g.sid
            and a.status_code='R'
            and a.phase_code='R';


Lock on sessions:

select owner,OBJECT_NAME,LOCKED_MODE,SESSION_ID,client_identifier,program,module,action from v$locked_object,dba_objects,v$session
where dba_objects.owner='AP' and v$locked_object.object_id=dba_objects.object_id
and SESSION_ID=sid;


Find out SID from OS PID:


select * from v$session where paddr in (select addr from v$process where spid =31665)


To Check for the SYSTEM process Associted with Concurrent Request

select distinct s.inst_id,S.TYPE,substr(''''||S.SID||','||S.SERIAL#||'''',1,15) SI_SE#, p.spid,s.process client_process,
 s.MACHINE, s.TERMINAL,P.PID, G.NAME,P.PROGRAM,s.module,S.USERNAME,q.module qmodule ,s.command,s.osuser,
 q.hash_value, substr(q.sql_text,1,30) SQL_TEXT,w.EVENT,W.WAIT_TIME,w.SECONDS_IN_WAIT,
 --,w.p1,w.p2,W.STATE,
 s.logon_time,t.start_time,
 t.used_ublk, t.log_io,t.phy_io,t.cr_change,q.EXECUTIONS,
 q.PLAN_HASH_VALUE, q.CHILD_NUMBER,
.LOGON_TIME,s.last_call_et, s.status
9 from gV$SESSION S, gV$SQL Q, gV$PROCESS P, gV$BGPROCESS G, gv$session_wait w,gV$TRANSACTION T
 where S.SQL_ADDRESS = Q.ADDRESS (+) and S.SQL_HASH_VALUE = Q.HASH_VALUE (+)
 and S.PADDR = P.ADDR
 and S.PADDR = G.PADDR (+)
 and s.sid=w.sid (+)
 AND S.SADDR = T.SES_ADDR (+)
 and s.process in ('&client_process_list')


To Check Business Events Exist or not:

select guid from wf_events where name ='xops.oracle.apps.gmd.rerollandcutbacks'

select *

from WF_BPEL_QTAB

where q_name = 'xops.oracle.apps.gmd.rerollandcutbacks'


To find out SPID:

SELECT s.sid,       p.spid,       s.osuser,       s.program FROM   v$process p,

v$session s WHERE  p.addr = s.paddr

alter system kill session ’134,47747′ immediate;


SQL> select username,sid,serial#,terminal from v$session;


WFUPLOAD  and WFDOWNLOAD


WFLOAD apps/X4wNG4rg 0 Y DOWNLOAD XXWIN_POWFRQAG.wft XXWINPOR


WFLOAD apps/X4wNG4rg 0 Y UPLOAD XXWIN_POWFRQAG.wft XXWINPOR

WFLOAD apps/X4wNG4rg 0 Y FORCE XXWIN_POWFRQAG.wft

select * from fnd_application_tl


Solution For the REP 3000, Report Cache Errors.

1- Shutdown the Concurrent Manager.
2- Manually delete all the temporary files under the /Base/logs/ora/10.1.2/reports/cache 
3- Startup up the Concurrent Manager. 


To Fix this Issue permanently.

Intermittent Oracle Reports REP-0069: REP-57054: Error (Doc ID 1237834.1). 

note 1237834.1 and 1322704.1 mentioned in the support SR and come up the below action plan.


Action Plan
========
1. Stop the MT services
2. Review the patch readme and apply the patch 11669923, 11776182
3. change the cachesize parameter value from 50 to 0 within the file $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf also add the <property name="noVoidedOutputError" value="yes"/>
<property name="cacheSize" value="50"/>
to 
<property name="cacheSize" value="0"/>
<property name="noVoidedOutputError" value="yes"/>
4. Copy the file $FND_TOP/admin/template/rwbuilder_conf_1012.tmp to $FND_TOP/admin/template/custom (ensure the file is not there in the custom directory before copy the file.
5. Add <property name="noVoidedOutputError" value="yes"/> to the $FND_TOP/admin/template/custom/rwbuilder_conf_1012.tmp and also change the cachesize value to 0.

in this file under the <cache class="oracle.reports.cache.RWCache"> section
ie:
<cache class="oracle.reports.cache.RWCache">
<property name="cacheSize" value="0"/>
<property name="noVoidedOutputError" value="yes"/>
<property name="cacheDir"
value="%s_logs_dir%/ora/10.1.2/reports/cache"/>
<!--property name="maxCacheFileNumber" value="max number of cache files"/-->
<!--property name="ignoreParameters" value="parameter names to be
ignored in constructing cache key, separated by comma ','"/-->
</cache>

6. Start the MT services


Huge Pages on the Server:


The host is up and huge page looks good now:

[root@auohswnct08 ~]# cat /proc/meminfo |grep -i huge
HugePages_Total: 8100
HugePages_Free: 6825
HugePages_Rsvd: 6750
Hugepagesize: 2048 kB
[root@auohswnct08 ~]# sysctl -a |grep -i huge
vm.hugetlb_shm_group = 0
vm.nr_hugepages = 8100



The host is hung in console after starting using xm create :

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

[root@auod09m2al141r23 27118_auohswnct08]# xm create -c vm.cfg
Using config file "./vm.cfg".

Started domain 27118_auohswnct08 (id=13)
[root@auod09m2al141r23 27118_auohswnct08]# xm console 27118_auohswnct08

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

We tried booting from the snapshot of Mar 24 - which is the only available snapshot in this DOM0 , but did not help.

Tried starting the host with the huge page entry commented out as the host was rebooted after the huge page changes, but no luck.

Checking further with Maint SA


Remove the hold of concurrent requests from back end:


SQL>  @CMGR_del_hold

SQL> update apps.fnd_concurrent_requests

   2  set hold_flag = 'N'

   3  WHERE

   4  nvl(hold_flag,'N') = 'Y' and phase_code = 'P' and

   5  request_id not in (SELECT request_id FROM apps.tmp_program_on_hold) ;


Workflow  Quries:

SELECT COUNT (*), machine, process

    FROM gv$session

   WHERE program LIKE 'JDBC%'

GROUP BY machine, process

ORDER BY 2 ASC


Workflow processed Query


select MSG_STATE, count(*) from applsys.aq$wf_notification_out group by MSG_STATE; 





imap services are fine
==============

spawn telnet auohswnct09.oracleoutsourcing.com 143
Trying 141.146.174.10...
Connected to auohswnct09.oracleoutsourcing.com (141.146.174.10).
Escape character is '^]'.
* OK Dovecot ready.
1 login wfpwncti wfpwncti
1 OK Logged in.
1 select INBOX
* FLAGS (\Answered \Flagged \Deleted \Seen \Draft NonJunk)
* OK [PERMANENTFLAGS (\Answered \Flagged \Deleted \Seen \Draft NonJunk \*)] Flags permitted.
* 1 EXISTS
* 0 RECENT
* OK [UNSEEN 1] First unseen.
* OK [UIDVALIDITY 1300614072] UIDs valid
* OK [UIDNEXT 172901] Predicted next UID
1 OK [READ-WRITE] Select completed.
1 select PROCESS
* FLAGS (\Answered \Flagged \Deleted \Seen \Draft)
* OK [PERMANENTFLAGS (\Answered \Flagged \Deleted \Seen \Draft \*)] Flags permitted.
* 1229 EXISTS
* 1229 RECENT
* OK [UIDVALIDITY 1340822807] UIDs valid
* OK [UIDNEXT 1376] Predicted next UID

1 OK [READ-WRITE] Select completed.
1 select DISCARD
* FLAGS (\Answered \Flagged \Deleted \Seen \Draft)
* OK [PERMANENTFLAGS (\Answered \Flagged \Deleted \Seen \Draft \*)] Flags permitted.
* 214 EXISTS
* 214 RECENT
* OK [UIDVALIDITY 1340822807] UIDs valid
* OK [UIDNEXT 407] Predicted next UID
1 OK [READ-WRITE] Select completed.
IMAP Validation Success



The messages are processed successfully
==========
SQL> select MSG_STATE, count(*) from applsys.aq$wf_notification_out group by MSG_STATE;

MSG_STATE COUNT(*)
---------------- ----------
PROCESSED 7650



No messages are stuck in out bound queue
=================
SQL> select count(*) from applsys.wf_notification_out where state=0;

COUNT(*)
----------
0


Workflow Quries for JDBC Connections:

select *
from gv$session
where program like '%JDBC%'
and last_call_et > 1800
and status = 'ACTIVE'
order by last_call_et asc



select s.process, s.sid, t.sql_text
from gv$session s, gv$sql t
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and s.program like '%JDBC%'
and s.last_call_et > 1800
and s.status = 'ACTIVE'


To Find out the Report Builder Version through command line:


Findings
========
$ rwrun help=yes | grep Release
REP-0503: You did not specify the name of a report.
Report Builder: Release 10.1.2.3.0 - Production on Fri Nov 2 02:20:56 2012
$ pwd
/pwncti/product/1012/OPatch
$ opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc


OPatch succeeded.
$ opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 11669923

Workflow notification mailer Useful Scripts.

 In Which node Running Workflow Notification
SQL> select target_node from fnd_concurrent_queues where concurrent_queue_name like 'WFMLRSVC%';
TARGET_NODE
------------------------------
SRI_FIN02
Current workflow mailer log file location

SQL> select fl.meaning,fcp.process_status_code, 
decode(fcq.concurrent_queue_name,'WFMLRSVC','maile r container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and
fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;

Workflow log_file  path
$APPLCSF/$APPLLOG/FNDCPGSC*.txt
bash-4.2$ pwd
/u01/oracle/prod/inst/apps/PROD02/logs/appl/conc/log
bash-4.2$ ls -lrt FNDC*
-rw-r--r--    1 oracle   dba        28710170 Apr 16 08:52 FNDCPGSC167995.txt
-rw-r--r--    1 oracle   dba       779053450 Apr 16 08:52 FNDCPGSC167990.txt
-rw-r--r--    1 oracle   dba        58267057 Apr 16 08:52 FNDCPGSC167989.txt
-rw-r--r--    1 oracle   dba          344758 Apr 16 09:10 FNDCPGSC168589.txt
-rw-r--r--    1 oracle   dba        34715995 Apr 16 09:10 FNDCPGSC168588.txt
-rw-r--r--    1 oracle   dba          296340 Apr 16 09:10 FNDCPGSC168587.txt

bash-4.2$ tail -l FNDCPGSC167989.txt


Check workflow mailer service current status
bash-4.2$ sqlplus

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Apr 16 10:17:16 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: apps
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select running_processes
    from apps.fnd_concurrent_queues
   where concurrent_queue_name = 'WFMLRSVC';  2    3

RUNNING_PROCESSES
-----------------
                1


  Number of running processes should be greater than 0
 Find current mailer status
  sqlplus apps/<apps password>
  select component_status
    from apps.fnd_svc_components
   where component_id = 
        (select component_id
           from apps.fnd_svc_components
          where component_name = 'Workflow Notification Mailer');
COMPONENT_STATUS ------------------------------ RUNNING  
Possible values:
  RUNNING
  STARTING
  STOPPED_ERROR
  DEACTIVATED_USER
  DEACTIVATED_SYSTEM
Stop notification mailer
  sqlplus apps/<apps password>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin
       -- Find mailer Id
       -----------------
       select component_id
         into m_mailerid
         from fnd_svc_components
        where component_name = 'Workflow Notification Mailer';
       --------------
       -- Stop Mailer
       --------------
       fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
       commit;
  end;
  /

Start notification mailer

  sqlplus apps/<apps password>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin
       -- Find mailer Id
       -----------------
       select component_id
         into m_mailerid
         from fnd_svc_components
        where component_name = 'Workflow Notification Mailer';
       --------------
       -- Start Mailer
       --------------
       fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
       commit;
  end;
  /




























How to Upload & Download Workflow (wft) Files in R12
























WFLOAD Utility :-















 oracle has provided WFLOAD  for workflow files. Below are the available options -















To upgrade      - WFLOAD apps/pwd 0 Y UPGRADE filename.wft














To upload        - WFLOAD apps/pwd 0 Y UPLOAD filename.wft









To force           - WFLOAD apps/pwd 0 Y FORCE filename.wft








To download   - WFLOAD apps/pwd 0 Y DOWNLOAD filename.wft 











WorkflowDOWNLOADWFLOAD apps/$passwd 0 Y DOWNLOAD XXWFITEMTYPE.wft



UPGRADEWFLOAD apps/$pwd 0 Y UPGRADE xxwfitemtype.wft



FORCEWFLOAD apps/$pwd 0 Y FORCE xxwfitemtype.wft

















Log location same place 



Use pwd command and find log file in same location.
















Location wft files are stored in server:-







 $PRODUCT_TOP/patch/115/import/<LANG> directory.













  Standard Concurrent Program – Run the program Workflow Definitions Loader using sysadmin responsibility (you can add  this program to any of responsibility and run from there).



 This program has 3 parameters as below







1       Mode – Upload/Download







2       file - /staging/sandeep/Project/PAPROWF.wft 



                  (wft file name with Full Path. Make sure file is available at given path for UPLOAD Mode)







3  I   Item Type – Item type Name for above file (This can be get from workflow builder. Open your wft file in workflow builder and create any process. The first attribute will be Item Type Name).


Oracle Password expiry notification



set pagesize 500
set linesize 200
set trimspool on
column "EXPIRE DATE" format a15
select username as "USER NAME", expiry_date as "EXPIRE DATE", account_status
from dba_users
where expiry_date < sysdate+3
and account_status IN ( 'OPEN', 'EXPIRED(GRACE)' )
order by account_status, expiry_date, username;


How to delete Trace file or .trm file before 5 days
$ cd /ora11/PROD/db/tech_st/11.1.0/admin/PROD/diag/rdbms/prod/PROD/trace
$ find . -name '*.trm' -mtime +5 | xargs rm -f

$ find . -name '*.trc' -mtime +5 | xargs rm -f

How to delete expired archive log files using rman?

The following commands will helpful to delete the expired archive log files using Oracle Recovery Manager(RMAN).Connect to the Rman prompt and try the bellow commands.
RMAN>list expired archivelog all;
RMAN>crosscheck archivelog all;
RMAN>delete noprompt expired archivelog all;

RMAN>list expired archivelog all;
Posted by on

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)