.::: How to backup and restore table & database Oracle using import (impdb) & export (expdp) backup full, pertable in Oracle 19C, 21C :::.

 

A. Create DATA_PUMP_DIR or dba_directories

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO aisyah;

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO ADMIN;

CREATE OR REPLACE DIRECTORY BACKUP_DAILY AS '/u01/backup/daily';
GRANT READ, WRITE ON DIRECTORY BACKUP_DAILY TO aisyah;

SELECT directory_name, directory_path
FROM dba_directories
  

B. Backup & Restore table Oracle
1. Backup Table Database oracle
exp aisyah/hanin@tgh tables=pasok file="/home/oracle/backup/pasok.dmp"
exp aisyah/hanin@localhost:1521/tgh tables=pasok file="/home/oracle/backup/pasok.dmp"

expdp aisyah/hanin@tgh tables=pasok file="/home/oracle/backup/pasokdp.dmp"
expdp aisyah/hanin@localhost:1521/tgh tables=pasok file="/home/oracle/backup/pasokdp.dmp" logfile=pasokdp.log

2. Restore Table Oracle
imp aisyah/hanin@tgh
imp aisyah/hanin@tgh file="/home/oracle/backup/pasok.dmp" IGNORE=Y TABLES=pasok CONSTRAINTS=N

-- force restore all (table+data)
impdp aisyah/hanin@tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=pasokdp.dmp LOGFILE=import_force.log TABLE_EXISTS_ACTION=REPLACE IGNORE=Y

-- force restore data only
impdp aisyah/hanin@tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=pasokdp.dmp LOGFILE=import_force.log TABLE_EXISTS_ACTION=TRUNCATE IGNORE=Y

C. Backup & restore database oracle
1. backup database oracle
exp aisyah/hanin@tgh
exp aisyah/hanin@tka@localhost:1521/tgh

exp aisyah/hanin@localhost:1521/tgh file="/home/oracle/backup/alltableahk.dmp"
expdp aisyah/hanin@localhost:1521/tgh file="/home/oracle/backup/alltableahkdp.dmp" logfile=alltableahkdp.log
expdp aisyah/hanin@localhost:1521/tgh file="/home/oracle/backup/alltableahkexlucedp.dmp" exclude=procedure logfile=alltableahkexlucedp.log

expdp aisyah/hanin@localhost:1521/tgh DUMPFILE=alltableahkdp.dmp logfile=alltableahkdp.log

exp admin/oracle@localhost:1521/tgh file="/home/oracle/backup/alltableadm.dmp" DIRECT=Y
expdp admin/oracle@localhost:1521/tgh file="/home/oracle/backup/alltableadmdp.dmp"
 

2. import database oracle

imp aisyah/hanin@localhost:1521/tgh
imp aisyah/hanin@localhost:1521/tgh  file="/home/oracle/backup/alltableahk.dmp"
imp aisyah/hanin@localhost:1521/tgh tables=barang,suplier,customer,pasok,pembelian file="/home/oracle/backup/alltableahk.dmp" IGNORE=Y
imp aisyah/hanin@localhost:1521/tgh tables=barang,suplier file="/home/oracle/backup/alltableahk.dmp" IGNORE=Y

impdp aisyah/hanin@tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=alltableahkdp.dmp LOGFILE=import_force.log TABLE_EXISTS_ACTION=REPLACE
impdp aisyah/hanin@tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=alltableahkdp.dmp LOGFILE=import_force.log tables=barang,suplier TABLE_EXISTS_ACTION=REPLACE
 

D. Backup custome

expdp aisyah/hanin@localhost:1521/tgh directory=DATA_PUMP_DIR dumpfile=backup_table_only.dmp content=metadata_only tables=barang,customer reuse_dumpfiles=y
expdp aisyah/hanin@localhost:1521/tgh directory=DATA_PUMP_DIR dumpfile=backup_data_only.dmp content=data_only tables=barang,customer
expdp aisyah/hanin@localhost:1521/tgh directory=DATA_PUMP_DIR dumpfile=backup_all.dmp content=all tables=barang,customer

impdp aisyah/hanin@tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=backup_table_only.dmp LOGFILE=import_force.log TABLE_EXISTS_ACTION=REPLACE
impdp aisyah/hanin@tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=backup_data_only.dmp LOGFILE=import_force.log
impdp aisyah/hanin@tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=backup_all.dmp LOGFILE=import_force.log TABLE_EXISTS_ACTION=REPLACE