ORA-01031: insufficient privileges upon instance startup

Posted by David Yahalom under Windows, Oracle

A friend of mine who is a junior DBA approached me today with a strange problem that was bugging him for hours. He installed a new Windows Oracle 9i server and wanted to create the databases manually (something I always recommend over DBCA, as you get finger grain control over your database creation process and you feel more “in control” of whats going on inside your DB).

He created an init.ora file for his Instance and since he wanted to use OS authentication for Oracle, he left the parameter remote_login_passwordfile to its default value (”none”).

He then proceeded to creating his instance using the following command:

oradim -new -sid InstanceSID -startmode m -pfile C:location_to_pfileinit.ora -intpwd password

After which, the instance was created. He proceeded to creating the database itself. Since he was all keen about doing this manually he opened up a Windows CMD and did the following steps:

SET ORACLE_SID=InstanceSID
C:> sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Dec 13 10:18:27 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn sys/password as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=C:location_to_fileinit.ora;
ORA-01031: insufficient privileges
SQL>

As you see, he got an ORA-01031. He didn’t understand what is causing this. His O/S user was a member of the ORA_DBA group. He tried logging off windows and then logging backup in. Nothing.

The solution is actually quite simple. By default, if you install Oracle and don’t use the network configuration assistance you get a ORA_HOME without an SQLNET.ORA file. Without it, Oracle will not be able to use O/S authentication.

To solve this problem, simply create a file named sqlnet.ora in your %ORA_HOME%\network\network\admin directory and add to it the following line:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Save the file.

After this, try starting the instance using the same procedure described above and your O/S based logon should work just fine!

Misc tip: search for all unread mail in Gmail

Posted by David Yahalom under General IT

For ages I’ve looked for a way to quick search for all unread mail in Gmail.

I need this because most of the time I let a few unimportant (read: advertisements) emails slip by me without reading them or marking them as read. After time, this list grows and I find it annoying to always receive notifications as if I have unread mail in my inbox (I have unread mail, it is just unimportant mail I forgot to mark as read).

Well, there doesn’t seem to be a way to search for only unread mail using Gmail’s interface but there is quick way around this.

Just type:

is:unread

Into the search box!

Materialized view will become invalid after a refresh - documented Oracle bug in older versions+ workaround.

Posted by David Yahalom under Oracle

Today I stumbled across a very annoying Oracle bug regarding Materialized Views becoming invalid after a refresh. I’m talking about Oracle Bug 2639679 (QUERY_REWRITE flag changes for MVIEW with DATE RANGE in WHERE) which affects Oracle 8i and even some versions of 9i (can’t be sure which versions exactly as Metalink says this is resolved in 9i but clearly this is not the case).

When you create a materialized view with DISABLE QUERY REWRITE option, when you refresh the materialized view (using DBMS_SNAPSHOT.REFRESH, DBMS_REFRESH.REFRESH or any other method), the QUERY REWRITE flag will automatically be turned back on. This is a problem if your materialized view has a where clause in it as QUERY REWRITE + a where clause is a no-no.

This bug will cause your materialize view to become invalid every time you refresh it. Very annoying.

The solution I found was to add a disable query rewrite command before and after the refresh of the materialized view.

EXECUTE IMMEDIATE('alter materialized view SCHEMA.MV_NAME disable query rewrite');
DBMS_SNAPSHOT.REFRESH( 'MV_NAME','C');
EXECUTE IMMEDIATE('alter materialized view SCHEMA.MV_NAME disable query rewrite');

You can put this in a database job to have the materialized view refresh automatically.

Hope this helps.

Insufficient privileges error while installing Oracle DBCA under Linux

Posted by David Yahalom under Oracle

When installing Oracle 9 on Linux, you may receive the following error during installation:

“you do not have sufficient privileges to write to the specified path Database Configuration Assistant 9.2.0.1.0…”

The problem usually happens when you forget to set the $ORACLE_BASE (not to be confused with $ORACLE_HOME) environment variable prior to initiating the OUI installation.

You want to place such variables in the .bash_profile file located under the oracle user home directory (usually /home/oracle).

If you still get this error even after you have set $ORACLE_BASE (you did remember to you a “-” when SUing to the oracle user, right? su - oracle) then make sure you have proper permissions set.

ORA-600 [16201] when dropping a procedure

Posted by David Yahalom under Oracle

In certain older versions of Oracle database (such as 8.1.7.4 like this case) you may receive an ORA-600 error when trying to drop or recompile a database PL/SQL package or procedure. Usually when the source is wrapped.

SQL> drop procedure schema.proc_name;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [16201], [], [], [], [], [], [], []

SQL> create or replace procedure schema.proc_name
as
begin
end;
/

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [16201], [], [], [], [], [], [], []

This is a documented Oracle bug (No. 2422726). It affects Oracle versions 8.1.7.4, 9.0.1.4, 9.2.0.1 and fixed in Oracle 9.2.0.2 and 10g. However, even if you are running an older version of Oracle there’s a possible workaround available to solve this issue. Note, this is an ugly hack, but it works, and sometimes your only solution for this annoying problem.

The solution is as follows:

1) Connect as SYS to the problematic db.

2) Run the following query to identify the object# of the INVALID object you can’t drop.

SQL> select obj#,owner#,type# from sys.obj$ where name = 'PROC_NAME';

OBJ#     OWNER#      TYPE#
---------- ---------- ----------
1396504          5          7

3) Now try and select the procedure from v$procedure using the OBJ# from the above query.

SQL> select * from procedure$ where obj# in (1396504);

no rows selected

4) You can’t. That’s because of the above mentioned bug.

5) The only way to fix this is to insert a fake row into the procedure$ view to fool Oracle to allow you to successfully drop the procedure.

SQL> insert into procedure$ values (1396504, '-----------------------',NULL,2);

1 row created.

SQL> commit;

Commit complete.

6) Now you can successfully drop the INVALID procedure.

SQL> drop procedure schema.proc_name;

Procedure dropped.

As I said, it’s a hack, so you are better of upgrading your Oracle installation to a version not effected by this bug. But as we all know, many times this is simply not an option. And for those occasions the solution I’ve written here seems to be the only one.

Good luck!

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