Move datafile to new location with RMAN.

This is the new way to move datafile by using RMAN.
My goal for this situation is to move datafile of index partition to another ASM DISKGROUP, so in this sample I will show you move data file from +DATA1 to +DATA2

First Step : find the file number of datafile in sqlplus

SQL> select b.file#, c.name from v$tablespace a, v$datafile b, v$dbfile c where a.TS# = b.TS# and b.FILE# = c.FILE# and a.NAME like 'INDX_XXX_PART01_01%';

247,+DATA1/dmcdb/datafile/indx_xxx_part01_01.454.691783655

Second Step: Using the RMAN to backup datafile copy and switch datafile to use the new one.

rman> connect target /
rman> backup as copy datafile 247 format '+DATA2';
rman> sql 'ALTER DATABASE DATAFILE 247 OFFLINE';
rman> SWITCH DATAFILE 247 TO COPY;
rman> RECOVER DATAFILE 247;
rman> sql 'ALTER DATABASE DATAFILE 247 ONLINE';
rman> delete datafilecopy '+DATA1/dmcdb/datafile/indx_xxx_part01_01.454.691783655';

Final Step: Check the new location of datafile that you moved.

SQL> select b.file#, c.name from v$tablespace a, v$datafile b, v$dbfile c where a.TS# = b.TS# and b.FILE# = c.FILE# and a.NAME like 'INDX_XXX_PART01_01%';

247,+DATA2/dmcdb/datafile/indx_xxx_part01_01.490.691783739


== TaTsHuYa ==

Sqlplus : output newline

If you want to output your result to have a new line , you could add chr(10).

** without new line **
sql> select 'Hello!!!!!, My name is TaTsHuYa' from dual;

'HELLO!!!!!,MYNAMEISTATSHUYA'
-------------------------------
Hello!!!!!, My name is TaTsHuYa

** with new line **
sql> select 'Hello!!!!!,' || chr(10) || 'My name is TaTsHuYa' from dual;

'HELLO!!!!!,'||CHR(10)||'MYNAME
-------------------------------
Hello!!!!!,
My name is TaTsHuYa

== TaTsHuYa ==

Error when install Oracle Enterprise Manager 10g Release 5 (10.2) on RHEL 5 i386

File : /oracle/product/10.2.0/oms10g/opmn/logs/HTTP_Server~1
/oracle/product/10.2.0/oms10g/Apache/Apache/bin/apachectl start: execing httpd
/oracle/product/10.2.0/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or dire

[oracle@tatshuya ~]$ ls -l /usr/lib/libdb.so.2
ls: /usr/lib/libdb.so.2: No such file or directory

++ resolve by ++
[oracle@tatshuya ~]$ sudo ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2

++ check again ++
[oracle@tatshuya ~]$ ls -l /usr/lib/libdb.so.2
lrwxrwxrwx 1 root root 25 Dec 17 10:42 /usr/lib/libdb.so.2 -> /usr/lib/libgdbm.so.2.0.0

then try to stop & start opmn manual again
[oracle@tatshuya ~]$ /oracle/product/10.2.0/oms10g/opmn/bin/opmnctl stopall
[oracle@tatshuya ~]$ /oracle/product/10.2.0/oms10g/opmn/bin/opmnctl startall


check log again
File : /oracle/product/10.2.0/oms10g/opmn/logs/HTTP_Server~1

09/12/17 10:44:34 Stop process
--------
/oracle/product/10.2.0/oms10g/Apache/Apache/bin/apachectl stop: httpd stopped

--------
09/12/17 10:44:51 Start process
--------
/oracle/product/10.2.0/oms10g/Apache/Apache/bin/apachectl start: execing httpd

--------


== TaTsHuYa ==

Setup Linux Syslog Server ( RHEL )

Actually every linux server has the process 'sysklog' run on every servers but it's handling own server.

if you want to allow 'sysklog' can receive log from the client server you on set '/etc/init.d/syslog' by change parameter option

++ OLD ++
SYSLOGD_OPTIONS="-m 0"

++ NEW ++
SYSLOGD_OPTIONS="-r -x -m 0"

-r This option will enable the facility to receive message from the network using an internet domain socket with the syslog service (see ser-vices(5)). The default is to not receive any messages from the network.

-x Disable name lookups when receiving remote messages. This avoids deadlocks when the nameserver is running on the same machine that runs the syslog daemon.

++ Configure of Client Server ++
Add below lines to '/etc/syslog.conf'
*.* @syslogserver

++ Configure of Cisco Devices ++
config term
logging
logging trap notifications
service timestamps log datetime
logging on


Lastly, don't forget to allow iptables by add this below line to '/etc/sysconfig/iptables'

-A RH-Firewall-1-INPUT -p udp -m udp --dport 514 -j ACCEPT

reference: http://www.aboutdebian.com/syslog.htm

== TaTsHuYa ==

Script for manage table partition.

I want to manage table partition that have partition with RANGE and sub partition with HASH and the developer does not need the data older than 4 months so I decided to create the script to add partition the next 2 month and drop the fifth previous month in script.

create or replace manage_table_partition_xx
as
next_2_month char(8);
next_month char(8);
PREV_5_MONTH CHAR(8);

which_month char(3);
which_part char(6);
begin
select to_char(add_months(sysdate,2),'MON_YYYY') into next_2_month from dual;
select to_char(add_months(sysdate,1),'MON_YYYY') into next_month from dual;
select to_char(add_months(sysdate,-5),'MON_YYYY') into PREV_5_MONTH from dual;

which_month := substr(next_month,1,3);
if ( which_month = 'JAN' or which_month = 'JUL') THEN
which_part := 'PART01';
elsif ( which_month = 'FEB' or which_month = 'AUG') THEN
which_part := 'PART02';
elsif ( which_month = 'MAR' or which_month = 'SEP') THEN
which_part := 'PART03';
elsif ( which_month = 'APR' or which_month = 'OCT') THEN
which_part := 'PART04';
elsif ( which_month = 'MAY' or which_month = 'NOV') THEN
which_part := 'PART05';
elsif ( which_month = 'JUN' or which_month = 'DEC') THEN
which_part := 'PART06';
end if;

-- DROP PARTITION
execute immediate ('alter table TATSHUYA.XX drop partition XX_' || PREV_5_MONTH );


-- Alter table add partition
execute immediate ('alter table TATSHUYA.XX add partition XX_' || next_month || ' values less than (to_date(''01_' || next_2_month || ''',''DD_MON_YYYY''))
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255 TABLESPACE DATA
(subpartition XX_' || next_month || '_01 tablespace XX_' || which_part || '_01,
subpartition XX_' || next_month || '_02 tablespace XX_' || which_part || '_02,
subpartition XX_' || next_month || '_03 tablespace XX_' || which_part || '_03,
subpartition XX_' || next_month || '_04 tablespace XX_' || which_part || '_04,
subpartition XX_' || next_month || '_05 tablespace XX_' || which_part || '_05,
subpartition XX_' || next_month || '_06 tablespace XX_' || which_part || '_06,
subpartition XX_' || next_month || '_07 tablespace XX_' || which_part || '_07,
subpartition XX_' || next_month || '_08 tablespace XX_' || which_part || '_08,
subpartition XX_' || next_month || '_09 tablespace XX_' || which_part || '_09,
subpartition XX_' || next_month || '_10 tablespace XX_' || which_part || '_10)
');

execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_01 tablespace INDX_XX_' || which_part || '_01');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_02 tablespace INDX_XX_' || which_part || '_02');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_03 tablespace INDX_XX_' || which_part || '_03');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_04 tablespace INDX_XX_' || which_part || '_04');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_05 tablespace INDX_XX_' || which_part || '_05');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_06 tablespace INDX_XX_' || which_part || '_06');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_07 tablespace INDX_XX_' || which_part || '_07');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_08 tablespace INDX_XX_' || which_part || '_08');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_09 tablespace INDX_XX_' || which_part || '_09');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_10 tablespace INDX_XX_' || which_part || '_10');


dbms_stats.gather_table_stats(ownname => 'DMC', tabname => 'XX', cascade => TRUE, partname => 'XX_' || next_month );

end;

== TaTsHuYa ==

Error : asmcmd

when I run asmcmd command, I found the error.

[oracle@tatshuya]$ asmcmd
install_driver(Oracle) failed: Can't load '/oracle/product/10.2.0/db_1/perl/lib/site_perl/5.8.3/i686-linux-thread-multi/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: /oracle/product/10.2.0/db_1/lib32/libnnz10.so: cannot restore segment prot after reloc: Permission denied at /oracle/product/10.2.0/db_1/perl/lib/5.8.3/i686-linux-thread-multi/DynaLoader.pm line 229.
at (eval 2) line 3
Compilation failed in require at (eval 2) line 3.
Perhaps a required shared library or dll isn't installed where expected
at /oracle/product/10.2.0/db_1/bin/asmcmdcore line 5953

[root@tatshuya]# setenforce 0

[oracle@tatshuya]$ export ORACLE_SID=+ASM
[oracle@tatshuya]$ asmcmd
ASMCMD>


== More Information ==
[oracle@tatshuya] man: setenforce


setenforce(1) SELinux Command Line documentation setenforce(1)

NAME
setenforce - modify the mode SELinux is running in.

SYNOPSIS
setenforce [ Enforcing | Permissive | 1 | 0 ]

DESCRIPTION
Use Enforcing or 1 to put SELinux in enforcing mode. Use Permissive or 0 to put SELinux in permissive mode. You need to modify
/etc/grub.conf or /etc/selinux/config to disable SELinux.

AUTHOR
Dan Walsh,

SEE ALSO
selinux(8), getenforce(8), selinuxenabled(8)

FILES
/etc/grub.conf, /etc/selinux/config


== TaTsHuYa ==

Error ASM IO exception: The Network Adapter could not establish the connection.

When you see this error, the first thing you must check is the Oracle's listener.

[oracle@tatshuya]$ ps -ef |grep lsn
oracle 16197 15713 0 12:20 pts/2 00:00:00 grep lsn

there is no listener process start in this server, you can start by command.
[oracle@tatshuya]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 14-DEC-2009 12:22:50

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

Starting /oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tatshuya)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 14-DEC-2009 12:22:50
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tatshuya)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[oracle@tatshuya]$ ps -ef |grep lsn
oracle 16204 1 0 12:22 ? 00:00:00 /oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit
oracle 16256 15713 0 12:35 pts/2 00:00:00 grep lsn


After start the listener process, retry again, you should see the next step to configure ASM Disk Group.

SQL statements to check status of datafile headers

Query the V$DATAFILE_HEADER view to determine the status of your datafiles. Run the following SQL statements to check the datafile headers:

COL FILE# FORMAT 999
COL STATUS FORMAT A7
COL ERROR FORMAT A10
COL TABLESPACE_NAME FORMAT A10
COL NAME FORMAT A30

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

You can also queryV$RECOVER_FILE to list datafiles requiring recovery by datafile number with their status and error information.

SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME
FROM V$RECOVER_FILE;


To find datafile and tablespace names, you can also perform useful joins using the datafile number and the V$DATAFILE and V$TABLESPACE views. For example:

COL DF# FORMAT 999
COL DF_NAME FORMAT A35
COL TBSP_NAME FORMAT A7
COL STATUS FORMAT A7
COL ERROR FORMAT A10
COL CHANGE# FORMAT 99999999
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,
d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE#
;



reference: Oracle's Document

== TaTsHuYa ==

SQL Loader ( Sample )

** command line **
sqlldr xxx/yyyy control=zzz.par data=zzz.dat log=zzz.log

** Control File **

LOAD DATA
APPEND
INTO TABLE ZZZZ
(
TID "zzz_seq.nextval",
START position(17:25),
STOP position(33:41),
BARBY CONSTANT "ZZZ"
)

** Data File **
021234567
022345678
023456789
021111111



== TaTsHuYa ==

Configuration sendmail forward to another host.

Host for the msp feature (v8.12 and above)

The msp feature can take an optional argument. That argument determines whether the mail collected by the MSP invocation of sendmail should be delivered to the local machine or to another machine:


So when you want to forward mail to another server for send out ( if you have firewall and configuration one server can send mail out ), you just change the following line
'/etc/mail/submit.cf'.
** OLD **
D{MTAHost}[127.0.0.1]

** NEW **
D{MTAHost}[ww.xx.yy.zz]


== TaTsHuYa ==

Cross-site tracing attack via HTTP TRACK / TRACE method

Risk Level: Low
Finding:
    Debugging functions are enabling on the remote web server.
Description : The remote webserver supports the TRACE and/or TRACK methods. TRACE and TRACK are HTTP methods, which are use to debug web server connections.

    In addition, it has be shown that servers supporting the TRACE method are subject to cross-site scripting attacks, dubbed XST for "Cross-Site Tracing", when used in conjunction with various weaknesses in browsers.

    An attacker may use this flaw to trick your legitimate web users to give him their credentials.

Recommendation:
    Disable these methods on the apache.conf or httpd.conf by
        RewriteEngine on
        RewriteCond %{REQUEST_METHOD} ^(TRACE|TRACK)
        RewriteRule .* - [F]

    Alternatively, note that Apache versions 1.3.34, 2.0.55, and 2.2 support disabling the TRACE method by the following line
        TraceEnable Off

== TaTsHuYa ==

Deploy Applcation on OracleAS

If you have ear or war, you can deploy application by CMI by below command.

/oracle/product/10.1.3.1/OracleAS/jdk/bin/java -jar ../admin_client.jar deployer:oc4j:opmn://xxxx:6004/home oc4jadmin yyyyy -deploy -file ZZZ.ear -deploymentName ZZZ -bindAllWebApps

/oracle/product/10.1.3.1/OracleAS/opmn/bin/opmnctl status -l -app
Applications in Instance: xxxx

application type: OC4J
------+-----------+---------+---------+----------------+----------+------------------------------+--------
pid | name | state | rtid | classification | routable | appctx | parent
------+-----------+---------+---------+----------------+----------+------------------------------+--------
15234 | system | started | g_rt_id | external | true | /dmsoc4j,/JMXSoapAdapter |
15234 | WSIL-App | started | g_rt_id | internal | true | /inspection.wsil | default
15234 | default | started | g_rt_id | external | true | /jmsrouter,/j2ee,/.rmiTunnel | system
15234 | ascontrol | started | g_rt_id | external | true | /em | system
15234 | datatags | started | g_rt_id | internal | true | /webapp | default
15234 | TMWIFI | started | g_rt_id | | true | | default
15234 | javasso | stopped | g_rt_id | internal | true | /jsso |

I found, there is null on appctx, when I stop & start again If appeared /TMWIFI.

/oracle/product/10.1.3.1/OracleAS/opmn/bin/opmnctl stopall
/oracle/product/10.1.3.1/OracleAS/opmn/bin/opmnctl startall
/oracle/product/10.1.3.1/OracleAS/opmn/bin/opmnctl status -l -app
Applications in Instance: home_admin.uat01

application type: OC4J
------+-----------+---------+---------+----------------+----------+------------------------------+--------
pid | name | state | rtid | classification | routable | appctx | parent
------+-----------+---------+---------+----------------+----------+------------------------------+--------
15483 | system | started | g_rt_id | external | true | /dmsoc4j,/JMXSoapAdapter |
15483 | WSIL-App | started | g_rt_id | internal | true | /inspection.wsil | default
15483 | default | started | g_rt_id | external | true | /jmsrouter,/j2ee,/.rmiTunnel | system
15483 | ascontrol | started | g_rt_id | external | true | /em | system
15483 | datatags | started | g_rt_id | internal | true | /webapp | default
15483 | TMWIFI | started | g_rt_id | external | true | /TMWIFI | default
15483 | javasso | stopped | g_rt_id | internal | true | /jsso |

== TaTsHuYa ==

OracleAS 10.1.3 AS Control Returns '404 Not Found'

/oracle/product/10.1.3.1/OracleAS/opmn/bin/opmnctl status -l -app

Applications in Instance: XXX

application type: OC4J
------+-----------+---------+---------+----------------+----------+------------------------------+--------
pid | name | state | rtid | classification | routable | appctx | parent
------+-----------+---------+---------+----------------+----------+------------------------------+--------
11887 | system | started | g_rt_id | external | true | /dmsoc4j,/JMXSoapAdapter |
11887 | default | started | g_rt_id | external | true | /jmsrouter,/j2ee,/.rmiTunnel | system
11887 | WSIL-App | started | g_rt_id | internal | true | /inspection.wsil | default
11887 | ascontrol | stopped | g_rt_id | external | false | /em |
11887 | datatags | started | g_rt_id | internal | true | /webapp | default
11887 | javasso | stopped | g_rt_id | internal | true | /jsso |


## J2EE_HOME/config/server.xml ##
-- OLD --

-- NEW --


## J2EE_HOME/config/server.xml ##
-- OLD --

-- NEW --


/oracle/product/10.1.3.1/OracleAS/opmn/bin/opmnctl stopall
/oracle/product/10.1.3.1/OracleAS/opmn/bin/opmnctl startall
/oracle/product/10.1.3.1/OracleAS/opmn/bin/opmnctl status -l -app

Applications in Instance: XXX

application type: OC4J
------+-----------+---------+---------+----------------+----------+------------------------------+--------
pid | name | state | rtid | classification | routable | appctx | parent
------+-----------+---------+---------+----------------+----------+------------------------------+--------
12125 | system | started | g_rt_id | external | true | /dmsoc4j,/JMXSoapAdapter |
12125 | default | started | g_rt_id | external | true | /jmsrouter,/j2ee,/.rmiTunnel | system
12125 | WSIL-App | started | g_rt_id | internal | true | /inspection.wsil | default
12125 | ascontrol | started | g_rt_id | external | true | /em | system
12125 | datatags | started | g_rt_id | internal | true | /webapp | default
12125 | javasso | stopped | g_rt_id | internal | true | /jsso |


== TaTsHuYa ==

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 ==

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 ==

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 ==

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 ==

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 ==

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 --

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==

Vsock error when install vmware 2.0.x on Ubuntu 9.04

Save the below to file '/path/to/vmware-config.patch'

#### Begin ####

--- /usr/bin/vmware-config.pl.orig 2008-11-28 12:06:35.641054086 +0100
+++ /usr/bin/vmware-config.pl 2008-11-28 12:30:38.593304082 +0100
@@ -4121,6 +4121,11 @@
return 'no';
}

+ if ($name eq 'vsock') {
+ print wrap("VMWare config patch VSOCK!\n");
+ system(shell_string($gHelper{'mv'}) . ' -vi ' . shell_string($build_dir . '/../Module.symvers') . ' ' . shell_string($build_dir . '/vsock-only/' ));
+ }
+
print wrap('Building the ' . $name . ' module.' . "\n\n", 0);
if (system(shell_string($gHelper{'make'}) . ' -C '
. shell_string($build_dir . '/' . $name . '-only')
@@ -4143,6 +4148,10 @@
if (try_module($name, $build_dir . '/' . $name . '.o', 0, 1)) {
print wrap('The ' . $name . ' module loads perfectly into the running kernel.'
. "\n\n", 0);
+ if ($name eq 'vmci') {
+ print wrap("VMWare config patch VMCI!\n");
+ system(shell_string($gHelper{'cp'}) . ' -vi ' . shell_string($build_dir.'/vmci-only/Module.symvers') . ' ' . shell_string($build_dir . '/../'));
+ }
remove_tmp_dir($build_dir);
return 'yes';
}

### End ###

run before you install
patch ./vmware-server-distrib/bin/vmware-config.pl /path/to/vmware-config.patch

Thanks El Di Pablo to this information

-- TaTsHuYa --

Trace Session in Oracle.

There are many way to trace session in oracle, 'trcsess' is the one way to do that.

trcsess [output=

] [session=] [clientid=] [service=] [action=] [module=]

output=

output destination default being standard output.
session= session to be traced.
Session id is a combination of session Index & session serial number e.g. 8.13.
clientid= clientid to be traced.
service= service to be traced.
action= action to be traced.
module= module to be traced.
Space separated list of trace files with wild card '*' supported

*** Example ***
trcsess output=/tmp/trace.log session=983.29799


-- TaTsHuYa --

How to enable IPF on solaris 10

*** Edit configure file at '/etc/ipf/ipf.conf'

#
# ipf.conf
#
# IP Filter rules to be loaded during startup
#
# See ipf(4) manpage for more information on
# IP Filter rules syntax.
# Block any packets which are too short to be real
block in log quick all with short
#
# drop and log any IP packets with options set in them.
block in log all with ipopts
#
# Allow all traffic on loopback.
pass in quick on lo0 all
pass out quick on lo0 all
#
# Public Network. Block everything not explicity allowed.
block in on ce0 all
pass out on ce0 all
#
# Allow pings out.
pass out quick on ce0 proto icmp all keep state
#
# Allow outbound state related packets.
pass out quick on ce0 proto tcp/udp from any to any keep state
#
# allow ssh from 172.16.0.0/16 only.
# pass in log quick on ce0 from 172.16.0.0/16 to 172.16.1.100/32 port = 22
# Actually, allow ssh only from ben, jerry, MSU
pass in log quick on ce0 proto tcp from any to any port = 22
pass in log quick on ce0 proto tcp from any to any port = 1521

*** End file

*** Enable ipfilter by command
[tatshuya#] svcadm enable ipfilter

*** Disable ipfilter
[tatshuya#] svcadm disable ipfilter

*** Reconfig ipfilter
[tatshuya#] svcadm refresh ipfilter

-- TaTsHuYa --

How to know network interface name in Solaris.

by command 'prtconf -D'

[tatshuya#] prtconf -D |grep network

network, instance #0 (driver name: ce)
network, instance #1 (driver name: ce)

-- TaTsHuYa --

Automatic start apache in Solaris 10 ( Not use package in solaris )

#!/bin/sh

APACHE_HOME=/usr/local/apache

export APACHE_HOME

case "$1" in
start)
$APACHE_HOME/bin/apachectl start
;;

restart)
$APACHE_HOME/bin/apachectl restart
;;

stop)
$APACHE_HOME/bin/apachectl stop
;;

*)
echo "Usage: apache stop|start|restart"
;;

esac


#### Save file & chmod +x
chmod +x /etc/init.d/apache

#### Create symbolic link

ln -s /etc/init.d/apache /etc/rc0.d/K01apache

ln -s /etc/init.d/apache /etc/rc2.d/K01apache
ln -s /etc/init.d/apache /etc/rc2.d/S90apache

ln -s /etc/init.d/apache /etc/rc3.d/K01apache
ln -s /etc/init.d/apache /etc/rc3.d/S90apache

-- TaTsHuYa --

Automatic start tomcat in Solaris 10 ( Not use package in solaris )

#!/bin/sh

JAVA_HOME=/usr/local/java
CATALINA_HOME=/usr/local/tomcat

export JAVA_HOME CATALINA_HOME

case "$1" in
start)
$CATALINA_HOME/bin/startup.sh
;;

restart)
$CATALINA_HOME/bin/shutdown.sh
$CATALINA_HOME/bin/startup.sh
;;

stop)
$CATALINA_HOME/bin/shutdown.sh
;;

*)
echo "Usage: tomcat stop|start|restart"
;;

esac

##### save file & chmod +x
chmod +x /etc/init.d/tomcat

##### create symbolic link
ln -s /etc/init.d/tomcat /etc/rc0.d/K01tomcat

ln -s /etc/init.d/tomcat /etc/rc2.d/K01tomcat
ln -s /etc/init.d/tomcat /etc/rc2.d/S90tomcat

ln -s /etc/init.d/tomcat /etc/rc3.d/K01tomcat
ln -s /etc/init.d/tomcat /etc/rc3.d/S90tomcat


-- TaTsHuYa --

UUID, TYPE, LABEL of device.

[tatshuya]# vol_id /dev/sda5

ID_FS_USAGE=filesystem
ID_FS_TYPE=ext3
ID_FS_VERSION=1.0
ID_FS_UUID=5a13115f-246d-4eb8-a2c9-fa16c9ce9fab
ID_FS_UUID_ENC=5a13115f-246d-4eb8-a2c9-fa16c9ce9fab
ID_FS_LABEL=
ID_FS_LABEL_ENC=

-- TaTsHuYa --

Oracle EM Alert.

*** ERROR ***
Collection Timestamp Aug 7, 2009 10:46:33 AM
Error Type Collection Problem
Message snmhsutl.c:executable nmhs should have root suid enabled

*** Cause ***
This error occur because you did not run root.sh when you install oracle agent.


*** Solution ***
run root.sh in oracle agent home.

[root@tatshuya]# /oracle/product/agent10g/root.sh

-- TaTsHuYa --

Resize Datafile in Oracle.

*** Select DATAFILE

select c.name from v$tablespace a, v$datafile b, v$dbfile c where a.TS# = b.TS# and b.FILE# = c.FILE# and a.NAME= 'SYSTEM';

NAME
--------------------------------------------------------------------------------
+DATA1/tatshuya/datafile/system.258.616675057

*** use the result upper

alter database datafile '+DATA1/tatshuya/datafile/system.258.616675057' resize 1G;

-- TaTsHuYa --

Mount ISO File in Solaris

[solaris]# lofiadm -a /<absolute path>/sol-10-u8-ga-sparc-dvd.iso
/dev/lofi/1

[solaris]# mount -F hsfs -o ro /dev/lofi/1 /mnt

[solaris]# ls -l /mnt
total 983
-r--r--r-- 1 root root 6582 Aug 22 02:34 Copyright
-r--r--r-- 1 root root 487593 Aug 22 02:32 JDS-THIRDPARTYLICENSEREADME
drwxr-xr-x 2 root bin 2048 Sep 17 06:06 License
drwxr-xr-x 7 root root 2048 Sep 17 06:07 Solaris_10
drwxr-xr-x 2 root root 2048 Sep 17 06:07 boot
-rwxr-xr-x 1 root root 257 Sep 17 05:54 installer
drwxr-xr-x 5 root root 2048 Sep 17 06:07 platform

-- TaTsHuYa --

Mount ISO File in Linux

[root@tatshuya]# ls -l /mnt
total 0
[root@tatshuya]# mount -o loop rhel-server-5.4-i386-disc1.iso /mnt
[root@tatshuya]# ls -l /mnt
..
..
..
-r--r--r-- 7 root root 1706 2009-08-04 15:25 RPM-GPG-KEY-redhat-beta
-r--r--r-- 7 root root 1164 2009-08-04 15:25 RPM-GPG-KEY-redhat-release
dr-xr-xr-x 3 root root 94208 2009-08-19 13:35 Server
-r--r--r-- 1 root root 14594 2009-08-19 13:39 TRANS.TBL
dr-xr-xr-x 3 root root 2048 2009-08-19 13:30 VT



-- TaTsHuYa --

Bash: If and Case

for ((i=0;i<4;i+=1));
do
case $i in
"0" )
echo "${i} : zero"
;;
"1" )
echo "${i} : one"
;;
esac

if [ ${i} -eq "3" ]; then
echo "Here is number : Three "
fi
done

*** Result ***
0 : zero
1 : one
Here is number : Three

-- TaTsHuYa --

Bash: Loop & Random

*** For Loop ***
for ((i=0;i<10;i+=1)); do
let R=$RANDOM%20;
echo "i=${i} : R=${R}";
done

*** Result For Loop ***
i=0 : R=15
i=1 : R=17
i=2 : R=14
i=3 : R=11
i=4 : R=16
i=5 : R=14
i=6 : R=16
i=7 : R=8
i=8 : R=12
i=9 : R=5

*** While Loop ***
i=0
while [ $i -lt 10 ]; do
let R=$RANDOM%20;
echo "i=${i} : R=${R}";
(( i+=1 ));
done

*** Result While Loop ***
i=0 : R=6
i=1 : R=19
i=2 : R=13
i=3 : R=19
i=4 : R=7
i=5 : R=3
i=6 : R=16
i=7 : R=5
i=8 : R=19
i=9 : R=4


-- TaTsHuYa --

NTP Server

Here is my configure to setup ntp server
'/etc/ntp.conf'

driftfile /var/lib/ntp/ntp.drift
restrict default ignore


statistics loopstats peerstats clockstats
filegen loopstats file loopstats type day enable
filegen peerstats file peerstats type day enable
filegen clockstats file clockstats type day enable


# Server that we want to sync time from.
restrict 66.187.233.4 mask 255.255.255.255 nomodify notrap noquery
server 66.187.233.4 prefer

# Client group that access to our ntp server.
restrict 127.0.0.1
restrict 192.168.0.0 mask 255.255.0.0 nomodify


server 127.127.1.0 # local clock
fudge 127.127.1.0 stratum 10

-- TaTsHuYa --

Agent admitted failure to sign using the key.

[tatshuya]# ssh remotehost
Agent admittd failure to sign using the key.
password:

Now, My solution to solve this problem is logout you desktop and login again.

SSH without password.

[tatshuya]# ssh-keygen -t dsa -b 1024

Generating public/private rsa key pair.
Enter file in which to save the key (/home/tatshuya/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:

[tatshuya]# ls -al .ssh
-rw------- 1 tatshuya tatshuya 887 Oct 14 04:20 id_rsa
-rw-r--r-- 1 tatshuya tatshuya 244 Oct 14 04:20 id_rsa.pub

*** Then copy public key 'id_rsa.pub' to remote host and rename to 'authorized_keys'
[tatshuya]# scp /home/tatshuya/.ssh/id_rsa.pub tatshuya@remotehost:.ssh/authorized_keys

*** Test connection by ssh
[tatshuya]# ssh remotehost
Last login: Wed Oct 14 16:10:47 2009 from 10.10.10.2
You have new mail.
[remotehost]#



-- TaTsHuYa --

How to know the interface speed and duplex in your linux.

[tatshuya]# ethtool eth0
Settings for eth0:
Supported ports: [ TP ]
Supported link modes: 10baseT/Half 10baseT/Full
100baseT/Half 100baseT/Full
1000baseT/Full
Supports auto-negotiation: Yes
Advertised link modes: 1000baseT/Full
Advertised auto-negotiation: Yes
Speed: 1000Mb/s
Duplex: Full
Port: Twisted Pair
PHYAD: 0
Transceiver: internal
Auto-negotiation: on
Supports Wake-on: umbg
Wake-on: g
Current message level: 0x00000007 (7)
Link detected: yes


If you want to force speed to 1000 and duplex to full you can set like this.
ethtool -s eth0 speed 1000 duplex full autoneg off

-- TaTsHuYa --

Grep command.

if you want to grep one word on the file you can type grep .
Ex.

grep facebook cookies.txt
.facebook.com TRUE / FALSE 1256032076 locale en_GB

but when you want to grep or , this command will help you.

grep "facebook\|zynga" cookies.txt
.facebook.com TRUE / FALSE 1256032076 locale en_GB
mwfb.zynga.com FALSE /mwfb/ FALSE 1255435201

I can use the upper command only in Linux but in Solaris use this pattern
'/usr/xpg4/bin/grep -E 'facebook|zynga' cookies.txt'

-- TaTsHuYa --

List process id.

Today I learn to use the new command that use to list process id

pgrep sendmail
12615
12625

if you want to see the name of process, use -l
pgrep -l sendmail
12615 sendmail
12625 sendmail


-- TaTsHuYa --

How to check which scheduler job run longer than 1000 seconds.

Today we found scheduler job running very long time, and I want to create script to check which job has been running more than 1000 seconds.


desc ALL_SCHEDULER_RUNNING_JOBS;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
JOB_NAME NOT NULL VARCHAR2(30)
JOB_SUBNAME VARCHAR2(30)
SESSION_ID NUMBER
SLAVE_PROCESS_ID NUMBER
SLAVE_OS_PROCESS_ID VARCHAR2(12)
RUNNING_INSTANCE NUMBER
RESOURCE_CONSUMER_GROUP VARCHAR2(32)
ELAPSED_TIME INTERVAL DAY(3) TO SECOND(2)
CPU_USED INTERVAL DAY(3) TO SECOND(2)

SQL> select JOB_NAME, ELAPSED_TIME from ALL_SCHEDULER_RUNNING_JOBS where ELAPSED_TIME > INTERVAL '1000' SECOND ;

JOB_NAME ELAPSED_TIME
------------------- ---------------------------------------------------------------------------
JOB_XMLSEND_LDAP +001 13:43:31.84
SENDXML_COLLECTION +004 13:33:31.91
SENDXML_CHARGING +004 21:00:42.84


-- TaTsHuYa --

ORA-00600: internal error code, arguments: kghstack_alloc, datablk : kspptsp], [, ], [, ], [, [].

This error occur because I try to start the database that I copied datafile,controlfile,redologfile from RHEL5 to Solaris 10 and try to start it on solaris.

-- TaTsHuYa --

Change data to lower captical in awk.

*** Original data in upper capital

[tatshuya]# cat user-lmi3.txt |awk '{print $1}'
SYS
SYSTEM
DBSNMP
TATSHUYA

*** When you want to change data in lower capital.

[tatshuya]# cat user-lmi3.txt |awk '{print tolower($1) }'
sys
system
dbsnmp
tatshuya


-- TaTsHuYa --

SSH Password prompt very slow when connect to server.

When I connect to the server I am waiting for password prompt around 15 seconds.

ssh -v 10.10.10.2

....
....
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: gssapi-keyex,gssapi-with-mic,publickey,password,keyboard-interactive
debug1: Next authentication method: gssapi-keyex
debug1: No valid Key exchange context
debug1: Next authentication method: gssapi-with-mic

debug1: Unspecified GSS failure. Minor code may provide more information
No credentials cache found

debug1: Unspecified GSS failure. Minor code may provide more information
No credentials cache found

debug1: Unspecified GSS failure. Minor code may provide more information


debug1: Next authentication method: publickey
debug1: Trying private key: /home/boonchai_noo/.ssh/identity
debug1: Trying private key: /home/boonchai_noo/.ssh/id_rsa
debug1: Trying private key: /home/boonchai_noo/.ssh/id_dsa
debug1: Next authentication method: keyboard-interactive
Password:

*** In the debug mode I found there are 3 times to try authentication method : GSSAPI so I man the ssh_config to look how to disable how to disable GSSAPI,
the option is GSSAPIAuthentication = no

I try to login againt with option
[tatshuya]# ssh -v -o GSSAPIAuthentication=no 10.1010.2

....
....
debug1: ssh_rsa_verify: signature correct
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: gssapi-keyex,gssapi-with-mic,publickey,password,keyboard-interactive
debug1: Next authentication method: publickey
debug1: Trying private key: /home/boonchai_noo/.ssh/identity
debug1: Trying private key: /home/boonchai_noo/.ssh/id_rsa
debug1: Trying private key: /home/boonchai_noo/.ssh/id_dsa
debug1: Next authentication method: keyboard-interactive
Password:


**** there is no use the authentication method in debug mode and the time for display password prompt faster than before.

Thank you walker for this information.

-- TaTsHuYa --

Automatic start Oracle 10g on Solaris 10.

Create new file in '/etc/init.d' in this instruction I use new file name is oracle.

[tatshuya#] vi /etc/init.d/oracle

#!/bin/sh

user=oracle
SU=/bin/su
ORACLE_HOME=/oracle/product/10.2.0/db
export ORACLE_HOME

case "$1" in
start)
$SU $user $ORACLE_HOME/bin/dbstart
;;
restart)
$SU $user $ORACLE_HOME/bin/dbshut
$SU $user $ORACLE_HOME/bin/dbstart
;;
stop)
$SU $user $ORACLE_HOME/bin/dbshut
;;
*)
echo "Usage : oracle start restart stop"
;;
esac

# Save file and then create symbolic link

ln -s /etc/init.d/oracle /etc/rc0.d/K01oracle

ln -s /etc/init.d/oracle /etc/rc2.d/K01oracle
ln -s /etc/init.d/oracle /etc/rc2.d/S88oracle

ln -s /etc/init.d/oracle /etc/rc3.d/K01oracle
ln -s /etc/init.d/oracle /etc/rc3.d/S88oracle

*** Change parameter from N to Y in oratab , In solaris 10 this file place in
'/var/opt/oracle/oratab'

tatshuya:/oracle/product/10.2.0/db:Y

thank you A! help for this instruction.

--TaTsHuYa--

ORA-12154: TNS:could not resolve the connect identifier specified

Sometime you may start listener and want to connect by ORANET to the database immediately, but you receive the message "ORA-12154: TNS:could not resolve the connect identifier specified" after you investigate you found you listener still have no service register.

[tatshuya]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 03-OCT-2009 20:53:53

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

Starting /oracle/product/10.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /oracle/product/10.2.0/db/network/admin/listener.ora
Log messages written to /oracle/product/10.2.0/db/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tatshuya.home.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tatshuya.home.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 03-OCT-2009 20:53:53
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/10.2.0/db/network/admin/listener.ora
Listener Log File /oracle/product/10.2.0/db/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tatshuya.home.com)(PORT=1521)))
The listener supports no services
The command completed successfully


If you want to force Oracle's Listerner to have service you can wait or force a database to register service in listener by.

connect to sys or system.
[tatshuya]# sqlplus "/ as sysdba"

SQL> alter system register;

System altered.

Then check you listener status again.

[tatshuya]# lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 03-OCT-2009 21:06:41

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tatshuya.home.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 03-OCT-2009 20:53:53
Uptime 0 days 0 hr. 12 min. 47 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/10.2.0/db/network/admin/listener.ora
Listener Log File /oracle/product/10.2.0/db/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tatshuya.home.com)(PORT=1521)))
Services Summary...
Service "tatshuya" has 1 instance(s).
Instance "tatshuya", status READY, has 1 handler(s) for this service...
Service "tatshuya_XPT" has 1 instance(s).
Instance "tatshuya", status READY, has 1 handler(s) for this service...
The command completed successfully


--TaTsHuYa--

How to change lable partition on RHEL

When I want to change mount point in '/etc/fstab' in RHEL, if found it use Label to identified partition disk.

[root@tatshuya]# cat /etc/fstab
LABEL=/ / ext3 defaults 1 1
LABEL=/opt /opt ext3 defaults 1 2
LABEL=/usr /usr ext3 defaults 1 2
LABEL=/var /var ext3 defaults 1 2
LABEL=/boot /boot ext2 defaults 1 2
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
LABEL=SWAP-sda6 swap swap defaults 0 0

I want to change '/opt' to '/oracle'.

*** List existing label
[root@tatshuya]# e2label /dev/sda7
/opt

*** Change to new lable
[root@tatshuya]# e2label /dev/sda7 /oracle

*** List new label
[root@tatshuya]# e2label /dev/sda7
/oracle


-- TaTsHuYa --

Problem when installation Oracle 10g on RHEL5 ( 64 bits )

When I try to install Oracle10g on RHEL5

[tatshuya#] ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Passed


All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-10-03_12-48-53PM. Please wait ...[oracle@migrate ~]$ Exception in thread "main" java.lang.UnsatisfiedLinkError: /tmp/OraInstall2009-10-03_12-48-53PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
at java.lang.ClassLoader$NativeLibrary.load(Native Method)
at java.lang.ClassLoader.loadLibrary0(Unknown Source)
at java.lang.ClassLoader.loadLibrary(Unknown Source)
at java.lang.Runtime.loadLibrary0(Unknown Source)
at java.lang.System.loadLibrary(Unknown Source)
at sun.security.action.LoadLibraryAction.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.awt.NativeLibLoader.loadLibraries(Unknown Source)
at sun.awt.DebugHelper.(Unknown Source)
at java.awt.Component.(Unknown Source)

I try to look what the library missing by command 'ldd'.
[tatshuya#] ldd /tmp/OraInstall2009-10-03_12-50-33PM/jre/1.4.2/lib/i386/libawt.so
linux-gate.so.1 => (0xffffe000)
libmlib_image.so => not found
libjvm.so => not found
libXp.so.6 => not found
libXt.so.6 => /usr/lib/libXt.so.6 (0xf7bcf000)
libXext.so.6 => /usr/lib/libXext.so.6 (0xf7bbe000)
libXtst.so.6 => /usr/lib/libXtst.so.6 (0xf7bb8000)
libX11.so.6 => /usr/lib/libX11.so.6 (0xf7ab5000)
libm.so.6 => /lib/libm.so.6 (0xf7a8e000)
libdl.so.2 => /lib/libdl.so.2 (0xf7a8a000)
libjava.so => not found
libc.so.6 => /lib/libc.so.6 (0xf7945000)
libSM.so.6 => /usr/lib/libSM.so.6 (0xf793c000)
libICE.so.6 => /usr/lib/libICE.so.6 (0xf7922000)
libXau.so.6 => /usr/lib/libXau.so.6 (0xf791f000)
libXdmcp.so.6 => /usr/lib/libXdmcp.so.6 (0xf7919000)
/lib/ld-linux.so.2 (0x0048d000)

When I check the package 'libXp' I found it not installed in my system.
[tatshuya#] rpm -q libXp
package libXp is not installed

*** Find the package & install

[tatshuya#] rpm -ivh libXp-1.0.0-8.1.el5.i386.rpm
warning: libXp-1.0.0-8.1.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 37017186
Preparing... ########################################### [100%]
1:libXp ########################################### [100%]


./database/runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Passed


All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-10-03_12-59-08PM. Please wait ...

Finally it's work.

** Do not confuse about why I use i386 package because you should look carefully in the error "OraInstall2009-10-03_12-48-53PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6" you see i386 word. **

Installation Oracle10gR2 on RHEL 5 x86_64

*** Add Group & User
[tatshuya]# groupadd oinstall
[tatshuya]# groupadd dba
[tatshuya]# groupadd oper
[tatshuya]# useradd -g oinstall -G dba,oper oracle
[tatshuya]#


*** Check package that Oracle need

[tatshuya]# rpm -q binutils compat-db compat-libstdc++-33 glibc glibc-devel glibc-headers gcc gcc-c++ libstdc++ cpp make libaio ksh elfutils-libelf sysstat libaio libaio-devel setarch libXp --qf '%{name}.%{arch}\n'|sort

*** Result when check package
binutils.x86_64
compat-libstdc++-33.i386
compat-libstdc++-33.x86_64
cpp.x86_64
elfutils-libelf.i386
elfutils-libelf.x86_64
gcc-c++.x86_64
gcc.x86_64
glibc-devel.i386
glibc-devel.x86_64
glibc-headers.x86_64
glibc.i686
glibc.x86_64
ksh.x86_64
libaio.i386
libaio.i386
libaio.x86_64
libaio.x86_64
libstdc++.i386
libstdc++.x86_64
make.x86_64
package compat-db is not installed
package libaio-devel is not installed
package libXp is not installed
package sysstat is not installed
setarch.x86_64

*** Find Package that not installed and install them!!!!

[tatshuya]# rpm -ivh libaio-devel-0.3.106-3.2.x86_64.rpm compat-db-4.2.52-5.1.x86_64.rpm sysstat-7.0.2-3.el5.x86_64.rpm libXp-1.0.0-8.1.el5.i386.rpm
warning: libaio-devel-0.3.106-3.2.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 37017186
Preparing... ########################################### [100%]
1:sysstat ########################################### [ 33%]
2:libaio-devel ########################################### [ 67%]
3:compat-db ########################################### [100%]


*** Setting System parameters at /etc/sysctl.cong
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

*** After save file then execute command 'sysctl -p' to apply
[tatshuya]# sysctl -p

*** Result of 'sysctl -p' command
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

*** Edit the /etc/pam.d/login file and add following line:
session required pam_limits.so

*** Edit the /etc/security/limits.conf file and add following lines:
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

*** Run installer
[tatshuya#] ./runInstaller


--TaTsHuYa--

How to find your usb external harddisk on Solaris 9.

There are many ways to know control number of your usb external harddisk

#iostat -En
iostat -En
c0t0d0 Soft Errors: 1 Hard Errors: 0 Transport Errors: 31
Vendor: TOSHIBA Product: DVD-ROM SD-C2612 Revision: 1011 Serial No:
Size: 0.00GB <0>
Media Error: 0 Device Not Ready: 0 No Device: 0 Recoverable: 0
Illegal Request: 1 Predictive Failure Analysis: 0
c4t0d0 Soft Errors: 1 Hard Errors: 0 Transport Errors: 0 Vendor: WD Product: 1600BEV External Revision: 1.05 Serial No: Size: 160.04GB <160039272960> Media Error: 0 Device Not Ready: 0 No Device: 0 Recoverable: 0 Illegal Request: 1 Predictive Failure Analysis: 0

-- or --
# format -e
Searching for disks...done
AVAILABLE DISK SELECTIONS:
0. c1t0d0
/pci@9,600000/SUNW,qlc@2/fp@0,0/ssd@w500000e010a7d191,0
1. c1t1d0 mirror
/pci@9,600000/SUNW,qlc@2/fp@0,0/ssd@w500000e010a7d2e1,0
2. c2t5006016810209AB5d0
/pci@8,600000/SUNW,qlc@2/fp@0,0/ssd@w5006016810209ab5,0
3. c2t5006016110209AB5d0
/pci@8,600000/SUNW,qlc@2/fp@0,0/ssd@w5006016110209ab5,0
4. c4t0d0 /pci@9,700000/usb@1,3/storage@2/disk@0,0
5. emcpower0a
/pseudo/emcp@0


-- Dmesg doesn't tell the controll number

#dmesg
Sep 24 18:45:28 onestopII usba: [ID 855233 kern.info] USB-device: storage@2, scsa2usb1 at bus address 2
Sep 24 18:45:28 onestopII usba: [ID 593373 kern.info] Western Digital External HDD 575843373038433030363435
Sep 24 18:45:28 onestopII genunix: [ID 936769 kern.info] scsa2usb1 is /pci@9,700000/usb@1,3/storage@2
Sep 24 18:45:28 onestopII genunix: [ID 408114 kern.info] /pci@9,700000/usb@1,3/storage@2 (scsa2usb1) online
Sep 24 18:45:28 onestopII scsi: [ID 193665 kern.info] sd2 at scsa2usb1: target 0 lun 0
Sep 24 18:45:28 onestopII genunix: [ID 936769 kern.info] sd2 is /pci@9,700000/usb@1,3/storage@2/disk@0,0
Sep 24 18:45:28 onestopII genunix: [ID 408114 kern.info] /pci@9,700000/usb@1,3/storage@2/disk@0,0 (sd2) online

Waiting for Job queue slaves to complete

SQL> shutdown immediate

..
..
Stopping background process CJQ0
Thu Sep 24 17:07:47 2009
Stopping background process MMNL
Thu Sep 24 17:07:48 2009
Stopping background process MMON
Thu Sep 24 17:07:49 2009
Shutting down instance (immediate)
License high water mark = 11
Thu Sep 24 17:07:49 2009
Stopping Job queue slave processes
Thu Sep 24 17:07:52 2009
Waiting for Job queue slaves to complete
.
.
Thu Sep 24 17:18:37 2009
Job queue slave processes stopped

I found this message in alter log file after I waiting for the database shutdown.
wait .. wait .. wait .. wait..and wait...


Finally my database was shutdown after issue the command around 10 minutes.

--TaTsHuYa--

ORA-00600: internal error code, arguments: [kccrbl_stale]

This error is related with previous my blog [ ORA-00214: controlfile xxx version yyyy consistent with file zzz ]

It happen because I changed to use the wrong control file when I try to start database after server crashed. I never see the version of checkpoint before choosen.

So after I try to mount the database this error occur.

alter database mount
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kccrbl_stale], [0], [61176],
[61127], [], [], [], []

I resolve this problem by shutdown database and change the control file in init file to use the lastest update.

--TaTsHuYa--

ORA-00214: controlfile xxx version yyy inconsistent with file zzzz.

My database server was crashed and when I try start the database, it show error.

SQL> startup
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 780056 bytes
Variable Size 275519720 bytes
Database Buffers 260046848 bytes
Redo Buffers 524288 bytes
ORA-00214: controlfile '/oracle/product/10.2.0/oradata/emrep/control01.ctl'
version 61130 inconsistent with file
'/oracle/product/10.2.0/oradata/emrep/control02.ctl' version 61127

resolve by
look in the version of checkpoint in control file which one is lastest update.
In this situation 'control01.ctl' is the lastest update so I will change init file to use only 'control01.ctl'

Existing:
*.control_files='/oracle/product/10.2.0/oradata/emrep/control01.ctl','/oracle/product/10.2.0/oradata/emrep/control02.ctl','/oracle/product/10.2.0/oradata/emrep/control03.ctl'

New:
*.control_files='/oracle/product/10.2.0/oradata/emrep/control01.ctl'


SQL> shutdown immediate
SQL> startup pfile=inittatshuya.ora
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 780056 bytes
Variable Size 275519720 bytes
Database Buffers 260046848 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.



You should to shutdown again and copy control01.ctl to control02.ctl and control03.ctl.
and change your init file to the old.

ORA-00600: internal error code, arguments: [17059]

What's happen of our database today??????????

Today we just found one of our database has many errors on alert log ORA-00600 with argument [ 17059 ] , actually this error occur since around 1 a.m. but nobody known until we received SMS in the evening ( 7 p.m. ) that has lower free spaces in the system.

My co-worker access to that server, he found the service has been shutdown already because Oracle Path is out of space and it's full of dump file in udump directory.

When he clear the space and oracle back to the online state, he found the ORA-00600 argument [ 17059 ] occur every time when user call a package.
He to find the solution on the Oracle Metalink, that may be a bug of the oracle again. The short term to resolve this problem is flush shard pool by command "alter system flush shared pool".

After solve the problem, we saw the database back to the normal state, we dicided to go back home. And when I'm home, I want to make sure the database back to the normal state really, Yep!!!! there are no error with ORA-00600 agument [ 17059 ] again but I found the new error in the another database.

.............. T-T .................

--TaTsHuYa--

configure: error: C++ compiler cannot create executables

When you found this error while you use "configure" on ubuntu that mean, you need some package before you're trying to compile program.

..
..
checking for FCC... no
checking for KCC... no
checking for RCC... no
checking for xlC_r... no
checking for xlC... no
checking for C++ compiler default output file name... configure: error: C++ comp
iler cannot create executables
See `config.log' for more details.

try to install "build-essential" package, It can resolve this problem.
#sudo apt-get install build-essential

-TaTsHuYa-

How to build Tora 2.1 with Oracle support

How to build Tora 2.1 with Oracle support

In ubuntu 8.10, "tora" package version is 1.3.23 and not support Oracle, If you want to use the new version use must download TORA souce code from "http://torasql.com" and then if you want to use TORA connect to Oracle use must download Oracle Instantclient or Oracle Client from Oracle Website.

Download Oracle Instantclient 11 from Oracle's webstie
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html
There are 2 packages that you need

- Instant Client Package - Basic
- Instant Client Package - SDK
- Instant Client Package - SQL Plus ( Optional for test connection only )

Then when you have everything already, let go to next step
unzip Instant Client Package - Basic
# unzip instantclient-basic-linux32-11.2.0.1.zip
unzip Instant Client Package - SDK
# unzip instantclient-sdk-linux32-11.2.0.1.zip
unzip Instant Client Package - SQL Plus
# unzip instantclient-sqlplus-linux32-11.2.0.1.zip

Create soft link for Oracle Instantclient library:
#cd /home/tatshuya/instantclient_11_2
#ln -s libclntsh.so.11.1 libclntsh.so

Before you compile you need some library
#apt-get install libqt4-dev libqscintilla2-dev build-essential

Prepare Variable environment before compile the tora
#export LD_LIBRARY_PATH=/home/tatshuya/instantclient_11_2
#export CPPFLAGS="-I/home/tatshuya/instantclient_11_2/sdk/include"

Compile TORA
#cd ../tora-2.1.0
#./configure --with-instant-client=/home/tatshuya/instantclient_11_2
#make
#make install

Then use tora program
#export LD_LIBRARY_PATH=/home/tatshuya/instantclient_11_2
#/usr/local/tora/bin/tora