Client Connection Fails to Instance on Linux

I faced with a problem which clients were failing to connect to a DB2 instance on linux. To check it out I logged on to the system as root and tried to "su" as instance user and got the following error message:

$ su - ctginst1
"cannot set user id: Resource temporarily unavailable" while trying to login or su as a local user in Red Hat Enterprise Linux

Issue was the user has reached the limit of maximum number of running processes which is set by nproc limit.

The resolution is change the limit for the user. In Red Hat 6 and above add a line for the user with higher limits as follow, mind the newly added last line:

$ vi /etc/security/limits.d/90-nproc.conf 

# Default limit for number of user's processes to prevent
# accidental fork bombs.
# See rhbz #432903 for reasoning.

*          soft    nproc     1024
ctginst1   soft    nproc     65536 

Immediately after, problem is resolved. 

Adding Agent Software to 12c Cluod Control

When you install 12c Cloud Control (you can find how to install 12c Cloud Control in my previous post) it comes with its default agent which depends on your environment. If you install it on a Linux 64-bit box, you get only Linux x86-64 agent. Therefore you need to get agents for multi-OS environment which requires 4 easy steps:

1. Log on to Enterprise manager as sysman or an administrator user, from Setup menu on the right corner move to My Oracle Support --> Set Credentials to access Oracle Support.


2. Check for updates to download from Setup --> Extensibility --> Self Update


A job will be created to check, you can schedule it for later or run it right away. 

3. When job succeeded, go to Setup --> Extensibility --> Self Update. Dig into Agent Software, select the ones you want to download and click download.


Again a job will be created.

4. When download job succeeds, go back to Agent Software page, select the one downloaded and apply it.

Now agent software is ready to deploy.

Installing 12c Cloud Control R5

To install 12 Cloud Control first thing you need is a database. You can find how to install a 12c database for 12c Cloud Control from my last post. After this crucial step, run the installer for Enterprise Manager as oracle (or any user you wish to use as software owner):

$ unzip em12105_linux64_disk1.zip -d oem
$ unzip em12105_linux64_disk2.zip -d oem
$ unzip em12105_linux64_disk3.zip -d oem
$ cd oem
$ ./runInstaller

As a note, pre-install package for RDBMS 12c sets maximum number of files to 1024. However if you're installing OEM on the same server as its database, you need to increase t to 4096 as a requirement of 12c Cloud Control. Otherwise you may get a failure during prerequisite checks step of installation. To achieve this requirement, as root open /etc/security/limits.conf file and edit the necessary lines as follows:

# oracle-rdbms-server-11gR2-preinstall setting for nofile soft limit is 1024
# oracle   soft   nofile    1024
oracle   soft   nofile    4096



I'll choose to install in Simple mode and keep settings and preferences as default. You can move on with advanced type to customize configuration.



Set database connection parameters.



Run root script


On the summary screen you may find URLs to access Enterprise Manager.

Next step is deploying agents...

Installing 12c Database for 12c Cloud Control

As you know, you need a database for 12c Cloud Control as management repository. Since 12c is out there for a while and we are not upgrading our 11gR2 databases to 12c yet, I think it's a good idea to use 12c for this purpose and get custom with it. As a reminder, Oracle says it's free to install a database if you use it for OEM purposes only. So, let's begin.

We'll install 12.1.0.2.4 database on Oracle Linux 6.6

First thing to do, as a requirement, is installing oracle-rdbms-server-12cR1-preinstall package. This package makes the whole process pretty easier. I can say this is first -and frankly the second- reason I choose to use Oracle Linux.

As root:
$ yum install oracle-rdbms-server-12cR1-preinstall

Then create necessary directory for installation and grant privileges:

$ mkdir /u01
$ chown -R oracle:oinstall /u01

Do not forget to set password for oracle user. Then log on as oracle and start installation. Unzip installation files an run the installer:

$ unzip linuxamd64_12102_database_1of2.zip
$ unzip linuxamd64_12102_database_2of2.zip
$ cd database
$ ./runInstaller


Choose to install software only. It'll be a single instance.








 Run root scripts as root




Now install the latest PSU. You can check the latest one from Doc ID 756671.1. Before applying PSU update OPatch first. Check patch number 6880880 for latest OPatch. PSU Installation may differ from version to version, so please refer to its documentation.

Now it's time to create a database. There're Enterprise Manager templates you can download. Follow the link and download db templates zip file. Before running DBCA to create a database unzip the file you downloaded to templates directory:

$ unzip 12.1.0.2.0_Database_Template_for_EM12_1_0_5_0_Linux_x64 -d /u01/app/oracle/product/12.1.0.2/dbhome_1/assistants/dbca/templates/

$ dbca


Choose advanced mode to continue:


Select Database Template fro EM according to your EM deployment size; small, medium or large.


Set your database name

De-select "Configure EM Database Express"




Set appropriate directories for database files


Select the following script file to run. It'll set a couple of parameters:

/u01/app/oracle/product/12.1.0.2/dbhome_1/assistants/dbca/templates/shpool_12.1.0.2.0_Database_SQL_for_EM12_1_0_5_0.sql


Set SGA and PGA according to your server size:








And your database for 12c Cloud Control is ready. On my next post, I'll be explaining 12 Cloud Control installation.




Connection Timeouts and DNS

On an Oracle 11.1.0.7.0 database running on AIX system, clients were complaining about connection timeouts. Tnsping was showing latency more than 1000 ms.

I opened a trace for tnsping using AIX truss:

$ truss -aefo tnsping.trc tnsping <tns_db>

30539848: 31654123: _getpid()                           = 30539848
30539848: 31654123: kopen("/etc/resolv.conf", O_RDONLY) = 5
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kread(5, " n a m e s e r v e r   1".., 4096) = 114
30539848: 31654123: kread(5, " n a m e s e r v e r   1".., 4096) = 0
30539848: 31654123: statx("/etc/resolv.conf", 0x0FFFFFFFFFFF9108, 176, 0) = 0
30539848: 31654123: close(5)                            = 0
30539848: 31654123: socket(1, 1, 0)                     = 5
30539848: 31654123: kfcntl(5, F_SETFD, 0x0000000000000001) = 0
30539848: 31654123: connext(5, 0x0FFFFFFFFFFF8BD8, 1025)        Err#2  ENOENT
30539848: 31654123: close(5)                            = 0
30539848: 31654123: kopen("/etc/netsvc.conf", O_RDONLY) = 5
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kread(5, " #   @ ( # ) 4 3        ".., 4096) = 4096
30539848: 31654123: kread(5, " o n   a n d   r e s o l".., 4096) = 638
30539848: 31654123: close(5)                            = 0
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: kopen("/etc/hesiod.conf", O_RDONLY) Err#2  ENOENT
30539848: 31654123: __libc_sbrk(0x0000000000010020)     = 0x00000001106A4D40
30539848: 31654123: kopen("/etc/irs.conf", O_RDONLY)    Err#2  ENOENT
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: getdomainname(0x09001000A02273B0, 1024) = 0
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: getdomainname(0x09001000A02273B0, 1024) = 0
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: getdomainname(0x09001000A02273B0, 1024) = 0
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: getdomainname(0x09001000A02273B0, 1024) = 0
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: kopen("/etc/hosts", O_RDONLY)               = 5
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kfcntl(5, F_SETFD, 0x0000000000000001) = 0
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kread(5, " #   @ ( # ) 4 7\t 1 . 2".., 4096) = 2018
30539848: 31654123: kread(5, " #   @ ( # ) 4 7\t 1 . 2".., 4096) = 0
30539848: 31654123: close(5)                            = 0
30539848: 31654123: kopen("/etc/hosts", O_RDONLY)               = 5
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kfcntl(5, F_SETFD, 0x0000000000000001) = 0
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kread(5, " #   @ ( # ) 4 7\t 1 . 2".., 4096) = 2018
30539848: 31654123: kread(5, " #   @ ( # ) 4 7\t 1 . 2".., 4096) = 0
30539848: 31654123: socket(2, 2, 0)                     = 6
30539848: 31654123: getsockopt(6, 65535, 4104, 0x0FFFFFFFFFFF7BE4, 0x0FFFFFFFFFFF7BE0) = 0
30539848: 31654123: connext(6, 0x09001000A0167878, 16)  = 0
30539848: 31654123: _esend(6, 0x0FFFFFFFFFFF8AF0, 35, 0, 0x0000000000000000) = 35
30539848: 31654123: _poll(0x0FFFFFFFFFFF7CB0, 1, 1000)  = 1
30539848: 31654123: _enrecvfrom(6, 0x0FFFFFFFFFFF9E20, 1024, 0, 0x0FFFFFFFFFFF8470, 0x0FFFFFFFFFFF7C98, 0x0000000000000000) = 104
30539848: 31654123: _esend(6, 0x0FFFFFFFFFFF8AF0, 47, 0, 0x0000000000000000) = 47
30539848: 31654123: _poll(0x0FFFFFFFFFFF7CB0, 1, 1000)  = 1
30539848: 31654123: _enrecvfrom(6, 0x0FFFFFFFFFFF9E20, 1024, 0, 0x0FFFFFFFFFFF8470, 0x0FFFFFFFFFFF7C98, 0x0000000000000000) = 122
30539848: 31654123: close(6)                            = 0
30539848: 31654123: close(5)                            = 0

As you might notice, what tnsping and also any other Oracle Net service is doing is to check DNS server first then use local hosts file for name resolution. This is the cause of the latency because a public DNS server was set for this server and naturally there was no record for it on the DNS. Even you set hosts file as primary source in the netsvc.conf, Oracle does not care and uses DNS at first. 

After clearing DNS configuration on the server, connection times get into order. It's also a known bug that is fixed in 11.2.0.2. So, you need to upgrade for an appropriate solution.

Spatial Index Creation Fails with ORA-01031

While re-creating a spatial index it failed as follow:

DROP INDEX TEST.SIX_POLYGON;

CREATE INDEX TEST.SIX_POLYGON ON TEST.POLYGON (GEOLOC) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('TABLESPACE=INDEX_TS') NOPARALLEL;

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in R-tree: [mdrcrtscrt]
ORA-13231: failed to create index table [MDRS_1EEB04$] during R-tree creation
ORA-13249: Stmt-Execute Failure: CREATE SEQUENCE "TEST".MDRS_1EEB04$ ORDER START WITH 1 CACHE 100
ORA-29400: data cartridge error
ORA-01031: insufficient privileges
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10

After some digging in, I found the missing privilege: create sequence. You can check if the user has required rights by:

select privilege
from DBA_SYS_PRIVS
where privilege in ('CREATE TABLE', 'CREATE SEQUENCE' )
and grantee = 'TEST';

All needed is to grant create sequence to the target user before creating index. Afterwards you can revoke the right:

GRANT CREATE SEQUENCE TO TEST;

CREATE INDEX TEST.SIX_POLYGON ON TEST.POLYGON (GEOLOC) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('TABLESPACE=INDEX_TS') NOPARALLEL;


REVOKE CREATE SEQUENCE FROM TEST;

Upgrade Failed with PRKO-3226

It's been quite a while since my latest post. Let me get back on track with this short one.

During an upgrade of 10.2.0.5 two node RAC -CRS and ASM- to 11.2.0.4, rootupgrade.sh script failed with some errors on the second node. I corrected them and re-run the script a couple of times. At last rootupgrade.sh ended with the following:

PRKO-3226 : Upgrade from version 11.2.0.4.0 to version 11.2.0.4.0 using srvctl upgrade model command of version 11.2.0.4.0 is not supported

When it's queried, CRS's version seems upgraded:

$ crsctl query crs softwareversion
Oracle Clusterware version on node [node2] is [11.2.0.4.0]

To be sure, I opened an SR on the issue. Oracle Support replied by stating that it's totally a misleading error since the node  already has been upgraded and it's safe to continue. So I ignored the error and didn't re-run rootupgrade.sh, continued through UI and completed the process successfully.