Tips For DBA Part 7

How To get TK Prof

1. the execution plan for each query (see step 3.2 from NOTE.235530.1 Methods for Obtaining a Formatted Explain Plan)
2. the 10046 trace file for each execution:
SQL> alter session set max_dump_file_size=unlimited;
sql> ALTER SESSION SET SQL_TRACE = true;
SQL> alter session set events='10046 trace name context forever, level 12';
sql> select
  u_dump.value   || '/'     ||
  lower(db_name.value)  || '_ora_' ||
  v$process.spid ||
  nvl2(v$process.traceid,  '_' || v$process.traceid, null )
  || '.trc'  "Trace File" 
from
             v$parameter u_dump
  cross join v$parameter db_name
  cross join v$process
        join v$session
          on v$process.addr = v$session.paddr
where
 u_dump.name   = 'user_dump_dest' and
 db_name.name  = 'instance_name'        and
v$session.audsid=sys_context('userenv','sessionid');
SQL> -- run the query
SQL> select * from dual;
SQL> exit

==================

Create Domain INdex:

1) Run the below query where index was sucssfully created and in VALID mode:
set long 2000000000
set head off
set pagesize 10000
select ctx_report.create_index_script('<INDEX_NAME>') from dual;

2) Then Drop the index for with you received error:
Drop the failed index
Drop index <INDEX_NAME> force;
3) and recreate with above generated script.


=============================

Table Defragmentation Activity: ========================================================>
One Way :--->
alter table table_name enable row movement;
alter table table_name shrink space cascade ;
alter table table_name disable row movement;
Reason --> segment managment is not auto that is why we are using the below lines
Second Way :--->
alter table table_name move tablespace bv_data;
analyze table table_name estimate statistics sample 30 percent;
alter index index_name rebuild online;
--> Check if the indexs are valid
select * from all_indexes where status <> 'VALID' and owner = 'VIVEK';
--> Check the spaces but this query is customized check before use :
select table_name, act_size_mb, est_size_mb, act_size_mb- est_size_mb from(
select 'Table' type, table_name,  (st.bytes /1024/1024 )act_size_mb,
round((avg_row_len * num_rows * (1 + PCT_FREE/100) * 1.15)/1024/1024,2) est_size_mb,
st.blocks, st.extents
from all_tables dt, dba_segments st
where dt.table_name = st.segment_name
and st.owner = dt.owner
and st.owner = 'VIVEK'
--and dt.table_name = 'TABLE2'
)
where act_size_mb- est_size_mb  > 20
order by 2 desc;
2 .. select table_name, tablespace_name, num_rows, blocks, empty_blocks, avg_space,
chain_cnt, avg_row_len, last_analyzed, degree, buffer_pool 
from all_tables;
-----> Find the index for the tables :
select index_name,status from all_indexes where table_name = 'TABLE3';

=======================================================================================================>
Other's in table Defragmentations L
select table_name, tablespace_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt,
avg_row_len, last_analyzed, degree, buffer_pool 
from all_tables;
select * from all_indexes where table_name = 'TABLE2';
select * from all_indexes where status <> 'VALID' and owner = 'VIVEK';
select segment_name, segment_type, (bytes)/1024/1024 from dba_segments where
owner = 'VIVEK' order by 3 desc;
select segment_name, segment_type, (bytes)/1024/1024 from dba_segments where
owner = 'VIVEK' order by 3 desc;
select 'Table' type, table_name,  (st.bytes /1024/1024 )act_size_mb,
round((avg_row_len * num_rows * (1 + PCT_FREE/100) * 1.15)/1024/1024,2) est_size_mb,
st.blocks, st.extents
from all_tables dt, dba_segments st
where dt.table_name = st.segment_name
and st.owner = dt.owner
and st.owner = 'VIVEK'
--and dt.table_name = 'TABLE2'
order by 3 desc;
alter table VIVEK.TABLE2 disable row movement;
alter table VIVEK.TABLE2 move tablespace bv_data;
analyze table VIVEK.TABLE2 estimate statistics sample 30 percent;
alter index VIVEK.INDEX2 rebuild online;
====================================================================================================>

Tips for DBA Part 6

Print The Ourput in HTML


sqlplus -S -M "HTML ON TABLE 'BORDER="2"'" username/passwd@server_name  @c:\a.txt > c:\a.html
set longchunksize 13000
set long 13000
set linesize 15000
select long_desc from ag_product_content where rownum <10 and long_desc is not null and rownum < 10;
exit



Table Count Script:

set serveroutput on
declare
v_str varchar2(4000):=null;
v_cnt number;
begin
for i in (select log_table, master from user_mview_logs)
loop
v_str:='Select count(1) from '||i.log_table;
execute immediate v_str into v_cnt;
if v_cnt>0 then
dbms_output.put_line('Master Table name: '||i.master||chr(9)||' Log Table_Name: '||i.log_table||chr(9)||' Count: '||v_cnt);
end if;
End loop;
End;



Table Space Size Details

SELECT  t. tablespace_name,t.total_space_in_GB , f.free_space_in_GB--, TO_CHAR((f.free_space_in_MB*100/t.total_space_in_MB),'99990.000') "Free%"
FROM (SELECT tablespace_name, trunc(SUM(bytes)/1024/1024/1024,2) Total_space_in_GB FROM DBA_DATA_FILES GROUP BY tablespace_name) t, 
(SELECT tablespace_name, trunc(SUM(bytes)/1024/1024/1024,2) Free_space_in_GB FROM DBA_FREE_SPACE GROUP BY tablespace_name) f
WHERE  t.tablespace_name= f.tablespace_name
order by 1;
SELECT aus.tablespace_name, df.file_name, df.TOTAL_SPACE_MB, NVL(dfs.FREE_SPACE_MB,0) Free_space_MB,
NVL(trunc( (free_space_mb*100/total_space_mb),2) ,0)"FREE%", TRUNC(df.TOTAL_SPACE_MB-USED_SPACE_MB ) Reclaim_space_MB, AUTOEXTENSIBLE, MAXBYTES_MB,INCREMENT_BY_MB
FROM (SELECT  tablespace_name, file_id,SUM(bytes)/1024/1024 AS "FREE_SPACE_MB" FROM DBA_FREE_SPACE GROUP BY tablespace_name, file_id) dfs, (SELECT file_id, file_name, SUM(bytes)/1024/1024 AS "TOTAL_SPACE_MB" FROM DBA_DATA_FILES GROUP BY file_id, file_name) df, (SELECT DISTINCT f.TABLESPACE_NAME, file_name,((ROUND(f.bytes / 1024 / 1024) - NVL(ROUND(s.bytes / 1024 / 1024),0))) "USED_SPACE_MB", f.AUTOEXTENSIBLE, TRUNC(f.MAXBYTES/1024/1024,2) MAXBYTES_MB, TRUNC(f.INCREMENT_BY/128,2) INCREMENT_BY_MB FROM DBA_DATA_FILES f, DBA_FREE_SPACE s WHERE f.file_id = s.file_id (+)
AND NVL(s.block_id,0) IN (NVL((SELECT MAX(block_id) FROM DBA_FREE_SPACE WHERE file_id = s.file_id),0))) aus
WHERE dfs.FILE_ID (+)= df.FILE_ID
AND aus.file_name (+)= df.file_name
ORDER BY 1,2;

select tablespace_name, count(1) No_of_DataFiles, sum(TOTAL_SPACE_MB) TOTAL_SPACE_MB, sum(FREE_SPACE_MB) FREE_SPACE_MB, sum(Reclaim_space_MB) Reclaim_space_MB from
(SELECT aus.tablespace_name, df.file_name, df.TOTAL_SPACE_MB, NVL(dfs.FREE_SPACE_MB,0) Free_space_MB,
NVL(trunc( (free_space_mb*100/total_space_mb),2) ,0)"FREE%", TRUNC(df.TOTAL_SPACE_MB-USED_SPACE_MB ) Reclaim_space_MB, AUTOEXTENSIBLE, MAXBYTES_MB,INCREMENT_BY_MB
FROM (SELECT  tablespace_name, file_id,SUM(bytes)/1024/1024 AS "FREE_SPACE_MB" FROM DBA_FREE_SPACE GROUP BY tablespace_name, file_id) dfs, (SELECT file_id, file_name, SUM(bytes)/1024/1024 AS "TOTAL_SPACE_MB" FROM DBA_DATA_FILES GROUP BY file_id, file_name) df, (SELECT DISTINCT f.TABLESPACE_NAME, file_name,((ROUND(f.bytes / 1024 / 1024) - NVL(ROUND(s.bytes / 1024 / 1024),0))) "USED_SPACE_MB", f.AUTOEXTENSIBLE, TRUNC(f.MAXBYTES/1024/1024,2) MAXBYTES_MB, TRUNC(f.INCREMENT_BY/128,2) INCREMENT_BY_MB FROM DBA_DATA_FILES f, DBA_FREE_SPACE s WHERE f.file_id = s.file_id (+)
AND NVL(s.block_id,0) IN (NVL((SELECT MAX(block_id) FROM DBA_FREE_SPACE WHERE file_id = s.file_id),0))) aus
WHERE dfs.FILE_ID (+)= df.FILE_ID
AND aus.file_name (+)= df.file_name)
group by tablespace_name
ORDER BY 1;
select owner, sum(bytes)/1024 from dba_segments
where owner in ('USER','READ','DBA')
group by owner
order by 1
select owner, trunc(sum(bytes)/1024/1024/1024,2) size_in_gb from dba_segments group by owner order by 2 desc;
select * from dba_objects where owner in ('TOAD');
select * from dba_recyclebin;
select trunc(sum(bytes)/1024/1024,2) size_in_mb from dba_segments where (owner, segment_name)  in (select owner, object_name from dba_recyclebin);

Tips for DBA PArt 5

To find which users password is expiring till 30 days from now
-------------------------------------------------------------------------------------
Select upper (sys_context( 'USERENV', 'SERVER_HOST' ) ||','||sys_context( 'USERENV', 'DB_NAME' )||','|| username ||','||ACCOUNT_STATUS||','||EXPIRY_DATE||','|| LOCK_DATE) from dba_users where EXPIRY_DATE between sysdate and sysdate +  30;

Object Dependecies =============


--Dependency used by:
SELECT   owner,
         object_type,
         object_name,
         object_id,
         status
  FROM   sys.DBA_OBJECTS
 WHERE   object_id IN
               (    SELECT   object_id
                      FROM   public_dependency
                CONNECT BY   PRIOR object_id = referenced_object_id
                START WITH   referenced_object_id =
                                (SELECT   object_id
                                   FROM   sys.DBA_OBJECTS
                                  WHERE       owner = :Owner
                                          AND object_name = :name
                                          AND object_type = :TYPE));
---Dependency usages:
SELECT   a.object_type,
         a.object_name,
         b.owner,
         b.object_type,
         b.object_name,
         b.object_id,
         b.status
  FROM   sys.DBA_OBJECTS a,
         sys.DBA_OBJECTS b,
         (    SELECT   object_id, referenced_object_id
                FROM   public_dependency
          START WITH   object_id =
                          (SELECT   object_id
                             FROM   sys.DBA_OBJECTS
                            WHERE       owner = :owner
                                    AND object_name = :name
                                    AND object_type = :TYPE)
          CONNECT BY   PRIOR referenced_object_id = object_id) c
 WHERE       a.object_id = c.object_id
         AND b.object_id = c.referenced_object_id
         AND a.owner NOT IN ('SYS', 'SYSTEM')
         AND b.owner NOT IN ('SYS', 'SYSTEM')
         AND a.object_name <> 'DUAL'
         AND b.object_name <> 'DUAL'



select * from dba_registry
select * from v$session
select * from
select * from v$session where program like 'JDBC T%' and username = 'ADM' --and machine = 'abc24'
and status = 'ACTIVE'
select s.last_call_et, s.sid, S.BLOCKING_SESSION, S.BLOCKING_SESSION_STATUS , sw.event, sa.sql_id, sa.sql_fulltext from v$session s, v$sqlarea sa, v$session_wait sw
where program like 'JDBC T%'
and username = 'ADM' --and machine = 'abc24'
and status = 'ACTIVE'
--and sa.sql_id = '2xdxk0bdfwz5c'--'568fas464rsmt'
and s.sql_id = sa.sql_id
and s.sid = sw.sid
order by 1 desc

select * from v$sql_plan where sql_id in( '568fas464rsmt','gk0h0q5gb1b2z')
select * from v$sql where upper(sql_fulltext) like '%MULTILANG%'
select S.BLOCKING_SESSION, S.BLOCKING_SESSION_STATUS, count(1) from v$session s
group by S.BLOCKING_SESSION, S.BLOCKING_SESSION_STATUS
select s.sid, S.BLOCKING_SESSION, S.BLOCKING_SESSION_STATUS , sw.event, sa.sql_id, sa.sql_fulltext from v$session s, v$sqlarea sa, v$session_wait sw
where program like 'toad%'
and username = 'ADM' --and machine = 'abc24'
and status = 'ACTIVE'
--and sa.sql_id = '568fas464rsmt'
and s.sql_id = sa.sql_id
and s.sid = sw.sid
SELECT * FROM TABLE(AG_GET_PROD_LIB_DATA('','US','eng','All','1000001295:epsg:pro','34401A','PRODUCT'));

select event, count(1) from v$session_wait where sid in (select sid from v$session where program like 'JDBC T%' and username = 'ADM' --and machine = '192.23.43.12'
and status = 'ACTIVE')
group by event
select * from v$locked_object
select ddl.* from dba_ddl_locks ddl, v$session s
where s.sid = ddl.session_id
and program like 'JDBC T%'
and username = 'ADM'
--and machine = 'acomd24'
order by ddl.session_id;
select name, ddl.type, count(1) from dba_ddl_locks ddl, v$session s
where s.sid = ddl.session_id
and program like 'JDBC T%'
and username = 'ADM1T'
and machine = 'acomd24'
group by name, ddl.type
order by 3 desc;
SELECT sid, event, p1raw
  FROM sys.v_$session_wait
 WHERE event = 'library cache pin'
   AND state = 'WAITING';

select sql_id, count(*)
from dba_hist_active_sess_history
where event_id = (select event_id from v$event_name where name = 'control file sequential read')
and sample_time >= trunc(sysdate)
group by sql_id
order by 2 desc ;
select sql_fulltext from v$sqlarea where sql_id in
(select sql_id
from dba_hist_active_sess_history
where event_id = (select event_id from v$event_name where name = 'control file sequential read')
and sample_time >= trunc(sysdate) )
and lower(sql_fulltext) not like '%dual%';
SELECT kglnaown AS owner, kglnaobj as Object
  FROM sys.x$kglob

SELECT waiting_session, holding_session FROM dba_waiters;
select sesion.sid,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address;

select * from dba_hist_sqlstat where sql_id = 'cy07djsd15920' and parsing_schema_name = 'ADM1T'

Tips for DBA part 3

Formating Output : =====================================>
set lines 200 pages 100
col username format a16
col ACCOUNT_STATUS format a20
select USERNAME, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, PROFILE from DBA_USERS;
col object_name for a30;
col object_type format a20
col object_name format a30
select object_type,object_name from user_objects where status='INVALID' group by object_type,object_name ;
select object_type, count(0) from user_objects where status='INVALID' group by rollup(object_type);
select object_type,object_name from user_objects where status='INVALID' group by rollup(object_type,object_name);
select 'exec DBMS_MVIEW.refresh ('||''''||object_name||''''||','||''''||'C'||''''||');' MVQuery from user_objects where object_type = 'MATERIALIZED VIEW'
'GRANT SELECT ON '||table_name||' TO <read_only_user>;' from user_tables
select 'GRANT EXECUTE ON '||object_name||' TO campread;' from user_objects where object_type in ('FUNCTION','PACKAGE','PACKAGE BODY');
select 'exec DBMS_MVIEW.refresh ('||''''||object_name||''''||','||''''||'C'||''''||');' MVQuery from user_objects where object_type = 'MATERIALIZED VIEW'

 
 
Enable and Disable Constraints. : =====================================>
select 'alter table ' || table_name || ' disable constraint ' || constraint_name || ' ; 'from user_constraints where constraint_type = 'R';
select 'alter table ' || table_name || ' enable constraint ' || constraint_name || ' ; 'from user_constraints where constraint_type = 'R'; 

Tips For DBA part 3

Tnsnames.ora Path :: =====================================>
/etc/tnsnames.ora
DB Objects Queries:: =====================================>
select * from user_objects where status='INVALID' order by object_type;
select * from user_constraints;
select * from dba_objects where object_name = 'obj_name'
select * from dba_directories
select * from user_objects where trunc(last_ddl_time) > trunc(sysdate - 3) and object_type NOT IN ( 'TABLE', 'INDEX','VIEW','FUNCTION')
select * from user_objects where object_type = 'TABLE';
select * from v$session where status='ACTIVE' and type!='BACKGROUND' and schemaname='vivek'
select * from v$sql where sql_id = '81q8p3wcy6jjh'
select * from v$sql_plan where sql_id = '81q8p3wcy6jjh' and operation = 'HASH JOIN' order by bytes desc
select * from dba_objects where data_object_id = 2337498
select * from v$session_longops where sofar<totalwork
select INSTANCE_NUMBER,INSTANCE_NAME,STATUS,STARTUP_TIME,DATABASE_STATUS,ACTIVE_STATE from v$instance;
select * from dict where table_name like '%SORT%'
select * from V$TEMPSTAT
select * from V$TEMPSEG_USAGE
select * from V$TEMP_EXTENT_POOL
select * from V$TEMP_SPACE_HEADER
select * from V$TEMPSTATXS
select * from V$TEMP_HISTOGRAM
select * from V$SORT_SEGMENT
select * from V$SORT_USAGE
select * from v$parameter where name like 'pga%'
select * from v$instance@publish
select * from user_db_links
select * from dba_directories
select * from v$session where status = 'ACTIVE'
select * from dba_mview_refresh_times;
select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment
select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from all_objects;
select segment_type , count(1) from dba_segments where tablespace_name = 'V_INDEX' group by segment_type;
select * from recyclebin;
select sum(bytes/1024/1024) from dba_free_space where tablespace_name='&tbname' / 2 Enter value for tbname: V_Index
select segment_type , count(1) from dba_segments where tablespace_name = 'V_Index' group by segment_type;
select * from recyclebin;
select sum(bytes/1024/1024) from dba_free_space where tablespace_name='&tbname'
select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin
flashback table tst to before drop;


Utilities:: =====================================>
exec sys.alter_user ('alter user vivread account unlock');
exec dbms_utility.compile_schema(schema => '`echo $USERNAME | tr [:lower:] [:upper:]`',compile_all=>FALSE);
exec dbms_utility.compile_schema('VIVEK',FALSE);
exec alter_user('alter user username identified by passwd')
exec sys.kill_session(30,'52981');
exec sys.kill_session(sid,'serialno');
exec alter_user('alter user a2 account unlock');
exec alter_user('alter user a2 identified by nji90okm');
exec dbms_mview.refresh('MVIEW_NAME','C')
C -> complete
exec flush_sga('SHARED_POOL');
exec flush_sga('BUFFER_CACHE');
select * from dual@im_tns
select * from user_objects where object_type = 'TABLE';
select * from gv$session where type <> 'BACKGROUND' order by last_call_et desc;

Tips For DBA Part 2

select username, profile, account_status, lock_date, expiry_date from dba_users --where profile = 'DEFAULT'
order by 1
select * from dba_users
select resource_name, resource_type, max(DEFAULT_LIMIT) DEFAULT_LIMIT , max(MONITORING_PROFILE_LIMIT) MONITORING_PROFILE_LIMIT from (
select resource_name, resource_type, limit DEFAULT_LIMIT, null MONITORING_PROFILE_LIMIT from dba_profiles where profile = 'DEFAULT'
union all
select resource_name, resource_type, null DEFAULT_LIMIT,limit MONITORING_PROFILE_LIMIT from dba_profiles where profile = 'MONITORING_PROFILE'
)
where resource_type = 'PASSWORD'
group by resource_name, resource_type
order by 1
select distinct profile from dba_profiles
select resource_name, resource_type, max(DEFAULT_LIMIT) DEFAULT_LIMIT , max(PROFILE_EXEMPTED_LIMIT) PROFILE_EXEMPTED_LIMIT,
max(PROFILE_NON_EXPIRED_LIMIT) PROFILE_NON_EXPIRED_LIMIT from (
select resource_name, resource_type, limit DEFAULT_LIMIT, null PROFILE_EXEMPTED_LIMIT, null PROFILE_NON_EXPIRED_LIMIT from dba_profiles where profile = 'DEFAULT'
union all
select resource_name, resource_type, null DEFAULT_LIMIT,limit PROFILE_EXEMPTED_LIMIT, null PROFILE_NON_EXPIRED_LIMIT from dba_profiles where profile = 'PROFILE_EXEMPTED'
union all
select resource_name, resource_type, null DEFAULT_LIMIT, null PROFILE_EXEMPTED_LIMIT ,limit PROFILE_NON_EXPIRED_LIMIT from dba_profiles where profile = 'PROFILE_NON_EXPIRED'
)
where resource_type = 'PASSWORD'
group by resource_name, resource_type
order by 1
==Run below queries to find out Failed Login Attempt========
select os_username, username,userhost,timestamp,action_name,returncode from dba_audit_trail where returncode =1017 and username='BVREAD';
select os_username, username,userhost,timestamp,action_name,returncode from DBA_AUDIT_SESSION where returncode =1017 and username='BVREAD';

Things for you DBA Part 1

what is DBA_Directories==============
select * from DBA_DIRECTORIES;
======================================
How to see when the last time object was modified===================
select * from user_objects;
select * from user_tables;
=========================================
How to create DB Link
=============================================
How to analyze tables ==========================================
ANALYZE TABLE emp VALIDATE STRUCTURE;
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE FAST;
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE ONLINE;
========================================================
What is Mview======================================
===================================================
How many ways of refresh==========================
=====================================================
How to updated a table==========================
update table table_name
set column_name ='new value'
where column_name = 'condition';
===============================================
How to see all objects===============================
select * from user_objects;
select * from user_tables;
=======================================================
How to see space consumed
==================================================
check your oracle version===============>
SELECT * FROM PRODUCT_COMPONENT_VERSION;
=================================================
parameters of database comes from below :
select * from V$PARAMETER
select * from V$PARAMETER2
select * from V$SESSION
select * from V$DATABASE
select * from GLOBAL_NAME
select * from DATABASE_PROPERTIES
================================================
ALTER ANY Table privilige is required
Enable Triggers
ALTER TABLE inventory ENABLE ALL TRIGGERS;
=================================================
ALTER ANY PROCEDURE privilige is required
Manually Recompile View
ALTER VIEW emp_dept COMPILE;
=================================================
ALTER ANY PROCEDURE privilige is required
Manually Recompile Procedures and function
ALTER PROCEDURE update_salary COMPILE;
============================================
ALTER ANY PROCEDURE privilige is required
ALTER PACKAGE acct_mgmt COMPILE BODY;
ALTER PACKAGE acct_mgmt COMPILE PACKAGE;
===========================================
search in sysnonyms
 SELECT TABLE_OWNER, TABLE_NAME, SYNONYM_NAME
    FROM DBA_SYNONYMS
    WHERE OWNER = 'JWARD';

=======================================
check for privileges
SELECT DISTINCT PRIVILEGE AS "Database Link Privileges"
FROM ROLE_SYS_PRIVS
WHERE PRIVILEGE IN ( 'CREATE SESSION','CREATE DATABASE LINK',
                     'CREATE PUBLIC DATABASE LINK')
/

CleanUp Your Server

#! /usr/bin/ksh

LOGPATH=/var/support/log/
LOGFILE=$LOGPATH/spaceclean.log
RECIPIENTS=vivek.tiwari@home.com
HOSTNAME=`hostname`
app_name=/base_path/
echo "Cleaning/Compressing log files\n\n\n " >$LOGFILE
echo "============================================================================================\n\n\n" >> $LOGFILE
logPath=/targ_dir/logs/logserver_output
echo "Compresssing below logs older than 2days"  >>$LOGFILE
echo "\n==========================================================================================\n" >> $LOGFILE
find $logPath -type f -mtime +2 ! \( -name filename -o -name *.gz \) -print >> $LOGFILE
echo "\n==========================================================================================\n" >> $LOGFILE
find $logPath -type f -mtime +2 ! \( -name filename -o -name *.gz \) -exec gzip {} \; >>$LOGFILE
if [ $? -eq 1 ]; then
echo "Unable to compress the logs  at $logPath\n"  >> $LOGFILE
else
echo "Log Compression Successfull at $logPath\n" >> $LOGFILE
fi
echo "Removing below logs older than 90days"  >>$LOGFILE
echo "\n==========================================================================================\n" >> $LOGFILE
find $logPath -type f -mtime +90 -exec ls -ltr {} \; >>$LOGFILE
echo "\n==========================================================================================\n" >> $LOGFILE
find $logPath -type f -mtime +90 -exec rm -rf {} \;
if [ $? -eq 1 ]; then
echo "Unable to delete the logs at $logPath\n"  >> $LOGFILE
else
echo "Removing of 90days old log files Successfull at $logPath\n" >> $LOGFILE
fi
OtherlogPath2=/targ_dir/logs/dgraphs
echo "compressing below logs older than 1 days from $OtherlogPath2" >>$LOGFILE
echo "\n==========================================================================================\n" >> $LOGFILE
find $OtherlogPath2 -type f -mtime +1 ! \( -name ".*" -o -name *.gz \) -print >> $LOGFILE
echo "\n==========================================================================================\n" >> $LOGFILE
echo "Compression Started at $OtherlogPath2 for logs"  >>$LOGFILE
find $OtherlogPath2 -type f -mtime +1 ! \( -name ".*" -o -name *.gz \) -print -exec gzip {} \; >>$LOGFILE
if [ $? -eq 1 ]; then
echo "Unable to compress the logs  at $OtherlogPath2 \n"  >> $LOGFILE
else
echo "Compression folder_name at $OtherlogPath2 for (*.reqlog.*) logs"  >>$LOGFILE
fi
echo "\n==========================================================================================\n" >> $LOGFILE
per=`df -h /var/opt | tail -1 | awk '{print $4}'`
df -h /var/opt >> $LOGFILE
echo "Removing Report logs older than 30days"  >>$LOGFILE
echo "\n==========================================================================================\n" >> $LOGFILE
#find $app_name/1_report -type f -mtime +30 -exec ls -ltr {} \; >>$LOGFILE
echo "\n==========================================================================================\n" >> $LOGFILE
echo "Removing  Report logs older than 30days"  >>$LOGFILE
echo "\n==========================================================================================\n" >> $LOGFILE
#find $app_name/2_report -type f -mtime +30 -exec ls -ltr {} \; >>$LOGFILE
echo "\n==========================================================================================\n" >> $LOGFILE
echo "Removing Report  logs older than 30days"  >>$LOGFILE
echo "\n==========================================================================================\n" >> $LOGFILE
#find $app_name/3_report -type f -mtime +30 -exec ls -ltr {} \; >>$LOGFILE
echo "\n==========================================================================================\n" >> $LOGFILE

mailx -s "On $HOSTNAME the file system is now $per" $RECIPIENTS < $LOGFILE

Disable Warning in Perl

Simple... use below line in the subroutine if you have used "use warnings" in the top of your programm.

no warning;

Perl Take Error and Output in one file

sub executeCommand
{
  my $command = join ' ', @_;
  ($? >> 8, $_ = qx{$command 2>&1});
}

my ($status, $output) = executeCommand ('/bin/ls', '/');

Unix Pointers

How will you find the files that are accessed before 10 minutes?
find / -amin -10

find ~ -mmin -10

"history | awk '{print $2}' | sort | uniq -c | sort -nr | head -5".

What UNIX command do you use to find out the open ports in a system?
 lsof -i tcp | grep LISTEN | awk '{print $(NF -1)}' | sort | uniq

Explain kill() and its possible return values?

There are four possible results from this call:‘kill()’ returns 0. This implies that a process exists with the given pid, and the system would allow you to send signals to it. It is system-dependent whether the process could be a zombie.‘kill()’ returns -1, ‘errno == esrch’ EIther no process exists with...

As we all know kill command sends a signal to the kernel. This signals are of 64 types. To list all these signals you can use kill -l. Signal no 1 -31 can be invoked by user, whereas, signal no 34-64 ...

How to terminate a process which is running and the specialty on command kill 0?
With the help of kill command we can terminate the process.Syntax: kill pidkill 0 - kills all processes in your system except the login shell.

kill -0 : All processes in the current process group are signaled.
Kill -0 commnd is use to sends signal to all process and group of process.

kill 0  will delete all the processes

Difference between kill and kill -9
kill      ---> generates SIGTERM signal requesting process to terminate
kill -9 ---> generates SIGKILL signal for process to terminate immediately

Kill -9 is force termination of a job means processes which refuse to die

2. How do you find how many cpu are in your system and there details?
By looking into file /etc/cpuinfo for example you can use below command:
cat /proc/cpuinfo

7. In a file word UNIX is appearing many times? How will you count number?
grep -c "Unix" filename

10. How do you find whether your system is 32 bit or 64 bit ?
Either by using "uname -a" command or by using "arch" command.

1. How do you find which processes are using a particular file?
By using lsof command in UNIX. It wills list down PID of all the process which is using a particular file

5. If one process is inserting data into your MySQL database? How will you check how many rows inserted into every second?

1. How to display the 10th line of a file?
head -10 filename | tail -1
2. How to remove the header from a file?
sed -i '1 d' filename
3. How to remove the footer from a file?
sed -i '$ d' filename
4. Write a command to find the length of a line in a file?
The below command can be used to get a line from a file.
sed –n '<n> p' filename
We will see how to find the length of 10th line in a file
sed -n '10 p' filename|wc -c
5. How to get the nth word of a line in Unix?
cut –f<n> -d' '
6. How to reverse a string in unix?
echo "java" | rev
7. How to get the last word from a line in Unix file?
echo "unix is good" | rev | cut -f1 -d' ' | rev
8. How to replace the n-th line in a file with a new line in Unix?
sed -i'' '10 d' filename       # d stands for delete
sed -i'' '10 i new inserted line' filename     # i stands for insert
9. How to check if the last command was successful in Unix?
echo $?
10. Write command to list all the links from a directory?
ls -lrt | grep "^l"
11. How will you find which operating system your system is running on in UNIX?
uname -a
12. Create a read-only file in your home directory?
touch file; chmod 400 file
13. How do you see command line history in UNIX?
The 'history' command can be used to get the list of commands that we are executed.
14. How to display the first 20 lines of a file?
By default, the head command displays the first 10 lines from a file. If we change the option of head, then we can display as many lines as we want.
head -20 filename
An alternative solution is using the sed command
sed '21,$ d' filename
The d option here deletes the lines from 21 to the end of the file
15. Write a command to print the last line of a file?
The tail command can be used to display the last lines from a file.
tail -1 filename
Alternative solutions are:
sed -n '$ p' filename
awk 'END{print $0}' filename

For larger data sets where sorting may not be desirable, you can also use the following perl script:
./yourscript.ksh | perl -ne 'if (!defined $x{$_}) { print $_; $x{$_} = 1; }'
This basically just remembers every line output so that it doesn't output it again.
It has the advantage over the "sort | uniq" solution in that there's no sorting required up front.

search exact word :
grep -nw '101' autogen_dimensions.xml
8:         <PVAL>101</PVAL>
Print a particular line with line number in Unix :
awk 'NR==8' autogen_dimensions.xml

# substitute "foo" with "bar" ONLY for lines which contain "baz" {selective substitution}
sed '/baz/s/foo/bar/g' sourcefile > target_file

How Do I Add User Jerry To A Secondary Group Called Sales?
Type the following command:
# useradd -G sales -m jerry
# passwd jerry
How Do I Add /bin/ksh as A Shell While Creating A User Account?
Use the following syntax:
useradd -m -s /bin/ksh usernameHow Do I Setup HomeDirectory Path While Creating A User Account?
Use the following syntax:
useradd -m -s /bin/ksh -d /path/to/home/user usernameYou can pass all those options as follows:
# useradd -d /nas/users/v/vivek -m -s /bin/bash -c "Vivek Gite" vivek
# passwd vivek

You can search recursively i.e. read all files under each directory for a string "192.168.1.5"
$ grep -r "192.168.1.5" /etc/
scp from detination
scp acomsl12:/tmp/Cheetah_Publish_Report_29-Feb-2012.log /home/anurverm
scp f

To see the port
netstat -an | grep portno

Key Gen :
ssh-keygen -t rsa

./dos2ux fap_item.txt   > fap_item.txt.bak
bad interpreter issue:
perl -i -pe 's/\r//g' inputfile
dos2unix inputfile

symlink creation:
ln -s mysql-advanced-5.5.15-linux2.6-x86_64 mysql
--> pipe the find command with grep with the help of xargs

sed -e '/[^0-9]$/{N;s/\n//;}' testing.txt > new.txt

$ echo $VAR_NAME | tr '[:upper:]' '[:lower:]'
$ echo $VAR_NAME | tr '[:lower:]' '[:upper:]'
\
/var/spool/mqueue is empty
                Total requests: 0

Unix Top 10 Files:
find /start/dir -type f -exec du {} \; | sort -rn | head -10

find ./ -type d -name .svn -exec rm -rf {} \;

split -l 1000 -a 3 install_ctl_file.ksh  new-install_ctl_file.ksh

1.  if u want to select the arguement by number i.e. if u know that the element is placed  in nth row and nth column.
cat sam | awk -F" " '{print $1}' | sed -n '4p'
this lil script will take the 4th row element from 1st column. filename is "sam" here.
2.  if u know the pattern which u wanna take out, then:
cat sam | awk -F" " '{print $1}' | sed -n '/manshul/p'
u can subtitute manshul wid the pattern u require
Now if u wanna execute this in ur script,then
cat sam | awk -F" " '{print $1}' | sed -n '4p' | xargs -t ./zz
here "zz" is the script name u wanna execute after picking the variable.

to find a string in all files and in all subdirectories......
find   . -type f  -exec  grep "Albert" /dev/null {} \;
find . -type f -exec grep "199.106.64.131" /dev/null {} \;
to find the biggest 10 files under a user:
du -ha /home/user30 | sort -n -r | head -n 10

mailx -s "Subject" mailid

EXAMPLE CRON FILE
     # use /bin/sh to run commands, overriding the default set by cron
     SHELL=/bin/sh
     #
     # mail any output to `paul', no matter whose crontab this is
     MAILTO=paul
     #
     # run five minutes after midnight, every day
     5 0 * * *      $HOME/bin/daily.job >> $HOME/tmp/out 2>&1
     #
     # run at 2:15pm on the first of every month -- output mailed to paul
     15 14 1 * *     $HOME/bin/monthly
     #
     # run at 10 pm on weekdays, annoy Joe
     0 22 * * 1-5    mail -s "It's 10pm" joe%Joe,%%Where are your kids?%
     23 0-23/2 * * * echo "run 23 minutes after midn, 2am, 4am ..., everyday"
     5 4 * * sun     echo "run at 5 after 4 every sunday"

ps -efx | grep perfuser

2. Last 3 words in line print
date=`echo $line | awk '{print $(NF-2), $(NF-1), $NF}'`
3. Send a varibale through mail:
echo | mailx -s "this is current memory status $temp1 mail_id

lsof | grep /tmp

search exact word :
grep -nw '101' autogen_dimensions.xml
8:         <PVAL>101</PVAL>
Print a particular line with line number in Unix :
awk 'NR==8' autogen_dimensions.xml

# substitute "foo" with "bar" ONLY for lines which contain "baz" {selective substitution}
sed '/baz/s/foo/bar/g' sourcefile > target_file

How Do I Add User Jerry To A Secondary Group Called Sales?
Type the following command:
# useradd -G sales -m jerry
# passwd jerry
How Do I Add /bin/ksh as A Shell While Creating A User Account?
Use the following syntax:
useradd -m -s /bin/ksh usernameHow Do I Setup HomeDirectory Path While Creating A User Account?
Use the following syntax:
useradd -m -s /bin/ksh -d /path/to/home/user usernameYou can pass all those options as follows:
# useradd -d /nas/users/v/vivek -m -s /bin/bash -c "Vivek Gite" vivek
# passwd vivek

You can search recursively i.e. read all files under each directory for a string "192.168.1.5"
$ grep -r "192.168.1.5" /etc/
scp from detination

To see the port
netstat -an | grep portno

Key Gen :
ssh-keygen -t rsa

./dos2ux fap_item.txt   > fap_item.txt.bak
bad interpreter issue:
perl -i -pe 's/\r//g' inputfile
dos2unix inputfile

symlink creation:
ln -s mysql-advanced-5.5.15-linux2.6-x86_64 mysql
--> pipe the find command with grep with the help of xargs

sed -e '/[^0-9]$/{N;s/\n//;}' testing.txt > new.txt

$ echo $VAR_NAME | tr '[:upper:]' '[:lower:]'
$ echo $VAR_NAME | tr '[:lower:]' '[:upper:]'

Unix Top 10 Files:
find /start/dir -type f -exec du {} \; | sort -rn | head -10

find ./ -type d -name .svn -exec rm -rf {} \;

split -l 1000 -a 3 install_ctl_file.ksh  new-install_ctl_file.ksh

1.  if u want to select the arguement by number i.e. if u know that the element is placed  in nth row and nth column.
cat sam | awk -F" " '{print $1}' | sed -n '4p'
this lil script will take the 4th row element from 1st column. filename is "sam" here.
2.  if u know the pattern which u wanna take out, then:
cat sam | awk -F" " '{print $1}' | sed -n '/manshul/p'
u can subtitute manshul wid the pattern u require
Now if u wanna execute this in ur script,then
cat sam | awk -F" " '{print $1}' | sed -n '4p' | xargs -t ./zz
here "zz" is the script name u wanna execute after picking the variable.

to find a string in all files and in all subdirectories......
find   . -type f  -exec  grep "Albert" /dev/null {} \;
find . -type f -exec grep "199.106.64.131" /dev/null {} \;
to find the biggest 10 files under a user:
du -ha /home/user30 | sort -n -r | head -n 10

mailx -s "Subject" mailid

EXAMPLE CRON FILE
     # use /bin/sh to run commands, overriding the default set by cron
     SHELL=/bin/sh
     #
     # mail any output to `paul', no matter whose crontab this is
     MAILTO=paul
     #
     # run five minutes after midnight, every day
     5 0 * * *      $HOME/bin/daily.job >> $HOME/tmp/out 2>&1
     #
     # run at 2:15pm on the first of every month -- output mailed to paul
     15 14 1 * *     $HOME/bin/monthly
     #
     # run at 10 pm on weekdays, annoy Joe
     0 22 * * 1-5    mail -s "It's 10pm" joe%Joe,%%Where are your kids?%
     23 0-23/2 * * * echo "run 23 minutes after midn, 2am, 4am ..., everyday"
     5 4 * * sun     echo "run at 5 after 4 every sunday"

ps -efx | grep perfuser

2. Last 3 words in line print
date=`echo $line | awk '{print $(NF-2), $(NF-1), $NF}'`

# substitute "foo" with "bar" ONLY for lines which contain "baz" {selective substitution}
sed '/baz/s/foo/bar/g' sourcefile > target_file

How Do I Add User Jerry To A Secondary Group Called Sales?
Type the following command:
# useradd -G sales -m jerry
# passwd jerry
How Do I Add /bin/ksh as A Shell While Creating A User Account?
Use the following syntax:
useradd -m -s /bin/ksh usernameHow Do I Setup HomeDirectory Path While Creating A User Account?
Use the following syntax:
useradd -m -s /bin/ksh -d /path/to/home/user usernameYou can pass all those options as follows:
# useradd -d /nas/users/v/vivek -m -s /bin/bash -c "Vivek Gite" vivek
# passwd vivek

Perl File Copy and Move tb/w Dir

use File::Copy;
copy("hash2.txt","hash.txt") or die "Copy failed: $!";
move("dest_dir/test_file","hash2.txt") or die "Move failed: $!";

Script for checking the file older than 10 minutes

#!/bin/ksh
export job_status=0
export ENV=abc
export log_path=/var/opt/logs
export log_file=/tmp/check.log
export fail_dir=/var/opt/fail
#export log_path=/home/vivt
rm $log_file
cd $log_path
touch test_check_temp.inprog
sleep 600
export latest_log=`cd ${log_path};ls -ltr lnk* | tail -1 | awk '{ print $9 }'`
if [[ test_check_temp.inprog -ot $latest_log ]];then
        echo 'The test job is running fine' >> $log_file
        rm $fail_dir/*
else
        echo "The job is hung" >> $log_file
        job_status=1
        touch $fail_dir/test_job_hung
fi
rm test_check_temp.inprog
if [[ $job_status -eq 1 ]] ; then
        echo "The test job is hung " | mailx -s "job is hung " -m $RECIPENTS
        if [[ $? -eq 1 ]];then
                echo 'No E-mail' >> $log_file
        else
                echo 'E-mail sent ' >> $log_file
        fi
else
        echo 'The job running fine' >> $log_file
        rm $fail_dir/*
fi

Unix Scripts You May Need Day to Day

CleanUp Your Server :

find . -mtime +85 -exec ls -ltr {} \;
find . -mtime +77 -exec rm -rf {} \;
find . -type f -name *.Z* -mtime +30 -exec ls -ltr {} \;
find . -type f -name *.dat.Z* -mtime +30 -exec rm -f {} \;
find . -type f -name *.gz* -mtime +30 -exec ls -ltr {} \;
find . -type f -name *.gz* -mtime +30 -exec rm -f {} \;
find . -type f -name *.dmp* -mtime +30 -exec ls -ltr {} \;
find . -type f -name *.dmp* -mtime +30 -exec rm -f {} \;
find . -type f -name *.dmp -mtime +2 -exec compress {} \;
find . -type f -name *.dat -mtime +2 -exec compress {} \;
find . -mtime +93 -exec ls -ltr {} \;
find . -mtime +93 -exec mv {} ./archive/ \;
find . -mtime +93 -exec rm -rf {} \;


search exact word :
grep -nw '101' autogen_dimensions.xml
8:         <PVAL>101</PVAL>

Print a particular line with line number in Unix :
awk 'NR==8' autogen_dimensions.xml

# substitute "foo" with "bar" ONLY for lines which contain "baz" {selective substitution}
sed '/baz/s/foo/bar/g' sourcefile > target_file

How Do I Add User Jerry To A Secondary Group Called Sales?
Type the following command:
# useradd -G sales -m jerry
# passwd jerry
How Do I Add /bin/ksh as A Shell While Creating A User Account?
Use the following syntax:
useradd -m -s /bin/ksh usernameHow Do I Setup HomeDirectory Path While Creating A User Account?
Use the following syntax:
useradd -m -s /bin/ksh -d /path/to/home/user usernameYou can pass all those options as follows:
# useradd -d /nas/users/v/vivek -m -s /bin/bash -c "Vivek Gite" vivek
# passwd vivek

You can search recursively i.e. read all files under each directory for a string "192.168.1.5"
$ grep -r "192.168.1.5" /etc/

To see the port
netstat -an | grep portno

Key Gen :
ssh-keygen -t rsa

./dos2ux fap_item.txt   > fap_item.txt.bak

bad interpreter issue:
perl -i -pe 's/\r//g' inputfile
dos2unix inputfile

symlink creation:
ln -s mysql-advanced-5.5.15-linux2.6-x86_64 mysql
--> pipe the find command with grep with the help of xargs

sed -e '/[^0-9]$/{N;s/\n//;}' testing.txt > new.txt

uuencode tail_file.txt tail_file.txt | mailx -m -s "test" mail_id

$ echo $VAR_NAME | tr '[:upper:]' '[:lower:]'
$ echo $VAR_NAME | tr '[:lower:]' '[:upper:]'


pbrun mailq
/var/spool/mqueue is empty
                Total requests: 0

Unix Top 10 Files:
find /start/dir -type f -exec du {} \; | sort -rn | head -10

find ./ -type d -name .svn -exec rm -rf {} \;


 sed –i '5,7 d' file.txt

 sed –i '$ d' file.txt

  sed '1 d' file.txt > new_file.txt
$> mv new_file.txt file.txt
 sed –n '4 p' test

 $ ln -s 1.3 latest

 $ ln -nsf 1.2 latest

 find . -perm 644

find . –iname "error"–print ( -i is for ignore )
find . -name"*.tmp" -print | xargs rm–f

find . -maxdepth1 -type f -newer first_file

find . -type f -cmin 15 -prune

find . -size +1000c -exec ls-l {} \;

Always use a c after the number, and specify the size in bytes, otherwise you will get confuse because find -size list files based on size of disk block.
to find files using a range of file sizes, a minus or plus sign can be specified before the number. The minus sign means "less than,"
and the plus sign means "greater than." Suppose if you want to find all the files within a range you can use find command as in below example of find:

find . -size +10000c -size-50000c-print
find . -mtime +10 -size +50000c -execls -l{} \;