TaTsHuYa BLOG: December 2009

Monday, December 21, 2009

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

Wednesday, December 16, 2009

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

Tuesday, December 15, 2009

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

Monday, December 14, 2009

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

Sunday, December 13, 2009

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.

Wednesday, December 09, 2009

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

Tuesday, December 08, 2009

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

Wednesday, December 02, 2009

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

Tuesday, December 01, 2009

Deploy Applcation on OracleAS

TaTsHuYa BLOG