Wednesday 10 January 2018

DATAGAURD SWITCHOVER IN ORACLE:-

Preparing for a Role Transition:-
=>Verify that there are no redo transport errors or redo gaps at the standby database
=>by querying the V$ARCHIVE_DEST_STATUS view on the primary database.
=>Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database.

 ******SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;  ******


Step 1 :  Verify whether it is possible to perform a switchover
On the current primary database, query the "switchover_status" column of the V$DATABASE fixed view
 on the primary database to verify it is possible to perform switchover.

SQL> select name,open_mode,log_mode,protection_mode,switchover_status from v$database ;
SWITCHOVER_STATUS
--------------------
TO STANDBY

NOTE:-
=>The TO STANDBY value in the "switchover_status" column indicates that it is possible to switch the primary database to the standby role.
=>If the TO STANDBY value is not displayed, then verify the configuration is functioning correctly
 .  (for example, verify all  "log_archive_dest_n"  parameter values are specified correctly).
=>If the value in the switchover_status column is SESSIONS ACTIVE or FAILED DESTINATION then click here .

Step  2  : Check that there is no active users connected to the databases.
SQL> select distinct osuser,username from v$session;

Step  3 : Switch the current online sdbto log file on primary database and verify that it has been appleid
SQL>alter system switch logfile ;


Step  4 : Connect with primary database and initiate the switchover
[www.sakthidbtech.com@stby ]sqlplus sys/xxxx@sakthi as sysdba
SQL> alter database commit to switchover to physical standby;
Database altered.

Now, the primary database is converted into standby database.
The controlfile is backed up to the current SQL session trace file before the switchover.
 This makes it possible to reconstruct a current control file,if necessary.
If we try to perform a switchover when other instances are running then we will get ORA-01105 as follows :

SQL>alter database commit to switchover to standby ;
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
*
ORA-01105: mount is incompatible with mounts by other instances

In order to perform a switchover, run below command on the primary database.

SQL>alter database commit to switchover to physical standby with session shutdown ;
NOTE:-
=>The above statement first terminates all active sessions by closing the primary database.
=>Then any non-archived sdbto log files are transmitted and applied to standby database.
=>Apart from that an end-of-sdbto marker is added to the header of the last log file that was archived.
=>A backup of current control file is created and the current control file is converted into a standby control file.

Step  5 : Shut down and restart the  primary instance(sdbt).
SQL>shutdown immediate;

SQL> startup mount ;

Step  6 : Verify the switchover status in the v$database view.
After we change the primary database to the physical standby role and the switchover notification is received by the standby databases in the configuration, we should verify if the switchover notification was processed by the target standby database by querying the "switchover_status"  column of the v$database fixed view on the target standby database.

On old Primary database(sakthi)
SQL> select name,open_mode,db_unique_name from v$database;
NAME      OPEN_MODE       DB_UNIQUE_NAME         SWITCHOVER_STATUS
------       -----------          --------------------         ----------------------
Ravis   MOUNTED                Ravis                     TO PRIMARY

Step 7:On old standby database (sdbt)
SQL> select name,open_mode,db_unique_name,switchover_status from v$database;
NAME        OPEN_MODE         DB_UNIQUE_NAME     SWITCHOVER_STATUS
------         ------------         -----------------            ---------------------
Ravis     MOUNTED               sdbt                          TO PRIMARY

Step 8 : Switch the target physical standby database role to the primary role
We can switch a physical standby database from the standby role to the primary role
 when the standby database instance is either mounted in sdbto Apply mode or open for read-only access.
 It must be in one of these modes so that the primary database switchover request can be coordinated.
 After the standby database is in an appropriate mode, issue the following sql statement on the physical
 standby database that we want to change to the primary role:

SQL>alter database commit to switchover to primary ;
Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area        263639040 bytes
Fixed Size                               1373964 bytes
Variable Size                           213911796 bytes
Database Buffers                     41943040 bytes
sdbto Buffers                          6410240 bytes
Database mounted.
Database opened.

Step  9  : Check the new primary database(sdbt) and switch logfile :

SQL> select open_mode from v$database;
OPEN_MODE
---------------
READ WRITE

Note :  it's a good idea to perform a log switch on the primary .


SQL> alter system switch logfile;
System altered.

Step 10  :  Open new standby database(sakthi) in read-write

SQL> alter database open;
Database altered

SQL> select name,open_mode ,db_unique_name,switchover_status  from v$database;

NAME      OPEN_MODE    DB_UNIQUE_NAME     SWITCHOVER_STATUS
------      -------------      ------------------        -----------------------
Ravis   READ ONLY        Ravis                      RECOVERY NEEDED

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select  name,open_mode  from  v$database;
NAME              OPEN_MODE         
---------          ---------------------------------
Ravis          READ ONLY WITH APPLY

Patching In Oracle Version of 12.1.0.1.3

Patching in oracle 12.1.0.1.3

export ORACLE_HOME=/oracle/product/12.1.0.2/db_1
export PATH=$ORACLE_HOME/bin:$PATH
oracle@dbtech:/oradata/software/23054246> echo $ORACLE_HOME
/oracle/product/12.1.0.2/db_1
oracle@dbtech:/oradata/software/23054246> echo $PATH
/oracle/product/12.1.0.2/db_1/bin:/home/oracle/bin:/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/X11R6/bin:/usr/games:/usr/lib/mit/bin:/usr/lib/mit/sbin:/oracle/product/12.1.0/db_1/bin
oracle@dbtech:/oradata/software/23054246>

oracle@dbtech:/oradata/software/23054246> /oracle/product/12.1.0.2/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
/oracle/app/product/12.1.0.2/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
/oracle/app/product/12.1.0.2/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
/oracle/app/product/12.1.0.2/dbhome_1/OPatch/opatch apply
Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2016, Oracle Corporation.  All rights reserved.
PREREQ session
Oracle Home       : /oracle/product/12.1.0.2/db_1
Central Inventory : /oracle/oraInventory
   from           : /oracle/product/12.1.0.2/db_1/oraInst.loc
OPatch version    : 12.1.0.1.3
OUI version       : 12.1.0.2.0
Log file location : /oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2016-08-03_12-44-17PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
oracle@dbtech:/oradata/software/23054246>
create or replace directory ORACLE_BASE as '/oracle';
create or replace directory ORACLE_HOME as '/oracle/product/12.1.0.2/db_1';

Sql Commads

Find the objects that are currently being accessed.
select distinct sp. object_name from gv$sql_plan sp,gv$session gs
 where sp.sql_id =gs.sql_id
 and gs.status='ACTIVE'
 and sp.object_name is not null
 and gs.username not in ('SYS','SYSTEM')
 and sp.object_name not like '%:%'

Find the SQL and its duration that is currently running 
select distinct a.sid,a.serial#,a.logon_time,a.username,a.sql_id,a.sql_exec_start,sysdate,a.status,
substr(to_char(NUMTODSINTERVAL(a.sql_exec_start-sysdate, 'day')),5,15) duration ,a.event,a.program,a.module,b.sql_text
from gv$session a, gv$sql b
where a.status='ACTIVE'
and a.username not in ('SYS','SYSTEM')
and b.sql_id=a.sql_id
and b.parsing_schema_name=a.username
and a.program not like 'orac%'
and b.parsing_schema_id=a.user#
order by a.sql_exec_start;
 
Find the Top SQLs order by executing duration for a particular Period.
with qduration as ( select ash.sql_id,ash.user_id,ash.sql_exec_start,min(ash.sample_time) qmin ,max(ash.sample_time) qmax  ,
EXTRACT (hour FROM (max(ash.sample_time)-min(ash.sql_exec_start))) HH,
EXTRACT (MINUTE FROM (max(ash.sample_time)-min(ash.sql_exec_start)))  MI ,
EXTRACT (SECOND FROM (max(ash.sample_time)-min(ash.sql_exec_start))) SS
from dba_hist_active_sess_history ash
where ash.sample_time  BETWEEN    to_date('07-DEC-2017 10:00:00','DD-MON-YYYY HH24:MI:SS')
and to_date('07-DEC-2017 13:23:00','DD-MON-YYYY HH24:MI:SS')
and sql_id is not null
and sql_exec_start is not null
 --and ash.user_id=71
group  by ash.sql_exec_start,ash.sql_id,ash.user_id)
select qd.sql_id,qd.user_id,qd.sql_exec_start,qd.qmax,qd.qmin,qd.HH,qd.mi,qd.ss,dbms_lob.substr(dh.sql_text,4000,1)  from qduration qd,dba_hist_sqltext dh
where qd.sql_id=dh.sql_id
order by 6 desc , 7  desc,8 desc;
SQL to find the tables are analyzed.
select owner,table_name,num_rows,last_analyzed from dba_tables where table_name=&tablename and owner=&owner;
From delveloper perspective, use 
select table_name,num_rows,last_analyzed from dba_tables where table_name=&tablename ;
Find temp usage 

 select s.username,s.osuser,u.tablespace,s.sql_id,substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
 sum(round(((u.blocks*p.value)/1024/1024),2)) size_mb
 from v$sort_usage u, v$session s, v$sqlarea a,v$parameter p
 where s.saddr = u.session_addr
 and a.address (+) = s.sql_address
 and a.hash_value (+) = s.sql_hash_value
 and p.name = 'db_block_size'
 group by  s.username,s.osuser,u.tablespace,s.sql_id,
 substr(a.sql_text, 1, (instr (a.sql_text, ' ')-1)) 

oracle interview questions

1. Tell me about ur responsibilities in your project ?
2. How amny database instance do u maintain ?
3. Backup information in ur environment ?
4. Is it necessaryto restore the entire database when system datafile is corrupt r removed ?
5. How will you restore & recover system datafile ? Do we need to give resetlogs after recovering the datafile , is yes explain if no y ?
6. What is a cpu patch & psu patch ?
7. how will u apply psu patch ?
8. Abbrevation of psu & cpu ?
9. Types of clonning ?
10. Explain rman clonning steps ?
11. What is rac ?
12. How amny nodes do u have ?
13. How to apply patch in RAC database
14. 10g to 11g migration steps ?
15. What do u know abt performance tunning
16. What all u have done in tunning



1. How will configure ASM and data guard in RAC environment
2. What is SCAN listener
3. How will tune single node in RAC.
4. How will apply Patch in RAC.
5. What is HOT and Cold Cloning. Give steps
6. Upgrade DB in ASM with RAC environment
7. How will find Performance drop in DB
8. If we need Log file backup for Cold Cloning..? Yes means why..? No means why..?
9. If DB was crashed in RAC without backup of OCR and Voting Disk. Hw will you recover the DB.
10. Explain plan and TKPROF explain

1. Tell about your profile?
2. Day to day activities?
3. How to check flash_recovery enabled or not?
4. Dbv uses?
5. If two node using rac and archive are going to these two nodes,if we had 3rd node as non rac active standby ?what I have to do ,so that archive to go to 3rd node?
6. Row chaining and row migration? How to avoid row chaining?
7. Dataguard modes? And what parameter is used to set for that modes?
8. Oracle background process?
9. When checkpoint occur?
10. 10g and 11g difference?
11. What is the use of diag in 11g?
12. Trace location in 11g and 10g?
13. Opatch ? when evenly they release patches?
14. Patching steps?
15. What you will do when error occur during patching?
16. What ticketing tool using?
17. What is catbundle?
18. What kind of refresh and all done? What tool used for that?
19. How to create tablespace with blocksize of 16k?
20. Dirty buffer? How the dirty buffer will get clear/
21. Which process write the data in  buffer memory?
22. Instance recovery?
23. How you will connect database to client?
24. Obsolete backup? How you will use the obsolete backup as valid?
25. What happen when we set init trans parameter as 0?
26. Rman cloning steps?
27. Auxilary use?

 Tell   about   urself  and day to day activities ?
  1. Patching  steps  for psu ?
  2. Upgradation for 10 to 11g    steps?
  3. Is it possible for  reuse for logfiles ?
  4. If 1000 archives missing in  standby database what u will do ? explain the steps?
  5. Ur backup strategies in ur environment  ?
  6. Rman cloning   steps in oracle ?explain step by step procedure
  7. How to refresh ur database in oracle ?
  8. What is meant by mrp process ?
  9. How to stop mrp process ?
  10. Why we have  to run catlog and catproc ?
  11. Which   ticketing tool utility u have to use in ur environment ?
  12. DIfference between hot cloning and cold cloning?
  13. Difference between consistent backup,inconsistent backup and logical backup ?
  14. Asm advantages ?why we have to use asm instance ?
  15. How many servers u have to maintained ?
  16. How many db’s u have to handle ?
  17. Whats ur role in ur project ?
  18. Why we have to run catbundle ?
  19. What are all the background process in asm ?
  20. In awr report  what r all u have to seen in tat report?
  21. Any five wait events u have to tell about cause and solutions ?
  22. Explain between level 1 and level 0 backup?
  23. In ur environment which incremental backup ur preferable ? cumulative or differential ?
  24. If ur patch is apply wrong ly,how can u recover for original state ?
  25. What is rollback patch ?
1. tell briefly about yourself
2. what is day to day activites
3. will you write scripts in shell
4. what all necessary scripts required for a db
5. what all assistance tools we have in oracle
6. what are all levels of auditing
7. how to encrypt db files
8. what all information we can see in v$logminer_contents
9. how to check index fragmentation
10. diff b/w v$instance and gv$instance
11. diff b/w oracle home and oracle base
12. Rac wait events
13. what is ocr
14. what is GRD
15. migration steps
16. upgradation steps
17. Will create tablespace have a entry in RLBC
18. how to get metadata of a table
19. how you will change db name
20. In which language oui installer in designed
21. what is change management
22. full statement to create a user
23. what all statements wont have entry in RLBC
24. can v coalese LMT
25. any one 12c feature

Infosys Questions(25.08.2015)
1. Tell me about  yourself
2. Day to day activities?
3. Do you have work experience in RAC and ASM
4. How will you check database growth?
5. What is meant by patches?
6. Have u done upgradation? Tell the steps?
7. Steps for schema refresh?
8. Backup strategies?
9. I have 1,2,2 gb sizes in 3 different disks. How will take backup in expdp?
10. Tell the command for rman active cloning?

26. How you will remove ASM from RAC


 Step for upgrade from 10g to 11g
2. If i have 10 materilized view, all 9 are sync properly and one view is not sync. How you give solution.
3. Why 11g DB start with Undo TBS.
4. 11g Arc
5. How will u recover if DG failure in ASM.
6. Difference between One way and two way Mirroring
7. Steps to convert Non ASM to ASM and Why we take backip before convert and change name for Control file
8. Data Guard Modes and Explain
9. If archive was not sync properly means how you give solution
10. If u have one standby and some archive was miss at standby, if you have perform switchover. Tell the steps and hw u recover those missing archive
11. Why u kept FAL_ClIENT and FAL_SERVER parameters
12. How will you apply missing archive from one standby to another standby
13. RMAN Cloning
14. If I have Level 0 backup and i took level 1 differential for Monday, Tuesday. Now am going to take level 1 Cumulative at wednesday. Which backup is base backup for Cumulative backup. (Vice Versa)
15. Migration from Windows to Linux
16. Why we give REMAP SCHEMA and REMAP TABLESPACE parameter in imp/exp
17. Different between import and Data Pump.
18. Different between HOT and COLD backup.
19. Why you give ALTER DATABASE OPEN RESETLOG, If you provide that commend what happen the prev archives.
20. Explain HOT CLONING and COLD CLONING.
21. Why u change REUSE to SET in control file.
22. If the query is not respose properly. Hw will give solution on that.
23. What you do initial level of Tuning
24. Hw will force index to the table
25. If an object was move from one tbs to another tbs. Hw will you recover Index and Partition.
26. Startup and Shoutdown stages and Explain.
27. Hw will you change segment parameters.
28. Explain deadlock
29. What is ORA1555 error
30. IF RAC have two nodes one node was failure which process will take care of switching. Hw will check RAC health.

Upgrade from 12.1.0.1 to 12.1.0.2



Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (Doc ID 1503653.1)



1. Check Concurrent statistics gathering is set to FALSE

 SELECT dbms_stats.get_prefs('CONCURRENT') from dual;

if not, then

BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','FALSE');
END;
/

2. Check for invalid objects and run utlrp.sql

3. Copy  preupgrd.sql and utluppkg.sql into temporary directory and execute preupgrd.sql script on database and fix the recommendations

4. Check parallel settings

5. Check for Events and hidden parameters and disable before upgrade

SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2 WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE';
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE';
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';


6. Check Dependency on Network utility packages

SELECT * FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_
INADDR','DBMS_LDAP')
AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');

7. Gather Dictionary statisitics --> DONE

8. Check the recovery pending status

SELECT * FROM v$recover_file;

9. Purge DBA RECYCLE BIN

PURGE DBA_RECYCLEBIN


10. Shutdown the listener  --> DONE
11. Shutdown database --> DONE

12. Backup pfile,spfile, pwd file and update Pfile with following and place it in new Oracle home

#Recommended parameters for 12c
_optimizer_reduce_groupby_key =FALSE
_optimizer_aggr_groupby_elim=FALSE
_optimizer_unnest_scalar_sq=FALSE
_optimizer_coalesce_subqueries=FALSE
_use_single_log_writer=TRUE
#Parameters for parallelism
parallel_adaptive_multi_user=FALSE
parallel_degree_limit=20
parallel_degree_policy='AUTO'
parallel_force_local=TRUE
parallel_max_servers=32
parallel_min_servers=2
parallel_min_time_threshold=60
parallel_servers_target=28
parallel_threads_per_cpu=1
#


13. Update Environment variables for the oracle user to new userid and Oracle home and change the owner name of datafiles and other files

14. startup upgrade

15. Increase TEMP tablespace size to atleast 1.5GB after Upgrade resize to actual size

16. cd $ORACLE_HOME/rdbms/admin
    $ORACLE_HOME/perl/bin/perl catctl.pl -n  2 -l $ORACLE_HOME/diagnostics catupgrd.sql

17. Startup
18. Run utlu121s.sql --> DONE
19. Run utlrp.sql --> DONE
20. Modify Listener home and start it, following should be updated in sqlnet.ora file

for windows server after upgrade change below in sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

SQLNET.INBOUND_CONNECT_TIMEOUT = 180
SQLNET.EXPIRE_TIME = 30

# allows inbound connections from Oracle Clients version >= 12.1
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 12a
# allows outbound connections to Oracle Database Servers version >= 11.x (incl. PSU/CPU October 2012 or later)
SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 12

# Network Encryption (for inbound connections; acting as Database Server)
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)
# Network Integrity (for inbound connections; acting as Database Server)
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA1)

# Network Encryption (for outbound connections; acting as Database Client)
SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)
# Network Integrity (for outbound connections; acting as Database Client)
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA1)

21. Start db from spfile --> DONE
22. Rename the old ORACLE_HOME, pfile, spfile and listener file of Old ORACLE_HOME and Run Cockpit scan




/sysmgmt/opt/cfengine/sbin/cfagent -v -q -K



TIMEZONE UPGRADE

1. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

2. SELECT version FROM v$timezone_file;

3. purge dba_recyclebin;

4. EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv');

5. alter session set "_with_subquery"=materialize;

6. alter session set "_simple_view_merging"=TRUE;

7.  EXEC DBMS_DST.BEGIN_PREPARE(18);

8. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

9. TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

10. TRUNCATE TABLE sys.dst$affected_tables;

11. TRUNCATE TABLE sys.dst$error_table;

12. set serveroutput on

13. BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/

14. SELECT * FROM sys.dst$affected_tables;

15. SELECT * FROM sys.dst$error_table;

16. SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

17. SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';

18. SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

19. EXEC DBMS_DST.END_PREPARE;

20. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;

21. shutdown immediate;
22. startup upgrade
23. set serveroutput on;
24. set timing on;

--> execute steps 1,3,9,10,11, 4~6

25. EXEC DBMS_DST.BEGIN_UPGRADE(18);

26. SELECT * FROM sys.dst$error_table;

27. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

28. Shutdown/startup

29. alter session set "_with_subquery"=materialize;
    alter session set "_simple_view_merging"=TRUE;

30. set serveroutput on

VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/

SELECT * FROM sys.dst$error_table;

31. VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/

32. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;


33.SELECT version FROM v$timezone_file;

34. select TZ_VERSION from registry$database;

35. If output of above 2 queries differ then
update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

36. Commit;
 

Linux



ORACLE DBA INTERVIEW QUESTIONS from real companies   http://www.sakthidbtech.com
 
Oracle DBA Technical Articles                        http://www.sakthidbtech.com/blogs
 
Live Oracle dba training in Chennai                 http://www.sakthidbtech.com

Oracle Dba video Training                            http://www.sakthidbtech.com/training-videos
 
Oracle dba training contents                         http://www.sakthidbtech.com




What is UMASK?
When user creates a file or directory it will be created with  permission. The default value is 022.
If we create a file or directory the file will be created with 755 permission.
the calculation is the difference between 777 and the  umask value.
ie. 777-022=755.
If umask is 022 then
Owner will have read, write, execute permissions
Group will have read, execute permissions
Others will have read, execute permissions

How will you delete last ten days records in OS level?
$ find . –mtime -10 exec rm {} \

What is RPM? How will you install RPMS?
RPM Package Manager (originally Red Hat Package Manager, abbreviated RPM) is a package management system.
$ rpm -Uvh rpmname.rpm

How will you find how many users were connected to a database named “sss”  from linux level?
ps –ef|grep –i local|grep sss

How will you get the count of all arguments that is passed in a Shell script?
echo $#

How to check whether the last command executed successfully or not?
Echo$?
if the result is 0(zero) then the command is successfully executed  else if the number is from 1 to 255 it is failed.

From Linux how will you find how many users were connected to the database “sakthi”?
ps -ef|grep -i local|grep -i sakthi|wc -l

How will you view Alertlog file from different directory?
tail -f /home/oracle/db/bdump/alertlog.log

How will you see  all the arguments in shell script?
echo $*

How will you configure host name and ipaddress?
Connect to root user and use neat command to add the hostname and ipaddress. Then restart the network service using
# service network restart
In /etc/hosts add the ipaddress and hostname.

How will execute a shell script in background?
./script.sh &
or
nohup ./script.sh &
which will create a nohup.out in the current directory.

Where will you find the OS level errors
In /var/log/messages and
$ dmesg

How to view last 15 lines of a file?
$Tail -15 filename

How to copy a folder from one server to another
First compress the folder using tar command
$tar -zcvf directory_name.tar.gz directory_name
Scp oracle@192.168.1.140:/home/sss/directory_name.tar.gz oracle@192.168.1.130:/home/oracle/sss/
or
using FTP you can transfer a file
or using third party utilities like winscp, Wsftp etc.

How to edit the crontab? How will you schedule on a file January 26 10:30 am?
crontab -e Edit your crontab file, or create one if it doesn’t already exist.
crontab -l Display your crontab file.
crontab -r Remove your crontab file.
crontab -v Display the last time you edited your crontab file
Jan 26 10:30 am assign one job in crontab
30 10 26 1 * . text.sh

How to find the size of a directory and a file?
$du –sh directory_name
$ls -lrth filename

How will you find the modified the files in a home directory?
$find $HOME –type f –mtime 0
$find ~ –type f –mtime 0

What is Chmod –R?
chmod -R 755 directory (-R set permissions recursively , ie changes the permission to current directory,sub direstory and files)

How will you find obsolete path of “sqlplus”?
$which sqlplus
$type sqlplus

What are the Compress commands?
Gzip
Zip
Tar
Compress
cpio

Add two numbers in a shell script?
Echo `expr $1 + $2`——–arguments we pass two values
echo ‘$*’—————–list of arguments
echo ‘$#’—————-total number of count
echo ‘$0’————–output first word
How will you solve “filename.sh permission denied” in linux ?
$ chmod +x filename.sh

What will happen when you  execute root.sh?
It’s used to create /etc/oratab file
Set the environment variables (ORACLE_OWNER,ORACLE_HOME)
Intimate the environment variables to bin.

How do you see how many instancesare running in Linux?
ps -ef|grep pmon|grep -v grep|wc -l

Given an error number,  how will you see description of the error and action in OS level?
oerr ora 0060
00060, 00000, “deadlock detected while waiting for resource”
// *Cause: Transactions deadlocked one another while waiting for resources.
// *Action: Look at the trace file to see the transactions and resources
// involved. Retry if necessary.

What is SAR? (System activity report) ?
The sar (system activity reporter) command is useful for displaying both current and historical processor load. Use sar with the -u option to report on CPU statistics.
By default, sar will report on the current day’s activities:
$ sar -u
To report on the previous day’s worth of CPU statistics, use the -f option. The files that sar uses to report on statistics for different days of the month are located in the /var/log/sa directory and have the naming convention of saNN,where NN is the two-digit day of the month. For example, to have sar display CPU statistics for the tenth day of the month, run it as follows:
$ sar -u -f /var/log/sa/sa10
02:40:01 PM CPU %user %nice %system %iowait %idle
02:50:01 PM all 0.22 0.00 0.24 0.00 99.54
03:00:01 PM all 0.22 0.00 0.24 0.00 95.53
03:10:01 PM all 0.22 0.00 0.23 0.00 99.55
03:20:01 PM all 0.42 0.00 1.06 2.11 96.41
03:30:01 PM all 0.24 0.00 1.22 0.01 92.54
Average: all 0.19 0.00 0.19 0. 07 99.55
The columns in the prior output have the same meaning as the mpstat output A low %idle could be an indication that the CPUs are underpowered or indicative of a high application load.
$ sar -u 2 20
To use sar to report on the current day’s CPU activity, simply specify the -u option:
$ sar -u
To use sar to report on a previous day in the month, use the -f option. See the examples in
the “Solution” section of this recipe for techniques for reporting on a previous day’s statistics.
If you have multiple CPUs, you can view the output per CPU with the -P ALL options. You
should now see one line per CPU in the output:
$ sar -u -P ALL
Here is a partial listing of the output:
04:30:01 PM 0 0.10 0.00 0.01 0.00 99.99
04:30:01 PM 1 0.11 0.00 0.01 0.00 99.98
To display paging activity:
sar -B 3 100
To display swapping activity:
sar -W 3 100
To display block I/O activity:
sar -b 3 100
To display block I/O activity for each block device:
sar -d 3 100
To display network activity:
sar -n DEV 3 100

How will you remove memory segment in linux?
There are two instances currently running
[oracle@sss-oracle ~]$ ps -ef|grep pmon
oracle    4772     1  0 11:12 ?        00:00:00 ora_pmon_test
oracle    2753     1  0 14:12 ?        00:00:00 ora_pmon_sakthi
oracle   13149 13009  0 15:54 pts/1    00:00:00 grep pmon
use $ipcs to view the memory segments
[oracle@sss-oracle ~]$ ipcs
—— Shared Memory Segments ——–
key        shmid      owner      perms      bytes      nattch     status
0×00000000 3375105    oracle    600        393216     2          dest
0×00000000 3407875    oracle    600        393216     2          dest
0×00000000 3440644    oracle    600        393216     2          dest
0×00000000 3473413    oracle    600        393216     2          dest
0×00000000 3506182    oracle    600        393216     2          dest
0×00000000 3899399    oracle    600        393216     2          dest
0×00000000 3571720    oracle    600        393216     2          dest
0×00000000 3604489    oracle    600        393216     2          dest
0×00000000 3637258    oracle    600        393216     2          dest
0x0a2b21c4 3702795    oracle    660        633339904  21
0×51417090 3768332    oracle    640        289406976  15
—— Semaphore Arrays ——–
key        semid      owner      perms      nsems
0x4c0bf348 229376     oracle    660        104
0xa70c84e8 360449     oracle    640        154
—— Message Queues ——–
key        msqid      owner      perms      used-bytes   messages
[oracle@sss-oracle ~]$ sysresv -l “sakthi”
IPC Resources for ORACLE_SID “sakthi” :
Shared Memory:
ID              KEY
3768332         0×51417090
Semaphores:
ID              KEY
360449          0xa70c84e8
Oracle Instance alive for sid “sakthi”
[oracle@sss-oracle ~]$ ipcrm -s 360449
The memory segment is removed.
[oracle@sss-oracle ~]$ ps -ef|grep pmon
oracle    4772     1  0 11:12 ?        00:00:00 ora_pmon_test
oracle   13149 13009  0 15:54 pts/1    00:00:00 grep pmon

How will you create a swap size in your environment? And how will you find free space in swap and RAM in your environment?
To find RAM size
[oracle@sss-oracle ~]$ grep MemTotal /proc/meminfo
MemTotal:      1035064 kB
[oracle@sss-oracle ~]$ grep SwapTotal /proc/meminfo
SwapTotal:     2097144 kB
[oracle@sss-oracle ~]$ free -l
total       used       free     shared    buffers     cached
Mem:       1035064     867052     168012          0      89100     592468
Low:        904056     761404     142652
High:       131008     105648      25360
-/+ buffers/cache:     185484     849580
Swap:      2097144        820    2096324
If you’re short on swap space, you can temporarily add a swap file to your server. As the root user, run the following commands to add approximately 1GB of swap space:
# dd if=/dev/zero of=tempswap bs=1k count=1000000
# chmod 600 tempswap
# mkswap tempswap
# swapon tempswap
Verify that the swap space was added with the -s option of the swapon command:
# swapon -s
To remove the temporary swap file, as root run the following commands:
# swapoff tempswap
# rm tempswap
After disabling the swap file, you should see the swap space in /proc/meminfo return to its original value.


How will you print the 25th line in your file?
$ cat -n filename|grep 25
or
$ head -25 filename | tail -1

Write a shell script to  check diskspace/mountpoint limit 

#######################################################################
#
# check_FS.sh
#
# Script for checking mountpoint space by passing threshold limit as argument
#From Sakthi Software Solutions Pvt Ltd
#
#########################################################################

for i in `df -P|grep -v "File"|tr -s " " "~"|cut -d"~" -f5|tr -s "%" " "`
do
if [ $i -gt $1 ]
then
echo $i
fi
done


OR

#######################################################################
#
# check_FS.sh
#
# Script for checking mountpoint space by passing threshold limit as argument
#From Sakthi Software Solutions Pvt Ltd
#
#########################################################################

for i in `df -h|grep -v "File"|awk {"print $5"}|tr -s "%" " "`
do
if [ $i -gt $1 ]
then
echo $i
fi
done

OUTPUT :

[oracle@sss-oracle ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       18G   13G  3.7G  78% /
/dev/sda1              99M   12M   82M  13% /boot
tmpfs                 506M     0  506M   0% /dev/shm
/dev/sdb1              20G   15G  4.6G  76% /u02
.host:/               202G  156G   46G  78% /mnt/hgfs

[oracle@sss-oracle ~]$ ./check_FS.sh 20
78
76
78

With sendmail OPTION

 #######################################################################
#
# check_FS.sh
#
# Script for checking mountpoint space by passing threshold limit as argument
#
#From Sakthi Software Solutions Pvt Ltd
#
#########################################################################

for i in `df -P|grep -v "File"|tr -s " " "~"|cut -d"~" -f5|tr -s "%" " "`
do
if [ $i -gt $1 ]
then
echo "date: `date`" > /tmp/check_FS.log
echo "to: test@oraclehost.oracledomain" >> /tmp/check_FS.log
echo "subject: WARNING! - FileSystem" >> /tmp/check_FS.log
#echo "from: `hostname`" >> /tmp/check_FS.log
echo "" >>/tmp/check_FS.log
echo " Filesystem Threshold Check : " >> /tmp/check_FS.log
echo " -----------------------------    " >> /tmp/check_FS.log
echo "" >>/tmp/check_FS.log
echo "" >>/tmp/check_FS.log
echo  `df -h |grep $i%` >> /tmp/check_FS.log
/usr/sbin/sendmail -t test@oraclehost.oracledomain < /tmp/check_FS.log
fi
done 
crontab -l

# check for every 5 minutes with threshold value 90

*/5 * * * * /home/oracle/scripts/check_FS.sh 90


Write a shell script to  check analyzed objects

#######################################################################
#
#
# check_analyze.sh
#
# Script for checking analyzed objects
#
# From Sakthi Software Solutions Pvt Ltd
#
#########################################################################
for i in `echo $*`
do
echo " "
echo " "
echo "---------- Analyzed status of  $i   -------------"
echo " "
echo " "

sqlplus -s /nolog <<EOF
conn / as sysdba
select table_name,last_analyzed from dba_tables where owner=upper('$i');
exit
EOF
done

output:

[oracle@sss-oracle ~]$ ./check_analyze.sh test scott


---------- Analyzed status of  test   -------------



TABLE_NAME                     LAST_ANAL
------------------------------ ---------
A



---------- Analyzed status of  scott   -------------



TABLE_NAME                     LAST_ANAL
------------------------------ ---------
DEPT                           30-AUG-12
EMP                            30-AUG-12
BONUS                          30-AUG-12
SALGRADE                       30-AUG-12
B                              30-AUG-12


Write a shell script to analyze schema

#######################################################################
#
# analyze.sh
#
# Scripts to analyze schema/s , where schema name is passed as argument
#
# From Sakthi Software Solutions Pvt Ltd
#
#########################################################################
for i in `echo $*`
do
echo " "
echo " "
echo "---------- Analyzing schema  $i   -------------"
echo " "
echo " "

sqlplus -s /nolog <<EOF
conn / as sysdba
exec dbms_stats.gather_schema_stats('$i');
exit
EOF
done

Output : 


$ ./analyze.sh scott test

---------- Analyzing schema scott   -------------


PL/SQL procedure successfully completed.


---------- Analyzing schema  test   -------------


PL/SQL procedure successfully completed.



#######################################################################
#
# Usage : ./check_db.sh
#
# Script for checking status of databases in a server
#
# From Sakthi Software Solutions Pvt Ltd
#
#########################################################################
for i in `ps -ef|grep pmon | awk '{print $8}'|grep -v "grep"|cut -d"_" -f3`
do
export ORACLE_SID=$i
sqlplus -s /nolog <<EOF >/tmp/temp.sql
set head off echo off
conn / as sysdba
select decode(open_mode,'READ WRITE','OPEN','MOUNT') from v\$database;
exit
EOF
for x in `cat /tmp/temp.sql |egrep "OPEN|MOUNT|ORA-01507"|grep -v "^sel"|awk {'print $1}'`
do
if [ "$x" = "ORA-01507:" ]
   then
echo "Database $i in NOMOUNT stage"
else
echo  "Database $i in $x stage"
fi
done
rm /tmp/temp.sql
done


OUTPUT :

[oracle@sss-oracle ~]$ ps -ef|grep pmon
oracle    6061     1  0 16:10 ?        00:00:00 ora_pmon_sss
oracle   16682     1  0 21:04 ?        00:00:00 ora_pmon_sakthi
oracle   16758  7821  0 21:05 pts/1    00:00:00 grep pmon

[oracle@sss-oracle ~]$ ./check_db.sh
Database sss in OPEN stage
Database sakthi in NOMOUNT stage


==================================================================================================
=====================================================================================================
CHECK startup and shutdown messages from the alert log.


diff  alert_test.log alert_test.bkp |egrep  "immediate|normal|abort|transactional|Starting ORACLE instance"|egrep -i "Shut|Start" > /tmp/DBDown.log
x=`diff  alert_test.log alert_test.bkp |egrep  "immediate|normal|abort|transactional|Starting ORACLE instance"|egrep -i "Shut|Start"|wc -l`
 if [ $x -gt 0 ]
 then
  echo $x
else
  echo "$x"
 fi
cp alert_test.log alert_test.bkp


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

About Linux File system

/ - the topmost
/dev - all the devices are accessible as files
 /var - “variable” data such as mails, log files, databases
 /usr - almost all the packages installed
 /etc - configuration files
 /home - home directories for all the users
 /root - home directory of the privileged user root
 /mnt - used to mount other directories/partitions


Compression
tar -cvf, -tvf, -xvf, -zcvf, -zxvf
zip -r
compress
gzip
background jobs
nohup
fg jobs
jobs
bg jobs
System log commands
Dmesg
Cat  var/log/messages
Other Commands
pwd
cal month year
clear
sleep 2
date
alias
unalias
User and Process
Free
Kill
Mail
Man
Ping
Reboot
Shutdown –h now
Shutdown –r minutes
sync - completes all pending I/O Operations
telnet
top
traceroute host
uptime
w - current system users
su
passwd
who
wall - sends message to users
ps –ef, aux, u
ssh
fdisk
mount
umount
du
df
quota
reboot
poweroff
# useradd chuck
# passwd chuck
# userdel -r chuck
Who -r
Viewing commands
ls -l
ls -a
ls -lrt
ls -d

ls  –A option makes ls show files beginning with . but eliminates the . and .. files from the display.

–t option is used to sort the output of ls by the time the file was modified

 –u option will give the time the file was last used or accessed.
–S option displays files by their size, in descending order.

–r option reverses the display, sorting sizes in ascending order.
Ownership/Permission commands
umask
chown -R
chmod 777
chmod +t
File Search commands
locate
whereis
which
find  path -name -print -exec –mtime
Wild Crad characters
*,?
Echo
Grep –lr, -v , -i

Finding files older than 20 years

# find ./ -mtime +7300

Finding Any Files Modified in the Past 3 Days

$ find ./ -mtime -3

Finding .txt Files Modified in the Past 3 Days

$ find . -name '*.txt' -mtime -3

Finding Files Larger than 10,000k

# find . -size +10000k

Deleting Files Using File Properties
 to delete all files within a directory that have not been used or accessed in more than 30 days
$ find . –maxdepth 1 -atime +30 -exec rm {} \;


Directory commands
mkdir
mkdir -p (create the directories as mentioned recursively)
rm -rf
rm -i
rmdir
cd ..
cd ~
cd

File Manipulation
mv
cp
cp -i (checks existance of files)
cp -d(links)p(permissions) r(recursive
cat
cat >
cat >>
more
less
cmp f1 f2
comm f1 f2
diff f1 f2
diff -y
-q - says whether difference or not but not how
-r
-i
wc -l -c –w
ln –s
file  - describes the file types
scp
split file
cut -c, -d "" -f1
sort –r,-t,-n
sort -t" "  -k 2 (sorts the file based on the second field using the delimiter as space)
paste f1 f2

Other important commands
What is Xargs?
Xargs

Most Linux commands are about getting an output: a list of files, a list of strings, and so on. But what if you want to use some other command with the output of the previous one as a parameter? For example, the file command shows the type of the file (executable, ascii text, and so on); you can manipulate the output to show only the filenames and now you want to pass these names to the ls -l command to see the timestamp. The command xargs does exactly that. It allows you to execute some other commands on the output. Remember this syntax from Part 1:
file -Lz * | grep ASCII | cut -d":" -f1 | xargs ls -ltr
Let's dissect this command string. The first, file -Lz *, finds files that are symbolic links or compressed. It passes the output to the next command, grep ASCII, which searches for the string "ASCII" in them and produces the output similar to this:
alert_DBA102.log:        ASCII English text
alert_DBA102.log.Z:      ASCII text (compress'd data 16 bits)
dba102_asmb_12307.trc.Z: ASCII English text (compress'd data 16 bits)
dba102_asmb_20653.trc.Z: ASCII English text (compress'd data 16 bits)
Since we are interested in the file names only, we applied the next command, cut -d":" -f1, to show the first field only:
alert_DBA102.log
alert_DBA102.log.Z
dba102_asmb_12307.trc.Z
dba102_asmb_20653.trc.Z
Now, we want to use the ls -l command and pass the above list as parameters, one at a time. The xargs command allowed you to to that. The last part, xargs ls -ltr, takes the output and executes the command ls -ltr against them, as if executing:
ls -ltr alert_DBA102.log
ls -ltr alert_DBA102.log.Z
ls -ltr dba102_asmb_12307.trc.Z
ls -ltr dba102_asmb_20653.trc.Z
Thus xargs is not useful by itself, but is quite powerful when combined with other commands.

So what does load average from top command  indicate?
load averages: 2.43, 2.96, 3.41
one minute load average is 2.43
five minute is 2.96
fifteen minute load average is 3.41.
Here are some conclusions we can draw from this.
On average, over the past one minute there have been 2.43 processes running or waiting for a resource
Overall the load is on a down-trend since the average number of processes running or waiting in the past minute (2.43) is lower than the average running or waiting over the past 5 minutes (2.96) and 15 minutes (3.41)
This system is busy, but we cannot conclude how busy solely from load averages.
It is important here to mention that the load average does not take into account the number of processes. Another critical detail is that processes could be waiting for any number of things including CPU, disk, or network.
So what we do know is that a system that has a load average significantly higher than the number of CPUs is probably pretty busy, or bogged down by some bottleneck. Conversely a system which has a load average significantly lower than the number of CPUs is probably doing just fine.

What is sticky bit?
Used for shared directories to prevent users from renaming or deleting each others’ files.  The only users who can rename or delete files in directories with the sticky bit set are the file owner, the directory owner, or the super-user (root).  The sticky bit is represented by the letter t in the last position of the other permissions display.
SUID

Set user ID, used on executable files to allow the executable to be run as the file owner of the executable rather than as the user logged into the system.
SGID
Set group ID, used on executable files to allow the file to be run as if logged into the group (like SUID but uses file group permissions).
$ chmod +t public
or
$ chmod 1777 public
where the SUID and SGID options come in.
chmod u+s,g+s drop_box
or
 chmod 6777 drop_box

Finding Process Information by Process ID
$ ps u 4444
Find Processes Belonging to a Specific User
$ ps -u bb

How will you find CPU usage and what will you see in that?
Display the Most Active Processes
The top command provides a dynamic display of the current activity within a Linux system.  By default top will list running processes owned by all users.  The processes which are currently using the most CPU are listed first and top will list as many processes as will fit on the screen.
$ top
top - 22:58:28 up  4:49,  2 users,  load average: 0.03, 0.08, 0.05
Tasks:  79 total,   1 running,  78 sleeping,   0 stopped,   0 zombie
Cpu(s):  22.3% user,   5.6% system,   0.0% nice,  72.1% idle
Mem:    255656k total,   238208k used,    17448k free,    23460k buffers
Swap:   506008k total,        0k used,   506008k free,    96400k cached
                                                                                                                
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  Command
18325 root      17   0 41524  16m 1952 S 23.6  6.5   5:10.99 X
21234 terry     12   0 10648  10m 7180 S  2.6  4.2   0:33.20 gnome-terminal
11784 root      14   0   984  984  772 R  1.6  0.4   0:00.46 top
    1 root       9   0   500  500  448 S  0.0  0.2   0:05.12 init
    2 root       9   0     0    0    0 S  0.0  0.0   0:00.06 keventd
    3 root       9   0     0    0    0 S  0.0  0.0   0:00.15 kapmd
    4 root      18  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd_CPU0
    5 root       9   0     0    0    0 S  0.0  0.0   0:00.00 kswapd
    6 root       9   0     0    0    0 S  0.0  0.0   0:00.00 bdflush
    7 root       9   0     0    0    0 S  0.0  0.0   0:00.14 kupdated
    8 root      -1 -20     0    0    0 S  0.0  0.0   0:00.00 mdrecoveryd
   12 root       9   0     0    0    0 S  0.0  0.0   0:00.33 kjournald
13282 root       9   0     0    0    0 S  0.0  0.0   0:00.00 khubd
14301 root       9   0     0    0    0 S  0.0  0.0   0:00.00 kjournald
 8235 root       9   0   968  968  696 S  0.0  0.4   0:00.00 dhclient
13016 rpc        9   0   536  536  464 S  0.0  0.2   0:00.01 portmap
 1867 root       9   0   624  624  520 S  0.0  0.2   0:00.25 syslogd
 9038 root       9   0  1312 1312  436 S  0.0  0.5   0:00.32 klogd
27920 xfs        9   0  3724 3724  952 S  0.0  1.5   0:00.25 xfs
 4918 root       9   0  3096 3096 2952 S  0.0  1.2   0:00.18 gdm-binary
31982 daemon     9   0   504  504  444 S  0.0  0.2   0:00.00 atd
14666 root       9   0  1360 1360 1148 S  0.0  0.5   0:00.14 sshd
14744 root       9   0   876  876  756 S  0.0  0.3   0:00.02 xinetd
18588 root       9   0  4160 4160 3728 S  0.0  1.6   0:00.84 gdm-binary
22969 root       9   0  2040 2040 1296 S  0.0  0.8   0:01.08 cupsd
24020 lp         9   0  4232 4232 3756 S  0.0  1.7   0:07.72 foomatic-rip
22081 root       9   0  2332 2332 1164 S  0.0  0.9   0:00.58 smb
17197 root       9   0   452  452  416 S  0.0  0.2   0:00.00 rwhod
29921 root       9   0   552  552  508 S  0.0  0.2   0:00.07 rwhod
27808 lp         9   0  4236 4236 4056 S  0.0  1.7   0:00.01 foomatic-rip
Once you are done in top you just need to type q to quit and return to the command line.
Here we see the default top output.  There's a lot here, but here are a few of the highlights.  Some of this will be familiar from the ps output.
top output field
Description
up
How long this system has been running.  May be in days
users
Number of connected users
load average
Number of processes currently running plus the number waiting to run over one, five and fifteen minutes*.
Tasks (or processes)
The total number of active processes
CPU
   user
   system
   idle
   iowait (not shown)
How the CPU is currently being used
The percent of CPU consumed by user processes
The percent of CPU consumed by system processes
The percent of CPU not currently being used
The percent of CPU spent waiting for data (typically disk)
Mem
   total
   used
   free
Memory usage information
The total amount of memory in this system
Memory currently used in the system
Unused memory
Swap
   total
   used
   free
Information about disk being used as memory
Total amount of disk assigned for use as memory
Swap currently in use
Unused swap
PID
Process ID number of this process
USER
Username of the process owner
%CPU
Percent of CPU currently being used by this process
%MEM
Percent of total memory being used by this process
TIME
Total CPU time consumed by this process
Command
The command which was executed (truncated)

* The calculation is more complicated than this, but this simple explanation will suffice for now.  Generally a system with a load average lower than its number of CPUs is keeping up with its work.  One with a load average of one to two times its number of CPUs is starting to fall behind and things aren't getting done as quickly as they are coming in, but a load average of several times the number of CPUs typically indicates the system is bogged down.  Of course user experience is the best indicator of system performance.
The one, five and fifteen minute load averages are useful for identifying the trend of the system.  If the one minute load average is lower than the five and fifteen minute averages the system has probably finished some large tasks and the system is getting less busy than it previously was.  If the one and five minute averages are higher it is likely the system is getting busier than it was fifteen minutes ago.
While running top there are several commands which will change what top shows you.  Some common ones are:
* Spacebar: Refreshes the display
* h: Display the help screen
* k: Kills a process*
* n: Changes the number of processes displayed
* u: Specify what user's tasks to view (blank for all users)
* p: Sorts tasks by CPU usage
* s: Change the number of seconds between refreshing
* q: Quit top
*We'll talk more about killing processes later in this chapter.  For now you probably don't want to use this command.  The rest of them you can go ahead and experiment with.
As with most other commands, top can be started with a number of different options.  The most commonly used option when logged in as root is the -u option to limit the active process display to a particular user.
$ top –u terry
top - 23:20:22 up  5:11,  2 users,  load average: 0.33, 0.17, 0.06
Tasks:  79 total,   2 running,  77 sleeping,   0 stopped,   0 zombie
Cpu(s):  18.8% user,   4.2% system,   0.0% nice,  77.0% idle
Mem:    255656k total,   240748k used,    14908k free,    24772k buffers
Swap:   506008k total,        0k used,   506008k free,    96796k cached
                                                                                                                           
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  Command
21462 terry      9   0 12440  12m 8332 S  2.4  4.9   0:15.11 gnome-panel --sm-client-id default2
21234 terry      9   0 12380  12m 8160 S  1.8  4.8   0:47.21 /usr/bin/gnome-terminal
 4029 terry      9   0  7476 7476 5040 S  1.4  2.9   0:30.15 /usr/bin/metacity --sm-client-id=default1
 7416 terry      9   0 27600  26m  10m S  1.0 10.8   0:32.55 nautilus --no-default-window --sm-client-id default3
 6513 terry      9   0 11736  11m  10m S  0.2  4.6   0:02.20 kdeinit: kded
 3696 terry      9   0  8724 8720 5876 S  0.0  3.4   0:01.79 gnome-session
 4054 terry      9   0  7224 7224 1988 S  0.0  2.8   0:01.79 /usr/lib/gconfd-2 11
25812 terry      9   0  2472 2472 1904 S  0.0  1.0   0:00.39 /usr/lib/bonobo-activation-server --ac-activate --ior-output-
11467 terry      9   0  2676 2676 2148 S  0.0  1.0   0:00.88 gnome-smproxy --sm-client-id default0
13021 terry      9   0  6420 6416 5092 S  0.0  2.5   0:00.97 gnome-settings-daemon --oaf-activate-iid=OAFIID:GNOME_Settings
11135 terry      9   0  1744 1744 1140 S  0.0  0.7   0:06.07 [fam]
25609 terry      9   0 27600  26m  10m S  0.0 10.8   0:00.19 nautilus --no-default-window --sm-client-id default3
31992 terry      9   0 27600  26m  10m S  0.0 10.8   0:00.58 nautilus --no-default-window --sm-client-id default3
11456 terry      9   0 27600  26m  10m S  0.0 10.8   0:01.55 nautilus --no-default-window --sm-client-id default3
 2216 terry      9   0 27600  26m  10m S  0.0 10.8   0:00.00 nautilus --no-default-window --sm-client-id default3
18785 terry      9   0  6920 6916 5604 S  0.0  2.7   0:00.69 /usr/lib/notification-area-applet --oaf-activate-
id=OAFIID:GN
30999 terry      9   0  4228 4228 3508 S  0.0  1.7   0:01.08 pam-panel-icon --sm-client-id default4
 8494 terry      9   0 17724  17m  13m S  0.0  6.9   0:30.34 kwrite
 4890 terry      8   0  7888 7888 7684 S  0.0  3.1   0:00.03 kdeinit: Running...
16796 terry      9   0  8380 8380 8080 S  0.0  3.3   0:00.08 kdeinit: dcopserver --nosid --suicide
29590 terry      9   0  8924 8924 8540 S  0.0  3.5   0:00.21 kdeinit: klauncher
23583 terry      9   0  8524 8524 8188 S  0.0  3.3   0:00.23 kdeinit: kio_file file /tmp/ksocket-
erry/klauncherjEhebb.slav
17263 terry      9   0   576  576  504 S  0.0  0.2   0:00.02 [gnome-pty-helpe]
14241 terry      9   0  1540 1540 1096 S  0.0  0.6   0:00.28 bash
Here we see the top processes owned by the user terry.  As you can see, the –u option works very similarly with top as it did with ps.
While you are running top you can
Now that we know how to track down running processes we might find some we need to do away with.  In the next section we'll see how to kill processes.  Be careful with these commands, especially if you have root privileges!
The vi editor provides commands for deleting or replacing single characters, single or multiple words, and single or multiple lines of text.  Table 9.4 shows some common delete and replace commands with their associated actions.  Remember you need to be in command mode to use these.
Command
Action
x
Delete one character of text
r
Replace one character of text with the next character entered
dw
Delete entire word (3dw deletes 3 words)
dd
Delete entire line (3dd deletes 3 lines)
D
Delete from cursor to end of line
s
Switch to insert mode after deleting current character
cw
Delete entire word and switch to insert mode
cc
Delete entire line and switch to insert mode
C
Change (delete and switch to insert mode) from cursor position to end of line

Table 9.4: Delete or replace text commands
Searching for Text Strings
The vi editor allows the user to search for a text string either forward (down) in the file or backward (up).  It also allows a shortcut for repeating the search.   A special search capability that comes in handy when writing shell scripts is the ability to search for matching parentheses, brackets, and braces.  Table 9.5 below shows some search commands and their associated actions.
Command
Action
/text
Search forward in the file for text
?text
Search backward (up) in the file for text
%
With the cursor on a parentheses, bracket, or brace character, you can press the % key to move the cursor to its matching open or close character

Table 9.5: Search commands and their associated actions
Cutting, Copying, and Pasting Text
Any of the text deleting commands presented earlier work similarly to the cut feature of Windows in that they place the deleted text in a buffer area for potential retrieval at a later time. In Windows, the buffer is referred to as the clipboard. 
In vi there is a unnamed default buffer and 26 specifically referenced buffers, each identified by one of the letters of the alphabet (a, b, c, d, etc.).  In order to reference one of the named buffers, the buffer identifier character is preceded with a single open quotation.  So, “a refers to buffer a, “b refers to buffer b, and so on.
When one of the delete commands is used, the text is cut from the document and placed in the default buffer.  To retrieve text from the default buffer, the upper case P command can be used to paste the default buffer before the current cursor line, or the lower case p command can be used to paste the contents of the buffer after the current line.  Therefore, a 5dd command followed by a cursor movement and a P command would cut five lines of text and place them before the line where the cursor was moved.
Instead of cutting or deleting text, it is also possible to copy text from a file using the yank (yy) command.  The yank command presents the option of copying text to one of the specific named buffers.  Where yy would copy the current line to the unnamed default (unnamed), “cyy would copy the current line to the buffer named c.
The issuance of multiple yank commands to the same buffer without intervening paste commands will result in buffer overwrites.  In other words, the user cannot yank line five to buffer a, then yank line seven to buffer a and expect to be able to paste both lines five and seven somewhere.  When a user yanks line five, it is placed in buffer a as requested, but when a command to yank line 7 to buffer a follows, line 7 will overwrite line five, which is sitting in the buffer.  This is one of the reasons for providing multiple named buffers to use for multiple successive yanks.  Table 9.6 shows copy and paste commands and their associated actions.
Command
Action
yy
Copy (yank) the current line of text into the default (unnamed) buffer
“byy
Copy (yank) the current line of text into the buffer named b
5yy
Copy five lines of text to the default buffer
p
Paste the default buffer after the current cursor line
P
Paste the default buffer before the current cursor line
“bP
Paste the contents of named buffer b before the current cursor line

Table 9.6: Copy and paste commands and their actions
Undo and Other Useful Commands
Table 9.7 shows some additional miscellaneous commands and their associate actions.  Most important may be the u command which will undo the last change that was made.  In most vi editors you can undo several of the most recent commands.
Command
Action
J
Join the current cursor line with the next line in the file
Enter
Split the current line at the cursor position when in insert mode.
u
Undo the last change that was made
U
Undo any changes made to the current cursor line
:r filename
Read the file named filename and insert it below the current cursor line

Table 9.7: Miscellaneous commands and their associated actions
vi Reference
For your convenience we have compiled the tables of vi commands together for quick reference.
Command
Action
a
Append text to the right of the cursor
i
Insert text to the left of the cursor
o
Insert a new line below the current line
A
Append text to the end of the current line
I
Insert text at the beginning of the current line
O
Insert a new line above the current line

Insert Commands
To return to command mode from insert mode use the escape key.
Command
Action
:w
Write the file to disk
:wq
Write the file to disk and quit the editor
<shift>ZZ
Same as :wq
:w! newfile
Write the file to a new disk file called newfile

Write Commands:
Command
Action
h
Move cursor one position to the left (left arrow)
j
Move cursor one line down (down arrow)
k
Move cursor one line up (up arrow)
l
Move cursor one position to the right (right arrow)
^
Move to the beginning of the current line
$
Move cursor to the end of the current line
b
Move to beginning of previous word
w
Move to beginning of next word
e
Move to end of next word
G
Move to end of the file
:n
Move to line n
Enter
Move to the first word one the next line
ctrl+b
Page backward (up)
ctrl+f
Page forward (down)
Cursor Movement:


Command
Action
x
Delete one character of text
r
Replace one character of text with the next character entered
dw
Delete entire word (3dw deletes 3 words)
dd
Delete entire line (3dd deletes 3 lines)
D
Delete from cursor to end of line
s
Switch to insert mode after deleting current character
cw
Delete entire word and switch to insert mode
cc
Delete entire line and switch to insert mode
C
Change (delete and switch to insert mode) from cursor position to end of line
Delete and Replace Commands
Command
Action
/text
Search forward in the file for text
?text
Search backward (up) in the file for text
%
With the cursor on a parentheses, bracket, or brace character, you can press the % key to move the cursor to its matching open or close character
Search Commands
Command
Action
yy
Copy (yank) the current line of text into the default buffer
“byy
Copy (yank) the current line of text into the buffer named b
5yy
Copy five lines of text to the default buffer
p
Paste the default buffer after the current cursor line
P
Paste the default buffer before the current cursor line
“bP
Paste the contents of named buffer b before the current cursor line
Copy and Paste Commands
Command
Action
J
Join the current cursor line with the next line in the file
Enter
Split the current line at the cursor position when in insert mode.
u
Undo the last change that was made
U
Undo any changes made to the current cursor line
:r filename
Read the file named filename and insert it below the current cursor line
Undo and Miscellaneous Commands

crontab Options



Option  Purpose

-e      edit the current crontab file using the text editor specified by the EDITOR environment variable or the VISUAL environment variable

-l      list the current crontab file

-r       remove the current crontab file

-u       specifys the user’s crontab to be manipulated. This is usually used by root to manipulate the crontab of other users or can be used by you to correctly identify the crontab to be manipulated if you have used the su command to assume another identity.

Field         Valid values
 Minute        0-59
 Hour          0-23
 Day of Month  1-31
 Month         1-12
 Day of Week   0-7

 Examples :
#**********************************************************
# Run the Weekly file cleanup task at 6:00AM every Monday  and send any output to a file called cleanup.lst in the /tmp directory
#**********************************************************
00 06 * * 1 /home/terry/cleanup.ksh > /tmp/cleanup.lst
#**********************************************************
# Run the Weekly Management Report every Monday at 7:00 AM
# and save a copy of the report in my /home directory
#**********************************************************
00 07 * * 1 /home/terry/weekly_mgmt_rpt.ksh wprd > /home/terry/weekly_mgmt_rpt.lst

#**********************************************************
# Weekly Full Backup - run every Sunday at 1:30AM
#**********************************************************
30 01 * * 0 /home/terry/full_backup.ksh wprd > /tmp/full_backup.lst
#**********************************************************
# Nightly Incremental Backup - run Monday-Saturday at 1:30AM
#**********************************************************
30 01 * * 1-6 /home/terry/incr_backup.ksh  > /tmp/incr_backup.lst
#**********************************************************
# Low disk space alert ... run every 15 minutes, sending
# alerts to key individuals via e-mail
#**********************************************************
00,15,30,45 * * * * /home/terry/free_space.ksh > /tmp/free_space.lst
#**********************************************************
# Lunch Time Notification - run Monday-Friday at Noon -
# sends a message to all users indicating it's lunch time
#**********************************************************
00 12 * * 1-5 /home/terry/lunch_time.ksh wprd > /tmp/lunch_time.lst

Field                   Valid Entries (case insensitive)

Days of the week        sun, mon, tue, wed, thu, fri, sat
                                  SUN, MON, TUE, WED, THU, FRI, SAT
 Months of year          jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec
                                JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
init commands

* 0: Used to halt the system

* 1: Single-user mode for performing administrative tasks

* 2: Multi-user mode, without networking

* 3: Multi-user mode, with networking

* 4: Currently unused

* 5: Multi-user mode with graphical user interface (GUI), usually the default runlevel

* 6: Used to reboot the system


Ps command
$ ps –ef

UID        PID  PPID  C STIME TTY          TIME CMD
terry     3696 18588  0 18:10 ?        00:00:01 gnome-session
terry     4054     1  0 18:10 ?        00:00:01 /usr/lib/gconfd-2 11
terry    25812     1  0 18:10 ?        00:00:00 /usr/lib/bonobo-activation-serve
terry    11467     1  0 18:10 ?        00:00:00 gnome-smproxy --sm-client-id def
terry    13021     1  0 18:10 ?        00:00:00 gnome-settings-daemon --oaf-acti
terry     1519     1  0 18:10 ?        00:00:00 xscreensaver -nosplash
terry     4029     1  0 18:10 ?        00:00:11 /usr/bin/metacity --sm-client-id
terry    21462     1  0 18:10 ?        00:00:11 gnome-panel --sm-client-id defau
terry     2216 25609  0 18:10 ?        00:00:00 nautilus --no-default-window --s
terry    18785     1  0 18:10 ?        00:00:00 /usr/lib/notification-area-apple
terry    30999     1  0 18:10 ?        00:00:00 pam-panel-icon --sm-client-id de
terry     8494     1  0 18:55 ?        00:00:13 kwrite
terry     4890     1  0 18:55 ?        00:00:00 kdeinit: Running...
terry    16796     1  0 18:55 ?        00:00:00 kdeinit: dcopserver --nosid --su
terry    29590     1  0 18:55 ?        00:00:00 kdeinit: klauncher
terry     6513     1  0 18:55 ?        00:00:01 kdeinit: kded
terry    23583  4890  0 20:37 ?        00:00:00 kdeinit: kio_file file /tmp/ksoc
terry    21234     1 21 20:55 ?        00:00:01 /usr/bin/gnome-terminal
terry    17263 21234  0 20:55 ?        00:00:00 [gnome-pty-helpe]
terry    14241 21234  2 20:55 pts/0    00:00:00 bash
terry    18504 14241  0 20:55 pts/0    00:00:00 ps –ef
...

The command ps –ef will output lots of information about all the running commands on the system.  Here's a quick explanation of what this output means:

Column   Description

UID      Username of the process owner

PID      Process ID number of this process

PPID     Process ID of the process which started this one (parent)

C        CPU utilization of this process

STIME    Start time (listed as month and day if older than 1 day)

TTY      The terminal (if any) the process was started from

TIME     The amount of CPU time this process has consumed

CMD       The command which was executed (long commands are truncated)


–u option to show processes owned by a specific user.  The following display is for the lp user:

$ ps -u lp

  PID TTY          TIME CMD
22233 ?        00:00:07 foomatic-rip
28688 ?        00:00:00 foomatic-rip
30970 ?        00:00:00 foomatic-rip
 7593 ?        00:00:00 sh
10833 ?        00:00:00 sh
 2005 ?        00:00:00 perl
32267 ?        00:00:03 gs
26226 ?        00:00:00 sh
 6930 ?        00:00:00 cat

 to see only those processes owned by a specific user is the –u option. The following are the pids for the lp user:

$ pgrep –u lp

see the process IDs for processes owned by the user lp which match the pattern foomatic.



$ pgrep –u lp foomatic
22233
28688
30970
Server monitoring commands
Some popular commands/utilities for monitoring system resources and tasks managed by the Linux kernel are as follows:

* top: Provides a dynamic real-time view of a running system, including information about system resource usage and a constantly updated list of the processes which are consuming the most resources.  Because it is so useful for administration we will talk quite a bit about top in chapter 8.

* mpstat: Reports activities for each available processor, processor zero being the first one reported.  Global average activities across all processors are also reported.

* iostat: Used for monitoring the load on system input/output devices by observing the time the devices are active compared to the average transfer rate of the device.

* vmstat: Displays information about processes, memory, paging, block IO, and different levels of CPU activity
CPU Related Information

# cat /proc/cpuinfo

Display the Number of Processors in the Server

# cat /proc/cpuinfo | grep processor | wc –l

Displaying the Total RAM on the Linux system

# cat /proc/meminfo


 free -k


Top Memory and CPU Users

# ps u


Display Top CPU User:

# ps -aux | sort –n +2 | tail -1


Display Top Memory User:

# ps -aux | sort -n +3 | tail -1

Paging and Swapping Devices
# cat /proc/swaps


#swapon -s




Displaying Multi-Processor Statistics



$ mpstat 3 5

Linux 2.6.5-1.358 (Dell-Linux)  10/18/2004
10:33:26 PM  CPU   %user   %nice %system %iowait    %irq   %soft   %idle    intr/s
10:33:29 PM  all    1.00    0.00    0.33    0.00    0.00    0.00   98.67   1001.99
10:33:32 PM  all    0.33    0.00    0.33    0.00    0.00    0.00   99.33   1007.02
10:33:35 PM  all    0.67    0.00    0.33    0.00    0.00    0.00   99.00   1002.67
10:33:38 PM  all    0.66    0.00    0.33    0.00    0.00    0.00   99.00   1000.33
10:33:41 PM  all    0.67    0.00    0.33    0.00    0.00    0.00   99.00   1005.67
Average:     all    0.67    0.00    0.33    0.00    0.00    0.00   99.00   1003.53



The columns in the report generated by the mpstat command are defined as follows:

* CPU: Either the processor number or the keyword all, which indicates that statistics are calculated as averages among all processors or that there is only one processor in the server

* %user: The percentage of CPU used by user applications

* %nice: The percentage of CPU utilization at the user level with nice priority

* %system: The percentage of CPU used by the system. This does not include the time spent servicing interrupts or softirqs. A softirq is a software interrupt, one of up to 32 software interrupts which can run on multiple CPUs simultaneously.

* %iowait: The percentage of time the system had a pending disk I/O request

* %irq: The percentage of time spent by the CPUs servicing interrupts

* %soft: The percentage of time the processors spent servicing softirqs.

* %idle: The percentage of time that the processors were idle and the system did not have a pending disk I/O request.

* intr/s: The total number of interrupts per second received by the processor(s)


Displaying I/O Statistics



$ iostat 3 5

Linux 2.6.5-1.358 (Dell-Linux)  10/18/2004

avg-cpu:  %user   %nice    %sys %iowait   %idle
           0.51    0.14    0.22    0.26   98.86

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
hda               0.00         0.02         0.00       1192         38
hdf               0.99        20.83         5.33    1405186     359616

avg-cpu:  %user   %nice    %sys %iowait   %idle
           0.67    0.00    0.33    0.00   99.00

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
hda               0.00         0.00         0.00          0          0
hdf               0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice    %sys %iowait   %idle
           0.67    0.00    0.33    0.00   99.00

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
hda               0.00         0.00         0.00          0          0


For the average CPU report, %user, %nice, %iowait, and %idle are defined the same as they were in the mpstat command output.  One remaining piece of information is defined as:

* %sys: The percentage of processor utilization occurring at the system kernel level.

For the device utilization report:

* device: The device name as listed in the /dev directory is displayed.  These device names are mapped to mount points in the file /etc/fstab and are also listed in the output of the df command.

* tps: The number of transfers (I/O requests) per second issued to the device.

* blk_read/s: The number of blocks per second read from the device.

* blk_wrtn/s: The number of blocks per second written to the device.

* blk_read: The total number of blocks read.

* blk_wrtn: The total number of blocks written.

This information can assist in the determination of which devices are more heavily used than others and perhaps help with the determination of how to better distribute data to balance the workload.

Displaying Virtual Memory Statistics

The vmstat command displays information about processes, memory, paging, block IO, and different levels of CPU activity. As with iostat, the first detail lines produce report averages since the last reboot. Subsequent detail lines report information using the interval specified on the command line.

As with the other commands in this section, the vmstat command is driven by delay and count options that determine the time interval between report lines and the totals number of intervals to be reported.

$ vmstat 3 5

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0  0      0  63492  94856  24996    0    0     8     3  484    29  1  0 99  0
 0  0      0  63492  94856  24996    0    0     0     0 1005    25  1  0 99  0
 0  0      0  63492  94860  24996    0    0     0    13 1005    24  1  0 99  0
 0  0      0  63492  94860  24996    0    0     0     0 1002    21  0  0 99  0
 0  0      0  63492  94864  24996    0    0     0     4 1003    22  1  0 99  0

The Linux man page for vmstat defines the fields displayed as follows:

* procs

* r: The number of processes waiting for run time

* b: The number of processes in uninterruptible sleep, which means they are waiting on a resource

* memory

* swpd: Virtual memory used

* free: Idle memory

* buff: Amount of memory used as buffers

* cache: Current memory used as cache

* swap

* si: Memory swapped in per second from disk

* so: Memory swapped out per second to disk

* io

* bi: Blocks per second received from a block device

* bo: Blocks per second sent to a block device

* system

* in:.Number of interrupts per second, including the clock

* cs: Number of context switches per second

* cpu: These statistics are percentages of total CPU time:

* us: User time spent running non-kernel code, includes nice time

* sy: System time spent running kernel code

* id: Idle time

* wa: Wait time spent waiting for I/O

The vmstat information can be invaluable when studying resource utilization trends.  Here are a few examples of how vmstat output can be interpreted:

If over time the run queue value, procs-r, remains consistently higher than the number of processors in the server and CPU idle time is low, the system is CPU bound and can benefit from the addition of more and/or faster processors. Alternatively a high number displayed in the procs-b column also indicates a bottleneck, but one where processes are waiting on other resources.

If the virtual memory used (memory-swpd) remains high and the free memory (memory-free) remains low, then the system is memory constrained and will benefit from additional RAM.

Consistently high I/O rates paired with consistently low CPU utilization (cpu-us) indicates an I/O bound system that could benefit from a highly buffered disk array or possibly solid-state disk.


Featured post

Postgres commads

 [oracle@Tesdb ~]$ systemctl status postgresql-15 ● postgresql-15.service - PostgreSQL 15 database server    Loaded: loaded (/usr/lib/system...