MOVING/RENAMING DATAFILES WHILE DATABASE IS ONLINE

APPLY ONLY NON-SYSTEM TABLESPACE OR TEMP TABLESPACE

SQL> alter tablespace test offline;

Tablespace altered.

SQL>

first u copy datafile to another location 

SQL> ! cp /u02/app/oracle/oradata/test/test.dbf /u02/app/oracle/oradata/orcl/test.dbf

SQL>alter database rename file '/u02/app/oracle/oradata/test/test.dbf' to '/u02/app/oracle/oradata/orcl/test.dbf';


SQL> alter tablespace test online;

Tablespace altered.

if working properly then  delete old file from  
/u02/app/oracle/oradata/test/test.dbf location........

RESIZE TEST TABLESPACE DATAFILES

SQL> alter database datafile '/test/test01.dbf' autoextend off;

SQL> column file_name format a20

SQL> column tablespace_name format a10

SQL> select

  2              tablespace_name,

  3              file_id,

  4              file_name,

  5              dfsizeMB,

  6              hwmMB,

  7              dffreeMB,

  8              trunc((dffreeMB/dfsizeMB)*100,2) "% Free",

  9              trunc(dfsizeMB-hwmMB,2) "Resizeble"

 10     from

   (

 11   12        select

 13             df.tablespace_name tablespace_name,

 14             df.file_id file_id,

 15             df.file_name file_name,

 16             df.bytes/1024/1024 dfsizeMB,

 17             trunc((ex.hwm*(ts.block_size))/1024/1024,2) hwmMB,

 18             dffreeMB

 19        from

 20             dba_data_files df,

 21             dba_tablespaces ts,

 22        (

 23             select file_id, sum(bytes/1024/1024) dffreeMB

 24             from dba_free_space

 25             group by file_id

 26        ) free,

 27        (

 28             select file_id, max(block_id+blocks) hwm

 29             from dba_extents

           group by file_id

 30   31        ) ex

 32        where df.file_id = ex.file_id

 33        and df.tablespace_name = ts.tablespace_name

 34        and df.file_id = free.file_id (+)

 35        order by df.tablespace_name, df.file_id

 36      )

 37  /

TABLESPACE    FILE_ID FILE_NAME              DFSIZEMB      HWMMB   DFFREEMB     % Free  Resizeble

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

SYSAUX              3 /opt/oracle/oradata/        240      237.5        2.5       1.04        2.5

                      NMS/sysaux01.dbf

SYSTEM              1 /opt/oracle/oradata/        490     481.07        9.5       1.93       8.93

                      NMS/system01.dbf

TEST                6 /test/test01.dbf         3.9375        .69     3.3125      84.12       3.24

UNDOTBS1            2 /opt/oracle/oradata/         30      26.07    18.6875      62.29       3.93

                      NMS/undotbs01.dbf

USERS               4 /opt/oracle/oradata/          5        .44     4.5625      91.25       4.56

                      test/users01.dbf

SQL> alter database datafile '/test/test01.dbf' resize 3m;

Database altered.

SQL>

SQL> alter database datafile '/test/test01.dbf' autoextend on;

Database altered.

SQL>

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '/opt/oracle/db02/nms_index.dbf'

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size             104860528 bytes
Database Buffers           58720256 bytes
Redo Buffers                2912256 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/opt/oracle/db02/nms_index.dbf'


SQL>
SQL>
SQL> shut abort
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size             104860528 bytes
Database Buffers           58720256 bytes
Redo Buffers                2912256 bytes
Database mounted.
SQL> alter database datafile '/opt/oracle/db02/nms_index.dbf' offline drop;

Database altered.

SQL>
SQL>
SQL> shut abort
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size             104860528 bytes
Database Buffers           58720256 bytes
Redo Buffers                2912256 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>

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

  

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