Posts Tagged ‘Oracle 9i’

Solving ORA-01031: insufficient privileges while connecting as sqlplus / as sysdba.

Many times we can see an issue like this:
This is a very common and frequent error that can occur after the new oracle software install or due to some permissions changes at OS level. I will discuss the approach to solve ORA-1031 error on both Windows and Linux environment but first a little theory.
An administrative account is a user that is granted SYSOPER or SYSDBA privileges. Oracle DBAs and operators typically use administrative accounts to manage the database and database instance. SYSDBA and SYSOPER allow access to a database instance even if it is not running. Control of these privileges is managed outside of the database via password files and special operating system groups (dba on Unix/Linux and ORA_DBA on Windows). External password files are created with the orapwd utility.
If an administrative users belongs to the ‘dba’ group on Linux or the ‘ORA_DBA’ group on Windows, he/she can connect like this:

sqlplus / as sysdba

No password is required. This is equivalent to the unsupported “connect internal” method.
A password is required for “non-secure” administrative access. These passwords are stored in password files. Remote connections via Net8 are classified as non-secure. Look at this example:

sqlplus sys/password as sysdba

The Oracle password file ($ORACLE_HOME/dbs/orapw$ORACLE_SID or $ORACLE_HOME/database/orapw$ORACLE_SID) stores passwords for users with administrative privileges. One needs to create a password files before remote administrators will be allowed to connect.
Follow this procedure to create a new password file:
– Log in as the Oracle software owner
– Run command: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd
– Shutdown the database (SHUTDOWN IMMEDIATE)
– Edit the INIT.ORA file and ensure REMOTE_LOGIN_PASSWORDFILE=exclusive is set
– Startup the database (STARTUP)
Keep in mind that the orapwd utility presents a security risk in that it receives a password from the command line. This password is visible in the process table (at least as long as orapwd is running) and in the shell’s history file of many systems.
Last but not least, avoid at all cost the OPS$ accounts and setting the REMOTE_OS_AUTHENT initialization parameter (deprecated in Oracle 11g) which could be a security risk in a client/server environment.

Now, let’s go back to the ORA-01031 error – how to solve it in Windows environment?
First of all, be sure that your OS user belongs to the ORA_DBA group.
Then, set the SQLNET.AUTHENTICATION_SERVICES parameter to NTS in the sqlnet.ora file (located in the $ORACLE_HOME/network/admin):


which allows Windows users to be authenticated using Windows NT native security.
With your user account added to the ORA_DBA group and the SQLNET.AUTHENTICATION_SERVICES parameter being set to NTS, you should have sufficient privileges and be able to connect as SYSDBA without a password.

How to solve the ORA-01031 error in UNIX/Linux environment?
1. Check that $ORACLE_SID and $ORACLE_HOME are set correctly as:


Find the values returned by above commands and match these values under /etc/oratab file, these have to be listed there.
The values above are matching with /etc/oratab entries.
If the $ORACLE_SID and $ORACLE_HOME are not set properly then set it as:

export ORACLE_SID=orcl11gr2
export ORACLE_HOME=/u01/app/oracle/product/1120

and try to connect as “/ as sysdba” – it should work now.
If these are correct but still the error is coming then move to step 2.
2. Ensure you are invoking sqlplus from correct $ORACLE_HOME by checking the $PATH environment variable:

echo $PATH

If this is correct but still the error is coming then move to step 3.
3. Set the SQLNET.AUTHENTICATION_SERVICES parameter to ALL in the sqlnet.ora file (located in the $ORACLE_HOME/network/admin):


If this is correct but still the error is coming then move to step 4.
4. Ensure $TWO_TASK is not set.

echo $TWO_TASK

If it return any lines, then unset the environment variable as:

unset TWO_TASK

If this is correct but still the error is coming then move to step 5.
5. Check the permissions on the Oracle executable file.
It should show the permissions as on the above screen – if its not the same then issue the following command to set the correct permissions:

chmod 6751 oracle

If these are correct but still the error is coming then move to step 6.
6. Make sure that dba group at OS level only exists once in /etc/group file and that the users belonging to the dba group are properly comma separated. Check that the oracle user uid and gid are the same in /etc/group and /etc/passwd.
If these are correct but still the error is coming then move to step 7.
7. Check for the dba group at OS level. We need to make sure that OS user issuing “connect / as sysdba” belongs to dba group at OS level.
There is one file we need to check for this: $ORACLE_HOME/rdbms/lib/config.s or $ORACLE_HOME/rdbms/lib/config.c (file name vary depending on OS). The value for SS_DBA_GRP in these file is typically set to “dba” :
Let’s check it as oracle user:

[oracle@orclprod ~]$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(orcldba)

Look for the groups value here – 54322(orcldba), the value is orcldba so we need to modify the config.c or config.s so that it should look like:

#define SS_DBA_GRP "orcldba"

After making changes to config file make sure that:
– no oracle processes are running
– login as oracle
– $LD_LIBRARY_PATH and $ORACLE_HOME are set properly
and relink oracle binaries with the following command:

$ORACLE_HOME/bin/relink all

If all previous settings are correct and still ORA-1031 is coming then the only option is to take strace output and check while opening which file the error is coming – for example:

strace -o /tmp/strace.txt sqlplus "/ as sysdba"

Automating Oracle database startup and shutdown on Linux.

If you are using Oracle Clusterware 10gR2 or above for RAC or just for a single instance using ASM, the Clusterware automatically starts and stops the Oracle database instances and listeners, so the following procedures are not necessary. Where the Clusterware is not being used, these methods allow you to automate the startup and shutdown of Oracle databases on Linux.
The following represents the Oracle recommended method for automating database startup and shutdown of Oracle 9i instances on Linux, but it works equally well for Oracle 10g, 11G and 12c also. It can be used on any RHEL-style distribution, including Oracle Linux, up to and including Oracle Linux 7.

The below example was created on Oracle Linux 7 and Oracle database 11gR2.
Once the instance is created, edit the /etc/oratab file setting the restart flag for each instance to ‘Y’.


Create a file called ‘/etc/init.d/dbora’ as the root user, containing the following code.

# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
# Set ORA_OWNER to the user id of the owner of the 
# Oracle database in ORA_HOME.

export ORACLE_UNQNAME=orcl11gr2

if [ ! -f $ORA_HOME/bin/dbstart ]
    echo "Oracle startup: cannot start"

case "$1" in
        # Start the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        # Remove "&" if you don't want startup as a background process.
        #su $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start" &
        su $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME" &
        touch /var/lock/subsys/dbora
        # Stop the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
        #su $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
        rm -f /var/lock/subsys/dbora

The lines to start and stop the listener are no longer needed under Oracle 10g Release 2 onward, as the dbstart command includes an automatic start of the listener. If you still need the commands to start and stop listener – just unhash them.
Use the chmod command to set the privileges to 750:

chmod 750 /etc/init.d/dbora

Associate the ‘dbora’ service with the appropriate run levels and set it to auto-start using the following command.

chkconfig --add dbora

The relevant instances should now startup/shutdown automatically at system startup/shutdown.
Now to start the database run the command as root user:

service dbora start

Similarly to stop the database run the command as root user:

service dbora stop

How to install ODBC driver for Oracle in Windows 7.

2014-07-28 22 comments

Installation of the ODBC driver in Windows 7 is quite simple but there are a few tricks better to know. Here is a step-by-step instruction how to install the ODBC driver.
First of all, you need to download proper files:
– here is the link for Instant Client Downloads for Microsoft Windows (32-bit), for ODBC, you need and files (Oracle version)
– here is the link for Instant Client Downloads for Microsoft Windows (x64), for ODBC, you need and files (Oracle version)

Which version to choose – 32-bit or 64-bit?
Well, I think the best way to answer this question is to describe my situation. I need to get data from Oracle to Excel. I have on my PC: Windows 7 – 64-bit version, 64-bit Oracle client already installed but MS Office in 32-bit version. So in my situation I had to choose 32-bit ODBC driver. Generally, you choose ODBC driver version based on tool version, that you use to get data from Oracle database.

Anyway, both downloaded files, you need to unzip to THE SAME folder (for example: c:\oracle\instant_client_11), then add the folder to the PATH environment variable:
Then add the TNS_ADMIN environment variable indicating the path to the tnsnames.ora file (in my PC it is c:\oracle\11.2.0\CLIENT\network\admin):
Next, open the command line (Run as administrator) and go to the folder where you unzip ODBC driver, in my case:

cd c:\oracle\instant_client_11

and then – still in cmd, install ODBC:


When successfully, you should get the following info:

Oracle ODBC Driver is installed successfully.

So, right now we can configure ODBC connection: choose Control Panel, then Administrative Tools, then Data Sources (ODBC), then System DNS and then Add, on the list, choose instant_client_11, then Finish and then in the configuration window… wait. You don’t have instant_client_11 on the list. That’s the problem I also had – ODBC driver didn’t appear in ODBC data source.
This is because you use 64-bit ODBC administration panel. If you install 32-bit ODBC driver, you’ll need to use 32-bit ODBC administration panel – run odbcad32.exe from c:\windows\SysWOW64.

So, on the list, choose instant_client_11, then Finish and then in the configuration window add proper data in the Data Source Name, Description, TNS service name (from tnsnames.ora) and User ID.
Then, you can test connection and when everything is correct, save the connection, close ODBC window and enjoy your Oracle data in MS Office tools 🙂

Exploring the LOCAL_LISTENER parameter.

The original version of the below article was created by Ed Stevens and could be find here.
In one of my previous post “Resolving Oracle networking problems – ORA-12514” I mentioned the role of the initialization parameter LOCAL_LISTENER in dynamic registration of the database instance to the listener. Now I’d like to deliver on my promise to explore that piece of the puzzle.

The LOCAL_LISTENER initialization parameter.
So we’ve seen that the listener is able to start up and successfully handle connection requests without a listener.ora configuration file. It does this by using all defaults (including the listener name and port) and the database instance is able to register itself with the listener.
How does the instance know how to contact the listener in order to register itself? It uses the initialization parameter LOCAL_LISTENER. From the Oracle Database Reference 11g Release 2 (11.2) we read:
LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners
Let’s see what my instance says about that:

OK, so the instance is supposed to use LOCAL_LISTENER to locate the listener so that it (the instance) can register itself with the listener. But I don’t have LOCAL_LISTENER set to anything. Well, it so happens that LOCAL_LISTENER has a default value that dovetails nicely with the default settings of the listener. Again, from the Reference manual:
Default value: (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521)) where hostname is the network name of the local host.
I’ve seldom found a good reason NOT to run the listener with anything other the default name and port, but some people insist, and that’s when we need to adjust LOCAL_LISTENER to match up.

A mismatch has been detected between Redo records and Rollback (Undo) records ORA-00600/ORA-00607.

Last night I got the following error on one of my database (Oracle 9i):

ORA-00600: internal error code, arguments: [4193], [4271], [4056], ...

The Oracle Support gives more information about it:

versions 6.0 to 10.1
We are validating the Undo block sequence number in the undo block against
the Redo block sequence number relating to the change being applied.
This error is reported when this validation fails.
process failure
possible rollback segment corruption
This error may indicate a rollback segment corruption.
This may require a recovery from a database backup depending on the situation.

Here is the possible solution without recovery from a database backup. In SQLPLUS type the following command:

show parameter undo;

The output should be similar to this:

NAME              TYPE     VALUE
----------------- -------- ---------
undo_management   string   AUTO
undo_retention    integer  900
undo_tablespace   string   UNDOTBS

If the parameter UNDO_MANAGEMENT is set to AUTO, then change it to MANUAL (in my case I have the database on Windows Server and use the spfile):

alter system set undo_management='MANUAL' scope=spfile;
shutdown immediate;
create undo tablespace NEW_UNDOTBS datafile 'e:\oracle\oradata\SID\NEW_UNDOTBS01.DBF' size 1G;
alter system set undo_tablespace='NEW_UNDOTBS' scope=spfile;
alter system set undo_management='AUTO' scope=spfile;

After another shutdown the new parameters should be taken and if you want you can drop old UNDO tablespace. Sometimes when you try to do it, you can get the error similar to that:

drop tablespace UNDOTBS01 including contents;
ORA-01548: active rollback segment '_SYSSMU1_1255349037$' found, terminate dropping tablespace

How to handle that situation?
In SQLPLUS type the following command:

select * from v$rollname;

The output should be similar to this:

USN        NAME
---------- -----------------
0           SYSTEM
1          _SYSSMU1$
2          _SYSSMU2$
3          _SYSSMU3$
4          _SYSSMU4$
5          _SYSSMU5$
6          _SYSSMU6$

You need to create PFILE from SPFILE and shutdown the database:

create pfile='c:\new.init' from spfile;
File Created
shutdown immediate;

Then edit pfile and made the following changes:


Then startup database from new pfile, then try to drop old UNDO tablespace, this way should work now. After drop old UNDO tablespace shutdown the database and start it up with the spfile.

Resolving Oracle networking problems – ORA-12514.

The original version of the below article was created by Ed Stevens and could be find here.
In previous posts I have explored various reasons why a client process might not be able to connect to an Oracle database. In “Resolving Oracle networking problems – general introduction” I presented an overview of the process used by sqlnet to convert a connection request to a network connection descriptor and deliver that request to a listener capable of servicing the request. I followed that with two posts going into more depth on the types of problems that can prevent the request from reaching the host server. Now I would like to look at the next possible problem in the sequence, in particular the “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”.
For this demonstration I will generate the error then go through the standard analysis and solution. After that, I will explore some of the interesting factors that flow from this.

The ORA-12514 error.
The vast majority of the time, this error results from an incorrectly specified connect descriptor in the tnsnames.ora file. Let’s look at a very typical example then diagnose and fix it. After that we will dig in to how the listener comes to know of a service name.

Of course the very first thing anyone should do when confronted with an Oracle error is to check the official description. There are many sources on the web, but I like to start with ‘oerr’ utility on the server:

[oracle@orclsvr ~]$ oerr ora 12514
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a service name for a service (usually a database service)
// that either has not yet dynamically registered with the listener or has
// not been statically configured for the listener.  This may be a temporary
// condition such as after the listener has started, but before the database
// instance has registered with the listener.
// *Action:
//  - Wait a moment and try to connect a second time.
//  - Check which services are currently known by the listener by executing:
//    lsnrctl services
//  - Check that the SERVICE_NAME parameter in the connect descriptor of the
//    net service name used specifies a service known by the listener.
//  - If an easy connect naming connect identifier was used, check that
//    the service name specified is a service known by the listener.
//  - Check for an event in the listener.log file.

The error is pretty self-explanatory: “listener does not currently know of service requested in connect descriptor”. So how do we know exactly what service was “requested in connect descriptor”? First, do a sanity check by looking at the tnsnames.ora file.

# tnsnames.ora Network Configuration File: c:\oracle\11.2.0\CLIENT\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
myorcl =
(ADDRESS = (PROTOCOL = TCP)(HOST = orclsvr)(PORT = 1521))

In line 8 we see that we should be requesting SERVICE_NAME = orcl. In our discussion of ORA-12154 I described how we might not be using the tnsnames.ora we thought we were. At this point we know what SERVICE_NAME we should be using. We can use tnsping to confirm this.

In the above screen we can see that we are requesting a connection to service ‘orcl’. Don’t be fooled by the good return code. As we saw in “Resolving Oracle networking problems – TNSPING: what it is, what it isn’t“, tnsping only goes as far as confirming there is a listener at the specified IP address and port. It says nothing about any services the listener knows about. The presence of SERVCICE_NAME in the feedback is simply the result of showing the entire connect descriptor.
Now that we know what service name was actually requested, we need to check what the listener knows about. Examining the listener configuration file, listener.ora, could give some clues but it is not the whole story. In fact, the listener can be started without any listener.ora file at all. The only sure way to tell what the listener knows about is to ask it directly, with the lsnrctl command:

This listener knows about two service names (orclnew, orclnewXDB), all associated with the instance ‘orclnew’. The XDB service is for special use – we can ignore it for general connection problems. The vast majority of the time, that’s all the debugging we need to do. We know that we should be requesting a connection to service ‘orclnew’ instead of ‘orcl’. Since that request comes from the client, we have to fix tnsnames.ora:

# tnsnames.ora Network Configuration File: c:\oracle\11.2.0\CLIENT\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
myorcl =
(ADDRESS = (PROTOCOL = TCP)(HOST = orclsvr)(PORT = 1521))
(SERVICE_NAME = orclnew)

and make another test:

We have made the necessary correction in tnsnames.ora. The subsequent connection request is successful, thus validating our analysis and corrective action.

Registering the service with the listener.
So how did the listener come to know about service ‘orclnew’ in the first place? There are two methods by which a service is registered with the listener – ‘static’ and ‘dynamic’. We’ll discuss each in turn:
Static registration.
Static registration is accomplished by configuring the SID_LIST section of the listener.ora file (on the server side).

# listener.ora Network Configuration File: c:\oracle\11.2.0\DATABASE\network\admin\listener.ora
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\oracle\11.2.0\DATABASE)
      (PROGRAM = extproc)
    (SID_DESC =
      (GLOBAL_DBNAME = test)
      (ORACLE_HOME = c:\oracle\11.2.0\DATABASE)
      (SID_NAME = test)
    (SID_DESC =
      (ORACLE_HOME = c:\oracle\11.2.0\DATABASE)
      (SID_NAME = orclnew)

      (ADDRESS = (PROTOCOL = TCP)(HOST = orclsvr)(PORT = 1521))

We can see three SIDs listed: PLSExtProc, test and orclnew – checking the status of the listener, we get:

Notice the entry for service “test” maps back to “(GLOBAL_DBNAME=test)” and is related to instance “test”. Further, notice that its status is UNKNOWN. This status of UNKNOWN is the indication that this registration came from the SID_LIST section of listener.ora. It is unknown because the listener doesn’t make a check to see if there is an instance named “test” broadcasting a service name of “test”. The listener is just saying “if you ask for a connection to ‘test’, I’ll see what I can do to service it”. In fact, I have no database named “test”.
Notice also that service “orclnew” has two instances, one unknown, and one READY. Like “test”, the UNKNOWN “orclnew” comes from listener.ora, the READY instance comes from the database having registered with the listener (dynamic registration).
Again, for our current discussion, we can ignore the service orclnewXDB – it has special internal uses for Oracle.
For the remainder of the discussion, I am going to completely remove listener.ora, then restart the listener so that it has no static registrations and is running with all default values:

With no static registration the listener will start with all default values and support no services until a PMON process registers itself.

Dynamic registration.
Dynamic registration is accomplished when the PMON process of the database instance contacts the listener and requests registration. This occurs at instance startup, and every few minutes during the life of the instance.

There are three initialization parameters that affect what service name(s) PMON will register with the listener:

You should look up each one in the Reference Manual and read the descriptions, notice particularly in the description of SERVICE_NAMES the following:
If you do not qualify the names in this parameter with a domain, Oracle qualifies them with the value of the DB_DOMAIN parameter. If DB_DOMAIN is not specified, then no domain will be applied to the non-qualified SERVICE_NAMES values.
There is another interaction that is not spelled out in the Reference Manual, but mentioned in the Net Services Administrator’s Guide:
The service name defaults to the global database name, a name comprising the database name (DB_NAME parameter) and domain name (DB_DOMAIN parameter).
The normal practice might be to make the SERVICE_NAMES parameter the same as the DB_NAME.
All service names – those derived from DB_NAME as well as the one derived from SERVICE_NAMES – will have the value of DB_DOMAIN appended to them (if we have the fully qualified service name in the SERVICE_NAMES initialization parameter, the value of DB_DOMAIN will not apply).

We have explored the relationship between the connect descriptor issued by the client and the services supported by the listener, as well as the factors that control what services the listener supports. In the concluding post in this series, I will discuss how the database locates the listener in order to register its services – the LOCAL_LISTENER initialization parameter.

Exclude table/tables during export.

Let’s assume you’re using EXP, not EXPDP to backup your tables (I still have some Oracle 9i databases). You have schema with about 1000 tables, and the list can change. There is one table that is huge, and you don’t need to export this or one of this table is corrupted, DBMS_REPAIR doesn’t work and you want the full export without this one table. This is what you can do:

1. Assume that user you use to export is SYSTEM (of course you can use other user name).

2. Create function (SYS AS SYSDBA) that returns condition that is false when user is SYSTEM:

(obj_schema VARCHAR2, obj_name VARCHAR2)
RETURN VARCHAR2 IS d_predicate VARCHAR2(2000);
if sys_context ('USERENV', 'SESSION_USER') = 'SYSTEM' THEN
d_predicate := '1=2';
d_predicate := '';
end if;
RETURN d_predicate;
END exclude_table;

So, if user is SYSTEM, condition 1=2 will be added to select statement doing export, as a result no rows will be exported.

3. Add policy for each table you want to exclude:


4. Now when you run export, table EXAMPLE_SCHEMA.EXAMPLE_TABLE will be skipped, like this:

EXP-00079: Data in table "EXAMPLE_TABLE" is protected. Conventional path may only be exporting partial table.

One important thing to remember – you cannot use option DIRECT in export.
During import the empty table EXAMPLE_TABLE will be created with all related objects (indexes, constraints, triggers, etc.).