Oracle Export / Import

[Darren Etheridge_2]

Hello everyone. I am kinda new to oracle and need some direction. I have 2 systems, both with Oracle 8.1.7 and HP-UX 11i on them. I would like to export a Oracle DB from one machine and move it to the other. I am kinda confused on a couple of things. 1st - Do I need to create the DB on the new machine or will the import do this for me? 2nd - When Importing, will the import place the files in the exact location as they were on the machine I exported them from? Any help/procedures/directions would be greatly appreciated. Thanks in advance!!

[Steven E. Protter]

You have two options that I know work.

One is to create a db on the second server and use the import tool to bring in the file you exported from the first server.

You also may do whats called database cloning.

Shut down the database on server1

Copy all the files to server2 via ftp, tape transfer whatever.

generate new control files(I think) on server2 to accomodate instance name not being the same(you don't want the same instance name on two machines).

bring up the database.

SEP

[Jean-Luc Oudart]

If I understand you want to clone your database onto another server , same OS and same Oracle version installed.

Database cloning : follow the link


http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=227394


Regards,
Jean-Luc

[Michael Schulte zur Sur]

Hi,

1st: Yes, you will have to create the instance on the second database.
2nd: If you mean tablespaces with location, then the answer is yes.

Are the others right with their assumption, that you want to create a replica of the database on the one machine?

greetings,

Michael

[Jeff Ohlhausen]

Hi,
The easiest way to approach this is to perform a system copy.
I've attached an overview of the steps.

Hope this helps.
Jeff

[R. Allan Hicks]

Depending upon how active your database is imp/exp could give you some trouble. If a foreign key is created in the master after that tablespace was copied but the detail was created before its tablespace was copied, the tape will try to restore a detail without the master and the constraint will be violated. It's not fatal, imp will complain and the master detail will not be in your new database. If the new database is for testing, then it probably won't matter. You can minimize the risk back doing your exp when the database is reasonably quiet.

It has been my experience that imp will create the tablespaces for you if they do not exist, but you will have to have at least a minimum database on the new machine.

When you copy datafiles from a running oracle system, it is called a hot backup. When you copy files from a shutdown oracle system, it is called a cold backup. You didn't mention if your system was in archive log mode. If it is not, do not try to do a hot backup.

All changes in the oracle database are co-ordinated with system change numbers (SCN). When the database is opened, oracle checks the SCN numbers of each datafile against the SCN number of the control file. If they all match everything is fine. If they don't then oracle will do a database recovery.

A database recovery is where the archive logs and the redo logs are read and the changes that they contain are applied to the datafiles. These changes bump the SCNs until they match the controlfile. If they can't be matched, you cannot do a complete recovery and some data will be lost. If you are not in archive log mode, you will have no archive logs to restore. Although all the information that you need might be in the redo logs. Oracle does not recommend that you perform hot backups on systems not in archive log mode.

Cold backup procedure:

If you can shut the database down and do a off-line copy, you can avoid database recovery issues.

sqlplus sys/syspassword
sql>shutdown immediate;
<>
sql> quit

Use your favorite hp backup/copy utility to copy all of your oracle datafiles. (See select * from v$datafile for a list) Copy your control and redo logfiles. (See $ORACLE_BASE/admin/YOURORACLESID/pfile/initYOURORACLESID.ora for locations)

example: if your $ORACLE_SID = orcl then
the pfile should be at

$ORACLE_BASE/admin/orcl/pfile and have the name initorcl.ora

provided that you followed Oracle's recomendations about environment variables.

Copy /etc/oratab /opt/oracle

Copy everything out of $ORACLE_HOME and $ORACLE_BASE.

When you restore, as others have said.... The file system must have the same directory structure. More than likely the backup utility (tar, fbackup and cpio) will recreate it. _ALL_ files _MUST_ go back to the same directory name as they came from. You can move them around if you have to, but it makes the re-start painful.

The hot backup is basically what I understood jeff to say, the only advantage of this cold backup method is you don't have to do a recovery when you start back up. The disadvantage is that you have to take the other database down until the backup is complete. Oracle doesn't recommend that you do a hot backup unless you put each tablespace into backup mode prior to copying it and restore it to normal mode once it's been copied. As a newbie, you may not want to tackle that just yet, but there isn't really much to it.


One final word of warning... If you are tempted to copy every thing from / on down, you will be able to replicate on the other machine, but be warned that the new machine will have the same IP address as the original machine.

So, after you restore, bring it up in single user mode and do a set_parms to change the IP address back to the proper one to avoid network problems. Having a duplicate IP will among other things, cause your oracle clients to have trouble connecting to the server. This is a nasty situation because if you don't deal with it when you do the restore, you'll discover it the next time you boot the new machine and you might not remember what you've done to cause it. It's a time bomb. I know I did it once and had the production machine down for about an hour before I realised what had happened.

[Hari Kumar]

hai,
hope this will work out...

answer to ur 1st question
create a DATABASE

take an export from DB1 using

exp system/manager file=filename.dmp log=logfilename.log full=y grants=y constraints=y

example

exp system/manager file=/tmp/test.dmp log=/tmp/testlog.log full=y grants=y constraint=y
log file shows if there is any errors during export...
the dump filename and log filename should be different...

copying the .dmp file to the tape... or cut a CD
pls dont do ftp of the dmp files it is not recommended...
restore the tape or CD in second system...
in /tmp directory...

imp system/manager file=/tmp/test.dmp log=/tmp/testlog.log full=Y buffer=2048000 commit=Y ignore=Y

here ignore=Y option used for
ignore create errors RECORDLENGTH length of IO record