Solved

Oracle Exp/Imp to Make copy of DB

Posted on 2003-10-22
6
1,698 Views
Last Modified: 2012-08-13
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!!
0
Comment
Question by:jdrits
  • 3
  • 2
6 Comments
 
LVL 5

Assisted Solution

by:Pontis
Pontis earned 100 total points
ID: 9601282

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
 
LVL 23

Accepted Solution

by:
seazodiac earned 400 total points
ID: 9601287
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
 
LVL 2

Author Comment

by:jdrits
ID: 9601297
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
PRTG Network Monitor: Intuitive Network Monitoring

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.

 
LVL 2

Author Comment

by:jdrits
ID: 9601507
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
 
LVL 2

Author Comment

by:jdrits
ID: 9601767
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 9601867
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

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.

Join & Write a Comment

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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now