How to change the SGA_TARGET and SGA_MAX_SIZE in Oracle XE

I wnat to change SGA_TARGET and SGA_MAX_SIZE in Oracle XE but i ma getting this error when i am trying to start the database.

SQL> alter system set SGA_TARGET=1024M scope=spfile;

System altered.

SQL> alter system set SGA_MAX_SIZE=1024M scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-44412: XE edition memory parameter invalid or not specified
SQL>


Please tell me how to resolve this issue.Thanks.

use a valid parameter?

When i checked the limitation of Oracle XE I found that Oracle Database XE can address only 1GB of RAM. so I changed again SGA_TARGET parameter to 1000m But it is still showing the same error

ORA-44412: XE edition memory parameter invalid or not specified

This is parameter file:

xe.__java_pool_size=4194304
xe.__large_pool_size=4194304
xe.__shared_pool_size=155189248
xe.__streams_pool_size=0
*.audit_file_dest='C:\oraclexe\app\oracle\admin\XE\adump'
*.background_dump_dest='C:\oraclexe\app\oracle\admin\XE\bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oraclexe\oradata\XE\control.dbf'
*.core_dump_dest='C:\oraclexe\app\oracle\admin\XE\cdump'
*.db_name='XE'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.DB_RECOVERY_FILE_DEST='C:\oraclexe\app\oracle\flash_recovery_area'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.job_queue_processes=4
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=SSIPL-LAPTP-052)(PORT=1522))'
*.open_cursors=300
*.os_authent_prefix=''
*.pga_aggregate_target=190M
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.sga_target=1048576000
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDO'
*.user_dump_dest='C:\oraclexe\app\oracle\admin\XE\udump'

Can you see initXE.ora file under ORACLE_HOME\database ?

Look at it, check parameters and values then start your database pointing at it, like...

startup pfile=ORACLE_HOME\database\initXE.ora

before changing SGA_TARGET parameter i checked which parameter file is using by the Database and i found that Database is using SPFILE and pfile pointing to same spfile.

Create pfile from spfile then follow PAVB suggestion to start the database. Once started, then create spfile from pfile and bounce the DB.

Thanks,

I have done this step

Create pfile from spfile then follow PAVB suggestion to start the database. Once started, then create spfile from pfile and bounce the DB.

But getting the same error.

would you mind post the complete parameters from pfile??

Thanks,

This is the pfile that i have created after changing the SGA_TARGET parameter in database But XE Database is not starting with this pfile.

xe.__db_cache_size=574619648
xe.__java_pool_size=4194304
xe.__large_pool_size=8388608
xe.__shared_pool_size=213909504
xe.__streams_pool_size=0
*.audit_file_dest='C:\oraclexe\app\oracle\admin\XE\adump'
*.background_dump_dest='C:\oraclexe\app\oracle\admin\XE\bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oraclexe\oradata\XE\control.dbf'
*.core_dump_dest='C:\oraclexe\app\oracle\admin\XE\cdump'
*.db_name='XE'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.DB_RECOVERY_FILE_DEST='C:\oraclexe\app\oracle\flash_recovery_area'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.job_queue_processes=4
*.open_cursors=300
*.os_authent_prefix=''
*.pga_aggregate_target=256M
*.processes=200
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=200
*.sga_target=1048576000
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDO'
*.user_dump_dest='C:\oraclexe\app\oracle\admin\XE\udump'

add sga_max_size=1048576000 in the pfile and start the instance.

Thanks,

I am just looking back into my posts and realized that adding sga_max_size in pfile is not required. My apologies and please ignore my previous post.

Thanks,

This may help you.

http://forums.oracle.com/forums/thre...sageID=3753756

make sure your SGA + PGA is not exceeding more than 1GB.

Thanks,

SQL> startup pfile=C:\oraclexe\app\oracle\product\10.2.0\server\database\initXE.ora
ORA-44412: XE edition memory parameter invalid or not specified
ORA-01078: failure in processing system parameters
SQL>


This is two parameter in initXE.ora that is changed according to your instruction.

*.sga_max_size=1048576000
*.sga_target=1048576000

Thanks and sorry for my previous reply.

I followed your instruction

make sure your SGA + PGA is not exceeding more than 1GB.

and its works.Once again thanks for solving this issue.

Glad to hear that your issue has been resolved. Thanks for updating the forum.

Thanks,