both are methods of taking logical backups in these 2 databases.
Oracle Data Pump: expdp
1. Prepare the OS directorybashsudo mkdir -p /backup && sudo chown oracle:oinstall /backup2. Connect with SQL*Plus and list containerssql$ sqlplus / as sysdbaSQL> show con_nameCON_NAME------------------------------CDB$ROOTSQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 READ WRITE NO 4 ORCLPDB2 READ WRITE NO3. Create sample data in orclpdb1sqlSQL> alter session set container = orclpdb1;Session altered.SQL> create user scott identified by tiger quota unlimited on users;User created.SQL> grant create session, create table to scott;Grant succeeded.SQL> create table scott.employees (id number primary key, name varchar2(50), dept varchar2(20));Table created.SQL> insert into scott.employees values (1,'Anna','IT');SQL> insert into scott.employees values (2,'Erik','HR');SQL> insert into scott.employees values (3,'Lena','Finance');SQL> commit;SQL> select * from scott.employees; ID NAME DEPT---------- -------------------------------------------------- -------------------- 1 Anna IT 2 Erik HR 3 Lena Finance4. Create the Oracle directory object (inside the PDB)sqlSQL> create or replace directory backup_dir as '/backup';Directory created.SQL> grant read, write on directory backup_dir to scott;Grant succeeded.SQL> select directory_name, directory_path from dba_directories where directory_name='BACKUP_DIR';DIRECTORY_NAME DIRECTORY_PATH---------------- ---------------BACKUP_DIR /backupThe directory object must be created inside the PDB, because Data Pump runs against a PDB and a CDB$ROOT directory is not visible there.5. Run expdp against orclpdb1expdp connects through the listener using the PDB service name, not / as sysdba (which lands in CDB$ROOT):bashexpdp system@//localhost:1521/orclpdb1 \ schemas=scott \ directory=backup_dir \ dumpfile=orclpdb1_scott.dmp \ logfile=orclpdb1_scott_exp.logFor a full PDB export instead of one schema, replace schemas=scott with full=y.Expected output (trimmed):Export: Release 19.0.0.0.0 - ProductionConnected to: Oracle Database 19c Enterprise Edition - ProductionStarting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@//localhost:1521/orclpdb1 schemas=scott directory=backup_dir dumpfile=orclpdb1_scott.dmp logfile=orclpdb1_scott_exp.logProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATAProcessing object type SCHEMA_EXPORT/TABLE/TABLE. . exported "SCOTT"."EMPLOYEES" 5.5 KB 3 rowsMaster table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloadedDump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /backup/orclpdb1_scott.dmpJob "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed
Postgres : pg_dump
postgres=# CREATE DATABASE pgdump_test;CREATE DATABASEpostgres=# \c pgdump_testpsql (16.15 (Ubuntu 16.15-0ubuntu0.24.04.1), server 15.16 (Ubuntu 15.16-1.pgdg24.04+1))You are now connected to database "pgdump_test" as user "postgres".pgdump_test=# SELECT current_database(); current_database------------------ pgdump_test(1 row)pgdump_test=# CREATE TABLE employees ( employee_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, employee_name varchar(100) NOT NULL, department varchar(50), salary numeric(10,2), joining_date date);CREATE TABLEpgdump_test=# CREATE TABLE departments ( department_id integer PRIMARY KEY, department_name varchar(50) NOT NULL, location varchar(50));CREATE TABLEpgdump_test=# \dt List of relations Schema | Name | Type | Owner--------+-------------+-------+---------- public | departments | table | postgres public | employees | table | postgres(2 rows)pgdump_test=# INSERT INTO employees (employee_name, department, salary, joining_date)VALUES ('John Smith', 'Database', 75000, '2022-01-10'), ('Mary Jones', 'Database', 72000, '2022-03-15'), ('David Miller', 'Linux', 68000, '2021-06-20'), ('Sarah Brown', 'Cloud', 82000, '2020-11-05'), ('Robert Wilson','Cloud', 79000, '2023-02-12'), ('Lisa Taylor', 'Backup', 70000, '2022-08-17'), ('James Davis', 'Linux', 71000, '2021-12-01'), ('Emma White', 'Database', 76000, '2023-04-18'), ('Michael Clark','Backup', 69000, '2020-07-25'), ('Sophia Lewis', 'Cloud', 85000, '2024-01-08');INSERT 0 10pgdump_test=# INSERT INTO departmentsVALUES (10, 'Database', 'Stockholm'), (20, 'Linux', 'London'), (30, 'Cloud', 'Frankfurt'), (40, 'Backup', 'Amsterdam');INSERT 0 4pgdump_test=# select * from employees; employee_id | employee_name | department | salary | joining_date-------------+---------------+------------+----------+-------------- 1 | John Smith | Database | 75000.00 | 2022-01-10 2 | Mary Jones | Database | 72000.00 | 2022-03-15 3 | David Miller | Linux | 68000.00 | 2021-06-20 4 | Sarah Brown | Cloud | 82000.00 | 2020-11-05 5 | Robert Wilson | Cloud | 79000.00 | 2023-02-12 6 | Lisa Taylor | Backup | 70000.00 | 2022-08-17 7 | James Davis | Linux | 71000.00 | 2021-12-01 8 | Emma White | Database | 76000.00 | 2023-04-18 9 | Michael Clark | Backup | 69000.00 | 2020-07-25 10 | Sophia Lewis | Cloud | 85000.00 | 2024-01-08(10 rows)pgdump_test=# select * from departments; department_id | department_name | location---------------+-----------------+----------- 10 | Database | Stockholm 20 | Linux | London 30 | Cloud | Frankfurt 40 | Backup | Amsterdam(4 rows)pgdump_test=# \q
Leave a Reply