Creating New UNDO Tablespace

1. Create another undo tablespace (in Primary).

CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE

'/u03/oradata/undotbs201.dbf' SIZE 524288000 AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

2. Make sure file being created in Primary database, does not conflict with the file going to be created in standby database. This may happen if we have db_file_name_convert parameter setup in standby database.

3. In primary database switch the log file

alter system switch logfile

4. On standby database make sure tablespace is created

select name from v$tablespace

where name = 'UNDOTBS2';

5. Also check the data file is created properly on standby database

select name from v$datafile

where name like '%undotbs201%';

6. Switch the new undo tablespace in Primary database

alter system set undo_tablespace=undotbs2;

7. Make sure no transactions are pointing to old UNDO tablespace

SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo",
t1.tablespace_name
FROM SYS.v_$rollname r,
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x,
dba_rollback_segs t1
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
AND t1.segment_id = r.usn
AND t1.tablespace_name = 'UNDOTBS1';

8. If above query returns no rows selected, drop the old UNDO tablespace

drop tablespace undotbs1 including contents and datafiles;

9. Restart the standby database by setting the changed UNDO_TABLESPACE init.ora parameter to new undo tablespace.

Note: Do not forget it as it may create the issue when we will do the DR testing.

10. Change the standby init.ora parameter

*.undo_tablespace='UNDOTBS2'

11. Then restart the standby database as followings:

shutdown immediate;

startup nomount pfile='?/dbs/initbfrprd1.ora'

create spfile from pfile;

shutdown immediate;

startup nomount;

alter database mount standby database;

alter database recover managed standby database disconnect from session;

12. Make sure standby is in sync with primary.