jdrits
asked on
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/ad min/prod1a /pfile/ on the production server. The control files, logs, system01.dbf, etc. are stored in /oracle/ora8/app/oracle/or adata/prod 1a/
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!!
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/ad
The table space files are located in /oracle/oradata/oradata[1.
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!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Thanks, again!
ASKER
Ahh, do I do "startup pfile=/path/to/init<SID>.o ra" and then run the script to create the database and tablespace, or the other way around?
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.
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.
ASKER