Oracle World

Application startup and shutdown scripts [sreenidba@sreenidba scripts]$  cd $ADMIN_SCRIPTS_HOME 1.adalnctl.sh   2.adautocfg.sh         3.adformsctl.sh    4.adoacorectl.sh   5.adopmnctl.sh    6.adstpall.sh  7.adapcctl.sh   8.adcmctl.sh      9.adformsrvctl.sh  10.adoafmctl.sh     11.adpreclone.pl   12.adstrtal.sh  1.adalnctl.sh  : script used to start the application listener services. logfile located in LOG_HOME or INST_TOP/admin/log adalnctl.txt To know the status of apps listner  [sreenidba@sreenidba log]$ adalnctl.sh status adalnctl.sh version 120.3 Checking status for listener process APPS_UAT. LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 30-AUG-2017 12:47:58 Copyright (c) 1991, 2004, Oracle.  All rights reserved. Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=sreenidba)(Port=1600)) STATUS of the ...

Query to find running concurrent programs SQL>SELECT vs.username,  vs.osuser,  vh.sid locking_sid,  vs.status status,  vs.module module,  vs.program program_holding,  jrh.job_name,  vsw.username,  vsw.osuser,  vw.sid waiter_sid,  vsw.program program_waiting,  jrw.job_name,  'alter system kill session ' || ''''|| vh.sid || ',' || vs.serial# || ''';'  "Kill_Command" FROM v$lock vh,  v$lock vw,  v$session vs,  v$session vsw,  dba_scheduler_running_jobs jrh,  dba_scheduler_running_jobs jrw WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2  FROM v$lock  WHERE request = 0  INTERSECT  SELECT id1, id2  FROM v$lock  WHERE lmode = 0)  AND vh.id1 = vw.id1  AND vh.id2 = vw.id2  AND vh.request = 0  AND vw.lmode = 0  AND vh.sid = vs.sid  AND vw.sid = vsw.sid  AND vh.sid = jrh.session_id(+)  AND vw.s...

Query to find table locks SQL>SELECT vs.username,  vs.osuser,  vh.sid locking_sid,  vs.status status,  vs.module module,  vs.program program_holding,  jrh.job_name,  vsw.username,  vsw.osuser,  vw.sid waiter_sid,  vsw.program program_waiting,  jrw.job_name,  'alter system kill session ' || ''''|| vh.sid || ',' || vs.serial# || ''';'  "Kill_Command" FROM v$lock vh,  v$lock vw,  v$session vs,  v$session vsw,  dba_scheduler_running_jobs jrh,  dba_scheduler_running_jobs jrw WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2  FROM v$lock  WHERE request = 0  INTERSECT  SELECT id1, id2  FROM v$lock  WHERE lmode = 0)  AND vh.id1 = vw.id1  AND vh.id2 = vw.id2  AND vh.request = 0  AND vw.lmode = 0  AND vh.sid = vs.sid  AND vw.sid = vsw.sid  AND vh.sid = jrh.session_id(+)  AND vw.sid = jrw.session...

in Linux: --------------------------------- take the backup of jsp files in COMMON_TOP/_pages cd $COMMON_TOP/ [sreenidba@sreenidba comn]$ ls admin  clone  cz  exclude.xml  java  mvtaglib  _pages  temp  _TldCache  util  webapps [sreenidba@sreenidba comn]$ mv _pages _pages_backup create ne directory under common_top [sreenidba@sreenidba comn]$ mkdir _pages Now run the ojspCompile.pl script from FND_TOP/patch/115/bin cd $FND_TOP/patch/115/bin [sreenidba@sreenidba bin]$ pwd /d04/sreenidba/apps/apps_st/appl/fnd/12.0.0/patch/115/bin [sreenidba@sreenidba bin]$ ls ojspCompile.pl ojspCompile.pl [sreenidba@sreenidba bin]$ perl ojspCompile.pl -compile -flush -p 2 perl ojspCompile.pl –compile –flush -p 2(here "P" represents number of processes) Now check the new jsp files created or not in COMMON_TOP/_jsppages [sreenidba@sreenidba _pages]$ pwd /d04/sreenidba/apps/apps_st/comn/_pages [sreenidba@sreenidba _pages]$ csfwAddr.class...

[appldev@sreendba admin]$ sqlplus apps/apps SQL*Plus: Release 10.1.0.5.0 - Production on Tue Aug 29 11:21:19 2017 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select (case length(address) when 16 then '64-bit Oracle' when 8 then '32-bit Oracle' when 32 then '128-bit Oracle' end) Wordsize from v$sql where rownum <2 /   2    3    4    5    6    7 WORDSIZE ------------------------------------------ 64-bit Oracle SQL>

findout number of user process [sreenidba@sreenidba ~]$ ps -ef|grep sreenidba|wc -l 456 [sreenidba@sreenidba ~]$ ps -ef|grep sreenidba|wc -l 266 [sreenidba@sreenidba ~]$ find the disk space [root@sreenidba ~]# df -h Filesystem                         Size  Used Avail Use% Mounted on sreeni/sda2                           25G   17G  7.0G  71% / tmpfs                               32G  283M   32G   1% sreeni/shm sreeni/sda1                          788M   46M  702M   7% /boot sreeni/sda6                          6.9G  224M  6.4G   4% /tmp sreeni/sda5           ...

Query to find schema size in GB SQL>select    owner,    sum(bytes)/1024/1024/1024 schema_size_gig from    dba_segments group by    owner; --------------------------------------------------------------------------- OWNER                          SCHEMA_SIZE_GIG ------------------------------ --------------- APPLSYS                             111.531372 AK                                   .35546875 BSC                                 .297851563 CRP                                 .071289063 IGS                 ...

Changing Apps Password /* CHANGE APPLICATION USER PASSWORD USING FNDCPASS*/ FNDCPASS apps/APPS_PASSWORD 0 Y system/SYSTEM_PASSWORD USER USER_WHOSE_PASSWORD_WILL_BE_CHANGED NEWPASSWORD Example: FNDCPASS apps/apps 0 Y system/manager USER SYSADMIN sysadmin /* CHANGE "APPS" USER PASSWORD USING FNDCPASS in "R11"*/ FNDCPASS apps/apps 0 Y system/SYSTEM_PASSWORD SYSTEM APPLSYS NEWPASSWORD Note: Changing apps password will also change applsys password. apps and applsys passwords are always the same.. /* CHANGE "APPS" USER PASSWORD USING FNDCPASS in "R12"*/ FNDCPASS apps/APPS_PASSWORD 0 Y system/SYSTEM_PASSWORD SYSTEM APPLSYS NEW_PASSWORD

Find application accessing users in apps r12. open apache log file find the user names and server names. goto log_home cd $LOG_HOME/10.1.3/Apache/ grep the apache log file find out application accessing usernames access_log.1503100800 [sreenidba@sreenidba Apache]$ grep -i username access_log.1503100800 10.20dfvd fv [19/Aug/2017:08:46:29 +0530] "GET /OA_HTML/AppsLocalLogin.jsp?langCode=US& username=7df1 H TTP/1.1" 302 780 0 "-" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/7.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.3sdudedia Center PC 6.0; Tablet PC 2.0; MANM)" 10.20.127.65 - - [19/Aug/2017:08:46:37 +0530] "POST /OA_HTML/OA.jsp?page=/oracle/apps/fnd/sso/login/webui/MainLoginPG&_ri=0&_ti=1542395888&language_code=US&requestUrl=& username=75dpd langCode=US&oapc=6&oas=XynOEO7BE53L9TLf6mB9jA.. HTTP/1.1" 302 297 1 "http://sreenidba.sreenidbandia.com:sreenidba/OA_HTML/RF....

1.Creating tablespaces, Adding and Resizing datafiles SQL>create tablespace <Tablespace_name> datafile '<datafilelocation>' size 128m autoextend on next 128m maxsize unlimited; SQL>create tablespace USERS datafile '/soadev/oradata/soadohr2/datafile/users01.dbf' size 128m autoextend on next 128m maxsize unlimited ; 2.Resing the datafile SQL> alter database datafile'/d01/oracle/PROD/proddata/apps_ts_tx_idx538.dbf' resize 6144M; 3.adding datafile SQL>alter tablespace <Tablespace Name> add datafile <path of the datafile> size 500m; SQL>alter tablespace tbs add datafile '/d01/oracle/prod/tbs05.dbf' size 500m; 4.create tablespace in RAC Environment SQL>create tablespace <TABLESPACE_NAME> datafile size 128m autoextend on next 128m maxsize unlimited; SQL>create tablespace USERS datafile '+DATA' size 128m autoextend on next 128m maxsize unlimited ; 5.Add datafile in RAC Environmen...

1.crosscheck backup 2.crosscheck archivelog all 3.listout expired backup and expired archivelog all Connect Rman: [root@ltkbwlndb01 ~]# su - oraprod [oraprod@ltkbwlndb01 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Mon Aug 21 03:01:09 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. connected to target database: PROD (DBID=191713860) RMAN> RMAN> crosscheck backup device type disk; using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=65 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=175 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=413 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=479 device type=DISK allocated channel: ORA_DISK_5 channel ORA_DISK_5: SID=699 device type=DISK allocated channel: ORA_DISK_6 channel ORA_DISK_6: SID=850 devic...

Description: user process and server processes when ever user connnect's to the oracle server, then it will create one session. that session is colled as user session. session is nothing but user and server prosesses transactons between is colled as a session. Oracle instance:   An Oracle Instance consists of two different sets of components: SGA:System Global Area SGA(system global area or shared global area) contains memory structures and background processes. Memory Structures 1.Dabase Buffer cache 2.Redolog Buffer 3.Shared pool 4.javapool and largepool Background Processes 1.DBwn 2.ckpt 3.smon 4.pmon 5.arcn 6.lgwr Memory structures: 1.Database Buffer Cache: The database buffer cache holds blocks of data from disk that have been recently read to satisfy a SELECT statement or that contain modified blocks that have been changed or added from a DMLstatement.  2.Redo Log guffer The redo log buffer holds the most recent changes to the data b...

Scripts To Check Tablespace used and free space  set lines 132 set pages 60 set feedback on column name format a15 SELECT a.tablespace_name, (a.BYTES / 1024/1024)  AS total_MB, ROUND(((a.BYTES-b.BYTES)/1024/1024) ,2) AS used_MB, (b.BYTES / 1024/1024)  AS free_MB, ROUND (((a.BYTES - b.BYTES) / a.BYTES) * 100, 2) AS percent_used, ROUND (100 - ((a.BYTES - b.BYTES) / a.BYTES) * 100, 2) AS percent_free FROM (SELECT   tablespace_name, SUM (BYTES) BYTES FROM dba_data_files GROUP BY tablespace_name) a, (SELECT   tablespace_name, SUM (BYTES) BYTES, MAX (BYTES) largest FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name union select sh.tablespace_name, sum(sh.bytes_used + sh.bytes_free)/1048576 total, sum(sh.bytes_used)/1048576 used, sum(sh.bytes_free)/1048576 free, sum(sh.bytes_used) /sum(sh.bytes_used + sh.bytes_free) *100 percent_used, sum(sh.bytes_free) /sum(sh.byte...

Creating Recovery catalog: Catalog database is :UAT Target database is :DEV Enter the Tns entries in DEV and UAT. Tns entries in DEV $cd $TNS_ADMIN edit tnsname.ora Here enter the tns entries of UAT instance [oradev@sreenidba DEV_sreenidba]$vi tnsnames.ora UAT=         (DESCRIPTION=                 (ADDRESS=(PROTOCOL=tcp)(HOST=sreeni.dba.sreenidba.com)(PORT=1532))             (CONNECT_DATA=                 (SERVICE_NAME=UAT)                 (INSTANCE_NAME=UAT)             )         ) TNS Entries in UAT: here enter the tns entries DEV instance [orauat@sreenidba UAT_sreenidba]$vi tnsnames.ora DEV=         (DESCRIPTION=                 (ADDRESS=(PROTOCOL=tcp)(HOST=sreenidba..sreenidba.com)(PO...

Log on sysadmin user Navigate: à  Setup Mfg Responsibility à Click on Setup<Inventory>Transactions à à Interface Managers Click on interface Managers à

Step1 :   Check Database invalids and take the backup of database invalid objects. SQL>select count(*) from dba_objects where status like’INVALID’; SQL>create table dba_objects_backup as select * from dba_objects where status like ‘INVALID’; Step 2: Check The given patch applied or not from ad_bugs table. SQL>select creation_date, bug_number from ad_bugs where bug_number like’<Patch_Number>’; Step 3: Down the Application services. adstpall.sh Check FNDLIBR executable  $ps –ef|grep FNDLIBR $ps –ef|grep applprod|grep FNDLIBR Step 4: Enable maintenance mode using adadmin. By Enabling maintenance mode workflow bussiness events will be disabled auotmatically and end user's can't access the business. During a Maintenance mode downtime, user login is restricted.  Users are redirected to a system downtime URL, which informs them that the maintenance session is in progress.  $adadmin ...