Wait Events and Potential Causes in Oracle

Wait Event	General Area	Possible Causes	Look For / Examine
buffer busy waits	Buffer cache, DBWR	Dependent on type of buffer: index block in a primary key that is based on an ascending sequence rollback segment header	Examine V$SESSION_WAIT while the problem is occurring to determine the type of block contended for.
free buffer waits	Buffer cache, DBWR, I/O	Slow DBWR (possibly due to I/O?) Cache too small	Examine write time using OS statistics. Check buffer cache statistics for evidence of too small cache.
db file scattered read	I/O, SQL statement tuning	Poorly tuned SQL Slow I/O system	Investigate V$SQLAREA to see whether there are SQL statements performing many disk reads. Cross-check I/O system and V$FILESTAT for poor read time.
db file sequential read	I/O, SQL statement tuning	Poorly tuned SQL Slow I/O system	Investigate V$SQLAREA to see whether there are SQL statements performing many disk reads. Cross-check I/O system and V$FILESTAT for poor read time.
enqueue	Locks	Depends on type of enqueue	Look at V$ENQUEUE_STAT.
latch free	Latch contention	Depends on latch	Check V$LATCH.
log buffer space	Log buffer, I/O	Log buffer small Slow I/O system	Check the statistic redo buffer allocation retries in V$SYSSTAT. Check configuring log buffer section Check the disks that house the online redo logs for resource contention.
log file sync	I/O, over- committing	Slow disks that store the online logs Un-batched commits	Check the disks that house the online redo logs for resource contention. Check the number of transactions (commits + rollbacks) per second, from V$SYSSTAT.

How to avoid ORA-21561 : OID generation failed

Today we had to create a new test database in our virtual environment ...

our sys admin has duplicated an existing one but when i started to create a database with dbca, ive got an ORA-21561 : OID generation failed

I edited the /etc/hosts file and verify that the local node is correctly identified with a mapping for both the short and fully qualified name of the host.  

How to configure Firewall rules in Red Hat for Oracle listener with iptables

One of our Oracle databases is running on RedHat Enterprise Linux 5.3. Listener is running on 1521

But I can connect to the database only from server itself. From no other machines I can connect to my database. IPTables is blocking them on 1521.

So, I’m adding a new rule to IPTables so any traffic to 1521 is allowed.

I added the rule, saved the rule and restarted the IPTables service. Saving and restarting is not required to have the rule to be active but better not to let the rule forgotten.

iptables -I INPUT -p tcp –dport 1521 -j ACCEPT

Now, it works. I can connect from other machines 

How does the character set affect Import/Export in Oracle

Import and Export are client products, in the same way as SQL*Plus or Oracle Forms, and will therefore translate characters from the database character set to that defined by NLS_LANG. The character set used for the export will be stored in the export file and when the file is imported, the import will check the character set that was used. If it is different than that defined by NLS_LANG at the import site, the characters will be translated to the import character set and then, if necessary to the database character set.

Oracle recommends setting the character set part of the NLS_LANG environment variable to the same character set as the character set of the database you are using.

This query can help you to check the NLS_CHARACTERSET on the SOURCE database

SQL> select * from nls_database_parameters ;

Before running Oracle imp to import data set the NLS_LANG set to AMERICAN_AMERICA.WE8MSWIN1252 (=source NLS_CHARACTERSET)

On Unix this would be: export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252;
On Windows this would be: C:\>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252;

Quickest way to install Oracle 11gR2 on Linux / Unix

• Create Oracle groups oinstall and dba
• Create Oracle user oracle in groups oinstall and dba
• Download the installations files from Oracle site (in /u03 for example)
• unzip the installations files in the same folder
• navigate to the database folder ( cd /u03/database )
• define your DISPLAY variable ( export DISPLAY=your_ip_adress:0) you can use an free X Server like MobaXterm Personal Edition
• launch the runInstaller program ( ./runInstaller )
• In the prerequite checks page, click on Fix & Check again to create a fixup script

 

Allow oracle operating system user to schedule jobs on Linux / Unix

As the root user, add oracle to the /etc/cron.allow file with the echo command:

echo oracle >> /etc/cron.allow

Once the oracle entry is added to the /etc/cron.allow file, the os user oracle can use the crontab utility
to schedule a job

The root user can always schedule jobs with the crontab utility. Other users must be listed in
the /etc/cron.allow file. If the /etc/cron.allow file does not exist, then the operating system
user must not appear in the /etc/cron.deny file. If neither the /etc/cron.allow nor the /etc/
cron.deny file exists, then only the root user can access the crontab utility.

Manually removing orphaned Oracle memory structures in Linux

You can view the structures to be removed with the ipcs -sm command:
$ ipcs -sm

------ Shared Memory Segments --------key        shmid      owner      perms      bytes         nattch     status
0xb3e36378                                                        32768      oracle       640        421527552  16
0x34525e84                                                        65537      oracle       640        421527552  11

------ Semaphore Arrays --------key        semid      owner      perms      nsems
0x288e2800                                             360448     oracle      640        126
0x288e2801                                             393217     oracle      640        126
0x288e2802                                             425986     oracle      640        126

If you’re working on a server that has multiple Oracle instances running, ensure that you remove
the correct memory structure. If you remove the wrong structure, you will inadvertently crash another database.

You can use the sysresv Oracle utility to verify which memory structures belong
to the orphaned instance
By running the Oracle sysresv utility (located in the ORACLE_HOME/bin
directory). This command reports on memory structures that correspond to your current instance
setting of ORACLE_SID. Run this command as the owner of the Oracle binaries (usually oracle):

$ sysresv

IPC Resources for ORACLE_SID "REV10" :
Shared Memory:
ID                        KEY
2424843         0x00000000
2457612         0x00000000
2490381         0xa4746610
Semaphores:
ID                         KEY
2457602         0x62f172a8
Oracle Instance alive for sid "REV10"

You can remove memory objects either by the key or by ID. This next example uses the -m
option to remove a shared memory segment by its ID:

$ ipcrm -m 2686990

This next example uses the -s option to remove semaphore arrays using IDs:

$ ipcrm -s 2719748

You can verify that the memory structures have been removed by running sysresv again

Different ways to modify linux kernel parameters for Oracle installation

There are several valid techniques for changing kernel parameters beforeperforming a database installation :

• Run sysctl

Use the sysctl command with the -w option to dynamically modify kernel parameters. The
following command changes the kernel semaphore settings in the /proc/sys/kernel/sem
virtual file:

sysctl -w kernel.sem="250 32000 100 128"

To make changes persist across system reboots, use your favorite editor (like vi) to add the
parameters to the /etc/sysctl.conf file.

• Edit sysctl.conf

You can also directly modify the /etc/sysctl.conf file and then use the sysctl -p command
to make desired kernel parameter changes. This example uses vi to first edit the /etc/
sysctl.conf file:

vi /etc/sysctl.conf

Add changes and then exit...
After you modify the /etc/sysctl.conf file, you can use the sysctl -p command to make the
entries in the /etc/sysctl.conf file instantiated as the current values used by the Linux kernel:

sysctl -p

The previous command loads into memory the values found in the /etc/sysctl.conf file.
You can verify that the values were changed by using cat to view the corresponding virtual file.

• Add entries with echo

You can use the echo command to write the desired output to the specified virtual file. This
example writes the values 250 32000 100 128 to the virtual /proc/sys/kernel/sem file using the
echo command:

echo 250 32000 100 128 > /proc/sys/kernel/sem

The previous command immediately changes the kernel settings for the sem (semaphores)
parameter. If you want the change to persist across system reboots, then you also need to add
an entry to the /etc/sysctl.conf file. This file is read when the system boots to determine the
settings for kernel parameters. You can edit the /etc/sysctl.conf file directly (with an editor
such as vi) and add the following line:

kernel.sem = 250 32000 100 128

Alternatively, you can use the echo command to add the desired parameters to the end of
the /etc/sysctl.conf file, as shown here:

echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf

Notice that the previous command uses >> to concatenate the desired entry to the bottom
of the /etc/sysctl.conf file. You would not want to use just a single right arrow >, because that
would overwrite the contents of /etc/sysctl.conf.

• Add entries with cat

The technique shown here is handy for adding several entries to the /etc/sysctl.conf file at
the same time. First use the cat command to add entries to the /etc/sysctl.conf file. This
example shows how to use cat to write typical kernel parameter settings for an Oracle database:

cat >> /etc/sysctl.conf <<EOF
kernel.shmall = 2097152
kernel.shmmax = 536870912
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 = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
EOF

The previous command uses cat to write to the /etc/sysctl.conf file all the values encap-sulated between the two EOF markers. This allows you to add several parameters simultaneously
to the /etc/sysctl.conf file. When using cat and >> to write parameters to the /etc/sysctl.conf
file, there is no automatic checking to determine whether the parameters already exist in the file.
Using cat and >> will simply write to the bottom of the file.
After the desired changes are made, use the sysctl -p command to make the entries in the
/etc/sysctl.conf file the current values used by the Linux kernel, as shown here:
sysctl -p

How to get my Oracle session informations

In Oracle/PLSQL, the userenv function can be used to retrieve information about the current Oracle session.

The syntax for the userenv function is:

userenv( parameter )

parameter is the value to return from the current Oracle session.

For Example:

userenv('ENTRYID')	would return FALSE
userenv('LANGUAGE')	would return 'AMERICAN_AMERICA.WE8DEC'

 The possible values for parameter are:

Parameter	Explanation
CLIENT_INFO	Returns user session information stored using the DBMS_APPLICATION_INFO package
ENTRYID	Available auditing entry identifier
INSTANCE	The identifier number of the current instance
ISDBA	Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.
LANG	The ISO abbreviation for the language
LANGUAGE	The language, territory, and character of the session. In the following format:      language_territory.characterset
SESSIONID	The identifier of the auditing session
TERMINAL	The OS identifier of the current session

Simple way to gather system and kernel informations from your Unix / Linux system

The Proc File System can be used to gather useful information about the system and the running kernel.
Some of the important files are listed below

• /proc/cpuinfo - information about the CPU (model, family, cache size etc.)

• /proc/meminfo - information about the physical RAM, Swap space etc.

• /proc/mounts - list of mounted file systems

• /proc/devices - list of available devices

• /proc/filesystems - supported file systems

• /proc/modules - list of loaded modules

• /proc/version - Kernel version

• /proc/cmdline - parameters passed to the kernel at the time of starting

script to identify all objects belonging to a tablespace in Oracle

select owner
,      segment_name
,      segment_type
from   dba_segments
where  lower(tablespace_name) like lower('%&tablespace%')
order by owner, segment_name

Oracle listener commands

lsnrctl help command will display all available listener commands. In Oracle 11g following are the available listener commands.

• start - Start the Oracle listener
• stop - Stop the Oracle listener
• status - Display the current status of the Oracle listener
• services - Retrieve the listener services information
• version - Display the oracle listener version information
• reload - This will reload the oracle listener SID and parameter files. This is equivalent to lsnrctl stop and lsnrctl start.
• save_config – This will save the current settings to the listener.ora file and also take a backup of the listener.ora file before overwriting it. If there are no changes, it will display the message “No changes to save for LISTENER”
• trace - Enable the tracing at the listener level. The available options are ‘trace OFF’, ‘trace USER’, ‘trace ADMIN’ or ‘trace SUPPORT’
• spawn - Spawns a new with the program with the spawn_alias mentioned in the listener.ora file
• change_password – Set the new password to the oracle listener (or) change the existing listener password.
• show - Display log files and other relevant listener information.

Usefull tips and command for the vi editor

This are the most used commands in vi edtor

Shell script to calculate Tablespaces sizes in Oracle

This shell take an ORACLE_SID as parameter and calculate in MB the sizes of tablespaces

User / Group management in Red Hat Linux Enterprise

One of the most common administrative tasks is working with user and group accounts. The commands i use most often are

• useradd — Creates user login accounts 

The useradd command creates new user accounts and, when invoked with the
-D option, modifies the default values applied to new accounts. As a result, it can
be invoked in two ways. The syntax of the first form is

useradd [-c comment] [-d home_dir] [-e expire_date]
[-f inactive_time] [-g initial_group] [-G group[,...]] [-m [-k skeleton_dir] | -M]
[-p passwd] [-s shell] [-u uid [-o]] [-n] [-r] username

• userdel — Deletes user login accounts
• usermod — Modifies user login accounts
• passwd — Sets or changes account passwords

The  passwd command, generally regarded as  “the password changing utility,”
actually has more capabilities than merely changing passwords. In general, it
updates all of a user’s authentication tokens, of which the login password is only
one. Its syntax is:

passwd [-dkluf] [-S] username

-d removes the password for username, disabling the account. -k causes passwd
to update only expired authentication tokens (passwords, in this case). -l or -u lock
or unlock, respectively, username’s password by placing and removing a ! in front
of username’s password in /etc/shadow. The -S option, finally, displays a short
status message about  username, indicating whether the account is locked or
unlocked, the kind of encryption used, and so forth.

• chsh — Sets or changes a user’s default shell

chsh [-s shell ] [-l] [username]

-s  shell sets  username’s login shell to  shell. Unless configured otherwise,
shell can be the full pathname of any executable file on the system. One common
way to take advantage of this feature is to disable an account by setting shell to
/bin/false or another command that does not give the user a login prompt. Using
the -l option displays the shells listed in /etc/shells

• chage — Modifies password expiration information

How to avoid ORA-38726 error

You should always verifiy that flashback logging is enabled before you create a restore point.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
NO

To enable flashback logging, database must be in archive log mode, else you will get this error

ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.

So you will need to enable it

shutdown immediate;

startup mount;

alter database archivelog;

alter database flashback on;

alter database open;