<mohammadrony>

Backup and Restore

Backup Database

Using expdp command

Set Environment variables

export version=19 #21 23
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/${version}.0.0/dbhome_1
export ORACLE_SID=orcl
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export TIMESTAMP=`date +%a%d%b%Y`

CREATE DIRECTORY BACKUP_DIR AS '/backups';
GRANT READ, WRITE ON DIRECTORY BACKUP_DIR TO username;

sudo mkdir /backups
sudo chown oracle:oracle /backups

expdp 'user/password@sid' dumpfile=expdp_database_${TIMESTAMP}.dmp logfile=expdp_database_${TIMESTAMP}.log schemas=database_schemas compression=all exclude=table:"in ('table_name')"
# Might need to escape ' " and () with \

Using SQL Developer

Setting Export Environment

  1. Open SQL developer
  2. Go to Tools
  3. Preferences
  4. Expand Database
  5. Expand Utilities
  6. Select the Export option
  7. Set it for 'insert'
  8. Select Save As 'Single File' to export data into a single file
  9. Save.

Exporting the Database

  1. Open SQL developer
  2. Click Tools
  3. Database Export
  4. Select the database you want to Export from the connection dropdown
  5. Click next
  6. Select all the object types
  7. Click next
  8. Review the information in the Summary
  9. Click Finish.

Restore Database

Oracle Enterprise Manager

Download software

wget -O em13300_linux64.bin https://download.oracle.com/otn/linux/oem/13300/em13300_linux64.bin

Using impdp command

Create Backup directory

CREATE DIRECTORY BACKUP_DIR AS '/backups';
GRANT READ, WRITE ON DIRECTORY BACKUP_DIR TO username;

Grant Permissions to User

GRANT CREATE SESSION TO username;
ALTER USER username QUOTA UNLIMITED ON USERS;
GRANT DBA TO username;

Table spaces

SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;
SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'MYDATA';

Create data tables

CREATE TABLESPACE MYDATA DATAFILE 'MYDATA_01.dbf' SIZE 1M AUTOEXTEND ON, 'MYDATA_02.dbf' SIZE 1M AUTOEXTEND ON;
CREATE TABLESPACE MYIDX DATAFILE 'MYIDX.dbf' SIZE 1M AUTOEXTEND ON;

Add data files

ALTER TABLESPACE MYDATA ADD DATAFILE 'MYDATA_03.dbf' SIZE 1M AUTOEXTEND ON, 'MYDATA_04.dbf' SIZE 1M AUTOEXTEND ON;

Delete tablespaces

DROP TABLESPACE MYDATA INCLUDING CONTENTS AND DATAFILES;

Import data from file

impdp username/password@MYDB DIRECTORY=BACKUP_DIR DUMPFILE=MYDB.dmp LOGFILE=MYDB.log SCHEMAS=SCHEMA_NAME

impdp username/password@MYDB DIRECTORY=BACKUP_DIR DUMPFILE=MYDB.dmp LOGFILE=MYDB.log REMAP_SCHEMA=SOURCE_SCHEMA:DEST_SCHEMA