ORA-00823: Specified value of sga_target greater than sga_max_size

最新推荐文章于 2022-08-19 17:48:35 发布

原创最新推荐文章于 2022-08-19 17:48:35 发布 · 215 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

本文详细描述了一次在调整SGA_MAX_SIZE参数时因未正确添加单位而导致Oracle数据库无法启动的问题，并提供了从创建PFILE、修改参数、创建SPFILE到最终解决问题的完整步骤。通过此教程，读者可以避免类似错误并了解如何正确配置数据库参数以确保其稳定运行。

本人已经尝试，的确可以成功。

更改sga_max_size的时候不小心少加了单位'M'，结果遇到了ORA-00823导致数据库起不来。
1.根据pfile创建spfiel后，修改pfile中的sga_max_size参数
2.用pfile启动数据库，测试正常。
3.根据pfiel创建spfile
4.用spfile启动数据库。

SQL>ALTER SYSTEM SET sga_max_size = 4096 SCOPE=SPFILE; -- 少写了一个M,害得数据库起不来

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORA-00823: Specified value of sga_target greater than sga_max_size

SQL> CREATE PFILE='/opt/ora11g/product/11.2.3/db_1/dbs/pfile01.ora' FROM SPFILE;
File created.

SQL> !
[oracle@oradb01 emd]$ vi /opt/ora11g/product/11.2.3/db_1/dbs/pfile01.ora

*.sga_max_size=4096 改为
*.sga_max_size=4096M

SQL> startup PFILE='/opt/ora11g/product/11.2.3/db_1/dbs/pfile01.ora'
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size                  2266024 bytes
Variable Size            1681919064 bytes
Database Buffers         2583691264 bytes
Redo Buffers                7905280 bytes
Database mounted.
Database opened.
SQL> !
[oracle@oradb01 emd]$ cd /opt/ora11g/product/11.2.3/db_1/dbs/

[oracle@oradb01 dbs]$ cp spfileorcl.ora spfileorcl.ora_bak01

[oracle@oradb01 dbs]$ exit
exit

SQL> CREATE SPFILE='/opt/ora11g/product/11.2.3/db_1/dbs/spfileorcl.ora' FROM PFILE='/opt/ora11g/product/11.2.3/db_1/dbs/pfile01.ora';

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2235208 bytes
Variable Size            1677722808 bytes
Database Buffers         2583691264 bytes
Redo Buffers               12132352 bytes
Database mounted.
Database opened.