Today's testers call me the past, saying that it is a database, and it will not come.
I have seen the situation in the past and do the following.
SQL> Shutdown Immediate database has been turned off. The database has been uninstalled. Oracle routines have been closed. SQL> StartUporacle routines have been started.
Total System Global Area 135338868 Bytesfixed Size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 BYTESREDO BUFFERS 667648 BYTES Database loaded. ORA-01092: Oracle routine termination. Forced disconnection
I can't see the problem on the surface, I view a Alert_Oracas.log file
SYS auditing is disabledStarting up ORACLE RDBMS Version: 9.2.0.1.0.System parameters with non-default values: processes = 150 timed_statistics = TRUE shared_pool_size = 50331648 large_pool_size = 8388608 java_pool_size = 33554432 control_files = f: / oracle / oradata / oracas / CONTROL01 .CTL, f: /oracle/oradata/oracas/CONTROL02.CTL, f: /oracle/oradata/oracas/CONTROL03.CTL db_block_size = 8192 db_cache_size = 25165824 compatible = 9.2.0.0.0 db_file_multiblock_read_count = 16 fast_start_mttr_target = 300 undo_management = AUTO undo_tablespace = UNDOSTB1 undo_retention = 10800 remote_login_passwordfile = EXCLUSIVE db_domain = instance_name = oracas dispatchers = (PROTOCOL = TCP) (SERVICE = oracasXDB) job_queue_processes = 10 hash_join_enabled = TRUE background_dump_dest = f: / oracle / admin / oracas / bdump u ser_dump_dest = f: / oracle / admin / oracas / udump core_dump_dest = f: / oracle / admin / oracas / cdump sort_area_size = 524288 db_name = oracas open_cursors = 300 star_transformation_enabled = FALSE query_rewrite_enabled = FALSE pga_aggregate_target = 19922944 aq_tm_processes = 1PMON started with pid = 2DBW0 Started with pid = 3lgwr started with pid = 4CKPT Started with pid = 5smon started with pid = 6RECO Started with pid = 7cjq0 started with pid = 8qmn0 started with PID =
9MON APR 18 17:30:25 2005Starting Up 1 Shared Server (s) ... Starting Up 1 Dispatcher (s) for network address '(address = (partial = yes) (protocol = tcp))' ... MON APR 18 17:30:26 2005ALTER DATABASE MOUNTMon Apr 18 17:30:30 2005Successful mount of redo thread 1, with mount id 2424210674.Mon Apr 18 17:30:30 2005Database mounted in Exclusive Mode.Completed: ALTER DATABASE MOUNTMon Apr 18 17 : 30: 30 2005 RALTER DATABASE OpenMON APR 18 17:30:32 2005thread 1 Opened At Log Sequence 105 Current Log # 2 SEQ # 105 MEM # 0: f: /oracle/oradata/oracas/redo02.logsuccessful Open of Redo Thread 1. MON APR 18 17:30:32 2005SMON: Enabling Cache Recoverymon Apr 18 17:30:34 2005ERRORS IN FILE F: /Oracle/admin/racas/udump/oracas_ora_3404.trc: ORA-30012: Undo Table Space 'undostb1' does not exist Or type incorrect MON APR 18 17:30:34 2005ERROR 30012 HAPPENED DURING DB OPEN, SHUTTINED DatabaseUSER: TERMINATING INSTANCE DUE TO ERROR 30012MON APR 18 17:30:35 2005ERRORS IN FILE F: / ORACLE / Admin / ORAS / BDUMP / ORACAS_SMON_996.TRC: ORA-30012: UNDO TABLESPACE '' D Oes Not Exist or of WRONG TYPE
MON APR 18 17:30:35 2005ErrorS in file f: /oracle/admin/oracas/bdump/oracas_pmon_3500.trc: ORA-30012: undo tablespace 'does not exist or of wrong type
Instance Terminated by User, PID = 3404ORA-1092 Signalled During: Alter Database Open ...
So I viewed the following information to confirm the problem of undo table space
SQL> CONN SYS / SYS @ ORACAS AS SYSDBA is connected to the free routine. SQL> Startup Mountoracle routines have been launched.
Total System Global Area 135338868 Bytesfixed Size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 BYTESREDO BUFFERS 667648 BYTES Database loaded. SQL> SELECT NAME FROM V $ datafile;
Name ------------------------------------- ---------------------- f: /oracle/oradata/oracas/system01.dbff: /oracle/oradata/oracas/undotbs01.dbff: / Oracle / ORADATA / ORACAS / CWMLITE01.DBFF: /Oracle/oradata/oracas/drsys01.dbff: /oxample01.dbff: / ORACLE/Ordata/Oracas/indx01.dbff: / Oracle / ORADATA / ORACAS / ODM01. DBFF: /oracle/oradata/racas/tools01.dbff: /oracle/oradata/racas/Users01.dbff: /oracle/oradata/racas/xdb01.dbfd: / ORADATA/Or Acassa/tscas11.dbfe: / oraData / ORACAS / TFCAS12 .Dbfd: /oradata/racas/tscas21.dbfe: /oradata/racas/tfcas22.dbfd: /oradata/racas/tscas31.dbfe: /oradata/racas/tfcas32.dbfd: /oradata/racas/tscas41.dbfe: / ORADATA /Oracas/tfcas42.dbfd:/oradata/racas/tscasindx1.dbfe:/oradata/racas/tscasindx2.dbfd:/oradata/oracas/tfcas13.dbfd:/oradata/oracas/tfcas14.dbfd:/oradata/oracas/tfcas23. DBFD: /ORADATA/Oracas/tfcas24.dbfe: /oradata/racas/tscasindx12.dbfe: /oradata/racas/tscasindx13.dbfe: /oradata/racas/tscasindx24.dbfe: /oradata/racas/tscasindx25.dbfc: / snapshot01. DBFC : /TSCAS1.DBF has been selected for 30 lines.
SQL> Show Parameter Undo
Name Type Value --------------------------------- ----------------------- undo_management string autoundo_retention integer 10800undo_suppress_errors boolean falseundo_tablespace string undostb1sql> SELECT NAME from V $ TABLESPACE;
Name ---------------------------- CWMLITEDRSEXAMPLEINDXODMSYSTEMTOOLSUNDOTBS1USERSERSERSXDBTEMPTSCAS1TSCAS2TSCAS3TSCAS4TSCASDX1TSCASINDX2SNAPSHOT_TS
18 lines have been selected. At that time, I didn't see the problem, I thought it was the undo file corrupted and decided to rebuild.
Note: Later, it was only found that the operation here was a detour to solve the problem.
Since there is no way to recreate the undo table space, you decided to start using the system default undo table space, and then rebuild UNDO
Table space, the specific operation is as follows: SQL> CREATE PFILE from SPFILE;
The file has been created.
Modify the Pfile file
# *. undo_management = 'auto' # *. undo_tablespace = 'undostb1'undo_management = manualundo_tablespace =' system '
Turn off the database and remove the spfileORACAS.ORA file from the directory f: / oracle / ORA92 / DATABASE
Restart
SQL> Shutdown Immediateora-01109: Database is not open
The database has been uninstalled. Oracle routines have been closed. SQL> Startup Mountoracle routines have been launched.
Total System Global Area 135338868 Bytesfixed Size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 BYTESREDO BUFFERS 667648 BYTES Database loaded. SQL> ALTABASE OPEN;
The database has been changed.
View Table Space Information
SQL> Show Parameter Undo
Name Type Value --------------------------------- ----------------------------- undo_management string manualundo_retention integer 10800undo_suppress_errors boolean falseundo_tablespace string systemsql> Select Name from V $ TABLESPACE;
Name ---------------------------- CWMLITEDRSEXAMPLEINDXODMSYSTEMTOOLSUNDOTBS1USERSERSERSXDBTEMPTSCAS1TSCAS2TSCAS3TSCAS4TSCASDX1TSCASINDX2SNAPSHOT_TS
And open the graphical interface to view, this time, the name of the undo table space is undotbs1
In fact, I have already discovered it. Everyone analyzes the problem later, it is necessary to pay too fast, to analyze it. . .
So the following operations change the name of the undo table space
SQL> CREATE SPFILE from Pfile;
The file has been created.
SQL> Shutdown Immediate database has been turned off. The database has been uninstalled. Oracle routines have been closed. SQL> StartUporacle routines have been started.
Total System Global Area 135338868 Bytesfixed Size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 BYTESREDO BUFFERS 667648 BYTES Database loaded. The database has been opened. SQL> Show Parameter Undo
Name Type Value --------------------------------- ------------------------------ undo_management string MANUALundo_retention integer 10800undo_suppress_errors boolean FALSEundo_tablespace string systemSQL> alter database set undo_management = auto; alter database set undo_management = auto * ERROR at line 1: ORA-02231: missing or invalid ALTER DATABASE options SQL> alter database set undo_management = auto scope = spfile; alter database set undo_management = auto scope = spfile * ERROR at line 1: ORA -02231: ALTABASE option lacking or invalid
SQL> ALTER SESTEM SET undo_management = auto scope = spfile;
The system has changed.
SQL> ALTER system set undo_tablespace = 'undotbs1' scope = SPFILE;
Restart verification:
SQL> Shutdown Immediate database has been turned off. The database has been uninstalled. Oracle routines have been closed. SQL> StartUporacle routines have been started.
Total System Global Area 135338868 Bytesfixed Size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 BYTESREDO BUFFERS 667648 BYTES Database loaded. The database has been opened. SQL> CREATE FPILE from SPFILE; CREATE FPILE from SPFILE * Error Located in Chapter 1: ORA-00901: Invalid CREATE command
SQL> CREATE PFILE from SPFILE;
The file has been created.
SQL> Show Parameter Undo
Name Type Value --------------------------------- ------------------------------ UNDO_MANAGEMENT STRING AutOUndo_retention integer 10800undo_suppress_errors boolean falseundo_tablespace string undotbs1
It is already good
Lessons Summary: In fact, as long as the V $ TABLESPACE can find the name of the undo table space, it can be solved soon. But I can use this method to use the situation of the undo table space file Corrupt :)
