Archive

Posts Tagged ‘Oracle 12c’

ORA-00604 and ORA-29516 while dropping user accounts.

Lately, I was trying to drop a user account in one of my database. During this operation I came across the following errors:
ORA-00604: error occurred at recursive SQL level 1 and ORA-29516: Aurora assertion failure: Assertion failure.
It was obvious that the the real problem was the second error: ORA-29516. I quickly figured out that the JAVA component was corrupted – you can check this by executing java related packages:

select dbms_java.longname('TEST') from dual;

and then you’ve got the following error:

ERROR at line 1:
ORA-29516: Aurora assertion failure: Assertion failure at eox.c:359
Uncaught exception Root of all Java exceptions: java.lang.UnsatisfiedLinkError
sun.net.PortConfig.getLower0
ORA-06512: at “SYS.DBMS_JAVA”, line 139

The solution is relatively simple, just follow below steps:

execute sys.dbms_registry.loaded('JAVAVM');
execute sys.dbms_registry.valid('JAVAVM');
alter system set "_system_trig_enabled"=false scope=memory;
alter system set java_jit_enabled=false;
create or replace java system 
/

and now you can check :

select dbms_java.longname(‘TEST’) from dual;

DBMS_JAVA.LONGNAME(‘TEST’)
--------------------------
TEST

That’s all.

ORA-12638: Credential retrieval failed.

If the Oracle client was installed using the domain administrator account instead of the local administrator, the following message could be displayed when trying to connect:
ORA-12638: Credential retrieval failed

From the wording of the error message one could think that this error means that he is using the wrong password but this is not the case. To solve this:
1. Go to ORACLE_HOME/network/admin and open sqlnet.ora file.
2. Search for SQLNET.AUTHENTICATION_SERVICES=(NTS) and change it to SQLNET.AUTHENTICATION_SERVICES=(NONE)
3. Save the file and try to connect, this should work…

Categories: Oracle, RDBMS Tags: , ,

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

How to simplify Oracle Database 11gR2 and 12cR1 installation on Oracle Linux 7.

Before installing Oracle Database 11g or 12c on a Linux system, you need to preconfigure the operating system environment since the Oracle database requires certain software packages, package versions, and tweaks to kernel parameters.
You can do it manually – be sure to review the appropriate Oracle Database installation guide to familiarize yourself with hardware, software, and operating system requirements. On Oracle Linux, however, there is a remarkably easy way to address these installation prerequisites automatically.
First, depending on your database version, install either the RPM package called oracle-rdbms-server-11gR2-preinstall or oracle-rdbms-server-12cR1-preinstall (formerly known as oracle-validated). This RPM packages performs a number of preconfiguration steps, including the following:
1. Automatically downloading and installing any additional software packages and specific package versions needed for installing Oracle Grid Infrastructure and 11g Release 2 (11.2.0.4) or Oracle Database 12 c Release 1 (12.1), with package dependencies resolved via yum or up2date capabilities.
2. Creating the user oracle and the groups oinstall (for OraInventory) and dba (for OSDBA), which are used during database installation. For security purposes, this user has no password by default and cannot log in remotely. To enable remote login, you need to set a password manually.
3. Modifying kernel parameters in /etc/sysctl.conf file to change settings for shared memory, semaphores, the maximum number of file descriptors, and so on.
4. Setting hard and soft shell resource limits in /etc/security/limits.conf file, such as the locked-in memory address space, the number of open files, the number of processes, and core file size.
5. Setting numa=off in the kernel for x86_64 machines.
Note that oracle-rdbms-server-11gR2-preinstall or oracle-rdbms-server-12cR1-preinstall parses the existing /etc/sysctl.conf and /etc/security/limits.conf files and updates values only as needed for database installation. Any precustomized settings not related to database installation are left as is.
The oracle-rdbms-server-11gR2-preinstall and oracle-rdbms-server-12cR1-preinstall RPM packages are accessible through the Oracle Unbreakable Linux Network (ULN, which requires a support contract), from the Oracle Linux distribution media, or from the Oracle public yum repository. In addition, the Oracle public yum repository now includes all security and bug errata, ensuring systems are secured and stable with the latest security updates and bug fixes.
To install the oracle-rdbms-server-11gR2-preinstall package with YUM repository use the following command (as root):

[root@orclprod ~]# yum -y --enablerepo=ol7_addons install oracle-rdbms-server-11gR2-preinstall.x86_64

To check what the above package changed, you could use the following command (you should see something like this “# oracle-rdbms-server-11gR2-preinstall setting for …” if the preinstall package was installed successfully):

[root@orclprod ~]# more /etc/sysctl.conf
# oracle-rdbms-server-11gR2-preinstall setting for fs.file-max is 6815744
fs.file-max = 6815744
# oracle-rdbms-server-11gR2-preinstall setting for kernel.sem is '250 32000 100 
128'
kernel.sem = 250 32000 100 128
...
# oracle-rdbms-server-11gR2-preinstall setting for net.ipv4.ip_local_port_range 
is 9000 65500
net.ipv4.ip_local_port_range = 9000 65500

In the perfect world, we should trust the preinstall package and proceed with the Oracle database installation. But I would prefer to check and verify what the preinstall package did and what not. And to do this I’ll use the RDA.sh script.
RDA stands for “Remote Diagnostic Agent” and is a script that can be run to obtain information on the system that it is being run on. Oracle Support will often ask for the results of running this script (which is normally and HTML formatted report), so that they can very quickly with minimal effort of the DBA get an overall picture of the setup and potentially information on the problem/issue.
First, we need to download rda.zip script – it can be download from the Metalink note 314422.1, Remote Diagnostic Agent (RDA) – Getting Started.
Then unzip the tool as user oracle:

[oracle@orclprod ~]$ unzip p21769913_891598_Linux-x86-64.zip

Execute the script as follows (parameters HCVE stand for Health Check Validation Engine):

[oracle@orclprod ~]$ ./rda.sh -T hcve

You will be prompted to answer a question – what ruleset to use (what configuration we want to analyse for):

Processing HCVE tests ...
Available Pre-Installation Rule Sets:
   1.  Oracle Database 10g R1 (10.1.0) Preinstall (Linux)
   2.  Oracle Database 10g R2 (10.2.0) Preinstall (Linux)
   3.  Oracle Database 11g R1 (11.1) Preinstall (Linux)
   4.  Oracle Database 11g R2 (11.2.0) Preinstall (Linux)
   5.  Oracle Database 12c R1 (12.1.0) Preinstallation (Linux)
...

In this case, I am checking for 11.2.0 Preinstall (Linux) which is option 4 – below is a transcript of this:

Enter the HCVE rule set number or 0 to cancel the test
Press Return to accept the default (0)
> 4

Performing HCVE checks ...
Enter value for < Planned ORACLE_HOME location >
> /u01/app/oracle/products/1120    

Enter value for < JDK Home >
> 

Test "Oracle Database 11g R2 (11.2.0) Preinstall (Linux)" executed at 21-Oct-2015 12:27:33

Test Results
~~~~~~~~~~~~
ID     NAME                 RESULT  VALUE
====== ==================== ======= ==========================================
A00010 OS Certified?        WARNING 11.2.0.4+
A00020 User in /etc/passwd? PASSED  userOK
A00040 Group in /etc/group? PASSED  GroupOK
A00050 Enter ORACLE_HOME    RECORD  /u01/app/oracle/products/1120
A00060 ORACLE_HOME Valid?   FAILED  OHnotvalid
A00070 O_H Permissions OK?  SKIPPED Requires valid Oracle home
A00080 oraInventory Permiss PASSED  oraInventoryNotFound
A00090 Got Software Tools?  FAILED  ArElsewhere LdElsewhere NmElsewhere M...
A00100 Umask Set to 022?    PASSED  UmaskOK
A00120 Limits Processes     PASSED  Adequate
A00125 Limits Stacksize     PASSED  Adequate
A00130 Limits Descriptors   PASSED  Adequate
A00140 LDLIBRARYPATH Unset? PASSED  UnSet
A00180 JAVA_HOME Unset?     PASSED  UnSet
A00190 Enter JDK Home       RECORD  
A00200 JDK Version          FAILED  JDK home is missing
A00210 Other O_Hs in PATH?  PASSED  NoneFound
A00220 Other OUI Up?        PASSED  NoOtherOUI
A00230 Temp Adequate?       PASSED  TempSpaceOK
A00240 Disk Space OK?       SKIPPED Requires valid Oracle home
A00250 Swap (in MB)         RECORD  3967
A00260 RAM (in MB)          PASSED  7985
A00270 Swap OK?             FAILED  SwapLessThanRam
A00280 Network              PASSED  Connected
A00290 IP Address           RECORD  10.0.2.101
A00300 Domain Name          RECORD  NotFound
A00310 DNS Lookup           FAILED  nslookup host.domain
A00320 /etc/hosts Format    FAILED  No entry found
A00330 Kernel Parameters OK PASSED  KernelOK
A00380 Tainted Kernel?      PASSED  NotVerifiable
A00400 ip_local_port_range  PASSED  RangeOK
A00480 OL4 RPMs OK?         SKIPPED NotOL4
A00490 OL5 RPMs OK?         SKIPPED NotOL5
A00500 OL6 RPMs OK?         SKIPPED NotOL6
A00510 OL7 RPMs OK?         FAILED  [glibc(i686)] not installed [glibc-de...
A00530 RHEL4 RPMs OK?       SKIPPED NotRedHat
A00540 RHEL5 RPMs OK?       SKIPPED NotRedHat
A00550 RHEL6 RPMs OK?       SKIPPED NotRedHat
A00560 RHEL7 RPMs OK?       SKIPPED NotRedHat
A00570 SLES10 RPMs OK?      SKIPPED NotSuSE
A00580 SLES11 RPMs OK?      SKIPPED NotSuSE
Result file: output/collect/DB_HCVE_A_DB11R2_lin_res.htm

As you can see, we have a bit of work to do, even after the preinstall package installed. So, first of all we need to take care of missing packages. The ‘A00310 DNS Lookup’ and ‘A00320 /etc/hosts Format’ could be ignore – especially, if you don’t have a DNS server. If the ping command resolve tha server name, then it should work. Swap size also could be ignore (4GB will be more than enough).
By the way, many people follow an old rule of thumb that your swap partition should be twice the size of your main system RAM. This rule is a pure nonsense. On a modern system, that’s a lot of RAM, most people prefer that their systems never swap. You don’t want your system to ever run out of RAM+swap, but you usually would rather have enough RAM in the system, so it doesn’t need to swap. The more RAM a system has the less swap space it typically needs.
So what is the lesson learned? Trust, but verify. Alternatively, RTFM 🙂

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 🙂

Yet another post about new earthshaking feature in Oracle 12c.

Dont’ worry – this post (despite the title) won’t be about yet another new earthshaking feature in Oracle 12c. In fact you WILL NOT find here any post about Oracle 12c. At least until Oracle 12c R2 will be ready.
I’ve started to work with Oracle from version 9.2, then Oracle 10g R2, and then Oracle 11g R2. I see no reasons to change something and I’ll wait calmly for the version 12c R2.
By the way, the Oracle 12c pluggable database is a feature (as one Oracle executive has called a “fundamental” architectural change for database technology) that SQL Server had already for at least 10 years.

Categories: Oracle, RDBMS Tags: