OraFAQ Forum: Server Administration » How can I create a duplicate copy of a database?

Home » RDBMS Server » Server Administration » How can I create a duplicate copy of a database?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How can I create a duplicate copy of a database? [message #132895]

Tue, 16 August 2005 11:59

mito
Messages: 19
Registered: August 2005

Junior Member

I am new to Oracle administration. I hope you can help me with my question. We have an Oracle database in production server with 20 schemas in it. I need to take 5 schemas from this production database and duplicate them in a development server. I need your instructions to put me in the right direction and get me started. Thanks.

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #132896 is a reply to message #132895]

Tue, 16 August 2005 12:07

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> I need your instructions to put me in the right direction and get me started. Thanks.

1. export the schemas/users one by one.
The URL takes you to the best Tutorial available on this topic.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm#1013411
This little page would answer some of your questions and give a kickstart.
http://www.orafaq.com/faq/import_export
2. Pre-create the users,tablespace in the other database
3. Import the data
use fromuser/touser option in import utility.
4. If you have issues,
read documentation again, google/search this site for similiar issues
( these are handled maybe a 100,000 times).
If nothing helps, post here again with your oracle version, os,exact error message, what you have done.

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #133884 is a reply to message #132895]

Mon, 22 August 2005 10:43

Mark.Brown
Messages: 2
Registered: August 2005

Junior Member

You can also use tool from AgileInfoSoftware DataStuduio to make a copy of your source schema and write to target schema.

Download can be found at: http://www.agileinfollc.com

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #133892 is a reply to message #133884]

Mon, 22 August 2005 11:19

mito
Messages: 19
Registered: August 2005

Junior Member

How about other tools like PL/SQL developer? Have you had any experience/observations for using this tool for export/import a whole schema? Thanks!

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #133894 is a reply to message #133892]

Mon, 22 August 2005 11:43

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

and what functionalities do the above said tools do in addition to the native export / import tools?
exp/imp is free. And why would you want to spend $$ on anyother tool that does the same functionality ( with a few clicks?)

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #133895 is a reply to message #133894]

Mon, 22 August 2005 11:47

mito
Messages: 19
Registered: August 2005

Junior Member

Mahesh, you've got a point. I wouldn't definitely pay $$ to these tools just for export/import. But my company has already bought PL/SQL developer. Just wanted to check if PL/SQL is dependable for export/import utility. I find that tool to be very user friendly and easier and faster to work with Oracle.

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134106 is a reply to message #132895]

Tue, 23 August 2005 10:32

mito
Messages: 19
Registered: August 2005

Junior Member

I am currently doing the export in User mode using Oracle export utility. It is giving me many messages like:

EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

Is this something I should be concerned about? Or is it safe to import from the exported file despite these messages?

Thanks...

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134108 is a reply to message #134106]

Tue, 23 August 2005 10:35

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Ignore it.
IF you dont want the statistics to be export have statistics=none during export

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134109 is a reply to message #134108]

Tue, 23 August 2005 10:38

mito
Messages: 19
Registered: August 2005

Junior Member

Thanks Mahesh,

How about DIRECT parameter? I am thinking of setting it to "yes" because I want it to be faster. The schema I will be exporting is more than 100GB. Do you see any potential problem there with the DIRECT=y?

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134111 is a reply to message #134109]

Tue, 23 August 2005 10:44

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>he schema I will be exporting is more than 100GB
Then export / import is not an very good option here.
It will take for ever!.
Continue with export/import if you can afford wait a long time.
Direct = y may help a little.
It will take a longer time during import.
Import only data. Then, Recreate the index in parallel from extracted ddl.

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134114 is a reply to message #134111]

Tue, 23 August 2005 10:48

mito
Messages: 19
Registered: August 2005

Junior Member

Are you suggesting I do the export in table mode instead of user mode? I did not quite understand what you meant by "Recreate the index in parallel from extracted ddl".

Also what other option do I have other than export/import for such a case where the schema size is over 100GB?

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134120 is a reply to message #134114]

Tue, 23 August 2005 10:57

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

export/import is a serial process.
To export 100gb data ( How much of it is actual data? excluding index data?) it will take a while.
Similarly, during import, the process is done serially.
first create table, insert data, create index.
create index is very painful operation here.
during import say
1.) rows=y indexes=n constraints=n
so index is not created.
2.) Get the ddl of indexes ( using metadata or any gui tool or from exported dump itself)
Edit the DDL script.
Create the indexes with parallel option

create index myindex on mytable(Mycolumn) tablespace index_tablespace parallel (degree n);

This would be much faster.

other options with export/import:
1. run several export sessions in parallel , with their own subset of tables.
2. do the same with import.

Other options:
RMAN Duplication.
Unfortunately you cannot do this on the fly.
It takes a while to understand this.
Once env is set, this works like a charm.
I duplicate a 40g database frequently with RMAN
It takes less around 10 mins!.

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134122 is a reply to message #134120]

Tue, 23 August 2005 11:02

mito
Messages: 19
Registered: August 2005

Junior Member

excellent! thanks for you help. I will try your suggestion about the index. I guess I dont want to get involved in RMAN right now.

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134126 is a reply to message #134122]

Tue, 23 August 2005 11:07

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

http://www.orafaq.com/faq/Server_Utilities/ImportExport/faq187.htm

Please go through this. might be useful.

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134389 is a reply to message #132895]

Wed, 24 August 2005 13:36

mito
Messages: 19
Registered: August 2005

Junior Member

Everything is going well. Except the following:

I am trying to do the import now for this huge (>100GB) schema.
In the original server, this schema used a different tablespace. In the destination server, I created an empty schema before starting the import. Since I did not create a new tablespace in the destination server, I think it is trying to import data into SYSTEM tablespace and giving me the following errors. What would you recommend I do? If I need to create a tablespace, please let me know what type of syntax I should use. I want all the extent and space allocation to be automatic. I dont want to deal with adding new datafiles manually as the tablespace grow bigger. Space is not a big constraint, I have plenty of space in the server.

Thanks!

ORA-01653: unable to extend table SOME_SCHEMA.SOME_TABLE by 8192 in tablespace SYSTEM

ORA-01658: unable to create INITIAL extent for segment in tablespace SYSTEM

ORA-01659: unable to allocate MINEXTENTS beyond 1 in tablespace SYSTEM

[Updated on: Wed, 24 August 2005 13:37]

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134393 is a reply to message #134389]

Wed, 24 August 2005 14:02

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

First you are not supposed to import anything into system schema
(Even if the source database has custom objects in system schema).
You precreate the required tablespaces in target.
Precreate users.
If you have given resource role, it enables the user to write to any available tablespace. So If the objects are already in system tablespace (in source), during import, data will be imported in SYSTEM tablespace.
assign userA a default tablespace tablespaceA.
assign unlimited quota on tablespaceA to userA.
do import with fromuser=userA touser=userA.
So by default all data will go into tablespaceA.

>>If I need to create a tablespace, please let me know what type of syntax I should use
REfer docs.
USE LMT with uniform extent size.
>> I want all the extent and space allocation to be automatic. I dont want to deal with adding new datafiles manually as the tablespace grow bigger.

You can specify auto extention.
Docs should discuss about it.

[Updated on: Wed, 24 August 2005 14:21]

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134395 is a reply to message #134393]

Wed, 24 August 2005 14:11

mito
Messages: 19
Registered: August 2005

Junior Member

if I use LMT with uniform extent size, wouldn't I have to manually manage it? I was going to select automatic allocation to avoid maintanence in the future.

[Updated on: Wed, 24 August 2005 14:11]

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134397 is a reply to message #134395]

Wed, 24 August 2005 14:20

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Yes. absolutely. You should go for it.

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134400 is a reply to message #134397]

Wed, 24 August 2005 14:49

mito
Messages: 19
Registered: August 2005

Junior Member

I have dropped all previously imported schemas which used SYSTEM tblspace but SYSTEM tablespace size did not drop. It is still 33G!

Is there anyway to shrink the SYSTEM tablespace?

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134401 is a reply to message #134400]

Wed, 24 August 2005 15:01

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/dfiles.htm#7459

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134408 is a reply to message #134393]

Wed, 24 August 2005 15:41

mito
Messages: 19
Registered: August 2005

Junior Member

I am getting these errors while importing even after I created an empty tablespace and user.

ORA-01658: unable to create INITIAL extent for segment in tablespace USR1_DEFAULT_TBLSPC
ORA-01659: unable to allocate MINEXTENTS beyond 1 in tablespace USR1_DEFAULT_TBLSPC
ORA-01659: unable to allocate MINEXTENTS beyond 4 in tablespace USR1_DEFAULT_TBLSPC

Here is the statement I used to create the user:

create user USR_NAME
identified by USR_PASS
default tablespace USR1_DEFAULT_TBLSPC
QUOTA UNLIMITED ON USR1_DEFAULT_TBLSPC;
grant CONNECT to USR_NAME;
grant RESOURCE to USR_NAME;
grant CREATE ANY TABLE to USR_NAME;
grant QUERY REWRITE to USR_NAME;
grant UNLIMITED TABLESPACE to USR1_DEFAULT_TBLSPC;

Here is the statement I used to create the user's default tablespace:

CREATE TABLESPACE "USR1_DEFAULT_TBLSPC" LOGGING DATAFILE 'F:\ORACLE\ORADATA\DBS1\USR1_DEFAULT_TBLSPC.ORA' SIZE 10M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

I am looking forward to your answers as I am kind of stuck in the problem. Thanks!

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134409 is a reply to message #134408]

Wed, 24 August 2005 15:44

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>SIZE 10M
size is too low!.
estimate your source tablespace size.
Recreate it here, in target.
DO import.

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134410 is a reply to message #134409]

Wed, 24 August 2005 15:53

mito
Messages: 19
Registered: August 2005

Junior Member

Mahesh,

Actually I know all the table sizes. It varies a lot. For one schema I am importing the default tablepace size is 150GB, for another schema it is only 15MB. I know it sounds weird but this is how it is in the original database I am exporting from.

So should I set the tablespace sizes like the following???
CREATE TABLESPACE "USR1_DEFAULT_TBLSPC" LOGGING DATAFILE 'F:\ORACLE\ORADATA\DBS1\USR1_DEFAULT_TBLSPC.ORA' SIZE 153600M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "USR2_DEFAULT_TBLSPC" LOGGING DATAFILE 'F:\ORACLE\ORADATA\DBS1\USR2_DEFAULT_TBLSPC.ORA' SIZE 15M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Thanks for all your great help!

Mito

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134412 is a reply to message #134409]

Wed, 24 August 2005 15:55

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

And did you take an export with COMPRESS=N?
Else
You may endup in similiar troubles, as during import, you will be need one BIG initial extent.

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134413 is a reply to message #134410]

Wed, 24 August 2005 15:58

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

try this again.
Export your 15m size schema again with compress=n.

$exp dba/user@sourcedb owner=15m_sized_schema file=somefile.dmp Compress=n

import it

$imp dba/user@targetdb fromuser=15m_sized_schema touser=15m_sized_schema
file=somefile.dmp

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134414 is a reply to message #134412]

Wed, 24 August 2005 15:58

mito
Messages: 19
Registered: August 2005

Junior Member

I dont know. I have not specified COMPRESS parameter during export. I am not going to be able to do the export all over again. It will cost me another day. I am running against my deadline. So I will try to handle the tablespace configuration correctly and continue without redoing the export.

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134415 is a reply to message #134414]

Wed, 24 August 2005 16:01

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Else, You need to resize the tablespace.
But, Fixing during the export is right option.
You first test it by exporting / importing only the 15m schema as mentioned.

and COMPRESS=Y is the default!

[Updated on: Wed, 24 August 2005 16:01]

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134416 is a reply to message #134415]

Wed, 24 August 2005 16:16

mito
Messages: 19
Registered: August 2005

Junior Member

Here are my observations:

Setting COMPRESS=n for the 15M schema did not help. I got the same errors while importing.

Recreating the empty tablespace with more than 15M size DID HELP. I got no errors. So that's great.

But I have two questions:

1) Does it seem logical to set the initial tablespace size as huge as follows for the 150GB schema import?

CREATE TABLESPACE "USR1_DEFAULT_TBLSPC" LOGGING DATAFILE 'F:\ORACLE\ORADATA\DBS1\USR1_DEFAULT_TBLSPC.ORA' SIZE 153600M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

2) What if the schema grows bigger after import is done. Let's say 150GB is imported successfully, but it grew to 170GB. Would automatic space management take care of it without my manual intervention in the future?

Thanks for your comments and help...

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134421 is a reply to message #134416]

Wed, 24 August 2005 18:15

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

But in future, always export with COMPRESS=n
>>size as huge as follows for the 150GB
Make sure your OS can support such a big datafile.
(any it will painfull to backup/maintainence).
Usually it is better to keep the datafile less than 4g in windows ( 1m less than 4g. There are some known bugs in 9i windows edtion with autoextend on. Either Keep autoxtend off. or datafilesize should be less than 4g).

If you are in 10g, make use of bigtablespace option ( you can create a tablespace with 1 datafile sized in Terabytes).

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134578 is a reply to message #134421]

Thu, 25 August 2005 11:29

mito
Messages: 19
Registered: August 2005

Junior Member

I have created an index file while doing the import. I have completed regular import with data. Now it is time for me to construct the indexes from previously imported index script.

I see many REM commented lines. What should I do with these? Should I ignore them or should I uncomment and run all these commented lines after indexes are created?

Thanks...

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134591 is a reply to message #134578]

Thu, 25 August 2005 13:02

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

you can just exatract the index ddl from indexfile.
or
extract the index ddl from the source database liek this.
SPool the output and run against target database.

scott@9i > get ddl.ddl
  1  set long 500000
  2  set linesize 1000
  3  set feed off;
  4  SET HEAD off
  5  exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
  6  SELECT DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)||';' FROM user_indexes ;
  7* set head on;
scott@9i > @ddl.ddl


  CREATE INDEX "SCOTT"."MYINDEX" ON "SCOTT"."EMP" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS"
 ;

scott@9i >

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134592 is a reply to message #134591]

Thu, 25 August 2005 13:09

mito
Messages: 19
Registered: August 2005

Junior Member

my question was more regarding the comments... there are bunch of extra lines in the index file created with all the comments about creating primary and foreign keys, etc. I dont know why they are commented... Is it because I should simply ignore them? I saw that after table imports primary keys are missing in some tables. So should I run the index file first to create the indexes, and then uncomment other statements in the index file and run again to create contraints???

Thanks.

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #134594 is a reply to message #134592]

Thu, 25 August 2005 13:22

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>I dont know why they are commented..
And i dont know what you did and what contents are commented?

If the import is with constraints=y (default) then, the index related to constraints (primary keys create indexes) are already created. So they might be commented.~

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #142684 is a reply to message #132895]

Mon, 17 October 2005 04:00

AnalystParth
Messages: 15
Registered: September 2005
Location: India

Junior Member

Just one quick suggestion that you may want to consider, and see if it makes sense.

In most cases, the development servers/databases are created as copies of the production database. (Yes, even if you need only 5 schemas out of 20, very often DBAs decide to take a complete copy)

So why dont you just consider using the latest Database backup cold or hot and recreating the development database?

I can understand that this would not be a good idea if:
1. the complete db is in terrabytes and the option of copying and recreating is going to be equally cumbersome.
2. you want to retain some data in the development db.
(sometimes it works faster to export this particular data to be retained and then import it back)

Export import often causes errors, needs a little baby sitting etc., while the option of recreating db from datafiles usually faster and once practiced a couple of times, very routine.

-- Parth

Report message to a moderator

Re: How can I create a duplicate copy of a database? [message #143248 is a reply to message #142684]

Wed, 19 October 2005 10:57

Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida

Senior Member

AnalystParth wrote on Mon, 17 October 2005 04:00

Just one quick suggestion that you may want to consider, and see if it makes sense.

In most cases, the development servers/databases are created as copies of the production database. (Yes, even if you need only 5 schemas out of 20, very often DBAs decide to take a complete copy)

So why dont you just consider using the latest Database backup cold or hot and recreating the development database?

I can understand that this would not be a good idea if:
1. the complete db is in terrabytes and the option of copying and recreating is going to be equally cumbersome.
2. you want to retain some data in the development db.
(sometimes it works faster to export this particular data to be retained and then import it back)

Export import often causes errors, needs a little baby sitting etc., while the option of recreating db from datafiles usually faster and once practiced a couple of times, very routine.

-- Parth

I was gonna suggest the same thing. CLONE the prod database into the dev server.

consideration:
1. dev server needs to have the same kind of disk used for prod (> 150GB in this case).
2. do you have a cold backup on tape ?
3. copy over the init.ora, tnsnames.ora and lsitener.ora (in reality make the SAME environment as prod.)
4. make identical file systems between prod and dev to avoid confusion. Copy from tape.
5. If needed create control file and change the name of the db.

I just copied our prod warehouse (250GB). It took 4 hours to copy from tape (last nights). Then another 15-20 minutes to create the control files and final check.
--
Sanjay

Report message to a moderator

Previous Topic:	oracle session licence
Next Topic:	clone database in windows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 11:26:45 CST 2025