Oracle – Query locking
Display locks in Oracle database [Look for UBABLK in gv$transaction – if value remains static for a while, the transaction is potentially blocked
Oracle – RMAN – Display RMAN Backups
RMAN -Display RMAN Backups — display rman backups select j.session_recid, j.session_stamp, to_char(j.start_time, ‘dd.mm.yyyy hh24:mi:ss’) start_time, to_char(j.end_time, ‘dd.mm.yyyy 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’,…
Oracle – Display Hidden Initialization Parameters
Hidden Oracle Initialization Parameters To display hidden Oracle initialization parameters: select ksppinm, ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and substr(ksppinm,1,1) = ‘_’;
Oracle – TFA – Trace File Analyzer Collector
See MOS: Oracle SSO account required. My Oracle Support note 1513912.1 “TFA Collector – Tool for Enhanced Diagnostic Gathering” at https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1513912.1
Oracle – Direct NFS with a Windows NFS Server
Scenario : Oracle Direct NFS, NFS on Windows Server you are using Oracle Direct NFS, and the NFS is running on Windows, there is a potential issue with permissions and ownership changes required. Example An…
Oracle ASM – List ASM files
SELECT concat(‘+’||gname, sys_connect_by_path(aname, ‘/’)) full_path, gnumber group_number, gname group_name, file_number, system_created, nvl(file_type, ‘Directory’) file_type, decode(file_type, null, ‘N’, decode (system_created, ‘N’, ‘Y’, ‘N’)) file_alias, block_size, blocks, bytes, space, redundancy, striped, creation_date, modification_date , redundancy_lowered FROM (SELECT…
Oracle – FRA – display usage of flash recovery area
select round(space_used/(1024*1024*1024),2) GBUsed,space_limit/(1024*1024*1024) MaxGB from v$recovery_file_dest — init parameter log_archive_dest_1 must be equal to LOCATION=USE_DB_RECOVERY_FILE_DEST — and db_recovery_file_dest must be set select * from v$flash_recovery_area_usage
Oracle RAC – Display differences between instance initialization parameter values
SELECT p1.name, p1.value val_inst1, p2.value val_inst2 FROM gv$parameter p1 JOIN gv$parameter p2 ON p1.name = p2.name WHERE p1.inst_id = 1 AND p2.inst_id = 2 AND p1.value != p2.value AND p1.name NOT IN (‘instance_number’, ‘instance_name’, ‘local_listener’,’thread’,’undo_tablespace’,’core_dump_dest’)
Oracle – RMAN DROP DATABASE
— set ORACLE env first export ORACLE_SID=ORCL12 export ORACLE_HOME=/oracle/….. oracle@oravm:~/ [ORCL12] rman target / Recovery Manager: Release 12.1.0.1.0 – Production on Wed Dec 27 09:45:33 2017 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All…
Oracle – find sessions using database links
Run on both databases: select /*+ ORDERED */ substr(s.ksusemnm,1,10)||’-‘|| substr(s.ksusepid,1,10) “ORIGIN”, substr(g.K2GTITID_ORA,1,35) “GTXID”, substr(s.indx,1,4)||’.’|| substr(s.ksuseser,1,5) “LSESSION” , s2.username, substr( decode(bitand(ksuseidl,11), 1,’ACTIVE’, 0, decode( bitand(ksuseflg,4096) , 0,’INACTIVE’,’CACHED’), 2,’SNIPED’, 3,’SNIPED’, ‘KILLED’ ),1,1 ) “S”, substr(s2.event,1,10) “WAITING” from…