Oracle Observations

November 18, 2012

11g forms error

Filed under: 11g,Oracle forms — bigdaveroberts @ 1:21 pm

After migrating to 11g we encountered the following error in 2 of our forms after migration to 11g:

Error 801 at line 0, line 0
internal error [phd_get_defn:DI_U_NAM_RHS]
Error 908 at line 0, column 0
The stored format of <schema name>.<Procedure name> is not supported by this release.

I believe that the issue related to compiling in form code that called database packages that had dependencies in other databases.

But that isn’t important, there was one hit on the OTN forum that correctly pointed me at the correct Metalink article and patch:

https://forums.oracle.com/forums/thread.jspa?threadID=1038775

So the problem is described in article 1058803.1 and resolved by 7708340.

So ultimately the problem doesn’t seem to require a blog post.

There was however one anomaly  the patch is a database patch and the read-me implied that the patch needed to be applied to the Weblogic server also the patch was relatively speaking large.

I have to admit I was highly dubious about applying a database patch to the middle tier so I went ahead and applied the patch to my development PC, and to my great surprise, it worked.

It also appears that technically you don’t need to apply it to the middle tier, it simply needs to be applied to the PC on which the forms are compiled, although obviously it would be good practice to keep the development PCs and server environments in sync with the live environment.
So sometimes, however unlikely it sounds, Oracle instructions are sometimes accurate straight out of the tin!

Advertisements

November 17, 2012

Oracle forms 11g, OLE2, Windows 2008 R2 64bit issue. (Possibly.)

Filed under: 11g,OLE,Oracle forms,Uncategorized — bigdaveroberts @ 10:12 pm

For clarity, its definitely a forms issue and It’s definitely an OLE2 issue.

But the rest is speculative.

The application was developed to produce reports as Excel spreadsheets using OLE2 to to populate the cells with data.

This was originally developed under Forms 6i and was successfully migrated to Forms 10g using the middle tier to generate the spreadsheets and then pass it back to the user using the same mechanism (WEB.SHOW_DOCUMENT) as used to pass oracle reports back.

Upon migration to Forms 11g this implementation still worked using 32bit windows server 2003 for the middle tier, but when we moved to 64bit Windows Server 2008 R2 the process failed.

We could see Excel being invoked, but it ran only briefly and then terminated without generating a spreadsheet or logging any form of error that we could find.

I tested using my development environment under windows 7 64 bit and the functionality worked fine.

So, it was back to Sysinternals process monitor (http://technet.microsoft.com/en-us/sysinternals/bb896645) to try and work out the difference in behavior.

My expectation was that it would be some obscure registry setting that would be the cause, in fact the behavior diverged after an attempt to read the following directory:

c:\windows\system32\config\systemprofile\desktop

Strangely this directory was also missing on my windows 7 PC but it was present on windows server 2003.

I copied the desktop directory and its contents from the windows 2003 server to the windows 2008 server and the problem disappeared.

I then deleted the contents of the desktop directory and the Excel spreadsheet was still successfully generated.

So in conclusion, I guess, that when Oracle implemented it’s forms OLE2 interface, they based it on configuration information that is no longer present on 64 bit Windows Server 2008 R2.

However I am uncertain as to when that directory disappeared.

There is also the issue that on my development machine the problem didn’t manifest itself, indicating that the problem may be more complex than this initial investigation has exposed.

As a last observation, the closest I’ve come to an official document that hints at being related on Metalink is:

Bug 11704826 : PROPOSAL FAILS IN 8.1.1.3 WITH WINDOWS 2008 AND OFFICE 2007

Although there is no explicit reference to OLE2 and the bug appears unresolved.

Not quite the conclusive conclusion that I like, but interesting enough for a blog post.

(Possibly.)

April 18, 2010

The dawn of the 4k sector hard drive and its impact for the DBA.

Filed under: 11g,ASM — bigdaveroberts @ 12:47 pm

Firstly, I should state that, despite the fact that the launch of the new standard 4K (or ‘Advanced format’ drives as they are also known) were originally slated to co-inside with the release of Windows Vista (the first operating system to support them) in 2006, I have to admit that the first I heard of them was from an issue of a Custom PC article earlier this year, just before their belated, imminent launch.

Secondly, I would also suspect that as only one manufacturer (Western Digital) currently has product available and other manufacturers might have differing approaches to how they handle the issue of backward compatibility, chances are this post will at least in part be out of date fairly quickly!

Fundamentally, there will be two main areas in which the DBA may need to be aware.

1. Support for older operating systems.

2. ASM support.

Operating system support.

At the time of writing, to my knowledge, only 3 operating systems natively support the 4k standard, (Windows (Vista, 7 & Server 2008), MacOS (10.4 and later) and Linux (2.6.31 kernel and later.), that and the suggestion that by the end of this year the majority of hard disks are expected to be based on the new 4K standard has lead to speculation that if you intend to support older operating systems into the medium term future you need to start stockpiling 512 byte sector hard drives.

The truth is somewhat more complex than this.

While newer operating systems can operate with hard disks of both standards, there has been an attempt to produce some sort of backwards compatibility for older operating systems such as Windows 2000 and XP.

In simple terms, there should be no issues with using 4K dives as non-bootable data drives, although I’m confident that attempting to mix 512 and 4K drives in a raid array would be foolish and I strongly suspect that it would be impossible. The complexities and problems are likely to be encountered when attempting to boot older operating systems from the 4K hard drives.

The first solution(s) to booting older operating systems from the Western digital Green drives consists of a drive compatibility setting and a software kludge.

However while with minimal additional effort you will be able to boot XP from a 4K hard drive, the solution is unlikely to be compatible enough to facilitate the installation of multiple operating systems on the same drive or the use of some boot loaders.

NB. I am informed that if windows Vista is installed on a second partition of a disk where the primary partition is XP, then Vista will replace the XP boot loader with its own. In that case, I suspect that it would be potentially possible, with Vista or 7 on the primary partition, then the boot loader installed might be sophisticated enough to boot XP installed on a secondary partition, but at the moment that is pure speculation.

ASM support

I struggled to find any information about ASM support for 4K hard drives until I eventually found this document:

Extending ASM to Manage All Data

Essentially, 4K hard drives will be supported from Oracle 11.2, where both the RDBMS version and the ASM instance version are required to be at this level.

The type of hard drive in use will need to be specified during the disk group creation and you will not be able to mix disk types in a single group.

Example of the new syntax option taken from the Oracle document:

CREATE DISKGROUP data NORMAL REDUNDANCY
FAILGROUP ctrl_a DISK
‘/devices/diska1’, ‘/devices/diska2’
FAILGROUP ctrl_b DISK
‘/devices/diskb1’, ‘/devices/diskb2’
ATTRIBUTE ‘compatible.asm’ = ’11.2’, ‘compatible.rdbms’ = ’11.2’,
‘sector_size’=’4096’;

I see no indication that mixing 4K hard drives in 512 byte compatibility mode and true 512K drives will be supported by Oracle, and the fact that different hard disk manufacturers may implement 512 byte compatibility using different mechanisms, I strongly suspect that it never will be.

Ultimately, there are potential issues for ASM environments that can’t be upgraded to Oracle 11.2 in the medium term.

For those that can migrate, there are still potential issues where ASM disk groups using 512 byte disks will have to be migrated to disk groups based on 4K hard disks over time, potentially increasing complexity during recovery operations, and forcing administrators to manage 2 inventories of spare hard dives.

Final observations.

While the goals of 4K sector drives is to increase the areal density and maximum capacity, neither of these goals will be achieved with the first generation of new disks, as initially the areal density is unchanged (The extra capacity is currently left as extra space at the end of sectors.), there is no great value in being an early adopter of this technology apart from for research purposes.

While SCSI 4K disks will all be standardised, because SCSI has inbuilt mechanisms to allow the operating system to interrogate the underlying structure of the disk facilitating a standard approach, there is likely to be a variation in the approaches taken by hard disk manufacturers to SATA/IDE hard drives, which would discourage me from mixing disks from different manufacturers and would lead me to scour Metalink for patches before entrusting my data to a non-scsi 4K ASM disk array.

Ultimately, there could be potential issues.

Additional information.

An overview of the Western digital approach to backwards compatibility along with benchmarks:

bit-tech.net: The Facts: 4K Advanced Format Hard Disks

And article regarding formatting SSDs indicating that the type of issues encountered with 4K hard drives is not limited to this technology:

thunk.org: Aligning filesystems to an SSD’s erase block size

Discussion thread on the Linux kernel mailing list archive:

ATA support for 4k sector size

March 5, 2008

Oracle Home selector

Filed under: 11g — bigdaveroberts @ 5:35 pm

What does the Oracle home selector do?
Is the fact that it doesn’t appear to be included with 11g a problem?
And finally is Oracle locator express (ole.exe http://www.dbmotive.com/oracle_home_selector.php) an adequate replacement?

The first place to investigate the function of the Oracle home selector has to be the the Metalink Note:66464.1.

This note describes the behavior of the oracle home selector thus:

‘A change in selection [of the oracle home] means that the specified Oracle home will appear as the first directory in the PATH environment variable, but no change is written to the Windows NT Registry.’

Secondly we can analyse the behavior of the utility further using regmon and filemon (http://technet.microsoft.com/en-us/sysinternals/default.aspx)

Using regmon we can establish that;

It opens HKLMSOFTWAREORACLEALL_HOMES
It enumerates HKLMSOFTWAREORACLEALL_HOMES
It iteratively reads HKLMSOFTWAREORACLEALL_HOMESID?, HKLMSOFTWAREORACLEALL_HOMESID?
and HKLMSOFTWAREORACLEALL_HOMESID?PATH

It reads HKLMSYSTEMCurrentControlSetControlSession ManagerEnvironmentPATH

It removes the selected oracle home (if present) from the path, and it adds the selected oracle home to the beginning of the path, writing the resulting new path back to the registry.

Using filemon we can see that:
It writes to the system and system.alt files under c:winntsystem32configsystem folder (essentially confirmation that it is modifying the registry)

So contrary to the Metalink note, the way that the Oracle home selector changes the PATH variable is in fact implemented via a modification of the associated registry entry.

What doesn’t the Oracle Home selector do?

1 It doesn’t interact with the ORACLE_HOME environment variable.

2 It doesn’t touch the Java path related elements, so while it allows you to select a specific version of the oracle binaries that are to be used, the java binaries used will be the latest installed, unless manual editing of the PATH decrees otherwise.

3 It wont change the environment from the command line without user interaction. (It prompts the user to accept the home change even when this is done via the command line parameter.)

What does ole.exe do?

Well ole.exe uses different registry keys, enumeration all the keys under HKLMSOFTWAREORACLE and selecting the oracle path information from the HKLMSOFTWAREORACLEHOME?ORACLE_HOME tree, it also examines the TNS_ADMIN registry key and the TNS_ADMIN environment variable, although, as yet I haven’t seen the application modify any settings.

Is ole.exe an adequate replacement for the oracle home selector?

I suppose it is, although because it locates the ORACLE_HOME information from different registry settings, then it is possible that in some circumstances it may behave differently to the Oracle home selector.

Is the oracle home selector of any value at all?

Considering that it doesn’t appear to interact with the ORACLE_HOME environment variable, I can’t see that the utility has that much value anyway. If you have to change the ORACLE_HOME manually, then you are probably better off writing a wrapper for your oracle utilities instead:

So to run a specific version of sqlplus I create a bat file sqlplusw.bat that contains the following lines:

set ORACLE_HOME=c:orantdesigner
PATH c:orantdesignerbin
sqlplusw.exe

Which seems to me to be a much more convenient solution than using the home selector to change the path variable, and then use the System properties dialog box to change the ORACLE_HOME environment variable setting, and then invoke the utility.

So in conclusion, while OLE appears to be an adequate replacement for the Oracle Home selector, unless I have missed some functionality of the two utilities I can’t see any value in either except for the command line phobics of which I suppose there are a few.

December 14, 2007

PLSQL_Warnings in 11g

Filed under: 11g,PL/SQL — bigdaveroberts @ 4:05 pm

In preparation for the on/off/on upgrade of our application from Oracle 8.1.7 to 9.2.8, I thought it would be interesting to migrate the PL/SQL to my installation of an 11g Oracle database that came out of the UKOUG conference OTN install fest, and also, out of interest see what warnings come out of the PL/SQL compiler when you set PLSQL_Warnings=’enable:all’ compilation option.

Pleasingly out of almost 45,000 lines of source, only one new error was generated:

SQL> show errors;
Errors for PACKAGE BODY P_BODY:

LINE/COL ERROR
——– —————————————————————–
211/5 PLS-00593: default value of parameter “P_PARAM” in body must match that of spec

Here the only problem was that while Oracle 8i is happy for a default parameter value to be specified only in the package body definition, Oracle 11g requires the default parameter to be specified in both the package and the package body.

Eg. while the following code works in 8i, it doesn’t in later releases:

SQL> create or replace PACKAGE P_BODY IS
2 PROCEDURE delete_actual_data (
3 p_parameter IN VARCHAR2
4 );
5 END;
6 /

Package created.

SQL> create or replace PACKAGE body P_BODY IS
2 PROCEDURE delete_actual_data (
3 p_parameter IN VARCHAR2 := ‘N’
4 ) IS
5 BEGIN
6 NULL;
7 END;
8 END;
9
10 /

Package body created.

A small anomaly:

If a package does include errors or warnings, then if the package and body is compiled, then the errors are not displayed, only the following cryptic error message:

SQL> alter PACKAGE P_BODY compile plsql_warnings=’enable:all’;

SP2-0809: Package altered with compilation warnings

SQL> show errors;
SP2-0564: Object “p_bodyplsql_warnings=’enable:all'” is INVALID, it may not be described.

However if only the body is compiled, then the errors and warnings are successfully displayed.

So that leaves me with a selection of PLW warnings to evaluate and/or fix:

PLW-05005: function P_FUNCTION returns without value at line 244
PLW-06002: Unreachable code
PLW-06006: uncalled procedure “P_PROCEDURE” is removed.
PLW-06009: procedure “P_PROCEDURE” OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
PLW-06010: keyword “NAME” used as a defined name
PLW-06010: keyword “REF” used as a defined name
PLW-06010: keyword “RESULT” used as a defined name
PLW-07202: bind type would result in conversion away from column type
PLW-07203: parameter ‘P_PARAMETER’ may benefit from use of the NOCOPY compiler hint
PLW-07204: conversion away from column type may result in sub-optimal query plan

A superficial review of the error messages manual indicates that the number of potential warnings has grown substantially from the 9 and 11 warnings of 10g releases 1 and 2, to a total of 36 documented warnings in 11g release 1. With 3 of the 8 warning types received being new ones from 11g.

Oracle 10 R1 manual page:

http://download.oracle.com/docs/cd/B14117_01/server.101/b10744/plwus.htm#sthref64

Oracle 10 R2 manual page:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/plwus.htm#ERRMG53664

Oracle 11 R1 manual page:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28278/plwus.htm

Blog at WordPress.com.