ORA-01552: non-system tablespace cannot use system rollback segments-Problem Solving

Problem description

In the Oracle RAC environment, the Undo tablespace used by an instance is faulty. After being adjusted by the on-site implementers, the instance can be started, but the write data service cannot be provided, but the query service can only be provided.

The management of the table space corresponding to the instance has been changed to manual, when writing data (creating a table or inserting data) reported a ORA-01552 error.

Problem Analysis

After the problem is solved, the idea should be clearer. Oracle Undo is used in DDL and DML operations. Because the Undo method corresponding to the instance is manual, automatic expansion of space cannot be realized. When the Undo space is full, more space cannot be allocated. This is the problem.

Problem Solving

There are two ways to solve this problem: first, restore through RMAN. I guess the root cause of the problem is that the Undo data file is corrupted and it should be recovered through RMAN, due to my knowledge, I am not familiar with rman, and I have no time to go about it, so I am not using this method.

Second, you can create an undo tablespace to modify the default undo space corresponding to the instance. This method should be relatively easy to operate. This is also my choice. The following describes the implementation process at that time.

1. Create an undo tablespace. Note that the Undo tablespace can be automatically expanded.

2. Modify the Undo tablespace corresponding to the instance.

Now let's look back at this problem. It is estimated that only one command can "alter system set undo_tablespace = utbs1 scope = spfile SID = 'rac1';", because no Sid was set at the time, after executing this command, although the command is successfully executed, the Undo tablespace of the instance is still original after the instance is restarted. My analysis is that although the "alter system set undo_tablespace = utbs1 scope = spfile;" command is successfully executed, the spfile does have an additional record *. undo_tablespace = utbs1, but the original record rac1.undo _ tablespace = utbs exists in the spfile. In this case, when the instance is started, rac1.undo _ tablespace = utbs takes effect, therefore, the Undo tablespace of the instance is not modified successfully.

I didn't realize it at the time. the method I chose was to modify the rac1.undo _ tablespace value in pfile for the purpose of modification.

(1) generate pfile through spfile (note that the RAC instances share a single spfile file and all files exist in ASM );

(2) modify the rac1.undo _ tablespace value of pfile to the name of the newly created undo tablespace;

(3) Start the instance through the pfile file.

(4) generate a spfile file through pfile.

(5) use the spfile file to start the database instance

(6) modify the Undo tablespace management mode.

Notes:

(1) There are two ways to generate a spfile: pfile, memory, and memory. The generated spfile can only start one RAC instance, but other instances cannot start, the reason for my analysis is that the spfile generated by memory is a parameter file for a single instance. When two or more instances use the same parameter configuration, problems may occur, such as the instance_number busy problem, an issue with ORA-01105 ORA-01606 with inconsistent parameters. Here, my practice is to directly generate spfile through the pfile file. Note that the spfile must be a path, and it is the path under ASM.

(2) The order of reading parameter files (spfiletrain1.ora, inittrain1.ora) by Oracle's startup command. When Oracle starts with startup, it first looks for the spfilesid. ora file. If this file does not exist, it looks for the initsid. ora file. Since I didn't specify the spfile path when generating the spfile, The spfiletrain1.ora file exists under the default path, which causes me to be confused when I start the instance, after re-creating the spfile path, restart the instance and check that the spfile parameters are not files in ASM. Then, remove the spfiletrain1.ora file from the local directory and restart the instance.

Commands used

The generated environment makes it difficult to obtain the SQL statement at that time. Only images can be provided here.

ORA-01552: non-system tablespace cannot use system rollback segments-Problem Solving