Oracle Observations

March 31, 2008

BMC and O7_DICTIONARY_ACCESSIBILITY

Filed under: BMC — bigdaveroberts @ 4:05 pm

For those wishing to install BMC Patrol on an Oracle 8.1.7.4 database whit the init.ora parameter O7_DICTIONARY_ACCESSIBILITY is set to FALSE the following grants have to be performed as sys:

grant select on V_$DATAFILE to bmc;
grant select on V_$SESSION to bmc;
grant select on V_$SESSION_WAIT to bmc;
grant select on V_$LATCH to bmc;
grant select on V_$FILESTAT to bmc;
grant select on V_$LATCHNAME to bmc;
grant select on V_$SESS_IO to bmc;
grant select on V_$SQL to bmc;
grant select on V_$SESSTAT to bmc;
grant select on V_$LATCHHOLDER to bmc;
grant select on V_$PARAMETER to bmc;
grant select on V_$STATNAME to bmc;
grant select on DBA_USERS to bmc;
grant select on V_$DATABASE to bmc;
grant select on V_$SYSTEM_EVENT to bmc;
grant select on V_$SYSSTAT to bmc;
grant select on V_$SGA to bmc;
grant select on V_$ROWCACHE to bmc;
grant select on V_$SHARED_SERVER to bmc;
grant select on V_$SGASTAT to bmc;
grant select on V_$LOCK to bmc;
grant select on V_$LOG to bmc;
grant select on V_$ROLLSTAT to bmc;
grant select on DBA_ROLLBACK_SEGS to bmc;
grant select on DBA_DATA_FILES to bmc;
grant select on DBA_FREE_SPACE to bmc;
grant select on V_$LIBRARYCACHE to bmc;
grant select on V_$DISPATCHER to bmc;
grant select on DBA_TABLESPACES to bmc;
grant select on V_$PQ_SYSSTAT to bmc;
grant select on DBA_JOBS to bmc;
grant select on V_$ARCHIVED_LOG to bmc;
grant select on SM$TS_AVAIL to bmc;
grant select on DBA_OBJECTS to bmc;
grant select on V_$INSTANCE to bmc;
grant select on V_$ARCHIVE_DEST to bmc;
grant select on SM$TS_FREE to bmc;
grant select on V_$TEMP_SPACE_HEADER to bmc;

grant execute on DBMS_LOCK to bmc;
grant execute on DBMS_ALERT to bmc;
grant execute on DBMS_SYSTEM to bmc;

If the above are not performed then it will not be possible to compile the BMC$PKKPKG package.

NB. statements above assume BMC as the name of the BMC Oracle user account.

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.

Blog at WordPress.com.