Solution to ora-01190 ora-01110

SQLPLUS> alter database datafile 2 ONLINE;
Alter database datafile 2 ONLINE
*
ERROR is located in row 1st:
ORA-01190: control file or data file 2 comes before the last RESETLOGS
ORA-01110: data file 2: 'e: \ Oracle9I \ ORA9I \ undotbs01.dbf'
Next, because the CHECKPOINT_CHANGE # In V $ DATAFILE is still greater than the CHANGE # In V $ RECOVER_FILE, we decided to use ADJUST_SCN to adjust the SCN.
First, you must set the _ allow_resetlogs_upload uption parameter. Otherwise, the operation fails)
SQLPLUS> alter system set "_ allow_resetlogs_uption" = true scope = SPFILE;
The system has been changed.
SQLPLUS> SHUTDOWN IMMEDIATE
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
SQLPLUS> startup mount;
The ORACLE routine has been started.
Total System Global Area 135339940
Bytes
Fixed Size 454564
Bytes
Variable Size 109051904
Bytes
Database Buffers 25165824
Bytes
Redo Buffers' 667648
Bytes
The database has been loaded.
SQLPLUS> alter database open;
The database has been changed.
SQLPLUS> alter session set events 'immediate trace name ADJUST_SCN LEVEL 1 ′;
The session has been changed.
SQLPLUS> SHUTDOWN IMMEDIATE
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
SQLPLUS> startup mount;
The ORACLE routine has been started.
Total System Global Area 135339940
Bytes
Fixed Size 454564
Bytes
Variable Size 109051904
Bytes
Database Buffers 25165824
Bytes
Redo Buffers' 667648
Bytes
The database has been loaded.
SQLPLUS> select ts #, FILE #, NAME, STATUS, CHECKPOINT_CHANGE # from v $ DATAFILE;
TS # FILE # name status CHECKPOINT_CHANGE #
---------------------------
0 1 E: \ ORACLE9I \ ORA9I \ SYSTEM01.DBF SYSTEM 1041478416
1 2 E: \ ORACLE9I \ ORA9I \ UNDOTBS01.DBF OFFLINE 0
3 3 E: \ ORACLE9I \ ORA9I \ DRSYS01.DBF OFFLINE 0
4 4 E: \ ORACLE9I \ ORA9I \ INDX01.DBF OFFLINE 0
5 5 E: \ ORACLE9I \ ORA9I \ TOOLS01.DBF OFFLINE 0
6 6 E: \ ORACLE9I \ ORA9I \ USERS01.DBF OFFLINE 0
7 7 E: \ ORACLE9I \ ORA9I \ XDB01.DBF OFFLINE 0
8 8 E: \ ORACLE9I \ ORA9I \ OEM_REPOSITORY.DBF OFFLINE 0
13 9 E: \ ORACLE9I \ ORADATA \ GAXZTEMP. db offline 0
14 10 E: \ ORACLE9I \ ORADATA \ GAXZUSR. db offline 0
15 11 E: \ ORACLE9I \ ORADATA \ GAXZRBS. db offline 0
TS # FILE # name status CHECKPOINT_CHANGE #
---------------------------
16 12 E: \ ORACLE9I \ ORADATA \ GAXZWEB. db offline 0
12 rows have been selected.
SQLPLUS> SELECT * from v $ RECOVER_FILE;
FILE # ONLINE _ error change # TIME
------------------------
2 offline wrong file create 37667-12-04
3 offline wrong file create 37667-12-04
4 offline wrong file create 37667-12-04
5 offline wrong file create 37667-12-04
6 offline wrong file create 37667-12-04
7 offline wrong file create 37667-12-04
8 offline wrong file create 37667-12-04
9 offline wrong file create 37667-12-04
10 offline wrong file create 37667-12-04
11 offline wrong file create 37667-12-04
12 offline wrong file create 37667-12-04
11 rows have been selected.
SQLPLUS> recover until cancel;
Media recovery is completed.
This step is very important. Although you can open the database without doing this operation, we need to use RESETLOGS to open the database. Otherwise, when other data files are online
Still reports ORA-01189.
Then, bring the data file status online.
SQLPLUS> alter database datafile 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 ONLINE;
The database has been changed.
Open the database.
SQLPLUS> alter database open resetlogs;
The database has been changed.
View V $ DATAFILE. The file status is already ONLINE.
TS # FILE # name status CHECKPOINT_CHANGE #
--------------------------
0 1 E: \ ORACLE9I \ ORA9I \ SYSTEM01.DBF SYSTEM 1041478418
1 2 E: \ ORACLE9I \ ORA9I \ UNDOTBS01.DBF ONLINE 1041478418
3 3 E: \ ORACLE9I \ ORA9I \ DRSYS01.DBF ONLINE 1041478418
4 4 E: \ ORACLE9I \ ORA9I \ INDX01.DBF ONLINE 1041478418
5 5 E: \ ORACLE9I \ ORA9I \ TOOLS01.DBF ONLINE 1041478418
6 6 E: \ ORACLE9I \ ORA9I \ USERS01.DBF ONLINE 1041478418
7 7 E: \ ORACLE9I \ ORA9I \ XDB01.DBF ONLINE 1041478418
8 8 E: \ ORACLE9I \ ORA9I \ OEM_REPOSITORY.DBF ONLINE 1041478418
13 9 E: \ ORACLE9I \ ORADATA \ GAXZTEMP. db online 1041478418
14 10 E: \ ORACLE9I \ ORADATA \ GAXZUSR. db online 1041478418
15 11 E: \ ORACLE9I \ ORADATA \ GAXZRBS. db online 1041478418
TS # FILE # name status CHECKPOINT_CHANGE #
--------------------------
16 12 E: \ ORACLE9I \ ORADATA \ GAXZWEB. db online 1041478418
So far, the database has been restored, and the following work is simple:
Retrieve temporary tablespace files:
SQLPLUS> alter tablespace temp add tempfile 'e: \ ORACLE9i \ ORA9I \ temp01.dbf' REUSE;
The tablespace has been changed.
Change the UNDO management mode to automatic
SQLPLUS> alter system set UNDO_MANAGEMENT = auto scope = SPFILE;
The system has been changed.
SQLPLUS> alter system set UNDO_TABLESPACE = undotbs scope = SPFILE;
The system has been changed.
Use EXP to export data and recreate the database.
Conclusion: at the end of the solution, I couldn't believe it was actually solved. Because of this ORA-01189 error caused by the SYSTEM tablespace, I always thought it was only possible to solve it through DUL.
On the Internet also can not find the true solution of the information, usually solve the ORA-01190. The difference between the two problems is that if no 01189 (the resetlogs option is used) is generated during the creation of the control file, no other data files need to be removed during the creation, in addition, you only need to set _ allow_resetlogs_uption when opening the database, and do not need adjust_scn to modify change #. So I did several experiments on my machine:
1. Shut down the database;
2. Back up the SYSTEM tablespace;
3. Open the database;
4. Switch logs;
5. Shut down the database;
6. Replace the old SYSTEM tablespace.
The same problem was simulated. Then solve the problem in the same way.