Shulou(Shulou.com)05/31 Report--

This article is about how to modify the parameters of spfile when the Oracle instance is closed. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The specific operations are as follows:

1. Query sga size

SQL > show parameter sgaNAME TYPE VALUE---lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size big integer 1Gsga_target big integer 1G

Second, modify the sga size to 500m

SQL > alter system set sga_target=500M scope=spfile;System altered.

3. Since the specified parameter is spfile, you need to restart instance for the parameter to take effect.

SQL > shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.

4. When restarting instance, prompt that the minimum value of sga should be 668m

SQL > startupORA-01078: failure in processing system parametersORA-00821: Specified value of sga_target 500m is too small, needs to be at least 668m

Solve the problem:

First of all, we need to understand the parameters that the oracle database calls during startup, in the following order:

Spfilesid.ora-> spfile.ora-> initsid.ora- > init.ora from this startup line, if the first spfilesid.ora file cannot be found, it will find two spfile.ora files. If it can not be found, it can start normally. If not, continue to find the next initsid.ora file, until you find the last init.ora file, indicating that if you find one of them, you will start instance normally.

Solution:

Start the database by creating a pfile file, and then use the pfile file as a template to create a spfile. The specific steps are as follows:

1. Create the pfile file with the spfile template and specify the path as the local hard disk location

Create pfile='/oracle/initudevasm.ora' from spfile='+DGDATA02/udevasm/spfileudevasm.ora'

2. Modify the initudevasm.ora file, find the sga_target field, and modify more than 668m.

Vi / oracle/initudevasm.oraudevasm.__db_cache_size=826277888udevasm.__java_pool_size=4194304udevasm.__large_pool_size=8388608udevasm.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environmentudevasm.__pga_aggregate_target=629145600udevasm.__sga_target=1073741824udevasm.__shared_io_pool_size=0udevasm.__shared_pool_size=222298112udevasm.__streams_pool_size=0*.audit_file_dest='/oracle/app/oracle/admin/udevasm/adump'*.audit_trail='db '* .compatible='11.2.0.4.0'*.control_files='+DGDATA02/udevasm/controlfile/current.256.945212019'*.db_block_size=8192*.db_create_file_dest='+DGDATA02'*.db_domain=''*.db_name='udevasm'*.db_recovery_file_dest_size=3221225472*.diagnostic_dest='/oracle/app/oracle'*.dispatchers=' (PROTOCOL=TCP) (SERVICE=udevasmXDB)' udevasm.log_archive_dest_1='LOCATION=+DGRECOVERY/'*.nls_ Language='AMERICAN'*.nls_territory='AMERICA'*.open_cursors=300*.pga_aggregate_target=629145600*.processes=1000*.remote_login_passwordfile='EXCLUSIVE'*.sessions=170*.sga_target= 754288000-- greater than 668M. Undotted tablespaceful UNDOTBS1'

3. Start instance with pfile file

SQL > startup pfile='/ oracle/initudevasm.ora'ORACLE instance started.Total System Global Area 751595520 bytesFixed Size 2257032 bytesVariable Size 603983736 bytesDatabase Buffers 142606336 bytesRedo Buffers 2748416 bytesDatabase mounted.Database opened.

4. Use pfile file as template to create spfile file.

SQL > create spfile='+ DGDATA02/udevasm/spfileudevasm.ora' from pfile='/ oracle/initudevasm.ora'; File created.

5. Close the instance and restart spfile, because the original path has been specified by default when creating the spfile, and the original spfile file has been overwritten, so you can just start it directly.

SQL > shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL > startupORACLE instance started.Total System Global Area 751595520 bytesFixed Size 2257032 bytesVariable Size 603983736 bytesDatabase Buffers 142606336 bytesRedo Buffers 2748416 bytesDatabase mounted.Database opened.

6. Query the modified sga size

SQL > show parameter sgaNAME TYPE VALUE---lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size big integer 720Msga_target big integer 720m

According to the above query, it is judged that it has been modified and restored successfully.

Summary:

1. When the database cannot start normally because of abnormal parameter files, the principle can be understood first, and then the startup problem can be solved by creating pfile and spfile files.

2. Back up the relevant database parameter files to prevent misoperation from causing the database to fail to start normally

Thank you for reading! This is the end of the article on "how to modify the parameters of spfile when the Oracle instance is closed". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.