Day 6: Tip Log_Archive_Dest_1 Set To Default in Oracle Database 11.1

Posted by Paola Pullas | Posted in Base de Datos, Oracle, Tips | Posted on 03-10-2010

Tags: , , , ,


In this post I want to share with you the information about the Bug 6373164 present in Oracle Database 11.1. This bug was fixed in release


I configured Archivelog Mode in my database and set the flash recovery area like the default location to store achivelog files. I configured the next parameters in order to set the flash recovery area:


Other parameters related to Archivelog Mode aren’t configured in my linux box, for example:

  • LOG_ARCHIVE_DEST is not set
  • LOG_ARCHIVE_DEST_n is not set

In the next screen I show you my actual configuration:

When the database starts to generate archivelog files, these was send to flash recovery area location but additionally to $ORACLE_HOME/dbs causing higher disk space utilization in my server, like I show you in the next screen:

How to solve the problem

In order to solve this problem you should change the configuration of LOG_ARCHIVE_DEST_1 parameter with the next command:

  • alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

Author: Paola Pullas
Do you need to buy support?: Contact me at

If you think that this tutorial helped you. Make a donation to this initiative. We appreciate your support. Also your donation can help me to buy more coffee or Red Bull.

Day 5: Configuring ARCHIVELOG Mode

Posted by Paola Pullas | Posted in Administración, Base de Datos, Oracle | Posted on 05-09-2010

Tags: , , , , , ,


Before to review this tutorial review this links:

Configuring the Flash Recovery Area

The flash recovery area is a storage location for all recovery related files. All files that are needed to completely recover a database from a media failure are part of the flash recovery area. Some files in the flash recovery area are: control files, redo logs, archived logs, backup pieces, image copies, flashback logs and foreign archived logs.

By allocating a storage location and unifying related recovery files within a specific area, the Oracle database server relieves the database administrator from having to manage the disk files created by these components.

When setting up a flash recovery area, you choose a directory, file system or Automatic Storage Management disk group to hold the files, and set a disk quota for the maximum space to be used for all files stored in the flash recovery area.

The flash recovery area should be on a separate disk from the working area. Keeping the flash recovery area on the same disk as the working area exposes you to loss of both your live database files and backups in the event of a disk failure.

You can execute the next command from SQL*Plus in order to verify the actual configuration of flash recovery area:

  • show parameter db_recovery_file_dest

If you want to change this configuration you can execute the alter system command from SQL*Plus to set the values that are more appropriate for your environment:

  • alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' scope=both;
  • alter system set db_recovery_file_dest_size='2G' scope=both;

The first parameter shows you the physical directory of the flash recovery area and the second parameter shows you the quota or size assigned for this area. Keep in mind that quota should be bigger enough to keep the recovery files.

Another method to verify the flash recovery area configuration is using Enterprise Manager Dbconsole going to Availability –> Recovery Settings like I show you in the next screens.

Configuring the Archivelog Mode from SQL*Plus

The firs step is make a clean shutdown of your database using any of the next commands from SQL*Plus:

  • shutdown immediate;
  • shutdown normal;
  • shutdown transactional;

Then you should use the next commands from SQL*Plus in order to mount the database, configure the archivelog mode and finally open the database:

  • alter database mount;
  • alter database archivelog;
  • alter database open;

Next I will verify if my configuration is working properly with the next commands:

  • select log_mode from v$database;
  • alter system archive log current;

In the first screen the command verify the log mode of the database and in the second screen I am forcing the archiving process in the database:

Configuring the Archivelog Mode from Enterprise Manager Dbconsole

Login in Enterprise Manager Dbconsole page.

Go to Recovery Settings link under Availability tab.

Go to Media Recovery section and check the ARCHIVED Mode option then click in Apply button.

A Confirmation screen will appear after click in Apply button, then click in Yes button.

The next screen will appear and you should fill the text boxes with the os and database users information, in my case the os user is oracle and the database user with SYSDBA privilege is sys, then click in OK button.

The next screens will appear after this operation. You should wait a few minutes until the process finished. In order to know that this complete successfully you should refresh your browser until the login screen appears.

Author: Paola Pullas
Do you need to buy support?: Contact me at

If you think that this tutorial helped you. Make a donation to this initiative. We appreciate your support. Also your donation can help me to buy more coffee or Red Bull.

Day 4: How to submit SQL commands to Oracle database

Posted by Paola Pullas | Posted in Administración, Base de Datos, Oracle | Posted on 05-09-2010

Tags: , , , , , , ,


There are different tools to access Oracle database in order to submit SQL commands. In this section I will show you 3 Oracle tools that you have with no extra cost. Before to review this tutorial review this links:


The firs tool is SQL*Plus, this is a command-line interface tool to access to Oracle database. You use SQL*Plus to execute many administrative tasks for example: start up and shut down the database, set database initialization parameters, create and manage users, create and alter database objects (such as tables and indexes), and more. In addition SQL*Plus can be used to insert and update data and run SQL queries.

The format to establish a connection using SQL*Plus is:

  • CONN[ECT] [logon] [AS {SYSOPER | SYSDBA}]
  • {username | /}[@connect_identifier]

In the next screen I will show you some examples in order to gain access to database from SQL*Plus.

  • sqlplus /nolog

  • connect sys/oracle as sysdba

  • conn sys/oracle@eva as sysdba

  • conn system/oracle

  • connect system/oracle@eva

  • connect system/oracle as sysdba

  • conn system/oracle as sysoper

  • conn hr/hr

  • conn hr/hr@eva

Enterprise Manager Dbconsole

Oracle Enterprise Manager Dbconsole is an intuitive graphical interface to administer the database.

In order to gain access to Enterprise Manager you should be a user defined like administrator in the application dbconsole. In the next screen I will show you the users that can login in the Oracle Enterprise Manager Dbconsole. In order to acess this screen you should login in Enterprise Manager Dbconsole like sys user and then go to Setup link in the right upper corner of the application:

In the next screens I will show you some examples in order to login in Enterprise Manager Dbconsole. The url to access have the format https://server_ip:em_port/em. If you don’t know the port to access the application you can find this in the file portlist.ini located in $ORACLE_HOME/install:

SQL Developer

Start the application with sqldeveloper command from a operating system terminal, and next create a connection in the graphical interface like I show you in the next screens:

Author: Paola Pullas
Do you need to buy support?: Contact me at

If you think that this tutorial helped you. Make a donation to this initiative. We appreciate your support. Also your donation can help me to buy more coffee or Red Bull.

Day 3: How to install SQL Developer in Linux

Posted by Paola Pullas | Posted in Aplicaciones, Base de Datos, Oracle | Posted on 04-09-2010

Tags: , , , , ,


In this post I will show you how to install Oracle SQL Developer in a Linux box. First, you should download the SQL Developer software from

Next, if you downloaded the software without JDK included, you need to download a JDK 1.6.11 version or above from

Install JDK

First install the JDK version in your Linux box in any directory. I will install the JDK in /u01/app/oracle like I show you in the next screens:

  • chmod a+x jdk-6u-linux-i586.bin
  • ./jdk-6u-linux-i586.bin

Install SQL Developer

Then install SQL Developer rpm using the next command:

  • rpm -Uhv sqldeveloper-

Next, verify if the file jdk contains the JDK’s path. This file is located in $HOME/.sqldeveloper. If the JDK’s path that you installed previously isn’t registered in the jdk file, the first time that you launch the application, you should be prompted to provide the path.

Finally execute sqldeveloper with the next command:

  • sqldeveloper

Author: Paola Pullas
Do you need to buy support?: Contact me at

If you think that this tutorial helped you. Make a donation to this initiative. We appreciate your support. Also your donation can help me to buy more coffee or Red Bull.

Paola Pullas’s Oracle Magazine Interview

Posted by Paola Pullas | Posted in Noticias, Opinión, Oracle | Posted on 24-08-2010

Tags: , , , , , , ,


Oracle Magazine Interview published at:


As Published In
Oracle Magazine
September/October 2010

Stronger Together
By Jeff Erickson

A growing Ecuadorian user group builds ties with its Latin American neighbors.

Paola Pullas knows how to do a lot with a little. With the nearest Oracle corporate office in neighboring Colombia and user group members spread across a geographically diverse nation, Pullas, president of the Ecuador Oracle Users Group (ECUOUG), is helping to forge a single Oracle user group that can attract top speakers and make its voice heard.

ECUOUG represents more than 200 corporate members in the country’s major industries, including mining, construction, and telecommunications, as well as agricultural production of crops such as bananas, cocoa, and flowers. “We have three main telecommunications companies in Ecuador, and all of them are members of our group,” says Pullas. “But there are many smaller businesses, such as in our flower industry, that are coming to us.”

Across the Stack

The group holds as many as 12 meetings a year, mostly in Quito, Ecuador’s capital city, and mostly on subjects such as Oracle Database, Oracle WebLogic Server, and Oracle JDeveloper. But the group is expanding to support other parts of the technology stack. “We’ve found that lots of midsize companies in Ecuador use [Oracle’s] JD Edwards, and many of our members use [Oracle’s] PeopleSoft and Siebel,” says Pullas. “We are in the process of launching an applications user group within ECUOUG.”

With Oracle’s acquisition of Sun, ECUOUG is also reaching out to Sun users through a new Sun special interest group and is working with an existing Java user group to fold it into ECUOUG. “As a small country, we want to bring all Oracle users, Sun, applications, Java, and tech under one roof,” says Pullas.

With members dotted across Ecuador, the ECUOUG community has become adept at using social media to communicate. The group is easy to find and follow on Facebook, Twitter, and Oracle Mix. Its blog, at, was the online foundation from which the current user group was formed, and it is still a great place to keep up with Oracle technologists in Ecuador. “We organize our live event each month to share experiences and network because we want to cover all the subjects and we want this community to stay together,” says Pullas. “In between events, we go online to share tutorials, tips, and news.”

Banding Together

On the heels of a successful Oracle OpenWorld conference in São Paulo, Brazil, in 2009, the Latin American user group community began working together to keep the momentum going. User group leaders organized a Latin American user group council, with each member country providing a board member. Pullas is the board member from Ecuador. Current member countries include Argentina, Brazil, Chile, Colombia, Costa Rica, Mexico, Peru, Uruguay, and Ecuador.

For 2010 the group has organized what Pullas calls a Latin American Oracle Technology Network tour. The plan, says Pullas, is for smaller countries to hold their annual meetings in quick succession so that top speakers can make a swing through Latin America and visit several user groups along the way. Top Oracle ACEs from the U.S. and Europe and speakers from Oracle Consulting are expected to make the trip—even Oracle architect and Oracle Magazine columnist Tom Kyte is on the bill in some countries. Ecuador’s event is scheduled for October. “We are now working like one big integrated community,” says Pullas. “We know that Latin American technologists are a big presence in the Oracle world, and this community will be a voice for sharing our experiences with members in neighboring countries and with Oracle.”

Day 2: Creating an Oracle Database 11g

Posted by Paola Pullas | Posted in Base de Datos, Oracle, Refundation, Unix/Linux | Posted on 23-08-2010

Tags: , , , , , ,


First ensure that you installed the Oracle Software according the tutorial in In this post I show you how to create an Oracle Database.

Listener Creation

When an instance starts, a listener process establishes a communication pathway to Oracle Database. When a user process makes a connection request, the listener determines whether it should use a shared server dispatcher process or a dedicated server process and establishes an appropriate connection.

In order to create a listener open a terminal in your linux box, logged in like oracle user, and call the Net Configuration Assistant with command netca.

I the next screens I will show you step by step the process to create a listener with the next information:

  • Listener Name: LISTENER

  • Protocol: TCP

  • Port: 1521

Continue with the wizard until the Finish button appears, then Click Finish.

Database Creation

After the listener creation I will continue with database creation. Open a terminal in your linux box, logged in like oracle user, and call the Database Configuration Assistant with command dbca.

In the next screen named Welcome click Next.

In the screen 1 of 14 named Operations select Create Database and click Next.

In the screen 2 of 14 named Database Templates select General Purpose and Transaction Processing template and click Next. This template contains a pre-created database, so the database could be created in minutes, as opposed to an hour or more. In case that you require to change some database characteristics like block size you should create a Custom Database because this attribute can’t be changed with templates.

In the screen 3 of 14 named Database Identification fill the text boxes with the Global Database Name and SID and click Next. In this example I will use the next information:

  • Global Database Name:

  • SID eva

In the screen 4 of 14 named Management Options check the options:

  • Configure Enterprise Manager
  • Configure Database Control for local management

Then click Next. I won’t select the other options because these can be configured later.

In the screen 5 of 14 named Database Credentials select the option Use the Same Administrative Password for All Accounts and click Next. In this example I will use this option for easily remember the password but in productions environments we recommend choose a different password for each account:

In the screen 6 of 14 named Storage Options select the option File System and click Next. In this example I will use this option because I don’t the other options configured in my linux box, we recommend evaluate the use of Automatic Storage Management in production environments:

In the screen 7 of 14 named Database File Locations select the option Use Common Location for All Database Files, fill the text box with the desired location, in this case I will use the location $ORACLE_BASE/oradata for this example, and click Next. Don’t remember to evaluate if the other options are valid for your production environment.

In the screen 8 of 14 named Recovery Configuration check the option Specify Flash Recovery Area and click Next. I will use the next information:

  • Flash Recovery Area: {ORACLE_BASE}/flash_recovery_area

  • Flash Recovery Area Size: 2048 (MB)

I won’t check Enable Archiving option because I will configure this option later.

In the screen 9 of 14 named Database Content check the option Sample Schemas and click Next. I recommend not install the sample schemas in production environments:

In the screen 10 of 14 named Initialization Parameters you can configure: Memory, Sizing, Character Sets and Connection Model. In the next screens I will show you the options that I chose for this screen, in all cases I am using the default configuration that can be changed after the database is created. When you finished click Next.

In the screen 11 of 14 named Security Settings I will keep the settings by default and click Next.

In the screen 12 of 14 named Automatic Maintenance Tasks I will keep the settings by default and click Next.

In the screen 13 of 14 named Database Storage you can review the configuration of controlfiles, datafiles, and redo log files. When you finished click Next.

In the screen 14 of 14 named Creation Options check the option Create Database and click Finish.

A confirmation screen will be displayed and you can save this information like an HTML file. When you finished click OK.

If the database is created successfully a new screen will appear showing you the configuration information. When you finished click Exit.

How to Monitor Database from Enterprise Manager Dbconsole

In the next screen I will show you how to review the Enterprise Manager Console service.

While I was developing this post I noticed that the wizard has a bug in the graphical interfaces. There are only 14 screens in the wizard but the firsts screens show you like there are 15 screens :-) .

Author: Paola Pullas
Do you need to buy support?: Contact me at

If you think that this tutorial helped you. Make a donation to this initiative. We appreciate your support.

Day 1: Installing Oracle Database 11g on Red Hat Linux 5.1 (32 bits)

Posted by Paola Pullas | Posted in Base de Datos, Refundation, Unix/Linux | Posted on 20-08-2010

Tags: , , , , , ,


This guide describes how to install Oracle Database 11g Release 1 on Red Hat Enterprise Linux 5.1.

In order to install Oracle Database in production systems, we recommend to read the official installation guide in

Hardware Requirements

Oracle says that the system must have at least 1GB of RAM and for the swap space you should use the next sizes:

  • If you physical memory is between 1 GB and 2 GB, swap should be 1.5 times the size of RAM.
  • If you physical memory is between 2 GB and 16 GB, swap should be equal to the size of RAM.
  • If you physical memory is more than 16 GB, swap should be 16 GB.

To check the size of physical memory, execute:

grep MemTotal /proc/meminfo

To check the size of swap space, execute:

grep SwapTotal /proc/meminfo

Software Requirements

The next screen show you the list of packages needed to install Oracle Database 11g Release 1. You can find this information in the official installation guide:

Please note that some packages are missing in the previous list. In the next images I show you the configuration of my Red Hat Linux box for your reference. To determine whether the required packages are installed I use a command similar to the following:

# rpm -qa package_name
# rpm -qa binutils* compat-libstdc++* elfutils-libelf* gcc* glibc* libaio* libgcc* libstdc* make* numactl-devel* sysstat*

If a package is not installed, then install it from the Red Hat Linux cd’s or dvd’s, or if you purchased a Red Hat Suscription download the required package version from Red Hat Network in

If you noticed that some packages are not installed in your linux box use rpm -Uvh package_name command to install the packages.

Configure Name Resolution

Verify that the hosts file contains the fully qualified host name. You should have almost two lines in this file, one line with ip and localhost information and another line with your server ip and hostname information.

Creating Required Operating System Groups and Users

The following operating system groups and user are required if you are installing Oracle Database:

The OSDBA group (dba): You must create this group the first time you install Oracle Database software on the system. It identifies operating system user accounts that have database administrative privileges (the SYSDBA privilege).

The OSOPER group (oper): Create this group if you want a separate group of operating system users to have a limited set of database administrative privileges (the SYSOPER privilege). By default, members of the OSDBA group also have the SYSOPER privilege.

The OSASM group (asmadmin): This feature introduces a new SYSASM privilege that is specifically intended for performing Automatic Storage Management administration tasks. Using the SYSASM privilege instead of the SYSDBA privilege provides a clearer division of responsibility between Automatic Storage Management administration and database administration. Members of the OSASM group can connect as SYSASM using operating system authentication and have full access to Automatic Storage Management.

The Oracle Inventory group (oinstall): You must have a group whose members are given access to write to the Oracle Central Inventory (oraInventory).

The oracle user (oracle): The first time you install Oracle software on the system you must create the oracle user. This user owns all of the software installed during the installation. This user must have the Oracle Inventory group as its primary group. It must also have the OSDBA and OSOPER groups as secondary groups.

In the next images I show you how to create the Oracle user, the oinstall group and the dba group. Additionally I assigned the both groups to oracle user:

Identifying Required Software Directories

Create the Oracle base directory for Oracle software installations. In the next images I show you how to create a base directory and how to assign permissions in this directory to the Oracle user:

Optimal Flexible Architecture (OFA)

All Oracle components on the installation media are compliant with Optimal Flexible Architecture, which means that Oracle Universal Installer places Oracle Database components in directory locations that follow Optimal Flexible Architecture guidelines.

For Oracle 11g Database, the OFA recommends that $ORACLE_HOME path should be:


  • Oracle recommends to use mount points such as /u01, /u02, etc. which complies with the OFA guidelines, but others can be used, for example:

  • /disk1/app/oracle/product/11.1.0/db_1

  • app is a standard directory name:

  • /u01/app/oracle/product/11.1.0/db_1

  • oracle is the name of who owns the Oracle software, so if the user is “paola”, then the path of the $ORACLE_HOME directory should be:

  • /u01/app/paola/product/11.1.0/db_1

  • product is a standard directory name:

  • /u01/app/oracle/product/11.1.0/db_1

  • 11.1.0 is the version of the product that you are installing in your linux box:

  • /u01/app/oracle/product/11.1.0/db_1

  • db_1 is the type of installation that you are doing, for example: db for database, client for client, and so on:

  • /u01/app/oracle/product/11.1.0/db_1

Configure Oracle Installation Owner Shell Limits

To improve the performance of the software, you must increase the following shell limits for the oracle user:

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

Configuring Kernel Parameters

Verify that the kernel parameters are set to values greater than or equal to the minimum value needed by Oracle Software. If the current value for any parameter is higher than the value required, then do not change the value of that parameter. If you want that changes in kernel parameters persist when you restart your server, you should update the sysctl.conf file located in /etc folder:

fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

Enter the following command to change the current values of the kernel parameters or restart your server in order the changes take effect in the operating system:

# /sbin/sysctl -p

Installing Oracle Software

For the installation, you need either the CD’s, DVD’s or a downloaded version of the Oracle Software that you could find in:
After you downloaded the software, compute a cyclic redundancy check (CRC) checksum for the downloaded files and compare the checksum numbers against the numbers posted on OTN’s website. For example:


In order to proceed with the installation you should authenticate in the server like oracle user, and then from the directory where the software was downloaded, open a terminal window and enter the following command:

$ /directory_path/runInstaller

Choose between the Basic and Advanced Installation Method. The first one is the default installation method and permits that you quickly install Oracle Database because this requires minimal user input. The second option lets you complete advanced tasks, for example: select a database character set or different product languages, create a database on a different file system from the software, configure Automatic Storage Management for database storage, specify different passwords for administrative schemas, configure automated backups or Oracle Enterprise Manager notification, etc. Click Next.

This screen is displayed only during the first installation of Oracle products on a system. Specify the full path of the Oracle Inventory directory. Click Next.

Choose between the different installation types: Oracle Standard Edition, Oracle Enterprise Edition, or Custom. The first one, installs an integrated set of management tools, full distribution, replication, Web features, and facilities for building business-critical applications. The second one, installs licensable Oracle Database options and database configuration and management tools in addition to all of the products that are installed during a Standard Edition installation. It also installs products most commonly used for data warehousing and transaction processing. The other one, enables you to select the individual components that you want to install from the list of all available components. Click Next.

Enter the Oracle home name and directory path in which you want to install Oracle components. The directory path should not contain spaces. Click Next.

The screen checks that the system meets the minimum requirements for the installation. Correct any errors that Oracle Universal Installer may have found, and then click Next.

Select one of the following options: Create a database, Configure Automatic Storage Management or Install database software only. The first one lets you create a database. The second one lets you create an Automatic Storage Management instance only. The other one lets you install the database software only. This option does not create a database or configure Automatic Storage Management. Click Next.

In this screen you should specify the operating system groups that you created previously to the installation. Click Next.

Review the information displayed in the screen. Click Next.

This screen displays status information while the product is being installed.

Before to accept this screen, you should read the instructions and run the scripts and like root user in a separate terminal.

Review the information displayed in the screen. Click Exit.

Configure the environment variables in order to use the Oracle Software, in order to do that you should edit the .bash_profile file located in the $HOME of oracle user.

Author: Paola Pullas
Do you need to buy support?: Contact me at

If you think that this tutorial helped you. Make a donation to this initiative. We appreciate your support.