Tablespace Usage

SELECT d.status,
       d.tablespace_name,
       d.contents TYPE,
       d.extent_management extent_mgt,
       d.segment_space_management segment_mgt,
       NVL (a.bytes, 0) ts_size,
       NVL (f.bytes, 0) free,
       NVL (a.bytes - NVL (f.bytes, 0), 0) used,
       TO_CHAR (
          TRUNC (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')
UNION ALL
SELECT d.status,
       d.tablespace_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 (t.bytes, 0), 0) free,
       NVL (t.bytes, 0) used,
       TO_CHAR (TRUNC (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'
ORDER BY 9 DESC;

Log Switch Frequency

A log switch occurs when LGWR stops writing to one redo log group and starts writing to another. By default, a log switch occurs automatically when the current redo log file group fills.

More frequently log switch will cause performance problem in oracle database. DBA has to monitor log switch frequency and per hour this value  should be less than 6 or 8.

Below script will give log switch frequency map for oracle database.
 SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  v$log_history  a
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) desc

Also you can check average log switch per minute value with query below

WITH redo_log_switch_times AS
     (SELECT   sequence#, first_time,
               LAG (first_time, 1) OVER (ORDER BY first_time) AS LAG,
                 first_time
               - LAG (first_time, 1) OVER (ORDER BY first_time) lag_time,
                 1440
               * (first_time - LAG (first_time, 1) OVER (ORDER BY first_time)
                 ) lag_time_pct_mins
          FROM v$log_history
      ORDER BY sequence#)
SELECT round(AVG (lag_time_pct_mins),2) avg_log_switch_per_min
  FROM redo_log_switch_times;

Purging Strategy in Oracle E-Business Suite -3- (FNDSCPRG)

Concurrent Program Shortname is  FNDSCPRG
Program Name is "Purge Signon Audit Data Concurrent Program"

This concurrent program purge tables listed below.

FND_LOGIN_RESP_FORMS
FND_LOGIN_RESPONSIBILITIES
FND_LOGINS
FND_UNSUCCESSFUL_LOGINS

Purging Strategy in Oracle E-Business Suite -2- (FNDCPPUR)

Concurrent Program shortname is FNDCPPUR

Program name is "Purge Concurrent Request and/or Manager Data"

This concurrent program purge tables listed below.

FND_CONCURRENT_REQUESTS      

FND_RUN_REQUESTS       

FND_CONC_REQUEST_ARGUMENTS

FND_DUAL                 

FND_CONCURRENT_PROCESSES 

FND_CONC_STAT_LIST     

FND_CONC_STAT_SUMMARY    

FND_ENV_CONTEXT

Recommendation is scheduling this program every day with purging 30 days older data. To realize this schedule program with parameters ENTITY=ALL and Mode=Age and value is 30

Do not forget that purge concurrent request data will cause losing audit information which are used Signon Audit Concurrent Request Report.

As additional info with different values of ENTITY paramter will change purged tables.


ENTITY = ALL       : Fnd_Concurrent_Processes
                                  Fnd_Dual
                                  Fnd_Concurrent_Requests,
                                  Fnd_Run_Requests
                                  Fnd_Conc_Request_Arguments
                                  Fnd_Dual
                                  Fnd_Context_Env
                                  Deletes concurrent requests' log and out files from OS

ENTITY = MANAGER   : Fnd_Concurrent_Processes
                                          Fnd_Dual
                                          Deletes concurrent manager log files from OS

ENTITY = REQUEST     : Fnd_Concurrent_Requests,
                                          Fnd_Run_Requests
                                          Fnd_Conc_Request_Arguments
                                          Fnd_Dual
                                          Deletes concurrent requests' log and out files from OS

Oracle Support Document ID :  104282.1

Purging Strategy in Oracle E-Business Suite -1-(FNDLGPRG)

Purging Strategy in Oracle E-Business Suite -1- (FNDLGPRG)

Concurrent Program shortname is FNDLGPRG

Program name is;
"Purge Logs and Closed System Alerts" in R12
"Purge Debug Log and System Alerts" in 11i

This concurrent program purge tables listed below.

FND_EXCEPTION_NOTES

FND_OAM_BIZEX_SENT_NOTIF

FND_LOG_METRICS

FND_LOG_UNIQUE_EXCEPTIONS

FND_LOG_EXCEPTIONS

FND_LOG_MESSAGES

FND_LOG_TRANSACTION_CONTEXT

FND_LOG_ATTACHMENTS

Recommendation is scheduling this program every day with purging 10 days older data.

If these tables are very big as workaround truncate will be possible for these table

truncate table applsys.FND_EXCEPTION_NOTES;
truncate table applsys.FND_OAM_BIZEX_SENT_NOTIF;
truncate table applsys.FND_LOG_METRICS;
truncate table applsys.FND_LOG_UNIQUE_EXCEPTIONS;
truncate table applsys.FND_LOG_EXCEPTIONS;
truncate table applsys.FND_LOG_MESSAGES;
truncate table applsys.FND_LOG_TRANSACTION_CONTEXT;
truncate table applsys.FND_LOG_ATTACHMENTS;

Purging Strategy in Oracle E-Business Suite -2-(FNDCPPUR)

Find Password in Oracle E-Business Suite 11i and R12

This scripts allows to view the passwords which are stored encrypted in fnd_user table.

You can use different sqls to find apps password and application user passwords.

Create Package

CREATE OR REPLACE PACKAGE XXARTO_GET_PWD

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2;

END XXARTO_GET_PWD;

CREATE OR REPLACE PACKAGE BODY XXARTO_GET_PWD

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2

   AS

      LANGUAGE JAVA

      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt

(java.lang.String,java.lang.String) return java.lang.String';

END XXARTO_GET_PWD;

Find apps password in 11i

SELECT (SELECT XXARTO_GET_PWD.Decrypt (

                  UPPER( (SELECT UPPER (Fnd_Profile.VALUE ('Guest_User_Pwd'))

                            FROM DUAL)),

                  Usertable.Encrypted_Foundation_Password)

          FROM DUAL)

          AS Apps_Password

  FROM applsys.Fnd_User Usertable

 WHERE Usertable.User_Name LIKE

          UPPER( (SELECT SUBSTR (

                            Fnd_Profile.VALUE ('Guest_User_Pwd'),

                            1,

                            INSTR (Fnd_Profile.VALUE ('Guest_User_Pwd'), '/')

                            - 1)

                    FROM DUAL));
 

Find apps password in R12

 SELECT (SELECT XXARTO_GET_PWD.Decrypt (

                  Fnd_Web_Sec.Get_Guest_Username_Pwd,

                  Usertable.Encrypted_Foundation_Password)

          FROM DUAL)

          AS Apps_Password

  FROM applsys.Fnd_User Usertable

 WHERE Usertable.User_Name LIKE

          (SELECT SUBSTR (

                     Fnd_Web_Sec.Get_Guest_Username_Pwd,

                     1,

                     INSTR (Fnd_Web_Sec.Get_Guest_Username_Pwd, '/') - 1)

             FROM DUAL);
 

Find application user password (for exaample SYSADMIN) in 11i

SELECT Usertable.User_Name,

       (SELECT XXARTO_GET_PWD.Decrypt (

                  UPPER( (SELECT (SELECT XXARTO_GET_PWD.Decrypt (

                                            UPPER( (SELECT UPPER(Fnd_Profile.VALUE('Guest_User_Pwd'))

                                                      FROM DUAL)),

                                            Usertable.Encrypted_Foundation_Password)

                                    FROM DUAL)

                                    AS Apps_Password

                            FROM applsys.Fnd_User Usertable

                           WHERE Usertable.User_Name LIKE

                                    UPPER( (SELECT SUBSTR (

                                                      Fnd_Profile.VALUE (

                                                         'Guest_User_Pwd'),

                                                      1,

                                                      INSTR (

                                                         Fnd_Profile.VALUE (

                                                            'Guest_User_Pwd'),

                                                         '/')

                                                      - 1)

                                              FROM DUAL)))),

                  Usertable.Encrypted_User_Password)

          FROM DUAL)

          AS Encrypted_User_Password

  FROM Applsys.Fnd_User Usertable

 WHERE Usertable.User_Name LIKE UPPER ('&Username');
 

Find application user password (for exaample SYSADMIN) in R12

SELECT Usr.User_Name,

       Usr.Description,

       XXARTO_GET_PWD.Decrypt (

          (SELECT (SELECT XXARTO_GET_PWD.Decrypt (

                             Fnd_Web_Sec.Get_Guest_Username_Pwd,

                             Usertable.Encrypted_Foundation_Password)

                     FROM DUAL)

                     AS Apps_Password

             FROM applsys.Fnd_User Usertable

            WHERE Usertable.User_Name =

                     (SELECT SUBSTR (

                                Fnd_Web_Sec.Get_Guest_Username_Pwd,

                                1,

                                INSTR (Fnd_Web_Sec.Get_Guest_Username_Pwd,

                                       '/')

                                - 1)

                        FROM DUAL)),

          Usr.Encrypted_User_Password)

          Password

  FROM applsys.Fnd_User Usr

 WHERE Usr.User_Name = '&User_Name';

Resize or Shrink Temporary Tablespace

If temporary tablespace size very large because of some bad queries or some other unexpected sorting operations, as maintanance DBA should shrink and reclaim that extra space. First, try resize the tempfile.

SQL> alter database tempfile '/oracle/oradata/ORCL/test01.dbf' resize 500M;

and faced error ORA-03297: file contains used data beyond request RESIZE value.

There are several methods to reclaim unused space depend on Oracle versions.

Method I	Database Version < 9i
	Database Version >=9i and related tablespace is not default temporary tablespace
Method II	Database Version >=9i and and related tablespace is default temporary tablespace
Method III	Database Version >=9i
Method IV	Database Version >=11g

Note: It is advisable to realize this operations during off hours. Otherwise  If tempfile is used then could not drop and give error ORA-25152: TEMPFILE cannot be dropped at this time. In this situation there are 2 scripts depend on oracle versions to find who use the temporary  segments.

Version <9i
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text

  FROM v$session a, v$sort_usage b, v$sqlarea c
  WHERE b.tablespace = 'TEMP'
  and a.saddr = b.session_addr
  AND c.address= a.sql_address
  AND c.hash_value = a.sql_hash_value
  ORDER BY b.tablespace, b.blocks; 
Version >=9i   
   SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text
    FROM v$session a, v$tempseg_usage b, v$sqlarea c
    WHERE b.tablespace = 'TEMP'
     and a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    ORDER BY b.tablespace, b.blocks;

Method I : 

Drop and recreate temporary tablespace.

SQL> DROP TABLESPACE temp;

SQL> CREATE TEMPORARY TABLESPACE TEMP
2 TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' SIZE 500M
3 AUTOEXTEND ON NEXT 100M MAXSIZE 2048M
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Method II : 

Drop and recreate temporary tablespace when TEMP is default temporary tablespace.

If TEMP is default temporary tablespace and try to drop this tablespace like Method I faced error ORA-12906: cannot drop default temporary tablespace

In this situation steps like below

1. Create another temporary tablespace(create TEMP2)

2. Make new temporary tablespace is default (TEMP2 default)

3. Drop old temporary tablespace (drop TEMP)

4. Create temporary tablespace with old name (create TEMP)

5. Make new created tablespace is default (TEMP default)

6. Drop tablespace which created in step 1 (drop TEMP2)

SQL> CREATE TEMPORARY TABLESPACE TEMP2

2 TEMPFILE '/oracle/oradata/ORCL/temp02.dbf' SIZE 100M

3 AUTOEXTEND ON NEXT 10M MAXSIZE 2048M

4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

SQL> CREATE TEMPORARY TABLESPACE TEMP 2 TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' SIZE 500M 3 AUTOEXTEND ON NEXT 100M MAXSIZE 2048M 4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

SQL> DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;

Method III : 

This method drop a tempfile. Only valid for >=9i version.First find tempfiles from v$tempfile and then drop

SQL> ALTER DATABASE TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' DROP INCLUDING DATAFILES;

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M;

Method IV: 

Shrink temporary tablespace and/or tempfile. Only valid for >=11g

ALTER TABLEPSACE TEMP SHRINK SPACE
or
ALTER TABLESPACE TEMP SHRINK TEMPFILE '................' KEEP 250M;

DBConsole Shows Listener and Database Down If Not Using Default Values

Oracle Database 10g dbconsole is configured for default port usage and default listener name.
(Port=1521, listener name is LSTENER)
If database uses different port number and listener name, everything will shown down in dbconsole.

To solve this issue action plan is below.

1. emcl stop dbconsole
2. Change related parts of mentioned files 
1. $ORACLE_HOME/hostname_SID/sysman/config/emoms.properties
2. $ORACLE_HOME/hostname_SID/sysman/emd/targets.xml
3. emctl start dbconsole

How To Determine Running Linux is Vmware or not

There are several ways to determine that running system is vmware or not. 

1. dmidecode -s system-manufacturer
2. view  /sys/class/dmi/id/sys_vendor
3. lspci | grep -i vmware
4. grep -i vmware /proc/scsi/scsi /proc/ide/*/model
5. dmesg |grep Vmware
6. rpm -qa | grep -i vmware

Change Date and Time in Linux

To change date and time in Linux use command below

date -s "MM/DD/YYYY HH24:MM:SS"

Example : To set 20 November 2011 Hour 11:25:23 AM
date -s "11/20/2011 11:25:23"

Kill Spesific User Process in Linux

To kill spesific user's all process use command below

kill -9 `ps -ef | grep <osuser> | grep -v grep | awk '{print $2}'`

replace <osuser> with related user name, example oracle, applora etc.

Change Apps Password For Oracle EBS 11i and R12 with FNDCPASS

Oracle E-Business Suite provides a command line utility called FNDCPASS to change passwords.
4 mode is avaliable for FNDCPASS, details below
Note: To use this command line utility set application enviroment file (for example APPSORA.env)

1. SYSTEM ==> To change the APPS and APPLSYS schema password
2. ORACLE ==> To change single Application Schema password (other than APPS/APPLSYS) 
3. ALLORACLE === To change all ORACLE schema passwords
4. USER ==> To change Oracle Applications user password

1. Usage: FNDCPASS <logon> 0 Y <system/password> SYSTEM <username> <new_password>
    Example:  FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME
    Note:  Passwords for APPLSYS and the APPS schemas must be the same.
              If you change the password for one, FNDCPASS automatically changes the others.
             Change password for APPS and APPLSYS when concurrent manager is down.
    After changing APPLSYS and APPS password in 11i should change hardcoded password manually in wdbsvr.app file under $IAS_ORACLE_HOME/Apache/modplsql/cfg. Also recommend to run autoconfig
Also there are some files to modify listed below but for new versions of 11.5.x is not necessary

• $FND_TOP/resource/wfmail.cfg
• $COMMON_TOP/admin/scripts/<context_name>/adcmctl.sh
• $AD_TOP/admin/template/CGIcmd.dat
• $OA_HTML/bin/appsweb.cfg

2. Usage: FNDCPASS <logon> 0 Y <system/password> ORACLE <username> <new_password>

     Example: FNDCPASS apps/apps 0 Y system/manager ORACLE AP passwd1

3. Usage: FNDCPASS <logon> 0 Y <system/password> ALLORACLE <new_password>
    Example: FNDCPASS apps/apps 0 Y system/manager ALLORACLE WELCOME
    Note: Usernames must appear in the FND_USER or FND_ORACLE_USERID tables.
             FNDCPASS utility and ALLORACLE functionality was designed for applications users/schemas.
             Other users passwords must be manually changed(Examle: ABM,CSS,DBSNMP,EVM,SYS,etc.)

4. Usage: FNDCPASS <logon> 0 Y <system/password> USER <username> <new_password>
    Example : FNDCPASS apps/apps 0 Y system/manager USER SYSADMIN WELCOME


Oracle Support Document ID :  437260.1 , 1306938.1