Thursday, October 27, 2011

reclaim disk space from oracle tablespace;

select 'alter database datafile ' || a.file_id || ' resize ' || round((a.bytes - b.free_space + 1024*1024)/1024/1024)|| 'm ;'
from dba_data_files a,
(select sum(bytes) free_space , file_id from dba_free_space group by file_id) b
where a.file_id = b.file_id
;

----------
----------

set verify off
column file_name format a50 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"
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 a75 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
/


references:
http://dbaforums.org/oracle/index.php?showtopic=4444
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:3259860865329305::::P11_QUESTION_ID:153612348067

Enterprise manager 11g (database control or grid control)

Check dbconsole web
emctl status dbconsole

https://192.168.1.1:1158/em

bin/rm: Argument list too long.

find . -name "*.aud" | xargs rm

Tuesday, October 25, 2011

Why is V$SESSION.SQL_ID null for an ACTIVE session

we may get sql_in from v$active_session_history(the top_level_sql column in V$ACTIVE_SESSION_HISTORY reports the sql_id )


reference:
https://forums.oracle.com/forums/thread.jspa?threadID=2244920

massive deletion solution

massive DELETE operation deletes millions of rows from a table with indexes and constraints. This operation is database intensive and time consuming, mainly because it forces the database to generate and save to disk significant amounts (possibly gigabytes) of redo and undo data.
You can perform massive DELETEs as bulk INSERT operations: instead of removing data you no longer need, you insert data you want to keep. The key to this option is performing it efficiently with minimum logging by using direct-path INSERT. Part 1 of this article series compares direct-path INSERT and regular INSERT. It also describes the technique of performing DELETEs as INSERTs. Part 2 will discuss the packaged APIs that implement the bulk INSERT technique.

Problem
How do I complete a massive DELETE operation in my Oracle database without having to pay the heavy performance overhead?

Solution.
Perform the massive DELETE operation as a direct-path (direct-load) INSERT (INSERT with APPEND hint) into a table whose logging parameter is set to NOLOGGING. This operation will complete significantly faster than DELETE and with minimum logging, but you have to take a backup afterwards to establish a new baseline.

Why INSERT Could Be Faster Than DELETE (or UPDATE)
Direct-path INSERT is a special database operation. Like SQL*Loader, it writes data directly to the database files, bypassing the buffer cache. It does this with minimum logging, recording only data dictionary changes. The logic behind this method is that because the data files are already up-to-date when an instance failure occurs, logging isn't necessary.

The two distinct cases in which direct-path INSERT is important are when:

1.The database is in noarchivelog mode. Media recovery is not possible, and you don't need redo data for that either.
2.The database is in archivelog mode. It logs redo blocks for media recovery by default. However, if you explicitly set a table to NOLOGGING mode, the database won't log redo blocks.
Therefore, with the direct-path INSERT when the database is in noarchivelog mode or when it is in archivelog mode and the table is in NOLOGGING mode, it performs only minimum redo logging—to protect the data dictionary.


reference:
http://www.devx.com/dbzone/10MinuteSolution/22191

last_call_et in v$session

this column give the running time of active session in seconds or waiting time of inactive session.

last call et is time in seconds that the backgrounds have been work -- roughly the same..

lock blocking

view name: v$lock

Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can tell which session is being blocked by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0.

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

ROW_WAIT_OBJ# is the object_id but dbms_rowid.rowid_create expects the data_object_id
ROW_WAIT_FILE# is the absolute file_id but dbms_rowid.rowid_create expects a relative file number

So that will be wrong when object_id<>data_object_id. You can test that after truncating the table - as data_object_id will change
And it will be wrong as well when file_id<>relative_fno. You will have that case when having a big number of datafiles.


select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

Note that ID1 and ID2 in Session 2, which is requesting the TX lock (LMODE=0, REQUEST=6), point back to the rollback and transaction entries for Session 1. That's what lets us determine the blocking session for Session 2.

find object_id and info from v$session;

select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid=479 ;

generate rowid

select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=543
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;




reference
http://orafaq.com/node/854


comment
I came upon your article that is excellent.
However, there is a small mistake about dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ):

ROW_WAIT_OBJ# is the object_id but dbms_rowid.rowid_create expects the data_object_id
ROW_WAIT_FILE# is the absolute file_id but dbms_rowid.rowid_create expects a relative file number

So that will be wrong when object_id<>data_object_id. You can test that after truncating the table - as data_object_id will change
And it will be wrong as well when file_id<>relative_fno. You will have that case when having a big number of datafiles.

That means that you need to join with dba_objects to get the data_object_id and with dba_data_files to get the absolute file_id.

Oracle Secure Backup

oracle Secure Backup is an individual product for enterprise backup solution.

It is like NetBackup, which need an infrastructure setup with tape machine and library.

Oracle secure backup have good interface to RMAN and provide oracle database backup facility. it can also backup file systems.

Oracle secure backup need server and client setup and provide quick tape location to speed restore time.

oracle promise oracle secure backup has best cost/performance than other enterprise backup software system.

Friday, October 21, 2011

explain plan

explain plan statement will generate execution plan for sql statement in oracle against current environment ( statistics, optimizer settings)

explain plan statement will populate the result to plan table
public synonym plan_table
real tale sys.plan_table$

any user can generate its one plan table by run below script
@?/rdbms/admin/utlxplan.sql


resulted execution plan can be pull out by run below script
@?/rdbms/admin/utlxpls.sql

1. UTLXPLS.SQL This script displays the plan table output for serial processing.

2. UTLXPLP.SQL This script displays the plan table output including parallel execution columns.

or use below statement

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


the execution plan from explain plan statement may not be the exact running plan if environment changed when a statement is executed.

real execution plan info can be found in below dynamic views
v$sql_plan and V$SQL_PLAN_STATISTICS and V$SQL_PLAN_STATISTICS_ALL


explain plan statement examples
1. explain plan for STATEMENT
2. EXPLAIN PLAN
SET STATEMENT_ID = 'st1' FOR statement;
3. EXPLAIN PLAN
INTO my_plan_table
FOR statement ;
4. EXPLAIN PLAN
SET STATEMENT_ID = 'st1'
INTO my_plan_table
FOR statement;





###############
DBMS_XPLAN.DISPLAY procedure

This procedure accepts options for displaying the plan table output. You can specify:

*

A plan table name if you are using a table different than PLAN_TABLE
*

A statement Id if you have set a statement Id with the EXPLAIN PLAN
*

A format option that determines the level of detail: BASIC, SERIAL, and TYPICAL, ALL,

Some examples of the use of DBMS_XPLAN to display PLAN_TABLE output are:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));

Thursday, October 20, 2011

add hard drive to linux

1. find the added disk
ls /dev/hd* for ide drive
or ls /dev/sd8 for scsi drive

2. fdisk -l /dev/sdb # find out the label of the new drive

3. fdisk /dev/sdb #create partition on new drive

4. mkfs -t ext3 /dev/sdb1 # create file system

5. mkdir /newdrive # create mount point

6. mount /dev/sdb1 /newdrive # mount the new drive

then you can use it

*** to add new drive to LVM group
1. pvdisplay # list physical volume

2. pvcreate /dev/sdb1 # create physical volume on sdb1

3. vgextend volumegroupname /dev/sdb1 #add new physical volume to exist volume group

4. lvextend -L+20G /dev/volumegroupname/root #extend logical volume

5. resize2fs /dev/volumegroupname/root #resize file system

You have space in new drive to user now.


To make access of disk block devices in RHL5/OEL5,

Step 1: Make an entry in /etc/udev/rules.d/60-raw.rules or create your own file for example ., /etc/udev/rules.d/-oracle-raw.rules (eg. 63-oracle-raw.rules)


#vi /etc/udev/rules.d/63-oracle-raw.rules
ACTION=="add", KERNEL=="sdb1", RUN+="/bin/raw /dev/raw/raw1 %N"
ACTION=="add", KERNEL=="sdc1", RUN+="/bin/raw /dev/raw/raw2 %N"
ACTION=="add", KERNEL=="sdd1", RUN+="/bin/raw /dev/raw/raw3 %N"
ACTION=="add", KERNEL=="sde1", RUN+="/bin/raw /dev/raw/raw4 %N"
ACTION=="add", KERNEL=="sdf1", RUN+="/bin/raw /dev/raw/raw5 %N"
KERNEL=="raw[1-2]*", OWNER="root", GROUP="oinstall", MODE="640"
KERNEL=="raw[3-5]*", OWNER="oracle", GROUP="oinstall", MODE="644"


So our sdb1 will be recognized as /dev/raw/raw1, sdb2 will be as /dev/raw/raw2 and so on.

Step 2: For the mapping to have immediate effect, run below command

#/bin/raw /dev/raw/raw1 /dev/sdb1
/bin/raw /dev/raw/raw2 /dev/sdc1
/bin/raw /dev/raw/raw3 /dev/sdd1
/bin/raw /dev/raw/raw4 /dev/sde1
/bin/raw /dev/raw/raw5 /dev/sdf1

Step3: Adjust the permission settings by,
chown root:oinstall /dev/raw/raw1
chown root:oinstall /dev/raw/raw2
chown oracle:oinstall /dev/raw/raw3
chown oracle:oinstall /dev/raw/raw4
chown oracle:oinstall /dev/raw/raw5
chmod 640 /dev/raw/raw1
chmod 640 /dev/raw/raw2
chmod 640 /dev/raw/raw3
chmod 640 /dev/raw/raw4
chmod 640 /dev/raw/raw5

Open /etc/sysconfig/rawdevices and add following lines:

/dev/raw/raw1 /dev/sdb1 /dev/raw/raw2 /dev/sdc1 /dev/raw/raw3 /dev/sdd1 /dev/raw/raw4 /dev/sde1

Restart the rwdevices service to make your changes effective.

# service rawdevices restart Assigning devices: /dev/raw/raw1 --> /dev/sdb1 /dev/raw/raw1: bound to major 8, minor 17 /dev/raw/raw2 --> /dev/sdc1 /dev/raw/raw2: bound to major 8, minor 33 /dev/raw/raw3 --> /dev/sdd1 /dev/raw/raw3: bound to major 8, minor 49 /dev/raw/raw4 --> /dev/sde1 /dev/raw/raw4: bound to major 8, minor 65 done


reference
http://www.turnkeylinux.org/blog/extending-lvm
http://arjudba.blogspot.com/2008/08/on-rhl5-rawdevices-unrecognized-service.html
http://oracleflash.com/11/How-to-Configure-ASM-disks-using-ASMLib-or-Raw-Disks.html

Thursday, October 6, 2011

virtual console switch

alt-crl-fx

usually f7 is the gui console

yum and rpm

yum list package-name
yum install package-name or yum update package-name/rpm
yum erase package-name
yum search package-name # find package from repository
yum provides command-name # find package according to commmand

rpm -q package-name
rpm -i package-name
rpm -e package-name

yum will not install a package that public key is not imported, but rpm will install it with warning.

rlwrap

package: rlwrap-0.37-1.el5.i386.rpm

Now I could call sqlplus this way:
$ rlwrap sqlplus user/password@sid.

Finally I create an alias
alias sqlp =”rlwrap sqlplus”



alias sqlplus=”rlwrap sqlplus”

this I would avoid. rlwrap does not support non-interactive mode (echo xxx | sqlplus), is not an oracle support tool, and crashes occasionnaly.

Personnaly I do
alias q=”rlwrap sqlplus”
so that I can always use sqlplus to run important script, and keep rlwrap for not-productive stuff.


the first variant is ok:
$ alias sqlplus=”rlwrap sqlplus”

If you want history, then you run alias:
$ sqlplus

To run command instead of the alias you add backslash:
$ \sqlplus