oracle_backup_recovery [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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====
<sxh bash>
expdp SCHEMAS=LUKAS DIRECTORY=EXPORT DUMPFILE=lukas.dmp LOGFILE=lukas_Export.log
</sxh>

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 **
<sxh bash>
sbttest test
</sxh>

==== Creating Recovery catalog ====
**Create dedicated tablespace : **
<sxh sql>
create tablespace rcatalog
datafile '</path/to/db/datafiles/rcatalog01.dbf'
size 24M autoextend on;
</sxh>

**Create new user for Catalog connections :**
<sxh sql>
create user rcat identified by <password>
default tablespace rcatalog
quota unlimited on rcatalog;
</sxh>

**Grant Recovery catalog privileges to new user :**
<sxh sql>
grant connect,resource,recovery_catalog_owner to rcat;
</sxh>

**Create Catalog with RMAN register new DB :**
<sxh sql>
rman target / catalog rcat/<password>@<service>

create catalog tablespace rcatalog;

register database;
</sxh>

**Configure default channel device type for TSM :**
<sxh bash>
configure channel device type 'sbt' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
</sxh>

**RMAN TSM :**
<sxh bash>
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;
}
</sxh>

** Backup DB (version 1) :**
<sxh bash>
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;
}
</sxh>

**RMAN Offline backup using TSM :**
<sxh sql>
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;
}
</sxh>

**Missing archivelog file (failed backup) :**
<sxh bash>
crosscheck archivelog all;
delete expired archivelog all;

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

**Slaves :**
<sxh bash>
alter system set dbwr_io_slaves=4 scope=spfile;
# enable large pool
alter system set large_pool_size=10M;
</sxh>

**Recovery advisor :**
<sxh bash>
list failure;
advise failure;
repair failure preview;
repair failure;
</sxh>

**Tablespace PITR :**
<sxh bash>
recover tablespace <TBSP> until scn 2280334 auxiliary destination '+DATA';
</sxh>

===== Encrypted backups =====
**One-time password encryption :**
<sxh bash>
set encryption on identified by '<PASSWORD>' only;
backup ...;
</sxh>

**Decryption during restore :**
<sxh bash>
set decryption identified by '<PASSWORD>';
restore ...;
</sxh>



<sxh bash>
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)';
</sxh>