ORA-600 2131故障处理---惜分飞

数据库启动报ORA-600 2131错误,查看alert日志发现是在mount过程报错

Fri May 17 20:58:28 2024 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Initial number of CPU is 16 Number of processor cores in the system is 8 Number of processor sockets in the system is 1 Picked latch-free SCN scheme 3 Autotune of undo retention is turned on. IMODE=BR ILAT =249 LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options. Windows NT Version V6.2  CPU                 : 16 - type 8664, 8 Physical Cores Process Affinity    : 0x0x0000000000000000 Memory (Avail/Total): Ph:93799M/97925M, Ph+PgF:78891M/112261M Using parameter settings in server-side spfile E:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEXFF.ORA System parameters with non-default values:   processes                = 1500   sessions                 = 2272   nls_language             = "SIMPLIFIED CHINESE"   nls_territory            = "CHINA"   sga_target               = 29440M   control_files            = "E:\ORADATA\xff\CONTROL01.CTL"   db_block_size            = 8192   compatible               = "11.2.0.4.0"   log_archive_dest_1       = "LOCATION=e:\app\archivelog\"   log_archive_format       = "ARC%S_%R.%T"   undo_tablespace          = "UNDOTBS2"   sec_case_sensitive_logon = FALSE   remote_login_passwordfile= "EXCLUSIVE"   db_domain                = ""   dispatchers              = "(PROTOCOL=TCP) (SERVICE=xffXDB)"   audit_file_dest          = "E:\APP\ADMINISTRATOR\ADMIN\xff\ADUMP"   audit_trail              = "NONE"   db_name                  = "xff"   open_cursors             = 300   pga_aggregate_target     = 9792M   diagnostic_dest          = "E:\APP\ADMINISTRATOR" Fri May 17 20:58:29 2024 PMON started with pid=2, OS id=6696 Fri May 17 20:58:29 2024 PSP0 started with pid=3, OS id=2424 Fri May 17 20:58:30 2024 VKTM started with pid=4, OS id=5472 at elevated priority VKTM running at (10)millisec precision with DBRM quantum (100)ms Fri May 17 20:58:30 2024 GEN0 started with pid=5, OS id=5764 Fri May 17 20:58:30 2024 DIAG started with pid=6, OS id=372 Fri May 17 20:58:30 2024 DBRM started with pid=7, OS id=2992 Fri May 17 20:58:30 2024 DIA0 started with pid=8, OS id=4960 Fri May 17 20:58:30 2024 MMAN started with pid=9, OS id=6036 Fri May 17 20:58:30 2024 DBW0 started with pid=10, OS id=4724 Fri May 17 20:58:30 2024 DBW1 started with pid=11, OS id=2652 Fri May 17 20:58:30 2024 LGWR started with pid=12, OS id=5320 Fri May 17 20:58:30 2024 CKPT started with pid=13, OS id=5732 Fri May 17 20:58:30 2024 SMON started with pid=14, OS id=936 Fri May 17 20:58:30 2024 RECO started with pid=15, OS id=2192 Fri May 17 20:58:30 2024 MMON started with pid=16, OS id=5576 Fri May 17 20:58:30 2024 MMNL started with pid=17, OS id=5748 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... starting up 1 shared server(s) ... ORACLE_BASE from environment = E:\app\Administrator Fri May 17 20:58:31 2024 ALTER DATABASE   MOUNT Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_5452.trc  (incident=403399): ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], [] Incident details in: E:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_403399\xff_ora_5452_i403399.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. ORA-600 signalled during: ALTER DATABASE   MOUNT...

这个错误是由于controlfile损坏导致,有这个库以前部署过rman备份，解决起来比较简单，使用rman还原控制文件,并尝试recover

RMAN> restore controlfile from 'E:\rmanback\rmanfile\CTL_20240517_A62R067K_1_1.RMAN'; 启动 restore 于 17-5月 -24 使用通道 ORA_DISK_1 通道 ORA_DISK_1: 正在还原控制文件 通道 ORA_DISK_1: 还原完成, 用时: 00:00:01 输出文件名=E:\ORADATA\XFF\CONTROL01.CTL 完成 restore 于 17-5月 -24 RMAN> RMAN> RMAN> alter database mount; 数据库已装载 释放的通道: ORA_DISK_1 RMAN> recover database; 启动 recover 于 17-5月 -24 分配的通道: ORA_DISK_1 通道 ORA_DISK_1: SID=996 设备类型=DISK 正在开始介质的恢复 线程 1 序列 4100 的归档日志已作为文件 E:\ORADATA\XFF\REDO02.LOG 存在于磁盘上 线程 1 序列 4101 的归档日志已作为文件 E:\ORADATA\XFF\REDO03.LOG 存在于磁盘上 线程 1 序列 4102 的归档日志已作为文件 E:\ORADATA\XFF\REDO01.LOG 存在于磁盘上 归档日志文件名=E:\APP\ARCHIVELOG\ARC0000004025_1165094245.0001 线程=1 序列=4025 归档日志文件名=E:\APP\ARCHIVELOG\ARC0000004026_1165094245.0001 线程=1 序列=4026 ………… 归档日志文件名=E:\APP\ARCHIVELOG\ARC0000004099_1165094245.0001 线程=1 序列=4099 归档日志文件名=E:\ORADATA\XFF\REDO02.LOG 线程=1 序列=4100 归档日志文件名=E:\ORADATA\XFF\REDO03.LOG 线程=1 序列=4101 归档日志文件名=E:\ORADATA\XFF\REDO01.LOG 线程=1 序列=4102 介质恢复完成, 用时: 00:00:22 完成 recover 于 17-5月 -24 RMAN> exit 恢复管理器完成。 E:\oradata\XFF>

这种恢复情况下,如果现在要打开库,需要resetlogs方式,考虑通过创建ctl直接打开（不想用resetlogs）

SQL> shutdown immediate; ORA-01109: 数据库未打开 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> startup nomount; ORACLE 例程已经启动。 Total System Global Area 3.0732E+10 bytes Fixed Size                  2296264 bytes Variable Size            3825206840 bytes Database Buffers         2.6844E+10 bytes Redo Buffers               61206528 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  ARCHIVELOG   2      MAXLOGFILES 16   3      MAXLOGMEMBERS 3   4      MAXDATAFILES 100   5      MAXINSTANCES 8   6      MAXLOGHISTORY 876   7  LOGFILE   8    GROUP 1 'E:\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,   9    GROUP 2 'E:\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,  10    GROUP 3 'E:\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512  11  -- STANDBY LOGFILE  12  DATAFILE  13    'E:\ORADATA\XFF\SYSTEM01.DBF',  14    'E:\ORADATA\XFF\SYSAUX01.DBF',  15    'E:\ORADATA\XFF\USERS01.DBF',  16    'E:\ORADATA\XFF\XFF_DATA01.DBF',  17    'E:\ORADATA\XFF\XFF_INDEX01.DBF',  18    'E:\ORADATA\XFF\UNDOTBS2.DBF'  19  CHARACTER SET ZHS16GBK  20  ; 控制文件已创建。 SQL> recover database; 完成介质恢复。 SQL> alter database open; 数据库已更改。 SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORADATA\XFF\TEMP01.DBF' REUSE; 表空间已更改。

至此本次恢复晚上,由于arch,redo和数据文件没有损坏,恢复非常完美，参考以前类似说明：ORA-600 2131故障说明