Sunday, December 12, 2010

Configure RMAN for RAC (multiple instances)

On our exadata machine we were planning for RMAN backup satisfying below:

1. Parallelism 14
2. Use more tan 1 instance (instance 3 & 4)
3. Exclude some of the tablespaces [due to limitation]

In order to satisfy 1 & 2 below changes have beed done using rman:

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT 'sys/password@dw3';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT 'sys/password@dw4';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK CONNECT 'sys/password@dw3';
CONFIGURE CHANNEL 4 DEVICE TYPE DISK CONNECT 'sys/password@dw4';
CONFIGURE CHANNEL 5 DEVICE TYPE DISK CONNECT 'sys/password@dw3';
CONFIGURE CHANNEL 6 DEVICE TYPE DISK CONNECT 'sys/password@dw4';
CONFIGURE CHANNEL 7 DEVICE TYPE DISK CONNECT 'sys/password@dw3';
CONFIGURE CHANNEL 8 DEVICE TYPE DISK CONNECT 'sys/password@dw4';
CONFIGURE CHANNEL 9 DEVICE TYPE DISK CONNECT 'sys/password@dw3';
CONFIGURE CHANNEL 10 DEVICE TYPE DISK CONNECT 'sys/password@dw4';
CONFIGURE CHANNEL 11 DEVICE TYPE DISK CONNECT 'sys/password@dw3';
CONFIGURE CHANNEL 12 DEVICE TYPE DISK CONNECT 'sys/password@dw4';
CONFIGURE CHANNEL 13 DEVICE TYPE DISK CONNECT 'sys/password@dw3';
CONFIGURE CHANNEL 14 DEVICE TYPE DISK CONNECT 'sys/password@dw4';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 14;



Now we have our default configuration satisfied for 1 & 2, we are ready to deal with point 3. We can easily do this inside the RMAN script [above can also be done inside the RMAN backup script but in that case password of sys or system will become open]:


FULL LEVEL 0


configure exclude for tablespace 'USERS';
configure exclude for tablespace 'TBS1';
configure exclude for tablespace 'TBS2';
configure exclude for tablespace 'TBS3';
configure exclude for tablespace 'TBS4';

backup incremental level 0 database;

configure exclude for tablespace 'USERS' clear;
configure exclude for tablespace 'TBS1' clear;
configure exclude for tablespace 'TBS2' clear;
configure exclude for tablespace 'TBS3' clear;
configure exclude for tablespace 'TBS4' clear;

SQL 'alter system archive log current';

backup archivelog all DELETE INPUT;
backup current controlfile;

delete noprompt obsolete;

delete noprompt backup of archivelog until time 'sysdate-8';



LEVEL 1 DIFFERENTIAL


configure exclude for tablespace 'USERS';
configure exclude for tablespace 'TBS1';
configure exclude for tablespace 'TBS2';
configure exclude for tablespace 'TBS3';
configure exclude for tablespace 'TBS4';

backup incremental level 1 database;

configure exclude for tablespace 'USERS' clear;
configure exclude for tablespace 'TBS1' clear;
configure exclude for tablespace 'TBS2' clear;
configure exclude for tablespace 'TBS3' clear;
configure exclude for tablespace 'TBS4' clear;

SQL 'alter system archive log current';
backup archivelog all DELETE INPUT;
backup current controlfile;

delete noprompt obsolete;

delete noprompt backup of archivelog until time 'sysdate-8';



LEVEL 1 CUMULATIVE:

configure exclude for tablespace 'USERS';
configure exclude for tablespace 'TBS1';
configure exclude for tablespace 'TBS2';
configure exclude for tablespace 'TBS3';
configure exclude for tablespace 'TBS4';

backup incremental level 1 cumulative database;

configure exclude for tablespace 'USERS' clear;
configure exclude for tablespace 'TBS1' clear;
configure exclude for tablespace 'TBS2' clear;
configure exclude for tablespace 'TBS3' clear;
configure exclude for tablespace 'TBS4' clear;


SQL 'alter system archive log current';
backup archivelog all DELETE INPUT;
backup current controlfile;

delete noprompt obsolete;

delete noprompt backup of archivelog until time 'sysdate-8';


After configuration as above the result summery is as below:

Total Data Size: 6 TB
Excluded Data Size: 4 TB
Total Backed-up Data Size(on DB): 2 TB [90% data using EHCC or compressed for query high]
Backupset Size of one level 0 : 1.25 TB
Backupset Size of one level 1 : 100 GB
Total time for level 0: 6 hours
Total time for level 1: 60 minutes

In order to find backup piece size we have used below query:

select ctime "Date",
decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type,
bsize "Size GB"
from (select trunc(bp.completion_time) ctime,
backup_type,
round(sum(bp.bytes / 1024 / 1024/1024), 2) bsize
from v$backup_set bs, v$backup_piece bp
where bs.set_stamp = bp.set_stamp
and bs.set_count = bp.set_count
and bp.status = 'A'
group by trunc(bp.completion_time), backup_type)
order by 1, 2;
[thanks to ebrian: http://forums.oracle.com/forums/thread.jspa?threadID=1094667]

No comments:

Post a Comment