Re: Problem after my migration from 8.1.7 to 9.2

From: cschang <cschang_at_maxinter.net>
Date: Tue, 15 Mar 2005 22:12:06 -0500
Message-ID: <113f90n6j9hp63b@corp.supernews.com>

Thierry PLASSART wrote:

> Hi,
> 
> there's an other point regarding accounts: assuming we have an account in
> 8.1.7 with connect and resource grants and 'select any table', you'll have
> to add in 9.2 the 'select any dictionnary'.
> That should be usefull for querying sys tables (to have execution plans...)
> 
> T PLASSART
> 
> "cschang" <cschang_at_maxinter.net> a écrit dans le message de
> news:113cklckst66743_at_corp.supernews.com...
> 

>>After I upgrade my 8.1.7 to 9.2.0.1 with the oracle upgrade assistant,
>>the new instance has problem to start itself. During the upgrading
>>there were three steps showing alert messages. First, about the NCHAR
>>issue, which is recommended to resolve before continuing the upgrade. I
>>chose to continue. Second. the upgrade result showed that I had an
>>ora-28003: password verification for the specified password failed.
>>ORA-20001: password same as user.. The third. I skip the installation
>>of the Enterprise Manager Configuration Assistant because it took too
>>long (one days). The problem is that after I rebooted the machine, I
>>cannot use the user, which was granted as full resource and dba role in
>>old 8.1.7 schema. (not sys user) to login. The error message at
>>sqlplus was ORA-01034: “Oracle not available” and ora-27101: “shared
>>memory realm does not exist”. And with SYS user connected, it said that
>>“connect to an idle instance”. When I used the Oracle enterprise manager
>>with user, there was an error pop-up “ The application requires you have
>>been granted the SELECT_CATALOG_ROLE….”. I think I understand what
>>could cause this. Maybe it was the old 8.1.7 instance still there and
>>the startup got confused. But I do not know where I should do to
>>resolve it. Do I need to remove the 8.1.7? or copy something from
>>8.1.7 %home_dir% to 9.2 %home_dir%. I thought the Upgrade Assistant
>>was supposed to do all those. I have read the chapter 3 and 4 of the
>>Oracle Migration document few times, especially the chapter about after
>>migration. However nothing mentioned except to upgrade the NCHAR, which
>>I did after the upgrade. I have to manually startup the 9.2. instance.
>>
>>C Chang

> 
> 
> 
Here is the way I resolved the problem with tips from you and other on-line
1.	sqlplus "/ as sysdba"
2.	sql> show parameter spfile
3.	sql> create pfile='d:\path\pfile\init.ora' from
spfile='d:\oracle\ora92\database\spfileinit.ora';
4.	sql>exit
5.	Stop the OracleService under the MS windows 2000 services
6.	cmd>oradim -delete -sid mySID
7.	remove the all files under the %oracle_home%\admin\ddump\ and udump
8.	remove the PWD.ora under the 'oracle\ora92\database
8a.      copy the new init.ora to d:\path\pfile\init.ora
9.	cmd>set oracle_sid=mySID
10.	cmd> oradim -new -sid mySID -intpwd <sys pw> -startmode auto -pfile
d:\path\pfile\init.ora
11.	sqlplus "/ as sysdba"
12.	shutdown
13.	startup

and then login as sys as sysdba in the Oracle Manager console and then give the "SELECT ANY DICTIONARY" to that problem user. Now every thing for regular user is fine for both login at console and SQL mode.

However, there is still two issued I can not resolve. 1. It seems that the client machine with the Oracle Manager console of 8.1.7 version can not log in with SYS user. The window has no action. Only regular user can still use the old Console. 2. If I type "show parameter spfile" at the SQL>, it will return the location of the spfile. However, if I reboot the machine, then type it again, the spfile location value is null again. 3. Do I need to remove the old 8.1.7 home folder under the \oracle\ora81, so that it won't cause any confusion to operate. Thanks.

C Chang Received on Tue Mar 15 2005 - 21:12:06 CST