Friday, 30 May 2014

Spotlight on SQL Server Enterprise



Spotlight on SQL Server Enterprise

Spotlight on SQL Server is a powerful database diagnostic and resolution tool. Its unique user interface provides you with an intuitive, visual representation of the activity on a SQL server. Graphical flows illustrate the rate at which data is moving between server components. Icons display the value of key statistics and alarms. Alarms inform you of bottlenecks and other potential problems. Drilldowns show detailed information on all parts of your SQL Server.
Spotlight integrates data from various sources into a single display. It seamlessly combines data from SQL Server system tables, SQL commands, performance monitor counters, and the Windows registry. Spotlight diagnoses not just your SQL Server, but also the Windows machine that SQL Server is running on.
Spotlight on Windows is now packaged and integrated with Spotlight on SQL Server. The purchase of Spotlight on SQL Server on each licensed server includes Spotlight on Windows for free.
With Spotlight, diagnostic data can be collected continuously, even when Spotlight is not open on the desktop. A Diagnostic Server can perform data collection continuously from a middleware machine, ready for Spotlight to display whenever Spotlight is started on any client machine.
Business benefits include:
Data collected and alarms evaluated on a 24x7 basis, even when no Spotlight clients are running.
Identical data and alarms provided to all users, providing a consistent view across the enterprise.
Diagnostic data is saved in one place (on the Diagnostic Server), avoiding unnecessary replication of data.
Diagnostic Server reduces load on databases and operating systems by collecting data once and sharing it between all connected Spotlight clients.
Long-term historical data can be stored in a repository for future analysis and planning purposes. SQL Server professionals can report off the data for a deeper understanding of their performance issues.
Diagnostic Server configuration is centralized and applied to all connections. Individual connections can then be customized as required.
SQL Analysis captures SQL from a SQL Server based on a collection filter you specify and allows you to quickly get a picture of the most expensive SQL running on the server both from real-time and historical perspectives.
Operating System data is collected only once, even if multiple instances are being monitored on that particular host.

Tuesday, 27 May 2014

How to add OID/OVD/WebLogic/Middleware Target in OEMGC 12C


Oracle Enterprise Manager (OEM) Cloud Control is recommended monitoring tool for Oracle Identity Management components like Oracle Internet Directory (OID),  Oracle Virtual Directory (OVD), Oracle Access Manager(OAM), and Oracle Identity Manager (OIM).


1.      Log into Enterprise Manager Grid Control 12C.

2.      Discover Host/Agent running OID/OVD service (This can be done using auto discovery or manual discovery) : OEM CC -> Setup -> Add Target ->  Add Target Manually (or Auto Discover).













3. Once Target Host and OEM Agent (hosting OID/OVD/WebLogic with EM) are discovered then add Middleware Target: OEM CC -> Targets -> Middleware ->Add -> Oracle Fusion Middleware/WebLogic Domain.





Monday, 26 May 2014

What are the differences between InnoDB and MyISAM?

  • InnoDB has row-level locking, MyISAM can only do full table-level locking.
  • InnoDB has better crash recovery.
  • MyISAM has FULLTEXT search indexes, InnoDB did not until MySQL 5.6.
  • InnoDB implements transactions, foreign keys and relationship constraints, MyISAM does not.

MYISAM

The main mechanism used is the key cache. It only caches index pages from .MYI files. To size your key cache, run the following query:
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;
This will give the Recommended Setting for MyISAM Key Cache (key_buffer_size) given your current data set (the query will cap the recommendation at 4G (4096M). For 32-bit OS, 4GB is the limit. For 64-bit, 8GB.
The MyISAM storage engine in MySQL.
  • Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
  • Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
  • Full-text indexing.
  • Especially good for read-intensive (select) tables.
MyISAM Limitations:
  • No foreign keys and cascading deletes/updates
  • No transactional integrity (ACID compliance)
  • No rollback abilities
  • Row limit of 4,284,867,296 rows (232)
  • Maximum of 64 indexes per row.


InnoDB

The main mechanism used is the InnoDB Buffer Pool. It caches data and index pages from InnoDB tables accessed. To size your InnoDB Buffer Pool, run the following query:
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;
This will give the Recommended Setting for the size of the InnoDB Buffer Pool (innodb_buffer_pool_size) given your current data set.
Don't forget to resize the InnoDB Log Files (ib_logfile0 and ib_logfile1). MySQL Source Code places a cap of the combined sizes of all InnoDB Log Files must be < 4G (4096M). For the sake of simplicity, given just two log files, here is how you can size them:
  • Step 1) Add innodb_log_file_size=NNN to /etc/my.cnf (NNN should be 25% of innodb_buffer_pool_size or 2047M, whichever is smaller)
  • Step 2) service mysql stop
  • Step 3) rm /var/log/mysql/ib_logfile[01]
  • Step 4) service mysql start (ib_logfile0 and ib_logfile1 are recreated)


The InnoDB storage engine in MySQL.
  • Support for transactions (giving you support for the ACID property).
  • Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
  • Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables.
  • InnoDB is more resistant to table corruption than MyISAM.
  • Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
  • MyISAM is stagnant; all future enhancements will be in InnoDB

InnoDB Limitations:
  • No full text indexing (Below-5.6 mysql version)
  • Cannot be compressed for fast, read-only

Friday, 16 May 2014

Oracle Application(R12) Health Check and performance scripts


As per Junior DBA demand i have create Daily Health check Scripts.
In this document, You need to modify SID,$ORACLE_HOME,.env location.Apps user/password.

File save as Apps Health Checks.sh

Linux OS use below command regarding scripts creations.


$ Vi Apps_Health_Checks.sh

#--<copy below all contain after that save it>
Use same scripts like this:-

$ ./Apps_Health_Checks.sh

#--
#-- Program :Daily  Apps Health Checks Report
#--
#-- Description : Apps_Health_Checks.sh
#--
#-- Purpose : Run a variety of Oracle Apps Health Checks.
#-- Warning section at beginning should usually return no rows.

#-- Creation Date : May 17, 2014

#-- ...........................................................................
dt=`date +"%d.%m.%y"`
. <DB Home>/<SID>_<domain>.env
sqlplus -s <<!
apps/<apps password>
SET linesize 80
set pagesize 500
SET space 1
#COLUMN db_name new_value dbname noprint
#COLUMN today new_value runtime noprint
spool <log location>/apps_health_checkup_$dt.txt
SELECT name db_name FROM v\$database;
SELECT TO_CHAR(sysdate,'YYYY/MM/DD HH24:MI') today FROM dual;
SET pagesize 1000
COLUMN owner FORMAT a8
COLUMN object_type FORMAT a12
COLUMN object_name FORMAT a30
COLUMN created FORMAT a9
COLUMN application_id on heading app_id FORMAT 99999
COLUMN profile_option_id on heading opt_id FORMAT 99999COLUMN level_value on heading 'level|value' FORMAT 99999
COLUMN prof_value on heading 'Prof|value' FORMAT a15
COLUMN prof_name FORMAT a20 trunc
COLUMN user_prof_name FORMAT a40 trunc
COLUMN user_name FORMAT a10 trunc
--
-- Health Check Logic starts Here
--
PROMPT
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT ++ Warning Section: Should see no rows in this section
PROMPT
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT
COLUMN obj_name FORMAT a18
COLUMN own FORMAT a5
-- General Warnings:
PROMPT Confirm not in Maintenance Mode - should see no rows
SELECT
 pot.user_profile_option_name "Profile Name"
 ,v.PROFILE_OPTION_VALUE "Profile Value"
 ,v.LEVEL_ID "Level ID"
 ,v.LAST_UPDATE_DATE "Last Update Date"
FROM
 apps.fnd_profile_options o
 , apps.fnd_profile_options_tl pot
 , apps.fnd_profile_option_values v
WHERE
 o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 10001) = 10001
AND pot.user_profile_option_name = 'Applications Maintenance Mode'
AND v.profile_option_value != 'NORMAL';
/*PROMPT Warn login accounts that should not become locked out like SYSADMIN
PROMPT
COLUMN user_name FORMAT a25
COLUMN creation_date FORMAT a30
COLUMN last_logon_date FORMAT a30
COLUMN password_date FORMAT a3SELECT user_name, creation_date
, TO_CHAR(last_logon_date, 'DD-Mon-YY') last_logon
--, ENCRYPTED_USER_PASSWORD
, TO_CHAR(password_date, 'DD-Mon-YY') passwd_date
FROM apps.fnd_user
WHERE ENCRYPTED_USER_PASSWORD = 'INVALID'
AND END_date IS NULL
AND USER_NAME IN ('SYSADMIN')
ORDER BY user_name;*/
/*
PROMPT User Security AND Access Issues section:
PROMPT Show any logins whose password is NOT SET to expire in 60 days
COLUMN user_name FORMAT a20
COLUMN encrypted_password FORMAT a20
SELECT user_id "User ID"
 , user_name "User Name"
 , password_lifespan_days "Password Lifespan Days"
FROM apps.fnd_user
WHERE end_date IS NULL
AND nvl(password_lifespan_days, 0) != 60
AND user_id > 1051;
*/

PROMPT Non-DBAs who have System Administrator or other powerful Responsibilities
COLUMN user_name FORMAT a10
COLUMN description FORMAT a20
COLUMN responsibility_name FORMAT a25
COLUMN end_date FORMAT a20
SELECT fu.user_name "User Name"
 ,fu.description "Description"
 ,substr(fr.responsibility_name,1,35) "Responsibility"
 ,fur.end_date "End Date"
FROM
 apps.fnd_user fu,
 apps.fnd_user_resp_groups_direct fur,
 apps.fnd_responsibility_vl fr,
 apps.fnd_application_vl fa
WHERE
 fur.user_id = fu.user_id
AND fu.user_name NOT IN ('YOUR_DBA_NAME','SYSADMIN')
AND fur.responsibility_application_id = fa.application_id
AND fur.responsibility_application_id = fr.application_id
AND fur.responsibility_id = fr.responsibility_idAND fu.end_date is null -- only show active users
AND nvl(fur.end_date, sysdate+1) > sysdate -- only show active resp assignment
AND fr.responsibility_name in ('System Administrator'
 ,'Application Developer'
 ,'Application Developer Common Modules'
 ,'CRM HTML Administration'
 ,'FICO Sys Admin Conversion'
 ,'FICO System Administration GUI'
 ,'Functional Administrator' -- can chg Profiles
 ,'Functional Developer'
 ,'Knowledge Administrator' --use FICO instead
 ,'Oracle Sales Administrator' --use FICO instead
 ,'Sales Administrator' --use FICO instead
 ,'SFM System Administrator'
 ,'TeleSales Administration' --use FICO instead
 )
ORDER BY 1;
COL emp_num format a7
COL per_serv_id format 999999
-- Check for terminated employees
PROMPT Terminated employees who still have an active login to Oracle Apps
SELECT DISTINCT(fu.user_name)
, papf.employee_number "Emp No."
, to_char(ppos.actual_termination_date, 'DD-Mon-YY') "Termination Date"
, period_of_service_id "Period Serv ID"
FROM apps.fnd_user fu
, apps.per_all_people_f papf
, apps.per_periods_of_service ppos
WHERE fu.employee_id = papf.person_id
AND papf.person_id = ppos.person_id
AND fu.end_date IS NULL
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id)
 FROM per_periods_of_service
 WHERE person_id = papf.person_id)
AND ppos.actual_termination_date < TRUNC(SYSDATE);
PROMPT Show users whose Responsibility roles have early end date in future
-- To remove, Use Define User Form toggle fnd_user end_date by
-- adding end_date, commit, remove user end_date, commit. Some time lag
-- Remove this logic when Oracle no longer end dates resp roles earlyCOLUMN user_name FORMAT a25
COLUMN user_end_date FORMAT a20
select distinct user_name "User Name"
 , user_end_date "End Date"
from apps.wf_local_user_roles
where user_end_date >= sysdate -- in future
and user_end_date <= sysdate + 700 -- within next week
and user_orig_system != 'WF_LOCAL_USERS'
and to_char(user_end_date,'DD-MON-YYYY') != '31-DEC-4712'
order by user_end_date, user_name;
-- Duplicate email addresses have security issues with workflow notifications
-- Two people should not have same email address since one user can get
-- another user's email for instance when doing password reset
COL email_address FORMAT a50
PROMPT Show fnd_users who have same email address
SELECT user_name "User Name"
 , email_address "Email Address"
FROM fnd_user WHERE email_address IN (
SELECT
 email_address
FROM apps.fnd_user
WHERE email_address is not NULL
AND email_address != 'employee@<company_name>.com'
AND end_date is null
GROUP BY
 email_address
HAVING count(*) > 1);
PROMPT Show active fnd_users tied to the same HR employee (shows person_id)
SELECT
 fu.employee_id "Employee ID"
, count(*) "Count"
FROM apps.fnd_user fu
WHERE fu.employee_id is not null -- this is person_id in per_all_people_f
AND fu.end_date is null
GROUP BY fu.employee_id
HAVING count(*) > 1;
PROMPT Workflow Section:PROMPT
--Show users whose mailpref is QUERY since causes Workflow Notification issues
PROMPT Users whose mail preference is 'do not send' since causes WF notif issues
SELECT fu.user_name
FROM apps.fnd_user_preferences fup
 , apps.fnd_user fu
WHERE fup.preference_name = 'MAILTYPE'
AND fup.preference_value = 'QUERY'
AND fup.user_name = fu.user_name
AND fu.end_date IS NULL
ORDER BY fu.user_name;
PROMPT Show any wf adhoc roles that have wrong notification_preference
SELECT notification_preference "Notification Preference"
 , COUNT(*) "Count"
FROM apps.wf_local_roles
WHERE orig_system /*= 'WF_LOCAL_ROLES'*/in ('FND_USR','PER')
AND notification_preference NOT IN ('MAILHTML', 'MAILHTM2')
GROUP BY notification_preference;
PROMPT Show if workflow mailer setting PROCESSOR_READ_TIMEOUT_CLOSE has
changed
PROMPT to something other than Y. Should not see any rows
SELECT p.parameter_name
, v.last_update_date
, u.user_name
FROM apps.fnd_svc_comp_param_vals v
, apps.fnd_svc_comp_params_b p
, apps.fnd_svc_components c
, apps.fnd_user u
WHERE c.component_type = 'WF_MAILER'
AND c.component_id = v.component_id
AND v.parameter_id = p.parameter_id
AND p.parameter_name = 'PROCESSOR_READ_TIMEOUT_CLOSE'
AND v.parameter_value <> 'Y'
AND v.last_updated_by = u.user_id;
PROMPT Profile Check Section:
PROMPT-- Confirm Examine function is password protected in Production
-- If profile is Y can use Examine without password PROMPT so unprotected
PROMPT Profile 'Utilities:Diagnostics' for Examine set to unprotected
SELECT
 v.profile_option_id "Profile Option ID"
 , pot.user_profile_option_name "Prof Name"
 , substr(v.profile_option_value, 1,5) "Prof Value"
 , v.level_id "Level ID"
 , v.level_value "Level Value"
FROM
 apps.fnd_profile_options o
 , apps.fnd_profile_options_tl pot
 , apps.fnd_profile_option_values v
WHERE
 o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND pot.user_profile_option_name like 'Utilities%Diag%'
AND v.profile_option_value != 'N' -- N means password protected
ORDER BY pot.user_profile_option_name;
PROMPT User Level 'Utilities:Diagnostics' Profile Settings for Examine

SELECT
 v.profile_option_id
 , pot.user_profile_option_name prof_name
 , substr(v.profile_option_value, 1,5) prof_value
 , v.level_id
 , v.level_value
 , fu.user_name
FROM
 apps.fnd_profile_options o
 , apps.fnd_profile_options_tl pot
 , apps.fnd_profile_option_values v
 , apps.fnd_user fu
WHERE
 o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 1001) = 10004 -- user level Profile
AND v.level_value = fu.user_id
AND pot.user_profile_option_name like 'Utilities%Diag%'
AND v.profile_option_value != 'N' order by pot.user_profile_option_name;
-- List Profile options for personalization related settings
--
PROMPT User level profile for Personalization related Profiles
SELECT
 user_name
 , pot.user_profile_option_name prof_name
 , v.profile_option_value prof_value
FROM
 apps.fnd_profile_options o
 , apps.fnd_profile_options_tl pot
 , apps.fnd_profile_option_values v
 , apps.fnd_user fu
WHERE
 o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND level_id = 10004 -- user level option
AND v.level_value = fu.user_id
AND pot.user_profile_option_name in
 (
 'AMS : Oracle Personalization Enabled'
 ,'Create Seeded Personalizations'
 ,'FND: Personalization Region Link Enabled'
 ,'FND: Personalization Seeding Mode'
 ,'HR: Enable User Personalization'
 ,'OSO : Enable Admin Personalization Feature'
 ,'Personalize Self-Service Defn'
 )
ORDER BY
 pot.user_profile_option_name
 , user_name;
PROMPT Find Personalization related profiles at other levels
PROMPT
PROMPT *** Site level ***
select t.user_profile_option_name, profile_option_value
from apps.fnd_profile_options o
 ,apps.fnd_profile_option_values v
 ,apps.fnd_profile_options_tl t
where o.profile_option_id = v.profile_option_id and o.application_id = v.application_id
 and profile_option_value = 'Y'
 and start_date_active <= SYSDATE
 and nvl(end_date_active,SYSDATE) >= SYSDATE
 and o.profile_option_name = t.profile_option_name
 and level_id=10001
 and (upper(t.user_profile_option_name) like '%PERSONALIZ%'
 and upper(t.user_profile_option_name) not like '%PERSONALIZ%WYSIWYG%'
 )
 order by user_profile_option_name;
PROMPT
PROMPT *** Application level ***
select t.user_profile_option_name, profile_option_value, application_name
from apps.fnd_profile_options o
 ,apps.fnd_profile_option_values v
 ,apps.fnd_profile_options_tl t
 ,apps.fnd_application_tl a
where o.profile_option_id = v.profile_option_id
 and o.application_id = v.application_id
 and profile_option_value = 'Y'
 and start_date_active <= SYSDATE
 and nvl(end_date_active,SYSDATE) >= SYSDATE
 and o.profile_option_name = t.profile_option_name
 and a.application_id = level_value
 and level_id=10002
 and t.language = a.language
 and upper(t.user_profile_option_name) like '%PERSONALIZ%'
 order by user_profile_option_name, application_name;
PROMPT
PROMPT *** Responsibility level ***
select t.user_profile_option_name, profile_option_value,
 responsibility_name
from apps.fnd_profile_options o
 ,apps.fnd_profile_option_values v
 ,apps.fnd_profile_options_tl t
 ,apps.fnd_responsibility_tl r
where o.profile_option_id = v.profile_option_id
 and o.application_id = v.application_id
 and profile_option_value = 'Y' and start_date_active <= SYSDATE
 and nvl(end_date_active,SYSDATE) >= SYSDATE
 and o.profile_option_name = t.profile_option_name
 and responsibility_id = level_value
 and level_id=10003
 and t.language = r.language
 and upper(t.user_profile_option_name) like '%PERSONALIZ%'
 order by user_profile_option_name, responsibility_name;
COLUMN prof_value on heading 'Prof|value' FORMAT a5
PROMPT Confirm self service diagnostics are not turned on
SELECT
 v.profile_option_id
 , pot.user_profile_option_name prof_name
 , substr(v.profile_option_value, 1,5) prof_value
 , v.level_id
 , v.level_value
FROM
 apps.fnd_profile_options o
 , apps.fnd_profile_options_tl pot
 , apps.fnd_profile_option_values v
WHERE
 o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND pot.user_profile_option_name like 'FND%Diag%'
AND nvl(v.level_id, 1001) != 10004 -- skip user level options
ANd v.profile_option_value != 'N' -- when Y then can use ss diagnostics
order by pot.user_profile_option_name;
PROMPT Confirm only DBA and sysadmin users have access to SS Diagnostics
SELECT
 v.profile_option_id
 , pot.user_profile_option_name prof_name
 , substr(v.profile_option_value, 1,5) prof_value
 , v.level_id
 , v.level_value
 , fu.user_name
FROM
 apps.fnd_profile_options o
 , apps.fnd_profile_options_tl pot
 , apps.fnd_profile_option_values v
 , apps.fnd_user fuWHERE
 o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 1001) = 10004 -- user level options
AND fu.user_name NOT IN ('YOUR_DBA_NAME', 'SYSADMIN')
AND v.level_value = fu.user_id
AND pot.user_profile_option_name like 'FND%Diag%'
order by pot.user_profile_option_name;
PROMPT Look for Password Profiles set at user level
SELECT
 v.profile_option_id
 , pot.user_profile_option_name prof_name
 , fu.user_name
 , substr(v.profile_option_value, 1,5) prof_value
 , v.level_value
FROM
 apps.fnd_profile_options o
 , apps.fnd_profile_options_tl pot
 , apps.fnd_profile_option_values v
 , apps.fnd_user fu
WHERE
 o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 1001) = 10004
AND v.level_value = fu.user_id
AND pot.user_profile_option_name like '%Password%'
order by pot.user_profile_option_name;
-- End User Security Warnings
PROMPT Warnings about Concurrent Programs and Concurrent Manager
column user_concurrent_program_name on heading Program format a32 trunc
column concurrent_program_name on heading Program format a10 trunc
column description on heading Description format a35 trunc
--column user_name format a10
PROMPT Show any requests by SYSADMIN that ended in error in last 5 days
PROMPT
select request_id "Request ID" /*,fu.user_name User_Name*/
 ,to_char(cr.actual_start_date, 'DD HH24:MI') "When Started"
 ,to_char(cr.actual_completion_date, 'DD HH24:MI') "When Ended"
 ,cp.concurrent_program_name "Program Name"
 ,phase_code "Phase Code"
 ,status_code "Status Code"
from
 apps.fnd_concurrent_programs cp,
 apps.fnd_user fu,
 apps.fnd_concurrent_requests cr
where
 cr.concurrent_program_id = cp.concurrent_program_id
and cr.program_application_id = cp.application_id
and cr.actual_completion_date is not null
and cr.requested_by = fu.user_id
and fu.user_name = 'SYSADMIN'
and cr.status_code = 'E' -- requests that ended in error
and trunc(cr.actual_start_date) >= trunc(sysdate-5) -- started recently
order by cr.actual_start_date DESC;
PROMPT Show conc programs that have trace enabled. Normally should be none
PROMPT
-- To Turn off: Go to Define Con Pgm form and uncheck the Enable Trace field
col prog_name format a35
col enable_trace format a15
SELECT concurrent_program_name "Prog Name"
 , enable_trace "Trace Enabled"
 , last_update_date "Last Update Date"
FROM apps.fnd_concurrent_programs fcp
WHERE NVL(enable_trace,'N') = 'Y';
-- Confirm Alert Periodic Scheduler is pending so that alerts will run
PROMPT Confirm Alert Periodic Scheduler is okay, should see no rows
select 'PROBLEM: No pending Alert Periodic Scheduler request' "Message" from dual
where not exists (
 select
 cp.concurrent_program_name Program,
 cr.request_id Request,
 phase_code S,
 status_code C,
 cr.argument1||', '|| cr.argument2||', '||
 cr.argument3||', '||
 cr.argument4||', '||
 cr.argument5||', '||
 cr.argument6||', '||
 cr.argument7||', '||
 cr.argument8 Arguments
 from
 apps.fnd_concurrent_requests cr,
 apps.fnd_concurrent_programs cp
 where
 cr.concurrent_program_id = cp.concurrent_program_id
 and cp.application_id >= 0 -- force on index leading edge
 and cr.program_application_id = cp.application_id
 and cp.concurrent_program_name = 'ALEPPE' -- Alert scheduler con pgm
 and cr.phase_code = 'P'
 and cr.status_code != 'H' -- not on hold
 );
-- End Con Program section
/*-- Recent changes to objects with Custom Direct Changes - Research
-- Add standard Apps objects we Customized and will be lost when patched
PROMPT Recent changes to objects with Custom Direct Changes - usually None
PROMPT
PROMPT Any ADP views that have been changed recently
SELECT owner, object_name, created, last_ddl_time
FROM dba_objects
WHERE object_name like Upper('%ADP%')
AND owner = 'APPS'
AND object_type = 'VIEW'
AND last_ddl_time > (sysdate -7)
ORDER BY object_type, owner, object_name;
PROMPT Any changes to iExpense pkg where we have custom direct chg
SELECT owner, object_name, created, last_ddl_time
FROM dba_objects
WHERE object_name = 'AP_WEB_DB_HR_INT_PKG'
AND owner = 'APPS'
AND object_type in ('PACKAGE SPEC', 'PACKAGE')AND last_ddl_time > (sysdate -7)
ORDER BY object_type, owner, object_name;
PROMPT Any OTL/iExpense views changed recently
SELECT owner, object_name, created, last_ddl_time
FROM dba_objects
WHERE object_name in ('AP_WEB_PA_PROJECTS_V')
AND owner = 'APPS'
AND object_type = 'VIEW'
AND last_ddl_time > (sysdate -7)
ORDER BY object_type, owner, object_name;
PROMPT PA Gen Draft Rev parm we want disabled
col short_name format a10
col executable_name format a10
col parameter format a10
SELECT SUBSTR(fcp.user_concurrent_program_name,1,60) concurrent_program_name
, fcp.concurrent_program_name short_name
, SUBSTR(fe.executable_name,1,25) executable_name
, SUBSTR(fl_em.meaning,1,18) execution_method
, LPAD(TO_CHAR(fdfcu.column_seq_num,'fm990'),4) " SEQ"
, fdfcu.end_user_column_name parameter
, RPAD(fdfcu.enabled_flag,8) "ENABLED?"
, SUBSTR(ffvs.flex_value_set_name,1,45) validation_value_set
, SUBSTR(ffvs.description,1,75) value_set_description
, SUBSTR(fl_dt.meaning,1,13) default_type
, RPAD(fdfcu.required_flag,5) "REQD?"
-- If the default value is populated and more than 80 chars
-- then trim it to 80 for purposes of this report.
, RPAD(DECODE(SIGN(NVL(LENGTH(fdfcu.default_value),0) - 80)
 , +1, SUBSTR(fdfcu.default_value,1,80) || '...'
 , fdfcu.default_value),83) default_value
, RPAD(fdfcu.display_flag,10) "DISPLAYED?"
FROM apps.fnd_application fa
, apps.fnd_application_tl fat
, apps.fnd_concurrent_programs_vl fcp
, apps.fnd_executables fe
, apps.fnd_descr_flex_column_usages fdfcu
, apps.fnd_descr_flex_col_usage_tl fdfcut
, apps.fnd_flex_value_sets ffvs
, apps.fnd_lookups fl_dt, apps.fnd_lookups fl_em
WHERE USERENV('LANG') = fat.language
AND fat.application_id = fa.application_id
AND fa.application_id = fcp.application_id
AND fcp.concurrent_program_name = 'PARGDR' -- PA program
AND fdfcu.end_user_column_name = 'Release Draft Revenue' -- this parameter
AND fdfcu.enabled_flag = 'Y' -- has become enabled when it should not
AND fcp.executable_application_id = fe.application_id
AND fcp.executable_id = fe.executable_id
AND fcp.application_id = fdfcu.application_id
AND '$SRS$.'
 || fcp.concurrent_program_name = fdfcu.descriptive_flexfield_name
AND fdfcu.application_id = fdfcut.application_id
AND fdfcu.descriptive_flexfield_name = fdfcut.descriptive_flexfield_name
AND fdfcu.descriptive_flex_context_code = fdfcut.descriptive_flex_context_code
AND fdfcu.application_column_name = fdfcut.application_column_name
AND USERENV('LANG') = fdfcut.language
AND fdfcu.flex_value_set_id = ffvs.flex_value_set_id
AND fdfcu.default_type = fl_dt.lookup_code (+)
AND 'FLEX_DEFAULT_TYPE' = fl_dt.lookup_type (+)
AND fcp.execution_method_code = fl_em.lookup_code (+)
AND 'CP_EXECUTION_METHOD_CODE' = fl_em.lookup_type (+)
ORDER BY fcp.user_concurrent_program_name
, fdfcu.column_seq_num;
-- End Custom Direct Changes Section*/
/*PROMPT Confirm selected Applimation DB triggers that cause problems are off
select count(*)
from dba_triggers
where trigger_name like 'AM_WF_LOCAL_USER_ROLES%'
and status != 'DISABLED' -- these triggers cause severe perf problems when on
/*/
-- Std RDBMS Checks Section
PROMPT Watch for HighWaterMark -- higher than 1000 since may need to incr processes
PROMPT
SELECT V.SESSIONS_CURRENT "Current Sessions",
 V.SESSIONS_HIGHWATER "HighWater Sessions",
 V.CPU_COUNT_CURRENT "CPU Current Count", V.CPU_COUNT_HIGHWATER "CPU Count HighWater"
 FROM V\$LICENSE V;
--where SESSIONS_HIGHWATER > 100
-- Check for datafiles that have autoextended to their max so stuck
column bytes format 999999999
column file_name format a35
column TSPACE format a12
prompt
prompt Datafiles with autoextend ON that have reached their max so have got STUCK
select tablespace_name "Tablespace"
 , file_name "Filename"
 , bytes/1048576 "Size"
 , maxbytes / 1048576 "Maxsize(MB)" -- convert to MB
from dba_data_files
where autoextensible = 'YES'
and bytes >= maxbytes -- datafile already extended to max
order by tablespace_name;
-- DB Security Warnings:
-- Check for any locked db user accounts
COLUMN username FORMAT a14
COLUMN default_tablespace heading 'DEFAULT TS' FORMAT a10
COLUMN temporary_tablespace heading 'TEMP' FORMAT a4
COLUMN profile FORMAT a13
COLUMN account_status heading 'ACCT|STAT' FORMAT a6 trunc
PROMPT List Locked Oracle schema accounts
PROMPT
SELECT username
 ,to_char(created, 'DD-MON-YY') created
 ,default_tablespace
 ,temporary_tablespace
 ,account_status
 ,to_char(lock_date, 'MM/DD/YY HH24:MI') lock_date
 ,profile
FROM dba_users
WHERE lock_date IS NOT NULL
AND account_status NOT LIKE 'EXPIRED%';
PROMPT Check whether UNDO datafiles have autoextended
PROMPT May want to resize datafiles down lower after big AutoExtendCOLUMN sname FORMAT a12 heading 'Tablespace' justify c
COLUMN ssize FORMAT 999,999.99 heading 'Mb|Total' justify c
COLUMN extents FORMAT 9,999 heading 'Free|Exts' justify c
COLUMN mbytes FORMAT 9,999.99 heading 'Largest|Free Ext' justify c
COLUMN tbytes FORMAT 999,999.99 heading 'Mb|Avail' justify c
COLUMN mused FORMAT 999,999.99 heading 'Mb|Used' justify c
COLUMN pct FORMAT 990.99 heading 'Percent|Free' justify c
SELECT
 total.tablespace_name sname,
 COUNT(free.bytes) extents,
 max(free.bytes)/1048576 mbytes,
 total.ts_size/1048576 ssize,
 (total.ts_size/1048576) - (sum(free.bytes/1048576)) mused,
 sum(free.bytes)/1048576 tbytes,
 sum(free.bytes)/total.ts_size * 100 pct
FROM
 dba_free_space free
 ,(SELECT tablespace_name,
 sum(bytes) ts_size
 FROM dba_data_files
 GROUP BY tablespace_name)
 total
WHERE
 total.tablespace_name = free.tablespace_name (+)
AND total.tablespace_name = 'APPS_UNDOTS1'
AND total.ts_size/1048576 > 8600 -- greater than 8600 MB then autoextended
GROUP BY
 total.tablespace_name,
 total.ts_size;

PROMPT
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT END Warning Section
PROMPT
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT
PROMPT
PROMPT
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT Begin General Health Checks SectionPROMPT
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT
PROMPT
-- Revise prompt as persistent invalid objects increase or reduce
-- See SharePoint Invalid Object Log for details about these objects
PROMPT Invalid APPS objects, at least 6: FND_OID_DIAG FND_TS_SIZE and 4 MRP_
COLUMN object_name FORMAT a30
SELECT
 owner, object_name, object_type
, to_char(created, 'DD-MON-YY') created
, status
FROM dba_objects
WHERE Owner = 'APPS'
AND status = 'INVALID'
ORDER BY object_name;
-- Add any custom schemas here
PROMPT Invalid objects owned by custom schema XXCUSTOM Account
SELECT
 owner, object_name, object_type, created, status
FROM dba_objects
WHERE Owner = 'XXCUSTOM'
AND status = 'INVALID'
ORDER BY object_name;
-- RDBMS Checks
PROMPT Check SYS audit table to determine number of audit rows
SELECT
 COUNT(*)
FROM sys.aud$;
PROMPT List Expired schema accounts
PROMPT
SELECT username
 ,to_char(created, 'DD-MON-YY') created
 ,default_tablespace
 ,temporary_tablespace
 ,account_status
 ,to_char(lock_date, 'MM/DD/YY HH24:MI') lock_date ,profile
FROM dba_users
WHERE /*lock_date is not null
AND */ account_status != 'OPEN';
PROMPT Usage Checks Section:
-- Check highest DB connections COUNT since last DB start
-- Count variety of current Oracle connections
PROMPT Current Oracle Connection Characteristics including highwater mark
COLUMN formsusers HEADING "Forms connects" FORMAT 99999
COLUMN selfservusers HEADING "Self Service" FORMAT 99999
COLUMN rundatetime HEADING "TIME OF DAY" FORMAT A16
COLUMN currsession HEADING "Current Sess" FORMAT 99999
COLUMN sesshighwater HEADING "High Water" FORMAT 99999
COLUMN jdbcthinclient HEADING "JDBC Thin" FORMAT 99999
SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI') "Current Time"
--show currently logged in Forms connections, multiple connections per user
--approximately half as many Forms users as DB forms connections
,(SELECT COUNT(*)
 FROM apps.fnd_logins fl -- only has Forms login rows
 , apps.fnd_user fu
 , v\$session s
 , v\$process p
 WHERE fl.end_time IS NULL
 AND fl.user_id = fu.user_id(+)
 AND fl.process_spid = p.spid
 AND fl.pid = p.pid
 AND fl.serial# = p.serial#
 AND p.addr = s.paddr
 AND s.program IS NULL) "Forms Users"
--
--
-- SelfServ Activity last 60 min based on icx_sessions
,(SELECT COUNT(*)
 FROM apps.icx_sessions icx
 , apps.fnd_user fu
 , apps.fnd_responsibility_vl frv
 WHERE icx.user_id = fu.user_id(+)
 AND icx.disabled_flag = 'N'
 AND icx.last_connect > SYSDATE - 1/24--Preferences
 AND NVL(icx.responsibility_id,20873) = frv.responsibility_id(+)
 AND icx.responsibility_application_id = frv.application_id(+)
-- only web self service responsibilities
 AND frv.version(+) = 'W') "Self Serv Users"
--
--
,(SELECT SESSIONS_CURRENT
 FROM v\$license ) "Curr Session"
--
--
,(SELECT SESSIONS_HIGHWATER
 FROM v\$license ) "Sess HighWater"
--
--
,( SELECT COUNT(*)
 FROM sys.v_\$session
 WHERE program = 'JDBC Thin Client') "JDBC ThinClient"
FROM DUAL;
PROMPT User Checks Section:
PROMPT
PROMPT Users whose FND Login Account password has become INVALID (11.5.10.2)
PROMPT and whose account is not end dated
COLUMN encrypted_password FORMAT a20
SELECT user_name
, TO_CHAR(last_logon_date, 'DD-Mon-YY') last_logon
, TO_CHAR(password_date, 'DD-Mon-YY') passwd_date
, ENCRYPTED_USER_PASSWORD encrypted_password
, TO_CHAR(end_date, 'DD-Mon-YY') end_date
FROM apps.fnd_user
WHERE encrypted_user_password = 'INVALID' -- value when user is blocked
AND user_name NOT IN ('ANONYMOUS', 'APPSMGR','XML_USER') --inactive seeded
AND end_date is null -- only look at active logins in this query
ORDER BY user_name;
PROMPT Non-Terminated people with end dated fnd_user plus INVALID Password
PROMPT We assume that locked users should not be both end dated and INVALID
SELECT DISTINCT(fu.user_name)
, ENCRYPTED_USER_PASSWORD encrypted_password
, fu.end_dateFROM apps.fnd_user fu
, apps.per_all_people_f papf
WHERE fu.encrypted_user_password = 'INVALID' -- value when user is blocked
AND fu.end_date IS NOT NULL
AND fu.employee_id = papf.person_id
AND papf.current_employee_flag = 'Y'
AND trunc(sysdate) between papf.effective_start_date
 and papf.effective_end_date;
-- Profiles Section:
PROMPT PROFILES Section: Check Selected PROFILE Settings:
PROMPT
PROMPT List Site level Profiles that have changed in last Month
COLUMN application_id on heading app_id FORMAT 99999
COLUMN profile_option_id on heading 'opt|id' FORMAT 9999
COLUMN level_value on heading 'level|value' FORMAT 99999
COLUMN prof_value on heading 'Prof|value' FORMAT a15
COLUMN prof_name FORMAT a20 trunc
COLUMN user_prof_name FORMAT a40 trunc
COLUMN user_name FORMAT a10 trunc
PROMPT Site Level Profile Settings
SELECT
 o.user_profile_option_name user_prof_name
 , substr(v.profile_option_value, 1,45) prof_value
 , fu.user_name
 , to_char(v.last_update_date,'DD-Mon-YYYY') update_date
FROM apps.fnd_profile_option_values v
 , apps.fnd_profile_options_vl o
 , apps.fnd_user fu
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND level_id = 10001
AND v.last_updated_by = fu.user_id
AND (v.last_update_date >= sysdate - 30 -- updated in last 4 weeks
 OR v.creation_date > sysdate -30) -- created in the last 4 weeks
ORDER BY fu.user_name, o.user_profile_option_name;
PROMPT User Level Profile Settings relating to Trace or Debug
--COLUMN application_id on heading appl_id FORMAT 999999
COLUMN profile_option_id on heading prof_id FORMAT 99999
COLUMN level_value on heading 'level|value' FORMAT 99999
COLUMN level_id on heading 'level|id' FORMAT 99999
COLUMN prof_value on heading 'Prof|value' FORMAT a5
COLUMN prof_name FORMAT a25 trunc
COLUMN user_name FORMAT a15
PROMPT List Profile options WHERE debug/trace is SET on
SELECT
 v.profile_option_id
 , v.level_id
 , user_name
 , pot.user_profile_option_name prof_name
 , substr(v.profile_option_value, 1,45) prof_value
FROM
 apps.fnd_profile_options o
 , apps.fnd_profile_options_tl pot
 , apps.fnd_profile_option_values v
 , apps.fnd_user fu
WHERE
 o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND level_id = 10004 -- user level option
AND v.level_value = fu.user_id
AND fu.end_date is null -- only look at active logins
AND nvl(v.profile_option_value, 'N') = 'Y'
AND (pot.user_profile_option_name like '%Trace%'
 OR pot.user_profile_option_name like '%Debug%')
ORDER BY pot.user_profile_option_name;
PROMPT Non-User Level Profile Settings relating to Trace or Debug
SELECT
 v.profile_option_id
 , v.level_id
 , v.level_value
 , pot.user_profile_option_name prof_name
 , substr(v.profile_option_value, 1,45) prof_value
FROM
 apps.fnd_profile_options o
 , apps.fnd_profile_options_tl pot
 , apps.fnd_profile_option_values vWHERE
 o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND level_id != 10004 -- not user level option
AND nvl(v.profile_option_value, 'N') = 'Y'
AND (pot.user_profile_option_name like '%Trace%'
 OR pot.user_profile_option_name like '%Debug%')
order by pot.user_profile_option_name;
PROMPT Module Specific Checks Section:
PROMPT
PROMPT Module: Order Mgmt
PROMPT Check OE Processing Msgs for Number AND types of rows
PROMPT If too many rows check whether 'Message Purge' Con Pgm is scheduled
SELECT
 request_id
, substr(opmt.message_text,1,50) msg
, COUNT(*)
FROM ont.oe_processing_msgs opm
, ont.oe_processing_msgs_tl opmt
WHERE opm.transaction_id = opmt.transaction_id
group by request_id,opmt.message_text;
PROMPT Module: Advanced Supply Chain Planning
PROMPT We run Purge ATP Temp Tables but it leaves rows with null creation_date
PROMPT Need to truncate mrp.mrp_atp_schedule_temp periodically when too many
PROMPT rows due to the null problem.
SELECT
 COUNT(*)
FROM mrp.mrp_atp_schedule_temp;
PROMPT Module: AOL
PROMPT We run Purge FND_STATS History Records but it leaves some rows.
PROMPT Need to truncate apps.fnd_stats_hist periodically when too many rows
SELECT
 COUNT(*)
FROM apps.fnd_stats_hist;PROMPT End Module specific Checks Section
PROMPT
PROMPT
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT ++ End of oracle_apps_full_report.sh Logic
PROMPT
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SPOOL OFF
CLEAR COLUMNS
EXIT
!

Internal Concurrent Manager status could not be determined



There can a scenario where while running

adcmctl status apps/apps_password
The output is:
$ adcmctl.sh status apps/apps_password
You are running adcmctl.sh version 115.28
Internal Concurrent Manager status could not be determined.
adcmctl.sh: exiting with status 0


Solution:
1. We can run cmclean.sql script.
    As suggested by (Oracle Note: 134007.1 - CMCLEAN.SQL - Non Destructive Script to
    Clean Concurrent Manager Tables). Shutdown the CM, run the script (make sure you issue
    commit once the script is  done), start the CM and check for the issue.
2. If after running the the cmclean.sql the issue still persists then take down the application tier
    services and run autoconfig. This will relink the binaries of the adcmctl.sh. After autoconfig is
    successful then take up  the application services and and check the issue.

Wednesday, 14 May 2014

Export full Database Scripts


                          Oracle Database 11.2 Full Database Export Scripts


Create scripts with .sh extension through Vi editor.

In this scripts delete your backup older than 3 days.
you need to change all path as per your environment.

Like ORACLE_HOME path, ORACLE_SID.
change system password.
Here script name is exp_full_DB.sh..



[Oracle ] $ vi exp_full_DB.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
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 directory=TESTODS_EXP_DIR dumpfile=TESTODS_$fdate.dmp logfile=TESTODS_export_$fdate.log full=y