Adjusting the size of Oracle sga causes ORA-00851 and ORA-00844 errors

Adjusting the size of Oracle sga causes ORA-00851 and ORA-00844 errors

Today, I adjusted the size of Oracle sga. the following error is returned when I restart the database.
SQL> alter system set sga_max_size = 1024 M scope = spfile;
SQL> shutdown immediate
SQL> startup
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 784334848.
After checking in Oracle, a new MEMORY_TARGET parameter is added for 11g, and its size is equal to PGA + SGA. When the size of sga is greater than MEMORY_TARGET, the above error is reported.
 

Solution:
You can directly modify the MEMORY_TARGET size in the spflie file. However, since you have not checked the spfile location and do not know the specific position of the spfile, you can only use the following method:
1. sqlplus/as sysdba
SQL> create pfile = '/home/oracle/pfile' from spfile;
Create a pfile and modify the MEMORY_TARGET parameter in the pfile,
Then recreate the spfile file.
SQL> create spfile from pfile = '/home/oracle/pfile ';
 

The following error is reported after the restart.
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
This is because the system tmpfs is smaller than MEMORY_TARGET and the size of tmpfs needs to be adjusted.
Vi/etc/fstab
Tmpfs/dev/shm tmpfs ults, size = 2G 0 0
Modify the size
Remount
Mount-o remount/dev/shm
Verify
Df-h
 

Now the database can be started properly.
SQL> startup
ORACLE instance started.
 

Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 943722328 bytes
Database Buffers 117440512 bytes
Redo Buffers 5554176 bytes
Database mounted.
Database opened.

To sum up, the sequence for adjusting the sga is to first adjust the size of tmpfs, then adjust the size of MEMORY_TARGET, and finally adjust the sga to ensure that tmpfs> MEMORY_TARGET> sga + pga

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------