Shulou(Shulou.com)06/02 Report--

After the database has been running for a certain amount of time, performance tuning is important, one of which is to adjust the SGA area.

A friend practiced magnifying SGA on the test machine. Issue the following command:

Change the sga size of the system

Alter system set sga_max_size=4g scope=spfile

Alter system set pga_aggregate_target=2G

Alter system set sga_target=4g scope=spfile

Shutdown immediate

Startup

The system reports errors directly: SQL > conn / as sysdba

Connected to an idle instance.

SQL > startup

ORA-27102: out of memory

If the program tester wants to use the database, how to recover?

The solution is as follows:

[hqmss@topup dbs] $sqlplus / nolog

SQL*Plus: Release 10.2.0.1.0-Production on Mon Jul 1 10:45:58 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL > conn / as sysdba

Connected to an idle instance.

SQL > create pfile from spfile

Produce the pfile file under the current system and modify the memory information of the parameter file.

[hqmss@topup dbs] $vi initHQMSS.ora

HQMSS.__db_cache_size=1660944384

HQMSS.__java_pool_size=33554432

HQMSS.__large_pool_size=16777216

HQMSS.__shared_pool_size=419430400

HQMSS.__streams_pool_size=0

* .audit_file_dest='/opt/oracle/admin/HQMSS/adump'

* .background_dump_dest='/opt/oracle/admin/HQMSS/bdump'

* .compatible='10.2.0.1.0'

* .control_files='/opt/oracle/oradata/HQMSS/control01.ctl','/opt/oracle/oradata/HQMSS/control02.ctl','/opt/oracle/oradata/HQMSS/control03.ctl'

* .core_dump_dest='/opt/oracle/admin/HQMSS/cdump'

* .db_block_size=8192

* .db_domain=''

* .db_file_multiblock_read_count=8

* .db_name='HQMSS'

* .DB_UNIQUE_NAME='stanDB'

* .dispatchers=' (PROTOCOL=TCP) (SERVICE=HQMSSXDB)'

* .FAL_CLIENT='primDB'

* .FAL_SERVER='stanDB'

* .job_queue_processes=10

* .LOG_ARCHIVE_CONFIG='DG_CONFIG= (primDB,stanDB)'

* .LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/flash_recovery_area/HQMSS/onlinelog/

VALID_FOR= (ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stanDB'

* .LOG_ARCHIVE_DEST_2='SERVICE=primDB LGWR ASYNC

VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primDB'

* .LOG_ARCHIVE_DEST_STATE_1='ENABLE'

* .LOG_ARCHIVE_DEST_STATE_2='ENABLE'

* .open_cursors=300

* .pga_aggregate_target=2147483648

* .processes=750

* .REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'

* .sessions=830

* .sga_max_size=4294967296

* .sga_target=4294967296

* .STANDBY_FILE_MANAGEMENT='AUTO'

* .undo_management='AUTO'

* .undo_tablespace='UNDOTBS1'

* .user_dump_dest='/opt/oracle/admin/HQMSS/udump'

~

The modified file is as follows:

[hqmss@topup dbs] $vi initHQMSS.ora

HQMSS.__db_cache_size=1660944384

HQMSS.__java_pool_size=33554432

HQMSS.__large_pool_size=16777216

HQMSS.__shared_pool_size=419430400

HQMSS.__streams_pool_size=0

* .audit_file_dest='/opt/oracle/admin/HQMSS/adump'

* .background_dump_dest='/opt/oracle/admin/HQMSS/bdump'

* .compatible='10.2.0.1.0'

* .control_files='/opt/oracle/oradata/HQMSS/control01.ctl','/opt/oracle/oradata/HQMSS/control02.ctl','/opt/oracle/oradata/HQMSS/control03.ctl'

* .core_dump_dest='/opt/oracle/admin/HQMSS/cdump'

* .db_block_size=8192

* .db_domain=''

* .db_file_multiblock_read_count=8

* .db_name='HQMSS'

* .DB_UNIQUE_NAME='stanDB'

* .dispatchers=' (PROTOCOL=TCP) (SERVICE=HQMSSXDB)'

* .FAL_CLIENT='primDB'

* .FAL_SERVER='stanDB'

* .job_queue_processes=10

* .LOG_ARCHIVE_CONFIG='DG_CONFIG= (primDB,stanDB)'

* .LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/flash_recovery_area/HQMSS/onlinelog/

VALID_FOR= (ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stanDB'

* .LOG_ARCHIVE_DEST_2='SERVICE=primDB LGWR ASYNC

VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primDB'

* .LOG_ARCHIVE_DEST_STATE_1='ENABLE'

* .LOG_ARCHIVE_DEST_STATE_2='ENABLE'

* .open_cursors=300

* .pga_aggregate_target=343597384

* .processes=750

* .REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'

* .sessions=830

* .sga_max_size=2147483648

* .sga_target=2147483648

* .STANDBY_FILE_MANAGEMENT='AUTO'

* .undo_management='AUTO'

* .undo_tablespace='UNDOTBS1'

* .user_dump_dest='/opt/oracle/admin/HQMSS/udump'

Start the database:

SQL > conn / as sysdba

Connected to an idle instance.

SQL > startup pfile='/opt/oracle/product/10.2.0/db/dbs/initHQMSS.ora'

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 1220460 bytes

Variable Size 469762196 bytes

Database Buffers 1660944384 bytes

Redo Buffers 15556608 bytes

Database mounted.

Database opened.

SQL > create spfile from pfile

File created.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.