Oracle Backup with RMAN

QDObS Quick'nDirtyOracleBackupSolution !

RMAN is an old friend for me.

I installed RMAN at every clients I worked for, since I started with Oracle in 2000 :

- 2002, RMAN with Oracle 8i became reliable enough to get rid of old flat file system backup

( in addition to the fact that my client's backup tool EDM - from EMC - could not save an open file !! ).

- 2007, it was important for my client to get the benefit of RMAN Incremental backup capability,

because daily DB backuped volume was around 15 TB - I'll tell this Flash Copy & RMAN integration in another post ...

- 2010, following the main Production DB server crash, System Team had to quickly configure a new server with new disks, but was not able to re-install DB backups ( NetApp ) without further validation.

Below is about this RMAN installation that had been Quick, not Dirty, allowed end-users service re-opening and, in the end, saved the life of my manager.

1] Online Backup NoCatalog

See B] 11G below for scripting and backup expiration improvments.

A] [ 10g ]

Features

Media:                                     Disk - Compressed ( 10g+ )

Type:                                       Online backup

Unit:                                        Database

Level:                                      Full ( 0 ) & Cumulative ( 2 ) with Block Change Tracking BCT

Parallel:                                   4

Recovery Catalog RCAT:          No

RMAN Dumps disk space:        /rman/<db_name>

Retention policy:                      16-31 days DEV-PRD ( purge is running every FULL backup )

Notes 1

- COMPRESSED backups size is around 10% of total segments DB volume

- Cumulative backups need only 2 backups to be restored ( FULL + INCR ).

A LEVEL=0 backup is a pre-requisite for LEVEL=1 ( Classical FULL backup can't be used ).

- The INCRemental backups are around 10 % of FULL backup size.

- RMAN script takes care of archivelogs deletion after backup : 'delete input'.

- The 'controlfile autobackup' option allows us to restore from scratch without any RCAT ( we'll see this in the Restore post ).

At every backup & every DB structure change, a controlfile autobackup will be generated at the same place as RMAN dumps ( avoids default destination in $ORACLE_HOME ).

- I intentionnaly didn't use the Flash Recovery Area because I want to control everything by myself. Dumps should eventually be saved by third-party backup tool or NetApp snapshots.

- BCT may be installed later, same as for the RCAT.

Notes 2 About online database backup,

What RMAN can do :

§  Online database backup without Hot-backup mode ( begin / end backup )

What RMAN can not do :

§  Online database backup on db in Noarchivelog mode

§  Archivelog mode is still mandatory.

Hence, the impact of backup on application depends on the archivelog mode :

Mode	Types of database Backup	Impact on application
No Archivelog	Offline	Stopped
Archivelog	Offline , or Online	Stopped , or Read i/o overhead

1.1 KSH script to launch RMAN backup

Note see New 11G+ script below.

$ cat rman_backup_db.ksh

#!/bin/ksh

# 22/10/10 - AUC

# RMAN backup

# 0/ test args

if [ $# -lt 3 ] ; then

echo "Missing arguments"

echo "$0 BDc [DSK|TSM] [FULL|INCR] [ONLINE|CLOSED]"

echo "$0 <DB_NAME> DSK FULL ONLINE" ; echo ;  return 1 ; fi

# 1/ arg

BDC=$1 ; BDc_min=$( echo ${BDC} | tr "[:upper:]" "[:lower:]" )

MED=$2 ; MED_min=$( echo ${MED} | tr "[:upper:]" "[:lower:]" )

TYP=$3 ; TYP_min=$( echo ${TYP} | tr "[:upper:]" "[:lower:]" )

ONL=$4 ; ONL_min=$( echo ${ONL} | tr "[:upper:]" "[:lower:]" )

export REP_SCRIPTS=/work/oracle/${BDc_min}/rman

# launched with crond ( no unix environment ) : get env variables

. /etc/profile10.ora

# or Linux equivalent : /etc/profile.d/<file>

export ORACLE_SID=${BDC}

export NLS_DATE_FORMAT='MON DD YYYY HH24:MI:SS'

# 2/ log

if [[ ! -d ${REP_SCRIPTS}/log ]] ; then mkdir ${REP_SCRIPTS}/log ; fi

# gestion des logs

find ${REP_SCRIPTS}/log -name "*.log" -mtime +30 -exec rm {} \;

log_rma=${REP_SCRIPTS}/log/rman_backup_db_${TYP}_${ONL}_${MED}_noc_${BDC}_$(date '+%y%m%d_%H:%M:%S').log

# 3/ execution

print "\n[${BDC}] Remove old CTL autobackup"

find /rman/${BDc_min} -name 'c-*' -mtime +31 -exec rm -f {} \;

print "\n[${BDC}] Backup RMAN DB ${TYP} mode ${ONL} on ${MED_min}"

print "Start - $(date '+%y%m%d_%H:%M:%S')\n"

print "RMAN script = ${REP_SCRIPTS}/scr/rman_backup_db_${ONL_min}_${TYP_min}_${MED_min}_noc_${BDC}.scr"

print "RMAN log = ${log_rma}\n"

rman @${REP_SCRIPTS}/scr/rman_backup_db_${ONL_min}_${TYP_min}_${MED_min}_noc_${BDC}.scr log=${log_rma}

print "\nEnd - $(date '+%m%d%y_%H:%M:%S')\n"

return 0

Calling example: $0 BDc [DSK|TSM] [FULL|INCR] [ONLINE|CLOSED]

RMAN Dumps may be removed as follow to keep only last FULL & INCR backups.

0 SUN

$ ./rman_backup_db.ksh <DB_NAME> DSK FULL ONLINE

$ for i in <db_name_1> <db_name_2> …

do

find /rman/$i -name "db_F_*"  -mtime +7 -exec rm {} \;

find /rman/$i -name "ar_F_*"  -mtime +7 -exec rm {} \;

done

1-6 MON-SAT

$ ./rman_backup_db.ksh <DB_NAME> DSK INCR ONLINE

$ for i in <db_name_1> <db_name_2> …

do

find /rman/$i -name "db_I_*"  -mtime +1 -exec rm {} \;

find /rman/$i -name "ar_I_*"  -mtime +1 -exec rm {} \;

done

1.2 RMAN scripts

Located in ${REP_SCRIPTS}/scr

1.2.1 FULL

$ cat rman_backup_db_online_full_dsk_noc_<DB_NAME>.scr

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# RMAN backup script

# Standard Database 10g

# Media : DISK

# NoCatalog

# FULL

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# 9i cmd 'backup database + archivelogs + controlfile'

# when channel type 'sbt_tape', default is "plus archivelog"

# cinematic :

# sql 'alter system archive log current' ;

# backup archivelog all

# backup database

# sql 'alter system archive log current' ;

# backup archivelog -> archivelogs generated during backup

# delete input

# autobackup of controlfile & spfile

connect target /

# expiration policy

configure retention policy to recovery window of 16 days ;

# autobackup of controlfile & spfile after a successful backup

# in a well-known format that can be retrieved without rcat !!

# default = OFF

configure controlfile autobackup on ;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/<db_name>/%F';

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rman/<db_name>/snapcf_<db_name>.f';

# avoid backup of archivelogs if ALREADY BACKUPED ONCE :

configure backup optimization on ;

# Media : DISK

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET ;

run {

# rcat/ctl & disk arclogs sync

# note : 'archive log current' is the only way of switching log in every RAC instances

# whereas 'switch logfile' is only local

# resync catalog ;

Change Archivelog All Crosscheck ;

backup INCREMENTAL LEVEL = 0 database format '/rman/<db_name>/db_F_%d_s%s_U%U_%Y%M%D'

plus archivelog format '/rman/<db_name>/ar_F_%d_s%s_U%U_%Y%M%D' tag = 'F' delete input ;

}

# expiration policy : old backups purge from CTL

allocate channel for maintenance device type disk ;

delete force noprompt obsolete recovery window of 16 days ;

If we need to keep the most recent archivelogs on disk, the « delete input » should be removed and this Deletion command added :

DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1' BACKED UP 1 TIMES to device type disk ;

1.2.2 INCR ( CUMULATIVE )

$ cat rman_backup_db_online_incr_dsk_noc_<DB_NAME>.scr

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# RMAN backup script

# Standard Database 10g

# Media : DISK

# NoCatalog

# INCR

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

connect target /

# Media : DISK

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET ;

run {

# resync catalog ;

Change Archivelog All Crosscheck ;

backup INCREMENTAL LEVEL 2 cumulative database format '/rman/<db_name>/db_I_%d_s%s_U%U_%Y%M%D'

plus archivelog format '/rman/<db_name>/ar_I_%d_s%s_U%U_%Y%M%D' tag = 'I' delete input ;

}

1.3 Runs

$ ./rman_backup_db.ksh <DB_NAME> DSK FULL ONLINE

[<DB_NAME>] Remove old CTL autobackup

[<DB_NAME>] Backup RMAN DB FULL mode ONLINE on dsk

Start - 111019_13:21:49

RMAN script = /work/oracle/<db_name>/rman/backup/rman_backup_db_online_full_dsk_noc_<DB_NAME>.scr

RMAN log = /work/oracle/<db_name>/rman/log/rman_backup_db_FULL_ONLINE_DSK_noc_<DB_NAME>_111019_13:21:49.log

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34> 35> 36> 37> 38> 39> 40> 41> 42> 43> 44> 45> 46> 47> 48> 49> 50> 51>

End - 101911_13:49:55

$

$ ./rman_backup_db.ksh <DB_NAME> DSK INCR ONLINE

[<DB_NAME>] Remove old CTL autobackup

[<DB_NAME>] Backup RMAN DB INCR mode ONLINE on dsk

Start - 111019_14:16:56

RMAN script = /work/oracle/<db_name>/rman/backup/rman_backup_db_online_incr_dsk_noc_<DB_NAME>.scr

RMAN log = /work/oracle/<db_name>/rman/log/rman_backup_db_INCR_ONLINE_DSK_noc_<DB_NAME>_111019_14:16:56.log

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34> 35> 36> 37> 38> 39> 40> 41> 42> 43> 44> 45> 46> 47> 48>

End - 101911_14:21:17

$

[GRID] Example of an RMAN Online Backup execution PARALLEL 4      600 000 KB / Sec.

Dumps

/rman/<db_name> > ls -ltr

total 20942348

-rw-r-----  1 oracle dba 1137140224 2011-10-19 13:27 ar_<DB_NAME>_s862_Uqumpgasl_1_1_20111019

-rw-r-----  1 oracle dba 1222368768 2011-10-19 13:28 ar_<DB_NAME>_s861_Uqtmpgasl_1_1_20111019

-rw-r-----  1 oracle dba 2783854592 2011-10-19 13:48 db_F_<DB_NAME>_s864_Ur0mpgb7n_1_1_20111019

-rw-r-----  1 oracle dba 2753085440 2011-10-19 13:49 db_F_<DB_NAME>_s863_Uqvmpgb7n_1_1_20111019

-rw-r-----  1 oracle dba   28635136 2011-10-19 13:49 ar_<DB_NAME>_s865_Ur1mpgcfg_1_1_20111019

-rw-r-----  1 oracle dba   10600448 2011-10-19 13:49 c-179121004-20111019-00

-rw-r-----  1 oracle dba   16663040 2011-10-19 14:17 ar_<DB_NAME>_s867_Ur3mpge44_1_1_20111019

-rw-r-----  1 oracle dba    7553024 2011-10-19 14:20 db_I_<DB_NAME>_s869_Ur5mpge4e_1_1_20111019

-rw-r-----  1 oracle dba   19177472 2011-10-19 14:21 db_I_<DB_NAME>_s868_Ur4mpge4e_1_1_20111019

-rw-r-----  1 oracle dba    2066432 2011-10-19 14:21 ar_<DB_NAME>_s870_Ur6mpgeao_1_1_20111019

-rw-r-----  1 oracle dba   10600448 2011-10-19 14:21 c-179121004-20111019-01

B] 11g Better Version

• We take advantage of the new feature Variables Substitution in the calling of rman executable

• The logfile is tested against RMAN ERRORS

• Moreover, we are deleting obsolete backups with RMAN commands in Catalog and on media ( Disk ).

KSH to launch Backup

#!/bin/ksh

# 22/10/10 - AUC

# RMAN backup

# 0/ test args

if [ $# -lt 3 ] ; then

echo "Missing arguments"

echo "$0 BDc [DSK|TSM] [FULL|INCR] [ONLINE|CLOSED]"

echo "$0 INST1 DSK FULL ONLINE" ; echo ;  return 1 ; fi

# 1/ arg

BDC=$1 ; BDc_min=$( echo ${BDC} | tr "[:upper:]" "[:lower:]" )

MED=$2 ; MED_min=$( echo ${MED} | tr "[:upper:]" "[:lower:]" )

TYP=$3 ; TYP_min=$( echo ${TYP} | tr "[:upper:]" "[:lower:]" )

ONL=$4 ; ONL_min=$( echo ${ONL} | tr "[:upper:]" "[:lower:]" )

export REP_SCRIPTS=/work/oracle/${BDc_min}/rman

codret=0

. /etc/profile.d/oracle.sh

export ORACLE_SID=${BDC}

export NLS_DATE_FORMAT='MON DD YYYY HH24:MI:SS'

# 2/ log

if [[ ! -d ${REP_SCRIPTS}/log ]] ; then mkdir ${REP_SCRIPTS}/log ; fi

# gestion des logs

find ${REP_SCRIPTS}/log -name "*.log" -mtime +30 -exec rm {} \;

log_rma=${REP_SCRIPTS}/log/rman_backup_db_${TYP}_${ONL}_${MED}_noc_${BDC}_$(date '+%y%m%d_%H:%M:%S').log

# 3/ execution

print "\n[${BDC}] Backup RMAN DB ${TYP} mode ${ONL} on ${MED_min}"

print "Start - $(date '+%y%m%d_%H:%M:%S')\n"

print "RMAN script = ${REP_SCRIPTS}/scr/rman_backup_db_${ONL_min}_${TYP_min}_${MED_min}_noc_${BDC}.scr"

print "RMAN log = ${log_rma}\n"

rman @${REP_SCRIPTS}/scr/rman_backup_db_${ONL_min}_${TYP_min}_${MED_min}_noc_${BDC}.scr log=${log_rma} USING ${BDc_min}

# return code

if [[ -f ${log_rma} ]] ; then

        egrep -v "RMAN-06908|RMAN-06909" ${log_rma} | egrep "RMAN-|ORA-" > /dev/null

        if (( $? == 0 ))

                then print "\n!! Error execution rman : check log ${log_rma} !!\n" ; codret=1

        fi

else    print "\n!! Error execution rman : missing log ${log_rma} !!\n" ; codret=1

fi

print "\nEnd - $(date '+%m%d%y_%H:%M:%S')\n"

return ${codret}

11G scripts ONLINE FULL & INCR / CLOSED FULL

FULL

::::::::::::::

rman_backup_db_online_full_dsk_noc_<DB_NAME>.scr

::::::::::::::

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# RMAN backup script

# Standard Database 10g

# Updated for 11g Substitution Variables

# Media : DISK

# NoCatalog

# FULL

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# 9i cmd 'backup database + archivelogs + controlfile'

# when channel type 'sbt_tape', default is "plus archivelog"

# cinematic :

# sql 'alter system archive log current' ;

# backup archivelog all

# backup database

# sql 'alter system archive log current' ;

# backup archivelog -> archivelogs generated during backup

# delete input

# autobackup of controlfile & spfile

connect target /

# expiration policy

configure retention policy to recovery window of 16 days ;

# autobackup of controlfile & spfile after a successful backup

# in a well-known format that can be retrieved without rcat !!

# default = OFF

configure controlfile autobackup on ;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/&1/ctl/%F';

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rman/&1/ctl/snapcf_&1.f';

# avoid backup of archivelogs if ALREADY BACKUPED ONCE :

configure backup optimization on ;

# Media : DISK

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET ;

run {

# rcat/ctl & disk arclogs sync

# note : 'archive log current' is the only way of switching log in every RAC instances

# whereas 'switch logfile' is only local

# resync catalog ;

Change Archivelog All Crosscheck ;

backup INCREMENTAL LEVEL = 0 database format '/rman/&1/full/db_F_%d_s%s_U%U_%Y%M%D'

keep until time 'SYSDATE+14' logs

plus archivelog format '/rman/&1/full/ar_F_%d_s%s_U%U_%Y%M%D' tag = 'F' delete input ;

}

# expiration policy : old backups purge from CTL & RCAT

allocate channel for maintenance device type disk ;

delete force noprompt obsolete recovery window of 7 days ;

::::::::::::::

rman_backup_db_online_incr_dsk_noc_<DB_NAME>.scr

::::::::::::::

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# RMAN backup script

# Standard Database 10g

# Updated for 11g Substitution Variables

# Media : DISK

# NoCatalog

# INCR

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

connect target /

# Media : DISK

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET ;

run {

# resync catalog ;

Change Archivelog All Crosscheck ;

backup INCREMENTAL LEVEL 2 cumulative database format '/rman/&1/incr/db_I_%d_s%s_U%U_%Y%M%D'

plus archivelog format '/rman/&1/incr/ar_I_%d_s%s_U%U_%Y%M%D' tag = 'I' delete input ;

}

# expiration policy : old backups purge from CTL & RCAT

# Full backups are flagged as "kept until" more than 7 days

allocate channel for maintenance device type disk ;

delete force noprompt obsolete recovery window of 7 days ;

CLOSED

::::::::::::::

rman_backup_db_closed_full_dsk_noc_<DB_NAME>.scr

::::::::::::::

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# RMAN backup script

# Archivelog mode:      N

# Backup type:          Offline

# Backup element:       database

# Standard Database 10g

# Media : DISK

# NoCatalog

# FULL

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# Cinematic:

#       clean shutdown

#       startup mount

#       backup database

#       alter database open

#

connect target /

shutdown immediate ;

startup mount ;

# expiration policy

configure retention policy to recovery window of 16 days ;

# autobackup of controlfile & spfile after a successful backup

# in a well-known format that can be retrieved without rcat !!

# default = OFF

configure controlfile autobackup on ;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/&1/%F';

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rman/&1/snapcf_&1.f';

# avoid backup of archivelogs if ALREADY BACKUPED ONCE :

configure backup optimization on ;

# Media : DISK

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET ;

run {

  backup database format '/rman/&1/db_F_%d_s%s_U%U_%Y%M%D' tag='F' ;

}

alter database open ;

# expiration policy : old backups purge from CTL

allocate channel for maintenance device type disk ;

delete force noprompt obsolete recovery window of 16 days ;

C] 12C Version : how to backup a whole CDB with RMAN ?

12.1.0.2 Closed database Backup : same script as a 11G DB :

RMAN SCRIPT

$ cat rman_backup_db_closed_full_dsk_noc_PRO12.scr

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# RMAN backup script

# Archivelog mode:      N

# Backup type:          Offline

# Backup element:       database

# Standard Database 12c

# Media : DISK

# NoCatalog

# FULL

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# Cinematic:

#       clean shutdown

#       startup mount

#       backup database

#       alter database open

#

connect target /

shutdown immediate ;

startup mount ;

#-------------------- Backup part --------------------#

configure retention policy to recovery window of 14 days ;

# autobackup of controlfile & spfile after a successful backup

# in a well-known format that can be retrieved without rcat !!

# default = OFF

configure controlfile autobackup on ;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/&1/ctl/%F';

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rman/&1/ctl/snapcf_&1.f';

# avoid backup of archivelogs if ALREADY BACKUPED ONCE :

configure backup optimization on ;

# Media : DISK

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET ;

run {

  backup database format '/rman/&1/full/db_F_%d_s%s_U%U_%Y%M%D' tag='F' ;

}

alter database open ;

LOG

$ cat rman_backup_db_FULL_CLOSED_DSK_noc_PRO12_150720_09:22:29.log

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jul 20 09:22:29 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

RMAN> #+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

2> # RMAN backup script

10> # FULL

11> #+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

12> # Cinematic:

13> #       clean shutdown

14> #       startup mount

15> #       backup database

16> #       alter database open

17> #

18>

19> connect target *

20> shutdown immediate ;

21> startup mount ;

22>

23> #--------- delete Backup from 2 weeks before ---------#

24> # expiration policy : old backups purge from CTL

25> # expiration policy to keep only 2 Full Backups

26> ###allocate channel for maintenance device type disk ;

27> ###crosscheck backup;

28> ###delete noprompt backup completed before 'sysdate-10' ;

29>

30> #-------------------- Backup part --------------------#

31> configure retention policy to recovery window of 14 days ;

32>

33> # autobackup of controlfile & spfile after a successful backup

34> # in a well-known format that can be retrieved without rcat !!

35> # default = OFF

36> configure controlfile autobackup on ;

37> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/pro12/ctl/%F';

38> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rman/pro12/ctl/snapcf_pro12f';

39>

40> # avoid backup of archivelogs if ALREADY BACKUPED ONCE :

41> configure backup optimization on ;

42>

43> # Media : DISK

44> CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET ;

45>

46> run {

47>

48>   backup database format '/rman/pro12/full/db_F_%d_s%s_U%U_%Y%M%D' tag='F' ;

49>

50> }

51>

52> alter database open ;

53>

54>

connected to target database: PRO12 (DBID=3979658611)

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area    6442450944 bytes

Fixed Size                     2938792 bytes

Variable Size               3238004824 bytes

Database Buffers            3187671040 bytes

Redo Buffers                  13836288 bytes

old RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;

new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/pro12/ctl/%F';

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/pro12/ctl/%F';

new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rman/pro12/ctl/snapcf_pro12f';

new RMAN configuration parameters:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rman/pro12/ctl/snapcf_pro12f';

new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:

CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters:

CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

new RMAN configuration parameters are successfully stored

Starting backup at JUL 20 2015 09:23:02

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=393 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=580 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=6 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=200 device type=DISK

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00003 name=/data/pro12/sysaux01.dbf

channel ORA_DISK_1: starting piece 1 at JUL 20 2015 09:23:04

channel ORA_DISK_2: starting compressed full datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00053 name=/data/pdb1/PDB1_oim_arch_data.dbf

input datafile file number=00056 name=/data/pdb1/PDB1_ias_opss.dbf

input datafile file number=00055 name=/data/pdb1/PDB1_mds.dbf

channel ORA_DISK_2: starting piece 1 at JUL 20 2015 09:23:04

channel ORA_DISK_3: starting compressed full datafile backup set

channel ORA_DISK_3: specifying datafile(s) in backup set

input datafile file number=00013 name=/data/pdb1/sysaux01.dbf

input datafile file number=00054 name=/data/pdb1/PDB1_oim.dbf

input datafile file number=00052 name=/data/pdb1/PDB1_soainfra.dbf

channel ORA_DISK_3: starting piece 1 at JUL 20 2015 09:23:04

channel ORA_DISK_4: starting compressed full datafile backup set

channel ORA_DISK_4: specifying datafile(s) in backup set

input datafile file number=00057 name=/data/pdb1/PDB1_oimlob.dbf

input datafile file number=00012 name=/data/pdb1/system01.dbf

input datafile file number=00058 name=/data/pdb1/PDB1_iassdpm.dbf

channel ORA_DISK_4: starting piece 1 at JUL 20 2015 09:23:04

channel ORA_DISK_2: finished piece 1 at JUL 20 2015 09:23:11

piece handle=/rman/pro12/full/db_F_PRO12_s74_U2aqcgobo_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07

channel ORA_DISK_2: starting compressed full datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00001 name=/data/pro12/system01.dbf

channel ORA_DISK_2: starting piece 1 at JUL 20 2015 09:23:11

channel ORA_DISK_4: finished piece 1 at JUL 20 2015 09:23:18

piece handle=/rman/pro12/full/db_F_PRO12_s76_U2cqcgobo_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:14

channel ORA_DISK_4: starting compressed full datafile backup set

channel ORA_DISK_4: specifying datafile(s) in backup set

input datafile file number=00011 name=/data/pdb2/sysaux01.dbf

input datafile file number=00022 name=/data/pdb2/PDB2_ias_opss.dbf

input datafile file number=00025 name=/data/pdb2/GBM_ias_iau.dbf

input datafile file number=00028 name=/data/pdb2/GBM_wlsservices.dbf

channel ORA_DISK_4: starting piece 1 at JUL 20 2015 09:23:18

channel ORA_DISK_3: finished piece 1 at JUL 20 2015 09:23:33

piece handle=/rman/pro12/full/db_F_PRO12_s75_U2bqcgobo_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_3: backup set complete, elapsed time: 00:00:29

channel ORA_DISK_3: starting compressed full datafile backup set

channel ORA_DISK_3: specifying datafile(s) in backup set

input datafile file number=00015 name=/data/pdb3/sysaux01.dbf

input datafile file number=00050 name=/data/pdb3/PDB3_ias_iau.dbf

channel ORA_DISK_3: starting piece 1 at JUL 20 2015 09:23:34

channel ORA_DISK_1: finished piece 1 at JUL 20 2015 09:23:49

piece handle=/rman/pro12/full/db_F_PRO12_s73_U29qcgobo_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00010 name=/data/pdb2/system01.dbf

input datafile file number=00016 name=/data/pdb2/PDB2_soainfra.dbf

input datafile file number=00024 name=/data/pdb2/GBM_mds.dbf

input datafile file number=00019 name=/data/pdb2/PDB2_wlsservices.dbf

channel ORA_DISK_1: starting piece 1 at JUL 20 2015 09:23:49

channel ORA_DISK_2: finished piece 1 at JUL 20 2015 09:23:49

piece handle=/rman/pro12/full/db_F_PRO12_s77_U2dqcgobv_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:38

channel ORA_DISK_2: starting compressed full datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00020 name=/data/pdb2/PDB2_UMS.dbf

input datafile file number=00023 name=/data/pdb2/GBM_soainfra.dbf

input datafile file number=00029 name=/data/pdb2/GBM_svctbl.dbf

input datafile file number=00021 name=/data/pdb2/PDB2_ias_iau.dbf

channel ORA_DISK_2: starting piece 1 at JUL 20 2015 09:23:49

channel ORA_DISK_4: finished piece 1 at JUL 20 2015 09:23:49

piece handle=/rman/pro12/full/db_F_PRO12_s78_U2eqcgoc6_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:31

channel ORA_DISK_4: starting compressed full datafile backup set

channel ORA_DISK_4: specifying datafile(s) in backup set

input datafile file number=00007 name=/data/pro12/pdbseed/sysaux01.dbf

channel ORA_DISK_4: starting piece 1 at JUL 20 2015 09:23:49

channel ORA_DISK_2: finished piece 1 at JUL 20 2015 09:23:50

piece handle=/rman/pro12/full/db_F_PRO12_s81_U2hqcgod5_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_2: starting compressed full datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00027 name=/data/pdb2/GBM_UMS.dbf

input datafile file number=00017 name=/data/pdb2/PDB2_mds.dbf

input datafile file number=00018 name=/data/pdb2/PDB2_svctbl.dbf

input datafile file number=00026 name=/data/pdb2/GBM_ias_opss.dbf

channel ORA_DISK_2: starting piece 1 at JUL 20 2015 09:23:50

channel ORA_DISK_2: finished piece 1 at JUL 20 2015 09:23:57

piece handle=/rman/pro12/full/db_F_PRO12_s83_U2jqcgod6_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07

channel ORA_DISK_2: starting compressed full datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00014 name=/data/pdb3/system01.dbf

input datafile file number=00049 name=/data/pdb3/PDB3_ias_opss.dbf

channel ORA_DISK_2: starting piece 1 at JUL 20 2015 09:23:57

channel ORA_DISK_3: finished piece 1 at JUL 20 2015 09:23:57

piece handle=/rman/pro12/full/db_F_PRO12_s79_U2fqcgocm_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_3: backup set complete, elapsed time: 00:00:23

channel ORA_DISK_3: starting compressed full datafile backup set

channel ORA_DISK_3: specifying datafile(s) in backup set

input datafile file number=00004 name=/data/pro12/undotbs01.dbf

channel ORA_DISK_3: starting piece 1 at JUL 20 2015 09:23:57

channel ORA_DISK_3: finished piece 1 at JUL 20 2015 09:23:58

piece handle=/rman/pro12/full/db_F_PRO12_s85_U2lqcgodd_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_3: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_3: starting compressed full datafile backup set

channel ORA_DISK_3: specifying datafile(s) in backup set

input datafile file number=00005 name=/data/pro12/pdbseed/system01.dbf

channel ORA_DISK_3: starting piece 1 at JUL 20 2015 09:23:58

channel ORA_DISK_1: finished piece 1 at JUL 20 2015 09:24:05

piece handle=/rman/pro12/full/db_F_PRO12_s80_U2gqcgod5_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00048 name=/data/pdb3/PDB3_oam.dbf

input datafile file number=00051 name=/data/pdb3/PDB3_mds.dbf

channel ORA_DISK_1: starting piece 1 at JUL 20 2015 09:24:06

channel ORA_DISK_1: finished piece 1 at JUL 20 2015 09:24:09

piece handle=/rman/pro12/full/db_F_PRO12_s87_U2nqcgodm_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00006 name=/data/pro12/users01.dbf

channel ORA_DISK_1: starting piece 1 at JUL 20 2015 09:24:09

channel ORA_DISK_2: finished piece 1 at JUL 20 2015 09:24:09

piece handle=/rman/pro12/full/db_F_PRO12_s84_U2kqcgodd_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:12

channel ORA_DISK_3: finished piece 1 at JUL 20 2015 09:24:09

piece handle=/rman/pro12/full/db_F_PRO12_s86_U2mqcgode_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_3: backup set complete, elapsed time: 00:00:11

channel ORA_DISK_4: finished piece 1 at JUL 20 2015 09:24:09

piece handle=/rman/pro12/full/db_F_PRO12_s82_U2iqcgod5_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:20

channel ORA_DISK_1: finished piece 1 at JUL 20 2015 09:24:10

piece handle=/rman/pro12/full/db_F_PRO12_s88_U2oqcgodp_1_1_20150720 tag=F comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at JUL 20 2015 09:24:10

Starting Control File and SPFILE Autobackup at JUL 20 2015 09:24:10

piece handle=/rman/pro12/ctl/c-3979658611-20150720-00 comment=NONE

Finished Control File and SPFILE Autobackup at JUL 20 2015 09:24:11

Statement processed

Recovery Manager complete.

2] Backup / Delete Achivelogs

If necessary, run this RMAN script along the day to backup & remove archivelogs ( to avoid filling up archivelog_dest space ).

#

#       RMAN Save/Purge des archivelogs

#       <DB_NAME>

#

00 06 * * * /work/oracle/db10g/rman/rman_backup_ar.ksh > /work/oracle/db10g/rman/log/rma_del_arc_noc.log 2>&1

00 12 * * * /work/oracle/db10g/rman/rman_backup_ar.ksh > /work/oracle/db10g/rman/log/rma_del_arc_noc.log 2>&1

00 18 * * * /work/oracle/db10g/rman/rman_backup_ar.ksh > /work/oracle/db10g/rman/log/rma_del_arc_noc.log 2>&1

$ cat rman_backup_ar.ksh

#!/bin/ksh

# Purge des archivelogs par RMAN

export ORACLE_SID=<DB_NAME>

export ORACLE_HOME=/oracle/10GDB

export LIBPATH=/usr/lib:/usr/vac/lib:/oracle/10GDB/lib:/oracle/10GDB/network/lib

export PATH=/oracle/10GDB/bin:/oracle/10GDB/OPatch:/oracle/10GDB/OPatch:/usr/linux/bin:/usr/java14/bin:/usr/sbin:/sbin:/usr/bin:/bin:/usr/ucb:/usr/local/bin:/commun:/usr/vac/bin:/usr/vacpp/bin:.

export REP_SCRIPTS=/work/oracle/db10g/rman

if [[ ! -d ${REP_SCRIPTS}/log ]] ; then mkdir ${REP_SCRIPTS}/log ; fi

rman @${REP_SCRIPTS}/scr/rma_bck_arc_noc.scr log=${REP_SCRIPTS}/log/rma_bck_arc_noc_$(date '+%m%d%y_%H:%M:%S').log

return 0

$ cat rma_bck_arc_noc.scr

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

#

# RMAN Delete archivelogs

#

# Target:               Primary DB

# Recovery catalog:     N

# Action:               backup archivelog

# Instance state:       open

# controlfile state:    current

# Oracle version:       10g

# Media:                Disk

#

# Cinematic:

#       crosscheck

#       connect target

#       BACKUP ARCHIVELOG

#

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

connect target /

run {

  sql 'alter system archive log current' ;

#  resync catalog ;

  Change Archivelog All Crosscheck ;

backup archivelog all format '/rman/devqu/ar_I_%d_s%s_U%U_%Y%M%D' tag = 'I' delete input ;

}

$

3] Add BCT

To speed up Incremental Backup, 10g+ « Block Change Tracking » may be installed without risk ( if BCT file is missing, there is no instance hanging ! ) and a small overhead ( to track block updates ) as follow :

alter database enable block change tracking using file '/<file_path>/<base>_bct.trk' ;

4] Cold Backup [ 10g ]

4.1 Full

As the Instance needs to be shutdowned, I’ll propose only a weekly Closed Backup. It’s interesting for Datawarehouse purpose or Test/UAT environment where it’s allowed to loose some data.

• Cron this, use same KSH backup script as Online Backup

#-----------------------------------------------------------------

#       RMAN Backup

30 22 * * 0 /work/oracle/<db_name>/rman/rman_backup_db.ksh <DB_NAME> DSK FULL CLOSED > /work/oracle/<db_name>/rman/log/sauvegarde_rma.log 2>&1

• Script

$ cat rman_backup_db_closed_full_dsk_noc_<DB_NAME>.scr

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# RMAN backup script

# Archivelog mode:      N

# Backup type:          Offline

# Backup element:       database

# Standard Database 10g

# Media : DISK

# NoCatalog

# FULL

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# Cinematic:

#       clean shutdown

#       startup mount

#       backup database

#       alter database open

#

connect target /

shutdown immediate ;

startup mount ;

# expiration policy

configure retention policy to recovery window of 16 days ;

# autobackup of controlfile & spfile after a successful backup

# in a well-known format that can be retrieved without rcat !!

# default = OFF

configure controlfile autobackup on ;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/<db_name>/%F';

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rman/<db_name>/snapcf_<db_name>.f';

# avoid backup of archivelogs if ALREADY BACKUPED ONCE :

configure backup optimization on ;

# Media : DISK

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET ;

run {

# without any Incremental strategy

  backup database format '/rman/<db_name>/db_F_%d_s%s_U%U_%Y%M%D' tag='F' ;

# with an Incremental strategy

# backup INCREMENTAL LEVEL = 0 database format '/rman/<db_name>/db_F_%d_s%s_U%U_%Y%M%D' tag='F' ;

}

alter database open ;

# expiration policy : old backups purge from CTL

allocate channel for maintenance device type disk ;

delete force noprompt obsolete recovery window of 16 days ;

• Run RMAN, will clean shut the instance and open it afterwards

$ ./rman_backup_db.ksh <DB_NAME> DSK FULL CLOSED

[<DB_NAME>] Remove old CTL autobackup

[<DB_NAME>] Backup RMAN DB FULL mode CLOSED on dsk

Start - 120516_16:07:31

RMAN script = /work/oracle/<db_name>/rman/scr/rman_backup_db_closed_full_dsk_noc_<DB_NAME>.scr

RMAN log = /work/oracle/<db_name>/rman/log/rman_backup_db_FULL_CLOSED_DSK_noc_<DB_NAME>_120516_16:07:31.log

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34> 35> 36> 37> 38> 39> 40> 41> 42> 43> 44> 45> 46> 47> 48> 49> 50>

End - 051612_16:08:32

$

Log

$ cat /work/oracle/<db_name>/rman/log/rman_backup_db_FULL_CLOSED_DSK_noc_<DB_NAME>_120516_16:07:31.log

Recovery Manager: Release 10.2.0.5.0 - Production on Wed May 16 16:07:31 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN> #+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

2> # RMAN backup script

3> # Archivelog mode:      N

4> # Backup type:          Offline

5> # Backup element:       database

6> # Standard Database 10g

7> # Media : DISK

8> # NoCatalog

9> # FULL

10> #+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

11> # Cinematic:

12> #       clean shutdown

13> #       startup mount

14> #       backup database

15> #       alter database open

16> #

17>

18> connect target *

19> shutdown immediate ;

20> startup mount ;

21>

22> # expiration policy

23> configure retention policy to recovery window of 16 days ;

24>

25> # autobackup of controlfile & spfile after a successful backup

26> # in a well-known format that can be retrieved without rcat !!

27> # default = OFF

28> configure controlfile autobackup on ;

29> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/<db_name>/%F';

30> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rman/<db_name>/snapcf_<db_name>.f';

31>

32> # avoid backup of archivelogs if ALREADY BACKUPED ONCE :

33> configure backup optimization on ;

34>

35> # Media : DISK

36> CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET ;

37>

38> run {

39>

40>   backup database format '/rman/<db_name>/db_F_%d_s%s_U%U_%Y%M%D' tag='F' ;

41>

42> }

43>

44> alter database open ;

45>

46> # expiration policy : old backups purge from CTL

47> allocate channel for maintenance device type disk ;

48> delete force noprompt obsolete recovery window of 16 days ;

49>

50>

connected to target database: <DB_NAME> (DBID=1549352623)

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     268435456 bytes

Fixed Size                     2095672 bytes

Variable Size                167773640 bytes

Database Buffers              92274688 bytes

Redo Buffers                   6291456 bytes

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 16 DAYS;

new RMAN configuration parameters are successfully stored

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/<db_name>/%F';

new RMAN configuration parameters are successfully stored

snapshot control file name set to: /rman/<db_name>/snapcf_<db_name>.f

new RMAN configuration parameters are successfully stored

new RMAN configuration parameters:

CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters are successfully stored

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

new RMAN configuration parameters are successfully stored

Starting backup at MAY 16 2012 16:07:54

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=499 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=497 devtype=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: sid=496 devtype=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: sid=495 devtype=DISK

channel ORA_DISK_1: starting compressed full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/data/<db_name>/system_01.dbf

channel ORA_DISK_1: starting piece 1 at MAY 16 2012 16:07:57

channel ORA_DISK_2: starting compressed full datafile backupset

channel ORA_DISK_2: specifying datafile(s) in backupset

input datafile fno=00002 name=/data/<db_name>/undo_01.dbf

input datafile fno=00007 name=/data/<db_name>/its_dat.dbf

channel ORA_DISK_2: starting piece 1 at MAY 16 2012 16:07:57

channel ORA_DISK_3: starting compressed full datafile backupset

channel ORA_DISK_3: specifying datafile(s) in backupset

input datafile fno=00003 name=/data/<db_name>/sysaux_01.dbf

input datafile fno=00004 name=/data/<db_name>/users_01.dbf

channel ORA_DISK_3: starting piece 1 at MAY 16 2012 16:07:57

channel ORA_DISK_4: starting compressed full datafile backupset

channel ORA_DISK_4: specifying datafile(s) in backupset

input datafile fno=00005 name=/data/<db_name>/xdb_01.dbf

input datafile fno=00006 name=/data/<db_name>/adb_dat.dbf

channel ORA_DISK_4: starting piece 1 at MAY 16 2012 16:07:57

channel ORA_DISK_2: finished piece 1 at MAY 16 2012 16:08:00

piece handle=/rman/<db_name>/db_F_<DB_NAME>_s2_U02nb4sqt_1_1_20120516 tag=F comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03

channel ORA_DISK_4: finished piece 1 at MAY 16 2012 16:08:00

piece handle=/rman/<db_name>/db_F_<DB_NAME>_s4_U04nb4sqt_1_1_20120516 tag=F comment=NONE

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:03

channel ORA_DISK_3: finished piece 1 at MAY 16 2012 16:08:07

piece handle=/rman/<db_name>/db_F_<DB_NAME>_s3_U03nb4sqt_1_1_20120516 tag=F comment=NONE

channel ORA_DISK_3: backup set complete, elapsed time: 00:00:10

channel ORA_DISK_1: finished piece 1 at MAY 16 2012 16:08:22

piece handle=/rman/<db_name>/db_F_<DB_NAME>_s1_U01nb4sqt_1_1_20120516 tag=F comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

Finished backup at MAY 16 2012 16:08:22

Starting Control File and SPFILE Autobackup at MAY 16 2012 16:08:22

piece handle=/rman/<db_name>/c-1549352623-20120516-00 comment=NONE

Finished Control File and SPFILE Autobackup at MAY 16 2012 16:08:26

database opened

released channel: ORA_DISK_1

released channel: ORA_DISK_2

released channel: ORA_DISK_3

released channel: ORA_DISK_4

allocated channel: ORA_MAINT_DISK_1

channel ORA_MAINT_DISK_1: sid=499 devtype=DISK

no obsolete backups found

Recovery Manager complete.

$

4.2 Incremental

If one needs an Incremental Backup from a NoArchivelog Mode DB, use the syntax in the full backup script

# backup INCREMENTAL LEVEL = 0 database format '/rman/<db_name>/db_F_%d_s%s_U%U_%Y%M%D' tag='F' ;

Then

• Script

$ cat rman_backup_db_closed_incr_dsk_noc_<DB_NAME>.scr

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# RMAN backup script

# Standard Database 10g

# Media : DISK

# NoCatalog

# INCR

# NoArchivelog Mode

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

connect target /

shutdown immediate ;

startup mount ;

# Media : DISK

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET ;

run {

backup INCREMENTAL LEVEL 2 cumulative database format '/rman/<db_name>/db_I_%d_s%s_U%U_%Y%M%D'

plus archivelog format '/rman/<db_name>/ar_I_%d_s%s_U%U_%Y%M%D' tag = 'I' delete input ;

}

alter database open ;

5] Purge old Backups

The retention of backups is drove by RMAN according to the « recovery window number of days », as follow :

configure retention policy to recovery window of x days ;

Ex :

# expiration policy : old backups purge from CTL & RCAT

allocate channel for maintenance device type disk ;

crosscheck backup;

delete force noprompt obsolete recovery window of 7 days ;

6] Exclude Tablespaces from DB Backup

Sometimes you may want to omit a specified tablespace from part of the regular backup schedule. Here are some possible scenarios to consider:

A tablespace is easy to rebuild, so it is more cost-effective to rebuild it than back it up every day.

A tablespace contains temporary or test data that you do not need to back up.

A tablespace does not change often and therefore should be backed up on a different schedule from other backups.

You can still back up the configured tablespaces by explicitly specifying them in a BACKUP command or by specifying the NOEXCLUDE option on a BACKUP DATABASE command. For example, you can enter one of the following commands:

You can disable the exclusion feature for cwmlite and example as follows:

CONFIGURE EXCLUDE FOR TABLESPACE cwmlite CLEAR;

CONFIGURE EXCLUDE FOR TABLESPACE example CLEAR;

RMAN includes these tablespaces in future whole database backups.

11G Example

$ cat rman_backup_db_closed_full_dsk_noc_<DB_NAME>.scr

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# RMAN backup script

# Archivelog mode:      N

# Backup type:          Offline

# Backup element:       database

# Standard Database 10g

# Media : DISK

# NoCatalog

# FULL

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# Cinematic:

#       clean shutdown

#       startup mount

#       backup database

#       alter database open

#

connect target /

shutdown immediate ;

startup mount ;

# expiration policy

configure retention policy to recovery window of 16 days ;

# autobackup of controlfile & spfile after a successful backup

# in a well-known format that can be retrieved without rcat !!

# default = OFF

configure controlfile autobackup on ;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/&1/%F';

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rman/&1/snapcf_&1.f';

# avoid backup of archivelogs if ALREADY BACKUPED ONCE :

configure backup optimization on ;

# Media : DISK

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET ;

# specific <DB_NAME> : exclude Production Tablespaces

CONFIGURE EXCLUDE FOR TABLESPACE TBSLOG ;

CONFIGURE EXCLUDE FOR TABLESPACE TBSIDX ;

CONFIGURE EXCLUDE FOR TABLESPACE DBATBS_DAT ;

CONFIGURE EXCLUDE FOR TABLESPACE TBSIBOIDX ;

CONFIGURE EXCLUDE FOR TABLESPACE INDX_LGT ;

CONFIGURE EXCLUDE FOR TABLESPACE TBS ;

CONFIGURE EXCLUDE FOR TABLESPACE TBSIDXFCT ;

CONFIGURE EXCLUDE FOR TABLESPACE TBSIBO ;

CONFIGURE EXCLUDE FOR TABLESPACE TBSBRIO ;

CONFIGURE EXCLUDE FOR TABLESPACE TBSHPV ;

CONFIGURE EXCLUDE FOR TABLESPACE TBSBLOB ;

run {

  backup database format '/rman/&1/db_F_%d_s%s_U%U_%Y%M%D' tag='F' ;

}

alter database open ;

# expiration policy : old backups purge from CTL

allocate channel for maintenance device type disk ;

delete force noprompt obsolete recovery window of 16 days ;