After the oracle sga parameter is changed to a large value, the database cannot be started. How can this problem be solved ?, Oraclesga

After the oracle sga parameter is changed to a large value, the database cannot be started. How can this problem be solved ?, Oraclesga

The SGA_MAX_SIZE initialization parameter specifies the maximum size of the global region of the instance during its lifecycle. You can dynamically change the initialization parameters that affect the buffer cache, Shared Pool, large pool, Java pool, and stream pool size, but only the sum of these sizes and the size of other SGA components (fixed SGA, variable SGA and redo log buffer) will not exceed the specified value SGA_MAX_SIZE.

If SGA_MAX_SIZE is not specified, the Oracle database selects a default value, which is the sum of all components specified during initialization or default. If SGA_MAX_SIZE is specified and the value is smaller than the total memory allocated to all components during database initialization (explicitly in the parameter file or by default ), the database ignores SGA_MAX_SIZE settings and selects the correct value as this parameter.

Enable the automatic shared memory management function by setting the SGA_TARGET parameter to a non-zero value. This parameter sets the total size of SGA. It replaces the parameters that control the memory allocated to a group of specific components. These components can be automatically adjusted as needed ).

However, if the value of sga_max_size is too large, the system cannot allocate enough memory. As a result, after the sga_max_size parameter is modified in the database, the next time the database is started

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

In this case, if you have performed a backup before setting and modifying the spfile settings, you can create a backup spfile to start the backup. The default spfile path is the $ ORACLE_HOME/dbs directory, backup spfile can copy a copy directly or generate a pfile File

SQL>create pfile='pfile path' from spfile;  

Specify the spfile path to start

SQL> startup nomount spfile='spfile path'  

If you do not copy or back up the file before the change, it is a tragedy. However, you can modify a pfile from the modified spfile file, start and adjust the parameters. The procedure is as follows:

1. Make a backup of the spfile. Although the current spfile cannot be used, it is really difficult if it is lost even if it cannot be used.

2. Change the current spfile to the name init + Instance name. ora. For example, if the Instance name is orcl, change it to initorcl. ora, and change ORCL to initORCL. ora in upper case.

3. Edit initorcl. the ora file, that is, to delete the special characters that cannot be understood, and find the sga_MAX_SIZE parameter, and change it to a value that has not been modified before or to a smaller value. For example, sga_max_size = 900 M. Change the size. After the change, it looks like

orcl.__db_cache_size=356515840  orcl.__java_pool_size=4194304  orcl.__large_pool_size=4194304  orcl.__oracle_base='/data/oracledba'#ORACLE_BASE set from environment  orcl.__pga_aggregate_target=385875968  orcl.__sga_target=650117120  orcl.__shared_io_pool_size=0  orcl.__shared_pool_size=264241152  orcl.__streams_pool_size=8388608  *.audit_file_dest='/data/oracledba/admin/orcl/adump'  *.audit_trail='db'  *.compatible='11.2.0.0.0'  *.control_files='/data/oracledba/oradata/orcl/control01.ctl','/data/oracledba/fast_recovery_area/orcl/control02.ctl'  *.db_block_size=8192  *.db_domain=''  *.db_name='orcl'  *.db_recovery_file_dest='/data/oracledba/fast_recovery_area'  *.db_recovery_file_dest_size=4322230272  *.diagnostic_dest='/data/oracledba'  *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'  *.log_archive_dest_1='location=/data/archivelog'  *.memory_target=1033895936  *.open_cursors=300  *.processes=150  *.remote_login_passwordfile='EXCLUSIVE'  *.sga_max_size=988M  *.undo_tablespace='UNDOTBS1'  

4. check whether there are any files named lk + instance in the $ ORACLE_HOME/dbs directory. For example, if lkorcl exists, you can change the name of the file and delete it.

5. It can be started.

starup nomount  

If nomount is enabled, it will be okay.

alter database mount;  alter database open;  

Remember, you must back up the control file before modifying the spfile parameters. It is best to back up the control file together and leave a retreat for yourself. Otherwise, you will have to run the road.