Friday, December 22, 2006

ssoreg.sh throws exception: IAS Property not found

When running ssoreg.sh you may run into this error:

Repository Access API throws exception :oracle.ias.repository.schema.SchemaException: iAS Property could not be found Property Name OIDhost in ...

This is most likely because you used the actual ORACLE_HOME when running it instead of using $ORACLE_HOME. For some reason you MUST use $ORACLE_HOME.

Wednesday, December 06, 2006

Suse Linux and ORA-27125

It's basically Unix, right, so you almost never reboot it. That's great, except when you can't remember what you did the last time to fix something. Well, I rebooted today. I didn't really need to, but something made me do it. Then I tried to bring up my database. I saw the following:

SQL> startup;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORA-27125: unable to create shared memory segment
Linux-x86_64 Error: 1: Operation not permitted

What to do about it!? It turns out I had two distinct problems. That can be tough, because it looks like it could be one problem. Somehow the database OEM had introduced an old init.ora parameter, that's the ora-32004. If the db won't start, you don't get the output in the alert log telling you which parameter is the problem. First I solved the ora-27125, then I found that I had plsql_compiler_flags specified and reset it.

So, what caused the ora-27125? Would you believe... metalink had the answer, note: 293988.1

I'm going to paste the note here just so it is handy.

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2 to 10.2
Linux Itanium
SUSE \ UnitedLinux x86-64
Linux x86-64
Linux x86
This Problem can be seen on the SUSE SLES9 linux distribution which runs on kernel '2.6.5-7.191'

Symptoms

While starting the database instance following error is encountered.

SQL> startup
ORA-27125: unable to create shared memory segment
Linux Error: 1: Operation not permitted

Cause

This is caused by '2.6.5-7.nnn' kernels requiring MLOCK privilege for hugetlb memory.

Solution



To enable use of "hugetlb memory", or to allow the init.ora parameter "lock_sga=true" to be used, do:
--- Login as root
--- # echo 1 > /proc/sys/vm/disable_cap_mlock
If you need to make this change persistent
--- Login as root
--- Edit /etc/sysctl.conf
Add following lines
# Oracle requires MLOCK priviledge for hugetlb memory.
vm.disable_cap_mlock=1
--- Execute the command
#/etc/init.d/boot.sysctl start
Setting current sysctl status from /etc/sysctl.conf
vm.disable_cap_mlock = 1
--- Dont forget to enable the service.
chkconfig boot.sysctl on

Wednesday, November 22, 2006

Export Portal schema and move to another db

OK, so I admit this is very esoteric, but if you ever need to move a Portal schema by using export/import (e.g., you are changing database operating system), you may run into this problem... (I'll try to put up a more comprehensive "how to" later, but thought this little issue deserved its own spot.) In the meantime, we have done a bunch of these moves. If you need some help, click on the C2 Consulting link to the right.

The solution is right in the documentation
http://download-east.oracle.com/docs/cd/B14099_18/core.1012/b13995/prodtest.htm#BHAHIHBH
but if you miss it, the errors are not too helpful.

You get a servlet error

Servlet Error

An unexpected servlet error was encountered.

Please check the log file for more details.

In the log file
$ORACLE_HOME/j2ee/OC4J_Portal/application-deployments/portal/OC4J_Portal_default_island_1
you will see the following:

06/11/22 09:43:41 portal: [module=PortalServlet, ecid=92635952220,1] ERROR: oracle.webdb.repository.service.RepositoryServiceException: Unable to obtain Portal Repository version.

Search all through metalink and on the web and it will likely tell you that your PORTAL schema password is wrong. Well, that might be true, but once you find it isn't wrong, log in as either PORTAL, sys or system and run the following command:

ALTER USER PORTAL_PUBLIC GRANT CONNECT THROUGH PORTAL;

Bounce everything. I'm interested in finding out if this helps anyone. If it does, leave a comment.

Thanks,

Anton

Wednesday, November 08, 2006

High Availability Identity Management Install (10gAS)

The installation guides tell you that you must have your load balancer resolve via dns or in the host file. The guides don't mention that if you have a metadata repository on a separate box, then the load balancer must resolve from that box as well. If it doesn't, you will get the following error in InstallActions.log:

SQL> Connected.

SQL> Creating OID entries for SSO
Error code : 1

Error message: User-Defined Exception

LDAP error : ORA-31203: DBMS_LDAP: PL/SQL - Init Failed.

ERROR: deleting application entry

Error code: 1

Error message: User-Defined Exception

ERROR: creating SSO users and groups in OID

PL/SQL procedure successfully completed.

Tuesday, November 07, 2006

Oracle REPCA on a RAC Database

If you run the Oracle Metadata Repository Creation Assistant (RepCA) into a RAC Database you may find that the PORTAL_DEMO schema does not get created. You get an error saying that some things failed at the same moment that you get an installation completed successfully message. The fix is fairly easy, just remove the MR and re-install, but only indicate a single RAC node in the installer. Section 2.3.2, Step 4 of the following document mentions this:
http://download-west.oracle.com/docs/cd/B14099_19/sol.1012/repca.1012/repca/load.htm#sthref158

Tuesday, October 31, 2006

Oracle Portal Virtual Host and SSL

I recently ran into an issue that Oracle Portal was presenting the wrong base href in some pages. This was a virtual hosted environment, that is, several machine names point to the same installation of portal. We had followed the virtual hosting documentation perfectly, and in fact, we had it running for quite some time without any issue. The issue cropped up when we added an additional virtual host.
This additional host was the first one that was not SSL. It took a little while to figure it out, but the solution was to make sure that all of the https (ssl) virtual hosts appear in the httpd.conf file prior to the first http virtual host. I'm still not sure why this would make a difference, but it solved the problem.

Monday, October 30, 2006

Setting Up SSL for Application Express

If you are reading this, you might be interested in this post as well and probably this one.

I'll try to give sufficient info to do this...
All steps are done with ORACLE_HOME set to the middle tier home (Apache, Companion CD home)

1. You need to use Oracle Wallet Manager (OWM) to create a wallet and get it set up.
-- a. set up ORACLE_HOME, etc.
-- b. go to $ORACLE_HOME/bin
-- c. run Oracle Wallet Manager ./owm
-- d. create a new wallet and save in a new directory (e.g. /home/oracle/myWallet/)
-- e. create a certificate request
-- -- i. the common name is the name of the url, for example www.concept2completion.net
-- -- ii. Organizational Unit and Organization are just text associated with your company
-- -- iii. You should spell out the state
-- f. export the request to a text file
-- g. You will need to go to a certificate authority (CA) to get a certificate and paste in the contents of the text file created in (f). I have had problems with both goDaddy and Verisign. I have had good luck with entrust.com and thawte.com
-- h. If you get a trial certificate you will need to get the trial Root Certificate (aka trusted certificate) from the CA. Save it as a text file. Install that into OWM as a trusted cert.
-- i. Now install your cert (from g) in OWM and save.
2. Configure your ssl.conf file, located in $ORACLE_HOME/Apache/Apache/conf
-- a. You can use the default listen and port settings (probably 4443 or 4447) or you can change to 443. If you change to 443, you need to change all occurences. Also, the apachectl file will need to be owned by root (located in $ORACLE_HOME/Apache/Apache/bin/). Same requirement as running on Port 80.
---- 1. Setting up Apache to run on ports below 1024
1 Shutdown OHS
2 Become root
3 cd $ORACLE_HOME/Apache/Apache/bin
4 chown root .apachectl
5 chmod 6750 .apachectl
6 cd $ORACLE_HOME/Apache/Apache/logs
7 rm -f *
-- b. Besides the port change, you need to change the location of your wallet and give your wallet password. You can encrypt the password, but I'm not covering that here and now. (See Oracle Support Note 184677.1)
SSLWallet file:/home/oracle/myWallet/
SSLWalletPassword mySuperPW1
-- c. Save ssl.conf
3. Here is the trick. You need to configure OPMN to run in SSL mode. Edit the file opmn.xml (located in $ORACLE_HOME/opmn/conf/)
-- a. under start mode, look for ssl-disabled
-- -- change to
-- -- ssl-enabled
-- b. Save opmn.xml
4. go to $ORACLE_HOME/opmn/bin and restart
-- a. opmnctl stopall
-- b. opmnctl startall

OK, now you are running in SSL. You might want to run everything in SSL, or just some things. To do this, you can set an Apache Rewrite Rule. Here is an example that will rewrite everything that is in pls/apex to https (assumes you are running on port 443). Edit your httpd.conf file, add the lines below at the end of the file, opmnctl stopall, opmnctl startall:
RewriteEngine On
RewriteCond %{SERVER_PORT} !^443$
RewriteRule ^/pls/apex/(.*)$ https://concept2completion.net/pls/apex/$1 [L,R]

Hope this captures all of the steps. Let me know if this helps.

If you are reading this, you might be interested in this post as well and probably this one.

Thursday, October 19, 2006

Oracle Instant Portal - Public

I recently began playing with OIP a bit more. It has a fun UI, so when my son asked to make a website for his school project I suggested that my son use it for his project. I figured it would force me to learn the in-and-outs a bit better. Well, I was right.

After getting some content into the site, my son asked to make it available so anyone could see it. I had never even tried it. So, I just tried it. I added PUBLIC to the oip_available_users group in Oracle Internet Directory (OID). Then I added PUBLIC as a view user to the pages I wanted to be public. Seems to make sense, right? It didn't work. After a little poking around I found out that OIP won't allow a page to be public. So, what's the next thing to do? Remove the public user, right? Wrong!

If you use the OIP user management feature to remove a user, it DELETES the user from OID. That, of course, is exactly what I did. I deleted the public user from OID, thereby causing all of my public portal pages to break. I could barely log in.

I did a little research to find a solution. The two solutions I could find were reinstall and try reseeding all of the initial users, then clean up. I did not like either choice. I just created a new user in OID called PUBLIC. Everything has worked since then, but I may yet have issues.

Update: Since this original post I have found that a lot of permissions get lost. You will need to run the script that recreates all of the public permissions on form building, etc. If you run in to this, run all the set_user_acl procedures in the /portal30/admin/plsql/wwv/wwvsecd.sql script, which reference the PUBLIC user (USER_PUBLIC).

Wednesday, September 20, 2006

Oracle Application Express File Download

I recently had a client complain that when clicking on a file from an ApEx application it always asked them to save or open the file rather than just rendering it in the browser via a plug-in (e.g. rendering a pdf in Acrobat Reader).

I almost always want that, so it never seemed to be an issue to me, but I could see the point. I had utilized the standard ApEx routines for downloading files from a blob in my custom table. This has the following code:

htp.p('Content-length: ' || v_length);
-- the filename will be used by the browser if the users does a save as
htp.p('Content-Disposition: attachment; filename="' || v_file_name || '"');
-- close the headers
owa_util.http_header_close;
-- download the BLOB
wpg_docload.download_file( Lob_loc );


This code is fine and it does just what it will do, but the blue text causes (at least some, including IE) browsers to force a download, and never open within the browser.

It was easy to comment out that line, but that left another issue. Now when the user does a save (right clicks and does "Save as" or saves from the plugin) it doesn't have the correct filename. It will have as a filename whatever comes after the last slash (/) in the url. That probably looks something like my_package.download?p_id=12442. Users really didn't like that.

So . . . What to do? We changed the download links to look like this instead:
/apexfiledownload//
like this
/apexfiledownload/12442/myFile.pdf

Then we used an Apache rewrite rule to route that to our download routine (my_package.download?p_id=12442). Here is the rule:

RewriteRule ^/apexfiledownload/([^/]+)/?(.*) /htmldb/my_package.download?p_id=$1 [PT]

That works and everyone is happy now.

I have an idea that I could also have implemented this by simply changing the offending line from attachment to inline:
htp.p('Content-Disposition: inline; filename="' || v_file_name || '"');
Testing that will be left to another day.

Thursday, September 14, 2006

Always something to do during a con-call


Just to show that there is more to do than Oracle . . . I was recently on a long con-call. And you can always find something to do during a long con-call. I had to switch phones to make the call because a couple of the keys on my cordless Motorola phone (md400 series) had stopped working. It happened over time, becoming more and more annoying until a few just don't work at all. Of course, the "1" doesn't work--and I live in the Boston area, where you need to dial a 1 to call next door. So, I grabbed another phone, dialed the call-in number, and got to work.

The hardest part, of course, is to just get the darn thing apart. At least nothing was glued together. It just required some prying and determination (and removing a few screws, 2 obvious, 2 hidden under a panel).

Then off to figure out the problem. The contacts were all good, and the keys all looked the same, but a few just wouldn't cause the contacts to connect.

So, I added a little piece of aluminum foil over each key and reassembled (you can see the first five in the picture). Now it works great, maybe a little too easy to get a number, but I suspect that will change over time. Now I don't need to buy a new phone, and it didn't take me any time because I was on a con-call.

Monday, August 07, 2006

Oracle 10gAS install in a custom database

If you create a custom database (10.2.0.2) with the dbca, it will automatically install Oracle Spatial. If you use the command line or sqlplus, it doesn't. Spatial is required prior to running Repca. But, the pre-check doesn't catch that it isn't there. And the select statement given in the 10gAS documentation returns true when it isn't really there. So, be sure to put in prior to installing, otherwise you'll have to remove the metadata repository and start over. No fun.

Friday, July 28, 2006

Application Express (html db) Breadcrumbs

Here is a little bug (in 2.0 anyway).

Create a breadcrumb, b1. Create a child, b1_1. Delete the page associated with b1. The child still exists, but there is no way to edit it or delete it.

Solution:
select to_char(id), * from flows_02000.wwv_menu_options
where flow_id =

find the option that you want to edit. Copy the id.

Edit a different menu option, you'll see a link like the one below:
htmldb/f?p=4000:290:::NO:290:F4000_P290_ID:6009524130400412

change the final number to the id you copied.

Voila! Now you can edit your menu option and give it a new parent.

Hope this helps someone.

Oracle Advanced Queue pl/sql Subscriptions

Here is a quick pointer on Oracle Advanced Queues and pl/sql registrations. In the documenation it tells you that the queue name must be less than or equal to 30 characters for pl/sql subscriptions to work. It doesn't mention that schema.queue_name must be <= 30. But that is the case--at least in 9i. Once you get to 10g (or 10.2.0.1 anyway) it is even more difficult. Same rule applies, but what they don't tell you is that in 10g it is "schema"."queue_name" must be <= 30. So really, the length of the queue name plus the length of the schema must be <= 25. This can be tricky if you do an upgrade and suddenly your plsql queue stops working.

Friday, May 19, 2006

Installing Oracle 10g AS w/ Forms on SuSE 9

Wow, this has been a challenge. I'll try to put up a few useful URLs and then add comments later. I wish I had taken closer notes along the way.

Be sure to install Motif (32 Bit). And get this... the SuSE ES9 will likely have a newer version of Motif than Forms Server will accept, so you will need to be sure to install the correct version. If you just want to get it to install, but don't want to compile forms, you can get it to work with a link, but then frmcmp.sh won't work:
ln -s libXm.so.3 libXm.so.2
So, you should just do the download (I think this is the link, though I'm not even sure anymore):
http://support.novell.com/techcenter/psdb/388a4c0511756c9a8aff38e2c5170509.html

If you forget to do this, you will get an error like this:
01/05/21 09:42:44 Internal server error
java.lang.UnsatisfiedLinkError: /lib/librwu.so: libXm.so
.2: cannot open shared object file: No such file or directory


Set up Enviroment: Run OraRun by Novell
http://ftp.novell.com/partners/oracle/sles-9/

Install GCC 32 Bit, DB1 32 Bit

Error:
checking for hardnofiles = 65536 found hardnofiles = 1024 Failed
checking for softnofiles = 4096; softnofiles = 1024
Solution: Set Up Security Parameters (I should boil this down, but the URL below gets you there, ):
http://asktom.oracle.com/pls/ask/f?p=4950:8:::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:454420165038

Error in WebCache Configuration Assistant: error while loading shared libraries: libdb
See http://nextre.it/oracledocs/gridcontrolonsles9.html (basically install db1 32 bit)

Also check this URL:
http://ivan.kartik.sk/oracle/install_ids10_suse.html

Fast Refreshing a Materialized View

Just a quick post about fast refreshing an MV.

Example:
schema1 in db1 has a database link to schema2 in db2

schema2 has select on table t1 in schema3 in db2

You want to create a fast refreshable MV in schema1 on table t1.

You must create a materialized view log on t1 and grant select on the log to schema2:

SQL> connect schema3@db2
Enter password: ********
Connected.
SQL> create materialized view log on t1;

Materialized view log created.

SQL> grant select on mlog$_t1 to schema2;

Grant succeeded.

If you neglect to do the grant, you will get the following error:

ORA-12008: error in materialized view refresh path
ORA-01031: insufficient privileges
ORA-02063: preceding line from LINK_TO_ESUBMIT
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 2

Seems like there could be a better error message, but that's what you get.



Monday, April 24, 2006

Oracle 10gAS SSO UID attribute

Here is a quick tidbit on Oracle SSO for v10.1.2.0.2. There is a new user attribute, uid, that is required for v10.1.2.0.2. I'm not sure what version introduced it, but it was not required in 9iAS R2. You will get a 500 Internal Server Error if you attempt an SSO login with a OID user account that does not have a UID attribute.

I'll try to get a full upgrade plan from 9i R2 Portal to 10gAS v10.1.2.0.2 shortly.

Friday, March 24, 2006

Time to Start

Wow. I can't believe it has taken me this long to join the blogging fray, but here I am. Of course, I've had time to create the blog site, but that's all the time I have at the moment. More soon . . .