ORA-00064 processes set too large causes the database to open

Processes setting too large causes the database to open

After the processes is set too large, it may cause the database to open, the database will be opened after the error:

Sql> Startupora-00064:object is too large to allocate on this O/S (1,7746920) sql>

Workaround:

1. First find the Pfile location, then start the database from Pfile;

   Startup pfile= $ORACLE _base/admin/sid/pfile/init.ora.49201715235 '

   Pfile is generally in the _sid/pfile directory of $oracle_base/admin/$ORACLE.

2. Locate the SPFile location. Then use SPFile to generate pfile;

   Create pfile= '/tmp/pfile.ora ' from spfile= ' +datadg/sid/spfilesid.ora '

   The SPFile file location is indicated in the file $oracle_home/dbs/init${oracle_sid}.ora file.

3. Modify the newly generated pfile, change the process value to small, generate spfile with pfile;

   Create spfile= ' +datadg/sid/spfilesid.ora ' from pfile= '/tmp/pfile.ora ';

4. After restarting the database, perform show parameter spfile, view the current spfile location, and if the location and the location indicated in the $oracle_home/dbs/init${oracle_sid}.ora file are inconsistent, put the current SPFile alias after , restart the database.

Examples of resolution steps:

1. See the location of SPFile

[Email protected] ~]$ more/opt/oracle/product/10.2.0/db_1/dbs/initkhadb1.oraspfile= ' +mydata/khadb/ Spfilekhadb.ora '

2. Generate SPFile with pfile

Sql>sql> startupora-00064: object is too large to allocate on  this O/S  (1,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.

3. Modify the newly generated pfile, change the process value to small, generate spfile with Pfile

650) this.width=650; "title=" Image_564.png "src=" https://s3.51cto.com/wyfs02/M00/96/3D/wKiom1kehK3h21sZAAAhpXYQ8_ O151.png-wh_500x0-wm_3-wmp_4-s_3057916340.png "alt=" Wkiom1kehk3h21szaaahpxyq8_o151.png-wh_50 "/>

sql> create spfile= ' +mydata/khadb/spfilekhadb.ora ' from pfile= '/tmp/pfile.ora '; File created.

4. 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     &Nbsp;         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, the waiting time needs to be ensured ksmg_granule_size=16m or 32M, while ksmg_granule_size size is determined by sga_max_size, when sga_max_size<= 1024, Ksmg_granule_size=4m;sga_max_size is based on the sga_target to determine, modify the sga_target>=1025m can be;

Here, the following method, first restore the database, follow up with your own plan to modify the Sga_target, and then modify the process value

1. Alter system set sga_target=1200m scope=spfile

   Modify sga_target=1200m, sga_max_size=1200m when restarting the database;

   650) this.width=650; "title=" 3.png "src=" https://s2.51cto.com/wyfs02/M02/96/3D/ Wkiol1kehr-ab982aaamd9asuoa401.png-wh_500x0-wm_3-wmp_4-s_2178974039.png "alt=" Wkiol1kehr-ab982aaamd9asuoa401.png-wh_50 "/>
   

2. Select x.ksppinm NAME,Y.KSPPSTVL value, X.ksppdesc descbtion from X$ksppi X,X$KSPPCV 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 the SGA is large enough, we can set the _KSMG_GRANULE_SIZE=32MB manually;

   Alter system set "_ksmg_granule_size" =33554432 scope=spfile;

   650) this.width=650; "title=" 2.png "src=" https://s1.51cto.com/wyfs02/M00/96/3D/ Wkiol1kehpcjqbapaaacembs5zo288.png-wh_500x0-wm_3-wmp_4-s_1968909150.png "alt=" Wkiol1kehpcjqbapaaacembs5zo288.png-wh_50 "/>
   

3. Then fix the process size according to your needs

Alter system set sessions=10000 Scope=spfile;

This article is from the "Future People" blog, please be sure to keep this source http://zaa47.blog.51cto.com/6181689/1927506

ORA-00064 processes set too large causes the database to open