UNIX Commands for DBAs

UNIX Commands for DBAs
———————–

This article contains a brief list of commands that most UNIX DBAs will need on a regular basis. Over time I’ve been adding more Linux-related entries.

–Basic File Navigation

The “pwd” command displays the current directory.

root> pwd
/u01/app/oracle/product/9.2.0.1.0

–The “ls” command lists all files and directories in the specified directory. If no location is defined it acts on the current directory.

root> ls
root> ls /u01
root> ls -al

The “-a” flag lists hidden “.” files. The “-l” flag lists file details.

–The “cd” command is used to change directories.

root> cd /u01/app/oracle

–The “touch” command is used to create a new empty file with the default permissions.

root> touch my.log

–The “rm” command is used to delete files and directories.

root> rm my.log
root> rm -R /archive

–The “-R” flag tells the command to recurse through subdirectories.

–The “mv” command is used to move or rename files and directories.

root> mv [from] [to]
root> mv my.log my1.log
root> mv * /archive
root> mv /archive/* .

–The “.” represents the current directory.

–The “cp” command is used to copy files and directories.

root> cp [from] [to]
root> cp my.log my1.log
root> cp * /archive
root> cp /archive/* .

–The “mkdir” command is used to create new directories.

root> mkdir archive

–The “rmdir” command is used to delete directories.

root> rmdir archive

–The “find” command can be used to find the location of specific files.

root> find / -name dbmspool.sql
root> find / -print | grep -i dbmspool.sql

–The “/” flag represents the starting directory for the search. Wildcards such as “dbms*” can be used for the filename.

–The “which” command can be used to find the location of an executable you are using.

oracle> which sqlplus

The “which” command searches your PATH setting for occurrences of the specified executable.
File Permissions

See Linux Files, Directories and Permissions.

–The “umask” command can be used to read or set default file permissions for the current user.

root> umask 022

The umask value is subtracted from the default permissions (666) to give the final permission.

666 : Default permission
022 : – umask value
644 : final permission

–The “chmod” command is used to alter file permissions after the file has been created.

root> chmod 777 *.log

Owner Group World Permission
========= ========= ========= ======================
7 (u+rwx) 7 (g+rwx) 7 (o+rwx) read + write + execute
6 (u+rw) 6 (g+rw) 6 (o+rw) read + write
5 (u+rx) 5 (g+rx) 5 (o+rx) read + execute
4 (u+r) 4 (g+r) 4 (o+r) read only
2 (u+w) 2 (g+w) 2 (o+w) write only
1 (u+x) 1 (g+x) 1 (o+x) execute only

Character eqivalents can be used in the chmod command.

root> chmod o+rwx *.log
root> chmod g+r *.log
root> chmod -Rx *.log

–The “chown” command is used to reset the ownership of files after creation.

root> chown -R oinstall.dba *

The “-R” flag causes the command ro recurse through any subdirectories.
OS Users Management

See Linux Groups and Users.

–The “useradd” command is used to add OS users.

root> useradd -G oinstall -g dba -d /usr/users/my_user -m -s /bin/ksh my_user

The “-G” flag specifies the primary group.
The “-g” flag specifies the secondary group.
The “-d” flag specifies the default directory.
The “-m” flag creates the default directory.
The “-s” flag specifies the default shell.

–The “usermod” command is used to modify the user settings after a user has been created.

root> usermod -s /bin/csh my_user

–The “userdel” command is used to delete existing users.

root> userdel -r my_user

The “-r” flag removes the default directory.

–The “passwd” command is used to set, or reset, the users login password.

root> passwd my_user

–The “who” command can be used to list all users who have OS connections.

root> who
root> who | head -5
root> who | tail -5
root> who | grep -i ora
root> who | wc -l

The “head -5” command restricts the output to the first 5 lines of the who command.
The “tail -5” command restricts the output to the last 5 lines of the who command.
The “grep -i ora” command restricts the output to lines containing “ora”.
The “wc -l” command returns the number of lines from “who”, and hence the number of connected users.

Process Management
——————

See Linux Process Management (ps, top, renice, kill).

–The “ps” command lists current process information.

# ps
# ps -ef | grep -i ora
# ps -ef | grep -i ora | grep -v grep
# ps -ef | grep -i [o]ra

–Specific processes can be killed by specifying the process id in the kill command.

# kill 12345
# kill -9 12345

–You can kill multiple processes using a single command by combining “kill” with the “ps” and “awk” commands.

# kill -9 `ps -ef | grep ora | awk ‘{print $2}’`

uname and hostname

–The “uname” and “hostname” commands can be used to get information about the host.

root> uname -a
OSF1 oradb01.lynx.co.uk V5.1 2650 alpha

root> uname -a | awk ‘{ print $2 }’
oradb01.lynx.co.uk

root> hostname
oradb01.lynx.co.uk

–Compress Files

See Linux Archive Tools (tar, star, gzip, bzip2, zip, cpio).

In order to save space on the filesystem you may wish to compress files such as archived redo logs. This can be using either the gzip or the compress commands. The gzip command results in a compressed copy of the original file with a “.gz” extension. The gunzip command reverses this process.

gzip myfile
gunzip myfile.gz

–The compress command results in a compressed copy of the original file with a “.Z” extension. The uncompress command reverses this process.

compress myfile
uncompress myfile

–General Performance
vmstat

Reports virtual memory statistics.

# vmstat 5 3
procs ———–memory———- —swap– —–io—- –system– —–cpu——
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 1060608 24372 739080 0 0 1334 63 1018 1571 14 11 66 10 0
0 0 0 995244 24392 799656 0 0 6302 160 1221 1962 10 10 62 18 0
0 0 0 992376 24400 799784 0 0 1 28 992 1886 3 2 95 0 0
#

See the vmstat man page.
–free

Reports the current memory usage. The “-/+ buffers/cache:” line represents the true used and free memory, ignoring the Linux file system cache.

# free
total used free shared buffers cached
Mem: 8178884 4669760 3509124 0 324056 1717756
-/+ buffers/cache: 2627948 5550936
Swap: 10289148 0 10289148
#

–iostat

Reports I/O statistics.

# iostat
Linux 3.2.10-3.fc16.x86_64 (maggie.localdomain) 03/19/2012 _x86_64_(4 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
2.02 0.23 0.51 0.78 0.00 96.46

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 9.23 100.55 62.99 1796672 1125538
dm-0 13.60 100.31 62.99 1792386 1125524
dm-1 0.02 0.08 0.00 1432 0

#

–CPU Usage

See Linux Process Management (ps, top, renice, kill).
sar

On Linux systems sar (System Activity Reporter) is probably one of the simplest and most versatile tools for reporting system utilization including CPU, memory, disk and network activity. It automatically collects system activity statistics when installed using the following command.

# yum install sysstat

The sar command syntax takes the following form.

# sar [options] [interval [count]]

The “options” parameters determine what is reported, which will be discussed later. The “interval” parameter indicates the time interval in seconds between samples. The “count” parameter indicates the number of samples that will be taken before the command ends. If “count” is omitted, the sampling will continue indefinitely. If both “interval” and “count” are omitted, the command will report the values from the 10 minute samples taken since the machine was last restarted.

As seen in the sar man page, there are lots of available options, but some starting points you may find interesting include:

CPU:
Basic CPU: sar [-u] [interval [count]]
Load Average: sar -q [interval [count]]
Memory:
Kernel Paging: sar -B [interval [count]]
Unused Memory: sar -r [interval [count]]
Swap Space: sar -S [interval [count]]
Disk:
Average Disk I/O: sar -b [interval [count]]
Disk I/O: sar -dp [interval [count]]
Network:
Network: sar -n DEV [interval [count]]
Network Errors: sar -n EDEV [interval [count]]

Here is an example of the output from a CPU report.

# sar -u 1 5
Linux 2.6.32-100.0.19.el5 (ol5-112.localdomain) 06/27/2011

03:10:07 PM CPU %user %nice %system %iowait %steal %idle
03:10:08 PM all 0.00 1.01 23.23 75.76 0.00 0.00
03:10:09 PM all 0.00 1.02 35.71 63.27 0.00 0.00
03:10:10 PM all 0.98 3.92 35.29 59.80 0.00 0.00
03:10:11 PM all 0.00 1.03 29.90 69.07 0.00 0.00
03:10:12 PM all 0.00 2.00 35.00 63.00 0.00 0.00
Average: all 0.20 1.81 31.85 66.13 0.00 0.00
#

–mpstat

Reports processor related statistics.

# mpstat 10 2
Linux 2.6.32-100.0.19.el5 (ol5-112.localdomain) 06/27/2011

01:59:57 PM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
02:00:07 PM all 1.21 0.00 0.90 0.20 0.00 0.00 0.00 97.69 980.50
02:00:17 PM all 0.70 0.00 0.40 0.00 0.00 0.10 0.00 98.79 973.77
Average: all 0.95 0.00 0.65 0.10 0.00 0.05 0.00 98.24 977.14
#

See the mpstat man page.
–top

Displays top tasks.

# top
top – 13:58:17 up 2 min, 1 user, load average: 2.54, 1.11, 0.41
Tasks: 160 total, 6 running, 154 sleeping, 0 stopped, 0 zombie
#

The PID column can then be matched with the SPID column on the V$PROCESS view to provide more information on the process.

SELECT a.username,
a.osuser,
a.program,
spid,
sid,
a.serial#
FROM v$session a,
v$process b
WHERE a.paddr = b.addr
AND spid = ‘&pid’;

See the top man page.

–Hide Passwords

You may be required to use passwords in scripts calling Oracle tools, like SQL*Plus, Export/Import and RMAN etc. One method to remove the credentials from the script itself is to create a credentials file to hold them. In this case I’m using “/home/oracle/.scottcred”, which contains the following.

scott/tiger

Change the permissions to make sure the file is only visible to the owner.

$ chmod 600 /home/oracle/.scottcred

Now replace references to the credentials with the contents of the file.

$ expdp < /home/oracle/.scottcred schemas=SCOTT directory=DATA_PUMP_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

Alternatively, consider using one of the following:

Secure External Password Store
OS Authentication

–Automatic Startup Scripts on Linux

This text has been replaced by a separate article here.
CRON

See CRON : Scheduling Tasks on Linux.

There are two methods of editing the crontab file. First you can use the “crontab -l > filename” option to list the contents and pipe this to a file. Once you’ve editied the file you can then apply it using the “crontab filename”.

Login as root
crontab -l > newcron
Edit newcron file.
crontab newcron

Alternatively you can use the “crontab -e” option to edit the crontab file directly.

The entries have the following elements.

field allowed values
—– ————–
minute 0-59
hour 0-23
day of month 1-31
month 1-12
day of week 0-7 (both 0 and 7 are Sunday)
user Valid OS user
command Valid command or script.

The first 5 fields can be specified using the following rules.

* – All available values or “first-last”.
3-4 – A single range representing each possible from the start to the end of the range inclusive.
1,2,5,6 – A specific list of values.
1-3,5-8 – A specific list of ranges.
0-23/2 – Every other value in the specified range.

The following entry runs a cleanup script a 01:00 each Sunday. Any output or errors from the script are piped to /dev/null to prevent a buildup of mails to root.

0 1 * * 0 /u01/app/oracle/dba/weekly_cleanup > /dev/null 2>&1
—-Useful Files

Here are some files that may be of use.
Path Contents
/etc/passwd User settings
/etc/group Group settings for users.
/etc/hosts Hostname lookup information.
/etc/system Kernel parameters for Solaris.
/etc/sysconfigtab Kernel parameters for Tru64.
/etc/sysctl.conf Kernel parameters for Linux.

Advertisements

Migration From SQL Server To Oracle Using SQL Developer: ——————————————————– Migration is the process of copying the schema objects and data from a non-Oracle database, such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, or IBM DB2, to an Oracle database. To migrate a third-party database to Oracle easily, you can choose the following options using SQL Developer: – Migrating using the Migration Wizard – Copying tables to Oracle Migrating Using the Migration Wizard The Migration wizard provides a screen to manage all the steps needed for the migration to Oracle database. These steps are as follows: – Capturing the source database (MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, IBM DB2), – Converting it to Oracle format, – Generating DDL, – Performing the conversion. After a brief explanation about the process of migration, let’s do a simple migration from SQL Server database to Oracle database. 1- I created sample SQL Server database named TALIPTEST. 2- The migration repository is a collection of schema objects that SQL Developer uses to manage metadata for migrations. For a migration repository create a database connection to convenient Oracle database and give following grants. CREATE USER MIGRATIONS IDENTIFIED BY “migration” DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; grant create session to migrations; grant resource to migrations; grant create view to migrations; For multischema migrations, you must grant the privileges with the ADMIN option as follows. grant resource to migrations with admin option; grant create role to migrations with admin option; grant alter any trigger to migrations with admin option; grant create user to migrations with admin option; 3- Download SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html 4- Unzip the file named sqldeveloper-3.2.20.09.87.zip and extract it. Open sqldeveloper.exe file to open SQL Developer. 5- Right click to the “Connections” and click the “New Connections”. 6- Create a database connection named Migration_Repository that connects to the MIGRATIONS user. 7- Right-click the Migration_Repository connection, and select “Migration Repository” menu , then “Associate Migration Repository” to create the repository. 8- Schema objects needed for migration will be created in MIGRATIONS schema. 9- To connect to third-party database (MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, IBM DB2) using SQL Developer, we need jTDS driver. You can download needed jTDS driver from following link. http://sourceforge.net/projects/jtds/files/jtds/1.2/jtds-1.2-dist.zip/download . Extract the dowloaded zip file named jtds-1.2-dist.zip 10- Click “Tools” and then “Prefenrences”. 11- Select “Third Party JDBC Drivers” and click “Add Entry” button to add jTDS driver for SQL Server. 12- Select jar file located in jTDS driver folder. 13- Click “OK” button. 14- Now you can connect to SQL Server or Sybase database using SQL Developer. Connect to the SQL Server database as follows. 15- And finally, let us create the database connection which we will migrate to. 16- The migration wizard is invoked in a variety of contexts. You can right-click a third-party database connection and select “Migrate to Oracle” or you can click “Tools>Migration>Migrate…”. 17- Click “Next” button. 18- Select migration repository database connection. 19- Give a name for migration project and select a directory to write output. 20– Select third party database connection. You can migrate database on online or offline mode. If you choose online migration then Migration Wizard will perform all needed operations. If you choose offline migration then Migration Wizard will generate all needed DDL scripts. 21- Select SQL Server database which we want to migrate to Oracle. 22- Specify the conversion options. And click “Advanced Options” link to ensure “Microsoft SQL Server : Is quoted identifier on” option is selected. 23- Select target database connection. 24- If you select offline migration then offline migration script will be generated in the project output directory. 25- Select the connections to be used for online data move. 26- Click “Finish” button so start migration. 27- Migration and conversion process will be perfomed as follows. Now, Our SQL Server database in Oracle :) Copying tables to Oracle Right click the table located in third party database and click “Copy To Oracle” Select destination (Oracle) database connection and click “Apply” button. If you choose “Include Data” check box it will create table structure and move data. Copy to Oracle database is finished. Note: This method doesn’t move indexes, triggers, etc. It only moves table structure and data.

Migration From SQL Server To Oracle Using SQL Developer:
——————————————————–

Migration is the process of copying the schema objects and data from a non-Oracle database,
such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, or IBM DB2, to an Oracle database.

To migrate a third-party database to Oracle easily, you can choose the following options using SQL Developer:

– Migrating using the Migration Wizard

– Copying tables to Oracle

Migrating Using the Migration Wizard

The Migration wizard provides a screen to manage all the steps needed for the migration to Oracle database. These steps are as follows:

– Capturing the source database (MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, IBM DB2),

– Converting it to Oracle format,

– Generating DDL,

– Performing the conversion.

After a brief explanation about the process of migration, let’s do a simple migration from SQL Server database to Oracle database.

1- I created sample SQL Server database named TALIPTEST.

2- The migration repository is a collection of schema objects that SQL Developer uses to manage metadata for migrations. For a migration repository create a database connection to convenient Oracle database and give following grants.

CREATE USER MIGRATIONS IDENTIFIED BY “migration”

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP;

grant create session to migrations;

grant resource to migrations;

grant create view to migrations;

For multischema migrations, you must grant the privileges with the ADMIN option as follows.

grant resource to migrations with admin option;

grant create role to migrations with admin option;

grant alter any trigger to migrations with admin option;

grant create user to migrations with admin option;

3- Download SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

4- Unzip the file named sqldeveloper-3.2.20.09.87.zip and extract it. Open sqldeveloper.exe file to open SQL Developer.

5- Right click to the “Connections” and click the “New Connections”.

6- Create a database connection named Migration_Repository that connects to the MIGRATIONS user.

7- Right-click the Migration_Repository connection, and select “Migration Repository” menu , then “Associate Migration Repository” to create the repository.

8- Schema objects needed for migration will be created in MIGRATIONS schema.

9- To connect to third-party database (MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, IBM DB2) using SQL Developer, we need jTDS driver. You can download needed jTDS driver from following link. http://sourceforge.net/projects/jtds/files/jtds/1.2/jtds-1.2-dist.zip/download . Extract the dowloaded zip file named jtds-1.2-dist.zip

10- Click “Tools” and then “Prefenrences”.

11- Select “Third Party JDBC Drivers” and click “Add Entry” button to add jTDS driver for SQL Server.

12- Select jar file located in jTDS driver folder.

13- Click “OK” button.

14- Now you can connect to SQL Server or Sybase database using SQL Developer. Connect to the SQL Server database as follows.

15- And finally, let us create the database connection which we will migrate to.

16- The migration wizard is invoked in a variety of contexts. You can right-click a third-party database connection and select “Migrate to Oracle” or you can click “Tools>Migration>Migrate…”.

17- Click “Next” button.

18- Select migration repository database connection.

19- Give a name for migration project and select a directory to write output.

20– Select third party database connection. You can migrate database on online or offline mode. If you choose online migration then Migration Wizard will perform all needed operations. If you choose offline migration then Migration Wizard will generate all needed DDL scripts.

21- Select SQL Server database which we want to migrate to Oracle.

22- Specify the conversion options. And click “Advanced Options” link to ensure “Microsoft SQL Server : Is quoted identifier on” option is selected.

23- Select target database connection.

24- If you select offline migration then offline migration script will be generated in the project output directory.

25- Select the connections to be used for online data move.

26- Click “Finish” button so start migration.

27- Migration and conversion process will be perfomed as follows.

Now, Our SQL Server database in Oracle 🙂

Copying tables to Oracle

Right click the table located in third party database and click “Copy To Oracle”

Select destination (Oracle) database connection and click “Apply” button. If you choose “Include Data” check box it will create table structure and move data.

Copy to Oracle database is finished.

Note: This method doesn’t move indexes, triggers, etc. It only moves table structure and data.

Clean Up Oracle archive log using cron ————————————– Cron List Oracle has capability for creating hot backup using archive log mechanism. This feature act like recovery data system for restoring database content to specified time if the original database has force major which make data corrupted. But, this mechanism will suck up the free space of hard disk and typically if we don’t maintain the following problem maybe occured : 1. The Space allocated for archive run out which make all transaction will be suspended 2. Hard disk space will be run out Usually we can delete completed archive log using RMAN command, but this sometimes takes time. Below is automation script for deleting completed archive log using cron. 1. create script (named cleanarchive.sh) #!/bin/bash export ORACLE_BASE=/database/oracle/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 export ORACLE_OWNR=oracle export ORACLE_SID=orcl export PATH=$PATH:$ORACLE_HOME/bin su – oracle -c rman target / </database/oracle/crontab/arch.log 2>&1 3. Restart cron service Voila …, now the completed archive log will be checked and cleaned up every day, and we just left the archive less then 7 day till the day executed.

Clean Up Oracle archive log using cron
————————————–
Cron List

Oracle has capability for creating hot backup using archive log mechanism. This feature act like recovery data system for restoring database content to specified time if the original database has force major which make data corrupted.

But, this mechanism will suck up the free space of hard disk and typically if we don’t maintain the following problem maybe occured :

1. The Space allocated for archive run out which make all transaction will be suspended

2. Hard disk space will be run out

Usually we can delete completed archive log using RMAN command, but this sometimes takes time.

Below is automation script for deleting completed archive log using cron.

1. create script (named cleanarchive.sh)

#!/bin/bash
export ORACLE_BASE=/database/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_OWNR=oracle
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin

su – oracle -c rman target / << __EOF__
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE -7’;
EXIT
__EOF__

2. create new job on cron sequence (will be executed everyday at 22:55)

crontab -e

55 22 * * * /bin/sh /database/oracle/crontab/cleanarchive.sh >/database/oracle/crontab/arch.log 2>&1

3. Restart cron service

Voila …, now the completed archive log will be checked and cleaned up every day, and we just left the archive less then 7 day till the day executed.

10g backup and recovery11g_Grid_control_new11gr2-rac-on-aix6-1-0825105300FMcmp1br_test_lossof-datafile_10gcookbook-v2[1].0-10grac r2 – asm – aix5l – san storagedeploying-oracle-11gr2-rac-on-rhel6_1.1expert-oracle-database-architecturegrow_that_dba_career_-_a_wissen_white_paperHowtoBackupandRestoreaRACDatabaseloss-of-all-controlfilesloss-of-all-online-redolog-filesnoarchivelog-database-recovery-via-rmanOracle 11g XE TutorialOracle Database 11g and MySQL 5.5 Developer Handbookoracle_ggOracle+Database+11g+PL+SQL+ProgrammingOracle11gR2-RAC-Solaris10-Iscsi-ZFS-Vmware-RageshMoyanOracle-Database Comparisions of Various Editionsoracle-database-11g-the-complete-referenceOVM-2.2-Server-InstallationPerforming-a-database-clone-using-a-Data-Guard-physical-standby-databasePLSQL Scheduleplsql_tutorialpoint-in-time-recovery-using-rmanpoint-in-time-recovery-via-rmanRAC 11gR2 Setup_v1.2recovery-of-a-dropped-tablespacerecovery-scenario_loss_of_allfilesRefresh-Test-Database-From-Production-Database-Procedurerestore-on-scratch-serverRestoring a ASM backup to non-ASM and restoring from RAC to single-instanceRestoring the SPFILE – with and without AUTOBACKUPrman_datafile_recoveryrman_disater_recoveryrman-duplicate-database-on-the-same-host1RMAN-Recovering-Block-Corruptionrman-recovery-scenarios-related-to-databaseRMAN-recovery-through-RESETLOGSrman-restore-on-another-machine-with-different-file-system-same-database-namerman-tutorialScenario is loss of control files – AUTOBACKUP is enabledScenario is loss of control files – No AUTOBACKUPSQL ReferenceSQL-Commands-1KeydataTROUBLESHOOTINGORACLECLUSTERWAREWaitsinRACworkshop

Oracle database 10gR2 installation in RedHat 5.4

In this post I’ll show you the steps required to install Oracle Database 10g Release 2 on Red Hat Enterprise Linux 5.4 (64-bit)

Assuming you have downloaded Oracle Database 10g R2 from their official website.

If not you can get it from the below mentioned link

Oracle Database 10g R2 for Linux 64-bit

For my case I have disable Selinux. Please make sure you apply relevant policy for selinux in case you have it enforcing state.

Prerequisites before installing Oracle 10gR2

  • GUI Environment

Make sure you have a running GUI environment with all the packages inside group GNOME or KDE package installed.

You can do the same using

# yum groupinstall “GNOME Desktop Environment” “X Window System”

In case you want to use KDE desktop you can select the KDE group package instead of GNOME

  • Memory and Swap space

You can follow the below table which is as per the Oracle standards for installing Oracle Database 10g in a 64 bit Linux machine

RAM Swap Space
Up to 512 MB 2 times the size of RAM
Between 1024 MB and 2048 MB 1.5 times the size of RAM
Between 2049 MB and 8192 MB Equal to the size of RAM
More than 8192 MB 0.75 times the size of RAM

NOTE: In case you donot have enough swap memory space in your machine the follow this page for

How to increase Swap Memory size in Linux

You can check the RAM and SWAP space using this command
# free -m              total       used       free     shared    buffers     cached Mem:          4392       4352         39          0        149       3828 -/+ buffers/cache:        374       4018 Swap:         6399          0       6399
NOTE: In case you have assigned a different partition size for /tmp directory then make sure it has minimum 400 MB free space.

  • Hosts File

Your hosts file should be proper as shown below

# vi /etc/hosts 127.0.0.1 localhost.localdomain localhost ::1 localhost6.localdomain6 localhost6 10.10.20.41 test.example test
Your machine should have a proper hostname. To check use the below command
# hostname test.example

  • Kernel Parameters

For Oracle 10g, the following kernel parameters have to be set to values greater than or equal to the recommended values.

To verify shmmax
# cat /proc/sys/kernel/shmmax shmmax = 2147483648
To verify shmmni
# cat /proc/sys/kernel/shmmni shmmni = 4096
To verify the shmall parameter
# cat /proc/sys/kernel/shmall shmall = 2097152
To verify shmmin
# ipcs -lm |grep “min seg size” shmmin = 1
Note that shmseg is hardcoded in the kernel, the default is much higher.

shmseg = 10
To verify semmsl
# cat /proc/sys/kernel/sem | awk ‘{print $1}’ semmsl = 250
To verify semmns
# cat /proc/sys/kernel/sem | awk ‘{print $2}’ semmns = 32000
To verify semopm
# cat /proc/sys/kernel/sem | awk ‘{print $3}’ semopm = 100
To verify semmni
# cat /proc/sys/kernel/sem | awk ‘{print $4}’ semmni = 128
To verify file-max

# cat /proc/sys/fs/file-max file-max = 65536
To verify ip_local_port_range

# cat /proc/sys/net/ipv4/ip_local_port_range ip_local_port_range = 1024 65000

In case your default kernel parameter is less than the above mentioned parameters then you will have to change them. This is how you can add or change these parameters:
Add the following lines in sysctl.conf

# vi /etc/sysctl.conf kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 1024 65000 net.core.rmem_default=262144 net.core.rmem_max=262144 net.core.wmem_default=262144 net.core.wmem_max=262144
Run the following command to refresh the kernel parameters just added
# sysctl -p
To view all the kernel parameters
# sysctl -a
Add the following lines to the “/etc/security/limits.conf” file.
* soft nproc 2047 * hard nproc 16384 * soft nofile 1024 * hard nofile 65536
Add the following line to the “/etc/pam.d/login” file, if it does not already exist.
session    required    pam_limits.so

  • RPM pre-requisites

Make sure the following rpm version is equal or greater installed in your machine
gcc-3.4.4-2 gcc-c++-3.4.4-2 libstdc++-devel-3.4.4-2 libgcc-4.1.2-50 cpp-3.4.4-2 compat-libf2c-34-3.4.6-4.1 compat-libcom_err-1.0-7 glibc-2.3.4-2.13 glibc-common-2.3.4-2.13 glibc-devel-2.3.4-2.13 glibc-headers-2.3.4-2.13 gnome-libs-1.4.1.2.90-44.1 compat-db-4.1.25-9 sysstat-5.0.5-1 libaio-0.3.103-3 openmotif21-2.1.30-11 xorg-x11-deprecated-libs-6.8.2-1 compat-libstdc++-33-3.2.3-47.3 make-3.80-5libXp-1.0.0

  • User and Group

You have to create a different user and group who will run the database

# useradd oracle # groupadd dba # usermod -G dba oracle

So we have added oracle user to dba group using the above command.

  • Create home directory

# mkdir -p /u01/oracle/product/10.2.0/db_3/ # mkdir /u01/oraInventory # chown -R /u01 # chmod -R oracle:dba /u01We will use the standard format for creating the oracle database. Create a directory with the name used above.

  • Specify the Display protocol

Log in to oracle user

# su – oracle $ export $DISPLAY=localhost:0.0

NOTE: In case you get DISPLAY related errors at the installation stage follow this page
How to specify DISPLAY protocol

  • Installation

Go to the location where you have downloaded and unzipped the database setup file using oracle user

# su – oracle
$ cd /database10gr2/database/ $ ./runInstaller
You will get the below screen. Change the Oracle Home location as the directory row created in “Create Home Directory” section of this blog

(Click on the images in case you find trouble reading the texts)
Give database password in the provided section

Give the directory path as mentioned below

Make sure you get “Succeeded” in all the pre-requisite section. In case you get a failure at any of the parameter, Re-check the failed parameter and it is advised not to move to next step until and unless all the  pre-requisite are “Passed

Click on Next and Finish the installation

How to Fix “Xlib: No protocol specified” 

While installing Oracle database I was getting this error. I searched various blogs and forums and

found out many solutions but lastly I got one solution which worked for me.

But well I will post all the possible ways which you can try (as per my knowledge) to overcome this issue.

Error:

Xlib: connection to ":0.0" refused by server
Xlib: No protocol specified
Specify the display protocol by logging into the user account you are getting the error

Solution 1:
$ export DISPLAY=localhost:0.0
$ xhost +

Solution 2:

Syntax:
$ export DISPLAY=(machine_name):0.0

For example:
$ export DISPLAY=10.10.20.42:0.0
$ xhost +

Solution 3 (which worked for me)
Login to the user account where you are getting the above error

$ xauth $DISPLAY
xauth: creating  new authority file /home/oracle/.Xauthority
xauth: (argv):1: unknown command ":0.0"

The best way to check whether your Xlib display protocol is working or not is by using xclock command

$ xclock
After running this command you should see a small clock on your screen.

Solution 4
In case the above solution does not works
Login as root

# xauth $DISPLAY 
xauth: creating new authority file /home/root/.Xauthority
# xauth list $DISPLAY
test.example.com/unix:0 MIT-MAGIC-COOKIE-1 f601e6a29ea688786765434c5c6325071
Now copy the above output and su to the user you are facing issue
# su - oracle
$ xauth add test.example.com/unix:0 MIT-MAGIC-COOKIE-1 f601e6a29ea688786765434c5c6325071
xauth: creating new authority file /home/oracle/.Xauthority

Now test if your DISPLAY is working

$xclock

Oracle database 11gR2 installation in RedHat 6.2 (32-bit)

I will guide with the steps and the pre-requisites required for the installation of Oracle 11gR2 in Red Hat 6.2 (32-bit)

Pre-requisites before installing database

  • GUI Environment

Make sure you have a running GUI environment with all the packages inside group GNOME or KDE package installed.

You can do the same using

# yum groupinstall “GNOME Desktop Environment” “X Window System”

In case you want to use KDE desktop you can select the KDE group package instead of GNOME

  • Memory Requirements

Minimum 1 GB of RAM

Recommended 2 GB of RAM or more

You can follow the below table which is as per the Oracle standards for installing Oracle Database 11g in a 32 bit Linux machine

RAM Swap Space
Between 1024 MB and 2048 MB 1.5 times the size of RAM
Between 2049 MB and 16384 MB Equal to the size of RAM
More than 16 GB 16 GB

You can check the RAM and SWAP space using this command
# free -m              total       used       free     shared    buffers     cached Mem:          4392       4352         39          0        149       3828 -/+ buffers/cache:        374       4018 Swap:         6399          0       6399

In case you want to change the swap size of your machine as per the default standards, Follow this link

How to increase Swap Memory size in Linux

  • Disk Space Requirements

For the installation of Oracle 11g minimum of 1 GB is required for /tmp directory

You can check the same using

# dh -h /tmp

  • Display Requirements

Minimum display of 1024 x 768 or higher is required for the installation of Oracle 11g

  • Operating system and Kernel Requirement

Follow this link for making sure that the OS you are using is compatible with the database you are installing

Compatibility Requirement for Oracle 11gR2

  • Package Requirement

binutils-2.20.51.0.2-5.11.el6.i686 compat-libcap1-1.10-1.i686 compat-libstdc++-33-3.2.3-69.el6.i686 gcc-4.4.4-13.el6.i686 gcc-c++-4.4.4-13.el6.i686 glibc-2.12-1.7.el6.i686 glibc-devel-2.12-1.7.el6.i686 pdksh-5.2.14-1.i386 libgcc-4.4.4-13.el6.i686 libstdc++-4.4.4-13.el6.i686 libstdc++-devel-4.4.4-13.el6.i686 libaio-0.3.107-10.el6.i686 libaio-devel-0.3.107-10.el6.i686 make-3.81-19.el6.i686 sysstat-9.0.4-11.el6.i686 unixODBC-2.2.14-11.el6.i686 or later unixODBC-devel-2.2.14-11.el6.i686 or later

  • Kernel parameters

Add the following lines at the end of sysctl.conf file

# vi /etc/sysctl.conf fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576

Run the following command to refresh the kernel parameters just added

# sysctl -pTo view all the kernel parameters

# sysctl -aSet the proper upper and lower limits in your machine. Add these lines in the given file as shown below

oracle soft nproc 2047 oracle hard nproc 16384 oracle soft nofile 1024 oracle hard nofile 65536 oracle soft stack 10240

  • Hostname

Check your hosts file and hostname as it should be proper

# vi /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 10.10.20.42 test test.example.com

Your machine should have a proper hostname. To check use the below command

# hostname test.example.com

  • User and Group

You have to create a different user and group who will run the database

# useradd oracle # passwd oracle # groupadd dba # usermod -G dba oracle

So we have added oracle user to dba group using the above command.

  • Create home directory

# mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1/ # mkdir /u01/app/oraInventory # chown -R /u01/ # chmod -R oracle:dba /u01/We will use the standard format for creating the oracle database. Create a directory with the name used above.

  • Specify the Display protocol

Log in to oracle user

# su – oracle $ export $DISPLAY=localhost:0.0

  • Installation

Go to the location where you have downloaded and unzipped the database setup file using oracle user

# su – oracle $ cd /database11gr2/database/ $ ./runInstaller

NOTE: In case you get DISPLAY related errors at the installation stage follow this page

How to specify DISPLAY protocol

Put your email address in case you want recieve updates via Oracle Support. In my case I skip this part

Select the first option as this is a fresh installation

Select the database class you want to install. All the details as per the database class is mentioned along with their option.

We will go with the typical install

Browse the location as specified below accordingly as we had created while filling the pre-requisite section

Select the specified directory in your machine
Next the set up will verify all the pre-requisites. In case you are warned for any error or missing package, make sure you correct and move ahead or else you might face issues after or during the installation.

NOTE: It is recommended that all the pre-requisites should be passed successfully.
Click on Finish to start the installation

Next you will get this screen. Click on “Password management

Next this screen will come up. Create a password for SYS and SYSTEM user and click on Ok

Click on Ok

Now Click on close. You have successfully installed Oracle 11gR2 in your machine

How to generate ER diagram using Oracle SQL developer

Tool Used : SQL Developer version 3.1.07

Schema Used : HR
Tables Used : Countries, Departments, Employees, Job_History, Jobs, Locations, Regions

Navigate the below path and step by step screen shots:

File > Data Modeler > Import > Data Dictionary

1# Select DB Connection and click on next

2# Select Schema/DB you wish to import and select Import to New Relational Model and click on next

3# select the objects you wish to import and click on next and finish

4# E-R relationship diagram is created between list of tables

5# You can save and print ER relationship by following below path

File > Data Modeler > Print Diagram or Print

Oracle Concepts – Find indexes for a table

See the indexes for a table

It is not unusual to wonder what indexes a specific table might have, and what columns in the table are assigned to those indexes. The following query will provide this information to you. It draws on the information in the dba_ind_columns data dictionary view:

SQL> column table_owner format a15

SQL> column table_name format a20

SQL> column index_name format a20

SQL> column column_name format a20

SQL> Select owner, table_name, index_name, column_name 2  FROM dba_ind_columns Order by owner, table_name, column_name  Where owner=’SCOTT’  AND table_name=’EMP’;

NOTE: You normally rebuild an index when moving it to a different tablespace or when it becomes invalid as the table relocates to a different tablespace. Rebuilding indexes on a regular basis is not required and
should not be practiced.
— Rebuild an index
ALTER INDEX salary_idx REBUILD;
To drop an index, use the DROP INDEX command, as shown in the following screenshot:
— Permanently remove the index from the database
DROP INDEX salary_idx;

SQLNET: How does Oracle Client connect with Oracle Server

Lets first try to understand what is Oracle Server and what is Oracle Client:

Oracle Database Server:
Oracle Database Server is usually a physical machine running one or more Oracle Database Instance along with its Database, which also facilitate by providing a connection mechanism to database using listener.

Oracle Client:
Oracle Client is usually a process or a program on a machine, which tries to connect to Oracle Database Server. Oracle Client can exist on any computer usually connected to Oracle Database Server machine by a network. It may be on Same Oracle Database Server also.

Now lets understand what happens when an Oracle Client tries to connect to Oracle Database Server in happy scenario i.e. every thing is up and running and properly configured. Lets try to understand it by what happens when we try to connect Oracle Server by sqlplus as

sqlplus scott/tiger@orcl

Steps / Process Involved
1) Operating System locates “sqlplus” and pass command line parameter “scott/tiger@orcl”
2) sqlplus now asks TNS to connect to “orcl” using user “scott” and password “tiger”
3) TNS now looks into “tnsnames.ora” file to find out the meaning of “orcl”. Default location of the file is “$ORACLE_HOME/network/admin” and most probably has entry for “orcl” like

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ngarg.mydomain.co.in)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.mydomain.co.in)
    )
  )

4) Now it will try to connect Oracle Database Server Machine “ngarg.mydomain.co.in” on port 1521.
5) On Oracle Database Server there will be a listener running on 1521 port, listening to “orcl” service.
6) The listener will assign a server process to client.

General Issues we can face in connecting Oracle Client to a Oracle Database Server:
1) ORA-12154: TNS:could not resolve the connect identifier specified: When there is an issue in TNSNAMES.ORA like connect identifier entry for “ORCL” is not existing or proper in TNSNAMES.ORA.
2) ORA-12545: Connect failed because target host or object does not exist: “ngarg.mydomain.co.in” can not be reached over the network.
3) ORA-12541: TNS:no listener: There is no listener running on “ngarg.mydomain.co.in”
4) ORA-12560: TNS:protocol adapter error: There is no listener running on “ngarg.mydomain.co.in” at port 1521, or listener not properly configured.
5) ORA-12514: TNS:listener does not currently know of service requested in connect descriptor: Listener is running but is not configured for the service “orcl.mydomain.co.in”