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

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.

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:

1. Create User on the Source
2. Grant him the necessary rights
3. Create a DB Link on the Destination
4. Test the connection

Let's start one by one:

Check the Tape library

sbttest test

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';

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)';