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

  

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.

Prefix	Description
-	Regular file, such as an ASCII text file, binary executable, or hard link.
b	Block special file. Block input/output device file such as a physical hard drive.
c	Character special file. Raw input/output device file such as a physical hard drive
d	Directory file that contains a listing of other files and directories.
l	Symbolic link file. Links on any regular file.
p	Named pipe. A mechanism for interprocess communications
s	Socket 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

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

IMPORTANT SQL QUERY

TO DELETE DUPLICATE RECORD FROM TABLE
IF TABLE TEST CONTAIN DUPLICATE RECORD

DELETE FROM TEST WHERE ROWID NOT IN 

(SELECT MIN(ROWID) FROM TEST GROUP BY ID)

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

CREATE DUPLICATE TABLE WITH DATA USING ANOTHER TABLE

CREATE TABLE TEST AS SELECT * FROM TEST1;

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

CREATE DUPLICATE TABLE SAME SCHEMA AS USING ANOTHER 

TABLE

CREATE TABLE TEST AS SELECT * FROM TEST1 WHERE 1=2;