تجربه رفع خطای ORA-04031 shared pool در Oracle 19c

در سیستم‌های انبار داده (Data Warehouse) و هوش تجاری، اجرای پکیج‌های سنگین دوره‌ای (مانند پردازش‌های پایان ماه) می‌تواند فشار ناگهانی و شدیدی به منابع دیتابیس وارد کند. در این مقاله، به بررسی یک سناریوی واقعی در دیتابیس اوراکل نسخه 19 می‌پردازیم که در آن اجرای یک پکیج سنگین منجر به بروز خطای ORA-04031 و از کار افتادن Instance شد و در ادامه با خطای ORA-00821 در زمان استارتاپ مواجه شدیم.

شرح مشکل و بررسی اولیه: بروز خطای ORA-04031

دیتابیس در زمان اجرای یک پکیج ماهانه کاملاً فریز شد و در فایل Trace مربوط به پروسس‌های پس‌زمینه (مانند J000) خطای زیر مشاهده گردید:

ORA-04031: unable to allocate 16272 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","ksu:stats_freelist")

بررسی پارامترهای حافظه نشان داد که مدیریت حافظه به صورت کامل به ASMM واگذار شده بود و مقادیر زیر برای Poolها تنظیم شده بود:

• shared_pool_size = 0
• large_pool_size = 0

تحلیل حافظه با vsgaresizeopsوvsga_resize_ops و vsgar​esizeo​psوvsgainfo

برای درک بهتر رفتار حافظه در زمان بروز خطا، خروجی ویوی v$sga_resize_ops را بررسی کردیم. لاگ‌ها نشان دادند که در زمان اوج بار کاری، ASMM سعی کرده است عملیات Shrink را روی Shared Pool انجام دهد، اما با وضعیت ERROR مواجه شده است:

OPER_TYPE | PARAMETER        | INITIAL_SIZE | TARGET_SIZE | START_TIME          | STATUS
------------------------------------------------------------------------------------------
STATIC    | shared_pool_size | 0 MB         | 3840 MB     | 19-MAY-26 09:00:46  | COMPLETE
SHRINK    | shared_pool_size | 3840 MB      | 3072 MB     | 19-MAY-26 09:01:54  | ERROR

همچنین خروجی ویوی v$sgainfo نشان داد که هیچ گرانیول (Granule) آزادی در SGA وجود ندارد و بخش قابل توجهی از حافظه به In-Memory اختصاص یافته است:

NAME                                  BYTES           RES 
------------------------------------- --------------- --- 
...
In-Memory Area                        53687091200     Yes  (معادل ۵۰ گیگابایت)
Free SGA Memory Available             0               
...

از آنجایی که مقدار Free SGA Memory Available برابر با 0 بود، ASMM برای تخصیص حافظه مجبور به جابجایی از Buffer Cache بود که در زمان بار سنگین قادر به انجام آن نبوده و منجر به SHRINK ERROR و سپس ORA-04031 شده است.

یک اصل مهم: همیشه قبل از تغییرات، از SPFILE بکاپ بگیرید!

قبل از اینکه هرگونه تغییری در پارامترهای حافظه دیتابیس (مخصوصاً SGA) ایجاد کنید، بسیار مهم است که از وضعیت فعلی تنظیمات یک بکاپ تهیه کنید. این کار به شما کمک می‌کند در صورت بروز مشکلاتی مانند استارتاپ نشدن دیتابیس، سریعاً به وضعیت قبل برگردید:

CREATE PFILE='/home/oracle/pfile_backup_before_change.ora' FROM SPFILE;

راه‌حل اول: تعیین حداقل حافظه برای Poolها و چالش ORA-04033

برای جلوگیری از این مشکل، نباید سایز Poolهای حیاتی را روی 0 رها کرد. تصمیم گرفتیم حداقل حافظه را برای این Poolها تنظیم کنیم. اما از آنجایی که در بررسی‌های قبلی دیدیم هیچ حافظه آزادی در SGA وجود ندارد (Free SGA Memory Available = 0)، تلاش برای تخصیص داینامیک حافظه با پارامتر SCOPE=BOTH با خطای کمبود حافظه مواجه شد:

sys@dwn(558)> ALTER SYSTEM SET large_pool_size = 4G SCOPE=BOTH;
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

sys@dwn(558)> ALTER SYSTEM SET shared_pool_size = 12G SCOPE=BOTH;
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

دلیل بروز خطای ORA-04033 این است که اوراکل نمی‌تواند در لحظه این حجم از حافظه را از Buffer Cache آزاد کرده و به Poolهای درخواستی اختصاص دهد.

بنابراین، رویکرد صحیح این است که ابتدا از spfile بکاپ گرفته و سپس تغییرات را صرفاً در spfile اعمال کنیم تا در استارتاپ بعدی دیتابیس لحاظ شوند:

sys@dwn(558)> create pfile='/home/oracle/pfile050229.ora' from spfile;
File created.

sys@dwn(558)> ALTER SYSTEM SET large_pool_size = 4G SCOPE=spfile;
System altered.

sys@dwn(558)> ALTER SYSTEM SET shared_pool_size = 12G SCOPE=spfile;
System altered.

چالش دوم: عدم استارتاپ و خطای ORA-00821

پس از اعمال تغییرات، دیتابیس را ری‌استارت کردیم اما دیتابیس در حالت NOMOUNT متوقف شد و خطای زیر را دریافت کردیم:

ORA-00821: Specified value of sga_target 180736M is too small, needs to be at least 191488M

دلیل خطا: با اضافه شدن مقادیر جدید برای Poolها ($16$ گیگابایت در مجموع) و همچنین وجود $50$ گیگابایت حافظه In-Memory، مجموع حداقل‌های درخواستی از مقدار کل sga_target (که حدود $176$ گیگابایت بود) بیشتر شده بود.

بازیابی و حل نهایی مشکل با استفاده از PFILE

از آنجا که دیتابیس بالا نمی‌آمد و امکان استفاده از ALTER SYSTEM وجود نداشت، باید فایل spfile را اصلاح می‌کردیم. به لطف بکاپی که از PFILE گرفته بودیم، توانستیم سریعاً دیتابیس را بازیابی کنیم. مراحل زیر برای رفع مشکل در محیط کلاستر طی شد:

۱. ابتدا دیتابیس را با همان PFILE بکاپ استارت کردیم:

STARTUP PFILE='/home/oracle/pfile_backup_before_change.ora';

۲. اعمال نهایی تغییرات در SPFILE کلاستر

پس از اینکه دیتابیس با موفقیت توسط فایل PFILE بکاپ استارت شد، برای اینکه مقادیر جدید حافظه به صورت دائمی در SPFILE اصلی دیتابیس (که توسط Clusterware استفاده می‌شود) ثبت شوند، دستورات زیر را اجرا کردیم. در این مرحله مقدار کلی SGA را به $200$ گیگابایت افزایش دادیم تا فضای کافی برای Poolها و In-Memory وجود داشته باشد:

sys@dwn(1713)> alter system set sga_max_size=200g scope=spfile;

System altered.

Elapsed: 00:00:00.02
sys@dwn(1713)> alter system set sga_target=200g scope=spfile;

System altered.

Elapsed: 00:00:00.01
sys@dwn(1713)> ALTER SYSTEM SET large_pool_size = 4G scope=spfile;

System altered.

Elapsed: 00:00:00.01
sys@dwn(1713)> ALTER SYSTEM SET shared_pool_size = 12G scope=spfile;

System altered.

Elapsed: 00:00:00.01

پس از اجرای موفق این دستورات، دیتابیس را یک بار SHUTDOWN IMMEDIATE کرده و سپس از طریق srvctl start database -d dwn آن را استارت کردیم تا دیتابیس با مقادیر جدید و به صورت استاندارد از روی SPFILE کلاستر بالا بیاید و مشکل کمبود حافظه برطرف گردد.

• در محیط‌های Data Warehouse با پردازش‌های موازی سنگین، هرگز پارامترهای shared_pool_size و large_pool_size را روی 0 تنظیم نکنید. تخصیص یک حداقل منطقی، از خطای ORA-04031 جلوگیری می‌کند.
• پیش از افزایش حداقل مقادیر Poolها، همیشه محاسبه کنید که آیا sga_target فضای کافی برای پوشش دادن این مقادیر به همراه In-Memory و Buffer Cache را دارد یا خیر.
• ایجاد یک فایل PFILE پیش از تغییر پارامترهای حساس، یک قانون طلایی برای هر DBA است و شما را از مشکلات پیش‌بینی نشده (مانند خطای ORA-00821) نجات می‌دهد.