Posted by Kamran Agayev A. on June 25th, 2009
Recently I’ve seen a very interesting article posted by Mr. Paul Sorensen in general Oracle Blog. This article was about CNN research on “Ten promising jobs for class of 2009“. I suggest you to read this post and be proud of your career choice 
Posted in Administration | No Comments »
Posted by Kamran Agayev A. on June 10th, 2009
The dba_scheduler_job_log view can be used to view log entries for previously executed jobs. This view displays log information for all dbms_scheduler jobs in the database. When creating a job, we define log_history parameter and logs in that view are available for number of days as it specified in log_history parameter (default is 30)
To read full article, refer to this link
Posted in Administration | 2 Comments »
Posted by Kamran Agayev A. on June 10th, 2009
A very important auditing task for any production database is the ability to track changes to the powerful initialization parameters. Many of these parameters are “silver bullets”, single parameters that have a profound impact on system-wide behavior. This is especially true for SQL optimizer changes
To read full article, refer to this link
Posted in Administration | No Comments »
Posted by Kamran Agayev A. on May 31st, 2009
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!
Posted in Administration, Oracle on Linux | 66 Comments »
Posted by Kamran Agayev A. on May 1st, 2009
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:
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:
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:
Let’s explain each option in details:
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
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
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
Posted in Administration, DBA scripts, Oracle on Linux | 227 Comments »
Posted by Kamran Agayev A. on April 6th, 2009
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
Posted in Oracle on Linux | 15 Comments »
Posted by Kamran Agayev A. on April 3rd, 2009
As I’m active user in Oracle forums, in one of the posts, OP asked a question about DML triggers to be written for all schemas. OP wanted to write a schema level DML trigger. As we now, it’s impossible to write schema level DML trigger. We can write DDL trigger to be fired on schema level.
Now, we have to choices.
1. To track all DML statements using Audit
2. To create one (INSERT, UPDATE, DELETE) trigger for all tables of the schema automatically
Its better to create audit for that tables, but as the OP wanted it to be solved in triggering way, I provided this type of solution:
In order to create trigger for each object, I followed below steps:
–I create a new user
SQL> create user test_trigger identified by kamran;
User created.
–Give it dba role (Don’t do it in production database)
SQL> grant dba to test_trigger;
Grant succeeded.
— Connect with that user
SQL> conn test_trigger/kamran
Connected.
— Create a new table
SQL> create table tab1 (id number);
Table created.
— Create a second table
SQL> create table tab2 (id number);
Table created.
— Create another table to log information which UPDATED, DELETED and INSERTED on two other tables
SQL> create table tab1_2_log (information varchar2(20));
Table created.
— Write a PL/SQL block to create a trigger for each table of the newly created user automatically
SQL> DECLARE
2 CURSOR all_tables
3 IS
4 SELECT table_name
5 FROM user_tables
6 WHERE table_name ‘TAB1_2_LOG’;
7
8
9 BEGIN
10 FOR rec_cur IN all_tables
11 LOOP
12 EXECUTE IMMEDIATE ‘create or replace trigger trg_’
13 || rec_cur.table_name
14 || ‘
15 before insert or update or delete on ‘
16 || rec_cur.table_name
17 || ‘
18 declare
19 begin
20 if UPDATING then
21 insert into tab1_2_log values(”UPDATING on ‘||rec_cur.table_name||”’);
22 elsif DELETING then
23 insert into tab1_2_log values(”DELETING on ‘||rec_cur.table_name||”’);
24 elsIF INSERTING then
25 insert into tab1_2_log values(”INSERTING on ‘||rec_cur.table_name||”’);
26 end if;
27 end;’;
28 END LOOP;
29 END;
30 /
PL/SQL procedure successfully completed.
1. Here, I create a cursor to take all tables in newly created schema
2. Open the cursor and get name of tables in my schema
3. Create EXECUTE IMMEDIATE statement and run creation of trigger by passing it the name of each table
Now, I begin to test my triggers by inserting to, updating and deleting data from tables
SQL> insert into tab1 values(1);
1 row created.
SQL> update tab1 set id=1;
1 row updated.
SQL> delete from tab1;
1 row deleted.
SQL> insert into tab2 values(1);
1 row created.
SQL> update tab2 set id=1;
1 row updated.
SQL> delete from tab2 ;
1 row deleted.
— Now, let’s check what’s in our log table
SQL> select * from tab1_2_log;
INFORMATION
——————–
INSERTING on TAB1
UPDATING on TAB1
DELETING on TAB1
INSERTING on TAB2
UPDATING on TAB2
DELETING on TAB2
6 rows selected.
— As it seen, all information related DELETING, UPDATING and INSERTING on tables have been gathered. Now, check name of triggers which was created on that schema to track all DML statement of each table
SQL> SELECT trigger_name, table_name FROM all_triggers WHERE table_owner=’TEST_TRIGGER’;
TRIGGER_NAME TABLE_NAME
—————————— ——————————
TRG_TAB1 TAB1
TRG_TAB2 TAB2
To get more information related Triggers, please refer to documentation :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/triggers.htm#i6061
Posted in DBA scripts | 5 Comments »
Posted by Kamran Agayev A. on March 23rd, 2009
Posted in Administration, Oracle on Linux | 9 Comments »
Posted by Kamran Agayev A. on February 23rd, 2009
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
Posted in Oracle on Linux | 12 Comments »
Posted by Kamran Agayev A. on February 22nd, 2009
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)
Posted in Oracle on Linux | 29 Comments »
