SQL Plus || Changing the Prompt to show connected user and database

Amend your $ORACLE_HOME\sqlplus\admin\glogin.sql script - add:

set sqlprompt "_user '@' _connect_identifier > "

to the end of the file.

In Oracle 10g this will change correctly each time you issue a "conn". For clients before 10g it won't change when you do a "conn" but will remain as the username/db you first connected to.

You can also use _date for the current date and _privilege for the privilege (eg SYSDBA) of the connected user.

Useful Sar (Sysstat) Examples for UNIX / Linux Performance Monitoring

SAR  stands for System Activity Reporter. The name indicates the functionality of it.
SAR is the system monitoring utility inside the sysstas.  Using sar you can monitor performance of various Linux subsystems (CPU, Memory, I/O..) in real time. We can easily install it using the following command:

sudo apt-get install sysstat
(or)
yum install sysstat
(or)
rpm -ivh sysstat-10.0.0-1.i586.rpm

After installing, verify the sar version using “sar -V”. Version 10 is the current stable version of sysstat.

$ sar -V

Finally, make sure sar works. For example, the following gives the system CPU statistics 3 times (with 1 second interval).

$ sar 1 3

Following are the other sysstat utilities.

• sar collects and displays ALL system activities statistics.
• sadc stands for “system activity data collector”. This is the sar backend tool that does the data collection.
• sa1 stores system activities in binary data file. sa1 depends on sadc for this purpose. sa1 runs from cron.
• sa2 creates daily summary of the collected statistics. sa2 runs from cron.
• sadf can generate sar report in CSV, XML, and various other formats. Use this to integrate sar data with other tools.
• iostat generates CPU, I/O statistics
• mpstat displays CPU statistics.
• pidstat reports statistics based on the process id (PID)
• nfsiostat displays NFS I/O statistics.
• cifsiostat generates CIFS statistics.

This article focuses on sysstat fundamentals and sar utility.

Collect the sar statistics using cron job – sa1 and sa2

Create sysstat file under /etc/cron.d directory that will collect the historical sar data.

# vi /etc/cron.d/sysstat
*/10 * * * * root /usr/local/lib/sa/sa1 1 1
53 23 * * * root /usr/local/lib/sa/sa2 -A

1. CPU Usage of ALL CPUs (sar -u)

This gives the cumulative real-time CPU usage of all CPUs. “1 3″ reports for every 1 seconds a total of 3 times. Most likely you’ll focus on the last field “%idle” to see the cpu load.

$ sar -u 1 3

2. CPU Usage of Individual CPU or Core (sar -P)

If you have 4 Cores on the machine and would like to see what the individual cores are doing, do the following.

“-P ALL” indicates that it should displays statistics for ALL the individual Cores.

In the following example under “CPU” column 0, 1, 2, and 3 indicates the corresponding CPU core numbers.

$ sar -P ALL 1 1

“-P 1″ indicates that it should displays statistics only for the 2nd Core. (Note that Core number starts from 0).

$ sar -P 1 1 1

3. Memory Free and Used (sar -r)

This reports the memory statistics. “1 3″ reports for every 1 seconds a total of 3 times. Most likely you’ll focus on “kbmemfree” and “kbmemused” for free and used memory.

$ sar -r 1 3

4. Swap Space Used (sar -S)

This reports the swap statistics. “1 3″ reports for every 1 seconds a total of 3 times. If the “kbswpused” and “%swpused” are at 0, then your system is not swapping.

$ sar -S 1 3

5. Overall I/O Activities (sar -b)

This reports I/O statistics. “1 3″ reports for every 1 seconds a total of 3 times.

Following fields are displays in the example below.

• tps – Transactions per second (this includes both read and write)
• rtps – Read transactions per second
• wtps – Write transactions per second
• bread/s – Bytes read per second
• bwrtn/s – Bytes written per second

$ sar -b 1 3

6. Individual Block Device I/O Activities (sar -d)

To identify the activities by the individual block devices (i.e a specific mount point, or LUN, or partition), use “sar -d”

$ sar -d 1 1

7. Display context switch per second (sar -w)

This reports the total number of processes created per second, and total number of context switches per second. “1 3″ reports for every 1 seconds a total of 3 times.

$ sar -w 1 3

8. Reports run queue and load average (sar -q)

This reports the run queue size and load average of last 1 minute, 5 minutes, and 15 minutes. “1 3″ reports for every 1 seconds a total of 3 times.

$ sar -q 1 3

9. Report network statistics (sar -n)

This reports various network statistics. For example: number of packets received (transmitted) through the network card, statistics of packet failure etc.,. “1 3″ reports for every 1 seconds a total of 3 times.

sar -n KEYWORD

KEYWORD can be one of the following:

• DEV – Displays network devices vital statistics for eth0, eth1, etc.,
• EDEV – Display network device failure statistics
• NFS – Displays NFS client activities
• NFSD – Displays NFS server activities
• SOCK – Displays sockets in use for IPv4
• IP – Displays IPv4 network traffic
• EIP – Displays IPv4 network errors
• ICMP – Displays ICMPv4 network traffic
• EICMP – Displays ICMPv4 network errors
• TCP – Displays TCPv4 network traffic
• ETCP – Displays TCPv4 network errors
• UDP – Displays UDPv4 network traffic
• SOCK6, IP6, EIP6, ICMP6, UDP6 are for IPv6
• ALL – This displays all of the above information. The output will be very long.

10. Report Sar Data Using Start Time (sar -s)

When you view historic sar data from the /var/log/sa/saXX file using “sar -f” option, it displays all the sar data for that specific day starting from 12:00 a.m for that day.

Using “-s hh:mi:ss” option, you can specify the start time. For example, if you specify “sar -s 10:00:00″, it will display the sar data starting from 10 a.m (instead of starting from midnight) as shown below.

You can combine -s option with other sar option.

For example, to report the load average on 26th of this month starting from 10 a.m in the morning, combine the -q and -s option as shown below.

$ sar -q -f /var/log/sa/sa23 -s 10:00:01

There is no option to limit the end-time. You just have to get creative and use head command as shown below.

For example, starting from 10 a.m, if you want to see 7 entries, you have to pipe the above output to “head -n 10″.

$ sar -q -f /var/log/sa/sa23 -s 10:00:01 | head -n 10

Commands to get Hardware Information in Linux

1. lshw - List Hardware

    Lshw extracts the information from different /proc files.It reports detailed and brief information about multiple different hardware units such as cpu, memory, disk, usb controllers, network adapters etc.

2. lscpu

    This command reports information about the cpu and processing units. It does not have any further options or functionality.

3. hwinfo - Hardware Information

    Hwinfo is another general purpose hardware probing utility that can report detailed and brief information about multiple different hardware components, and more than what lshw can report.
   Command: $ hwinfo --short

4. Inxi

Inxi is a 10K line mega bash script that fetches hardware details from multiple different sources and commands on the system, and generates a beautiful looking report that non technical users can read easily.
    command: $ inxi -Fx

5. lspci - List PCI

    The lspci command lists out all the pci buses and details about the devices connected to them. The vga adapter, graphics card, network adapter, usb ports, sata controllers, etc all fall under this category.
    Filter out specific device information with grep.
   Command: $ lspci -v | grep "VGA" -A 12

6. lsscsi - List scsi devices

    Lists out the scsi/sata devices like hard drives and optical drives.

7. lsusb - List usb buses and device details

    This command shows the USB controllers and details about devices connected to them. By default brief information is printed. Use the verbose option "-v" to print detailed information about each usb port

8. lsblk - List block devices

To get the the list of all block devices, which are the hard drive partitions and other storage devices like optical drives and flash drives

9. df - disk space of file systems

Various partitions Information, their mount points and the used and available space on each.
    command: $ df -H

10. fdisk

To modify hard drive partitions Fdisk is a utility, and can be used to list out the partition information.
    command: $ sudo fdisk -l

11. Pydf - Python df

Its wriiten in python, that displays colored output that looks better than df

12. mount

The mount is used to mount/unmount and view mounted file systems.
    command: $ mount | column -t
                      $ mount | column -t | grep ext

13. free - Check RAM

Check the amount of used, free and total amount of RAM on system with the free command.
    command: $ free -m

14. hdparm

The hdparm command gets information about sata devices like hard disks.
    commnad: $ sudo hdparm -i /dev/sda

15. dmidecode

The dmidecode command is different from all other commands. It extracts hardware information by reading data from the SMBOIS data structures (also called DMI tables).
Commands:
    # display information about the processor/cpu
    $ sudo dmidecode -t processor

    # memory/ram information
    $ sudo dmidecode -t memory

    # bios details
    $ sudo dmidecode -t bios

16. /proc files

Many of the virtual files in the /proc directory contain information about hardware and configurations. Here are some of them   
    Commands:
    # memory information
    $ cat /proc/meminfo
   
    # cpu information
    $ cat /proc/cpuinfo

    #Partition Information
    $ cat /proc/partitions

    #Linux or Kernel Verison
    $ cat /proc/version

    #SCSI / Sata Devices
    $ cat /proc/scsi/scsi
   

Starting and stoping Oralce Database

In this article is a quick note to start and stop an oracle database.

How To Startup Oracle Database

1. Login to the system with oracle username

Typical oracle installation will have oracle as username and dba as group. On Linux, do su to oracle as shown below.

$ su - oracle

2. Connect to oracle sysdba

Make sure ORACLE_SID and ORACLE_HOME are set properly as shown below.

$ env | grep ORA
ORACLE_SID=DEVDB
ORACLE_HOME=/u01/app/oracle/product/10.2.0


You can connect using either “/ as sysdba” or an oracle account that has DBA privilege.

$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 18 11:11:28 2009
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
SQL>

3. Start Oracle Database

The default SPFILE (server parameter file) is located under $ORACLE_HOME/dbs. Oracle will use this SPFILE during startup, if you don’t specify SPFILE.

Oracle will look for the parameter file in the following order under $ORACLE_HOME/dbs. If any one of them exist, it will use that particular parameter file.

    spfile$ORACLE_SID.ora
    spfile.ora
    init$ORACLE_SID.ora


Type “startup” at the SQL command prompt to startup the database as shown below.

SQL> startup
ORACLE instance started.

Total System Global Area  812529152 bytes
Fixed Size                  2264280 bytes
Variable Size             960781800 bytes
Database Buffers           54654432 bytes
Redo Buffers                3498640 bytes
Database mounted.
Database opened.
SQL>


If you want to startup Oracle with PFILE, pass it as a parameter as shown below.

SQL> STARTUP PFILE=/u01/app/oracle/product/10.2.0/dbs/init.ora

How To Shutdown Oracle Database

Following three methods are available to shutdown the oracle database:

    Normal Shutdown
    Shutdown Immediate
    Shutdown Abort

1. Normal Shutdown

During normal shutdown, before the oracle database is shut down, oracle will wait for all active users to disconnect their sessions. As the parameter name (normal) suggest, use this option to shutdown the database under normal conditions.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

2. Shutdown Immediate

During immediate shutdown, before the oracle database is shut down, oracle will rollback active transaction and disconnect all active users. Use this option when there is a problem with your database and you don’t have enough time to request users to log-off.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

3. Shutdown Abort

During shutdown abort, before the oracle database is shutdown, all user sessions will be terminated immediately. Uncomitted transactions will not be rolled back. Use this option only during emergency situations when the “shutdown” and “shutdown immediate” doesn’t work.

$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 18 11:11:33 2009
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to an idle instance.

SQL> shutdown abort
ORACLE instance shut down.
SQL>

How To Start, Stop and Restart Oracle Listener

For a database administrator, starting up and shutting down the oracle listener is a routine task .

Displaying oracle listener status:

Before starting, stopping or restarting make sure to execute lsnrctl status command to check the oracle listener status as shown below. Apart from letting us know whether the listener is up or down, you can also find the following valuable information from the lsnrctl status command output.

    Listner Start Date and Time.
    Uptime of listner – How long the listener has been up and running.
    Listener Parameter File – Location of the listener.ora file. Typically located under $ORACLE_HOME/network/admin
    Listener Log File – Location of the listener log file. i.e log.xml

command: $ lsnrctl status

Starting oralce listener:

If the Oracle listener is not running, start the listener as shown below. This will start all the listeners. If you want to start a specific listener, specify the listener name next to start. i.e lsnrctl start [listener-name]

command: $ lsnrctl start

Stoppping listener:

If the Oracle listener is running, stop the listener as shown below. This will stop all the listeners. If you want to stop a specific listener, specify the listener name next to stop. i.e lsnrctl stop [listener-name]

command: $ lsnrctl stop

Restarting listener:

To restart the listener use lsnrctl reload as shown below instead of lsnrctl stop and lsnrctl start. realod will read the listener.ora file for new setting without stop and start of the Oracle listener.

command: $ lsnrctl reload

Available listener commands:

lsnrctl help command will display all available listener commands. In Oracle 11g following are the available listener commands.

    start – Start the Oracle listener
    stop – Stop the Oracle listener
    status – Display the current status of the Oracle listener
    services – Retrieve the listener services information
    version – Display the oracle listener version information
    reload – This will reload the oracle listener SID and parameter files. This is equivalent to lsnrctl stop and lsnrctl start.
    save_config – This will save the current settings to the listener.ora file and also take a backup of the listener.ora file before overwriting it. If there are no changes, it will display the message “No changes to save for LISTENER”
    trace – Enable the tracing at the listener level. The available options are ‘trace OFF’, ‘trace USER’, ‘trace ADMIN’ or ‘trace SUPPORT’
    spawn – Spawns a new with the program with the spawn_alias mentioned in the listener.ora file
    change_password – Set the new password to the oracle listener (or) change the existing listener password.
    show – Display log files and other relevant listener information.