best way to clone 9i database from window old oracle 9i server to 10g in new window Oracle server

What is the best way to clone database in Oracle 9i window sever to Oracle 10g window server(new server)? Use DBCA(database creation assiatant? or from DOS command line? I have 15 tablespaces and multiple schemas? If I user DBCA, do I need to create all tablespaces and users in 10g first before I import 9i database ? Thanks.
juneguiAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Computer101Connect With a Mentor Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0
 
LindaCCommented:
1- Install oracle product 10.2 in windows and apply latest patchsets and latest Cpu patches (Critical update patchsets)
2- Create the same name tablespaces as the 9i, but with the difference of letting oracle manage the space for you.
3- Create users - roles as in 9i
4 - Import the schemas to 10g.
5- Create the scripts to automate the cleanup of old traces, logs in the windows server using forfiles.
6- Create the listener with another port number that is not 1521 or 1523 or 1528.  
7-  Change the "default passwords" of your users.  Except the ones that would be "hard coded" in your company applications.
0
 
LindaCCommented:
Also I should add to create db manually in the Dos prompt.
Only run catalogs catalog, catproc, catblock,pupbld.sql,hlpbld.sql helpus.sql;
Automate your daily exports.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
virtucondgCommented:
Easiest way is to use DBCA to create your database and users.  Then do a full export/import of the database to move objects and data.
0
 
juneguiAuthor Commented:
Thanks for responding. I tried both ways to create db--1. through DBCA ,2 Command line.
 If I use  DBCA to create database,  do I have to create all users and tablespaces before I import the exp file? Also, DBCA automatically create data filesa and tablespacesd on C drive, but my 9i control file like :
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "BVPROD" NORESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 10663
LOGFILE
  GROUP 5 'C:\oracle\product\10.2.0\ORADATA\TEST\REDO05.LOG'  SIZE 200M,
  GROUP 6 'C:\oracle\product\10.2.0\ORADATA\TEST\REDO06.LOG'  SIZE 200M,
  GROUP 7 'C:\oracle\product\10.2.0\ORADATA\TEST\REDO07.LOG'  SIZE 200M,
  GROUP 8 'C:\oracle\product\10.2.0\ORADATA\TEST\REDO08.LOG'  SIZE 200M,
  GROUP 9 'C:\oracle\product\10.2.0\ORADATA\TEST\REDO09.LOG'  SIZE 200M,
  GROUP 10 'C:\oracle\product\10.2.0\ORADATA\TEST\REDO10.LOG'  SIZE 200M,
  GROUP 11 'C:\oracle\product\10.2.0\ORADATA\TEST\REDO11.LOG'  SIZE 200M
-- STANDBY LOGFILE
DATAFILE
  'F:\oracle\product\10.2.0\ORADATA\TEST\SYSTEM01.DBF',
  'E:\oracle\product\10.2.0\ORADATA\TEST\UNDOTBS_02.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\CWMLITE01.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\DRSYS01.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\EXAMPLE01.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\INDX01.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\ODM01.DBF',
  'G:\oracle\product\10.2.0\ORADATA\TEST\TOOLS01.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\USERS01.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\XDB01.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\PREMIADMG_01.ORA',
  'F:\oracle\product\10.2.0\ORADATA\TEST\DISCEUL.ORA',
  'F:\oracle\product\10.2.0\ORADATA\TEST\SYSTEM02.DBF',
  'G:\oracle\product\10.2.0\ORADATA\TEST\TOOLS02.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\PREMIA03.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\PREMIANEW.DBF',
  'H:\oracle\product\10.2.0\ORADATA\TEST\PREMIA_INDEX_01.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\PREMIA02.DBF',
  'E:\oracle\product\10.2.0\ORADATA\TEST\UNDOTBS_01.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\IVR01.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\BVDW_DATA.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\BVDW_INDX01.DBF',
  'F:\oracle\product\10.2.0\ORADATA\TEST\PREMIANEW2.DBF'
CHARACTER SET WE8MSWIN1252
;

when I used command line to create db, when I created the control file, I got an error 'SQL> DATAFILE
SP2-0042: unknown command "DATAFILE" - rest of line ignored'

any thoughts?



0
 
sathyagiriCommented:
Put the sql into a file and run the file instead of pasting the sql directly onto SQLPLUS.
0
 
juneguiAuthor Commented:
Thanks. Continue with virtucondg's answer. Can I import the entire 9i database with system user into Oracle 10g without creating other users and schemas and tablespace? In anotherr word, my export ifle is for entire databse, is the import utility able to import all users and tablespace with their data ?
0
All Courses

From novice to tech pro — start learning today.