sqlplus>ALTER DATABASE datafile 2 ONLINE; ALTER DATABASE datafile 2 ONLINE * ERROR on line 1th:ORA-01190: Control file or data file 2 from the last ResetlogsORA-01110: Data file 2: ' E:\ORACLE9I\ORA9I\UNDOTBS01. DBF 'Next, because the checkpoint_change# in V$datafile is still larger than the change# in V$recover_file, it is decided to use ADJUST_SCN to adjust the SCN. First you need to set up _allow_resetlogs_ Corruption parameter, otherwise it will not succeed (experiment) sqlplus>ALTER SYSTEM SET "_allow_resetlogs_corruption" =true scope=spfile;
The system has changed.
sqlplus>SHUTDOWN IMMEDIATE The database is closed. The database has been uninstalled. The ORACLE routine has been closed. 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 is loaded. sqlplus>ALTER DATABASE OPEN; The database has changed. sqlplus>ALTER SESSION SET EVENTS ' IMMEDIATE TRACE NAME adjust_scn level 1 ';The session has changed. sqlplus>SHUTDOWN IMMEDIATE The database is closed. The database has been uninstalled. The ORACLE routine has been closed. 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 is 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 10414784161 2 E:\ORACLE9I\ORA9I\UNDOTBS01. DBF OFFLINE 03 3 E:\ORACLE9I\ORA9I\DRSYS01. DBF OFFLINE 04 4 E:\ORACLE9I\ORA9I\INDX01. DBF OFFLINE 05 5 E:\ORACLE9I\ORA9I\TOOLS01. DBF OFFLINE 06 6 E:\ORACLE9I\ORA9I\USERS01. DBF OFFLINE 07 7 E:\ORACLE9I\ORA9I\XDB01. DBF OFFLINE 08 8 E:\ORACLE9I\ORA9I\OEM_REPOSITORY. DBF OFFLINE 09 E:\ORACLE9I\ORADATA\GAXZTEMP. DB OFFLINE 0E:\ORACLE9I\ORADATA\GAXZUSR. DB OFFLINE 0E:\ORACLE9I\ORADATA\GAXZRBS. DB OFFLINE 0ts# file# NAME STATUS checkpoint_change#---- ----- ---------------------------------------- ------- ------------------ E:\ORACLE9I\ORADATA\GAXZWEB. DB OFFLINE 012 rows have been selected. sqlplus>SELECT * from V$recover_file; file# ONLINE online_ ERROR change# time ----- ------- ------- -------------------- --------------- ---------- 2 OFFLINE OFFLINE wrong FILE CREATE 37667 1 August-December -043 OFFLINE OFFLINE wrong FILE CREATE 37667 1 August-December -044 OFFLINE OFFLINE wrong FILE CREATE 37667 1 August-December -045 OFFLINE OFFLINE wrong FILE CREATE 37667 1 August-December -046 OFFLINE OFFLINE wrong FILE CREATE 37667 1 August-December -047 OFFLINE OFFLINE wrong FILE CREATE 37667 1 August-December -048 OFFLINE OFFLINE wrong FILE CREATE 37667 1 August-December -049 OFFLINE OFFLINE wrong FILE CREATE 37667 1 August-December -04OFFLINE OFFLINE wrong FILE CREATE 37667 1 August-December -04OFFLINE OFFLINE wrong FILE CREATE 37667 1 August-December -04OFFLINE OFFLINE wrong FILE CREATE 37667 1 August-December -0411 rows have been selected.
sqlplus>RECOVER UNTIL CANCEL; Complete Media recovery. This step is very important, although do not do this operation can open the database, but we want to use Resetlogs to open the database, or else will still bring other data files online whenwill still report 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 changed. Open the database. sqlplus>ALTER DATABASE OPEN resetlogs; The database has changed. View V$datafile, the file status is already online. ts# file# NAME STATUS checkpoint_change#--- ----- ---------------------------------------- ------- ------------------ 0 1 E:\ORACLE9I\ORA9I\SYSTEM01. DBF SYSTEM 10414784181 2 E:\ORACLE9I\ORA9I\UNDOTBS01. DBF ONLINE 10414784183 3 E:\ORACLE9I\ORA9I\DRSYS01. DBF ONLINE 10414784184 4 E:\ORACLE9I\ORA9I\INDX01. DBF ONLINE 10414784185 5 E:\ORACLE9I\ORA9I\TOOLS01. DBF ONLINE 10414784186 6 E:\ORACLE9I\ORA9I\USERS01. DBF ONLINE 10414784187 7 E:\ORACLE9I\ORA9I\XDB01. DBF ONLINE 10414784188 8 E:\ORACLE9I\ORA9I\OEM_REPOSITORY. DBF ONLINE 10414784189 E:\ORACLE9I\ORADATA\GAXZTEMP. DB ONLINE 1041478418E:\ORACLE9I\ORADATA\GAXZUSR. DB ONLINE 1041478418E:\ORACLE9I\ORADATA\GAXZRBS. DB ONLINE 1041478418
ts# file# NAME STATUS checkpoint_change# --- ----- ---------------------------------------- ------- ------------------ E:\ORACLE9I\ORADATA\GAXZWEB. DB ONLINE 1041478418 Now that the database has been restored, the next task is simple: Retrieve the temporary tablespace file: sqlplus>ALTER tablespace TEMP ADD tempfile ' E:\ORACLE9i\ORA9I\TEMP01. DBF ' reuse; table space has changed. Change the Undo management mode to Automaticsqlplus>ALTER SYSTEM SET undo_management=auto scope=spfile; The system has changed. sqlplus>ALTER SYSTEM SET undo_tablespace=undotbs scope=spfile; The system has changed. Export the data with exp and rebuild the database.
Summary: Just finished the time, a little can't believe unexpectedly solved. Because of this error caused by the system table space, I have always thought that ORA-01189 can only be solved by Dul. On the internet also can not find the real solution of the information, is generally resolved ORA-01190. The difference between the two problems is that if you create a control file without generating 01189 (with the resetlogs option) then you do not need to remove the other data files, and open the database as long as the setting of _allow_resetlogs_corruption, Also do not need to ADJUST_SCN to modify the change#. Then he made several experiments on his machine:1 shut down the database;2 Back up the system table space;3 Open the database;4 switching logs;5 off the database;6 replace the old system table space. The same problem is simulated. and solved it in the same way.
Solutions for ora-01190 and ora-01110
