| TO Make A Copy Of Oracle DB in Local PC [message #492869] |
Thu, 03 February 2011 05:06  |
 |
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 #494686 is a reply to message #494683] |
Wed, 16 February 2011 10:16  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #495444 is a reply to message #492869] |
Sat, 19 February 2011 07:08 |
 |
itralarcon
Messages: 36
Registered: February 2011
Location: San Salvador
|
Member |
|
|
There are some methods to backup an Oracle Database:
- Import/Export Utility
- 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 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
itralarcon
Messages: 36
Registered: February 2011
Location: San Salvador
|
Member |
|
|
Michel Cadot wrote on Sat, 19 February 2011 09:10I 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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
