Record a series of errors after modifying the size of sga once

After modifying the sga size of oracle, we encountered a series of errors.

00844 and 00851 error reporting

SQL> startup;
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 2147483648 cannot be set to more than MEM
SQL> 

01078 error reporting

SQL> startup;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.4/db_1/dbs/inittest.ora'

I. modification process

1.1 why should I change the size of sga

During the test, a lot of data was inserted and 30009 errors were found. I thought it was caused by sga

SQL> insert into t select rownum from dual connect by level<=10000000;
insert into t select rownum from dual connect by level<=10000000
            *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation

Elapsed: 00:00:13.51
SQL>

View sga

SQL> show parameter sga;

NAME                     TYPE                  VALUE
------------------------------------ --------------------------------- ------------------------------
lock_sga                 boolean                   FALSE
pre_page_sga                 boolean                   FALSE
sga_max_size                 big integer               1584M
sga_target               big integer               1G
SQL> 

View pga

SQL> show parameter pga

NAME                     TYPE                  VALUE
------------------------------------ --------------------------------- ------------------------------
pga_aggregate_target             big integer               0
SQL> 

1.2 modify sga

Change sga

SQL> alter system set sga_max_size=2048M scope=spfile;

System altered.

Elapsed: 00:00:00.06
SQL>

1.3 error 1

Shut down and start the database

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 2147483648 cannot be set to more than MEMORY_TARGET 1660944384.

There was an error. After searching the data, it is found that 11g has added the parameter memory "target, whose size is equal to PGA+SGA. When the size of sga is greater than memory" target, the above error will be reported.

Check the size of memory "target and find that the size is 1584M, which is not consistent with sga.

SQL> show parameter memory_target;

NAME                     TYPE                  VALUE
------------------------------------ --------------------------------- ------------------------------
memory_target                big integer               1584M
SQL> 

Modify parameters in spfile file

sga_max_size=1653604352

Start database

SQL> startup nomount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.4/db_1/dbs/inittest.ora'
SQL> 

1.4 error 2

01078 error reported after startup. The solution is as follows:
Copy the init.ora.xxx file under the test (instance name) folder to the dbs directory, and enter the copied name according to the previous error prompt.

[oracle@localhost ~]$ cp /u01/app/oracle/admin/test/pfile/init.ora.922018114616 /u01/app/oracle/product/11.2.4/db_1/dbs/inittest.ora

Start database

SQL> startup;
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size          2213896 bytes
Variable Size         956303352 bytes
Database Buffers      687865856 bytes
Redo Buffers            7135232 bytes
Database mounted.
Database opened.
SQL> 

Two, summary

At the end of the day, it's still caused by a lack of familiarity with database principles. Remember that the memory target value should be the sum of sga+pga memory.