Home » RDBMS Server » Enterprise Manager » Database copy (Oracle 10g R 10.2.0)  () 1 Vote
Database copy [message #500937] Thu, 24 March 2011 10:14 Go to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
Hi all,
I need to copy all the database objects from one database to another database.
I have 32 bit windows environment I tried export and put it in a dump file but dump file size exceeds the limit, my database size is about 300 GB.

What would be efficient way to replicate all the database objects move to another box ..?
any help is appreciated.

Thanks
JP




Re: Database copy [message #500938 is a reply to message #500937] Thu, 24 March 2011 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>What would be efficient way to replicate all the database objects move to another box ..?
RMAN
Re: Database copy [message #500939 is a reply to message #500938] Thu, 24 March 2011 10:21 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
does RMAN copies all the objects like mat_views, packages, functions,procedures, tables,views, indexes ..?
Basically I need to copy/transfer all of the above object from one of my schema to another machine.

I am not a DBA know nothing about RMAN.
JP
Re: Database copy [message #500940 is a reply to message #500939] Thu, 24 March 2011 10:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Basically I need to copy/transfer all of the above object from one of my schema to another machine.
expdp would be a good utility for this task.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#i1007466
Re: Database copy [message #500947 is a reply to message #500940] Thu, 24 March 2011 10:57 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
Thanks BlackSwan. That's a helpful link.
One last thing If I want transfer all the objects is "Data Pump" be a good option .?

Please advise,
Jay
Re: Database copy [message #500948 is a reply to message #500947] Thu, 24 March 2011 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on what you actually want to transfer and your purpose in the end.
Define "objects". Tell us why do you transfer them, what do you want to do with them?

Regards
Michel
Re: Database copy [message #500949 is a reply to message #500948] Thu, 24 March 2011 11:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>One last thing If I want transfer all the objects is "Data Pump" be a good option .?
I am not clear how to know exactly what "all" includes or exactly what you consider to be as "objects".
Re: Database copy [message #500950 is a reply to message #500948] Thu, 24 March 2011 11:04 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
Hi Michel,
Need to copy mostly all database objects like (procedures, packages, functions,tables, mat views, indexes, views, sequences).

It need toby in Insync once a month. Currently my database is backed up offline (cold back-up once a week).

Thanks
Jay
Re: Database copy [message #500951 is a reply to message #500950] Thu, 24 March 2011 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It need toby in Insync once a month. Currently my database is backed up offline (cold back-up once a week).

Restore once a month with the latest backup.
No need of additional work.

Regards
Michel
Re: Database copy [message #500954 is a reply to message #500951] Thu, 24 March 2011 11:11 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
Great. To restore need to use RMAN.
Thanks Mike and BlackSwan for your tips.

You guys made my day.
Regds,
J
Re: Database copy [message #500970 is a reply to message #500954] Thu, 24 March 2011 13:42 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
What remains unclear is if you also want to transfer the data or just the schema definitions.

If you DO NOT need the data, then you can use either export with the "ROWS=N" option or datapump with "CONTENT=METADATA_ONLY" option.

Good luck!

[Updated on: Thu, 24 March 2011 14:09] by Moderator

Report message to a moderator

Re: Database copy [message #500976 is a reply to message #500970] Thu, 24 March 2011 15:21 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
I need to have all the data basically It's a full replication of all database object for a schema to new box.

I have one question Is there a way I can filter out a table off full replication?

Thanks
J
Re: Database copy [message #500982 is a reply to message #500976] Thu, 24 March 2011 16:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah ah, "all objects for a schema" is not the same thing than "all objects".

You have to clearly and completly define what you want to do.

Regards
Michel
Re: Database copy [message #501062 is a reply to message #500982] Fri, 25 March 2011 08:55 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
Need to replicate all oracle objects from one oracle schema to a new machine except 2 tables.

I tried simple export
exp username/passwd@Instance filec=c:\test_export.dmp DIRECT=Y GRANTS=Y INDEXES = y LOG=C:\test_export.log STATISTICS=NONE;

but It failed because my database size is 350gb so my current 32 bit windows environment doesn't allow me store every thing here (test_export.dmp) in
one file. I am not sure how to break this task ?

Please advise
Jay
Re: Database copy [message #501064 is a reply to message #501062] Fri, 25 March 2011 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put all the objects of the schema in some not shared tablespaces (tablespaces that are not shared with other schemas) but the 2 tables you don't want, put them in an another tablespace.
Then you can use transportable tablespaces method. It is most likely the fastest way to do it but in your version you have to be able to put the source tablespaces in read-only mode during the RMAN operation.

Now, if the 2 tables are the great part of your schema, you can use data pump instead.

Once again, all depends on information we have not.
So give:
- the total size of the database
- the total size of the schema
- the total size of the 2 tables and its indexes

Regards
Michel
Re: Database copy [message #501066 is a reply to message #501062] Fri, 25 March 2011 09:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
expdp help=yes

FILESIZE
Specify the size of each dump file in units of bytes.

Re: Database copy [message #501067 is a reply to message #501062] Fri, 25 March 2011 09:10 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Am I missing something? Surely all you need to do is specify the FILESIZE parameter and a list of files in the FILE parameter on your export command?

[update: I was too late!]

[Updated on: Fri, 25 March 2011 09:10]

Report message to a moderator

Re: Database copy [message #501069 is a reply to message #501066] Fri, 25 March 2011 09:30 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
Hi Michel

Is data pump same as export ? I am confused between export and data pump.

Here is the Information:
SELECT sum(bytes)/1024/1024/1024 size_in_GB FROM user_segments; -- schema 131.91 gb
select sum(bytes)/1024/1024/1024 from dba_data_files; -- databse size 353.12 gb

select ((blocks*8192)-(blocks*avg_space))/1024/1024 "MB size", empty_blocks,avg_space, num_freelist_blocks from user_tables where table_name = 'CLAIMS'; --48GB (THIS TABLE ALREADY INTO SEPERATE TABLESPACE)

Thanks
J

Re: Database copy [message #501070 is a reply to message #501069] Fri, 25 March 2011 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
when all else fails Read The Fine Manual

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/toc.htm

Re: Database copy [message #501072 is a reply to message #501069] Fri, 25 March 2011 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is data pump same as export ? I am confused between export and data pump.

Better than an export, faster, more features like for you using exclude option to exclude 2 tables.

Quote:
Here is the Information:

Do not use DBA_TABLES to get physical information, use DBA_SEGMENTS.
Repost the last query with USER_SEGMENTS like in the first one.
Use SQL*Plus and read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

What is 'CLAIMS'? A table you want to exclude or include?

Regards
Michel

Re: Database copy [message #501073 is a reply to message #501072] Fri, 25 March 2011 11:06 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
SELECT sum(bytes)/1024/1024/1024 size_in_GB FROM user_segments where segment_name like 'CLAIMS'; --44.6 GB

CLAIMS is the table which I want to EXCLUDE off my export list.

Thanks,
Jay
Re: Database copy [message #501078 is a reply to message #501073] Fri, 25 March 2011 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So use Data Pump.

Regards
Michel
Re: Database copy [message #501079 is a reply to message #501078] Fri, 25 March 2011 11:49 Go to previous message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
Right. I may want to write stored proc which depicted on below link under section "Data Pump API"
There create seperate .dmp files for each database objects like Tables one dmp, mat view seperate dmp and so on.

http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

Thanks
J
Previous Topic: How can I add Standby monitoring on Grid Control?
Next Topic: Unable to Create EMCA in Oracle 11g
Goto Forum:
  


Current Time: Thu Mar 28 04:30:09 CDT 2024