DBA Imporatant queries .
How to find session details using SPID:
col STATUS for a14
col USERNAME for a14
col OSUSER for a14
col SERVER for a14
col MACHINE for a28
col PROGRAM for a28
col MODULE for a28
col EVENT for a30
set lines 300 pages 3000
select p.Spid,s.SID,s.SERIAL#,s.STATUS,s.USERNAME,s.OSUSER,s.MACHINE,s.PROGRAM,s.MODULE,
to_char(s.LOGON_TIME,'DD-MM-YYYY HH24:MI:SS') Logon_Time,s.LAST_CALL_ET,s.EVENT,s.SQL_ID
from v$session s,v$process p where s.paddr=p.addr and p.spid=&spid;
How to find session details using PID:
SELECT 'USERNAME : ' || s.username || CHR (10)
|| 'SCHEMA : ' || s.schemaname || CHR (10)
|| 'OSUSER : ' || s.osuser || CHR (10)
|| 'PROGRAM : ' || s.program || CHR (10)
|| 'MACHINE : ' || s.machine || CHR (10)
|| 'TERMINAL : ' || s.terminal || CHR (10)
|| 'SPID : ' || p.spid || CHR (10)
|| 'SID : ' || s.sid || CHR (10)
|| 'SERIAL# : ' || s.serial# || CHR (10)
|| 'TYPE : ' || s.TYPE || CHR (10)
|| 'SQL ID : ' || q.sql_id || CHR (10)
|| 'CHILD_NUMBER : ' || q.child_number || CHR (10)
|| 'SQL TEXT : ' || q.sql_text
RESULT
FROM v$session s, v$process p, v$sql q
WHERE s.paddr = p.addr AND s.sql_id = q.sql_id(+) AND p.spid = '&MY_PID';
How to find session details using SID:
set lines 999
col event for a20
col username for a10
select sid,serial#,paddr,username,status,process, event,sql_id,PREV_SQL_ID,last_call_et,BLOCKING_SESSION,SECONDS_IN_WAIT
from v$session where sid= &SID;
Blocking & Locking query :
SELECT l1.sid || ' is blocking ' || l2.sid blocking_sessions 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;
col SQL_TEXT for a40
select s.sid,s.serial#,s.status,s.program,s.event,s.sql_id,b.sql_text from v$session s, v$sqlarea b where s.sql_address=b.address and s.sid='&blockingid'
############################Concurrent Manager Query###########################
How to find Actual & Target using query:
-------------------------------------------------------
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');
Query for Identifying correct trace file for request id
PROMPT IDENTIFY CONCURRENT REQUEST FILE
PROMPT From Bug.3211206
PROMPT Use the following query to identify the correct trace file:
PROMPT where "request" is the concurrent request id for the inventory transaction
PROMPT worker.
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
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 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;
How to submit Active user request using backend:
------------------------------------------------------------
CONCSUB apps/passwdtest12 SYSADMIN 'System Administrator' SYSADMIN WAIT=N CONCURRENT FND FNDSCURS
How to find request ID using query:
-----------------------------------------------
set lines 180
set pages 300
col name format a20
col QUEUE for a20
col U_NAME for a20
select fcr.request_id req_id,
substr(fcq.concurrent_queue_name, 1, 20) queue,
to_char(fcr.actual_start_date,'hh24:mi') s_time,
substr(fcr.user_concurrent_program_name, 1, 60) name,
substr(fcr.requestor, 1, 9 ) u_name,
round((sysdate -actual_start_date) *24, 2) elap,
decode(fcr.phase_code,'R','Running','P','Inactive','C','Completed', fcr.phase_code) Phase,
substr(decode( fcr.status_code, 'A', 'WAITING', 'B', 'RESUMING',
'C', 'NORMAL', 'D', 'CANCELLED', 'E', 'ERROR', 'F', 'SCHEDULED',
'G', 'WARNING', 'H', 'ON HOLD', 'I', 'NORMAL', 'M', 'NO MANAGER',
'Q', 'STANDBY', 'R', 'NORMAL', 'S', 'SUSPENDED', 'T', 'TERMINATED',
'U', 'DISABLED', 'W', 'PAUSED', 'X', 'TERMINATED', 'Z', 'WAITING',
'UNKNOWN'), 1, 10)
from
apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_processes fcp,
apps.fnd_conc_req_summary_v fcr
where fcp.concurrent_queue_id = fcq.concurrent_queue_id
and fcp.queue_application_id = fcq.application_id
and fcr.controlling_manager = fcp.concurrent_process_id
and fcr.request_id = '&RequstID'
order by request_id ;
How to find pending request using query:
---------------------------------------------------
select count(1)
from apps.fnd_concurrent_requests Fcr,
apps.Fnd_Concurrent_Programs Fcp,
apps.fnd_oracle_userid O
where Status_Code = 'Q'
And (Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id
And Program_Application_ID = Application_ID )
And Hold_Flag = 'N'
And Fcr.Oracle_ID = O.Oracle_ID
And Requested_Start_Date <= Sysdate
Pending request query:
---------------------------
select COUNT (distinct cwr.request_id) Peding_Requests 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 and cq.user_concurrent_queue_name
in ( select unique user_concurrent_queue_name from apps.fnd_concurrent_queues_tl);
##################################Tablespace query############################
Find Tablespace total size:
-------------------------------------
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
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 ;
Individual tablespace percentage :
---------------------------------
set pages 200
set line 180
col file_name for a60
col TABLESPACE_NAME for a25
SELECT A.TABLESPACE_NAME,round(SUM(A.TOTS)/1024/1024) "Tot size MB",
round(SUM(A.SUMB)/1024/1024) "Tot Free MB",
round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%FREE",
100-round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%USED",
round(SUM(A.LARGEST)/1024/1024) MAX_FREE,SUM(A.CHUNKS) CHUNKS_FREE
FROM (
SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
MAX(BYTES) LARGEST,COUNT(*) CHUNKS
FROM SYS.DBA_FREE_SPACE A
GROUP BY TABLESPACE_NAME
UNION
SELECT TABLESPACE_NAME,SUM(BYTES) TOTS,0,0,0
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A, V$INSTANCE B
where A.TABLESPACE_NAME=upper('&ts')
GROUP BY UPPER(B.INSTANCE_NAME),A.TABLESPACE_NAME
Find tablespace with its datafiles:
----------------------------------------
select df.FILE_ID,df.FILE_NAME,df.TABLESPACE_NAME,round(df.BYTES/1024/1024) Allocated,
round(nvl(dfs.BYTES/1024/1024,0)) FREE_SPACE, round(((df.BYTES/1024/1024)-nvl((dfs.BYTES/1024/1024),0))) USED_SPACE, df.AUTOEXTENSIBLE,
df.maxbytes/1024/1024 maxsize_mb,(df.maxbytes-df.bytes)/1024/1024 Space_for_maxsize
from dba_data_files df,
(select file_id,tablespace_name,sum(bytes) bytes
from dba_free_space
group by file_id,tablespace_name) dfs
where df.FILE_ID=dfs.FILE_ID (+)
AND df.TABLESPACE_NAME=upper('&ts')
order by df.FILE_ID
Individual table percentage:
--------------------------------------
col file_name for a45
select file_id,file_name,tablespace_name,bytes/1024/1024/1024,autoextensible,maxbytes/1024/1024/1024 from dba_data_files where tablespace_name='&ts_name'
order by 4;
How to add datafile into Tablespace (ASM):
-------------------------------------------------------
alter tablespace < Tablespacename> add datafile <+ ASM Group name> size 30G;
alter tablespace APPS_TS_TX_IDX add datafile '+PRDATA_DG1' size 30G;
How to resize datafile:
alter database datafile 76 resize 15G;
alter database datafile '/u02/oradata/prod/datafile/apps_ts_summary.274.789676071' resize 27G;
alter tablespace APPS_TS_TX_IDX add datafile '/u07/oracle/proddata/a_txn_ind43.dbf' size 1g autoextend on next 100m maxsize 30G;
###############Rman backup check query########################################
select session_key,
input_type,
status,
to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
output_bytes_display,
time_taken_display
from v$rman_backup_job_details
order by session_key asc;
RMAN Backup:
set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a100 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;
SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS TOTAL_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS USED_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
set pagesize 2000
set linesize 2000
select
output
from
GV$RMAN_OUTPUT
where
session_recid =
(
select
session_recid
from
V$RMAN_BACKUP_JOB_DETAILS
where
session_key=(select max(session_key) from v$RMAN_BACKUP_JOB_DETAILS)
);
###############################################Basic RAC Commands#######
crsctl start crs
srvctl start asm
srvctl start database -d <dbname>
crs_stat -t
crs_stat -t -v
crsctl check crs
crsctl check cluster
crsctl check cluster -all
Starting cluster:
ps -eaf|grep d.bin
./crsctl check crs
./crsctl stop crs
./crsctl start crs
./crsctl config crs
./crsctl check cluster
./crsctl check cluster -all
Database:
srvctl status -d <dbname>
srvctl stop -d <dbname>
srvctl start -d <dbname>
srvctl config -d <dbhome>
Startup shutdown procedure for RAC:
-----------------------------------
srvctl status database -d <dbname> -i <instanceName> --> stoping rac instance
srvctl stop database -d oradb
srvctl status database -d oradb
ASM:
srvctl stop asm -n node1 -f
srvctl stop asm -n node2 -f
srvctl status asm -n node1
srvctl status asm -n node2
shutdown node application:
--------------------------
srvctl stop nodeapps -n node1 -f
srvctl status nodeapps -n node1
shutdown down the clusterware
------------------------------
crsctl check cluster -all
crsctl stop crs
crsctl check cluster -all
start oracle clusterware :
-----------------------
crsctl start crs
crsctl check cluster -all
select instance_name, host_name, archiver, thread#, status from gv$instance.
Querying the v$asm_diskgroup view...
select group_number, name,allocation_unit_size alloc_unit_size,state,type,total_mb,usable_file_mb
from v$asm_diskgroup;
Querying v$asm_disk for our volumes (remember the ones we created first on OS level with the asmlib) :
select name, path, header_status, total_mb free_mb, trunc(bytes_read/1024/1024) read_mb, trunc(bytes_written/1024/1024) write_mb
from v$asm_disk;
RAC INSTANCE SPECIFIC COMMANDS:
-------------------------------
srvctl status instance -d <db_name> -i <instance_name>
srvctl stop instance -d <db_name> -i <instance_name>
Relocating scan vip from node one to another node:
-------------------------------------------------
srvctl status scan
srvctl relocate scan -h
srvctl relocate scan -i 2 -n <nodename>
STOP SEQUENCE
COMMAND DESCRIPTION
------------------------------------------------------------------------------------------
srvctl stop database -d ORCL STOP DATABASE
srvctl stop instance -d ORCL -i ORCL1 STOP first INSTANCE (skip it if running 'stop database' as that will stop both instances)
srvctl stop instance -d ORCL -i ORCL2 STOP second INSTANCE (skip it if running 'stop database' as that will stop both instances)
srvctl stop asm -n NODE1 STOP ASM INSTANCES on NODE 1 (In 11G , we have OCR on ASM so we cannot stop ASM, but if you have OCR in NON-ASM you should stop it)
srvctl stop asm -n NODE2 STOP ASM INSTANCES on NODE 2 (In 11G , we have OCR on ASM so we cannot stop ASM, but if you have OCR in NON-ASM you should stop it)
srvctl stop nodeapps -n NODE1 STOP NODEAPPS on NODE 1
srvctl stop nodeapps -n NODE2 STOP NODEAPPS on NODE 2
/etc/init.d/init.crs stop STOP CRS PROCESSES (AS ROOT USER)
START SEQUENCE
COMMAND DESCRIPTION
-------------------------------------------------------------------------------------------
/etc/init.d/init.crs start START CRS PROCESS (AS ROOT USER)
srvctl start asm -n NODE1 START ASM INSTANCE on node 1
srvctl start asm -n NODE2 START ASM INSTANCE on node 2
srvctl start database -d ORCL START DATABASE
srvctl start instance -d ORCL -i ORCL1 START first INSTANCE (skip it if running 'start database' as that will start both instances)
srvctl start instance -d ORCL -i ORCL2 START second INSTANCE (skip it if running 'start database', as taht will start both instances)
srvctl start nodeapps -n NODE1 START NODEAPPS on NODE1
srvctl start nodeapps -n NODE2 START NODEAPPS ON NODE2
connect usig user:
set define off
@scrriptname:
OTHER USEFUL COMMANDS
COMMAND DESCRIPTION
---------------------------------------------------------------------
crsctl status resource -t Clusterware Resource Status Check
srvctl status database -d ORCL STATUS OF DATABASE
srvctl stop listener -l LISTENER_NAME STOP A LISTENER
srvctl start listener -l LISTENER_NAME START A LISTENER
crsctl stop has stop all the clusterware services/ resources on specific node (including DB and listener) (run as root)
crsctl start has start all the clusterware services/ resources on specific node (including DB and listener) (run as root)
crsctl stop cluster -all to stop csr services on all nodes of clusterware (run as root)
crsctl start cluster -all to start crs services on all nodes of clusterware (run as root)
crsctl check has to check if ohasd is running/ stopped (run as root)
crsctl enable has enable Oracle High Availability Services autostart (run as root)
crsctl disable has disable Oracle High Availability Services autostart (run as root)
crsctl config has check if Oracle High Availability Services autostart is enabled/ disabled (run as root)
srvctl status nodeapps to check the status of services on all nodes
crsctl stop crs stop all the clusterware services/ resources ON THAT NODE! (run as root)
crsctl start crs start all the clusterware services/ resources ON THAT NODE! (run as root)
cluvfy comp scan -verbose Verifying scan status scan_listener
srvctl config scan_listener Verifying scan port
srvctl relocate scan -i 1 -n NODE1 Relocate scan listener 1 to the mentioned node
- Enable the crs, css, and evm daemons in /etc/inittab
2 - Execute /etc/init.d/init.crs enable
3 - Execute /etc/init.d/init.crs start
4 - Execute $ORA_CRS_HOME/bin/crsctl start crs
###########################How to create TAR File ##################################
---------------------------------------------------
Creating tar file:(compress )
-----------------
nohup tar -cvzf tech_st.gz tech_st &
Untar file :
-------------
nohup tar -xvzf tech_st.gz -C /u01/app/DEV > /tmp/apps
or
untar ---> nohup tar -xvzf tech_st.gz -C /u01/dbTier/VIS/db/ > /u01/dbTier/VIS/db/untar.log &
scp file to destination:
-----------------------
step 1:
nohup scp apps.gz oratest@<ipaddress>:/u01/app/DEV > scp.out 2>&1
ste 2:
Enter password
step 3:
ctr + Z
step 4:
bg <jobid>
NOV@2017
192.9.121.151 oraprod/oraprod
/data/RDCPRD_RMAN_BKP
nohup tar -cvzf /data/RDCPRD_RMAN_BKP/1120.tar.gz 11.2.0 > /tmp/tar1.log &
How to check fnd_product_level in oracle apps .
SQL> select
abbreviation
,codelevel
from
ad_trackable_entities
where
abbreviation in( 'ad','txk','fnd','fwk','atg_pf','icx','bne' )
order by
abbreviation;
################################
How to find table size in database.
SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024 * 1024)) AS GB FROM DBA_SEGMENTS DS WHERE SEGMENT_NAME = '&tableName'
GROUP BY DS.TABLESPACE_NAME, SEGMENT_NAME;
Comments
Post a Comment