Home » RDBMS Server » Backup & Recovery » TO Make A Copy Of Oracle DB in Local PC (Windows XP, Oracle10g)
TO Make A Copy Of Oracle DB in Local PC [message #492869] Thu, 03 February 2011 05:06 Go to next message
oraQ
Messages: 57
Registered: January 2011
Member
I want a copy of data from oracle database in Production to my local machine as upgrade is going to be there soon. How can I do that so that I may check my old data later irrespective of upgrade?

Thanks in advance.
Re: TO Make A Copy Of Oracle DB in Local PC [message #492870 is a reply to message #492869] Thu, 03 February 2011 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Depending on what is your final goal: RMAN/backup database or export/<your schemas>.

Regards
Michel
Re: TO Make A Copy Of Oracle DB in Local PC [message #494683 is a reply to message #492870] Wed, 16 February 2011 10:02 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
Thanks Michel for your input. But, I am a new user and want to take the backup of some objects swiftly. Will you help in suggesting the procedure to achieve this?
Thanks.
Re: TO Make A Copy Of Oracle DB in Local PC [message #494686 is a reply to message #494683] Wed, 16 February 2011 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
C:\>exp help=y

Export: Release 10.2.0.4.0 - Production on Mer. FÚvr. 16 17:15:54 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.



You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

     Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export

Export terminated successfully without warnings.

Regards
Michel

[Updated on: Wed, 16 February 2011 10:18]

Report message to a moderator

Re: TO Make A Copy Of Oracle DB in Local PC [message #494780 is a reply to message #494686] Thu, 17 February 2011 02:00 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
Thanks Michel for your prompt reply. But, can you guide me how to do the export of important objects like procedures, functions through TOAD? Can I use sql*plus from within TOAD?
Thanks.
Re: TO Make A Copy Of Oracle DB in Local PC [message #494785 is a reply to message #494780] Thu, 17 February 2011 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
can you guide me how to do the export of important objects like procedures, functions through TOAD?

Select the procedures, right-click and select "create script".

Quote:
Can I use sql*plus from within TOAD?

go to main menu, Edit> Execute SQL via SQL*PLUS

Regards
Michel
Re: TO Make A Copy Of Oracle DB in Local PC [message #495444 is a reply to message #492869] Sat, 19 February 2011 07:08 Go to previous messageGo to next message
itralarcon
Messages: 36
Registered: February 2011
Location: San Salvador
Member

There are some methods to backup an Oracle Database:



  1. Import/Export Utility
  2. Physical Backups


I will just explain the 1st Way, because is the easiest one.

Import/Export Utility The easiest way, you can use this one when you dont know to much about physical database files. This method its a logical backup, you can export/import one or multiples schemas or all schemas.

Steps for Export:
Export For all schemas backup: You must to do this on your host Database. This will help you to do a complete Backup of the database.

If you are using Windows OS, just go to CMD and type command:

EXP System/SystemPassword@OracleSID file=HereTypeTheName.dmp Full=yes


100% Works, more than 2 years using this on Oracle 10G and XE Database.

You only need:
*System Password (when you install Database you must to type this password).
*OracleSID of Database, you can find it on the TNSNames.ORA; same on the services.msc from windows like a service.
*Dont Forget to identify your *.DMP File, because this will be your backup file when you import the database on the other PC.

Steps for Import:
This part will be on the Destination PC. Copy *.DMP file to the "local" computer.
Almost the same than export, just identify your *.DMP file and Type on CMD the path of the file (remember using cd pathoftheDMPFile.

*You must to create the users of the host database and apply the Grantsfor each one. The Users/Schema must to be created or this method wont work.
*After created the users (remember the *.dmp path must be set), just type this on CMD:

IMP SYSTEM/SystemPassword@OracleSID FULL=YES


And this it all... You will get an exactly copy of the database on your "local" computer.


On Toad:
You can do the same using Export Utility Wizard, and Import Utility Wizard on Database Menu. Creating a DMP File and Importing it.


P.D. My english is not the better, but i think you can understand.





Re: TO Make A Copy Of Oracle DB in Local PC [message #495449 is a reply to message #495444] Sat, 19 February 2011 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think we already answered and more precisely to the questions because:
1/ You don't explain how to export SOME OBJECTS which is the first question
2/ You don't explain how to export SOME PROCEDURES AND FUNCTIONS through TOAD which is the second question.

Keep answering but take more care of the questions and answers, it is not useful to repeat the same thing.
Better chooses to reply to unanswered question, there are many.

If I can add something, you said "100% Works, more than 2 years using this on Oracle 10G and XE Database", of course you did not do it on several hundreds of GB databases, may I tell you that in this case it is far from the best way to do it.

Regards
Michel

[Updated on: Sun, 20 February 2011 00:24]

Report message to a moderator

Re: TO Make A Copy Of Oracle DB in Local PC [message #495472 is a reply to message #495449] Sat, 19 February 2011 19:33 Go to previous messageGo to next message
itralarcon
Messages: 36
Registered: February 2011
Location: San Salvador
Member

Michel Cadot wrote on Sat, 19 February 2011 09:10
I think we already answered and more precisely to the questions because:
1/ You don't explain how to export SOME OBJECTS which is the first question
2/ You don't explain how to export SOME PROCEDURES AND FUNCTIONS through TOAD which is the second question.

Keep answering but take more care of the questions and answers, it is not useful to repeat the same thing.
Better chooses to reply to unanswered question, there are many.

If I can add something, you said "100% Works, more than 2 years using this on Oracle 10G and XE Database", of course you did not do it on several hundreds of GB databases, may I tell you that in this case it is far from the best way to do it.

Regards
Michel

Ok

[Updated on: Sun, 20 February 2011 00:25] by Moderator

Report message to a moderator

Re: TO Make A Copy Of Oracle DB in Local PC [message #496328 is a reply to message #495472] Sat, 26 February 2011 10:01 Go to previous message
oraQ
Messages: 57
Registered: January 2011
Member
Hope these types of examples are very good to start with such things. Thanks everyone for guiding me in this regard.
Previous Topic: RMAN hot backup script to disk
Next Topic: RMAN Backup on DataGaurd Database
Goto Forum:
  


Current Time: Thu Mar 28 11:09:42 CDT 2024