How to copy oracle instance from one hp-ux server to another?

[Hein van den Heuvel]

Ah... one more thing... whatever method you choose in the end, but sure to consider to COMPRESS the intermediate files.
Do this even if you do everything 'by air' (pipes, networks). Use GZIP, or whatever tickles you, but do spend a little time setting up compress and save a lot of time and space moving the data.

Regards,
Hein.

[RolandH]

I you have also copied the binaries from oracle
I recommend you to relink the database, too.

That is very easy.

- shudown oracle
- shutdown listener
- shutdown every other oracle application, like dbsnmp

su - oracleuser
relink all


Roland

[Mark Steffes]

In answering Twang.

The oracle instance name is different between the servers and the RDBMS versions are the same.

Also, more background information for everyone.

The size of the database being moved is only about 10 GB. The file locations look like they are on different paths. However, some of the tablespace names, tables names, index names are the same. I think this is okay since the instance name will be different? We will be most likely moving all of the oracle files using FTP over a shared T1 line.

I really likes T G 's response since it is so simple. I have two more questions here.

1. I cannot guarantee that the init.ora has been updated when the database has been changed. Is there a way to regenerate the init.ora?

2. There is a tnsnames.ora file on the client pcs which I assume has to change to point to the new ip address of the moved database. They are already accessing both databases.

More background here. Currently the tnsnames has an entry for each of the databases but they are pointing to different ip addresses (since the databases are on two different servers). I am assuming that all I have to do is point the entry for the database I am moving to its new ip address here and push the file back out to all of my clients. Also I need to make the same changes to the server tnsnames. A new entry will need to be added to the listener file on the server for the database being moved. So there will be two entries - one for each database in listener now. So when I am done there will only be one tnsnames and one listener file on the server containing entries for both databases.

Here are the steps I believe I need to follow to move the instance.

1. BACKUP THE CURRENT SERVER AND DATABASE.

2. query database for the controlfiles,logfiles,datafiles

SQL>select name from v$datafile;
SQL>select * from controlfile;
SQL>select * from logfile;

3. Do a consistent shutdown of the database
using "shutdown immediate".

4. After this move the above datafiles,logfile,controlfile to the new server.Make sure you are moving the files to the //same path// as the old server.

5. Copy the init.ora to the new server.

6. modify the listener.ora and tnsnames.ora on the new server to point to both databases now (the moved database and one that has always been on the source server).

7. Then you can startup the database.

To determine the space.

1. check the file size of the datafiles,controlfiles,logfiles using "ls -l"which you had queried.

2. Then check whether the new server will be able to accomodate.

Also,if u are moving the files to different path on the new machine then.

After copying the init.ora to the new server.

1. modify the parameter CONTROL_FILES to reflect any change in path for the controlfile copied database.

2. If you have copied files to the new server to different path that are different from the original server, you must rename the files/logs inside of Oracle.

svrmgrl>startup mount pfile=init.ora

svrmgrl>alter database rename file 'filename' to 'filename'; svrmgrl>alter database open

If you have copied files to the new server that are identical to the original server, you may simply start the database normally.

I appreciate all of the help.

Mark

[RolandH]

I recommend you a little bit changed prcedure.

1.query database for the controlfiles,logfiles,datafiles

SQL>select name from v$datafile;
SQL>select * from controlfile;
SQL>select * from logfile;

2.Do a consistent shutdown of the database
using "shutdown immediate".


3.BACKUP THE CURRENT SERVER AND DATABASE
( database is closed and in an consistent state )

4. After this move the above datafiles,logfile,controlfile to the new server.Make sure you are moving the files to the //same path// as the old server.

I recommend rcp for the copy procedure NOT FTP.
FTP changes permissions and owner/group that can bring a lot of trouble to your new database server. If you have created your account on the new server (same EUID/EGID) you can do this as .


Another way to copy your oracle files to the new server is to RESTORE your orcale files from the BACKUP to the new server. But this depends what you are using for BACKUP/RESTORE.

5. Copy the init.ora to the new server.

6. modify the listener.ora and tnsnames.ora on the new server to point to both databases now (the moved database and one that has always been on the source server).

7. If you are NOT sure that the new server is absolutely on the same patch level as your old server then relink your databse.

8. Then you can startup the database.

That is what I would do.

Please, can someone check this. 4 eyes are better than 2.

I have not checked the other points if the path is differant, but seems to be ok.


Roland