check hidden parameter in oracle

there are many hidden parameter in oracle database.

below  query can be used to check the value of those hidden paramters.

select a.ksppinm name,b.ksppstvl value,b.ksppstdf deflt,
decode(a.ksppity, 1,'boolean', 2,'string', 3,'number', 4,'file', a.ksppity) type,a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '_%'
order by name;

reset hidden paramter value in a session:
alter session set "_gby_hash_aggregation_enabled"= FALSE;

reference:
http://asanga-pradeep.blogspot.com/2010/08/checking-hidden-parameters.html

stop rman job by kill rman session

CAUSE: you might want to kill rman job because performance is bad or job is hunging or something else.

1. find rman session as well as sid and serial#

select s.sid ,s.serial#, spid, s.status, s.event, s.program
from v$session s, V$process p
where s.program like '%rman%' and wait_class='System I/O'
and s.paddr = p.addr;


2. kill the session

alter system kill session 'sid,serial#'


3. kill the process on os level if killed session didn't disappear from v$session view.

kill -9 spid


reference:
http://furqansindhu.wordpress.com/2012/02/28/kill-rman-session/

Exception in thread "main" java.sql.SQLException: Underflow Exception trying to bind 1.0000000000000001E-131

CAUSE: a double is converted to a Oracle NUMBER before passing it to the database and Oracle NUMBER does not support 1.0e-131, the range is 1E-130 .. 10E125

reference:
 https://forums.oracle.com/forums/thread.jspa?threadID=458586

Private strand flush not complete in oracle alert log

this message is not an error except that the gap between this message and next log allocation is large.

The message means some log in memory must be written to log file before going to next sequence.

check log usage:

set pagesize 6000
set linesize 600
col MEMBER format a50
select a.group#, lf.member, a.bytes/1024/1024 mb, a.status, a.archived
   from v$logfile lf, v$log a
   where a.group# = lf.group#
  order by 1, 2;


reference:
http://www.orafaq.com/forum/t/122199/0/

oracle components and option

column comp_name for a40
column comp_id for a20
column status for a20
select comp_id, comp_name, version, status from dba_registry;

set linesize 120
select * from v$option;

reference:
http://amoratech.wordpress.com/2011/07/22/how-to-find-the-installed-components-of-an-oracle-database/
http://javeedkaleem.blogspot.com/2010/03/install-database-components.html

disable password verify function in oracle

ISSUE:
You may fail to run some oracle provided sql scripts because of user creation failure. the creation failure is because provided password didnt' pass verify function.

CAUSE:
default password provided in oracle script doesn't satisfy your password verify function in default profile

SOLUTION:
alter profile default limit password_verify_function null;
run the script
alter profile default limit password_verify_function your-password-verify-function-name ;

reference;
http://www.dbapool.com/forumthread/topic_1140.html

oracle can not startup by error ORA-27102: out of memory

ISSUE:
Database was shutdown for maintenance but it can not be start up.

ERROR:

ORA-27102: out of memory

Linux-x86_64 Error: 28: No space left on device

CAUSE: 

shmmax was not configured correctly. it also might cause by init file memory parameter or user limitation.

SOLUTION:

edit /etc/sysctl.conf

increate kernel.shmmar to physical memory minus 1

reference:

https://forums.oracle.com/forums/thread.jspa?threadID=2322835&start=15&tstart=0

http://www.orafaq.com/forum/t/175487/0/

http://docs.oracle.com/cd/E11882_01/install.112/e24321/pre_install.htm#CIHEGJEH

oracle odbc driver architecture

1. Issue:
Oracle ODBC driver was installed and DSN was configured and test connection was succeed. But it gave error when you try to use the DSN through MS Excel or MS Access 2007.

2. Error:
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
3. Root Cause:
ODBC driver installed is 64 bit because OS is Win 7. MS Excel or MS Access 2007 is 32 bit. So the platforms are mismatched.

4. solution:
4.1 install 32bit ODBC driver
4.2 run C:\windows/sysWOW64\odbcad32.exe
4.3 configure DSN
4.4 use the conifured DSN in MS Excel or MS Access 2007


reference:
https://forums.oracle.com/forums/thread.jspa?messageID=10327205
http://msdn.microsoft.com/en-us/library/windows/desktop/ms712362(v=vs.85).aspx
https://forums.oracle.com/forums/thread.jspa?messageID=9726983

zip command in linux

function: compress and archive files. zip is a cross-platform format.

zip output.zip *     ## include all file and directory in current directory
zip output.zip *.* ## include all file with a dot in middle in current diretory.
zip -r output.zip  test  ## recurve to test directory
zip -R output.zip *.txt     ## include current directory
zip -R output.zip  -x \*.o ## exclude .o files


reference:
man zip

dbms_metadata.get_ddl in oracle

select dbms_metadata.get_ddl( 'TABLE','TABLE_NAME','SCHEMA_NAME') from dual;

to format the output in sqlplus:


set long 200000 pages 0 lines 2000
column txt format a2000 word_wrapped
spool trigger_ddl_n.sql
select dbms_metadata.get_ddl( 'TRIGGER',Trigger_name,upper('test')) txt
from
dba_triggers
where trigger_name in
(select trigger_name from dba_triggers where owner=upper('test')
minus
select trigger_name from dba_triggers where owner=upper('test1')
)
and owner=upper('test')
;
spool off


reference:
http://pages.videotron.com/orautils/pages/dbms_metadata.htm
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30802454515375

awk command in linux

awk '/search pattern/ {action}
        /search partern/ {action}' file

awk '{print $2, $5;}' examp.txt
ps -ef | grep user | awk '{print "kill -9 " $2;}'
NF is a built in variable which represents total number of fields in a record


reference:
http://www.thegeekstuff.com/2010/01/awk-introduction-tutorial-7-awk-print-examples/

fork: Resource temporarily unavailable

I meet this error when can sudo to oracle to do dba tasks.

fork: Resource temporarily unavailable


1. issue:
there is some limitatoin  hit by user oracle.

2. check memory ( this command should be fine since you run it in root domain)
free -m

3. check disk  ( this command  should be fine sicne it run in root domain)
df -k

4. check user limitation (this command might fail since it run in oracle domain)
ulimit -a

5. root cause:
nfs interfase has issue and failed all 'df' command. Cron job was issued more then 800 times and all hung on 'df'' command. Oracle used up process quota.

6. kill df process
kill -9 process_id
bulk kill
ps -ef | grep oracle | grep df | awd ' {print "kill -9 ' $2;}
run the output.


reference:
http://www.linuxquestions.org/questions/linux-general-1/fork-resource-temporarily-unavailable-100086/
http://www.webhosting.uk.com/forums/linux-dedicated-servers/5770-fork-resource-temporarily-unavailable.html
http://www.thegeekstuff.com/2010/01/awk-introduction-tutorial-7-awk-print-examples/

nologging in oracle

1. nologging option is used for bulk insert to speed up operation and improve performance by reduce redo generation.

2. It will not work for update and delete.

3. for insert, /* append */ hint must be used and talbe must be nologging mode

4. it is very useful for create table from existing resource so that no recovery needed if instance crashed.

EXP: create table tt nologging as select * from testtable.

5. alter table move  and alter index build just like APPEND.

6. table from ASK TOM

Tom's Table:
------------

Table Mode    Insert Mode     ArchiveLog mode      result
-----------   -------------   -----------------    ----------
LOGGING       APPEND          ARCHIVE LOG          redo generated
NOLOGGING     APPEND          ARCHIVE LOG          no redo
LOGGING       no append       ""                   redo generated
NOLOGGING     no append       ""                   redo generated
LOGGING       APPEND          noarchive log mode   no redo
NOLOGGING     APPEND          noarchive log mode   no redo
LOGGING       no append       noarchive log mode   redo generated
NOLOGGING     no append       noarchive log mode   redo generated

reference:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869
http://jakub.wartak.pl/blog/?page_id=107

how to get sql execution information

the basic steps to find out sql exectuion information for tuning

1. explain plan
This will give you how sql is executed.

explain plan for <sql statement >
select * from table (dbms_xplan.display);

2. autotrace
Autotrace can be set on to display explain plan and statistics automatically

set autotrace on


3. trace file
Trace file can be generated to provide more execution information. User need alter session privilege.

alter session set sql_trace true

find out trace file name
select value from v$diag_info where name = 'Default Trace File';

user tkprof to generate readable file from trace file
tkprof <trace file> a.txt sys=no sort=prsela exeela fetchela

4. user dbms_profiler to check plsql code



reference:
https://forums.oracle.com/forums/thread.jspa?threadID=501834&start=0&tstart=0
http://www.articles.freemegazone.com/oracle-trace-file.php
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:458240723799

sqlplus in linux

sqlplus command
L : last command
R: rerun last command \
c/newtext/oldtext: correct last input
ed: use editor to edit last command


reference:
http://en.kioskea.net/faq/577-oracle-using-sqlplus-under-linux

Informatica PCSF_46008 Cannot Connect to domain Domain_test to lookup service Core Service/UserManagementService

1.Problem:
informatica power center repository client can not connect to informatica server.

2.Error:
PCSF_46008 Cannot Connect to domain Domain_test to lookup service Core Service/UserManagementService

3.Condition:
informatica server is running well. Repository service test is available. domain is accessable through server and admin console is accessible from remote login. hostame is DNS resovable.

4.Cause:
partial hostname is used in domain name and the partial hostname is not resovable to IP.

EXP:     hostname is test.new.com
             domain name is Domain_test
            when you configure domain in repository client, it will user test to access the informatica server.   
            if test is not resovable, then repository client connection will fail with error " PCSF_46008 Cannot Connect to domain Domain_test to lookup service Core Service/UserManagementService"

5.Solution:
a. reinstall the informatica server with domain name like Domain_test.new.com
b. add IP test into your local hosts file.

6.testing:
./infacmd.sh listAllUsers -dn Domain_test001 -un Administrator -pd Administrator -sdn Native -hp test001:6001 -re 100
http://test.new.com:6001/coreservices/DomainService



reference:
https://forums.oracle.com/forums/thread.jspa?messageID=4346396
https://kr.forums.oracle.com/forums/thread.jspa?threadID=2220126
http://datawarehouse.ittoolbox.com/groups/technical-functional/informatica-l/can-you-change-the-domain-name-in-informatica-86-2889070

dbms_system in oracle database

dbms_system is a system package, which is not supported by Oracle.

It has below procedure:
1. ksdwrt -- write message to alert or trace file
  dbms_system.ksdwrt(1,'put message to alert')
        option 1- in trace file
        option 2- in alert log
        option 3- in both

2. set_sql_trace_in_session -- set sql trace to another session
    dbms_system.set_sql_trace_in_session(sid, serial#, true)

3. set_ev -- set event trace on session
    dbms_system.set_ev(sid,serial#, event, level, name)
    dbms_system.Set_Ev(31, 97, 10046, 4, '');

4. read_ev -- read event trace setting on session.
    dbms_system.read_ev(event, output)

Privilege to use dbms_system
1. login as sys
   grant execute on dbms_system to user;
2. login as user
   exec sys.dbms_system.ksdwrt(2,'alert message') ;



preference:
http://www.oracle-base.com/articles/8i/dbms_system.php
http://space.itpub.net/519536/viewspace-616481