启动数据库报错ORA-09968，且出现pga_aggregate_limit value is too high的告警

denglj

问题描述：启动数据库报错ORA-09968，且出现pga_aggregate_limitvalue is too high的告警，如下所示：

系统：centos7.9 64位

数据库：oracle12.2.0.1 64位

环境：单实例 + noncdb+ 物理内存4g

1、异常告警

启动到mount出现ORA-01102。

idle15-OCT-22> startup mount

ORACLEinstance started.

TotalSystem Global Area 2533359616 bytes

FixedSize                  8623880 bytes

VariableSize             687868152 bytes

DatabaseBuffers         1828716544 bytes

RedoBuffers                8151040 bytes

ORA-01102:cannot mount database in EXCLUSIVE mode

trace文件显示如下.

Startingbackground process LGWR

2022-10-15T00:06:02.787145+08:00

WARNING:pga_aggregate_limit value is too high for the

amount ofphysical memory on the system

  PGA_AGGREGATE_LIMIT is 2048 MB

  PGA_AGGREGATE_TARGET is 604 MB.

  physical memory size is 3770 MB

  limit based on physical memory and SGA usageis 977 MB

  SGA_TARGET is 2416 MB

2022-10-15T00:06:06.111674+08:00

Usingdefault pga_aggregate_limit of 2048 MB

2022-10-15T00:06:07.304102+08:00

LGWRstarted with pid=20, OS id=9621

2022-10-15T00:06:07.315238+08:00

Startingbackground process CKPT

2022-10-15T00:06:07.472158+08:00

CKPTstarted with pid=8, OS id=9640

Startingbackground process SMON

2022-10-15T00:06:07.525671+08:00

LGWRslave LG00 created with pid=21, OS pid=9644

2022-10-15T00:06:07.572361+08:00

SMONstarted with pid=22, OS id=9646

LGWRslave LG01 created with pid=23, OS pid=9650

Startingbackground process SMCO

Startingbackground process RECO

2022-10-15T00:06:07.620640+08:00

SMCOstarted with pid=24, OS id=9653

2022-10-15T00:06:07.646708+08:00

RECOstarted with pid=25, OS id=9655

Startingbackground process LREG

2022-10-15T00:06:07.676753+08:00

LREGstarted with pid=27, OS id=9659

Startingbackground process PXMN

2022-10-15T00:06:07.724452+08:00

PXMNstarted with pid=29, OS id=9663

Startingbackground process FENC

2022-10-15T00:06:07.743038+08:00

FENCstarted with pid=30, OS id=9665

Startingbackground process MMON

2022-10-15T00:06:07.773322+08:00

MMONstarted with pid=31, OS id=9667

Startingbackground process MMNL

2022-10-15T00:06:07.792956+08:00

MMNLstarted with pid=32, OS id=9669

2022-10-15T00:06:07.813840+08:00

startingup 1 dispatcher(s) for network address'(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

startingup 1 shared server(s) ...

Startingbackground process TMON

2022-10-15T00:06:07.896610+08:00

TMONstarted with pid=34, OS id=9675

ORACLE_BASEfrom environment = /u01//app/oracle

2022-10-15T00:06:08.422278+08:00

ALTERDATABASE   MOUNT

2022-10-15T00:06:08.433345+08:00

sculkget:failed to lock /u01//app/oracle/product/12.2.0/db/dbs/lkORCL exclusive

sculkget:lock held by PID: 8221

2022-10-15T00:06:08.439347+08:00

ORA-09968:unable to lock file

Linux-x86_64Error: 11: Resource temporarily unavailable

Additionalinformation: 8221

ORA-1102signalled during: ALTER DATABASE  MOUNT...

2022-10-15T01:05:05.258705+08:00

Resizeoperation completed for file# 3, old size 471040K, new size 481280K

ALTERDATABASE   MOUNT

2022-10-15T10:05:51.341444+08:00

sculkget:failed to lock /u01//app/oracle/product/12.2.0/db/dbs/lkORCL exclusive

sculkget:lock held by PID: 1956

2022-10-15T10:05:51.341763+08:00

ORA-09968:unable to lock file

Linux-x86_64Error: 11: Resource temporarily unavailable

Additionalinformation: 1956

ORA-1102 signalledduring: ALTER DATABASE   MOUNT...

2022-10-15T10:05:52.042139+08:00

Usingdefault pga_aggregate_limit of 2048 MB

2、异常原因

a、对于pga_aggregate_limit告警，原因为物理内存设置过小；

b、对于ORA-09968告警，是由于在startupnomount过程中shm共享内存和共享的信号均未建立，若同时startup可能会启动2套实例.此时便使用lk$oracle_sid文件，先锁住该文件的实例就优先继续后面的启动过程.

3、处理过程

a、调整虚拟机内存为8g.

b、删除lk$ORACLE_SID文件

#cd /u01/app/oracle/product/12.2.0/db/dbs

# rm -rflkORCL

c、调整内存

将数据库启动到mount阶段,调整数据库内存.

idle15-OCT-22> alter system set sga_target=3500m scope=spfile;

Systemaltered.

idle15-OCT-22> alter system set sga_max_size=3500m scope=spfile;

Systemaltered.

idle15-OCT-22> alter system set pga_aggregate_target=2300m scope=spfile;

Systemaltered.

此后重启数据库，无异常发生.