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

Transportable Tablespaces (TTS) is a feature in Oracle databases that allows to move large amounts of data between databases quickly by transporting entire 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 | DB LINK

DB Link (Database Link):

A Database Link (DB Link) like a bridge between two databases. It allows one Oracle database to connect to and access objects (like tables, views, etc.) in another Oracle database—as if they were part of the same system.

Two types of Database links:

· Private DB Links (Only user who created the link)

CREATE DATABASE LINK dblink_name CONNECT TO remote_db_user IDENTIFIED BY password USING ‘tnsnames_entry’;

· Public DB Links (All the database users)

CREATE PUBLIC DATABASE LINK dblink_name CONNECT TO remote_db_user IDENTIFIED BY password USING ‘tnsnames_entry’;

Dictionary View - DBA_DB_LINKS

v Local Database [20.20.20.21]

[oracle@local admin]$ cat tnsnames.ora

MAXPDB =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = maxpdb)

)

v Remote Database [20.20.20.23] – where I want to access table from Local Database [20.20.20.21]

Ñ Require information from Local Database [20.20.20.21]

· Username

· Password

· TNS details

[oracle@remote admin]$ vi tnsnames.ora

LOCALPDB =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = maxpdb)

)

[oracle@remote ~]$ sqlplus sys/oracle@maxpdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 15 20:40:49 2025

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

SQL> CREATE DATABASE LINK testdb CONNECT TO hr IDENTIFIED BY hr USING 'LOCALPDB';

Database link created.

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

CREATE DATABASE LINK testdb CONNECT TO hr IDENTIFIED BY hr USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=20.20.20.21)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=maxpdb)))';

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

SQL> SELECT * FROM employees@testdb;

v Drop Database Link

SQL> DROP DATABASE LINK testdb;

Database link dropped.

A NETWORK_LINK is a parameter used with the impdp (Data Pump Import) utility that allows you to import data directly from a remote database over the network — without needing to create or copy .dmp files. It relies on a database link (DB LINK) to connect the target database (where you're running impdp) to the source database (where the data lives).

#Source (Local) 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:

REMOTE =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = maxpdb)

)

#Target (remote) Database:

Step 1: Adding TNS entry in tnsnames.ora

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

REMOTE =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = maxpdb)

)

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 testdb CONNECT TO hr IDENTIFIED BY hr USING 'REMOTE';

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=imp_ocp.log network_link=testdb schemas=ocp

Import: Release 12.2.0.1.0 - Production on Sun Dec 26 12:54:34 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

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