TaTsHuYa BLOG: November 2009

Monday, November 30, 2009

OracleAS 10.1.3 AS Control Returns '404 Not Found'

-- NEW --

Oracle WebServices Inspection Language Configuration Assistant Fails During OracleAS 10.1.3 Install

Symptoms

Output generated from configuration assistant "Oracle Web Services Inspection Language Configuration Assistant"
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/tools/ant/launch/Launcher
Configuration assistant "Oracle Web Services Inspection Language Configuration Assistant" failed

Cause of Scenario:

This error is caused by the ANT_HOME setting in the file /etc/ant.conf in some Linux distributions. The OracleAS 10.1.3 installation guide for Linux states that the ANT_HOME environment variable must be unset before installing the product. For example, refer to:

Oracle® Application Server Installation Guide 10g Release 3 (10.1.3.1.0) for Linux x86

Solution for Scenario:

Ensure the ANT_HOME environment variable is unset. Note, it may be necessary to edit the /etc/ant.conf and comment out the line ANT_HOME

AND/OR

rename, or backup / then temporarily remove this file.

reference: metalink.oracle.com

== TaTsHuYa ==

Thursday, November 26, 2009

Maintain Partition & Subpartition table.

I have some table that have partition and sub partition in it.
so this is sample command to add partition & sub partition
( partition - use range partition, subpartition - use hash partition )

sqlplus > alter table XXX.LOG_QS add partition LOG_QS_PART01 values less than (to_date('06_DEC_2008','DD_MON_YYYY'))
PCTFREE 10 PCTUSED 40 INITRANS 20 MAXTRANS 255 TABLESPACE XXX_DATA
( SUBPARTITION LOG_QS_PART01_01 TABLESPACE LOG_QS_PART01_01,
SUBPARTITION LOG_QS_PART01_02 TABLESPACE LOG_QS_PART01_02,
SUBPARTITION LOG_QS_PART01_03 TABLESPACE LOG_QS_PART01_03,
SUBPARTITION LOG_QS_PART01_04 TABLESPACE LOG_QS_PART01_04,
SUBPARTITION LOG_QS_PART01_05 TABLESPACE LOG_QS_PART01_05,
SUBPARTITION LOG_QS_PART01_06 TABLESPACE LOG_QS_PART01_06,
SUBPARTITION LOG_QS_PART01_07 TABLESPACE LOG_QS_PART01_07,
SUBPARTITION LOG_QS_PART01_08 TABLESPACE LOG_QS_PART01_08,
SUBPARTITION LOG_QS_PART01_09 TABLESPACE LOG_QS_PART01_09,
SUBPARTITION LOG_QS_PART01_10 TABLESPACE LOG_QS_PART01_10);

then after add parition & subpartition of table, you should maintainance your index to use the right tablespace.

sqlplus >
alter index dmc.LOG_QS_idx01 rebuild SUBPARTITION LOG_QS_PART01_01 TABLESPACE INDX_LOG_QS_PART01_01;
alter index dmc.LOG_QS_idx01 rebuild SUBPARTITION LOG_QS_PART01_02 TABLESPACE INDX_LOG_QS_PART01_02;
alter index dmc.LOG_QS_idx01 rebuild SUBPARTITION LOG_QS_PART01_03 TABLESPACE INDX_LOG_QS_PART01_03;
alter index dmc.LOG_QS_idx01 rebuild SUBPARTITION LOG_QS_PART01_04 TABLESPACE INDX_LOG_QS_PART01_04;
alter index dmc.LOG_QS_idx01 rebuild SUBPARTITION LOG_QS_PART01_05 TABLESPACE INDX_LOG_QS_PART01_05;
alter index dmc.LOG_QS_idx01 rebuild SUBPARTITION LOG_QS_PART01_06 TABLESPACE INDX_LOG_QS_PART01_06;
alter index dmc.LOG_QS_idx01 rebuild SUBPARTITION LOG_QS_PART01_07 TABLESPACE INDX_LOG_QS_PART01_07;
alter index dmc.LOG_QS_idx01 rebuild SUBPARTITION LOG_QS_PART01_08 TABLESPACE INDX_LOG_QS_PART01_08;
alter index dmc.LOG_QS_idx01 rebuild SUBPARTITION LOG_QS_PART01_09 TABLESPACE INDX_LOG_QS_PART01_09;
alter index dmc.LOG_QS_idx01 rebuild SUBPARTITION LOG_QS_PART01_10 TABLESPACE INDX_LOG_QS_PART01_10;

== TaTsHuYa ==

Wednesday, November 25, 2009

Oralce: kill session

When I found the session that still holding in the database, and I checked that is not the user's working session, so I try to kill the session by command.

alter system kill session 'SID,SERIAL#' [immediate];

if you killed by 'alter session kill session' but they still in the oracle ,
so I have the other way to kill it in os session.

kill -9 SPID

== TaTsHuYa ==

Query user session lock.

Many time I found there are many sessions still holding in database but they are not blocking another session, but I am not sure this is the right way to solve this problem. By use the below script to search and kill the session that not the user's work.

-- Query --
select b.username, spid, a.sid, b.serial#, ctime
from v$lock a, v$session b, v$process c
where a.sid = b.sid
and b.paddr = c.addr
and b.username not in ('SYS','SYSTEM');

-- Result --
USERNAME SPID SID SERIAL# CTIME
------------------------------ ------------ ---------- ---------- ----------
XXXCLIENT 25735 362 49353 55914
YYYCLIENT 19971 480 60967 0
YYYCLIENT 19971 480 60967 0
XXXUSER 13439 135 65198 55886
XXXCLIENT 25735 362 49353 55914
YYYCLIENT 20456 252 34722 55907

== TaTsHuYa ==

Sleep on Oracle.

If you want to use "dbms_lock" with the normal user, you should grant execute this package to user before he/she use.

--- Query --

SQL> select sysdate from dual;
exec dbms_lock.sleep(11);
select sysdate from dual;

-- Result --
SYSDATE
--------------------
25-NOV-2009 16:54:30

SQL>

PL/SQL procedure successfully completed.

SQL>
SYSDATE
--------------------
25-NOV-2009 16:54:41

== TaTsHuYa ==

Tuesday, November 17, 2009

Oracle Flashback Drop.

When you want to recovery table from you dropped a mistake table.

sqlplus> select * from tab where tname = 'MM_200911';

no rows select

** Flashback **
sqlplus> select object_name, original_name from user_recyclebin

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$eID06aNB3xrgQFs92OZNdw==$0 MM_200911

sqlplus> FLASHBACK TABLE "BIN$eID06aNB3xrgQFs92OZNdw==$0" TO BEFORE DROP;

*** Check table again.
sqlplus> select * from tab where tname = 'MM_200911';

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MM_200911 TABLE

-- OR -- if you want to rename to other name.
sqlplus> FLASHBACK TABLE "BIN$eID06aNB3xrgQFs92OZNdw==$0" TO BEFORE DROP RENAME TO xxxxx;

== TaTsHuYa ==

How to find object dependency in oracle.

** if you want to know the package body, procedure or function that have the object referenced to , you can use **

select referenced_owner, referenced_name, referenced_type from user_dependencies where name = 'XXXXMGMT';

XXXXMGR XXXX_HISTORY TABLE
YYYYMGR XXXX_MGMG PACKAGE
XXXXMGR XXXX_SENDNPM TABLE
XXXXMGR XXXX_CUSTOMER TABLE
XXXXMGR XXXX_SWAPLOGIN FUNCTION
XXXXMGR XXXX TABLE
XXXXMGR XXXX_CUSTOMER TABLE
XXXXMGR XXXX_LOGIN NON-EXISTENT

-- OR --

** if you want to know which object_name reference to table, you this command **
select name,type from user_dependencies where referenced_name = 'XXXX_CUSTOMER'

YYYYMGMT PACKAGE
YYYYMGMT PACKAGE
ZZZZMGMT PACKAGE BODY
ZZZZMGMT PACKAGE BODY

== TaTsHuYa ==

How to find DBID of oracle instance.

sqlplus > SELECT DBID FROM V$DATABASE;

DBID
----------
4260302596

-- OR --

[tatshuya#] $ORACLE_HOME/bin/rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Nov 17 15:36:13 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: WHDB (DBID=4260302596)

== TaTsHuYa ==

How to know "uptime" in oracle.

select startup_time from v$instance;

STARTUP_TIME
--------------------
16-oct-2009 03:43:13

== TaTsHuYa ==

Thursday, November 12, 2009

Oracle did not start listener when use "dbstart"

when I try to use dbstart in solaris, I found this error.

oracle# dbstart
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener
Usage: /oracle/product/10.2.0/db/bin/dbstart ORACLE_HOME
Processing Database instance "wp0": log file /oracle/product/10.2.0/db/startup.log

*** Oracle start the process but did not start listener ***
oracle# ps -ef |grep ora
oracle 8207 1 0 18:48:53 ? 0:00 ora_lgwr_wp0
oracle 8211 1 0 18:48:53 ? 0:00 ora_smon_wp0
oracle 8225 1 0 18:49:01 ? 0:00 ora_qmnc_wp0

oracle# telnet 10.1.40.12 1521
telnet: Unable to connect to remote host: Connection refused

I solved this problem by use dbstart and following parameter $ORACLE_HOME

*** Try again with parameter ***
oracle# dbstart $ORACLE_HOME
Processing Database instance "wp0": log file /oracle/product/10.2.0/db/startup.log

telnet 10.1.40.12 1521
Trying 10.1.40.12...
Connected to 10.1.40.12.
Escape character is '^]'.

It's work!!!!!

== TaTsHuYa ==

Oracle Blocking locks.

select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK
from gv$lock where (ID1,ID2,TYPE) in
(select ID1,ID2,TYPE from gv$lock where request>0);

INST_ID SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
------- ------- -- ---------- ---------- ----- ------- ---------- -----
3 220 TX 1376290 8505052 6 0 22 2
4 237 TX 1376290 8505052 0 6 22 0
3 220 TX 2621476 11211311 0 4 22 0
4 237 TX 2621476 11211311 6 0 22 2

Finding root blocker

Run query provided in Script section and do one of the following.

(1) Find and kill root blockers

a) - Find oldest resource request - row with highest CTIME (this is row L1)

b) - Exists there another row with the same SID as L1? (this is row L2 if exists)

NOT - this is root blocker, kill it

YES - Find row with the same values of ID1,ID2 columns as in L2 where LOCK > 0 (this is row L3)

- Repeat (b) with L3 (L3 becomes L1) until You find root blocker

(2) Or use simple rule (may not be best)

a) Kill oldest blocking session (highest CTIME)
b) Run script again and repeat (a) until blocking session exists

reference : MetaLink : Detecting Blocking Sessions in RAC and non-RAC (Enqueue Locks) Environments [ID 398519.1]

== TaTsHuYa ==

Friday, November 06, 2009

GET DDL from DBMS_METADATA.

if you want to get your table or index DDL , there is an easy way to get it on CLI.

--- command --
set long 10000

select dbms_metadata.get_ddl('INDEX','SYS_C0018108','TAT') from dual;

--- result ---
CREATE UNIQUE INDEX "TAT"."SYS_C0018108" ON "TAT"."TEST" ("TID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAT_DATA"

-- TaTsHuYa --

Monday, November 02, 2009

No wireless on Ubuntu 9.10 ( Dell D630 : Broadcom Corporation BCM4312 802.11b/g )

After installation new ubuntu 9.10, I can not see interface of wireless device, so I connect to the network and install bcmwl-kernel-source

[tatshuya]# sudo apt-get update
[tatshuya]# sudo apt-get install bcmwl-kernel-source

then restart the now I can found the wireless device.

==TaTsHuYa==

TaTsHuYa BLOG