Thursday, May 3, 2018

Change UNDO tablespace from smallfile to bigfile in RAC environment

With below steps UNDO tablespace in RAC can be converted from small file to bigfile.

RAC Database: TESTQA
Instances: TESTQA1,TESTQA2


1.NODE1-(Login to TESTQA1 instance)

create bigfile undo tablespace UNDOTBS3 datafile
size 200m autoextend on next 5m maxsize 32727m
online
retention noguarantee
blocksize 8k
flashback on;

alter system set undo_tablespace= UNDOTBS3 scope=both sid='testqa1';

drop tablespace UNDOTBS1 including contents and datafiles;


create bigfile undo tablespace UNDOTBS1 datafile
size 200m autoextend on next 5m maxsize 32727m
online
retention noguarantee
blocksize 8k
flashback on;

Alter system set undo_tablespace= UNDOTBS1 scope=both sid='testqa1';

Drop tablespace UNDOTBS3 including contents and datafiles;

2.NODE2-(Login to TESTQA2 instance)

Create bigfile undo tablespace UNDOTBS4 datafile
size 200m autoextend on next 5m maxsize 32727m
online
retention noguarantee
blocksize 8k
flashback on;

Alter system set undo_tablespace= UNDOTBS4 scope=both sid='testqa2';
Drop tablespace UNDOTBS2 including contents and datafiles;

Create bigfile undo tablespace UNDOTBS2 datafile
size 200m autoextend on next 5m maxsize 32727m
online
retention noguarantee
blocksize 8k
flashback on;

Alter system set undo_tablespace= UNDOTBS2 scope=both sid='testqa2';
Drop tablespace UNDOTBS4 including contents;

No comments:

Post a Comment