How to replace undo tablespace in an Oracle database

Task: replace UNDO tablespace in an Oracle database

Steps:

1. See the UNDO settings in the database: SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- -------------------- undo_management string AUTO undo_retention integer 10800 undo_tablespace string UNDO 2. Determine the size of the UNDO datafiles: SQL> select bytes, name from v$datafile where upper(name) like '%UNDO%'; BYTES NAME -------------------- ----------------------------------------------- 1433075712 /fs/u02/oradata/testdb/undo_01.dbf 3. Create a new UNDO tablespace according to the requirement: CREATE UNDO TABLESPACE UNDOTBS1 datafile '/fs/u02/oradata/testdb/undotbs01.dbf' size 2000m; Tablespace created. 4. Edit the init.ora file and change the parameter "undo_tablespace=UNDOTBS1" and "undo_management=MANUAL". Note that a pfile may need to be created first by using "create pfile=… from spfile=…". "undo_management=MANUAL" ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again. 5. Shut down the databse cleanly by performing a shutdown immediate. SQL> shutdown immediate 6. Startup the database (specify the pfile if needed.) SQL> startup 7. Confirm the new tablespace is in use: SQL> show parameter undo_tablespace NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace string UNDOTBS1 8. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline: SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3; OWNER SEGMENT_NAME TABLESPACE_NAME STATUS ------ ------------------------------ ------------------------------ ---------------- SYS SYSTEM SYSTEM ONLINE PUBLIC _SYSSMU1$ UNDO OFFLINE PUBLIC _SYSSMU2$ UNDO OFFLINE PUBLIC _SYSSMU3$ UNDO OFFLINE PUBLIC _SYSSMU4$ UNDO OFFLINE PUBLIC _SYSSMU5$ UNDO OFFLINE PUBLIC _SYSSMU6$ UNDO OFFLINE PUBLIC _SYSSMU7$ UNDO OFFLINE PUBLIC _SYSSMU8$ UNDO OFFLINE PUBLIC _SYSSMU9$ UNDO OFFLINE PUBLIC _SYSSMU10$ UNDO OFFLINE PUBLIC _SYSSMU11$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU12$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU13$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU14$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU15$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU16$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU17$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU18$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU19$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU20$ UNDOTBS1 OFFLINE 21 rows selected. If the old segments are online, then they must be taken offline as follows: SQL>alter rollback segment "_SYSSMU3$" offline; 9. Drop the old undo tablespace and remove from the OS directory: SQL> drop tablespace UNDO; Tablespace dropped. SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3; OWNER SEGMENT_NAME TABLESPACE_NAME STATUS ------ ------------------------------ ------------------------------ ---------------- SYS SYSTEM SYSTEM ONLINE PUBLIC _SYSSMU11$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU12$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU13$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU14$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU15$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU16$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU17$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU18$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU19$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU20$ UNDOTBS1 OFFLINE 11 rows selected. SQL> shutdown immediate 10. Modify the init.ora file to change undo_management to AUTO and restart the database. spfile may need to be created. : SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3; OWNER SEGMENT_NAME TABLESPACE_NAME STATUS ------ ------------------------------ ------------------------------ ---------------- SYS SYSTEM SYSTEM ONLINE PUBLIC _SYSSMU11$ UNDOTBS1 ONLINE PUBLIC _SYSSMU12$ UNDOTBS1 ONLINE PUBLIC _SYSSMU13$ UNDOTBS1 ONLINE PUBLIC _SYSSMU14$ UNDOTBS1 ONLINE PUBLIC _SYSSMU15$ UNDOTBS1 ONLINE PUBLIC _SYSSMU16$ UNDOTBS1 ONLINE PUBLIC _SYSSMU17$ UNDOTBS1 ONLINE PUBLIC _SYSSMU18$ UNDOTBS1 ONLINE PUBLIC _SYSSMU19$ UNDOTBS1 ONLINE PUBLIC _SYSSMU20$ UNDOTBS1 ONLINE 11 rows selected.

More Oracle DBA tips, please visit Oracle DBA Tips  

DBA_NETWORK_ACLS

 Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

====================================================================

DBA_NETWORK_ACLS describes the access control list assignments to network hosts.

Column	Datatype	NULL	Description
HOST	VARCHAR2(1000)	NOT NULL	Network host
LOWER_PORT	NUMBER(5)		Lower bound of the port range
UPPER_PORT	NUMBER(5)		Upper bound of the port range
ACL	VARCHAR2(4000)		Path of the access control list
ACLID	RAW(16)	NOT NULL	Object ID of the access control list

Note:

1. There are two data dictionary views related to ACL: DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES. ACLs are stored in XML DB, which must be installed for the use of ACLs. Two steps are needed to create ACL. The first step is to create the actual ACL and define privileges for it:

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => ’file_name.xml’,
description => ’file description’,
principal => ’user_or_role’,
is_grant => TRUE|FALSE,
privilege => ’connect|resolve’,
start_date => null|timestamp_with_time_zone,
end_date => null|timestamp_with_time_zone);
END;

The second step is to assign network hosts to the ACL:

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => ’file_name.xml’,
host => ’network_host’,
lower_port => null|port_number,
upper_port => null|port_number);
END;

Oracle data dictionary views

Oracle dynamic performance views

DBA_NETWORK_ACL_PRIVILEGES

DBA_NETWORK_ACL_PRIVILEGES describes the network privileges defined in all access control lists that are currently assigned to network hosts.

Column	Datatype	NULL	Description
ACL	VARCHAR2(4000)		Path of the access control list
ACLID	RAW(16)	NOT NULL	Object ID of the access control list
PRINCIPAL	VARCHAR2(4000)		Principal (database user or role) whom the privilege is granted to or denied from
PRIVILEGE	VARCHAR2(7)		Network privilege
IS_GRANT	VARCHAR2(5)		Indicates whether the privilege is granted (true) or denied (false)
INVERT	VARCHAR2(5)		Indicates whether the access control entry contains invert principal (true) or not (false)
START_DATE	TIMESTAMP(9) WITH TIME ZONE		Start date of the access control entry
END_DATE	TIMESTAMP(9) WITH TIME ZONE		End date of the access control entry

Note:

1. There are two data dictionary views related to ACL: DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES. ACLs are stored in XML DB, which must be installed for the use of ACLs. Two steps are needed to create ACL. The first step is to create the actual ACL and define privileges for it:

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => ’file_name.xml’,
description => ’file description’,
principal => ’user_or_role’,
is_grant => TRUE|FALSE,
privilege => ’connect|resolve’,
start_date => null|timestamp_with_time_zone,
end_date => null|timestamp_with_time_zone);
END;

The second step is to assign network hosts to the ACL:

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => ’file_name.xml’,
host => ’network_host’,
lower_port => null|port_number,
upper_port => null|port_number);
END;

Oracle data dictionary views

Oracle dynamic performance views