Oracle Exp/Imp to Make copy of DB

I have a development database on our dev server, and a production database on our production server. What I need to do is to create a new test database on the dev server, export the production database, and import it into the test database. In other words, I need to make an exact duplicate of the production database on the dev server.

I have Oracle 8.1.7 on Solaris 8. I am using a pfile. The pfile is stored in /oracle/ora8/app/oracle/admin/prod1a/pfile/ on the production server. The control files, logs, system01.dbf, etc. are stored in /oracle/ora8/app/oracle/oradata/prod1a/

The table space files are located in /oracle/oradata/oradata[1..8]/ (don't know why it was scattered around like this).

On the dev server (also Oracle 8.1.7, Solaris 8), I created a subdir called oratest from an exisitng mount (/test). There is plenty of room here that isn't being used, which is why I chose it. I need to recreate the production database (EXCEPT FOR REPLICATION, which DOES exist in production) in its entirety in this mount on the dev server.

I'm going to go with the exp/imp method and create the database manually. I have the following 3 questions:

1) Where should I put the init<SID>.ora file before creating the database with the "Create Database" command. Should I put it in the same location as where system01.dbf will go? If so, I am assuming Oracle will use that one instead of creating a new one. Can you confirm?

2) I want to make sure the replication in the production database doesn't import into the test database. The last thing I need is for the test database to start syncronizing with our other production server. Do I have to do anything special to omit reaplication on exp/imp?

3) 1) Do I need to create tablespaces for temp ones in addition to permanent, or will import handle that? If I need to create them, how do I specify temporary using the following create:

1) Where should I put the init<SID>.ora file before creating the database with the "Create Database" command. Should I put it in the same location as where system01.dbf will go? If so, I am assuming Oracle

1. You should put your init file into $ORACLE_HOME/dbs
2. Not sure about replication, but I would suggest schema level export, which might not pickup the system replication dictionary and thus disable it. You would probably need to do some cleanup afterwards
3. You better create the tablespaces before you import - import can create them if you do database level import, but will create datafiles with exact same names as in the export, for which you might not have mountpoints and folders defined.

1) Where should I put the init<SID>.ora file before creating the database with the "Create Database" command. Should I put it in the same location as where system01.dbf will go? If so, I am assuming Oracle will use that one instead of creating a new one. Can you confirm?

---->Like I said before, init<SID>.ora file should exist before you can create the database. Init<SID>.ora is a configuration file for your oracle instance and database. so you need this file before you start the instance and database. So, you will need to copy over this file from the production database server to the development database server. This file should be preferrably placed in the <oracle_home>/admin/oratest/pfile/ folder and create a link in <Oracle_HOME>/dbs/ folder . but it can be placed anywhere in your system as long as you can remember.
So when you start your oracle instance , you will issue "startup pfile=/path/to/init<SID>.ora"

2) I want to make sure the replication in the production database doesn't import into the test database. The last thing I need is for the test database to start syncronizing with our other production server. Do I have to do anything special to omit reaplication on exp/imp?

--->You don't need to do anything special to do that. by default, when you do exp/imp, replication won't work

3) 1) Do I need to create tablespaces for temp ones in addition to permanent, or will import handle that? If I need to create them, how do I specify temporary using the following create:

Thanks. I will create the tablespaces before I import, but I was wondering about the temp ones. If I need to create them too, how do I specify temp instead of permanent (or do I need to---maybe import will adjust according?).

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Thanks, seazodiac. Looks like I was replying to Pontis at the same time you were typing. You've answered my questions. Sorry I am a little dense when it comes to the init<SID>.ora. I know from your previous post that I need to create it before creating the database, but I was unsure where to put it. Both of you have clarified that.

Featured Post

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…

Configuring and using Oracle Database Gateway for ODBC
Introduction
First, a brief summary of what a Database Gateway is. A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…