Connecting to Oracle 12c

A couple of weeks ago, I installed Oracle 12c on my Debian amd64 system. It is pretty easy to do, all you have to do is create a few symlinks that fuck up your 32/64 lib dirs and hack the makefiles ... another story for another blog post.

Today, I would like to talk about Oracle's new CDB & PDB architecture.

SID is Viciously dead, they say, but instance name is not - this is crazy, I need the address of Larry's pharmacist, he must have good stuff.

So, I have an Oracle instance named myinstance, it contains the CDB and a few PDB's.


Now, I connect to Oracle using user sys, I immediately end up in the CDB. Usernames/schemas in a CDB require a silly prefix, WTF, this is not the case in a PDB and it gets worse ...

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
myinstance

So I change to a PDB:

SQL> alter session set container=MyPDB;

Session altered.

SQL>  select instance_name from v$instance;

INSTANCE_NAME
----------------
myinstance


Now, this makes sense, one instance with multiple DB's.

So, Oracle says we are on the same instance as the CDB. When I connect to the instance_name, I will always end up in the CDB .... to reach a PDB, I must connect with the service name, the service name is the name of the PDB, in my case MyPDB.


Now, why, why did they not deprecate connecting with instance_name ?


Always use the service name when you connect to Oracle, this is what they said in the ojdbc drivers for 10g, iirc.

  Connection conn = DriverManager.getConnection
     ("jdbc:oracle:thin:@//localhost:1521/MyPDB", "scott", "tiger");

However, they removed that recommendation in the  12c jdbc driver docs, do they need help or are they stupid ?

Oracle 10R2 on Mac OS X

I wanted to install Oracle 10.2 on Mac OS X 10.6.6 64Bit and found a couple of issues that you might encounter as well. I could not get it to link the Oracle binaries, so this is all a bit moot, for the moment. Will post as soon as I have found a solution for that - I use db2 instead, also a post on installing db2 on darwin in the works.

First of all, create the users and groups as described in the installation instructions:
1. find free id's for groups:

dscl . -list /groups gid | awk '{print $2}' | sort -n

2. find free id's for oracle user:

dscl . -list /users uid | awk '{print $2}' | sort -n

I used uid/gid's 300, 301, 301 for oracle and oinstall, dba, oper, respectively

3. create the groups

dscl . -create /groups/oinstall
dscl . -append /groups/oinstall gid 300
dscl . -append /groups/oinstall passwd "*"


dscl . -create /groups/dba
dscl . -append /groups/dba gid 301
dscl . -append /groups/dba passwd "*"
dscl . -append /groups/dba GroupMembership hans

dscl . -create /groups/oper
dscl . -append /groups/oper gid 302
dscl . -append /groups/oper passwd "*"
dscl . -append /groups/oper GroupMembership hans

4. create the oracle user

You could also use system preferences to create the user, in which case you only have to execute the last three of the dscl commands.

dscl . -create /users/oracle
dscl . -append /users/oracle uid 300
dscl . -append /users/oracle shell /bin/bash
dscl . -append /users/oracle home /Users/oracle
dscl . -append /users/oracle realname "Oracle software owner"

dscl . -append /users/oracle gid 302
dscl . -append /groups/dba GroupMembership oracle
dscl . -append /groups/dba GroupMembership hans

mkdir /Volumes/oracle
chown oracle:oinstall /Volumes/oracle
passwd oracle

5. Next, specify the kernel parameters:

I created a file named /etc/sysctl.conf with the following contents:

kern.sysv.semmsl=87381
kern.sysv.semmns=87381
kern.sysv.semmni=87381
kern.sysv.semmnu=87381
kern.sysv.semume=10
kernel.shmall=2097152
kernel.sys.shmmax=2147483648
kernel.sys.shmmni=4096
kern.maxfiles=65536
kern.maxfilesperproc=65536
net.inet.ip.portrange.first=1024
net.inet.ip.portrange.last=65000
kern.corefile=core
kern.maxproc=2068
kern.maxprocperuid=2068

6. Reboot, log on as oracle
In terminal, you want to start the 32bit Java VM:
Run:

java -version

If java -version shows it is running a 64Bit JVM, you need the 32Bit one:

./runInstaller -J-d32

I got the following error when trying to start runInstaller with the 64Bit JVM:

Install2011-02-08_07-12-16AM/oui/lib/mac_osx/liboraInstaller.dylib:  no suitable image found.  Did find:  /private/tmp/OraInstall2011-02-08_07-12-16AM/oui/lib/mac_osx/liboraInstaller.dylib: mach-o, but wrong architecture

TNS-04413: A shared object was found in the subtree

I installed Oracle 11g on Linux, debian squeeze, last week and came across a weird error after having had to configure the sqlnet stuff. Oracle doesn't seem to be able to do that on debian-based systems, nor on Solaris ... so maybe all unices are affected. I don't know, I must be making a big mistake somewhere during installation ... ;-)

So, I thouht I had set everything up as it should be, but I had a problem in the sqlnet configuration, as the listener did not want to serve my database.

I started dbca to check if it could find the database and got several errors, that pointed me in the right direction ... ;-).

I figured it out in the end, I had this line:


(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
iso
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

I had somehow gotten the listener port at the end of EXTPROC, don't ask me how that happened ... must have made a typo in vi. The weird thing is, that I found nothing on the interwebs about the error:

TNS-04413: A shared object was found in the subtree

So here is a blog entry about that ... ;-)

See below:

oracle@hpcarpenter:~$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-MAY-2010 14:57:07

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /u00/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u00/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u00/app/oracle/diag/tnslsnr/hpcarpenter/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hpcarpenter.masalan)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 17-MAY-2010 14:57:09
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u00/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u00/app/oracle/diag/tnslsnr/hpcarpenter/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hpcarpenter.masalan)(PORT=1521)))
The listener supports no services
The command completed successfully
oracle@hpcarpenter:~$ dbca
Can't get a listener LISTENER Exception: oracle.net.config.ListenerException: TNS-04412: A problem reading or writing an address
caused by: oracle.net.config.SOExceptionConfig:
TNS-04413: A shared object was found in the subtree