ORA-01552

Message

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERDATA'

Cause

Tried to use the system rollback segment for operations involving non-system tablespace. If your DB is a clone database then this will happen when you attempting any data modification outside of the system tablespace. Only the system rollback segment can be online in a clone database.

Action

Create one or more private/public segment(s), shutdown and then startup your DB again. May need to modify the INIT.ORA parameter rollback_segments to acquire private rollback segment. If this is a clone database being used for tablspace point in time recovery then this operation is not allowed. If the non-system tablespace has AUTO segment space management, then create an undo tablespace and make it the default undo tablespace of your DB.

Resize the Online Redo Log files

When you are working on large data movement in Oracle, you must concern about some performance overhead and online redo log switch is one of them. Recently i am working on a migration project where i need to migrate a 19.5GB MySQL 5.0 database to ORACLE 10gR2 database. I used Oracle SQL developer to do the job and the job takes about 5 hour to complete. To do the job faster i perform some tuning stuff on ORACLE and increasing the Online Redo Log files is the most fruitful one.

Now i am going to describe how i have done this. The procedure was learned from a release note of metalink .



1. First see the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 52428800 INACTIVE
2 52428800 CURRENT
3 52428800 INACTIVE

Logs are 50MB from above which is default in oracle 10g, let's size them to 100MB.


2. Retrieve all the log member names for the groups:

SQL> select group#, member from v$logfile;

GROUP# MEMBER
--------------- ----------------------------------------
1 /usr/oracle/dbs/log1PROD.dbf
2 /usr/oracle/dbs/log2PROD.dbf
3 /usr/oracle/dbs/log3PROD.dbf


3. Now drop the log group 1 and recreate it with increased size

SQL> alter database drop logfile group 1;

SQL> alter database add logfile group 1
'/usr/oracle/dbs/log1PROD.dbf' size 100M reuse;

4. Check the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 CURRENT
3 52428800 INACTIVE
5. Do the same for log group 3

SQL> alter database drop logfile group 3;

SQL> alter database add logfile group 3
'/usr/oracle/dbs/log3PROD.dbf' size 100M reuse;


SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 CURRENT
3 10485760 INACTIVE
6. Now we go for group 2 but it is now used by oracle, so first switch the log

SQL> alter system switch logfile;

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 INACTIVE
3 10485760 CURRENT
SQL> alter database drop logfile group 2;


SQL> alter database add logfile group 2
'/usr/oracle/dbs/log2PROD.dbf' size 100M reuse;

7. Check the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 10485760 UNUSED
3 10485760 CURRENT

** some times you may find that a log group status is ACTIVE, in that case you should
make a database checkpoint like this

SQL> ALTER SYSTEM CHECKPOINT;

** FOR safety reason please take a full backup of your database before performing this.

Automatic Undo Management

In earlier versions of ORACLE, transactions undo information was stored into undo segment until a commit or rollback command was issued. When the commit or rollback command was issued, it purge the undo segment which contains corresponding transaction undo information. This system is known as manual undo management.

In version 9i ORACLE introduce automatic undo management along with manual management. So one can use either automatic or manual management but not both at a time. The new method freed DBA from periodical undo management and tuning. In also facilitate the DBA to specify how long undo information is stored after a commit occur. This feature eliminate the "snapshot too old" error of long running queries and also support ORACLE flashback queries.

Create Undo Tablespace

CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/u01/undotbs01_01.dbf'
SIZE 1024M BLOCKSIZE 16K;

alter system set undo_tablespace='UNDOTBS' scope=both;

Enabling Automatic Undo Management

UNDO_MANAGEMENT = AUTO -- Default is MANUAL
UNDO_TABLESPACE = undotbs_01 -- The name of the undo tablespace.
UNDO_RETENTION = 900 -- The time undo is retained. Default is 900 seconds.
UNDO_SUPPRESS_ERRORS = TRUE -- Suppress errors when MANUAL undo admin SQL statements are issued.

Please set the following parameters

-- Dynamic Parameters.
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS_02;
ALTER SYSTEM SET UNDO_RETENTION=1800;
ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=FALSE;

-- Static Parameters.
ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;

Maintenance of Undo Tablespace

-- Add a datafile.

ALTER TABLESPACE undotbs_01 ADD DATAFILE '/u0/undo0102.dbf'
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

-- Resize an undo datafile.

ALTER DATABASE DATAFILE '/u0/undo0102.dbf' RESIZE 10M;

-- Perform backup operations

ALTER TABLESPACE undotbs_01 BEGIN BACKUP;
ALTER TABLESPACE undotbs_01 END BACKUP;

-- Drop an undo tablespace.
DROP TABLESPACE undotbs_01;

Sometimes the undo tablespace become too big to manage. In such case you can resize the datafiles or create a new undo tablespace in another disk location

- Resize an undo datafile.

ALTER DATABASE DATAFILE '/u0/undo0102.dbf' RESIZE 10M;

-- create new undo tablespace and drop the old one

CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/u0/undotbs01_01.dbf' SIZE 1024M BLOCKSIZE 16K;

ALTER SYSTEM SET undo_tablespace='UNDOTBS' scope=both;

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Monitoring Undo Tablespace

You may use the following dictionary viewers:

V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
DBA_UNDO_EXTENTS