How do I move a Oracle 9iR2 DB from one server to a new one?

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Some good info here. Thanks. Will look into it real soon. Will get back to give points/ask more.

As a sidenote: I have 9.2.0.6 already installed on the new server.. it's just sitting there waiting for data (the database) :D

ASKER

Oracle is just not very easy for a Oracle newbie :( I'm mostly used to MySQL when it comes to db software.

Anyway.. I'm doubling the points and hopefully it will inspire to help me getting a (small) grip on Oracle installation++ :)

Here is what I've done so far on the new server:

- Installed Oracle 9.2.0.6 on RHEL4ES (2GB RAM) using the guide at http://www.puschitz.com/InstallingOracle9i.shtml#RunningOracleInstallationOnRHELAS4
- $ORACLE_SID is set to "DBIDS"
- $ORACLE_HOME is set to "/u01/app/oracle/product/9.2.0"
- Used 'dbca' to create a database. I guess it is named DBIDS(.localhost). I used only defaults, except I configured the database to use 80% of system memory (this server will only run Oracle).
- Copied /u01/app/oracle/admin/DBIDS/pfile/init.ora to /u01/app/oracle/product/9.2.0/dbs/initDBIDS.ora
- shutdown and startup migrate, and connected as sysdba to run @/u01/app/oracle/product/9.2.0/rdbms/admin/catpatch.sql

config files I've looked at/edited:

/etc/oratab:
------------
*:/u01/app/oracle/product/9.2.0:N
DBIDS:/u01/app/oracle/product/9.2.0:Y

/u01/app/oracle/product/9.2.0/network/admin/listener.ora:
---------------------------------------------------------
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbora.soc47.secode.net)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = DBIDS.localhost)
(ORACLE_HOME = /u01/app/oracle/product/9.2.0)
(SID_NAME = DBIDS)
)

/u01/app/oracle/product/9.2.0/network/admin/tnsnames.ora:
---------------------------------------------------------
DBIDS.LOCALDOMAIN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.160)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBIDS.localhost)
)
)

/u01/app/oracle/product/9.2.0/network/admin/sqlnet.ora:
---------------------------------------------------------
NAMES.DEFAULT_DOMAIN = localdomain

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

I can tnsping from the old Oracle server to the new one, so I guess it's working so far.
So far so good? I've probably forgotten something or done something wrong.. Please advise.

Typing 'sqlplus' on the console of the server results in me being asked for a username and password.
Using scott/tiger results in "ORA-01017: invalid username/password; logon denied". Why? This works on my old Oracle server.
Using system/<password> works, where <password> is the password I set when I used dbca.

Using "sqlplus /nolog" and then "connect / as sysdba" gets me into the database without any password? Why no password? Isn't this bad for security? It probably isn't, since this is a well know command etc. Any explanation?

Am I ready for export and import (described above in answers from Metanil, schwertner and markgeer)?

You logged on as 'oracle' user that is member of the OS 'orainstall' or 'dba' group.
Now you can change the password
SQL>alter user sys identified by newpassword;

There are two major ways to migrate to 10g:
1. Using the Migration Assistant
2. Using Export/Import

I did many migrations from 8i to 9i using Export/Import.

Install 9i and create the seed DB.

Create the NONSYSTEM tablespaces and users:

CREATE TABLESPACE "TSIMEDICAL"
LOGGING
DATAFILE 'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TSIMEDICAL00.DBF' SIZE 100M
AUTOEXTEND ON NEXT 100M
MAXSIZE 32768M
PERMANENT ONLINE
EXTENT MANAGEMENT LOCAL
SEGMENTSPACE MANAGEMENT MANUAL;

CREATE TABLESPACE "TSDMEDICAL"
LOGGING
DATAFILE 'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TSDMEDICAL00.DBF' SIZE 100M
AUTOEXTEND ON NEXT 100M
MAXSIZE 32768M
PERMANENT ONLINE
EXTENT MANAGEMENT LOCAL
SEGMENTSPACE MANAGEMENT MANUAL;

CREATE TEMPORARY TABLESPACE "TSTEMPORARY"
NOLOGGING
TEMPFILE 'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TSTEMPORARY00.DBF' SIZE 100M
AUTOEXTEND ON NEXT
100M MAXSIZE 32768M
TEMPORARY ONLINE
EXTENT MANAGEMENT LOCAL
SEGMENTSPACE MANAGEMENT MANUAL;

CREATE USER LSMEDICAL IDENTIFIED BY LSMEDICAL
DEFAULT TABLESPACE TSDMEDICAL TEMPORARY TABLESPACE TSTEMPORARY
QUOTA UNLIMITED ON TSDMEDICAL;
ALTER USER LSMEDICAL QUOTA UNLIMITED ON TSIMEDICAL;
ALTER USER LSMEDICAL QUOTA UNLIMITED ON TSTEMPORARY;

GRANT create procedure, create session,create table,create type,create view,create synonym TO LSMEDICAL;

After that do an export (full or schemas level) on 7/8i using Oracle 7 Export Utility.

Transfer the dump file in BINARY mode to the 9i instance machine.

Using 9i Import import the nonsystem schemas.

Thats all.

ASKER

schwertner - thanks for posting, but I think there are some misunderstandings here. The old Oracle is 9.2.0.1, and my new server is 9.2.0.6. And I'm using linux (RHEL4ES).

Seems Scots account is locked.
Go to the OEM and unlock it.
% oem console

I didn't see misunderstanding. Export dump files can be used across platforms and even oracle releases (the last one only upwards). The only prerequisite is to transfer the dump file using binary mode.

ASKER

Thanks for clearifying.

oem console did not work

I tried to unlock the scott user using sqlplus:

<snip>
[oracle@dbora ~]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.6.0 - Production on Sun Nov 27 19:49:16 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> alter user scott account unlock;
alter user scott account unlock
*
ERROR at line 1:
ORA-01918: user 'SCOTT' does not exist
</snip>

Looks like the user does not exist.. Could I have messed up the installation?

SQL> select username from dba_users;

USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
OUTLN
WMSYS
ORDSYS
ORDPLUGINS
MDSYS
CTXSYS
XDB
ANONYMOUS

USERNAME
------------------------------
WKSYS
WKPROXY
ODM
ODM_MTR
LBACSYS
OLAPSYS

17 rows selected.

ASKER

I tried to export from the old server:

exp SYSTEM/password FULL=y FILE=dba.dmp GRANTS=y ROWS=y

It was not that successfull:

<snip>
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
EXP-00056: ORACLE error 600 encountered
ORA-00600: internal error code, arguments: [kpotcpop1], [], [], [], [], [], [], []
ORA-19206: Invalid value for query or REF CURSOR parameter
EXP-00056: ORACLE error 600 encountered
ORA-00600: internal error code, arguments: [kpotcpop1], [], [], [], [], [], [], []
ORA-19206: Invalid value for query or REF CURSOR parameter
EXP-00000: Export terminated unsuccessfully
</snip>

I search a bit but did not find a good answer/solution.

Also, is the "CREATE TEMPORARY TABLESPACE" statement correct for Oracle 9iR2? I tried this:
<snip>
CREATE TEMPORARY TABLESPACE "IDS_TEMP"
TEMPFILE '/u01/app/oracle/oradata/DBIDS/ids_temp_01.dbf' SIZE 500M
TEMPORARY ONLINE;
</snip>

and it resulted it an error "ERROR at line 3: ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE".

Often this happens when you have more then one Oracle product installed on the same machine (e.g. Oracle Server and Application Server). In this case often invokibg Export you use the export of the App. server (an significant elder version) instead the export executable of the Oracle server. the old export version can not deal the new database structure and fails in the way you describe. I fall in this trap last Friday importing a Database and 4 hours were very upset ...

Also try to export/import nonsystem users and precreate nonsystem users and tablespaces before import.
Set good values in NLS_LANG environt variables in both servers - this is very important not to lose non-english letters.

CREATE TEMPORARY TABLESPACE "TSTEMPORARY"
TEMPFILE '/u02/oradata/oradata/o10f/tsTemporary00.dbf' SIZE 100M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1024K
;

works with 9i and 10g.

Temporary tablespaces should be always online.

ASKER

Schwertner - the last "create temporary tablespace" command worked :) Thanks. I did however use dbca at first to create my DBIDS database, and in the folder there is a temp01.dbf already for temp. Would you recommend not using dbca at all to create the database files?

As you probably have come to understand.. I'm no Oracle expert. I'm not sure what you mean by nonsystem users and tablespace ("Also try to export/import nonsystem users and precreate nonsystem users and tablespaces before import.").

About the exp function and more than one Oracle product - I think it's one one oracle product on the old server. I'm not the one that installed it some time back. I only found one 'exp' file:

/opt/oracle/product/9.2.0.1.0/bin/exp

Do I need to shutdown the database and start it in a speacial mode to do exports perhaps?

Upping the points a bit again.. :)

If dbca works, it's fine to use that. Schwertner and I have been working with Oracle long enough to know how to create a database without using Oracle's GUI tools, but for people new to Oracle, the GUI tools are handy - when they work! (They don't always work for me.)

By "non-system" users, he means those user accounts that have been created for your application after the initial Oracle install (which creates some "system" accounts with names like: SYS, SYSTEM, SCOTT, etc.). A full export will include all "non-system" user accounts and their data.

The tablespaces he referred to are any that your application needs that are different than the default tablespaces created during the initial database creation (these include: SYSTEM, TEMP, USERS, UNDO, etc.). Most Oracle DBAs create tablespaces with other names (for example: table1, table2, index1, index2, etc.) for the tables and indexes needed for your application.

Do I need to shutdown the database and start it in a special mode to do exports?" No. An Oracle database must be open to do an export. (It is possible to have the database in a special "open restrict" mode and then do an export with no other user processes active, but this is not typical.)

ASKER

markgeer - Thanks for clearing up some stuff. Not that easy for a oracle newbie. I guess the most important problem now is my errors when doing export from the old oracle server. Don't happen to know what to do about it?

I checked that ORA-00600 ...kpotcpop1 error on Oracle's Metalink web site, and a couple of bug reports mention it, but there is no suggested work-around or fix. I would suggest trying a user-level export rather than a full database export to see if that avoids the problem. If that fails, try a table-level export of individual table(s).

When you do export check what export utility are you using!
Sometimes there are more then one Oracle homes (complete set of installed Oracle products) and I as said one use an elder or newer Export version. Believe me ... it is important.

Its not clear to me what schwertner meant by this: "use an elder or newer Export version". Usually the version of export must match the version of the database being exported. It is sometimes possible to use a newer version of export (from a different Oracle_home), but it will not work to use an older version of export than the version of the database being exported.

If there is just one Oracle_home on the server, then it is simple: just use the one version of Oracle export on the server which is then guranteed to be the same version as the database.

When export succeeds and creates a *.dmp file (or multiple *.dmp files) the version of import that is used to process it/them must be the same Oracle version that was used to produce the export, or a higher version. A lower version of import WILL NOT work with a *.dmp file from a higher version of export.

ASKER

schwertner:
<snip from my earlier post>
I only found one 'exp' file:

/opt/oracle/product/9.2.0.1.0/bin/exp
</snip>

markgeer:
Think I have to go for a "user-level export" next. And again I don't know exactly what that is.. Will try google and docs on exp/imp.

ASKER

I tried to do a user-level export adding "OWNER=username" to the exp command, but it failed (see below). My exp cmd, is it right?

<snip>
[oracle@linux oracle]$ exp SYSTEM/>password> OWNER=IDS FILE=OraDUMP-Test.dmp LOG=OraDUMP-Test.log GRANTS=Y ROWS=Y

Export: Release 9.2.0.1.0 - Production on Thu Dec 1 02:21:28 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user IDS
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user IDS
About to export IDS's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 600 encountered
ORA-00600: internal error code, arguments: [kpotcpop1], [], [], [], [], [], [], []
ORA-19206: Invalid value for query or REF CURSOR parameter
EXP-00000: Export terminated unsuccessfully
</snip>

I wiil suggest first to upgrade 9.2.0.1 DB at least to 9.2.0.4 (also possible to 9.2.0.6).
It is suicide to use 9.2.0.1 and 10.1.0.... software. Please believe me! These initial releases are so
bugy.....
Take in account that you have not only to upgrade the software using OUI but also to patch the dictionary tables (see the instruction in the patch set and execute catpatch.sql script).

@Mark:
The trap I sat on Friday was:
1. I have installed 9.2.0.7 on my laptop
2. I have installed Oracle Developer Suite 10g after that.
3. I decided to import an development DB because I had to travel to Germany and due some
security reasons do not will to use local DBs for tests
4. The import fails with ORA-600.
5. The reason was that by default an 9.0.1 Import executable was used because Dev Suite 10g is
provided with these old utilities. And Import 9.0.1 was not able to import the 9.2.0.7 Export dump file.
6. Fix ----> in the Command Prompt describe the full path to the 9.2.0.7 Import executable. After 4 hours efforts I did this and Import run successfully.

Yes, that exp syntax is one way to do a user-level export. I would usually do that by logging in as the user I'm trying to export, rather than by logging in as SYSTEM with an "OWNER=..." parameter, but either way should work. The main problem may be the 9.2.0.1 software version. You may have to upgrade to 9.2.0.6 to get export to work.

ASKER

Ouch.. I think I'm out for a real adventure here. Guess there will be some major downtime here. The database I'm going to move is heavily used 24/7.

Looks like it's the only way, updating to 9.2.0.4 (or 9.2.0.6) first. Would it be wise to ditch 9.2.0.6 on the new server and just go for the latest Oracle 10g R2? If so, why (a few reasons please)?

Looking at the help I've got so far to fix my problem and what I'm in for.. I'm upping the points again :) I'll probably gonna splitt it on you guys when I'm done. Thanks so far.

Before you ditch 9.2.0.6 on your new server, try creating a database link to the current server. If that succeeds, that would give you an alternate way to get the data over to the new server without having to apply an Oracle patch on the old server first. To create a database link, you need to do two things:
1. first add an entry to the tnsnames.ora file on the new server that points to the old server.
2. then create the (probably public) database link using this entry in the tnsnames.ora file

If that succeeds, you could use that link to move the data, but you would need to do it somewhat manually, or with a script that you create with a line for every table. This would require you though to pre-create every table manually in the new server, and to create all constraints, indexes, grants, triggers, views, procedures, etc. manually. So, if you can get export to work, that is much easier.

Another option: install the 9.2.0.1 software on the new server, take a backup of the current database, copy it to the new server, then apply the 9.2.0.6 patch and upgrade the database.

If you can get export to work, I would recommend that you use Oracle10.1, not the latest version of Oracle10.2 on your new server. But that is based on my tests of 10.2 for Linux about two months ago. At that time, I did not consider 10.2 ready for production even though Oracle claimed it was. Maybe in the past few weeks they have cleaned it up, but we went with 10.1.0.4 for the upgrade we put into production last week on Linux.

ASKER

Another quick question.

If I'm not using multiple disks (I _am_ using two disks in RAID-1), would there be any reason, performance and security wise, to go for multiple tablespace files?

Yes. Multiple tablespace files will make it easier someday to put portions of the database on separate disks if you ever do need additional space. Also, I like separate tablespaces to manage space better, with tables and indexes grouped by their physical characteristics (size and rate of growth) rather than by their logical relationships. This is not as much of a problem anymore if you use "locally-managed" tablespaces. But if you don't, and you have all of your tables and indexes in one tablespace, they will be different sizes. Then if you ever need to drop or move one, it is likely that the remaining freespace will not be easily (or efficiently) re-used.

At least, use a tablespace other than SYSTEM for your own tables and indexes! I recommend that you use a tablespace other than any the ones that Oracle creates by default. That way it is easier to upgrade or migrate your applciation if you ever need to.

ASKER

Thanks markgeer. I was at least planning to use a seperate tablespace (eg. not using SYSTEM). I finally found an Oracle 9i doc that described how Oracle was building its database, so I'm a bit more up to speed in regards of tablespace, control files etc..

The database I'm trying to move uses 4 datafiles, but I think it's only one tablespace though.

I've got one more question. The old server is using RHEL21ES, and the new is using RHEL4ES. What are your experience with Oracle and Windows Server (2000? 2003?)? Would you recomment me to stay with Linux or is Windows as stable as Linux? I would guess it's a bit easier to manage an Oracle DB on Windows, but stability is kinda important I would say.

I've managed Oracle on Windows for the past 10 years and I'm very familiar with that combination. I find that combination easy to manage and stable enough for small organizations (with dozens or maybe up to 200 users). Linux has a reputation for greater stability. We have just migrated our production database to Linux RedHat AS4 because our management is convinced that Linux is more stable. It may be more stable, but I *DO NOT* consider Linux user-friendly or easy to manage! If you have years of experience with Unix, then Linux may work well.

ASKER

Thanks once again :)