Shulou(Shulou.com)06/01 Report--

Record a series of errors that occur after a change in sga size

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

00844 and 00851 error reports

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

01078 error report

SQL > startup;ORA-01078: could not open parameter file'/ u01 sga startup;ORA-01078: could not open parameter file'/ u01AccessUniverse product 11.2.4Universe dbqqqdbAccording inittest.ora1, modify process 1.1 Why should I change the size of oracle

During the test, a large amount of data was inserted and 30009 error was found. I thought it was caused by a small sga.

SQL > insert into t select rownum from dual connect by level show parameter sga NAME TYPE VALUE -- lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size big integer 1584Msga_target big integer 1GSQL >

View pga

SQL > show parameter pgaNAME TYPE VALUE -pga_aggregate_target big integer 0SQL > 1.2 modify sga

Change sga

SQL > alter system set sga_max_size=2048M scope=spfile;System altered.Elapsed: 00:00:00.06SQL > 1.3Error 1

Shut down and start the database

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

And it went wrong. After searching the data, it is found that 11g has added a parameter MEMORY_TARGET, whose size is equal to PGA+SGA. When the size of sga is greater than MEMORY_TARGET, the error will be reported as above.

Looking at the MEMORY_TARGET size, it is found that the size is 1584m, which does not match the sga.

SQL > show parameter memory_target NAME TYPE VALUE -- memory_target big integer 1584MSQL >

Modify parameters in the spfile file

Sga_max_size=1653604352

Start the database

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

01078 error report was encountered after startup. The solution is as follows:

Just copy the init.ora.xxx file under the test (instance name) folder to the dbs directory, and enter the copied name according to the prompt of the previous error report.

[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 the database

SQL > startup;ORACLE instance started.Total System Global Area 1653518336 bytesFixed Size 2213896 bytesVariable Size 956303352 bytesDatabase Buffers 687865856 bytesRedo Buffers 7135232 bytesDatabase mounted.Database opened.SQL > II. Summary

In the final analysis, it is caused by being unfamiliar with the principle of the database. Keep in mind that the memory_target value needs to be the sum of sga+pga memory.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.