upgrade ora 9i to 10g

[Former Member]

Hi

I am having problem in database upgrade from 9i to 10g.

I started DBUA (database upgrade assistant) and and my 9i database was down .THe DBUA poped up to inform that the DB is not available.

I stated the database and continued the upgrade.Then it prompted that the shared_pool_size is to be increased.I stopped upgrade assistant and made changes in the initsid.ora file and stated the and restated the DBUA.

now the whenever the dbua is stated by me it is giving a message that the

1 ."you dont have operating system authentication and specify a user with sysdba privilegde."

Initially it never asked for user id and password.

Then I give the user id and password and press next

it gives an error

2 ."the oracle home /oracle/C11/920_64 for database "C11" does not exist"

Please note that the existing database and sap is working fine without any problem but DBUA is giving error 1&2.

Please help to resolve this issue.

Kind Regards

Amit

[Former Member]

Hi

In the upgrade guide nothing is mentioned about catproc and catalog scripts.

The DBUA is supposed to execute and complete this task but some how it did not completed either the connection got disconnected or something went wrong.

Though I executed the catproc manually and is running for the last 1 hour?Is it supposed to run that long?

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning and Data Mining options

SQL> @catalog.sql

DOC>######################################################################

DOC>######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error and terminate the SQLPLUS session if the user is not SYS.

DOC> Disconnect and reconnect with AS SYSDBA.

DOC>######################################################################

DOC>######################################################################

DOC>#

no rows selected

I ran also catproc.sql also but failed with ora -03113 end of communication file.

Let me know your thought on this.

Any way thankyou for your kind reply.

Amit

[former_member204746]

it all depends on the speed of your hardware.

you should revive your Oracle database upgrade logs for any errors. Personally I often upgrade my database manually through SQL scripts instead of using the DBUA. this is why I though that you had to run catalog.sql script.

[Former Member]

Hi Stefan,Joe

I managed to run the DBUA woithout any problem .

I left the upgrade process running and came back to check .I tried to start the database with 10g.

I was able to mount database but open database was failing.

I ran the sqlplus while db was in mount position.

sql>select version from V$instnace

10.2.0.2.0

but the alert log file was giving the error

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

I started the database with sql

startup upgrade and ran below sql.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 616562688 bytes

Fixed Size 2052200 bytes

Variable Size 562040728 bytes

Database Buffers 50331648 bytes

Redo Buffers 2138112 bytes

Database mounted.

Database opened.

SQL> SQL> select comp_id, version ,status from dba_registry;

COMP_ID VERSION STATUS

-

---

-

---

-

---

CATALOG 9.2.0.4.0 VALID

CATPROC 9.2.0.4.0 VALID

SQL> select version from V$INSTANCE;

VERSION

-

---

10.2.0.2.0

SQL>

It is showing the db as desired but the

COMP_ID VERSION STATUS

-

---

-

---

-

---

CATALOG 9.2.0.4.0 VALID

CATPROC 9.2.0.4.0 VALID

How to correct the catalog and cataproc version to 10.2.0.2.0?

do i need to do something after that.

Let me know please.

Regards

Amit

[former_member204746]

read the upgrade guide, it will tell you what script to run.

search for CATPROC

[Former Member]

I read same upgrade guides and using same variables as mentioned by you.I stated DBUA from /oracle/C11/102_64 .

Since you asked me to provide the output of sql>shutdown and startup

It is not possible from 102_64 environment as it will throw an error oracle not available.

Do you think I delete the 102_64 installation and redo the installation will help in it.I beleive the problem is in structure some where not in database.

Let me know please if you have some other suggestion.

Regards

Amit

[stefan_koehler]

Hello Amit,

> I stated DBUA from /oracle/C11/102_64

That is not enough. You have to set the specific environment variables too (like described above and in the upgrade guide). After that it should work.

> I beleive the problem is in structure some where not in database.

No the error is in the environment. Because the DBUA does not fit to the binaries/libraries that are used.

Regards

Stefan

[Former Member]

Hi Stefen

Please find the details as requested by you.

1 >shell> su - ora<SID>

celerp2g:orac11 1>

2>shell> cat $ORACLE_HOME/rdms/lib/config.c | grep GRP

celerp2g:orac11 8> cat config.c

/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */

/* Refer to the Installation and User's Guide for further information. */

#define SS_DBA_GRP "dba"

#define SS_OPER_GRP "oper"

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};

shell> id

uid=109(orac11) gid=108(dba) groups=107(oper)

shell> sqlplus "/ as sysdba"

celerp2g:orac11 11> sqlplus /"as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jan 9 12:32:38 2009

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

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.4.0 - Production

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 5161961472 bytes

Fixed Size 727040 bytes

Variable Size 268435456 bytes

Database Buffers 4891475968 bytes

Redo Buffers 1323008 bytes

Database mounted.

Database opened.

SQL>

[stefan_koehler]

Hello Amit,

thanks - the group definition looks fine - but why are you still executing Oracle 9.2.0.4.0?

Which environment do you use for the DBUA?

Please check the Upgrade Guide carefully -> here are the two points:

Page 30/50

Make sure that the oratab file contains the following entry: <DBSID>:<old_ORACLE_HOME>:N

Where old_ORACLE_HOME stands for the path to the old oracle software installation.

Page 31/50

Make sure that you start the DBUA in the Oracle 10.2 user environment. To do this, carefully check

the first point below. Confirm that the following environment variables point to Oracle 10.2 directories:

• ORACLE_HOME points to /oracle/<DBSID>/102_64

• PATH

• ORA_NLS10

• LD_LIBRARY_PATH or SHLIB_PATH or LIBPATH

Regards

Stefan

[Former Member]

Hi Stefen

I have included the orasid into the DBA group and problem remains the same.

Regards

amit

[stefan_koehler]

Hello Amit,

that makes no sense.

Please post the output of the following commands:

shell> su - ora<SID>
shell> cat $ORACLE_HOME/rdms/lib/config.c | grep GRP
shell> id
shell> sqlplus "/ as sysdba"
SQL> startup
SQL> shutdown immediate

Regards

Stefan

[Former Member]

ok . Stefen

I will add these id's in the dba group and try again.

I will inform you guys soon.

Kind Regards

Amit

[Former Member]

I am able to start my old db and sap.Then group assignment for user can never be worng,

my users in the groups are as below

oper::107:orac11,c11adm,orac21,c21adm

dba::108:c11adm,c21adm

Let me know if you have some different setting.

Kind Regards

Amit

[stefan_koehler]

Hello Amit,

> oper::107:orac11,c11adm,orac21,c21adm

> dba::108:c11adm,c21adm

Ok in this case, the ora<SID> users are missing in the group dba. You can only login (local) with SYSDBA privileges, if the user is in the group dba (default configuration).

Michael Braunstein also writes a nice blog about that topic:

/people/michael.braunstein/blog/2007/10/28/db-connect-mechanisms-in-oracle-and-sap--part-i

Regards

Stefan

[Former Member]

Hi Joe

I am using oraSID to start DBUA.

if you read my request ,I mentioned that I started DBUA at the time when my 9i db was down.

but at that time it did not prompt for any userid and password.This was my first round execution of DBUA.

The Second time I started DBUA and it did not asked for any userid and password and DBUA prompted a warning that shared_pool_size needs to be increased nnnnn.I increased the value and restrated the DB to activate the parameter change.

Then Third time when I ran dbua ..it is asking userid and password.

Kind Regards

Amit

[Former Member]

I had no problem initially when I ran dbua and sure env variables are set properly.

Thanks and Regards

Amit

[Former Member]

Please clarify:

What exactly do you mean by I had no problem initially when I ran dbua ?

Usually you run dbua only once for each SID. Once you have upgraded your SID from 9i to 10g, there will be no need to do it again. Or what am I missing here?

In case the previous runs where for other SIDs: I'd say this SID or this database now is set up differently. You will have to find out.

You didn't answer the question whether you are using user ora<sid>, btw.

Is user ora<sid> a member of dba group? The error message seems to indicate it isn't.

Another thing that has to be checked when restarting dbua:

Make sure that your oratab file still has the old Oracle home directory. The first start of dbua may have changed it.

hope this helps

[Former Member]

Did you start dbua as user ora<sid>?

Please make sure environment variables at operating system level are set according to the upgrade guide.