Archive

Posts Tagged ‘Oracle 10g’

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

Many times we can see an issue like this:
screen1
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.
screen2
Then, set the SQLNET.AUTHENTICATION_SERVICES parameter to NTS in the sqlnet.ora file (located in the $ORACLE_HOME/network/admin):

SQLNET.AUTHENTICATION_SERVICES=(NTS)

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:

echo $ORACLE_SID
echo $ORACLE_HOME

Find the values returned by above commands and match these values under /etc/oratab file, these have to be listed there.
screen3
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):

SQLNET.AUTHENTICATION_SERVICES=(ALL)

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.
screen4
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” :
screen5
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’.

orcl11gr2:/u01/app/oracle/product/1120:Y

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

#!/bin/sh
# 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.

#ORA_HOME=/u01/app/oracle/product/10.2.0/db_1
#ORA_HOME=/u01/app/oracle/product/11.1.0/db_1
ORA_HOME=/u01/app/oracle/product/11.2.0/db_1
#ORA_HOME=/u01/app/oracle/product/12.1.0/db_1
ORA_OWNER=oracle
export ORACLE_UNQNAME=orcl11gr2

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

case "$1" in
    'start')
        # 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')
        # 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
        ;;
esac

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

Messages ORA-0 and SMG-3503 in the alert log file.

Last night I got the following error in the alert log file on one of my database (Oracle 10g):

ORA-0 encountered when generating server alert SMG-3503

I started to search and found that probably the SYS.ALERT_QT is missing or corrupted. So I checked the SYS.ALERT_QT table more specifically and found that there are some bad blocks in the SYSAUX tablespace (object with bad blocks was of course the SYS.ALERT_QT table).
So solution is to recreate the SYS.ALERT_QT table – in the Metalink you can find something about it:
– note 430146.1 How to recreate the SYS.ALERT_QUE

For Oracle 11g you can use catmwin.sql which has the steps to recreate the ALERT_QT. Alternatively, for 11g you can use the catproc.sql to recreate.
In some cases you had to force drop the queue before, please do the following:
Run sqlplus, log in as SYS AS SYSDB:

alter system enable restricted session;

To drop server alert schema and ALERT_QT table.

@$ORACLE_HOME/rdbms/admin/catnoalr.sql
exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => ‘sys.alert_qt’, FORCE => TRUE);

To re-create tables, sequence, type and queue for server alert.

@$ORACLE_HOME/rdbms/admin/dbmsslrt.sql
@$ORACLE_HOME/rdbms/admin/catalrt.sql

To recompile the invalid objects

@$ORACLE_HOME/rdbms/admin/utlrp.sql
alter system disable restricted session;

This procedure may leave DBSNMP.MGMT_BSLN_INTERNAL invalid. To validate the same run catsnmp.sql script.

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 instantclient-basic-nt-11.2.0.3.0.zip and instantclient-odbc-nt-11.2.0.3.0.zip files (Oracle 11.2.0.3 version)
– here is the link for Instant Client Downloads for Microsoft Windows (x64), for ODBC, you need instantclient-basic-windows.x64-11.2.0.3.0.zip and instantclient-odbc-windows.x64-11.2.0.3.0.zip files (Oracle 11.2.0.3 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:
screen1
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):
screen2
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:

odbc_install.exe

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 🙂

Invalid DBMS_SQLPA package and poor database performance.

Recently, when I delivered Oracle 10gR2 (10.2.0.4) production database to the users, they started to claim that application is working very slowly. I’ve checked server and noticed unusual CPU overload. And then started to dig deeper. Finally, I noticed that I have a few invalid objects in database (what was strange because I could swear that everything was perfect just few hours before).
Anyway, I tried to recompile invalid object and left with one that I couldn’t recompile – DBMS_SQLPA. In the Metalink I found something about it:
– note 565600.1 ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA
– bug 6893073 – DBMS_SQLPA IS INVALID ORA-904: “OTHER_XML”: INVALID IDENTIFIER

And above could happen after upgrade to Oracle 10.2.0.4 – but the problem is that I didn’t upgrade to this version (at least not at that time). So, to make long story short, below you have the solution to recompile invalid DBMS_SQLPA package. Log in to the sqlplus (SYS AS SYSDBA) and execute the following commands:

drop table plan_table;
@ORACLE_HOME\rdbms\admin\utlxplan.sql
@ORACLE_HOME\rdbms\admin\prvtspao.plb
@ORACLE_HOME\rdbms\admin\utlrp.sql

To verify if everything is valid execute the following command:

select substr(comp_id,1,15) comp_id, substr(comp_name,1,30) comp_name,
substr(version,1,10) version, status from dba_registry order by modified;
Categories: Oracle, RDBMS Tags: , ,

How to install Java in Oracle Database – ORA-29538.

2014-02-15 1 comment

Recently, the developer met the following error message while trying to create a java object using SQL Developer:
ORA-29538: Java not installed

Well, first let’s check if the JVM components is really not installed in Oracle – verify installed components:

select comp_name, version, status from dba_registry;

java1

to be double sure, we run another query which should return no rows:

select distinct owner,name from dba_source where lower(NAME)='dbms_java';

So, to install the JVM you should run (sys as sysdba) initjvm.sql located in $ORACLE_HOME\javavm\install:
java2

Now, check if JVM was installed successfully:

select comp_name, version, status from dba_registry;

java3

The problem solved 🙂

How to (re)configure Oracle Enterprise Manager – unexpected part II.

2013-07-30 1 comment

When I wrote about (re)configuring Oracle Enterprise Manager last time, I wrote something like that: “When you have a problem with creating the Oracle EM via command line (you’ve got the insufficient privileges error) try to use the Database Configuration Assistant“. I think it’s time to be more specific 🙂
Suppose, we did the following:

set ORACLE_SID=orcl
emca -config dbcontrol db -repos create

and get the insufficient privileges error:

STARTED EMCA at Jun 28, 2012 11:08:59 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID:  orcl
Exception in thread "main" oracle.sysman.emcp.exception.DatabaseUnavailableException: Database instance unavailable.
        at oracle.sysman.emcp.DatabaseChecks.throwDBUnavailableException(DatabaseChecks.java:151)
        at oracle.sysman.emcp.DatabaseChecks.checkDbAvailabilityImpl(DatabaseChecks.java:144)
        at oracle.sysman.emcp.DatabaseChecks.checkDbAvailability(DatabaseChecks.java:163)
        at oracle.sysman.emcp.DatabaseChecks.getDbServiceName(DatabaseChecks.java:582)
        at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1272)
        at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:574)
        at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:522)

and the emca log contains the below details:

oracle.sysman.emcp.util.GeneralUtil initSQLEngineLoacly
CONFIG: ORA-01031: insufficient privileges
oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-01031: insufficient privileges
at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(SQLEngine.java:1655)
at oracle.sysman.assistants.util.sqlEngine.SQLEngine.connect(SQLEngine.java:978)
at oracle.sysman.emcp.util.GeneralUtil.initSQLEngineLoacly(GeneralUtil.java:445)
at oracle.sysman.emcp.util.GeneralUtil.initSQLEngine(GeneralUtil.java:637)
at oracle.sysman.emcp.DatabaseChecks.checkDbAvailabilityImpl(DatabaseChecks.java:118)

From the above logs, we find two hints: database instance is down and ORA-01031. So, some possible reasons may be:
1. Database may not be up – confirm that the database is up. Connect to the database via SQLPLUS

sqlplus / as sysdba

and do

select instance_name, status, version from v$instance;

the output should be similar to that:

INSTANCE_NAME STATUS VERSION
------------- ------ ----------
orcl          OPEN   11.2.0.3.0

2. Confirm whether the ORACLE_HOME environmental variable is correct. This involves comparing the current ORACLE_HOME environmental setting, with the one that was set the last time the database was started.
To check the current ORACLE_HOME (MS Windows):

set ORACLE_HOME

To check the ORACLE_HOME which was set at the time when the database was last started, connect to SQLPLUS (sys as sysdba) and issue the following:

var OHM varchar2(100);
exec dbms_system.get_env('ORACLE_HOME', :OHM);
print OHM;

If the ORACLE_HOME was set wrongly at the time that the database was started (as determined by above steps), then the database will need to be shut down and restarted. To shut the database down, it will be necessary to set the “wrong” ORACLE_HOME (the one which is returned in the OHM output). After the database has been successfully shut down, then the correct ORACLE_HOME should be set, and the database can be re-started.

3. Confirm whether it’s possible to connect to the database as the SYSDBA user remotely, using sqlplus. As part of the repository creation/drop process, the SYS user needs to be able to connect as SYSDBA. The “ORA-01031: insufficient privileges error” is returned when the SYS user is unable to connect as SYSDBA. To test this outside of dbconsole, test a sqlplus remote connection (one which connects via a tnsnames.ora alias) as sysdba, eg:

sqlplus sys/@ as sysdba

If the above returns the ORA-01031 error check whether the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set correctly in the database. For the purposes of configuring dbconsole, it must be set to ‘EXCLUSIVE’. This value can be checked this way:

show parameter remote_login_passwordfile

and the output should be like that

NAME                      TYPE   VALUE
------------------------- ------ ---------
remote_login_passwordfile string EXCLUSIVE

If this parameter is not set to ‘EXCLUSIVE’, the following command can be run:

alter system set remote_login_passwordfile=EXCLUSIVE scope=both;

For dbconsole 11.2.0.1, 11.2.0.2 and 11.2.0.3 check whether the %ORACLE_HOME%\network\admin\sqlnet.ora (or %TNS_ADMIN%\sqlnet.ora) contains the entry:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

In my case the value of SQLNET.AUTHENTICATION_SERVICES was NONE. So, I changed to NTS and think that finally I’ll be able to configure the OEM. Unfortunatelly, still there was an error. So, I started to dig up the logfiles in the %ORACLE_HOME%\_\sysman\log folder and there found the source of the problem (the OracleDBConsolesrvc.log and emdb.nohup are especially useful).