Saturday, December 6, 2014

Shell Script:- How to colorful your shell script in linux
Shell scripts commonly used ANSI escape codes for color output. Following table shows Numbers representing colors in Escape Sequences.
Attribute codes:
00=none 01=set to bold intensity 02=set to faint intensity 03=use italic font 04=underscore 05=slow blink 06=fast blink 07=reverse foreground/background colors 08=Set foreground color to background color
Text color codes:
30=black 31=red 32=green 33=yellow 34=blue 35=magenta 36=cyan 37=white
Background color codes:
40=black 41=red 42=green 43=yellow 44=blue 45=magenta 46=cyan 47=white
Numbers Representing Colors in Escape Sequences

Color
Foreground
Background
Black
30
40
Red
31
41
Green
32
42
Yellow
33
43
Blue
34
44
Magenta
35
45
Cyan
36
46
White
37
47

The numbers in the above table work for xterm terminal.Result may vary for other terminal emulators.
Use the following template for writing colored text.
$ echo -e "\033[COLORm Sample text"
The "\033[" begins the escape sequence.You can also use "\e[" instead of "\033[". COLOR specifies a foreground color, according to the table above.The "m" terminates escape sequence, and text begins immediately after that.
eg:
$ echo -e "\033[1mThis is bold text.\033[0m"
The “\033[“ represents an escape( also you can use “\E[“ or “\e[“ ), the "1" turns on the bold attribute, 
while the "0" switches it off. The "m" terminates each term of the escape sequence.

Note:
Always use \033[0m at the end of the line to turn off the colour attribute at the end of the line. With an echo, the -e option enables the escape sequences.

Use the following template for writing colored text on a colored background.
$ echo -e "\E[COLOR1;COLOR2mSome text goes here."
The "\E[" begins the escape sequence. The semicolon-separated numbers "COLOR1" and "COLOR2" specify a foreground and a background color, according to the table below. (The order of the numbers does not matter, since the foreground and background numbers fall in non-overlapping ranges.) The "m" terminates the escape sequence, and the text begins immediately after that. Note also that single quotes enclose the remainder of the command sequence following the echo -e.

Note:
With an echo, the -e option enables the escape sequences.You can also use printf instead of echo.
printf "\e[COLORm sample text\n"

To print Green text
echo -e "\033[32m Hello World"
or
printf "\e[32m Hello World"

The problem with above statement is that the blue color that starts with the 32 color code is never switched back to the regular color, so any text you type after the prompt and even prompt also is still in the Green color.To return to the plain, normal mode, we have yet another sequence. The "\033[0m" ( so you have to add these entry whenever you required)

Note: Blink attribute doesn't work in any terminal emulator, but it will work on the console.
Combining all these Escape Sequences, you can get more fancy effect.

Use the following template for writing colored text on a colored background.
echo -e "\033[COLOR1;COLOR2m sample text\033[0m";

The semicolon separated numbers "COLOR1" and "COLOR2" specify a foreground and a background color. The order of the numbers does not matter, since the foreground and background numbers fall in non

- overlapping ranges."m" terminates the escape sequence, and the text begins immediately after that.
Although setting the colors separately also work (i.e. \033[44m\033[32m).

There are some differences between colors when combining colors with bold text attribute.
The following table summarises these differences.

Bold OFF
Color
Bold On
Color
0;30
Balck
1;30
Dark Gray
0;31
Red
1;31
Dark Red
0;32
Green
1;32
Dark Green
0;33
Brown
1;33
Yellow
0;34
Blue
1;34
Dark Blue
0;35
Magenta
1;35
Dark Magenta
0;36
Cyan
1;30
Dark Cyan
0;37
Light Gray
1;30
White

The following shell script prints all the colors and codes on the screen.
#!/bin/bash
# This script echoes colors and codes
echo -e "\n\033[4;31mLight Colors\033[0m \t\t\t \033[1;4;31mDark Colors\033[0m"
echo -e " \e[0;30;47m Black \e[0m 0;30m \t\t \e[1;30;40m Dark Gray \e[0m 1;30m"
echo -e " \e[0;31;47m Red \e[0m 0;31m \t\t \e[1;31;40m Dark Red \e[0m 1;31m"
echo -e " \e[0;32;47m Green \e[0m 0;32m \t\t \e[1;32;40m Dark Green \e[0m 1;32m"
echo -e " \e[0;33;47m Brown \e[0m 0;33m \t\t \e[1;33;40m Yellow \e[0m 1;33m"
echo -e " \e[0;34;47m Blue \e[0m 0;34m \t\t \e[1;34;40m Dark Blue \e[0m 1;34m"
echo -e " \e[0;35;47m Magenta \e[0m 0;35m \t\t \e[1;35;40m Dark Magenta\e[0m 1;35m"
echo -e " \e[0;36;47m Cyan \e[0m 0;36m \t\t \e[1;36;40m Dark Cyan \e[0m 1;36m"
echo -e " \e[0;37;47m Light Gray\e[0m 0;37m \t\t \e[1;37;40m White \e[0m 1;37m"

----------------------------------------end of script---------------------------------------------

Run the following shellscript on a console and also in a terminal and see the difference.
#!/bin/bash
clear
echo -e " \033[30m* 30 black forground *\033[0m"
echo -e " \033[31m* 31 red forground *\033[0m"
echo -e " \033[32m* 32 green forground *\033[0m"
echo -e " \033[33m* 33 yellow forground *\033[0m"
echo -e " \033[34m* 34 blue forground *\033[0m"
echo -e " \033[35m* 35 magenta forground *\033[0m"
echo -e " \033[36m* 36 cyan forground *\033[0m"
echo -e " \033[37m* 37 white forground *\033[0m"

echo -e "\033[33;40m 33;40 yellow text on black background\033[0m"
echo -e "\033[33;41m 33;41 yellow text on red background\033[0m"
echo -e "\033[33;42m 33;42 yellow text on green background\033[0m"
echo -e "\033[33;44m 33;44 yellow text on blue background\033[0m"
echo “ Note that 33 will display as brown in console and as yello in terminal”
echo -e "\033[33;45m 33;45 yellow text on magenta background\033[0m"
echo -e "\033[33;46m 33;46 yellow text on cyan background\033[0m"
echo -e "\033[33;47m 33;47 yellow text on white background\033[0m"

echo -e " \033[35;5m * 35 magenta text with slow blink*\033[0m"
echo -e "\033[1;4;33;44m 1;4;33;44 Bold yellow underlined text on blue background\033[0m"

Thus we can conclude
Colors:
\033[30m set foreground color to black
\033[31m set foreground color to red
\033[32m set foreground color to green
\033[33m set foreground color to yellow
\033[34m set foreground color to blue
\033[35m set foreground color to magenta (purple)
\033[36m set foreground color to cyan
\033[37m set foreground color to white
\033[40m set background color to black

\033[41m set background color to red
\033[42m set background color to green
\033[43m set background color to yellow
\033[44m set background color to blue
\033[45m set background color to magenta (purple)
\033[46m set background color to cyan
\033[47m set background color to white

\033[1;30m set foreground color to dark gray
\033[1;31m set foreground color to light red
\033[1;32m set foreground color to light green
\033[1;33m set foreground color to yellow
\033[1;34m set foreground color to light blue
\033[1;35m set foreground color to light magenta (purple)
\033[1;36m set foreground color to light cyan
\033[1;37m set foreground color to white

\033[1;40m set background color to dark gray
\033[1;41m set background color to light red
\033[1;42m set background color to light green
\033[1;43m set background color to yellow
\033[1;44m set background color to light blue
\033[1;45m set background color to light magenta (purple)
\033[1;46m set background color to light cyan
\033[1;47m set background color to white

For other features:

\033[0m reset; clears all colors and styles (to white on black)
\033[1m bold on
\033[3m italics on
\033[4m underline on
\033[5m blink on
\033[7m reverse video on
\033[8m nondisplayed (invisible)
\033[x;yH moves cursor to line x, column y
\033[xA moves cursor up x lines
\033[xB moves cursor down x lines
\033[xC moves cursor right x spaces
\033[xD moves cursor left x spaces
\033[2J clear screen and home cursor

Thursday, March 13, 2014

Concept of mirroring Diskgroup in ASM

Mirroring or Redundancy protects data integrity by storing copies of data on multiple disks. To achieve mirroring ,Oracle introduce concept of Diskgroup in Automatic Storage Management.
Oracle mirrors at the extent level, so you have a primary extent and a mirrored extent.When a disk fails, ASM rebuilds the failed disk using mirrored extents from the other disks within the group, this may have a slight impact on performance as the rebuild takes place.
A diskgorup is a group of fail groups together, a fail group is a group of disks together. So, disk is the minimum identity in this architecture.The disk group type determines the mirroring levels with which Oracle creates files in a disk group.In general, ASM supports three types of redundancy (mirroring*) options.When you create a disk group, you specify an Automatic Storage Management (ASM) disk group type based on one of the following three redundancy levels.The default mirroring levels indicate the mirroring level with which each file is created unless a different mirroring level is designated.

Mirroring Options for ASM Disk Group Types
Disk Group TypeSupported Mirroring LevelsDefault Mirroring Level
External redundancy
Unprotected (none)
Unprotected
Normal redundancy

Two-way
Three-way
Unprotected (None)
Two-way
High redundancy
Three-way
Three-way


External redundancy - doesn't have failure groups and thus is effectively a no-mirroring strategy, which is used when mirroring is provided by the disk subsystem such as RAID.Any write error cause a forced dismount of the disk group. 
Normal redundancy - provides two-way mirroring of all extents in a disk group, which result in two failure groups .A loss of one ASM disk is tolerated.
High redundancy - provides three-way mirroring of all extents in a disk group, which result in three failure groups.A loss of two ASM disks in different failure groups is tolerated.
 
The redundancy level controls how many disk failures are tolerated without dismounting the diskgroup or losing data.There are always failure groups even if they are not explicitly created. If you do not specify a failure group for a disk, then Oracle automatically creates a new failure group containing just that disk. A normal redundancy disk group must contain at least two failure groups. A high redundancy disk group must contain at least three failure groups. However, Oracle recommends using several failure groups.

Below is an example of disk group,
Let's assume, we have two disk controllers at Hardware level .Controller 1 (have four disk connected from diska), Controller 2 (have four disk connected from diskb). If, controller will fail, all disks under it will be unavailable. So, we need to plan our Disk group to take case this as well.

Controller 1:
/devices/diska1
/devices/diska2
/devices/diska3
/devices/diska4

Controller 2:
/devices/diskb1
/devices/diskb2
/devices/diskb3
/devices/diskb4

creates a disk group named dgroup1 with normal redundancy consisting of two failure groups controller1 or controller2 with four disks in each failure group.

$SQLPLUS /NOLOG
SQL> CONNECT / AS SYSASM
Connected to an idle instance.
SQL> STARTUP NOMOUNT

SQL>CREATE DISKGROUP dgroup1 NORMAL REDUNDANCY
FAILGROUP controller1 DISK
'/devices/diska1' NAME diska1,
'/devices/diska2' NAME diska2,
'/devices/diska3' NAME diska3,
'/devices/diska4' NAME diska4

FAILGROUP controller2 DISK
'/devices/diskb1' NAME diskb1,
'/devices/diskb2' NAME diskb2,
'/devices/diskb3' NAME diskb3,
'/devices/diskb4' NAME diskb4

ATTRIBUTE 'au_size'='4M',
 'compatible.asm' = '11.1', 
 'compatible.rdbms' = '11.1';

In the above example,  We made a disk group dgroup1 with normal redundancy (2-way mirroring),  So, we need two fail groups controller1 and controller2. 

When Automatic Storage Management allocates an extent for a normal redundancy file, ASM allocates a primary copy and a secondary copy. Automatic Storage Management chooses the disk on which to store the secondary copy in a different failure group other than the primary copy. Failure groups are used to place mirrored copies of data so that each copy is on a disk in a different failure group. The simultaneous failure of all disks in a failure group does not result in data loss. 

Failure Scenarios:
1. Single Disk failure: Suppose a disk failed in controller2 say(controller2.diskb4) data will remain intact, This disk either will have primary copy or secondary copy of extents. Since, we have two copies, so any how we will have another copy of failed disk into another disk group. So, I would say in this scenario, your data is 100% safe.

2. Multiple Disk Failure: This situation can further divide into two parts Multiple disk failure from Same disk group:  In this case, you will not loos data, because another disk group will have all data. Multiple disk failure from different disk group: Here is the risk, Suppose you loose controller1.diska1 and controller2.diskb4 and unfortunately, extents in diska1 have their mirror copy into diskb4 and both disks are unavailable. So, you will end up loosing data.

3. Disk Controller Failure: Suppose you loose one of your disk controller Controller 1 that means all disks in a fail group. Even, in this case data will not lost because, each extant in fail groupfailgroup1 will have a copy in failgroup2.

You define the failure groups for a disk group when you create an ASM disk group. After a disk group is created, you cannot alter the redundancy level of the disk group. To change the redundancy level of a disk group, create another disk group with the appropriate redundancy and then move the files to the new disk group. 

 Note:- Disk groups with external redundancy do not use failure groups.

Friday, January 17, 2014

Active Database Duplication in 11g

RMAN has the ability to duplicate, or clone, a database from a backup or from an active database. It is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure.

Active database duplication requires that the auxiliary instance have a password file.  This is because the source database will be connecting directly to the auxiliary database, requiring a password file.  One other key is that you want to use the same SYSDBA password as the source database . Also the destination server requires static listener configuration in a "listener.ora" file. 

Active Database Duplication
Oracle 11g introduced the ability to create duplicate databases directly without the need for a backup. This is known as active database duplication. The process is similar to the backup-based duplication, with a few exceptions.First, and most obviously, you don't need a backup of the source system, but it does have to be in ARCHIVELOG mode.
Here i done my experiments in the same machine .

C:\Users\Admin>rman target sys/sys@db11g catalog rman/rman@catdb auxiliary sys/sys@clonedb



Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 17 11:25:30 2014


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

connected to target database: DB11G (DBID=305973962)
connected to recovery catalog database
connected to auxiliary database: CLONE11G (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO CLONE11G from active database;

Starting Duplicate Db at 17-JAN-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=189 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB11G'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''CLONE11G'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  'D:\DATA\CLONE11G\CONTROL01.CTL';
   restore clone controlfile to  'D:\DATA\CLONE11G\CONTROL02.CTL' from
 'D:\DATA\CLONE11G\CONTROL01.CTL';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DB11G'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''CLONE11G'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     534462464 bytes

Fixed Size                     2177456 bytes
Variable Size                348128848 bytes
Database Buffers             176160768 bytes
Redo Buffers                   7995392 bytes

Starting backup at 17-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=C:\APP\ADMIN\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFDB11G.ORA tag=TAG20140117T112848 RECID=1 STAMP=837084529
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 17-JAN-14

Starting restore at 17-JAN-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=156 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 17-JAN-14

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
 "D:\DATA\CLONE11G\SYSTEM01.DBF";
   set newname for datafile  2 to
 "D:\DATA\CLONE11G\SYSAUX01.DBF";
   set newname for datafile  3 to
 "D:\DATA\CLONE11G\UNDOTBS01.DBF";
   set newname for datafile  4 to
 "D:\DATA\CLONE11G\USERS01.DBF";
   backup as copy reuse
   datafile  1 auxiliary format
 "D:\DATA\CLONE11G\SYSTEM01.DBF"   datafile
 2 auxiliary format
 "D:\DATA\CLONE11G\SYSAUX01.DBF"   datafile
 3 auxiliary format
 "D:\DATA\CLONE11G\UNDOTBS01.DBF"   datafile
 4 auxiliary format
 "D:\DATA\CLONE11G\USERS01.DBF"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 17-JAN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=D:\DATA\DB11G\SYSTEM01.DBF
output file name=D:\DATA\CLONE11G\SYSTEM01.DBF tag=TAG20140117T112857
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=D:\DATA\DB11G\SYSAUX01.DBF
output file name=D:\DATA\CLONE11G\SYSAUX01.DBF tag=TAG20140117T112857
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=D:\DATA\DB11G\UNDOTBS01.DBF
output file name=D:\DATA\CLONE11G\UNDOTBS01.DBF tag=TAG20140117T112857
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=D:\DATA\DB11G\USERS01.DBF
output file name=D:\DATA\CLONE11G\USERS01.DBF tag=TAG20140117T112857
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 17-JAN-14

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "D:\ARCHIVE2\ARC0000000022_0836304397.0001" auxiliary format
 "D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001"   ;
   catalog clone archivelog  "D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 17-JAN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=22 RECID=21 STAMP=837084582
output file name=D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 17-JAN-14

cataloged archived log
archived log file name=D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001 RECID=21 STAMP=837084585

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=837084585 file name=D:\DATA\CLONE11G\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=837084585 file name=D:\DATA\CLONE11G\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=837084585 file name=D:\DATA\CLONE11G\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=837084585 file name=D:\DATA\CLONE11G\USERS01.DBF

contents of Memory Script:
{
   set until scn  1453391;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 17-JAN-14
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 22 is already on disk as file D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001
archived log file name=D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001 thread=1 sequence=22
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-JAN-14

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
 ''CLONE11G'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     534462464 bytes

Fixed Size                     2177456 bytes
Variable Size                348128848 bytes
Database Buffers             176160768 bytes
Redo Buffers                   7995392 bytes

sql statement: alter system set  db_name =  ''CLONE11G'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     534462464 bytes

Fixed Size                     2177456 bytes
Variable Size                348128848 bytes
Database Buffers             176160768 bytes
Redo Buffers                   7995392 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE11G" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( 'D:\DATA\CLONE11G\REDO01.LOG' ) SIZE 50 M  REUSE,
  GROUP  2 ( 'D:\DATA\CLONE11G\REDO02.LOG' ) SIZE 50 M  REUSE,
  GROUP  3 ( 'D:\DATA\CLONE11G\REDO03.LOG' ) SIZE 50 M  REUSE
 DATAFILE
  'D:\DATA\CLONE11G\SYSTEM01.DBF'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "D:\DATA\CLONE11G\TEMP01.DBF";
   switch clone tempfile all;
   catalog clone datafilecopy  "D:\DATA\CLONE11G\SYSAUX01.DBF",
 "D:\DATA\CLONE11G\UNDOTBS01.DBF",
 "D:\DATA\CLONE11G\USERS01.DBF";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to D:\DATA\CLONE11G\TEMP01.DBF in control file

cataloged datafile copy
datafile copy file name=D:\DATA\CLONE11G\SYSAUX01.DBF RECID=1 STAMP=837084606
cataloged datafile copy
datafile copy file name=D:\DATA\CLONE11G\UNDOTBS01.DBF RECID=2 STAMP=837084606
cataloged datafile copy
datafile copy file name=D:\DATA\CLONE11G\USERS01.DBF RECID=3 STAMP=837084606

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=837084606 file name=D:\DATA\CLONE11G\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=837084606 file name=D:\DATA\CLONE11G\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=837084606 file name=D:\DATA\CLONE11G\USERS01.DBF

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 17-JAN-14

RMAN> exit

Note:- 
  • Auxiliary instance must be available through Oracle Net if you are duplicating from an ACTIVE database.
  • Rman automatically creates  a default server parameter file for the auxiliary instance if the  auxiliary instance was not started with a server parameter file.


Tuesday, January 7, 2014

Retention Policy in rman REDUNDANCY or RECOVERY WINDOW , which one is effective ?

What is  Retention Policy in RMAN ?
Setting the RETENTION POLICY in RMAN keep track about the backup files and tells the report whether they are obsoleted and whether it is needed to backup the database or datafile.
Notes:- 
  • If you have flash recovery configured then the database automatically deletes unnecessary files from the flash recovery area based on its internal disk quota rules. The disk quota rules are distinct from the backup retention policy rules, but the database will never delete files in violation of the retention policy to satisfy the disk quota.If you don't have flash recovery configured then the database does not delete any file even they are obsolete.
How to set retention policy in RMAN ? 
The configuration parameter RETENTION POLICY can be set by CONFIGURE RETENTION POLICY  TO .... 

What is the use of crosschek and obsolete command ?
A backup becomes obsolete based on retention policy, that it is not needed for recovery.A backup becomes expired only when RMAN perform CROSSCHECK and can't find the file. (A most common is file is deleted by OS). Obsolete means "not needed," whereas expired means "not found."

Issue the following RMAN command to crosscheck backup of an Oracle Database,
RMAN> crosscheck backup of database;
using channel ORA_DISK_1
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=D:\STAGE\RACDEVD1_503\DATAFILE_BACKUP recid=79 stamp=475839783
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=D:\STAGE\RACDEVD1_504\DATAFILE_BACKUP recid=137 stamp=475839784
crosschecked backup piece: found to be ‘AVAILABLE’
backup piece handle=D:\DGSTAGE\DPLCU5L5_1_1 recid=132 stamp=475839755
crosschecked backup piece: found to be ‘AVAILABLE’
backup piece handle=D:\DGSTAGE\DQLCU62F_1_1 recid=133 stamp=475839756Crosschecked 4 objects

As we see the RMAN crosscheck command compares the RMAN  catalog entries with the actual OS files and reports to locate "expired" or "obsolete" RMAN catalog entries.
Once a RMAN crosscheck identified expired, deleted  entries you can run an RMAN delete command to remove these entries to synchronize the RMAN catalog / controlfile  with the real database files:
RMAN> delete expired backup;
Identify which backups are obsolete and no longer needed for recovery
RMAN> REPORT OBSOLETE;
Delete obsolete backup information from RMAN repository.
RMAN> DELETE OBSOLETE;
How can we set retention policy in RMAN ?
There is two mutually exclusive options for setting a retention policy , redundancy and recovery window.

To set Retention policy to  redundancy 2 copies,
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

set Retention Policy to  Recovery Window of 7 days,
RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Whenever retention policy is set to redundancy of 2 copies the rman at least retains latest 2 copies of each datafile. If I took 3rd backup of datafile 3 then 1st backup of datafile 3 become obsolete. 

If you specify a recovery window of 7 days. That means rman will not obsolete any backup needed for recovery to any point in the last seven days. Check. But what's the subtle detail?




Suppose you take a full backup on 1 Sep, then start taking incremental backups going forward. Now we're down to 10 Sep which puts that full backup 3 days earlier than the recovery window. However, it is still the most recent full backup and so is needed to recover into any time during the 7 day recovery window. Therefore, it will NOT be marked obsolete.
Think about what this does in the textbook backup routine of a full backup every 7 days, with incremental backups the other 6 days. We take a full backup on day 1. We take incremental backups on days 2 - 7. We take a full backup again on Day 8, and incrementals on days 9 - 14. 
Day  =  1  2  3  4  5  6  7  8  9 10 11 12 13 14
Bkup =  F  I   I   I  I   I   I  F   I   I   I   I   I    I
On day 14, our recovery window reaches back to day 7, and the only way to recover to day 7 is to use the full backup from day 1 and the incrementals from 2 through 7. So even though we have a 7 day recovery window, we actually have 14 days worth of backups that are yet to go obsolete. On day 15, we can obsolete and drop everything prior to the full backup on Day 8. Thus the Recovery Window retention policy doesn't just keep the last N days of the backup. It calculates the point-of-recovery (POR) value, accounting the current date and the number of the days mentioned in the retention policy. 

The default retention policy is REDUNDANCY = 1
you can disable the retention policy by setting,
RMAN>CONFIGURE RETENTION POLICY TO NONE;
If the retention policy is configured to NONE, then REPORT OBSOLETE and DELETE OBSOLETE do not consider any backups to be obsolete

Conclusion :- 
When having a rman retention policy based on REDUNDANCY is a bad idea ,if you intend to incomplete recover your database to a previous time in the past it's really a good idea to switch to a retention policy based on a "RECOVERY WINDOW" instead.