本问题在linux环境下,windows环境下类似。
问题描述:
在数据库操作时不小心修改了share_pool的大小,导致重启数据库无法正常启动,报错为:
1 SQL>startup; 2 ORA-00821: Specified value of sga_target 512M is too small, needs to be at least 804M 3 SQL>startup nomount; 4 ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0 5 SQL>show parameter sga 6 NAME TYPE VALUE 7 ------------------------------------ ----------- ------------------------------ 8 lock_sga boolean FALSE 9 pre_page_sga boolean FALSE 10 sga_max_size big integer 512M 11 sga_target big integer 512M 12 SQL> quit 13 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 14 With the Partitioning, OLAP, Data Mining and Real Application Testing options
原因分析及解决:
原因一定是不当的操作导致shared_pool_size为非正常值,只需要改回正常值(根据报错,大于804M即可)。
SQL>create pfile='/home/oracle/temp_init.ora' from spfile; 注意:/home/oracle/这个目录需要oracle用户有读写权限才行 SQL>quit;
[oracle@dbs ~]$ cd /home/oracle/ [oracle@dbs ~]$ vi temp_init.ora
将标记处改为大于804M即可,这里改为900M
ORCL.__db_cache_size=188743680 ORCL.__java_pool_size=4194304 ORCL.__large_pool_size=12582912 ORCL.__oracle_base='/home/oracle'#ORACLE_BASE set from environment ORCL.__pga_aggregate_target=12972982272 ORCL.__sga_target=536870912 ORCL.__shared_io_pool_size=0 ORCL.__shared_pool_size=314572800 ORCL.__streams_pool_size=4194304 *.audit_file_dest='/home/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/home/oracle/oradata/orcl/control01.ctl','/home/oracle/fast_recovery_area/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/home/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/home/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' *.open_cursors=300 *.pga_aggregate_target=12972982272 *.processes=500 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=1500 *.sga_target=943718400 *.undo_tablespace='UNDOTBS1'
然后强制加载pfile即可:
SQL> startup pfile='/home/oracle/temp_init.ora' ORACLE instance started. Total System Global Area 939495424 bytes Fixed Size 2258840 bytes Variable Size 666896488 bytes Database Buffers 260046848 bytes Redo Buffers 10293248 bytes Database mounted. Database opened. SQL> show parameter sga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 900M sga_target big integer 900M SQL> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@dbs ~]$
用PLSQL登录或者平台登录测试看,数据库已经正常了