Thursday, June 07, 2007

A test bed, or how to install Linux fast!

How do you prepare for Oracle testing? Or anything else, for that matter, based on Linux? Here's how:

Preparation:
  1. Hardware. Of course, you should have the hardware. I have.
  2. Download the CentOS 4.4 Server CD; it can be found here.
  3. Burn the .iso on a CD - any burning program can do that, many recognize the downloaded file as an image.
The next steps take about twenty minutes, even less on a modern CPU.
  1. Boot the designated system from newly created CD, some systems may require fiddling the boot options in the BIOS to allow for CD-drives to be searched for bootable products. Consult your motherboard manual if your system will not boot from the CD.
    Once it booted, you will see a startup screen, type:
  2. linux text
  3. Skip the CD test
  4. Choose English for a language, and a US Keyboard (or your own favourite, of course!)
  5. Choose to Auto Partition the disks, and remove any partitions (if applicable).
  6. OK the default partitioning offered (this is not about optimal Linux, but Oracle installations)
  7. Use Grub as bootloader, no password, no special config options
  8. Allow the Master Boot Record to boot from.
  9. Use a fixed IP-address: I used 192.168.1.210. Servers should always have fixed IP-addresses.
  10. As for Default gateway and DNS servers: use your own network settings. You could type:
    ipconfig/all
    on a windows PC, connected to the internet to find out your settings (assuming you have both a server, which now is cleaned out, and a workstation).
  11. Hostname: anything.home.local (no, choose your own, but do not use a domain, that might exist now or in the future - the domain home.local will never exist)
  12. Choose to use No firewall (yes, sure about that). Not that you cannot get Oracle installations to work with a firewall, but not having to bother with ports and firewall configurations while installing Oracle stuff makes the install process so much easier. Besides - you do have a central firewall in your network, don't you? If you don't: disconnect the RJ45 Ethernet connector NOW!
  13. SE Linux level: select to warn
  14. No additional languages (or: what you want)
  15. Indicate correct timezone
  16. Package install: select Customize.Leave the defaults for the following parts as is, uless otherwise mentioned. Deselect any other!
  • X-Windows
  • Gnome
  • Server Config Tools: add Samba (unless you want FTP - then add that)
  • Windows File Server (unless you like FTP)
  • Admin tools
Wait for 8 minutes or so, then the server will reboot. You have now completed your CentOS 4.4 install - congratulations!

Next step - upgrade.
The time, next step will take heavily depends on the type of network connection your have; it took me about half an hour to complete, with a 4Mbps downstream connection.
See if you can ssh into your server - I like TeraTerm. There seem to be new versions out, just search the net. You may have to use the ip-address you used (I used 192.168.1.210, remember?) until changes are made to your local hosts file.
Log on as root, and issue:

[root@oidhost ~]# uname -a
Linux oidhost.home.local 2.6.9-42.EL #1 Sat Aug 12 09:17:58 CDT 2006 i686 athlon i386 GNU/Linux
[root@oidhost ~]# yum upgrade

This will take some time, but when asked, answer Y - it will replace 155 of the installed packages (and upgrade to CentOS 4.5 - that distro does not have a Server Only iso, though...)
When you get the warning:

rpmts_HdrFromFdno: V3 DSA signature: NOKEY, key ID 443e1821
Public key for net-tools-1.60-37.EL4.9.i386.rpm is not installed
Retrieving GPG key from http://mirror.centos.org/centos/RPM-GPG-KEY-centos4
Importing GPG key 0x443E1821 "CentOS-4 key "
it is OK: type 'Y' and continue.
When yum finally has completed:

[root@oidhost ~]# reboot -n

After the boot, we'll have a new kernel:

[root@oidhost ~]# uname -a
Linux oidhost.home.local 2.6.9-55.EL #1 Wed May 2 13:52:16 EDT 2007 i686 athlon i386 GNU/Linux


That's it! You now run CentOS 4.5 - congratulations!

Additions:
1. maintenance

There's a neat, web based administrative package, I'm rather fond of: webmin. Installing is easy as 1-2-3:
Visit the webmin site, and find the url of the download mirror nearest to you - I actually needed to start the download, in order to get the url copied. Using the url, that is under "Download" will do redirects, and the rpm installer does not understand them.
I use the Amsterdam mirror (By the way: that is one line you need to feed rpm with):

[root@oidhost ~]# rpm -iv http://surfnet.dl.sourceforge.net/sourceforge/webadmin/webmin-1.340-1.noarch.rpm
Retrieving http://surfnet.dl.sourceforge.net/sourceforge/webadmin/webmin-1.340-1.noarch.rpm
warning: /var/tmp/rpm-xfer.cxUGCI: V3 DSA signature: NOKEY, key ID 11f63c51
Preparing packages for installation...
Operating system is CentOS Linux
webmin-1.340-1
Webmin install complete. You can now login to http://oidhost.home.local:10000/
as root with your root password.

Yeah - right, not until I changed my Windows hosts file... which, by the way, has the following contents:
127.0.0.1       localhost
192.168.1.210 oidhost.home.local oidhost
192.168.1.220 idmhost.home.local idmhost
192.168.1.104 infradbhost.home.local infradbhost
The first entry is default, and best left alone (your loopback connector needs this!)
The second and third are my application server layers to be, and the last entry is my workstation, where the databases reside. Runs XP, and has 3GB memory available.

OK, after the alteration you can actually log in to webmin (and TeraTerm) using the hostname, not the IP-address.
Locate the Bootup and Shutdown section (under System), and stop and disable:
sendmail, cups, kudzu, pcmcia, xinetd.
Of course, you could have entered:
chkconfig sendmail off
chkconfig cups off
chkconfig kudzu off
chkconfig pcmcia off
chkconfig xinetd off
from your SSH session... followed by
service sendmail stop
service cups stop
service xinetd stop

2. Allowing copies from your workstation
Now, I installed Samba, you may have chosen FTP Server. If so, configure it. I'l do the Samba Windows thing, from WebMin:
  1. select Servers, Samba Windows file sharing.
  2. open the Windows Networking options, and alter:
  3. Workgroup - to whatever you use, the Windows default is WORKGROUP. You may leave it at WORKGROUP.
  4. You may alter the Server Description and Server name, leaving them default will cause misunderstanding with the second server in use...
    I used the hostname, oidhost and for the second machine: idmhost.
  5. Chance security to Share
  6. SAVE.
Under the shares, Create a New one, and call it install. Directory: /install.
Now click on the 'Start Samba Server' button, and browse your network - you should be able to see the install share from your windows client.
You will be asked for a password (and a user), whcih we still need to setup:
[root@oidhost ~]# useradd frank
Now - in WebMin, click on the 'Convert Unix users to Samba users', leave everything default,
and verify that frank is added after clicking the 'Convert' button. Then setup the
password that will be used for the share:
[root@oidhost ~]# smbpasswd frank
New SMB password:
Retype new SMB password:

You should now be able to map \\oidhost\install to a drive, using frank as user (select 'Connect as a different user'), and using the supplied password.
You still can't do a thing, as security prohibits.
For the sake of speed (again: we're in it for Oracle, not Linux):
chown frank /install
You now own the bloody thing!
Copy over any .cpio files and patches.

You are now ready to install Oracle - congratulations!
Addition: Not quite... some extra packages are required, better install these now:
[root@oidhost ~]# yum install xorg-x11-deprecated-libs.i386
[root@oidhost ~]# yum install gcc gcc-c++ openmotif21 sysstat gnome-libs libstdc++-devel compat-libstdc++-296 compat-db-4
After installing these, I noticed I had to set the kernel params; here's how:
edit /etc/security/limits.conf, and add this line:
*       hard    nofile  65536
Edit /etc/sysctl.conf, and add:
# Oracle specifics:
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 256 32000 100 142
fs.file-max = 131072
net.ipv4.ip_local_port_range = 10000 65000
kernel.msgmni = 2878
kernel.msgmax = 8192
kernel.msgmnb = 65535

A reboot is required...

Wednesday, April 04, 2007

Oracle Apex goes commercial!

Yesterday, Oracle launched version 3 of Application Express, also known as ApEx. I wasn't sure about the new frills it would contain - PDF from within wasn't something I was missing. After all, it has been possible to produce PDF since version 1.5.

But things sure have been improved - PDF printing is now so much easier!
Edit: I fell for it. I'll admit; I fell for the marketing machine.
PDF printing actually is the same as it ever was: it uses the Open Source Java printing engine, Apache FOP. The 'easy' way would be to buy the BI suite from Oracle. So I changed the title into Apex goes commercial. Quite disappointed, or rather, mad with myself, so not reading better.

Oracle 11g.

And ApEx is Oracle 11 compliant - wait till that hits the streets. So much is new for Oracle 11 (still the Grid verion: 11g): it has an embedded pl/sql gateway, which will be used as default.

Installations with an application server and using mod_plsql will still be possible; they just will not be the default. Looks like ApEx is becoming more of a database centric tool - it does not need the iAS anymore.

Not that it actually did - ever noticed how ports 8080 and 2100 are opened for http and ftp protocols respectively after a default install? XDB is accountable for that.

Features

If you have never taken a look at ApEx, do so now. My favourites are the Webservices (now JDeveloper and BPEL are supported), password and account control enhancements (expiry, grace time, forced changes upon first login). Some people will probably like the flash charts.

I will install 3.0 in a develop environment tomorrow, and see if I can find the time to mess around with it.
Edit: Of course not... The day started off with a webservice, not performing (due to a wrongly configured log4j), continued with jobs that needed killing or scheduling, software that had to be tested and rolled out, production crash actions involving InterConnect, and before you know it, you're in the car back home, trying to aviod the traffic jam, caused by accidents, and closed highways...

Wednesday, December 27, 2006

Who said I was?

I already noted the nasty side effects, but instead of altering the once published blog, I decided to add this one. After all, the portal single-sign on (SSO) was just an example, and I want the originally registered applications still to be registered, including OIDDAS, orasso and both the infrastructure as well as the middle tier Oracle HTTP Servers (or OHS).

Inventory taking.
What is it I will have to move? Let's check with the Single Sign-On:orasso entry on the Infrastructure management console. Opening sub pages will get me to the Orasso SSO home page, in this exercise located at http://asinfra101202.mymachine.local:7777/pls/orasso/orasso.home.
This page provides a list of all current "partner applications" that rely on orasso to take care of the login procedure.
After logging in (with orcladmin) by clicking the modest link in the right upper hand corner, you will be shown another link: "Maintain the SSO server". Particulary interesting is the link about maintenance on partner applications!

History
Was did I already do? Actually, not a lot - the changing of the DAD is what was done, to allow the midtier to access /pls/orasso, but that was about it. Reread that thread first.
In the mean time, I do have access to the orasso home page, using the midtier link as well; however, the login link provides me with an error, and all page links refer to the infrastructure.

Moving the SSO server
Less of a move than fact - only the references will be changed to the middle tier instance, not the actual server.
First of all, I want to get rid of the references to the infrastucture on the orasso home page. I can do that by changing the home URL, success URL and logout URL in the Maintain Partner Applications screen of orasso. Unfortunately, I cannot change the login URL, nor the Single Sign-Off URL on this page, and those still point to the infrastructure.
So, the second step is to reregister orasso, by running this on the infrastructure: $ORACLE_HOME/sso/bin/ssoreg.sh -oracle_home_path $ORACLE_HOME -site_name "The SSO Server (OraSSO)" -config_mod_osso TRUE -mod_osso_url http://asmt101202.mymachine.local:7777/pls/orasso/orasso.home


After that, stop and start all opmn registered processes - both sides!

Friday, December 15, 2006

So, you're the security expert on Oracle iAS... or How To Relocate the SSO server

Well, uhm, actually I'm not, but how often do you find yourself in the position that the consulting firm you work for positions you as one?
Picture the following: a perfectly reasonable setup of Oracle's internet Application Server, with Oracle's LDAP implementation (Oracle Internet Directory, or OID) and Portal, Single Sign On (SSO), the Oracle Certificate Authority (OCA), Forms, Discoverer, the Oracle HTTP Server (OHS) and WebCache.
Basically, it looks like this:


What runs where?
Here's what the documentation has to say about the middle or Application Server tier:
The middle-tier is the part of an Oracle Application Server architecture that contains several components responsible for accepting requests from clients, validating the requests, and providing content, while using intelligent data caching for faster and reliable performance. For OracleAS Portal, the middle-tier handles all Web requests by forwarding them to the appropriate provider. This is also where Portal pages are assembled, and where the caching of Portal content is managed. The middle-tier also provides other functions for other Oracle Application Server components.

Components on the Middle tier are:
  • The Oracle HTTP Server (powered by Apache), or OHS
    OHS handles all requests for Portal, either via mod_plsql, or via the Parallel Page Engine (PPE)
  • Webcache
  • the Application Server containers for J2EE (OC4J)
Same for the Infrastructure tier:
By default, the infrastructure tier handles all authentication requests and hosts the Oracle Application Server Metadata Repository, which contains schemas and business logic used by application server components (including OracleAS Portal) and other pieces of the infrastructure.
Mind the "By default"...

Components, running in the infrastructure are:
  • Application Server Control
  • Oracle Internet Directory (OID)
  • Oracle Application Server Single Sign on (SSO)
  • Oracle Application Server Metadata Repository
    some middle tier products, like Portal, store their metadata in this repository.
Simple enough, isn't it? Sure, until the network guys came in, as they demanded -and rightly so- this:

So - what's the problem? The middle tier is moved to the DMZ, and firewalls are in place. What can be the problem? Well, Portal doesn't work anymore, as nobody can login...
How comes?

A closer look.
First of all, Portal "runs" on the middle tier; the OHS on the middle tier uses mod_plsql and/or the Parallel Page Engine. Do'nt be mislead by Portal pages themselves, as there is a rewrite engine. A portal page reference (before the rewrite!) may look like:
http://asmt101202.mymachine.local:7777/pls/portal
So, there must be a DAD defined on the middle tier, because of the /pls/ :
[oracle@asmt101202 bin]$ cat $ORACLE_HOME/Apache/modplsql/conf/dads.conf
<Location /pls/portal>
SetHandler pls_handler
Order allow,deny
Allow from All
AllowOverride None
PlsqlDatabaseUsername portal
PlsqlDatabasePassword @BW01fa60ocxhrL9E2N88jEyS0bXgwimw==
PlsqlDatabaseConnectString cn=asdb,cn=oraclecontext NetServiceNameFormat
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode SingleSignOn
PlsqlDocumentTablename portal.wwdoc_document
PlsqlDocumentPath docs
PlsqlDocumentProcedure portal.wwdoc_process.process_download
PlsqlDefaultPage portal.home
PlsqlPathAlias url
PlsqlPathAliasProcedure portal.wwpth_api_alias.process_download
</Location>

So, there you have it - on the middle tier, there's a Database Access Descriptor, pointing to the (standard infrastrutcure setup named) database "asdb". Whenever someone logs on to Portal, SSO is activated, the login link on Portal looks like
http://asmt101202.mymachine.local:7777/pls/portal/PORTAL.wwsec_app_priv.login?p_requested_url=http%3A%2F%2Fasmt101202.mymachine.lo....

Following that link, you will get redirected to
http://asinfra101202.mymachine.local:7777/sso/jsp/login.jsp?site2pstoretoken=v1.2~458......

and that is where the trouble starts: asinfra101202 is not in the DMZ, it's host name cannot be resolved by the DNS-server, in short: you'll get a "page not found" response.

What SSO does.
The Single Sign On server uses mod_osso, which is configured on the infrastructure server. The flow of events is as follows:
  1. The user requests a page; the Apache configuration is such that authentication is needed.
  2. The mod_osso module kicks in, and looks for a cookie with an encrypted token. If found, the token is passed back to the application.
    If not found, the request gets redirected to the SSO server.
  3. The SSO server requests it's own cookie - if not found, it produces a login page.
  4. The user credentials are checked and validated against OID
  5. The SSO server sets it's own cookie, and redirects the user identity in encrypted form back to mod_osso.
  6. mod_osso sets it's own cookie, with a token, which holds the user identity in encrypted form (see step 2)


Also, one needs to realize that Oracle Portal is considered a "partner application".
SSO used to be incorporated in Portal, hence the -incorrect- saying 'portal is a partner app of itself'.
Not since iAS 9.0.2, where SSO and Portal were disconnected (and mod_osso was born).




What to do?
Now that the problem is clear, so is the solution. Basically, we need to reconfigure mod_osso, so that:
  1. it can be accessed from the middle tier
  2. redirection takes place to the middle tier
The SSO server Home Page is located at [host][:port]/pls/orasso, where the host is your infrastructure server, e.g.
http://asinfra101202.mymachine.local:7777/pls/orasso/orasso.home
We will have to change that for starters, so that is will become
http://asmt101202.mymachine.local:7777/pls/orasso/orasso.home

As this is a /pls/ entry, too, it must be a DAD, just like Portal uses, so let's check the dads.conf file on the infrastructure machine:
[oracle@asinfra101202 ~]$ cat $ORACLE_HOME/Apache/modplsql/conf/dads.conf
#=========================================================== # mod_plsql DAD Configuration File
# ===========================================================
# 1. Please refer to dads.README for a description of this file
# ===========================================================
# Note: This file should typically be included in your plsql.conf file with
# the "include" directive.
# Hint: You can look at some sample DADs in the dads.README file
# ===========================================================
<Location /pls/orasso>
SetHandler pls_handler
Order deny,allow
Allow from All
AllowOverride None
PlsqlDatabaseUsername orasso
PlsqlDatabasePassword @BcyVNZv4W9rd5+bPajizTuISOz9AvJ3lLg==
PlsqlDatabaseConnectString cn=asdb,cn=oraclecontext NetServiceNameFormat
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode SingleSignOn
PlsqlSessionCookieName orasso
PlsqlDocumentTablename orasso.wwdoc_document
PlsqlDocumentPath docs
PlsqlDocumentProcedure orasso.wwdoc_process.process_download
PlsqlDefaultPage orasso.home
PlsqlPathAlias url
PlsqlPathAliasProcedure orasso.wwpth_api_alias.process_download
</Location>

Just add that to the existing DADs in the dads.conf file on the middle tier, and bounce the OHS:
[oracle@asmt101202 bin]$ opmnctl restartproc type=ohs
opmnctl: restarting opmn managed processes...


Then, try to access the SSO Server's home page:
http://asmt101202.mymachine.local:7777/pls/orasso/orasso.home

That works...[edit]But you cannot login; you will get: WWC-41439.[/edit]
However, the Portal/SSO login page still redirects you to the infrastructure. You will have to reregister the SSO server. Now, there are two ways of doing that:
  1. Call the ssoreg.jar in $ORACLE_HOME/sso/lib
  2. use the ssoreg script (or batch, when using MS), located in $ORACLE_HOME/sso/bin.
Some posts suggest to use the latter, as it sets some environment variables; so I will. What this registration does is create an encrypted osso.conf file.
[edit]There are some nasty side-effects... See Metalink Note:249408.1 What will happen, is that an empty obfuscated configuration file, osso.conf, is created, to which Portal is added. Portal may work OK after these actions, other vital SSO applications, like OIDDAS, will not.[/edit]
[oracle@asmt101202 bin]$ $ORACLE_HOME/sso/bin/ssoreg.sh -oracle_home_path $ORACLE_HOME -site_name asmt101202.mymachine.local:7777/pls/portal -config_mod_osso TRUE -mod_osso_url http://asmt101202.mymachine.local:7777/pls/portal

I did it on the infrastructure tier as well, seemed logical, but I have no real explanation as to why. I do remeber a note on metalink, mentioning it should be done on both.
Make sure all configs are updated: $ORACLE_HOME/dcm/bin/dcmctl updateconfig -d -v
stop and start all processes, both ends: opmnctl stopall, opmnctl startall
You will stil get an error page; make sure to alter the iasadmin.xml file on the middle tier ($ORACLE_HOME/portal.conf/iasadmin.xml - alter the Host entry on line 2), and run ptlconfig:

oracle@asmt101202 conf]$ ./ptlconfig -dad portal -site -pw welcome2
~/product/as101202/midtier/portal/conf ~/product/as101202/midtier/portal/conf

Portal Dependency Settings Tool

Processing Portal instance '/pls/portal' (cn=asdb,cn=oraclecontext)
Processing complete

Now - following the "login" link on the orasso home page, I get:
You cannot login because there is either invalid or no configuration information stored in the enabler configuration table (WWSEC_ENABLER_CONFIG_INFO$ (WWC-41439))
The WWC-41439 is a link to an error page, that tells me:
WWC-41439 - You cannot login because there is either invalid or no configuration information stored in the enabler configuration table (WWSEC_ENABLER_CONFIG_INFO$).
Cause:
One or both of the following occurred:
  • An alias defined in the Apache configuration caused Apache to translate host.domain.com to just host. If this is the case, the Login link only shows host:port (dropping the domain).
  • The default domain was not set in the Apache configuration. When this occurs, only the hostname is shown in the Login link and the domain is not included.
  • The Portal was configured with an incorrect host or port.
Action:
Do one or both of the following:
  • Remove all such aliases from your Apache configuration.
  • Include the domain in the ServerName parameter.
  • Fix the Host in the IASInstance element and ListenPort in the WebCacheComponent element in iasconfig.xml and run ptlconfig -dad portal -site. The ptlconfig script and the iasconfig.xml file is normally located in the directory portal/conf under the OracleAS Portal and OracleAS Wireless middle-tier home.

Looks like we still have to explain to portal, that the url's are routed somewhere else, which makes sense, of course.
Just fixing the host entry in iasconfig.xml and running the ptlconfig utility does not seem to be enough; inspecting the table shows:
SQL> select LS_LOGIN_URL from portal.WWSEC_ENABLER_CONFIG_INFO$
/

LS_LOGIN_URL
------------------------------------------------------------------------------------------------------------------------------------
http://asinfra101202.mymachine.local:7777/pls/orasso/orasso.wwsso_app_admin.ls_login

SQL> update portal.WWSEC_ENABLER_CONFIG_INFO$
2 set LS_LOGIN_URL = 'http://asmt101202.mymachine.local:7777/pls/orasso/orasso.wwsso_app_admin.ls_login';

1 row updated.

SQL> commit;


That does the trick - no more redirects.
In a nutshell:
  1. Enable the orasso DAD on the middle tier.
  2. Reregister the SSO server (middle tier)
  3. Fix iasconfig.xml
  4. Update the portal table

Thursday, December 14, 2006

Troubles....

About two weeks ago, my monitor went dead. I switched it on, and it did ''poof!" - lights out. Funny smell came from it, too. Anyway - no fuse to be seen, and as the main switch was dead as well (no power light), I decided to say farewell to my 19" Iiyama, that has served me for about 8 years.
So - something new had to be bought, but what? I wanted a 22" wide Samsung (the SyncMaster 225 BW), but after discussing the pro and cons, I decided for a 204B (20", 1600 by 1200 resolution), and a graphics card upgrade (my old card could neither handle the resolution, nor DVI - not that DVI is a requirement...).
Luckily, the "new" card fitted in my computer (which still uses AGP - I know, hopelessly old fashioned), so I'm typing away with a crisp, clear screen, and a lot more space on my desk.

Wednesday, September 27, 2006

mod_plsql and flexible parameter passing

This is a continuation on a previous entry, where I introduced a HTML wrapper. This wrapper has a nice way to produce a combo box:
htp.p(htmlform.selecttable(label => 'Directory:'
, name => 'p_directory'
, tablename => 'dba_directories'
, textcol => 'directory_name'
, listsize => 10
, orderby => '1'
, p_default => r_sel_pd.directory_name ) );
This little piece of code will actually display a combo box, 10 lines deep, based on the dba_directories table. It will display the column directory_name, and pass it back in a parameter, called p_directory. The list will be ordered on the (one) column displayed.
As the select_table function is overloaded, the fact that listsize is mentioned means it will be a multi select combo box. For the HTML coders amongst you, the select element will have multiple="multiple" added.

This means the call to the database will pass an unknown number of parameters. how to handle this?
Well, this is where the flexible parameter passing of mod_plsql kicks in. First of all, we'll have to tell mod_plsql we're passing an unknown number of parameters. this is done by changing the call to the procedure that will handle the request. in stead of this:
HTP.p(htmlform.formstart(p_action => 'fablogspr.bsavepd'));
the call is changed to this:
HTP.p(htmlform.formstart(p_action => '!fablogspr.bsavepd'));
Note the exclamation mark. That's all there is to change the mode of mod_plsql.

Procedure changes.
The original procedure just had three parameters, id, profile and directory name. The declaration part looks like:
procedure bsavepd (
p_id IN VARCHAR2,
p_profiel IN VARCHAR2,
p_directory IN VARCHAR2 );
The new procedure declaration is:
procedure bsavepd(
name_array IN OWA.vc_arr,
value_array IN OWA.vc_arr)
As I designed the page to use three parameters originally, and the first two do not change, the combo box procedding can be:
begin
for i in 3 .. name_array.LAST
loop
savenewpd(l_prf, value_array(i));
end loop;
EXCEPTION
WHEN extra_page
-- Just return the generated error or warning page.
THEN
null;
WHEN others
THEN
htp.p(format.errorpage(SQLERRM));
END;
I do not need the name_array(i) in the update loop, as it will always contain the name of the parameter I defined in the htmlform.selecttable above, p_directory.
Enjoy modding!

Friday, September 15, 2006

Space advisor - ORA-20000

Ran against a bug today, with dbms_space:
ORA-12012: error on auto execute of job 8897
ORA-20000: ORA-20000: Content of the tablespace specified is not permanent or tablespace name is invalid
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1344
ORA-06512: at "SYS.DBMS_SPACE", line 1560

Turns out to be bug 4707226, which "will be fixed in release 11.0", explained in Note:343795.1. One of the symptoms is that you have a tablespace referenced in table DBA_AUTO_SEGADV_CTL, that no longer exists.
In my case, I used drop tablespace xxxx including contents and datafiles;
The note ends with "No workaround available". Well, here is one that worked for me:
- recreate the tablespace with a minimal sized datafile (100k will do)
- exec dbms_space.auto_space_advisor_job_proc
That should now run without problems.
- use drop tablespace xxx;
- exec dbms_space.auto_space_advisor_job_proc
Should still run without errors.
You may check DBA_AUTO_SEGADV_CTL, so verify your tablespace is not mentioned anymore.