Oracle 10g modify SGA_TARGET value experience ORA-01078

Backup

To ensure that the memory settings do not affect the database startup failure settings
Therefore, you need to back up the spfile/pfile file.
[Oracle @ ur65dbs] $ cd/u01/app/oracle/oraInventory/db_1/dbs
[Oracle @ ur65dbs] $ cp spfile1_db. ora spfile1_db. ora. bak

Cd/u01/app/oracle/oraInventory/db_1/admin/analytic dB/pfile/
Cp init. ora.00002009144930 init. ora.00002009144930.bak

Two implementation methods
1) modify the sga-target file in the init. ora Time Point file or add sga_max_size to modify the maximum memory and the actual memory usage value ===" create spfile = spfileSID_NAME.ora
2) http: // 192.168.0.65: 1158/em/
Adjust Memory Value
The entire process is as follows:

Direct MODIFICATION TEST
-------- Directly restore the largest SGA value in EM
Alter system set sga_max_size = 524288000 COMMENT = 'internally adjusted 'SCOPE = SPFILE

Restart the database
SQL> conn sys/analytic dB as sysdba
Why? Á ?? ? Bytes
SQL> shutdown immediate;

Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 222298336 bytes
Database Buffers 297795584 bytes
Redo Buffers 2973696 bytes
Ê y? Y? A × ° ?? Íade± ?? Bytes

SQL> show parameter sga_max_size;

NAME TYPE VALUE
-----------------------------------------------------------------------------
Sga_max_size big integer 500 M

This method is valid, but the database needs to be started to take effect.

View the corresponding file
1) vi/u01/app/oracle/oraInventory/db_1/dbs/spfile1_db. ora
$ ORACLE_HOME/dbs/spfileSID_NAME.ora
Found
*. Sga_max_size = 524288000 # internally adjusted # modified at this time and added a field to spfile1_db. ora.
*. Sga_target = 424288000
2)
/U01/app/oracle/oraInventory/db_1/admin/analytic dB/pfile/init. ora.20.2009144930
$ ORACLE_HOME/admin/SID_NAME/pfile/init. ora. Time Point
# SGA Memory
######################################## ###
Sga_target = 424288000

-------- Actual SGA_TARGET value repaired directly in EM

Directly modify the memory value in em
Old
Sga_target = 408 M
Sga_max_size = 500 M

Alter system set sga_target = 471859200 SCOPE = BOTH
Modify the value of the spfile/current service process at the same time, and restart the service to take effect.
Update message

Post-modification report
1) Changes
Vi/u01/app/oracle/oraInventory/db_1/dbs/spfile1_db. ora
*. Sga_max_size = 524288000 # internally adjusted
*. Sga_target = 473956352
2) No changes
/U01/app/oracle/oraInventory/db_1/admin/analytic dB/pfile/init. ora.20.2009144930
# SGA Memory
######################################## ###
Sga_target = 424288000
Changes have been made. It may take some time for the change to take effect.
3) restart the database
SQL> startup
ORACLE ?? -??? Why? Bytes

Total System Global Area 524288000 bytes = 500 M = sga_max_size
Fixed Size 1220384 bytes
Variable Size 222298336 bytes
Database Buffers 297795584 bytes
Redo Buffers 2973696 bytes
View
SQL> show parameter sga_target

NAME TYPE VALUE
-----------------------------------------------------------------------------
Sga_target big integer 452 M

SQL> show parameter sga_max_size

NAME TYPE VALUE
-----------------------------------------------------------------------------
Sga_max_size big integer 500 M
1) Check memory, no changes
Vi/u01/app/oracle/oraInventory/db_1/admin/analytic dB/pfile/init. ora.2009144930
# SGA Memory
######################################## ###
Sga_target = 424288000
2) vi/u01/app/oracle/oraInventory/db_1/dbs/spfile1_db. ora has not changed

*. Sga_max_size = 524288000 # internally adjusted
*. Sga_target = 473956352

Conclusion
When modifying the current memory, you do not need to restart the database, but you need to restart the database to modify the maximum memory.

$ ORACLE_HOME/admin/SID_NAME/pfile/init. ora. Time Point
$ ORACLE_HOME/dbs/spfileSID_NAME.ora
There is no necessary link between the two, unless you use create spfile from pfile = '/opt/oracle/admin/analytic dB/pfile/init. ora.20.2009144930'
To generate a new spfile file format.

To ensure that the memory settings do not affect the database startup failure settings
Therefore, you need to back up the spfile/pfile file.
[Oracle @ ur65dbs] $ cd/u01/app/oracle/oraInventory/db_1/dbs
[Oracle @ ur65dbs] $ cp spfile1_db. ora spfile1_db. ora. bak

Cd/u01/app/oracle/oraInventory/db_1/admin/analytic dB/pfile/
Cp init. ora.00002009144930 init. ora.00002009144930.bak

------------ Create a spfile file separately

 

SQL> create spfile from pfile = '/u01/app/oracle/oraInventory/db_1/admin/analytic dB/pfile/init. ora.2009144930'
2/
Create spfile from pfile = '/u01/app/oracle/oraInventory/db_1/admin/analytic dB/pfile/init. ora.20.2009144930'
*
μú1 DD3 ??? ? Ó:
ORA-32002 :? T ′′? ? ? μ? SPFILE

You need to close the database and create pfile under nmount. Otherwise, the database fails to be used.
SQL> shutdown immediate;
Ê y? Y? A challenge ?? -1? ± ?? Bytes
Why ?? -D ??? Ê y? Y? A? Bytes
ORACLE ?? -1? ± ?? Bytes
SQL> create spfile from pfile = '/u01/app/oracle/oraInventory/db_1/admin/analytic dB/pfile/init. ora.2009144930'
2/

SQL> startup
ORACLE ?? -??? Why? Bytes

Total System Global Area 436207616 bytes
Fixed Size 1219856 bytes
Variable Size 125829872 bytes
Database Buffers 306184192 bytes
Redo Buffers 2973696 bytes

When the startup detects that the memory value changes, spfilemongodb. ora is consistent with init. ora2132009144930.
*. Sga_target = 434288000;
However, sga_max_size does not appear in sfile1_db. ora. It should not be specified in init. ora2132009144930

[Oracle @ ur65pfile] $ pwd
/U01/app/oracle/oraInventory/db_1/admin/analytic dB/pfile
[Oracle @ ur65pfile] $ vi init. ora.20.2009144930

Add
Sga_max_size = 524288000 # internally adjusted

--- Close
SQL> shutdown immediate;
Ê y? Y? A challenge ?? -1? ± ?? Bytes
Why ?? -D ??? Ê y? Y? A? Bytes
ORACLE ?? -1? ± ?? Bytes

SQL> create spfile from pfile = '/u01/app/oracle/oraInventory/db_1/admin/analytic dB/pfile/init. ora.2009144930'
2/

SQL> startup
ORACLE ?? -??? Why? Bytes

Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 213909728 bytes
Database Buffers 306184192 bytes
Redo Buffers 2973696 bytes
Ê y? Y? A × ° ?? Íade± ?? Bytes
Ê y? Y? A challenge ?? -'Timeout? A? Bytes

The startup detection value has changed, and the maximum memory size is 524288000 bytes and sga_max_size = 524288000 # internally adjusted
Added

Therefore, you can directly modify init. ora.20.2009144930 ----> to add sga_max_size = 524288000 # internally adjusted, *. sga_target = 434288000 ;== create spfile to generate
Achieve simultaneous modification, the most effective

----- Directly modify the value of spfiledomaindb. ora
*. Sga_max_size = 504288000 # internally adjusted
*. Sga_target = 404288000
SQL> shutdown immediate
Ê y? Y? A challenge ?? -1? ± ?? Bytes
Why ?? -D ??? Ê y? Y? A? Bytes
ORACLE ?? -1? ± ?? Bytes
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109 :???????????????? '/U01/app/oracle/oraInventory/db_1/dbs/initjwdb. ora'

Report error description. You cannot directly modify the spfilemongodb. ora file. Instead, you need to modify init. ora.20.2009144930.

Solution:

SQL> create spfile from pfile = '/u01/app/oracle/oraInventory/db_1/admin/analytic dB/pfile/init. ora.2009144930'
2/

SQL> startup
ORACLE

Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 213909728 bytes
Database Buffers 306184192 bytes
Redo Buffers 2973696 bytes

Set successfully !!!!