ORA-00823: Specified value of sga_target greater than sga_max_size

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

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

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#oracle #数据库 #system #sql #测试

在调整SGA_MAX_SIZE参数时未正确添加单位'M'，导致数据库无法启动。通过重新创建SPFILE和PFILE，成功解决了该问题。

更改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 CREATE PFILE='/opt/ora11g/product/11.2.3/db_1/dbs/pfile01.ora' FROM SPFILE;

*.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.