mattypenny

opatch failed with the above error.

Again its really a problem with the wetware (i.e. me), but worth typing up as its fairly obscure.

The problem seems to have been that I was logged in as a different user on the server which holds our staging area when I copied the software across. I mapped the drive on the the target server as the Oracle user, but it still seems to have inherited the wrong ownership at some level. I didn’t have much time to investigate so I can’t be 100% sure.

opatch failed with the above error.

Again its really a problem with the wetware (i.e. me), but worth typing up as its fairly obscure.

The problem seems to have been that I was logged in as a different user on the server which holds our staging area when I copied the software across. I mapped the drive on the the target server as the Oracle user, but it still seems to have inherited the wrong ownership at some level. I didn’t have much time to investigate so I can’t be 100% sure.

This is a fairly foolish pitfall that I stumbled into last week.

One of the recommendations from our external security audit was that we rename the listener from the default ‘LISTENER’ to something else.

I’m a little bit dubious as to how much renaming the listener increases ‘security’ since the listener name is visible in the Services panel or through net start. However, from a security POV it can’t do any harm, and it’s not a lot of effort, so we went ahead with it.

Semi-interestingly, as far as I can see there’s no Oracle recommendation to rename the listener.

This went fine for all but one of our servers.

The problem arose for one particular application which had some of its own services dependent on the Oracle listener. When the listener was renamed from ‘LISTENER’ to something else, the application didn’t come up.

Service dependencies are viewable under:

• Services Panel
• Right click the Listener Service
• Select the Dependencies Tab
• This shows services which are dependent on the Listener, and any services on which the listener depends.

I got this error running an analyze from within a package called by perl:

[Oracle][ODBC][Ora]ORA-02074: cannot ANALYZE TABLE in a distributed transaction

It’s basically the same issue as descibed in the link - you have to turn off the Microsoft Distributed Transaction Server, as follows:

• fire up the Microsoft ODBC Administrator
• Select the System DSN Tab
• Select the DSN that it’s using
• Click Configure
• Select the Workarounds Tab
• click Disable Microsoft Transaction Server.

You have to go into Microsoft ODBC Administrator

I’ve been having problems with opatch.

todo - fix the link Setting ACTIVE_STATE_PERL=TRUE cured at least one of these (although I’m not sure that I didn’t patch at least once without the setting, because there was another version of perl - mksnt - on the server) as per the previous post

Its sometimes failed, then worked without me being entirely sure what I changed in the meantime.

Last night I sussed that at least some of this is caused by having a space following the ACTIVE_STATE_PERL=TRUE

This is a stupid, but unlikely mistake - certainly a problem with the wetware, but it’s not easy to spot and it cost me a lot of time (and grief).

The following test case does the following:

1. Sets the other variables
2. Sets ACTIVE_STATE_PERL=TRUE with a trailing space on the end of true
3. echos the variable back to display the space
4. runs opatch -lsinventory and it fails
5. Re-sets ACTIVE_STATE_PERL=TRUE with the trailing space removed
6. runs opatch -lsinventory and it succeeds

-some other variables. Allegedly the case of the D: is significant
set ORACLE_HOME=D:\ORACLE\ORA92
set PERL5LIB=D:\Perl\lib

-- typing TRUE then a space before the carriage return
set ACTIVE_STATE_PERL=TRUE

-- show there's a space at the end
echo %ACTIVE_STATE_PERL%x
TRUE x

-- run opatch
D:\oracle\ora92\OPatch>d:\perl\bin\perl.exe opatch.pl lsinventory -all

-- it fails. Its possibly another discussion, but you wouldn't necessarily know
-- this was failure if you were doing an apply and you hadnt seem it before, but
-- anyway...

Oracle Interim Patch Installer version 1.0.0.0.53
Copyright (c) 2005 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home = D:\oracle\ora92
Location of Oracle Universal Installer components = D:\oracle\ora92\oui
Location of OraInstaller.jar = "D:\oracle\ora92\oui\jlib"
Oracle Universal Installer shared library = D:\oracle\ora92\oui\lib\win32\oraInstaller.dll
Location of Oracle Inventory Pointer = N/A
Location of Oracle Inventory = D:\oracle\ora92\inventory
Path to Java = "D:\oracle\ora92\jre1.4.2\bin\java.exe"
Log file = D:\oracle\ora92\.patch_storage\\*.log

-- reset without the spurious space
D:\oracle\ora92\OPatch>set ACTIVE_STATE_PERL=TRUE

-- prove there's no space
D:\oracle\ora92\OPatch>echo %ACTIVE_STATE_PERL%x
TRUEx

-- and it works
D:\oracle\ora92\OPatch>d:\perl\bin\perl.exe opatch.pl lsinventory -all

Oracle Interim Patch Installer version 1.0.0.0.53
Copyright (c) 2005 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home = D:\oracle\ora92
Location of Oracle Universal Installer components = D:\oracle\ora92\oui
Location of OraInstaller.jar = "D:\oracle\ora92\oui\jlib"
Oracle Universal Installer shared library = D:\oracle\ora92\oui\lib\win32\oraInstaller.dll
Location of Oracle Inventory Pointer = N/A
Location of Oracle Inventory = D:\oracle\ora92\inventory
Path to Java = "D:\oracle\ora92\jre1.4.2\bin\java.exe"
Log file = D:\oracle\ora92\.patch_storage\\*.log

Remove extra quote surrounding system commands on Windows...
Creating log file "D:\oracle\ora92.patch_storage\LsInventory__01-25-2006_10-35-14.log"

Remove extra quote surrounding system commands on Windows...
ORACLE HOME LOCATION
----------- --------

OraHome8160 D:\Oracle\Ora\8160
OraHome92 d:\oracle\ora92


OPatch succeeded.

Grrrr!

Keyboard macros in gvim are much easier than using the map command. Basically you do:

qx - to start recording a macro called ‘x’ q - to stop recording the macro @x - to execute it

So, Raptor seems to need 10G client.

I knew there was not going to be a 10G version of Names, as it was going to be discontinued in favour of OID.

What I didn’t realize was that 10G wouldn’t work at all with Names.

Sadly all our clients and servers use Names. So until I install OID, switch all of those clients and servers I’m going to have to create and maintain a separate directory of all our Names to run Raptor.

ShowDoc

So, Raptor seems to need 10G client.

I knew there was not going to be a 10G version of Names, as it was going to be discontinued in favour of OID.

What I didn’t realize was that 10G wouldn’t work at all with Names.

Sadly all our clients and servers use Names. So until I install OID, switch all of those clients and servers I’m going to have to create and maintain a separate directory of all our Names to run Raptor.

ShowDoc

I think this is because we had a problem with our corporate mail server, because:

a) I have a vague memory (but sadly no notes!) that it happened before, and b) in this case we had the problem when the mail server was knackered, it’s now fixed and the problem has gone away.

Make of this what you will

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "DBUSER.PK_ESERVICE_LOAD", line 496
ORA-20000: Unable to send e-mail message from pl/sql : ORA-29260: network error: TNS:operation timed out 

Having a first look at Raptor, I got an error message to the effect that it couldn’t find ocijdbc10.

This was because I didn’t have Oracle 10G client installed. I installed it and it now connects OK.

I always used to find it a nuisance that applying the lower level Oracle patches was such a manual activity - you got a bunch of exe’s, dll’s etc and have to manually copy them into place. Typically you would create a script to do it, and wonder why Oracle hadn’t done so.

Recently patches though have been applicable with opatch - a perl script to do the donkey work.

However, admittedly mainly through my own ability to rtfm (or maybe to rt the whole fm, and all the surrounding fm), I missed these two crucial variables that you sometimes need to set.

set PERL5LIB=C:\Perl\lib

set ACTIVE_STATE_PERL=TRUE    -- must be upper case 'TRUE'

I didn’t need to do this in development - my guess is that it picked up another perl install, but in acceptance test I got the following:

d:\perl\bin\perl d:oracleora92opatchopatch.pl lsinventory


Oracle Interim Patch Installer version 1.0.0.0.53

Copyright (c) 2005 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under OPatch/docs for usage reference. We also recommend using

the latest OPatch version. For the latest OPatch version and other support related issues, please refer to document

293369.1 which is viewable from metalink.oracle.com


Oracle Home = d:oracleora92

Location of Oracle Universal Installer components = D:oracleora92oui

Location of OraInstaller.jar = "D:oracleora92ouijlib"

Oracle Universal Installer shared library =

D:\oracle\ora92\oui\lib\win32\oraInstaller.dll

Location of Oracle Inventory Pointer = N/A

Location of Oracle Inventory = D:\oracle\ora92\inventory

Path to Java = "D:\oracle\ora92\jre1.4.2\bin\java.exe"

Log file = d:/oracle/ora92/.patch_storage//*.log

…and then nothing, and no log files.

Here are instructions for disabling wsh. This is particularly used by DBAs to run RDA (Remote Diagnostic Agent) to gather Oracle and Oracle-related diagnostics.

• Run regedit

• Go to HKEY_LOCAL_MACHINE\Software\Microsoft\Windows Script Host\Settings\Enabled (the Microsoft doc says to create it if its not there)

• Set the value to 0

That’s it. You get the message:

Windows Script Host access is disabled on this machine. Contact your administrator for details.

If you try to use it.

If it needs to be re-enabled, then you set the value to 1 - we often need to use it on the database servers to get diags for Oracle, but we should disable it each time after use.

The MS reference is:

Ms ref

Here are instructions for disabling wsh. This is particularly used by DBAs to run RDA (Remote Diagnostic Agent) to gather Oracle and Oracle-related diagnostics.

• Run regedit

• Go to HKEY_LOCAL_MACHINE\Software\Microsoft\Windows Script Host\Settings\Enabled (the Microsoft doc says to create it if its not there)

• Set the value to 0

That’s it. You get the message:

Windows Script Host access is disabled on this machine. Contact your administrator for details.

If you try to use it.

If it needs to be re-enabled, then you set the value to 1 - we often need to use it on the database servers to get diags for Oracle, but we should disable it each time after use.

The MS reference is:

Ms ref

I got this error trying to specify the tablespace for my text indexes. I was moving them from a default tablespace because they are taking up quite a lot of space.

Looking up oerr, you get:

Cause:
The user specified an invalid option like ASC, DESC, SORT or a parallel clause, partitioning clause or physical attributes clause.
Action:
Choose one of the valid clauses for creation of domain indexes.

You can' specify a tablespace as such in the create index statement. Something I would have found out had I been arsed to rtfm in the first place.

Anyway, as per Oracle note 150453.1 , you have to create a preference for the storage, and then use the parameter syntax when you create the index, as follows:

begin 
Ctx_Ddl.Drop_Preference ( 'eservice_text_storage' ); 
end; 
/
begin 
Ctx_Ddl.Create_Preference('eservice_text_storage', 'BASIC_STORAGE'); 
ctx_ddl.set_attribute('eservice_text_storage',
'I_TABLE_CLAUSE', 
'tablespace text_indexes'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'K_TABLE_CLAUSE', 
'tablespace text_indexes'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'R_TABLE_CLAUSE', 
'tablespace text_indexes storage (initial 1M) lob (data) store as (cache)'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'N_TABLE_CLAUSE', 
'tablespace text_indexes'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'I_INDEX_CLAUSE', 
'tablespace text_indexes compress 2'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'P_TABLE_CLAUSE', 
'tablespace text_indexes'); 
end; 
/ 

drop index sc_sol_textidx_desc;
create index sc_sol_textidx_desc on owner.sc_solutions(description)
indextype is ctxsys.Context
parameters ('storage eservice_text_storage'); 
;

drop index sc_sol_textidx_dref;
create index sc_sol_textidx_dref on sc_solutions (document_ref)
indextype is ctxsys.context 
parameters ('storage eservice_text_storage LEXER ctxsys.mylex'); 

I got this error trying to specify the tablespace for my text indexes. I was moving them from a default tablespace because they are taking up quite a lot of space.

Looking up oerr, you get:

Cause:
The user specified an invalid option like ASC, DESC, SORT or a parallel clause, partitioning clause or physical attributes clause.
Action:
Choose one of the valid clauses for creation of domain indexes.

You can' specify a tablespace as such in the create index statement. Something I would have found out had I been arsed to rtfm in the first place.

Anyway, as per Oracle note 150453.1 , you have to create a preference for the storage, and then use the parameter syntax when you create the index, as follows:

begin 
Ctx_Ddl.Drop_Preference ( 'eservice_text_storage' ); 
end; 
/
begin 
Ctx_Ddl.Create_Preference('eservice_text_storage', 'BASIC_STORAGE'); 
ctx_ddl.set_attribute('eservice_text_storage',
'I_TABLE_CLAUSE', 
'tablespace text_indexes'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'K_TABLE_CLAUSE', 
'tablespace text_indexes'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'R_TABLE_CLAUSE', 
'tablespace text_indexes storage (initial 1M) lob (data) store as (cache)'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'N_TABLE_CLAUSE', 
'tablespace text_indexes'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'I_INDEX_CLAUSE', 
'tablespace text_indexes compress 2'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'P_TABLE_CLAUSE', 
'tablespace text_indexes'); 
end; 
/ 

drop index sc_sol_textidx_desc;
create index sc_sol_textidx_desc on owner.sc_solutions(description)
indextype is ctxsys.Context
parameters ('storage eservice_text_storage'); 
;

drop index sc_sol_textidx_dref;
create index sc_sol_textidx_dref on sc_solutions (document_ref)
indextype is ctxsys.context 
parameters ('storage eservice_text_storage LEXER ctxsys.mylex'); 

My procedure has a create database link in it:

v_exe := 'create database link temp_security_monitor '               ||
         'connect to '  p_username  ' identified by '  p_password    ||
         ' using '  ''''  p_tns_string  '''';          

if v_debug = TRUE
    then  dbms_output.put_line (v_exe);
end if;

execute immediate v_exe;

This was failing:

Unexpected error-1031 ORA-01031: insufficient privileges

SQL> conn <a href="mailto:sys@db">sys@db</a> as sysdba
Connected.
SQL> grant create database link to system;
Grant succeeded.

Then all was hunky-dory

The problem

I lost SQL connectivity to all databases between about 11.29 and 12.06 today (21st June).

According to the trace files whereas normally the Names servers are addressed as follows:

[000001 19-MAY-2005 14:11:16:517] NAMES.PREFERRED_SERVERS = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = vAlias1)(PORT = 101)) (ADDRESS = (PROTOCOL = TCP)(HOST = vAlias2)(PORT = 101)))

during this period they were addressed:

[000001 21-JUN-2005 11:43:34:114] NAMES.PREFERRED_SERVERS = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 46.2.3.44499)(PORT = 101)) (ADDRESS = (PROTOCOL = TCP)(HOST = 46.2.3.44599)(PORT = 101)))

The IP addresses in the incorrect address are the correct addresses (for dbs18 and dbs15) respectively, but with a ‘99’ appended. And ‘normally’ the trace file shows the alias anyway.

Empirix didn’t show any issues - it could have just been me, but I didn’t make any changes that might have caused, or fixed, it.

Oracles (correct) response

I don’t think this is a DNS issue. I would have to say that the sqlnet.ora has been changed. The beginning of the trace shows the sqlnet.ora after it has been read, but before it has been parsed at all. This shows the invalid value already there.

Your two traces differ in that the ‘incorrect’ one appears to be reading its sqlnet.ora from C:\Temp\sqlnet.ora

Presumably I was in c:temp when I was kicking off sql.

As I say it was a problem with the wetware

But on my PC its at:

C:\WINNT\system32\ODBCAD32.exe

On one of the users XP boxes it was

C:\WINDOWS\system32\ODBCAD32.exe

I had a query from a user about Text not retrieving the expected data for a given bit of Text

The Text field (‘Solution’) contains:

1. What type of OS can be used with the xxx?
2. What are the minimum requirements of a PC for use with xxx?
3. What versions of browser can be used?
4. What modem is recommended in PPP (Point-to-Point Protocol)?
Solution
1. Recommended Operating Systems:
· Windows 95/98
· Windows NT 4.0 with service pack 5 or later
· Windows 2000
Note:The Mac OS is not supported because, Mac version browsers do not have some key functions
(related to server push).
2. PC Requirements:
· Minimum requirements: 400MHz Pentium III with 64MB RAM
3. Recommended Browsers:
· Internet Explore 5.0 (or higher)
Note:The xxx does not require a special plug-in because the picture images use JPEG compression. However, some versions of Internet Explorer do not support 'Server Push' functions and the 'Active X' component will need to be installed . supplied with the xxx.
4. The xxx supports the following modems:
· 3Com 56k Faxmodem
· US Robotics Sportster Flash
· Ericsson K56 DTV
· Diamond Supra Express 56e PRO

The user found that:

• Search ‘What are the minimum requirements of a PC for use with’ finds document
• Search ‘What type of OS can be used with the’ finds document
• Search ‘What type os’ doesn’t
• Search ‘What type OS’ doesn’t
• Search ‘What os’ does
• Search ‘What type of OS can be used with the xxx-yyy’ doesn’t
• Search ‘What type of OS can be used with the xxx yyy’ does

As words are ored together these should all find the relevant document

Just going to paste my response in ‘as is’ for the time being:

First there’s a couple of points which I haven’t necessarily got chapter and verse on, but (currently) believe are true. These are related to:

• or-ing
• stopwords

Or-ing

To get the search terms to be ‘OR’-ed within the query I think you have to use the '' syntax. Spaces on there own don’t do it. The ‘$’ enables stemming, I think.

So running:

select document_id, SOLUTION from sc_solutions where contains(solution, ‘&1’) > 0 and document_id = 15526

SQL> / Enter value for 1: Flash Sportster old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘Flash Sportster’) > 0

no rows selected

SQL> / Enter value for 1: FlashSportster old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘FlashSportster’) > 0

15526

1. What type of OS can be used with the xxx?
2. What are the minimum requirements of a PC for use with xxx?
3. What versions of browser can be used?
4. What modem is recommended in

Note: not hugely relevant but I was tyring to prove this using ‘requirements minimum’. It appeared to be or-ing it in any case, but eventually noticed the text in green below. I think it was matching this.

Stoplist words

The way Oracle seems to process stopwords is, I reckon, just weird. If you have a stoplist in the search string in effect it turns it into a wild card word matching any other word (stopword or not).

So:

SQL> / Enter value for 1: requirements be what PC old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘requirements be what PC’) > 0

15526

1. What type of OS can be used with the xxx?
2. What are the minimum requirements of a PC for use with xxx?
3. What versions of browser can be used?
4. What modem is recommended in

and also

SQL> / Enter value for 1: Mac is browsers old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘Mac is browsers’) > 0

15526

1. What type of OS can be used with the xxx?
2. What are the minimum requirements of a PC for use with xxx?
3. What versions of browser can be used?
4. What modem is recommended in

[ its matching ‘Mac version browsers’ ]

But it doesn’t match if you leave the stop words out of the search:

SQL> / Enter value for 1: requirements PC old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘requirements PC’) > 0

no rows selected

As I say this seems to me to be weird. But it seems to be as per the documentation. At:

http://www.stanford.edu/dept/itss/docs/oracle/9i/text.920 /a96517/query.htm (text manual)

It says

When you include a stopword within your query phrase, the stopword matches any word. For example, the query: ‘Jack was big’ matches phrases such as Jack is big and Jack grew big assuming was is a stopword.

Examples

So re: the examples below, currently I’d make the following assumptions.

Search ‘What are the minimum requirements of a PC for use with’ finds document

• as expected

Search ‘What type of OS can be used with the’ finds document

• as expected

Search ‘What type os’ doesn’t

As per the points above re ‘or-ing’ and stoplists, I believe you either need to explicitly ‘or’ it:

SQL> / Enter value for 1: whattypeos old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘whattypeos’) > 0

15526

1. What type of OS can be used with the xxx?
2. What are the minimum requirements of a PC for use with xxx?
3. What versions of browser can be used?
4. What modem is recommended in

Or explicitly have a stopword in the place where the ‘of’ is. I used ‘what’ again here.

SQL> / Enter value for 1: what type what os old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘what type what os’) > 0

15526

1. What type of OS can be used with the xxx?
2. What are the minimum requirements of a PC for use with xxx?
3. What versions of browser can be used?
4. What modem is recommended in

Search ‘What type OS’ doesn’t

• as above, I think

Search ‘What os’ does

• ‘What’ being a stopword is behaving like a wildcard. So it would also get a match with any other stopword I think. As per:

SQL> / Enter value for 1: mz os old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘mz os’) > 0

15526

1. What type of OS can be used with the xxx?
2. What are the minimum requirements of a PC for use with xxx?
3. What versions of browser can be used?
4. What modem is recommended in

Note:‘mz’ is an Oracle default stopword, for some reason.

Started getting the error above this morning. The SQL server involved supports our Alteris installation. None of the users could get on either.

The main Microsoft reference is:
Microsoft doc

This covers a lot of ground, but something that seems/seemed to be significant is the (lack of) a SPN. SPN is a “service principle name”. My understanding is that it is a record in Active Directory which shows that resource X is up and running on server Y. It seems that this needs to be ther for the client to talk to the server.

The SPNs can be displayed by running setspn on the server. Setspn is in the Windows resource kit. I think there was a link from the doc above

> setspn -L bigserver

Registered ServicePrincipalNames for CN=BIGSERVER,OU=LONDON,OU=Servers,OU=SBE and SCE (UK),DC=eu,DC=mycompany,DC=com:
MSSQLSvc/bigserver.eu.mycompany.com:1433
HOST/bigserver
HOST/bigserver.eu.mycompany.com

When I ran the above for the server with the connectivity problem there was no entry for MSSQLSvc. I tried to add one as follows:

> setspn -A MSSQLSvc/otherserver.eu.mycompany.com:1433 otherserver

But this failed with:

Failed to assign SPN to account…

Instead out AD administrator used ADSI Edit (an AD GUI) to manually add the entry. This seems to have fixed it at a sqlserver level. Alteris users still can’t get in but I suspect this is because the Alteris application servers need to be bounced. This is happening as I type….

Why did the error suddenly occur ? The server was bounced for the first time in several weeks last night and the following hotfixes were applied:

KB899923
KB893066
KB893086
KB890859
KB893803
KB842773

Dunno which of the above if any made any difference