Backup and Restore
Data Pump
Create a user with proper privileges...
SQL> create user <user> identified by <password>;
User created.
SQL> grant connect, resource, exp_full_database, imp_full_database to <user>;
Grant succeeded.
Create a directory...
SQL> create directory <dir_name> as '<dir_location>';
Directory created.
SQL> grant read, write on directory <dir_name> to <user>;
Grant succeeded.
Export full database...
expdp <user>/<password>@<database> \
DIRECTORY=<dir_name> \
DUMPFILE=full_exp.dmp \
LOGFILE=full_exp.log \
FULL=YES
To export tablespaces only...
Oracle comes with the following default tablespaces: SYSTEM, SYSAUX, USERS, UNDOTBS1, and TEMP.
To view all the tablespaces...
SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;
To export tablespaces...
expdp <user>/<password>@<database> \
DIRECTORY=<dir_name> \
DUMPFILE=tablespace.dmp \
LOGFILE = tablespace_log.log \
TABLESPACES=TABLESPACE_ONE,TABLESPACE_TWO
Tips & tricks on taking backups...
Here are some of my favorite articles:
https://www.oracle-dba-online.com/retention-policy-in-oracle-rman.htm
http://www.dba-oracle.com/t_rman_recovery_window_retention.htm
CREATE OR REPLACE directory DATA_PUMP_DIR AS '/home/oracle/datapump';
impdp 'system/password@ABERTEST' dumpfile=exp_ABER_TRILNT_20200612_01.dmp,exp_ABER_TRILNT_20200612_02.dmp,exp_ABER_TRILNT_20200612_03.dmp,exp_ABER_TRILNT_20200612_04.dmp schemas=TRILNT_TM,TRILNT_KM
SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
NOARCHIVELOG
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/u02/oracle/logfiles';
[root@gesaasora1 ]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 63G 0 63G 0% /dev
tmpfs 63G 0 63G 0% /dev/shm
tmpfs 63G 149M 63G 1% /run
tmpfs 63G 0 63G 0% /sys/fs/cgroup
/dev/mapper/rhel-root 50G 21G 30G 41% /
/dev/mapper/rhel-ora_rman_medium 1000G 33M 1000G 1% /ora_rman_medium
/dev/mapper/rhel-ora 50G 30G 21G 60% /ora
/dev/mapper/rhel-ora_export_medium 1000G 33M 1000G 1% /ora_export_medium
/dev/mapper/rhel-oracle 1000G 103G 897G 11% /oracle
/dev/mapper/rhel-fast_recovery_area 450G 2.5G 448G 1% /ora/app/oracle/fast_recovery_area
/dev/mapper/rhel-oradata 900G 403G 498G 45% /ora/app/oracle/oradata
/dev/sda1 950M 235M 715M 25% /boot
tmpfs 13G 0 13G 0% /run/user/0
tmpfs 13G 4.0K 13G 1% /run/user/54321
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 420G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/ora/app/oracle/fast_recovery_area';
---
Fixing things...
RMAN
configure retention policy to redundancy 2;
configure retention policy to recovery window of 7 days;