Kamran Agayev’s Oracle Blog

Kamran Agayev’s Oracle Blog

Archive for the ‘Oracle on Linux’ Category

Change forgotten password of the root user at the Linux server

Posted by Kamran Agayev A. on November 14, 2009

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

Advertisements

Posted in Administration, Oracle on Linux | 6 Comments »

Create Database Manually – Step by Step instruction

Posted by Kamran Agayev A. on May 31, 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

 manual_1

 

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

manual_2

 

 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

 manual_3 

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

 

manual_4 

 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!

 manual_5

Posted in Administration, Oracle on Linux | 27 Comments »

Step by Step Installing Oracle Database 10g Release 2 on Linux (CentOS) and AUTOMATE the installation using Linux Shell Script

Posted by Kamran Agayev A. on May 1, 2009

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

oracle_install1

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

oracle_install2

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

oracle_install3

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

oracle_install4

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:

oracle_install5

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

oracle_install6

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:

oracle_install7

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

oracle_install8

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

oracle_install9

Installation file of Oracle Database will be opened:

oracle_install10

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:

oracle_install11

After unzip completes, installation will begin automatically

oracle_install12

Oracle Database 10g Installation

oracle_install13

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

oracle_install14

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

oracle_install15

Here we see that all Prerequisite Checks succeeded.

oracle_install16

Click Install to begin installation

oracle_install17

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

oracle_install18

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

oracle_install19

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:

oracle_install20

Installation of Oracle Database 10g completed successfully! Congratulations!

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

oracle_install21

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”‘

oracle_install22

Now, let’s open new terminal and try again

oracle_install23

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

oracle_install24

Then, start Enterprise Manager

oracle_install25

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

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

oracle_install26

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

oracle_install27

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:

 oracle_install28

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

oracle_install29

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

oracle_install30

oracle_install31

oracle_install32

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

oracle_install33

After it finishes unzipping, we’ll get Installation Window

oracle_install34

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 | 200 Comments »

Mount Windows folder on Linux

Posted by Kamran Agayev A. on April 6, 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:

mount_1

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

mount_23

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

mount_3

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

mount_4

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

Posted in Oracle on Linux | 7 Comments »

Step by Step multiplexing Control File of Oracle Database by adding new hard drive to Linux

Posted by Kamran Agayev A. on March 23, 2009

Each Oracle DBA has to secure the database against data loss. In the first place, files forming database’s physical structure have to be secured. As we know the physical structure of Oracle Database mainly consist of Control Files, Redo Log files, Data files and Archived Redo Log files. Today, I’m going to show you how to protect control files which are important for database, as well as the way to restore control file in case it is lost

“Loss of Control file” means corruption of hard disk in which Control Files are locate. Oracle DBA should configure database so that in case Control file located hard disk get corrupted, it is able to restore the database with survived one within short span of time possible.

When we say “loss of Control file”, we mean corruption of hard drive where Control files locate.

Today we’re going to analyze three different projects related to Control File backup and recovery

1.       As Control File has been multiplexed, in spite of its loss we’ll start our database with survived one stored in another hard disk

2.       After we get “creation script” of control file, we create new control file using that script and start our database when all Control Files are lost

3.       After we get binary copy of the Control File, we recover our database using archived redo log files in spite of some changes made to database after the binary backup of the Control file

Project 1

First of all, we create new “virtual hard disk”. Then we multiplex Control Files and put the multiplexed Control file into “virtual hard disk”. Then we remove this hard disk and show how the control file is lost and get error during startup of database. Then as the control file has been multiplexed, we retrieve Control file from another hard drive that is safe and open the database

My task plan is as follows:

1.    Mounting additional hard drive into Linux

2.    Multiplexing Control File

3.    Removing newly added hard drive, loss of control file, dysfunction of database

4.    Retrieval of Control file

Before using Control files, we need to know “what the Control file is and why it’s so useful for database?”

Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. The control file includes:

·                        The database name

·                        Names and locations of associated data files and redo log files

·                        The timestamp of the database creation

·                        The current log sequence number

·                        Checkpoint information

The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

When we install Oracle database, it automatically creates 3 copies of Control file in the same directory as a default

My first step will be adding new hard disk to locate a copy of Control file. In order to do it, shutdown VMware and by entering “Edit Virtual machine Settings” window click on “Add”button as shown below

control_file11

Here, select “Hard Disk” and click on Next

control_file2

Here, leave the option to be as default and Click on “Next”

control_file3

Click on Next and leave the option to be as default “SCSI”

control_file4

Specify disk capacity as “100MB”. So type “0.1” (GB) in the text field and check “Allocate all disk space now” checkbox

control_file5

Here, specify destination where you want to save information of new hard disk and click on “Finish” button. Now, you can see new hard disk being created with 100Mb size

control_file6

After applying new hard disk into VMware, we start OS. Unlike Windows, in Linux newly applied hard disk should be mounted to system in order to be recognized by OS. In order to mount it to OS, we follow the following steps:

1. In Linux, SCSI disks are labled /dev/sda, /dev/sdb, /dev/sdc etc… to represent the first, second, third,… SCSI hard drives

/dev/sda is hard disk where OS and Oracle are installed. Its size is 15GB. The hard disk that newly created is /dev/sdb. We should mount it to our system. We use fdisk program to create a partition and mount this hard drive to our system. To mount this hard disk to any folder, we need to create that folder  

[root@localhost ~]# cd /

[root@localhost /]# mkdir /control_file_folder

Here, new folder named “/control_file_folder” created in the root directory. Now using fdisk program, create a new partition

[root@localhost /]# /sbin/fdisk /dev/sdb

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklab

Building a new DOS disklabel. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won’t be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): m  (Click m to view all commands)

Command action

   a   toggle a bootable flag

   b   edit bsd disklabel

   c   toggle the dos compatibility flag

   d   delete a partition

   l   list known partition types

   m   print this menu

   n   add a new partition

   o   create a new empty DOS partition table

   p   print the partition table

   q   quit without saving changes

   s   create a new empty Sun disklabel

   t   change a partition’s system id

   u   change display/entry units

   v   verify the partition table

   w   write table to disk and exit

   x   extra functionality (experts only)

 

Command (m for help): n

Command action

   e   extended

   p   primary partition (1-4)

e

Partition number (1-4): 1

First cylinder (1-102, default 1): 1

Last cylinder or +size or +sizeM or +sizeK (1-102, default 102):

Using default value 102

Command (m for help): p

Disk /dev/sdb: 106 MB, 106954752 bytes

64 heads, 32 sectors/track, 102 cylinders

Units = cylinders of 2048 * 512 = 1048576 bytes

Device Boot      Start         End      Blocks   Id  System

/dev/sdb1               1         102      104432    5  Extended

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

 

Now using mkfs program, build Linux file system on this device. mkfs  is used to build a Linux file system on a device, usually a hard disk partition

[root@localhost /]# mkfs -t ext3 /dev/sdb

mke2fs 1.35 (28-Feb-2004)

/dev/sdb is entire device, not just one partition!

Proceed anyway? (y,n) y

Filesystem label=

OS type: Linux

Block size=1024 (log=0)

Fragment size=1024 (log=0)

26208 inodes, 104448 blocks

5222 blocks (5.00%) reserved for the super user

First data block=1

Maximum filesystem blocks=67371008

13 block groups

8192 blocks per group, 8192 fragments per group

2016 inodes per group

Superblock backups stored on blocks:

        8193, 24577, 40961, 57345, 73729

Writing inode tables: done

Creating journal (4096 blocks): done

Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 30 mounts or 180 days, whichever comes first.  Use tune2fs -c or -i to override.

Now that the new hard drive is partition and formated, the last step is to mount the new drive. Mount the new hard drive on the directory /control_file_folder using mount command

[root@localhost /]# mount -t ext3 /dev/sdb /control_file_folder/

[root@localhost /]# cd /control_file_folder/

[root@localhost control_file_folder]# df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/mapper/VolGroup00-LogVol00

                       14G  9.6G  3.4G  74% /

/dev/sda1              99M   13M   82M  14% /boot

none                  252M     0  252M   0% /dev/shm

/dev/sdb               99M  5.6M   89M   6% /control_file_folder

[root@localhost control_file_folder]#

2. Enter the drive into the fstab file so that it is recognized and mounted upon system boot

/dev/sdb      /control_file_folder auto   defaults      1 2

Now reboot your system and after reboot, run

[root@localhost /]# df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/mapper/VolGroup00-LogVol00

                       14G  9.6G  3.4G  74% /

/dev/sda1              99M   13M   82M  14% /boot

none                  252M     0  252M   0% /dev/shm

/dev/sdb               99M  5.6M   89M   6% /control_file_folder

As seen, the newly created hard drive was mounted automatically to the folder /control_file_folder after reboot

3. In this step, multiplex the Control File. For this, we need to follow the following steps:

SQL> CREATE PFILE=’/tmp/pfile.ora’ FROM SPFILE;

File created.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 – Production

With the Partitioning, OLAP and Data Mining options

[oracle@localhost /]$ vi /tmp/pfile.ora

Here, change CONTROL_FILES parameter and indicate the second control file on the newly created hard disk, which mounted to /control_file_folder

*.control_files=’/home/oracle/product/10.1.0/oradata/qafqaz/control01.ctl’,’/control_file_folder/control02.ctl’

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host

[oracle@localhost /]$ su root

Password:

[root@localhost /]# chown -R oracle:oinstall /control_file_folder/

[root@localhost /]# exit

exit

[oracle@localhost /]$ cp /home/oracle/product/10.1.0/oradata/qafqaz/control01.ctl /control_file_folder/control02.ctl

[oracle@localhost /]$ cd /control_file_folder/

[oracle@localhost control_file_folder]$ ls -lh

total 2.8M

-rw-r—–  1 oracle oinstall 2.8M Mar 14 23:56 control02.ctl

[oracle@localhost control_file_folder]$

[oracle@localhost control_file_folder]$ sqlplus “/as sysdba”

SQL*Plus: Release 10.1.0.3.0 – Production on Sat Mar 14 23:57:15 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile=’/tmp/pfile.ora’;

File created.

SQL> startup

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                   778212 bytes

Variable Size              61874204 bytes

Database Buffers          104857600 bytes

Redo Buffers                 262144 bytes

Database mounted.

Database opened.

SQL> show parameter control_files

NAME                TYPE        VALUE

——————- ———- ———-

control_files       string      /home/oracle/product/10.1.0/oradata/qafqaz/control01.ctl, /control_file_folder/control02.ctl

SQL>

4. Now let’s remove that hard disk and see what is going to happen to database

Before removing hard disk, shutdown OS

control_file7

Select new hard disk on the main page and click on “Remove” button to remove that hard disk

control_file8

Then start the system. Open new terminal and type “df –h” to verify that hard disk was not mounted

control_file9

As you can see, the hard disk created and mounted a while ago in the list has disappeared. This is because the Control file located on that hard disk has been deleted. Since the Control File has been deleted, the database doesn’t start

To verify this, connect to database and try to start it

[oracle@localhost /]$ sqlplus “/as sysdba”

SQL*Plus: Release 10.1.0.3.0 – Production on Sun Mar 15 00:57:19 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                   778212 bytes

Variable Size              61874204 bytes

Database Buffers          104857600 bytes

Redo Buffers                 262144 bytes

ORA-00205: error in identifying controlfile, check alert log for more info

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01507: database not mounted

As seen from above, database didn’t start. To check the reason, open alert.log file to investigate it. Use tail -20 command to get last 20 lines of the file. To exit from Sql*Plus by keeping it connected, use host command. This will take you to OS terminal, and by running exit command, you can return to Sql*Plus easily and you don’t need to connect once again to database

SQL> host

[oracle@localhost /]$ tail -20 /home/oracle/product/10.1.0/Db_1/admin/qafqaz/bdump/alert_qafqaz.log

SMON started with pid=7, OS id=4374

RECO started with pid=8, OS id=4376

CJQ0 started with pid=9, OS id=4378

Sun Mar 15 00:57:22 2009

starting up 1 dispatcher(s) for network address ‘(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))’…

starting up 1 shared server(s) …

Sun Mar 15 00:57:22 2009

ALTER DATABASE   MOUNT

Sun Mar 15 00:57:22 2009

ORA-00202: controlfile: ‘/control_file_folder/control02.ctl’

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Sun Mar 15 00:57:22 2009

Controlfile identified with block size 16384

Sun Mar 15 00:57:25 2009

ORA-205 signalled during: ALTER DATABASE   MOUNT…

Sun Mar 15 00:57:29 2009

alter database open

ORA-1507 signalled during: alter database open…

[oracle@localhost /]$

At the moment the database doesn’t open. To solve this problem you need to copy survived control file to /conrol_file_folder directory

In order to do it, we follow the following steps:

[oracle@localhost /]$ cp /home/oracle/product/10.1.0/oradata/qafqaz/control01.ctl /control_file_folder/control02.ctl

[oracle@localhost /]$ sqlplus “/ as sysdba”

SQL*Plus: Release 10.1.0.3.0 – Production on Sun Mar 15 01:05:25 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 – Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                   778212 bytes

Variable Size              61874204 bytes

Database Buffers          104857600 bytes

Redo Buffers                 262144 bytes

Database mounted.

Database opened.

SQL> select status from v$instance;

STATUS

————

OPEN

SQL>

From these steps it can be seen that as our control files have already been multiplexed, so you can start you database without error even though hard drives where control files located are corrupted

But sometimes there might be some cases when you lose all your control files because of single fault. In that case you should crest control files using sql command

Project 2

Lets create “creation script” of Control File and then delete all Control files. After observing database corruption, we create new Control file by using “creation script” of Control file and open database  

For this, we follow the following steps

1. Initially, we get “creation script” of Control file. To get this script, run the following command:

SQL> alter database backup controlfile to trace as ‘/tmp/trace.txt’;

Database altered.

SQL> host

 [oracle@localhost /]$ ls -lh /tmp/trace.txt

-rw-r–r–  1 oracle oinstall 5.8K Mar 19 09:38 /tmp/trace.txt

[oracle@localhost /]$

2. Then find destination where Control files are stored and delete them all

SQL> select name from v$controlfile;

NAME

——————————————————————————–

/home/oracle/OraHome_1/oradata/qafqaz/control01.ctl

/home/oracle/OraHome_1/oradata/qafqaz/control02.ctl

/home/oracle/OraHome_1/oradata/qafqaz/control03.ctl

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host

[oracle@localhost /]$ ls -lh /home/oracle/OraHome_1/oradata/qafqaz/*.ctl

-rw-r—–  1 oracle oinstall 2.8M Mar 19 09:41 /home/oracle/OraHome_1/oradata/qafqaz/control01.ctl

-rw-r—–  1 oracle oinstall 2.8M Mar 19 09:41 /home/oracle/OraHome_1/oradata/qafqaz/control02.ctl

-rw-r—–  1 oracle oinstall 2.8M Mar 19 09:41 /home/oracle/OraHome_1/oradata/qafqaz/control03.ctl

[oracle@localhost /]$ rm -rf  /home/oracle/OraHome_1/oradata/qafqaz/*.ctl

[oracle@localhost /]$ exit

exit

SQL> startup

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size                   779580 bytes

Variable Size             397679300 bytes

Database Buffers         1207959552 bytes

Redo Buffers                4194304 bytes

ORA-00205: error in identifying controlfile, check alert log for more info

SQL> host

[oracle@localhost /]$ tail -10 /home/oracle/OraHome_1/admin/qafqaz/bdump/alert_qafqaz.log

ALTER DATABASE   MOUNT

Thu Mar 19 09:47:28 2009

ORA-00202: controlfile: ‘/home/oracle/OraHome_1/oradata/qafqaz/control01.ctl’

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Thu Mar 19 09:47:28 2009

Controlfile identified with block size 0

Thu Mar 19 09:47:28 2009

ORA-205 signalled during: ALTER DATABASE   MOUNT…

[oracle@localhost /]$

As you have already seen from the above mentioned steps, when Control files are deleted, database doesn’t open. Therefore , we can run “creation script” of Control file to create new Control file.  For this, we open this file with any editor, then copy CREATE CONTROLFILE command and run it in Sql*Plus to create Control file. We can see it from below steps

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “QAFQAZ” NORESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 454

LOGFILE

  GROUP 1 ‘/home/oracle/OraHome_1/oradata/qafqaz/redo01.log’  SIZE 10M,

  GROUP 2 ‘/home/oracle/OraHome_1/oradata/qafqaz/redo02.log’  SIZE 10M,

  GROUP 3 ‘/home/oracle/OraHome_1/oradata/qafqaz/redo03.log’  SIZE 10M

— STANDBY LOGFILE

DATAFILE

  ‘/home/oracle/OraHome_1/oradata/qafqaz/system01.dbf’,

  ‘/home/oracle/OraHome_1/oradata/qafqaz/undotbs01.dbf’,

  ‘/home/oracle/OraHome_1/oradata/qafqaz/sysaux01.dbf’,

  ‘/home/oracle/OraHome_1/oradata/qafqaz/users01.dbf’

CHARACTER SET WE8ISO8859P1;

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size                   779580 bytes

Variable Size             397679300 bytes

Database Buffers         1207959552 bytes

Redo Buffers                4194304 bytes

SQL>  

Control file created.

 

SQL> shutdown immediate

ORA-01109: database not open

 

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size                   779580 bytes

Variable Size             397679300 bytes

Database Buffers         1207959552 bytes

Redo Buffers                4194304 bytes

Database mounted.

Database opened.

SQL> select name from v$controlfile;

NAME

——————————————————————————–

/home/oracle/OraHome_1/oradata/qafqaz/control01.ctl

/home/oracle/OraHome_1/oradata/qafqaz/control02.ctl

/home/oracle/OraHome_1/oradata/qafqaz/control03.ctl

SQL> select status from v$instance;

 

STATUS

————

OPEN

In above mentioned example, as we’ve got creation script of Control file, even all Control files are lost,  however we can create new Control files using this script and open database successfully.

Project 3

Now, we are going to learn how to handle another troubleshooting. In the first place, please note that its one of strict recommendations of Oracle to run the database in archive log mode. It’s not possible to recover database running in NOARCHIVELOG mode. The scenario of this project is as follows:

Let us assume that we have a database running in ARCHIVELOG mode. We backup Control File of this database as a binary mode. After making some changes on the database and creating archived redo log files, instantly we lose all control files. As the binary copy of control file was taken, by recovering database using archived redo logs, we can open database using binary copy of Control File  

SQL> host

[oracle@localhost root]$ cd /home/oracle/OraHome_1/oradata/qafqaz/

[oracle@localhost qafqaz]$ ls

arch           control02.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf

control01.ctl  control03.ctl  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

[oracle@localhost qafqaz]$ rm -rf arch/

[oracle@localhost qafqaz]$ mkdir arch

[oracle@localhost qafqaz]$ pwd

/home/oracle/OraHome_1/oradata/qafqaz

[oracle@localhost qafqaz]$ exit

exit

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 1610612736 bytes

Fixed Size                   779580 bytes

Variable Size             431233732 bytes

Database Buffers         1174405120 bytes

Redo Buffers                4194304 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter system set log_archive_dest_1=’location=/home/oracle/OraHome_1/oradata/qafqaz/arch’ scope=both;

System altered.

SQL> alter database open;

Database altered.

SQL> select log_mode from v$database;

LOG_MODE

————

ARCHIVELOG

SQL> alter system switch logfile;

System altered.

SQL> select name from v$archived_log;

NAME

——————————————————————————–

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_6_681908323.dbf

SQL> alter system switch logfile;

System altered.

SQL> select name from v$archived_log;

NAME

——————————————————————————–

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_6_681908323.dbf

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_7_681908323.dbf

SQL> alter database backup controlfile to ‘/tmp/control01.ctl’;

Database altered.

SQL> select name from v$controlfile;

NAME

——————————————————————————–

/home/oracle/OraHome_1/oradata/qafqaz/control01.ctl

/home/oracle/OraHome_1/oradata/qafqaz/control02.ctl

/home/oracle/OraHome_1/oradata/qafqaz/control03.ctl

SQL> create table t1 (id number);

Table created.

SQL> begin

  2  for i in 1..1000 loop

  3  insert into t1 values(i);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select count(1) from t1;

  COUNT(1)

———-

      1000

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> begin

  2  for i in 1..1000 loop

  3  insert into t1 values(i);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

 

SQL> select count(1) from t1;

  COUNT(1)

———-

      2000

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host

[oracle@localhost root]$ rm -rf /home/oracle/OraHome_1/oradata/qafqaz/*.ctl

[oracle@localhost root]$ cp /tmp/control01.ctl /home/oracle/OraHome_1/oradata/qafqaz/control01.ctl

[oracle@localhost root]$ cp /tmp/control01.ctl /home/oracle/OraHome_1/oradata/qafqaz/control02.ctl

[oracle@localhost root]$ cp /tmp/control01.ctl /home/oracle/OraHome_1/oradata/qafqaz/control03.ctl

[oracle@localhost root]$ exit

exit

SQL> startup

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size                   779580 bytes

Variable Size             414456516 bytes

Database Buffers         1191182336 bytes

Redo Buffers                4194304 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: ‘/home/oracle/OraHome_1/oradata/qafqaz/system01.dbf’

 

SQL> recover database using backup controlfile;

ORA-00279: change 571494 generated at 03/19/2009 11:48:59 needed for thread 1

ORA-00289: suggestion :

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_8_681908323.dbf

ORA-00280: change 571494 for thread 1 is in sequence #8

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

 

ORA-00279: change 572763 generated at 03/19/2009 11:54:23 needed for thread 1

ORA-00289: suggestion :

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_9_681908323.dbf

ORA-00280: change 572763 for thread 1 is in sequence #9

ORA-00278: log file

‘/home/oracle/OraHome_1/oradata/qafqaz/arch/1_8_681908323.dbf’ no longer needed

for this recovery

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

 

ORA-00279: change 572768 generated at 03/19/2009 11:54:35 needed for thread 1

ORA-00289: suggestion :

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_10_681908323.dbf

ORA-00280: change 572768 for thread 1 is in sequence #10

ORA-00278: log file

‘/home/oracle/OraHome_1/oradata/qafqaz/arch/1_9_681908323.dbf’ no longer needed

for this recovery

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

 

ORA-00308: cannot open archived log

‘/home/oracle/OraHome_1/oradata/qafqaz/arch/1_10_681908323.dbf’

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

SQL> host

[oracle@localhost root]$ ls /home/oracle/OraHome_1/oradata/qafqaz/arch/

1_6_681908323.dbf  1_7_681908323.dbf  1_8_681908323.dbf  1_9_681908323.dbf

[oracle@localhost root]$ exit

exit

 

SQL> recover database using backup controlfile

ORA-00279: change 572768 generated at 03/19/2009 11:54:35 needed for thread 1

ORA-00289: suggestion :

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_10_681908323.dbf

ORA-00280: change 572768 for thread 1 is in sequence #10

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/home/oracle/OraHome_1/oradata/qafqaz/redo01.log

ORA-00310: archived log contains sequence 8; sequence 10 required

ORA-00334: archived log: ‘/home/oracle/OraHome_1/oradata/qafqaz/redo01.log’

 

SQL> recover database using backup controlfile

ORA-00279: change 572768 generated at 03/19/2009 11:54:35 needed for thread 1

ORA-00289: suggestion :

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_10_681908323.dbf

ORA-00280: change 572768 for thread 1 is in sequence #10

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/home/oracle/OraHome_1/oradata/qafqaz/redo02.log

ORA-00310: archived log contains sequence 9; sequence 10 required

ORA-00334: archived log: ‘/home/oracle/OraHome_1/oradata/qafqaz/redo02.log’

 

SQL> recover database using backup controlfile

ORA-00279: change 572768 generated at 03/19/2009 11:54:35 needed for thread 1

ORA-00289: suggestion :

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_10_681908323.dbf

ORA-00280: change 572768 for thread 1 is in sequence #10

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/home/oracle/OraHome_1/oradata/qafqaz/redo03.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

 

SQL> select count(1) from t1;

  COUNT(1)

———-

      2000

SQL>

 

In the previous project we enabled archived log mode, took binary copy of Control File, made some changes on database, generated archived redo logs and by using these files recovered the database and started it in spite of loss of all Control files.

The reason for applying archived redo log files is that the control file change sequence number in the data file is greater than the number in the control file. This implies that the wrong control file is being used. That’s why, we used archived redo logs in our recovery process

Posted in Administration, Oracle on Linux | 6 Comments »

Using Oracle UTL_FILE, UTL_SMTP packages and Linux Shell Scripting and Cron utility together

Posted by Kamran Agayev A. on February 23, 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 »