How to copy an Oracle database to another server

Hi,
I would like to set up a test environment for an Oracle 9i database. What is the easiest way to copy the database to the test server? Please note that the production server has different hard drive structure. I.E, the production server has data files on c, d, e, f drives but there is only a C drive on test server.

Thanks in advance,

Celia

You can copy the datafiles to test server and recreate the control files with the path name you want. Let me know if you still have questions.

What datafiles? what are there extentions and where would these files be found on the oracle folder?

You can look at dba_data_files to see all the datfiles in the datadbase with its current location. You need to shutdown the existing database or keep the tablespaces in backup mode (you need to be in archive log mode to do this) and copy all these files to the new server. Note down the location of the files you copied on the new test server.

2)startup the existing database.

3)at the existing database
sql> alter database backup control file to trace.

4)go to udump and collect the trace file edit it and add th enew datafile locations.

5)stratup nomount the new database.

6) craete control file from the trace file.

7) Recover or open the new database.

To see where your datafiles are, go to SQLPLUS: select file_name, maxbytes,user_bytes,tablespace_name
from dba_data_files ;

This will give the tablespace, the size allocated, the size used, and the location of datafile that holds the data for the tablespace(S)in your database.

For the test server question, I'm going to make the assumption that an Oracle Instance is currenly on the server.

1. To make test look as close as possible to production, I would create tablespaces with the same name as production linking them to datafiles with simular names on the test server.
Ex. create tablespace noname
datafile 'C:\oracle\oradata\name01.dbf'
extent management local
uniform size 64k;

2. Create a user schema in test db
Ex.
create user xyz identified by xyz123
default tablespace spacename
temporary tablespace temp
;
...

3. Perform an export of prod
Ex.
exp scott/tiger file=MyExportFile.dat Ful=y;

4. Copy file to test server and perform an Import. I prefer the fromuser touser scenario.
EX. imp system/manager
file=MyExportFile.dat fromuser=scott touser=xyz;

hope it helps.

Thanks for the quick answer dgrif0 6, greatly appreciated. Got a another question. I have Oracle 8.0.X.X running on my home computer. What do I need to do to host the database to all the computers on my local network? I know I need to have SQLNET or something like that. Does SQLNET come with ORACLE ENTERPRICE 8.0.x.x?

No prob.....

SQLNET is packaged in Oracle 8i, so Install Oracle Enterprise software on your host computer; config your listner, tnsnames, and sqlnet.ora files. This can be achieve through the DB config Assistance tool that is packaged as well. More specific to your question, the SQLNET entries should be simular to the following:

# SQLNET.ORA Network Configuration File: # C:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES)
# tells client to attempt a connect through
# the TNSNAMES.ora file

# if you are using an LDAP Server, then
NAMES.DIRECTORY_PATH= (LDAP,TNSNAMES)
# tells client to attempt a connection through
# LDAP first

Install the client on every node machine you want to reach the host machine from;

On the client(s) set SQLNET simular to the following:

# SQLNET.ORA Network Configuration File: C:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES)# Should reflect # host's SQLNET

SQLNET.EXPIRE_TIME= 5 # Dead Connection Wait # time


I think that should do it....


If anyone has any additional comments, please chime in.


Thanks

Thanks for the advice to copy Oracle database.
However when trying to create a control file, I had error 01161. Seems that the new database on the test server will have to have exact db_name and global database name as the production database. So my question is will this have any impact on the production database at all? I mean any naming conflict?

C,

If you are creating a schema on an existing DB, then you do not have to create control files. They already established. When the new Tablespace(s) and schema are created on test, simple export from product and import on test fromuser/touser method.


If you are trying to create a new database and not just create an user account/ schema on the existing, then do the following:

1. Create your new database the same size as your original database with a different SID and database name.
(use OEM -- this help u set up the basic file structure for the database)

2. When the new database has been built successfully including adequatly sized private rollback segments, create your users.
3. On the original database perform a FULL database EXPORT.
4. New database do a FULL database IMPORT.

Hope this helps.

Hello dgrif0 6,

Thanks for your quick response on the SQLNET question. I have another if you don't mind. I am getting the following error when I try to log into the host database from my client machine or when I run tnsping80.exe on the client machine.

TNS-12571: TNS:packet writer failure

Any ideas?, please respond.