Monthly Archives: May 2010

The difference between a direct grant and a role in PL/SQL

A little post about “beginning Oracle development”. Some people I work with sometimes struggle to grasp the permission-granted-via-role PL/SQL problem. But it’s so simple! One of the testers scratched his head many times asking himself why the procedure doesn’t compile. ORA-942 in the procedure, but in SQL Plus he can select from it. Let’s create the test case. As user martin, owner of table “charlie” we grant select on the table via role to alice.

mbach@pc:~> sqlplus martin@dev

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 27 15:10:59 2010

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

Enter password: *****

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user alice identified by xxxx;

User created.

SQL> grant connect to alice;

Grant succeeded.

This is the table Alice requires access to.

SQL> create table charlie (id number);

Table created.

Now let’s populate the table so we can test.

SQL> begin for i in 1..100 loop insert into charlie values (i); end loop; end;
 2  /

PL/SQL procedure successfully completed.

This is the role we initially create to grant Alice access to table martin.charlie. NB I initially complicated the scenario with “bob” but decided that was overkill-hence the table is called charlie. First we create the role, then we grant the select privilege on martin.charlie to it and finally allow Alice to make use of it

SQL> create role delta;

Role created.

SQL> grant select on martin.charlie to delta;

Grant succeeded.

SQL> grant delta to alice;

Grant succeeded.

SQL> grant resource to alice;

Grant succeeded.

With this initial work done, let’s see what Alice can access.

mbach@pc:~> sqlplus alice/xxxx@dev

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 27 15:12:53 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho user
USER is "ALICE"
SQL> select * from martin.charlie
 2  /
select * from martin.charlie
 *
ERROR at line 1:
ORA-00942: table or view does not exist

Oops- forgot to enable the role.

SQL> set role all;

Role set.
With the role 
SQL> select * from martin.charlie
 2  /

 ID
----------
 1
 2
 3
 4
...
 98
 99

 ID
----------
 100

100 rows selected.

This is better. Now let’s think of a function f which returns the ID from table charlie for a given input parameter. Yes, the example is silly and nothing you’d ever see, but it helps to get the idea.

SQL> get f
 1  create or replace function alice.f(pi_nbr number) return number
 2  as
 3    v_id number;
 4  begin
 5    select id into v_id from martin.charlie
 6    where id = pi_nbr;
 7  exception
 8    when no_data_found then
 9     raise_application_error(-20001, 'no data found for id ' || pi_nbr);
 10* end;

Let's try to run the script

SQL> @f

Warning: Function created with compilation errors.

What’s the problem?

SQL> show err
Errors for FUNCTION F:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3     PL/SQL: SQL Statement ignored
4/38     PL/SQL: ORA-00942: table or view does not exist

Remember that the select in sqlplus actually worked! This is what gets many people’s head in. But we know better-now grant select on the table directly as the owner of the table (martin)

SQL> grant select on chalie to alice;

As alice, try again:

SQL> @f

Function created.

Lesson learned:

  • When you grant access to a table via a role, you can select it from clients such as sqlplus
  • If you want to use the object you are granted access to in PL/SQL, then the grant via the role isn’t enough
  • Access needs to be granted _directly_ to the user, not through a role
Advertisements

Moving OCR and voting disks to block devices in CRS 10.2

Anyone to suggest a shorter title for this: you are welcome to comment! Initially I wanted to have this in the first of today’s posts, but got side tracked. So here’s the next step in the CRS 10.2.0.5 installation: moving off the deprecated raw devices to block devices. This is something you can do with OUI in 11.1, but in 10.2 it either requires raw devices or a clustered file system. Here’s the step by step protocol of my session.

The Setup

Before talking about what I am doing a little background information is in order. This example uses single pathing, the difference to multipathing is marginal-just remember to use the logical device names instead of the physical ones. In device mapper multipathing this might be similar to /dev/mapper/ocrvotep{5,6,7,8,9} in case you map your WWID for the OCR and voting disks to a logical name called “ocrvote”. If presenting only 1 device to the server to store all copies of OCR and voting disk make sure to have it well protected on the array! With this said, here is my setup. First for the voting disk-it went to /dev/raw/raw2 during the installation:

[root@crs102node1 bin]# ./crsctl query css votedisk
 0.     0    /dev/raw/raw2

located 1 votedisk(s).

My OCR doesn’t have a mirror and it on /dev/raw/raw1

[root@crs102node1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
 Version                  :          2
 Total space (kbytes)     :    1003728
 Used space (kbytes)      :       2008
 Available space (kbytes) :    1001720
 ID                       : 1189351742
 Device/File Name         : /dev/raw/raw1
 Device/File integrity check succeeded

 Device/File not configured

 Cluster registry integrity check succeeded

Now let’s change directory to $ORA_CRS_HOME/bin before the action starts.

Continue reading

Patching CRS to 10.2.0.5

A small blog post about patching CRS 10.2.0.1 to 10.2.0.5 instead of applying the latest PSU on top of 10.2.0.4.

So it finally happened! Oracle has released the terminal patchset for 10g Release 2. Long overdue! So the task at hand today is to upgrade CRS to 10.2.0.5.

Patch

Download patch 8202632 to a convenient location and unzip it. The patch is quite large, again, about 1.2G in size for Linux 64bit RHEL5.5, my platform.

[oracle@crs102node1 10205]$ ls -lh p8202632_10205_Linux-x86-64.zip
-rw-r--r-- 1 1000 users 1.2G May 25 16:48 p8202632_10205_Linux-x86-64.zip

The first thing that caught my attention was the built-in support for SLES 11 and RHEL 6. This is interesting as RHEL 6 is still beta, where SLES 11 is out for production use. Means no more hacking the oraparam.ini file then, good (if you are on one of these platforms).

[oracle@crs102node1 10205]$ ./Disk1/runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5,
redhat-6, UnitedLinux-1.0, asianux-1, asianux-2, asianux-3, enterprise-4, enterprise-5 or SuSE-11
 Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-05-26_10-14-13AM.
Please wait ...[oracle@crs102node1 10205]$ Oracle Universal Installer, Version 10.2.0.5.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

Continue reading

Thousands of core dumps in $OMS_HOME

I have been asked to debug a problem with another office’s Grid Control 10.2.0.5 setup. In a nutshell users complained they could not log in, and those who were previously connected were kicked out. Hmm, so much for a briefing.

When troubleshooting Grid Control it is important to remember that Oracle’s monitoring product is made up of 3 parts:

  1. The User Interface, called Oracle Management Server
  2. The repository database
  3. An arbitrary large number of agents deployed to the monitoring targets

With the briefing in mind, I first checked the OMS for problems and sure enough, the OMS partition on was 100% full on the operating system level. Must have been a runaway logfile I thought and checked the $OMS_HOME. To my surprise there ware thousands of core.pid files. Things got even stranger… The oldest core files dated from Sunday, at which point lots of network changes took place. Coincidence? The Apache process’s error_log showed lots of handshake problems between OMS and targets which seemed to undermine the theory. Continue reading

tnsping segmentation fault with 10.2 client

The tale of a very interesting problem with a segmentation fault on RHEL 5.3 64bit when invoking tnsping. I initially thought the box the client was installed on (a development virtual machine) was seriously ill but it turned out to be something else altogether.

Here is the initial problem. One of the developers contacted me saying that he couldn’t connect to one of the databases. Sure enough, sqlplus wouldn’t connect:

[oracle@dev-vm-001 tns]$ sqlplus a/b@devone

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 20 15:03:47 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name: 

I thought that this was simple enough, the probably haven’t defined the database in the tnsnames.ora file. Checking the file revealed that I was wrong!

[oracle@dev-vm-001 tns]$ cat /u01/app/oracle/product/10.2.0/client_1/network/admin/tnsnames.ora 
DEVONE =
 (DESCRIPTION =
   (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dev1db)(PORT = 1521)))
   (CONNECT_DATA =
    (SERVICE_NAME = DEVONE)))

So then maybe there is no connectivity to dev1db? tnsping would tell me.

[oracle@dev-vm-001 tns]$ tnsping devone

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 20-MAY-2010 14:59:10

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Segmentation fault

Continue reading

PRKP-1061, CRS-2518 and CRS-0219 with 10.2 RDBMS and 11.2 CRS

Couldn’t find a better title for this, but hopefully makes it easier to locate the problem when using your favourite search engine. I tried to manage my 10.2.0.4.1 3 node RAC database “QA” for which I upgraded Grid Infrastructure to 11.2.0.1. The problem arose when I tried to disable the 10g database, and then when I wanted to start it. Here’s the output of my feeble attempt:

[oracle@rac11gr2node1 ~]$ srvctl disable database -d QA
PRKP-1061 : Can not find a service member to start for service qa.
CRS-2518: Invalid directory path ‘/u01/app/grid/product/10.2.0/bin/racgwrap’
CRS-0219: Could not update resource ‘ora.qa.db’.

Ooops. That looked bad, although some other commands worked, like srvctl status database etc. It turned out that the problem is with the database’s resource profile:

export ORACLE_HOME=/u01/app/grid/product/11.2.0/grid  
# note the 11.2 Grid Infrastructure home!

$ORACLE_HOME/bin/crsctl stat res ora.qa.db -p | less

NAME=ora.qa.db
TYPE=application
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--,
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=/u01/crs/oracle/product/crs/bin/racgwrap
ACTIVE_PLACEMENT=0

This strange behaviour is caused by bug  bug 9257105. Even though the upgrade from CRS 10.2 to 11.2 finishes successfully, the OCR configuration for any pre-11.2 database still points to the pre-11.2 CRS home (remember that the 11.2 upgrade is out-of-place!). The bug is fixed with 11.2.0.1.2 and 11.2.0.2.0 (none of which are out yet).

A workaround is availble: as the pre-11.2 database owner, execute the following command for each pre-11.2 database from the Grid Infrastructure home:

[grid@rac11gr2node1:~ ]$ crsctl modify res ora.<dbname>.db -attr "ACTION_SCRIPT=$GRID_HOME/bin/racgwrap"

You can use the crsctl stat res ora.<dbname>.db -p command to check the action script now points to the correct ORACLE_HOME. Done, enjoy!

Compiling PHP 5.3.2 for Oracle 11.2 and Apache 2.2.3

Today I wanted to experiment with the database resident connection pool. It seems to have been specifically written with PHP in mind so I thought this might be a good idea to get going with this new technology. I didn’t want to start completely from scratch so I searched the docs for PHP and Oracle related documentation. It turns out that you can be an Oracle aware PHP developer in 2 days so I took this as a starting point.

Unfortanately Oracle use Zend Server for PHP. Sure a great product, but I didn’t want to give my details to yet another company to spam me with information I don’t want/need. So I decided to use the source-how difficult can configure && make && make install be?. Continue reading