Importing Oracle 19c Database Dump: A Comprehensive Guide

Importing Oracle 19c Database Dump: A Comprehensive Guide

Importing an Oracle 19c database dump is a common task for database administrators and developers. Whether you’re migrating data, setting up a new environment, or recovering from a backup, understanding how to use the impdp utility is essential. In this article, we'll walk you through the steps to import an Oracle 19c database dump, covering user creation, directory setup, and important import parameters like REMAP_SCHEMA and table_exists_action.

Setting Up Directory and Permissions

1. Creating Directory for Data Pump

Create a directory for Data Pump using the CREATE DIRECTORY command:

CREATE DIRECTORY dp_dir AS '/u01/backups/datapump/';

2. Granting Permissions to Export User

Grant read and write permissions on the directory to the export user (expuser):

GRANT READ, WRITE ON DIRECTORY dp_dir TO expuser;

Creating Users and Granting Permissions

3. Creating expuser

Create a new user expuser and grant necessary privileges:

CREATE USER expuser IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO expuser;
GRANT imp_full_database TO expuser;
GRANT dba TO expuser;

4. Creating Additional Users

Create additional users (ahmet1 and ahmet) and grant necessary privileges:

CREATE USER ahmet1 IDENTIFIED BY test123;
GRANT CONNECT, RESOURCE TO ahmet1;
GRANT imp_full_database TO ahmet1;
GRANT dba TO ahmet1;
CREATE USER ahmet IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO ahmet;
GRANT imp_full_database TO ahmet;
GRANT dba TO ahmet;

Creating Tablespaces

5. Creating Tablespaces for Users

Create tablespaces for users expuser, ahmet1, and ahmet:

CREATE TABLESPACE EXP_DATA2 
DATAFILE '/u01/app/oracle/oradata/CBS/EXP_DATA2.dbf' 
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
ALTER USER expuser DEFAULT TABLESPACE EXP_DATA2;
CREATE TABLESPACE DENEME 
DATAFILE '/u01/app/oracle/oradata/CBS/DENEME.dbf' 
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
ALTER USER ahmet1 DEFAULT TABLESPACE DENEME;

Importing Database Dump Using impdp

6. Importing Database Dump

Use the impdp utility to import the database dump:

impdp expuser/password@cbs_yol3 directory=dp_dir dumpfile=FULL_DB_02.dmp logfile=IMP.log REMAP_SCHEMA=old_schema:new_schema exclude=PROC_SYSTEM_GRANT,USER,TABLESPACE,DIRECTORY FULL=Y table_exists_action=replace

Conclusion

Importing an Oracle 19c database dump involves several steps, from setting up directories and permissions to creating users, tablespaces, and finally importing the dump using the impdp utility. Understanding these steps and the various import parameters like REMAP_SCHEMA and table_exists_action is crucial for successfully importing the database dump and ensuring data integrity.

By following the comprehensive guide provided in this article, you can successfully import an Oracle 19c database dump and manage users, tablespaces, and import parameters tailored to your specific requirements.