need help on upgrade 1

[maswien]

Hi,

This is a real trouble for me! I didn't aware of the messy before I started doing upgrade for a 9i database on a wondows server. I made a cold backup for the database files as well as the controls files and redo files, then I started the DBUA, it fails in the middle.

Now I realized I can't just copy back the cold backup files to the original directory, because the database has been changed to a middle state, I can see there are some items for the DB in the 10G home from the registry.

What I should do to restore it back? What I can think of is as following:

1. 10G dbca to remove the bad DB
2. 9i dbca to create a 9i DB using the same name
3. shutdown the 9i database
4. copy back all the backup files to overwrite the newly created database files


Any suggestion? Thanks

 

[SantaMufasa]

Maswien,

I infer from your post that you want to upgrade a 9i database to a 10g database, is that correct? If so, then I have always had best success by creating export dump files from the old database, then importing the dump files into the new database.

Trying to upgrade an old version of an Oracle database's database files has always become an exercise in frustration in my experience.

So, yes, I would do the following:[ul][li]Get the 9i instance back up and running with your backup data files,[/li][li]Export the application schemas (to individual dump files, I recommend)[/li][li]Create the new, empty 10g database.[/li][li]Create empty USERs whose names match the 9i users.[/li][li]Import the individual dump files from 9i.[/li][/ul]Please advise us how this process goes for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[maswien]

Hi Mufasa,

I didn't do export so I have no way to upgrade it to 10G now.

The current issue is to restore it back, the problem is the windows OS, I can't just copy back evrything. I need to take care of windows service, registry stuff, I'm fear maybe I miss something when restore the DB.

 

[SantaMufasa]

Step 1: Remove Oracle Windows Service:

c:\ oradim -delete -sid <current instance name>

Step 2: Uninstall Oracle: Run Oracle Installer, and choose "Uninstall Oracle" option.

Step 3: Confirm that all Oracle-installation-related registry keys are gone:

c:\ regedit
My Computer -> HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE
(delete this last key and its children).

Step 4: Confirm that all System Variables that relate to Oracle are appropriate.

Start -> Control Panel -> System -> Advanced -> Environment Variables -> (System variables)

LOCAL = <your Oracle 9i instance>
ORACLE_HOME = <the path for your Oracle 9i software>
ORACLE_SID = <your Oracle 9i instance>

Step 5: [b]Re-install Oracle 9i[/b]. Using the Oracle 9i Installer.

Step 6: [b]Re-establish your Oracle 9i Database.[/b] Since you have backup files of your entire Oracle 9i Database, you should be able to simply place the files back on the drives and in the paths where you had them before. You should be able to startup the database as before.

Step 7: [b]Export the Oracle users you want on the new 10g database[/b].

Step 8: (Follow steps 1-5, above, but for Oracle 10g.)

Step 9: [b]Create your 10g database and users[/b].

Step 10: [b]Import the dump file created during Step 7.[/b]

Let us know if you have questions about the above process.

 [santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: [b]www.dasages.com[/b]]

 

[jasper06]

Maswein

After completion of upgrade to oracle 10g database make sure to recreate synonyms for your users otherwise you will see table does not errors!!!

 

[maswien]

Thanks a lot Mufasa and Jasper!

The real life situation is just always complicated. I'm doing the upgrade on a less critical 9i database, this means we have another 9i production database which is more critical in the same box. I can't unintall oracle 9i, even worse I can't even reboot the box, so when I used oradim to delete the corrupt database during the upgrade, I got error message like ' the service has been marked as deletion'. Looked the documentation and come to a solution is to reboot the box. I need schedule down time and notify users for this.

Any better ideas than reboot the box?

 

[KenCunningham]

Not much help, but sometimes I'm afraid you just have to grasp the nettle and schedule some down time!

I want to be good, is that not enough?

 

[maswien]

Thank you everybody!

I finally restored the database using RMAN, it's amazing tool! The 100G database restore just matter of a couple of hours on a windows box! I'm sure I want use it forever!

And I found why the upgrade failed, the DBUA reied to create a account 'DMSYS' which uses TEMP as the temporary tablespace, and there isn't a temporary tablespace named 'TEMP' in the database, so the create user fails and then cause the upgrade failed. how is that possible? Doesn't make sense to me, anyone know this DBUA problem?