VERITAS CLUSTER COMMANDS

To check List

[root@iptvdb1]hagrp -list

ClusterService          iptvdb1

ClusterService          iptvdb2

oracle-rg               iptvdb1

oracle-rg               iptvdb2

To check status

[root@iptvdb1] hastatus -sum

-- SYSTEM STATE

-- System               State                Frozen

A  iptvdb1              RUNNING              0

A  iptvdb2              RUNNING              0

-- GROUP STATE

-- Group           System               Probed     AutoDisabled    State

B  ClusterService  iptvdb1              Y          N               ONLINE

B  ClusterService  iptvdb2              Y          N               OFFLINE

B  oracle-rg       iptvdb1                 Y          N               ONLINE

B  oracle-rg       iptvdb2                 Y          N               OFFLINE

To Switch system 

hagrp -switch  <service group name> -to <system name>

 1> hagrp -switch  oracle-rg -to iptvdb2    

after complete switch then issue below command           

2> hagrp -switch  ClusterService   -to  iptvdb2              

** First switch Oracle Resource Group 

**Second switch Cluster resource Group

To check all information about Service Group

[root@iptvdb1] hagrp -display oracle-rg

#Group       Attribute             System     Value

oracle-rg    Administrators        global

oracle-rg    Authority             global     0

oracle-rg    AutoFailOver          global     1

oracle-rg    AutoRestart           global     1

oracle-rg    AutoStart             global     1

oracle-rg    AutoStartIfPartial    global     1

oracle-rg    AutoStartList         global     iptvdb1 iptvdb2

oracle-rg    AutoStartPolicy       global     Order

oracle-rg    ClusterFailOverPolicy global     Manual

oracle-rg    ClusterList           global

oracle-rg    Evacuate              global     1

oracle-rg    ExtMonApp             global

oracle-rg    ExtMonArgs            global

oracle-rg    FailOverPolicy        global     Priority

oracle-rg    FaultPropagation      global     1

oracle-rg    Frozen                global     0

oracle-rg    GroupOwner            global

oracle-rg    IntentOnline          global     1

oracle-rg    Load                  global     0

oracle-rg    ManageFaults          global     ALL

oracle-rg    ManualOps             global     1

oracle-rg    NumRetries            global     0

oracle-rg    OnlineRetryInterval   global     0

oracle-rg    OnlineRetryLimit      global     0

oracle-rg    Operators             global

oracle-rg    Parallel              global     0

oracle-rg    PreOffline            global     0

oracle-rg    PreOnline             global     0

oracle-rg    PreonlineTimeout      global     300

oracle-rg    Prerequisites         global

oracle-rg    PrintTree             global     1

oracle-rg    Priority              global     0

oracle-rg    Restart               global     0

oracle-rg    SourceFile            global     ./main.cf

oracle-rg    SystemList            global     iptvdb1 0 iptvdb2 1

oracle-rg    SystemZones           global

oracle-rg    TFrozen               global     0

oracle-rg    Tag                   global     Oracle

oracle-rg    TriggerEvent          global     1

oracle-rg    TriggerResStateChange global     0

oracle-rg    TypeDependencies      global

oracle-rg    UserIntGlobal         global     0

oracle-rg    UserStrGlobal         global

oracle-rg    AutoDisabled          iptvdb1    0

oracle-rg    AutoDisabled          iptvdb2    0

oracle-rg    Enabled               iptvdb1    1

oracle-rg    Enabled               iptvdb2    1

oracle-rg    PreOfflining          iptvdb1    0

oracle-rg    PreOfflining          iptvdb2    0

oracle-rg    PreOnlining           iptvdb1    0

oracle-rg    PreOnlining           iptvdb2    0

oracle-rg    Probed                iptvdb1    1

oracle-rg    Probed                iptvdb2    1

oracle-rg    ProbesPending         iptvdb1    0

oracle-rg    ProbesPending         iptvdb2    0

oracle-rg    State                 iptvdb1    |ONLINE|

oracle-rg    State                 iptvdb2    |OFFLINE|

oracle-rg    UserIntLocal          iptvdb1    0

oracle-rg    UserIntLocal          iptvdb2    0

oracle-rg    UserStrLocal          iptvdb1

oracle-rg    UserStrLocal          iptvdb2

oracle-rg    VCSi3Info             iptvdb1

oracle-rg    VCSi3Info             iptvdb2

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

Cluster Status

hastatus -summary – Outputs the status of cluster
hasys -display – Displays the cluster operation status

Start or Stop services
hastart [-force|-stale] – ‘force’ is used to load local configuration
hasys -force 'system' – start the cluster using config file from the mentioned “system”
hastop -local [-force|-evacuate] – ‘local’ option will stop the service only on the system you type the command
hastop -sys 'system' [-force|-evacuate] – ‘sys’ stops had on the system you specify
hastop -all [-force] – ‘all’ stops had on all systems in the cluster

Change VCS Configuration online
haconf -makerw – makes VCS configuration in read/write mode
haconf -dump -makero – Dumps the configuration changes

Agent Operations
haagent -start agent_name -sys system – Starts an agent
haagent -stop agent_name -sys system – Stops an agent

Cluster Operations
haclus -display – Displays cluster information and status
haclus -enable LinkMonitoring – Enables heartbeat link monitoring in the GUI
haclus -disable LinkMonitoring – Disables heartbeat link monitoring in the GUI

Add and Delete Users
hauser -add user_name – Adds a user with read/write access
hauser -add VCSGuest – Adds a user with read-only access
hauser -modify user_name – Modifies a users password
hauser -delete user_name – Deletes a user
hauser -display [user_name] – Displays all users if username is not specified

System Operations
hasys -list – List systems in the cluster
hasys -display – Get detailed information about each system
hasys -add system – Add a system to cluster
hasys -delete system – Delete a system from cluster

Resource Types
hatype -list – List resource types
hatype -display [type_name] – Get detailed information about a resource type
hatype -resources type_name – List all resources of a particular type
hatype -add resource_type – Add a resource type
hatype -modify .... – Set the value of static attributes
hatype -delete resource_type – Delete a resource type

Resource Operations
hares -list – List all resources
hares -dep [resource] – List a resource’s dependencies
hares -display [resource] – Get detailed information about a resource
hares -add resource_type service_group – Add a resource
hares -modify resource attribute_name value – Modify the attributes of the new resource
hares -delete resource – Delete a resource type
hares -online resource -sys systemname – Online a resource, type
hares -offline resource -sys systemname – Offline a resource, type
hares -probe resource -sys system – Cause a resource’s agent to immediately monitor the resource on a particular system
hares -clear resource [-sys system] – Clear a faulted resource
hares -local resource attribute_name value – Make a resource’s attribute value local
hares -global resource attribute_name value – Make a resource’s attribute value global
hares -link parent_res child_res – Specify a dependency between two resources
hares -unlink parent_res child_res – Remove the dependency relationship between two resources

Service Group Operations
hagrp -list – List all service groups
hagrp -resources [service_group] – List a service group’s resources
hagrp -dep [service_group] – List a service group’s dependencies
hagrp -display [service_group] – Get detailed information about a service group
hagrp -online groupname -sys systemname – Start a service group and bring it resources
hagrp -offline groupname -sys systemname – Stop a service group and take it resources offline
hagrp -switch groupname -to systemname – Switch a service group from one system to another
hagrp -freeze -sys -persistent – Gets into Maintenance Mode. Freeze a service group. This will disable online and offline operations
hagrp -unfreeze -sys -persistent] – Take the servicegroup out of maintenance mode
hagrp -enable service_group [-sys system] – Enable a service group
hagrp -disable service_group [-sys system] – Disable a service group
hagrp -enableresources service_group – Enable all the resources in a service group
hagrp -disableresources service_group – Disable all the resources in a service group
hagrp -link parent_group child_group relationship – Specify the dependency relationship between two service groups
hagrp -unlink parent_group child_group – Remove the dependency between two service groups

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

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;