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

[Mark Steffes]

We are decommissioning a hp 9000 server at another location. The oracle data and instance has to be moved from this server to another. What is the easiest way to copy/move the oracle instance and data from this old server to our other server. Both servers are hp 9000 k class series. They both are running hp-ux 10.20. And they both have oracle 7.3.4 already installed on them. The target server already has a different oracle instance defined on it which will need to co-exist with the copied instance. Also, how do I determine if I have enough space for the new oracle instance? I really appreciate any help that anyone can give me.

[twang]

Hi,
Does the instance on the target server has same instance name as the one you want to move?
Does the version of the RDBMS on the target is same as the RDBMS on the source server?
If they have diff instance name and same version, you can simply prepare space for the source instance and then copy the the control files, datafiles and parameter file to the target server.
If you need to change the instance name, you need to recreate the controlfile, if this is the case, you need to issue "alter database backup controfile to trace" to create trace for recreate controlfile.

Good luck!
twang

[Steven E. Protter]

Bring the database down.

Take a cold backup with the operating systenm.

Copy those files to the new server.

Set up init.ora tnsnames.ora listener.ora and all other control files to the correct location and new instance name.

Bring up the instance.

Its called database cloning.

It does not work 32 bit to 64 bit.

It does now work necessarily between different verions of the database.

It will not work on an oracle designer repository file.

See attachment.

SEP

[Yogeeraj_1]

Hi,

since you want to run two instances of your database on the same server, you need to review your parameters (Kernel and Oracle database initialisation - configuration files, control files, archive log files, etc) and startup procedures (listener and database).

Have you considered running only one instance and "moving" the data to that single instance?

In terms of disk space, you can only consider the space taken by the datafiles (since the Oracle installation already exists) and the archived log files that will be generated for by "new" database.

hope this helps!

regards
Yogeeraj

[T G Manikandan]

To clone your instance you can copy all the database and initlialization files to the new machine and startup the database.

1.query database for the controlfiles,logfiles,datafiles


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

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

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.

Copy the init.ora to the new server.
copy listener.ora and tnsnames.ora to the new server.
Then you can startup the database.

MAKE SURE THAT YOU BACKUP THE CURRENT SERVER AND DATABASE before any operation.


Thanks

[T G Manikandan]

To determine the space.

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

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

Thanks

[T G Manikandan]

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

[Alexander M. Ermes]

Hi there.
Question ( a little bit off your point ) :

Why not upgrade to HP-UX 11.00 64-bit ?
K-class should be capable.
That should also include a database upgrade to 8.1.7.4

A different approach to your problem is to export the data from the old server,
create a new database on the econd server
and import the data to this. This way all extents can be compressed and you can spread
the database files just the way you want for better performance.

Rgds
Alexander M. Ermes

[Yogeeraj_1]

hi again,

just in case you chose to upgrade to 8.1.7.4, and convert to Locally managed tablespaces, you will benefit from the following advantages:

o no fragmentation
o faster (no recursive sql to UET$ and FET$ to update the data dictionary)
o faster (try creating a dictionary managed table with 1000 extents, then do it with a LMT)
o faster (try dropping that table with 1000 extents now)
o faster (try a temporary tablespace in a DMT vs LMT -- benchmark it, its amazing)

regards
Yogeeraj

[Hein van den Heuvel]

> We are decommissioning a hp 9000 server at another location.
> The oracle data and instance has to be moved from this server to another.

Physically move the storage and reconnect?
"Never underestimate the bandwith of a truck full of disks"

> What is the easiest way to copy/move the oracle instance and data from this old server to our other server.

Books have been written on this subject. Read one or two!. I'm suprprised noone mentioned this before, but the PROPER way, the one ture method, is to EXPORT and re-IMPORT the database. As a side benefit you can readily move oracle versions and even OS platforms that way.

The easiest way move a DB is admittedly, as others pointed out, to move all physical files.
To find all files, check out the SQLplus command: ALTER DATABASE BACKUP CONTROLFILE TO (filename|TRACE), or try the script below.
TO re-integrate the files once moved, notably when they changed mounpoint/directory names, check out the "CREATE CONTROLFILE" command in that backup, and modify to match new environment, keeping the order in place.

> And they both have oracle 7.3.4 already installed on them.

Way old stuff. You are missing out on a lot of goodies, and notably support. Consider upgrading while the DB is down for moving

> how do I determine if I have enough space for the new oracle instance?

On disk? List size for file mentioned in control file backup or from procedure below.
In memory? SHOW SGA (select * from v$sga).

Good luck,

column Tablespace format a15
column file format a45
column id format 99

set pages 9999
set feedback off
select 'redo_'||l.group# "Tablespace", l.group# "Id", l.bytes/(1024*1024) "MB",
MEMBER "File" from v$logfile f, v$log l where l.group# = f.group#
union
select tablespace_name "Tablespace", FILE_ID "Id", bytes/(1024*1024) "MB",
file_name "File" from dba_data_files
union
select tablespace_name "Tablespace", FILE_ID "Id", bytes/(1024*1024) "MB",
file_name "File" from dba_temp_files
union
select 'Control file' "Tablespace", rownum "Id", 0 "MB",
name "File" from v$controlfile
order by 1
/

[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