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.