1.問題描述
Oracle伺服器斷電重啟以後無法資料庫無法正常連線，使用sqlplus envision/envision連線報錯。常見的錯誤有以下這些：
- ORA-12518: TNS:listener could not hand off client connection
- ORA-12560: TNS:protocol adapter error
- ORA-01034: ORACLE not available
- ORA-27101: shared memory realm does not exist
- ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
- ORA-00600:[4194]
2.基本處理
上述這些ORA報錯，基本可以歸類為一下幾種：
- 環境變數ORACLE_SID是否為prjdb，如果不是就設定環境變數
- TNSListener服務是否啟動，如果沒有啟動就重新啟動一下。
- prjdb這個oracle服務是否啟動，如果沒有啟動就啟動一下
- 使用dba身份能否連線oralce。
3.檢視日誌確認問題
將上面的這些問題都過一遍，並且都操作一遍以後發現問題還是沒有解決，此時我們可以檢視oracle 的alert log。我的alert log的路徑為：
D:\app\oracle\diag\rdbms\prjdb\prjdb\trace\alert_prjdb.txt
開啟alert log檔案，找到日誌的最後一行，發現行號是1003417，如下圖所示：
接著嘗試重啟oracle服務，
重啟完畢以後再次檢視log檔案，看看日誌有什麼錯誤。當我們找到ora-00600[4194]的時候，就定位到undo表空間檔案損壞這個問題。最後解決方法就是重建undo表空間。
4.問題處理步驟：重建undo表空間
4.1思路：
在INITprjdb.ora這個配置檔案中將undo表空間指向系統表空間system，然後刪除undo表空間，最後在建立undo表空間，最後再在INITprjdb.ora這個配置檔案中將undo表空間指向剛剛建立的undo表空間。
4.2檢視錶空間
select * from v$tablespace;
4.3檢視undo表空間
show parameter undo
4.4管理員身份登陸：
sqlplus / as sysdba
4.5建立pfile
create pfile from spfile;
關閉例項
shutdown immediate
修改INITprjdb.ora引數
undo_management= MANUAL
undo_tablespace= SYSTEM
這個可以參考：http://www.xifenfei.com/2111.html
啟動資料庫
在修改INITprjdb.ora以後，以INITprjdb.ora這個引數檔案中的配置啟動oracle例項
$SQL> startup pfile=D:\app\oracle\product\11.2.0\dbhome_1\database\INITprjdb.ora ORACLE instance started. Total System Global Area 857903104 bytes Fixed Size 1377952 bytes Variable Size 503318880 bytes Database Buffers 348127232 bytes Redo Buffers 5079040 bytes Database mounted. Database opened.
刪除undo表空間
因為重新指定了undo表空間，所以我們可以將undotbs1這個表空間刪除
SQL> drop tablespace undotbs1 including CONTENTS;
重建表空間
CREATE SMALLFILE UNDO TABLESPACE UNDOTBS1 DATAFILE 'D:\app\oracle\oradata\prjdb\UNDOTBS01.DBF' SIZE 200M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 8G;
重新建立pfile
create pfile from spfile;
重啟oracle資料庫
PS:ORA-00600[4193]
Starting up: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options. Using parameter settings in server-side spfile D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEPRJDB.ORA System parameters with non-default values: processes = 150 nls_language = "SIMPLIFIED CHINESE" nls_territory = "CHINA" memory_target = 1232M control_files = "D:\ORACLE\ORADATA\PRJDB\CONTROL01.CTL" control_files = "D:\ORACLE\FLASH_RECOVERY_AREA\PRJDB\CONTROL02.CTL" db_block_size = 8192 compatible = "11.2.0.0.0" db_recovery_file_dest = "d:\oracle\flash_recovery_area" db_recovery_file_dest_size= 3852M undo_tablespace = "UNDOTBS1" remote_login_passwordfile= "EXCLUSIVE" db_domain = "" dispatchers = "(PROTOCOL=TCP) (SERVICE=prjdbXDB)" audit_file_dest = "D:\ORACLE\ADMIN\PRJDB\ADUMP" audit_trail = "DB" db_name = "prjdb" open_cursors = 300 deferred_segment_creation= FALSE diagnostic_dest = "D:\ORACLE" Wed Jun 11 10:43:02 2014 PMON started with pid=2, OS id=3868 Wed Jun 11 10:43:02 2014 VKTM started with pid=3, OS id=2484 at elevated priority VKTM running at (10)millisec precision with DBRM quantum (100)ms Wed Jun 11 10:43:02 2014 GEN0 started with pid=4, OS id=2544 Wed Jun 11 10:43:02 2014 DIAG started with pid=5, OS id=2376 Wed Jun 11 10:43:02 2014 DBRM started with pid=6, OS id=3264 Wed Jun 11 10:43:02 2014 PSP0 started with pid=7, OS id=3936 Wed Jun 11 10:43:02 2014 DIA0 started with pid=8, OS id=3836 Wed Jun 11 10:43:02 2014 MMAN started with pid=9, OS id=2688 Wed Jun 11 10:43:02 2014 DBW0 started with pid=10, OS id=708 Wed Jun 11 10:43:02 2014 LGWR started with pid=11, OS id=3716 Wed Jun 11 10:43:02 2014 CKPT started with pid=12, OS id=492 Wed Jun 11 10:43:02 2014 SMON started with pid=13, OS id=1324 Wed Jun 11 10:43:02 2014 RECO started with pid=14, OS id=1828 Wed Jun 11 10:43:02 2014 MMON started with pid=15, OS id=3672 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... Wed Jun 11 10:43:02 2014 MMNL started with pid=16, OS id=3908 starting up 1 shared server(s) ... ORACLE_BASE from environment = d:\oracle Wed Jun 11 10:43:02 2014 alter database mount exclusive Successful mount of redo thread 1, with mount id 1302693302 Database mounted in Exclusive Mode Lost write protection disabled Completed: alter database mount exclusive alter database open Beginning crash recovery of 1 threads parallel recovery started with 2 processes Started redo scan Completed redo scan read 34 KB redo, 20 data blocks need recovery Started redo application at Thread 1: logseq 4145, block 3 Recovery of Online Redo Log: Thread 1 Group 2 Seq 4145 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO02.LOG Completed redo application of 0.02MB Completed crash recovery at Thread 1: logseq 4145, block 71, scn 176123270 20 data blocks read, 20 data blocks written, 34 redo k-bytes read Wed Jun 11 10:43:14 2014 Thread 1 advanced to log sequence 4146 (thread open) Thread 1 opened at log sequence 4146 Current log# 3 seq# 4146 mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Successful open of redo thread 1 Wed Jun 11 10:43:15 2014 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Wed Jun 11 10:43:15 2014 SMON: enabling cache recovery Successfully onlined Undo Tablespace 2. Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is US7ASCII Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_smon_1324.trc (incident=69779): ORA-00600: ??????, ??: [4194], [], [ Incident details in: d:\oracle\diag\rdbms\prjdb\prjdb\incident\incdir_69779\prjdb_smon_1324_i69779.trc No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Wed Jun 11 10:43:19 2014 Trace dumping is performing id=[cdmp_20140611104319] Doing block recovery for file 3 block 502 Resuming block recovery (PMON) for file 3 block 502 Block recovery from logseq 4146, block 63 to scn 176123517 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Block recovery stopped at EOT rba 4146.73.16 Block recovery completed at rba 4146.73.16, scn 0.176123515 Doing block recovery for file 3 block 240 Resuming block recovery (PMON) for file 3 block 240 Block recovery from logseq 4146, block 63 to scn 176123507 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Block recovery completed at rba 4146.69.16, scn 0.176123509 Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_smon_1324.trc: ORA-01595: ??? (2) ??? (8) ??? ORA-00600: ??????, ??: [4194], [], [ Starting background process QMNC Wed Jun 11 10:43:22 2014 QMNC started with pid=22, OS id=2124 Wed Jun 11 10:43:23 2014 Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_mmon_3672.trc (incident=69795): ORA-00600: ??????, ??: [4193], [], [ Incident details in: d:\oracle\diag\rdbms\prjdb\prjdb\incident\incdir_69795\prjdb_mmon_3672_i69795.trc Completed: alter database open Trace dumping is performing id=[cdmp_20140611104326] Doing block recovery for file 3 block 5380 Resuming block recovery (PMON) for file 3 block 5380 Block recovery from logseq 4146, block 73 to scn 176123528 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Block recovery stopped at EOT rba 4146.77.16 Block recovery completed at rba 4146.77.16, scn 0.176123527 Doing block recovery for file 3 block 272 Resuming block recovery (PMON) for file 3 block 272 Block recovery from logseq 4146, block 73 to scn 176123520 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Block recovery completed at rba 4146.73.16, scn 0.176123521 Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_mmon_3672.trc (incident=69796): ORA-00600: ??????, ??: [4193], [: ?? Incident details in: d:\oracle\diag\rdbms\prjdb\prjdb\incident\incdir_69796\prjdb_mmon_3672_i69796.trc Doing block recovery for file 3 block 5380 Resuming block recovery (PMON) for file 3 block 5380 Block recovery from logseq 4146, block 73 to scn 176123528 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Block recovery completed at rba 4146.77.16, scn 0.176123530 Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_mmon_3672.trc (incident=69797): ORA-00600: ??????, ??: [4193], [], [ Incident details in: d:\oracle\diag\rdbms\prjdb\prjdb\incident\incdir_69797\prjdb_mmon_3672_i69797.trc Wed Jun 11 10:43:33 2014 Trace dumping is performing id=[cdmp_20140611104333] Wed Jun 11 10:43:34 2014 Doing block recovery for file 3 block 5380 Resuming block recovery (PMON) for file 3 block 5380 Block recovery from logseq 4146, block 73 to scn 176123528 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Block recovery completed at rba 4146.77.16, scn 0.176123530 Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_mmon_3672.trc (incident=69798): ORA-00600: ??????, ??: [4193], [], [ Incident details in: d:\oracle\diag\rdbms\prjdb\prjdb\incident\incdir_69798\prjdb_mmon_3672_i69798.trc Trace dumping is performing id=[cdmp_20140611104335] Doing block recovery for file 3 block 5380 Resuming block recovery (PMON) for file 3 block 5380 Block recovery from logseq 4146, block 73 to scn 176123528 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Trace dumping is performing id=[cdmp_20140611104337] Block recovery completed at rba 4146.77.16, scn 0.176123530 Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_mmon_3672.trc (incident=69799): ORA-00600: ??????, ??: [4193], [], [ Incident details in: d:\oracle\diag\rdbms\prjdb\prjdb\incident\incdir_69799\prjdb_mmon_3672_i69799.trc Doing block recovery for file 3 block 5380 Resuming block recovery (PMON) for file 3 block 5380 Block recovery from logseq 4146, block 73 to scn 176123528 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Block recovery completed at rba 4146.77.16, scn 0.176123530 Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_mmon_3672.trc (incident=69800): ORA-00600: ??????, ??: [4193], [], [ Trace dumping is performing id=[cdmp_20140611104339] Doing block recovery for file 3 block 5380 Resuming block recovery (PMON) for file 3 block 5380 Block recovery from logseq 4146, block 73 to scn 176123528 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Block recovery completed at rba 4146.77.16, scn 0.176123530 Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_mmon_3672.trc (incident=69801): ORA-00600: ??????, ??: [4193], [], [ Doing block recovery for file 3 block 5380 Resuming block recovery (PMON) for file 3 block 5380 Block recovery from logseq 4146, block 73 to scn 176123528 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Block recovery completed at rba 4146.77.16, scn 0.176123530 Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_mmon_3672.trc (incident=69802): ORA-00600: ??????, ??: [4193], [], [ Doing block recovery for file 3 block 5380 Resuming block recovery (PMON) for file 3 block 5380 Block recovery from logseq 4146, block 73 to scn 176123528 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Block recovery completed at rba 4146.77.16, scn 0.176123530 Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_mmon_3672.trc (incident=70876): ORA-00600: ??????, ??: [4193], [], [ Doing block recovery for file 3 block 5380 Resuming block recovery (PMON) for file 3 block 5380 Block recovery from logseq 4146, block 73 to scn 176123528 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Block recovery completed at rba 4146.77.16, scn 0.176123530 Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_mmon_3672.trc (incident=70877): ORA-00600: ??????, ??: [4193], [], [ Doing block recovery for file 3 block 5380 Resuming block recovery (PMON) for file 3 block 5380 Block recovery from logseq 4146, block 73 to scn 176123528 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Block recovery completed at rba 4146.77.16, scn 0.176123530 Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_mmon_3672.trc (incident=70878): ORA-00600: ??????, ??: [4193], [], [ Doing block recovery for file 3 block 5380 Resuming block recovery (PMON) for file 3 block 5380 Block recovery from logseq 4146, block 73 to scn 176123528 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Block recovery completed at rba 4146.77.16, scn 0.176123530 Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_mmon_3672.trc (incident=70879): ORA-00600: ??????, ??: [4193], [], [ Wed Jun 11 10:43:46 2014 Doing block recovery for file 3 block 5380 Resuming block recovery (PMON) for file 3 block 5380 Block recovery from logseq 4146, block 73 to scn 176123528 Recovery of Online Redo Log: Thread 1 Group 3 Seq 4146 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\PRJDB\REDO03.LOG Block recovery completed at rba 4146.77.16, scn 0.176123530 Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_pmon_3868.trc (incident=69691): ORA-00600: ??????, ??: [4193], [], [ Errors in file d:\oracle\diag\rdbms\prjdb\prjdb\trace\prjdb_pmon_3868.trc: ORA-00600: ??????, ??: [4193], [], [ PMON (ospid: 3868): terminating the instance due to error 472
從這裡可以看到出現了ORA-600[4194]/[4193]，第一感覺就是undo出現問題。
4193：表示undo和redo不一致（Arg [a] Undo record seq number，Arg [b] Redo record seq number ）；
4194：表示也是undo和redo不一致（Arg [a] Maximum Undo record number in Undo block，Arg [b] Undo record number from Redo block）
所以出現4193錯誤，解決方法跟4194一樣。