Oracle Backup & Recovery

Oracle Backup & Recovery

Oracle Backup is divided in two main parts:

Physical: RMAN / File System
Logical: Exp / IMP, EXPDP / IMPDP

Let's first discuss the Logical backup

Export schema

expdp SCHEMAS=LUKAS DIRECTORY=EXPORT DUMPFILE=lukas.dmp LOGFILE=lukas_Export.log

That file: lukas.dmp will contain all objects owned by this user: Lukas, however for that file to be useful, we need to have running DB into which we can import it.

DB Link Import

Importing via database link is most convenient for data which has t obe migrated between 2 databases. In order to transport data between 2 Oracle Database via LINK we need to:

Create User on the Source
Grant him the necessary rights
Create a DB Link on the Destination
Test the connection

Let's start one by one:

DB Link Creation

Physical - RMAN

Check the Tape library

sbttest test

Creating Recovery catalog

Create dedicated tablespace :

create tablespace rcatalog
datafile '</path/to/db/datafiles/rcatalog01.dbf'
size 24M autoextend on;

Create new user for Catalog connections :

create user rcat identified by <password>
default tablespace rcatalog
quota unlimited on rcatalog;

Grant Recovery catalog privileges to new user :

grant connect,resource,recovery_catalog_owner to rcat;

Create Catalog with RMAN register new DB :

rman target / catalog rcat/<password>@<service>

create catalog tablespace rcatalog;

register database;

Configure default channel device type for TSM :

configure channel device type 'sbt' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

RMAN TSM :

run {
allocate channel ch1 device type 'sbt' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel ch2 device type 'sbt' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel ch3 device type 'sbt' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
backup incremental level 0 database plus archivelog delete input;
release channel ch1;
release channel ch2;
release channel ch3;
}

Backup DB (version 1) :

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS;
run {
allocate channel c1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=C:\Program Files\tivoli\TSM\AgentOBA64\tdpo.opt)';
allocate channel c2 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=C:\Program Files\tivoli\TSM\AgentOBA64\tdpo.opt)';
backup database format 'DB_%d_%s_%p_%t' include current controlfile filesperset 12;
sql 'alter system archive log current';
change archivelog all crosscheck;
backup archivelog all format 'AL_%d_%s_%p_%t' filesperset 32;
backup current controlfile format '%d_%T_%t_%s_%p.ctl';
release channel c1;
release channel c2;
}

RMAN Offline backup using TSM :

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS;
run {
shutdown immediate;
startup mount;
allocate channel c1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=C:\Program Files\tivoli\TSM\AgentOBA64\tdpo.opt)';
allocate channel c2 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=C:\Program Files\tivoli\TSM\AgentOBA64\tdpo.opt)';
backup database format 'DB_%d_%s_%p_%t' include current controlfile filesperset 12;
backup current controlfile format '%d_%T_%t_%s_%p.ctl';
release channel c1;
release channel c2;
alter database open;
shutdown immediate;
}

Missing archivelog file (failed backup) :

crosscheck archivelog all;
delete expired archivelog all;

# moved archived log
RMAN> catalog archivelog '</PATH/TO/MOVED/ARCHIVED/LOG>';

Slaves :

alter system set dbwr_io_slaves=4 scope=spfile;
# enable large pool
alter system set large_pool_size=10M;

Recovery advisor :

list failure;
advise failure;
repair failure preview;
repair failure;

Tablespace PITR :

recover tablespace <TBSP> until scn 2280334 auxiliary destination '+DATA';

Encrypted backups

One-time password encryption :

set encryption on identified by '<PASSWORD>' only;
backup ...;

Decryption during restore :

set decryption identified by '<PASSWORD>';
restore ...;

run {
allocate channel ch1 device type 'sbt' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel ch2 device type 'sbt' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel ch3 device type 'sbt' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
backup tablespace newtbs;
release channel ch1;
release channel ch2;
release channel ch3;
}


allocate channel for maintenance device type 'sbt' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';



CURRENT_SCN
-----------
    2280334


recover tablespace tpittbs until scn 2280334 auxiliary destination '+DATA';


run {
allocate channel ch1 device type 'sbt' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel ch2 device type 'sbt' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel ch3 device type 'sbt' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel ch4 device type 'sbt' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
recover tablespace newtbs until scn 2317530 auxiliary destination '+DATA';
}


configure channel device type 'sbt' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

Table of Contents