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.
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.
Only run catalogs catalog, catproc, catblock,pupbld.sql,hlpbld
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.
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\TE ST\REDO05. LOG' SIZE 200M,
GROUP 6 'C:\oracle\product\10.2.0\ ORADATA\TE ST\REDO06. LOG' SIZE 200M,
GROUP 7 'C:\oracle\product\10.2.0\ ORADATA\TE ST\REDO07. LOG' SIZE 200M,
GROUP 8 'C:\oracle\product\10.2.0\ ORADATA\TE ST\REDO08. LOG' SIZE 200M,
GROUP 9 'C:\oracle\product\10.2.0\ ORADATA\TE ST\REDO09. LOG' SIZE 200M,
GROUP 10 'C:\oracle\product\10.2.0\ ORADATA\TE ST\REDO10. LOG' SIZE 200M,
GROUP 11 'C:\oracle\product\10.2.0\ ORADATA\TE ST\REDO11. LOG' SIZE 200M
-- STANDBY LOGFILE
DATAFILE
'F:\oracle\product\10.2.0\ ORADATA\TE ST\SYSTEM0 1.DBF',
'E:\oracle\product\10.2.0\ ORADATA\TE ST\UNDOTBS _02.DBF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\CWMLITE 01.DBF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\DRSYS01 .DBF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\EXAMPLE 01.DBF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\INDX01. DBF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\ODM01.D BF',
'G:\oracle\product\10.2.0\ ORADATA\TE ST\TOOLS01 .DBF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\USERS01 .DBF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\XDB01.D BF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\PREMIAD MG_01.ORA' ,
'F:\oracle\product\10.2.0\ ORADATA\TE ST\DISCEUL .ORA',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\SYSTEM0 2.DBF',
'G:\oracle\product\10.2.0\ ORADATA\TE ST\TOOLS02 .DBF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\PREMIA0 3.DBF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\PREMIAN EW.DBF',
'H:\oracle\product\10.2.0\ ORADATA\TE ST\PREMIA_ INDEX_01.D BF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\PREMIA0 2.DBF',
'E:\oracle\product\10.2.0\ ORADATA\TE ST\UNDOTBS _01.DBF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\IVR01.D BF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\BVDW_DA TA.DBF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\BVDW_IN DX01.DBF',
'F:\oracle\product\10.2.0\ ORADATA\TE ST\PREMIAN EW2.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?
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\
GROUP 6 'C:\oracle\product\10.2.0\
GROUP 7 'C:\oracle\product\10.2.0\
GROUP 8 'C:\oracle\product\10.2.0\
GROUP 9 'C:\oracle\product\10.2.0\
GROUP 10 'C:\oracle\product\10.2.0\
GROUP 11 'C:\oracle\product\10.2.0\
-- STANDBY LOGFILE
DATAFILE
'F:\oracle\product\10.2.0\
'E:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'G:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'G:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'H:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'E:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
'F:\oracle\product\10.2.0\
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.