Oracle Database Discussions

Hi;

Please see:
Master Note For Oracle Database Upgrades and Migrations [ID 1152016.1]
Different Upgrade Methods For Upgrading Your Database [ID 419550.1]

Migration of Oracle Database Instances Across OS Platforms[ID 733205.1]
How To Use RMAN CONVERT DATABASE on Source Host for Cross Platform Migration [ID 413586.1]

Please also check my blog

http://heliosguneserol.wordpress.com/2010/06/17/move-to-oracle-database-11g-release-2-wiht-mike-dietrich/

In this pdf you can see patch of to upgrade db from x to n wiht many senerios wiht all related metalinks notes which is created by Oracle worker Mike Dietrich

Regard
Helios

Hello;

Here's some questions I would ask myself :

Questions

1. Are there any scripts which need to move which can tested in advance?

2. Are there Jobs/Crons which need to be account for on the old and new system? ( Off on old and ON on new )

3. Was Lthe OS setup with LVM? ( Given your large size might save headache down the road )

4. What method will be used to compare objects between the old and the new?

5. Does you checklist have a start and stop time for each item?

6. Has the net80 been tested in advance?

Are you building a new server with a new OS. What is your current OS?

Best Regards

mseberg

9i is not supported first upgrade to 11gR2
Experts posted couple of links do refer, if you want to stick on 9i you can use exp/imp.

i think you mistaken my post.....

i don't want to upgrade...

i want to move the database from one server to another server..

Oracle database is 9i and
OS is AIX

Pl post OS details of the two servers. Are they both on the same network ? Are the database software and datafiles installed on local storage on the source server, or are both on external detachable storage ?

Srini

OS is AIX in both server....

server's are in different network...

Yes datafile are in local storage of server...

pls guide me......

export, import utilty is good OR RMAN is good......

I think you have not followed Helios mentioned links, here it once again


Migration of Oracle Database Instances Across OS Platforms[ID 733205.1]

Is it the exact same version of AIX on both servers ? How big is the source database ?

One option could be to install the database software and patches on the new server, perform a full export on source, create a new empty database and target and perform a full import.

If the two servers can be made accessible to each other, an easier method would be to clone the ORACLE_HOME from source to target, then shut down the database on source and copy all of the database files to target and start up the database on the target.

HTH
Srini

Ya... both OS is same and database size is 3GB.

Below is the export command from source database:-
exp sys/password FULL=Y FILE=\hr\EXP.DMP LOG=\hr\hr.LOG INDEXES=Y GRANTS=Y;

moved exp.dmp from source server to target server...

Below is the import command to target database:-
imp sys/password FULL=Y FILE=\mantra\EXP.DMP LOG=\MANTRA\man.LOG INDEXES=Y GRANTS=Y;

Is the above command is correct? or any other parameter i have to mention..

If the OS and version are the same why not just move the database cold using SCP?

That way you have no chance of losing anything.

Move the datafiles, password file, INIT etc.

I just did this on an Oracle 10

scp hostname:/u01/app/oracle/oradata/ORCL/system01.dbf /u01/app/oracle/oradata/ORCL

Then all you have to account Net80, OS jobs and Directories

I still have my checklist :

1. Create directories on new server
2. Update authorized_keys
3. Shutdown database
4. Run SCP move script
5. Move password and Init files to new server.
6. Update Net80 on New server
7. Perform tnsping check
8. Start Database on new server NOMOUNT
9. Check alert log and Alter database MOUNT
10. Check alert log and alter database open
11. Check listener and start/stop if needed
12. Perform connect check
13. Take  RMAN backup
14. Inform customer
15. Check other jobs on new server

The thing is the database is just these files anyway. Move them and you are done. If something isn't working you still have an exact copy on your old server.

Best Regards

mseberg

Edited by: mseberg on Jan 2, 2012 9:38 AM

Hi,

You can refer the steps provided in the MOS Document *How to Move an 8.1.7 Database to Another Windows Server and Upgrade it to 9.2 Without the Need to Install Both Oracle Releases on the Same Server [ID 214237.1]*


Though this documents speaks about the Windows server and also later speaks about the upgradation, the inital steps of the document speaks about the migration from one server to other with the same OS.

Perform the exp/imp as SYSTEM user rather than SYS.

http://docs.oracle.com/cd/B10501_01/server.920/a96652/ch01.htm#1006128

HTH
Srini

Hi Pravin,

Since you have a small database you may look at a cold backup to move the database to new setup.

1. Install Oracle Binary and the patch sets identical to current setup.
2. Shutdown the database.
3. Create the file systems according to the current setup on new setup
4. Copy the password file and init / spfile to new setup ($ORACLE_HOME/dbs)
5. Copy All the data files and Redo logs to corresponding new setup locations
6. Check if you have any cron jobs on current setup and make it available on the new setup.
7. Startup the database on new setup.

For these activity you need a down time. If you are not have a sufficient down time then you can setup a data guard on the new setup and switch over to new setup.

Cheers
Kanchana

hi,

i am getting below error, while importing :-


IMP-00003:ORACLE error 1435 encountered.
ORA-01435 user doesn't exist
IMPORT terminated unsuccessfully.

Please advice...

What the export dump file contains, What the commands/syntax used to import? paste here.

Hi,

Below is the exp and imp command i am using....
Source Database
exp system/password@cord FULL=Y FILE=\hr\EXP.DMP LOG=\hr\hr.LOG INDEXES=Y GRANTS=Y;


target database (new database)
imp system/password@cordprd FULL=Y FILE=\mantra\EXP.DMP LOG=\mantra\man.LOG INDEXES=Y GRANTS=Y indexfile=\mantra\index_database.sql rows=n
and
imp system/password@cordprd FULL=Y FILE=\mantra\EXP.DMP LOG=\mantra\man.LOG INDEXES=Y GRANTS=Y rows=y

is my above command is correct...

You are making this way harder than it has to be.

Two different people gave you step by step on how to move this without using import/export.

If you had followed their advise you would have finished this yesterday.

Just to crosscheck, once check that user has IMP_FULL_DATABASE role or not?

import user has IMP_FULL_DATABASE role....

try to import using sys user.

Pl identify all of the steps you took to create the database on the target server. Are the filesystem and directories identical on source and target servers ? Have users/tablespaces been pre-created on the target ?

Pl post the complete import log file.

Errors IMP-17 ORA-1119 ORA-27038 When Cloning Database Using Export/Import [Document 438658.1]

HTH
Srini

Edited by: Srini Chavali on Jan 3, 2012 11:54 AM (corrected "source" to "target")

hi srini,

import worked fine after the pre-creation of tablespace and user....
pre-creation of tablespace and user...is OK, but is there any other way to make it simply....:)


But while exporting, only sys object e.g table is not getting exported why? all other user objects are exported fine. I tried to export with sys and system.

To perform full import you should create tablespaces and users also when you perform traditional import, in case of datapump you no need to create users.

Even before importing you should create a database , in that already you have system/susaux. So it's no need of that even it won't perform of export too in full database

If your target server matches the source server in terms of file system directories/layout, then you do not need to pre-create users/tablespaces - imp will attempt to create them in the same locations/directories as the source server.

> Export does not export sys objects - this is normal and expected

HTH
Srini