startup fails after migration of Oracle DB 8i to 9i on Alpha 7.3-2

[Guinaudeau]

We are migrating an Oracle DB on a VMS 7.3-2 Alpha system from 8.1.7 to 9.2.0. Migration apparently ran OK, but we cannot start
the DB since then. Audit indicates a failure "SETPRV missing privilege", although i eventually set /DEFPRIV=ALL/PRIV=ALL for the Oracle account including BYPASS for test purpose.

We red lot of release notes, metalink notes and our check-list was hopefully exhaustive enough. Quotas, PQL_M, CPU level (our AXP = EV6 462MHz), FW, ODS-5 format for the product tree, installation from CD-ROM using its own JRE 1.3.1. PAGEFILE.SYS, SYSGEN esp PQL_M* ...

We had initially some confusions between both env 8i/9i or misses in the logical name settings, partly because we did
not use the OFA standards under 8i. We repaired this, lognames and bequeath listener start are OK.

After the migration itself, we cannot start the Oracle DB.

Alert log indicates the SGA allocation then the non-default parameter values and nothing afterwards. No trace file is generated by Oracle anywhere.

On the SQL session :

APOLLO/ORAOMV920[GASLZ]> sqlplus "sys/change_on_install as sysdba"

SQL*Plus: Release 9.2.0.2.0 - Production on Fri Jan 6 02:14:36 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-03113: end-of-file on communication channel
SQL> exit


Audit results in :

%%%%%%%%%%% OPCOM 6-JAN-2006 03:41:50.66 %%%%%%%%%%%
Message from user AUDIT$SERVER on APOLLO
Security alarm (SECURITY) on APOLLO, system id: 4119
Auditable event: Privilege failure
Event information: SETPRV not used to enable temporary privilege not authorized to process (SET PROCESS/PRIVILEGE, $SETPRV)
Event time: 6-JAN-2006 03:41:50.66
PID: 0000029D
Process name: ORA_GASLZB4498
Username: ORAOMV920
Process owner: [ORAOMV920]
Image name: APOLLO$DKC500:[ORACLE.ORA_920.][BIN]ORACLE.EXE
New privileges: ALTPRI,SETPRV,EXQUOTA,BYPASS
Old privileges: CMKRNL,SYSNAM,GRPNAM,IMPERSONATE,LOG_IO,GROUP,PRMCEB,
PRMMBX,TMPMBX,WORLD,OPER,NETMBX,PRMGBL,SYSGBL,PFNMAP,
SYSPRV,SYSLCK,SHARE
Privileges missing: SETPRV
Posix UID: -2
Posix GID: -2 (%XFFFFFFFE)

also i set /DEFPRIV=ALL/PRIV=ALL for the Oracle account, i ever catch
this failure SETPRV missing privilege failure.

any ideas ???

[Robert_Boyd]

The problem here appears to be with the username ORAOMV920 not ORACLE. The process running under ORAOMV920 must have SETPRV enabled. Either this process is not getting SETPRV enabled when it is started because the ORACLE start code for it fails to do so, or the authorized privileges need to be adjusted to include SETPRV.

Robert

[Guinaudeau]

Robert,

the privilege SETPRV is both authorized and default privilege by the account ORAOMV920.

Should the account name be the same as the owner / user name ? We have [ORAOMVN] for the account and [ORAOMV920] for the owner/user. I have choosen the user name then remarked that account name are max 8 chars long.

Would it be that troublesome ?

Louis

[Marc Van den Broeck]

Can you do a show ORAOMV920 in authorize please and show us the output?

Rgds
Marc

[Wim Van den Wyngaert]

Have no to test it but is the oracle.exe installed with privs but not enough privs ?

Wim

[Guinaudeau]

Robert,

sorry : your answer does worth probably more than 4 points

i am testing the following, but i am pretty sure about my trouble.

see attachement. that's not trivial to understand anyway on the first snapshot, but i assume i catched the error because you suggested me the right ideas, looking at user/owner/account names. it was a confusing situation and i did not catch the confusion at the first look. But why does it happen is nevertheless unclear.

i am not enough experienced with SYSUAF / security , and may be i should have user=owner=account in my case. anyway, that has been the source of confusion.

i could not set

actually, the audit failure said :

Username: ORAOMV920
Process owner: [ORAOMV920]

but it was the important data : account = ORAOMVN.

question :

what about this ? is this a bug or a feature ? is this Oracle or VMS ?

i am in the process to change the user account strategy, that is, i discuss with a colleague ... and let you know. just your idea has opened my mind to a potential error.

i have also forgotten to grant ORA_DBA to ORACLE, but it is not the user we actuallay use.

thanks

louis

[Wim Van den Wyngaert]

Your uaf seems ok to me. Don't bother about the account field.

Could you post the output of
$ mc install list/fu APOLLO$DKC500:[ORACLE.ORA_920.][BIN]ORACLE.EXE

Wim

[Wim Van den Wyngaert]

Just to be sure :
. did you re-login after modif of the sysuaf
. do you redirect sysuaf to another sysuaf (sh log sysuaf/tab=*)

Wim

[Guinaudeau]

sorry, i thought on friday i had found the trouble and the correct solution was around this SYSUAF trouble. it is not so obvious, at least i did not catch something. we have some other departments in our company using Oracle9i under Alpha 7.3-2 and i will
check tomorrow morning with them (they can remotely connect our systems) what might go wrong, 4 eyes better than two.

Wim :

>>> did you re-login after modif of the sysuaf

yes

>>> do you redirect sysuaf to another sysuaf (sh log sysuaf/tab=*)

APOLLO/ORAOMV9[GASLZ]> sh log sysuaf
"SYSUAF" = "CLU_DAT:SYSUAF.DAT" (LNM$SYSTEM_TABLE)

so redirected to one file. in normal case, we have separate system
drives for each node, but one CLU_DAT directory common for nodes in
our environment, so one SYSUAF for every nodes in our cluster of
test machines.

>>> Have no to test it but is the oracle.exe installed with privs but not enough privs ?

i did not understand you at first reply : assume you expected following output to check this, but it is unclear what privs you mean :

APOLLO/ORAOMV9[GASLZ]> install list/full apollo$dkc500:[oracle.ora_920.bin]oracle

DISK$ORA920:.EXE
ORACLE;1 Open Hdr Shared
Entry access count = 1
Current / Maximum shared = 1 / 1
Global section count = 1

else, additional informations :

i observed "bizarr" phenomenon with the fact that account was ORAOMVN, and that ORAOMVN user exists, as one can see here :

APOLLO/ORAOMV920[GASLZ]> mc authorize show [30,*] /brief
Owner Username UIC Account Privs Pri Directory

ORAOMVN ORACLE [30,1] ORAOMVN All 4 DISK$OMVN_ORA1:[ORACLE]
ORAOMVN ORAOMVN [30,11] ORAOMVN All 4 DISK$OMVN_ORA2:[ORACLE]
ORAOMV920 ORAOMV920 [30,14] ORAOMVN All 4 DISK$ORA920:[ORACLE]

i had this audit alarm "SETPRV failure" first with these entries. i changed
account name so that was :

ORAOMV920 ORAOMV920 [30,14] ORAOMV9 All 4 DISK$ORA920:[ORACLE]

then audit alarm "SETPRV failure" vanished. that does mean : there should have been something in Oracle code which is confused when account name is the name of another user ...

i discussed with colleagues and anyway will from now have :

user=owner=account

last question for now :

did someone use an HW like our :

System Type AlphaServer DS10 466 MHz, 462 MHz
CPU Type EV6 Pass 2.3 (21264)

PAL Code 1.98-83 ; Console Vers V6.7-1

i might upgrade the FW to the last one, if it sounds safer. just, did someone
experience with EV6 462MHz ? i red the Oracle note 222553.1 and it should be ok, but ...

louis

[Jan van den Ende]

Louis,


then audit alarm "SETPRV failure" vanished. that does mean : there should have been something in Oracle code which is confused when account name is the name of another user ...

i discussed with colleagues and anyway will from now have :

user=owner=account



I am not so sure if the last line is such a good idea, especially with respect to your observation that Oracle code CAN get confused.

As long as ALL usernames, UICs, and account names are unique, each username and each acount name will by default also be the name of an UIC-valued IDENTIFIER. Trying to enter a duplicate value (either the name or the value) silently is accepted, without creating the extra identifier.

But another type of identifier, (Numeric-valued) can be granted to UIC-value idents.
_IF_ the code checks for identifiers-held (good VMS usance!), then confusing your identifiers might well confuse the code!

So, please keep your account names as well as usernames unique, also from one another, and maintain exactly ONE accountname per UIC-group. This is: set ACCOUNT for every username in the same UIC group to the same value, and have a separate name for each UIC group.

Hope this helps,

Proost.

Have one on me.

jpe

[Ian Miller.]

AFAIK The Account field is normally only used for accounting and reports and does not have much effect otherwise.

[Jan van den Ende]

Ian,

but,
the first time you enter a user in a group, specifying a name for the account field that does not yet translate into an identifier, that name is entered with the special identifier value of [,*] (and /attrib=resource, so it can take ownership etc)
If you thereafter add a username with the same name, then an identifier for that username is NOT created.
And now you are in for all kinds of "interesting" behavior for functionalities that depend on that identifier...

Proost.

Have one on me.

jpe

[Ian Miller.]

Jan, Do you mean if you add a user with a name equal to a name used as an account name? I can see this will cause problems.

I think AUTHORIZE trying to be helpful by adding the identifier with value [group,1777777].

You can modify users in a particular UIC group to have different account names if it suits your environment.

[Jan van den Ende]

Ian, yes.

Look back, THAT was what the OP planned, and what I was warning against.

Proost.

Have one on me.

jpe

[Guinaudeau]

Ian (Miller):

look at my previous remark about the confusion around ORAOMVN user and account name, as long as the account name was ORAOMVN in our user ORAOMV920.

Jan should be correct, it sounds like Oracle code CAN be confused. I think it has been confused. I will anyway pay attention in the future to these issues, with Oracle and with other apps we have.

Jan (the other one):

may be you quote here the link for this issue account/owner/user names in the documentation 7.3-2, thanks in advance. it will be helpfull to me and many, pretty sure.

i look at your suggestion(s). i have now compared our SYSUAF with the one of our colleages of the other department, here so called "they". attached extract of their SYSUAF, one VMS user ORACLE9 is used for both installation and DBA tasks.

they have only one DB on their test systems, and they installed directly 9i, they did not migrate from 8i.

they also have a Galaxy on DS25, we have a single VMS instance (test machine = DS10, customer machine = ES45). this should not be the issue, but :

=> did someone install oracle9i on DS10 machines ? could he confirm it's OK with our platform EV6 / SRM console V6.7-1 ?

they have actually used another setting than we had, and their settings match your advices in some sense.

their config :

1) one user only ORACLE9 is defined, used both to install the product
and to administrate the DB (incl start and stop instance)

2) this user has been defined without owner and account name, both strings are blank

=> i understand now that this UIC is used to create the files. i have been confused with the "owner" field of the SYSUAF configuration.

surprisingly, no SETPRV privilege in their user definition : this corresponds with Oracle9i documentation, but we got the explicit AUDIT privilege failure for it, so i dont understand what happened on our installation ...

3) they did not grant ORA_SGA to the ORACLE9 user : i overlooked that recommendation in Oracle9i installation guide (dec 2002, page 55) since we used to have this id granted to the installer+DBA account in Oracle8i

=> silly error of mine in our SYSUAF !!! will remove

4) they have defined but not granted ORA_ID ; i understand this could be forgotten, is very specific to their installation. i didn't find any trace of this ORA_ID ID under Oracle documentation and
Metalink, also.

5) they have defined and granted ORA_AGENT_ID but this should not be
significant / could be forgotten because the OEMAGENT does not work
properly under VMS.

i will compare further a GET_ENVIRONMENT (if you know that script) output of their machine for every SYSGEN parameters and other system informations etc ...

jan, since we had until now a group for different Oracle DBs, following your advice, i will define a new group for the Oracle9i user, will either not define explicitly owner and account (alike their example of
ORACLE9). or will define new owner and account names not used until now and different from the newly defined user name (alike your suggestion)

just we are waiting for better exchange of information and support between the two installations in our company (same company, but not same network, some stuff N/A just now now). should continue afterwards and update this thread.

thanks to you and every helpers until now. i am ever very pleased to attend this forum, never loose my time.

louis

nota -> jan : found a translator for your "have one on me" and might have the corresponding "HW" if you were close to Essen/Ruhr in Germany or would attend the next bootcamp.

[Wim Van den Wyngaert]

Just to be sure : if you put the account back to the old value, is the problem coming back ?

And fyi : numeric user names are very badly diggested because they are valid but no identifier is created (numeric identifiers names are not supported).

Wim

[Jan van den Ende]

Louis,

for your information: my LAST involvement with Oracle was V6.1-something, on Vax, way back in the times when Oracle was still a native VMS development.

All my remarks were in essence generic VMS remarks, not limited to Oracle per se, and I was not even sure _IF_ they apply in your specific case.
But, they ARE quite generic, and your symptoms seemed to fit, that is why I reacted.

The principle of a new UIC group for each new version of Oracle, with all installation and DBA activity by members of that group, used to be, and probably still is, the thing to do to avoid many problems that might otherwise arise.

Furthe specific Oracle details are probably better dealt with by current ORA users, which are not too rare in this forum.

About Bootcamp: Yes, I _DO_ intend to be there. When in Nashua, I am a regular at the hotel bar, ( just like Ian ), am if you locate me, and make yourself known, we will definitely share a Samuel Adams.

Proost.

Have one on me.

jpe

[Guinaudeau]

wim :

>>> if you put the account back to the old value, is the problem
coming back ?

cannot test this now now. will try this

>>> numeric user names are very badly diggested because they are valid
but no identifier is created (numeric identifiers names are not
supported).

do you mean with numeric user name alpha-num too alike "ORACLE9" ? what a surprise !

louis

[Jan van den Ende]

Louis,


do you mean with numeric user name alpha-num too alike "ORACLE9" ? what a surprise !


oh no, THOSE are perfectly acceptable.

But usernames like
Username: 12345
ARE valid usernames as well, but THOSE can NOT be associated with an identifier.

hth,

Proost.

Have one on me.

jpe

[Guinaudeau]

we first tried to migrate the existing DB.

we will try to run DBCA with the installed product, to compare the results. if new DB can be created, that is at least something and mean a correct product installation and account but something wrong with the migrated DB ! we would prefer migrate to
export/import for that large DB, for sure. (and we red one should forget DBCA tool itself ... sounds incorrect or misunderstood)

*************

ian + jan :

to summary, if i well understand you and the documentation around 732final\AA-PV5MH-TK\AA-PV5MH-TK.HTML :

please confirm or correct following strategy. My goal is to have a clear guideline (= 100% safe) to add a user account for a new Oracle release :

1) check whether the new user name is not already entered as an ID in SYSUAF

2) add the new user with a new group UIC : either w/o account name (alike the ORACLE9 example) or with account=user

=> this account name should not be confusing for VMS, because no previous user (= no existing ID) has been configured in SYSUAF with same name

=> this will add one unique ID, type alphanumeric, both for account name and user name

=> or did i misunderstood your explanation and should really add a new user name X with a new group UIC and a new account name Y ?

=> what about owner ? should i prefer to let owner undefined or to
have it = user name

3) continue with quota, password, privileges as requested by Oracle ; probably add SETPRV (although not documented by Oracle) : sounds to me the minimal list of privileges (auth+default) would be :

CMKRNL GROUP GRPNAM IMPERSONATE LOG_IO NETMBX
PFNMAP PRMCEB PRMGBL PRMMBX SETPRV SHARE
SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX WORLD

4) in Oracle9i, at least, def and dir should be set such a way that it is a new path and new LOGIN.COM which does not run old ORAUSER env.

i dont care about ACL at that time : should i immediately care with them, since we intend eventually to have the Oracle tree to be read accessed as a Advanced Server share (prefer to see those UNIX-like paths and file names from PC, or ?) and we may add for this purpose an ACL ?

[Marc Van den Broeck]

A small remark about your migrate and export/import topic.

We used the Oracle migrate tool for years because we assumed that it is faster then export/import. And if it would work well it is faster indeed but every time we had problems day after day and it took us a lot of time to have a working db.
So, we decided to install the new Oracle version and then export/import the data. It takes more time initially, but it saves us lot of 'problem'-time.

Rgds
Marc

[Guinaudeau]

marc,

interesting remark, the colleagues of our other department suggested to examine further that direction.

due to the DB size and need of temp space, we considered first the migration, but that might be feasible.

louis

[Jan van den Ende · Accepted solution]

Louis,

<-
1) check whether the new user name is not already entered as an ID in SYSUAF
->

Yes,
plus:
1a) Check the UIC group to be unused:

$ MCR AUTHORIZE SHOW [,*]

if this does show ANY username, try another grpnr

1c) $ Check the chosen username:
X
$ MCR AUTHORIZE SHOW
If you get any value returned, choose another one

1c) Check the ACCOUNT name to be not yet used as identifier:

$ MCR AUTHORIZE SHOW/IDENT
If you get any value returned, choose another one

<-
2) add the new user with a new group UIC : (a) either w/o account name (alike the ORACLE9 example) or (b) with account=user
->
(a) If you do NOT specify an account name, than that (like all other not specified fields) is COPIED from the DEFAULT username, ans therefore, not unique!
(b) NO! That would inhibit creating the identifier for the UIC group

For Oracle usernames, it is customary, but not necessary, to choes ORAxxxx, where xxxx relates to the version and/or its use.
eg: ORA9I_PRD or ORA920_TST
Make it easy on yourself, choose an ACCOUNT name that derives from that (but _IS_ different!) eg, ORA9I_P and ORA920_T.
The owner field is documentary only, to clearly identify it to human readers, eg "Oracle 9i Prod database serv" or "Orac 9.2.0 Test database serv" --- feel free to use your own description.

<-
=> this account name should not be confusing for VMS, because no previous user (= no existing ID) has been configured in SYSUAF with same name
->

correct

<-
=> this will add one unique ID, type alphanumeric, both for account name and user name
->

It will add TWO unique IDs, one for the USERNAME and one for the ACCOUNT name.

The will have a both an alphanumeric and a numric value; but the numeric value will normally be formatted as [grpnr,memnr]
( the underlying pure-numeric value can be calculated as ( %X10000 * %Ogrpnr + %Omemnr ), usually represented in Hex ; for the grpname itself use %O377776 as memnr)

<-
=> or did i misunderstood your explanation and should really add a new user name X with a new group UIC and a new account name Y ?
->
THAT is what you do!

<-
=> what about owner ? should i prefer to let owner undefined or to
have it = user name
->

see above. But to leave it undefined, you probably have to specify an empty string, or else it will be a copy of whatever it is for the DEFAULT username

<-
3) continue with quota, password, privileges as requested by Oracle ; probably add SETPRV (although not documented by Oracle) : sounds to me the minimal list of privileges (auth+default) would be :
->

sounds good to me

<-
4) in Oracle9i, at least, def and dir should be set such a way that it is a new path and new LOGIN.COM which does not run old ORAUSER env.
->

YES definitely

<-
i dont care about ACL at that time : should i immediately care with them, since we intend eventually to have the Oracle tree to be read accessed as a Advanced Server share (prefer to see those UNIX-like paths and file names from PC, or ?) and we may add for this purpose an ACL ?
->

you can add those any time you like
(but make sure they propagate correctly trough the entire tree)



Success!

Proost.

Have one on me.

jpe

[Guinaudeau]

jan,

thanks for the long reply : it helps me to understand how it works, in fact i will use this as a "how to configure a new Oracle account" input, just will reformat it for convenience.

nobody replies until now, so it should be definitive or alike, this time ...

every,

many thanks for your help ! this or these forum(s) are great things for VMS users/admins.

status :

after config SYSUAF Oracle9i account + set file /owner + patch 9.install (9.2.0.5, and this automatically relinked oracle executables) we can now now start the DB instance, and are looking at it. sounds pretty good. i dont close immediately the thread.

addon :

- we may just have needed to relink with the right SYSUAF account to get Oracle working ; but they (= our colleges from other department) have experienced and already installed, and eg DBCA could not start without patch install [or rebuild ?]) ; i dont care about it now, useless to try again ...

- i "commit" everything with an offline backup of the DB and tests further with that system and oracle status

- i will in any case let the SETPRV privilege, since it was on our Oracle8i account

- i will verify again with that account name confusion [wims suggestion "what if you put back the account name ..."]

before i close definitively that thread

louis

[Guinaudeau]

i will verify again with that account name confusion [wims suggestion "what if you put back the account name ..."]



after a backup, i have tried different things and i could not reproduce this error : putting back the account name to be the same as another user name which has not SETPRV in default privileges and
removing the account ID [31,1777777]. the DB starts without trouble.

the error may be the result of an incorrect setting of the account during the product installation ? it takes much time and does not worth to look further ...

i learned several things thru that trouble, esp the check-list to create a new user (possibly a new group), and the impact of account name as an additional ID in rightslist database. we are unfortunately used to have user=account name, which appears to be a bad habit, even if it worked apparently well until now.

i may have understood quicker (possibly ?) when i would have looked at SHOW PROC/ALL output, especially privileges and process rights, at the time my problem occured.

thanks for the many helps

louis