Backing up and restoring a database on Oracle

Back up and restore your Oracle database with the Oracle Data Pump technology. The expdp and impdp parameters enable to export and import data and metadata for a complete database or for subsets of a database.

You have created a directory on your server machine to store all the exported files, such as .dump or .log files.
You have logged on to your database as sys user with sysdba privileges.
```
sqlplus.exe / as sysdba
```
You have created a directory object that points to the directory located on your server machine for reading and writing files.
```
CREATE OR REPLACE DIRECTORY <directory_object_name> AS '<backup_directory_path>';
```
You have granted read and write privileges on the directory object.
```
GRANT read, write ON DIRECTORY <directory_object_name> TO <user_name>;
```
You have stopped Talend Data Catalog application server before performing the backup and restore tasks.

Backing up using the expdp command

Open a command prompt window and execute the following command to backup your Oracle database to a file and write the export log in the operating system directory:
```
expdp <username>/<password> schemas=<schema_name> directory=<directory_object_name> dumpfile=<dump_file_name> logfile=<log_file_name>
```

Restoring using the impdp command

Drop the schema to delete existing objects and data from the database instance. The restore will recreate the schema.
```
sqlplus.exe SYS@<db_name> as SYSDBA
DROP USER <user_name> CASCADE;
```
Execute the following command to restore your Oracle database from a file and write the import log in the operating system directory:
```
Impdp schemas=<schema_name> directory=<directory_object_name> dumpfile=<dump_file_name> logfile=<log_file_name>
```
When prompted for Username, enter / as sysdba.
Restart Talend Data Catalog application server.
To ensure the optimal Talend Data Catalog application server performance, go to MANAGE > Schedules to run the database maintenance script.
Right-click Run Database Maintenance and click Run operation now to update the database indexes and statistics.

For more information on the expdp and impdp commands, refer to the Oracle Data Pump documentation.

You can also use the Oracle Recovery Manager (RMAN). It is recommended to create a separate table space for the database instance and restore only from that table space. For more information, refer to Oracle Database Backup and Recovery User's Guide.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here