{"id":6735,"date":"2019-03-19T13:26:06","date_gmt":"2019-03-19T13:26:06","guid":{"rendered":"https:\/\/ittutorial.org\/?p=6735"},"modified":"2020-08-27T20:31:16","modified_gmt":"2020-08-27T20:31:16","slug":"alter-system-set-command-oracle-database-parameters-change","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/alter-system-set-command-oracle-database-parameters-change\/","title":{"rendered":"Alter System Set Command | Oracle Database Parameters Change"},"content":{"rendered":"

Hi,<\/p>\n

In this article, I will continue to explain the parameter files and their changing.<\/p>\n

\"spfile\"<\/a><\/p>\n

Because the Spfile file does not contain text content, you cannot open the file and change it directly, as in pfile.\u00a0You can change any parameter from SQL * Plus as follows if database using spfile.<\/p>\n\n

 <\/p>\n

[oracle@MehmetSalih ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 19 15:57:55 2019\r\n\r\nCopyright (c) 1982, 2016, Oracle.  All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\r\n\r\nSQL> show parameter log_archive_max_processes;\r\n\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlog_archive_max_processes            integer     4\r\nSQL>\r\n\r\n\r\n\r\n\r\nSQL>\r\nSQL> alter system set log_archive_max_processes=20 scope=BOTH;<\/strong>\r\n\r\nSystem altered.\r\n\r\nSQL> show parameter log_archive_max_processes;<\/strong>\r\n\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlog_archive_max_processes            integer     20\r\nSQL>\r\nSQL><\/pre>\n\n
Since the above parameter is a dynamic parameter, we can directly change the parameters using scope=BOTH<\/strong>\u00a0option and see the effect immediately. But some static parameters are not changed like above with both<\/strong> option.<\/p>\n
You can change this parameters with scope=spfile\u00a0<\/strong>option and see its effect after restart.<\/p>\n
When we type the Scope parameter BOTH, we immediately change it, the dynamic parameters change as above.\u00a0If you change the static parameters in this way, it gives an error and we can change these parameters by using Scope =spfile.\u00a0\u00a0<\/strong>You can see the effect of these parameters when restarting the system.<\/p>\n
 <\/p>\n\n
[oracle@MehmetSalih ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 19 16:05:39 2019\r\n\r\nCopyright (c) 1982, 2016, Oracle.  All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\r\n\r\nSQL> show parameter processes\r\n\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\naq_tm_processes                      integer     1\r\nasm_io_processes                     integer     20\r\ndb_writer_processes                  integer     1\r\ngcs_server_processes                 integer     0\r\nglobal_txn_processes                 integer     1\r\njob_queue_processes                  integer     4000\r\nlog_archive_max_processes            integer     20\r\nprocesses                            integer     300\r\nSQL>\r\nSQL>\r\nSQL>\r\nSQL> alter system set processes=1000 scope=BOTH;\r\nalter system set processes=1000 scope=BOTH\r\n                 *\r\nERROR at line 1:\r\nORA-02095: specified initialization parameter cannot be modified\r\n\r\n\r\nSQL>\r\nSQL> alter system set processes=1000 scope=spfile;\r\n\r\nSystem altered.\r\n\r\nSQL>\r\nSQL> show parameter processes\r\n\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\naq_tm_processes                      integer     1\r\nasm_io_processes                     integer     20\r\ndb_writer_processes                  integer     1\r\ngcs_server_processes                 integer     0\r\nglobal_txn_processes                 integer     1\r\njob_queue_processes                  integer     4000\r\nlog_archive_max_processes            integer     20\r\nprocesses                            integer     300\r\nSQL>\r\n\r\n\r\n\r\n\r\nrestart database and see the effect.\r\n\r\nSQL> shutdown immediate\r\nDatabase closed.\r\nDatabase dismounted.\r\nORACLE instance shut down.\r\nSQL> startup\r\nORACLE instance started.\r\n\r\nTotal System Global Area\u00a0 534462464 bytes\r\nFixed Size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2230072 bytes\r\nVariable Size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 339740872 bytes\r\nDatabase Buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 184549376 bytes\r\nRedo Buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7942144 bytes\r\nDatabase mounted.\r\nDatabase opened.\r\nSQL> show parameter processes;\r\n\r\nNAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALUE\r\n------------------------------------ ----------- ------------------------------\r\naq_tm_processes\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 integer\u00a0\u00a0\u00a0\u00a0 1\r\ndb_writer_processes\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 integer\u00a0\u00a0\u00a0\u00a0 1\r\ngcs_server_processes\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 integer\u00a0\u00a0\u00a0\u00a0 0\r\nglobal_txn_processes\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 integer\u00a0\u00a0\u00a0\u00a0 1\r\njob_queue_processes\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 integer\u00a0\u00a0\u00a0\u00a0 1000\r\nlog_archive_max_processes\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 integer\u00a0\u00a0\u00a0\u00a0 20\r\nprocesses\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 integer\u00a0\u00a0\u00a0\u00a0 1000<\/strong>\r\nSQL>\r\n\r\n<\/pre>\n
 <\/p>\n
\n
\n
\n
\n
\n
In addition, if you are doing a critical change in the spfile, you can guarantee yourself to create pfile from spfile. You have taken backup of spfile with this method.<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n<\/div>\n<\/div>\n
<\/div>\n<\/div>\n<\/div>\n<\/div>\n
<\/pre>\n
[oracle@MehmetSalih ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 19 16:05:39 2019\r\n\r\nCopyright (c) 1982, 2016, Oracle. All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\r\n\r\nSQL>  create pfile='\/home\/oracle\/initDEVECI.ora' from spfile;\r\n\r\nFile created.\r\n\r\nSQL> exit\r\n\r\n[oracle@MehmetSalih ~]$ ls -l initDEV*\r\n-rw-r--r-- 1 oracle oinstall 1450 May 6 11:46 initDEVECI.ora\r\n<\/pre>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Do you want to learn Oracle Database for Beginners, then read the following articles.<\/span><\/h5>\n
https:\/\/ittutorial.org\/oracle-database-19c-tutorials-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Hi, In this article, I will continue to explain the parameter files and their changing.<\/p>\n","protected":false},"author":1,"featured_media":3311,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1994],"tags":[2638,2637,2640,9199,2628,2634,12829,2627,508,12830,12834,12828,1031,2641,1120,2622,2636,2631,2630,2635,2624,2623,1309,2629,2639,1338,1339,1345,1411,1515,12831,1571,12833,2625,2626,12832],"class_list":["post-6735","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle","tag-alter-system-set-log_archive_max_processes","tag-alter-system-set-oracle","tag-alter-system-set-processes","tag-alter-system-set-spfile","tag-create-pfile-from-spfile","tag-create-pfile-oracle","tag-create-spfile","tag-create-spfile-from-pfile","tag-deveci","tag-how-to-change-static-parameter-in-oracle","tag-how-to-rename-parameter-file-in-oracle","tag-how-to-reset-parameter-oracle","tag-mehmet-salih-deveci","tag-ora-02095-specified-initialization-parameter-cannot-be-modified","tag-oracle","tag-oracle-database-parameter-files-spfile-pfile","tag-oracle-database-parameters-change","tag-oracle-oracle-server-parameter-file-pfie-spfile","tag-oracle-parameter-file","tag-oracle-parameter-file-change","tag-oracle-parameter-files","tag-oracle-parameters","tag-oracle-pfile","tag-oracle-server-parameter-file","tag-oracle-show-parameter","tag-oracle-spfile","tag-oracle-spfile-pfile","tag-oracle-support","tag-pfile","tag-salih-deveci","tag-show-parameter-spfile","tag-spfile","tag-vparameter","tag-what-is-the-pfile","tag-what-is-the-spfile","tag-where-the-oracle-startup-parameters-stored-explain-few-of-its-contents"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2013\/10\/spfile.gif","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6735","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=6735"}],"version-history":[{"count":4,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6735\/revisions"}],"predecessor-version":[{"id":18260,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6735\/revisions\/18260"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/3311"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=6735"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=6735"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=6735"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}