Link to home
Start Free TrialLog in
Avatar of junegui
junegui

asked on

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.
Avatar of LindaC
LindaC
Flag of Puerto Rico image

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.
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.
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.
Avatar of junegui
junegui

ASKER

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?



Put the sql into a file and run the file instead of pasting the sql directly onto SQLPLUS.
Avatar of junegui

ASKER

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 ?
ASKER CERTIFIED SOLUTION
Avatar of Computer101
Computer101
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial