how to get column names,DATA_TYPE,DATA_LENGTH of any table....

select a.table_name,U.OWNER, column_name,DATA_TYPE,DATA_LENGTH from user_tab_columns a,ALL_CATALOG u
where a.TABLE_NAME=u.TABLE_NAME
AND u.table_name='table_name'
and column_name like upper('%column_name%')
and u.owner='owner'
order by DATA_LENGTH desc;

ORA-15018, ORA-15072 on node 2 when running root.sh

U have to give the path of asm disks manually when installing 11g grid Infrastructure

click Disk discovery path and then write

/dev/oracleasm/disks

automatically asm disks will be showned on list.And dont forget to check owner of disks they should belong to oracle:oinstall..

then edit /etc/sysconfig/oracleasm and add dm,sd parameters otherwise u can have a trouble with disks after restarting server.(if  you are using multipath.)

ORACLEASM_SCANORDER="dm"
ORACLEASM_SCANEXCLUDE="sd"

last thing, dont forget to restart  oracleasm

/etc/init.d/oracleasm restart.

after these steps are done simply run rootcrs.pl -verbose -deconfig -force on first node , if u have more than two nodes then u have to add -verbose. good luck.

applying patch 9413827 before upgrading crs to 11.2.0.3.0

patch id 9413827
with root
/u01/app/11.2.0/grid/crs/install/rootcrs.pl -unlock
su - oracle
9413827/custom/scripts/prepatch.sh -dbhome $ORACLE_HOME
First applying patch for grid;
/u01/app/11.2.0/grid/OPatch/opatch napply -local -oh $CRS_HOME -id 9413827
then for database;
/u01/app/oracle/product/11.2.0/db_1/OPatch/opatch napply custom/server/ -local -oh $ORACLE_HOME -id 9413827

again with root
chmod +w /u01/app/11.2.0/grid/log/rac1/agent/
chmod +w /u01/app/11.2.0/grid/log/rac1/agent/crsd/

su - oracle

custom/server/9413827/custom/scripts/postpatch.sh -dbhome $ORACLE_HOME


su - root

/u01/app/11.2.0/grid/crs/install/rootcrs.pl -patch

same steps for node 2 too..

problem with du and df commands disagree (OCFS2 1.4.1 BUG)

Today our production database stopped working because of ocfs2 1.4.1 bug..du -sch and df -h was showing different size on location where archive logs are..so after searching on google, i realize we hit a bug with ocfs2 1.4.1. Here is the solution of fixing this problem;

 first of all u need to umount ocfs2 partition using below command line;

 umount /oradata1 (location where partition as ocfs2 is mounted)

then u need to run ;

/sbin/fsck.ocfs2 -fy /dev/sda1

it could take a while to finish; u can break the operation while fixing..wont effect any datafiles or system files on this partition...in case of losing any datafiles, better taking a fresh backup before running fsck.ocfs2.

ASMCMD-08102: no connection to ASM

[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/grid
[oracle@localhost ~]$ export PATH=/u01/app/oracle/grid/bin
[oracle@localhost ~]$ export ORACLE_SID=+ASM
[oracle@localhost ~]$ asmcmd

ORA-04021: timeout occurred while waiting to lock object

U have to kill all sessions using object which u wanna compile, u can use this below script to find out which sessions are using any object..

select 'kill -9 '||b.spid||' # username '||c.username||'' from gv$access a, gv$process b, gv$session c where a.object=upper('object_name') and a.sid=c.sid and b.addr=c.paddr


run the output on linux to kill all sessions then compile object

How to check ASM HOME !!

cat /etc/oratab

create a directory for datapump

CREATE OR REPLACE DIRECTORY DP AS '/u01/app/oracle/datapump/';
GRANT READ, WRITE ON DIRECTORY DP TO system;

Extract Tablespaces with dbms_metadata.get_ddl

select 'select dbms_metadata.get_ddl(''TABLESPACE'','''
  ||  tablespace_name || ''') from dual;' from dba_tablespaces

How to list of dates between 2 dates

How to list of dates between 2 dates

select to_date('23-04-2011', 'DD-MM-YYYY') + rownum -1 dt
    from dual
    connect by level <= to_date('05-05-2011', 'DD-MM-YYYY') - to_date('23-04-2011', 'DD-MM-YYYY') + 1;