When working with Oracle databases, exporting schema and other database objects from databases and importing them into new database copies is a common task that anyone may come across. Oracle data-pump is now the oracle supported and recommended technology for database exports and imports.
The following images gives an illustration on how oracle data-pump operates.
In the oracle documentation it is nicely explained what oracle data pump is and what it really does.
You can get to oracle documentation from here
But when working with Oracle Data pump for a project that I was involved in, I realized that there are very few simple and to the point articles on how to use this. Therefore I am writing this blog post with some simplified steps of how to use Oracle data-pump.
Here is how you can import a dump file in to a database using Oracle Data pump.
1. To use the oracle data pump first you should prepare a parameter file. Recommended file extension for this file is .par. But you can use any file extension you prefer. (even .txt would ok to use).
The name of the parameter file that I am using for this article is 'imp_full_db.txt'.
The mandatory parameters in the file are,
2. To use Oracle data pump you should be aware of the 'system' user in your database and you should make sure that you have required privileges for the 'system' user to operate as expected.
After verifying it, login to your database through sqlplus through the 'system' user.
--- sqlplus system/password@dbhost/dbsid
Once you are logged in execute the following command to create the oracle directory. Pleas enote that executing this will only create a directory in the oracle memory. It won't create any physical directory in the hard drive.
--- create or replace directory impdp as 'D:\export\dbsid\';
After that run this sql to check if the directory is properly created,
select directory_name, directory_path from dba_directories where directory_name='impdp';
Once you are done, exit from the sqlplus prompt and return to cmd prompt. Change your command prompt path to location where you have the parameter file and dump file.
3. After that the final step should be to execute command which starts the import process.
--- impdp system/password@dbhost/dbsid parfile=imp_full_db.txt
This should start the import and the output will be written to 'imp_full_db.log'.
When doing an export from a database, You can use the same entries in the parameter file with some different values.
After creating the oracle directory export should be executed using the 'expdp' command.
--- expdp system/password@dbhost/dbsid parfile=exp_full_db.txt
The following images gives an illustration on how oracle data-pump operates.
 |
| Image Source: http://i-3.co.id/tips-berguna-pada-oracle-datapump-expdp-dan-impdp-11g |
In the oracle documentation it is nicely explained what oracle data pump is and what it really does.
You can get to oracle documentation from here
But when working with Oracle Data pump for a project that I was involved in, I realized that there are very few simple and to the point articles on how to use this. Therefore I am writing this blog post with some simplified steps of how to use Oracle data-pump.
Here is how you can import a dump file in to a database using Oracle Data pump.
1. To use the oracle data pump first you should prepare a parameter file. Recommended file extension for this file is .par. But you can use any file extension you prefer. (even .txt would ok to use).
The name of the parameter file that I am using for this article is 'imp_full_db.txt'.
The mandatory parameters in the file are,
JOB_NAME=FULL_IMP
FULL=Y
CONTENT=ALL
DIRECTORY=IMPDPDIR
DUMPFILE=FULL_EXP_ANONYMIZED_HYPER5.DMP
CONTENT=ALL
DIRECTORY=IMPDPDIR
DUMPFILE=FULL_EXP_ANONYMIZED_HYPER5.DMP
LOGFILE=imp_full_db.log
Since we will execute the oracle commands from the same directory were this parameter file is, it is not necessary to have the full paths. In that case please make sure you have the oracle dump file in the same directory too.
If the schema in the original database (one which the import was taken) is different to schema which you are trying to import the dump file into, then an additional parameter should be included to map the schema.
REMAP_SCHEMA=source_schema:target_schema
If the schema in the original database (one which the import was taken) is different to schema which you are trying to import the dump file into, then an additional parameter should be included to map the schema.
REMAP_SCHEMA=source_schema:target_schema
2. To use Oracle data pump you should be aware of the 'system' user in your database and you should make sure that you have required privileges for the 'system' user to operate as expected.
After verifying it, login to your database through sqlplus through the 'system' user.
--- sqlplus system/
Once you are logged in execute the following command to create the oracle directory. Pleas enote that executing this will only create a directory in the oracle memory. It won't create any physical directory in the hard drive.
--- create or replace directory impdp as 'D:\export\dbsid
After that run this sql to check if the directory is properly created,
select directory_name, directory_path from dba_directories where directory_name='impdp';
Once you are done, exit from the sqlplus prompt and return to cmd prompt. Change your command prompt path to location where you have the parameter file and dump file.
3. After that the final step should be to execute command which starts the import process.
--- impdp system/
This should start the import and the output will be written to 'imp_full_db.log'.
When doing an export from a database, You can use the same entries in the parameter file with some different values.
After creating the oracle directory export should be executed using the 'expdp' command.
--- expdp system/password@dbhost/dbsid
No comments:
Post a Comment