Archiving not possible: No primary destinations errors after fixing a space issue on an archive destination

Posted by David Yahalom under Oracle

If space ran out in an archive destination, after you fix the problem, you may still recieve the following error in your alert_log:

ARC1: Archiving not possible: No primary destinations

A quick way to solve this is to modify your archive destination with the reopen parameter. This parameter specifies the minimum number of seconds before redo transport services should try to reopen a failed destination.

SQL> show parameter log_archive_dest_1

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
log_archive_dest_1                   string  location=/db/orcl/archive

SQL> alter system set log_archive_dest_1='location=/db/orcl/archive reopen=5' scope=both;

System altered.

Following that modification you should start seeing successful archive messages in your alert_log.

ARC1: Beginning to archive log# 1 seq# 115127
...
ARC0: Completed archiving log# 1 seq# 115127
...

UltraSparc Vs. x86 servers. Which one runs Oracle faster?

Posted by David Yahalom under Hardware, Solaris, Linux, Oracle

I was doing research for a client that is trying to replace old Sun UltraSparc Solaris servers with new, cheaper (and hopefully better performing), Linux x86 machines. I want to see if I can find any benchmarks that will help me compare the performance of SPARC servers to x86 machines when running Oracle Database.

First I started by looking in TPC’s OLTP benchmark results, which are considered somewhat of an industry standard. I found several interesting things.

Let’s look at two low end HP machines, the ProLiant ML350G5 and the ML370G5. These are both low-end Dual-CPU socket x86 servers from HP starting at a price lower then $2,500 for both servers.

low-end HP ProLiant ML350G5 (1 X Intel X5355 Quad-Core) - 100,926 TPC-C/Sec
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=107061101

low-end ProLiant ML370 G5 (2 X Intel X5460 Quad-Core) - 273,666 TPC-C/Sec
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=107111201

While I was unable to fund recent benchmarks of Sparc machines in TPC, I was able to find benchmark that while conducted several years ago might still be useful.

OLDish (2003) Fujitsu PRIMEPOWER 2500 (64 X SPARC64 - 1.3 GHz ) - 595,702 TPC/Sec
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=103103101

This is a high-end server with 64 SPARC processors. In its time it was quite the beast and can still be found in many data centers worldwide crunching very important numbers.

As you can see it scored twice the number of points compared to the ProLiant ML370G5. Impressive. Sure. Just keep in mind that we are talking about a really expensive high-end 64CPU machine against a very low-end 2 X QuadCore (8 cores total) Intel-based commodity server. So eight times the CPUs for roughly twice the performance? 83.2 total SPARC Ghz vs. 25.28 total x86 Ghz ? Still confused? According to TPC the Fujitsu machine is almost X20 more expensive compared to the x86 HP server. Twice the performance for 20 times the price?

So yes, the Fujitsu PRIMEPOWER is an older server with older CPU models but it is also a much more expensive server and we can still use this data when considering replacing old SUN servers with newer X86 alternatives. These results can help us better understand what type of x86 hardware we need in order to replace our old SPARC servers. This isn’t %100 scientific comparison but it can still give us a ballpark estimate.

We’ll need to do some more digging to find more current results using newer SPARC processors (such as the Sun T1). What I was able to find were some MySQL benchmarks done by tweakers.net for their own web application. While these are very tailored benchmarks done for a specific web site application it does help contribute to the general picture here.

http://tweakers.net/reviews/646/14/server-duel-xeon-woodcrest-vs-punt-opteron-socket-f-pagina-14.html

http://tweakers.net/reviews/649/8/database-test-sun-ultrasparc-t1-vs-punt-amd-opteron-pagina-8.html

You can see that the Xeons fares much better compared to the UltraSparcs.

Another very interesting benchmark I found was a blog entry quoting official SAP benchmarks between x86 and SPARC hardware. The SAP benchmark shows the x86 servers to be faster compared to the Sun T2000 SPARC server with some limitations.

2 X Quad-Core Intel Xeon X5355 2.66 GHz - 9330 SAPS
www.sap.com/solutions/benchmark/pdf/cert4207.pdf

2 X Dual-Core Intel Xeon 5160 3 GHz - 5120 SAPS
www.sap.com/solutions/benchmark/pdf/cert3107.pdf

Sun Fire Model T2000 - 1 processor / 8 cores / 32 threads - 4780 SAPS
www.sap.com/solutions/benchmark/pdf/cert4705.pdf

You should go ahead and read the reservations made by the author of the above post. They are all valid. Just keep in mind that the Sparc system is a $33,000 server compared to a much cheaper HP.

Of course I’m only talking about raw performance here. The x86/Linux hardware has several other advantages such as being more open, more future-proof and arguably easier and cheaper to service and maintain.

This is all very interesting stuff. I’ll continue to do more digging and update this post as necessary.

How to perform a manual uninstallion of an $ORACLE_HOME on a Linux server

Posted by David Yahalom under Oracle

While the safest way to remove an $ORACLE_HOME installation from a server is by using OUI, sometimes it might not be possible (for example, if you are not running X).

In case you ever need to perform a manual uninstall of an $ORACLE_HOME installation from a Linux server, you can do so by following these steps:

1.Stop (or kill) all Oracle processes running on the machine.
2. Remove the oraInventory directory (check /etc/oraIns.loc if unsure about location).
3. Remove (using rm -rf) the $ORACLE_HOME(s) you have on your system.
4. Remove all other traces of Oracle from your server:

/usr/local/bin/oraenv
/usr/local/bin/coraenv
/usr/local/bin/dbhome
/etc/oraInst.loc
/etc/oratab
/var/tmp/.oracle

That’s it. Following this you won’t have any trace of Oracle software on your server.

What to do if you receieve an “ORA-00600: internal error code, arguments: [keltnfy-ldmInit]” when creating a new database

Posted by David Yahalom under Oracle

If you try and create a new Oracle database (either using DBCA or manually) and get the Oracle ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], [] error, this usually signifies that there was a problem resolving your server’s hostname.

Oracle needs to be able to resolve your server’s hostname from within the server on which you are installing Oracle. In case of the above error check your /etc/hosts file and verify that your server hostname is pingable.

How to resolve EXP-00056: ORACLE error 19206 encountered during an export

Posted by David Yahalom under Oracle

Sometimes you might receive the following error during an export of an entire schema in Oracle9i/10g:

EXP-00056: ORACLE error 19206 encountered
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at "SYS.DBMS_XMLGEN", line 83
ORA-06512: at "SYS.DBMS_METADATA", line 353
ORA-06512: at "SYS.DBMS_METADATA", line 418
ORA-06512: at "SYS.DBMS_METADATA", line 457
ORA-06512: at "SYS.DBMS_METADATA", line 1181
ORA-06512: at "SYS.DBMS_METADATA", line 1162
ORA-06512: at line 1
EXP-00056: ORACLE error 19206 encountered
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at "SYS.DBMS_XMLGEN", line 83
ORA-06512: at "SYS.DBMS_METADATA", line 353
ORA-06512: at "SYS.DBMS_METADATA", line 418
ORA-06512: at "SYS.DBMS_METADATA", line 457
ORA-06512: at "SYS.DBMS_METADATA", line 1181
ORA-06512: at "SYS.DBMS_METADATA", line 1162
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully

While an export of a single table might work, exporting an entire user (”schema“) will fail with the above error.

This problem is caused when the metadata tables needed to perform an export are missing or invalid and can sometimes occur if you have dropped or modified the XMLDB schema.

It is quite easily resolved, simply run the following script when connected as sysdba:

$ORACLE_HOME/rdbms/admin/catmeta.sql 

Installing Oracle 9 on RHEL4

Posted by David Yahalom under Linux, Oracle

Installing Oracle 9i on RHEL4 isn’t difficult but requires a certain set of pre-installation actions that aren’t very well documented in the Oracle official installation readme.

This could lead to allot of frustration and hair pulling during installation if you don’t follow the exact set of actions described in this document.

The following errors are known errors which may appear during the Oracle 9i installation on RHEL4. Usually, the cause for these problems is a bad configuration or package dependency at the O/S level. These errors can usually be easily fixed but following the procedure described in this document.

compat-gcc-32 is needed by compat-oracle-rhel4-1.0.5.i386

error invoking target 'install' of makefile
$ORACLE_HOME/sqlplus/lib/ins_sqlplus.mk

error invoking target relink of makefile
$ORACLE_HOME/precomp/lib/ins_precomp.mk

GLIBC_2.0 not defiend in file libc.so.6 with link time reference...

Installing RHEL4/ CentOS 4.1

Continue to installing RHEL4/CentOS 4.1 normally as you would usually do just make sure that you have allocated at least 2GB of swap space, disabled SE Linux (THIS IS CRITICAL) and installed the following packages:
X Window System, GNOME, Editors, Server configuration tools, Administration tools, development tools and system tools.

Create users and groups:

As with any Oracle installation we will need to create two groups and one user before we can continue with the installation.

To do so we will run the following commands as root:

groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle

This creates two Linux groups named oinstall and dba and a user called Oracle that is made a member of these two groups.

Create the directory structure:

Before we extract and install the Oracle software we will need to create the directory structure first. You can obviously use any directory structure you want but the dir structure I’m using here is popular amongst many DBAs.

Run the following commands as root:

mkdir /oracle
mkdir /oracle/9i
chown -R oracle:oinstall /oracle
mkdir /osource
chown -R oracle:oinstall /osource

This creates two directories (/oracle and /osource). The oracle home will be located in /oracle and the raw installation files in /osource.

Now we will edit the bash_profile file for the Oracle user located in /home/oracle/.bash_profile and add the following lines at the end:

#### Oracle 9i parameters ####
ORACLE_BASE=/oracle
ORACLE_HOME=/oracle/9i
ORACLE_SID=[ENTER SID NAME HERE]
PATH=$ORACLE_HOME/bin:$PATH:.
LD_LIBRARY_PATH=$ORACLE_HOME/lib;
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib;
LD_ASSUME_KERNEL=2.4.19;</code>

export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
export LD_LIBRARY_PATH
export CLASSPATH
export LD_ASSUME_KERNEL

Download Oracle 9i for Linux:

Download the Oracle CDs from otn.oracle.com. The version I’m using in this tutorial is 9.2.0.4.

Beside the Oracle CDs you will also need to download an additional compatibility patch from Metalink. Patch number 4198954. This patch contains two RPMs that we need to install:

compat-oracle-rhel4-1.0-5.i386.RPM
compat-libcwait-2.0-2.i386.RPM

But more on this later on. For now, just download the zip file from the note.

Save all files in the /osource directory we created earlier.

Unpacking the files:
After we have downloaded the Oracle CDs we will need to extract the files inside.

To do this run the following commands as the oracle user:

Cd /osource
gunzip ship_9204_linux_disk1.cpio.gz
gunzip ship_9204_linux_disk2.cpio.gz
gunzip ship_9204_linux_disk3.cpio.gz

Followed by:

cpio -idmv < ship_9204_linux_disk1.cpio
cpio -idmv < ship_9204_linux_disk2.cpio
cpio -idmv < ship_9204_linux_disk3.cpio

(CPIO is an archive utility usually used for tape backups. You can read more about it here.)

After successful completion of the above steps you should now have three directories inside /osource named “Disk1″, “Disk2″ and “Disk3″.

Configure host parameters:

Make sure /etc/hosts contains a FQN of this server. If the hostname of the server isn’t pingable from the server itself, you’ll have problems later on.

[IP_ADDR] [FQ machine name] [machine name]

Configure kernel Parameters:

Login as root and add the following lines to /etc/sysctl.conf file:

kernel.shmall = 2097152
kernel.shmmax = 2147483648
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.wmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_max = 262144

Make sure there is a cartridge return after the last line. This is important.

Save the file and run /sbin/sysctl -p to apply the new values to the currently running host.

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

oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384

Pre installation configuration:

As with Metalink note 303859.1 there are additional packages required before we can continue with our Oracle installation.

All the required packages are on the RHEL CDs but they might not have been installed during the initial O/S installation (depending on the selections you
made during package selection).

RHEL Disk 2 / Centos 4.1 Disk 1
cd /media/cdrom/CentOS/RPMS
OR
cd /media/cdrom/RedHat/RPMS

rpm -Uvh xorg-x11-deprecated-libs-6*
rpm -Uvh make-3*
rpm -Uvh compat-libstdc++-33-3*

RHEL Disk 3 / Centos 4.1 Disk 2
cd /media/cdrom/CentOS/RPMS
OR
cd /media/cdrom/RedHat/RPMS

rpm -Uvh xorg-x11-devel*
rpm -Uvh xorg-x11-deprecated-libs-devel-6*
rpm -Uvh gcc-c++-3*
rpm -Uvh gnome-libs-1*
rpm -Uvh freetype-devel*
rpm -Uvh compat-db*
rpm -Uvh gcc-3*
rpm -Uvh fontconfig-devel*

RHEL Disk 4 / Centos 4.1 Disk 3
cd /media/cdrom/CentOS/RPMS
OR
cd /media/cdrom/RedHat/RPMS

rpm -Uvh compat-libgcc-296-2*
rpm -Uvh openmotif21-2*
rpm -Uvh compat-gcc-32-c++-3*
rpm -Uvh compat-gcc-32-3*
rpm -Uvh compat-libstdc++-296-2*
rpm -Uvh libaio-devel-0*
rpm -Uvh libaio-0*
rpm -Uvh gnome-libs-devel-1*

It is okay if during installations of some of these packages you’ll receive a message indicating some of these packages are already installed.

Now we’ll install the Metalink patch (4198954) we downloaded earlier. Extract the two packages located in the patch and run the following commands:

rpm -Uvh compat-libcwait-2*
rpm -Uvh compat-oracle-rhel4-1*

Be advised that the second rpm is actually only a placeholder for checking if certain RPMs are installed on your system. If during the installation of either of the two RPMs above you get an error indicating a dependency error (such as “compat-gcc-32 is needed by compat-oracle-rhel4-1.0.5.i386″) DO NOT CONTINUE! Go back to the beginning of this document and check if there was anything you may have accidentally missed. The installation will not work otherwise.

Installation:

Now you are ready to continue with the graphical installation of Oracle 9.

How to set a single table read-only in Oracle

Posted by David Yahalom under Oracle

It is easy to set an entire TABLESPACE read-only in Oracle by using the following command:<

ALTER TABLESPACE tbs_name READ ONLY;

But what about setting a single table in read only mode?

Usually the answer was “you can’t”. That is, however, not completely true.

While there isn’t any dedicated syntax in Oracle for setting a table read only, there is a quick and easy workaround. Simply create a check constraint on the table while specifying disable validate.

ALTER TABLE table_name ADD CONSTRAINT table_name_read_only check(1=1) disable validate;

Trying to insert, delete or update that table would yield the an ORA-25128: No insert/update/delete on table with constraint disabled and validated error and prevent the DML operations.

Great Oracle RLS article

Posted by David Yahalom under Security, Oracle

Yesterday I stumbled across a great article about Oracle Row Level Security (RLS) with a great and detailed beginners tutorial.

RLS is a great way to implement application security where many users must connect to the same data but only see different parts of the data based on their role or position in the organization.

In many cases RLS is easier to implement and maintain compared to using views, tirggers or using different schema for data access as business rules and security implementations are done through one PL/SQL procedure per table instead of being spread throughout the applications code.

Another great benefit of RLS is tighter security than what views or triggers provide because RLS can prevent a user from circumventing the security policy by accessing the database directly and query the tables underlying the views.

Oracle Row Level Security: Part 1

Leverage your Oracle 10g skills to learn DB2 9.1 for Linux, UNIX and Windows

Posted by David Yahalom under DB2 LUW, Oracle

Leverage your Oracle10g skills to learn DB2 9.1 for Linux, UNIX and Windows.

This article makes direct comparison between the Oracle architecture (instance, databases, physical files, network, configuration files, etc) and the DB2 architecture thus allowing Oracle DBAs to understand the similarities and differences between Oracle and DB2.

Because of its comparative nature (showing you how the same things work in DB2 compared to Oracle) I think its a great first read if you are an Oracle DBA new to DB2.

I do have some comments I would like to add:

1) IBM says:

“DB2 does contain a binary file known as the system database directory that contains entries of all the databases you can connect from your DB2 machine.”

This compares best to Oracle tnsnames.ora file. I don’t understand why IBM didn’t draw this comparison.

2) IBM says:

“Every Oracle database contains a table space named SYSTEM, which Oracle creates automatically when the database is created. Other table spaces for user, temporary and index data need to be created after the database has been created, and a user needs to be assigned to these table spaces before they can be used.”

While I get IBMs point (that you have to create tablespaces manually after the create database command) This is not technically true. Frist, when you issue a create database command in Oracle you explicitly specify the creation of a temp tablesapce. Second, after the database creation all other tablespaces you have created can be used without being “assigned to users” simply by using them in the storage clause of the object creation statement.

3) IBM Says:

“As indicated earlier, Oracle’s data buffer concept is equivalent to DB2’s bufferpool; however, DB2 allows for multiple bufferpools to exist. There is no predefined number of bufferpools that you can create, and they can have any name.”

Again, not entirely true. While less flexible, Oracle 9i/10g allows for multiple db buffers with different block size. The limitation is one buffer per one block size while in DB2 it is one buffer per tablespace.

Using DBMS_FLASHBACK in Oracle to set your entire session back in time

Posted by David Yahalom under Oracle

Last week I’ve talked to you about using Flashback query, Flashback table and FlashBack database in Oracle 10g to help you improve productivity and perhaps save you time in doing complete or partial database recovery.

Today I want to talk to you about a less known feature of Flashback, the DBMS_FLASHBACK package available since Oracle 9iR1.

The DBMS_FLASHBACK package provides almost the same functionality as FlashBack query but with one big difference. Instead of being able to “go back in time” one query at a time, the DBMS_FLASHBACK package acts as a time machine, or a time tunnel, allowing you to “turn back the clock” and carry out normal SQL queries or PL/SQL code, without change, but as if you were running them from a time in the past.

Flashback query in Oracle 9iR2 (using the as of clause in your SQL statement) is actually based and internally using the capabilities of DBMS_FLASHBACK package.

To use DBMS_FLASHBACK you simply call DBMS_FLASHBACK.ENABLE_AT_TIME (and specify a specific timestamp in the database life cycle) or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (and specify an SCN number in the database life cycle) to “set back the clock” of your current session as a whole.

Following that you can run you queries and PL/SQL code normally without any modifications but instead of getting the results you normally would (which represent to current state of the data in your database) the results you receive represent the state of the database as it was at the timestamp you provided with the DBMS_FLASH.ENABLE_AT_TIME command.

To return to the “present” simply cancel the “time tunnel” using the DFBMS_FLASHBACK.DISABLE command.

Let’s write an example that will show us just how easy it is to use the DBMS_FLASHBACK package.

SQL> conn admin/*********@daviddb
Connected.
SQL> create table employees (ename char(20), sal number(6));

Table created.

SQL> insert into employees values ('Linus Torvalds', 5000);

1 row created.

SQL> insert into employees values ('Tom Kyte', 4000);

1 row created.

SQL> insert into employees values ('Steve Ballmer', 9000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from employees;

ENAME                       SAL
-------------------- ----------
Linus Torvalds             5000
Tom Kyte                   4000
Steve Ballmer              9000
This is our base table of employees. At this time the table holds "good" data.

SQL> var sysscn number
SQL> EXEC :sysscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER

PL/SQL procedure successfully completed.

I’m creating a sysscn variable that I will use to store the current SCN of the system. In this example, using a system SCN number as the reference to which we will later Flashback to is easier to demonstrate than using a timestamp.

SQL> update employees set sal = 9999;

3 rows updated.

SQL> commit;

Commit complete.

SQL> select * From employees;

ENAME                       SAL
-------------------- ----------
Linus Torvalds             9999
Tom Kyte                   9999
Steve Ballmer              9999

An employee from the HR department has accidentally updated all employee salaries in the company to 9999. When he notices this mistake he notifies the DBA immediately that something is wrong with employee salaries. Normally, this would require us to recover that table from a backup. We can avoid this by using the DBMS_FLASHBACK package.

SQL> EXEC DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:sysscn);

PL/SQL procedure successfully completed.

SQL> select * from employees;

ENAME                       SAL
-------------------- ----------
Linus Torvalds             5000
Tom Kyte                   4000
Steve Ballmer              9000

SQL> exec dbms_flashback.disable

PL/SQL procedure successfully completed.

Remember that while you are in a “time tunnel” you cannot use DML or DDL statements and that you cannot open a “time tunnel” from within a “time tunnel”. That is, you must use

Also keep in mind that you must have the execute privilege on the DBMS_FLASHBACK package in order to use this wonderful feature.

Using DBMS_FLASHBACK will sometimes make your life easier than using a regular Flashback query. Because it sets your entire session “back in time” you can run multiple database-wide queries or execute PL/SQL code freely from your session without restriction or the need to re-write existing code with the as of clause.