ORA-00837 RAC正常，节点2宕机故障处理

一、RAC节点2宕机：

\[oracle@Node1 ~\]$ srvctl status database -d ycdb  
Instance ycdb1 is running on node node1  
Instance ycdb2 is not running on node node2  
Instance ycdb3 is running on node node3


复制

二、节点2查看告警

\[oracle@Node2 trace\]$ tail -8000 alert\_ycdb2.log |more

复制

（一）超过最大进程数300

Mon Mar 07 10:04:25 2022
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Mon Mar 07 10:04:25 2022
Errors in file /u01/app/oracle/diag/rdbms/ycdb/ycdb2/trace/ycdb2_arc3_17792.trc:
ORA-19809: 超出了恢复文件数的限制
ORA-19804: 无法回收 27262976 字节磁盘空间 (从 214748364800 限制中)
ARC3: Error 19809 Creating archive log file to ‘+RECOC1’
Mon Mar 07 10:04:25 2022
Errors in file /u01/app/oracle/diag/rdbms/ycdb/ycdb2/trace/ycdb2_arc0_17786.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 214748364800 字节) 已使用 100.00%, 尚有 0 字节可用。
Mon Mar 07 10:04:25 2022

（二）有abort记录

Wed Mar 30 12:45:41 2022  
kkjcre1p: unable to spawn jobq slave process  
Wed Mar 30 12:45:41 2022  
Errors in file /u01/app/oracle/diag/rdbms/ycdb/ycdb2/trace/ycdb2\_cjq0\_17855.trc:  
Process J000 died, see its trace file  
Wed Mar 30 12:45:43 2022  
kkjcre1p: unable to spawn jobq slave process  
Wed Mar 30 12:45:43 2022  
Errors in file /u01/app/oracle/diag/rdbms/ycdb/ycdb2/trace/ycdb2\_cjq0\_17855.trc:  
Wed Mar 30 12:45:45 2022  
Shutting down instance (abort)  
License high water mark = 224  
Wed Mar 30 12:45:45 2022  
USER (ospid: 25229): terminating the instance  
Wed Mar 30 12:45:50 2022  
Instance terminated by USER, pid = 25229  
Wed Mar 30 12:45:51 2022  
Instance shutdown complete

复制

三、尝试重启报错

SQL> startup;
ORA-00837: Specified value of MEMORY\_TARGET greater than MEMORY\_MAX\_TARGET

复制

四、查看正常节点信息

SQL> show parameter process;  
  
NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
aq\_tm\_processes integer 1  
cell\_offload\_processing boolean TRUE  
db\_writer\_processes integer 1  
gcs\_server\_processes integer 2  
global\_txn\_processes integer 1  
job\_queue\_processes integer 1000  
log\_archive\_max\_processes integer 4  
processes integer 300  
processor\_group\_name string  
SQL> select count(\*) from v$session;  
  
COUNT(\*)  
\----------  
64

复制

SQL> show parameter memory\_max\_target;  
  
NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
memory\_max\_target big integer 25G  
SQL> show parameter memory\_target;  
  
NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
memory\_target big integer 25G  

复制

SQL> show parameter sga  
  
NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
lock\_sga boolean FALSE  
pre\_page\_sga boolean TRUE  
sga\_max\_size big integer 25G  
sga\_target big integer 0  
unified\_audit\_sga\_queue\_size integer 1048576  
SQL> show parameter pga  
  
NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
pga\_aggregate\_limit big integer 19200M  
pga\_aggregate\_target big integer 0  
SQL> exit
复制

五、导出pfile，看下spfile参数  ，参数和内存不一致

MEMORY\_TARGET 大于 MEMORY\_MAX\_TARGET 

SQL> create pfile='/home/oracle/p2.ora' from memory;  
  
??????  
  
SQL> exit

复制

六、修改成内存参数后用pfile起节点2，证明参数设置正确

\[oracle@Node2 tmp\]$ sqlplus / as sysdba  
  
SQL\*Plus: Release 12.1.0.2.0 Production on Mon Aug 29 19:32:04 2022  
  
Copyright (c) 1982, 2014, Oracle. All rights reserved.  
  
Connected to an idle instance.  
  
SQL> startup nomount pfile='/tmp/pfile\_ycdb1.ora';  
ORACLE instance started.  
  
Total System Global Area 2.6844E+10 bytes  
Fixed Size 2939560 bytes  
Variable Size 1.2616E+10 bytes  
Database Buffers 1.4160E+10 bytes  
Redo Buffers 64167936 bytes  
SQL> select status from v$instance;  
  
STATUS  
\------------  
STARTED  
  
SQL> 

复制

七、节点1修改参数，节点2正常startup，拉起正常，结论：有人参数修改错后，节点2经不起重启后，其他节点没动，问题一直遗留到最近发现

alter system set memory\_max\_size=25G scope=spfile;  
alter system set memory\_target=25G scope=spfile;  
alter system set sga\_max\_size=25G scope=spfile;

复制