Wednesday, April 06, 2011

Steps for releasing lock on a table:

Finding Locks


select session_id "sid",SERIAL# "Serial",
substr(object_name,1,20) "Object",
substr(os_user_name,1,10) "Terminal",
substr(oracle_username,1,10) "Locker",
nvl(lockwait,'active') "Wait",
decode(locked_mode,
2, 'row share',
3, 'row exclusive',
4, 'share',
5, 'share row exclusive',
6, 'exclusive', 'unknown') "Lockmode",
OBJECT_TYPE "Type"
FROM
SYS.V_$LOCKED_OBJECT A,
SYS.ALL_OBJECTS B,
SYS.V_$SESSION c
WHERE
A.OBJECT_ID = B.OBJECT_ID AND
C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc

Finding Blocking sessions :

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0
and l1.id1=l2.id1 and l1.id2=l2.id2

select s1.username '@' s1.machine ' ( SID=' s1.sid ' ) is blocking '
s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;

Sessions with highest CPU consumption :
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;

Sessions with the highest time for a certain wait  :

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;

Sessions with highest DB Time usage :

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0; 
 


Step1:To verify the lock object Here is the import query:
---------------------------------------------------------------

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;

Step 2: Find the serial# for the sessions holding the lock:

SQL> select SERIAL# from v$session where SID=667;

SERIAL#
----------
21091

SQL> alter system kill session '667,21091';

System altered.


Regards,
Sukhwinder Singh

Oracle Database Health check scripts

Health of the Database can be check in various ways.  It includes:

Monitoring Scope Current Status OS Level : -

1 Physical memory / Load :Load normal, Load averages: 0.35, 0.37, 0.36
2 OS Space threshold ( archive, ora_dump etc.. ) :Sufficient Space available.
3 Top 10 process consuming memory:No process using exceptional high memory
4 Free volumes available :Sufficient disk space is available on the mount points
5 Filesystem space Under normal threshold
Database level.
6 Check extents / Pro active Space addition:Space is being regularly added.
7 Check alert log for ORA- and warn messages.
8 Major wait events (latch/enqueue/Lib cache pin) No major wait events
9 Max Sessions
10 Long running Jobs 6 inactive sessions running for more than 8 hrs
11 Invalid objects 185
12 Analyze Jobs ( once in a week ) Done on 20-JAN-2008 Time 06:00:06
13 Temp usage / Rollback segment usage Normal
14 Nologging Indexes
15 Hotbackup/Coldbackup Gone fine
16 Redo generation normal
17 PQ proceses Normal
18 I/O Generation Under normal threshold
19 2 PC Pending transactions 0
DR / backup
1 Sync arch Normal
2 Purge arch Normal
3 Recovery status Normal
20)DATABASE HEALTH CHECK SCRIPT: Showing locks and Archive generation details

In Detail DATABASE Health check:
OPERATING SYSTEM:

1)Physical memory/ Load:
1) Free:free command displays amount of total, free and used physical memory (RAM) in the system as well as showing information on shared memory, buffers, cached memory and swap space used by the Linux kernel.
Usage:
$ free -m

2) vmstat:vmstat reports report virtual memory statistics, which has information about processes, swap, free, buffer and cache memory, paging space, disk IO activity, traps, interrupts, context switches and CPU activity
Usage:
$vmstat 5

3) top:top command displays dynamic real-time view of the running tasks managed by kernel and in Linux system. The memory usage stats by top command include real-time live total, used and free physical memory and swap memory with their buffers and cached memory size respectively
Usage:
$top
4) ps :ps command reports a snapshot on information of the current active processes. ps will show the percentage of memory resource that is used by each process or task running in the system. With this command, top memory hogging processes can be identified.
Usage:
$ps aux

2) OS Space threshold ( archive, ora_dump etc.. ):
Checking the OS space is available in all filesystems,specially the location which is having archive logs ,oracle Database files.We can use the below OS commands:
$df –h
$du –csh *
3) Top 10 process consuming memory:
We can Displaying top 10 memory consuming processes as follows:

ps aux|head -1;ps aux|sort -m

We can use the top command, and press M which orders the process list by memory usage.

4) Free volumes available:

We have to make sure Sufficient disk space is available on the mount points on each OS servers where the Database is up and running.

$df –h

5)Filesystem space:

Under normal threshold.Check the filesystem in the OS side whether the sufficient space is available at all mount points.

DATABASE :

6)Check extents / Pro active Space addition:
Check each of the Data,Index and temporary tablespaces for extend and blocks
Allocation details.

SET LINES 1000
SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS
FROM DBA_SEGMENTS;

SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS
FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=’STAR01D’;

7) Check alert log for ORA- and warn messages:

Checking the alert log file regulary is a vital task we have to do.In the alert log files we have to looks for the following things:

1) Look for any of the oracle related errors.
Open the alert log file with less or more command and search for ORA-
This will give you the error details and time of occurrence.

2) Look for the Database level or Tablespace level changes
Monitor the alert log file and search the file for each Day activities happening
In the Database either whether it is bouncing of Database.Increase in the size of the tablespaces,Increase in the size of the Database parameters.In the 11g Database we can look for TNS errors in the alert log file.

8) Major wait events (latch/enqueue/Lib cache pin):

We can check the wait events details with the help of below queries:

SELECT s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
UPPER (s.program) program, s.TYPE, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
FROM v$session s
WHERE ( (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND') AND STATUS='ACTIVE'
)
ORDER BY "PROGRAM";

The following query provides clues about whether Oracle has been waiting for library cache activities:

Select sid, event, p1raw, seconds_in_wait, wait_time
From v$session_wait
Where event = 'library cache pin'
And state = 'WAITING';

The below Query gives details of Users sessions wait time and state:

SELECT NVL (s.username, '(oracle)') AS username, s.SID, s.serial#, sw.event,
sw.wait_time, sw.seconds_in_wait, sw.state
FROM v$session_wait sw, v$session s
WHERE s.SID = sw.SID
ORDER BY sw.seconds_in_wait DESC;

9) Max Sessions:
There should not be more than 6 inactive sessions running for more than 8 hours in a Database in order to minimize the consumption of CPU and I/O resources.

a)Users and Sessions CPU consumption can be obtained by below query:

Set lines 1000
select ss.username, se.SID,VALUE/100 cpu_usage_seconds
from v$session ss, v$sesstat se, v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID and ss.status='ACTIVE'
and ss.username is not null
order by VALUE desc;


b) Users and Sessions CPU and I/O consumption can be obtained by below query:

-- shows Day wise,User wise,Process id of server wise- CPU and I/O consumption
set linesize 140
col spid for a6
col program for a35 trunc
select p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,
ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,
round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day
from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg
where s.paddr=p.addr and ss.sid=s.sid
and ss.statistic#=12 and si.sid=s.sid
and bg.paddr(+)=p.addr
and round((ss.value/100),0) > 10
order by 8;

10) Long running Jobs:

We can find out long running jobs with the help of the below query:

col username for a20
col message for a50
col remaining for 9999
select username,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started,
time_remaining remaining, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc;

11) Invalid objects:

We can check the invalid objects with the help of the below query:

select owner||' '||object_name||' '||created||' '||status from dba_objects where status='INVALID';

12) Analyze Jobs ( once in a week ):

We need to analyze the jobs that are running once in a week as a golden rule.
The below steps can be considered for analyzing jobs.

Analyzing a Running Job
The status of a job or a task changes several times during its life cycle. A job can have the following as its status:
Scheduled: The job is created and will run at the specified time.
Running: The job is being executed and is in progress.
Initialization Error: The job or step could not be run successfully. If a step in a job fails initialization, the job status is Initialization Error.
Failed: The job was executed but failed.
Succeeded: The job was executed completely.
Stopped: The user canceled the job.
Stop Pending: The user has stopped the job. The already running steps are completing execution.
Suspended: This indicates that the execution of the job is deferred.
Inactive: This status indicates that the target has been deleted.
Reassigned: The owner of the job has changed.
Skipped: The job was not executed at the specified time and has been omitted.
The running jobs can be found out by the help of below query:

select sid, job,instance from dba_jobs_running;

We can find out the failed jobs and Broken jobs details with the help of the Below query:

select job||' '||schema_user||' '||Broken||' '||failures||' '||what||' '||last_date||' '||last_sec from dba_jobs;

13) Temp usage / Rollback segment/PGA usage:

We can get information of temporary tablespace usage details with the help of below query:
Set lines 1000
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;

We can get information of Undo tablespace usage details with the help of the below query:
set lines 1000
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';

We can get the PGA usage details with the help of the below query:
select st.sid "SID", sn.name "TYPE",
ceil(st.value / 1024 / 1024/1024) "GB"
from v$sesstat st, v$statname sn
where st.statistic# = sn.statistic#
and sid in
(select sid from v$session where username like UPPER('&user'))
and upper(sn.name) like '%PGA%'
order by st.sid, st.value desc;
Enter value for user: STARTXNAPP
14)Validating the Backup:

We have to verify the Hotbackup/Coldbackup(or any physical or logical backup) of all the Production and non-production Databases went fine.Make sure you are having a valid backups of all the Databases.Check the Backup locations to make sure the Backup completed on time with the required Backup data.

14)Hotbackup/Coldbackup:
Validating the backup of Database.It should complete on time with the required data for restoring and recovery purpose if required.

15) Redo generation/Archive logs generation details:
We should make sure there should not be frequent log switch happening in a Database.If there are frequent log switches than archive logs might generate more which may decrease the performance of the Database however in a production Database log switches could vary depending upon the Server configuration between 5 to 20.

We can the log switch details with the help of the below query:

Redolog switch Datewise and hourwise:
-------------------------------
set lines 120;
set pages 999;
select to_char(first_time,'DD-MON-RR') "Date",
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') " 00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') " 01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') " 02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') " 03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') " 04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') " 05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') " 06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') " 07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') " 08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') " 09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') " 10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') " 11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') " 12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') " 13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') " 14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') " 15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') " 16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') " 17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') " 18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') " 19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') " 20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') " 21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') " 22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') " 23"
from v$log_history
group by to_char(first_time,'DD-MON-RR')
order by 1
/
Archive logs generations is directly proportional to the number of log switches happening in a Database. If there are frequent log switches than archive logs might generate more which can affect the performance of Database.


We can use the below queries for archive logs generation details:

a)Archive logs by dates:
set lines 1000
select to_char(first_time,'DD-MON-RR') "Date",
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') " 00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') " 01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') " 02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') " 03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') " 04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') " 05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') " 06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') " 07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') " 08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') " 09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') " 10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') " 11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') " 12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') " 13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') " 14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') " 15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') " 16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') " 17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') " 18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') " 19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') " 20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') " 21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') " 22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') " 23"
from v$log_history
group by to_char(first_time,'DD-MON-RR')
order by 1
/
b)Archive log generation details Day-wise :

select to_char(COMPLETION_TIME,'DD-MON-YYYY'),count(*)
from v$archived_log group by to_char(COMPLETION_TIME,'DD-MON-YYYY')
order by to_char(COMPLETION_TIME,'DD-MON-YYYY');

c) Archive log count of the day:

select count(*)
from v$archived_log
where trunc(completion_time)=trunc(sysdate);

count of archived logs generated today on hourly basis:
-------------------------------------------------------
select to_char(first_time,'DD-MON-RR') "Date",
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') " 00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') " 01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') " 02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') " 03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') " 04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') " 05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') " 06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') " 07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') " 08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') " 09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') " 10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') " 11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') " 12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') " 13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') " 14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') " 15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') " 16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') " 17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') " 18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') " 19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') " 20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') " 21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') " 22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') " 23"
from v$log_history
where to_char(first_time,'DD-MON-RR')='16-AUG-10'
group by to_char(first_time,'DD-MON-RR')
order by 1
/

16)I/O Generation:
We can find out CPU and I/O generation details for all the users in the Database with the help of the below query:
-- Show IO per session,CPU in seconds, sessionIOS.
set linesize 140
col spid for a6
col program for a35 trunc
select p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,
ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,
round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day
from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg
where s.paddr=p.addr and ss.sid=s.sid
and ss.statistic#=12 and si.sid=s.sid
and bg.paddr(+)=p.addr
and round((ss.value/100),0) > 10
order by 8;
To know what the session is doing and what kind of sql it is using:

-- what kind of sql a session is using
set lines 9999
set pages 9999

select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid order by piece;

eg: sid=1853

17)Sync arch:
In a Dataguard environment we have to check primary is in sync with the secondary Database.This we can check as follows:
The V$ MANAGED_STANDBY view on the standby database site shows you the activities performed by
both redo transport and Redo Apply processes in a Data Guard environment
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
In some situations, automatic gap recovery may not take place and you will need to perform gap recovery manually. For example, you will need to perform gap recovery manually if you are using logical standby databases and the primary database is not available.
The following sections describe how to query the appropriate views to determine which log files are missing and perform manual recovery.
On a physical standby database
To determine if there is an archive gap on your physical standby database, query the V$ARCHIVE_GAP view as shown in the following example:
SQL> SELECT * FROM V$ARCHIVE_GAP;

If it displays no rows than the primary Database is in sync with the standy Database.If it display any information with row than manually we have to apply the archive logs.

After you identify the gap, issue the following SQL statement on the primary database to locate the archived redo log files on your primary database (assuming the local archive destination on the primary database is LOG_ARCHIVE_DEST_1):
Eg:
SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 7 AND 10;
Copy these log files to your physical standby database and register them using the ALTER DATABASE REGISTER LOGFILE statement on your physical standby database. For example:
SQL> ALTER DATABASE REGISTER LOGFILE
'/physical_standby1/thread1_dest/arcr_1_7.arc';
SQL> ALTER DATABASE REGISTER LOGFILE
'/physical_standby1/thread1_dest/arcr_1_8.arc';

After you register these log files on the physical standby database, you can restart Redo Apply. The V$ARCHIVE_GAP fixed view on a physical standby database only returns the next gap that is currently blocking Redo Apply from continuing. After resolving the gap and starting Redo Apply, query the V$ARCHIVE_GAP fixed view again on the physical standby database to determine the next gap sequence, if there is one. Repeat this process until there are no more gaps.

On a logical standby database:
To determine if there is an archive gap, query the DBA_LOGSTDBY_LOG view on the logical standby database. For example, the following query indicates there is a gap in the sequence of archived redo log files because it displays two files for THREAD 1 on the logical standby database. (If there are no gaps, the query will show only one file for each thread.) The output shows that the highest registered file is sequence number 10, but there is a gap at the file shown as sequence number 6:
SQL> COLUMN FILE_NAME FORMAT a55
SQL> SELECT THREAD#, SEQUENCE#, FILE_NAME FROM DBA_LOGSTDBY_LOG L
2> WHERE NEXT_CHANGE# NOT IN
3> (SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# = THREAD#)
4> ORDER BY THREAD#,SEQUENCE#;

THREAD# SEQUENCE# FILE_NAME
---------- ---------- -----------------------------------------------
1 6 /disk1/oracle/dbs/log-1292880008_6.arc
1 10 /disk1/oracle/dbs/log-1292880008_10.arc

Copy the missing log files, with sequence numbers 7, 8, and 9, to the logical standby system and register them using the ALTER DATABASE REGISTER LOGICAL LOGFILE statement on your logical standby database. For example:
SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '/disk1/oracle/dbs/log-1292880008_10.arc';

After you register these log files on the logical standby database, you can restart SQL Apply.

The DBA_LOGSTDBY_LOG view on a logical standby database only returns the next gap that is currently blocking SQL Apply from continuing. After resolving the identified gap and starting SQL Apply, query the DBA_LOGSTDBY_LOG view again on the logical standby database to determine the next gap sequence, if there is one. Repeat this process until there are no more gaps.
Monitoring Log File Archival Information:
Step 1 Determine the current archived redo log file sequence numbers.
Enter the following query on the primary database to determine the current archived redo log file sequence numbers:
SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG
WHERE STATUS='CURRENT';
Step 2 Determine the most recent archived redo log file.
Enter the following query at the primary database to determine which archived redo log file contains the most recently transmitted redo data:
SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;
Step 3 Determine the most recent archived redo log file at each destination.
Enter the following query at the primary database to determine which archived redo log file was most recently transmitted to each of the archiving destinations:
SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#
2> FROM V$ARCHIVE_DEST_STATUS
3> WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

DESTINATION STATUS ARCHIVED_THREAD# ARCHIVED_SEQ#
------------------ ------ ---------------- -------------
/private1/prmy/lad VALID 1 947
standby1 VALID 1 947
The most recently written archived redo log file should be the same for each archive destination listed. If it is not, a status other than VALID might identify an error encountered during the archival operation to that destination.
Step 4 Find out if archived redo log files have been received.
You can issue a query at the primary database to find out if an archived redo log file was not received at a particular site. Each destination has an ID number associated with it. You can query the DEST_ID column of the V$ARCHIVE_DEST fixed view on the primary database to identify each destination's ID number.
Assume the current local destination is 1, and one of the remote standby destination IDs is 2. To identify which log files are missing at the standby destination, issue the following query:
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
2> (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)
3> LOCAL WHERE
4> LOCAL.SEQUENCE# NOT IN
5> (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
6> THREAD# = LOCAL.THREAD#);

THREAD# SEQUENCE#
--------- ---------
1 12
1 13
1 14

18)Purge arch:
We have to make sure the archive logs files are purged safely or move to Tape drive or any other location in order to make space for new archive logs files in the Archive logs destination locations.

19)Recovery status:
In order to do recover make sure you are having latest archive logs,so that you can restore and do the recovery if required.


20) MY DATABASE HEALTH CHECK SCRIPT:
/* SCRIPT FOR MONITORING AND CHECKING HEALTH OF DATABASE-USEFUL FOR PRODUCTION DATABASES */

-- SHOWS RUNNING JOBS
select 'RUNNING JOBS', sid, job,instance from dba_jobs_running;
set lines 1000
-- SHOWS ARCHIVE LOGS GENERAION DETAILS HOURLY AND DATE WISE BASIS
select 'ARCHIVE LOG REPORT',to_char(first_time,'DD-MON-RR') "Date",
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') " 00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') " 01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') " 02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') " 03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') " 04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') " 05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') " 06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') " 07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') " 08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') " 09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') " 10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') " 11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') " 12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') " 13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') " 14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') " 15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') " 16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') " 17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') " 18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') " 19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') " 20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') " 21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') " 22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') " 23"
from v$log_history
group by to_char(first_time,'DD-MON-RR')
order by 1
/
-- WHAT ALL THE SESSIONS ARE GETTING BLOCKED
select 'SESSIONS BLOCKED',process,sid, blocking_session from v$session where blocking_session is not null;
-- WHICH SESSION IS BLOCKING WHICH SESSION
set lines 9999
set pages 9999
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
-- SHOWS BLOCK CHANGES DETAILS AND PHYSICAL READS DETAIL
select a.sid,b.username,block_gets,consistent_gets,physical_reads,block_changes
from V$SESS_IO a,V$SESSION b
where a.sid=b.sid and block_changes > 10000 order by block_changes desc;
-- show IO per session / CPU in seconds. sessionIOS.sql
set linesize 140
col spid for a6
col program for a35 trunc
select p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day
from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg
where s.paddr=p.addr and ss.sid=s.sid
and ss.statistic#=12 and si.sid=s.sid
and bg.paddr(+)=p.addr
and round((ss.value/100),0) > 10
order by 8;
-- SCRIPT TO IDENTIFY LONG RUNNING STATEMENTS
rem LONGOPS.SQL
rem Long Running Statements
rem Helmut Pfau, Oracle Deutschland GmbH
set linesize 120
col opname format a20
col target format a15
col units format a10
col time_remaining format 99990 heading Remaining[s]
col bps format 9990.99 heading [Units/s]
col fertig format 90.99 heading "complete[%]"
select sid,
opname,
target,
sofar,
totalwork,
units,
(totalwork-sofar)/time_remaining bps,
time_remaining,
sofar/totalwork*100 fertig
from v$session_longops
where time_remaining > 0
/
-- ACTIVE SESSIONS IN DATABASE
select 'ACTIVE SESSION', sid, serial#,machine, osuser,username,status from v$session where username!='NULL' and status='ACTIVE';
-- WHAT SQL A SESSION IS USING
set lines 9999
set pages 9999
select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid order by piece;

eg:SID=1844
I would like to add one more script which will tell me details regarding the Size of the Database used,occupied and available and Tablespace usage
details along with hit ratio of various SGA components which can be very helpfull
to monitor the performance of the Databases.

Database_monitor.sql:

ttitle "1. :============== Tablespace Usage Information ==================:" skip 2
set linesize 140
col Total format 99999.99 heading "Total space(MB)"
col Used format 99999.99 heading "Used space(MB)"
col Free format 99999.99 heading "Free space(MB)"
break on report
compute sum of Total space(MB) on report
compute sum of Used space(MB) on report
compute sum of Free space(MB) on report
select a.tablespace_name, round(a.bytes/1024/1024,2) Total,
round( nvl( b.bytes,0)/1024/1024,2) Used,
round(nvl(c.bytes, 0)/1024/1024,2) Free ,
round(nvl(b.bytes,0)*100/nvl(a.bytes,0),2) "% Used"
from sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
where a.tablespace_name=b.tablespace_name(+)
and b.tablespace_name=c.tablespace_name(+);

ttitle "2. :============== Hit Ratio Information ==================:" skip 2
set linesize 80
clear columns
clear breaks
set pagesize 60 heading off termout off echo off verify off
REM
col val1 new_val lib noprint
select 100*(1-(SUM(Reloads)/SUM(Pins))) val1
from V$LIBRARYCACHE;

ttitle off
col val2 new_val dict noprint
select 100*(1-(SUM(Getmisses)/SUM(Gets))) val2
from V$ROWCACHE;

ttitle off
col val3 new_val phys_reads noprint
select Value val3
from V$SYSSTAT
where Name = 'physical reads';

ttitle off
col val4 new_val log1_reads noprint
select Value val4
from V$SYSSTAT
where Name = 'db block gets';

ttitle off
col val5 new_val log2_reads noprint
select Value val5
from V$SYSSTAT
where Name = 'consistent gets';

ttitle off
col val6 new_val chr noprint
select 100*(1-(&phys_reads / (&log1_reads + &log2_reads))) val6
from DUAL;

ttitle off
col val7 new_val avg_users_cursor noprint
col val8 new_val avg_stmts_exe noprint
select SUM(Users_Opening)/COUNT(*) val7,
SUM(Executions)/COUNT(*) val8
from V$SQLAREA;
ttitle off
set termout on
set heading off
ttitle -
center 'SGA Cache Hit Ratios' skip 2

select 'Data Block Buffer Hit Ratio : '||&chr db_hit_ratio,
' Shared SQL Pool ',
' Dictionary Hit Ratio : '||&dict dict_hit,
' Shared SQL Buffers (Library Cache) ',
' Cache Hit Ratio : '||&lib lib_hit,
' Avg. Users/Stmt : '||
&avg_users_cursor||' ',
' Avg. Executes/Stmt : '||
&avg_stmts_exe||' '
from DUAL;

ttitle "3. :============== Sort Information ==================:" skip 2

select A.Value Disk_Sorts,
B.Value Memory_Sorts,
ROUND(100*A.Value/
DECODE((A.Value+B.Value),0,1,(A.Value+B.Value)),2)
Pct_Disk_Sorts
from V$SYSSTAT A, V$SYSSTAT B
where A.Name = 'sorts (disk)'
and B.Name = 'sorts (memory)';

ttitle "4. :============== Database Size Information ==================:" skip 2


select sum(bytes/1024/1024/1024) Avail from sm$ts_avail union all select sum(bytes/1024/1024/1024) Used from sm$ts_used union all select sum(bytes/1024/1024/1024) Free from sm$ts_free;


Hope this helps you in monitoring your Databases.



Regards,
Sukhwinder Singh

Tuesday, April 05, 2011

How to Find, Which product are installed or not installed in oracle database

1. Execute the query then value is true means installed and false means not installed.

SELECT parameter, VALUE
FROM v$option

2. in unix

$> opatch lsinventory -detail


Regards,
Sukhwinder singh

How to know the table/index uses information

1. By enabling AUDIT .

2. By QUERY THE FOLLOWING

SELECT *
FROM v$segment_statistics
WHERE owner = 'USER_NAME'
AND object_name = 'TABLE_NAME';

******************************************

select owner,object_name,sum((case when STATISTIC# = '0' then value end )) "logical reads"
,sum((case when STATISTIC# = '1' then value end )) "buffer busy waits"
,sum((case when STATISTIC# = '2' then value end )) "gc buffer busy"
,sum((case when STATISTIC# = '3' then value end )) "db block changes"
,sum((case when STATISTIC# = '4' then value end )) "physical reads"
,sum((case when STATISTIC# = '5' then value end )) "physical writes"
,sum((case when STATISTIC# = '6' then value end )) "physical reads direct"
,sum((case when STATISTIC# = '7' then value end )) "physical writes direct"
,sum((case when STATISTIC# = '8' then value end )) "null"
,sum((case when STATISTIC# = '9' then value end )) "gc cr blocks received"
,sum((case when STATISTIC# = '10' then value end )) "gc current blocks received"
,sum((case when STATISTIC# = '11' then value end )) "ITL waits"
,sum((case when STATISTIC# = '12' then value end )) "row lock waits"
,sum((case when STATISTIC# = '14' then value end )) "space used"
,sum((case when STATISTIC# = '15' then value end )) "space allocated"
,sum((case when STATISTIC# = '17' then value end )) "segment scans"
from v$segment_statistics
where owner='USER_NAME'
---and object_name='TABLE_NAME'
and object_type='TABLE'
group by owner,object_name
order by "space used" desc; 


3. OR ONLY FOR INSERT/UPDATE/DELETE

SELECT * FROM (select * from USER_tab_modifications);


Regards,
Sukhwinder Singh

Oracle Database Security Checklist

For a production Database, must need to check the following points for
better security
: -

1. Protecting the database environment.
2. Install only what is required.
3. Lock and expire default user accounts.
4. Changing default user passwords.
5. Change passwords for administrative accounts.
6. Change default passwords for all users.
7. Enforce password management.
8. Secure batch jobs.
9. Manage access to SYSDBA and SYSOPER roles.
10. Enable Oracle data dictionary protection.
11. Follow the principle of least privilege.
12. Public privileges.
13. Restrict permissions on run-time facilities.
14. Authenticate clients.
15. Restrict operating system access.
16. Secure the Oracle listener.
17. Secure external procedures.
18. Prevent runtime changes to listener.
19. Checking network IP addresses.
20. Harden the operating system.
21. Encrypt network traffic.
22. Apply all security patches.
23. Report security issues to Oracle.


Regards,
Sukhwinder singh

Kill Oracle Session

Total Number Of Active Sessions: - 

ps -ef|grep -i applvis|wc -l

First Way via SQL* PLUS utility: -

SQL> select username,sid,serial#,terminal from v$session;
SQL> alter system kill session ‘SID, SERIAL#’;

************************** **************************

Second Wayvia KILL Command (for Unix/Linux)  : -


SELECT s.sid,p.spid, s.osuser, s.program
FROM v$process p, v$session s
WHERE p.addr = s.paddr
and s.sid=&your_given_sid;


Then issue the KILL Command
$ kill -9 SPID

[Note: Do not kill the sessions at the OS level (as per as possible). 

************************** **************************


Third Wayvia ORAKILL Command (in Windows command prompt) : -

SELECT s.sid,p.spid, s.osuser, s.programFROM
v$process p, v$session s
WHERE p.addr = s.paddr
and s.sid=&your_given_sid;

Then issue the orakill Command
C:\> orakill ORACLE_SID SPID

[Note: Do not kill the sessions at the OS level (as per as possible).

************************** ************************** 


Regards,
Sukhwinder singh

Friday, April 01, 2011

Merge Patches Using admrgpch

You can merge multiple patches into a single patch by using AD Merge Patch.
This AD utility is an executable located in AD_TOP/bin that merges multiple
AutoPatch compatible patches into a single, integrated patch. Once the merge is
complete, you use AutoPatch to apply the resulting patch in a single operation.
Using a merged patch reduces the time it takes to complete the patching process.
The command for merging patches is admrgpch.


AD Merge Patch reads the c.drv, d.drv, and g.drv

for each patch in the source directory and merges them to create a single set
of driver files (for example, cmerge99.drv, dmerge99.drv, and gmerge99.drv) in
the destination directory. It also merges the set of files contained in the
individual patches under the source directory according to file revision and
copies them to the destination directory. If a file exists in more than one
source patch, only the highest revision of the file is copied to the
destination directory.


Complete these steps:

1. Review the readme files carefully.
Some patch readme files contain special instructions for applying merged
patches. The patch may also require manual steps.


2. Create directories.
In the patch top area, create a source directory and a destination directory.
Choose any name for these directories.


3. Unzip patches.
Copy all the patches to be merged into the source directory and unzip them.


4. Run AD Merge Patch.
Run AD Merge Patch and supply the arguments for the destination directory
name and the source directory name. You also need to specify the merged patch
name, or accept the default.


5. Check AD Merge Patch log files.
After AD Merge Patch runs, check the admrgpch.log file for errors. The file
is located in the current working directory (where AD Merge Patch was run).


6. Run the merged patch.
Once a merged patch is created, apply it just like a single patch.
AD Merge Patch cannot merge patches of different releases, different parallel
modes, or different platforms. However, it can merge patches for a specific
platform with a generic patch, or patches with different source character sets.
AD Merge Patch notifies you if you try to merge incompatible patches.



Regards,
Sukhwinder Singh

Tables used by adpatch

Important tables for ADPATCH utility: -

AD_APPL_TOPS
This table holds the various APPL-TOP’s in the Oracle Applications installation that have ever been patched.


AD_APPLIED_PATCHES
AD_APPLIED_PATCHES holds information about the “distinct” Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. “merged” patches), then they are considered distinct and this table will therefore hold 2 records.

AD_BUGS
AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.

AD_PATCH_DRIVERS
This table holds information about the patch drivers that comprise a patch.

AD_FILE_VERSIONS
This table holds the various versions of Oracle Applications files (real files, not “pseudo-files”), that have ever been patched or executed in the Oracle Applications installation.

AD_FILES
AD_FILES is the “files repository”. It contains information about the various files that have been patched in the Oracle Applications installation.Some entries are “pseudo-files” and not real files, (eg. directories) in which case some of the columns are not applicable and would then hold the value “DUMMY”

AD_PATCH_DRIVER_LANGS
NLS patches (or more specifically, NLS patch drivers) pertain to a language or multiple languages. This table holds that language (or multiple languages).

AD_PATCH_DRIVER_MINIPKS
This table holds information about the various Mini Packs contained in a patch (driver)

AD_PATCH_RUN_BUG_ACTIONS 

holds the various actions present in “applied” bug (fix). If Autopatch determined not to apply a bug (fix), then this table will not hold any records for that “unapplied” bug fix.

AD_PATCH_RUN_BUG_ACTIONS
Even though a patch may have been applied on an Oracle Applications installation, some actions in some of its included bugs (fixes) may not have got executed if the “Autopatch” utility determined that it was not necessary to execute those actions. In such cases, EXECUTED_FLAG is set to N.

AD_PATCH_RUN_BUGS
This table holds information about the bugs fixed in a specific run of Autopatch.AD_PATCH_RUN_BUGS holds information about the various bugs fixed in a specific run of Autopatch.
Even though a patch may have been applied on an Oracle Applications installation, some bugs (fixes) contained in it may not get applied due to some reason. In such cases, the REASON_NOT_APPLIED column holds the reason.

AD_PATCH_RUNS
AD_PATCH_RUNS holds information about the various invocations of Autopatch for applying Oracle Applications patches to a specific release of an Oracle Applications installation.
If multiple drivers are run in one invocation of Autopatch, they result in multiple records in this table. These multiple records will all have the same SESSION_ID (because they arose from one Autopatch invocation), but different TASK_NUMBER’s. The TASK_NUMBER’s in this case will be numbered sequentially as 1, 2, 3, etc.Note that when the database driver of a Maintenance Pack is applied, it bumps up the release version by creating a new record in
AD_RELEASES, which is then pointed to by the UPDATED_TO_RELEASE_ID column of the old record.

AD_RELEASES
AD_RELEASES holds the various Oracle Applications releases that an installation of Oracle Applications has gone through in its entire life cycle.
It should be noted that START_DATE_ACTIVE, END_DATE_ACTIVE and BASE_RELEASE_FLAG are loosely-maintained informational columns and are not accurately maintained, and therefore should not be relied upon heavily.

AD_PATCH_COMMON_ACTIONS
This table holds distinct information about the various actions that are (often repeatedly) performed by Autopatch as part of applying patches.



Regards,
Sukhwinder singh

Linux Server Sends Email Alert when logged in as Root

We can enable the linux server to automatically send a notification email to predefined email address everytime someone logs in as root to the host. To configure the automatic email alert notification to a default email address on each incident of root log on on the server, use the following guide.
Login to the server using as root ID.
Ensure that you’re at home directory of root. open the .bash_profile for editing using vi editor as shown below


vi .bash_profile

Scroll down to the end of the file and add the following line:

echo 'ALERT - Root Shell Access on:' `date` `who` mail -s "Alert: Root Access from `who cut -d"(" -f2 cut -d")" -f1`" user@example.com

 
Replace user@email.com with the actual email account address that you want to the root access alert notification been sent to. Note that you can change the text contains in the email alert too. The text starting with first ALERT is written as email body, and you can add in other info such as host name or change the wordings. The second Alert is the email title which you can enter your own text.



Regards,
Sukhwinder Singh

The road gladdens the obsessed ghost.

The road gladdens the obsessed ghost.

To drop and recreate UNDO Tablespace in Oracle 9i/10g/11g

 1. Determine the size of your undo tablespace

SQL> select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='UNDOTBS1';

SUM(BYTES)/1024/1024/1024
-------------------------
                12.09375
               
2. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.
SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\UNDOTBS02.DBF' size 400M;

    Tablespace created.
   
3. Edit your init.ora file and change the parameter "undo_tablespace=UNDOTBS2" so it points to the newly created tablespace.
    Change undo_tablespace=UNDOTBS2
    Change undo_management=MANUAL

    Setting undo_management now ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again in Step 7.
   
4. Arrange a time when the database can be shutdown cleanly and perform a shutdown immediate.   

5. Startup the database

6. Confirm the new tablespace is in use:

    SQL> show parameter undo_tablespace

    NAME                 TYPE              VALUE
    -------------        -----------       ------------
    undo_tablespace      string            UNDOTBS2
   
7. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline.
    SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

    OWNER         SEGMENT_NAME         TABLESPACE_NAME         STATUS
    ------              --------------------------- ------------------------------ -----------
    PUBLIC         _SYSSMU3$                      UNDOTBS1                         OFFLINE
    PUBLIC         _SYSSMU2$                      UNDOTBS1                         OFFLINE
    PUBLIC         _SYSSMU19$                     UNDOTBS2                         OFFLINE

    ....etc.

    If the old segments are online, then they must be taken offline:

    SQL>alter rollback segment "_SYSSMU3$" offline;
    SQL>alter rollback segment "_SYSSMU2$" offline;

    This should be executed for all online rollback segments in the old tablespace.

8. Provided all the segments in the old undo tablespace are offline, you can now drop the old undo tablespace:
    SQL>drop tablespace UNDOTBS1 including contents and datafiles;

    Tablespace dropped.

9. Edit your init.ora file do the following changes.

    undo_management='AUTO'
    undo_tablespace='UNDOTBS2'
   
10. Shutdown the database (shutdown immediate) and restart it.

11. Monitor the alert log simultaneously during all steps.


Regards,
Sukhwinder Singh

To drop and recreate TEMP Tablespace in Oracle 9i/10g/11g

1. Create Temporary Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘/u01/app/oradata/temp01′ SIZE 2000M, ‘/u01/app/oradata/temp02′ SIZE 2000M';

2. Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Make sure No sessions are using your Old Temp tablespace

   a.  Find Session Number from V$SORT_USAGE:
       SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

   b.  Find Session ID from V$SESSION:

       If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
       OR
       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

    c.  Kill Session:

    Now kill the session with IMMEDIATE.

    ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

4. Drop temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

5. Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u01/app/temp/temp01′ SIZE 2000M;

6 Move Tablespace Temp, back to new temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

7. Drop temporary for tablespace temp

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

 No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.


Regards,
Sukhwinder Singh

Imp Questions for Oracle EBS DBA

1)What happens if the ICM goes down?

All the other managers will keep working. ICM only takes care of the queue control requests, which means starting up and shutting down other concurrent managers.



2)How will you speed up the patching process?

You can merge multiple patches.
You can create a response file for non-interactive patching.
You can apply patches with options (nocompiledb, nomaintainmrc, nocompilejsp) and run these once after applying all the patches.


3)How will you handle an error during patching?

Look at the log of the failed worker, identify and rectify the error and restart the worker using adctrl utility.


4)Provide a high-level overview of the cloning process and post-clone manual steps.

Run pre-clone on the source (all tiers), duplicate the DB using RMAN (or restore the DB from a hot or cold backup), copy the file systems and then run post-clone on the target (all tiers).
Manual steps (there can be many more):
Change all non-site profile option values (RapidClone only changes site-level profile options).
Modify workflow and concurrent manager tables.
Change printers.

5)Provide an introduction to AutoConfig. How does AutoConfig know which value from the XML file needs to be put in which file?

AutoConfig uses a context file to maintain key configuration files. A context file is an XML file in the $APPL_TOP/admin directory and is the centralized repository.
When you run AutoConfig it reads the XML files and creates all the AutoConfig managed configuration files.
For each configuration file maintained by AutoConfig, there exists a template file which determines which values to pick from the XML file.


6)Can you tell me a few tests you will do to troubleshoot self-service login problems? Which profile options and files will you check?

Check guest user/password in the DBC file, profile option guest user/password, the DB.
Check whether apache/jserv is up.
Run IsItWorking, FND_WEB.PING, aoljtest, etc.

7)What could be wrong if you are unable to view concurrent manager log and output files?

Most likely the FNDFS listener is down. Look at the value of OUTFILE_NODE_NAME and LOGFILE_NODE_NAME in the FND_CONCURRENT_REQUESTS table. Look at the FND_NODES table. Look at the FNDFS_entry in tnsnames.ora.


8)How will you change the location of concurrent manager log and output files?

The location of log files is determined by parameter $APPLCSF/$APPLLOG and that of output files by $APPLCSF/$APPLOUT.



9)If the user is experiencing performance issues, how will you go about finding the cause?

Trace his session (with waits) and use tkprof to analyze the trace file.
Take a statspack report and analyze it.
O/s monitoring using top/iostat/sar/vmstat.
Check for any network bottleneck by using basic tests like ping results.


10)How will you change the apps password?

Use FNDCPASS to change APPS password.
Manually modify wdbsvr.app/cgiCMD.dat files.
Change any DB links pointing from other instances.


11)Provide the location of the DBC file and explain its significance and how applications know the name of the DBC file.

Location: $FND_TOP/secure directory.
Significance: Points to the DB server amongst other things.
The application knows the name of the DBC file by using profile option "Applications Database Id."


12) What's the diff. btw. 'adconfig.sh' and 'adautocfg.sh'

You will use script adautocfg.sh (on both Database & Application tier) which inturn will call adconfig.sh , which further will calladconfig.pl. You should not worry about later two scripts its for information only. adautoconfig.sh is script which you use to run autoconfig.


13) Do I need to run autoconfig on both appl and db node after I change user applsys password.

yes, first on db tier and next on apps tier


14) Must I stop all running application processes before I run autoconfig. i.e by running adstpall.sh?


Yes, if you are running autoconfig on application tier u need to stop the application tier, always database should be up



15) Is it better to run adautocfg.sh on 11.5.10.2 rather that adconfig.sh.

Refer question 1, alway we run adautocfg.sh


16) What are .lct and .ldt files in Patch Directory?

The patch metadata LDT files (also called datafiles) are FNDLOAD data files included in the top-level directory of all recent patches. The LDT files contain prerequisite patch information and a manifest of all files in the patch with their version numbers. The Patch Information Bundle metadata also include information about the relationships between patches, such as which minipacks are contained in the recommended.

LCT files (also called configfiles) are the configuration files which are used to download/upload data. Without configfiles, datafiles are useless.



17) If Ad worker fails during Ad patching, How many times by default adpatch automatically tries to resume the patching?
Three Times


18) While trying to change one profile option at site level, the option is not editable mode? How to make it editable mode?
Goto Application Developer -> Profiles -> Give Profile Options Names -> Check updatable

 
19) How do you check Compatibility of Oracle Applications with Any operating System?
Metalink -> Certify Tab -> View Certification by platform

 
20) How do you check Latest CPU Patch for Oracle Server and Applications?

Metalink -> Patches & Updates Tab

 
21) In RAC env, Each node contains How many IPs?

Three

 
22) What are the tables updated when you apply application patch?

ad_applied_patches and ad_bugs

 
23) Can you apply Opatch without inventory?

No

 
24) If there is no inventory, How do you apply a opatch?

Create inventory using runInstaller

 
25) What are the tables get created during Apps Patching?

ad_deferred_jobs and fnd_installed_processes

 
26) Default environment variable to be set for Forms Config files?

FORMS60_WEB_CONFIG_FILE

 
27) Profile option to determine which dbc file to use?

Application Database ID

 
28) How do you hide apps password during adpatching?

adpatch flags=hidepw

 
29) What is inter operability patch?

OS compatibility patch, mostly applied during upgradation

 
30)  How do you compile jsp files?

Using adadmin or ojspCompile(perl -x $JTF_TOP/admin/scripts/ojspCompile.pl)

 
31) What is cache in Concurrent Managers Definition?

No of concurrent requets that have to be cached from fnd_concurrent_requests while reading fnd_concurrent_requests

 
32) Types of profile options?

1. Site level
2. User level
3. Responsibility Level
4. Server Level
5. Application Level

 
33) Opatch log file location ?

$ORACLE_HOME/.patch_storage/patch_number/*.log

 
34) Different levels of SQL Tracing?

Regular (Level 1 – standard/default level)
Level 4 (standard + binds)
Level 8 (standard + waits)
Level 12 (standard + binds and waits)

 
35)  If you lost all redo logs files during DB is up and running? What will happen how do you recover it?

DB will get crashed immediately

Solution;

1. You have to go for Incomplete Recovery
2. One way: Take previous backup, recover up to last archive and open the database
3. Second way: open the database in no mount state, create control file with rest logs and open the database with rest logs.

 
36) DB is up and running fine? you lost one data file? DB is in archive log mode? How do you recover it?

1. If you have a backup of datafile, restore it and apply archives.
2. If you don't have backup of datafile, create datafile in database and apply archives.

 
37) How do you clone a context file or how do you change existing port pool?

Using adclonectx.pl, you can clone next context file, during cloning you can give new port pool, and run autoconfig

 
38)  How do you run autoconfig in test mode?

adchkcfg.sh (AD_TOP/bin)

 
39) If you lost dbc file, How will you recover it?

Using adgendbc($AD_TOP/bin) or run Autoconfig 



Regards,
Sukhwinder Singh

Queries : Application

To find the responsibilty for which form was assigned

SELECT fu.user_name,fu.description,furg.start_date,
frvl.responsibility_name,fff.function_name,ff.form_name
FROM fnd_user fu,fnd_user_resp_groups furg,fnd_responsibility_vl frvl,
fnd_compiled_menu_functions fcmf,fnd_form_functions_vl fff,fnd_form_vl ff
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = frvl.responsibility_id
AND frvl.menu_id = fcmf.menu_id
AND fff.function_id = fcmf.function_id
AND fff.form_id = ff.form_id
AND (fu.end_date IS NULL OR fu.end_date >= SYSDATE)
AND (furg.end_date IS NULL OR furg.end_date >= SYSDATE)
AND (frvl.end_date IS NULL OR frvl.end_date >= SYSDATE)
AND form_name IN ('FORMNAME');



To find the latest application version 


select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,
ROW_SOURCE_COMMENTS "how it is done",
BASE_RELEASE_FLAG "Base version"
FROM AD_RELEASES
where END_DATE_ACTIVE IS NULL;


To find the base application version 


select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated,
ROW_SOURCE_COMMENTS "how it is done"
from AD_RELEASES
where BASE_RELEASE_FLAG = 'Y' ;


To find all available application version


select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated,
END_DATE_ACTIVE "when lasted",
CASE WHEN BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE 'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done"
from AD_RELEASES;


How many users are connected to Oracle Applications.

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;



HOW TO find a concurrent program's trace file

SELECT req.request_id ,req.logfile_node_name node
,req.oracle_Process_id ,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name ,phase_code
,status_code ,ses.SID ,ses.serial# ,ses.module
,ses.machine

FROM  fnd_concurrent_requests req
,v$session ses ,v$process proc ,v$parameter dest
,v$parameter dbnm ,fnd_concurrent_programs_vl prog
,fnd_executables execname

WHERE 1=1
AND req.request_id = &request
AND req.oracle_process_id=proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME='user_dump_dest'
AND dbnm.NAME='db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id=execname.executable_id;




Regards,
Sukhwinder Singh

Queries : Related to Patches

To find out if any patch except localisation patch is applied or not, if applied, that what all drivers it contain and time of it's application


select A.APPLIED_PATCH_ID, A.PATCH_NAME,
A.PATCH_TYPE,
B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME,
B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE,
B.CREATION_DATE, B.FILE_SIZE,
B.MERGED_DRIVER_FLAG, B.MERGE_DATE
from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B
where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID
and A.PATCH_NAME = '<patch number>' ;

To know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id ... patch run id 


select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME,
A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID,
A.PATCH_TOP, A.START_DATE, A.END_DATE,
A.SUCCESS_FLAG, A.FAILURE_COMMENTS

from AD_PATCH_RUNS A, AD_APPL_TOPS B,
AD_PATCH_DRVIERS C, AD_APPLIED_PATCHES D

where A.APPL_TOP_ID = B.APPL_TOP_ID
AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID
and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID
and A.PATCH_DRIVER_ID
in (select PATCH_DRIVER_ID
from AD_PATCH_DRIVERS
where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID
from AD_APPLIED_PATCHES  
where PATCH_NAME = '<patch number>')) ORDER BY 3;


To get information related to how many time driver file is applied for bugs


select * from AD_PATCH_RUN_BUGS
where BUG_ID in (select BUG_ID
from AD_BUGS
where BUG_NUMBER = '<BUG NUMBER>');


To find latest patchset level for module installed


select APP_SHORT_NAME, max(PATCH_LEVEL)
from AD_PATCH_DRIVER_MINIPKS
GROUP BY APP_SHORT_NAME;


To find what is being done by the patch


select A.BUG_NUMBER "Patch Number",
B. PATCh_RUN_BUG_ID "Run Id",
D.APP_SHORT_NAME appl_top, D.SUBDIR,
D.FILENAME, max(F.VERSION) latest,
E.ACTION_CODE action

from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F  
where A.BUG_ID = B.BUG_ID
and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID
and C.FILE_ID = D.FILE_ID
and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID
and D.FILE_ID = F.FILE_ID  
and A.BUG_NUMBER = '<patch number>'
and B.PATCH_RUN_BUG_ID = ' < > '  
and C.EXECUTED_FLAG = 'Y'  

GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID,
D. APP_SHORT_NAME, D>SUBDIR,
D.FILENAME, E.ACTION_CODE ;


To find Merged patch Information from database in Oracle Applications


select bug_number  
from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches  
where patch_driver_id =(select patch_driver_id
from ad_patch_drivers  
where applied_patch_id =&n) );

  
To know, what all has been done during application of PATCH


Select J.PATCh_NAME,
H.APPLICATIONS_SYSTEM_NAME Instance_Name,
H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top,
D.SUBDIR, D.FILENAME, max(F.VERSION) latest,
E.ACTION_CODE action  

from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F,
AD_PATCH_RUNS G, AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J

where A.BUG_ID = B.BUG_ID
and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID
and C.FILE_ID = D.FILE_ID  
and E.COMMON_ACION_ID = C.COMMON_ACTION_ID  
and D.FILE_ID = F.FILE_ID  
and G.APPL_TOP_ID = H.APPL_TOP_ID  
and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID
and I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID  
and B.PATCH_RUN_ID = G.PATCH_RUN_ID  
and C.EXECUTED_FLAG = 'Y'  
and G.PATCH_DRIVER_IDin (select PATCH_DRIVER_ID
from AD_PATCH_DRIVERS  
where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID  
from AD_APPLIED_PATCHES  
where PATCH_NAME = '<Patch Number>'))  

GROUP BY J.PATCH_NAME, H.APPLICATINS_SYSTEM_NAME,
H.NAME, I.DRIVER_FILE_BNAME, D.APP_SHORT_NAME,
D.SUBDIR, D.FILENAME, E.ACTION_CODE ;


To get file version of any application file which is changed through patch application


select A.FILE_ID, A.APP_SHORT_NAME,
A.SUBDIR, A.FILENAME, max(B.VERSION)
from AD_FILES A, AD_FILE_VERSIONS B
where A.FILE_ID = B.FILE_ID
and B.FILE_ID = 86291
group by A.FILE_ID, A.APP_SHORT_NAME,
A.SUBDIR, A.FILENAME;



To find out Patch level of mini Pack


Select product_version,patch_level  
from FND_PROUDCT_INSTALLATIONS  
where patch_level like '%&shortname%';

Replace short name by name of Oracle Apps Minipack for which you want to find out Patch level . ex.
AD - for Applications DBA
GL - for General Ledger
PO - Purchase Order 




Regards,
Sukhwinder Singh