Thursday, October 31, 2013

TO MOUNT USB STORAGE IN SOLARIS 10 OS

plug usb storage

iostat -En (its give information about usb storage)


/etc/init.d/volmgt stop

rmformat (give information about path)

mount -F pcfs /dev/dsk/c0t0d0s2:c /mnt

check at /mnt  your usb storage mount


df -h

ORACLE DATABASE INSTALLATION ON SOLARIS 10

//********To check RAM ********///

prtconf | grep "Memory size"


//*******To Check SWAP Memory Size *******///

 swap -s



///*****************To check /tmp size********///

   df -h /tmp  (minimum 400 mb required)

//************Determine the System Architecture********////

   32-bit i386 kernel modules

///******To check & Install package  required for oracle installation*******////////


pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot \
 SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt


//********** To Add Package***/////////

pkgadd -d /cdrom/sol_10_1106_x86/Solaris_10/Product SUNWi1cs SUNWi15cs



//********Change Kernal Parameter**********//////////

change kernel parameters by editing /etc/system file as follows:

set shmsys:shminfo_shmmax=4294967295

set shmsys:shminfo_shmmin=1

set shmsys:shminfo_shmmni=100

set shmsys:shminfo_shmseg=10

set semsys:seminfo_semmns=1024

set semsys:seminfo_semmsl=256

set semsys:seminfo_semmni=100

set semsys:seminfo_semvmx=32767

 set noexec_user_stack=1


//*****Create group for Oracle Installation******/////

groupadd oinstall
groupadd dba


//******** Create project group for system resource allocate*******//////

projadd group.dba
projects -l group.dba

group.dba
 projid : 101
 comment: ""
 users  : (none)
 groups : (none)
 attribs:

projmod -sK "project.max-shm-memory=(privileged,2G,deny)" group.dba
projmod -sK "project.max-sem-ids=(privileged,100,deny)" group.dba
projmod -sK "project.max-shm-ids=(privileged,100,deny)" group.dba
projmod -sK "project.max-sem-nsems=(privileged,256,deny)" group.dba


//*******Create user in respective home directory*****///////

useradd -g oinstall -G dba -m -d /opt/oracle -s /bin/ksh -K project=group.dba oracle

passwd -r files oracle

//*****Making Directory as per requirment*****/////////

( option to create directory login with oracle user )

mkdir -p /opt/oracle/product/9.2.0/db_1
chown -Rh oracle:oinstall /opt/oracle
chmod -R 755 /opt/oracle


///*********
               To Manage .profile
         Login to user “oracle” and edit “.profile” to set ORACLE_BASE,ORACLE_HOME,etc.

****////

if root user then

gedit /opt/oracle/.profile

gedit .profile
or
vi .profile


TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.2.0/db_1; export ORACLE_HOME
ORACLE_SID=NMS; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH




logout again login

you will see to all parameter

env


///*********** copy or unzip  oracle database software***//////////

if another location

cp -r /mnt/oracle /opt/oracle/


unzip -o 10201_database_solx86_64.zip


./runInstaller

  

Thursday, October 17, 2013

BASIC UNIX FOR DBA'S

Unix is operating system is come in 1969 at AT&T bell labs by Ken Thompson and Dennis Ritchie.
KERNAL- It is heart of OS. is intract with H/W. also most of work like task management ,memory management  etc.
SHELL- It is utility to process request .is tell the what to do to OS.C shell, Bourne Shell,Krone Shell.

$ls -l
total 1962188

drwxrwxr-x  2 amrood amrood      4096 Dec 25 09:59 uml
-rw-rw-r--  1 amrood amrood      5341 Dec 25 08:38 uml.jpg
drwxr-xr-x  2 amrood amrood      4096 Feb 15  2006 univ
drwxr-xr-x  2 root   root        4096 Dec  9  2007 urlspedia
-rw-r--r--  1 root   root      276480 Dec  9  2007 urlspedia.tar
drwxr-xr-x  8 root   root        4096 Nov 25  2007 usr
drwxr-xr-x  2    200    300      4096 Nov 25  2007 webthumb-1.01
-rwxr-xr-x  1 root   root        3192 Nov 25  2007 webthumb.php
-rw-rw-r--  1 amrood amrood     20480 Nov 25  2007 webthumb.tar
-rw-rw-r--  1 amrood amrood      5654 Aug  9  2007 yourfile.mid
-rw-rw-r--  1 amrood amrood    166255 Aug  9  2007 yourfile.swf
drwxr-xr-x 11 amrood amrood      4096 May 29  2007 zlib-1.2.3
$
Here is the information about all the listed columns:
  1. First Column: represents file type and permission given on the file. Below is the description of all type of files.
  2. Second Column: represents the number of memory blocks taken by the file or directory.
  3. Third Column: represents owner of the file. This is the Unix user who created this file.
  4. Fourth Column: represents group of the owner. Every Unix user would have an associated group.
  5. Fifth Column: represents file size in bytes.
  6. Sixth Column: represents date and time when this file was created or modified last time.
  7. Seventh Column: represents file or directory name.
In the ls -l listing example, every file line began with a d, -, or l. These characters indicate the type of file that's listed.
PrefixDescription
-Regular file, such as an ASCII text file, binary executable, or hard link.
bBlock special file. Block input/output device file such as a physical hard drive.
cCharacter special file. Raw input/output device file such as a physical hard drive
dDirectory file that contains a listing of other files and directories.
lSymbolic link file. Links on any regular file.
pNamed pipe. A mechanism for interprocess communications
sSocket used for interprocess communication.
###################################################################3

COPY FILES 
                         cp source  destination

     exp: cp /oracle/anurag/test.txt    /oracle/

##############################################################

Move file to one location to another location
(cut & paste)

mv /oracle/test.txt    /iptv/rest/

#################################################################
Delete 

rm test.txt

rm test1.txt test2.txt test3.txt
rm -rf anurag.txt  (its remove the file without asking )

##################################################################

mkdir /test

mkdir -p  /test/test1/test2/test3/
(is create  parent and sub folder)

rmdir /anurag
(but directory should be empty)
#######################################################3

File Permission


-rwxr-xr--
 r :   read-4
w :  write (modify)-2
e  :  execute-1
-  :  no permission-0

rwx wrx xw-

first 3 -owner
second 3 - group
third 3 - others

######################################
Directory permission

chmod

To set/modify a file's permissions you need to use the chmod program. Of course, only the owner of a file may use chmod to alter a file's permissions. chmod has the following syntax:chmod [options] mode file(s)
The 'mode' part specifies the new permissions for the file(s) that follow as arguments. A mode specifies which user's permissions should be changed, and afterwards which access types should be changed. Let's say for example:chmod a-x socktest.plThis means that the execute bit should be cleared (-) for all users. (owner, group and the rest of the world) The permissions start with a letter specifying what users should be affected by the change, this might be any of the following:
  • u the owner user
  • g the owner group
  • o others (neither u, nor g)
  • a all users
This is followed by a change instruction which consists of a +(set bit) or -(clear bit) and the letter corresponding to the bit that should be changed.


chmod 777 /oracle/test.txt

-rwxwrxxwr



#########################################################################

Compress folder using tar command 

tar -cvf   /db/test.tar /test

if folder size greater then 8GB the use 

tar -cvEf /db/test.tar  /test

Wednesday, September 25, 2013

DATABASE MONITORING

CHECK  BUFFER CACHE HIT RATIO


SELECT ROUND((1-(phy.VALUE / (cur.VALUE + con.VALUE)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
/
 

CHECK DATAFILE SIZE


SELECT dp.tablespace_name
, df.file_name
, dp.block_size
, round(sum(df.maxbytes) /(1024*1024*1024),0) "Max_Size_GB"
, round(sum(df.bytes) /(1024*1024*1024),0) "Used_Size_GB"
FROM dba_tablespaces dp, dba_data_files df
WHERE dp.tablespace_name=df.tablespace_name
GROUP BY dp.tablespace_name, dp.block_size , df.file_name,df.maxbytes
ORDER BY dp.tablespace_name, df.file_name;


######################################################################

CHECK DATABASE SIZE 


SQL> select sum(bytes)/(1024*1024*1024) DB_SIZE_IN_GB from dba_data_files;

DB_SIZE_IN_GB
-------------
   5.93847656

////////////////////////////////////////

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20



select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p;


###################################################################

TOP  QUERIES IN LAST 5 MIN



SELECT * FROM

(select parsing_schema_name as users, sql_id, sql_text
,executions, disk_reads,buffer_gets
,DECODE (executions,0,0,round(((cpu_time/1000000)/executions),2)) "CPU Time"
,DECODE (executions,0,0,round(((elapsed_time/1000000)/executions),2)) "Elapsed Time"
FROM v$sqlarea
WHERE LAST_ACTIVE_TIME BETWEEN (sysdate - 5/1440) AND sysdate
AND parsing_schema_name NOT IN ('SYS', 'SYSTEM')
ORDER BY "CPU Time" DESC) x
WHERE rownum <=5; 

###############################################################3

TOP WAITING SESSION


SELECT sess.sid, sess.serial#, sess.username, sess.machine, SUM(ash.wait_time + ash.time_waited) wait_time
FROM v$active_session_history ash, v$session sess
WHERE ash.session_id = sess.sid
AND ash.sample_time BETWEEN (sysdate - 15/1440) AND sysdate
GROUP BY sess.sid, sess.serial#, sess.username, sess.machine
ORDER BY 4 DESC;


#####################################################################


KILL SESSION 


alter system kill session 'sid,serial#' ;

USING V$SESSION TO GET SID,SERIAL#

############################################################

TO CHECK HOW MANY TABLE IN USER 'HR'


select count(*) from dba_objects do where do.owner = 'HR'
and lower(object_type)=lower('Table');

####################################################################3