Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to upgrade from Oracle 8i to 9i?

Posted on 2004-04-19
6
Medium Priority
?
17,674 Views
Last Modified: 2013-12-11
Hi, All:

Now I want to upgrade from Oracle 8.1.7 to 9.2.0. How and where can I get some documents about this task, especially about the usage of DBUA tools?

Now, I have Oracle 8i installed and have two databases on it. The $ORACLE_HOME is set to /myoracle/8.1.7 for example.

1) I install Oracle 9i to the new path and let Oracle 8i and 9i coexist in the same server. To make sure the existing software using Oracle 9i other than 8i, the $ORACLE_HOME is changed to /myoracle/9.2.0 after the installation. Now, I run $ORACLE_HOME/bin/dbua to upgrade database from 8i to 9i, but it can't find the old Oracle 8i database. Why? How can I do then?

2) If I only want one Oracle instance in the machine, so I install Oracle 9i to the old 8i path. How can I do in this scenario?

Thanks,
0
Comment
Question by:GoldDragon
6 Comments
 
LVL 1

Accepted Solution

by:
ora-dba earned 150 total points
ID: 10860076
Hello,
for the first scenario, the reason you don't see any databases in dbua might be because the ORACLE_SID has been removed from the oratab file. The DBUA reads the oratab file to determine what databases are available on this server. To check and fix this you can edit the oratab file and add the ORACLE_SID: <PATH>  (The oratab file is generally located in /var/opt/oracle or /etc)
If you'd like to follow the second scenario, you need to run the installer to uninstall Oracle 8i software products (it doesn't harm the databases), then install Oracle 9i software using the same old ORACLE_HOME path. Finally you invoke dbua as in scenario 1.
0
 
LVL 12

Assisted Solution

by:catchmeifuwant
catchmeifuwant earned 150 total points
ID: 10865815
0
 
LVL 48

Expert Comment

by:schwertner
ID: 10866639
For this task we are using Export/Import way.

Do full Export on 8i.
Save the dump file on a secure machine (use BINARY transfer!!!)

Install 9.2.0.1 on the new machine. Upgrade to 9.2.0.4


The best you can do is to precreate on 9i the nonsystem tablespaces. because 9i uses another technology. After that create the nonsystem users and assign quota.

After that do schema by schema import:

Example:

CREATE TABLESPACE "TSDAUDIT"
LOGGING
DATAFILE  'E:\oracle_db_main\oradata\bugf\tsdAudit00.dbf'  SIZE 200M
AUTOEXTEND ON    NEXT 200M   MAXSIZE UNLIMITED
PERMANENT   ONLINE
EXTENT  MANAGEMENT LOCAL
;






CREATE TABLESPACE "TSDCONTACTS"
LOGGING
DATAFILE  'E:\oracle_db_main\oradata\bugf\tsdContacts00.dbf'  SIZE 100M
AUTOEXTEND ON    NEXT 100M   MAXSIZE UNLIMITED
PERMANENT   ONLINE
EXTENT  MANAGEMENT LOCAL
;

CREATE USER ICWUSER IDENTIFIED BY ICWUSER
DEFAULT TABLESPACE SYSTEM    TEMPORARY TABLESPACE TSTEMPORARY;

CREATE USER LSCONTACTS IDENTIFIED BY LSCONTACTS
DEFAULT TABLESPACE TSDCONTACTS   TEMPORARY TABLESPACE TSTEMPORARY
QUOTA UNLIMITED ON TSDCONTACTS;

Now import, after transfering the export dump file in BINARY mode:

imp  PARFILE=c:\box\schema_params.dat

The file schema_params.dat:

USERID="sys/reks@bugf as sysdba"
FILE=c:\box\03_22_2004.dmp
SHOW=n
IGNORE=y
GRANTS=y
ROWS=y
FULL=n
FROMUSER=LSCONTACTS,LSCONTAINER,LSDOCUMENTS,LSIDMAP,LSMEDICAL,LSSYSTEM,LSUSER
LOG=c:\box\imp.log

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GoldDragon
ID: 10897447
Thank you for all of your answers. But I face another problems: I do the Oracle upgrade according to the steps in http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96530/upgrade.htm#1009019 and use the dbua tool.

This tool tells me that the upgrade process successful. Need I do the steps in the section "Tasks to Complete After Upgrading Your Database" and section "Tasks to Complete Only after Upgrading a Release 8.1.7 or Lower Database"?

In the file /var/opt/oracle/oratab, I find there is the entry for the database, for example:

demo:/myoracle/9.2.0:Y

But if I want use dbstart to start the database, the error message is

Can't find init file for Database "demo".
Database "demo" NOT started.

I notice that the init file, named "initdemo.ora", is located under /myoracle/8.1.7/dbs. Of cource, Oracle can't find the init file under /myoracle/9.2.0/dbs. How can I solve this problem now?

Thanks
0
 

Author Comment

by:GoldDragon
ID: 10897492
BTW, after upgrade, I find there is a new file /myoracle/9.2.0/dbs/spfiledemo.ora, which is generated by dbua post upgrade steps.

What's the use of this file and how can I use it?

Thanks,
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 270 total points
ID: 10897689
This is the new concept of the old init.ora file.
Now the parameters are stored in the DB in SPFILE (find it!  SHOW PARAMETER SPFILE).
You can not directly edit it.
You shoud create a parameter file
CREATE PFILE='c:/.....init.ora' FROM SPFILE;
edit this file and recreate the SPFILE:
CREATE SPFILE='.....' FROM PFILE='.....';

You can also change the parameters in SPFILE
ALTER SYSTEM SET open_cursors=400 SCOPE=SPFILE

Read these new comands from the manual.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question