Oracle Exp/Imp to Make copy of DB

Hi, folks.

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:

create tablespace TEMP
  DATAFILE '/SOS/oratest/temp.dbf' SIZE 68M REUSE
  AUTOEXTEND ON NEXT 5M
  MAXSIZE UNLIMITED;

Thank you!!
LVL 2
jdritsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PontisCommented:

Answers:

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.
 
0
seazodiacCommented:
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:

create tablespace TEMP
 DATAFILE '/SOS/oratest/temp.dbf' SIZE 68M REUSE
 AUTOEXTEND ON NEXT 5M
 MAXSIZE UNLIMITED;

---->EXP/IMP does not include temporary tablespace, so you should create a new temporary tablespace.  the command you gave out is about right, you just need to put "temporary" keyword at the end:

create tablespace TEMP
 DATAFILE '/SOS/oratest/temp.dbf' SIZE 68M REUSE
 AUTOEXTEND ON NEXT 5M
 MAXSIZE UNLIMITED
TEMPORARY;

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jdritsAuthor Commented:
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?).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jdritsAuthor Commented:
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.

Thanks, again!
0
jdritsAuthor Commented:
Ahh, do I do "startup pfile=/path/to/init<SID>.ora" and then run the script to create the database and tablespace, or the other way around?
0
seazodiacCommented:
when you get connected to the oracle

give:

SQL>startup NOMOUNT pfile=<path_to_init.ora>;

this will direct oracle to allocate memory for SGA and starts the background processes.
then you issue the database creation script and then create the tablespace needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.