DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: How to change the SGA_TARGET and SGA_MAX_SIZE in Oracle XE

  1. #1
    Join Date
    Apr 2007
    Location
    india
    Posts
    11

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    use a valid parameter?

  3. #3
    Join Date
    Apr 2007
    Location
    india
    Posts
    11
    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'

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Apr 2007
    Location
    india
    Posts
    11
    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.

  6. #6
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Create pfile from spfile then follow PAVB suggestion to start the database. Once started, then create spfile from pfile and bounce the DB.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  7. #7
    Join Date
    Apr 2007
    Location
    india
    Posts
    11
    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.

  8. #8
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    would you mind post the complete parameters from pfile??

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  9. #9
    Join Date
    Apr 2007
    Location
    india
    Posts
    11
    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'

  10. #10
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    add sga_max_size=1048576000 in the pfile and start the instance.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width