First we discuss what is role of Undo Tablespace.
* Undo tablespace contains undo-segments which will undo the transactions in oracle database.
* Purpose of Undo :-
1. It provides read consistency because of this Users always get consistent data.
2. If you face any disaster in your database Undo records are used for recovery purpose.
3. Undo records are used to undo any uncommitted changes applied from the redo log to the data files.
Undo in RAC:-
* Each instance in the RAC system can only use one undo tablespace at a time. In other words, instances cannot share undo tablespace in RAC.
* Each instance in the cluster, being an independent transaction-processing environment, maintains its own UNDO area for undo management.
NOTE:-
* On RAC configuration each instance has assigned its own UNDO tablespace. So in order to resize the UNDO you must create a new one for each instance and assign it to the instance then you cane drop the old ones.
* When the instance starts up, the database automatically selects first available undo tablespace. If there is no undo tablespace available,
Oracle uses the SYSTEM rollback segment for undo. and an alert the message to the alert log file to warn that the system is running without
an undo tablespace. ORA-01552 error is issued for any attempts to write non-SYSTEM related undo to the SYSTEM rollback segment.
***************
SQL> select name from v$database;
NAME
---------
HRLIVE
- CREATING NEW UNDO TABLESPACE FOR INSTACE 1
SQL> create undo tablespace undotbs01 datafile '+DATA' size 5g;
Tablespace created.
- NOW CREATE UNDO FOR INSTANCE 2
SQL> create undo tablespace undotbs02 datafile '+DATA' size 5g;
Tablespace created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/hrlive/datafile/system.286.867076971
+DATA/hrlive/datafile/sysaux.279.867076971
+DATA/hrlive/datafile/undotbs1.282.867076971
+DATA/hrlive/datafile/users.285.867076971
+DATA/hrlive/datafile/undotbs2.278.867077097
+DATA/hrlive/datafile/users.289.867607927
+DATA/hrlive/datafile/undotbs01.287.867689853
+DATA/hrlive/datafile/undotbs02.290.867690103
8 rows selected.
>> Now make it permanent to undo-tablspaces for both the instances <<
SQL> alter system set undo_tablespace=undotbs01 scope=both sid='hrlive1';
System altered.
SQL> alter system set undo_tablespace=undotbs02 scope=both sid='hrlive2';
System altered.
SQL> SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE a.usn = b.usn
AND a.usn = c.xidusn
AND c.ses_addr = d.saddr
AND a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS01'
);
no rows selected
SQL> SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE a.usn = b.usn
AND a.usn = c.xidusn
AND c.ses_addr = d.saddr
AND a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS02'
);
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
>>> AFTER 3 MINUTES SAME COMMAND I EXECUTED <<<
SQL> SELECT a.name,b.status , d.username , d.sid , d.serial#
2 FROM v$rollname a,v$rollstat b, v$transaction c , v$session d
3 WHERE a.usn = b.usn
4 AND a.usn = c.xidusn
5 AND c.ses_addr = d.saddr
6 AND a.name IN (
7 SELECT segment_name
8 FROM dba_segments
9 WHERE tablespace_name = 'UNDOTBS1'
10 );
no rows selected
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name='UNDOTBS01' and status='ONLINE';
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
----------------------------------------------------------------------------------------------------
PUBLIC _SYSSMU188_1826508972$ UNDOTBS01 ONLINE
SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name='UNDOTBS02' and status='ONLINE';
no rows selected
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.
SQL> select b.tablespace_name, tbs_size SizeGB, a.free_space FreeGB
from
(select tablespace_name, round(sum(bytes)/1024/1024/1024,1) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;
TABLESPACE_NAME SIZEGB FREEGB
------------------------------ ---------- ---------------------------------
SYSAUX 2 1.5
USERS 20 11.1
SYSTEM 2 1.2
UNDOTBS01 5 5
UNDOTBS02 5 5
TEMP 5
6 rows selected.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/hrlive/tempfile/temp.283.867077081
FROM NODE 1:-
=============
SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name='UNDOTBS01' and status='ONLINE';
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------------------------------------------------------------------------------
PUBLIC _SYSSMU188_1826508972$ UNDOTBS01 ONLINE
PUBLIC _SYSSMU189_4021780268$ UNDOTBS01 ONLINE
PUBLIC _SYSSMU190_1974295278$ UNDOTBS01 ONLINE
93 rows selected.
FROM NODE 2:-
=============
SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name='UNDOTBS02' and status='ONLINE';
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
---------------------------------------------------------------------------------------------------
PUBLIC _SYSSMU281_1191757940$ UNDOTBS02 ONLINE
PUBLIC _SYSSMU282_1634911936$ UNDOTBS02 ONLINE
PUBLIC _SYSSMU283_1904379378$ UNDOTBS02 ONLINE
93 rows selected.
Happy Learning ....
great thanks
ReplyDelete