Posts Tagged spfile
An invalid entry in the spfile may prevent the instance from starting up:
SQL> alter system set sga_target=500m scope=spfile; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORA-00821: Specified value of sga_target 512M is too small, needs to be at least 1392M
The instance doesn’t come up! This is easy to fix without having to restore the spfile from backup:
SQL> create pfile='/home/oracle/init.ora' from spfile; File created. SQL> host vi /home/oracle/init.ora
Now correct the value in the text file. I just removed the sga_target parameter from it here. Then
SQL> create spfile from pfile='/home/oracle/init.ora' ; File created. SQL> startup ORACLE instance started. Total System Global Area 1342177280 bytes Fixed Size 2924112 bytes Variable Size 218104240 bytes Database Buffers 1107296256 bytes Redo Buffers 13852672 bytes Database mounted. Database opened.
Problem solved! That fix works for any invalid entry in the spfile, not only for sga_target. I don’t know how often I had to do that over the years 🙂