Sometimes we got some folders filled with unknown files either due to any bug where Oracle creates 100 files in a second or due to the OS bugs. And in some cases we’re not able to find in which folder files were created. For that, you can use the simplest command in Linux to get the output for the folders with its size sorted. Here’s the command:

du -s /u01/* | sort -n -r

.......

28G     /u01/oracle/product/10.2.0/db_1/flash_recovery_area

........

 

From the output you will get the folder which consumes more space

Today I asked our SysAdmin about a password of one of the servers, but unfortunately he wasn’t able to remember (and didn’t noted it in somewhere). So I decided to crack it using the following steps. It’s documented and is written in the Linux manual

http://www.redhat.com/docs/manuals/enterprise/RHEL-4-Manual/step-guide/s1-q-and-a-root-passwd.html

Today, I’ll show you how we can create a Database without Database Configuration Assistant (DBCA). We’re going to create the database manually, using CREATE DATABASE statement. Sometimes, we are not able to use GUI in order to create a database. For this, we should know syntax of database creation from SQL*Plus. To create database manually, follow below steps:    

1. Firstly, export Environment Variables. To export EV automatically for every session, do below changes to /home/oracle/.bashrc file:

 export ORACLE_SID=kamran

export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

 

 

2. Create parameter file and modify it by setting minimum required parameters:

*.db_name=kamran

*.db_block_size=8192

*.sga_target=1677721600

*.undo_management=’AUTO’

*.control_files = (‘/home/oracle/oracle/product/10.2.0/control01.ctl’)

*.user_dump_dest=’/home/oracle/oracle/product/10.2.0/udump’

*.background_dump_dest=’/home/oracle/oracle/product/10.2.0/bdump’

*.core_dump_dest=’/home/oracle/oracle/product/10.2.0/cdump’

After creation of this parameter file, create below folders in /home/oracle/oracle/product/10.2.0/ directory. Three of them are dump folders (needed for trace files and alert.log file). We’re going to keep Control Files and DataFiles in oradata folder.

-          oradata

-          udump

-          bdump

-          cdump

 

 3. Create Server parameter file (SPFILE) using this parameter file and STARTUP the instance in NOMOUNT mode.

 CREATE SPFILE FROM PFILE=’/home/oracle/oracle/product/10.2.0/init.ora’;

STARTUP NOMOUNT

  

Now our instance started, SGA allocated and background processes started

 

4. To create a new database, use the CREATE DATABASE statement. As a result, below files will be created:

-          Redo Log files

-          system.dbf and sysaux.dbf (files for SYSTEM tablespace)

-          undo01.dbf file (for UNDO tablespace)

-          temp_tbs.dbf file (for TEMPORARY tablespace)

-          users.dbf (for DEFAULT PERMANENT tablespace)

 

//######## Database Creation Code ####### 

CREATE DATABASE kamran

    USER SYS IDENTIFIED BY kamran

    USER SYSTEM IDENTIFIED BY kamran

LOGFILE GROUP 1 (‘/home/oracle/oracle/product/10.2.0/oradata/redo01.log’) SIZE 50 m,

    GROUP 2 (‘/home/oracle/oracle/product/10.2.0/oradata/redo02.log’) SIZE 50 m,

    GROUP 3 (‘/home/oracle/oracle/product/10.2.0/oradata/redo03.log’) SIZE 50 m

    MAXLOGFILES 5

    MAXLOGMEMBERS 5

    MAXLOGHISTORY 1

   MAXDATAFILES 100

   MAXINSTANCES 1

   CHARACTER SET us7ascii

   NATIONAL CHARACTER SET al16utf16

   DATAFILE ‘/home/oracle/oracle/product/10.2.0/oradata/system01.dbf’ SIZE 325 m REUSE EXTENT MANAGEMENT LOCAL

   SYSAUX DATAFILE ‘/home/oracle/oracle/product/10.2.0/oradata/sysaux.dbf’ SIZE 400 m REUSE

    DEFAULT TABLESPACE tbs_1 DATAFILE ‘/home/oracle/oracle/product/10.2.0/oradata/users.dbf’ SIZE 200m REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

    DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE ‘/home/oracle/oracle/product/10.2.0/oradata/temp_tbs.dbf’ SIZE 20m REUSE

    undo TABLESPACE undotbs DATAFILE ‘/home/oracle/oracle/product/10.2.0/oradata/undo01.dbf’ SIZE 200m REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

 

 

 5. Run the scripts necessary to build views, synonyms, and PL/SQL packages

CONNECT / AS SYSDBA

SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql

SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

 

6. Shutdown the instance and startup the database. Your database is ready for use!

 

Note: Before reading the post, you can have a look to my latest VIDEO instruction on Installing OEL and Oracle 10gR2

 

In order to use Oracle Database, first of all we need to setup Oracle Software. Installation of Oracle Database on Windows is very easy. By running setup.exe from installation CD of Oracle for Windows, we can invoke a setup and by clicking NEXT buttons we can install Oracle Software and Database without any problem. We don’t need any prerequisite actions before installation. But in Linux it’s different. If we want to install Oracle Database on Linux OS, we should follow some prerequisite actions.

Today, we’re going to install Oracle Database on CentOS. To do it, we use last version of CentOS (the latest release for now) – “CentOS-5.2” and mostly used release of Oracle Database – “Oracle Database 10g Release 2”. And we’re going to practice this whole project on VMware 6.0.0

Before starting, we need to install VMware. Then, we need to install CentOS on VMware. After that, we are going to install Oracle Database. You should refer to my previous posts in order to install VMware and Centos

Step by step installing VMware

Step by Step Installing CentOS on VMware

But there’s one thing we should keep in mind. During installation of CentOS, on the “package lists” page, we should behave differently. It will be discussed in the next paragraphs.  

This project covers following steps:

1. Checking minimum hardware requirements
2. Installing rpm packages which are required for Oracle installation
3. Configuring kernel parameters
4. Creating groups and user for Oracle Installation
5. Installing Oracle Software
6. Creating an Oracle Database
7. Connecting to Database with Enterprise Manager
8. AUTOMATING all processes and steps of installation Oracle 10g R2 on Centos using Shell Script

As you see from the list above, in order to setup Oracle Database, we need to change some parameters in the system. Changing these parameters each time could lead to mistakes and waste of time. The main purpose of our article is to automate all these processes and save your time. For this purpose we’re going to use “Shell Script”

Now I’m going to explain above mentioned steps one by one

1. Checking minimum hardware requirements

At least, your system should meet the following requirements:

-           1GB RAM 

-           Requirement for swap space in Oracle installation is as follows:

                Available RAM                                      Swap Space Required

                Between 1 GB and 2 GB        1.5 times the size of RAM

                Between 2 GB and 8 GB       Equal to the size of RAM

                More than 8 GB                     .75 times the size of RAM

-           400MB free space in /tmp directory

-           Depending on type of the installation, 1.5-3.5 GB free space for Oracle Software

-           1.5GB free space if new Oracle Database is created

Getting familiar with requirements mentioned above, we need to get hardware information of our system. To check the size of RAM, Swap space and tmp directory, we run these commands:

-           To check the size of physical memory, execute                             grep MemTotal     /proc/meminfo

-           To check the size of swap space, execute                                          grep SwapTotal     /proc/meminfo

-           To check the space in  /tmp directory, execute                            df –h /tmp

2. Installing rpm packages which are required for Oracle installation

While installing CentOS, we have to install some rpm packages. During the installation, on the installation window you get list of packages. Here, we select “Customize” choice

On “Customized” window, we check required packages and uncheck packages that are not required for Oracle Installation

In the packages list, check following packages. Uncheck all packages that are not in the list below

Desktop Environments

                                                  GNOME Desktop Environment  

Applications

                                                  Graphical Internet

Development

                                                  Development Libraries

                                                  Development Tools

                                                  GNOME Software Development

                                                  Java Development

                                                  Legacy Software Development

                                                  X Software Development

Servers

                                                  Server Configuration Tools

                                                  Web Server

                                                  Windows File Server

Base System

                                                  Administration Tools

                                                  Base

                                                  Java

                                                  Legacy Software Support

                                                  System Tools

                                                  X Window System

Furthermore, after installation of CentOS, we have manually to install these four rpm packages. Surely, you can select them (except libaio-devel package) from the package list during setup, for many people who don’t want to find these packages in the package list, installing it manually after system installation is the best option. These are packages which should be installed before Oracle installation

-           compat-db-4.2.52-5.1.i386.rpm

-           sysstat-7.0.2-1.el5.i386.rpm

-           libaio-devel-0.3.106-3.2.i386.rpm

-           libXp-1.0.0-8.1.el5.i386.rpm

In order to install these packages, you can use rpm –Uvh command by changing directory to CentOS directory inside the CD (DVD) of CentOS installation as shown below

3. Changes to be made to Kernel parameters

After installing above mentioned packages, we need to change some Kernel parameters and make them match to Oracle requirements. Parameters which should be changed are shown below

shmmax	2147483648
shmmni	4096
shmall	2097152
shmmin	1
semmsl	250
semmns	32000
semopm	100
semmni	128
file-max	65536
ip_local_port_range	1024   65000
rmem_default	1048576
rmem_max	1048576
wmem_default	262144
wmem_max	262144

 

We do all these changes in the /etc/sysctl.conf file by adding these lines to that file:

kernel.shmmax = 2147483648

kernel.shmall = 2097152

kernel.shmmni=4096

kernel.sem=250 32000 100 128

fs.file-max=65536

net.ipv4.ip_local_port_range=1024 65000

net.core.rmem_default=1048576

net.core.rmem_max=1048576

net.core.wmem_default=262144

net.core.wmem_max=262144

After appending those lines we save that file and run the following command to make these changes effective immediately in the running system

/sbin/sysctl –p

Setting Shell limits for the Oracle UserTo improve the performance of the software on Linux systems, you must increase the following shell limits for the oracle user:

1. Add the following lines to /etc/security/limits.conf file

oracle              soft    nproc   2047

oracle              hard    nproc   16384

oracle              soft    nofile  1024

oracle              hard    nofile  65536

2. Add the following lines to /etc/pam.d/login file

session    required     /lib/security/pam_limits.so

session    required     pam_limits.so

3. In order to use Oracle Software, we need to make a change in “oracle” user’s buffer size and number of opened file descriptors. In order to do it, we add below lines to /etc/profile file

if [ $USER = "oracle" ]; then

        if [ $SHELL = "/bin/ksh" ]; then

              ulimit -p 16384

              ulimit -n 65536

        else

              ulimit -u 16384 -n 65536

        fi

fi

4. Changing redhat-release file

One of the first checks performed by the Oracle Universal Installer (OUI) is to determine if the host platform is supported. The OUI uses the file /etc/redhat-release to determine the platform. For the case of Red Hat Enterprise Linux, Oracle Database 10g Release 2 expects either RHEL 3 or RHEL/OEL 4.

The easiest way to get around this error is to modify the /etc/redhat-release file replacing the current release information (CentOS release 5 (Final)) with the following:

redhat-4

Before modifying /etc/redhat-release, make a backup copy of the file and ensure to replace the original one after the Oracle installation and patch process has been completed.

cp /etc/redhat-release /etc/redhat-release.original

echo “redhat-4″ > /etc/redhat-release

After all these configurations, you should get this result:

4. Create groups and user for Oracle Installation

In this step, we create “oinstall” and “dba” groups and “oracle” user to install Oracle Software, and create new Database  

groupadd  oinstall

groupadd  dba

useradd -m -g oinstall -G dba -d /home/oracle -s /bin/bash -c “Oracle Software Owner” oracle

passwd oracle

5. Installing Oracle Database 10g Release 2

First of all, we need to download Oracle Database 10g R2. To download it, use this link:

http://download.oracle.com/otn/linux/oracle10g/10201/10201_database_linux32.zip

After download finish, we need to copy this file to the Virtual Machine. To copy it, we have two choices:

• Copy it using USB Flash Drive
• Copy it using Samba service
• Create an image file (.iso) from zipped installation and mount it to Virtual Machine

Here, we’re going to copy the zipped file using second technique. With any ISO creator program, create .iso file from zipped installation file of Oracle Database. And then mount it to the Virtual Machine as shown below:

Now switch to the desktop of CentOS, right click on DVD of CentOS installation on the desktop and click “Eject” as shown below:

Now enter to “Computer” and double click on “CD-ROM” icon.

Installation file of Oracle Database will be opened:

Now, create install folder on the /tmp directory,  change owner of this folder to “oracle”, copy this file into /tmp/install directory

Then unzip this file and begin installation as follows:

After unzip completes, installation will begin automatically

Oracle Database 10g Installation

If you want to create new database after software installation, check “Create Starter Database” checkbox and enter database name and password, then click Next

Specify Inventory directory (keep it as default) and click Next

Here we see that all Prerequisite Checks succeeded.

Click Install to begin installation

Now, we’re installing Oracle 10g Software.

6.  Creating an Oracle Database

As we’ve checked “Create Starter Database” at the first page of the installation, new database will be created automatically after software installation

After database created, you’ll get information about your database, Enterprise Manager and Spfile

Click Ok. In the below window, you’ll get location for two scripts which you should run as a root user to complete last configurations. Open new Terminal window and run those two scripts:

Installation of Oracle Database 10g completed successfully! Congratulations!

Now reboot your server and login as oracle user and start newly created database.  

If we want to login to our database, we run sqlplus. But before it, we need to set Environment Variables. We can do it automatically by adding them to .bashrc file in the /home/oracle directory as below:

export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$PATH

alias database =’export ORACLE_SID=kamran;sqlplus “/ as sysdba”‘

Now, let’s open new terminal and try again

As you see, after setting Environment Variables to their correct values, I was able to login to SQL*Plus using two ways.

7.   Connecting to Database with Enterprise Manager

Now, let’s start Oracle Enterprise Manager. In order to use EM, we should firstly start listener. In production environment, you need automate database, listener and EM startup. To automate them, please refer to one of my previous blogs – “Automatically StartUp and Shutdown an Oracle Database in Linux OS”

Start the listener

Then, start Enterprise Manager

Now, we can login to EM page to administer our database using above given address:

http://localhost.localdomain:1158/em/

Enter user sys and its password, then select “SYSDBA” as a role and click Login

Using Enterprise Manager, you can administer your database in very easy steps

That’s all!! Our Database and EM is ready for use! Congratulations!!!

By following above mentioned steps we were able to install Oracle 10g R2 on CentOS 5. But if we carry out frequent tests and as a result of these tests each time we are to install Oracle Database, then we need to automate installation of Oracle Database. I would suggest two options to overcome this problem:

1. To install CentOS+Oracle on VMware and copy image of VMware to elsewhere, then each time use this image to get fresh copy of Oracle Database
2. To automate installation of Oracle Database by using Shell Script

Let’s explain each option in details: 

1. Firstly, I usually create new Virtual Machine with 10GB size. Then, I install CentOS on it and create an Oracle Database with all its configurations. Then I shut down Virtual Machine and copy all the folders to another directory. Usually, I create two copies of the Virtual Machine. Then I do all my tests on the first Virtual Machine. When it becomes useless, I shut down and delete it as a whole folder, open second Virtual Machine which I’ve copied and continue my tests.
2. Second way is the best method of approach to the problem. For this, I create a shell script and write all steps and do all configuration changes from this script. By running this script once, all configurations needed for Oracle Installation will be changed automatically and we will only be asked for new oracle user’s password and next we’ll see installation page open.

 

Automating installation of Oracle Database 10g Release 2 on Centos 5 using Shell Script

To automate this job, we wrote a Shell Script. Before running this script, we should follow some steps as shown below: 

1. Firstly you should know that before running this script we should create install directory in /tmp directory and copy zipped installation file of Oracle to this directory  with this installation script

2. Before running this script we must be sure that installation DVD of CentOS has been inserted or installation DVD image of CentOS mounted to the system. You can check it by running this code:

 

3. After installation completes, change the following Environment Variables and alias name into proper names which you’ve used during installation in the .bashrc file

1. ORACLE_HOME  – If it is different folder, change it to correct value
2. ORACLE_SID – If it is different SID, change it to correct SID  
3. As an alias, you can assign whatever you want

If everything is OK as mentioned above, we change directory to /tmp/install folder and run install.sh file. By running this shell script, all configurations needed for installation will be automatically changed and only thing asked will be oracle user’s password. After that we’ll see Oracle Database installation page. By clicking Next buttons we’ll be able to install Oracle Software and Database very easily

Now, let’s install Oracle Database 10g using this Shell Script

First of all, we need to create install folder in the /tmp directory and copy zipped installation file of Oracle Database 10g to /tmp/install directory with install.sh script and check their existence. After that we need to mount Centos DVD once more, because we’re going to install some packages that were not installed with CentOS. After getting below screen, you can start installation

Here we see that

• We have zipped installation file of Oracle Database 10g in the /tmp/install directory
• We mounted CentOS DVD

 Now, switch to /tmp/install folder and run install.sh script

Here, enter oracle user’s password. Then click ok. After this step, zipped file will be unzipped

After it finishes unzipping, we’ll get Installation Window

That’s all! After getting this page, you should follow above mentioned installation steps in which we installed Oracle Database manually.

Using automatic install script we’ve avoided of all configuration settings and got Oracle 10gR2 installer page opened successfully

The automatic installation script for Oracle Database on Linux is as follows:

 

#########———— Installing Rpm files —–########

 

#Change directory to /tmp/install

cd /tmp/install

#Install all packages that are not installed during OS installation and that are required packages for Oracle Database 10gR2

echo “Installing rpm packages …”

 

rpm -Uvh “$(find /media/ -name compat-db*)”

rpm -Uvh “$(find /media/ -name sysstat*)”

rpm -Uvh “$(find /media/ -name libaio-devel*)”

rpm -Uvh “$(find /media/ -name libXp-1*)”

 

echo “Rpm packages installed

“

 

#Add lines to limits.conf file

echo “Changing limits.conf file”

cat >> /etc/security/limits.conf <<EOF

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

EOF

echo “limits.conf file changed successfully

“

 

#Add lines to profile to give maximum limit for Oracle user

echo “Changing /etc/profile file ….”

cat >> /etc/profile <<EOF

if [ \$USER = "oracle" ]; then

                                                  if [ \$SHELL = "bin/ksh" ]; then

                                                                ulimit -p 16384

                                                                ulimit -n 65536

                                                  else

                                                                ulimit -u 16384 -n 65536

                                                  fi

                                                  umask 022

fi

EOF

echo “/etc/profile file changed successfully

“

 

#Add line to /etc/pam.d/login file

echo “Changing /etc/pam.d/login file …”

cat >> /etc/pam.d/login <<EOF

session required /lib/security/pam_limits.so

EOF

echo “/etc/pam.d/login file changed successfuly

“

 

#Add some kernel parameters to /etc/sysctl.conf file

echo “Changing kernel parameters … “

 

cat >> /etc/sysctl.conf <<EOF

kernel.shmmax = 2147483648

kernel.shmall = 2097152

kernel.shmmni=4096

kernel.sem=250 32000 100 128

fs.file-max=65536

net.ipv4.ip_local_port_range=1024 65000

net.core.rmem_default=1048576

net.core.rmem_max=1048576

net.core.wmem_default=262144

net.core.wmem_max=262144

EOF

 

echo “Kernel parameters changed successfully

“

#Save all new kernel parameters

 

/sbin/sysctl -p

 

#Add “redhat-4″ line to /etc/redhat-release file

 

echo “Changing /etc/redhat-release file …”

cp /etc/redhat-release /etc/redhat-release.original

echo “redhat-4″ > /etc/redhat-release

echo “/etc/redhat-release file changed successfully

“

 

#Create new groups and “oracle” user and add this user to group

echo “Creating new groups and ‘oracle’ user …”

groupadd oinstall

groupadd dba

useradd -m -g oinstall -G dba -d /home/oracle -s /bin/bash -c “Oracle Software Owner” oracle

passwd oracle

echo “Groups and user created successfully

”

#Adding Environment Variables

#Adding Environment Variables

cat >> /home/oracle/.bashrc <<EOF

export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$PATH

alias mydb=’export ORACLE_SID=mydb;sqlplus “/ as sysdba”‘

export ORACLE_SID=mydb

EOF

EOF

 

 

#Unzip setup of Oracle

echo “Unzipping setup of Oracle 10g Release 2…. “

unzip 10201_database_linux32.zip

echo “Setup file successfully unzipped

“

#Enter to installation directory and run the installation …

echo “Installation begins …”

cd /tmp/install/database

chmod 755 runInstaller

chmod 755 install/.oui

chmod 755 install/unzip

xhost +

sudo -u oracle /tmp/install/database/runInstaller

Sometimes, we need to mount a Windows folder on Linux. In order to do it, let’s follow these steps:

1. Create a directory in Windows, share it and give full permission:

2. Create new directory in /tmp directory on Linux, name it to “test_dir” and mount shared Windows directory into that directory by running below code:

mount -t cifs -o username=#####,password=###### //192.168.##.##/test   /tmp/test_dir

3. Now, by creating new file and folder in Windows directory, we can get them from Linux box. Let’s create a file and a directory in Windows folder

4. At last, let’s check it from Linux, whether we can see the file and the directory or not

As you can see, we can mount any Windows folder in Linux with very easy steps

Recently, I was required by our company to solve an important task related to subscriber payments. 
The subscribers transfer their payment through some post offices. Despite the fact that these post offices use our online payment transfer system, however some of them use their own programs to make payments. In order to view the list of our subscribers and from time to time to update list of subscribers, they need to add new subscribers to their database system. We were also asked to send newly created subscriber codes automatically to these post offices. To put this into practice I followed the steps mentioned below

Firstly, I decided that, I have to:

1. Provide SQL script to prepare subscribers list
2. To make daily schedule
3. To make schedule to run the above mentioned SQL script and write it to a file
4. When prepared, make this file zipped and mailed automatically to relevant person

On the end of this task we’ll be familiar with
1. Creating a file based on SQL script using Oracle UTL_FILE built in Package
2. Creating shell script which runs Sql procedure
3. Sending e-mail with attachment using Oracle UTL_SMTP built in Package
4. Using cron to schedule a job in Linux
5. Combine all together and automate them

Lets implement above mentioned steps by explaining them one by one

1. First of all we create SQL script and get the list of subscriber codes.  As it is not permissible to view our main table’s structure online, we create virtual code table and use it during this process

CREATE TABLE subscriber_list (kod VARCHAR2(5))
/
INSERT INTO subscriber_list VALUES(’11111′)
/
INSERT INTO subscriber_list VALUES(’65498′)
/
INSERT INTO subscriber_list VALUES(’78946′)
/
INSERT INTO subscriber_list VALUES(’55666′)
/
INSERT INTO subscriber_list VALUES(’46667′)
/
COMMIT

Our SQL script will be as simple as our table

SELECT * FROM subscriber_list;

2. Each time when this SQL script is called, its output should be written to file and file name should bear today’s date

In order to create this file we use UTL_FILE package and create a procedure. To create the file in a proper directory, we need to create a directory object

CREATE DIRECTORY export_dir AS ‘/home/oracle/export’;

And create the following procedure to create the file:

CREATE OR REPLACE PROCEDURE proc_export_file AS
        CURSOR cur_kod IS
                SELECT kod from subscriber_list;

        CURSOR cur_date IS
                SELECT sysdate FROM dual;

rec1 cur_kod%ROWTYPE;
rec2 cur_date%ROWTYPE;
created_file_name VARCHAR2(100);
file_name UTL_FILE.FILE_TYPE;

BEGIN
 –Here, we open cur_date cursor, get today’s date and set it to created_file_name variable
 OPEN cur_date;
       
 LOOP
                FETCH cur_date INTO rec2;
                EXIT WHEN cur_date%NOTFOUND;
                created_file_name:=rec2.sysdate;
 
  –We send parameter ‘w’ (to create the file), file name and directory name to UTL_FILE.FOPEN function to create this file with .exp extention
       
         file_name:=UTL_FILE.FOPEN(‘EXPORT_DIR’,created_file_name||’.exp’,'W’);
                OPEN cur_kod;
                LOOP

   –We open our mail cursor and get all rows and put it to our file line by line
                 FETCH cur_kod INTO rec1;
                        EXIT WHEN cur_kod%NOTFOUND;
                        UTL_FILE.PUTF(file_name,’%s\n’,rec1.kod);
                END LOOP;
                UTL_FILE.FCLOSE(file_name);
        END LOOP;

CLOSE cur_kod;

CLOSE cur_date;
END proc_export_file;
/

Here, we declared 2 cursors. One of them set today’s date to a variable called created_file_name. Then we create a file using UTL_FILE.FOPEN and ‘w’ parameter, give it a name which we have obtained from the first cursor (today’s date) and then open our second cursor to get all rows from subscriber table and insert them to file with .exp extension

3. In this step we create a shell script which calls above mentioned procedure. Its name will be export_code.sh. We give it execute permission to make it executable

[oracle@localhost ~]

# We move to /home/oracle directory
cd /home/oracle  

#Create new directory to save file that will be created using proc_export_file procedure
mkdir export

#Give it permission
chmod 755 export

#Change directory
cd export

#Create export_code.sh file
touch export_code.sh

#Give it executable permission
chmod 755 export_code.sh

#Open it in order to change its source
vi export_code.sh

4. Shell Script’s source is as follows (Don’t forget to change your ORACLE_SID value in this script)

###################### Shell Script to create file using procedure, zip and send it as an attachment to relevant address ####################
#!/bin/sh

#Export Oracle Environment Variables
export ORACLE_HOME=/home/oracle/OraHome_1
export ORACLE_SID=#######
export PATH=/home/oracle/OraHome_1/bin:$PATH

#Open sqlplus and connect as sys user with sysdba privilege
$ORACLE_HOME/bin/sqlplus /nolog <<EOF
connect / as sysdba

#Run proc_export_file procedure to create file
exec proc_export_file;
exit;
EOF

#Change directory to /home/oracle/export
cd /home/oracle/export

#Find newly created .exp file, take its name, zip it by using its name with tar utility
tar -cvf “$(ls *.exp).tar” “$(ls *.exp)”

#Remove original .exp file,
rm -rf *.exp

#Get list of tar file in /home/oracle/export directory. As there’s only one file, the file name will be set to tar_file variable
tar_file=’/home/oracle/export/*.tar’

#Here we connect to sqlplus and use email_files procedure to send mail
$ORACLE_HOME/bin/sqlplus /nolog <<EOF
connect / as sysdba
exec email_files(from_name=>’kamran.agayev@bes.az’,to_names=>’kamran.agayev@bes.az’, subject=> ‘Subscriber codes’,filename1=>’$(printf $tar_file)’, filetype1=> ‘application/x-tar’);
exit
EOF

#Remove .tar file, because we’ve just sent it as an attachment
rm -rf /home/oracle/export/*.tar

######################### Shell Script to create file using procedure, zip and send it as an attachment to relevant address ##########################

Let’s examine this script step by step

In the first three lines, we set Oracle environment variables to their proper values to use sqlplus and connect to our database. Then we call SqlPlus with nolog option. The code that between <<EOF and EOF,  will be used by SqlPlus. Firstly, we connect to our database as sys user and call our newly created procedure named proc_export_file to create a file with subscriber codes and then exit SqlPlus

Then we switch to the directory where our file have been created and zip it. As our file is created with .exp extension and it is only one file in this directory with such extension, we list it and get its name by “$(ls *.exp)” command. By using tar command with cvf parameter and “($ls *.exp).tar” command, we find the file with .exp extension and zip it using its own name and by adding tar extension

Then, as we’ve obtained zipped format of .exp file, we delete original .exp file with rm -rf *.exp command

In the next step, we create variable tar_file and get the name of only one newly created tar file from /home/oracle/export directory and set it to this variable

Then again, we connect to SqlPlus with sys user and run email_files procedure. This procedure, is used to send mail with attachment to defined recipient. In the from_name parameter we write our mail, in the to_name parameter we write recipient mail, in the subject parameter we write mail’s subject, in the message parameter, we write mail’s text and in the filename1 parameter, we write newly created zipped file’s name. As we’ve set its name to tar_file variable, we can use this variable inside our script, using $(printf $tar_file) command

In order to create email_files procedure, we use below mentioned code. The v_smtp_server and v_smtp_server_port variables which we use in this procedure, are our SMPT Server’s IP address and Port number. Change it and make it to be correct values to be able to send mail

———– This procedure used to send file as attachment using UTL_SMTP package ————

create or replace procedure email_files(from_name varchar2,
                      to_names varchar2,
                      subject varchar2,
                      message varchar2 default null,
                      html_message varchar2 default null,
                      cc_names varchar2 default null,
                      bcc_names varchar2 default null,
                      filename1 varchar2 default null,
                      filetype1 varchar2 default ‘text/plain’,
                      filename2 varchar2 default null,
                      filetype2 varchar2 default ‘text/plain’,
                      filename3 varchar2 default null,
                      filetype3 varchar2 default ‘text/plain’)
is

   — Change the SMTP host name and port number below to your own values,
   — if not localhost on port 25:

   smtp_host          varchar2(256) := ‘####################’;
   smtp_port          number := 25;

   boundary           constant varchar2(256) := ‘CES.Boundary.DACA587499938898′;

   recipients         varchar2(32767);
   directory_path     varchar2(256);
   file_name          varchar2(256);
   crlf               varchar2(2):= chr(13) || chr(10);
   mesg               varchar2(32767);
   conn               UTL_SMTP.CONNECTION;
   type varchar2_table is table of varchar2(256) index by binary_integer;
   file_array         varchar2_table;
   type_array         varchar2_table;
   i                  binary_integer;

   PROCEDURE split_path_name(file_path IN VARCHAR2, directory_path OUT VARCHAR2,
      file_name OUT VARCHAR2) IS

      pos number;

   begin

      — Separate the filename from the directory name

      pos := instr(file_path,’/',-1);
      if pos = 0 then
         pos := instr(file_path,’\',-1);
      end if;
      if pos = 0 then
         directory_path := null;
      else
         directory_path := substr(file_path,1,pos – 1);
      end if;
      file_name := substr(file_path,pos + 1);

   end;

   — Procedure to append a file’s contents to the e-mail

   PROCEDURE append_file(directory_path IN VARCHAR2, file_name IN VARCHAR2,
      file_type IN VARCHAR2, conn IN OUT UTL_SMTP.CONNECTION) IS

      generated_name  varchar2(30) := ‘CESDIR’ || to_char(sysdate,’HH24MISS’);
      directory_name  varchar2(30);
      file_handle     utl_file.file_type;
      bfile_handle    bfile;
      bfile_len       number;
      pos             number;
      read_bytes      number;
      line            varchar2(1000);
      data            raw(200);
      my_code         number;
      my_errm         varchar2(32767);

   begin

      begin

         — Grant access to the directory, unless already defined, and open
         — the file (as a bfile for a binary file, otherwise as a text file).
   
         begin
            line := directory_path;
            select dd.directory_name into directory_name from dba_directories dd
               where dd.directory_path = line and rownum = 1;
         exception
            when no_data_found then
               directory_name := generated_name;
         end;
         if directory_name = generated_name then
            execute immediate ‘create or replace directory ‘ || directory_name ||
               ‘ as ”’ || directory_path || ””;
            execute immediate ‘grant read on directory ‘ || directory_name ||
               ‘ to public’;
         end if;
         if substr(file_type,1,4) != ‘text’ then
            bfile_handle := bfilename(directory_name,file_name);
            bfile_len := dbms_lob.getlength(bfile_handle);
            pos := 1;
            dbms_lob.open(bfile_handle,dbms_lob.lob_readonly);
         else
            file_handle := utl_file.fopen(directory_name,file_name,’r');
         end if;
   
         — Append the file contents to the end of the message
   
         loop
   
            — If it is a binary file, process it 57 bytes at a time,
            — reading them in with a LOB read, encoding them in BASE64,
            — and writing out the encoded binary string as raw data
   
            if substr(file_type,1,4) != ‘text’ then
               if pos + 57 – 1 > bfile_len then
                  read_bytes := bfile_len – pos + 1;
               else
                  read_bytes := 57;
               end if;
               dbms_lob.read(bfile_handle,read_bytes,pos,data);
               utl_smtp.write_raw_data(conn,utl_encode.base64_encode(data));
               pos := pos + 57;
               if pos > bfile_len then
                  exit;
               end if;
   
            — If it is a text file, get the next line of text, append a
            — carriage return / line feed to it, and write it out
   
            else
               utl_file.get_line(file_handle,line);
               utl_smtp.write_data(conn,line || crlf);
            end if;
     
         end loop;
   
      — Output any errors, except at end when no more data is found
   
      exception
         when no_data_found then
            null;
         when others then
            my_code := SQLCODE;
            my_errm := SQLERRM;
            dbms_output.put_line(‘Error code ‘ || my_code || ‘: ‘ ||
               my_errm);
      end;

      — Close the file (binary or text)

      if substr(file_type,1,4) != ‘text’ then
         dbms_lob.close(bfile_handle);
      else
         utl_file.fclose(file_handle);
      end if;
      if directory_name = generated_name then
         execute immediate ‘drop directory ‘ || directory_name;
      end if;

   end;

begin

   — Load the three filenames and file (mime) types into an array for
   — easier handling later

   file_array(1) := filename1;
   type_array(1) := filetype1;
   — Open the SMTP connection and set the From and To e-mail addresses

   conn := utl_smtp.open_connection(smtp_host,smtp_port);
   utl_smtp.helo(conn,smtp_host);
   recipients := from_name;
   utl_smtp.mail(conn,recipients);
   recipients := to_names;
   utl_smtp.rcpt(conn,recipients);
   utl_smtp.open_data(conn);

   — Build the start of the mail message

   mesg := ‘Date: ‘ || TO_CHAR(SYSDATE,’dd Mon yy hh24:mi:ss’) || crlf ||
      ‘From: ‘ || from_name || crlf ||
      ‘Subject: ‘ || subject || crlf ||
      ‘To: ‘ || to_names || crlf;
   mesg := mesg || ‘Mime-Version: 1.0′ || crlf ||
      ‘Content-Type: multipart/mixed; boundary=”‘ || boundary || ‘”‘ ||
      crlf || crlf ||
      ‘This is a Mime message, which your current mail reader may not’ || crlf ||
      ‘understand. Parts of the message will appear as text. If the remainder’ || crlf ||
      ‘appears as random characters in the message body, instead of as’ || crlf ||
      ‘attachments, then you”ll have to extract these parts and decode them’ || crlf ||
      ‘manually.’ || crlf || crlf;
   utl_smtp.write_data(conn,mesg);

   — Write the text message or message file, if any

   if message is not null then
      mesg := ‘–’ || boundary || crlf ||
         ‘Content-Type: text/plain; name=”message.txt”; charset=US-ASCII’ ||
          crlf ||
         ‘Content-Disposition: inline; filename=”message.txt”‘ || crlf ||
         ‘Content-Transfer-Encoding: 7bit’ || crlf || crlf;
      utl_smtp.write_data(conn,mesg);
      if substr(message,1,1) = ‘/’ then
         split_path_name(message,directory_path,file_name);
         append_file(directory_path,file_name,’text’,conn);
         utl_smtp.write_data(conn,crlf);
      else
         utl_smtp.write_data(conn,message || crlf);
      end if;
   end if;

   — Append the files
      if file_array(1) is not null then

         split_path_name(file_array(1),directory_path,file_name);

         — Generate the MIME boundary line according to the file (mime) type
         — specified.

         mesg := crlf || ‘–’ || boundary || crlf;
         if substr(type_array(1),1,4) != ‘text’ then
            mesg := mesg || ‘Content-Type: ‘ || type_array(1) ||
               ‘; name=”‘ || file_name || ‘”‘ || crlf ||
               ‘Content-Disposition: attachment; filename=”‘ ||
               file_name || ‘”‘ || crlf ||
               ‘Content-Transfer-Encoding: base64′ || crlf || crlf ;
         else
            mesg := mesg || ‘Content-Type: application/octet-stream; name=”‘ ||
               file_name || ‘”‘ || crlf ||
               ‘Content-Disposition: attachment; filename=”‘ ||
               file_name || ‘”‘ || crlf ||
               ‘Content-Transfer-Encoding: 7bit’ || crlf || crlf ;
         end if;
         utl_smtp.write_data(conn,mesg);

         — Append the file contents to the end of the message

         append_file(directory_path,file_name,type_array(1),conn);
         utl_smtp.write_data(conn,crlf);

      end if;
  

   — Append the final boundary line

   mesg := crlf || ‘–’ || boundary || ‘–’ || crlf;
   utl_smtp.write_data(conn,mesg);

   — Close the SMTP connection

   utl_smtp.close_data(conn);
   utl_smtp.quit(conn);

end;
/

———– This procedure used to send file as attachment using UTL_SMTP package ————

5. Finaly we have SQL script, procedure that writes its result to file, shell script that runs this procedure, zips and sends the file as attachment to required mail

In the end, we need to create schedule to automatically implement it independently. In Linux we can do it with cron. We need to create cron, make it run within time interval

In Linux system, we need to follow below mentioned steps to create a cron with any username

[oracle@localhost ~] crontab /etc/crontab
With this command we make /etc/crontab file as oracle user’s default cron file. Based on the text of this file, crond service defines the processes and their run times

No we change this crontab’s text and make it to run our shell script in our desired time. To change this file’s text, run:

[oracle@localhost ~]crontab -e

We have to note that, when you run crontab -e command, standard editor vi is opening. In order to change the file opened with this editor, type “i” and change the file

To make shell script run every night, we add this line into our cron file

0 0 * * * /home/oracle/export/export_code.sh

When changes made, type “:wq” to save these changes and exit

To create a specific cron setting you need to understand a bit about the way a command is written for cron. The best way to help you understand is to show you some examples.

Remember that a cron entry is specified in the following sequence: Minute (0-59)  Hour (0-23)  Day of Month (1-31)  Month (1-12 or Jan-Dec)  Day of Week (0-6 or Sun-Sat)  Command

The line we added will run the shell script named export_code.sh file at midnight, of every month, for all days of the week. The 0 is in place of Hour and minutes, and represents midnight (0-23 of a 24 hour clock).

[oracle@localhost ~] crontab -l
By running crontab -l command, we can see connected user’s crontab file entry

Our script is ready to use. Let’s run and test it
[oracle@localhost ~] ./export_code.sh

Now, this script will run, create a file, zip it and send it as attachment to our mail

Today I would like to introduce you some articles related to advanced Linux commands. These commands will be useful when working with Oracle Database

There’re 5 articles that I would like to introduce you

Guide to Linux File Command Mastery (Sheryl Calish) 

Guide to Advanced Linux Command Mastery – part 1 (Arup Nanda)

Guide to Advanced Linux Command Mastery – part 2 (Arup Nanda)

Guide to Advanced Linux Command Mastery – part 3 (Arup Nanda)

UNIX Commands for DBAs (Tim Hall) 

   

Today, I’m going to show you, installation of CentOS on a virtual machine

First of all, we’ve to download CentOS. CentOS is a freely-available operating system that is based on Red Hat Enterprise Linux system.  You can download the latest release (5) from its website. To download, please refer to download page of CentOS web site

http://isoredirect.centos.org/centos/5/isos/i386/

Select nearest location and begin downloading *.iso files.

After you finish downloading *.iso files, you have to mount it and let your VMware recognize it as a virtual “cd” or “dvd”. For this purpose, I prefer to download one DVD *.iso installation file. Please open VMware, double click on CD-ROM (IDE 1-0) item, select Use ISO image option and click Browse button to select your recently downloaded *.iso installation file of CentOS as shown below

 

Now you virtually inserted you installation DVD into DVD-ROM. And you’re ready to install your OS virtually.  

 

You should change your Memory to 1024 MB for your further Oracle installation. Double click on Memory item and set it to 1024

Then click Power button to start your virtual pc

  

When your virtual pc starts, you’ll see welcome screen of installation CentOS

 

 

If you’ll not be able to see this screen, then restart your virtual pc and click Esc button for Boot menu. And from this menu, select CD-ROM Drive

Click enter. The installation will tell you to begin testing of your installation cd or dvd. Select Skip button. It will skip testing your cd media and save your time

 

Then you’ll get another welcome screen, you only have to click next button on this screen

 

Select your language of installation and click Next

 

Then, select your keyboard configuration to use it for the system you’re installing and click Next

 

Then, you’ll be warned to create new partitions by erasing ALL DATA on your newly created hard drive. As there’s no information in this drive, you’ll select Yes

 

In this step, just let it remain selected and click next

 

 

Then there’ll be warning “You have chosen to remove all Linux partitions (and ALL DATA on them) on the following drives: /dev/sda   Are you sure you want to do this?” Click Yes

Then, you’ll see Network Configurations screen, as it is your test virtual pc, don’t change anything and click next. You can change whatever you want after installation, for now, let it continue in this way and click Next

 

 

From this screen, select your region and click Next

 

 

Then, you’ll be prompted to enter password for root user, enter it and click Next

  

 

To install packages, especially for working with Oracle Server, select all options except Clustering and Storage Clustering. Then select Customize now option and click Next

 

 

Then, from Base System menu, select Legacy Software Support, System Tools and click Next

 

Now, you’re ready for installation. Click next on this screen, the installation will begin

 

You’ll see how all packages are installing. After a while, your OS will be ready for use

 

After the installation is completed, click Reboot button to reboot your OS

 

After reboot, you’ll face with another Welcome screen. There’re some steps need to be completed

Click Forward button on the screen.

 

Here, just disable your Firewall. Its only for test purpose, don’t disable your Firewall on real system! And click forward button

 

For testing purpose, it would be good to disable SELinux too

 

Then keeping Kdump disabled, click Forward

 

 

If you need, make your Date and Time configuration and click Forward

 

 

If you want to create a new user, you can fill these blanks. You can pass this step too by clicking Forward button

 

 

Then Click Finish and the system will reboot

 

 

After reboot, CentOS will be opened and you’ll be prompted to enter username (root) and password (root’s password)

 

 

 

Your Centos is ready to use! Congratulations.

 

 

In the next article, I’m going to show installation of Oracle 10g Release 2 on our Centos OS

 

Additionally, while using an operating system inside VMware, you recognize that every time you have to enter to its area and then press Ctrl+Alt buttons to leave this area and to return to your system.

To solve this issue, you should install VMware Tools.

As the VMware Tools will be installed from CD-Rom device, you should disconnect your connected CD-Rom device as shown below

 

 

Next, Select VM menu on VMware application and select Install VMware Tools menu item.

 

 

Then click Install button on this message box to Install VMware Tools

 

 

 

Then enter to My Computer and double click on CD-ROM Drive. You’ll see there two packages.

 

 

Now, double click on *.rpm file to setup   

After installation complete, copy another *.gz file to anywhere in hard drive. In our example we are coping it to tmp directory inside FileSystem, paste the file into this folder, right click on it and select Extract Here menu item

 

 

Then enter to the folder vmware-tools-distrib and double click on vmware-install.pl file and click Run in Terminal button

 

 

Then, whenever you’re asked to enter the value, just click enter

But when you’re prompted to enter value for the display size of your X, be careful to enter default value. If you want your Virtual Machine to be little size, enter 2 (800-600)

That’s all! Your VMware Tools installed. Now you can enter to VMware’s area and exit it without clicking Ctrl+Alt

P.S. If you’ll face with “VMware hgfs: HGFS is disabled in the host” error, just reboot your system

As we know, if you want to setup Oracle Server – especially for testing purpose – it would be better to setup it on your “virtual PC”, not on PC directly. For this purpose, you can use software named “VMware” to achieve this aim. In this document, firstly we discuss how we can use VMware, how we can setup it, create a new “Virtual PC”. On the next articles, we’ll discuss how to install OS (as an operating system, I’m using Centos) on this virtual machine. CentOS is a freely-available operating system that is based on Red Hat Enterprise Linux system. Then we’ll try to setup Oracle Server (10g Release 2) on this virtual machine

In this article, let’s show how we can setup and create new VMware Virtual machine and prepare it for further OS installation

1. First of all, enter to the official site of VMware and download VMware Workstation with a 30-day free evaluation from this address:

https://www.vmware.com/tryvmware/?p=workstation-w

Currently I’m using version 6.0.0 and all these examples are compatible and tested with this version

2. After downloading VMware workstation, install it on your PC

3. Setup will open Welcome Screen

Click on Next button and choose Typical option

 

4. By clicking “Next” buttons, to begin the installation, click on Install button at the end

5. This will install VMware Workstation software on your PC, After installation complete, click on Finish button. Then restart your PC. Then open this software

 

 

6. In this step we try to create new “virtual machine”. Enter to File menu, then New-> Virtual Machine

Click on Next button, then check Typical option as below

Then click Next button, and check your OS version. In this example, as we’re going to setup Oracle server on CentOS, we’ll check Linux option and from “version” option we’ll check Red Hat Enterprise Linux 4

By clicking Next button, we’ll give a name to our virtual machine, and give directory to create this new virtual machine

 

 

Then select Use bridged networking option and click Next.

 

 

Then you’ve to define size of hard disk by entering its size. I’ll give 15 GB hard disk space and please check Allocate all disk space now option.  Although it will take longer to create new hard disk, you’ll see high performance of your virtual machine when installing new applications and adding new files. If you uncheck this option, new hard disk will be created automatically, but when you install new applications, it will impact your performance, because it will allocate space each time new files added or applications installed

That’s all. Your virtual machine is ready to use

 

 

 

Here, you can delete Sound Adapter, Floppy and USB Controller by entering “Edit virtual machine settings”. If you’re going to setup Oracle Server, please make sure you’ve increased your Memory (RAM) to 1GB

In the next article, we’ll show how to setup CentOS on this newly created virtual machine. It’s very easy, believe me J