check datapump jobs

1. find job name
select * from user_datapump_jobs;
select * from dba_datapump_sessions;

2. login to datapump and attache the job
impdp attach=[schema].job_name

ATTEN: don't user hyphen - in you job name. You may not be able to attach to a job have hyphen in its name.
3.
impdp> status



reference:
http://peerdba.wordpress.com/2011/01/13/killing-expdpimpdp-job/

codepage setting informatica

1. the error you may get

LM_36011
Code page mismatch. Service process is running in code page [UTF-8 encoding of Unicode] whereas the service is configured in the Admin Console to run in code page [ISO 8859-1 Western European]


2. system variable used to set codepage

Use LANG and LC_ALL to set the UNIX code page for the installer.
Set the LANG and LC_ALL environment variables to the following values for the
language that you use:
- English: C
- Japanese: ja_JP.SJIS or ja_JP.PCK
- Portuguese: pt_BR.ISO8859-1, pt_BR.8859-15, or pt_BR
After you install, update the locale setting to one that is compatible with the code
page of the repository.

3. another vairable used to set codepage
INFA_CODEPAGENAME


4. there are three place you have code page set up

4.1 the informatica server process
4.2 the power center repository service
4.3 the power center integration service

5. the requirements is
5.1 both service must have superset or same of codepage as the server process
5.2 Repository service must have superset or same of codepage as the integration service.

6. situation cause the error
6.1 set INFA_CODEPAGENAME=UTF-8
repository = [UTF-8 encoding of Unicode]
integration = [ISO 8859-1 Western European]

6.2 repository = [ISO 8859-1 Western European]
integration = [UTF-8 encoding of Unicode]
if INFA_CODEPAGENAME is not set, default is [ISO 8859-1 Western European]

there are other situations,which may cause the error. It should be fixed after assign correct code page.

reference:
http://dbcrusade.blogspot.com/2009/06/informatica-setting-codepage.html
Informatica 9.0.1 administrator Guide
Power center 9.0.1 Hotfix 1 Installation and Configuration Guide

ulimit - user limit set in linux

1. check your limitation
ulimit -a
ulimit -n (number of open file)

2. set new limitation temporarily
ulimit -n newvalue

3. set new limitation permenant

3.1 vi /etc/security/limits.conf
add
username - nofile 2048

3.2. sysctl -p


4. set limitation system-wide
4.1 vi /etc/sysctl.conf
4.2 sysctl -p


reference:
http://cherry.world.edoors.com/CPZKoGkpxfbQ
http://www.linuxquestions.org/questions/slackware-14/increasing-file-descriptors-228004/
http://www.xenoclast.org/doc/benchmark/HTTP-benchmarking-HOWTO/node7.html

Process for service Admin Console failed to start - informatica 9.1.0 hf3 installation.

1. got following error when install informatica 9.1.0 HF3

 OutPut : [ICMD_10033] Command [ping] failed with error [[INFACMD_10053] Service [_AdminConsole] Domain [Domain_pncuatinformatica.evolvsuite.local] has failed to ping back.].

Error :

Troubleshooting Tips

1. Refer to the following log files for more information:

Administrator logs  $INFA_HOME/tomcat/temp/_AdminConsole/logs : Refer to this file to debug issues related to the Administrator tool.

administrator.log  $INFA_HOME/services/AdminConsole : This file contains some, but not all, log events pertaining to the Administrator tool.

2.Run the following command to enable the Informatica Administrator manually:

$INFA_HOME/isp/bin/infacmd.bat/sh enableservice -dn <MyDomain> -un Administrator -pd Administrator -sn _AdminConsole

Installation Status:WARNING

The Informatica 9.1.0 HotFix 3 installation is complete.


2. check $INFA_HOME/tomcat/logs/node.log

ERROR [Thread 6 of 6 in DomainServiceThreadPool] [SPC_10013] Process for service _AdminConsole failed to start.

3. check  $INFA_HOME/tomcat/temp/_AdminConsole/logs
 java.io.FileNotFoundException: /u01/app/informatica/tomcat/temp/_AdminConsole/work/_AdminConsole/localhost/administrator/tldCache.ser (Too many open files)

4. the problem is the open file limitation for user informatica was set to default 1024 in redhat linux.

5. unlimit -n 2048 as root

6. restart informatica. it is good.

rman command

1. create spfile backup and control file backup
backup spfile format '/tmp/spfileorcl.ora';
backup current controlfile format '/tmp/controlorcl.copy';

2. restore spfile and control file
rman target /
starup force nomount
restore spfile from '/tmp/spfilecolouat2012_01_12.ora';

shutdown
startup nomount
restore controlfile from '/tmp/controlcolouat2012_01_12.ctl';
alter database mount

3.
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT '/u01/backup/DB10G/%d_DB_%u_%s_%p';
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL ch1;
}
EXIT;
4. run scripts
#!/bin/bash

rman target / < LIST ARCHIVELOG ALL;
RMAN> LIST BACKUP OF ARCHIVELOG ALL;
RMAN> LIST BACKUP;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST BACKUP OF DATAFILE 1;
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST INCARNATION;
RMAN> LIST BACKUP BY FILE;
RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL;
RMAN> LIST COPY OF DATAFILE 1, 2, 3;
RMAN> LIST BACKUP OF DATAFILE 11 SUMMARY;
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
RMAN> LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";
RMAN> LIST BACKUPSET OF DATAFILE 1;
RMAN> LIST FAILURE;
RMAN> LIST FAILURE 641231 detail;




reference:
http://www.oracle-base.com/articles/misc/OracleShellScripting.php
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmconfa.htm
http://ss64.com/ora/rman_list.html

add swap space

login as root

1. create the file
dd if=/dev/zero of=/swapfile1 bs=1024 count=524288

2. make swap
mkswap /swapfile1

3. bring up swap
swapon /swapfile1

4.add it to /etc/fstab
vi /etc/fstab
append
/swapfil1 swap swap defaults 0 0

5.change mod
chmod 0600 /swapfile1

6. check
free -m

reference:
http://www.cyberciti.biz/faq/linux-add-a-swap-file-howto/

backup on the cloud

1. downlaod osbows_install.jar
from http://www.oracle.com/technetwork/database/secure-backup/secure-backup-s3-484709.html

2. run command
/u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin/java -jar osbws_install.jar -AWSID NNNNNNNNNNNNNNNNNNNN -AWSKey NNNNNNNnnnnnnnnnNNNNNNNNNNnnnn -otnUser user@yahoo.com -otnPass whoknows -walletDir /u01/app/oracle/database/product/11.2.0/dbs/osbws_wallet -libDir /u01/app/oracle/database/product/11.2.0/lib

the output:

Oracle Secure Backup Database Web-Service Install Tool, build 2011-02-04.0001
AWS credentials are valid.
S3 user already registered.
Registration ID: NNNNNNNNNNNNNNNNNNNNNNNNNNNNNN
S3 Logging Bucket: NNNNNNNNNNNNNNNNNN
Validating log bucket location ...
Validating license file ...
Create credential oracle.security.client.connect_string1
OSB web-services wallet created in directory /u01/app/oracle/product/11.2.0/dbs/osbws_wallet.
OSB web-services initialization file /u01/app/oracle/product/11.2.0/dbs/osbwsORCL.ora created.
Downloading OSB Web Services Software Library from file osbws_linux64.zip.
Downloaded 20851724 bytes in 1 seconds. Transfer rate was 20851724 bytes/second.
Download complete.
Extracted file /u01/app/oracle/product/11.2.0/lib/libosbws12.so

3. run rman
run {
allocate channel dev1 type sbt parms='SBT_LIBRARY=libosbws11.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)';
restore database;
release channel dev1
}


reference:
http://www.unbreakablecloud.com/wordpress/2010/08/21/46/
http://www.oracle.com/technetwork/topics/cloud/osbws-readme-083624.html

oracle asm 11gr2 installation guide

http://blog.ronnyegner-consulting.de/2009/09/14/oracle-11g-release-2-install-guide-%E2%80%93-grid-infrastructure-installation/

reclaim disk space from oracle

in database, space was allocated to segment through extents. Extents are bundle of blocks. If a datafile has free blocks after the large-used block_id, it can be resize to give these free blocks back to OS.

below is the script written by Tom Kyte:

-- This script was written by Tom Kyte and retrieved from asktom.oracle.com

set pages 0
set lin 150
set verify off
column file_name format a60 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
column sum format 999,999,999
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a95 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

You might see a lot of free space in a datafile but it can not be given back to OS. These free space are the gap blocks between used blocks in a datafile.

you can use below script to check gaps in a datafile or a tablespace.

-- -----------------------------------------------------------------------------------
-- File Name : http://www.oracle-base.com/dba/monitoring/ts_extent_map.sql
-- Author : DR Timothy S Hall
-- Description : Displays gaps (empty space) in a tablespace or specific datafile.
-- Requirements : Access to the DBA views.
-- Call Syntax : @ts_extent_map (tablespace-name) [all | file_id]
-- Last Modified: 25/01/2003
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK OFF
SET TRIMOUT ON
SET VERIFY OFF

DECLARE
l_tablespace_name VARCHAR2(30) := UPPER('&1');
l_file_id VARCHAR2(30) := UPPER('&2');

CURSOR c_extents IS
SELECT owner,
segment_name,
file_id,
block_id AS start_block,
block_id + blocks - 1 AS end_block
FROM dba_extents
WHERE tablespace_name = l_tablespace_name
AND file_id = DECODE(l_file_id, 'ALL', file_id, TO_NUMBER(l_file_id))
ORDER BY file_id, block_id;

l_block_size NUMBER := 0;
l_last_file_id NUMBER := 0;
l_last_block_id NUMBER := 0;
l_gaps_only BOOLEAN := TRUE;
l_total_blocks NUMBER := 0;
BEGIN
SELECT block_size
INTO l_block_size
FROM dba_tablespaces
WHERE tablespace_name = l_tablespace_name;

DBMS_OUTPUT.PUT_LINE('Tablespace Block Size (bytes): ' || l_block_size);
FOR cur_rec IN c_extents LOOP
IF cur_rec.file_id != l_last_file_id THEN
l_last_file_id := cur_rec.file_id;
l_last_block_id := cur_rec.start_block - 1;
END IF;

IF cur_rec.start_block > l_last_block_id + 1 THEN
DBMS_OUTPUT.PUT_LINE('*** GAP *** (' || l_last_block_id || ' -> ' || cur_rec.start_block || ')' ||
' FileID=' || cur_rec.file_id ||
' Blocks=' || (cur_rec.start_block-l_last_block_id-1) ||
' Size(MB)=' || ROUND(((cur_rec.start_block-l_last_block_id-1) * l_block_size)/1024/1024,2)
);
l_total_blocks := l_total_blocks + cur_rec.start_block - l_last_block_id-1;
END IF;
l_last_block_id := cur_rec.end_block;
IF NOT l_gaps_only THEN
DBMS_OUTPUT.PUT_LINE(RPAD(cur_rec.owner || '.' || cur_rec.segment_name, 40, ' ') ||
' (' || cur_rec.start_block || ' -> ' || cur_rec.end_block || ')');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Gap Blocks: ' || l_total_blocks);
DBMS_OUTPUT.PUT_LINE('Total Gap Space (MB): ' || ROUND((l_total_blocks * l_block_size)/1024/1024,2));
END;
/

PROMPT
SET FEEDBACK ON

How can you reclaim the gap blocks?

1. export the object that used the datafile. Then resize the datafile, import it back.
2. move the object to other tablespace then resize the datafile. move it back. rebuild index.

all above methods make the object unavailable during the procedure, but they definite will work.

3. alter table t move. rebuild indexes. this will make the table unvailable too, but it may not work since there is no guarant for extent change. It save one object move.

4. alter table t shrink space compact. it might work since there is no guarant for extent change. However it will keep the object online, however you have to enable row movement for the table which will cause chain migration.


reference:
http://www.dba-oracle.com/t_reclaiming_disk_space.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:54178027703899
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7246820117571
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:531274103649
http://www.dba-oracle.com/t_alter_table_move_shrink_space.htm

Log File Switch Completion (Checkpoint Incomplete)

If you find session is waiting on event Log File Switch Completion (Checkpoint Incomplete), the problem is log switch can not complete because check point process is writing dirty db buffer to data files.

the solution is increasing log file size and/or adding more log group(s).

this problem will stop redo generating and make database freeze.


sql examples:
1. add more log groups.
ALTER DATABASE
ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo')
SIZE 4M;

2. force switch log file
alter system switch logfile;

3. drop log group
alter database drop logfile group 1;

reference:
http://oracledba-vinod.blogspot.com/2009/10/log-file-switch-completion-checkpoint.html
http://docs.oracle.com/cd/B28359_01/server.111/b28310/onlineredo003.htm

cron job not executed

If you find your corn job doesn't executed and your crontab line is correct after careful check, it might be crond problem.

1. check if crond is running
ps -ef |grep crond
if crond is not running, start it.
service crond start

2. check log ( you must be root)
cat /var/log/cron
if you find error like below:
pam_access(crond:account): access denied for user `someuser' from `cron'
crond[9985]: CRON (someuser) ERROR: failed to open PAM security session: Success
crond[9985]: CRON (someuser) ERROR: cannot set security context
2.a check /etc/cron.allow and /etc/cron.deny
make sure user is allowed if cron.allow file exits
or not denied if cron.allow file doesn't exits.
2.b check /etc/security/access.conf
add +:ALL:cron crond

reference:
http://mochabomb.com/blog/cron-errors-crons-not-running-etc-centos-52/
http://www.flagword.net/2009/09/failed-to-open-pam-security-session/
http://www.linuxquestions.org/questions/linux-enterprise-47/cron-jobs-not-running-498478/

make index unusable for fast insert.

1. run the querry to generate SQL TO make index unusable

select 'alter index ' || owner|| '.' || index_name || ' unusabble;'
from all_indexes
where owner= 'DBMON'
and table_name=upper('pt')
;

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'UNUSABBLE;'
--------------------------------------------------------------------------------
alter index DBMON.IDX_PT_3 unusabble;


2. run the sql
alter index DBMON.IDX_PT_3 unusabble;


3. run your insert, update, delete statement

if your insert, update, delete statement fail.
run "alter session set skip_unusable_indexes = true;"


4. run the querry to generate SQL TO make index rebuild
select 'alter index ' || owner|| '.' || index_name || ' rebuild;'
from all_indexes
where owner= 'DBMON'
and table_name=upper('pt')
;


'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
alter index DBMON.IDX_PT_3 rebuild;


5. run the reduild sql

alter index DBMON.IDX_PT_3 rebuild;



this will make your insert, update, delete statement fast.