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

The processes setting is too large so that the database cannot be opened.

When the processes setting is too large, the database may not be opened, and an error will be reported after opening the database:

SQL > startupORA-00064: object is too large to allocate on this Oshock S (1mem7746920) SQL > solution:

First find the pfile location, and then start the database from pfile

Startup pfile=$ORACLE_BASE/admin/SID/pfile/init.ora.49201715235'

Pfile is usually in the $ORACLE_BASE/admin/$ORACLE_SID/pfile directory.

Find the spfile location. Then use spfile to generate pfile

Create pfile='/tmp/pfile.ora' from spfile='+DATADG/SID/spfileSID.ora'

The location of the spfile file is indicated in the file $ORACLE_HOME/dbs/init$ {ORACLE_SID} .ora file.

Modify the newly generated pfile, reduce the process value, and generate spfile with pfile

Create spfile='+DATADG/SID/spfileSID.ora' from pfile='/tmp/pfile.ora'

After restarting the database, execute show parameter spfile to check the current spfile location. If the location does not match the location indicated in the $ORACLE_HOME/dbs/init$ {ORACLE_SID} .ora file, please put the current spfile alias and restart the database.

Example of resolution steps:

View the location of spfile

[oracle@kdb01 ~] $more / opt/oracle/product/10.2.0/db_1/dbs/initkhadb1.oraSPFILE='+MYDATA/khadb/spfilekhadb.ora'

Using spfile to generate pfile

SQL > SQL > startupORA-00064: object is too large to allocate on this Oamp S (1m 7746920) SQL > startup pfile='/opt/oracle/admin/khadb/pfile/init.ora.492017152117';ORACLE instance started.Total System Global Area 343932928 bytesFixed Size 2096152 bytesVariable Size 113247208 bytesDatabase Buffers 222298112 bytesRedo Buffers 6291456 bytesDatabase mounted.Database opened.SQL > SQL > show parameter spfile NAME TYPE VALUE---spfile stringSQL > SQL > create pfile='/tmp/pfile.ora' from SPFILE='+MYDATA/khadb/spfilekhadb.ora';File created.

Modify the newly generated pfile, reduce the process value, and generate spfile with pfile

SQL > create SPFILE='+MYDATA/khadb/spfilekhadb.ora' from pfile='/tmp/pfile.ora';File created.

Restart the database to view processes settings

SQL > shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL > startupORACLE instance started.Total System Global Area 343932928 bytesFixed Size 2096152 bytesVariable Size 142607336 bytesDatabase Buffers 192937984 bytesRedo Buffers 6291456 bytesDatabase mounted.Database opened.SQL > show parameter processes NAME TYPE VALUE-- aq_tm_processes integer 0db_writer_processes integer 1gcs _ server_processes integer 1job_queue_processes integer 10log_archive_max_processes integer 2processes integer 1000SQL > SQL > show parameter spfile NAME TYPE VALUE-- spfile string + MYDATA/khadb/spfilekhadb.oraSQL > cause analysis

When PROCESSES > 1500, you need to ensure ksmg_granule_size=16M or 32m, and the ksmg_granule_size size is determined according to sga_max_size, when sga_max_size=1025M

Here, use the following method to restore the database first, and then modify the sga_target value later according to your own planning.

Alter system set sga_target=1200m scope=spfile

Modify sga_target=1200M, restart the database, sga_max_size=1200m

Select x.ksppinm name,y.ksppstvl value,x.ksppdesc descbtion from x$ksppi xonomer xsancksppcv y where x.inst_id=userenv ('Instance') and y.inst_id=userenv (' Instance') and x.indx=y.indx and x.ksppinm like'% _ ksmg_granule%'

View the ksmg_granule_size size. When SGA is large enough, we can set _ ksmg_granule_size=32MB manually

Alter system set "_ ksmg_granule_size" = 33554432 scope=spfile

Then modify the process size according to your own needs.

Alter system set sessions=10000 scope=spfile

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.