ORA-30036
This error ORA-30036 could be very serious in a situation that is described below and may make users panic:
One day, a DBA was importing data and waited it for a long time. He suspected the operation was hanged, so he tried to cancel the operation by interrupting the job, but it didn't work. After he killed the server process of importing job on OS-level, all users were hanged. He made a decision to shutdown abort the database. As of he startup the database, the story began with the error message:
...
Sat May 12 19:04:27 2010
Errors in file D:\oracle\admin\dbname\udump\dbname_ora_2154.trc:
ORA-30036 UNABLE TO EXTEND SEGMENT BY 8 IN UNDO TABLESPACE 'UNDOTBS1'
...
There're two symptoms of ORA-30036 in above case that we should notice:
- Database hanged
- Database refused to startup
Description
ORA-30036: unable to extend segment by string in undo tablespace 'string'
Cause
the specified undo tablespace has no more space available.
Action
Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.
Before you follow "Action", the first thing you must do is to open the database. In our case, we can't even open the database, how can we add more space to the undo tablespace?
Solutions
You can choose one of the following solutions to open your database:
- Set initialization parameter UNDO_MANAGEMENT to MANUAL: It's to help the database escape from the confinement of the default undo tablespace.
- Set initialization parameter UNDO_RETENTION to a small value: It's to signal the database to discard expired undo data.
Steps for MANUAL UNDO_MANAGEMENT
The above two solutions against ORA-30036 all work. Let's see the first solution:
- Mount database.
- Enable manual undo management.
- Restart database.
- Add a datafile to undo tablespace (or resize the undo datafile).
- Enable automatic undo management.
- Restart database.
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT='MANUAL' SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
SQL> ALTER TABLESPACE undotbs1 ADD DATAFILE '/path_to_datafile/datafilename.dbf' SIZE 2G;
Further reading: How Big a Data File Can Be?
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT='AUTO' SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
If you use a modified pfile to open database, please skip the step 1, 2 and 5, and use the following statement to replace step 3
SQL> STARTUP OPEN PFILE='?/dbs/init<sid>.ora';
Steps for Smaller UNDO_RETENTION
In the second solution, we use a pfile to open the database:
- Make sure the pfile contains UNDO_RETENTION.
- Open the database with a modified pfile.
- Make sure the space is freed.
- Restart database, don't use pfile to open.
...
UNDO_RETENTION=1
...
SQL> STARTUP OPEN PFILE='?/dbs/init<sid>.ora';
SQL> SELECT SUM(bytes)/1024/1024 "Free Space (MB)" FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='UNDOTBS1';
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
Preventive Actions to ORA-30036
As a DBA, we should always think about how to prevent various errors including ORA-30036. There are several techniques to prevent this error when you import a chunk of data.
- Disable retention guarantee temporarily.
- Decrease UNDO_RETENTION to a smaller value temporarily.
- Enable AUTOEXTEND.
Retention guarantee is working well under normal conditions, but if you are going to import or load bulk data, there will be a lot of undo data generated, it's beyond normal expectation. So you may disable retention guarantee before import or bulk loading, just temporarily.
Now, let's check the retention type first.
SQL> SELECT retention FROM dba_tablespaces WHERE tablespace_name='UNDOTBS1';
If it's GUARANTEE, please change it to NOGUARANTEE.
SQL> ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE;
For more clear explanation about the relationship betweenUNDO_RETENTION and RETENTION GUARANTEE, you may check this post: UNDO_RETENTION, How and Why
It's not always a good idea, but it will work when your undo retention is currently a large value. For example, 21,600 seconds in this case. You can decrease the value to a smaller value, say 3600 seconds to age out undo data more quickly. Eventually, it will reduce space pressure that solves ORA-30036.
SQL> ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
We have to check the tablespace type first.
SQL> SELECT bigfile FROM v$tablespace WHERE name = 'UNDOTBS1';
If the tablespace is created with bigfile, we can enable AUTOEXTEND on tablespace-level.
SQL> ALTER TABLESPACE UNDOTBS1 AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
Otherwise, you can do it on datafile-level.
SQL> ALTER DATABASE DATAFILE '/path_to_datafile/datafilename.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;
As you can see, the whole concepts of UNDO is pretty complicated and challenging for DBA to understand. That's why we use this long post to explain ORA-30036, an UNDO related error.