Technology News and Usefull Informations: December 2012

Monday, December 17, 2012

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;

Sunday, December 16, 2012

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)

Friday, December 14, 2012

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

FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

RETURN VARCHAR2;

END XXARTO_GET_PWD;

CREATE OR REPLACE PACKAGE BODY XXARTO_GET_PWD

FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

RETURN VARCHAR2

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'),

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,

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'),

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,

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';

Technology News and Usefull Informations

Labels

Search This Blog

Monday, December 17, 2012

Tablespace Usage

Log Switch Frequency

Sunday, December 16, 2012

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

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

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

Friday, December 14, 2012

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

Create Package

Find apps password in 11i

Find apps password in R12

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

Find application user password (for exaample SYSADMIN) in R12

Visitors(Since 17 Dec 2012)

Total Pageviews

Popular Posts