DAY 14 - LOGICAL BACKUP | DATAPUMP UTILITY

q Oracle DATAPUMP utility – Expdp | Impdp

q Data pump (EXPDP & IMPDP) utilities are used to take Import and Export of Oracle databases.

& Following levels of Data Pump Export | Import is possible:

v Database Level

v Schema Level

v Table Level

v Row Level

v Tablespace Level

& We can Export and Import from two users; one is the user who owns the object & another is the SYS user. In real time it is always good to perform export & import using SYS user. You don't have to bother about this permission and all

q DATA PUMP [SERVER-SIDE UTILITY/INFRASTRUCTURE FAST DATA MOVEMENT BETWEEN ORACLE DATABASE]

v EXPDP

v IMPDP

q DATAPUMP UTILITY USES STREAMS POOL SIZE MEMORY AREA

SQL> show parameter streams;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

streams_pool_size big integer 0

SQL> alter system set STREAMS_POOL_SIZE=200M scope=both; [CURRENTLY AUTOMATIC MEMORY MANAGEMENT]

q Create Directory at OS level

[oracle@dbserver]$ export ORACLE_PDB_SID=maxpdb

[oracle@dbserver]$ mkdir -p /u01/backup

[oracle@dbserver]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 2 21:47:11 2023

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show con_name

CON_NAME

------------------------------

MAXPDB

q To Create Directory on Database level

SQL> create or replace directory datapump as '/u01/backup';

Directory created.

q To view Directory Information

SQL> select OWNER, DIRECTORY_NAME, DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='DATAPUMP';

OWNER DIRECTORY_NAME DIRECTORY_PATH

------------------------------ ------------------------------ ------------------------------

SYS DATAPUMP /u01/backup

q Check Estimate Export Time

[oracle@dbserver] expdp estimate_only=y schemas=hr

Username: / as sysdba

Password: oracle

q To take DATABASE Level export

[oracle@dbserver]$ expdp \"sys/oracle@maxpdb as sysdba\" directory=datapump dumpfile=fullprod.dmp logfile=fullprod.log full=y

[oracle@dbserver]$ expdp directory=datapump dumpfile=fullprod.dmp logfile=fullprod.log full=y

Export: Release 19.0.0.0.0 - Production on Wed Jan 31 20:03:58 2024

Version 19.3.0.0.0

Username: / as sysdba

Password: oracle

q To Import a Database

[oracle@dbserver]$ impdp directory=datapump dumpfile=fullprod.dmp logfile=fullprod_imp.log full=y

q To taka SCHEMA level export

[oracle@dbserver]$ expdp directory=datapump dumpfile=hr.dmp logfile=hr.log schemas=hr

Export: Release 19.0.0.0.0 - Production on Wed Jan 31 21:01:14 2024

Version 19.3.0.0.0

Username: / as sysdba

Password: oracle

SQL> grant read, write on directory datapump to hr;

[oracle@dbserver]$ expdp \"hr/hr@maxpdb\" directory=datapump dumpfile=hr.dmp logfile=hr.log schemas=hr

[oracle@dbserver]$ expdp directory=datapump dumpfile=hr.dmp logfile=hr.log schemas=hr

Export: Release 19.0.0.0.0 - Production on Wed Jan 31 21:27:56 2024

Version 19.3.0.0.0

Username: hr/hr@maxpdb

q To Import a Schema

[oracle@dbserver]$ impdp directory=datapump dumpfile=hr.dmp logfile=hr_imp.log

Import: Release 19.0.0.0.0 - Production on Wed Jan 31 23:37:06 2024

Version 19.3.0.0.0

Username: / as sysdba

Password: oracle

[oracle@dbserver]$ impdp \"hr/hr@maxpdb\" directory=datapump dumpfile=hr.dmp logfile=hr_imp.log remap_schema=hr:xy

q To taka schema level export using CONTENT

[oracle@dbserver]$ expdp directory=datapump dumpfile=hr_content.dmp logfile=hr_content.log schemas=hr content=METADATA_ONLY

q To Import a Schema using Content

[oracle@dbserver]$ impdp directory=datapump dumpfile=hr_content.dmp logfile=hr_content_imp.log schemas=hr content=METADATA_ONLY

q To taka schema level export using QUERY

[oracle@dbserver]$ expdp directory=datapump dumpfile=hr_content.dmp logfile=hr_content.log schemas=hr content=DATA_ONLY

q To Import a Schema using Query

[oracle@dbserver]$ impdp directory=datapump dumpfile=hr_content.dmp logfile=hr_content_imp.log schemas=hr content=DATA_ONLY

q TABLE_EXISTS_ACTION=[SKIP|APPEND|TRUNCATE|REPLACE] using IMPDP

q To taka Schema level export using ENCRYPTION

[oracle@dbserver]$ expdp directory=datapump dumpfile=hr_enc_pass.dmp logfile=hr_enc_pass.log encryption=all encryption_mode=password encryption_password=hrpass123 schemas=hr

q To Import a Schema using Encryption

[oracle@dbserver]$ impdp directory=datapump dumpfile=hr_enc_pass.dmp logfile=hr_enc_pass.log encryption=all encryption_mode=password encryption_password=hrpass123 schemas=hr

q To taka Schema level export using COMPRESSION

[oracle@dbserver]$ expdp directory=datapump dumpfile=hr_compress.dmp logfile=hr_compress.log schemas=hr compression=all compression_algorithm=high

q To Import a Schema using compression

[oracle@dbserver]$ impdp directory=datapump dumpfile=hr_compress.dmp logfile=hr_compress_imp.log schemas=hr compression=all compression_algorithm=high

q To taka Schema level export using PARALLEL

[oracle@dbserver]$ expdp directory=datapump dumpfile=hr.dmp logfile=hr.log schemas=hr parallel=4

q To Import a Schema using Parallel

[oracle@dbserver]$ impdp directory=datapump dumpfile=hr.dmp logfile=hr_imp.log schemas=hr parallel=4

q To take table level export

[oracle@dbserver]$ expdp directory=datapump dumpfile=hr_emp.dmp logfile=hr_emp.log tables=hr.employees

Export: Release 19.0.0.0.0 - Production on Mon Oct 2 21:53:35 2023

Version 19.3.0.0.0

Username: / as sysdba

Password: oracle

q To import table level

[oracle@dbserver]$ impdp directory=datapump dumpfile=hr_emp.dmp logfile=hr_emp_imp.log tables=hr.employees

[oracle@dbserver]$ impdp directory=datapump dumpfile=hr_emp.dmp logfile=hr_emp_imp.log remap_table=hr.employees:emp

q To take table level export using Include

[oracle@dbserver]$ expdp directory=datapump dumpfile=hr_emp_include.dmp logfile=hr_emp_include.log schemas=hr include=TABLE:\""IN ('employees')\""

q To Import a Table using Include

[oracle@dbserver]$ impdp directory=datapump dumpfile=hr_emp_include.dmp logfile=hr_emp_include_imp.log schemas=hr include=TABLE:\""IN ('employees')\""

q To take table level export using Exclude

[oracle@dbserver]$ expdp directory=datapump dumpfile=hr_emp_exclude.dmp logfile=hr_emp_exclude.log schemas=hr exclude=TABLE:\""IN ('employees')\""

q To Import a Table using Exclude

[oracle@dbserver]$ impdp directory=datapump dumpfile=hr_emp_exclude.dmp logfile=hr_emp_exclude_imp.log schemas=hr exclude=TABLE:\""IN ('employees')\""

q To taka row level export

[oracle@dbserver]$ expdp directory=datapump dumpfile=emprows.dmp logfile=emprows.log tables=hr.employees query=\"where department_id=10\"

q To Import a Row

[oracle@dbserver]$ impdp directory=datapump dumpfile=emprows.dmp logfile=emprows_imp.log tables=hr.employees query=\"where department_id=10\"

q To taka tablespace level export

[oracle@dbserver]$ expdp directory=datapump dumpfile=userstbs.dmp logfile=userstbs.log tablespaces=users

q To Import a tablespace

[oracle@dbserver]$ impdp directory=datapump dumpfile=userstbs.dmp logfile=userstbs_imp.log tablespaces=users

[oracle@dbserver]$ impdp directory=datapump dumpfile=userstbs.dmp logfile=userstbs_imp.log remap_tablespace=users:xyz

q To Export | Import Transportable Tablespaces

v SOURCE Database: 192.168.56.33

[oracle@dbserver]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 5 18:42:27 2023

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

v Switching the Tablespace to read only mode

SQL> ALTER TABLESPACE SRTBS READ ONLY;

Tablespace altered.

q Export the tablespace MetaData

[oracle@dbserver u01]$ expdp directory=datapump transport_tablespaces=SRTBS dumpfile=SRTBS_Transport.dmp logfile=SRTBS_Transport.log

Export: Release 19.0.0.0.0 - Production on Thu Oct 5 18:44:42 2023

Version 19.3.0.0.0

Username: / as sysdba

Password: oracle

Datafiles required for transportable tablespace SRTBS:

/data/oradata/MAXCDB/maxpdb/srtbs01.dbf

[oracle@dbserver]$ cd /u01/backup/

[oracle@dbserver backup]$ ls

SRTBS_Transport.dmp

SRTBS_Transport.log

[oracle@dbserver backup]$ scp /u01/backup/SRTBS_Transport.dmp oracle@192.168.56.98:/u01/backup/

The authenticity of host '192.168.56.98 (192.168.56.98)' can't be established.

ECDSA key fingerprint is SHA256:03rpoIcbmOdnzUsNLdt49TS76MexJny3nw+ayshne1o.

ECDSA key fingerprint is MD5:7b:0c:36:29:68:83:9a:6f:d9:6c:19:c4:85:83:d2:71.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.56.98' (ECDSA) to the list of known hosts.

oracle@192.168.56.98's password:

SRTBS_Transport.dmp 100% 532KB 44.8MB/s 00:00

[oracl

[oracle@dbserver backup]$ scp /data/oradata/MAXCDB/maxpdb/srtbs01.dbf oracle@192.168.56.98:/u01/app/oracle/oradata/MAXCDB/maxpdb/

oracle@192.168.56.98's password:

srtbs01.dbf 100% 100MB 27.2MB/s 00:03

v Switching back the Tablespace to read/write mode

SQL> ALTER TABLESPACE SRTBS READ WRITE;

Tablespace altered.

v TARGET Database: 192.168.56.98

[oracle@19c]$ export ORACLE_PDB_SID=maxpdb

[oracle@19c]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 5 19:36:32 2023

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> CREATE USER shohan IDENTIFIED BY oracle;

User created.

SQL> GRANT CONNECT, RESOURCE, DBA TO shohan;

Grant succeeded.

[oracle@19c maxpdb]$ impdp directory=datapump dumpfile=SRTBS_Transport.dmp logfile=SRTBS_Transport_imp.logtransport_datafiles='/u01/app/oracle/oradata/MAXCDB/maxpdb/srtbs01.dbf'

Import: Release 19.0.0.0.0 - Production on Thu Oct 5 19:50:16 2023

Version 19.3.0.0.0

Username: / as sysdba

Password: oracle

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": /******** AS SYSDBA directory=datapump dumpfile=SRTBS_Transport.dmp logfile=SRTBS_Transport_imp.log transport_datafiles=/u01/app/oracle/oradata/MAXCDB/maxpdb/srtbs01.dbf

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu Oct 5 19:50:38 2023 elapsed 0 00:00:16

v Switching back the Tablespace to read/write mode

SQL> ALTER TABLESPACE SRTBS READ WRITE;

Tablespace altered.

q EXPORT | IMPORT using PARFILE [Parameter]

v EXPORT as PAR file

[oracle@19c u01]$ vi /u01/tab.par

DIRECTORY=datapump

DUMPFILE=hr_exp_par.dmp

LOGFILE=hr_exp_par.log

SCHEMAS=hr

[oracle@19c u01]$ expdp parfile=/u01/tab.par

Export: Release 19.0.0.0.0 - Production on Sun Sep 3 10:35:07 2023

Version 19.3.0.0.0

Username: sys/oracle@maxpdb as sysdba

v IMPORT as PAR file

[oracle@19c u01]$ vi /u01/imp_tab.par

DUMPFILE=hr_exp_par.dmp

LOGFILE=xy_imp_par.log

REMAP_SCHEMA=hr:xy

[oracle@19c u01]$ impdp directory=datapump parfile=imp_tab.par

Import: Release 19.0.0.0.0 - Production on Sun Sep 3 12:11:42 2023

Version 19.3.0.0.0

Username: sys/oracle@maxpdb as sysdba

SQL> select CHECKPOINT_CHANGE#, CURRENT_SCN, ARCHIVELOG_CHANGE# from v$database;

CHECKPOINT_CHANGE# CURRENT_SCN ARCHIVELOG_CHANGE#

------------------ ----------- ------------------

2137910 2202280 2137910

SQL> select FILE#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME from v$datafile;

FILE# CHECKPOINT_CHANGE# CHECKPOIN

---------- ------------------ ---------

9 2137910 24-MAR-23

10 2137910 24-MAR-23

11 2137910 24-MAR-23

12 2139676 24-MAR-23

SQL> desc v$backup

Name Null? Type

----------------------------------------- -------- ----------------------------

FILE# NUMBER

STATUS VARCHAR2(18)

CHANGE# NUMBER

TIME DATE

CON_ID NUMBER

q NETWORK_LINK - EXPORT - IMPORT OVER THE NETWORK TWORK_LINK [DB LINK]

#Source Database:

Step 1: Create schema source side

SQL> create user ocp identified by ocp quota unlimited on users;

SQL> grant connect, resource to ocp;

Connect with OCP users and create a table and then insert some records.

SQL> conn ocp/ocp

SQL> create table t1 (id number, name varchar2(10));

SQL> insert into t1 values(10,'AARAV');

SQL> insert into t1 values(20,'SHRIPAL');

SQL> insert into t1 values(30,'SACHIN');

SQL> insert into t1 values(40,'VIKRAM');

SQL> insert into t1 values(50,'VIKASH');

SQL> commit;

Step 2: Grant export and import privileges OCP schema

Using the below query grants privileges to the user.

SQL> grant imp_full_database, exp_full_database to ocp;

Grant succeeded.

Step 3: Copy source TNS entry at target side

copy source tns entry and past it at target side in tnsnames.ora file.

My Source TNS Entry:

DIGITAL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = digital)

)

#Target Database:

Step 1: Adding TNS entry in tnsnames.ora

After adding the tns entry, my tnsnames.ora file looks like below:

DIGITAL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = digital)

)

TECH =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = tech)

)

Step 2: Create a directory

Create a directory for the export backup on the target side.

SQL> create directory dump_dir as '/u01/EXPORT_BKP';

Directory created.

Step 3: Create DB Link

It's time to create a DB Link using the source service name and schema name, which you want to move in another database like OCP.

SQL> create database link digital_DB connect to ocp identified by ocp using 'digital';

Database link created.

Step 4: Import schema using a DB link

The below command helps us to import schemas directly from the source Database. Datapump with network_link.

$ impdp directory=dump_dir logfile=ocp_imp.log network_link=digital_DB schemas=ocp

Import: Release 12.2.0.1.0 - Production on Sun Dec 26 12:54:34 2021

Username: / as sysdba

Step 5: Export schema using Datapump network_link parameter

$ expdp directory=dump_dir logfile=ocp_exp.log network_link=digital_DB dumpfile=ocp_exp.dmp schemas=ocp

Export: Release 12.2.0.1.0 - Production on Sun Dec 26 13:11:24 2021

Username: / as sysdba

License Key

DAY 14 - LOGICAL BACKUP | DATAPUMP UTILITY

Installl Oracle APEX 24.1 | JDK 11 | ORDS 23 | APACHE TOMCAT 9 | JASPER 6 on Linux 9

نموذج الاتصال