Blogs & URL to Reference

http://www.shutdownabort.com/

Oracle : Installations' URLs

10gR2 Installation : installation of Oracle Database 10g Release 2 (10.2.0.1) on Red Hat Enterprise Linux 5 (RHEL5).
The link is from "oracle-base". The site is very practicle and conscise; especially useful when a DBA wants to refer or refresh something "real quick" without going into concepts & theory surrounding implementation.
http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnRHEL5.php

Below is a post about switching from primary database to standby database. It a step by step instruction set.
Before begining to follow the instruction switch the logs

step 0) alter system switch logfile;
http://www.visi.com/~mseberg/Data_Guard_switchover.html

Oracle : Types of Locks

The locks are classified in many ways. The most common classifications are as below:-
Classification based on functionality

1. DML locks (data locks)
2. DDL locks (dictionary locks) 
3. Oracle Internal Locks/Latches
4. Oracle Distributed Locks
5. Oracle Parallel Cache Management Locks

...to be continued

Oracle Latches :Protect data structures in SGA. These are serialization mechanisms.

1. Latches prevent more than one process from executing the same  piece of code at the same time
2. Latch has a cleanup process associated with it.
3. Cleanup process of a latch kicks in if a process dies while holding a latch
4. Levels are associated with latches.
5. Once a process acquires a latch at a certain level, it can only acquire higher level latches.


......to be continued

Oracle (From Metalink) : Summary Of Bugs Which Could Cause Deadlock

Summary Of Bugs Which Could Cause Deadlock [Metalink Note : ID 554616.1]
--------------------------------------------------------------------------------
This summary is from metalink https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=REFERENCE&id=554616.1 .

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.4 to 11.1.0.6
Information in this document applies to any platform.

Purpose
The purpose of this Note is to explain various bugs filed specifically for the Dead lock errors against specific Oracle database versions (This Note covers bugs reported versions  above 9.2.0.4), and explain the symptoms of each bug, workarounds if any and references the patch availability at the time this article was written.

Bugs Fixed in Version  9.2.0.5
==============================
Note 2796282.8  Bug 2796282    False deadlock possible using shared servers
Note 3001270.8  Bug 3001270    Deadlock between SMON and foreground process for dc_suers 
Note 3030298.8 Bug 3030298    OERI:2103 from concurrent 'drop tablespace including datafiles
Note 3080929.8 Bug 3080929    ORA-4021 hang  SMON self deadlock  UNDO$ row cache lock
Note 3093080.8 Bug 3093080    ALTER TABLE ENABLE TABLE LOCK can cause a deadlock
Note 3271271.8 Bug 3271271    QMON can deadlock with job queue processes
Note 3009268.8 Bug 3009268    Recovery of DEAD prepared TX may deadlock with SMON
Note 2995746.8 Bug 2995746   Deadlock between session doing a GRANT 
Note 2918838.8 Bug 2918838   Undetected deadlock for dc_tablespace_quotas
Bugs Fixed in Version 9.2.0.6
=============================
Note 3398485.8 Bug.3398485   Deadlock during on demand materialized view refresh (ORA-4020)
Note 2615271.8 Bug.2615271   Deadlock from concurrent GRANT and logon
Note 2014833.8 Bug.2014833   Deadlock possible from concurrent SELECT and TRUNCATE
Note 3320292.8 Bug.3320292   Parallel recompilation hangs when recompiling type generated for pipeline function
Note 3424721.8 Bug 3424721   deadlock ALTER INDEX REBUILD on partition with concurrent SQL
Note.3166756.8 Bug.3166756   Self deadlock (ORA-60) / OERI possible on LOB index update
Note 3605165.8 Bug.3605165   Hang/deadlock between sessions concurrently loading a cursor with INVALID trigger
Note 3717619.8 Bug.3717619   Deadlock/hang possible due to concurrent cursor loads referencing same INVALID trigger
Note 3562032.8 Bug.3562032   Cancelled ONLINE index rebuild can deadlock with DML session
Note 3381218.8 Bug.3381218   Deadlock involving 'library cache lock' X mode request
Bugs Fixed in Version 9.2.0.7
=============================
Note 3314850.8 Bug.3314850     Can deadlock with query rewrite sessions
Note 3261205.8 Bug.3261205     Hang / OERI[kxttdropobj-1] on parallel direct load to temporary table
Note 2883771.8 Bug.2883771    "WAITED TOO LONG FOR ROWCACHE ENQUEUE" when using Resource Manager in PLSQL
Note 3896974.8 Bug.3896974     creating DIMENSIONs from schemas simultaneously
Bugs Fixed in 9.2.0.8 10.1.0.5
==============================
Note 4114238.8  Bug 4114238  Deadlock between dc_users and dc_usernames row cache lock enabling FK
Note 4416907.8 Bug 4416907   ORA-4020 DO_DEFERRED_REPCAT_ADMIN  concurrent SQL
Note 4329748.8 Bug 4329748   ORA-4020 / deadlock quiescing a replication group
Note 4029101.8 Bug 4029101  Concurrent CREATE TABLE / VIEW can deadlock (ORA-60)
Note 4275733.8 Bug 4275733  Deadlock between library cache lock and row cache lock from concurrent rename partition
Note 4313246.8  Bug 4313246  PLSQL execution can hold dc_users row cache lock leading to hang / deadlocks
Note 4185270.8 Bug.4185270   PMON "failed to acquire row cache enqueue" cleaning a dead process
Note 4446011.8 Bug.4446011   Hang with row cache lock deadlock from concurrent ALTER USER / TRUNCATE
Note 3987280.8 Bug.3987280    Concurrent GRANT / SET ROLE can hang / deadlock
Bugs Fixed in 10.1.0.4 and 10.2.0.1
===================================
Note 3540821.8 Bug.3540821  ORA-4020 deadlock from concurrent ANALYZE index / query compilation against a cluster
Note 3990235.8 Bug.3990235  Deadlock in disk drop and instance recovery in ASM
Note 3975268.8 Bug.3975268  Deadlock possible after gathering statistics for certain SYS objects
Note 3756949.8 Bug.3756949  Sequences can deadlock on space allocation failure
Note 4137000.8 Bug.4137000  Concurrent SPLIT PARTITION can deadlock / hang
Note 4008775.8 Bug.4008775  Self deadlock calling DBMS_SPACE / DBMS_STATS in same user call
Bugs Fixed in  10.2.0.2
=======================
Note 4153150.8 Bug.4153150   Deadlock on dc_rollback_segments from concurrent parallel load and undo segment creation
Note 4382653.8 Bug.4382653   Deadlock / ORA-4020 gathering statistics on indices
Note 4375798.8 Bug.4375798   ORA-60 deadlock from AQ enqueue
Note 4552067.8 Bug.4552067   Deadlock / ORA-4020 using TRUNCATE SQL against global temporary tables
Bugs Fixed in 10.2.0.3
======================
Note 4627237.8 Bug.4627237  autonomous_transaction with DB link to shared server can self deadlock on DX
Note 4732503.8 Bug.4732503  Self-deadlock on TT enqueue

Oracle : report roles assigned to users (SQL)

select
  username,
  default_tablespace    dts,
  temporary_tablespace  tts,
  profile prof,
  granted_role || ' ' ||
  decode(admin_option,'YES','- A',' ') ||
  decode(granted_role,'YES','- G',' ') role
from
  dba_users,
  dba_role_privs
where
  dba_users.username = dba_role_privs.grantee and
  username not in ('PUBLIC')
order by
  1,2,3,4;

Linux : 1-liners needed by a DBA

1. Convert variable data from upper/lowercase to lower/uppercase:

   STRING=ExaMple
   echo $STRING | tr '[:lower:]' '[:upper:]'  ==> EXAMPLE
   echo $STRING | tr '[:upper:]' '[:lower:]'  ==> example

   Another example (shell script : test.sh)  :-
   #!/bin/bash
   ############################################################
   #      Example : Convert case of a string                  #
   ############################################################
   HOSTNAME=`hostname|awk -F\. '{print $1}'|tr '[:lower:]' '[:upper:]'`
   echo "Upper case HOSTNAME : ${HOSTNAME}"
   HOSTNAME=`hostname|awk -F\. '{print $1}'|tr '[:upper:]' '[:lower:]'`
   echo "Lower case HOSTNAME : ${HOSTNAME}"

2.  Top 10 memory consuming processes
     ps uax --sort=-rss|head -10

3.  Top 10 CPU intensive processes
     ps uax --sort=-pcpu|head -10

Oracle : one liners needed by a DBA

Below is the reposirory of 1-liners (commands and some formatting) that we keep requiring most of the the time. I'll try to keep growing this repository

1. List datafile belonging to a tablespace
    set linesize 150
    set pagesize 200
    set echo off
    set verify off
    col file_name format a80
    select file_name , bytes/1024/1024 size_in_mb from dba_data_files where tablespace_name like upper('&1');

2. List all the data files.
    select name from v$datafile;

3. Versions:
     select * from v$version;
     select * from v$database;
     select * from product_component_version;
     select version from v$instance;
     select * from sys.gv_$version;
     select * from sys.product_component_version;
     select * from sys.sm_$version;
     select * from sys.gv_$instance;

4.  unzip Opatch to ORACLE_HOME
     unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME
5.  Opatch Prereq Check
     opatch prereq CheckConflictAgainstOHWithDetail -ph ./

6. Apply patch

    opatch apply

Redhat : How to add swap space

I needed to temporarily increase the swap space by 16GB. Simplest and the quickest method is to add a swap file for the duration and then remove it after the task is over.

First of all identify a large enough partition on the internal disk and then go about the task as below..

1. Locate the partition where the file can be created, say /u06/swap
2. make a directory : /u06/swap
3. create the swap file using the command "dd" : dd if=/dev/zero of=/u06/swap/swapfile bs=1024 count=16384000
4. make the file as swapfile : mkswap /u06/swap/swapfile
5. To make the file available after reboot , add it to the /etc/fstab file: /u06/swap/swapfile  swap  swap  defaults  0 0
6. Activate the swap space : swapon -a /tmp/swapfile
7. check the swap space : swapon -s

I followed redhat documentation , but the link below explains methods precisely the way I created the swap space. The link also shows how to drop the swap space.

http://www.technofunction.com/2010/08/adding-swap-space-in-redhatfedora-linux-manually-using-file-and-partition/


How to move swapfile from one location to another

1. Locate the file where the created swapfile exists

2. Turnoff swap from swapfile

   swapoff /mnt/dallas_prod_old/swapfile_dir/swapfile

3. 

Oracle : tablespace used , ordered by %used_space (SQL)

A simple script to see permanent / temporary tablespace usage in Oracle
=================================================
set linesize 250
set pagesize 200
set echo off
select b.tablespace_name,b.total_space_in_mb,(b.total_space_in_mb - nvl(a.free_space_in_mb,0)) used_space_in_mb,
nvl(a.free_space_in_mb,0) free_space_MB, round(((b.total_space_in_mb - nvl(a.free_space_in_mb,0)) / b.total_space_in_mb) * 100,2) as "%_USED_SPACE"
from
(select sum(bytes)/1024/1024 free_space_in_mb, tablespace_name from dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 total_space_in_mb,tablespace_name from dba_data_files group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name order by "%_USED_SPACE" desc
;
select b.tablespace_name,b.total_space_in_mb,(b.total_space_in_mb - nvl(a.free_space_in_mb,0)) used_space_in_mb,
nvl(a.free_space_in_mb,0) free_space_MB, round(((b.total_space_in_mb - nvl(a.free_space_in_mb,0)) / b.total_space_in_mb) * 100,2)
as "%_USED_SPACE" from
(select sum(bytes_free)/1024/1024 free_space_in_mb, tablespace_name from v$temp_space_header group by tablespace_name) a,
(select sum(bytes)/1024/1024 total_space_in_mb,tablespace_name from dba_temp_files group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name order by "%_USED_SPACE" desc ;