important commands on solaris & linux operating system we are using oracle

Today I am sharing the some important commands on solaris & linux operating system we are using oracle



it's will give the perfect ipaddress



nslookup <>

--> firing "awk" command

ls -l | grep 'Feb 15' | awk '{print $9}' | xargs -i rm {}

ls -l | grep 'Dec Jan' | awk '{print $9}' | xargs -i rm {}

I use the following ps commands in order to check for performance probelms:

1) Displaying top CPU_consuming processes:

ps aux | head -1; ps aux | sort -rn +2 | head -10

2) Displaying top 10 memory-consuming processes:

ps aux | head -1; ps aux | sort -rn +3 | head

3) Displaying process in order of being penalized:

ps -eakl | head -1; ps -eakl | sort -rn +5 |grep -i aioserver|wc -l --(to check aioserver)

4) Displaying process in order of priority:

ps -eakl | sort -n +6 | head

5) Displaying process in order of nice value

ps -eakl | sort -n +7

6) Displaying the process in order of time

ps vx | head -1;ps vx | grep -v PID | sort -rn +3 | head -10

7) Displaying the process in order of real memory use

ps vx | head -1; ps vx | grep -v PID | sort -rn +6 | head -10

8) Displaying the process in order of I/O

ps vx | head -1; ps vx | grep -v PID | sort -rn +4 | head -10

9) Displaying WLM classes

ps -a -o pid, user, class, pcpu, pmem, args

10) Determinimg process ID of wait processes:

ps vg | head -1; ps vg | grep -w wait

11) Wait process bound to CPU

ps -mo THREAD -p

REVERTING BACK FROM 64 BIT TO 32 BIT

Login as root on both servers

# bootinfo -K

# ln -sf /usr/lib/boot/unix_mp /unix

# ln -sf /usr/lib/boot/unix_mp /usr/lib/boot/unix

# bosboot -ad /dev/ipldevice

-- comment the line containing "orapw:2:wait:/etc/loadext -l /etc/pw-syscall64"

-- remove the comment from line "orapw:2:wait:/etc/loadext -L /etc"

# shutdown -Fr



you can find it the xclock this location at solaris operating system

/usr/openwin/bin/xclock

How can we verify the how many archives are generating per day

ls -ltr *|grep "Jul 8"|wc -l

cut -f 1,2 -d : /etc/passwd (it's displaying the first two fields of the password file)



using this command is changing the archives format

for FILENAME in `ls verpp_*.arc.Z`
do
NF=`echo $FILENAME | sed 's/verpp/verpq2/'`
mv $FILENAME $NF
done

This is of particularly the concurrent processes is running or not we can verify



ps -fu applerpu1 |grep -i FND | awk '{print $2}'





Killing the concurrent processes

ps -fu applerpu1 |grep -i FND | awk '{print $2}' | xargs kill -9

These are the different applications we are verifying

ps -ef|grep -i defunc

ps -ef|grep -i applerpu2
ps -fu applerpu2

FIND THE RAM MEMORY IN SOLARIS

/usr/sbin/prtdiag | more

cpio -idmv > (patchnumber) ---------à While installation .cpio extention patcheswe need to unzip using this command

DISPLAYING PROCESSES STATISTICS

prstat -a

Displaying the who are logging at OS level If you type (w) character.

-------------
w

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



This is the command in vi file how to take a space in starting line instead of kama (,)



In column mode like this
:g/,/s// /g (if you give this command in column mode you can receive space instead of coma)



find /U01/vol1/arc/*.arc -type f -mtime +1 -exec rm {} \; ---------In the cronjobs they are mentioning of removing archives older than oneday



For analyzing schema

exec dbms_stats.gather_schema_stats ( ownname=>'ARN', options=>'GATHER', estimate_percent =>dbms_stats.auto_sample_size, cascade=>TRUE, degree=>15 );

exec dbms_stats.gather_schema_stats (ownname => 'SCOTT',options => 'GATHER',estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto',cascade => true, degree => 4 );

USING THIS QUERY YOU CAN FIND IT HOW MANY DATAFILES & THEIR LOCATION FOR CLONING PURPOSE IT'S USEFUL

select distinct substr(file_name,1,13) from dba_data_files;



How can you check the contents in a procedure (or)package
select TEXT from dba_source where NAME='OWNER.PACKAGE (OR)PROCEDURE';

CONSTRAINT

alter table disable constraint ;

LOAD TEST OF COMMANDS

select count(1) from v$open_cursor;

select sid, count(1) from v$open_cursor group by sid order by 2;

SELECT sid, user_name, COUNT(*) "Cursors per session" FROM v$open_cursor
GROUP BY sid, user_name;

select username,status,sid,serial#,osuser,machine,program from v$session;

SELECT sess.sid,stxt.piece,stxt.sql_text FROM v$session sess,v$sqltext stxt WHERE sess.sql_address = stxt.address
AND sess.sql_hash_value = stxt.hash_value AND sess.sid = &sid ORDER BY stxt.piece


sar

top

vmstat

IF YOU CANNOT FIND THE TOP COMMAND IN SOLARIS

instead of top command you can execute this /usr/bin/prstat

perfstat/perfstat


@?/rdbms/admin/spreport.sql

we can find the cpu usage of inactive sessions using this command

select p.spid,s.sid,s.serial#,s.username,s.osuser,s.status,s.schemaname from v$process p,v$session s where s.paddr=p.addr and s.sid=18

HIGHER VERSION IMPORTING

TAKE EXPORT like this

Exp user/password@ db file='local file path.dmp' log='local path.log'

BLOCK CORRUPTION

v$copy_corruption
v$database_block_corruption
v$backup_corruption

SQL> select CORRUPTION_CHANGE#,CORRUPTION_TYPE,BLOCK#,a.BLOCKS,TABLE_NAME,OWNER from v$database_block_corruption a, dba_tables b
where a.BLOCKS=b.BLOCKS and b.TABLE_NAME='SYMC2_CUF_TXN';

SQL> SELECT SEGMENT_TYPE,OWNER||'.'||SEGMENT_NAME FROM DBA_EXTENTS WHERE FILE_ID = 32 AND 328013 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS -1;

SQL> alter index owner.indexname rebuild online;

select ds.*
from dba_segments ds, sys.uet$ e
where ds.header_file=e.segfile#
and ds.header_block=e.segblock# and ds.owner='EAIMGR'
and between e.block# and e.block#+e.length-1;
and e.header_file=
and between e.block# and e.block#+e.length-1;

Creating Database LINK

CREATING DATABASE LINK
create database link STAGE_DATBASE connect to USER0 identified by ST3G1 using 'ALIAS';

select 'grant '||privilege||' to '||r.grantee||';' from dba_role_privs r, dba_sys_privs s where r.granted_role = 'CONNECT' and s.grantee = 'CONNECT' and r.grantee <> 'SYS';

select * from dba_dml_locks where name='AGG_ORDER_STG';

How can you check the table corrupted using this view

SELECT segment_name,segment_type,owner,tablespace_name,block_id,blocks FROM dba_extents WHERE file_id=393 AND 114 BETWEEN block_id and block_id + blocks -1 ;

SELECT segment_name,segment_type, owner, tablespace_name, block_id, blocks FROM dba_extents WHERE file_id = 388 and block_id=33;

alter index indexname modify partition partition_name unuseable;

alter index indexname rebuild online;


This is procedure of EXPLAIN PLAN

explain plan for <>

After that you execute this procedure

@$ORACLE_HOME/rdbms/admin/utlxpls

How to check the OEM VERSIONG

select * from dba_registry;

Procedure of Datapump

First create a directory on OS level.

Then create directory on sql prompt using the OS directory path by using the user who is going to take the export.

Ex. Create directory pqr as 'path of os directory';


Expdp user/passwd directory=pqr dumpfile=xyz.dmp logfile=xyz.log schemas/tables=a,b,c



Mooving archives using this date

nohup mv `ls -ltr *.gz|grep "Mar 7"|tr -s " "|cut -d " " -f9` /U01/vol9/arc_bkp &



How can we verify the how many archives are generating per day

ls -ltr *|grep "Jul 8"|wc -l

How to take export using datapump

U01> Create directory export as '/U01/vol1/bkp/export';

Directory created.

expdp system/xxx directory=export dumpfile=23May2008.dmp logfile=23May2008.log tables='STAGE.RPT_SKU','STAGE.SU_SUBSCRIPTIONS_SPD_ORIG'

impdp dumpfile=23May2008.dmp logfile=imp_23May2008.log REMAP_TABLESPACE=STAGE_DATA01:SASSTGD_DATA01

YOU CAN CREATE A DUMP FILES USING THIS PARALLEL=4 WHILE TAKING EXPORT AT A TIME IN DATAPUMP

nohup expdp system/xxxx dumpfile=SR_ETL_exp_tables_%U.dmp directory=SD LOGFILE=SR_ETL_exp_table_1.log PARALLEL=4 &

nohup impdp system/xxxx dumpfile=SR_ETL_exp_tables_%U.dmp directory=HD LOGFILE=SR_ETL_imp_tables_1.log PARALLEL=4 &

How to analyze table

exec dbms_stats.gather_table_stats(ownname=>'USER',tabname=>'TABLENAME',estimate_percent=>20, cascade=>true,degree=>8,method_opt=>'FOR ALL COLUMNS size 250');

export & import



nohup exp system/xxxxx owner=xxxx rows=n file=xxxx.dmp log=xxxx.log Buffer=80000000 &

nohup imp \"/ as sysdba\" file=xxxxx.dmp log=xxxxxx.log fromuser=xxxxxx touser=xxxxxx buffer=800000 feedback=20000 ignore=y &

Taking export using PAR file Below is an example of an export parameter file: export_options.par.

compress=n

direct=y

buffer=1000

rows=n

tables=table_with_one_million_rows, table_with_one_million_rows_1

userid=scott/tiger

file= file.dmp

log=file.log

Using this parameter file, the export command line is executed by the following:

exp parfile=export_options.par


some important views

select s.username, sum(p.pga_alloc_mem), sum (p.pga_used_mem) from v$session s, v$process p where s.paddr = p.addr and sid=35 group by s.username order by 2;

select a.username,a.account_status,a.lock_date,a.expiry_date,a.profile,b.resource_name,b.resource_type,b.limit from dba_users a, dba_profiles b where a.profile=b.profile and a.username like 'SYSTEM';

select owner,object_name,object_type,status
2 from dba_objects
3 where status != 'VALID'
4 and substr(object_name,1,1) != chr(95)
5 order by 1,2;

select owner,constraint_name,constraint_type TYPE,status,table_name
2 from dba_constraints
3 where status != 'ENABLED'
4 order by 1,2;

select owner,trigger_name,table_name
2 from dba_triggers
3 where status = 'DISABLED' and owner != 'SYS';

select TABLE_NAME, TABLE_NAME, PRIVILEGE from dba_tab_privs where GRANTOR ='USER';

select * from dba_role_privs where grantee='USER';



Creating Tablespace

Create tablespace VEN_REF01_DATA datafile '/U01/vol3/dat/data_01.dbf' SIZE 5120m
LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

ANALYZING TABLE

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HYBRID',TABNAME=>'PS_INSTALLBASE',ESTIMATE_PERCENT=>20,CASCADE=>TRUE,DEGREE=>8);