Wednesday, October 24, 2012

configure new service on oracle database

ISSUE:
user like to use a specific tnsname entry to connect to new database

CAUSE:
It might require a lot effort or time to change tnsname configure on application side to connect. it is required to configure database server to accept request with old tnsname entry with hostname updated.

SOLUTION:
if the tnsname entry use SERVICE_NAME instead of SID_NAME, it can be resolved by configure a new service to match the one in tnsname entry.
if the tnsname entry use SID_NAME, rename your database is the only option as I know.

SOLUTION DETAILS OF CONFIGURE NEW SERVICE:
1. create new service
exec dbms_service.create_service('service_name','service_name');
2. start the service
exec dbms_service.start_service('service_name');
ATTN: manually do it -- alter system set service_names='service1, servcie2, service3'
4. dynamic register the service if local listerner parameter was configured
 alter system register
5. static register the service
add below inot sid_list

            (SID_DESC =
              (GLOBAL_DBNAME=service_name)
              (ORACLE_HOME = /u01/app/oracle/product/11.2.3.0/dbhome)
              (SID_NAME = sid)
              (SERVICE_NAME =srvice_name )
            )


reference:




Tuesday, October 23, 2012

recovery file dest in Oracle database

ISSUE:
Archiver might hang due to space usage in recovery area space.

CAUSE:
You can set up recovery area for recovery files such as archived log , control file to speed up database recovery.
two parameters to configure
db_recovery_file_dest -- where recovery area located e.g /u01/app/database/rflocation, +RFGRP
db_recovery-file_dest_size -- how many space can be used from the area

If size is too large, it will use too much space.
if size is too samll, it will hang your db if you save archvied log in the area.


CHECK:
v$recovery_file_dest
v$recovery_area_usage

reference:
http://www.pafumi.net/Flash_Recovery_Area.html

Monday, October 22, 2012

Encountered unrecognized patch ID: Z6MQ -- Oracle Weblogic Server Patch 10.3.5.0.5

ISSUE:
Message "Encountered unrecognized patch ID: Z6MQ" displayed when execute smartupdate (bsu) patch utility.

CAUSE: 
1. smartupdate (bsu) version is 3.2.1 or order
2. didn't put patch file into default direcotry

SOLUTION:
1. install smartupdate(bsu) version 3.3.0 - patch 12426828
2. unzip patch files to {MW_HOME}/utils/bsu/cache_dir
3. execute the patch command again.

command:
1. install
bsu.sh -install -patch_download_dir=/downlaod_dir -patchlist=Z6MQ -prod_dir={MW_HOME}/{WL_HOME}
2. view applied patch
bsu.sh -view -status=applied -prod_dir={MW_HOME}/{WL_HOME}
3. view downloaded patch
bsu.sh -view -status=downloaded -prod_dir={MW_HOME}/{WL_HOME} -patch_download_dir=/downlaod_dir
4. bsu version
bsu.sh -version
5. debug
bsu.sh -log=bsu.debug
6. report applied patch
bsu.sh -report

reference:

Tuesday, October 16, 2012

Run job in background and not terminated after logout in Linux

ISSUE:
We'd like to run a job in background and not terminated after logout after we already start the job or forget to use nohup or forget to include & at  the end.

SOLUTION:
we can use below command from shell prompt to fix the issue.
Cntl Z + bg
jobs
nohup
disown
disown -h   (will keep the process to current shell's process tree until you exit.)

e.g.
1. start a job and run foregroud
a. Crtl Z  -- stop the job to get back to shell prompt
b. jobs  -- list jobs with job_id
c. bg job_id -- start the job in backgroud
d. disown -h %job_id
2. start a job with nohup but forget to include & at the end
a. Crtl Z -- stop the job to get back to shell prompt
b. jobs -- list jobs with job_id
c. bg job_id --start the job in background
3. start a job without nohup in background
a. jobs -- list jobs with job_id
b. disown -h %job_id


reference:
http://unix.stackexchange.com/questions/3886/difference-between-nohup-disown-and
http://linux-quirks.blogspot.com/2011/04/nohup-or-disown.html
http://www.quantprinciple.com/invest/index.php/docs/tipsandtricks/unix/jobcontrol/
http://stackoverflow.com/questions/625409/how-do-i-put-an-already-running-process-under-nohup

Perl regular expression match

Simple Test Perl Script to test perl match

#!/usr/bin/perl

if ($ARGV[0] =~ m/.*ORA-0*(54|1142|1146|1234)(\D.*|$)/ ) {
print "match \n" ;
} else {
print "no match \n";
}

print "$ARGV[0] \n";

TEST:
[test@test shell]$ perl test.pl 'idfsdfsd ORA-1234'
match
idfsdfsd ORA-1234
[test@test shell]$ perl test.pl 'idfsdfsd ORA-123'
no match
idfsdfsd ORA-123
[test@test shell]$ perl test.pl 'idfsdfsd ORA-1234 ss'
match
idfsdfsd ORA-1234 ss
[test@test shell]$ perl test.pl 'idfsdfsd ORA-1234:'
match
idfsdfsd ORA-1234:
[test@test shell]$ perl test.pl 'idfsdfsd ORA-124:'
no match
idfsdfsd ORA-124:
[test@test shell]$ perl test.pl 'idfsdfsd OR-1234:'
no match
idfsdfsd OR-1234:
[test@test shell]$

Wild Character:
. - any character
.* - 0 or any repeated cahracter
a* - 0 or any repeated a
( a|b|c) - a or b or c
$ - end of sring
\D - none digit charactor
[0-9] -digit charactor
[^0-9] - none digit charactor

Reference:

Alertlog Error Filter in Oracle Cloud Control

ISSUE:
Some ORA- error in alert log file of oracle database instance is known and can be ignored for your system. You want to filter the error out so that no event will be generated when the error appears in the log

SOLUTION:
modify the filter expression
in all or any database instance target
Go to oracle database >monitoring> metric and collection setting>
Click edit for General Alter Log Error
change .*ORA-0*(54|1142|1146)\D.* to .*ORA-0*(54|1142|1146|???)(\D.*|$)

e. g.
to fileter ora-1234

new filter expression should be .*ORA-0*(54|1142|1146|1234)(\D.*|$)


reference:
http://docs.oracle.com/cd/E24628_01/em.121/e25160/osm_instance.htm
http://docs.oracle.com/cd/E24628_01/doc.121/e24473/toc.htm
http://docs.python.org/release/1.5.2p2/lib/re-syntax.html

Thursday, October 11, 2012

enable \n for echo linux command

echo -e " aaaa \n bb"
 aaaa
 bb

reference:
http://techteam.wordpress.com/2008/09/25/n-not-creating-new-line-in-bash-script/

Variable/Parameter Substitution in Shell Script

ISSUE:
You would like manipulate input argument in your shell script for many reasons.
for example
change input string to all upper case or lower case
create a log file according to prefix of script name

SOLUTION:
You can use various ways to do it (shell function, sed, awk).

PRACTICAL CASE:
1. convert string to lower case
${VARIABLE_NAME,,}

2. convert string to upper case
${VARIABLE_NAME^^}

3. get right of the .sh from script name
${0%.sh}

4.get string length
${#VARIABLE_NAME}

5. string position
${VARIABLE_NAME:postion} like ${1:0}

6. replacement
${VARIABLE_NAME/aa/bb/}

reference:
http://mintaka.sdsu.edu/GF/bibliog/latex/debian/bash.html
http://tldp.org/LDP/abs/html/parameter-substitution.html

Wednesday, October 10, 2012

STATISTICS ( STATS) collection job in oracle

10G:
job name - GATHER_STATS_JOB
 SELECT JOB_NAME, SCHEDULE_NAME, SCHEDULE_TYPE, ENABLED
FROM DBA_SCHEDULER_JOBS
WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';


interface - scheduler



11G:
job is changed to Auto TASK  using DBMS_AUTO_TASK_ADMIN

dba_auto_task_client

select client_name, status from dba_autotask_client;
CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED



"auto optimizer stats collection" is the stats collection task.

 interface - dbms_auto_task_admin

DBMS_AUTO_TASK_ADMIN.DISABLE;
DBMS_AUTO_TASK_ADMIN.DISABLE (
   client_name       IN    VARCHAR2,
   operation         IN    VARCHAR2,
   window_name       IN    VARCHAR2);
DBMS_AUTO_TASK_ADMIN.ENABLE;
DBMS_AUTO_TASK_ADMIN.ENABLE (
   client_name       IN    VARCHAR2,
   operation         IN    VARCHAR2,
   window_name       IN    VARCHAR2); 


reference:  
http://askdba.org/weblog/2009/08/gather_stats_job-is-it-enabled/
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1133388300346992024
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_autotaskadm.htm
http://psoug.org/reference/dbms_auto_task_admin.html

hostname of redhat linux in AMI

/etc/hosts:
this is the place linux system search for ip matching

/etc/sysconfig/network:
this is the file for hostname command

You can user hostname to reset hostname.

reference:
http://www.ducea.com/2006/08/07/how-to-change-the-hostname-of-a-linux-system/
http://www.howtogeek.com/wiki/Change_the_Hostname_on_a_Redhat_Linux_Machine



oracle s3 cloud module installation and check

DOWNLOAD LINK:
http://www.oracle.com/technetwork/products/secure-backup/secure-backup-s3-484709.html

INSTALLATION:
what you downloaded is the installer jar file
run below command as oracle user
/u01/app/oracle/database/product/11.2.0/jdk/bin/java  -jar osbws_install.jar -AWSID xxxxxxxxxxxxxxxxx  -AWSKey xxxxxxxxxxxxxxxxxxxxxxxxxxxxx  -otnUser test@yahoo.co.in  -otnPass test -walletDir /u01/app/oracle/database/product/11.2.0/dbs/osbws_wallet  -libDir /u01/app/oracle/database/product/11.2.0/lib

You must have s3 account to get AWSID and AWSKey
You must have oracle OTN account to do installation.
Java version need be 1.5 at the time of this post

JAVA VERION CHECK:
java -version

OSB LIB VERSION CHECK:
sbttest -libname /u01/app/oracle/database/product/11.2.0/lib/libosbws12.so
libosbws12.so  is the new version.
sbttest -libname /u01/app/oracle/database/product/11.2.0/lib/libosbws11.so
libosbws11.so is the old version.

E.X.
sbttest output -libname /u01/app/oracle/database/product/11.2.0/lib/libosbws12.so > output.log

CONFIGURATION FILE:
/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora

RMAN COMMAND TO CALL OSB LIB:
1. restore control file
rman target /
    SET DBID=12345678
    STARTUP NOMOUNT;
    run {
    ALLOCATE CHANNEL CH1 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)'  FORMAT '%d_%I_%T_%U.backup';
    RESTORE until time "to_date('15-JUL-2012 14:00:00','DD-MON-YYYY HH24:MI:SS')" CONTROLFILE FROM AUTOBACKUP ;
    RELEASE CHANNEL CH1 ;
    }

run {
    ALLOCATE CHANNEL CH1 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)'  FORMAT '%d_%I_%T_%U.backup';
    RESTORE until time 'sysdate- 11' CONTROLFILE FROM AUTOBACKUP ;
    RELEASE CHANNEL CH1 ;
    }

run {
    ALLOCATE CHANNEL CH1 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)'  FORMAT '%d_%I_%T_%U.backup';
    RESTORE CONTROLFILE FROM AUTOBACKUP ;
    RELEASE CHANNEL CH1 ;
    }

2. restore database
rman target /
run {
set until time "to_date('15-JUL-2012 14:00:00','DD-MON-YYYY HH24:MI:SS')";
        ALLOCATE CHANNEL CH1 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        ALLOCATE CHANNEL CH2 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        ALLOCATE CHANNEL CH3 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        ALLOCATE CHANNEL CH4 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        RESTORE DATABASE  ;
        RECOVER DATABASE ;
        RELEASE CHANNEL CH1;
        RELEASE CHANNEL CH2;
        RELEASE CHANNEL CH3;
        RELEASE CHANNEL CH4;
        }

3. simple shell script to run rman
after database is mounted.

#!/bin/sh

source /home/oracle/.bash_profile

rman target / << EOF
run {
set until time "to_date('15-JUL-2012 14:00:00','DD-MON-YYYY HH24:MI:SS')";
        ALLOCATE CHANNEL CH1 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        ALLOCATE CHANNEL CH2 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        ALLOCATE CHANNEL CH3 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        ALLOCATE CHANNEL CH4 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        RESTORE DATABASE  ;
        RECOVER DATABASE ;
        RELEASE CHANNEL CH1;
        RELEASE CHANNEL CH2;
        RELEASE CHANNEL CH3;
        RELEASE CHANNEL CH4;
        }
exit
EOF


reference:
http://www.oracle.com/technetwork/products/secure-backup/secure-backup-s3-484709.html
http://www.oracle.com/technetwork/topics/cloud/osbws-readme-083624.html
https://jira.primaticsfinancial.com/jira/login.jsp?permissionViolation=true&os_destination=%2Fbrowse%2FEMSS-5817

Monday, October 8, 2012

ORA-01797: this operator must be followed by ANY or ALL

ISSUE:
sql statment didn't run with error ORA-01797

CAUSE:
equal operator = followed by multiple value like (1,2)

SOLUTION:
 a. change = to in
b. add any before multiple value like = any (1, 2)

reference:
 http://oraclequirks.blogspot.com/2008/06/ora-01797-this-operator-must-be.html

rman catalog view

RC_RMAN_BACKUP_JOB_DETAILS:

select  start_time, elapsed_seconds/3600 Hour, input_bytes,output_bytes, input_type, status
from rc_rman_backup_job_details


reference:
http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcviews001.htm

Friday, October 5, 2012

Useful linux command

system-config-network #network configuration graphic tool
strace # system call trace


shutdown linux command

shutdown -h now # turn off server now
halt   # turn off server now
reboot # reboot server
shutdown -r #reboot server

reference:
http://www.linuxforums.org/forum/slackware-linux/63551-halt-vs-shutdown.html