IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi,
In this article, I will continue to explain the parameter files and their changing.
Because the Spfile file does not contain text content, you cannot open the file and change it directly, as in pfile. You can change any parameter from SQL * Plus as follows if database using spfile.
[oracle@MehmetSalih ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 19 15:57:55 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> show parameter log_archive_max_processes; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_max_processes integer 4 SQL> SQL> SQL> alter system set log_archive_max_processes=20 scope=BOTH; System altered. SQL> show parameter log_archive_max_processes; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_max_processes integer 20 SQL> SQL>
Since the above parameter is a dynamic parameter, we can directly change the parameters using scope=BOTH option and see the effect immediately. But some static parameters are not changed like above with both option.
You can change this parameters with scope=spfile option and see its effect after restart.
When we type the Scope parameter BOTH, we immediately change it, the dynamic parameters change as above. If you change the static parameters in this way, it gives an error and we can change these parameters by using Scope =spfile. You can see the effect of these parameters when restarting the system.
[oracle@MehmetSalih ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 19 16:05:39 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
asm_io_processes integer 20
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 4000
log_archive_max_processes integer 20
processes integer 300
SQL>
SQL>
SQL>
SQL> alter system set processes=1000 scope=BOTH;
alter system set processes=1000 scope=BOTH
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL>
SQL> alter system set processes=1000 scope=spfile;
System altered.
SQL>
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
asm_io_processes integer 20
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 4000
log_archive_max_processes integer 20
processes integer 300
SQL>
restart database and see the effect.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 339740872 bytes
Database Buffers 184549376 bytes
Redo Buffers 7942144 bytes
Database mounted.
Database opened.
SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 20
processes integer 1000
SQL>
[oracle@MehmetSalih ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 19 16:05:39 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> create pfile='/home/oracle/initDEVECI.ora' from spfile; File created. SQL> exit [oracle@MehmetSalih ~]$ ls -l initDEV* -rw-r--r-- 1 oracle oinstall 1450 May 6 11:46 initDEVECI.ora
Do you want to learn Oracle Database for Beginners, then read the following articles.
https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/
Thanks for your blog post. The things i would like to add is that computer memory is required to be purchased if the computer cannot cope with whatever you do by using it. One can set up two random access memory boards having 1GB each, in particular, but not certainly one of 1GB and one having 2GB. One should check the manufacturer’s documentation for the PC to make sure what type of memory it can take.
The articles published from you is greate. Thank you.